用RMAN複製搭建物理 Data Gurad 環境

引用：http://blog.csdn.net/tianlesoftware/article/details/5756750

Data Guard 環境：

作業系統： redhat 4.7

Primary資料庫：

IP地址：10.85.10.1

資料庫SID：orcl

DB_UNIQUE_NAME：orcl_pd

Standby資料庫：

IP地址：10.85.10.2

資料庫SID：orcl

DB_UNIQUE_NAME：orcl_st

Oracle Data Guard 理論知識

http://blog.csdn.net/tianlesoftware/archive/2010/04/22/5514082.aspx

Oracle Data Gurad Physical Standby 相關說明

http://blog.csdn.net/tianlesoftware/archive/2010/05/04/5557410.aspx

Oracle Data Guard Linux 平臺 Physical Standby 搭建例項

http://blog.csdn.net/tianlesoftware/archive/2010/04/30/5547565.aspx

之前也做過相關實驗，今天這個測試主要是用RMAN 來複製備份，之前一直是直接copy 檔案的。實驗步驟都差不多。

一． Primary 端的配置

1. 主庫設定為force logging 模式

SQL> alter database force logging;

2. 主庫設為歸檔模式

SQL> archive log list;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> archive log list;

3. 建立備庫的口令檔案

[oracle@localhost dbs]$ orapwd file=/u02/orapworcl password=admin

如果已經存在，就不用建立了。預設情況下，win下口令檔案的格式是pwdsid.ora，unix下的格式是orapwSID(大小寫敏感）。 Linux預設位置為$ORACLE_HOME/dbs目錄下下，Windows 預設為$ORACLE_HOME/database目錄。

用SCP命令將這個口令檔案傳輸到備庫的對應位置：

[oracle@db1 dbs]$ scp orapworcl 10.85.10.2:/u01/app/oracle/product/10.2.0/db_1/dbs

The authenticity of host '10.85.10.2 (10.85.10.2)' can't be established.

RSA key fingerprint is 1a:20:7a:05:bd:e0:ac:04:21:02:b1:72:01:69:40:d6.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '10.85.10.2' (RSA) to the list of known hosts.

oracle@10.85.10.2's password:

orapworcl 100% 1536 1.5KB/s 00:00

注意，如果不用RMAN 複製的話，還需要建立備庫的控制檔案。 RMAN複製的話，自己會建立控制檔案。

4. 修改初始化引數檔案

Pfile 預設位置在$ORACLE_HOME/dbs目錄下，也可以自己指定位置：

SQL> create pfile from spfile;

在initorcl.ora 新增如下內容：

*.DB_UNIQUE_NAME='orcl_pd'

*.log_archive_dest_1='location=/u02/archivelog'

*.log_archive_dest_2='SERVICE=orcl_st'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.standby_file_management='AUTO'

*.standby_archive_dest='/u02/archivelog'

*.FAL_SERVER='orcl_st'

*.FAL_CLIENT='orcl_pd'

如果主庫和備庫的資料檔案位置不同，還需要加如下2個引數：

*.log_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'

*.db_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'

-- 注意：orcl_st,orcl_pd 是在tnsnames檔案中配置的

用剛修改的pfile 啟動資料庫，並生成spfile。

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile=?/dbs/initorcl.ora

ORACLE instance started.

Total System Global Area 247463936 bytes

Fixed Size 1218748 bytes

Variable Size 75499332 bytes

Database Buffers 163577856 bytes

Redo Buffers 7168000 bytes

Database mounted.

Database opened.

SQL> !echo $ORACLE_HOME

/u01/app/oracle/product/10.2.0/db_1

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';

File created.

5. 修改listener.ora 和tnsnames.ora 檔案

Listener.ora 檔案：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

注意：SID_LIST_LISTENER 配置的是靜態註冊，如果沒有該引數，而且Data Guard 啟動順序又不正確，那麼在主庫可能會報 PING[ARC1]: Heartbeat failed to connect to standby 'orcl_st'. Error is 12514. 錯誤，導致歸檔無法完成。

Oracle Listener 動態註冊與靜態註冊

http://blog.csdn.net/tianlesoftware/archive/2010/04/30/5543166.aspx

Tnsnames.ora 檔案

ORCL_ST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.10.2)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

ORCL_PD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.10.1)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

二． Standby 端配置

1. 建立備庫存放資料檔案和後臺跟蹤目錄。

