Oracle DG从库Rman如何实现备份恢复测试

这篇文章主要介绍了oracle DG从库Rman如何实现备份恢复测试，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

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如何实现备份恢复测试”这篇文章对大家有帮助，同时也希望大家多多支持编程网，关注编程网数据库频道，更多相关知识等着你来学习!