通過 rman duplicate 配置Oracle 11g Active Dataguard

xulongxc發表於2014-12-24

本文介紹了11g active dataguard的詳細配置步驟和資料保護模式的修改！

一：環境介紹
主庫
IP地址:192.168.1.61/24
作業系統版本：rhel5.4 64bit
資料庫版本：11.2.0.3 64bit
資料庫sid名：dg
資料庫名：dg
資料庫db_unique_name:dg1

備庫1 物理備庫（只安裝oracle資料庫軟體，無需建庫）
IP地址:192.168.1.62/24
作業系統版本：rhel5.4 64bit
資料庫版本：11.2.0.3 64bit
資料庫sid名：dg
資料庫名：dg
資料庫db_unique_name:dg2

二：修改主備庫listener.ora，tnsnames.ora檔案如下，備庫根據自身情況修改


	
	
		[oracle@dg1 ~]$ cat $TNS_ADMIN/listener.ora  
	

	
		SID_LIST_LISTENER =  
	

	
		  (SID_LIST =  
	

	
		    (SID_DESC =  
	

	
		      (GLOBAL_DBNAME = dg1.yang.com)  
	

	
		      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db1)  
	

	
		      (SID_NAME = dg)  
	

	
		    )  
	

	
		  )  
	

	
		 
	

	
		LISTENER =  
	

	
		  (DESCRIPTION_LIST =  
	

	
		    (DESCRIPTION =  
	

	
		      (ADDRESS_LIST =  
	

	
		        (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.yang.com)(PORT = 1521))  
	

	
		      )  
	

	
		    )  
	

	
		  )  
	

	
		 
	

	
		[oracle@dg1 ~]$ cat $TNS_ADMIN/tnsnames.ora  
	

	
		dg1 =  
	

	
		  (DESCRIPTION =  
	

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

	
		    (CONNECT_DATA =  
	

	
		      (SERVER = DEDICATED)  
	

	
		      (SERVICE_NAME = dg1.yang.com)  
	

	
		    )  
	

	
		  )  
	

	
		 
	

	
		dg2 =  
	

	
		  (DESCRIPTION =  
	

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

	
		    (CONNECT_DATA =  
	

	
		      (SERVER = DEDICATED)  
	

	
		      (SERVICE_NAME = dg2.yang.com)  
	

	
		    )  
	

	
		  )  
	

	
		 
	

	
		for_db =   
	

	
		 (DESCRIPTION =  
	

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

	
		  )

三：在主庫上修改dataguard配置相關的各個引數，各引數的具體含義可以參考oracle線上文件


	
	
		SQL> alter database force logging;  
	

	
		Database altered.  
	

	
		 
	

	
		SQL> alter system set db_unique_name='dg1' scope=spfile;  
	

	
		System altered.  
	

	
		 
	

	
		SQL> alter system set log_archive_config='DG_CONFIG=(dg1,dg2)';  
	

	
		System altered.  
	

	
		 
	

	
		SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog valid_for=  
	

	
		 
	

	
		(all_logfiles,primary_role) db_unique_name=dg1' scope=spfile;  
	

	
		System altered.  
	

	
		 
	

	
		SQL> alter system set log_archive_dest_2='SERVICE=dg2 lgwr sync valid_for=(online_logfile,primary_role)   
	

	
		 
	

	
		db_unique_name=dg2';  
	

	
		System altered.  
	

	
		 
	

	
		SQL> alter system set log_archive_dest_3='LOCATION=/u01/app/oracle/standbylog valid_for=  
	

	
		 
	

	
		(standby_logfile,standby_role) db_unique_name=dg1' scope=spfile;  
	

	
		System altered.  
	

	
		 
	

	
		SQL> alter system set fal_client='dg1';  
	

	
		System altered.  
	

	
		 
	

	
		SQL> alter system set fal_server='dg2';  
	

	
		System altered.  
	

	
		 
	

	
		SQL> alter system set standby_file_management=auto;  
	

	
		System altered.  
	

	
		 
	

	
		SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/dg/standby04.log' size 50M;  
	

	
		Database altered.  
	

	
		 
	

	
		SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/dg/standby05.log' size 50M;  
	

	
		Database altered.  
	

	
		 
	

	
		SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/dg/standby06.log' size 50M;  
	

	
		Database altered.  
	

	
		 
	

	
		SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/dg/standby07.log' size 50M;  
	

	
		Database altered.  
	

	
		 
	

	
		SQL> shutdown immediate  
	

	
		Database closed.  
	

	
		Database dismounted.  
	

	
		ORACLE instance shut down.  
	

	
		 
	

	
		SQL> startup  
	

	
		ORACLE instance started.  
	

	
		 
	

	
		Total System Global Area  417546240 bytes  
	

	
		Fixed Size                  2228944 bytes  
	

	
		Variable Size             285216048 bytes  
	

	
		Database Buffers          121634816 bytes  
	

	
		Redo Buffers                8466432 bytes  
	

	
		Database mounted.  
	

	
		Database opened.  
	

	
		 
	

	
		SQL> alter system set local_listener='for_db';  
	

	
		System altered.  
	

	
		 
	

	
		SQL> create pfile='/home/oracle/initdg.ora' from spfile;  
	

	
		File created.

