长沙监控安装公司:用rman将Oracle迁移到异机 - 待做实验 - 众人拾贝
来源:百度文库 编辑:偶看新闻 时间:2024/04/27 11:28:10
用rman将Oracle迁移到异机
windows环境 oracle 10.2.0.1 源库:192.168.1.81 目标库:192.168.1.2 主要流程: 停止源库->备份->恢复->改数据文件位置->resetlogs打开 迁移数据库81 到192.168.1.2上 停81库,设置归档路径,将其启动到归档模式, 以administrator身份启动数据库服务oracleserviceorcl 备份测试
C:\>set oracle_sid=orcl
C:\>rman target /
RMAN> backup current controlfile format '\\192.168.1.2\oradata\81\cf_%U';
全备 run {
backup as compressed backupset database
format '\\192.168.1.2\oradata\81\dbfull_%d_%T_%s';
sql 'alter system archive log current';
}
搜集信息
sqlplus "/as sysdba" set linesize 130 pagesize 2000
set trimspool on
set echo off
set verify off
set timing off
set feedback off
set head off
set echo off spool renfile.sql
select 'set newname for datafile '''||name||''' to ''d:\oradata\81\'|| substr(name, instr(name, '\', -1) + 1)||'''; ' cmd from v$datafile
union all
select 'set newname for tempfile '''||name||''' to ''d:\oradata\81\'|| substr(name, instr(name, '\', -1) + 1)||'''; ' cmd from v$tempfile; spool off
创建pfile文件,拷贝到目标位置
create pfile from spfile; 修改pfile文件中的路径,注意dump目录、控制文件目录、归档位置目录(最好将需要恢复的归档日志拷贝到此目录 以便恢复)
启动实例到nomount状态,恢复控制文件
set oracle_sid=orcl
rman target /
startup nomount
restore controlfile from 'd:\oradata\81\DBFULL_ORCL_20110111_21'; #控制文件得提前知道放哪了
段句柄=\\192.168.1.2\ORADATA\81\DBFULL_ORCL_20110111_21 alter database mount;
catalog start with 'e:\oradata\bak' noprompt; #备份集的位置指定一下 run{ set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' to 'd:\oradata\81\SYSTEM01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' to 'd:\oradata\81\UNDOTBS01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF' to 'd:\oradata\81\SYSAUX01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF' to 'd:\oradata\81\USERS01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF' to 'd:\oradata\81\EXAMPLE01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_01.DBF' to 'd:\oradata\81\test_01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_02.DBF' to 'd:\oradata\81\test_02.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_0423_01.DBF' to 'd:\oradata\81\test_0423_01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_0423_02.DBF' to 'd:\oradata\81\test_0423_02.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_0423_03.DBF' to 'd:\oradata\81\test_0423_03.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_0423_04.DBF' to 'd:\oradata\81\test_0423_04.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_03.DBF' to 'd:\oradata\81\test_03.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_04.DBF' to 'd:\oradata\81\test_04.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_05.DBF' to 'd:\oradata\81\test_05.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_06.DBF' to 'd:\oradata\81\test_06.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_07.DBF' to 'd:\oradata\81\test_07.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\AAA.DBF' to 'd:\oradata\81\AAA.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TS_EV_01.DBF' to 'd:\oradata\81\TS_EV_01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TS_EV_02.DBF' to 'd:\oradata\81\TS_EV_02.DBF';
set newname for tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' to 'd:\oradata\81\TEMP01.DBF'; restore database;
switch datafile all;
switch tempfile all;
recover database;
#alter database open resetlogs;
} 如果缺少归档日志
run {
set ARCHIVELOG DESTINATION to 'd:\oradata\81\arch';
recover database;
} sqlplus "/as sysdba"
alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG' to 'd:\oradata\81\redo01.dbf';
alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG' to 'd:\oradata\81\redo02.dbf';
alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG' to 'd:\oradata\81\redo03.dbf'; 检查控制文件、数据文件、日志文件、临时文件位置是否正确。
参考:
run {
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
backup full tag 'dbfullbak' database maxsetsize 30g
include current controlfile format '\\192.168.1.2\oradata\81\dbfull_%U'
plus archivelog format '\\192.168.1.2\oradata\81\arch_%U' delete all input;
}
windows环境 oracle 10.2.0.1 源库:192.168.1.81 目标库:192.168.1.2 主要流程: 停止源库->备份->恢复->改数据文件位置->resetlogs打开 迁移数据库81 到192.168.1.2上 停81库,设置归档路径,将其启动到归档模式, 以administrator身份启动数据库服务oracleserviceorcl 备份测试
C:\>set oracle_sid=orcl
C:\>rman target /
RMAN> backup current controlfile format '\\192.168.1.2\oradata\81\cf_%U';
全备 run {
backup as compressed backupset database
format '\\192.168.1.2\oradata\81\dbfull_%d_%T_%s';
sql 'alter system archive log current';
}
搜集信息
sqlplus "/as sysdba" set linesize 130 pagesize 2000
set trimspool on
set echo off
set verify off
set timing off
set feedback off
set head off
set echo off spool renfile.sql
select 'set newname for datafile '''||name||''' to ''d:\oradata\81\'|| substr(name, instr(name, '\', -1) + 1)||'''; ' cmd from v$datafile
union all
select 'set newname for tempfile '''||name||''' to ''d:\oradata\81\'|| substr(name, instr(name, '\', -1) + 1)||'''; ' cmd from v$tempfile; spool off
创建pfile文件,拷贝到目标位置
create pfile from spfile; 修改pfile文件中的路径,注意dump目录、控制文件目录、归档位置目录(最好将需要恢复的归档日志拷贝到此目录 以便恢复)
启动实例到nomount状态,恢复控制文件
set oracle_sid=orcl
rman target /
startup nomount
restore controlfile from 'd:\oradata\81\DBFULL_ORCL_20110111_21'; #控制文件得提前知道放哪了
段句柄=\\192.168.1.2\ORADATA\81\DBFULL_ORCL_20110111_21 alter database mount;
catalog start with 'e:\oradata\bak' noprompt; #备份集的位置指定一下 run{ set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' to 'd:\oradata\81\SYSTEM01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' to 'd:\oradata\81\UNDOTBS01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF' to 'd:\oradata\81\SYSAUX01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF' to 'd:\oradata\81\USERS01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF' to 'd:\oradata\81\EXAMPLE01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_01.DBF' to 'd:\oradata\81\test_01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_02.DBF' to 'd:\oradata\81\test_02.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_0423_01.DBF' to 'd:\oradata\81\test_0423_01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_0423_02.DBF' to 'd:\oradata\81\test_0423_02.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_0423_03.DBF' to 'd:\oradata\81\test_0423_03.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_0423_04.DBF' to 'd:\oradata\81\test_0423_04.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_03.DBF' to 'd:\oradata\81\test_03.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_04.DBF' to 'd:\oradata\81\test_04.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_05.DBF' to 'd:\oradata\81\test_05.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_06.DBF' to 'd:\oradata\81\test_06.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test_07.DBF' to 'd:\oradata\81\test_07.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\AAA.DBF' to 'd:\oradata\81\AAA.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TS_EV_01.DBF' to 'd:\oradata\81\TS_EV_01.DBF';
set newname for datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TS_EV_02.DBF' to 'd:\oradata\81\TS_EV_02.DBF';
set newname for tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' to 'd:\oradata\81\TEMP01.DBF'; restore database;
switch datafile all;
switch tempfile all;
recover database;
#alter database open resetlogs;
} 如果缺少归档日志
run {
set ARCHIVELOG DESTINATION to 'd:\oradata\81\arch';
recover database;
} sqlplus "/as sysdba"
alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG' to 'd:\oradata\81\redo01.dbf';
alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG' to 'd:\oradata\81\redo02.dbf';
alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG' to 'd:\oradata\81\redo03.dbf'; 检查控制文件、数据文件、日志文件、临时文件位置是否正确。
参考:
run {
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
backup full tag 'dbfullbak' database maxsetsize 30g
include current controlfile format '\\192.168.1.2\oradata\81\dbfull_%U'
plus archivelog format '\\192.168.1.2\oradata\81\arch_%U' delete all input;
}
用VB将TXT文本中的内容导入oracle数据库.......急!!急
如何将ORACLE的字段加密?
怎么才能将word内容写入oracle
oracle怎么用啊
如何将dbf表导入到oracle数据库中,oracle版本是8.0.5
将sybase数据库的数据导入到Oracle数据库中
将sqlserver数据转换为oracle需要注意些什么?
oracle是否有将字符串顺序倒转的函数
能否在就学期间将户口从学校迁移?(大学)
求将外地户口迁移至广州市老八区的过程
办理驾驶证迁移是否需要在原来所在地将违章的分数补回才能办理迁移?
rman把数据库的备份放到哪了
怎么用pb9连接oracle 10g?
oracle中decode函数有什么用?
Oracle 9i配合什么好用?
Oracle里group 可以用函数吗?
如果户口迁移,身份证用不用修改
如果户口迁移,身份证用不用修改
怎样将oracle内的数据地图共享到mapinfo内
怎样用VB连接ORACLE数据库,并将查询结果生成为文本文件保存在本地?
将oracle中查询出来的表格转换成word或excel格式的表格
如何将oracle中的多个表自动导入到同一个excel文件里的不同sheet?
oracle中怎样写SQL,将某一列的所有记录连接成一个字符串?
如何将其它数据库中的事实数据导入到oracle中