DATAGUARD NOT IN SYNC MISSING ARCHIVELOG IN RAC TO RAC ENVIRONMENT

When a standby database is not being updated, due to missing or corrupt archive logs

DATAGUARD NOT IN SYSNC MISSING ARCHIVELOG IN RAC TO RAC ENVIRONMENT

Error : archive log seq 39212 not shiiped from primary to standby database rac to rac

Error details :FAL[client]: Failed to request gap sequence

GAP – thread 2 sequence 39213-39213

DBID 985436356 branch 1078654470

FAL[client]: All defined FAL servers have been attempted.

When a standby database is not being updated, due to missing or corrupt archive logs, the following steps are to be followed:-

1. In the standby database find and note the current SCN (system change number), using the following command:-

SQL> select current_scn from v$database;

CURRENT_SCN

———–

4.6896E+10

SQL> col current_scn for 99999999999

SQL> /

CURRENT_SCN

————

46896380858

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

2. check the latest archive log sequence and gap sequence in asm storage .

[oracle@RACDR1 ~]$ . oraenv

ORACLE_SID = [RACDR1] ? +ASM

ORACLE_HOME = [/home/oracle] ? ^C

[oracle@RACDR1 ~]$ . oraenv

ORACLE_SID = [+ASM] ? ^C

[oracle@RACDR1 ~]$

[oracle@RACDR1 ~]$ . .bash_profile

[oracle@RACDR1 ~]$ . oraenv

ORACLE_SID = [RACDR1] ? +ASM1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@RACDR1 ~]$ asmcmd

ASMCMD>ls

DATABASE/

FRA/

MGMT/

OCR_VOTE/

ASMCMD> cd fra

ASMCMD>ls

RACDR/

ASMCMD> cd RACDR

ASMCMD>ls

ARCHIVELOG/

CONTROLFILE/

ONLINELOG/

ASMCMD> cd archivelog

ASMCMD>ls

2022_01_14/

2022_01_15/

2022_01_16/

2022_01_17/

2022_01_18/

2022_01_21/

ASMCMD> cd 2022_01_21

ASMCMD>ls

thread_1_seq_30614.19314.1094556467

thread_2_seq_39211.19313.1094556467

thread_2_seq_39212.19312.1094556467

ASMCMD>

3. Check the correct scn no of gap sequence archive log is actual or not .

[oracle@RACDR1 ~]$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 21 12:55:24 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select name ,thread#,sequence#,status,first_time,next_time,first_change#,next_change# from v$archived_log where 51173663077 between first_change# and next_change#;

NAME

——————————————————————————–

THREAD# SEQUENCE# S FIRST_TIM NEXT_TIME FIRST_CHANGE# NEXT_CHANGE#

———- ———- – ——— ——— ————- ————

+FRA/RACDR/ARCHIVELOG/2022_01_18/thread_1_seq_30606.1787.1094292693

1 30606 A 18-JAN-22 18-JAN-22 4.6896E+10 4.6896E+10

+FRA/RACDR/ARCHIVELOG/2022_01_21/thread_2_seq_39212.19312.1094556467

2 39212 A 18-JAN-22 18-JAN-22 4.6896E+10 4.6896E+10

4. Cancel the recovery process in sby DB (RACDR) and Defer the archive_log_dest_state_2 for standby DB in primary DB(RAC) Then Take the incremental back in the Main/Primary database using RMAN. In the Main/Primery database connect to RMAN utility from oracle user.

oracle~]$rman target/

Start the incremental backup from RMAN utility

RMAN> run

2> {

3> allocate channel ch1 device type disk

4> format=’/u04/rman/back_%U’;

5> backup incremental from scn 46896380858 database;

6> }

using target database control file instead of recovery catalog

allocated channel: ch1

channel ch1: SID=9776 instance=RAC1 device type=DISK

Starting backup at 21-JAN-22

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

inputdatafile file number=00002 name=+DATABASE/RAC/DATAFILE/apmaster.272.1078654973

inputdatafile file number=00008 name=+DATABASE/RAC/DATAFILE/apsmaster.273.1078655297

inputdatafile file number=00009 name=+DATABASE/RAC/DATAFILE/apw.275.1078655549

inputdatafile file number=00013 name=+DATABASE/RAC/DATAFILE/opmaster.284.1078655811

inputdatafile file number=00001 name=+DATABASE/RAC/DATAFILE/system.281.1078654343

