公司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
http://blog.itpub.net/27067062/viewspace-2135152/

Oracle DG從庫 Rman備份恢復測試

相關文章