在ORACLE11gR2上使用RMAN实现整库迁移
背景:
数据库
IP
SID
端口
数据库版本
Target DB
192.168.1.133
orcl
1521
11.2.0.1.0
Auxiliary DB
192.168.1.133(可以是同一机器,也可以是不同机器)
V112
1522
11.2.0.1.0
1. 在Auxiliary 创建pfile 参数文件
[oracle@localhost dbs]$ pwd /u01/oracle11gR2/product/11.2.0/db_1/dbs [oracle@localhost dbs]$ more initv112.ora *.DB_NAME=v112 *.control_files='/u01/oracle11gR2/oradata/v112/control01.ctl','/u01/oracle11gR2/flash_recovery_area /v112/control02.ctl' *.db_block_size=8192 *.compatible='11.2.0.1' _compression_compatibility="11.2.0" 注:这个隐含参数可以绕过11.2.0.1的bug *.db_recovery_file_dest_size=4294967296 *.db_recovery_file_dest='/u01/oracle11gR2/flash_recovery_area' *.audit_file_dest='/u01/oracle11gR2/admin/v112/adump'
|
2. 在Auxiliary库上创建口令文件
[oracle@localhost dbs]$ pwd /u01/oracle11gR2/product/11.2.0/db_1/dbs [oracle@localhost dbs]$ orap orapipe orapki orapwd orapwdO [oracle@localhost dbs]$ orapwd file=orapwv112 password=Your password |
3. 在Auxiliary库创建相关的目录结构
[oracle@localhost dbs]$ mkdir /u01/oracle11gR2/admin/v112/ [oracle@localhost dbs]$ mkdir /u01/oracle11gR2/oradata/v112/ |
注:在duplicate过程中,如果目录不存在,则会报错“ORA-27040: file create error, unable to create file”
4. 启动Auxiliary 到nomout 状态
SQL> conn / as sysdba; Connected to an idle instance. SQL> startup nomount pfile=‘/u01/oracle11gR2/product/11.2.0/db_1/dbs/initv112.ora’ ORACLE instance started. Total System Global Area 146472960 bytes Fixed Size 1335080 bytes Variable Size 92274904 bytes Database Buffers 50331648 bytes Redo Buffers 2531328 bytes |
5. 在Target 和Auxiliary 都配置Oracle Net(Listener.ora and tnsnames.ora)[可选]
[oracle@localhost admin]$ more tnsnames.ora # tnsnames.ora Network Configuration File: /u01/oracle11gR2/product/11.2.0/db_1/network/admin/tnsna mes.ora # Generated by Oracle configuration tools. v112 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = v112) ) ) LISTENER_v112 = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1521)) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID_NAME = orcl ) ) LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1521))
[oracle@localhost admin]$ more listener.ora # listener.ora Network Configuration File: /u01/oracle11gR2/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = v112) (ORACLE_HOME = /u01/oracle11gR2/product/11.2.0/db_1) (SID_NAME = v112) ) )
LISTENER2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1522)) ) )
SID_LIST_LISTENER2 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/oracle11gR2/product/11.2.0/db_1) (SID_NAME = orcl) ) )
ADR_BASE_LISTENER1 = /u01/oracle11gR2 |
6. 开始RMAN duplicate from active database
[oracle@localhost temp]$ rman target sys/mxy19880122@192.168.1.133:1522/orcl auxiliary sys/mxy19880122@192.168.1.133:1521/v112
注:这里没有用tnsnames.ora文件中的配置。
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jun 3 17:31:11 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1311170080)
connected to auxiliary database: V112 (not mounted)
RMAN> run{
set newname for datafile '/u01/oracle11gR2/oradata/orcl/system01.dbf' to
3>
'/u01/oracle11gR2/oradata/v112/system01.dbf';
set newname for datafile '/u01/oracle11gR2/oradata/orcl/sysaux01.dbf' to
6>
'/u01/oracle11gR2/oradata/v112/sysaux01.dbf';
set newname for datafile '/u01/oracle11gR2/oradata/orcl/undotbs01.dbf' to
9>
'/u01/oracle11gR2/oradata/v112/undotbs01.dbf';
set newname for datafile '/u01/oracle11gR2/oradata/orcl/users01.dbf' to
11>
'/u01/oracle11gR2/oradata/v112/users01.dbf';
set newname for datafile
14> 15>
'/u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_mxy_7v8tdctq_.dbf' to
17>
18> '/u01/oracle11gR2/oradata/v112/mxy.dbf';
19> duplicate target database to V112 from active database nofilenamecheck;}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
注:由于RMAN需要将文件恢复到新的位置,因此需要对文件进行重新映射。
Starting Duplicate Db at 03-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script.:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script.:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
注:恢复spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
contents of Memory Script.:
{
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''V112'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u01/oracle11gR2/oradata/v112/control01.ctl';
restore clone controlfile to '/u01/oracle11gR2/flash_recovery_area/v112/control02.ctl' from
'/u01/oracle11gR2/oradata/v112/control01.ctl';
alter clone database mount;
}
executing Memory Script
注:恢复控制文件
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''V112'' comment= ''Modified by RMAN duplicate'' scope=spfile
注:重新设置db_name
Oracle instance shut down
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Starting backup at 03-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/oracle11gR2/product/11.2.0/db_1/dbs/snapcf_orcl.f tag=TAG20120603T173213 RECID=1 STAMP=785007137
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 03-JUN-12
Starting restore at 03-JUN-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 03-JUN-12
database mounted
contents of Memory Script.:
{
set newname for datafile 1 to
"/u01/oracle11gR2/oradata/v112/system01.dbf";
set newname for datafile 2 to
"/u01/oracle11gR2/oradata/v112/sysaux01.dbf";
set newname for datafile 3 to
"/u01/oracle11gR2/oradata/v112/undotbs01.dbf";
set newname for datafile 4 to
"/u01/oracle11gR2/oradata/v112/users01.dbf";
set newname for datafile 5 to
"/u01/oracle11gR2/oradata/v112/mxy.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/oracle11gR2/oradata/v112/system01.dbf" datafile
2 auxiliary format
"/u01/oracle11gR2/oradata/v112/sysaux01.dbf" datafile
3 auxiliary format
"/u01/oracle11gR2/oradata/v112/undotbs01.dbf" datafile
4 auxiliary format
"/u01/oracle11gR2/oradata/v112/users01.dbf" datafile
5 auxiliary format
"/u01/oracle11gR2/oradata/v112/mxy.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
注:开始恢复数据文件(耐心等待)
Starting backup at 03-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/oracle11gR2/oradata/orcl/system01.dbf
output file name=/u01/oracle11gR2/oradata/v112/system01.dbf tag=TAG20120603T173229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/oracle11gR2/oradata/orcl/sysaux01.dbf
output file name=/u01/oracle11gR2/oradata/v112/sysaux01.dbf tag=TAG20120603T173229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/oracle11gR2/oradata/orcl/undotbs01.dbf
output file name=/u01/oracle11gR2/oradata/v112/undotbs01.dbf tag=TAG20120603T173229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oracle11gR2/oradata/orcl/users01.dbf
output file name=/u01/oracle11gR2/oradata/v112/users01.dbf tag=TAG20120603T173229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_mxy_7v8tdctq_.dbf
output file name=/u01/oracle11gR2/oradata/v112/mxy.dbf tag=TAG20120603T173229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 03-JUN-12
sql statement: alter system archive log current
contents of Memory Script.:
{
backup as copy reuse
archivelog like "/u01/oracle11gR2/flash_recovery_area/ORCL/archivelog/2012_06_03/o1_mf_1_291_7wpd7h5x_.arc" auxiliary format
"/u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 03-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=291 RECID=176 STAMP=785007599
output file name=/u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_0hnckgvg_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 03-JUN-12
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_0hnckgvg_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_0hnckgvg_.arc
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=785007602 file name=/u01/oracle11gR2/oradata/v112/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=785007602 file name=/u01/oracle11gR2/oradata/v112/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=785007602 file name=/u01/oracle11gR2/oradata/v112/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=785007602 file name=/u01/oracle11gR2/oradata/v112/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=785007602 file name=/u01/oracle11gR2/oradata/v112/mxy.dbf
contents of Memory Script.:
{
set until scn 6603687;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 03-JUN-12
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 291 is already on disk as file /u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_0hnckgvg_.arc
archived log file name=/u01/oracle11gR2/flash_recovery_area/V112/archivelog/2012_06_03/o1_mf_1_291_0hnckgvg_.arc thread=1 sequence=291
media recovery complete, elapsed time: 00:00:01
Finished recover at 03-JUN-12
contents of Memory Script.:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''V112'' 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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: alter system set db_name = ''V112'' 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 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "V112" RESETLOGS ARCHIVELOG
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/oracle11gR2/oradata/v112/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script.:
{
set newname for tempfile 1 to
"/u01/oracle11gR2/oradata/orcl/temp01.dbf";
set newname for tempfile 2 to
"/u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_temp1_7vb4dcv5_.tmp";
set newname for tempfile 3 to
"/u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_temp2_7vb4djq8_.tmp";
switch clone tempfile all;
catalog clone datafilecopy "/u01/oracle11gR2/oradata/v112/sysaux01.dbf",
"/u01/oracle11gR2/oradata/v112/undotbs01.dbf",
"/u01/oracle11gR2/oradata/v112/users01.dbf",
"/u01/oracle11gR2/oradata/v112/mxy.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/oracle11gR2/oradata/orcl/temp01.dbf in control file
renamed tempfile 2 to /u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_temp1_7vb4dcv5_.tmp in control file
renamed tempfile 3 to /u01/oracle11gR2/oradata/orcl/ORCL/datafile/o1_mf_temp2_7vb4djq8_.tmp in control file
cataloged datafile copy
datafile copy file name=/u01/oracle11gR2/oradata/v112/sysaux01.dbf RECID=1 STAMP=785007628
cataloged datafile copy
datafile copy file name=/u01/oracle11gR2/oradata/v112/undotbs01.dbf RECID=2 STAMP=785007628
cataloged datafile copy
datafile copy file name=/u01/oracle11gR2/oradata/v112/users01.dbf RECID=3 STAMP=785007628
cataloged datafile copy
datafile copy file name=/u01/oracle11gR2/oradata/v112/mxy.dbf RECID=4 STAMP=785007628
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=785007628 file name=/u01/oracle11gR2/oradata/v112/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=785007628 file name=/u01/oracle11gR2/oradata/v112/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=785007628 file name=/u01/oracle11gR2/oradata/v112/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=785007628 file name=/u01/oracle11gR2/oradata/v112/mxy.dbf
contents of Memory Script.:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 03-JUN-12
RMAN> exit
7. 复制结束
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22700344/viewspace-732178/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22700344/viewspace-732178/