使用RMAN DUPLICATE...FROM ACTIVE DATABASE命令來建立DataGuard物理備庫

環境：

database_name：beijing
database_version：11.2.0.2.0

primary：
IP：10.0.2.118
hostname：beijing
oracle_sid：beijing

standby:
IP：10.0.2.112
hostname：shanghai
oracle_sid：shanghai

一、準備主資料庫：
1、確保主資料庫處於歸檔模式：
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

2、開啟force logging：
SQL> alter database force logging;

Database altered.

3、建立standby redologs：
SQL> col file_name for a50
SQL> select lf.group#,member as file_name,bytes/1024/1024 as size_MB from v$logfile lf,v$log l where lf.group#=l.group# order by group#;

    GROUP# FILE_NAME                         SIZE_MB
---------- -------------------------------------------------- ----------
     1 /u01/app/oracle/oradata/beijing/redo01.log              50
     2 /u01/app/oracle/oradata/beijing/redo02.log              50
     3 /u01/app/oracle/oradata/beijing/redo03.log              50
注意：建立的standby redo大小需要和主庫的redo大小保持一致
SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/beijing/stby04.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/beijing/stby05.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/beijing/stby06.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/beijing/stby07.log') size 50m;
Database altered.
SQL> select type,member from v$logfile;

TYPE    MEMBER
------- --------------------------------------------------
ONLINE    /u01/app/oracle/oradata/beijing/redo03.log
ONLINE    /u01/app/oracle/oradata/beijing/redo02.log
ONLINE    /u01/app/oracle/oradata/beijing/redo01.log
STANDBY /u01/app/oracle/oradata/beijing/stby04.log
STANDBY /u01/app/oracle/oradata/beijing/stby05.log
STANDBY /u01/app/oracle/oradata/beijing/stby06.log
STANDBY /u01/app/oracle/oradata/beijing/stby07.log

7 rows selected.

4、修改主資料庫的初始化引數：
SQL> alter system set log_archive_config='DG_CONFIG=(beijing,shanghai)';
System altered.
SQL> alter system set log_archive_dest_1='location=/archivelog/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=beijing';
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=shanghai lgwr async valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=shanghai';
System altered.
SQL> alter system set log_archive_dest_state_1=enable;
System altered.
SQL> alter system set fal_server=shanghai;
System altered.
SQL> alter system set fal_client=beijing;
System altered.
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/shanghai/','/u01/app/oracle/oradata/beijing/' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/shanghai/','/u01/app/oracle/oradata/beijing/' scope=spfile;
System altered.


二、配置網路：
1、在備庫上配置靜態監聽：
[oracle@shanghai ~]$ cd $ORACLE_HOME/network/admin
[oracle@shanghai admin]$ vi listener.ora
[oracle@shanghai admin]$ cat listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = shanghai)
     (ORACLE_HOME = /u01/app/oracle/product/11.2/db_1)
     (SID_NAME = shanghai)
    )
   )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = shanghai>)(PORT = 1521))
  )
2、在主庫和備庫上都編輯tnsnames.ora檔案：
[oracle@beijing admin]$ cat tnsnames.ora
beijing =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = beijing)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = beijing))
  )

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

檢查網路配置：
[oracle@beijing admin]$ tnsping shanghai
[oracle@shanghai admin]$ tnsping beijing


三、建立備用資料庫：
1、複製密碼檔案：
主資料庫和備資料庫的密碼必須相同，所以做好的辦法就是複製密碼檔案：
[oracle@beijing dbs]$ scp orapwbeijing oracle@shanghai:/u01/app/oracle/product/11.2/db_1/dbs/orapwshanghai
[oracle@shanghai dbs]$ pwd
/u01/app/oracle/product/11.2/db_1/dbs
[oracle@shanghai dbs]$ ls
hc_DBUA0.dat  init.ora  orapwshanghai
2、在備庫上建立引數檔案：
只需要一個引數db_name就行：
如下所示：
[oracle@shanghai dbs]$ cat initshanghai.ora
DB_NAME=beijing
DB_UNIQUE_NAME=shanghai
DB_BLOCK_SIZE=8192

