Record DDL Statements in DBA_LOGSTDBY_EVENTS & alert.log

This week, I am teaching another 11g Data Guard course in Kista (Sweden, near Stockholm). Our education center is there in a quiet area (Kronborgsgränd 17, 164 28 Kista) together with other buildings rented by mostly IT companies:

kistaOne nice little feature a came across during that course is the possibility to get DDL statements recorded in the alert.log file and in the DBA_LOGSTDBY_EVENTS view. If I recall that right, that was even the default with 10g Data Guard Logical Standby, but in 11g, you have to do the following at the Logical Standby for it:

SQL> exec dbms_logstdby.apply_set('RECORD_APPLIED_DDL','TRUE')
PL/SQL procedure successfully completed.
SQL> exec dbms_logstdby.apply_set('EVENT_LOG_DEST','DEST_ALL')
PL/SQL procedure successfully completed.
SQL> connect scott/tiger@prima
Connected.
SQL> create table test as select * from dept;
Table created.

The second exec lead to the additional recording of te DDL into the alert.log file, else it would only be visible in DBA_LOGSTDBY_EVENTS. Following is from the alert.log then:

Wed Jun 17 08:18:11 2009
LOGSTDBY: APPLY_SET: RECORD_APPLIED_DDL changed to TRUE
Wed Jun 17 08:18:43 2009
LOGSTDBY: APPLY_SET: EVENT_LOG_DEST changed to DEST_ALL
Wed Jun 17 08:18:43 2009
LOGSTDBY status: EVENT_LOG_DEST changed to DEST_ALL
Wed Jun 17 08:19:17 2009
LOGSTDBY stmt: create table test as select * from dept
LOGSTDBY status: ORA-16204: DDL successfully applied
About these ads

,

  1. Blogroll Report 12/06/09 – 19/06/09 « Coskan’s Approach to Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,429 other followers

%d bloggers like this: