Check dataguard apply lag too long.
DGMGRL> show database orclswfstby
Database - orclswfstby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 12 days 10 hours 3 minutes 42 seconds (computed 1 second ago)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
orcl2
Database Error(s):
ORA-16766: Redo Apply is stopped
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
Database Status:
ERROR
1. Restart Database to mount mode if physical standby not on mount mode.
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
2. edit standby database set apply off or cancel recover managed.
RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
DGMGRL> edit database orclswfstby set state='APPLY-OFF';
Succeeded.
3. Let us identify the datafiles on standby database which are out of sync with respect to primary.
-- Primary
SQL> set line 200
SQL> col SUBSTR(HXFNM,1,40) format a50
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- -------------------------------------------------- --------------------
1 +DATADG/ORCL/DATAFILE/system.257.1060083 3890609733
3 +DATADG/ORCL/DATAFILE/sysaux.258.1060083 3890609733
4 +DATADG/ORCL/DATAFILE/undotbs1.259.10600 3890609733
5 +DATADG/ORCL/DATAFILE/tdata.267.106008 3890609733
7 +DATADG/ORCL/DATAFILE/users.260.10600833 3890609733
-- Physical standby
SQL> set line 200
SQL> col SUBSTR(HXFNM,1,40) format a50
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- -------------------------------------------------- --------------------
1 +DATADG/ORCLSWFSTBY/DATAFILE/system.318. 3815201099
3 +DATADG/ORCLSWFSTBY/DATAFILE/sysaux.426. 3815201099
4 +DATADG/ORCLSWFSTBY/DATAFILE/undotbs1.42 3815201099
5 +DATADG/ORCLSWFSTBY/DATAFILE/tdata.316 3815201099
7 +DATADG/ORCLSWFSTBY/DATAFILE/users.603.1 3815201099
4. Take note of the current SCN of the physical standby database. This is required to determine, in a later step, if datafiles were added to the primary database.
-- Physical standby
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
3828749288
5. The RECOVER … FROM SERVICE command refreshes the standby data files and rolls them forward to the same point-in-time as the primary.
-- Physical standby
$ rman target /
RMAN> recover database from service orclswf noredo using compressed backupset;
Starting recover at 04-MAR-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=401 instance=orcl2 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclswf
destination for restore of datafile 00001: +DATADG/ORCLSWFSTBY/DATAFILE/system.318.1062847249
channel ORA_DISK_1: restore complete, elapsed time: 00:00:18
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclswf
destination for restore of datafile 00003: +DATADG/ORCLSWFSTBY/DATAFILE/sysaux.426.1062847249
channel ORA_DISK_1: restore complete, elapsed time: 00:00:49
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclswf
destination for restore of datafile 00004: +DATADG/ORCLSWFSTBY/DATAFILE/undotbs1.424.1062847249
channel ORA_DISK_1: restore complete, elapsed time: 00:00:38
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclswf
destination for restore of datafile 00005: +DATADG/ORCLSWFSTBY/DATAFILE/tdata.316.1062847249
channel ORA_DISK_1: restore complete, elapsed time: 00:00:48
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orclswf
destination for restore of datafile 00007: +DATADG/ORCLSWFSTBY/DATAFILE/users.603.1062847249
channel ORA_DISK_1: restore complete, elapsed time: 00:00:18
Finished recover at 04-MAR-22
6. Lets check the SCNs of the datafiles at primary and standby now.
-- Primary
SQL> set line 200
SQL> col substr(HXFNM,1,40) format a50
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- -------------------------------------------------- --------------------
1 +DATADG/ORCL/DATAFILE/system.257.1060083 3890641094
3 +DATADG/ORCL/DATAFILE/sysaux.258.1060083 3890643904
4 +DATADG/ORCL/DATAFILE/undotbs1.259.10600 3890646297
5 +DATADG/ORCL/DATAFILE/tdata.267.106008 3890651799
7 +DATADG/ORCL/DATAFILE/users.260.10600833 3890655337
-- Physical Standby
SQL> set line 200
SQL> col substr(HXFNM,1,40) format a50
SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;
FILE_NUM SUBSTR(HXFNM,1,40) FHSCN
---------- -------------------------------------------------- --------------------
1 +DATADG/ORCLSWFSTBY/DATAFILE/system.318. 3890641094
3 +DATADG/ORCLSWFSTBY/DATAFILE/sysaux.426. 3890643904
4 +DATADG/ORCLSWFSTBY/DATAFILE/undotbs1.42 3890646297
5 +DATADG/ORCLSWFSTBY/DATAFILE/tdata.316 3890651799
7 +DATADG/ORCLSWFSTBY/DATAFILE/users.603.1 3890655337
7. Use the following commands to shut down the standby database and then start it in NOMOUNT mode.
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;
8. Restore the standby control file by using the control file on the primary database using service primary.
RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE orclswf;
Starting restore at 04-MAR-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=783 instance=orcl2 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orclswf
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATADG/ORCLSWF/CONTROLFILE/current.313.1060603257
Finished restore at 04-MAR-22
9. Mount and check the location and datafile names of the standby datafiles.
RMAN> alter database mount;
RMAN> report schema;
Starting implicit crosscheck backup at 04-MAR-22
allocated channel: ORA_DISK_1
Finished implicit crosscheck backup at 04-MAR-22
Starting implicit crosscheck copy at 04-MAR-22
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 04-MAR-22
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +DATADG/ORCLSWFSTBY/AUTOBACKUP/2021_01_26/s_1062849434.326.1062849391
File Name: +DATADG/ORCLSWFSTBY/ARCHIVELOG/2022_03_04/thread_1_seq_33727.348.1098439061
File Name: +DATADG/ORCLSWFSTBY/ARCHIVELOG/2022_03_04/thread_1_seq_33728.703.1098439423
File Name: +DATADG/ORCLSWFSTBY/ARCHIVELOG/2022_03_04/thread_1_seq_33730.764.1098439423
...
File Name: +DATADG/ORCLSWFSTBY/TEMPFILE/temp.342.1062848705
File Name: +DATADG/ORCLSWFSTBY/TEMPFILE/temp.344.1062848775
File Name: +DATADG/ORCLSWFSTBY/DATAFILE/tdata.316.1062847249
File Name: +DATADG/ORCLSWFSTBY/DATAFILE/system.318.1062847249
File Name: +DATADG/ORCLSWFSTBY/DATAFILE/sysaux.426.1062847249
File Name: +DATADG/ORCLSWFSTBY/DATAFILE/undotbs1.424.1062847249
File Name: +DATADG/ORCLSWFSTBY/DATAFILE/users.603.1062847249
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLSWFSTBY
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +DATADG/ORCL/DATAFILE/system.257.1060083235
3 0 SYSAUX *** +DATADG/ORCL/DATAFILE/sysaux.258.1060083279
4 0 UNDOTBS1 *** +DATADG/ORCL/DATAFILE/undotbs1.259.1060083305
5 0 TDATA *** +DATADG/ORCL/DATAFILE/tdata.267.1060083891
7 0 USERS *** +DATADG/ORCL/DATAFILE/users.260.1060083307
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATADG/ORCL/TEMPFILE/temp.265.1060083389
10. Update the names of the data files and the temp files in the standby control file.
RMAN> catalog start with '+DATADG/ORCLSWFSTBY/';
searching for all files that match the pattern +DATADG/ORCLSWFSTBY/
List of Files Unknown to the Database
=====================================
File Name: +DATADG/ORCLSWFSTBY/TEMPFILE/temp.342.1062848705
File Name: +DATADG/ORCLSWFSTBY/TEMPFILE/temp.344.1062848775
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_31.322.1060605759
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_32.323.1060605805
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_33.324.1060605843
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_34.325.1060605851
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_1.328.1060607509
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_2.329.1060607519
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_3.331.1060607803
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_11.317.1062847495
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_11.417.1062847497
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_12.651.1062847501
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_12.415.1062847503
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_13.301.1062847509
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_13.362.1062847511
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_14.273.1062847513
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_14.271.1062847515
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_1.357.1062848119
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_1.356.1062848121
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_3.354.1062848153
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_3.351.1062848155
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_2.333.1062849387
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_2.327.1062849387
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +DATADG/ORCLSWFSTBY/TEMPFILE/temp.342.1062848705
File Name: +DATADG/ORCLSWFSTBY/TEMPFILE/temp.344.1062848775
List of Files Which Were Not Cataloged
=======================================
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_31.322.1060605759
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_32.323.1060605805
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_33.324.1060605843
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_34.325.1060605851
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_1.328.1060607509
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_2.329.1060607519
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_3.331.1060607803
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_11.317.1062847495
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_11.417.1062847497
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_12.651.1062847501
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_12.415.1062847503
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_13.301.1062847509
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_13.362.1062847511
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_14.273.1062847513
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_14.271.1062847515
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_1.357.1062848119
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_1.356.1062848121
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_3.354.1062848153
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_3.351.1062848155
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_2.333.1062849387
RMAN-07529: Reason: catalog is not supported for this file type
File Name: +DATADG/ORCLSWFSTBY/ONLINELOG/group_2.327.1062849387
RMAN-07529: Reason: catalog is not supported for this file type
11. Switch to the cataloged datafile copy.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATADG/ORCLSWFSTBY/DATAFILE/system.318.1062847249"
datafile 3 switched to datafile copy "+DATADG/ORCLSWFSTBY/DATAFILE/sysaux.426.1062847249"
datafile 4 switched to datafile copy "+DATADG/ORCLSWFSTBY/DATAFILE/undotbs1.424.1062847249"
datafile 5 switched to datafile copy "+DATADG/ORCLSWFSTBY/DATAFILE/tdata.316.1062847249"
datafile 7 switched to datafile copy "+DATADG/ORCLSWFSTBY/DATAFILE/users.603.1062847249"
12. Check the datafile size of the standby datafiles
RMAN> report schema;
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLSWFSTBY
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 2290 SYSTEM *** +DATADG/ORCLSWFSTBY/DATAFILE/system.318.1062847249
3 2200 SYSAUX *** +DATADG/ORCLSWFSTBY/DATAFILE/sysaux.426.1062847249
4 2110 UNDOTBS1 *** +DATADG/ORCLSWFSTBY/DATAFILE/undotbs1.424.1062847249
5 32765 TDATA *** +DATADG/ORCLSWFSTBY/DATAFILE/tdata.316.1062847249
7 5 USERS *** +DATADG/ORCLSWFSTBY/DATAFILE/users.603.1062847249
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATADG/ORCL/TEMPFILE/temp.265.1060083389
13. Use the current SCN returned in step 4 to determine if new data files were added to the primary database since the standby database was last refreshed. If yes, these datafiles need to be restored on the standby from the primary database.
SELECT file# FROM V$DATAFILE WHERE creation_change# >= 3828749288;
Note: For example, assume datafile 21 was returned by the query in step 13. Execute:
RMAN> RUN
{
SET NEWNAME FOR DATABASE TO '/';
RESTORE DATAFILE 21 FROM SERVICE prim;
switch datafile 21 to copy;
}
14. Update the names of the online redo logs and standby redo logs in the standby control file using the following methods:
SQL> select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;
GROUP#
----------
11
12
13
14
SQL> alter database clear logfile group 11;
SQL> alter database clear logfile group 12;
SQL> alter database clear logfile group 13;
SQL> alter database clear logfile group 14;
15. Using broker (DGMGRL), start the managed recovery process (MRP).
DGMGRL> edit database orclswfstby set state='APPLY-ON';
16. (Oracle Active Data Guard only) Perform the following steps to open the physical standby database:
-- Primary
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
-- Physical standby
SQL> RECOVER DATABASE;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
0 留言