13518219792
这篇文章主要介绍了Oracle DG从库Rman如何实现备份恢复测试，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。
我们提供的服务有：网站制作、成都网站设计、微信公众号开发、网站优化、网站认证、山阳ssl等。为1000+企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的山阳网站制作公司
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
感谢你能够认真阅读完这篇文章，希望小编分享的“Oracle DG从库Rman如何实现备份恢复测试”这篇文章对大家有帮助，同时也希望大家多多支持创新互联，关注创新互联行业资讯频道，更多相关知识等着你来学习!
