通过RMAN异机迁移数据库并修改存储路径【相同位数与平台版】

订正记录：
2013年12月4日 文档细节订正



-- 查询数据库的DBID

SQL> select name,dbid from v$database;

NAME            DBID

--------- ----------

ZHONGRDB  1755753531

-- 查询数据文件与临时文件信息

select file_id,file_name from dba_data_files;

select file_id,file_name from dba_temp_files;

SQL >   select  file_id,file_name   from  dba_data_files;

   FILE_ID FILE_NAME
- - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
          4  D:\ORADATA\ZHONGRDB\USERS01.DBF
          3  D:\ORADATA\ZHONGRDB\UNDOTBS01.DBF
          2  D:\ORADATA\ZHONGRDB\SYSAUX01.DBF
          1  D:\ORADATA\ZHONGRDB\SYSTEM01.DBF
          5  D:\ORADATA\ZHONGRDB\EXAMPLE01.DBF

SQL >   select  file_id,file_name   from  dba_temp_files;

   FILE_ID FILE_NAME
- - - - - - - - - -   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
          1  D:\ORADATA\ZHONGRDB\TEMP01.DBF

-- 通过RMAN数据库进行备份

-- 将RMAN备份的所有文件拷贝到新服务器指定的位置【 Target端存放备份路径必须与Source端一致，否则必须要用Catalog方式 】

-- 在新服务器创建密码文件

orapwd file=D:\app\Administrator\product\11.2.0\dbhome_1\database\PWDzhongrdb.ora password=zhong

-- 创建实例信息

oradim -new -sid zhongrdb

-- 通过RMAN还原配置文件

set oracle_sid=zhongrdb

RMAN TARGET /

STARTUP NOMOUNT

restore spfile to pfile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\initZHONGRDB.ora' from 'D:\ORABACKUP\C-1755753531-20131128-00';

RMAN >  STARTUP NOMOUNT

启动失败: ORA - 01078: failure   in  processing   system   parameters
LRM - 00109: ????????????????   'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITZHONGRDB.ORA'

在没有参数文件的情况下启动 Oracle 实例以检索 spfile
Oracle 实例已启动

系统全局区域总计       158662656  字节

Fixed   Size                        2253296  字节
Variable   Size                  104861200  字节
Database  Buffers               46137344  字节
Redo Buffers                     5410816  字节

RMAN >

RMAN >

RMAN >  restore spfile   to  pfile   'D:\app\Administrator\product\11.2.0\dbhome_1\database\initZHONGRDB.ora'   from   'D:\ORABACKUP\C-1755753531-20131128-00';

启动 restore 于   28 - 11月 - 13
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID = 249  设备类型 =DISK

通道 ORA_DISK_1: 正在从 AUTOBACKUP D:\ORABACKUP\ C - 1755753531 - 20131128 -00 还原 spfile
通道 ORA_DISK_1: 从 AUTOBACKUP 还原 SPFILE 已完成
完成 restore 于   28 - 11月 - 13

-- PFILE参数路径修正

*.control_files='D:\app\Administrator\oradata\zhongrdb\control01.ctl','D:\app\Administrator\oradata\zhongrdb\control02.ctl'

-- 数据库启动到NOMOUNT模式

STARTUP NOMOUNT

-- 还原原数据库控制文件

RMAN TARGET /

restore controlfile from 'D:\ORABACKUP\C-1755753531-20131128-00';

D:\app\Administrator\product\ 11. 2. 0\dbhome_1\BIN >rman target   /

恢复管理器: Release   11. 2. 0. 3. 0   -  Production   on  星期四   11月   28   23: 56:00   2013

Copyright ( c)   1982,   2011, Oracle   and / or  its affiliates.   All  rights reserved.

已连接到目标数据库: ZHONGRDB (未装载)

RMAN >  restore controlfile   from   'D:\ORABACKUP\C-1755753531-20131128-00';

启动 restore 于   28 - 11月 - 13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID = 63  设备类型 =DISK

