打算寫一系列的文章介紹11g的新特性和變化。

Oracle11g在備份和恢復方面新增了很多的功能，無論是效能、功能性、安全性和可操作性方面都有了不同程度的提高。

這一篇介紹DUPLICATE語法的增強。

Oracle11新特性——備份恢復功能增強（一）：http://yangtingkun.itpub.net/post/468/412991

Oracle11新特性——備份恢復功能增強（二）：http://yangtingkun.itpub.net/post/468/414647

Oracle11新特性——備份恢復功能增強（三）：http://yangtingkun.itpub.net/post/468/414834

Oracle11新特性——備份恢復功能增強（四）：http://yangtingkun.itpub.net/post/468/414941

Oracle11新特性——備份恢復功能增強（五）：http://yangtingkun.itpub.net/post/468/416015

Oracle11新特性——備份恢復功能增強（六）：http://yangtingkun.itpub.net/post/468/423531

Oracle在11g中增強了DUPLICATE語法的功能，在使用DUPLICATE資料庫或建立STANDBY資料庫的時候，不再需要任何備份。也就是說，在目標資料庫伺服器上和本地資料庫伺服器上都不需要存在任何的備份，Oracle直接利用源資料庫來生成目標資料庫，Oracle把這種方法叫做active database duplication。

下面這個測試是在虛擬機器上進行的，兩臺伺服器都沒有足夠的空間存放備份，這時候，DUPLICATE新特性的優勢就體現出來了。

利用11g提供的DUPLICATE功能，使得STANDBY建立過程簡化很多，只需要在STANDBY資料庫設定一個簡單的init.ora檔案，將STANDBY資料庫例項啟動起來就可以了：

[oracle@yangtk2 ~]$ echo 'db_name=ora11g' >> initora11g.ora
[oracle@yangtk2 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Nov 29 13:25:44 2007

Connected to an idle instance.

SQL> startup nomount pfile=initora11g.ora
ORACLE instance started.

Total System Global Area 146472960 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 2564096 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@yangtk2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 29-NOV-2007 13:26:02

Starting /data/oracle/product/11.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /data/oracle/product/11.1/network/admin/listener.ora
Log messages written to /data/oracle/diag/tnslsnr/yangtk2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.4.70)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.25.4.70)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 29-NOV-2007 13:26:02
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /data/oracle/product/11.1/network/admin/listener.ora
Listener Log File /data/oracle/diag/tnslsnr/yangtk2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.4.70)(PORT=1521)))
The listener supports no services
The command completed successfully

根據主庫的目錄結構在STANDBY資料庫伺服器建立相應的目錄。

在執行DUPLICATE之前，首先設定PRIMARY資料庫的FORCE LOGGING和DATA GUARD相關的初始化引數：

