Oracle 11g Data Guard 使用duplicate from active database 创建物理DG

概要介绍  

   直接把原数据库进行复制,11g的RMANduplicate可以通过Active databaseduplicate和Backup-based duplicate两种方法实现,这里用Activedatabase duplicate这种方式来搭建DG,主库的停机时间很少,只需要重启一下,使参数生效。也可以用这种方法进行DB迁移。DG搭建好，然后把备库激活就可以了,这样整个迁移中宕机时间也比较短。

环境说明         

primary主目录：/u01/app/oracle/product/11.2.0/dbhome_1  数据文件目录：/u01/app/oracle/oradata/orcl/

standby主目录：/opt/app/oracle/product/11.2.0/dbhome_1 数据文件目录：/data/oradata/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@edrsr12p1 admin]$cd $ORACLE_HOME/network/admin       
[oracle@edrsr12p1 admin]$cat tnsnames.ora

primary和standby都要 测试监听器：（我在做的过程出现了tnsping都没有问题，但是连接时候提示ORA-12514：原因配置listener是SID_NAME写错了）

[oracle@edrsr12p1 admin]$ tnsping STDB
[oracle@edrsr12p1 admin]$tnsping PORCL

[oracle@edrsr12p1 admin]$sqlplus sys/oracle@STDB as sysdba

[oracle@edrsr12p1 admin]$sqlplus sys/oracle@PORCL as sysdba

4、添加data guard 参数
   创建pfile

       [oracle@edrsr12p1 admin]$ sqlplus / as sysdba
       SYS@ora11g>create pfile from spfile;
      [oracle@edrsr12p1 admin]$ cd $ORACLE_HOME/dbs

  修改参数文件

     [oracle@edrsr12p1 admin]$ vi initorcl.ora

   --添加如下参数，上面如有重复的参数删除
    *.db_unique_name='primary'
    *.log_archive_config='dg_config=(primary,standby)'
    *.log_archive_dest_1='location=/oralog valid_for=(all_logfiles,all_roles) db_unique_name=primary'

    --注意：STDB为TNS文件中的network service name
    *.log_archive_dest_2='service=STDB reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby'
    *.log_archive_dest_state_1=enable
    *.log_archive_dest_state_2=enable
    *.standby_file_management='auto'

   --注意：如果将这个文件拷贝到standby库，要改成fal_server='PORCL',fal_client='STDB',这两个值写错了将导致不能传输日志。
     *.fal_server='STDB'
     *.fal_client='PORCL'

   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重启主库 primary

    SQL>shutdown immediate;

       Database closed.
       Database dismounted.
      ORACLE instance shut down.
    SQL>create spfile from pfile='?/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 bytes
     Redo Buffers                6094848 bytes
     Database mounted.
     Database opened.
  SQL>
      
二.  Standby 端设置：
 1. 创建相关目录结构(下面standby初始参数文件中所写的目录)

--这里创建的目录和primary库是不同的，如不同要在参数文件里转换一下
[oracle@linux7 oracle]$mkdir -p /opt/app/oracle/oradata/dave

[oracle@linux7 oracle]$mkdir -p /opt/app/oracle/flash_recovery_area

[oracle@linux7 oracle]$chown oracle  /opt/app/oracle/flash_recovery_area  -R

[oracle@linux7 ~]$chown oracle /opt/app/oracle/oradata/dave -R

2. 创建standby的口令文件（standby 和primary的sys用户的密码必须完全相同，否则rman会提示ora-07101错误）

 [oracle@linux7 ~]$ orapwd file=?/dbs/orapwora11g password=oracle entries=10 force=yignorecase=Y

或者：  [oracle@linux7 ~]$ scp orapwd  192.168.56.9:$ORACLE_HOME/dbs

例子： scp orapwd 192.168.56.9:/opt/app/oracle/product/11.2.0/dbhome_1/dbs
3.创建standby的初始化参数：

--添加如下参数，上面如有重复的参数删除

从主库上拷贝参数文件到备库

[oracle@linux7 ~]$scp initorcl.ora 192.168.56.9:$ORACLE_HOME/dbs/  

--在备库 standby上修改

orcl.__db_cache_size=352321536
orcl.__java_pool_size=4194304
orcl.__large_pool_size=8388608
orcl.__oracle_base='/opt/app/oracle' #ORACLE_BASE set from environment
orcl.__pga_aggregate_target=92274688
orcl.__sga_target=574619648
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=201326592
orcl.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/orcl/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
*.control_files='/data/oradata/orcl/control01.ctl','/data/oradata/orcl/control02.ctl','/data/oradata/orcl/control03.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST OPTIONAL REOPEN=300'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=873741824
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='DEFAULT_MAINTENANCE_PLAN'
*.sessions=1105
*.undo_tablespace='UNDOTBS1'

