Data Guard 之RMAN備份搭建物理standby

一、大致步驟

通過RMAN備份建立standby資料庫大致分為這幾個步驟：

1）設定主要資料庫為force loggin模式

2）設定主要資料庫為archived log模式

3）配置好主備庫的參數檔案

4）兩邊的密碼檔案保持一致

5）配置監聽

6）用參數檔案啟動standby到nomount狀態

7）通過rman對主庫做完全備份

8）通過rman為standby建立控制檔案

9）通過rman dumplicate命令建立備庫

10）建立完成後，自動將standby 資料庫啟動到mount狀態，並且不會自動啟動redo apply

二、實施建立操作

環境：primary資料庫名DGWH，standby資料庫名DGBJ，db_unique_name=DGWH

在我總結的10步中，前5步驟和使用常規的方式一樣，參見我的blog：

http://5073392.blog.51cto.com/5063392/1299100

理論知識：

http://5073392.blog.51cto.com/5063392/1297346

有詳細配置步驟，這裡省略，從第6步開始。

注意這裡我是直接建立好了spfile檔案，當修改好了參數檔案後可以用create spfile from pfile建立

1）在備庫上使用spfile啟動到nomount狀態。

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 318046208 bytes

Fixed Size 1299652 bytes

Variable Size 297798460 bytes

Database Buffers 12582912 bytes

Redo Buffers 6365184 bytes

SQL>

2）將主庫啟動到mount狀態下，然後用rman串連備份資料庫

RMAN> backup database include current controlfile for standby plus archivelog; //注意 備份資料庫的同時建立了standby控制檔案，並且包含了歸檔日誌。 也可以分開來做例如 backup database ；copy current controlfile for standby to '/tmp/st.ctl'

Starting backup at 22-SEP-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=154 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=463 RECID=917 STAMP=826842432

channel ORA_DISK_1: starting piece 1 at 22-SEP-13

channel ORA_DISK_1: finished piece 1 at 22-SEP-13

piece handle=/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_annnn_TAG20130922T224340_93y0k0wl_.bkp tag=TAG20130922T224340 comment=NONE

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

Finished backup at 22-SEP-13

Starting backup at 22-SEP-13

using channel ORA_DISK_1

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/DGWH/datafile/o1_mf_system_9361jnkf_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/DGWH/datafile/o1_mf_sysaux_9361jnkx_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/DGWH/datafile/o1_mf_undotbs1_9361jnol_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/DGWH/datafile/o1_mf_users_9361jnpq_.dbf

channel ORA_DISK_1: starting piece 1 at 22-SEP-13

channel ORA_DISK_1: finished piece 1 at 22-SEP-13

piece handle=/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_nnndf_TAG20130922T224346_93y0k69f_.bkp tag=TAG20130922T224346 comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including standby control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 22-SEP-13

channel ORA_DISK_1: finished piece 1 at 22-SEP-13

piece handle=/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_ncsnf_TAG20130922T224346_93y0n4ss_.bkp tag=TAG20130922T224346 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 //piece handle產生了3個備份片到/u01/app/oracle/flash_recovery_area/DGWH/backupset

Finished backup at 22-SEP-13

Starting backup at 22-SEP-13

using channel ORA_DISK_1

specification does not match any archived log in the recovery catalog

backup cancelled because all files were skipped

Finished backup at 22-SEP-13

3） 複本備份集到備庫

注意：我在這耽誤了幾個小時，總是報下列的錯誤，我理解為使用rman搭建不需要像常規方式那樣copy資料檔案，所以就沒有手工複本備份集到備庫，我在網上看了很多知名人寫的blog都沒提到這步，當出現下面錯誤的時候，我檢查目錄檔案缺少存在，許可權也沒什麼問題，開始以為備份的命令有問題，各種方法試了很多次還是不行，我就嘗試是否要要將備份組copy到與主庫備份組相同的目錄下，終於成功了。。。。

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_ncsnf_TAG20130922T201417_93xqvgm9_.bkp

channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece /u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_ncsnf_TAG20130922T201417_93xqvgm9_.bkp

ORA-19505: failed to identify file "/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_ncsnf_TAG20130922T201417_93xqvgm9_.bkp"

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

