ITPub博客

首页 > 数据库 > Oracle > oracle 10g通过 RMAN 克隆数据库(duplicate)

oracle 10g通过 RMAN 克隆数据库(duplicate)

原创 Oracle 作者:Toooooooo晓旭 时间:2015-07-30 21:29:36 0 删除 编辑

1 、做一些操作便于克隆后的验证
14:10:17 SYS@ prod>conn scott/tiger
Connected.
14:10:21 SCOTT@ prod>create table emp1 as select * from emp;

2 、备份目标数据库
[oracle@rh55 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 21 14:14:23 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=199802235)
//备份全库,所有的归档,当前的控制文件
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> backup full database format '/disk1/rman/%d_%s_%t.bak';
5> backup archivelog all format '/disk1/rman/arch_%s.bak';
6> backup current controlfile format '/disk1/rman/ctl_%s.bak';
7> }


3 、查看目标库数据文件存放信息
16:11:46 SYS@ prod>col member for a50
16:15:35 SYS@ prod>select GROUP#,MEMBER from v$logfile; // 查看目标库日志文件存放位置
GROUP# MEMBER
---------- --------------------------------------------------
3 /disk1/oradata/prod/redo03a.log
2 /disk1/oradata/prod/redo02a.log
1 /disk1/oradata/prod/redo01a.log
1 /disk2/oradata/prod/redolog01b.log
2 /disk2/oradata/prod/redolog02b.log
3 /disk2/oradata/prod/redolog03b.log
6 rows selected.
// 查看每个日志文件的大小
16:29:21 SYS@ prod>select GROUP#,THREAD#,SEQUENCE#,STATUS,BYTES/1024/1024 from v$log;
GROUP# THREAD# SEQUENCE# STATUS BYTES/1024/1024
---------- ---------- ---------- ---------------- ---------------
1 1 29 INACTIVE 50
2 1 30 CURRENT 50
3 1 28 INACTIVE 50
//查看目标库数据文件存放位置
16:15:42 SYS@ prod>col FILE_NAME for a50
16:16:01 SYS@ prod>select FILE_ID,FILE_NAME,TABLESPACE_NAME from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ----------------
4 /u01/app/oracle/oradata/prod/users01.dbf USERS
3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/prod/undotbs01.dbf UNDOTBS1
1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE
8 /u01/app/oracle/oradata/prod/test3.dbf TEST3


4 、配置辅助库参数
14:25:03 SYS@ prod>show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileprod.ora
14:25:10 SYS@ prod>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@rh55 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@rh55 dbs]$ ls
alert_prod.log c-199802235-20120812-22 hc_test1.dat inittest1.ora orapwcatdb
spfilecatdb.ora
alert_test1.log c-199802235-20120822-04 initdw.ora lkCATDB orapwprod
spfileprod.ora
c-199802235-20120812-1d hc_catdb.dat init.ora lkPROD orapwtest1
spfiletest1.ora
c-199802235-20120812-20 hc_prod.dat initprod.ora lkTEST1
snapcf_prod.f
[oracle@rh55 dbs]$ cp initprod.ora initclprod.ora
[oracle@rh55 dbs]$ vi initclprod.ora
%s/prod/clprod 将所有的 prod 替换为 clprod,修改所有相关目录,最后加上两句话

db_file_name_convert=('/u01/app/oracle/oradata/prod','/u01/database/clprod/oradata/')
log_file_name_convert=('/disk1/oradata/prod/','/u01/database/clprod/redo','/disk2/oradata/
prod','/u01/database/clprod/redo')


5 、创建相应的 dump  文件夹

[oracle@rh55 ~]$ mkdir -p /u01/database
[oracle@rh55 ~]$ mkdir -p /u01/database/clprod/adump
[oracle@rh55 ~]$ mkdir -p /u01/database/clprod/flash_recovery_area
[oracle@rh55 ~]$ mkdir -p /u01/database/clprod/oradata
[oracle@rh55 ~]$ mkdir -p /u01/database/clprod/redo
[oracle@rh55 ~]$ mkdir -p /u01/database/clprod/pfile
[oracle@rh55 ~]$ mkdir -p /u01/database/clprod/bdump
[oracle@rh55 ~]$ mkdir -p /u01/database/clprod/cdump
[oracle@rh55 ~]$ mkdir -p /u01/database/clprod/udump
[oracle@rh55 ~]$ mkdir -p /u01/database/clprod/logmnr




6 、生成辅助实例密码文件 (两个库密码最好相同)
[oracle@rh55 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwclprod password=oracle entries=10



7 、启动辅助库到 nomount  状态
[oracle@rh55 dbs]$ export ORACLE_SID=clprod
[oracle@rh55 dbs]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 21 15:32:45 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
15:32:45 SYS@ clprod>startup nomount
pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initclprod.ora;
ORACLE instance started.
Total System Global Area 272629760 bytes
Fixed Size 1218944 bytes
Variable Size 67110528 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes



8 、配置监听
[oracle@rh55 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rh55 admin]$ ls
listener.ora samples shrept.lst sqlnet.log tnsnames.ora
[oracle@rh55 admin]$ cat listener.ora


# listener.ora Network Configuration File:
/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rh55)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
LISTENER_CLPROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.48)(PORT = 1521))
)
)
LISTENER_PROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rh55)(PORT = 1521))
)
)
[oracle@rh55 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rh55)(PORT = 1521)

)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
CLPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.48)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = clprod)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@rh55 admin]$ lsnrctl start // 启动监听
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUL-2015 10:06:24
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh55)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rh55)(PORT=1521)))
STATUS of the LISTENER
-----------------------

Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 22-JUL-2015 10:06:24
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh55)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully



