How to deal with Oracle DataGuard archive log loss

How to deal with Oracle DataGuard archive log loss

A database is configured with Physical Transaction uard (hereinafter referred to as dg). Because no check is performed, the archive day is deleted on the master database, and the slave database is not fully applied, therefore, the slave database remains in the previous archive log.

Check the master database and find that log no. 25 for dest_id is lost.

PRIMARY> select dest_id, sequence #, applied, status, first_change #, next_change # from v $ archived_log
Where sequence #> 20 order by dest_id, sequence #;

DEST_ID SEQUENCE # applied s FIRST_CHANGE # NEXT_CHANGE #
-------------------------------------------------------
1 21 no d 1765541 1765764
1 22 no d 1765764 1768228
1 23 no d 1768228 1768269
1 24 no d 1768269 1768400
1 25 no x 1768400 1768516
1 26 no x 1768516 1768531
1 27 no x 1768531 1768611
2 21 yes a 1765541 1765764
2 22 yes a 1765764 1768228
2 23 yes a 1768228 1768269
2 24 no a 1768269 1768400

DEST_ID SEQUENCE # applied s FIRST_CHANGE # NEXT_CHANGE #
-------------------------------------------------------
2 26 no a 1768516 1768531
2 27 no a 1768531 1768611

Therefore, the slave database has never been used. In this case, you can either redo the entire slave database or restore the database from the time when the logs of the slave database breakpoint occur.
Because the database is large, the second solution is adopted;

On the slave database, we can view the largest SCN of the current data file.

STANDBY> select name, file #, checkpoint_change # from v $ datafile order by checkpoint_change #;

Name file # CHECKPOINT_CHANGE #
-----------------------------------------------------------------------------------
/U01/app/Oracle/oradata/dc1stby/undotbs01.dbf 4 1768269
/U01/app/oracle/oradata/dc1stby/sysaux01.dbf 3 1768269
/U01/app/oracle/oradata/dc1stby/system01.dbf 1 1768269
/U01/app/oracle/oradata/dc1stby/users01.dbf 6 1768269

Perform an incremental SCN backup on the master database. The starting position is the minimum SCN Number of the backup database data file.

[Oracle @ newplat ~] $ Rman target/nocatalog

[Oracle @ newplat ~] $ ORACLE_HOME/bin/rman target/nocatalog

RMAN> run
2> {
3> allocate channel c3 device type disk;
4> backup as compressed backupset incremental from scn 1768269 database format'/home/oracle/bak/% U ';
5> release channel c3;
6>}

Released channel: ORA_DISK_1
Allocated channel: c3
Channel c3: SID = 42 device type = DISK
........................................ .............
Including current control file in backup set
Channel c3: starting piece 1 at 03-APR-16
Channel c3: finished piece 1 at 03-APR-16
Piece handle =/home/oracle/bak/0rr22q7u_1_1 tag = TAG20160403T022709 comment = NONE
Channel c3: backup set complete, elapsed time: 00:00:01
Finished backup at 03-APR-16

After the backup is complete, we quickly upload the backup file to the machine in the slave database through SCP.

[Oracle @ newplat bak] $ scp * 1_1 192.168.56.22:/home/oracle/bak

In this case, we need to close the slave database and then start the instance to the nomount state.

STANDBY> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
STANDBY> startup nomount;
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 939524816 bytes
Database Buffers 268435456 bytes
Redo Buffers 13852672 bytes

After the instance is started to the mount state, we enter the rman of the standby database. At this time, we need to restore the control file. The control file of the original standby database is
The old application information including redo apply is unavailable.

[Oracle @ dg2 bak] $ rman target/nocatalog

RMAN> restore standby controlfile from '/home/oracle/bak/0rr22q7u_1_1 ';

Starting restore at 03-APR-16
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 23 device type = DISK
........................................ ....

Soon the control file is restored. We open another window and use sqlplus to mount the standby database in standby mode.

STANDBY> alter database mount standby database;

Database altered.

After the database is mounted, we return to the rman prompt. At this time, we can catalog the uploaded backup files. in this way, we can use these files for restoration in the next step.

RMAN> catalog start with '/home/oracle/Bak ';

Searching for all files that match the pattern/home/oracle/bak

List of Files Unknown to the Database
============================================
File Name:/home/oracle/bak/08r1ekse_1_1
File Name:/home/oracle/bak/0or22q7t_1_1
File Name:/home/oracle/bak/0kr22phj_1_1
File Name:/home/oracle/bak/0nr22pic_1_1
File Name:/home/oracle/bak/09r1eku6_1_1
File Name:/home/oracle/bak/0rr22q7u_1_1

Do you really want to catalog the above files (enter YES or NO )? Yes
Cataloging files...
Cataloging done

After the file is catalogued, we can start to officially restore the database.

RMAN> recover database;

Starting recover at 03-APR-16
Using channel ORA_DISK_1

Starting media recovery

.....................................

Media recovery complete, elapsed time: 00:03:01
Finished recover at 03-APR-16

After the slave database is restored, we can start redo apply.

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

Database altered.

STANDBY> select process, client_process, sequence #, status from v $ managed_standby;

PROCESS CLIENT_P SEQUENCE # STATUS
---------------------------------------
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
Rfs arch 0 IDLE
Rfs lgwr 33 IDLE
MRP0 N/A 33 WAIT_FOR_LOG

Now we can check the status of the slave database. The MRP process has started the latest log application. At this point, we have completed the process of restoring the archive log loss of the slave database through incremental SCN backup and recovery.

 

Steps for installing Oracle 11gR2 in vmwarevm

 

Install Oracle 11g XE R2 In Debian

 

Important configuration parameters of Oracle Data Guard

 

Configure Oracle 11g Data Guard based on the same host

 

Explore Oracle 11g elastic uard

 

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

 

Role conversion for Oracle Data Guard

 

FAL gap in Oracle Data Guard logs

 

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby