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

silencelion99發表於2015-06-04

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

一、概述

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

二、詳細步驟

 

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

[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

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

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.

 

新增日誌組,456

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:如有遺漏,歡迎拍磚

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30186176/viewspace-1686103/,如需轉載,請註明出處,否則將追究法律責任。

相關文章