11g 新特性—— Active Database Duplication for A standby database

ALLSTARS_ORACLE

11g 新特性—— Active Database Duplication for A standby database

简介
------------
从11G开始oracle提供了一个新功能Active Database Duplication for A standby database来创建配置物理standby 数据库。
Active Database Duplication for A standby database这个功能主要从一个正在运行的数据库复制数据文件，控制文件等到一个物理备库（physical standby database）。
这个功能简化了创建standby database过程中在主库备份和备库恢复的环节，实现了自动拷贝主库的控制文件，数据文件等到备库，对比基于备份集的创建standby database过程中需要手动在主库备份，然后将备份集拷贝到备库再手动恢复来说，减少了dba的大量工作。

创建物理standby database过程中的其他环节，如打开主库的force logging，主库init参数的修改等，以及备库开始应用redo log这些环节还需手动来完成的，与以往的创建配置物理standby database 是相同的。

备库的init参数需要在duplicate命令中指定，没有特殊指定的，就会默认使用主库的init参数值。

下面以一个测试例子来具体说明
-----------------------------------
主库：11.2.0.1 单机数据库，db_unique_name=orcl，数据文件存放在文件系统
备库：11.2.0.1 单机数据库，db_unique_name=orabak，使用ASM存储
Data Guard保护模式（Protection mode）采用最大性能模式（MAXIMIZE PERFORMANCE）
redo log传输采用LGWR进程的异步传输方式

1. 打开主库force logging:
SQL>  ALTER DATABASE FORCE LOGGING;

查看主库当前是否为force logging：
SQL> select force_logging from v$database;
FOR
---
YES

2. 修改主库的初始化参数如下：
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orabak)'
LOG_ARCHIVE_DEST_1=
  'LOCATION=/home/oracle/app/archdir
  VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)
  DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_2=
  'SERVICE=orabak LGWR ASYNC
   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   
   DB_UNIQUE_NAME=orabak'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_MAX_PROCESSES=10

3. 在备库创建ASM 磁盘组 '+DATA'，用来存放备库的数据文件，控制文件，standby redo文件等。

4. 在备库创建 adump 路径，与参数audit_file_dest的路径一致
$ mkdir -p /u01/app/admin/orabak/adump

5. 在备库创建init参数文件$ORACLE_HOME/dbs/initorabak.ora'，
有了这个临时的init参数文件，我们就可以在duplicate命令运行前将备库的AUXILIARY实例启动到nomount状态，这也是运行duplicate命令的一个必要条件。

initorabak.ora文件只有一行信息：
DB_NAME=orcl

6. 将密码文件从主库服务器拷贝到备库服务器，并且重命名：
$ mv orapworcl orapworabak

7. 在主库服务器和备库服务器, 编辑添加下面信息到$ORACLE_HOME/network/tnsnames.ora
orabak =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.113)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = orabak)
   ))

orcl =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = orcl)
   ))

8. 在备库服务器, 添加静态注册信息到 $GRID_HOME/network/listener.ora文件，
这主要是由于AUXILIARY实例启动到nomount状态时，listener无法注册AUXILIARY实例，listener会标志Auxiliary实例为'blocked'状态，因此duplicate命令就无法通过TNS的方式连接到Auxiliary实例，为了解决这个问题，需要先手动静态注册数据库实例到listener上。
当Data Guard配置完成后，就可以删除静态注册的配置信息。
（本测试使用ASM，因此需要安装GRID，有效的listener.ora文件是在GRID_HOME下面）

LISTENER =
(DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = ANYTHING))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.113)(PORT = 1521))
   ) )

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = orabak)
     (ORACLE_HOME = /home/oracle/app/product/11.2)
     (SID_NAME = orabak)
   ) )

9. 在备库服务器，执行duplicate命令前，先启动AUXILIARY实例到 nomount 状态：
$ export ORACLE_SID=orabak
$ sqlplus / as sysdba
SQL> startup nomount;

10. 在备库测试与AUXILIARY实例的连接和与主库的连接，如果连接成功，继续执行下面的步骤，
很多时候duplicate命令失败都是由于连接失败导致的。
$ sqlplus sys/oracle@orabak as sysdba
$ sqlplus sys/oracle@orcl as sysdba

  11. 创建备库
在duplicate命令中指定关键字'FOR STANDBY'和'FROM ACTIVE DATABASE'表示从一个active的数据库来复制创建物理备库
参数'DORECOVER'表示duplicate命令会执行recover动作，否则duplicate命令只执行restore，
备库与主库不同的初始化参数，需要在duplicate命令中特殊指定，这样创建备库的spfile时就会用指定的值替换主库的参数值，
主库与备库的数据文件的路径不同，因此设置参数'DB_FILE_NAME_CONVERT'

