用这种方式来搭建DG，主库的停机时间很少，只需要重启一下，使参数生效。也可以用这种方法进行DB迁移。DG搭建好，然后把备库激活就可以了。这样整个迁移中宕机时间也比较短。

Oracle 11g的pyhsical standby支持open read only下的apply和Real-time query。因此就有了physical standby稳定和logical standby的报表查询功能。

Oracle：11.2.0.1

OS：redhat 5.5

Primary IP: 192.168.2.42

DB_NAME=sanfu

Standby IP: 192.168.2.43

DB_NAME=sanfu

一.Primary端操作：

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

[oracle@qs-dmm-rh2 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost6.localdomain6)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = orcl)

)

)

--配置静态注册

tnsname.ora

[oracle@qs-dmm-rh2 admin]$ cat tnsnames.ora

sanfus=

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

sanfup=

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

4.添加data guard参数

创建pfile,添加如下文件：

SQL> create pfile from spfile;

*.db_name='orcl'

*.db_unique_name='sanfup'

*.log_archive_config='dg_config=(sanfup,sanfus)'

*.log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=sanfup'

*.log_archive_dest_2='service=sanfusreopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sanfus'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='sanfus'

*.fal_client='sanfup'

注意：

在Oracle 11g的Data Guard中，standby_archive_dest参数已经被取消了。

Standby归档文件的存放位置按如下规则来进行：

（1）当LOG_ARCHIVE_DEST_n设置了valid_for=(all_logfiles,all_roles)，那么在不定义standby_archive_dest参数时，Oracle就会选择LOG_ARCHIVE_DEST_n参数作为归档目标。

（2）如果在第一步设置的同时，又独立设置LOG_ARCHIVE_DEST_n参数为valid_for=(standby_logfile,*)属性，那么当compatible参数大于10.0的时候，会自动的选择任意一个LOG_ARCHIVE_DEST_n的值。

（3）如果LOG_ARCHIVE_DEST_n没有设置的话，默认位置是：

$ORACLE_HOME/dbs.

不过valid_for参数的默认值就是all_logfiles和all_roles.所以只要设置了本地的归档位置，远程的归档文件也会放到这个目录下面。

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

File created.

SQL>startup

ORACLE instance started.

Total System Global Area422670336 bytes

Fixed Size1336960 bytes

Variable Size310380928 bytes

Database Buffers104857600 bytes

Redo Buffers6094848 bytes

Database mounted.

Database opened.

SQL>

二.Standby端设置：

1.创建相关目录结构

[oracle@qs-dmm-rh3 trace]$ mkdir -p /u01/app/oracle/oradata/dave

--这里我们创建的目录和Target库不同，我们在参数文件里需要转换一下。

2.创建standby的口令文件

[oracle@qs-dmm-rh3 trace]$ orapwd file=?/dbs/orapwdave password=oracle

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

*.compatible='11.2.0.4.0'

*db_name=orcl

*.control_files='/u01/app/oracle/oradata/dave/control01.ctl', '/u01/app/oracle/oradata/dave/control02.ctl',

'/u01/app/oracle/oradata/dave/control03.ctl'

*.db_unique_name='sanfus'

*.log_archive_config='dg_config=(sanfup,sanfus)'

*.log_archive_dest_1='location=/u01/archivelog valid_for=(standby_logfile, standby_role) db_unique_name=sanfus'

*.log_archive_dest_2='service=sanfupreopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sanfup'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='sanfup'

*.fal_client='sanfus'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/dave'

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/dave'

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

SQL> startup nomount pfile=?/dbs/initorcl.ora

ORACLE instance started.

Total System Global Area146472960 bytes

Fixed Size1335080 bytes

Variable Size92274904 bytes

Database Buffers50331648 bytes

Redo Buffers2531328 bytes

5.开始duplicate

[oracle@qs-dmm-rh3 dbs]$rman target sys/oracle@sanfupauxiliary sys/oracle@sanfus

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;

Starting Duplicate Db at 08-MAR-11

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl';

--用duplicate创建standby时会复制口令文件

}

executing Memory Script

Starting backup at 08-MAR-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=45 device type=DISK

Finished backup at 08-MAR-11

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format'/u01/app/oracle/oradata/dave/control01.ctl';

restore clone controlfile to'/u01/app/oracle/oradata/dave/control02.ctl' from

'/u01/app/oracle/oradata/dave/control01.ctl';

