【DG】Oracle11g​异构平台之Linux到Windows DataGuard安装配置--duplicate

0    52    1

👉 本文共约4692个字,系统预计阅读时间或需18分钟。

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 数据库软件(略)

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

Ø 创建数据库实例(略)

Ø 创建监听(略)

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;

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

18 − 3 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部