--将audit_trail的值由db改成none,不然在alter日志中会报“AUDIT_TRAIL initializationparameter is changed to OS, as DB is NOT compatible for database opened with read-onlyaccess”

*.audit_trail='none

--添加如下参数，上面如有重复的参数删除

########add data gaure#############
*.db_unique_name='standby'
*.log_archive_config='dg_config=(primary,standby)'
*.log_archive_dest_1='location=/orachivelog valid_for=(all_logfiles,all_roles) db_unique_name=standby'

--PORCL为tnsname.ora中的网络名
*.log_archive_dest_2='service=PORCL reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=primary'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'

--备库上的fal_server、fal_client的参数千万不要写错，它的值是tnsname.ora文件中的网络名，不然备库不能接收日志，而且在启动备库到open时会报：ORA-01152: file 1 was not restoredfrom a sufficiently old backup
*.fal_server='STDB'
*.fal_client='PORCL'

注意：如果主从数据目录结构不同时，需要加以下参数

*.log_file_name_convert=’primary路径’,’standby路径’

*.db_file_name_convert=’primary路径’,’standby路径’

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

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

5. 开始duplicate

[oracle@linux7 ~]$ rman target sys/oracle@PORCL  auxiliary sys/oracle@STDB

  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)

--执行duplicate的时候，如果源库和目标库目录相同，那么在duplicate时，需要加上nofilenamecheck，不同则不需要加nofilenamecheck,否则会报：RMAN-05501: aborting duplication of target database

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

     StartingDuplicate Db at 22-FEB-13
     using targetdatabase control file instead of recovery catalog
     allocatedchannel: ORA_AUX_DISK_1
    channelORA_AUX_DISK_1: SID=20 device type=DISK
   contents ofMemory Script:
  {
   backup as copy reuse
--用duplicate创建standby会复制口令文件
   targetfile '/u01/oracle/product/11.2.0/dbs/orapwora11g' auxiliary format  '/u01/oracle/product/11.2.0/dbs/orapworcl'   ;
   }
  executing MemoryScript
  Starting backupat 22-FEB-13
 allocatedchannel: ORA_DISK_1
channel ORA_DISK_1:SID=41 device type=DISK
Finished backupat 22-FEB-13
--创建控制文件
  contents ofMemory Script:
  {
   backup as copy current controlfile forstandby auxiliary format '/oradata/orcl/control01.ctl';
   restore clone controlfile to  '/opt/oracle/flash_recovery_area/orcl/control02.ctl'from '/oradata/ora11g/control01.ctl';
 }
  executing MemoryScript
  Starting backupat 22-FEB-13
