--环境:
--目标数据库: 192.168.7.25/sybo3 /u01/database/sybo3 主机名:linux3
--辅助数据库: 192.168.7.26/sybo3 /u01/database/sybo3 主机名:linux4
目标数据库与辅助数据库使用相同的文件位置
[oracle@linux3 database]$ cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
- 备份及scp目标数据库
目标数据库存在的表及其内容,用于后续验证
SQL> select * from t;
NAME ACTION
---------- --------------------
Robinson Transfer DB
Jackson Transfer DB by rman
$ export ORACLE_SID=sybo3
$ rman target /
RMAN> backup database include current controlfile plus archivelog delete input;
$ scp -r fra 192.168.7.26:/u01/database/sybo3/ -->如果有必要将将备份文件打包
如果没有pfile文件,可从target db生成: create pfile from spfile;
$ scp $ORACLE_HOME/dbs/initsybo3.ora 192.168.7.26:$ORACLE_HOME/dbs
- 创建与源库相同目录
$ more sybo3.sh
#!/bin/sh
mkdir -p /u01/database
mkdir -p /u01/database/sybo3/adump
mkdir -p /u01/database/sybo3/controlf
mkdir -p /u01/database/sybo3/fra
mkdir -p /u01/database/sybo3/oradata
mkdir -p /u01/database/sybo3/redo
mkdir -p /u01/database/sybo3/dpdump
mkdir -p /u01/database/sybo3/pfile
$ ./sybo3.sh
- 配置辅助实例参数文件
$ more /u01/oracle/db_1/dbs/initsybo3.ora
sybo3.__db_cache_size=113246208
sybo3.__java_pool_size=4194304
sybo3.__large_pool_size=4194304
sybo3.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment
sybo3.__pga_aggregate_target=150994944
sybo3.__sga_target=226492416
sybo3.__shared_io_pool_size=0
sybo3.__shared_pool_size=96468992
sybo3.__streams_pool_size=0
*.audit_file_dest='/u01/database/sybo3/adump/'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/database/sybo3/controlf/control01.ctl','/u01/database/sybo3/controlf/control02.ctl'
*.db_block_size=8192
*.db_domain='orasrv.com'
*.db_name='sybo3'
*.db_recovery_file_dest='/u01/database/sybo3/fra'
*.db_recovery_file_dest_size=4039114752
*.dg_broker_config_file1='/u01/database/sybo3/db_broker/dr1sybo3.dat'
*.dg_broker_config_file2='/u01/database/sybo3/db_broker/dr2sybo3.dat'
*.dg_broker_start=FALSE
*.diagnostic_dest='/u01/database/sybo3'
*.log_archive_dest_1=''
*.memory_target=374341632
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
- 生成辅助实例密码文件
$ orapwd file=/u01/oracle/db_1/dbs/orapwsybo3 password=oracle entries=10
- 实施数据库克隆
启动辅助库到nomount并使用RMAN连接
$ export ORACLE_SID=sybo3
$ sqlplus / as sysdba
SQL> startup nomount;
$ rman auxiliary /
connected to auxiliary database: SYBO3 (not mounted)
RMAN> duplicate target database to sybo3 spfile backup location '/u01/database/sybo3/fra/SYBO3' nofilenamecheck;
Starting Duplicate Db at 31-JUL-13
contents of Memory Script:
{
restore clone spfile to '/u01/oracle/db_1/dbs/spfilesybo3.ora' from --->从自动备份还原spfile文件
'/u01/database/sybo3/fra/SYBO3/autobackup/2013_07_31/o1_mf_s_822220850_8zjy9lp8_.bkp';
sql clone "alter system set spfile= ''/u01/oracle/db_1/dbs/spfilesybo3.ora''";
}
executing Memory Script
Starting restore at 31-JUL-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /u01/database/sybo3/fra/SYBO3/autobackup/2013_07_31/o1_mf_s_822220850_8zjy9lp8_.bkp
channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 31-JUL-13
sql statement: alter system set spfile= ''/u01/oracle/db_1/dbs/spfilesybo3.ora''
contents of Memory Script:
{
sql clone "alter system set db_name = --->修改spfile并重启辅助实例
''SYBO3'' comment=
''duplicate'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''SYBO3'' comment= ''duplicate'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 376635392 bytes
Fixed Size 1336652 bytes
Variable Size 285215412 bytes
Database Buffers 83886080 bytes
Redo Buffers 6197248 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''SYBO3'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''SYBO3'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount -->下面的命令用于还原控制文件
restore clone primary controlfile from '/u01/database/sybo3/fra/SYBO3/autobackup/2013_07_31/o1_mf_s_822220850_8zjy9lp8_.bkp';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''SYBO3'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''SYBO3'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 376635392 bytes
Fixed Size 1336652 bytes
Variable Size 285215412 bytes
Database Buffers 83886080 bytes
Redo Buffers 6197248 bytes
Starting restore at 31-JUL-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/database/sybo3/controlf/control01.ctl
output file name=/u01/database/sybo3/controlf/control02.ctl
Finished restore at 31-JUL-13
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script:
{
set until scn 1177881; --->设置时点恢复scn,修改datafile name.
set newname for datafile 1 to
"/u01/database/sybo3/oradata/system01.dbf";
set newname for datafile 2 to
"/u01/database/sybo3/oradata/sysaux01.dbf";
set newname for datafile 3 to
"/u01/database/sybo3/oradata/undotbs01.dbf";
set newname for datafile 4 to
"/u01/database/sybo3/oradata/users01.dbf";
set newname for datafile 5 to
"/u01/database/sybo3/oradata/example01.dbf";
restore --->该命令用于还原辅助数据库
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 31-JUL-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/database/sybo3/oradata/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/database/sybo3/oradata/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/database/sybo3/oradata/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/database/sybo3/oradata/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/database/sybo3/oradata/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/database/sybo3/fra/SYBO3/backupset/2013_07_31/o1_mf_nnndf_TAG20130731T103921_8zjy6s8p_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/database/sybo3/fra/SYBO3/backupset/2013_07_31/o1_mf_nnndf_TAG20130731T103921_8zjy6s8p_.bkp
tag=TAG20130731T103921
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 31-JUL-13
contents of Memory Script:
{
switch clone datafile all; --->更新数据文件信息到控制文件
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=822223969 file name=/u01/database/sybo3/oradata/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=822223969 file name=/u01/database/sybo3/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=822223969 file name=/u01/database/sybo3/oradata/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=822223969 file name=/u01/database/sybo3/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=822223969 file name=/u01/database/sybo3/oradata/example01.dbf
contents of Memory Script:
{
set until scn 1177881;
recover --->recover 辅助数据库以及删除日志,注是不完全恢复
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 31-JUL-13
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=41
channel ORA_AUX_DISK_1: reading from backup piece /u01/database/sybo3/fra/SYBO3/backupset/2013_07_31/o1_mf_annnn_TAG20130731T104049_8zjy9k8g_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/database/sybo3/fra/SYBO3/backupset/2013_07_31/o1_mf_annnn_TAG20130731T104049_8zjy9k8g_.bkp
tag=TAG20130731T104049
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_31/o1_mf_1_41_8zk1c4xq_.arc thread=1 sequence=41
channel clone_default: deleting archived log(s)
archived log file name=/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_31/o1_mf_1_41_8zk1c4xq_.arc RECID=1 STAMP=822223972
media recovery complete, elapsed time: 00:00:01
Finished recover at 31-JUL-13
contents of Memory Script:
{
shutdown clone immediate; --->相关参数修改
startup clone nomount;
sql clone "alter system set db_name =
''SYBO3'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 376635392 bytes
Fixed Size 1336652 bytes
Variable Size 285215412 bytes
Database Buffers 83886080 bytes
Redo Buffers 6197248 bytes
sql statement: alter system set db_name = ''SYBO3'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 376635392 bytes
Fixed Size 1336652 bytes
Variable Size 285215412 bytes
Database Buffers 83886080 bytes
Redo Buffers 6197248 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SYBO3" RESETLOGS ARCHIVELOG --->重建控制文件,隐式重置了dbid
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/u01/database/sybo3/oradata/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/database/sybo3/oradata/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/database/sybo3/oradata/sysaux01.dbf",
"/u01/database/sybo3/oradata/undotbs01.dbf",
"/u01/database/sybo3/oradata/users01.dbf",
"/u01/database/sybo3/oradata/example01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/database/sybo3/oradata/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u01/database/sybo3/oradata/sysaux01.dbf RECID=1 STAMP=822223994
cataloged datafile copy
datafile copy file name=/u01/database/sybo3/oradata/undotbs01.dbf RECID=2 STAMP=822223994
cataloged datafile copy
datafile copy file name=/u01/database/sybo3/oradata/users01.dbf RECID=3 STAMP=822223994
cataloged datafile copy
datafile copy file name=/u01/database/sybo3/oradata/example01.dbf RECID=4 STAMP=822223994
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=822223994 file name=/u01/database/sybo3/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=822223994 file name=/u01/database/sybo3/oradata/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=822223994 file name=/u01/database/sybo3/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=822223994 file name=/u01/database/sybo3/oradata/example01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 31-JUL-13
- 验证结果
$ sqlplus / as sysdba
SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ------------------------------
sybo3 linux4.orasrv.com
SQL> select * from t;
NAME ACTION
---------- --------------------
Robinson Transfer DB
Jackson Transfer DB by rman