這個目錄可以和主庫相同，如果不同，就需要在主庫的初始化檔案中進行轉換。如：

*.log_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'

*.db_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'

$ORACLE_BASE/ORADATA/ORCL

$ORACLE_BASE/admin/orcl

$ORACLE_BASE/admin/orcl/adump

$ORACLE_BASE/admin/orcl/bdump

$ORACLE_BASE/admin/orcl/cdump

$ORACLE_BASE/admin/orcl/dpdump

$ORACLE_BASE/admin/orcl/pfile

$ORACLE_BASE/admin/orcl/udump

$ORACLE_BASE/admin/orcl/

2. 修改初始化引數檔案

從主庫copy過來，修改如下：

*.DB_UNIQUE_NAME='orcl_st'

*.log_archive_dest_1='location=/u01/archivelog'

*.log_archive_dest_2='SERVICE=orcl_pd'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.FAL_SERVER='orcl_pd'

*.FAL_CLIENT='orcl_st'

*.standby_file_management='AUTO'

*.standby_archive_dest='/u01/archivelog'

log_file_name_convert =('orcl','orcl') -- 該引數用於RMAN複製時重建redo 檔案。

4. 修改listener.ora 和 tnsnames.ora 檔案，同主庫。如果不存在，就從主庫上copy 過去。

5. RMAN 複製資料庫

5.1 備份主庫

[oracle@db1 backup]$ rman target sys/admin@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Jul 18 18:26:16 2010

connected to target database: ORCL (DBID=1248423599)

RMAN>RUN {

allocate channel c1 type disk;

allocate channel c2 type disk;

sql 'alter system archive log current';

backup current controlfile for standby format='/u02/backup/control_%U';

BACKUP FORMAT '/u02/backup/orcl_%U_%T' skip inaccessible filesperset 5 DATABASE ;

sql 'alter system archive log current';

BACKUP FORMAT '/u02/backup/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;

release channel c2;

release channel c1;

}

backup full database include current controlfile for standby plus archivelog FORMAT '/u02/backup/orcl_%U_%T' skip inaccessible filesperset 5 ;

備份指令碼，具體參考：

Linux 平臺下 RMAN 全備和增量備份 shell 指令碼

http://blog.csdn.net/tianlesoftware/archive/2010/07/16/5740630.aspx

5.2 用SCP將備份集複製到備庫相同的位置，或者直接用NFS 掛載到相同位置。這些位置寫入了控制檔案，所以位置必須相同。

Linux NFS 和 Samba 共享配置

http://blog.csdn.net/tianlesoftware/archive/2010/07/21/5752092.aspx

如果出現：ORA-27054 NFS file system where the file is created or resides is not mounted with correct options的錯誤，可能是NFS 掛載的引數使用不正確。

mount -t nfs 192.168.19.219:/u01/rman -o hard,rw,noac,rsize=32768,wsize=32768,suid,proto=tcp,vers=3 /u01/rman

如果用SCP複製，複製結束要後注意檔案使用者和許可權。

5.3 用pfile檔案將備庫啟動到nomout狀態

[oracle@localhost u01]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 21 09:35:07 2010

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/initorcl.ora

ORACLE instance started.

Total System Global Area 247463936 bytes

Fixed Size 1218772 bytes

Variable Size 79693612 bytes

Database Buffers 163577856 bytes

Redo Buffers 2973696 bytes

5.4 執行duplicate 複製standby庫

[oracle@db1 dbs]$ rman target / auxiliary sys/admin@orcl_st;

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 20 22:32:59 2010

connected to target database: ORCL (DBID=1248423599)

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby nofilenamecheck dorecover;

Starting Duplicate Db at 21-JUL-10

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=155 devtype=DISK

contents of Memory Script.:

