目标：实现dataguard redo传输cascade，即dg1的redo日志先传输到dg2，然后再从dg2传输到dg3,从而减少dg1主库的网络带宽压力！通常使用于dg1和dg2在同一局域网内，dg2和dg3通过广域网相连的场合！
一：环境介绍
主库
IP地址:192.168.1.61/24
操作系统版本：rhel5.4 64bit
数据库版本：11.2.0.3 64bit
数据库sid名：dg
数据库名：dg
数据库db_unique_name:dg1
备库1 物理备库
IP地址:192.168.1.62/24
操作系统版本：rhel5.4 64bit
数据库版本：11.2.0.3 64bit
数据库sid名：dg
数据库名：dg
数据库db_unique_name:dg2
备库2 物理备库
IP地址:192.168.1.62/24
操作系统版本：rhel5.4 64bit
数据库版本：11.2.0.3 64bit
数据库sid名：dg
数据库名：dg
数据库db_unique_name:dg3
二：统一修改三台数据库服务器的listener.ora和tnsnames.ora文件如下,listener.ora文件里面的GLOBAL_DBNAME和HOST参数做相应的修改[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 ~]$ grep -v '^#' $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)
)
)
dg3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.63)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg3.yang.com)
)
)
三：dg1和dg2数据库服务器构建active dataguard
3.1修改主库初始化参数,添加standby日志组
[oracle@dg1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 ProductiononSat Apr 7 14:11:18 2012
Copyright (c) 1982, 2011, Oracle.Allrights reserved.
SQL> conn /assysdba
Connected.
SQL> !mkdir /u01/app/oracle/archivelog
SQL>altersystemsetlog_archive_dest_1='LOCATION=/u01/app/oracle/archivelog'
valid_for=(all_logfiles,primary_role) db_unique_name=dg1;
System altered.
SQL> archive log list;
Databaselog mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog
Oldest online logsequence3
Nextlogsequencetoarchive 5
Currentlogsequence5
SQL>selectforce_logging,db_unique_namefromv$database;
FORDB_UNIQUE_NAME
--- ------------------------------
NOdg
SQL>alterdatabaseforcelogging;
Databasealtered.
SQL>altersystemsetdb_unique_name='dg1'scope=spfile;
System altered.
SQL>selectforce_logging,db_unique_namefromv$database;
FORDB_UNIQUE_NAME
--- ------------------------------
YES dg
SQL>altersystemsetlog_archive_config='DG_CONFIG=(dg1,dg2,dg3)';
System altered.
SQL>altersystemsetlog_archive_dest_2='SERVICE=dg2 valid_for=(online_logfile,primary_role) db_unique_name=dg2'scope=spfile;
System altered.
SQL>altersystemsetlog_archive_dest_3='SERVICE=dg3 valid_for=(standby_logfiles,standby_role) db_unique_name=dg3'scope=spfile;
System altered.
SQL> show parameter remote_login;
NAMETYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> show parameter log_archive_format;
NAMETYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL>alterdatabaseaddstandby logfilegroup4'/u01/app/oracle/oradata/dg/standby01.log'size50M;
Databasealtered.
SQL>alterdatabaseaddstandby logfilegroup5'/u01/app/oracle/oradata/dg/standby02.log'size50M;
Databasealtered.
SQL>alterdatabaseaddstandby logfilegroup6'/u01/app/oracle/oradata/dg/standby03.log'size50M;
Databasealtered.
SQL>alterdatabaseaddstandby logfilegroup7'/u01/app/oracle/oradata/dg/standby04.log'size50M;
Databasealtered.
SQL>selectmember,group#fromv$logfileorderby2;
MEMBERGROUP#
---------------------------------------- ----------
/u01/app/oracle/oradata/dg/redo01.log 1
/u01/app/oracle/oradata/dg/redo02.log 2
/u01/app/oracle/oradata/dg/redo03.log 3
/u01/app/oracle/oradata/dg/standby01.log 4
/u01/app/oracle/oradata/dg/standby02.log 5
/u01/app/oracle/oradata/dg/standby03.log 6
/u01/app/oracle/oradata/dg/standby04.log 7
7rowsselected.
一：环境介绍3.3.2 由spfile生成pfile,重启主库，使先前修改的参数生效!修改参数文件，scp至dg2
SQL>createpfile='/home/oracle/initdg.ora'fromspfile;
File created.
SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total SystemGlobalArea 417546240 bytes
FixedSize2228944 bytes
VariableSize281021744 bytes
DatabaseBuffers 125829120 bytes
Redo Buffers 8466432 bytes
Databasemounted.
Databaseopened.
SQL> show parametername;
NAMETYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string dg
db_unique_name string dg1
global_names booleanFALSE
instance_name string dg
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string dg1.yang.com
[oracle@dg1 ~]$ cat initdg.ora
dg.__db_cache_size=125829120
dg.__java_pool_size=4194304
dg.__large_pool_size=4194304
dg.__oracle_base='/u01/app/oracle'#ORACLE_BASEsetfromenvironment
dg.__pga_aggregate_target=167772160
dg.__sga_target=251658240
dg.__shared_io_pool_size=0
dg.__shared_pool_size=104857600
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)'
*.log_archive_config='DG_CONFIG=(dg1,dg2,dg3)'
*.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 valid_for=(online_logfiles,primary_role) db_unique_name=dg1'
*.log_archive_dest_3='SERVICE=dg3 valid_for=(standby_logfiles,standby_role) db_unique_name=dg3'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1
[oracle@dg1 ~]$ scp initdg.ora 192.168.1.62:/home/oracle/
3.3 在dg2上创建密码文件，由pfile生成spfile文件，启动数据库实例到nomount状态，通过rman进行duplicate操作，在11g中，duplicate数据库可以from active database，意味着不需要先在主库上进行rman备份，然后再将备份文件复制到备库上，在10g版本上需要这样操作，操作完成后将备库1置于active dataguard状态，即实时应用日志并只读的状态，adg也是11g dataguard非常大的一个飞跃(dg2只安装数据库软件，未建库)
[oracle@dg2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwdgpassword=123456 entries=5
[oracle@dg2 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 ProductiononSat Apr 7 15:40:33 2012
Copyright (c) 1982, 2011, Oracle.Allrights reserved.
SQL> conn /assysdba
Connectedtoan idle instance.
SQL>createspfilefrompfile='/home/oracle/initdg.ora';
File created.
SQL> startup nomount
ORACLE instance started.
Total SystemGlobalArea 417546240 bytes
FixedSize2228944 bytes
VariableSize281021744 bytes
DatabaseBuffers 125829120 bytes
Redo Buffers 8466432 bytes
[oracle@dg2 ~]$ rman target sys/123456@dg1 auxiliary sys/123456@dg2
Recovery Manager: Release 11.2.0.3.0 - ProductiononSat Apr 7 16:22:57 2012
Copyright (c) 1982, 2011, Oracleand/orits affiliates.Allrights reserved.
connectedtotargetdatabase: DG (DBID=1693312161)
connectedtoauxiliarydatabase: DG (notmounted)
RMAN> list backupofdatabasesummary; //这个时候主库没有任何的物理备份
using targetdatabasecontrol fileinsteadofrecovery catalog
specification doesnotmatchanybackupinthe repository
RMAN> duplicate targetdatabaseforstandby nofilenamecheckfromactivedatabase;
————————输出省略——————
[oracle@dg2 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 ProductiononSat Apr 7 16:30:11 2012
Copyright (c) 1982, 2011, Oracle.Allrights reserved.
SQL> conn /assysdba
Connected.
SQL>selectdatabase_role,open_mode,namefromv$database;
DATABASE_ROLE OPEN_MODENAME
---------------- -------------------- ---------
PHYSICAL STANDBY MOUNTED DG
SQL> archive log list;
Databaselog mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog
Oldest online logsequence8
Nextlogsequencetoarchive 0
Currentlogsequence10
SQL>alterdatabaseopenreadonly;
Databasealtered.
SQL>alterdatabaserecover managed standbydatabaseusingcurrentlogfile disconnectfromsession;
Databasealtered.
SQL>selectdatabase_role,open_mode,namefromv$database;
DATABASE_ROLE OPEN_MODENAME
---------------- -------------------- ---------
PHYSICAL STANDBYREADONLYWITHAPPLY DG
3.4测试主库上切换日志
SQL>altersystem switch logfile;
System altered.
SQL>selectmessagefromv$dataguard_status;
MESSAGE
--------------------------------------------------------------------------------
LNS: Completed archiving log 3 thread 1sequence9
ARC0: Beginningtoarchive thread 1sequence9 (1049579-1050098)
ARC0: Completed archiving thread 1sequence9 (1049579-1050098)
LNS: Standby redo logfile selectedforthread 1sequence10fordestination LOG_
ARCHIVE_DEST_2
LNS: Beginningtoarchive log 1 thread 1sequence10
LNS: Completed archiving log 1 thread 1sequence10
ARC2: Beginningtoarchive thread 1sequence10 (1050098-1050271)
ARC2: Completed archiving thread 1sequence10 (1050098-1050271)
LNS: Beginningtoarchive log 2 thread 1sequence11
备库上查看日志应用情况
SQL> archive log list;
Databaselog mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog
Oldest online logsequence9
Nextlogsequencetoarchive 0
Currentlogsequence11
3.5 实际操作应用情况，发现需要主库上切换日志后，数据才会同步，也就是standby日志组未使用
SQL>createtablet1asselect*fromdba_objects;
Tablecreated.
SQL>altersystem switch logfile;
System altered.
SQL> conn sys/123456@dg2assysdba
Connected.
SQL>selectcount(*)fromt1;
COUNT(*)
----------
74510
四：配置redo传输的cascade
4.1 将修改过的初始化文件传输到dg3上(dg3服务器上同样只安装数据库软件，不建库)[oracle@dg2 ~]$ cat initdg.ora
dg.__db_cache_size=125829120
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=104857600
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='dg3'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgXDB)'
*.log_archive_config='DG_CONFIG=(dg1,dg2,dg3)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog valid_for=(all_logfiles,primary_role) db_unique_name=dg3'
*.standby_archive_dest=/u01/app/oracle/standbylog
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=419430400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
4.2 通过pfile生成spfile,将数据库启动到nomount状态，然后进行duplicate
[oracle@dg3 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 ProductiononSat Apr 14 18:01:59 2012
Copyright (c) 1982, 2011, Oracle.Allrights reserved.
SQL> conn /assysdba
Connectedtoan idle instance.
SQL>createspfilefrompfile='/home/oracle/initdg.ora';
File created.
SQL> startup nomount
ORACLE instance started.
Total SystemGlobalArea 417546240 bytes
FixedSize2228944 bytes
VariableSize281021744 bytes
DatabaseBuffers 125829120 bytes
Redo Buffers 8466432 bytes
[oracle@dg3 ~]$ rman target sys/123456@dg1 auxiliary sys/123456@dg3
Recovery Manager: Release 11.2.0.3.0 - ProductiononSat Apr 14 18:02:59 2012
Copyright (c) 1982, 2011, Oracleand/orits affiliates.Allrights reserved.
connectedtotargetdatabase: DG (DBID=1693928392)
connectedtoauxiliarydatabase: DG (notmounted)
RMAN> duplicate targetdatabaseforstandby nofilenamecheckfromactivedatabase;
————————输出省略——————
4.3 将备库dg3置于只读且应用日志模式下
[oracle@dg3 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 ProductiononSat Apr 14 18:49:12 2012
Copyright (c) 1982, 2011, Oracle.Allrights reserved.
SQL> conn /assysdba
Connected.
SQL>alterdatabaseopenreadonly;
Databasealtered.
SQL>alterdatabaserecover managed standbydatabaseusingcurrentlogfile disconnectfromsession;
Databasealtered.
SQL>selectopen_mode,database_role,namefromv$database;
OPEN_MODE DATABASE_ROLENAME
-------------------- ---------------- ---------
READONLYWITHAPPLY PHYSICAL STANDBY DG
五：测试
SQL> conn /assysdba
Connected.
SQL>selectopen_mode,database_role,namefromv$database;
OPEN_MODE DATABASE_ROLENAME
-------------------- ---------------- ---------
READWRITEPRIMARYDG
SQL>truncatetablet1;
Tabletruncated.
SQL> conn sys/123456@dg2assysdba
Connected.
SQL>selectcount(*)fromt1;
COUNT(*)
----------
74510
SQL> conn sys/123456@dg3assysdba
Connected.
SQL>selectcount(*)fromt1;
COUNT(*)
----------
74510
SQL> conn /assysdba
Connected.
SQL>altersystem switch logfile; //主库上未切换日志前，数据不能同步
System altered.
SQL> conn sys/123456@dg2assysdba
Connected.
SQL>selectcount(*)fromt1;
COUNT(*)
----------
0
SQL> conn sys/123456@dg3assysdba //在dg3上可以看到明显的延迟情况
Connected.
SQL>selectcount(*)fromt1;
COUNT(*)
----------
74510
SQL>selectcount(*)fromt1;
COUNT(*)
----------
0
SQL>selectmessagefromv$dataguard_status; //查看备库dg3的dataguard状态
MESSAGE
--------------------------------------------------------------------------------
Media Recovery Log /u01/app/oracle/standbylog/1_13_780597393.dbf
Media Recovery Waitingforthread 1sequence14
FAL[client]: Error fetching gapsequence,noFAL server specified
RFS[1]: AssignedtoRFS process 2701
Media Recovery Log /u01/app/oracle/archivelog/1_14_780597393.dbf
Media Recovery Waitingforthread 1sequence15
FAL[client]: Error fetching gapsequence,noFAL server specified
18rowsselected.
SQL>altersystemsetfal_server='dg2';
System altered.
SQL>altersystemsetfal_client='dg3';
System altered.
总结：
1：在测试上，可以将主库的log_archive_dest_state_3参数设置为defer，然后切换日志，观察dg2，dg3的日志传输情况
2：在本文档中没有设置standby_file_management参数，因为不涉及新建数据文件的操作
3：在dg1和dg2上均未设置fal_server和fal_client参数，因为此处不考虑dg的角色切换问题，纯粹测试redo日志的cascade传输
4：本文档未能成功实现adg，猜想和几个归档日志路径配置的参数相关，后续将继续测试！
1304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