首先在備庫上使用oracle帳號建立/u01/app/oracle/flash_recovery_area/DGWH/backupset/目錄，如果使用root需要修改目錄許可權。

[oracle@localhost flash_recovery_area] mkdir -p DGWH/backupset

在主庫執行scp命令copy所有的備份片到備庫，可以從第2步中得知產生了哪些備份片，也可以通過相關命令查看

[oracle@localhost] cd /u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22

[oracle@localhost 2013_09_22]$ scp * 192.168.31.3:/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22

oracle@192.168.31.3's password:

o1_mf_annnn_TAG20130922T224340_93y0k0wl_.bkp 100% 480KB 479.5KB/s 00:00

o1_mf_ncsnf_TAG20130922T224346_93y0n4ss_.bkp 100% 13MB 12.6MB/s 00:00

o1_mf_nnndf_TAG20130922T224346_93y0k69f_.bkp 100% 1090MB 2.6MB/s 06:53

4）使用rman串連到主庫和備庫

[oracle@localhost admin]$ rman target / auxiliary sys/oracle@DGBJ//同時串連到主備庫

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Sep 22 19:41:53 2013

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

connected to target database: DGWH (DBID=773380365, not open) //顯示出主庫狀態是not open，即mount

connected to auxiliary database: DGWH (not mounted)//顯示出備庫狀態是為not mounted，即nomout

RMAN>

如果監聽配置有問題會返回下列錯誤。

connected to target database: DGWH (DBID=773380365, not open)

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

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

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

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

RMAN> duplicate target database for standby;//使用duplicate命令還原

Starting Duplicate Db at 22-SEP-13

using channel ORA_AUX_DISK_1

contents of Memory Script:

{

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 22-SEP-13

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_ncsnf_TAG20130922T224346_93y0n4ss_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_ncsnf_TAG20130922T224346_93y0n4ss_.bkp tag=TAG20130922T224346

channel ORA_AUX_DISK_1: restored backup piece 1

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

output file name=/u01/app/oracle/oradata/DGBJ/controlfile/DGBJ01.ctl //在備庫中指定備份 控制檔案的路徑，duplicate時會自動copy 2份到對應的路徑下，因此必須要啟動到nomount狀態，讀取到備庫的參數檔案

output file name=/u01/app/oracle/flash_recovery_area/DGBJ/controlfile/DGBJ02.ctl

Finished restore at 22-SEP-13

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_temp_9361onbf_.tmp";

switch clone tempfile all;

set newname for datafile 1 to

"/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_system_9361jnkf_.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_sysaux_9361jnkx_.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_undotbs1_9361jnol_.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_users_9361jnpq_.dbf";

restore

clone database

;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DGBJ/datafile/o1_mf_temp_9361onbf_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 22-SEP-13

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

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

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/DGBJ/datafile/o1_mf_system_9361jnkf_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/DGBJ/datafile/o1_mf_sysaux_9361jnkx_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DGBJ/datafile/o1_mf_undotbs1_9361jnol_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/DGBJ/datafile/o1_mf_users_9361jnpq_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_nnndf_TAG20130922T224346_93y0k69f_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DGWH/backupset/2013_09_22/o1_mf_nnndf_TAG20130922T224346_93y0k69f_.bkp tag=TAG20130922T224346

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:11

Finished restore at 22-SEP-13

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=9 STAMP=826325100 file name=/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_system_93g65fcl_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=10 STAMP=826325100 file name=/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_sysaux_93g65fht_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=11 STAMP=826325100 file name=/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_undotbs1_93g65fo3_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=12 STAMP=826325100 file name=/u01/app/oracle/oradata/DGBJ/datafile/o1_mf_users_93g65fqj_.dbf

Finished Duplicate Db at 22-SEP-13

5）搭建完成驗證

主庫建立測試表：

SQL> create table test1(id int);

Table created.

SQL> insert into test1 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered

備庫驗證：

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby databaSe disconnect from session;//執行redo apply

Database altered.

SQL> select * from test1;

ID

----------

1

如果發現無法同步，請檢查密碼檔案是否一致。

本文出自 “一步一步” 部落格，轉載請與作者聯絡！