通道 ORA_DISK_1: 正在还原控制文件
通道 ORA_DISK_1: 还原完成, 用时: 00:00: 03
输出文件名 =D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\CONTROL01.CTL
输出文件名 =D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\CONTROL02.CTL
完成 restore 于   28 - 11月 - 13

-- 启动数据库到MOUNT状态

alter database mount;

-- 还原数据文件并设置新路径

run

{

 set newname for datafile 1  to "D:\app\Administrator\oradata\zhongrdb\SYSTEM01.DBF";

 set newname for datafile 2  to "D:\app\Administrator\oradata\zhongrdb\SYSAUX01.DBF";

 set newname for datafile 3  to "D:\app\Administrator\oradata\zhongrdb\UNDOTBS01.DBF";

 set newname for datafile 4  to "D:\app\Administrator\oradata\zhongrdb\USERS01.DBF";

 set newname for datafile 5  to "D:\app\Administrator\oradata\zhongrdb\EXAMPLE01.DBF";

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

restore database;

switch datafile all;

recover database;

}

对switch datafile all的说明：

--对于nocatalog 模式下，rman备份的信息是保存在控制文件里的，包括文件的路径信息。 这里的switch datafile all的作用，就是更新控制文件里的信息。

已连接到目标数据库: ZHONGRDB (DBID = 1755753531, 未打开)

RMAN >  run
2 >  {
3 >    set  newname   for  datafile   1    to   "D:\app\Administrator\oradata\zhongrdb\SYSTEM01.DBF";
4 >    set  newname   for  datafile   2    to   "D:\app\Administrator\oradata\zhongrdb\SYSAUX01.DBF";
5 >    set  newname   for  datafile   3    to   "D:\app\Administrator\oradata\zhongrdb\UNDOTBS01.DBF";
6 >    set  newname   for  datafile   4    to   "D:\app\Administrator\oradata\zhongrdb\USERS01.DBF";
7 >    set  newname   for  datafile   5    to   "D:\app\Administrator\oradata\zhongrdb\EXAMPLE01.DBF";
8 >   allocate  channel c1 device   type  disk;
9 >   allocate  channel c2 device   type  disk;
10 >   allocate  channel c3 device   type  disk;
11 >  restore   database;
12 >  switch datafile   all;
13 >  }

正在执行命令:   SET  NEWNAME

正在执行命令:   SET  NEWNAME

正在执行命令:   SET  NEWNAME

正在执行命令:   SET  NEWNAME

正在执行命令:   SET  NEWNAME

使用目标数据库控制文件替代恢复目录
分配的通道: c1
通道 c1: SID = 63  设备类型 =DISK

分配的通道: c2
通道 c2: SID = 129  设备类型 =DISK

分配的通道: c3
通道 c3: SID = 193  设备类型 =DISK

启动 restore 于   29 - 11月 - 13
启动 implicit crosscheck backup 于   29 - 11月 - 13
已交叉检验的   3  对象
完成 implicit crosscheck backup 于   29 - 11月 - 13

启动 implicit crosscheck   copy  于   29 - 11月 - 13
完成 implicit crosscheck   copy  于   29 - 11月 - 13

搜索恢复区中的所有文件
正在编制文件目录...
没有为文件编制目录


通道 c1: 正在开始还原数据文件备份集
通道 c1: 正在指定从备份集还原的数据文件
通道 c1: 将数据文件   00001  还原到 D:\app\Administrator\oradata\zhongrdb\SYSTEM01.DBF
通道 c1: 将数据文件   00002  还原到 D:\app\Administrator\oradata\zhongrdb\SYSAUX01.DBF
通道 c1: 将数据文件   00003  还原到 D:\app\Administrator\oradata\zhongrdb\UNDOTBS01.DBF
通道 c1: 将数据文件   00004  还原到 D:\app\Administrator\oradata\zhongrdb\USERS01.DBF
通道 c1: 将数据文件   00005  还原到 D:\app\Administrator\oradata\zhongrdb\EXAMPLE01.DBF
通道 c1: 正在读取备份片段 D:\ORABACKUP\ 28_DB_03OQ4KFL_1_1_11M28D
通道 c1: 段句柄   =  D:\ORABACKUP\ 28_DB_03OQ4KFL_1_1_11M28D  标记   =  TAG20131128T232957
通道 c1: 已还原备份片段   1
通道 c1: 还原完成, 用时: 00: 01: 05
完成 restore 于   29 - 11月 - 13

