oracle11g單機資料透過RMAN恢復至RAC

一、概述

隨著越來越多的企業部署oracle的叢集RAC，增強了資料的安全性、提高資料庫效能及保障資料庫實時線上，將原有單機資料庫資料遷移至叢集環境中也成了我們工作中的一部分，遷移的方法也有許多，下面我們就看一下怎樣透過RMAN將單機資料恢復至oracle的RAC中。

二、詳細步驟

將備份檔案上傳至伺服器中，並檢視大小

[oracle@rac01 backup]$ ls -lrt

total 266496

-rw-r----- 1 oracle oinstall 261455872 Feb 24 12:17 full_02p1e2cu_1_1.bak

-rw-r----- 1 oracle oinstall 1097728 Feb 24 12:17 full_03p1e2ev_1_1.bak

-rw-r----- 1 oracle oinstall 10338304 Feb 24 12:20 arch_06p1e2kn_1_1.bak

根據之前備份及檔案大小，我們可以得知引數檔案和控制檔案存放的檔案。

下面恢復引數檔案，恢復之前設定環境變數，並強制啟動資料庫至nomount階段

RMAN> startup nomount force

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

LRM-00109: could not open parameter file '/oracle/app/oracle/product/11.2.0/dbs/initxysoul1.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_03p1e2ev_1_1.bak';

Starting restore at 2014-02-24 16:27:35

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oracle/backup/full_03p1e2ev_1_1.bak

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 2014-02-24 16:27:36

RMAN>

檢視生成的引數檔案，並建立pfile檔案

[oracle@rac01 backup]$ cd $ORACLE_HOME/dbs

[oracle@rac01 dbs]$ ls -lrt

total 18092

-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-rw---- 1 oracle asmadmin 1544 Feb 24 16:26 hc_xysoul1.dat

-rw-r----- 1 oracle asmadmin 2560 Feb 24 16:27 spfilexysoul1.ora

SQL> create pfile from spfile;

File created.

修改引數檔案，修改相關檔案目錄並建立對應目錄

修改完成後，生成spfile檔案，並啟動資料庫到nomount階段

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 534462464 bytes

Fixed Size 2254952 bytes

Variable Size 213911448 bytes

Database Buffers 314572800 bytes

Redo Buffers 3723264 bytes

恢復控制檔案並啟動資料庫到mount階段

RMAN> restore controlfile from '/oracle/backup/full_03p1e2ev_1_1.bak';

Starting restore at 2014-02-24 16:33:52

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=30 device type=DISK

channel ORA_DISK_1: restoring control file

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

output file name=+DATA/o1_mf_9jhz38tl_.ctl

Finished restore at 2014-02-24 16:33:56

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

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

1 24.57M DISK 00:00:04 2014-02-24 12:16:26

BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20140224T121622

Piece Name: /oracle/archivelog/XYSOUL/backupset/2014_02_24/o1_mf_annnn_TAG20140224T121622_9jokwpf5_.bkp

List of Archived Logs in backup set 1

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 18 763748 2014-02-22 10:22:45 798098 2014-02-22 18:31:34

1 19 798098 2014-02-22 18:31:34 819527 2014-02-24 08:48:16

1 20 819527 2014-02-24 08:48:16 827856 2014-02-24 12:15:15

1 21 827856 2014-02-24 12:15:15 827892 2014-02-24 12:16:21

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

2 Full 249.34M DISK 00:00:59 2014-02-24 12:17:29

BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TAG20140224T121629

Piece Name: /oracle/backup/full_02p1e2cu_1_1.bak

List of Datafiles in backup set 2

File LV Type Ckp SCN Ckp Time Name

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

1 Full 827904 2014-02-24 12:16:30 /oracle/oradata/XYSOUL/datafile/o1_mf_system_9jhz3xx6_.dbf

2 Full 827904 2014-02-24 12:16:30 /oracle/oradata/XYSOUL/datafile/o1_mf_sysaux_9jhz4vkj_.dbf

3 Full 827904 2014-02-24 12:16:30 /oracle/oradata/XYSOUL/datafile/o1_mf_undotbs1_9jhz5gsj_.dbf

4 Full 827904 2014-02-24 12:16:30 /oracle/oradata/XYSOUL/datafile/o1_mf_users_9jhz6fw2_.dbf