[oracle@yangtk ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Nov 7 03:42:16 2007

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(ora11g_p,ora11g_s)';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/data/oracle/oradata/ora11g/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11g_p' SCOPE = SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=ora11g_s ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g_s';

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER = ora11g_s;

System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT = ora11g_p;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO;

System altered.

下面登陸主站點，利用RMAN執行DUPLICATE命令：

[oracle@yangtk ~]$ rman target / auxiliary sys/test@172.25.4.70/ora11g

Recovery Manager: Release 11.1.0.6.0 - Production on Wed Nov 7 05:35:27 2007

connected to target database: ORA11G (DBID=4026820313)
connected to auxiliary database: ORA11G (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY
2> DORECOVER FROM ACTIVE DATABASE NOFILENAMECHECK
3> SPFILE SET DB_UNIQUE_NAME 'ora11g_s'
4> SET FAL_CLIENT 'ora11g_s'
5> SET FAL_SERVER 'ora11g_p'
6> SET LOG_ARCHIVE_DEST_1
7> 'LOCATION=/data/oracle/oradata/ora11g/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11g_s'
8> SET LOG_ARCHIVE_DEST_2
9> 'SERVICE=ora11g_p ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g_p'
10> SET CONTROL_FILES '/data/oracle/oradata/ora11g/ORA11G_P/controlfile/control01.ctl',
11> '/data/oracle/flash_recovery_area/ORA11G_P/controlfile/control02.ctl'
12> ;

Starting Duplicate Db at 07-NOV-07
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK

contents of Memory Script:
{
backup as copy reuse
file '/data/oracle/product/11.1/dbs/orapwora11g' auxiliary format
'/data/oracle/product/11.1/dbs/orapwora11g' file
'/data/oracle/product/11.1/dbs/spfileora11g.ora' auxiliary format
'/data/oracle/product/11.1/dbs/spfileora11g.ora' ;
sql clone "alter system set spfile= ''/data/oracle/product/11.1/dbs/spfileora11g.ora''";
}
executing Memory Script

Starting backup at 07-NOV-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
Finished backup at 07-NOV-07

sql statement: alter system set spfile= ''/data/oracle/product/11.1/dbs/spfileora11g.ora''

contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''ora11g_s'' comment=
'''' scope=spfile";
sql clone "alter system set FAL_CLIENT =
''ora11g_s'' comment=
'''' scope=spfile";
sql clone "alter system set FAL_SERVER =
''ora11g_p'' comment=
'''' scope=spfile";
sql clone "alter system set LOG_ARCHIVE_DEST_1 =
''LOCATION=/data/oracle/oradata/ora11g/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11g_s'' comment=
'''' scope=spfile";
sql clone "alter system set LOG_ARCHIVE_DEST_2 =
''SERVICE=ora11g_p ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g_p'' comment=
'''' scope=spfile";
sql clone "alter system set CONTROL_FILES =
''/data/oracle/oradata/ora11g/ORA11G_P/controlfile/control01.ctl'', ''/data/oracle/flash_recovery_area/ORA11G_P/controlfile/control02.ctl'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

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

sql statement: alter system set FAL_CLIENT = ''ora11g_s'' comment= '''' scope=spfile

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

sql statement: alter system set LOG_ARCHIVE_DEST_1 = ''LOCATION=/data/oracle/oradata/ora11g/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora11g_s'' comment= '''' scope=spfile

sql statement: alter system set LOG_ARCHIVE_DEST_2 = ''SERVICE=ora11g_p ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora11g_p'' comment= '''' scope=spfile

sql statement: alter system set CONTROL_FILES = ''/data/oracle/oradata/ora11g/ORA11G_P/controlfile/control01.ctl'', ''/data/oracle/flash_recovery_area/ORA11G_P/controlfile/control02.ctl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 267825152 bytes

Fixed Size 1299316 bytes
Variable Size 167775372 bytes
Database Buffers 96468992 bytes
Redo Buffers 2281472 bytes

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/data/oracle/oradata/ora11g/ORA11G_P/controlfile/control01.ctl';
restore clone controlfile to '/data/oracle/flash_recovery_area/ORA11G_P/controlfile/control02.ctl' from
'/data/oracle/oradata/ora11g/ORA11G_P/controlfile/control01.ctl';
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 07-NOV-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/data/oracle/product/11.1/dbs/snapcf_ora11g.f tag=TAG20071107T053542 RECID=5 STAMP=637997751
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 07-NOV-07

Starting restore at 07-NOV-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK

channel ORA_DISK_1: no AUTOBACKUP in 7 days found
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel clone_default: copied control file copy
Finished restore at 07-NOV-07

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_temp_3m11bo2t_.tmp";
switch clone tempfile all;
set newname for datafile 1 to
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_system_3d3795py_.dbf";
set newname for datafile 2 to
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_sysaux_3d379f1s_.dbf";
set newname for datafile 3 to
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_undotbs1_3d379k48_.dbf";
set newname for datafile 4 to
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf";
set newname for datafile 5 to
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_yangtk_3d3kn7k4_.dbf";
set newname for datafile 6 to
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_yangtk_3g73q02m_.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_system_3d3795py_.dbf" datafile
2 auxiliary format
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_sysaux_3d379f1s_.dbf" datafile
3 auxiliary format
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_undotbs1_3d379k48_.dbf" datafile
4 auxiliary format
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf" datafile
5 auxiliary format
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_yangtk_3d3kn7k4_.dbf" datafile
6 auxiliary format
"/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_yangtk_3g73q02m_.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_temp_3m11bo2t_.tmp in control file

executing command: SET NEWNAME

Starting backup at 07-NOV-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_system_3d3795py_.dbf
output file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_system_3d3795py_.dbf tag=TAG20071107T053611 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_sysaux_3d379f1s_.dbf
output file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_sysaux_3d379f1s_.dbf tag=TAG20071107T053611 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_undotbs1_3d379k48_.dbf
output file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_undotbs1_3d379k48_.dbf tag=TAG20071107T053611 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_yangtk_3d3kn7k4_.dbf
output file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_yangtk_3d3kn7k4_.dbf tag=TAG20071107T053611 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_yangtk_3g73q02m_.dbf
output file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_yangtk_3g73q02m_.dbf tag=TAG20071107T053611 RECID=0 STAMP=0
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=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf
output file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf tag=TAG20071107T053611 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 07-NOV-07

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like "/data/oracle/oradata/ora11g/archivelog/1_234_630620185.dbf" auxiliary format
"/data/oracle/oradata/ora11g/archivelog/1_234_630620185.dbf" ;
catalog clone archivelog "/data/oracle/oradata/ora11g/archivelog/1_234_630620185.dbf";
switch clone datafile all;
}
executing Memory Script

Starting backup at 07-NOV-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=234 RECID=340 STAMP=637998112
output file name=/data/oracle/oradata/ora11g/archivelog/1_234_630620185.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 07-NOV-07

cataloged archived log
archived log file name=/data/oracle/oradata/ora11g/archivelog/1_234_630620185.dbf RECID=1 STAMP=639940971

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=639940972 file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_system_3d3795py_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=639940972 file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_sysaux_3d379f1s_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=639940972 file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_undotbs1_3d379k48_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=639940972 file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_users_3d37bq45_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=639940972 file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_yangtk_3d3kn7k4_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=639940972 file name=/data/oracle/oradata/ora11g/ORA11G_P/datafile/o1_mf_yangtk_3g73q02m_.dbf

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

executing command: SET until clause

Starting recover at 07-NOV-07
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 234 is already on disk as file /data/oracle/oradata/ora11g/archivelog/1_234_630620185.dbf
archived log file name=/data/oracle/oradata/ora11g/archivelog/1_234_630620185.dbf thread=1 sequence=234
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-NOV-07
Finished Duplicate Db at 07-NOV-07

下面登陸STANDBY資料庫，並開始應用日誌：

[oracle@yangtk2 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Nov 29 17:43:07 2007

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database recover managed standby database disconnect from session;

Database altered.

在建立STANDBY的過程需要注意：

把密碼檔案從PRIMARY資料庫複製到STANDBY資料庫，否則執行DUPLICATE命令的時候會出現錯誤：

ORA-17627: ORA-01017: invalid username/password; logon denied

為STANDBY資料庫指定新的控制檔案位置，否則執行DUPLICATE命令的時候會出現錯誤：

ORA-19607: /data/oracle/oradata/ora11g/ORA11G_P/controlfile/o1_mf_3d378sn2_.ctl is an active control file

Oracle11g的新特性使得建立STANDBY資料庫變得簡化得多了，不再需要建立備份，不需要複製SPFILE、CONTROLFILE。

Oracle11新特性——備份恢復功能增強（七）

相關文章