環境：主備庫都為單例項並且資料庫SID相同

OS：red hat 6.5

Oracle:11.2.0.4.3

主庫操作

1. 開啟歸檔模式

建立歸檔目錄

[root@enn ~]# mkdir -p /u01/archivelog

[root@enn ~]# chown -R oracle:oinstall /u01/archivelog

[root@enn ~]# chmod 777 /u01/archivelog

資料庫到mount狀態開啟歸檔模式

[root@enn ~]# su - oracle

[oracle@enn ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 20 23:46:46 2014

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 541068368 bytes

Database Buffers 289406976 bytes

Redo Buffers 2371584 bytes

Database mounted.

設定主庫歸檔目錄

SQL> alter system set log_archive_dest='/u01/archivelog';

開啟歸檔模式

SQL> alter database archivelog;

Database altered.

檢視歸檔設定

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/archivelog

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

2. 啟動force_logging模式

檢視是否force_logging模式

SQL> select log_mode,force_logging from v$database;

LOG_MODE FOR

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

ARCHIVELOG NO

開啟force_logging模式

SQL> alter database force logging;

Database altered.

3.建立備庫日誌檔案路徑

檢視資料庫的日誌組個數與大小，因為我們建立 standby 日誌組的個數是原日誌

組個數+1 再與 thread 的積((2+1)*3)，size 不能小於原日誌檔案的大小。

SQL> select group#,thread#,bytes/1024/1024 M ,STATUS from v$log;

GROUP# THREAD# M STATUS

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

1 1 50 CURRENT

3 1 50 INACTIVE

2 1 50 INACTIVE

SQL> select member from v$logfile;

MEMBER

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

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

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

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

建立備庫日誌組路徑

SQL> alter database add standby logfile thread 1 group 4 ('/u01/app/oracle/oradata/ENN/redo04.log') size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5 ('/u01/app/oracle/oradata/ENN/redo05.log') size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6 ('/u01/app/oracle/oradata/ENN/redo06.log') size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/oradata/ENN/redo07.log') size 50M;

Database altered.

檢視狀態

SQL> select group#,status,type,member from v$logfile;

GROUP# STATUS TYPE MEMBER

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

3 ONLINE /u01/app/oracle/oradata/ENN/redo03.log

2 ONLINE /u01/app/oracle/oradata/ENN/redo02.log

1 ONLINE /u01/app/oracle/oradata/ENN/redo01.log

4 STANDBY /u01/app/oracle/oradata/ENN/redo04.log

5 STANDBY /u01/app/oracle/oradata/ENN/redo05.log

6 STANDBY /u01/app/oracle/oradata/ENN/redo06.log

7 STANDBY /u01/app/oracle/oradata/ENN/redo07.log

4.建立監聽

執行netca建立監聽器

[oracle@enn oracle]$ netca

修改tnsname檔案

[oracle@enn oracle]$ cd $ORACLE_HOME/network/admin

[oracle@enn admin]$ vim tnsname.ora

ENN =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = enn)

)

ENN_DG =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = enn_dg)

)

tnsname.ora複製到備庫中

[oracle@enn admin]$ scp tnsname.ora oracle@192.168.80.16:$ORACLE_HOME/network/admin/tnsname.ora

oracle@192.168.80.16's password:

tnsname.ora 100% 361 0.4KB/s 00:00

注：可以使用圖形配置tnsname

[oracle@enn admin]$ netmgr

附：如果備庫tnsping不通, 關閉防火牆

[oracle@enn_dg ~]$ tnsping enn

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-JUL-2014 09:26:09

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.15)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ENN)))

TNS-12543: TNS:destination host unreachable

[root@enn ~]# chkconfig iptables off

[root@enn ~]# service iptables stop

5. 設定主庫和備庫歸檔路徑

設定主庫歸檔路徑

SQL> alter system set log_archive_dest='';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ENN';

System altered.

設定備庫歸檔路徑

SQL> alter system set log_archive_dest_2='SERVICE=enn_dg async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ENN';

System altered.

配置歸檔最大程式數

