Media fuzzy while perform open standby database

Some times you need to perform restart standby database to apply some system changes. And here you can catch some pitfalls.
When you try to perform start up standby database in open for readonly mode, you can see this error in your alert log.

Standby crash recovery failed to bring standby database to a consistent
point because needed redo has not arrived yet.
MRP: Wait timeout: thread 1 sequence# 116623
Standby crash recovery aborted due to error 16016.
Errors in file /opt/oracle/admin/diag/rdbms/somedb/somedb/trace/somedb_ora_12783.trc:
ORA-16016: archived log for thread 1 sequence# 116623 unavailable
Recovery interrupted!
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Block change tracking service stopping.
Stopping background process CTWR
Completed standby crash recovery.
Errors in file /opt/oracle/admin/diag/rdbms/somedb/somedb/trace/somedb_ora_12783.trc:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+somegroup/somedb/datafile/system.385.778960273'
ORA-10458 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:0:115} */...

And this errors you can see in trace file as well.

----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 833972Kb in 114.25s => 7.13 Mb/sec
Total redo bytes: 833972Kb Longest record: 14Kb, moves: 641/2619135 moved: 1Mb (0%)
Longest LWN: 253Kb, reads: 164803
Last redo scn: 0x00a2.988bb85d (698343995485)
Change vector header moves = 288915/4883823 (5%)
...
...
...
In-flux buffer recovery was not started because datafiles were fuzzy beyond in-flux recovery target.
Highest datafile fuzzy SCN: 162.2560179885
In-flux buffer recovery target SCN: 162.2559293625
Managed Recovery: Not Active posted.

That mean you might don’t have the required changes in there where MRP can read and apply. For example in your standby log files.

For resolve this you need to execute this on primary side.

SQL> alter system set log_archive_dest_state_2=DEFER;

System altered.

SQL> alter system set log_archive_dest_state_2=ENABLE;

System altered.

And then you can see activity by RFS process.

RFS[1]: Assigned to RFS process 15969
RFS[1]: Selected log 11 for thread 1 sequence 116623 dbid 701953228 branch 778258892
RFS[2]: Assigned to RFS process 15977
RFS[2]: Selected log 12 for thread 1 sequence 116626 dbid 701953228 branch 778258892

And after few minutes success incomplete recovery

Incomplete Recovery applied until change 698344881837
...
...
...
Completed standby crash recovery.

698343995485 – scn which have on standby redo logs
698344881837 – scn which standby datafiles needs to consistent state.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s