数据文件   1  已转换成数据文件副本
输入数据文件副本 RECID = 7  STAMP = 832723508  文件名 =D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\SYSTEM01.DBF
数据文件   2  已转换成数据文件副本
输入数据文件副本 RECID = 8  STAMP = 832723508  文件名 =D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\SYSAUX01.DBF
数据文件   3  已转换成数据文件副本
输入数据文件副本 RECID = 9  STAMP = 832723509  文件名 =D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\UNDOTBS01.DBF
数据文件   4  已转换成数据文件副本
输入数据文件副本 RECID = 10  STAMP = 832723510  文件名 =D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\USERS01.DBF
数据文件   5  已转换成数据文件副本
输入数据文件副本 RECID = 11  STAMP = 832723511  文件名 =D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\EXAMPLE01.DBF
释放的通道: c1
释放的通道: c2
释放的通道: c3

RMAN >  recover   database;

启动 recover 于   29 - 11月 - 13
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID = 63  设备类型 =DISK

正在开始介质的恢复

通道 ORA_DISK_1: 正在开始将归档日志还原到默认目标
通道 ORA_DISK_1: 正在还原归档日志
归档日志线程 = 1  序列 = 237
通道 ORA_DISK_1: 正在读取备份片段 D:\ORABACKUP\ARCH_04OQ4KIB_1_1
通道 ORA_DISK_1: 段句柄   =  D:\ORABACKUP\ARCH_04OQ4KIB_1_1 标记   =  TAG20131128T233123
通道 ORA_DISK_1: 已还原备份片段   1
通道 ORA_DISK_1: 还原完成, 用时: 00:00: 01
归档日志文件名 =D:\ARCHIVELOGARC0000000237_0832114878. 0001  线程 = 1  序列 = 237
无法找到归档日志
归档日志线程 = 1  序列 = 238
RMAN - 00571:   = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
RMAN - 00569:   = = = = = = = = = = = = = = =  ERROR MESSAGE STACK FOLLOWS   = = = = = = = = = = = = = = =
RMAN - 00571:   = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
RMAN - 03002: recover 命令 (在   11 / 29 / 2013  00: 06: 51  上) 失败
RMAN - 06054: 介质恢复正在请求未知的线程   1  序列   238  的归档日志以及起始 SCN   6141572

RMAN >  recover   database   until  scn   6141571;

启动 recover 于   29 - 11月 - 13
使用通道 ORA_DISK_1
RMAN - 00571:   = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
RMAN - 00569:   = = = = = = = = = = = = = = =  ERROR MESSAGE STACK FOLLOWS   = = = = = = = = = = = = = = =
RMAN - 00571:   = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
RMAN - 03002: recover 命令 (在   11 / 29 / 2013  00: 09: 35  上) 失败
RMAN - 06556: 数据文件   1  必须从 SCN   6141571  之前的备份还原

RMAN >  recover   database   until  scn   6141572;

启动 recover 于   29 - 11月 - 13
使用通道 ORA_DISK_1

正在开始介质的恢复
介质恢复完成, 用时: 00:00: 01

完成 recover 于   29 - 11月 - 13

RMAN >   alter   database   open;

RMAN - 00571:   = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
RMAN - 00569:   = = = = = = = = = = = = = = =  ERROR MESSAGE STACK FOLLOWS   = = = = = = = = = = = = = = =
RMAN - 00571:   = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
RMAN - 03002:   alter  db 命令 (在   11 / 29 / 2013  00: 10: 05  上) 失败
ORA - 01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