{

set until scn 800873;

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

executing command: SET until clause

Starting restore at 21-JUL-10

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/control_6mljbc6r_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u02/backup/control_6mljbc6r_1_1 tag=TAG20100721T115609

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

output filename=/u01/app/oracle/oradata/orcl/control01.ctl

output filename=/u01/app/oracle/oradata/orcl/control02.ctl

output filename=/u01/app/oracle/oradata/orcl/control03.ctl

Finished restore at 21-JUL-10

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

contents of Memory Script.:

{

set until scn 800873;

set newname for tempfile 1 to "/u01/app/oracle/oradata/orcl/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to "/u01/app/oracle/oradata/orcl/system01.dbf";

set newname for datafile 2 to "/u01/app/oracle/oradata/orcl/undotbs01.dbf";

set newname for datafile 3 to "/u01/app/oracle/oradata/orcl/sysaux01.dbf";

set newname for datafile 4 to "/u01/app/oracle/oradata/orcl/users01.dbf";

set newname for datafile 5 to "/u01/app/oracle/oradata/orcl/example01.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control file

executing command: SET NEWNAME

Starting restore at 21-JUL-10

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=155 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore

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

restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf

restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf

restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/orcl_6oljbc7f_1_1_20100721

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u02/backup/orcl_6oljbc7f_1_1_20100721 tag=TAG20100721T115629

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

channel ORA_AUX_DISK_1: starting datafile backupset restore

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

restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/orcl_6nljbc7e_1_1_20100721

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u02/backup/orcl_6nljbc7e_1_1_20100721 tag=TAG20100721T115629

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:06

Finished restore at 21-JUL-10

contents of Memory Script.:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=10 stamp=724981117 filename=/u01/app/oracle/oradata/orcl/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=11 stamp=724981117 filename=/u01/app/oracle/oradata/orcl/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=12 stamp=724981117 filename=/u01/app/oracle/oradata/orcl/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=13 stamp=724981118 filename=/u01/app/oracle/oradata/orcl/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=14 stamp=724981118 filename=/u01/app/oracle/oradata/orcl/example01.dbf

contents of Memory Script.:

{

set until scn 800873;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 21-JUL-10

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=74

channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/arch_6sljbcd4_1_1_20100721

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u02/backup/arch_6sljbcd4_1_1_20100721 tag=TAG20100721T115930

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

archive log filename=/u01/archivelog/1_74_720642866.dbf thread=1 sequence=74

channel clone_default: deleting archive log(s)

archive log filename=/u01/archivelog/1_74_720642866.dbf recid=1 stamp=724981127

channel ORA_AUX_DISK_1: starting archive log restore to default destination

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=75

channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/arch_6vljbcd7_1_1_20100721

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u02/backup/arch_6vljbcd7_1_1_20100721 tag=TAG20100721T115930

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

archive log filename=/u01/archivelog/1_75_720642866.dbf thread=1 sequence=75

channel clone_default: deleting archive log(s)

archive log filename=/u01/archivelog/1_75_720642866.dbf recid=2 stamp=724981133

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

Finished recover at 21-JUL-10

Finished Duplicate Db at 21-JUL-10

RMAN>

6. 在備庫新增redo log file

如果主庫沒有新增redo log file，可以先用copy 過來的初始化檔案將資料庫啟動到mount 狀態。在建立個spfile，最後新增redo log。

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';

新增一個新的Standby Redologs組（注意組號不要與當前存在的Online Redologs組重複），併為該組指定一個成員：

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;

提示，由於從Primary資料庫複製檔案時並沒有複製Online Redologs，因此物理Standby資料庫在第一次啟動REDO應用時，會在Alert檔案中報Online Redo Logfile檔案不存在，沒有關係，物理Standby會自動重建這批檔案，同時你也不用擔心會丟失資料，Online Redologs中的資料會以歸檔檔案的形式從Primary端接收。

至此，Data Guard 的操作已經完成，下面來開始驗證。

注意Data Guard 啟動順序：

啟動順序：先standby ,後primary;

閉順序：先primary 後standby;

在備庫將例項啟動到mount 狀態：

SQL> startup nomount;

SQL>alter database mount standby database ;

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL>alter database recover managed standby database disconnect from session;

在備庫啟動監聽：

$lsnrctl start

在主庫啟動例項：

SQL> startup;

在主庫啟動監聽：

$lsnrctl start

在主庫驗證歸檔目錄是否有效：

SQL> SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;

如果有錯誤，要排查原因。

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

70

主備查詢結果一致，Data Guard 搭建結束。

注意：如果在主庫執行 alter database clear unarchived logfile或alter database open resetlogs ,　則dataguard要重建。

其他如DG 模式切換，主備庫切換等操作，參考如下blog：

Oracle Data Guard Linux 平臺 Physical Standby 搭建例項

http://blog.csdn.net/tianlesoftware/archive/2010/04/30/5547565.aspx

用RMAN複製 搭建 物理 Data Gurad 環境