目录
※使用物理备库备份数据库的一致性说明※DG环境介绍一、备库执行备份 1.1、备库取消恢复 1.2、备库执行备份 1.3、备库启用日志应用进程二、将备份文件传递到新主库三、在新主机执行恢复操作 3.1、恢复spfile 3.2、恢复控制文件 3.3、注册备份信息 3.4、还原数据文件 3.5、激活备库为主库，并启动数据库 3.6、删除不用的日志组
在部署完ADG（Active Data Guard）后，不但可以将只读的查询交给备库执行，还可以把日常的数据库备份工作放在备库上执行，从而减轻主库的压力，充分的发挥服务器资源。
本文演示的是利用Oracle DG环境的备库执行备份，然后异机还原恢复成一个新的主库的过程。
※使用物理备库备份数据库的一致性说明
之前发过一篇类似的文章，请参考： 【DB宝15】生产环境中，如何利用DG的备库来异机还原一个新库？ 连接地址为： https://mp.weixin.qq.com/s/ptB9D3sDzwNyHyHujTwKbQ
这几天还原了一个将近2T的库，碰到了一个坑。情况是这样的，我在物理备库进行的备份，但是当我把备份拿到新主机进行还原后，死活就是不能正常启动数据库，报错“数据库不一致，需要相关的归档日志进行恢复”，即使加上隐含参数“_allow_resetlogs_corruption”强制启库也不行。无奈之下，找了找相关资料，发现在物理备库进行的备份其实是存在备份一致性的问题的。
请看下图，来自于Benefits and Usage of RMAN with Standby Databases (文档 ID 602299.1)：
在备库进行备份，主要关注备份的一致性问题，分为2种情况：
1、备库可以连接到主库：
方法a、对数据文件备份完成后，需要在主库切换归档后，等最新的归档传递到备库后，再在备库备份归档，最后备份控制文件。需要注意的是，如果是11.2.0.4以上，可以不用单独在主库执行切换归档操作，因为在备库执行备份的过程中，会自动去连接主库执行一个切换归档的操作，但是若主库本身拒绝备库的连接，会报RMAN警告（RMAN-06820: WARNING: failed to archive current log at primary database），那么依然需要手工在主库进行切换。
方法b、灾备端可以在mount状态或read only状态进行备份，但是不能启用实时应用功能（mrp进程）。因为，即使数据库处于read only或mount状态，mrp进程也会持续更新数据块。
2、备库不能连接到主库：灾备端可以在mount状态或read only状态进行备份，但是不能启用实时应用功能（mrp进程）。因为，即使数据库处于read only或mount状态，mrp进程也会持续更新数据块。
如果备库还原完成后，还需要归档才能打开数据库，那么我们也可以在原主库上拷贝相关的归档日志到我们的新主库进行恢复（recover），即可打开数据库，只是有的情况下，环境不允许我们连接主库，主库一般都是生产库。
其它更多内容请参考：http://blog.itpub.net/26736162/viewspace-2723840/
参考文档：
( Doc ID 1616074.1) RMAN-06820 ORA-17629 During Backup at Standby Site
How to take consistent backups at standby site (文档 ID 1292126.1)
( Doc ID 1419923.1) Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode
Step by Step method to create Primary/Standby Database from Standby Backup (文档 ID 1604251.1)
How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (文档 ID 1354256.1)
Benefits and Usage of RMAN with Standby Databases (文档 ID 602299.1)
本环境中，我们选择第2种办法在灾备端进行备份数据库，并拿到新环境进行还原。
※DG环境介绍
| 项目 | primary db | physical standby db | 新库 |
|---|---|---|---|
| 数据库类型（rac或单实例） | 单实例 | 单实例 | 单实例 |
| 数据库版本 | 11.2.0.3.0 | 11.2.0.3.0 | 11.2.0.3.0 |
| platform_name | Linux x86 64-bit | Linux x86 64-bit | Linux x86 64-bit |
| ORACLE_SID | oradg11g | oradgphy | TEST |
| db_name/GLOBAL_DBNAME | oradg11g | oradg11g | oradg11g |
| db_unique_name | oradg11g | oradgphy | TEST |
| ORACLE_HOME | /u01/app/oracle/product/11.2.0/dbhome_1 | /u01/app/oracle/product/11.2.0/dbhome_1 | /u01/app/oracle/product/11.2.0/dbhome_1 |
一、备库执行备份
1.1、备库取消恢复
为了能在备库达到一致性备份，需要取消实时应用功能：
1alter database recover managed standby database cancel;
检查是否处于一致性状态：
1alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
2set line 1000
3set numwidth 16
4col name format a55
5select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ;
如果fuzzy为NO，且checkpoint_change#列的值都一致，那么就代表数据库处于一致性状态，可以进行rman备份。
1SYS@oradgphy > alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
2
3Session altered.
4
5SYS@oradgphy > set line 1000
6SYS@oradgphy > set numwidth 16
7SYS@oradgphy > col name format a55
8SYS@oradgphy > select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ;
9
10FUZ STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME FILE# NAME
11--- ------- ------------------ ------------------- ---------------- -------------------------------------------------------
12NO ONLINE 3500675 2020-09-25 14:44:17 1 /u01/app/oracle/oradata/oradgphy/system01.dbf
13NO ONLINE 3500675 2020-09-25 14:44:17 2 /u01/app/oracle/oradata/oradgphy/sysaux01.dbf
14NO ONLINE 3500675 2020-09-25 14:44:17 3 /u01/app/oracle/oradata/oradgphy/undotbs01.dbf
15NO ONLINE 3500675 2020-09-25 14:44:17 4 /u01/app/oracle/oradata/oradgphy/users01.dbf
16NO ONLINE 3500675 2020-09-25 14:44:17 5 /u01/app/oracle/oradata/oradgphy/example01.dbf
17NO ONLINE 3500675 2020-09-25 14:44:17 6 /u01/app/oracle/oradata/oradgphy/logmnrtbs1.dbf
18
19SYS@oradgphy > select open_mode , database_role, flashback_on from v$database;
20
21OPEN_MODE DATABASE_ROLE FLASHBACK_ON
22-------------------- ---------------- ------------------
23READ ONLY PHYSICAL STANDBY NO
1.2、备库执行备份
1export NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss'
2ORACLE_SID=oradgphy
3rman target /
4run{
5backup as compressed backupset database format '/home/oracle/oracle_bk/FULL_%d_%U.full' section size 100G;
6backup as compressed backupset archivelog from time 'sysdate-2/24' format '/home/oracle/oracle_bk/ARC_%d_%U.arc' section size 100G;
7backup current controlfile format '/home/oracle/oracle_bk/standby_1.ctl';
8backup spfile format '/home/oracle/oracle_bk/spfile_%d_%U.ora';
9}
执行过程：
1[oracle@rhel6lhr oracle_bk]$ export NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss'
2[oracle@rhel6lhr oracle_bk]$ rman target /
3
4Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 14:48:52 2020
5
6Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
7
8connected to target database: ORADG11G (DBID=1403587593)
9
10RMAN> run{
112> backup as compressed backupset database format '/home/oracle/oracle_bk/FULL_%d_%U.full' section size 100G;
123> backup as compressed backupset archivelog from time 'sysdate-2/24' format '/home/oracle/oracle_bk/ARC_%d_%U.arc' section size 100G;
134> backup current controlfile format '/home/oracle/oracle_bk/standby_1.ctl';
145> backup spfile format '/home/oracle/oracle_bk/spfile_%d_%U.ora';
156> }
16
17Starting backup at 2020-09-25 14:48:55
18using target database control file instead of recovery catalog
19allocated channel: ORA_DISK_1
20channel ORA_DISK_1: SID=20 device type=DISK
21channel ORA_DISK_1: starting compressed full datafile backup set
22channel ORA_DISK_1: specifying datafile(s) in backup set
23input datafile file number=00001 name=/u01/app/oracle/oradata/oradgphy/system01.dbf
24input datafile file number=00002 name=/u01/app/oracle/oradata/oradgphy/sysaux01.dbf
25input datafile file number=00006 name=/u01/app/oracle/oradata/oradgphy/logmnrtbs1.dbf
26input datafile file number=00005 name=/u01/app/oracle/oradata/oradgphy/example01.dbf
27input datafile file number=00004 name=/u01/app/oracle/oradata/oradgphy/users01.dbf
28input datafile file number=00003 name=/u01/app/oracle/oradata/oradgphy/undotbs01.dbf
29channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:48:55
30channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:30
31piece handle=/home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full tag=TAG20200925T144855 comment=NONE
32channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
33channel ORA_DISK_1: starting compressed full datafile backup set
34channel ORA_DISK_1: specifying datafile(s) in backup set
35including current control file in backup set
36including current SPFILE in backup set
37channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:49:31
38channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:32
39piece handle=/home/oracle/oracle_bk/FULL_ORADG11G_37vbaa3q_1_1.full tag=TAG20200925T144855 comment=NONE
40channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
41Finished backup at 2020-09-25 14:49:32
42
43Starting backup at 2020-09-25 14:49:32
44using channel ORA_DISK_1
45specification does not match any archived log in the repository
46backup cancelled because there are no files to backup
47Finished backup at 2020-09-25 14:49:32
48
49Starting backup at 2020-09-25 14:49:32
50using channel ORA_DISK_1
51channel ORA_DISK_1: starting full datafile backup set
52channel ORA_DISK_1: specifying datafile(s) in backup set
53including current control file in backup set
54channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:49:33
55channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:34
56piece handle=/home/oracle/oracle_bk/standby_1.ctl tag=TAG20200925T144932 comment=NONE
57channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
58Finished backup at 2020-09-25 14:49:35
59
60Starting backup at 2020-09-25 14:49:35
61using channel ORA_DISK_1
62channel ORA_DISK_1: starting full datafile backup set
63channel ORA_DISK_1: specifying datafile(s) in backup set
64including current SPFILE in backup set
65channel ORA_DISK_1: starting piece 1 at 2020-09-25 14:49:35
66channel ORA_DISK_1: finished piece 1 at 2020-09-25 14:49:36
67piece handle=/home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora tag=TAG20200925T144935 comment=NONE
68channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
69Finished backup at 2020-09-25 14:49:36
1.3、备库启用日志应用进程
1alter database recover managed standby database using current logfile disconnect from session;
2
3------------物理dg日志应用情况(主备库都可以)
4alter session set NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss';
5COL NAME FOR A100
6SET LINESIZE 9999 PAGESIZE 9999
7SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME
8 FROM V$ARCHIVED_LOG A,
9 (SELECT NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#
10 FROM V$ARCHIVED_LOG NB
11 WHERE NB.APPLIED = 'YES'
12 and NB.DEST_ID in (SELECT NB.DEST_ID FROM V$ARCHIVE_DEST_STATUS NB where STATUS <>'INACTIVE')
13 GROUP BY NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID) B
14 WHERE B.THREAD# = A.THREAD#
15 AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
16 AND A.DEST_ID=B.DEST_ID
17 AND A.SEQUENCE# >= MAX_SEQUENCE#
18 AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB)
19 ORDER BY A.THREAD#, A.SEQUENCE#;
执行过程：
1SYS@oradgphy > alter session set NLS_DATE_FORMAT='yyyy-mm-dd HH24:mi:ss';
2
3Session altered.
4
5SYS@oradgphy > COL NAME FOR A100
6SYS@oradgphy > SET LINESIZE 9999 PAGESIZE 9999
7SYS@oradgphy > SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME
8 2 FROM V$ARCHIVED_LOG A,
9 3 (SELECT NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE#
10 4 FROM V$ARCHIVED_LOG NB
11 5 WHERE NB.APPLIED = 'YES'
12 6 and NB.DEST_ID in (SELECT NB.DEST_ID FROM V$ARCHIVE_DEST_STATUS NB where STATUS <>'INACTIVE')
13 7 GROUP BY NB.THREAD#,NB.RESETLOGS_CHANGE#,NB.DEST_ID) B
14 8 WHERE B.THREAD# = A.THREAD#
15 9 AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
16 10 AND A.DEST_ID=B.DEST_ID
17 11 AND A.SEQUENCE# >= MAX_SEQUENCE#
18 12 AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB)
19 13 ORDER BY A.THREAD#, A.SEQUENCE#;
20
21 THREAD# NAME SEQUENCE# APPLIED FIRST_TIME
22---------- ---------------------------------------------------------------------------------------------------- ---------- --------- -------------------
23 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_242_hpv5042k_.arc 242 YES 2020-09-25 14:53:53
24 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_243_hpv5050f_.arc 243 YES 2020-09-25 14:53:56
25 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2020_09_25/o1_mf_1_244_hpv505oh_.arc 244 IN-MEMORY 2020-09-25 14:53:57
确保DG环境恢复正常运行。
二、将备份文件传递到新主库
接下来就是把/home/oracle/oracle_bk/备份目录下的内容都拷贝到新主机上，方法很多，例如scp、磁盘卸载重新挂载等。
1[oracle@rhel6lhr oracle_bk]$ scp /home/oracle/oracle_bk/* oracle@192.168.1.35:/home/oracle/oracle_bk/
2oracle@192.168.1.35's password:
3FULL_ORADG11G_36vbaa2n_1_1.full 100% 333MB 19.6MB/s 00:17
4FULL_ORADG11G_37vbaa3q_1_1.full 100% 1120KB 1.1MB/s 00:00
5spfile_ORADG11G_39vbaa3v_1_1.ora 100% 96KB 96.0KB/s 00:00
6standby_1.ctl 100% 10MB 9.8MB/s 00:00
7[oracle@rhel6lhr oracle_bk]$
三、在新主机执行恢复操作
3.1、恢复spfile
1export ORACLE_SID=TEST
2rman target /
3startup nomount;
4restore spfile to pfile '?/dbs/initTEST.ora' from '/home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora';
5
6-- 修改pfile，去除dg相关参数
7vi $ORACLE_HOME/dbs/initTEST.ora
-
需要根据情况对pfile做相关的修改，最终的参数文件内容：
1*.audit_file_dest='/home/oracle/oradata/TEST/adump'
2*.audit_trail='db'
3*.compatible='11.2.0.0.0'
4*.control_files='/home/oracle/oradata/TEST/control01.ctl'
5*.db_block_size=8192
6*.db_domain=''
7*.db_name='oradg11g'
8*.db_recovery_file_dest='/home/oracle/oradata/flash_recovery_area'
9*.db_recovery_file_dest_size=4322230272
10*.db_unique_name='TEST'
11*.dispatchers='(PROTOCOL=TCP) (SERVICE=oradg11gXDB)'
12*.memory_max_target=229715200
13*.open_cursors=300
14*.processes=150
15*.remote_login_passwordfile='EXCLUSIVE'
16*.standby_file_management='AUTO'
17*.undo_tablespace='UNDOTBS1'
18*.log_file_name_convert='/u01/app/oracle/oradata/oradg11g/','/home/oracle/oradata/'
19*.db_recovery_file_dest='/home/oracle/oradata/'建议加上log_file_name_convert和db_recovery_file_dest参数，可以避免很多后续的日志转换操作。
-
根据pfile文件内容创建相关目录
1mkdir -p /home/oracle/oradata/TEST/adump
2mkdir -p /home/oracle/oradata/flash_recovery_area -
根据pfile创建spfile，并启动到nomout
1create spfile from pfile='/home/oracle/a.txt';
2startup force nomount
整个执行过程：
1[oracle@lhrora11203 oracle_bk]$ export ORACLE_SID=TEST
2[oracle@lhrora11203 oracle_bk]$ rman target /
3
4Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:07:43 2020
5
6Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
7
8connected to target database (not started)
9
10RMAN> startup nomount;
11
12Oracle instance started
13
14Total System Global Area 242208768 bytes
15
16Fixed Size 2227176 bytes
17Variable Size 184550424 bytes
18Database Buffers 50331648 bytes
19Redo Buffers 5099520 bytes
20
21RMAN> restore spfile to pfile '?/dbs/initTEST.ora' from '/home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora';
22
23Starting restore at 2020-09-25 15:15:08
24using target database control file instead of recovery catalog
25allocated channel: ORA_DISK_1
26channel ORA_DISK_1: SID=156 device type=DISK
27
28channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora
29channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
30Finished restore at 2020-09-25 15:15:10
31
32RMAN> exit
33
34
35Recovery Manager complete.
36[oracle@lhrora11203 ~]$
37
38[oracle@lhrora11203 ~]$ more /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initTEST.ora
39oradgphy.__db_cache_size=37748736
40oradgphy.__java_pool_size=4194304
41oradgphy.__large_pool_size=4194304
42oradgphy.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
43oradgphy.__pga_aggregate_target=12582912
44oradgphy.__sga_target=197132288
45oradgphy.__shared_io_pool_size=0
46oradgphy.__shared_pool_size=142606336
47oradgphy.__streams_pool_size=0
48*.archive_lag_target=0
49*.audit_file_dest='/u01/app/oracle/admin/oradgphy/adump'
50*.audit_trail='db'
51*.compatible='11.2.0.0.0'
52*.control_files='/u01/app/oracle/oradata/oradgphy/crontal01.ctl','/u01/app/oracle/oradata/oradgphy/control02.ctl'
53*.db_block_size=8192
54*.db_domain=''
55*.db_file_name_convert='oradg11g','oradgphy'
56*.db_name='oradg11g'
57*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
58*.db_recovery_file_dest_size=4322230272
59*.db_unique_name='oradgphy'
60*.dg_broker_start=TRUE
61*.diagnostic_dest='/u01/app/oracle'
62*.dispatchers='(PROTOCOL=TCP) (SERVICE=oradg11gXDB)'
63*.fal_client='tns_oradg11g'
64*.fal_server='tns_oradg11g'
65*.log_archive_config='dg_config=(oradgphy,oradg11g,oradglg)'
66*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=oradgphy'
67*.log_archive_dest_2='SERVICE=tns_oradg11g LGWR ASYNC db_unique_name=oradg11g valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'
68*.log_archive_dest_state_1='ENABLE'
69*.log_archive_dest_state_2='ENABLE'
70oradgphy.log_archive_format='%t_%s_%r.dbf'
71*.log_archive_max_processes=4
72*.log_archive_min_succeed_dest=1
73oradgphy.log_archive_trace=0
74*.log_file_name_convert='oradg11g','oradgphy'
75*.memory_max_target=209715200
76*.memory_target=209715200
77*.open_cursors=300
78*.processes=150
79*.remote_login_passwordfile='EXCLUSIVE'
80*.standby_file_management='AUTO'
81*.undo_tablespace='UNDOTBS1'
82[oracle@lhrora11203 ~]$
83[oracle@lhrora11203 ~]$
84[oracle@lhrora11203 ~]$ cat > /home/oracle/a.txt <<"EOF"
85> *.audit_file_dest='/home/oracle/oradata/TEST/adump'
86> *.audit_trail='db'
87> *.compatible='11.2.0.0.0'
88> *.control_files='/home/oracle/oradata/TEST/control01.ctl'
89> *.db_block_size=8192
90> *.db_domain=''
91> *.db_name='oradg11g'
92> *.db_recovery_file_dest='/home/oracle/oradata/flash_recovery_area'
93> *.db_recovery_file_dest_size=4322230272
94> *.db_unique_name='TEST'
95> *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradg11gXDB)'
96> *.memory_max_target=229715200
97> *.open_cursors=300
98> *.processes=150
99> *.remote_login_passwordfile='EXCLUSIVE'
100> *.standby_file_management='AUTO'
101> *.undo_tablespace='UNDOTBS1'
102> *.log_file_name_convert='/u01/app/oracle/oradata/oradg11g/','/home/oracle/oradata/'
103> *.db_recovery_file_dest='/home/oracle/oradata/'
104> EOF
105[oracle@lhrora11203 ~]$ sas
106
107SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 25 15:18:00 2020
108
109Copyright (c) 1982, 2011, Oracle. All rights reserved.
110
111
112Connected to:
113Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
114With the Partitioning, OLAP, Data Mining and Real Application Testing options
115
116SYS@TEST> create spfile from pfile='/home/oracle/a.txt';
117
118File created.
119
120SYS@TEST> startup force nomount
121ORACLE instance started.
122
123Total System Global Area 242208768 bytes
124Fixed Size 2227176 bytes
125Variable Size 184550424 bytes
126Database Buffers 50331648 bytes
127Redo Buffers 5099520 bytes
3.2、恢复控制文件
1-- 因为要恢复为主库，所以需要加上primary关键字
2restore primary controlfile from '/home/oracle/oracle_bk/standby_1.ctl';
执行过程：
1[oracle@lhrora11203 ~]$ rman target /
2
3Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:24:08 2020
4
5Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
6
7connected to target database: ORADG11G (not mounted)
8
9RMAN> restore primary controlfile from '/home/oracle/oracle_bk/standby_1.ctl';
10
11Starting restore at 2020-09-25 15:24:30
12using target database control file instead of recovery catalog
13allocated channel: ORA_DISK_1
14channel ORA_DISK_1: SID=156 device type=DISK
15
16channel ORA_DISK_1: restoring control file
17channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
18output file name=/home/oracle/oradata/TEST/control01.ctl
19Finished restore at 2020-09-25 15:24:32
3.3、注册备份信息
1-- 启动到mout阶段
2alter database mount;
3
4-- 清除之前的备份信息
5EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(11); /** CLEAR V$ARCHIVED_LOG */
6EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(12); /** CLEAR V$BACKUP_SET */
7EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13); /** CLEAR V$BACKUP_PIECE */
8
9--重新注册,注意路径最后一定需要加上/
10catalog start with '/home/oracle/oracle_bk/';
执行过程：
1SYS@TEST> alter database mount;
2
3Database altered.
4
5SYS@TEST> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(11);
6
7PL/SQL procedure successfully completed.
8
9SYS@TEST>
10SYS@TEST> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(12);
11
12PL/SQL procedure successfully completed.
13
14SYS@TEST>
15SYS@TEST> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13);
16
17PL/SQL procedure successfully completed.
18
19SYS@TEST> exit
20Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
21With the Partitioning, OLAP, Data Mining and Real Application Testing options
22[oracle@lhrora11203 ~]$ rman target /
23
24Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:31:44 2020
25
26Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
27
28connected to target database: ORADG11G (DBID=1403587593, not open)
29
30RMAN> catalog start with '/home/oracle/oracle_bk/';
31
32Starting implicit crosscheck backup at 2020-09-25 15:31:47
33using target database control file instead of recovery catalog
34allocated channel: ORA_DISK_1
35channel ORA_DISK_1: SID=156 device type=DISK
36Finished implicit crosscheck backup at 2020-09-25 15:31:48
37
38Starting implicit crosscheck copy at 2020-09-25 15:31:48
39using channel ORA_DISK_1
40Crosschecked 4 objects
41Finished implicit crosscheck copy at 2020-09-25 15:31:48
42
43searching for all files in the recovery area
44cataloging files...
45no files cataloged
46
47searching for all files that match the pattern /home/oracle/oracle_bk/
48
49List of Files Unknown to the Database
50=====================================
51File Name: /home/oracle/oracle_bk/standby_1.ctl
52File Name: /home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full
53File Name: /home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora
54File Name: /home/oracle/oracle_bk/FULL_ORADG11G_37vbaa3q_1_1.full
55
56Do you really want to catalog the above files (enter YES or NO)? yes
57cataloging files...
58cataloging done
59
60List of Cataloged Files
61=======================
62File Name: /home/oracle/oracle_bk/standby_1.ctl
63File Name: /home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full
64File Name: /home/oracle/oracle_bk/spfile_ORADG11G_39vbaa3v_1_1.ora
65File Name: /home/oracle/oracle_bk/FULL_ORADG11G_37vbaa3q_1_1.full
66
67RMAN>
68RMAN> list backupset summary;
69
70using target database control file instead of recovery catalog
71
72List of Backups
73===============
74Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
75------- -- -- - ----------- ------------------- ------- ------- ---------- ---
761 B F A DISK 2020-09-25 14:49:32 1 1 NO TAG20200925T144932
772 B F A DISK 2020-09-25 14:48:55 1 1 YES TAG20200925T144855
783 B F A DISK 2020-09-25 14:49:35 1 1 NO TAG20200925T144935
794 B F A DISK 2020-09-25 14:49:30 1 1 YES TAG20200925T144855
3.4、还原数据文件
1run{
2SET NEWNAME FOR DATABASE TO '/home/oracle/oradata/%b';
3restore database;
4switch datafile all;
5}
执行过程：
1[oracle@lhrora11203 ~]$ rman target /
2
3Recovery Manager: Release 11.2.0.3.0 - Production on Fri Sep 25 15:31:44 2020
4
5Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
6
7connected to target database: ORADG11G (DBID=1403587593, not open)
8
9
10RMAN> run{
112> SET NEWNAME FOR DATABASE TO '/home/oracle/oradata/%b';
123> restore database;
134> switch datafile all;
145> }
15
16executing command: SET NEWNAME
17
18Starting restore at 2020-09-25 15:32:54
19using channel ORA_DISK_1
20
21channel ORA_DISK_1: starting datafile backup set restore
22channel ORA_DISK_1: specifying datafile(s) to restore from backup set
23channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/oradata/system01.dbf
24channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oradata/sysaux01.dbf
25channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/oradata/undotbs01.dbf
26channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oradata/users01.dbf
27channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oradata/example01.dbf
28channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/oradata/logmnrtbs1.dbf
29channel ORA_DISK_1: reading from backup piece /home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full
30channel ORA_DISK_1: piece handle=/home/oracle/oracle_bk/FULL_ORADG11G_36vbaa2n_1_1.full tag=TAG20200925T144855
31channel ORA_DISK_1: restored backup piece 1
32channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
33Finished restore at 2020-09-25 15:34:20
34
35datafile 1 switched to datafile copy
36input datafile copy RECID=36 STAMP=1052062460 file name=/home/oracle/oradata/system01.dbf
37datafile 2 switched to datafile copy
38input datafile copy RECID=37 STAMP=1052062460 file name=/home/oracle/oradata/sysaux01.dbf
39datafile 3 switched to datafile copy
40input datafile copy RECID=38 STAMP=1052062460 file name=/home/oracle/oradata/undotbs01.dbf
41datafile 4 switched to datafile copy
42input datafile copy RECID=39 STAMP=1052062460 file name=/home/oracle/oradata/users01.dbf
43datafile 5 switched to datafile copy
44input datafile copy RECID=40 STAMP=1052062460 file name=/home/oracle/oradata/example01.dbf
45datafile 6 switched to datafile copy
46input datafile copy RECID=41 STAMP=1052062460 file name=/home/oracle/oradata/logmnrtbs1.dbf
47
48RMAN> exit
49
50
51Recovery Manager complete.
3.5、激活备库为主库，并启动数据库
1-- 1、查询数据库状态
2select open_mode , database_role, flashback_on from v$database;
3
4-- 2、激活备库为主库，如果已经是主库了，那就跳过该步骤
5alter database activate standby database;
6-- ALTER DATABASE CLEAR LOGFILE GROUP 4;
7-- alter database drop logfile group 4;
8
9-- 3、启动数据库
10alter database open resetlogs;
结果：
1[oracle@lhrora11203 ~]$ sas
2
3SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 25 15:34:58 2020
4
5Copyright (c) 1982, 2011, Oracle. All rights reserved.
6
7
8Connected to:
9Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
10With the Partitioning, OLAP, Data Mining and Real Application Testing options
11
12SYS@TEST> select open_mode , database_role, flashback_on from v$database;
13
14OPEN_MODE DATABASE_ROLE FLASHBACK_ON
15-------------------- ---------------- ------------------
16MOUNTED PRIMARY NO
17
18SYS@TEST> set line 1000
19SYS@TEST> set numwidth 16
20SYS@TEST> col name format a55
21SYS@TEST> select fuzzy, status,checkpoint_change#, checkpoint_time,file#,name from v$datafile_header ;
22
23FUZ STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME FILE# NAME
24--- ------- ------------------ ------------------- ---------------- -------------------------------------------------------
25NO ONLINE 3500675 2020-09-25 14:44:17 1 /home/oracle/oradata/system01.dbf
26NO ONLINE 3500675 2020-09-25 14:44:17 2 /home/oracle/oradata/sysaux01.dbf
27NO ONLINE 3500675 2020-09-25 14:44:17 3 /home/oracle/oradata/undotbs01.dbf
28NO ONLINE 3500675 2020-09-25 14:44:17 4 /home/oracle/oradata/users01.dbf
29NO ONLINE 3500675 2020-09-25 14:44:17 5 /home/oracle/oradata/example01.dbf
30NO ONLINE 3500675 2020-09-25 14:44:17 6 /home/oracle/oradata/logmnrtbs1.dbf
31
32-- 此处查询出来fuzzy列都为NO，所以应该可以直接启动数据库，而不用执行recover操作。
33
34
35SYS@TEST> alter database open resetlogs;
36alter database open resetlogs
37*
38ERROR at line 1:
39ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
40ORA-00312: online log 2 thread 1: '/home/oracle/oradata/redo02.log'
41
42
43SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 2;
44
45Database altered.
46
47SYS@TEST> alter database open resetlogs;
48
49Database altered.
50
51SYS@TEST> select open_mode , database_role, flashback_on from v$database;
52
53OPEN_MODE DATABASE_ROLE FLASHBACK_ON
54-------------------- ---------------- ------------------
55READ WRITE PRIMARY NO
56
57SYS@TEST> archive log list;
58Database log mode Archive Mode
59Automatic archival Enabled
60Archive destination USE_DB_RECOVERY_FILE_DEST
61Oldest online log sequence 1
62Next log sequence to archive 1
63Current log sequence 1
3.6、删除不用的日志组
1col member format a100
2select * from v$logfile;
3select 'alter database drop logfile group '|| GROUP# ||';' from v$standby_log;
4alter database drop logfile group 4;
5ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;
执行过程：
1SYS@TEST> col member format a100
2SYS@TEST> select * from v$logfile;
3
4 GROUP# STATUS TYPE MEMBER IS_
5---------------- ------- ------- ------------------------------------------------------- ---
6 3 ONLINE /home/oracle/oradata/redo03.log NO
7 2 ONLINE /home/oracle/oradata/redo02.log NO
8 1 ONLINE /home/oracle/oradata/redo01.log NO
9 4 STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo04.log NO
10 5 STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo05.log NO
11 6 STANDBY /u01/app/oracle/oradata/oradgphy/standby_redo06.log NO
12 7 STANDBY /home/oracle/oradata/standby_redo07.log NO
13
147 rows selected.
15
16SYS@TEST> select 'alter database drop logfile group '|| GROUP# ||';' from v$standby_log;
17
18'ALTERDATABASEDROPLOGFILEGROUP'||GROUP#||';'
19---------------------------------------------------------------------------
20alter database drop logfile group 4;
21alter database drop logfile group 5;
22alter database drop logfile group 6;
23alter database drop logfile group 7;
24
25SYS@TEST> alter database drop logfile group 4;
26
27Database altered.
28
29SYS@TEST> alter database drop logfile group 5;
30alter database drop logfile group 5
31*
32ERROR at line 1:
33ORA-00313: open failed for members of log group 5 of thread 1
34ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log'
35ORA-27037: unable to obtain file status
36Linux-x86_64 Error: 2: No such file or directory
37Additional information: 3
38
39
40SYS@TEST> alter database drop logfile group 6;
41
42Database altered.
43
44SYS@TEST> alter database drop logfile group 7;
45
46Database altered.
47
48SYS@TEST> alter database drop logfile group 5;
49alter database drop logfile group 5
50*
51ERROR at line 1:
52ORA-00313: open failed for members of log group 5 of thread 1
53ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log'
54ORA-27037: unable to obtain file status
55Linux-x86_64 Error: 2: No such file or directory
56Additional information: 3
57
58
59SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 5;
60ALTER DATABASE CLEAR LOGFILE GROUP 5
61*
62ERROR at line 1:
63ORA-00350: log 5 of instance TEST (thread 1) needs to be archived
64ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo05.log'
65
66
67SYS@TEST> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5;
68ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5
69*
70ERROR at line 1:
71ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/oradgphy/standby_redo05.log'
72ORA-27040: file create error, unable to create file
73Linux-x86_64 Error: 2: No such file or directory
74Additional information: 1
75
76
77SYS@TEST> ALTER DATABASE CLEAR LOGFILE GROUP 5;
78ALTER DATABASE CLEAR LOGFILE GROUP 5
79*
80ERROR at line 1:
81ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/oradgphy/standby_redo05.log'
82ORA-27040: file create error, unable to create file
83Linux-x86_64 Error: 2: No such file or directory
84Additional information: 1
85
86
87SYS@TEST> alter database drop logfile group 5;
88
89Database altered.
90
91
92SYS@TEST> select * from v$logfile;
93
94 GROUP# STATUS TYPE MEMBER IS_
95---------------- ------- ------- --------------------------------- ---
96 3 ONLINE /home/oracle/oradata/redo03.log NO
97 2 ONLINE /home/oracle/oradata/redo02.log NO
98 1 ONLINE /home/oracle/oradata/redo01.log NO
本文结束，感兴趣的朋友可以自行测试不同情况下的还原。
本文结束。
• 微信公众号：DB宝，作者：小麦苗
• 作者博客地址：http://blog.itpub.net/26736162/
• 作者微信：db_bao• 作者QQ：646634621，QQ群：230161599、618766405
• 提供Oracle OCP、OCM、高可用（rac+dg+ogg）和MySQL DBA培训
• 版权所有，欢迎分享本文，转载请保留出处• 若有侵权请联系小麦苗删除
★DB宝分享的IT资料：https://mp.weixin.qq.com/s/Iwsy-zkzwgs8nYkcMz29ag★DB宝笔试面试详解：https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w
长按下图识别二维码，关注小麦苗的微信公众号：DB宝，学习最实用的数据库技术。
本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。