RMAN >   alter   database   open  resetlogs;

RMAN - 00571:   = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
RMAN - 00569:   = = = = = = = = = = = = = = =  ERROR MESSAGE STACK FOLLOWS   = = = = = = = = = = = = = = =
RMAN - 00571:   = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
RMAN - 03002:   alter  db 命令 (在   11 / 29 / 2013  00: 10: 13  上) 失败
ORA - 00344: 无法重新创建联机日志   'D:\ORADATA\ZHONGRDB\REDO01.LOG'
ORA - 27040: 文件创建错误, 无法创建文件
OSD - 04002: 无法打开文件
O /S -Error: (OS   3) 系统找不到指定的路径。

-- 通过只读状态查询数据库信息

alter database open read only;

select open_mode from v$database;

SYS@ zhongrdb   SQL > select  open_mode   from  v$ database;

OPEN_MODE
- - - - - - - - - - - - - - - - - - - -
READ   ONLY

-- 重建控制文件以修改REDOLOG的位置

alter database backup controlfile to trace as 'D:\CONTROLFILE_ZHONGRDB.txt';

+ NORESETLOGS方式

由于源库的日志没有被恢复，因此如果采用该方式将会报错。

+ RESETLOGS方式

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ZHONGRDB" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\REDO03.LOG'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\SYSTEM01.DBF',

  'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\SYSAUX01.DBF',

  'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\UNDOTBS01.DBF',

  'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\USERS01.DBF',

  'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\EXAMPLE01.DBF'

CHARACTER SET ZHS16GBK

;

alter database open RESETLOGS;

SYS@ zhongrdb   SQL >shutdown   immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SYS@ zhongrdb   SQL >STARTUP NOMOUNT
ORACLE 例程已经启动。

Total   System   Global  Area       855982080  bytes
Fixed   Size                        2260000  bytes
Variable   Size                  503317472  bytes
Database  Buffers               343932928  bytes
Redo Buffers                     6471680  bytes
SYS@ zhongrdb   SQL > CREATE  CONTROLFILE REUSE   DATABASE   "ZHONGRDB"  RESETLOGS  ARCHIVELOG
    2       MAXLOGFILES   16
    3       MAXLOGMEMBERS   3
    4       MAXDATAFILES   100
    5       MAXINSTANCES   8
    6       MAXLOGHISTORY   292
    7   LOGFILE
    8      GROUP   1   'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\REDO01.LOG'    SIZE   50M  BLOCKSIZE   512,
    9      GROUP   2   'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\REDO02.LOG'    SIZE   50M  BLOCKSIZE   512,
  10      GROUP   3   'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\REDO03.LOG'    SIZE   50M  BLOCKSIZE   512
  11    - -  STANDBY LOGFILE
  12   DATAFILE
  13      'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\SYSTEM01.DBF',
  14      'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\SYSAUX01.DBF',
  15      'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\UNDOTBS01.DBF',
  16      'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\USERS01.DBF',
  17      'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\EXAMPLE01.DBF'
  18    CHARACTER   SET  ZHS16GBK
  19   ;

控制文件已创建。

SYS@ zhongrdb   SQL > select  open_mode   from  v$ database;

OPEN_MODE
- - - - - - - - - - - - - - - - - - - -
MOUNTED

SYS@ zhongrdb   SQL > alter   database   open  RESETLOGS;

数据库已更改。

SYS@ zhongrdb   SQL >
SYS@ zhongrdb   SQL > select  open_mode   from  v$ database;

OPEN_MODE
- - - - - - - - - - - - - - - - - - - -
READ   WRITE

-- 重建临时表空间

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\APP\ADMINISTRATOR\ORADATA\ZHONGRDB\TEMP01.DBF' SIZE 100M;

-- 查询数据库的DBID

SQL> select name,dbid from v$database;

NAME            DBID

--------- ----------

ZHONGRDB  1755753531

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

转载于:http://blog.itpub.net/12974804/viewspace-1061919/