There are quite a lot of new features in the 12c database that I like and have discussed and talked about them at various events. On this #ThanksODC day, I would like to take the opportunity to thank Oracle Technology Network (or Oracle Developer Community) and Oracle ACE Program for all the support they provide to the DBA and Developer community to share and gain knowledge.

Two simple addition to the 12c database makes my life much easier on the security front. The requirement was to lock the user accounts that are not logged into the database for consecutive 60 days.

1.  Last Login Time 

In releases prior to Oracle Database 12c 12.1 to find out when a user last successfully logged into the database, we need to enable session auditing, which has some overhead and the DBA needs to set up routines to clean the audit table, etc. In 11g database, I had to enable session auditing (keep the session audit records for at least 61 days) and write a SQL querying DBA_USERS and DBA_AUDIT_SESSION to list out all the active users who did not use the database for 60 days.

In 12.1, Oracle automatically registers the successful login time in SYS.USER$ table and is visible in DBA_USERS. The LAST_LOGIN column of DBA_USERS shows the last login time of the user in the database. In 12.1 database, my requirement is met by writing a simple SQL against DBA_USERS using the LAST_LOGIN column! No auditing required. The result is used to build dynamic SQL to lock each account using ALTER USER ACCOUNT LOCK statement (similar to 11g).

BTW, have you noticed, when you login to the database using SQL*Plus, you will see the last login time displayed as well.

2.  Inactive Account Lock

My requirement got completely automated in 12.2 database. Oracle introduced the INACTIVE_ACCOUNT_TIME parameter for user profiles. The INACTIVE_ACCOUNT_TIME profile parameter locks a user account that has not logged in to the database instance in a specified number of days. The default value for INACTIVE_ACCOUNT_TIME is 35. The minimum setting is 15 and the maximum is 24855.

Thanks, Oracle!

#ThanksOTN

#ThanksODC

And thank you, @OracleBase, for doing the appreciation day again!

A new pre-built VirtualBox image is available for download  from OTN (updated on March 27, 2017).

Once you download, import and configure the VM, you can pretty much do whatever you want. But Roy and Mike have put together a very good lab environment and exercise for you to practice Oracle database upgrade from 11gR2 to 12cR2. The lab includes

  • Manual upgrade of 11.2.0.4 database to 12.2.0.1
  • Attach 12c non-container database as a PDB to existing 12.2.0.1 CDB
  • Upgrade 11.2.0.4 database using Full Transportable Export/Import to 12.1.0.2 as a PDB
  • Upgrade 12.1.0.2 PDB to 12.2.0.1 using unplug / plug method

The pre-built VM comes with Oracle software already installed.

  • 11gR2 11.2.0.4 – /u01/app/oracle/product/11.2.0.4
  • 12cR1 12.1.0.2 – /u01/app/oracle/product/12.1.0.2
  • 12cR2 12.2.0.1 – /u01/app/oracle/product/12.2.0.1

Practice upgrades, new features, patches, PSU, migration using this VirtualBox image.

Thanks to Mike Dietrich and Roy Swonger!

VirtualBox Download

12c Database Image Download

 