SQL> show parameter log_archive_max

NAME TYPE VALUE

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

log_archive_max_processes integer 4

SQL> alter system set log_archive_max_processes=30;

System altered.

6. 配置引數檔案

生成引數檔案

SQL> create pfile ='/u01/app/oracle/initENN.ora' from spfile;

File created.

修改引數檔案

[oracle@enn oracle]$ vim initENN.ora

DB_UNIQUE_NAME=ENN

#LOG_ARCHIVE_CONFIG='DG_CONFIG=(ENN,ENN_DG)'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=ENN_DG

FAL_CLIENT=ENN

STANDBY_FILE_MANAGEMENT=AUTO

7. 配置密碼檔案

備份standby庫引數檔案和密碼檔案

[oracle@enn pfile]$ cd $ORACLE_HOME/dbs

[oracle@enn dbs]$ cp orapwENN orapwENN.back

[oracle@enn dbs]$ cp spfileENN.ora spfileENN.ora.bak

將引數檔案和密碼檔案傳到備庫

[oracle@enn oracle]$ scp initENN.ora oracle@192.168.80.16:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora

[oracle@enn oracle]$ scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN oracle@192.168.80.16:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENN

備庫操作

關閉防火牆

[root@enn_dg ~]# chkconfig iptables off

[root@enn_dg ~]# service iptables stop

圖形方式建立監聽器，同主庫操作相同

[oracle@enn ~]$ netca

注：可以使用圖形方式配置tnsname.ora

[oracle@enn ~]$ netmgr

啟動備庫到nomount

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora';

Duplicate複製主庫到備庫

用rman連線主庫和備庫

[oracle@enn ~]$ rman target sys/oracle@ENN auxiliary sys/oracle@ENN_DG

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 21 11:02:39 2014

connected to target database: ENN (DBID=4141660501)

connected to auxiliary database: ENN (not mounted)

執行同步複製備庫操作

RMAN> duplicate target database for standby from active database spfile set db_unique_name 'ENN';

出現錯誤：

sql statement: alter database mount standby database

RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT

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

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

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

RMAN-03002: failure of Duplicate Db command at 07/21/2014 11:15:58

RMAN-05501: aborting duplication of target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/users01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/undotbs01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/sysaux01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENN/system01.dbf conflicts with a file used by the target database

附：a、在異機克隆時，如果auxiliary DB使用了與target DB相同的磁碟配置，目錄結構以及檔名時，必須指定NOFILENAMECHECK。NOFILENAMECHECK可以阻止檢查target DB的資料檔案及聯機日誌檔案是否處於正常使用的狀態。而auxiliary DB與target DB的磁碟配置，目錄結構以及檔名任一不同時，應避免使用NOFILENAMECHECK。

b、對於沒有連線到target DB或catalog的情形，應使用BACKUP LOCATION ''指定備份檔案所在的位置。

繼續執行duplicate(注：Duplicate時如果主庫與備庫檔案目錄結構與檔名都相同時需要使用nofilenamecheck引數)

RMAN> duplicate target database for standby from active database spfile set db_unique_name 'ENN'

nofilenamecheck;

出現問題：

Starting Duplicate Db at 21-JUL-14

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

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

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

RMAN-03002: failure of Duplicate Db command at 07/21/2014 11:35:59

RMAN-05501: aborting duplication of target database

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

MosL(備庫必需用主庫傳過來的pfile啟動到nomount狀態，才能執行duplicate)

CAUSE:
A DUPLICATE was attempted when the auxiliary database was started with a server parameter file and the SPFILE sub-clause was specified in Duplicate syntax.
RMAN cannot restore the server parameter file if the auxiliary database is already started with a server parameter file.

SOLUTION:
Start the auxiliary database with a client parameter file(pfile) or Do not specify SPFILE sub-clause and retry.

啟動備庫到nomount是使用pfile

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initENN.ora';

重新同步複製

RMAN> duplicate target database for standby from active database spfile set db_unique_name 'ENN' nofilenamecheck;

報錯但正常完成複製L

ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENN/redo01.log'