% rman target sys/oracle@orcl AUXILIARY SYS/oracle@orabak
RMAN>
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET "db_unique_name"="orabak"
SET FAL_SERVER="orcl"
SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/archdir_sbredo
                       VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE) DB_UNIQUE_NAME=orabak'
SET CONTROL_FILES '+DATA/orabak/control01.ctl'
set diagnostic_dest='/u01/app'
set audit_file_dest='/u01/app/admin/orabak/adump'
set DB_FILE_NAME_CONVERT='/home/oracle/app/oradata/orcl','+DATA/orabak/datafile'
NOFILENAMECHECK;

  以下是duplicate standby database过程中屏幕的log信息
-----------------------------------------------------
Starting Duplicate Db at 15-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:

  {
   backup as copy reuse
   targetfile  '/home/oracle/app/product/11.2/dbs/orapworcl' auxiliary format
'/u01/app/product/11.2/dbs/orapworabak'   targetfile
'/home/oracle/app/product/11.2/dbs/spfileorcl.ora' auxiliary format
'/u01/app/product/11.2/dbs/spfileorabak.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/product/11.2/dbs/spfileorabak.ora''";
}
executing Memory Script

Starting backup at 15-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
Finished backup at 15-APR-12

sql statement: alter system set spfile= ''/u01/app/product/11.2/dbs/spfileorabak.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_unique_name =
''orabak'' comment=
'''' scope=spfile";
   sql clone "alter system set  FAL_SERVER =
''orcl'' comment=
'''' scope=spfile";
   sql clone "alter system set  LOG_ARCHIVE_DEST_1 =
''LOCATION=/u01/app/archdir_sbredo                       VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE) DB_UNIQUE_NAME=orabak'' comment=
'''' scope=spfile";
   sql clone "alter system set  CONTROL_FILES =
''+DATA/orabak/control01.ctl'' comment=
'''' scope=spfile";
   sql clone "alter system set  diagnostic_dest =
''/u01/app'' comment=
'''' scope=spfile";
   sql clone "alter system set  audit_file_dest =
''/u01/app/admin/orabak/adump'' comment=
'''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
''/home/oracle/app/oradata/orcl'', ''+DATA/orabak/datafile'' comment=
'''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_unique_name =  ''orabak'' comment= '''' scope=spfile

sql statement: alter system set  FAL_SERVER =  ''orcl'' comment= '''' scope=spfile

sql statement: alter system set  LOG_ARCHIVE_DEST_1 =  ''LOCATION=/u01/app/archdir_sbredo                       VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE) DB_UNIQUE_NAME=orabak'' comment= '''' scope=spfile