3、在備庫上建立相應的目錄：
[oracle@shanghai ~]$ cd $ORACLE_BASE
[oracle@shanghai oracle]$ mkdir -p oradata/shanghai
[oracle@shanghai oracle]$ mkdir -p diag/rdbms/shanghai/shanghai/trace
[oracle@shanghai oracle]$ mkdir -p diag/rdbms/shanghai/shanghai/cdump
[oracle@shanghai oracle]$ mkdir -p admin/shanghai/adump
建立歸檔日誌目錄：
[root@shanghai ~]# cd /
[root@shanghai /]# mkdir archivelog
[root@shanghai /]# chown -R oracle:oinstall /archivelog

4、啟動備庫例項：
[oracle@shanghai ~]$ export ORACLE_SID=shanghai
[oracle@shanghai ~]$ sqlplus / as sysdba;
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initshanghai.ora
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size            2225064 bytes
Variable Size          159386712 bytes
Database Buffers       50331648 bytes
Redo Buffers            5214208 bytes
5、確認可以以sysdba許可權登入
[oracle@shanghai ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Sun Dec 23 16:07:18 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn sys/qweasd@shanghai as sysdba;
Connected.
SQL> conn sys/qweasd@beijing as sysdba;
Connected.

注意：如果遇到如下錯誤，則重啟監聽再試：
SQL> conn sys/qweasd@shanghai as sysdba;
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
6、在主庫上執行rman duplicate命令：
[oracle@beijing ~]$ rman target sys/qweasd@beijing auxiliary sys/qweasd@shanghai

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Dec 23 16:29:21 2012

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

connected to target database: BEIJING (DBID=767262686)
connected to auxiliary database (not started)
RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
  parameter_value_convert 'beijing','shanghai'
  set db_unique_name='shanghai'
  set db_file_name_convert='/beijing/','/shanghai/'
  set log_file_name_convert='/beijing/','/shanghai/'
  set control_files='/u01/app/oracle/oradata/shanghai/control01.ctl'
  set log_archive_max_processes='5'
  set fal_client='shanghai'
  set fal_server='beijing'
  set standby_file_management='AUTO'
  set log_archive_config='dg_config=(beijing,shanghai)'
  set log_archive_dest_2='service=beijing ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=beijing'
;
}


過程：
allocated channel: prmy1
channel prmy1: SID=961 device type=DISK

allocated channel: prmy2
channel prmy2: SID=1152 device type=DISK

allocated channel: prmy3
channel prmy3: SID=1344 device type=DISK

allocated channel: prmy4
channel prmy4: SID=6 device type=DISK

allocated channel: stby
channel stby: SID=91 device type=DISK

Starting Duplicate Db at 23-DEC-12

contents of Memory Script.:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2/db_1/dbs/orapwbeijing' auxiliary format
 '/u01/app/oracle/product/11.2/db_1/dbs/orapwshanghai'   targetfile
 '/u01/app/oracle/product/11.2/db_1/dbs/spfilebeijing.ora' auxiliary format
 '/u01/app/oracle/product/11.2/db_1/dbs/spfileshanghai.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2/db_1/dbs/spfileshanghai.ora''";
}
executing Memory Script

Starting backup at 23-DEC-12
Finished backup at 23-DEC-12

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

contents of Memory Script.:
{
   sql clone "alter system set  audit_file_dest = ''/u01/app/oracle/admin/shanghai/adump'' comment='''' scope=spfile";
   sql clone "alter system set  dispatchers = ''(PROTOCOL=TCP) (SERVICE=shanghaiXDB)'' comment='''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 = ''location=/archivelog/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=shanghai'' comment='''' scope=spfile";
   sql clone "alter system set  db_unique_name = ''shanghai'' comment='''' scope=spfile";
   sql clone "alter system set  db_file_name_convert = ''/beijing/'', ''/shanghai/'' comment='''' scope=spfile";
   sql clone "alter system set  log_file_name_convert = ''/beijing/'', ''/shanghai/'' comment='''' scope=spfile";
   sql clone "alter system set  control_files = ''/u01/app/oracle/oradata/control01.ctl'' comment='''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes = 5 comment='''' scope=spfile";
   sql clone "alter system set  fal_client = ''shanghai'' comment='''' scope=spfile";
   sql clone "alter system set  fal_server = ''beijing'' comment='''' scope=spfile";
   sql clone "alter system set  standby_file_management = ''AUTO'' comment='''' scope=spfile";
   sql clone "alter system set  log_archive_config = ''dg_config=(chicago,boston)'' comment='''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 = ''service=beijing ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=beijing'' comment=''''

scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/shanghai/adump'' comment= '''' scope=spfile
sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=shanghaiXDB)'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_dest_1 =  ''location=/archivelog/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=shanghai'' comment= '''' scope=spfile
sql statement: alter system set  db_unique_name =  ''shanghai'' comment= '''' scope=spfile
sql statement: alter system set  db_file_name_convert =  ''/beijing/'', ''/shanghai/'' comment= '''' scope=spfile
sql statement: alter system set  log_file_name_convert =  ''/beijing/'', ''/shanghai/'' comment= '''' scope=spfile
sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile
sql statement: alter system set  fal_client =  ''shanghai'' comment= '''' scope=spfile
sql statement: alter system set  fal_server =  ''beijing'' comment= '''' scope=spfile
sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_config =  ''dg_config=(chicago,boston)'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_dest_2 =  ''service=beijing ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=beijing'' comment= ''''

scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2231128 bytes
Variable Size                574620840 bytes
Database Buffers             255852544 bytes
Redo Buffers                   6578176 bytes
allocated channel: stby
channel stby: SID=958 device type=DISK

contents of Memory Script.:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/control01.ctl';
}
executing Memory Script

