Oracle 11g平台异构之Linux To Windows DataGuard安装配置

Oracle11g平台异构之

Linux To Windows DataGuard安装配置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数据库软件（略）

创建数据库实例，停止实例后删除数据文件（如果不建实例，不能以sysdba连接），报错如下

创建监听（略）

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\control

02.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 50M;

SQL>alter database add standby logfile group 6

>(‘/oradata/demo1/redo06.log’) size 50M;

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

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

TO 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;