inputdatafile file number=00007 name=+DATABASE/RAC/DATAFILE/users.282.1078654393

inputdatafile file number=00015 name=+DATABASE/RAC/DATAFILE/system.274.1078656163

inputdatafile file number=00004 name=+DATABASE/RAC/DATAFILE/undotbs1.285.1078654393

inputdatafile file number=00005 name=+DATABASE/RAC/DATAFILE/undotbs2.286.1078654497

inputdatafile file number=00010 name=+DATABASE/RAC/DATAFILE/cpmaster.278.1078655705

inputdatafile file number=00014 name=+DATABASE/RAC/DATAFILE/opw.268.1078655957

inputdatafile file number=00011 name=+DATABASE/RAC/DATAFILE/cpw.279.1078655749

inputdatafile file number=00003 name=+DATABASE/RAC/DATAFILE/sysaux.267.1078654377

inputdatafile file number=00016 name=+DATABASE/RAC/DATAFILE/testdba.280.1078702295

inputdatafile file number=00012 name=+DATABASE/RAC/DATAFILE/mdcop.266.1078655787

channel ch1: starting piece 1 at 21-JAN-22

piece handle=/u04/rman/back_070jrdo7_1_1 tag=TAG20220121T132631 comment=NONE

channel ch1: backup set complete, elapsed time: 00:40:56

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

including current control file in backup set

channel ch1: starting piece 1 at 21-JAN-22

channel ch1: finished piece 1 at 21-JAN-22

piece handle=/u04/rman/back_080jrg4v_1_1 tag=TAG20220121T132631 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 21-JAN-22

released channel: ch1

5. Monitor the rman backup progress .

SQL> col STATUS format a9

colhrs format 999.99

select SESSION_KEY , INPUT_TYPE, STATUS, to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,

to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,

elapsed_seconds/3600hrs from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SQL> SQL> 2 3 4

SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS

———– ————- ——— ————– ————– ——-

1 DB FULL FAILED 10/30/21 02:29 10/30/21 02:29 .00

4 DB FULL COMPLETED 10/31/21 13:42 10/31/21 14:59 1.27

8 DB FULL COMPLETED 11/13/21 14:09 11/13/21 15:29 1.34

11 DB FULL RUNNING 01/21/22 13:26 01/21/22 13:27 .01

6. Copy the rman backup from primary to standby DB

[oracle@RAC1 u04]$ scp -r rman RACDR1:/u03/

back_070jrdo7_1_1 100% 13GB 82.4MB/s 02:46

back_080jrg4v_1_1 100% 103MB 82.2MB/s 00:01

[oracle@RAC1 u04]$

7. Create the standby control file in primary DB for sby and copy to sbydb and restore the standbycontrolfile in sby DB in nomount stage .

[oracle@RAC1 u04]$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 22 11:55:02 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> alter database create standby controlfile as ‘/u04/RAC1.ctl’;

Database altered.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

[oracle@RAC1 u04]$ scp -r RAC1.ctl RACDR1:/u03/

RAC1.ctl 100% 103MB 80.9MB/s 00:01

[oracle@RACDR1 u01]$ rman target / (- nomount stage)

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Oct 19 07:49:16 2016

connected to target database: RACDR (not mounted)

RMAN> restore standby controlfile from ‘/u03/RAC1.ctl’ ;

Starting restore at 21-jan-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=3 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATABASE/RACDR/controlfile/current.260.925580779

output file name=+FRA/RACDR/controlfile/current.256.925580781

Finished restore at 21-jan-22

RMAN>exit

8. Shut down the sbyprod(RACDR) and start in mount stage and also check the mount stage status

[oracle@RACDR1 ~]$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 22 11:48:53 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select open_mode from v$database;

OPEN_MODE

——————–

MOUNTED

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

9. Start the recovery of the standby database, ensure that the standby database is in the mount stage.

[oracle@RACDR1 ~]$ rman target/

Recovery Manager: Release 12.2.0.1.0 – Production on Sat Jan 22 11:49:14 2022

connected to target database: RAC (DBID=985436356, not open)

RMAN> catalog start with ‘/u03/rman/’ ;

using target database control file instead of recovery catalog

searching for all files that match the pattern /u03/rman/

List of Files Unknown to the Database

=====================================

File Name: /u03/rman/back_070jrdo7_1_1

File Name: /u03/rman/back_080jrg4v_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files…

cataloging done

