【備份恢復】上:還原和恢復資料庫(即將一個庫還原到另外一個庫)

不一樣的天空w發表於2016-10-17

還原和恢復資料庫

本實驗是
將一個庫還原到另外一個庫。
說明:
源庫:
192.168.10.2   ORACLE_SID=ORA11GR2   DBID=237843809
目標庫:
192.168.10.3   ORACLE_SID= ORA11GR2   (保持與源庫一直的SID)

1.
準備工作

源庫 資料庫全備及 開啟 控制檔案 自動備份,當然也包括引數檔案

RMAN> backup as backupset database;

 

Starting backup at 02-OCT-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=40 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORA11GR2/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORA11GR2/users01.dbf

channel ORA_DISK_1: starting piece 1 at 02-OCT-16

channel ORA_DISK_1: finished piece 1 at 02-OCT-16

piece handle= /u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp tag=TAG20161002T195139 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

Finished backup at 02-OCT-16

 

Starting Control File and SPFILE Autobackup at 02-OCT-16

piece handle= /u01/app/FRA/ORA11GR2/autobackup/2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 02-OCT-16

2.複製
RMAN 備份檔案

在目標庫 建立相同目錄並將源庫 RMAN 備份檔案 複製到目標庫

[oracle@bing ~]$ mkdir -p /u01/app/FRA/ORA11GR2/ autobackup /2016_10_02/

[oracle@bing ~]$ mkdir -p /u01/app/FRA/ORA11GR2 /backupset /2016_10_02/

[oracle@bing ~]$

[oracle@bing 2016_10_02]$ pwd

/u01/app/FRA/ORA11GR2/ autobackup /2016_10_02

[oracle@bing 2016_10_02]$

[oracle@bing 2016_10_02]$ scp 192.168.10.2:/u01/app/FRA/ORA11GR2/ autobackup /2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp .

oracle@192.168.10.2's password:

o1_mf_s_924205924_cz1x757l_.bkp                                                                     100% 9600KB    9.4MB/s    00:01    

[oracle@bing 2016_10_02]$

[oracle@bing 2016_10_02]$ cd /u01/app/FRA/ORA11GR2/ backupset /2016_10_02/

[oracle@bing 2016_10_02]$ pwd

/u01/app/FRA/ORA11GR2/ backupset /2016_10_02

[oracle@bing 2016_10_02]$

[oracle@bing 2016_10_02]$

[oracle@bing 2016_10_02]$ scp 192.168.10.2:/u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp .

oracle@192.168.10.2's password:

o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp                                                        100% 1160MB   36.3MB/s    00:32    

[oracle@bing 2016_10_02]$

3.
配置目標庫

目標庫設定環境變數,啟動 rman nomount 狀態, 設定 dbid (即源庫 dbid

[oracle@bing 2016_10_02]$ echo $ORACLE_SID

PROD

[oracle@bing 2016_10_02]$ export ORACLE_SID=ORA11GR2

此步驟可以取與源庫不同的 ORACLE_SID ,也可以相同,本例子修改相同)

[oracle@bing 2016_10_02]$ echo $ORACLE_SID

ORA11GR2

[oracle@bing 2016_10_02]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 2 20:09:40 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.   All rights reserved.

 

connected to target database (not started)

 

RMAN> set dbid 237843809

 

executing command: SET DBID

4.
目標庫啟動到 nomount 模式

此時會報錯,原因是,目標庫沒有引數檔案,但也是能啟動到 nomount 模式,分配記憶體、啟動後臺程式

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/dbhome_1/dbs/initORA11GR2.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


5.
恢復引數檔案

1). 恢復的 pfile 檔案

RMAN> restore spfile to pfile '?/dbs/initORA11GR2.ora' from '/u01/app/FRA/ORA11GR2/ autobackup /2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp';

 

Starting restore at 02-OCT-16

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 /u01/app/FRA/ORA11GR2/autobackup/2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 02-OCT-16

 

RMAN>

2). 檢視恢復的 pfile 檔案

