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如何實現備份恢復測試”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!