sql statement: alter system set  CONTROL_FILES =  ''+DATA/orabak/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set  diagnostic_dest =  ''/u01/app'' comment= '''' scope=spfile

sql statement: alter system set  audit_file_dest =  ''/u01/app/admin/orabak/adump'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/home/oracle/app/oradata/orcl'', ''+DATA/orabak/datafile'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     238530560 bytes

Fixed Size                     1335724 bytes
Variable Size                 83889748 bytes
Database Buffers             150994944 bytes
Redo Buffers                   2310144 bytes

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DATA/orabak/control01.ctl';
}
executing Memory Script

Starting backup at 15-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/app/product/11.2/dbs/snapcf_orcl.f tag=TAG20120415T185859 RECID=8 STAMP=780692341
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:10
Finished backup at 15-APR-12

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
"+DATA/orabak/datafile/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
"+DATA/orabak/datafile/system01.dbf";
   set newname for datafile  2 to
"+DATA/orabak/datafile/sysaux01.dbf";
   set newname for datafile  3 to
"+DATA/orabak/datafile/undotbs01.dbf";
   set newname for datafile  4 to
"+DATA/orabak/datafile/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
"+DATA/orabak/datafile/system01.dbf"   datafile
2 auxiliary format
"+DATA/orabak/datafile/sysaux01.dbf"   datafile
3 auxiliary format
"+DATA/orabak/datafile/undotbs01.dbf"   datafile
4 auxiliary format
"+DATA/orabak/datafile/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA/orabak/datafile/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 15-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/app/oradata/orcl/system01.dbf
output file name=+DATA/orabak/datafile/system01.dbf tag=TAG20120415T185922
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:24
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/app/oradata/orcl/undotbs01.dbf
output file name=+DATA/orabak/datafile/undotbs01.dbf tag=TAG20120415T185922
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:13
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/app/oradata/orcl/sysaux01.dbf
output file name=+DATA/orabak/datafile/sysaux01.dbf tag=TAG20120415T185922
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:13
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/app/oradata/orcl/users01.dbf
output file name=+DATA/orabak/datafile/users01.dbf tag=TAG20120415T185922
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 15-APR-12

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/home/oracle/app/archdir/1_46_778869623.dbf" auxiliary format
"/u01/app/archdir_sbredo/1_46_778869623.dbf"   archivelog like
"/home/oracle/app/archdir/1_47_778869623.dbf" auxiliary format
"/u01/app/archdir_sbredo/1_47_778869623.dbf"   ;
   catalog clone archivelog  "/u01/app/archdir_sbredo/1_46_778869623.dbf";
   catalog clone archivelog  "/u01/app/archdir_sbredo/1_47_778869623.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 15-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=46 RECID=45 STAMP=780692365
output file name=/u01/app/archdir_sbredo/1_46_778869623.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=47 RECID=46 STAMP=780692416
output file name=/u01/app/archdir_sbredo/1_47_778869623.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 15-APR-12

cataloged archived log
archived log file name=/u01/app/archdir_sbredo/1_46_778869623.dbf RECID=1 STAMP=780696505

cataloged archived log
archived log file name=/u01/app/archdir_sbredo/1_47_778869623.dbf RECID=2 STAMP=780696505

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=780696506 file name=+DATA/orabak/datafile/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=780696506 file name=+DATA/orabak/datafile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=780696506 file name=+DATA/orabak/datafile/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=780696506 file name=+DATA/orabak/datafile/users01.dbf

contents of Memory Script:
{
   set until scn  303787;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 15-APR-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK

starting media recovery

archived log for thread 1 with sequence 46 is already on disk as file /u01/app/archdir_sbredo/1_46_778869623.dbf
archived log for thread 1 with sequence 47 is already on disk as file /u01/app/archdir_sbredo/1_47_778869623.dbf
archived log file name=/u01/app/archdir_sbredo/1_46_778869623.dbf thread=1 sequence=46
archived log file name=/u01/app/archdir_sbredo/1_47_778869623.dbf thread=1 sequence=47
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-APR-12

  ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/orcl/redo01.log'

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/home/oracle/app/oradata/orcl/redo02.log'

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 3 thread 1: '/home/oracle/app/oradata/orcl/redo03.log'

  RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 15-APR-12
---------------------------------------------------------------

以上创建完成了物理standby数据库(最后的错误警告信息可以忽略，我们在后面的附录1详细解释这个错误信息)。但是备库数据库还没有开始应用redo log。

12. 创建standby redo log，standby redo log大小等于主库online redo log大小：
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11
   ('+data/redo_sb_01.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12
   ('+data/redo_sb_02.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13
   ('+data/redo_sb_03.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14
   ('+data/redo_sb_04.log') SIZE 52428800;

13. 运行下面的命令开始应用redo log：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

  以上的全部步骤就配置完成了物理standby数据库，并且开始应用redo log。

附录1
----------
在创建备库时最后报出的错误警告信息ORA-19527和在MRP开始应用redo log时，alert log中报出下面的错误信息：

  Errors in file /u01/app/diag/rdbms/orabak/orabak/trace/orabak_mrp0_7644.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
.....
Errors in file /u01/app/diag/rdbms/orabak/orabak/trace/orabak_mrp0_7644.trc:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/orcl/redo01.log'

以上错误的原因：
oracle为了加快备库与主库switchover的速度，从10.2开始增加了一个增强的功能，就是当MRP启动时会去清理备库上online redo log。造成以上2个错误有2个原因，第一个是备库没有创建online redo log，第二个是备库没有设置log_file_name_convert参数。

解决方法：
方法#1：如果不考虑switchover（备库上不创建online reod log），那么可以忽略这个错误，因为这个错只是一个提示性的信息，不会影响备库的MRP的工作。
方法#2：如果考虑switchover，在备库上创建online reod log，并且设置log_file_name_convert参数：
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('+DATA/redo01.log') SIZE 52428800;
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('+DATA/redo02.log') SIZE 52428800;
SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('+DATA/redo03.log') SIZE 52428800;
SQL> alter system set log_file_name_convert='/home/oracle/app/oradata/orcl','+data' scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;