三：將生成的pfile檔案修改後傳遞到備庫，注意紅色字型部分

[oracle@dg1 ~]$ cat /home/oracle/initdg.ora
dg.__db_cache_size=121634816
dg.__java_pool_size=4194304
dg.__large_pool_size=4194304
dg.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dg.__pga_aggregate_target=167772160
dg.__sga_target=251658240
dg.__shared_io_pool_size=0
dg.__shared_pool_size=109051904
dg.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dg/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/dg/control01.ctl','/u01/app/oracle/fast_recovery_area/dg/control02.ctl'
*.db_block_size=8192
*.db_domain='yang.com'
*.db_name='dg'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='dg2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgXDB)'
*.fal_client='dg2'
*.fal_server='dg1'
*.local_listener='for_db'
*.log_archive_config='DG_CONFIG=(dg1,dg2)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog valid_for=(all_logfiles,primary_role)db_unique_name=dg2'
*.log_archive_dest_2='SERVICE=dg1 lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=dg1'
*.log_archive_dest_3='LOCATION=/u01/app/oracle/standbylog valid_for=(standby_logfile,standby_role)db_unique_name=dg2'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@dg1 ~]$ scp initdg.ora 192.168.1.62:/home/oracle/

四：將備庫啟動到nomount狀態，然後連線主庫進行duplicate操作


	
	
		[oracle@dg2 ~]$ lsnrctl start  
	

	
		[oracle@dg2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwdg password=123456 entries=5 
	

	
		[oracle@dg2 ~]$ sqlplus /nolog  
	

	
		SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 22 13:36:53 2012  
	

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

	
		 
	

	
		SQL> conn /as sysdba  
	

	
		Connected to an idle instance.  
	

	
		SQL> create spfile from pfile='/home/oracle/initdg.ora';  
	

	
		File created.  
	

	
		 
	

	
		SQL> startup nomount  
	

	
		ORACLE instance started.  
	

	
		 
	

	
		Total System Global Area  417546240 bytes  
	

	
		Fixed Size                  2228944 bytes  
	

	
		Variable Size             285216048 bytes  
	

	
		Database Buffers          121634816 bytes  
	

	
		Redo Buffers                8466432 bytes

[oracle@dg2 ~]$ rman target sys/123456@dg1 auxiliary sys/123456@dg2
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Apr 22 13:38:33 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: DG (DBID=1694605607)
connected to auxiliary database: DG (not mounted)

RMAN> duplicate target database for standby nofilenamecheck from active database;

Starting Duplicate Db at 2012-04-22-13:39:25
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile '/u01/app/oracle/product/11.2.0/db1/dbs/orapwdg' auxiliary format
'/u01/app/oracle/product/11.2.0/db1/dbs/orapwdg'   ;
}
executing Memory Script

Starting backup at 2012-04-22-13:39:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Finished backup at 2012-04-22-13:39:28

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format

'/u01/app/oracle/oradata/dg/control01.ctl';
restore clone controlfile to '/u01/app/oracle/fast_recovery_area/dg/control02.ctl' from
'/u01/app/oracle/oradata/dg/control01.ctl';
}
executing Memory Script

Starting backup at 2012-04-22-13:39:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db1/dbs/snapcf_dg.f tag=TAG20120422T133929 RECID=1

STAMP=781277970
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2012-04-22-13:39:32

Starting restore at 2012-04-22-13:39:32
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2012-04-22-13:39:34

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile 1 to
"/u01/app/oracle/oradata/dg/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile 1 to
"/u01/app/oracle/oradata/dg/system01.dbf";
   set newname for datafile 2 to
"/u01/app/oracle/oradata/dg/sysaux01.dbf";
   set newname for datafile 3 to
"/u01/app/oracle/oradata/dg/undotbs01.dbf";
   set newname for datafile 4 to
"/u01/app/oracle/oradata/dg/users01.dbf";
   backup as copy reuse
   datafile 1 auxiliary format