5 Full 827904 2014-02-24 12:16:30 /oracle/oradata/XYSOUL/datafile/xysoul01.dbf

也可以使用一下命令新增備份集，追加歸檔等

RMAN> catalog backuppiece '/oracle/backup/arch_06p1e2kn_1_1.bak';

--恢復歸檔並指定目錄
RMAN> run{

2> set archivelog destination to '/oracle/archivelog';

3> restore archivelog from sequence 23;

4> }

恢復資料檔案，恢復前建立相關資料檔案存放目錄

RMAN> run{

3> set newname for datafile '/oracle/oradata/XYSOUL/datafile/o1_mf_system_9jhz3xx6_.dbf' to '+DATA/oradata/xysoul/system01.dbf';

4> set newname for datafile '/oracle/oradata/XYSOUL/datafile/o1_mf_sysaux_9jhz4vkj_.dbf' to '+DATA/oradata/xysoul/sysaux01.dbf';

5> set newname for datafile '/oracle/oradata/XYSOUL/datafile/o1_mf_undotbs1_9jhz5gsj_.dbf' to '+DATA/oradata/xysoul/undotbs1_01.dbf';

6> set newname for datafile '/oracle/oradata/XYSOUL/datafile/o1_mf_users_9jhz6fw2_.dbf' to '+DATA/oradata/xysoul/user01.dbf';

7> set newname for datafile '/oracle/oradata/XYSOUL/datafile/xysoul01.dbf' to '+DATA/oradata/xysoul/xysoul01.dbf';

8> restore database;

9> switch datafile all;

10> }

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 2014-02-24 16:53:13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=27 device type=DISK

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 +DATA/oradata/xysoul/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to +DATA/oradata/xysoul/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to +DATA/oradata/xysoul/undotbs1_01.dbf

channel ORA_DISK_1: restoring datafile 00004 to +DATA/oradata/xysoul/user01.dbf

channel ORA_DISK_1: restoring datafile 00005 to +DATA/oradata/xysoul/xysoul01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/backup/full_02p1e2cu_1_1.bak

channel ORA_DISK_1: piece handle=/oracle/backup/full_02p1e2cu_1_1.bak tag=TAG20140224T121629

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:45

Finished restore at 2014-02-24 16:55:59

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=840387360 file name=+DATA/oradata/xysoul/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=840387360 file name=+DATA/oradata/xysoul/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=840387360 file name=+DATA/oradata/xysoul/undotbs1_01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=840387360 file name=+DATA/oradata/xysoul/user01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=10 STAMP=840387360 file name=+DATA/oradata/xysoul/xysoul01.dbf

選擇恢復點，還原資料庫

RMAN> run{

2> set until scn 827904;

3> recover database;

4> }

executing command: SET until clause

Starting recover at 2014-02-24 16:59:27

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 2014-02-24 16:59:28

啟動資料庫，檢視日誌組

SQL> alter database open resetlogs;

Database altered.

SQL> select member from v$logfile;

MEMBER

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

+DATA/xysoul/onlinelog/group_1.290.840387629

+DATA/xysoul/onlinelog/group_2.304.840387667

+DATA/xysoul/onlinelog/group_3.306.840387705

+DATA/xysoul/onlinelog/group_1.289.840387645

+DATA/xysoul/onlinelog/group_2.305.840387687

+DATA/xysoul/onlinelog/group_3.307.840387723

6 rows selected.

新增日誌組，4、5、6

SQL> alter database add logfile thread 2 group 4 '+DATA/xysoul/onlinelog/group_4_01.log' size 200M;

Database altered.

SQL> alter database add logfile member '+DATA/xysoul/onlinelog/group_4_02.log' to group 4;

Database altered.

啟用thread 2，並修改例項對應thread

SQL> select THREAD#, STATUS, ENABLED from v$thread;

THREAD# STATUS ENABLED

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

1 OPEN PUBLIC

2 CLOSED DISABLED

SQL> alter database enable thread 2;

Database altered.

SQL> alter system set thread=1 scope=spfile sid='xysoul1';

System altered.

SQL> alter system set thread=2 scope=spfile sid='xysoul2';

System altered.

設定叢集相關引數

SQL> alter system set instance_number=1 scope=spfile sid='xysoul1';

alter system set instance_number=2 scope=spfile sid='xysoul2';

