Saturday 31 December 2011

Archives not applying on Standby Database

Due to abnormal network outage for 10-12 hrs my standby database was behaving abnormally.

All the archives are moving properly from Primary node to DR.But it was not being applied.
Then i checked the gap using below command ,there was no gap.
select * from v$archive_gap;

Then i checked maximum sequence upto which archives had applied.
select THREAD#,max(SEQUENCE#),APPLIED from v$archived_log where APPLIED='YES' group by thread#,applied;

It showed sequence of 10 hrs old.It means my archives had not been applied for 10 hrs.

It means MRP process is hanged.Now, i need to do manually recovery.I cancelled the recovery,
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

But my session got hanged.I waited for 20min but unluckily no progress.

Then I down my database by shutdown immediate command.Even it also got hanged.

Finally,i abnormally shutdown my database by shutdown abort and did manual recovery.

a) shutdown abort;

b) startup nomount;

c) alter database mount standby database;

--Manual recovery:
d) RECOVER AUTOMATIC FROM '/ora_arch/archivelog' STANDBY DATABASE UNTIL CANCEL;
e) alter database recover cancel;
--Manual recovery completed successfully

Now put the database in recovery mode.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

But unfortunately at the same time one more problem encountered.When i checked  gap,
SQL> select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
2          4470           4470

There was a gap of only 1 archive of node 2.I waited for 50 min but there is no progress.Then i manually registered that archive.
alter database register or replace logfile '/ora_arch/arch_2_4470_718297972.arc';

Finally,there was no gap and even archives were applying normally.

1 comment: