系统环境： Linux x86_64
数据库版本：10.2.0.1.0
源库：orcl
目标库：yqldb
步骤如下：
（1）构建辅助数据库目录结构
（2）根据源库的initorcl.ora 创建辅助数据库初始化参数文件inityqldb.ora
（3）利用源库的密码文件创建辅助实例口令文件直接拷贝
（4）配置监听listener.ora 和 tnsnames.ora文件
（5）RMAN 备份源数据库
（6）启动辅助库至nomount 状态
（7）使用RMAN duplicate功能复制并创建目标数据库
（8）创建目标库的spfile
1）构建辅助数据库目录结构
oracle@yangDB1:/home/oracle>cd /opt/oracle/
oracle@yangDB1:/opt/oracle>mkdir yqldb_arch --创建归档地址文件目录
oracle@yangDB1:/opt/oracle>ls
10.2.0 admin extapi flash_recovery_area oradata oraInventory pri_arch std_arch yqldb_arch
oracle@yangDB1:/opt/oracle>cd oradata/
oracle@yangDB1:/opt/oracle/oradata>mkdir yqldb
oracle@yangDB1:/opt/oracle/oradata>ls
orcl yqldb
oracle@yangDB1:/opt/oracle/admin>ls
orcl
oracle@yangDB1:/opt/oracle/admin>mkdir yqldb
oracle@yangDB1:/opt/oracle/admin>ls
orcl yqldb
oracle@yangDB1:/opt/oracle/admin>cd orcl
oracle@yangDB1:/opt/oracle/admin/orcl>ls
adump bdump cdump dpdump pfile udump
oracle@yangDB1:/opt/oracle/admin/orcl>cd ../yqldb
oracle@yangDB1:/opt/oracle/admin/yqldb>mkdir adump bdump cdump dpdump pfile udump
oracle@yangDB1:/opt/oracle/admin/yqldb>ls
adump bdump cdump dpdump pfile udump
oracle@yangDB1:/opt/oracle/admin/yqldb>cd $ORACLE_HOME/dbs
2）根据源库的initorcl.ora 创建辅助数据库初始化参数文件inityqldb.ora
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs>cp init.ora inityqldb.ora
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs>vim inityqldb.ora
orcl.__db_cache_size=889192448
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__shared_pool_size=285212672
orcl.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/yqldb/adump'
*.background_dump_dest='/opt/oracle/admin/yqldb/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/oracle/oradata/yqldb/control01.ctl','/opt/oracle/oradata/yqldb/control02.ctl','/opt/oracle/ora
data/yqldb/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/yqldb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='yqldb'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/opt/oracle/yqldb_arch'
*.open_cursors=300
*.pga_aggregate_target=402653184
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1210056704
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/yqldb/udump'
*.db_file_name_convert = ('/opt/oracle/oradata/orcl','/opt/oracle/oradata/yqldb')
*.log_file_name_convert = ('/opt/oracle/oradata/orcl','/opt/oracle/oradata/yqldb')
3）利用源库的密码文件创建辅助实例口令文件直接拷贝,也可以手工创建密码文件
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs>cp orapworcl orapwyqldb
###orapwd file=$ORACLE_HOME/dbs/orapwORACLE_SID password=XXXX
4）修改listener.ora 中的内容添加蓝色内容，建议做lsnrctl status 测试！
oracle@yangDB1:/opt/oracle/10.2.0/orcl/network/admin>vim listener.ora
# listener.ora Network Configuration File: /opt/oracle/10.2.0/orcl/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/10.2.0/orcl)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME = yqldb)
(ORACLE_HOME = /opt/oracle/10.2.0/orcl)
(SID_NAME = yqldb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1 )(PORT = 1521))
)
)
5）备份源数据库。
RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
BACKUP FORMAT '/home/oracle/backup/rman、orcl_%U_%T' skip inaccessible filesperset 5 DATABASE TAG orcl_hot_db_bk;
sql 'alter system archive log current';
BACKUP FORMAT '/home/oracle/backup/rman、arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;
backup current controlfile tag='bak_ctlfile' format='/home/oracle/backup/rman/ctl_file_%U_%T';
backup spfile tag='spfile' format='/home/oracle/backup/rman/ORCL_spfile_%U_%T';
release channel c2;
release channel c1;
}
6）启动辅助库至nomount状态
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs>export ORACLE_SID=yqldb
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 18:28:17 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> startup nomount pfile=/opt/oracle/10.2.0/orcl/dbs/inityqldb.ora
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2020384 bytes
Variable Size 318770144 bytes
Database Buffers 889192448 bytes
Redo Buffers 14753792 bytes
SQL> exit --一定要退出，否则后面rman duplicate时会卡住。
7）使用RMAN duplicate功能复制并创建目标数据库
oracle@yangDB1:/home/oracle>export ORACLE_SID=orcl
oracle@yangDB1:/home/oracle>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Aug 17 18:29:40 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1286259285)
RMAN> connect auxiliary sys/yang@yqldb
connected to auxiliary database: YQLDB (not mounted)
RMAN> duplicate target database to yqldb;
Starting Duplicate Db at 17-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
contents of Memory Script.:
{
set until scn 1271492;
set newname for datafile 1 to
"/opt/oracle/oradata/yqldb/system01.dbf";
set newname for datafile 2 to
"/opt/oracle/oradata/yqldb/undotbs01.dbf";
set newname for datafile 3 to
"/opt/oracle/oradata/yqldb/sysaux01.dbf";
set newname for datafile 4 to
"/opt/oracle/oradata/yqldb/users01.dbf";
set newname for datafile 5 to
"/opt/oracle/oradata/yqldb/example01.dbf";
restore
check readonly
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 17-AUG-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /opt/oracle/oradata/yqldb/undotbs01.dbf
restoring datafile 00004 to /opt/oracle/oradata/yqldb/users01.dbf
restoring datafile 00005 to /opt/oracle/oradata/yqldb/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、orcl_0fmk82fk_1_1_20110817
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/yqldb/system01.dbf
restoring datafile 00003 to /opt/oracle/oradata/yqldb/sysaux01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、orcl_0emk82fk_1_1_20110817
failover to previous backup
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/yqldb/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/yqldb/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/yqldb/sysaux01.dbf
restoring datafile 00004 to /opt/oracle/oradata/yqldb/users01.dbf
restoring datafile 00005 to /opt/oracle/oradata/yqldb/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman/orcl_0cmjnr7e_1_1_%S.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/rman/orcl_0cmjnr7e_1_1_%S.bak tag=TAG20110811T140638
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 17-AUG-11
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "YQLDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/opt/oracle/oradata/yqldb/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/opt/oracle/oradata/yqldb/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/opt/oracle/oradata/yqldb/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/opt/oracle/oradata/yqldb/system01.dbf'
CHARACTER SET ZHS16GBK
contents of Memory Script.:
{
switch clone datafile all;
}
executing Memory Script
released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=759436245 filename=/opt/oracle/oradata/yqldb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=759436245 filename=/opt/oracle/oradata/yqldb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=759436245 filename=/opt/oracle/oradata/yqldb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=759436245 filename=/opt/oracle/oradata/yqldb/example01.dbf
contents of Memory Script.:
{
set until scn 1271492;
recover
clone database
delete archivelog;
}
executing Memory Script
executing command: SET until clause
Starting recover at 17-AUG-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
starting media recovery
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=23
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=24
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=25
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=26
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、arch_0jmk82gu_1_1_20110817
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/rman、arch_0jmk82gu_1_1_20110817 tag=TAG20110817T174917
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=18
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=19
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=20
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=21
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=22
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、arch_0imk82gu_1_1_20110817
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/rman、arch_0imk82gu_1_1_20110817 tag=TAG20110817T174917
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
archive log filename=/opt/oracle/yqldb_arch/1_18_758478551.dbf thread=1 sequence=18
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_18_758478551.dbf recid=5 stamp=759436252
archive log filename=/opt/oracle/yqldb_arch/1_19_758478551.dbf thread=1 sequence=19
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_19_758478551.dbf recid=6 stamp=759436257
archive log filename=/opt/oracle/yqldb_arch/1_20_758478551.dbf thread=1 sequence=20
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_20_758478551.dbf recid=7 stamp=759436257
archive log filename=/opt/oracle/yqldb_arch/1_21_758478551.dbf thread=1 sequence=21
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_21_758478551.dbf recid=8 stamp=759436257
archive log filename=/opt/oracle/yqldb_arch/1_22_758478551.dbf thread=1 sequence=22
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_22_758478551.dbf recid=9 stamp=759436257
archive log filename=/opt/oracle/yqldb_arch/1_23_758478551.dbf thread=1 sequence=23
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_23_758478551.dbf recid=2 stamp=759436249
archive log filename=/opt/oracle/yqldb_arch/1_24_758478551.dbf thread=1 sequence=24
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_24_758478551.dbf recid=3 stamp=759436249
archive log filename=/opt/oracle/yqldb_arch/1_25_758478551.dbf thread=1 sequence=25
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_25_758478551.dbf recid=4 stamp=759436249
archive log filename=/opt/oracle/yqldb_arch/1_26_758478551.dbf thread=1 sequence=26
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_26_758478551.dbf recid=1 stamp=759436249
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=32
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、arch_0lmk82he_1_1_20110817
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/rman、arch_0lmk82he_1_1_20110817 tag=TAG20110817T174917
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=27
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=28
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=29
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=30
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=31
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/rman、arch_0kmk82he_1_1_20110817
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/rman、arch_0kmk82he_1_1_20110817 tag=TAG20110817T174917
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
archive log filename=/opt/oracle/yqldb_arch/1_27_758478551.dbf thread=1 sequence=27
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_27_758478551.dbf recid=15 stamp=759436273
archive log filename=/opt/oracle/yqldb_arch/1_28_758478551.dbf thread=1 sequence=28
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_28_758478551.dbf recid=13 stamp=759436273
archive log filename=/opt/oracle/yqldb_arch/1_29_758478551.dbf thread=1 sequence=29
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_29_758478551.dbf recid=14 stamp=759436273
archive log filename=/opt/oracle/yqldb_arch/1_30_758478551.dbf thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_30_758478551.dbf recid=12 stamp=759436272
archive log filename=/opt/oracle/yqldb_arch/1_31_758478551.dbf thread=1 sequence=31
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_31_758478551.dbf recid=11 stamp=759436269
archive log filename=/opt/oracle/yqldb_arch/1_32_758478551.dbf thread=1 sequence=32
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/yqldb_arch/1_32_758478551.dbf recid=10 stamp=759436268
media recovery complete, elapsed time: 00:00:11
Finished recover at 17-AUG-11
contents of Memory Script.:
{
shutdown clone;
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 1224736768 bytes
Fixed Size 2020384 bytes
Variable Size 318770144 bytes
Database Buffers 889192448 bytes
Redo Buffers 14753792 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "YQLDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/opt/oracle/oradata/yqldb/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/opt/oracle/oradata/yqldb/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/opt/oracle/oradata/yqldb/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/opt/oracle/oradata/yqldb/system01.dbf'
CHARACTER SET ZHS16GBK
contents of Memory Script.:
{
set newname for tempfile 1 to
"/opt/oracle/oradata/yqldb/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/opt/oracle/oradata/yqldb/undotbs01.dbf";
catalog clone datafilecopy "/opt/oracle/oradata/yqldb/sysaux01.dbf";
catalog clone datafilecopy "/opt/oracle/oradata/yqldb/users01.dbf";
catalog clone datafilecopy "/opt/oracle/oradata/yqldb/example01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /opt/oracle/oradata/yqldb/temp01.dbf in control file
cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/yqldb/undotbs01.dbf recid=1 stamp=759436323
cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/yqldb/sysaux01.dbf recid=2 stamp=759436323
cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/yqldb/users01.dbf recid=3 stamp=759436323
cataloged datafile copy
datafile copy filename=/opt/oracle/oradata/yqldb/example01.dbf recid=4 stamp=759436323
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=759436323 filename=/opt/oracle/oradata/yqldb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=759436323 filename=/opt/oracle/oradata/yqldb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=759436323 filename=/opt/oracle/oradata/yqldb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=759436323 filename=/opt/oracle/oradata/yqldb/example01.dbf
contents of Memory Script.:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 17-AUG-11
8）--创建新的spfile文件,去掉如下两个参数
#*.db_file_name_convert = ('/opt/oracle/oradata/orcl','/opt/oracle/oradata/yqldb')
#*.log_file_name_convert = ('/opt/oracle/oradata/orcl','/opt/oracle/oradata/yqldb')
数据库已经开启，进行数据验证。
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs>export ORACLE_SID=yqldb
oracle@yangDB1:/opt/oracle/10.2.0/orcl/dbs>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 17 18:33:23 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/yang@yqldb as sysdba
Connected.
SQL> create spfile from pfile='/opt/oracle/10.2.0/orcl/dbs/inityqldb.ora';
File created.
SQL> col tname for a15
SQL> col tabletype for a10
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
--------------- --------------------- ----------
YANGB TABLE
YANGTAB TABLE
YANGOBJ TABLE
YANGUSER TABLE
OBJECTS TABLE
A TABLE
6 rows selected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------
yqldb
SQL>