Starting backup at 23-DEC-12
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2/db_1/dbs/snapcf_beijing.f tag=TAG20121223T163128 RECID=2 STAMP=802801888
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-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 "/u01/app/oracle/oradata/shanghai/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to "/u01/app/oracle/oradata/shanghai/system01.dbf";
   set newname for datafile  2 to "/u01/app/oracle/oradata/shanghai/sysaux01.dbf";
   set newname for datafile  3 to "/u01/app/oracle/oradata/shanghai/undotbs01.dbf";
   set newname for datafile  4 to "/u01/app/oracle/oradata/shanghai/users01.dbf";
   set newname for datafile  5 to "/u01/app/oracle/oradata/shanghai/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format "/u01/app/oracle/oradata/shanghai/system01.dbf"  
   datafile  2 auxiliary format "/u01/app/oracle/oradata/shanghai/sysaux01.dbf"  
   datafile  3 auxiliary format "/u01/app/oracle/oradata/shanghai/undotbs01.dbf"  
   datafile  4 auxiliary format "/u01/app/oracle/oradata/shanghai/users01.dbf"  
   datafile  5 auxiliary format "/u01/app/oracle/oradata/shanghai/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 23-DEC-12
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/beijing/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/beijing/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/beijing/example01.dbf
channel prmy4: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/beijing/undotbs01.dbf
output file name=/u01/app/oracle/oradata/shanghai/undotbs01.dbf tag=TAG20121223T163134
channel prmy4: datafile copy complete, elapsed time: 00:00:07
channel prmy4: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/beijing/users01.dbf
output file name=/u01/app/oracle/oradata/shanghai/example01.dbf tag=TAG20121223T163134
channel prmy3: datafile copy complete, elapsed time: 00:00:09
output file name=/u01/app/oracle/oradata/shanghai/users01.dbf tag=TAG20121223T163134
channel prmy4: datafile copy complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/shanghai/system01.dbf tag=TAG20121223T163134
channel prmy1: datafile copy complete, elapsed time: 00:00:15
output file name=/u01/app/oracle/oradata/shanghai/sysaux01.dbf tag=TAG20121223T163134
channel prmy2: datafile copy complete, elapsed time: 00:00:15
Finished backup at 23-DEC-12

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=2 STAMP=802801851 file name=/u01/app/oracle/oradata/shanghai/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=802801851 file name=/u01/app/oracle/oradata/shanghai/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=802801851 file name=/u01/app/oracle/oradata/shanghai/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=802801851 file name=/u01/app/oracle/oradata/shanghai/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=802801851 file name=/u01/app/oracle/oradata/shanghai/example01.dbf
Finished Duplicate Db at 23-DEC-12
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
複製結束。


四、連線到備庫啟動 MRP (Managed Recovery Process)
[oracle@shanghai ~]$ sqlplus / as sysdba;
SQL> alter database recover managed standby database disconnect;
Database altered.
如果需要啟用實時應用，則應該：
SQL> alter database recover managed standby database using current logfile disconnect;

五、如果有Active Dataguard (ADG)的license，可以以read only模式開啟備庫，並開啟恢復：
[oracle@shanghai ~]$ sqlplus / as sysdba;
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect;
或者啟用實時應用：
SQL> alter database recover managed standby database using current logfile disconnect;