Oracle 12c:New feature ------- Logging DDL data..

Oracle 12c:New feature ------- Logging DDL data..





Oracle had introduced a cool feature in version 11g, where we were able to log or track DDL statements executed in the database by means of a parameter called ENABLE_DDL_LOGGING without setting up database auditing. Setting ENABLE_DDL_LOGGING to TRUE results in logging DDL statements in to the instance alert log (s). Since the DDL statements are logged into alert log file, it becomes a hard task to scan through the alert log and find the DDL logs.

Oracle has made a significant change in terms of DDL logging with version 12c. In Oracle 12c, the DDL logs are maintained in dedicated DDL log file (s) unlike the instance alert log file which was the case with Oracle 11g. This makes it easier to track DDL statements executed in a database.

In 12c, Oracle maintains the DDL logs in two files (XML and plain text) under the ADR_HOME as listed below.

    XML Version: $ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl/log.xml
    Text Version: $ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl_${ORACLE_SID}.log

As per the Oracle documentation, setting ENABLE_DDL_LOGGING to TRUE will log following DDL statements executed in a database.

    ALTER/CREATE/DROP/TRUNCATE CLUSTER
    ALTER/CREATE/DROP FUNCTION
    ALTER/CREATE/DROP INDEX
    ALTER/CREATE/DROP OUTLINE
    ALTER/CREATE/DROP PACKAGE
    ALTER/CREATE/DROP PACKAGE BODY
    ALTER/CREATE/DROP PROCEDURE
    ALTER/CREATE/DROP PROFILE
    ALTER/CREATE/DROP SEQUENCE
    CREATE/DROP SYNONYM
    ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE
    ALTER/CREATE/DROP TRIGGER
    ALTER/CREATE/DROP TYPE
    ALTER/CREATE/DROP TYPE BODY
    DROP USER
    ALTER/CREATE/DROP VIEW

There are some discrepancies while using ENABLE_DDL_LOGGING for tracking DDL statements particularly in the context of multi tenant architecture. In this post, I will go through a quick demonstration to explore how this feature works and what are the discrepancies associated with this feature.

Let’s start with our demonstration. By default, DDL logging is not enabled as we can find by querying the v$parameter view.

   
---//
---// DDL logging is disabled by default //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select name,value,default_value from v$parameter where name='enable_ddl_logging';

NAME                      VALUE      DEFAULT_VALUE
------------------------- ---------- ---------------
enable_ddl_logging        FALSE      FALSE

Let’s enable DDL logging in our database by setting ENABLE_DDL_LOGGING to TRUE as shown below. My demonstration is targeted against a Oracle 12c (12.1.0.2) container database to understand how the feature works in a multi tenant environment.

---//
---// Enable DDL logging //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter system set enable_ddl_logging=TRUE;

System altered.

---//
---// Validate DDL logging is enabled in the database //---
---//

SQL> select name,value,default_value from v$parameter where name='enable_ddl_logging';

NAME                      VALUE      DEFAULT_VALUE
------------------------- ---------- ---------------
enable_ddl_logging        TRUE       FALSE

At this point, we have enabled DDL logging for our container database. However, there is no log file created yet as we haven’t performed any DDL after enabling the DDL logging. This can be confirmed by looking to the DDL log locations as shown below.

   
##---
##--- DDL log files are not created yet ---##
##---
[oracle@test1 ~]$ cd /app/oracle/diag/rdbms/OCDB1/OCDB1/log/ddl/
[oracle@test1 ddl]$ ls -lrt
total 0

Let’s perform few DDL statements on both container (CDB$ROOT) and pluggable (CPDB1) databases and observe how these statements are logged by Oracle.

---//
---// executing DDL in root(CDB$ROOT) container //---
---//
16:24:02 SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

16:24:16 SQL> create user utest identified by utest;

User created.

16:24:29 SQL> drop user utest cascade;

User dropped.


---//
---// connecting to PDB CPDB1 //---
---//
16:24:36 SQL> conn ktest@CPDB1
Enter password:
Connected.

---//
---// executing DDL in pluggable database CPDB1 //---
---//
16:24:49 SQL> show con_name

CON_NAME
------------------------------
CPDB1

16:25:34 SQL> create table test as select * from all_users;

Table created.

16:25:48 SQL> create table test1 as select * from test;

Table created.

16:26:04 SQL> truncate table test1;

Table truncated.

16:26:13 SQL> drop table test purge;

Table dropped.

