在上次搭建的DG测试环境上做主备切换时报错，发现备库的控制文件没有了，被误删除了。
SYS@SCPRD>alter database commit to switchover to physical standby with session shutdown;
alter database commit to switchover to physical standby with session shutdown
*
ERROR at line 1:
ORA-16416: No viable Physical Standby switchover targets available
SYS@SCPRD>select name,open_mode,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS DATABASE_ROLE
--------- -------------------- -------------------- -------------------- -------------------- ----------------
SCPRD READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE FAILED DESTINATION PRIMARY
SYS@SCPRD>select dest_id, dest_name, status, type, error, gap_status from v$archive_dest_status;
DEST_ID DEST_NAME STATUS TYPE ERROR GAP_STATUS
---------- ------------------------------ --------- ---------------- ----------------------------------------------------------------- ------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL
2 LOG_ARCHIVE_DEST_2 ERROR PHYSICAL ORA-00210: cannot open the specified control file RESOLVABLE GAP
备库实例异常中断
SYS@SCPRDDG>alter database recover managed standby database cancel;
ERROR:
ORA-03114: not connected to ORACLE
在主库创建备库控件文件并传到备库服务器。
SYS@SCPRD>alter database create standby controlfile as '/home/oracle/control01.ctl';
Database altered.
将控制文件更名后启动到mount。
[oracle@dataguard ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 16 12:56:11 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SYS@SCPRDDG>startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 8622776 bytes
Variable Size 503319880 bytes
Database Buffers 1627389952 bytes
Redo Buffers 8151040 bytes
Database mounted.
打开数据库到只读状态时报错，datafile 1 需要介质恢复。
SYS@SCPRDDG>alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oradata/SCPRDDG/SCPRD_system01.dbf'
备库可以正常开启日志应用，数据同步正常。
SYS@SCPRDDG>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SYS@SCPRDDG>select name,open_mode,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS DATABASE_ROLE
---------------------------------------------- ---------- -------------------- -------------------- -------------------- ----------------
SCPRD MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED PHYSICAL STANDBY
SYS@SCPRDDG>select name,sequence#,archived,applied from v$archived_log order by sequence#
NAME SEQUENCE# ARC APPLIED
---------------------------------------------------------------------- ---------- --- ---------
/u01/app/oradata/SCPRDDG/archivelog/scprd_1_155_1047142728.arc 155 YES YES
/u01/app/oradata/SCPRDDG/archivelog/scprd_1_156_1047142728.arc 156 YES YES
/u01/app/oradata/SCPRDDG/archivelog/scprd_1_157_1047142728.arc 157 YES YES
/u01/app/oradata/SCPRDDG/archivelog/scprd_1_158_1047142728.arc 158 YES YES
/u01/app/oradata/SCPRDDG/archivelog/scprd_1_159_1047142728.arc 159 YES YES
/u01/app/oradata/SCPRDDG/archivelog/scprd_1_160_1047142728.arc 160 YES YES
/u01/app/oradata/SCPRDDG/archivelog/scprd_1_161_1047142728.arc 161 YES YES
/u01/app/oradata/SCPRDDG/archivelog/scprd_1_162_1047142728.arc 162 YES YES
/u01/app/oradata/SCPRDDG/archivelog/scprd_1_163_1047142728.arc 163 YES YES
/u01/app/oradata/SCPRDDG/archivelog/scprd_1_164_1047142728.arc 164 YES IN-MEMORY
主库状态正常
SYS@SCPRD>select name,open_mode,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS DATABASE_ROLE
--------- -------------------- -------------------- -------------------- -------------------- ----------------
SCPRD READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY PRIMARY
SYS@SCPRD>select dest_id, dest_name, status, type, error, gap_status from v$archive_dest_status;
DEST_ID DEST_NAME STATUS TYPE ERROR GAP_STATUS
---------- ------------------------------ --------- ---------------- ----------------------------------------------------------------- ---------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL NO GAP
主库删除10条记录，备库数据同步正常。
SYS@SCPRD>select count(*) from t1;
COUNT(*)
----------
7900
SYS@SCPRD>delete from t1 where rownum<11;
10 rows deleted.
SYS@SCPRD>commit;
Commit complete.
SYS@SCPRDDG>select count(*) from t1;
COUNT(*)
----------
7890
现在的状况是数据是可以同步的，备库无法打开到只读状态。采用官方文档增量备份前滚备库解决了这个问题。
Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)
1、取消备库日志应用
SYS@SCPRDDG>alter database recover managed standby database cancel;
Database altered.
2、找到备库最小SCN
SYS@SCPRDDG>SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
4310397
SYS@SCPRDDG> select min(checkpoint_change#) from v$datafile_header
2 where file# not in (select file# from v$datafile where enabled = 'READ ONLY');
MIN(CHECKPOINT_CHANGE#)
-----------------------
4310398
3、对主库进行增量备份
RMAN> BACKUP INCREMENTAL FROM SCN 4310397 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
Starting backup at 16-OCT-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oradata/SCPRD/SCPRD_system01.dbf
input datafile file number=00002 name=/u01/app/oradata/SCPRD/SCPRD_sysaux01.dbf
input datafile file number=00003 name=/u01/app/oradata/SCPRD/SCPRD_undo1.dbf
input datafile file number=00006 name=/u01/app/oradata/SCPRD/common/sce_common_01.dbf
input datafile file number=00008 name=/u01/app/oradata/SCPRD/wmwhse/wmwhse1_01.dbf
input datafile file number=00009 name=/u01/app/oradata/SCPRD/wmwhse/wmwhse2_01.dbf
input datafile file number=00010 name=/u01/app/oradata/SCPRD/wmwhse/wmwhse3_01.dbf
input datafile file number=00011 name=/u01/app/oradata/SCPRD/wmwhse/wmwhse4_01.dbf
input datafile file number=00012 name=/u01/app/oradata/SCPRD/wmwhse/wmwhse5_01.dbf
input datafile file number=00007 name=/u01/app/oradata/SCPRD/component/sce_component_01.dbf
input datafile file number=00004 name=/u01/app/oradata/SCPRD/SCPRD_users01.dbf
input datafile file number=00005 name=/u01/app/oradata/SCPRD/SCPRD_tools01.dbf
channel ORA_DISK_1: starting piece 1 at 16-OCT-20
channel ORA_DISK_1: finished piece 1 at 16-OCT-20
piece handle=/tmp/ForStandby_28vd49v5_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-OCT-20
channel ORA_DISK_1: finished piece 1 at 16-OCT-20
piece handle=/tmp/ForStandby_29vd49vk_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-OCT-20
4、将增量备份传到备库并恢复
RMAN> CATALOG START WITH '/home/oracle/ForStandby';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/ForStandby
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/ForStandby_28vd49v5_1_1
File Name: /home/oracle/ForStandby_29vd49vk_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: /home/oracle/ForStandby_28vd49v5_1_1
File Name: /home/oracle/ForStandby_29vd49vk_1_1
RMAN> RECOVER DATABASE NOREDO;
Starting recover at 16-OCT-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=789 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: /u01/app/oradata/SCPRDDG/SCPRD_system01.dbf
destination for restore of datafile 00002: /u01/app/oradata/SCPRDDG/SCPRD_sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oradata/SCPRDDG/SCPRD_undo1.dbf
destination for restore of datafile 00004: /u01/app/oradata/SCPRDDG/SCPRD_users01.dbf
destination for restore of datafile 00005: /u01/app/oradata/SCPRDDG/SCPRD_tools01.dbf
destination for restore of datafile 00006: /u01/app/oradata/SCPRDDG/common/sce_common_01.dbf
destination for restore of datafile 00007: /u01/app/oradata/SCPRDDG/component/sce_component_01.dbf
destination for restore of datafile 00008: /u01/app/oradata/SCPRDDG/wmwhse/wmwhse1_01.dbf
destination for restore of datafile 00009: /u01/app/oradata/SCPRDDG/wmwhse/wmwhse2_01.dbf
destination for restore of datafile 00010: /u01/app/oradata/SCPRDDG/wmwhse/wmwhse3_01.dbf
destination for restore of datafile 00011: /u01/app/oradata/SCPRDDG/wmwhse/wmwhse4_01.dbf
destination for restore of datafile 00012: /u01/app/oradata/SCPRDDG/wmwhse/wmwhse5_01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/ForStandby_28vd49v5_1_1
channel ORA_DISK_1: piece handle=/home/oracle/ForStandby_28vd49v5_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 16-OCT-20
5、在主库创建一个备用的控制文件备份并传到备库服务器。
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
Starting backup at 16-OCT-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-OCT-20
channel ORA_DISK_1: finished piece 1 at 16-OCT-20
piece handle=/tmp/ForStandbyCTRL.bck tag=TAG20201016T145036 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-OCT-20
Starting Control File and SPFILE Autobackup at 16-OCT-20
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/c-1011173192-20201016-00 comment=NONE
Finished Control File and SPFILE Autobackup at 16-OCT-20
6、主备库的位置/文件名可能不相同，备份备用数据库中的数据文件信息。在此环境中是相同的。
spool datafile_names_step8.txt
set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
spool off
7、RMAN连接到备库，恢复备用控制文件:
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 2147483648 bytes
Fixed Size 8622776 bytes
Variable Size 503319880 bytes
Database Buffers 1627389952 bytes
Redo Buffers 8151040 bytes
RMAN> RESTORE STANDBY CONTROLFILE FROM '/home/oracle/ForStandbyCTRL.bck';
Starting restore at 16-OCT-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=762 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oradata/SCPRD/ctl1SCPRD.ora
output file name=/u01/app/oradata/SCPRD/ctl2SCPRD.ora
output file name=/u01/app/oradata/SCPRD/ctl3SCPRD.ora
Finished restore at 16-OCT-20
RMAN> SHUTDOWN;
Oracle instance shut down
RMAN> STARTUP MOUNT;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 2147483648 bytes
Fixed Size 8622776 bytes
Variable Size 503319880 bytes
Database Buffers 1627389952 bytes
Redo Buffers 8151040 bytes
8、由于控制文件是从主数据库中恢复的，所以备用控制文件中的数据文件位置将与主数据库相同，如果备用和主数据库之间的目录结构不同，或者您使用的是Oracle管理的文件名，那么在备用中将数据文件编目将执行必要的重命名操作。如果主服务器和备用服务器具有相同的结构和数据文件名称，则可以跳过此步骤。
在备库执行以下步骤：
RMAN> CATALOG START WITH '+DATA/mystd/datafile/';
List of Files Unknown to the Database
=====================================
File Name: +data/mystd/DATAFILE/SYSTEM.309.685535773
File Name: +data/mystd/DATAFILE/SYSAUX.301.685535773
File Name: +data/mystd/DATAFILE/UNDOTBS1.302.685535775
File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333
File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333
File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
如果在第2步确定的最小scn之后，有任何数据文件被添加到主服务器上，那么它们也必须被还原到备用主机上，并在进行切换之前进行注册。
see Note 1531031.1 Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary
确定从备用当前scn开始是否有文件被添加到主服务器:
SQL>SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > scn;
如果上面的查询返回0 0行，您可以切换数据文件。这将在备用站点将数据文件重命名为正确的名称:
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333"
datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335"
datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"
Note:
a) 在此时，您可以比较步骤6的查询输出中的任何差异(除了新添加的数据文件之外)，以确保我们将所有添加的数据文件都作为备库数据文件。
b) 再次运行步骤2中的查询，以确认增量应用程序已将数据文件向前移动。SCN现在应该比它的初始值大。
本案例中目录结构及文件名相同，未进行第8步的操作。
备库正常开启到只读状态。
SYS@SCPRDDG>alter database open read only;
Database altered.
SYS@SCPRDDG>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SYS@SCPRDDG>select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SYS@SCPRDDG>select name,open_mode,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS,DATABASE_ROLE from v$database
NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS DATABASE_ROLE
--------- -------------------- -------------------- -------------------- -------------------- ----------------
SCPRD READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED PHYSICAL STANDBY
SYS@SCPRD>select dest_id, dest_name, status, type, error, gap_status from v$archive_dest_status;
DEST_ID DEST_NAME STATUS TYPE ERROR GAP_STATUS
---------- ------------------------------ --------- ---------------- ----------------------------------------------------------------- ------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL NO GAP