- 28 Sep, 2023
- 0 Comments
- 5 Mins Read
Rolling Forward Standby Database When Archives Missing In Primary In 12c
Till 11g , we can make standby database sync with primary by taking an incremental backup from primary and apply the same in standby .
But with 12c , a new feature has been introduced ( RECOVER DATBASE USING SERVICE) , which simplifies this method.
1 : CHECK PRIMARY / STANDBY DATABASE STATUS
@ PRIMARY SQL> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- PRIM READ WRITE PRIMARY @STANDBY SQL> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY
2. CHECK ARCHIVE SEQUENCES
@PRIMARY SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /archive/PRIM/arch Oldest online log sequence 88 Next log sequence to archive 90 Current log sequence 90 @STANDBY SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /archive/PRIM/arch Oldest online log sequence 72 Next log sequence to archive 0 Current log sequence 74Archive sequences from 74 are missing from primary , before being shipped to standby . Follow the below steps to make standby in sync with primary in 12c .
STEP 3 : CANCEL RECOVERY ON STANDBY :
SQL> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- STANDBY READ ONLY PHYSICAL STANDBY SQL> recover managed standby database cancel; Media recovery complete.
STEP 4 : START STANDBY DATABASE IN MOUNT STATE
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1.3935E+10 bytes Fixed Size 6002112 bytes Variable Size 4630514240 bytes Database Buffers 9193914368 bytes Redo Buffers 104153088 bytes Database mounted.
STEP 5 : RECOVER STANDBY USING SERVICE.
SYNTAX – RECOVER DATABASE FROM SERVICE < PRIMARY DB SERVICE NAME > NOREDO USING COMPRESSED BACKUPSET;$ rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Tue Oct 18 18:32:15 2016 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: STANDBY (DBID=599956155, not open) RMAN> recover database from service PRIM noredo using compressed backupset; Starting recover at 18-OCT-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=689 device type=DISK channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service PRIM destination for restore of datafile 00001: /archive/PRIM/PRIM/system01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service PRIM destination for restore of datafile 00002: /archive/PRIM/PRIM/sysaux01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service PRIM destination for restore of datafile 00003: /archive/PRIM/PRIM/undotbs01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using compressed network backup set from service PRIM destination for restore of datafile 00004: /archive/PRIM/PRIM/users01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished recover at 18-OCT-16
STEP 6 : RESTORE STANDBY CONTROLFILE IN NOMOUNT STAGE:
SYNTAX – RESTORE STANDBY CONTROLFILE FROM SERVICE < PRIMARY DB SERVICE NAME > ;RMAN> shutdown immediate; database dismounted Oracle instance shut down RMAN> startup nomount; connected to target database (not started) Oracle instance started Total System Global Area 13934583808 bytes Fixed Size 6002112 bytes Variable Size 4630514240 bytes Database Buffers 9193914368 bytes Redo Buffers 104153088 bytes RMAN> restore standby controlfile from service PRIM; Starting restore at 18-OCT-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=593 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service PRIM channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/archive/PRIM/PRIM/control01.ctl output file name=/archive/PRIM/PRIM/control02.ctl Finished restore at 18-OCT-16
STEP 7 : CHECK DATAFILES
RMAN> report schema; Starting implicit crosscheck backup at 18-OCT-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=593 device type=DISK Finished implicit crosscheck backup at 18-OCT-16 Starting implicit crosscheck copy at 18-OCT-16 using channel ORA_DISK_1 Crosschecked 2 objects Finished implicit crosscheck copy at 18-OCT-16 searching for all files in the recovery area cataloging files... no files cataloged RMAN-06139: WARNING: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name PRIM List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 700 SYSTEM *** /archive/PRIM/PRIM/system01.dbf 2 550 SYSAUX *** /archive/PRIM/PRIM/sysaux01.dbf 3 335 UNDOTBS1 *** /archive/PRIM/PRIM/undotbs01.dbf 4 5 USERS *** /archive/PRIM/PRIM/users01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /archive/PRIM/PRIM/temp01.dbf RMAN> catalog start with 'c'; searching for all files that match the pattern c no files found to be unknown to the database
STEP 8 : RUN RECOVER DATABASE( IN STANDBY )
RMAN> RECOVER DATABASE; Starting recover at 18-OCT-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=673 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 18-OCT-16
STEP 9 : OPEN THE DATABASE and START MEDIA RECOVERY:
SQL> recover managed standby database cancel; Media recovery complete. SQL> alter database open; Database altered. SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete. Now standby database is in sync with primary . Do few log switch in primary and check whether archives are shipping to standby or not. But if we check the alert log of stand by , we can see warning like No standby redo . Archived Log entry 4 added for thread 1 sequence 93 rlc 925475123 ID 0x23c3441f dest 2: RFS[4]: No standby redo logfiles available for thread 1 RFS[4]: Opened log for thread 1 sequence 94 dbid 599956155 branch 925475123 Tue Oct 18 19:10:59 2016 To fix it, we need to drop and recreate the standby redo logs
STEP 10 : DROP AND RECREATE STANDBY REDO LOGS( ON STANDBY)
SQL> recover managed standby database cancel; Media recovery complete. SQL> alter database drop logfile group 4; Database altered. SQL> alter database drop logfile group 5; Database altered. SQL> alter database drop logfile group 6; Database altered. SQL> select thread#, group#, sequence#, status from v$standby_log; no rows selected SQL> SQL> alter database add standby logfile '/archive/PRIM/PRIM/stdby_redo04.log' size 52428800; Database altered. SQL> SQL> alter database add standby logfile '/archive/PRIM/PRIM/stdby_redo05.log' size 52428800; Database altered. SQL> SQL> alter database add standby logfile '/archive/PRIM/PRIM/stdby_redo06.log' size 52428800; Database altered. SQL> SQL> alter database add standby logfile '/archive/PRIM/PRIM/stdby_redo07.log' size 52428800; Database altered.
STEP 11 : START THE RECOVERY AGAIN:
SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete.