共计 9434 个字符，预计需要花费 24 分钟才能阅读完成。
这篇文章主要介绍了 Oracle DG 从库 Rman 如何实现备份恢复测试，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。
1. 系统初始化和数据库安装
略
2. 参数文件恢复
RMAN startup nomount ;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file /U01/app/oracle/product/11.2.0.4/dbs/inittest.ora
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 301990792 bytes
Database Buffers 754974720 bytes
Redo Buffers 9711616 bytes
RMAN restore spfile from /U01/tools/20170310/full_TEST_20170310_3839
Starting restore at 2017-03-10 15:14:11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1345 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /U01/tools/20170310/full_TEST_20170310_3839
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2017-03-10 15:14:12
SQL shutdown immediate;
[oracle@DB_TEST dbs]$ cd $ORACLE_HOME/dbs
[oracle@DB_TEST dbs]$ strings spfiletest.ora inittest.ora
[oracle@DB_TEST dbs]$ mkdir -p /U01/app/oracle/oradata/test/
[oracle@DB_TEST dbs]$ mkdir -p /U01/app/oracle/fast_recovery_area/test/
[oracle@DB_TEST dbs]$ mkdir -p /U01/app/oracle/fast_recovery_area/TEST/arch
[oracle@DB_TEST dbs]$ mv spfiletest.ora spfiletest.ora_bak
SQL !mkdir -p /U01/app/oracle/admin/test/adump
SQL startup nomount;
ORACLE instance started.
Total System Global Area 2.0310E+10 bytes
Fixed Size 2262008 bytes
Variable Size 3355446280 bytes
Database Buffers 1.6911E+10 bytes
Redo Buffers 40865792 bytes
SQL create spfile from pfile;
File created.
SQL shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL startup nomount;
ORACLE instance started.
Total System Global Area 2.0310E+10 bytes
Fixed Size 2262008 bytes
Variable Size 3355446280 bytes
Database Buffers 1.6911E+10 bytes
Redo Buffers 40865792 bytes
3. 控制文件恢复
通过备份恢复 standby 控制文件，然后创建新的控制文件做不完全恢复
RMAN restore standby controlfile from /U01/tools/20170310/full_TEST_20170310_3838
Starting restore at 2017-03-10 15:29:28
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 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/oracle/oradata/test/control01.ctl
output file name=/U01/app/oracle/fast_recovery_area/test/control02.ctl
Finished restore at 2017-03-10 15:29:29
RMAN alter database mount;
database mounted
released channel: ORA_DISK_1
SQL select open_mode from v$database;
OPEN_MODE
——————–
MOUNTED
4. 数据文件恢复
RMAN catalog start with /U01/tools/20170310
searching for all files that match the pattern /U01/tools/20170310
List of Files Unknown to the Database
=====================================
File Name: /U01/tools/20170310/arch_TEST_20170310_3835
File Name: /U01/tools/20170310/full_TEST_20170310_3836
File Name: /U01/tools/20170310/full_TEST_20170310_3839
File Name: /U01/tools/20170310/full_TEST_20170310_3837
File Name: /U01/tools/20170310/full_TEST_20170310_3838
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /U01/tools/20170310/arch_TEST_20170310_3835
File Name: /U01/tools/20170310/full_TEST_20170310_3836
File Name: /U01/tools/20170310/full_TEST_20170310_3839
File Name: /U01/tools/20170310/full_TEST_20170310_3837
File Name: /U01/tools/20170310/full_TEST_20170310_3838
RMAN restore database;
恢复到指定的 sequence
RMAN recover database until sequence 5545;
Starting recover at 2017-03-10 15:34:27
using channel ORA_DISK_1
starting media recovery
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: /U01/app/oracle/oradata/test/system01.dbf
media recovery complete, elapsed time: 00:00:01
Finished recover at 2017-03-10 15:34:28
5. 重建控制文件
SQL alter database backup controlfile to trace as /U01/tools/20170310/control.trc
SQL shutdown immediate;
SQL startup nomount;
[oracle@DB_TEST 20170310]$ rm -rf /U01/app/oracle/oradata/test/control01.ctl
[oracle@DB_TEST 20170310]$ rm -rf /U01/app/oracle/fast_recovery_area/test/control02.ctl
SQL @create_controlfile.sql
SQL select open_mode from v$database;
OPEN_MODE
——————–
MOUNTED
重建控制文件：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE TEST RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 40
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_1_bjzdlzks_.log SIZE 100M BLOCKSIZE 512,
GROUP 2 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_2_bjzdlzy4_.log SIZE 100M BLOCKSIZE 512,
GROUP 3 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_3_bjzdm0c7_.log SIZE 100M BLOCKSIZE 512,
GROUP 4 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_4_bjzdm0qj_.log SIZE 100M BLOCKSIZE 512,
GROUP 5 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_5_bjzdm14j_.log SIZE 100M BLOCKSIZE 512,
GROUP 6 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_6_bjzdm1js_.log SIZE 100M BLOCKSIZE 512,
GROUP 7 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_7_bjzdm1wz_.log SIZE 100M BLOCKSIZE 512,
GROUP 8 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_8_bjzdm29z_.log SIZE 100M BLOCKSIZE 512,
GROUP 9 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_9_bjzdm2p8_.log SIZE 100M BLOCKSIZE 512,
GROUP 10 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_10_bjzdm338_.log SIZE 100M BLOCKSIZE 512,
GROUP 11 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_11_bjzdm3hk_.log SIZE 100M BLOCKSIZE 512,
GROUP 12 /U01/app/oracle/fast_recovery_area/TEST02/onlinelog/o1_mf_12_bjzdm3vt_.log SIZE 100M BLOCKSIZE 512
,
DATAFILE
/U01/app/oracle/oradata/test/system01.dbf ,
/U01/app/oracle/oradata/test/sysaux01.dbf ,
/U01/app/oracle/oradata/test/undotbs01.dbf ,
/U01/app/oracle/oradata/test/users01.dbf ,
/U01/app/oracle/oradata/test/test_data01.dbf ,
/U01/app/oracle/oradata/test/test_index01.dbf ,
/U01/app/oracle/oradata/test/test_data02.dbf ,
/U01/app/oracle/oradata/test/test_data03.dbf ,
/U01/app/oracle/oradata/test/test_index02.dbf ,
/U01/app/oracle/oradata/test/test_index03.dbf
CHARACTER SET ZHS16GBK
;
6. 不完全恢复数据库
select file#,checkpoint_change# from v$datafile;
select checkpoint_change# from v$database;
查看隐藏参数值：_allow_resetlogs_corruption （整个调整的目标是强制启动数据库，设置此参数之后，在数据库 Open 过程中，Oracle 会跳过某些一致性检查，从而使数据库可能跳过不一致状态，Open 打开）
col KSPPINM for a30;
col KSPPSTVL for a30;
col KSPPDESC for a30;
set line 200;
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = _allow_resetlogs_corruption
SQL alter system set _allow_resetlogs_corruption =true scope=spfile;
SQL shutdown immediate;
SQL startup mount;
SQL alter database open resetlogs;
Database altered.
SQL alter system set _allow_resetlogs_corruption =FALSE scope=spfile;
SQL shutdown immediate;
SQL startup ;
ORACLE instance started.
Total System Global Area 2.0310E+10 bytes
Fixed Size 2262008 bytes
Variable Size 3355446280 bytes
Database Buffers 1.6911E+10 bytes
Redo Buffers 40865792 bytes
Database mounted.
Database opened.
SQL
至此数据恢复过程完成。
7. 监听和 tnsnames.ora 配置文件恢复
Listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_TEST)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /U01/app/oracle/product/11.2.0.4)
(SID_NAME = test)
)
(SID_DESC =
(GLOBAL_DBNAME = test00_DGMGRL)
(ORACLE_HOME = /U01/app/oracle/product/11.2.0.4)
(SID_NAME = test)
)
)
LISTENER1532 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_TEST)(PORT = 1532))
)
)
)
SID_LIST_LISTENER1532 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /U01/app/oracle/product/11.2.0.4)
(SID_NAME = test)
)
(SID_DESC =
(GLOBAL_DBNAME = test00_DGMGRL)
(ORACLE_HOME = /U01/app/oracle/product/11.2.0.4)
(SID_NAME = test)
)
)
LISTENER1522 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_TEST)(PORT = 1522))
)
)
)
SID_LIST_LISTENER1522 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /U01/app/oracle/product/11.2.0.4)
(SID_NAME = test)
)
)
LISTENER1523 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_TEST)(PORT = 1523))
)
)
)
SID_LIST_LISTENER1523 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /U01/app/oracle/product/11.2.0.4)
(SID_NAME = test)
)
)
LISTENER1525 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_TEST)(PORT = 1525))
)
)
)
SID_LIST_LISTENER1525 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /U01/app/oracle/product/11.2.0.4)
(SID_NAME = test)
)
)
LISTENER1528 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_TEST)(PORT = 1528))
)
)
)
SID_LIST_LISTENER1528 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /U01/app/oracle/product/11.2.0.4)
(SID_NAME = test)
)
)
LISTENER1526 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_TEST)(PORT = 1526))
)
)
)
SID_LIST_LISTENER1526 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = /U01/app/oracle/product/11.2.0.4)
(SID_NAME = test)
)
)
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
tnsnames.ora
# tnsnames.ora Network Configuration File: /U01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DB_TEST)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
可通过 lsnrctl start /lsnrctl start LISTENER1522/lsnrctl start LISTENER1523/lsnrctl start LISTENER1525 启动监听，可通过 tnsping test01 测试服务名连通性，可通过 netstat –luntp 查看启动监听端口。
8. 数据验证
SQL alter session set current_schema=TEST;
SQL select UPDATE_TIME from table_name where rownum =10 order by UPDATE_TIME desc;
UPDATE_TIME
——————-
2017-03-10 02:00:05
2017-03-10 01:59:48
2016-03-17 16:06:22
2016-03-17 14:43:47
2015-05-14 12:12:32
感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“Oracle DG 从库 Rman 如何实现备份恢复测试”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!