using channelORA_DISK_1
channelORA_DISK_1: starting datafile copy
copying standbycontrol file
output filename=/u01/oracle/product/11.2.0/dbs/snapcf_ora11g.f tag=TAG20130222T133953RECID=1 STAMP=808061996
channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at22-FEB-13
Starting restoreat 22-FEB-13
using channelORA_AUX_DISK_1
channelORA_AUX_DISK_1: copied control file copy
Finished restoreat 22-FEB-13
--将备库启动到mount standby standby database
contents ofMemory Script:
{
   sql clone 'alter database mount standbydatabase';
}
executing MemoryScript
sql statement:alter database mount standby database
--转换数据文件目录
contents ofMemory Script:
{
   set newname for tempfile  1 to "/oradata/ora11g/temp01.dbf";
  switch clone tempfile all;
   set newname for datafile  1 to "/oradata/ora11g/system01.dbf";
   set newname for datafile  2 to  "/oradata/ora11g/sysaux01.dbf";
   set newname for datafile  3 to "/oradata/ora11g/undotbs01.dbf";
   set newname for datafile  4 to "/oradata/ora11g/users01.dbf";
   backup as copy reuse
   datafile 1 auxiliary format
 "/oradata/ora11g/system01.dbf"   datafile 2 auxiliary format
 "/oradata/ora11g/sysaux01.dbf"   datafile 3 auxiliary format
 "/oradata/ora11g/undotbs01.dbf"  datafile 4 auxiliary format
 "/oradata/ora11g/users01.dbf" ;
  sql 'alter system archive log current';
}
executing MemoryScript
executingcommand: SET NEWNAME
renamed tempfile1 to /oradata/ora11g/temp01.dbf in control file
executingcommand: SET NEWNAME
executingcommand: SET NEWNAME
executingcommand: SET NEWNAME
executingcommand: SET NEWNAME
Starting backupat 22-FEB-13
using channelORA_DISK_1
--开始copy datafile，如果数据文件比较大，这里会比较慢
channelORA_DISK_1: starting datafile copy
input datafilefile number=00001 name=/oradata/ora11g/system01.dbf
output filename=/oradata/ora11g/system01.dbf tag=TAG20130222T134011
channelORA_DISK_1: datafile copy complete, elapsed time: 00:02:10
channelORA_DISK_1: starting datafile copy
input datafilefile number=00002 name=/oradata/ora11g/sysaux01.dbf
output filename=/oradata/ora11g/sysaux01.dbf tag=TAG20130222T134011
channelORA_DISK_1: datafile copy complete, elapsed time: 00:01:38
channelORA_DISK_1: starting datafile copy
input datafilefile number=00003 name=/oradata/ora11g/undotbs01.dbf
output filename=/oradata/ora11g/undotbs01.dbf tag=TAG20130222T134011
channel ORA_DISK_1:datafile copy complete, elapsed time: 00:00:27
channelORA_DISK_1: starting datafile copy
input datafilefile number=00004 name=/oradata/ora11g/users01.dbf
output filename=/oradata/ora11g/users01.dbf tag=TAG20130222T134011
channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backupat 22-FEB-13
sql statement:alter system archive log current
contents ofMemory Script:
{
   switch clone datafile all;
}
executing MemoryScript
datafile 1switched to datafile copy
input datafilecopy RECID=1 STAMP=808062273 file name=/oradata/ora11g/system01.dbf
datafile 2switched to datafile copy
input datafilecopy RECID=2 STAMP=808062273 file name=/oradata/ora11g/sysaux01.dbf
datafile 3switched to datafile copy
input datafilecopy RECID=3 STAMP=808062273 file name=/oradata/ora11g/undotbs01.dbf
datafile 4switched to datafile copy
input datafilecopy RECID=4 STAMP=808062273 file name=/oradata/ora11g/users01.dbf
--DG 到这里完成
FinishedDuplicate Db at 22-FEB-13

RMAN>

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

两点注意事项：
（1）如果使用的是非catalog，在rman 连接时，加上nocatalog关键字，如：
 [oracle@linux7 ~]$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 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/16/2016 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

注意：
Duplicate不会将primary上的temp表空间复制到standby库上,但是在将standby库启动到open时会自动创建，standby库上的redo log oracle也将会重新创建。
[oracle@linux7 ~]$host ls -l /data/oradata/orcl/temp01.dbf
  ls:/data/oradata/orcl/temp01.dbf: 没有那个文件或目录

三、建立完ADG后续工作

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
1. 创建备库spfile,并用spfile启库：
 SQL> create spfile from pfile;
 SQL> shut immediate;
 SQL> startup nomount;
 SQL> alter database mount standby database

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

3. 启用MRP进程

 复制结束后的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将其写入归档文件

--没有standby redo log ,启用时实应用日志会报以下错
SYS@ora11g>alter database recover managed standby database using current logfiledisconnect from session;
alter databaserecover managed standby database using current logfile disconnect from session
*
 ERROR at line 1:
 ORA-38500: USINGCURRENT LOGFILE option not available without standby redo logs

5.在备库添加standby redo log：

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

   alter database add standby logfile '/data/oradata/orcl/stdbyredo01.log' size 50m;
   *
   ERROR at line 1:
  ORA-01156: recovery or flashback in progress may need access to files

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

   Database altered.
SQL>alter database add standby logfile '/data/oradata/orcl/stdbyredo02.log' size 50m;

    Database altered.
SQL>alter database add standby logfile '/data/oradata/orcl/stdbyredo03.log' size 50m;

   Database altered.
SQL>alter database add standby logfile '/data/oradata/orcl/stdbyredo04.log' size 50m;

    Database altered.
SQL>alter database add standby logfile '/data/oradata/orcl/stdbyredo05.log' size 50m;

   Database altered.

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-timequery：

--在备库 standby

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
 
 小结：
     通过几天反复的实验，终于完成了整个实验，也遇到一些问题在这里总结一下：
1），网络连接文件listener.ora/tnsname.ora非常的敏感，因此最好少改动这个文件，并做好备份，在需要时直接复制备份文件。
2），ADG的参数文件中的参数一定要严格按照官方文档操作并了解相关参数的用途。
3），备库上的fal_server、fal_client的参数千万不要写错，否则备库不能接收日志。
4），主从库的sys口令必须完全相同。
5），使用tnsping命令测试主从库网络连通性。
6），切记，经常观察alter 报警日志的相关操作信息