RAC資料恢復至單機環境

一、概括

隨著越來越多的RAC環境，將RAC資料遷移至單機環境下的需求也隨之增加，怎樣才能更有效的遷移呢，方法有許多邏輯遷移（exp/expdp）、RMAN、DG、goldengate等，我們可根據自身環境及需求選擇相應的遷移方式，下面簡單介紹一下使用RMAN將RAC資料遷移至單機環境中的步驟。

二、模擬環境：整套RAC環境無故當機，暫無法恢復，我們將之前備份的資料進行恢復
將最近一次全備份檔案上傳至目標環境，並檢視檔案資訊

[oracle@oradb1 backup]$ pwd

/oracle/backup

[oracle@oradb1 backup]$ ls -lrt

總計 264312

-rw-r----- 1 oracle oinstall 132608 02-17 13:24 arch_0lp0ro84

-rw-r----- 1 oracle oinstall 19456 02-17 13:24 arch_0ip0ro4u

-rw-r----- 1 oracle oinstall 1114112 02-17 13:24 full_0kp0ro7v_1_1

-rw-r----- 1 oracle oinstall 269107200 02-17 13:24 full_0jp0ro50_1_

透過上述我們可以看到，歸檔檔案有兩個，由於是測試環境，資料量及備份資訊較簡單，資料檔案也是兩個，由此得知檔案full_0kp0ro7v_1_1存放引數檔案和控制檔案。
設定環境變數

[oracle@oradb1 ~]$ vi ~/.bash_profile

修改引數為：export ORACLE_SID=racdb1

恢復spfile檔案

