本文共 18231 字，大约阅读时间需要 60 分钟。
【DG】Oracle11g 异构平台之
Linux To Windows DataGuard 安装配置--duplicate
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\adumpmkdir Z:\archivemkdir Z:\app\Administrator\oradata\orcldg\set ORACLE_SID=orcldgservices.msc 中 启动服务sqlplus sys/wxqhaey as sysdbastartup nomountrman target sys/wxqhaey@orcl auxiliary sys/wxqhaey@orcldgduplicate target database for standby nofilenamecheckfrom 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_namealter database open;select * from v$log;set line 9999select 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=104152956928orcl.__java_pool_size=1879048192orcl.__large_pool_size=1342177280orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=22548578304orcl.__sga_target=138512695296orcl.__shared_io_pool_size=0orcl.__shared_pool_size=29796335616orcl.__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/wxqhaeysqlplus sys/wxqhaey as sysdbaorapwd file="D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ORA" password=wxqhaey force=yoradg11g--oradgphy---oradgss--oradgss oradg11g.__db_cache_size=125829120oradg11g.__java_pool_size=4194304oradg11g.__large_pool_size=4194304oradg11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentoradg11g.__pga_aggregate_target=83886080oradg11g.__sga_target=327155712oradg11g.__shared_io_pool_size=0oradg11g.__shared_pool_size=184549376oradg11g.__streams_pool_size=0audit_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=8192db_domain=''rman target sys/lhr@tns_oradgphy auxiliary sys/lhr@tns_oradg11gduplicate target database for standby nofilenamecheckfrom 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_oradglgduplicate target database for standby nofilenamecheckfrom 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_oradgssduplicate target database for standby nofilenamecheckfrom 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 nofilenamecheckfrom 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_archive_format='%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_archive_format='%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;
About Me
| ........................................................................................................................ ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub、博客园、CSDN和个人微 信公众号（ ）上有同步更新 ● 本文itpub地址： ● 本文博客园地址： ● 本文CSDN地址： ● 本文pdf版、个人简介及小麦苗云盘地址： ● 数据库笔试面试题库及解答： ● DBA宝典今日头条号地址： ........................................................................................................................ ● QQ群号： 230161599 、618766405 ● 微 信群：可加我微 信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ），注明添加缘由 ● 于 2019-08-01 06:00 ~ 2019-08-31 24:00 在西安完成 ● 最新修改时间：2019-08-01 06:00 ~ 2019-08-31 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ........................................................................................................................ ● 小麦苗的微店： ● 小麦苗出版的数据库类丛书： ● 小麦苗OCP、OCM、高可用网络班： ● 小麦苗腾讯课堂主页： ........................................................................................................................ 使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号（ ）及QQ群（DBA宝典）、添加小麦苗微 信， 学习最实用的数据库技术。 ........................................................................................................................ |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2655281/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-2655281/