what the blog?

{upgrade in progress..}

4

MRP0: Background Media Recovery terminated with error 19909

How to solve ORA-19909 error when re-sync dataguard, when the standby database shutdown ungracefully, sometime you may found an error when you put the standby database back online, in this scenario, I try to active the standby database and flash back to standby mode in order to let standby database re-apply archive logs again.

Error message as below

MRP0: Background Media Recovery terminated with error 19909

ORA-19909: datafile 1 belongs to an orphan incarnation

Setup flashback recovery (on Standby DB)

SQL> alter system set db_recovery_file_dest_size=<size>;

SQL> alter system set db_recovery_file_dest=’<location to store the flash recovery area>’;

Defer log transmission on primary database (on Primary DB)

SQL> alter system set log_archive_dest_state_2=defer scope=both;

SQL> alter system archive log current;

Stop standby mode and create restore point (on Standby DB)

SQL> alter database recover managed standby database cancel;

SQL> create restore point my_resotre_point guarantee flashback database;

Activate standby database (On Standby DB)

SQL> alter database activate standby database;

SQL> alter database set standby database to maximize performance;

SQL> alter database open;

Once database opened without errors, shut it down (on Standby DB)

SQL> shutdown immediate;

Flash back to restore point and revert to standby database (on Standby DB)

SQL> startup mount;

SQL> flashback database to restore point my_restore_pint;

SQL> alter database covert to physical standby;

SQL> startup mount force;

Enable managed recovery mode on standby database (on Standby DB)

SQL> alter database recover managed standby database using current logfile disconnect from session;

Enable log transitions on primary database (on Primary DB)

SQL> alter system set log_archive_dest_state_2=enable scope=both;

Monitor MRP status

select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

when you found

How to solve ORA-19909 error when re-sync dataguard, when the standby database shutdown ungracefully, sometime you may found an error when you put the standby database back online, in this scenario, I try to active the standby database and flash back to standby mode in order to let standby database re-apply archive logs again.
Error message as below
MRP0: Background Media Recovery terminated with error 19909
ORA-19909: datafile 1 belongs to an orphan incarnation
Setup flashback recovery
SQL> alter system set db_recovery_file_dest_size=<size>;
SQL> alter system set db_recovery_file_dest=’<location to store the flash recovery area>’;
Defer log transmission on primary database
SQL> alter system set log_archive_dest_state_2=defer scope=both;
SQL> alter system archive log current;
Stop standby mode and create restore point
SQL> alter database recover managed standby database cancel;
SQL> create restore point my_resotre_point guarantee flashback database;
Activate standby database
SQL> alter database activate standby database;
SQL> alter database set standby database to maximize performance;
SQL> alter database open;
Once database opened without errors, shut it down
SQL> shutdown immediate;
Flash back to restore point and revert to standby database
SQL> startup mount;
SQL> flashback database to restore point my_restore_pint;
SQL> alter database covert to physical standby;
SQL> startup mount force;
Enable managed recovery mode on standby database
SQL> alter database recover managed standby database using current logfile disconnect from session;
Enable log transitions on primary database
SQL> alter system set log_archive_dest_state_2=enable scope=both;
Monitor MRP status
select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

MRP     WAIT_FOR_GAP     1     2221    0    0

you may try to copy those gapped archive log from primary database to standby database manually.

oracle

levin • September 2, 2009


Previous Post

Next Post

Comments

  1. Givre August 18, 2010 - 10:02 pm Reply

    Hi !
    Thank you for this informations.

    I had the same problem on my Physical stanbdy , and now all it’s ok :)
    I dont have Gapped archive log and the process MRP is now running correctly

    I dont understand why with this senario, the database re-apply archive logs again..
    Witch archive logs ? since what time ?

    Can you givre me more explication about this scenario ?

    Thanks a lot.

    Regards,
    Benoit

  2. levin August 19, 2010 - 9:53 am Reply

    Hi Benoit,

    Thanks for your comment, I got this error twice after power outage in the DR site , and it only affect on Windows 2003 platform too. At that moment I couldn’t found any resolution from metalink, so I just try to resolve the problem by reactivate the standby db and put it back to physical standby. Luckily the problem disappeared, this is just a workaround and may not be a standard resolution.

    According to the gapped log, for my case, I found this is log 2221 is missing from standby database archive directory, and the archive date is 2 days ago. 19909 error happens on 1 day after this log.

    When I try to apply the workaround and re-establish the sync process, the primary db appeared this 2221 log was already shipped to standby db. However, no such log can be found. Since the archive log retention is set to 7 day, So i can able to copy this log from primary db to standby db in order to recover the gap manually.

  3. Givre August 19, 2010 - 3:37 pm Reply

    Hi,
    This morning, the problem is still here :-(

    I’m on Unix Machine (AIX) and i have got the same error like you

    MRP0: Background Media Recovery terminated with error 19909
    ORA-19909: datafile 1 belongs to an orphan incarnation
    ORA-01110: data file 1: ‘/xxxx/xxxx/sapdata1/system_1/system.data1’

    I think i will copy the datafile from Primary node.

    Have nice day.
    Benoit

  4. levin August 20, 2010 - 3:49 am Reply

    Do your standby db backup with RMAN? if yes, take a look on incarnation. Try to reset it to earlier incarnation and then start the standby db again.

Leave a Reply