oracle11g dg实时同步,ORACLE11G DG配置

1.设置归档模式

这个生产库都是这种模式。

SQL> archive log list;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> archive log list;

2. Primary设置force logging

SQL> alter database force logging;

SQL> select force_logging from v$database;

FORCE_LOG

---------

YES

3.配置Oracle Net

在Primary库和Standby都要修改。也可以使用netca和netmgr命令配置。

注意：修改完后记得重启listener。

listener.ora::(静态配置LISTENER(备库NOMOUNT状态时无法监听到动态的LSN))

SID_LIST_LISTENER =

(SID_LIST

(SID_DESC =

(GLOBAL_DBNAME = orcl200)

(ORACLE_HOME = /opt/oracle/product)

(SID_NAME = orcl)

)

)

tnsname.ora::

ORCL200 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.200)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl200)

)

)

ORCL206 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.206)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl206)

)

)

4.添加data guard参数

创建pfile,添加如下文件：

SQL> create pfile from spfile;

*.db_unique_name='orcl200'

*.log_archive_config='dg_config=(orcl200,orcl206)'

*.log_archive_dest_1='location=/u01/archivelog'

*.log_archive_dest_2='service=orcl206 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl206'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='orcl206'

*.fal_client='orcl200'

5.用新pfile重启主库

SQL>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora';(create spfile from pfile='';用spfile启动)

File created.

SQL>startup

ORACLE instance started.

二.Standby端设置：

1.创建相关目录结构

对应主库目录创建 /opt/oracle/oradata等

2.创建standby的口令文件

[oracle@qs-dmm-rh2 trace]$ orapwd file=?/dbs/orapwdave password=oracle(主库与备库必须相同)

3.创建standby的初始化参数：

*.db_unique_name='orcl206'

*.log_archive_config='dg_config=(orcl200,orcl206)'

*.log_archive_dest_1='location=/u01/archivelog'

*.log_archive_dest_2='service=orcl200 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl200'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='orcl200'

*.fal_client='orcl206'

*.log_file_name_convert='/opt/oracle/oradata/orcl200','/opt/oracle/oradata/orcl206'*.db_file_name_convert='/opt/oracle/oradata/orcl200','/opt/oracle/oradata/orcl206'

4.用pfile将standby启动到nomount状态：

SQL> startup nomount pfile=?/dbs/initorcl.ora(可创建spfile启动)

ORACLE instance started.

(RMAN 11G用主库创建备库无备份的情况)

5.开始duplicate

[oracle@qs-dmm-rh2 dbs]$

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 8 16:10:25 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.

connected to target database: ORCL (DBID=1272955137)

connected to auxiliary database: ORCL (not mounted)

RMAN>duplicate target database for standby from active database;

(2) 在执行duplicate的时候，如果源库和目标库目录相同，那么在duplicate 时，需要加上nofilenamecheck，如下：

RMAN> duplicate target database for standby from active database nofilenamecheck;

三.后续工作

1.主库已经使用了spfile，但是备库用的还是之前的pfile：

Primary:

SQL> show parameter pfile

NAMETYPEVALUE

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

spfilestring/u01/app/oracle/product/11.2.0

Standby:

SQL> show parameter pfile

NAMETYPEVALUE

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

spfilestring

pfile里面都是我们设置的一些基本参数。但是备库有冗余的作用，所以这里还是建议用主库的pfile copy过来，然后修改相关参数后，在创建spfile。这样即使切换了，对DB的影响也不大。

2.只要备库的监听不重启，重启备库后，主库还是能识别的。如果备库的监听重启了。那么主库也就需要重启。

3.复制结束后的Standby只启动到mount standby的状态。 并没有启动MRP的应用归档程序。 所以这个时候查询主备库，归档是不同步的。需要手动的启动MRP进程。

SQL> alter database recover managed standby database disconnect from session;

4.备库Standby redo log问题：

在duplicate结束后，备库没有添加standby redo log file。但是主库采用的是：lgwr async传送的日志。当备库的RFS进程接收到日志后，发现备库没有standby redo log的时候，备库会自动用ARCH将其写入归档文件。

以下是备库的alert log：(/opt/oracle/diag/rdbms/orcl206/orcl/alert tail -n 50 log.xml)

Tue Mar 08 16:53:32 2011

Archived Log entry 9 added for thread 1 sequence 21 rlc 745174404 ID 0x4bdfd301 dest 2:

RFS[2]: Opened log for thread 1 sequence 22dbid 1272955137 branch 745174404

Tue Mar 08 16:53:36 2011

Media Recovery Log/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_21_745174404.dbf

Media Recovery Waiting for thread 1 sequence 22(in transit) --传输中

Tue Mar 08 16:58:58 2011

Archived Log entry 10 added for thread 1 sequence 22rlc 745174404 ID 0x4bdfd301 dest 2:

RFS[2]: Opened log for thread 1 sequence 23 dbid 1272955137 branch 745174404

Tue Mar 08 16:59:00 2011

Media Recovery Log/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_22_745174404.dbf

Media Recovery Waiting for thread 1 sequence 23 (in transit)

--注意这里归档文件目录，使用的是$ORACLE_HOME/dbs,自动转换为ARCH时，也是使用默认的归档目录。

5.在备库添加standby redo log：

SQL> alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo01.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo01.log' size 50m

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

--在备库添加standby redo log需要先停MRP

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo01.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo02.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo03.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/dave/stdbyredo04.log' size 50m;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

在看一下日志：

Tue Mar 08 17:47:39 2011

Archived Log entry 15 added for thread 1 sequence 27 ID 0x4bdfd301 dest 1:

RFS[2]: Selected log 4 for thread 1 sequence 28 dbid 1272955137 branch 745174404

Tue Mar 08 17:47:43 2011

Archived Log entry 16 added for thread 1 sequence 28 ID 0x4bdfd301 dest 1:

Media Recovery Log /u01/archivelog/1_27_745174404.dbf

RFS[2]: Selected log 4 for thread 1 sequence 29 dbid 1272955137 branch 745174404

Media Recovery Log /u01/archivelog/1_28_745174404.dbf

Media Recovery Waiting for thread 1 sequence 29 (in transit)

--我们添加standby redo log之后，归档文件变成了我们指定的Log_archive_dest_n指定的参数。

6.在主库也添加一下standby redo log

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdbyredo01.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdbyredo02.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdbyredo03.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdbyredo04.log' size 50m;

Database altered.

7.启用real-time apply，从而实现real-time query：

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

(alter database recover managed standby database using current logfile disconnect from session)

Database altered.

8.验证real-time apply和real-time query：(同步时，主库的更改操作，必须加COMMIT生效)

Primary：

SQL> create table dave(id number,name varchar2(20));

Table created.

SQL> insert into dave values(1,'tianlesoftware');

1 row created.

SQL> commit;

Commit complete.

Standby：

SQL>select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

SQL> select * from dave;

ID NAME

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

1 tianlesoftware

备注：：

select max(sequence#) from v$archived_log;

select dest_id, status, destination from v$archive_dest where status != 'INACTIVE';

select message from v$dataguard_status;

alter database recover managed standby database using current logfile disconnect from session

oracle 日志文件

/u01/oinOracle/diag/rdbms/slave201/orcl/alert

oracle连接对方库

sqlplus sys/oracle_d@mast200 as sysdba

sqlplus sys/oracle_d@slave201 as sysdba