Monday, March 30, 2009

Applying Datafile resize in logical standby

with the record_unsupported_operations-true . I was able to capture the commands that oracle skipped. using that info i was able to write a simple piece of code to use that information to get
Alter database datafile commands executed on my logical standby database which in this case has exactly the same structure as my primary.

/* Program Name : Datafile Alter command capture
Author : Fuad Arshad
Purpose : To capture alter database datafile commands from dba_logstdby_events
and execute on logical standby
Prerequisites : dbms_logstdby.apply_set('RECORD_UNSUPPORTED_OPERATIONS','TRUE');
dbms_LOGSTDBY.apply_set('MAX_EVENTS','a value of your choosing based on events ');
Licensed : Creative Commons.

set serveroutput on
p_errbuf varchar2(500);
p_retcode number(1);
cursor dba_events is
SELECT to_char(event) event1 FROM dba_logstdby_events WHERE status_code='16226'and event_timestamp>systimestamp-1
and event like 'ALTER DATABASE DATAFILE%';
dbevent in dba_events LOOP
DBMS_OUTPUT.PUT_LINE('Statement : ' || dbevent.event1 );
execute immediate dbevent.event1;
end LOOP;
when no_data_found then
when others then
p_errbuf := sqlerrm;
p_retcode := 1;
DBMS_OUTPUT.PUT_LINE('Error : ' || p_errbuf|| 'Error Code' || p_retcode );

This is a very simple way of making sure all the datafiles on the standby look exactly the same as the Primary. I'm sure there are many other ways to do this and if there are please share how u would have taken care of the alter database datafile issue. Comments and improvements as well as ideas are always welcome.

Monday, March 23, 2009

DBMS_LOGSTDBY and recording unsupported operations

DBMS_LOGSTDBY is the heart and soul of the logical standby management infrastructure . While trying to figure out how to get my alter database datafile operations . i bumped in a record unsupported operations in $logstdby_stats . There seems to be no mention in the documentation and no reference to this parameter being set anywhere . If you to set it though

>exec dbms_logstdby.apply_set('RECORD_UNSUPPORTED_OPERATIONS','TRUE');

This will help capture the unsupported operations as the ORA-16226 operations. I am planning on then reading the clob and re executing the statements onto the logical standy. I still have to figure that part out but stay tuned ....

Google Search