restore clone controlfile to'/u01/app/oracle/oradata/dave/control03.ctl' from

'/u01/app/oracle/oradata/dave/control01.ctl';

--创建控制文件

}

executing Memory Script

Starting backup at 08-MAR-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20110308T161152 RECID=4 STAMP=745258313

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 08-MAR-11

Starting restore at 08-MAR-11

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 08-MAR-11

Starting restore at 08-MAR-11

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 08-MAR-11

contents of Memory Script:

{

sql clone 'alter database mount standby database';

--将备库启动到mount standby

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for tempfile1 to

"/u01/app/oracle/oradata/dave/temp01.dbf";

switch clone tempfile all;

set newname for datafile1 to

"/u01/app/oracle/oradata/dave/system01.dbf";

set newname for datafile2 to

"/u01/app/oracle/oradata/dave/sysaux01.dbf";

set newname for datafile3 to

"/u01/app/oracle/oradata/dave/undotbs01.dbf";

set newname for datafile4 to

"/u01/app/oracle/oradata/dave/users01.dbf";

backup as copy reuse

datafile1 auxiliary format

"/u01/app/oracle/oradata/dave/system01.dbf"datafile

2 auxiliary format

"/u01/app/oracle/oradata/dave/sysaux01.dbf"datafile

3 auxiliary format

"/u01/app/oracle/oradata/dave/undotbs01.dbf"datafile

4 auxiliary format

"/u01/app/oracle/oradata/dave/users01.dbf";

sql 'alter system archive log current';

--将datafile convert到其他目录

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/dave/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 08-MAR-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

--开始copy datafile，如果数据文件比较大，这个会比较慢

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

output file name=/u01/app/oracle/oradata/dave/system01.dbf tag=TAG20110308T161204

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

output file name=/u01/app/oracle/oradata/dave/sysaux01.dbf tag=TAG20110308T161204

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

output file name=/u01/app/oracle/oradata/dave/undotbs01.dbf tag=TAG20110308T161204

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

output file name=/u01/app/oracle/oradata/dave/users01.dbf tag=TAG20110308T161204

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 08-MAR-11

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=745258432 file name=/u01/app/oracle/oradata/dave/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=5 STAMP=745258432 file name=/u01/app/oracle/oradata/dave/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=745258432 file name=/u01/app/oracle/oradata/dave/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=7 STAMP=745258432 file name=/u01/app/oracle/oradata/dave/users01.dbf

Finished Duplicate Db at 08-MAR-11

RMAN>

DG复制到这一步已经操作完成了。但是还有一些细节需要处理。

（1）如果使用的是非catalog，在rman 连接时，加上nocatalog关键字，如：

DG2:/home/oracle> rman targetsys/oracle@DG_PD auxiliarysys/oracle@DG_STnocatalog

Recovery Manager: Release 11.2.0.3.0 -Production on Fri Oct 28 18:46:46 2011

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.

connected to target database: DG(DBID=1679060044)

using target databasecontrol file instead of recovery catalog

connected to auxiliary database: DG (notmounted)

不然会报如下错误：

DBGSQL:TARGET> begin :fhdbi := dbms_rcvcat.getDbid; end;

DBGSQL: sqlcode = 6550

DBGSQL: B :fhdbi = 32767

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of Duplicate Db commandat 10/28/2011 17:53:04

RMAN-05501: aborting duplication of targetdatabase

RMAN-03015: error occurred in stored scriptMemory Script

ORA-06550: line 1, column 17:

PLS-00201: identifier'DBMS_RCVCAT.GETDBID' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

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

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

我在上面的示例中，目录结构不同，所以没有用该参数，如果目录相同，而又没有加该参数，那么就会报如下错误：

RMAN> duplicate target database forstandby from active databasedorecover nofilenamecheck;

RMAN-05501: aborting duplication of targetdatabase

RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/users01.dbf conflicts with a file used by the targetdatabase

RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/undotbs01.dbf conflicts with a file used by thetarget database

RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/sysaux01.dbf conflicts with a file used by thetarget database

RMAN-05001: auxiliary file name/u01/app/oracle/oradata/dg/system01.dbf conflicts with a file used by thetarget database

三.后续工作

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：

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/arch2_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/arch2_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;

Database altered.

8.验证real-time apply和real-time query：

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

小结：

11gR2的物理Data Guard功能很强大.