System altered.

SQL>

System altered.

SQL>

SQL> alter system set cluster_database_instances=2 scope=spfile;

System altered.

SQL> alter system set cluster_database=true scope=spfile;

System altered.

新增undo表空間

SQL> create undo tablespace UNDOTBS2 datafile '+DATA/oradata/xysoul/undotbs2.dbf' size 755M;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS2'scope=spfile sid='xysoul2';

System altered.

建立spfile檔案，並修改pfile檔案（兩節點都需修改）

SQL> create spfile='+DATA/xysoul/spfilexysoul.ora' from pfile;

File created.

[oracle@rac01 dbs]$ cat initxysoul1.ora

spfile='+DATA/xysoul/spfilexysoul.ora'

兩個節點嘗試啟動資料庫

SQL> startup nomount

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2254952 bytes

Variable Size 276826008 bytes

Database Buffers 251658240 bytes

Redo Buffers 3723264 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

下面將資料庫新增到叢集資源中（此處需注意，新增資料庫資源時，需用oracle使用者）

[oracle@rac02 dbs]$ srvctl add database -d xysoul -o /oracle/app/oracle/product/11.2.0 -p +DATA/xysoul/spfilexysoul.ora

[oracle@rac02 ~]$ srvctl add instance -d xysoul -i xysoul1 -n rac01

[oracle@rac02 ~]$ srvctl add instance -d xysoul -i xysoul2 -n rac02

啟動資料庫，並檢視（之前已經將兩個節點例項關閉）

[grid@rac02 ~]$ srvctl start database -d xysoul

[grid@rac02 ~]$ crs_stat -t

Name Type Target State Host

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

ora.CRS.dg ora....up.type ONLINE ONLINE rac01

ora.DATA.dg ora....up.type ONLINE ONLINE rac01

ora....ER.lsnr ora....er.type ONLINE ONLINE rac01

ora....N1.lsnr ora....er.type ONLINE ONLINE rac02

ora.asm ora.asm.type ONLINE ONLINE rac01

ora.cvu ora.cvu.type ONLINE ONLINE rac02

ora.gsd ora.gsd.type OFFLINE OFFLINE

ora....network ora....rk.type ONLINE ONLINE rac01

ora.oc4j ora.oc4j.type ONLINE ONLINE rac02

ora.ons ora.ons.type ONLINE ONLINE rac01

ora....SM1.asm application ONLINE ONLINE rac01

ora....01.lsnr application ONLINE ONLINE rac01

ora.rac01.gsd application OFFLINE OFFLINE

ora.rac01.ons application ONLINE ONLINE rac01

ora.rac01.vip ora....t1.type ONLINE ONLINE rac01

ora....SM2.asm application ONLINE ONLINE rac02

ora....02.lsnr application ONLINE ONLINE rac02

ora.rac02.gsd application OFFLINE OFFLINE

ora.rac02.ons application ONLINE ONLINE rac02

ora.rac02.vip ora....t1.type ONLINE ONLINE rac02

ora.racdb.db ora....se.type OFFLINE OFFLINE

ora....ry.acfs ora....fs.type ONLINE ONLINE rac01

ora.scan1.vip ora....ip.type ONLINE ONLINE rac02

ora.xysoul.db ora....se.type ONLINE ONLINE rac01

也可透過此命令檢視更詳細狀態
crsctl stat res –t

--重建臨時表空間,刪除原來檔案，新增新臨時資料檔案
alter database tempfile '' drop;

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 10G autoextend on;

至此資料庫恢復工作已接近完成，根據相關需求請配置監聽、最佳化系統和資料庫引數等。

恢復完成，一定要做好檢查工作，警告日誌、表空間資訊、spfile等資訊

三、總結

在此恢復過程中，也遇到過一些問題，比如，相關目錄沒有建全、新增叢集資源及相關命令不熟悉等導致的問題，在解決過程中我更多的是依賴官方文件，雖然官方也有錯誤吧，但比一些網路資料要好。雖然我的技術不怎麼好，但有一個原則，既然寫了，就寫的詳細點，至少不漏下關鍵步驟，分享技術、分享快樂，在路上。PS:如有遺漏，歡迎拍磚

【RMAN】oracle11g單機資料透過RMAN恢復至RAC

相關文章