[oracle@oradb1 backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 17 11:37:45 2014

connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/oracle/db_1/dbs/initracdb1.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 281019272 bytes

Database Buffers 780140544 bytes

Redo Buffers 5517312 bytes

RMAN> restore spfile from '/oracle/backup/full_0kp0ro7v_1_1';

Starting restore at 17-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oracle/backup/full_0kp0ro7v_1_1

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 17-FEB-14

RMAN> restore spfile from '/oracle/backup/full_0kp0ro7v_1_1';

Starting restore at 17-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oracle/backup/full_0kp0ro7v_1_1

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 17-FEB-14

登入到資料庫，生成pfile檔案，並編輯，刪除與叢集即節點2相關引數，修改控制檔案及其他目錄路徑，並根據需求建立相應目錄（如閃回區、oracle基礎目錄）

idle> create pfile from spfile;

File created.

建立spfile，並重啟例項

idle> create spfile from pfile;

File created.

idle> show parameter pfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string

idle> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string

idle> shutdown abort

ORACLE instance shut down.

idle> startup nomount

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2254952 bytes

Variable Size 213911448 bytes

Database Buffers 314572800 bytes

Redo Buffers 3723264 bytes

idle> show parameter pfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /oracle/db_1/dbs/spfileracdb1.

Ora

恢復控制檔案，並啟動資料庫到mount模式

[oracle@oradb1 oracle]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 17 12:45:58 2014

connected to target database: RACDB (not mounted)

RMAN> restore controlfile from '/oracle/backup/full_0bp0rlcu_1_1';

Starting restore at 17-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

output file name=/oracle/oradata/racdb/current.273.839446247

output file name=/oracle/oradata/racdb/current.274.839446249

Finished restore at 17-FEB-14

RMAN> sql 'alter database mount';

sql statement: alter database mount

released channel: ORA_DISK_1

檢視備份集資訊

RMAN> list backup;

List of Backup Sets

===================

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

18 18.50K DISK 00:00:00 17-FEB-14

BP Key: 18 Status: AVAILABLE Compressed: YES Tag: TAG20140217T133110

Piece Name: /oracle/backup/arch_0ip0ro4u

List of Archived Logs in backup set 18

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 112 1482625 17-FEB-14 1482667 17-FEB-14

1 113 1482667 17-FEB-14 1482671 17-FEB-14

1 114 1482671 17-FEB-14 1482704 17-FEB-14

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

19 Full 256.63M DISK 00:01:34 17-FEB-14

BP Key: 19 Status: AVAILABLE Compressed: YES Tag: TAG20140217T133111

Piece Name: /oracle/backup/full_0jp0ro50_1_1

List of Datafiles in backup set 19

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 1482715 17-FEB-14 +DATA/racdb/datafile/system.279.839446279

2 Full 1482715 17-FEB-14 +DATA/racdb/datafile/sysaux.280.839446345

3 Full 1482715 17-FEB-14 +DATA/racdb/datafile/undotbs1.281.839446447

4 Full 1482715 17-FEB-14 +DATA/racdb/datafile/undotbs2.283.839446487

5 Full 1482715 17-FEB-14 +DATA/racdb/datafile/users.284.839446515

根據備份資訊，恢復資料檔案及資料庫

RMAN> run{

2> set newname for datafile 1 to '/oracle/oradata/racdb/system01.dbf';

3> set newname for datafile 3 to '/oracle/oradata/racdb/undotbs01.dbf';

4> set newname for datafile 2 to '/oracle/oradata/racdb/sysaux01.dbf';

5> set newname for datafile 5 to '/oracle/oradata/racdb/users01.dbf';

6> set newname for datafile 4 to '/oracle/oradata/racdb/undotbs02.dbf';

7> restore database;

8> switch datafile all;

9> }

executing command: SET NEWNAME

Starting restore at 17-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /oracle/oradata/racdb/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /oracle/oradata/racdb/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /oracle/oradata/racdb/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /oracle/oradata/racdb/undotbs02.dbf

channel ORA_DISK_1: restoring datafile 00005 to /oracle/oradata/racdb/users01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/backup/full_0jp0ro50_1_1

channel ORA_DISK_1: piece handle=/oracle/backup/full_0jp0ro50_1_1 tag=TAG20140217T133111

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:55

Finished restore at 17-FEB-14

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=839771072 file name=/oracle/oradata/racdb/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=839771072 file name=/oracle/oradata/racdb/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=839771072 file name=/oracle/oradata/racdb/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=839771072 file name=/oracle/oradata/racdb/undotbs02.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=10 STAMP=839771072 file name=/oracle/oradata/racdb/users01.dbf

RMAN> run{

2> set until scn 1482715; --scn 號根據備份集資訊得出

recover database;

}3> 4>

executing command: SET until clause

Starting recover at 17-FEB-14

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 17-FEB-14

開啟資料庫

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

檢視redo log 資訊，並刪除無效日誌組（節點2日誌）

sys@RACDB> select THREAD#, STATUS, ENABLED from v$thread;

THREAD# STATUS ENABLED

---------- ------ --------

1 OPEN PUBLIC

2 CLOSED PUBLIC

sys@RACDB> select group# from v$log where THREAD#=2;

GROUP#

----------

3

4

刪除日誌組3、4

sys@RACDB> alter database disable thread 2;

Database altered.
sys@RACDB> alter database clear unarchived logfile group 3;

Database altered.

sys@RACDB> alter database drop logfile group 3;

Database altered.

檢視undo表空間，並刪除節點2（在此不使用）的undo表空間

sys@RACDB> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

sys@RACDB> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME

------------------------------

UNDOTBS1

UNDOTBS2

sys@RACDB> drop tablespace UNDOTBS2 including contents and datafiles;

修改臨時表空間

sys@RACDB> select name from v$tempfile;

NAME

----------------------------------------------------------------------------------------------------

/oracle/admin/racdb/RACDB/datafile/o1_mf_temp_9j38pmmk_.tmp

sys@RACDB> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

TABLESPACE_NAME

------------------------------

TEMP

sys@RACDB> create temporary tablespace TEMP1 tempfile '/oracle/oradata/racdb/temp01.dbf' size 50M autoextend off;

Tablespace created.

sys@RACDB> alter database default temporary tablespace TEMP1;

Database altered.

sys@RACDB> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

檢視監聽狀態並配置，編輯tnsname.ora檔案。透過以下命令建立密碼檔案，注意密碼檔案的位置

[oracle@oradb1 dbs]$ orapwd file=orapwracdb1 password=oracle entries=30

附：

設定備份片

RMAN> catalog backuppiece '/oracle/backup/arch2_08p0rhm1';

cataloged backup piece

backup piece handle=/oracle/backup/arch2_08p0rhm1 RECID=4 STAMP=839766295

三、總結
在恢復過程中也遇到不少問題，起初沒太仔細看備份集資訊及歸檔日誌不完全，造成scn號不準確，以至於資料庫無法完成恢復。作為資料庫管理人員，在對資料庫操作時，請仔細核對相關資訊，修改檔案前最好先保留一份原始檔案。詳細RMAN管理語句、命令請參考oracle官方文件。

【RMAN】RAC資料恢復至單機環境

相關文章