"/u01/app/oracle/oradata/dg/system01.dbf"   datafile
2 auxiliary format
"/u01/app/oracle/oradata/dg/sysaux01.dbf"   datafile
3 auxiliary format
"/u01/app/oracle/oradata/dg/undotbs01.dbf"   datafile
4 auxiliary format
"/u01/app/oracle/oradata/dg/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/dg/temp01.dbf in control file

executing command: SET NEWNAME

Starting backup at 2012-04-22-13:39:42
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/dg/system01.dbf
output file name=/u01/app/oracle/oradata/dg/system01.dbf tag=TAG20120422T133943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/dg/sysaux01.dbf
output file name=/u01/app/oracle/oradata/dg/sysaux01.dbf tag=TAG20120422T133943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/dg/undotbs01.dbf
output file name=/u01/app/oracle/oradata/dg/undotbs01.dbf tag=TAG20120422T133943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/dg/users01.dbf
output file name=/u01/app/oracle/oradata/dg/users01.dbf tag=TAG20120422T133943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2012-04-22-13:45:05

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=781278308 file name=/u01/app/oracle/oradata/dg/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=781278308 file name=/u01/app/oracle/oradata/dg/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=781278308 file name=/u01/app/oracle/oradata/dg/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=781278308 file name=/u01/app/oracle/oradata/dg/users01.dbf
Finished Duplicate Db at 2012-04-22-13:45:29

RMAN> exit
Recovery Manager complete.

五：將備庫置於active dataguard模式下


	
	
		[oracle@dg2 ~]$ sqlplus /nolog  
	

	
		SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 22 13:47:17 2012  
	

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

	
		 
	

	
		SQL> conn /as sysdba  
	

	
		Connected.  
	

	
		SQL> select open_mode,database_role,db_unique_name from v$database;  
	

	
		 
	

	
		OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME  
	

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

	
		MOUNTED              PHYSICAL STANDBY dg2  
	

	
		 
	

	
		SQL> alter database open;  
	

	
		Database altered.  
	

	
		 
	

	
		SQL> alter database recover managed standby database using current logfile disconnect from session;  
	

	
		Database altered.  
	

	
		 
	

	
		SQL> select open_mode,database_role,db_unique_name from v$database;  
	

	
		 
	

	
		OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME  
	

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

	
		READ ONLY WITH APPLY PHYSICAL STANDBY dg2  
	

	
		 
	

	
		SQL> select status from v$standby_log;  
	

	
		 
	

	
		STATUS  
	

	
		----------  
	

	
		ACTIVE  
	

	
		UNASSIGNED  
	

	
		UNASSIGNED  
	

	
		UNASSIGNED  
	

	
		 
	

	
		SQL> select member from v$logfile;  
	

	
		 
	

	
		MEMBER  
	

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

	
		/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_3_7s76qbhq_.log  
	

	
		/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_2_7s76q94s_.log  
	

	
		/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_1_7s76q5w1_.log  
	

	
		/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_4_7s76qdpk_.log  
	

	
		/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_5_7s76qhmy_.log  
	

	
		/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_6_7s76qlhz_.log  
	

	
		/u01/app/oracle/fast_recovery_area/DG2/onlinelog/o1_mf_7_7s76qp99_.log  
	

	
		7 rows selected.

六：修改dataguard的資料保護模式為最高可用性模式，根據oracle文件的解釋，最高可用性資料保護模式需要先滿足以下幾個條件


	
	
		SQL> select db_unique_name,protection_mode,protection_level from v$database;  
	

	
		 
	

	
		DB_UNIQUE_NAME                 PROTECTION_MODE      PROTECTION_LEVEL  
	

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

	
		dg2                            MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  
	

	
		 
	

	
		 
	

	
		 
	

	
		SQL> select db_unique_name,protection_mode,protection_level from v$database;  
	

	
		 
	

	
		DB_UNIQUE_NAME                 PROTECTION_MODE      PROTECTION_LEVEL  
	

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

	
		dg1                            MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  
	

	
		 
	

	
		 
	

	
		SQL> alter database set standby database to maximize availability;  
	

	
		Database altered.  
	

	
		 
	

	
		SQL> select db_unique_name,protection_mode,protection_level from v$database;  
	

	
		 
	

	
		DB_UNIQUE_NAME                 PROTECTION_MODE      PROTECTION_LEVEL  
	

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

	
		dg1                            MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY  
	

	
		 
	

	
		 
	

	
		SQL> select db_unique_name,protection_mode,protection_level from v$database;  
	

	
		 
	

	
		DB_UNIQUE_NAME                 PROTECTION_MODE      PROTECTION_LEVEL  
	

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

	
		dg2                            MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY  
	

	
		 
	

	
		 
	

	
		將備庫shutdown後，主庫的PROTECTION_LEVEL將變為RESYNCHRONIZATION  
	

	
		SQL> select db_unique_name,protection_mode,protection_level from v$database;  
	

	
		 
	

	
		DB_UNIQUE_NAME                 PROTECTION_MODE      PROTECTION_LEVEL  
	

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

	
		dg2                            MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY  
	

	
		 
	

	
		SQL> alter database recover managed standby database cancel;  
	

	
		Database altered.  
	

	
		 
	

	
		SQL> shutdown immediate  
	

	
		Database closed.  
	

	
		Database dismounted.  
	

	
		ORACLE instance shut down.  
	

	
		 
	

	
		SQL> select db_unique_name,protection_mode,protection_level from v$database;  
	

	
		 
	

	
		DB_UNIQUE_NAME                 PROTECTION_MODE      PROTECTION_LEVEL  
	

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

	
		dg1                            MAXIMUM AVAILABILITY RESYNCHRONIZATION

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

