Oracle DG从库 Rman备份恢复测试

  • yepkeepmoving

    2017-03-11 11:09:02
  • Oracle
  • 原创

公司Oracle备份策略是10G采用DG主库每天Rman一全备份+expdp备份，11g采用的是DG从库每天rman一全备份。最近在做恢复演练测试，在从库备份恢复时，由于控制文件是standby类型，无法直接打开数据库，也没有找到standby controlfile ——>primary controlfile转换的方法，这里是通过控制文件重建的方式做的备库备份恢复，仅供有需要的同仁参考。如有其他方法，欢迎在评论区回复。

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

 



请使用浏览器的分享功能分享到微信等