【DG】Oracle11g异构平台之Linux到Windows DataGuard安装配置--duplicate
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 | select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from v$database; alter database force logging; alter database archivelog; select * from v$standby_log; select group#,bytes/1024/1024 ||'M' from v$log ; SELECT * FROM V$LOGFILE; alter database add standby logfile group 5 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo05A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo05B.log') size 100m; alter database add standby logfile group 6 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo06A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo06B.log') size 100m; alter database add standby logfile group 7 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo07A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo07B.log') size 100m; alter database add standby logfile group 8 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo08A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo08B.log') size 100m; alter database add standby logfile group 9 ( 'Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo09A.log','Z:\APP\ADMINISTRATOR\ORADATA\ORCL\standby_redo09B.log') size 100m; select * from v$standby_log; alter system set db_unique_name='orcl' scope=spfile; alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)'; alter system set log_archive_dest_1='LOCATION=Z:\archive db_unique_name=orcl valid_for=(ALL_LOGFILES,ALL_ROLES)'; alter system set log_archive_dest_2='SERVICE=orcldg LGWR ASYNC db_unique_name=orcldg valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'; alter system set log_archive_dest_state_1=ENABLE; alter system set log_archive_dest_state_2=ENABLE; alter system set log_archive_max_processes=4; alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile; alter system set db_file_name_convert='orcldg','orcl' scope=spfile; alter system set log_file_name_convert='orcldg','orcl' scope=spfile; alter system set standby_file_management='AUTO'; alter system set fal_server='orcldg'; alter system set fal_client='orcl'; 重启主库 ---------备库操作 oradim -new -sid orcldg 主备库配置监听和tnsnames.ora ---配置静态监听 -由于要求主库与备库sys使用相同的密码，在此处，我们直接复制了主库的密码文件到备库 echo db_name=orcl >$ORACLE_HOME/database/initorcldg.ora 而且db_name必须相同 创建相关路径 mkdir D:\app\Administrator\admin\orcldg\adump mkdir Z:\archive mkdir Z:\app\Administrator\oradata\orcldg\ set ORACLE_SID=orcldg services.msc 中 启动服务 sqlplus sys/wxqhaey as sysdba startup nomount rman target sys/wxqhaey@orcl auxiliary sys/wxqhaey@orcldg duplicate target database for standby nofilenamecheck from active database DORECOVER spfile set db_unique_name='orcldg' set log_archive_dest_1='LOCATION=Z:\archive\orcldg valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcldg' set log_archive_dest_2='service=orcl async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=orcl' set standby_file_management='AUTO' set fal_server='orcl' set fal_client='orcldg' set control_files='Z:\app\Administrator\oradata\orcldg\control01.ctl','Z:\app\Administrator\oradata\orcldg\control02.ctl' set db_file_name_convert='orcl','orcldg' set log_file_name_convert='orcl','orcldg' set memory_target='161061273600' set audit_file_dest='D:\app\Administrator\admin\orcldg\adump' ; -------校验 select open_mode from v$database; show parameter instance_name alter database open; select * from v$log; set line 9999 select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database; alter database recover managed standby database using current logfile disconnect; --主库切换日志 ALTER SYSTEM SWITCH LOGFILE; orcl.__db_cache_size=104152956928 orcl.__java_pool_size=1879048192 orcl.__large_pool_size=1342177280 orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=22548578304 orcl.__sga_target=138512695296 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=29796335616 orcl.__streams_pool_size=536870912 *.audit_file_dest='D:\app\Administrator\admin\orcl\adump' *.audit_trail='NONE' *.compatible='11.2.0.4.0' *.control_files='Z:\app\Administrator\oradata\orcl\control01.ctl','Z:\app\Administrator\oradata\orcl\control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.deferred_segment_creation=FALSE *.diagnostic_dest='D:\app\Administrator' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.job_queue_processes=1000 *.log_archive_dest_1='LOCATION=Z:\archive' *.log_archive_format='ARC%S_%R.%T' *.memory_target=161061273600 *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=300 *.pga_aggregate_target=21474836480 *.processes=2500 *.remote_login_passwordfile='EXCLUSIVE' *.sec_case_sensitive_logon=FALSE *.sessions=2755 *.sga_target=128849018880 *.undo_tablespace='UNDOTBS1' RUN { allocate channel c1 type disk; allocate channel c2 type disk; sql 'alter system archive log current'; backup current controlfile for standby format='Z:\backup\20150724\control_%U'; BACKUP FORMAT 'Z:\backup\20150724\oradg_%U_%T.bak' skip inaccessible filesperset 5 DATABASE ; sql 'alter system archive log current'; BACKUP FORMAT 'Z:\backup\20150724\arch_%U_%T.bak' skip inaccessible filesperset 5 ARCHIVELOG ALL; sql "alter database create standby controlfile as ''Z:\backup\20150724\control01.ctl'' reuse"; release channel c2; release channel c1; } sys/wxqhaey sqlplus sys/wxqhaey as sysdba orapwd file="D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ORA" password=wxqhaey force=y oradg11g--oradgphy---oradgss--oradgss oradg11g.__db_cache_size=125829120 oradg11g.__java_pool_size=4194304 oradg11g.__large_pool_size=4194304 oradg11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment oradg11g.__pga_aggregate_target=83886080 oradg11g.__sga_target=327155712 oradg11g.__shared_io_pool_size=0 oradg11g.__shared_pool_size=184549376 oradg11g.__streams_pool_size=0 audit_file_dest='/u01/app/oracle/admin/oradg11g/adump' audit_trail='db' compatible='11.2.0.0.0' control_files='/u01/app/oracle/oradata//oradg11g/control01.ctl','/u01/app/oracle/flash_recovery_area/oradg11g/control02.ctl' db_block_size=8192 db_domain='' rman target sys/lhr@tns_oradgphy auxiliary sys/lhr@tns_oradg11g duplicate target database for standby nofilenamecheck from active database DORECOVER spfile set db_unique_name='oradg11g' set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=oradg11g valid_for=(ALL_LOGFILES,ALL_ROLES)' set log_archive_dest_2='SERVICE=tns_oradgphy LGWR ASYNC db_unique_name=oradgphy valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' set log_archive_dest_3='SERVICE=tns_oradgss LGWR ASYNC db_unique_name=oradgss valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' set log_archive_dest_4='SERVICE=tns_oradgss LGWR ASYNC db_unique_name=oradgss valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)' set log_archive_dest_state_1='ENABLE' set log_archive_dest_state_2='ENABLE' set log_archive_dest_state_3='defer' set log_archive_dest_state_4='defer' set standby_file_management='AUTO' set fal_client='oradg11g' set fal_server='oradgphy' set control_files='/u01/app/oracle/oradata/oradg11g/control01.ctl','/u01/app/oracle/flash_recovery_area/oradg11g/control02.ctl' set db_file_name_convert='oradgphy','oradg11g' set log_file_name_convert='oradgphy','oradg11g' set memory_target='200M' set audit_file_dest='/u01/app/oracle/admin/oradg11g/adump' ; rman target sys/lhr@tns_oradg11g auxiliary sys/lhr@tns_oradglg duplicate target database for standby nofilenamecheck from active database DORECOVER spfile set db_unique_name='oradglg' set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=oradglg' set standby_file_management='AUTO' set fal_server='oradg11g' set fal_client='oradglg' set control_files='/u01/app/oracle/oradata/oradglg/control01.ctl','/u01/app/oracle/oradata/oradglg/control02.ctl' set db_file_name_convert='oradg11g','oradglg' set log_file_name_convert='oradg11g','oradglg' set memory_target='190M' ; rman target sys/lhr@tns_oradg11g auxiliary sys/lhr@tns_oradgss duplicate target database for standby nofilenamecheck from active database DORECOVER spfile set db_unique_name='oradgss' set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=oradgss' set standby_file_management='AUTO' set fal_server='oradg11g' set fal_client='oradgss' set control_files='/u01/app/oracle/oradata/oradgss/control01.ctl','/u01/app/oracle/oradata/oradgss/control02.ctl' set db_file_name_convert='oradg11g','oradgss' set log_file_name_convert='oradg11g','oradgss' set memory_target='190M' ; duplicate target database for standby nofilenamecheck from active database DORECOVER spfile set db_unique_name='orcldg' set log_archive_dest_1='LOCATION=Z:\archive\orcldg valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=orcldg' set log_archive_dest_2='service=orcl async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=orcl' set standby_file_management='AUTO' set fal_server='orcl' set fal_client='orcldg' set control_files='Z:\app\Administrator\oradata\orcldg\control01.ctl','Z:\app\Administrator\oradata\orcldg\control02.ctl' set db_file_name_convert='orcl','orcldg' set log_file_name_convert='orcl','orcldg' set memory_target='161061273600' set audit_file_dest='D:\app\Administrator\admin\orcldg\adump' ; alter system set db_unique_name='orcl' scope=spfile; alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)'; alter system set log_archive_dest_1='LOCATION=Z:\archive db_unique_name=orcl valid_for=(ALL_LOGFILES,ALL_ROLES)'; alter system set log_archive_dest_2='SERVICE=orcldg LGWR ASYNC db_unique_name=orcldg valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'; alter system set log_archive_dest_state_1=ENABLE; alter system set log_archive_dest_state_2=ENABLE; alter system set log_archive_max_processes=4; alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile; alter system set db_file_name_convert='orcldg','orcl' scope=spfile; alter system set log_file_name_convert='orcldg','orcl' scope=spfile; alter system set standby_file_management='AUTO'; alter system set fal_server='orcldg'; alter system set fal_client='orcl'; *.log_archive_config='DG_CONFIG=(oradg11g,oradgphy,oradgss,oradgss)' |
1 、环境介绍
1.1 、主库
Ø 操作系统：RedHat Enterprise Linux 5.4 64bit
Ø IP 地址： 172.168.10.231
Ø 数据库版本：oracle 11.2.0.3 64bit
Ø 数据库sid 名： demo1
Ø 数据库名：demo1
Ø 数据库db_unique_name demo231
Ø 数据文件路径：/oradata/demo1
Ø 归档路径：/oracle/arch
1.2 、备库
Ø 操作系统：windows server 2003 64bit
Ø IP 地址： 172.168.10.233
Ø 数据库版本：oracle 11.2.0.3
Ø 数据库sid 名： demo1
Ø 数据库名：demo1
Ø 数据库db_unique_name ： demo233
Ø 数据文件路径：c:\oradata\demo1
Ø 归档路径：c:\oracle\arch
2 、 DG 配置准备
2.1 、主库安装 oracle 11.2.0.3 数据库软件并创建数据库实例
Ø 安装oracle 数据库软件（略）
Ø 创建数据库实例（略）
Ø 创建监听（略）
2.2 、备库安装 oracle 11.2.0.3 数据库软件
Ø 安装oracle 数据库软件（略）
Ø 创建数据库实例：oradim -new -sid orcldg
Ø 创建监听（略）
3 、 DG 配置
3.1 、将主库设置为归档模式
Ø 略
3.2 、设置主库为强制归档模式
Ø SQL>alter database force logging;
3.3 、主库监听配置
Ø listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=demo1)
(ORACLE_HOME=/oracle/product/11.2.0/db_1)
(SID_NAME=demo1)
)
(SID_DESC=
(SID_NAME=PLSExtProc)
(ORACLE_HOME=/oracle/product/11.2.0/db_1)
(PROGRAM=extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg231)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Ø tnsnames.ora
DEMO231 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.10.231)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = demo1)
)
)
DEMO233 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.10.233)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = demo1)
)
)
3.4 、备库监听配置
Ø listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=demo1)
(ORACLE_HOME=C:\oracle\product\11.2.0.3/db_1)
(SID_NAME=demo1)
)
(SID_DESC=
(SID_NAME=PLSExtProc)
(ORACLE_HOME=C:\oracle\product\11.2.0.3/db_1)
(PROGRAM=extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ygdg)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Ø tnsnames.ora
DEMO231 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.10.231)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = demo1)
)
)
DEMO233 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ygdg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = demo1)
)
)
3.5 、主库初始化参数修改
Ø 生成pfile 文件（ create pfile from spfile ）
Ø 修改生成的pfile 文件，添加如下红色部分内容
demo1.__db_cache_size=205520896
demo1.__java_pool_size=4194304
demo1.__large_pool_size=4194304
demo1.__oracle_base='/oracle'#ORACLE_BASE set from environment
demo1.__pga_aggregate_target=218103808
demo1.__sga_target=327155712
demo1.__shared_io_pool_size=0
demo1.__shared_pool_size=104857600
demo1.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/demo1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/demo1/control01.ctl','/oradata/demo1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='demo1'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=demo1XDB)'
*.log_archive_dest_1='LOCATION=/oracle/arch'
*.log_archiveformat='%t%s_%r.dbf'
*.memory_target=545259520
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=demo231
*.log_archive_config='dg_config=(demo231,demo233)'
*.log_archive_dest_1='location=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=demo231'
*.log_archive_dest_2='SERVICE=demo233 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=demo233'
*.fal_server=demo233
*.fal_client=demo231
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_max_processes=10
*.standby_file_management=AUTO
*.db_file_name_convert='C:\oradata\demo1','/oradata/demo1'
*.log_file_name_convert='C:\oradata\demo1','/oradata/demo1'
Ø 由pfile 文件生成 spfile
SQL>shutdown immediate
SQL>create spfile from pfile;
SQL>startup
3.6 、备库初始化参数修改
Ø 将主库生成的pfile 文件传到备库，修改如下红色部分内容
demo1.__db_cache_size=205520896
demo1.__java_pool_size=4194304
demo1.__large_pool_size=4194304
demo1.__oracle_base='C:\oracle'#ORACLE_BASE set from environment
demo1.__pga_aggregate_target=218103808
demo1.__sga_target=327155712
demo1.__shared_io_pool_size=0
demo1.__shared_pool_size=104857600
demo1.__streams_pool_size=0
*.audit_file_dest='C:\oracle\admin\demo1\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='C:\oradata\demo1\control01.ctl','C:\oradata\demo1\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='demo1'
*.diagnostic_dest='C:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=demo1XDB)'
*.log_archive_dest_1='LOCATION=C:\oracle\arch'
*.log_archiveformat='%t%s_%r.dbf'
*.memory_target=545259520
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=demo233
*.log_archive_config='dg_config=(demo233,demo231)'
*.log_archive_dest_1='location=C:\oracle\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=demo233'
*.log_archive_dest_2='SERVICE=demo231 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=demo231'
*.fal_server=demo231
*.fal_client=demo233
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_max_processes=10
*.standby_file_management=AUTO
*.db_file_name_convert='/oradata/demo1','C:\oradata\demo1'
*.log_file_name_convert='/oradata/demo1','C:\oradata\demo1'
Ø 生成备库spfile文件
SQL>create spfile from pfile= ’ c:\oracle\initdemo1.ora ’ ;
Ø 将备库启动到nomount状态
SQL>startup nomount
3.7 、添加 standby 日志组
SQL>alter database add standby logfile group 4
>( ‘ /oradata/demo1/redo04.log ’ ) size 50M ；
SQL>alter database add standby logfile group 5
>(‘/oradata/ demo1 /redo05.log’) size 5 0 M;
SQL>alter database add standby logfile group 6
>(‘/oradata/ demo1 /redo06.log’) size 50 M;
SQL>alter database add standby logfile group 7
>( ‘ /oradata/demo1/redo07.log ’ ) size 50M;
3.8 、在主库上使用 rman 进行 duplicate 操作
$ rman target sys/oracle auxiliary sys/oracle@demo233
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
RMAN 恢复完以后会自动将 standby 数据库自动切换到 mount 状态。
3.9 、将备库置于 ADG 模式下
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;
3.10 、数据同步测试
Ø 在主库上以scott 用户执行
SQL>create table emp_test as select * from emp;
SQL>commit;
Ø 在备库上以scott 用执行查询
SQL>select * from emp_test;
4 、主备切换测试
4.1 、主库上查询切换状态（要有 TO STANDBY ）
SQL> select switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
T O STANDBY
查询关闭多余会话
SQL> select program,type from v$session where type='USER';
PROGRAM TYPE
sqlplus@node1 (TNS V1-V3) USER
4.2 、在主库上执行切换
SQL>alter database commit to switchover to physical standby;
4.3 、在备库上执行切换
SQL> alter database commit to switchover to primary ;
如果数据库处于read only 状态，则重启数据库到 open 状态
SQL>shutdown immediate
SQL>startup
否则
SQL>alter database open;
4.4 、重启原主库到 mount 状态，并将其置于 ADG 模式下
SQL> alter database recover managed standby database using current logfile disconnect from session;
4.5 、在新主库上执行日志切换，查看日志是否正常
SQL>ALTER SYSTEM SWITCH LOGFILE;