Oracle 11g Rman Active database duplicate
2011-04-07
OracleDatabase
Oracle 11g RAC 配置單例項 DataGuard（通過 DUPLICATE 方式）
2016-05-26
Oracle單例
Oracle 11g Active Dataguard
2012-11-28
Oracle
使用oracle 11g rman新特性 duplicate target database for standby from active database 建立物理dataguard並開啟Real
2015-09-21
OracleDatabase
通過rman duplicate database！
2007-01-05
Database
Oracle11g RMAN Duplicate from Active Database
2009-01-06
OracleDatabase
通過RMAN Duplicate建立Oracle物理standby備庫
2012-08-06
Oracle
使用RMAN DUPLICATE...FROM ACTIVE DATABASE命令來建立DataGuard物理備庫
2012-12-23
Database
Oracle 11g Active Dataguard Switchover實驗
2014-11-29
Oracle
11g rman新特性 duplicate target database for standby from active database
2010-09-03
Database
11g RMAN新特性active database duplicate 資料庫異構
2008-08-01
Database資料庫
Oracle 11g Data Guard 使用duplicate from active database
2013-12-04
OracleDatabase
11g Active DataGuard初探
2015-06-01
RMAN DUPLICATE建立DataGuard物理備庫
2013-12-04
oracle 11G dataguard配置
2014-03-21
Oracle
11g新特性--active dataguard
2013-07-04
RMAN學習筆記_ Duplicate建立DataGuard
2010-02-03
筆記
RMAN學習筆記_ Duplicate重做DataGuard
2010-02-03
筆記
Oracle 11g Data Guard (physical standby - active dataguard) [final]
2010-09-02
Oracle
[zt] Oracle 11g DataGuard 配置
2010-09-01
Oracle
Oracle 11g Data Guard 使用 duplicate from active database 建立 standby database
2013-12-18
OracleDatabase
oracle 11g duplicate from active database 複製資料庫(四)
2015-06-05
OracleDatabase資料庫
oracle 11g duplicate from active database 複製資料庫(三)
2015-07-01
OracleDatabase資料庫
oracle 11g duplicate from active database 複製資料庫(二)
2015-07-01
OracleDatabase資料庫
oracle 11g duplicate from active database 複製資料庫(一)
2015-07-01
OracleDatabase資料庫
Oracle 11g Data Guard 使用duplicate from active database 建立 standby database
2013-04-10
OracleDatabase
Oracle 11g rac 的 active dataguard的啟動步驟
2017-04-05
Oracle
最簡單的11g Active DataGuard(ADG)搭建配置過程（專案步驟）
2015-07-23
RMAN duplicate from active database 複製資料庫
2014-10-28
Database資料庫
Oracle 11gR2 Active DataGuard配置Step By Step（一）
2015-06-15
Oracle
Oracle 11gR2 Active DataGuard配置Step By Step（二）
2015-06-15
Oracle
Oracle 11gR2 Active DataGuard配置Step By Step（三）
2015-06-15
Oracle
ORACLE 11g dataguard配置練習
2014-09-22
Oracle
【DataGuard】11g 新特性：Active Data Guard
2012-06-24
【DataGuard】Oracle 11g DataGuard 新特性之 Active Standby：Real-Time Apply+Query
2016-12-01
OracleAPP
【DataGuard】同一臺主機部署Oracle 11g物理Active Data Guard詳細過程
2012-03-15
Oracle
oracle 11g active dataguard switch over 對ogg的影響
2015-10-09
Oracle
Oracle 11gR2 使用 RMAN duplicate from active database 複製資料庫
2017-11-09
OracleDatabase資料庫

通過 rman duplicate 配置Oracle 11g Active Dataguard

相關文章