List of Cataloged Files

=======================

File Name: /u03/rman/back_070jrdo7_1_1

File Name: /u03/rman/back_080jrg4v_1_1

RMAN> recover database;

Starting recover at 22-JAN-22

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1704 instance=RACDR1 device type=DISK

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: +DATABASE/RACDR/DATAFILE/system.274.1088625119

destination for restore of datafile 00002: DATABASE/RACDR/DATAFILE/apmaster.270.1088625117

destination for restore of datafile 00003: +DATABASE/RACDR/DATAFILE/sysaux.282.1088626031

destination for restore of datafile 00004: +DATABASE/RACDR/DATAFILE/undotbs1.277.1088625121

destination for restore of datafile 00005: +DATABASE/RACDR/DATAFILE/undotbs2.278.1088625317

destination for restore of datafile 00007: +DATABASE/RACDR/DATAFILE/users.275.1088625119

destination for restore of datafile 00008: +DATABASE/RACDR/DATAFILE/apsmaster.271.1088625117

destination for restore of datafile 00009: +DATABASE/RACDR/DATAFILE/apw.272.1088625119

destination for restore of datafile 00010: +DATABASE/RACDR/DATAFILE/cpmaster.279.1088625481

destination for restore of datafile 00011: +DATABASE/RACDR/DATAFILE/cpw.281.1088625929

destination for restore of datafile 00012: +DATABASE/RACDR/DATAFILE/mdcop.284.1088626201

destination for restore of datafile 00013: +DATABASE/RACDR/DATAFILE/opmaster.273.1088625119

destination for restore of datafile 00014: +DATABASE/RACDR/DATAFILE/opw.280.1088625635

destination for restore of datafile 00015: +DATABASE/RACDR/DATAFILE/system.276.1088625121

destination for restore of datafile 00016: +DATABASE/RACDR/DATAFILE/testdba.283.1088626161

channel ORA_DISK_1: reading from backup piece /u03/rman/back_070jrdo7_1_1

channel ORA_DISK_1: piece handle=/u03/rman/back_070jrdo7_1_1 tag=TAG20220121T132631

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:07:35

starting media recovery

archived log for thread 1 with sequence 30606 is already on disk as file +FRA/RACDR/ARCHIVELOG/2022_01_18/thread_1_seq_30606.1787.1094292693

archived log for thread 1 with sequence 30607 is already on disk as file +FRA/RACDR/ARCHIVELOG/2022_01_18/thread_1_seq_30607.1786.1094292705

archived log for thread 1 with sequence 30608 is already on disk as file +FRA/RACDR/ARCHIVELOG/2022_01_18/thread_1_seq_30608.1785.1094293517

archived log for thread 1 with sequence 30609 is already on disk as file +FRA/RACDR/ARCHIVELOG/2022_01_18/thread_1_seq_30609.1784.1094294227

archived log for thread 1 with sequence 30610 is already on disk as file +FRA/RACDR/ARCHIVELOG/2022_01_18/thread_1_seq_30610.1783.1094295075

archived log for thread 1 with sequence 30611 is already on disk as file +FRA/RACDR/ARCHIVELOG/2022_01_18/thread_1_seq_30611.1782.1094295205

archived log for thread 1 with sequence 30614 is already on disk as file +FRA/RACDR/ARCHIVELOG/2022_01_21/thread_1_seq_30614.19314.1094556467

media recovery complete, elapsed time: 00:00:00

Finished recover at 22-JAN-22

RMAN>

10. Moniter the recovery process

[oracle@RACDR1 ~]$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 22 12:32:39 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select opname,sofar/totalwork*100 PCT_COMPLETE, trunc(time_remaining/60) MIN_REMAIN,

trunc(ELAPSED_SECONDS/60) MIN_COMPLETED

fromv$session_longops where TOTALWORK>0 and opname like ‘%RMAN%’;

2 3

OPNAME PCT_COMPLETE

—————————————————————- ————

MIN_REMAIN MIN_COMPLETED

———- ————-

RMAN: incremental datafile restore 16.6820153

11 2

SQL>

11. After successfully recovery catalog the datafile through rman manager.

RMAN> catalog start with ‘+DATABASE/RACDR’;

searching for all files that match the pattern +DATABASE/RACDR

List of Files Unknown to the Database

=====================================

File Name: +DATABASE/RACDR/spfileRACDR.ctl

