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
{
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。
----------
在建立備庫時最後報出的錯誤警告資訊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;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-1770482/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 11g RMAN新特性 active database duplication createing standbyDatabase
- 11g 新特性—— Active Database DuplicationDatabase
- 11g rman新特性 duplicate target database for standby from active databaseDatabase
- 11g RMAN新特性 active database duplication 複製資料庫Database資料庫
- Oracle Active database duplicationOracleDatabase
- 使用oracle 11g rman新特性 duplicate target database for standby from active database 建立物理dataguard並開啟RealOracleDatabase
- Oracle 11g Data Guard 使用 duplicate from active database 建立 standby databaseOracleDatabase
- Oracle 11g Data Guard 使用duplicate from active database 建立 standby databaseOracleDatabase
- 11g Active Standby Database Automatic Block Corruption RepairDatabaseBloCAI
- 【DataGuard】Oracle 11g DataGuard 新特性之 Snapshot Standby DatabaseOracleDatabase
- 11g RMAN新特性active database duplicate 資料庫異構Database資料庫
- Oracle 11g Rman Active database duplicateOracleDatabase
- oracle 11gr2 活動資料庫複製(active database duplication)Oracle資料庫Database
- ORA-01555 on Active Data Guard Standby DatabaseDatabase
- standby database to primary database.Database
- standby databaseDatabase
- Convert a Physical Standby Database into a Snapshot Standby DatabaseDatabase
- Physical Standby Database 切換到 Snapshot Standby DatabaseDatabase
- 11g新特性--active dataguard
- Creating a Physical Standby using RMAN DUPLICATE FROM ACTIVE DATABASEDatabase
- Standby Database ---09Database
- Create RAC Standby Database for RAC Primary DatabaseDatabase
- Duplicating an Active DatabaseDatabase
- Oracle 11g Data Guard 使用duplicate from active databaseOracleDatabase
- 搭建11g data guard(duplicate from active database方式)Database
- standby database No RFS 程式Database
- Rman backup standby databaseDatabase
- Standby Database for reportDatabase
- 聊聊Oracle 11g的Snapshot Standby Database（上）OracleDatabase
- 聊聊Oracle 11g的Snapshot Standby Database（下）OracleDatabase
- 【DataGuard】11g 新特性：Active Data Guard
- 【DataGuard】Oracle 11g DataGuard 新特性之 Active Standby：Real-Time Apply+QueryOracleAPP
- Standby Database的工作原理Database
- 建立 Logical Standby DatabaseDatabase
- manage logical standby databaseDatabase
- How a Standby Database Is Mounted (295)Database
- 1 Oracle Database 19c 新特性OracleDatabase
- Oracle10G RMAN Database Duplication (Doc ID 259694.1)OracleDatabase