[oracle@bing ~]$ ls $ORACLE_HOME/dbs/initORA11GR2*

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORA11GR2.ora

[oracle@bing ~]$

6.
編輯 pfile 引數檔案

1). 檢視 pfile 引數檔案:

[oracle@bing dbs]$ cat initORA11GR2.ora

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

ORA11GR2.__db_cache_size=381681664

ORA11GR2.__java_pool_size=4194304

ORA11GR2.__large_pool_size=8388608

ORA11GR2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

ORA11GR2.__pga_aggregate_target=289406976

ORA11GR2.__sga_target=545259520

ORA11GR2.__shared_io_pool_size=0

ORA11GR2.__shared_pool_size=138412032

ORA11GR2.__streams_pool_size=4194304

*.audit_file_dest='/u01/app/oracle/admin/ORA11GR2/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/ORA11GR2/control01.ctl','/u01/app/oracle/oradata/ORA11GR2/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ORA11GR2'

*.db_recovery_file_dest_size=3221225472

*.db_recovery_file_dest='/u01/app/FRA'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11GR2XDB)'

*.memory_target=833617920

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

[oracle@bing dbs]$

——————————————————————————————————————————

因為我之前取了與源庫相同的例項名 ORACLE_SID, 所以不需要修改 pfile 引數檔案;需要注意一點,即使改了 ORACLE_SID ,在修改 pfile 引數檔案時唯一不能改的是 db_name 的值!!!!!!

 

2). 根據 pfile 檔案建立相應的目錄

[oracle@bing dbs]$ mkdir -p /u01/app/oracle/admin/ORA11GR2/adump

[oracle@bing dbs]$ mkdir -p /u01/app/oracle/oradata/ORA11GR2/

[oracle@bing dbs]$ mkdir -p /u01/app/FRA

3). 透過 pfile 啟動資料庫到 nomount 模式, 測試 pfile 是否有修改引數

[oracle@bing dbs]$ echo $ORACLE_SID

ORA11GR2