9 、用 RMAN  克隆数据库
[oracle@rh55 admin]$ !exp
export ORACLE_SID=clprod
[oracle@rh55 dbs]$ rman target sys/oracle@prod auxiliary sys/oracle // 连接目标库和辅助库
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 21 19:34:57 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=199802235)
connected to auxiliary database: CLPROD (not mounted)
RMAN> duplicate target database to clprod;
Starting Duplicate Db at 21-JUL-15
using target database control file instead of recovery catalog // 使用原数据库的控制文件
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of Memory Script:
{
set until scn 2865854;
set newname for datafile 1 to
"/u01/database/clprod/oradata//system01.dbf";
set newname for datafile 2 to
"/u01/database/clprod/oradata//undotbs01.dbf";

set newname for datafile 3 to
"/u01/database/clprod/oradata//sysaux01.dbf";
set newname for datafile 4 to
"/u01/database/clprod/oradata//users01.dbf";
set newname for datafile 5 to
"/u01/database/clprod/oradata//example01.dbf";
set newname for datafile 8 to
"/u01/database/clprod/oradata//test3.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
executing command: SET NEWNAME
Starting restore at 21-JUL-15 // 开始 restore 数据文件
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 00001 to /u01/database/clprod/oradata/system01.dbf
restoring datafile 00004 to /u01/database/clprod/oradata/users01.dbf
restoring datafile 00008 to /u01/database/clprod/oradata/test3.dbf
channel ORA_AUX_DISK_1: reading from backup piece /disk1/rman/PROD_410_885671332.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/disk1/rman/PROD_410_885671332.bak tag=TAG20150721T194851
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
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 /u01/database/clprod/oradata/undotbs01.dbf

restoring datafile 00003 to /u01/database/clprod/oradata/sysaux01.dbf
restoring datafile 00005 to /u01/database/clprod/oradata/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /disk1/rman/PROD_411_885671332.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/disk1/rman/PROD_411_885671332.bak tag=TAG20150721T194851
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 21-JUL-15
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLPROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 715
LOGFILE
GROUP 1 ( '/u01/database/clprod/redoredo01a.log',
'/u01/database/clprod/redo/redolog01b.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/database/clprod/redo/redolog02b.log',
'/u01/database/clprod/redoredo02a.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/database/clprod/redo/redolog03b.log',
'/u01/database/clprod/redoredo03a.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/database/clprod/oradata/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=885671689
filename=/u01/database/clprod/oradata/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=885671689
filename=/u01/database/clprod/oradata/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=885671689 filename=/u01/database/clprod/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=885671689
filename=/u01/database/clprod/oradata/example01.dbf
datafile 8 switched to datafile copy

input datafile copy recid=5 stamp=885671689 filename=/u01/database/clprod/oradata/test3.dbf
contents of Memory Script: // 下面脚本 oracle 自动设置了 scn 后发布 recover 命令
{
set until scn 2865854;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 21-JUL-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK // 下面使用 archivelog 进行 recover
starting media recovery
archive log thread 1 sequence 30 is already on disk as file /disk1/arch1/arch_1_30_797613178.log
archive log filename=/disk1/arch1/arch_1_30_797613178.log thread=1 sequence=30
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-JUL-15
contents of Memory Script: // 重新启动库到 nomount 状态
{
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 272629760 bytes
Fixed Size 1218944 bytes
Variable Size 67110528 bytes
Database Buffers 201326592 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLPROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 715
LOGFILE
GROUP 1 ( '/u01/database/clprod/redoredo01a.log',
'/u01/database/clprod/redo/redolog01b.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/database/clprod/redo/redolog02b.log',
'/u01/database/clprod/redoredo02a.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/database/clprod/redo/redolog03b.log',
'/u01/database/clprod/redoredo03a.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/database/clprod/oradata/system01.dbf'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/database/clprod/oradata//temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/database/clprod/oradata/undotbs01.dbf";
catalog clone datafilecopy "/u01/database/clprod/oradata/sysaux01.dbf";
catalog clone datafilecopy "/u01/database/clprod/oradata/users01.dbf";
catalog clone datafilecopy "/u01/database/clprod/oradata/example01.dbf";
catalog clone datafilecopy "/u01/database/clprod/oradata/test3.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /u01/database/clprod/oradata//temp01.dbf in control file
cataloged datafile copy
datafile copy filename=/u01/database/clprod/oradata/undotbs01.dbf recid=1 stamp=885671712
cataloged datafile copy
datafile copy filename=/u01/database/clprod/oradata/sysaux01.dbf recid=2 stamp=885671712
cataloged datafile copy
datafile copy filename=/u01/database/clprod/oradata/users01.dbf recid=3 stamp=885671712

cataloged datafile copy
datafile copy filename=/u01/database/clprod/oradata/example01.dbf recid=4 stamp=885671712
cataloged datafile copy
datafile copy filename=/u01/database/clprod/oradata/test3.dbf recid=5 stamp=885671712
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=885671712
filename=/u01/database/clprod/oradata/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=885671712
filename=/u01/database/clprod/oradata/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=885671712 filename=/u01/database/clprod/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=885671712
filename=/u01/database/clprod/oradata/example01.dbf
datafile 8 switched to datafile copy
input datafile copy recid=5 stamp=885671712 filename=/u01/database/clprod/oradata/test3.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 21-JUL-15

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30134188/viewspace-1757121/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-02-09

  • 博文量
    5
  • 访问量
    11783