Oracle *daily* TidBits” (#oratidbit) published on Facebook, Twitter and Google+ during weekdays in December 2016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well… Hope you find these helpful to learn something new or to remind you of its existence and use.

OraTidBit
#oratidbit #DB12cR2 You can capture Automatic Workload Repository (AWR) data for Oracle Active Data Guard standby databases. This feature enables performance tuning of Oracle Active Data Guard standby databases.
#oratidbit #DB12cR2 INACTIVE_ACCOUNT_TIME initialization parameter automatically locks the database user account who has not logged in to the database instance in a specified number of days.
#oratidbit #db12cR2 You can prioritize the PDBs in a CDB when you upgrade the CDB. The PDBs with higher priority are upgraded before PDBs with lower priority.
#oratidbit #db12cR2 A CDB can run in local undo mode or shared undo mode. Local undo mode means that every container in the CDB uses local undo. Shared undo mode means that there is one active undo tablespace for a single-instance CDB.
#oratidbit #db12cR2 In 12.2, tables with BFILE columns can be redefined online.
#oratidbit #db12cR2 In 12.2, Query the V$ONLINE_REDEF view to monitor the progress of an online table redefinition operation.
#oratidbit #db12cR2 In 12.2, SYSRAC is a new administrative privilege for tasks related to Oracle Real Application Clusters (#Oracle #RAC) operations.
#oratidbit #DB12cR2 ALLOW_GROUP_ACCESS_TO_SGA is new parameter and controls group access to shared memory on UNIX platforms. The default value is false, which means that database shared memory is created with owner access only. In Oracle Database releases prior to Oracle Database 12c Release 2 (12.2.0.1), database shared memory was created with owner and group access.
#oratidbit #DB12cR2 AWR_PDB_AUTOFLUSH_ENABLED is new parameter and enables you to specify whether to enable or disable automatic Automatic Workload Repository (AWR) snapshots for all the PDBs in a CDB or for individual PDBs in a CDB.The default value of AWR_PDB_AUTOFLUSH_ENABLED is false. Thus, by default, automatic AWR snapshots are disabled for all the PDBs in a CDB. When you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the CDB root, the new value takes effect in all the PDBs in the CDB. Therefore, if you change the value of AWR_PDB_AUTOFLUSH_ENABLED in the CDB root to true, the value of AWR_PDB_AUTOFLUSH_ENABLED is also changed to true in all of the PDBs, so that automatic AWR snapshots are enabled for all the PDBs. You can also change the value of AWR_PDB_AUTOFLUSH_ENABLED in any of the individual PDBs in a CDB, and the value that is set for each individual PDB will be honored. This enables you to enable or disable automatic AWR snapshots for individual PDBs.
#oratidbit #DB12cR2 ENABLE_AUTOMATIC_MAINTENANCE_PDB is new parameter and can be used to enable or disable the running of automated maintenance tasks for all the PDBs in a CDB or for individual PDBs in a CDB. When you change the value of ENABLE_AUTOMATIC_MAINTENANCE_PDB in the CDB root, the new value takes effect in the root and in all the PDBs in the CDB. You can also change the value of ENABLE_AUTOMATIC_MAINTENANCE_PDB in any of the individual PDBs in a CDB, and the value that is set for each individual PDB will be honored. This enables you to enable or disable automated maintenance tasks for individual PDBs.
#oratidbit #DB12cR2 ENABLED_PDBS_ON_STANDBY is new parameter and specifies which pluggable databases (PDBs) to replicate on an Oracle Data Guard standby database. The asterisk (*) and question mark (?) wildcard characters are supported.
#oratidbit #DB12cR2 REMOTE_RECOVERY_FILE_DEST is new parameter and specifies a directory from which to read archive log files during a pluggable database (PDB) refresh operation if the source is not available.
#oratidbit #DB12cR2 UNIFORM_LOG_TIMESTAMP_FORMAT is new parameter and specifies that a uniform timestamp format be used in Oracle Database trace (.trc) files. When the value of UNIFORM_LOG_TIMESTAMP_FORMAT is TRUE, the format used for timestamps in trace files is standardized on universal time with millisecond precision. When the value of UNIFORM_LOG_TIMESTAMP_FORMAT is FALSE, trace files include a mix of timestamps using different precisions, with some timestamps showing local time and other timestamps showing universal time.
#oratidbit Before you create a DBaaS instance on Oracle Database #Cloud (#DBaaS), you must setup an SSH public/private key pair to connect to compute node and optionally Oracle storage cloud service container to backup the database to cloud.
#oratidbit Use dbaascli utility on Oracle #DBaaS cloud deployments to perform a variety of life-cycle and administration operations including patching, database recovery and data guard switchover.
#oratidbit Use raccli utility #DBaaS cloud deployments that use Oracle Real Application Clusters (RAC) to perform a variety of life-cycle and administration operations including backup, recovery, patching, security configurations and so on.
#oratidbit Use dbpatchmdg utility #DBaaS cloud deployments that are configured with Oracle Data Guard to perform a variety of patching operations.
#oratidbit Use the oracle-dbcs-cli utility on your Linux computer to connect to Oracle Cloud and perform a variety of life-cycle and administration operations on Oracle Database Cloud Service deployments (non RAC).
#oratidbit To use #Oracle Database Backup Cloud Service you’ll subscribe to the service, install the Oracle Database Cloud Backup Module, and configure your environment to send backups to the cloud using familiar RMAN commands.
#oratidbit Oracle Database Exadata Express Cloud Service delivers a managed Oracle Database 12c Release 2 Enterprise Edition with options, running on Oracle Exadata engineered systems.
#oratidbit #DB12cR2 In 12.2, the maximum length for most database object names has increased from 30 bytes to 128 bytes.
#oratidbit #DB12cR2 In 12.2, you can encrypt undo tablespaces, temporary tablespaces, and the SYSTEM and SYSAUX tablespaces. Also, encrypt both offline and online tablespaces.
%d bloggers like this: