Monthly Archives: November 2013

Standby Redo Log Issues After Create of Standby

We ended up recreating our standby’s at the DR site unrelated to any Oracle issue which is always good.  However, when the duplication finished the standby redo logs were not available.

RFS[12]: No standby redo logfiles available for thread 1

Our first step was checking to see if the standby logs existed and whether they were being used by checking the v$standby_log view.  The status showed unassigned and the no SCN was listed.

Then I found the following from John Hallas that explained different in sizes between the primary and secondary can be a cause:

http://jhdba.wordpress.com/2011/02/28/incorrectly-sized-standby-redo-logs/

Comparing the sizes they were different sizes 200mb secondary for 600mb on primary.

Time to recreate the the standby redo logs with the correct size matching the primary.  I pull the standby redo log scripts dynamically on the primary to drop and then recreate the log files.  Again John Hallas does a really good job providing this information.

http://jhdba.wordpress.com/2011/02/28/scripts-to-resize-standby-redolog-files/

 

ORA-16224 Database Guard Is Enabled

Recently encountered this error after a roll out in production.  Several hundred procedures were invalid on the logical standby.  I was unable to recompile and received an ORA-16224 Database Guard is enabled error.

I checked the guard_status in v$database.  Usually this error is thrown when it is set to ALL, however, in this case it was set to Standby.  I was interested in disabling the guard which could be done with:

alter database guard none;   < other options include standby, which it was already set to and all, which wouldn’t have worked>

However, this can also be set at the session level:

alter session disable guard;

That turns it off for my session.  I wiped up a quick anonymous block to recompile all invalid procedures.

declare

sSQL varchar2(4000);

begin

for i in (select object_name from

dba_objects where owner = ‘MY_SCHEMA’

and status = ‘INVALID’

and object_type = ‘PROCEDURE’)

loop

sSQL:=’alter procedure my_schema.’||i.object_name||’ compile’;

execute immediate sSQL;

end loop;

end;

/

Once that executed successfully and just turned the guard status back to standby in my session:

alter session enable guard;

Had the end user attempt to run report again.  Success.

Instantiate A Table On Logical Standby

Seems today is the day for logical standby issues.  Two tables received a conflict, which we have suffered with previously and applied a patch.  It’s interesting we started receiving this again, however, I’m going to skip to how I resolved the immediate issue syncing the logical objects and allowing the SQL Apply to continue.  Will need a MOS for the other issue.

I started with trying to use DBMS_LOGSTDBY.INSTANTIATE_TABLE, which is nice and convenient since it uses a dblink — no moving files around.

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE(schema_name=>’MySchema’, table_name=>’TABLE1′, dblink=>’mydb’);

An error is thrown issue with a reference constraint.  Problem the parent table is out of sync as well.

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE(schema_name=>’MySchema’, table_name=>’PARENT1′,dblink=>’mydb’);

An error is thrown due to the PARENT1 having a LONG datatype column.

So now I’m forced to move files around using datapump.  I already have datapump directories defined on both databases so I won’t cover that task.

These tables are not updated frequently usually only throw a weekly file load.

alter database stop logical standby apply;

expdp system dumpfile=logical.dmp directory=data_pump_dir tables=myschema.parent1, myschema.table1

Once completed I move the dumpfile over to the logical database server under the appropriate directory.

impdp system dumpfile=logical.dmp directory=data_pump_dir tables=myschema.parent1, myschema.table1

alter database start logical standby apply immediate;

Then I watched the SQL Apply process close the lag.