File Name: +DATABASE/RACDR/spfileRACDR.ora

File Name: +DATABASE/RACDR/PARAMETERFILE/spfile.268.1088618813

File Name: +DATABASE/RACDR/TEMPFILE/temp.264.1088429797

File Name: +DATABASE/RACDR/ONLINELOG/group_1.262.1088429785

File Name: +DATABASE/RACDR/ONLINELOG/group_2.263.1088429785

File Name: +DATABASE/RACDR/ONLINELOG/group_3.266.1088429885

File Name: +DATABASE/RACDR/ONLINELOG/group_4.267.1088429885

File Name: +DATABASE/RACDR/PASSWORD/pwdRACDR.256.1088429555

File Name: +DATABASE/RACDR/PASSWORD/orapwRAC

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files…

no files cataloged

List of Files Which Were Not Cataloged

=======================================

File Name: +DATABASE/RACDR/spfileRACDR.ctl

RMAN-07518: Reason: Foreign database file DBID: 0 Database Name:

File Name: +DATABASE/RACDR/spfileRACDR.ora

RMAN-07518: Reason: Foreign database file DBID: 0 Database Name:

File Name: +DATABASE/RACDR/PARAMETERFILE/spfile.268.1088618813

RMAN-07518: Reason: Foreign database file DBID: 0 Database Name:

File Name: +DATABASE/RACDR/TEMPFILE/temp.264.1088429797

RMAN-07518: Reason: Foreign database file DBID: 2658825556 Database Name: RACDR

File Name: +DATABASE/RACDR/ONLINELOG/group_1.262.1088429785

RMAN-07518: Reason: Foreign database file DBID: 2658825556 Database Name: RACDR

File Name: +DATABASE/RACDR/ONLINELOG/group_2.263.1088429785

RMAN-07518: Reason: Foreign database file DBID: 2658825556 Database Name: RACDR

File Name: +DATABASE/RACDR/ONLINELOG/group_3.266.1088429885

RMAN-07518: Reason: Foreign database file DBID: 2658825556 Database Name: RACDR

File Name: +DATABASE/RACDR/ONLINELOG/group_4.267.1088429885

RMAN-07518: Reason: Foreign database file DBID: 2658825556 Database Name: RACDR

File Name: +DATABASE/RACDR/PASSWORD/pwdRACDR.256.1088429555

RMAN-07518: Reason: Foreign database file DBID: 0 Database Name:

File Name: +DATABASE/RACDR/PASSWORD/orapwRAC

RMAN-07518: Reason: Foreign database file DBID: 0 Database Name:

RMAN> exit

Recovery Manager complete.

[oracle@RACDR1 ~]$

11. Start the media recovery process insbyDb (RACDR).

[oracle@RACDR1 ~]$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 22 12:42:18 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

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

Database altered.

12. In the main/Primary database if you had DEFER the state of the standby archive log destination of the database which is being restored , is to be changed to ENABLE.

[oracle@RAC1 ~]$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 22 12:56:40 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL>sho parameter log_archive_dest_state_2

NAME TYPE VALUE

———————————— ———– ——————————

log_archive_dest_state_2 string defer

log_archive_dest_state_20 string enable

log_archive_dest_state_21 string enable

log_archive_dest_state_22 string enable

log_archive_dest_state_23 string enable

log_archive_dest_state_24 string enable

log_archive_dest_state_25 string enable

log_archive_dest_state_26 string enable

log_archive_dest_state_27 string enable

log_archive_dest_state_28 string enable

log_archive_dest_state_29 string enable

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

System altered.

13. In the standby database run the following query to know the status of the archive log being applied.

SQL>select thread#,max(sequence#) “last standby seq applied” from v$archived_logval,

v$databasedb where val.resetlogs_change# = db.resetlogs_change#

andval.applied in (‘YES’,’IN-MEMORY’)

group by thread# order by 1 ;

THREAD# last standby seq applied

———- ————————

1 30606

2 39214

SQL> /

THREAD# last standby seq applied

———- ————————

1 30609

2 39219

SQL> /

THREAD# last standby seq applied

———- ————————

1 30610

2 39220

SQL> /

THREAD# last standby seq applied

———- ————————

1 30611

2 39220

SQL> /

THREAD# last standby seq applied

———- ————————

1 30613

2 39221

Note -To check the pushing of archive log, in the Main/Primary database execute the following command to generate new archive log.

SQL>alter system switch logfile;