We have performed a number of DDL statements in both root (CDB$ROOT) and pluggable (CPDB1) databases. We can now see the existence of respective DDL log files as shown below.

   
##---
##--- DDL logs are created after DDL execution ---##
##---
[oracle@test1 ddl]$ pwd
/app/oracle/diag/rdbms/OCDB1/OCDB1/log/ddl
[oracle@test1 ddl]$ ls -lrt
total 4
-rw-r----- 1 oracle dba 1650 May  8 16:26 log.xml

[oracle@test1 log]$ pwd
/app/oracle/diag/rdbms/OCDB1/OCDB1/log
[oracle@test1 log]$ ls -lrt ddl_${ORACLE_SID}.log
-rw-r----- 1 oracle dba 225 May  8 16:26 ddl_OCDB1.log

Let’s see what is logged in the DDL log files for these DDL statements. First, let’s take a look into the text version of the logs
   
##---
##--- DDL logs from text version of log file ---##
##---
[oracle@test1 log]$ cat ddl_OCDB1.log
diag_adl:drop user utest cascade
Sun May 08 16:25:48 2016
diag_adl:create table test as select * from all_users
diag_adl:create table test1 as select * from test
diag_adl:truncate table test1
diag_adl:drop table test purge

We can see that all the DDL statements are logged (except the “create user” statement as that is not supported) into the log file. However, looks like the log file is just a sequence of DDL statements without any context of those statements. There are some vital information missing from this log file. If you observe it closely, you will find that TIMESTAMP information is missing for most of the statements. I was also expecting the container information to be associated with each of the logged DDL statement. However, that information is not there in the log file. Since, we are dealing with multi tenant database; the container information is very much required to be able to determine in which container a particularly DDL statement was executed. Without the container details, these DDL logging could not server any purpose.

Now let’s take a look into the XML version of the DDL log file to see if we can find any missing information there.

   
##---
##--- DDL logs from XML version of log file ---##
##---
[oracle@test1 ddl]$ cat log.xml
<msg time="2016-05-08T16:24:36.772+05:30" org_id="oracle" comp_id="rdbms" msg_id="opiexe:4409:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15" version="1">
 <txt>drop user utest cascade
 </txt>
</msg>
<msg time="2016-05-08T16:25:48.587+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
 <txt>create table test as select * from all_users
 </txt>
</msg>
<msg time="2016-05-08T16:26:04.508+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
 <txt>create table test1 as select * from test
 </txt>
</msg>
<msg time="2016-05-08T16:26:13.874+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
 <txt>truncate table test1
 </txt>
</msg>
<msg time="2016-05-08T16:26:25.462+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
 <txt>drop table test purge
 </txt>
</msg>




The XML version of the log file looks to be more informative than the text version. We have now additional details available for each of the DDL statements executed like the TIMESTAMP of the DDL along with some session specific details like host_id and host_addr

However, the logs are still not sufficient for a multi tenant container database. The container information is still missing from the logs and we can’t rely on it to track on which container a particular DDL statement was executed
Footnote:

ENABLE_DDL_LOGGING feature can be considered for use in Oracle 11g or Oracle 12c non-CDB database. We must query the XML version of the log file for detailed information related to a DDL statement rather than querying the text version as the text version seems to miss the TIMESTAMP and session specific information for most DDL statements. Moreover, ENABLE_DDL_LOGGING feature seems not to be an ideal option for DDL tracking in a multi tenant container database considering the fact that it doesn’t log the container details of DDL statements and in turn serves no purpose for tracking.

Controlfile restore from Primaey database to Standby

Below are the steps to accomplish the task :

Step 1: Create the Standby control file on primary database
 Step 2: Copy the controlfile backup to the standby system
 Step 3: Shutdown, restore, rename.

Example

Step 1 : Create the Standby control file on primary database.

$ export ORACLE_SID=test1
 $rman target /
 RMAN> backup current controlfile for standby format 'stdbyctl.bkp';
 RMAN> EXIT;

stdbyctl.bkp file will be created in "$ORACLE_HOME/dbs" (Unix) or "$ORACLE_HOME/database" (Windows).

Step 2. Copy the controlfile backup to the standby system

Using ftp/scp move stdbyctl.bkp to standby system

Step 3: Shutdown, restore, rename.

A. Shutdown all instances of the standby.

$ export ORACLE_SID=test
 $sqlplus / as sysdba
 SQL> shutdown immediate
 ORA-01109: database not open


Database dismounted.
 ORACLE instance shut down.

B. Depending on the location of the logfiles on the standby server remove all online and standby redo logs from the standby directories Using an Operating System utility or ASMCMD and make sure that you have the LOG_FILE_NAME_CONVERT parameter defined to translate any directory paths.

C. Startup one instance of Standby database in nomount stage:

$sqlplus / as sysdba
 SQL> startup nomount
 ORACLE instance started.

Total System Global Area 209715200 bytes
 Fixed Size 1248116 bytes
 Variable Size 75498636 bytes
 Database Buffers 125829120 bytes
 Redo Buffers 7139328 bytes

D. Connect to RMAN with nocatalog option and Restore the standby control file:

$rman nocatalog target /
 RMAN> restore standby controlfile from '\tmp\stdbyctl.bkp';

Starting restore at 29-AUG-08
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
 output filename=+DATA1/test1/controlfile/current.257.661096899
 Finished restore at 29-AUG-08

E. Mount standby database

RMAN> alter database mount;

database mounted

F. Catalog the datafiles of standby database

Below command will give you a list of files and ask if they should all be catalog. Review the list and say YES if all the datafiles are properly listed
 In below command while cataloging the files, the string specified should refer to the diskgroup/filesystem destination of the standby data files.

RMAN> catalog start with '+DATA1/test/DATAFILE/';

Starting implicit crosscheck backup at 29-AUG-08
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=155 devtype=DISK
 Crosschecked 10 objects
 Finished implicit crosscheck backup at 29-AUG-08

Starting implicit crosscheck copy at 29-AUG-08
 using channel ORA_DISK_1
 Finished implicit crosscheck copy at 29-AUG-08

searching for all files in the recovery area
 cataloging files...
 cataloging done

List of Cataloged Files
 =======================
 File Name: +fra/test/BACKUPSET/2008_07_28/nnndf0_TAG20080728T113319_0.296.661260801
 File Name: +fra/test/BACKUPSET/2008_07_28/ncsnf0_TAG20080728T113319_0.297.661260847
 File Name: +fra/test/CONTROLFILE/backup.272.661096103

searching for all files that match the pattern +DATA1/test/DATAFILE/

List of Files Unknown to the Database
 =====================================
 File Name: +data1/test/DATAFILE/SYSTEM.258.661097855
 File Name: +data1/test/DATAFILE/SYSAUX.259.661097855
 File Name: +data1/test/DATAFILE/UNDOTBS1.260.661097855
 File Name: +data1/test/DATAFILE/USERS.261.661097855

Do you really want to catalog the above files (enter YES or NO)? YES
 cataloging files...
 cataloging done

List of Cataloged Files
 =======================
 File Name: +data1/test/DATAFILE/SYSTEM.258.661097855
 File Name: +data1/test/DATAFILE/SYSAUX.259.661097855
 File Name: +data1/test/DATAFILE/UNDOTBS1.260.661097855
 File Name: +data1/test/DATAFILE/USERS.261.661097855

NOTE:
 a) This will only work if you are using OMF. If you are using ASM without OMF you have to catalog all non-OMF Datafiles as Datafile Copies manually using

RMAN> catalog datafilecopy '<File-Specification>';

b) If you have Datafiles on different Diskgroups you have to catalog from all Diskgroups, of course.


G. Commit the changes to the controlfile

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA1/test/datafile/system.258.661097855"
 datafile 2 switched to datafile copy "+DATA1/test/datafile/undotbs1.260.661097855"
 datafile 3 switched to datafile copy "+DATA1/test/datafile/sysaux.259.661097855"
 datafile 4 switched to datafile copy "+DATA1/test/datafile/users.261.661097855"

RMAN> EXIT;

H. Re-enable flashback on the standby database.
 $sqlplus / as sysdba
 SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

I. Query v$log and clear all online redo log groups

SQL> select group# from v$log;

GROUP#
 ----------
 1
 2
 3

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

J. Query v$standby_log and clear all standby redo logs

SQL> select group# from v$standby_log;

GROUP#
 ----------
 4
 5
 6

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database clear logfile group 5;

Database altered.

SQL> alter database clear logfile group 6;

Database altered.

Recreate the standby redo logs on standby database if standby redo logs are not present on the primary.

SQL> select group# from v$standby_log;

no row selected

SQL> alter database add standby logfile group 4 size 50m;

Database altered.

SQL> alter database add standby logfile group 5 size 50m;

Database altered.

SQL> alter database add standby logfile group 6 size 50m;

Database altered.

K. Start Managed recovery process on standby

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> exit

Oracle12c - The Top New 12c Features of Oracle 12c! (Part 1)

Oracle 12c Upgrade 112040 to 121010

Oracle Database 12c Multitenant Architecture Overview