在blog：Oracle 11gR2 使用 RMAN duplicate from active database 复制数据库

       http://blog.csdn.net/tianlesoftware/archive/2011/03/08/6230498.aspx

演示了11gR2 下duplicate from active database的例子。 在这里继续使用这个命令来创建一个物理standby。

 

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

 

Oracle 10g下rman duplicate 创建standby 参考Blog：

       用RMAN复制 搭建 物理 Data Gurad 环境

       http://blog.csdn.net/tianlesoftware/archive/2010/07/23/5756750.aspx

 

       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=orcl

 

Standby IP: 192.168.2.43

DB_NAME=orcl

 

一.  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-rh1 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-rh1 admin]$ cat tnsnames.ora

ORCL_ST =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =http://www.cndba.cn/dave/article/862

      (SERVICE_NAME = orcl)

    )

  )

 

ORCL_PD =

  (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='orcl_pd'

*.log_archive_config='dg_config=(orcl_pd,orcl_st)'

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

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

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='orcl_st'http://www.cndba.cn/dave/article/862

*.fal_client='orcl_pd'

 

 

注意：

       在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 Area  422670336 bytes

Fixed Size                  1336960 bytes

Variable Size             310380928 bytes

Database Buffers          104857600 byteshttp://www.cndba.cn/dave/article/862

Redo Buffers                6094848 bytes

Database mounted.

Database opened.

SQL>

 

 

二.  Standby 端设置：

 

1. 创建相关目录结构

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

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

 

2. 创建standby的口令文件

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

 

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

*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='orcl_st'

*.log_archive_config='dg_config=(orcl_pd,orcl_st)'

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

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

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='orcl_pd'

*.fal_client='orcl_st'

*.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 Area  146472960 bytes

Fixed Size                  1335080 bytes

Variable Size              92274904 bytes

Database Buffers           50331648 byteshttp://www.cndba.cn/dave/article/862

Redo Buffers                2531328 bytes

 

5. 开始duplicate

 

[oracle@qs-dmm-rh2 dbs]$ rman target sys/oracle@orcl_pd auxiliary sys/oracle@orcl_st

 

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 tempfile  1 to

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

   switch clone tempfile all;

   set newname for datafile  1 to

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

   set newname for datafile  2 to

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

   set newname for datafile  3 to

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

   set newname for datafile  4 to

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

   backup as copy reuse

   datafile  1 auxiliary format

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

 2 auxiliary format

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

 3 auxiliary format http://www.cndba.cn/dave/article/862

 "/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 到其他目录

}http://www.cndba.cn/dave/article/862

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_ST nocatalog

 

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 forstandby from active database dorecover nofilenamecheck;

 

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

 

RMAN> duplicate target database forstandby from active database dorecover 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

NAME                                 TYPE    VALUE

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

spfile                               string  /u01/app/oracle/product/11.2.0

 

Standby:

SQL> show parameter pfile

NAME                                 TYPE        VALUE

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

spfile                               string

 

       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 22 dbid 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 22 rlc 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;

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 功能很强大.

 

 

 

 

 

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

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Blog:     http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email:   tianlesoftware@gmail.com

Skype: tianlesoftware

 

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满);   DBA2 群：62697977(满)  DBA3 群：62697850(满)  

DBA 超级群：63306533(满);  DBA4 群：83829929(满) DBA5群： 142216823(满) 

DBA6 群：158654907(满)   DBA7 群：69087192(满)  DBA8 群：172855474

DBA 超级群2：151508914  DBA9群：102954821     聊天 群：40132017(满)

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle 11g