Wednesday, September 29, 2010

Logical Standby with Apply Process Fails With ORA-308

Recently, we encounter the Logical Standby Apply Process Fails With ORA-308 while testing the logical standby. Due to the archive log in Logical is automatically delete after a while.

To solve it, we first stop the automatically delete archivelog
exec dbms_logstdby.apply_set('LOG_AUTO_DELETE', 'FALSE');


Then, We check the ASM diskspace for the archivelog that is needed by standby.
Then we copy the archive log to the local folder for transfering it to standby by issuing below . The FLASH_DIR and EXPORT_DUMP_DIR is oracle directory, FLASH_DIR is the ASM disk space like +FLASHGRP/TESTDB/archivelog/

dbms_file_Transfer.copy_file('FLASH_DIR','1_3470_694714878.dbf','EXPORT_DUMP_DIR','1_3470_694714878.dbf');


After copying the file to standby, then we need to register the archivelog using below method.

Register the log file in standby

SQL> alter database register logfile '/TESTDB/archive/1_3470_694714878.dbf';

alter database register logfile '/TESTDB/archive/1_3470_694714878.dbf'

*

ERROR at line 1:

ORA-01289: cannot add duplicate logfile



If failed , then we have to update the logmnr table manually,

Stopped the logical standby.


SQL> alter database stop logical standby apply;

SQL> select file_name from system.logmnr_log$ where sequence#=3470;

FILE_NAME

------------------------------------------------------------------------------------------------------------------------------------------------------

+FLASHGRP/testdb/archivelog/2010_09_22/thread_1_seq_3470.3572.730402825

SQL> UPDATE SYSTEM.LOGMNR_LOG$ SET

FILE_NAME= 2 '/TESTDB/archive/1_3470_694714878.dbf' where sequence#=3470;

1 row updated.

SQL> commit;

Commit complete.


SQL> alter database start logical standby apply;

Database altered.




References:-
Logical Standby Apply Process Fails With ORA-308 [ID 274676.1]

Thursday, September 16, 2010

Oracle Error : ORA-21779 duration not active

Recently we encounter the error ORA-21779 duration not active, in the alert log. This error keeps repeating.

ORA-21779: duration not active
Cause: User is trying to use a duration that has been terminated.
Action: User should avoid performing such operation.


In the trace file , you will notice the below.

Errors in file ora_smon_610346.trc:
ORA-21779: duration not active
Fri Sep 17 14:25:54 2010


There are few problem that cause this, in below url show other issues. In our case this error keep repeating after few seconds. To solve this we do this.

After flushing the share pool , the error is no longer appeared

alter system flush shared_pool;

References:-

http://oraclequirks.blogspot.com/2006/07/ora-21779-brain-teaser.html