[oracle@bing dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 2 20:43:06 2016

 

Copyright (c) 1982, 2013, Oracle.   All rights reserved.

 

Connected to an idle instance.

 

SYS@ORA11GR2>startup nomount;

ORACLE instance started.

 

Total System Global Area   830930944 bytes

Fixed Size                   2257800 bytes

Variable Size              503319672 bytes

Database Buffers           322961408 bytes

Redo Buffers                  2392064 bytes

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter pfile

 

NAME                                  TYPE         VALUE

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

spfile                                string


7.生成
spfile

SYS@ORA11GR2> create spfile from pfile;

 

File created.

 

SYS@ORA11GR2>shutdown immediate;

ORA-01507: database not mounted

 

ORACLE instance shut down.

 

——以 spfile 引數檔案開啟例項 ORA11GR1(ORACLE_SID)

SYS@ORA11GR2> startup nomount;

ORACLE instance started.

 

Total System Global Area   830930944 bytes

Fixed Size                   2257800 bytes

Variable Size              503319672 bytes

Database Buffers           322961408 bytes

Redo Buffers                 2392064 bytes

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter spfile

 

NAME                                   TYPE         VALUE

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

spfile                                 string    /u01/app/oracle/product/11.2.0

                                                /dbhome_1/dbs/spfileORA11GR2.ora                                          



8.還原控制檔案

還原 控制檔案 並啟動到 mount 模式

[oracle@bing adump]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 2 20:49:58 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.   All rights reserved.

 

connected to target database: ORA11GR2 (not mounted)

 

RMAN> restore controlfile from '/u01/app/FRA/ORA11GR2 /autobackup/ 2016_10_02/o1_mf_s_924205924_cz1x757l_.bkp';

 

Starting restore at 02-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 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/ORA11GR2/control01.ctl

output file name=/u01/app/oracle/oradata/ORA11GR2/control02.ctl

Finished restore at 02-OCT-16

 

——連線資料庫( mount 狀態)

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

9. 還原資料檔案set newname操作

(即將資料檔案轉換路徑到 /u01/app/oracle/oradata/ORA11GR2/ 下)

RMAN> run{ set newname for datafile 1 to '/u01/app/oracle/oradata/ORA11GR2/system01.dbf';         

2> set newname for datafile 2 to '/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf';              

3> set newname for datafile 3 to '/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf';             

4> set newname for datafile 4 to '/u01/app/oracle/oradata/ORA11GR2/users01.dbf';               

5> set newname for datafile 5 to '/u01/app/oracle/oradata/ORA11GR2/example01.dbf';             

6> restore database;                                                                            

7> switch datafile all;                                                                         

8> recover database; }                                                                         

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 02-OCT-16

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 /u01/app/oracle/oradata/ORA11GR2/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORA11GR2/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORA11GR2/example01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp

channel ORA_DISK_1: piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_02/o1_mf_nnndf_TAG20161002T195139_cz1x6crk_.bkp tag=TAG20161002T195139

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 02-OCT-16

 

Starting recover at 02-OCT-16

using channel ORA_DISK_1

 

starting media recovery

 

unable to find archived log

archived log thread=1 sequence=14

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 10/02/2016 21:16:09

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 14 and s tarting SCN of 1199408

 

-- 此處我們會發現,在 recover 的時候,由於沒有歸檔日誌,所以,提示只能恢復到 SCN 1199408

解決:

RMAN>      run{                                                                           

2>     set until scn 1199408;                                                          

3>     restore database;                                                               

4>     switch datafile all;                                                           

5>     recover database;                                                              

6>     }                                                                               

 

executing command: SET until clause

 

Starting restore at 02-OCT-16

using channel ORA_DISK_1

 

skipping datafile 1; already restored to file /u01/app/oracle/oradata/ORA11GR2/system01.dbf

skipping datafile 2; already restored to file /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

skipping datafile 3; already restored to file /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

skipping datafile 4; already restored to file /u01/app/oracle/oradata/ORA11GR2/users01.dbf

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA11GR2/example01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 02-OCT-16

 

 

Starting recover at 02-OCT-16

using channel ORA_DISK_1

 

starting media recovery

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

10. resetlogs 方式開啟資料庫

RMAN> alter database open resetlogs;

 

database opened

 

——驗證:

SYS@ORA11GR2>select instance_name,status from v$instance;

 

INSTANCE_NAME     STATUS

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

ORA11GR2          OPEN

11. 收尾工作

此時已經 open 了資料庫, 再驗證 臨時表空間資料檔案 及日誌檔案

SYS@ORA11GR2> select file_name from dba_temp_files;

FILE_NAME

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

/u01/app/oracle/oradata/ORA11GR2/temp01.dbf

 

SYS@ORA11GR2> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/ORA11GR2/redo03.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

SYS@ORA11GR2> show parameter name

NAME                                  TYPE         VALUE

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

cell_offloadgroup_name                string

db_file_name_convert                  string

db_name                               string       ORA11GR2

db_unique_name                        string       ORA11GR2

global_names                          boolean      FALSE

instance_name                         string       ORA11GR2

lock_name_space                       string

log_file_name_convert                 string

processor_group_name                  string

service_names                         string       ORA11GR2

SYS@ORA11GR2>

注意:當改了例項名後,則恢復過程有一些不同,最後收尾也不同,需注意!!!!!!!!!!!!!!!!!!


注意:在open resetlogs前都需要檢查檔案位置是否正確,尤其是redo檔案的位置

 alter database rename file '/u01/oradata/orcl/temp01.dbf' to '/u01/oradata/testdb/temp01.dbf';

 alter database rename file '/u01/oradata/orcl/redo03.log' to '/u01/oradata/testdb/redo03.log';

 alter database rename file '/u01/oradata/orcl/redo02.log' to '/u01/oradata/testdb/redo02.log';

 alter database rename file '/u01/oradata/orcl/redo01.log' to '/u01/oradata/testdb/redo01.log';


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

相關文章