Well,
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
declare
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%';
begin
for
dbevent in dba_events LOOP
DBMS_OUTPUT.PUT_LINE('Statement : ' || dbevent.event1 );
execute immediate dbevent.event1;
end LOOP;
EXCEPTION
when no_data_found then
null;
when others then
p_errbuf := sqlerrm;
p_retcode := 1;
DBMS_OUTPUT.PUT_LINE('Error : ' || p_errbuf|| 'Error Code' || p_retcode );
end;
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.
This is Just stuff I find on various echnologies ranging from databases to Cloud related tech. This Blog will have topics and content based on things i learn. I can also be found at http://www.twitter.com/fuadar
Monday, March 30, 2009
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 ....
>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 ....
Subscribe to:
Posts (Atom)