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.
