ITPub博客

首页 > 数据库 > Oracle > RMAN还原oracle数据库到异机(原创)

RMAN还原oracle数据库到异机(原创)

Oracle 作者:华夏神逸 时间:2013-07-16 13:42:24 0 删除 编辑
一、RMAN的"rman: can't open target"错误
这是因为调用的是非Oracle的RMAN的缘故。
修正这个问题,我们只需要在Oracle用户的环境变量下,将$ORACLE_HOME/bin放在PATH变量前就可以了:
export PATH=$ORACLE_HOME/bin:$PATH:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
二、RMAN恢复到异机的步骤
1、准备工作:
确认备份(含控制文件,数据文件,归档文件)有效(没归档应该也可以,只要能确认数据文件和控制文件是一致备份)。
在目标服务器上创建与源服务器上一致的oracle目录结构,
主要创建$ORACLE_BASE/admin/$ORACLE_SID/下的六个目录;$ORACLE_BASE/oradata下创建$ORACLE_SID目录;
rman备份路径目录(这个地方必须要与源数据库一致,创建好后,把源数据库备份的数据文件复制到这个目录里);
归档日志目录(同样,创建好后,把需要的归档日志文件复制到此目录)。
以下均在目标服务器上操作
2、连接到该sid
$export ORACLE_SID=cms
$rman target /
RMAN> set dbid 345935189
RMAN> startup nomount
3、从备份的控制文件恢复初始化参数文件
RMAN>restore spfile to pfile '/usr/oracle/product/10.2.0/dbs/initcms.ora' from '/usr/oracle/backup/ctl_c-345935189-20090609-00';
RMAN> startup force nomount pfile='/usr/oracle/product/10.2.0/dbs/initcms.ora';
4、恢复控制文件并进入到加载状态
RMAN> restore controlfile from '/usr/oracle/backup/ctl_c-345935189-20090609-00';
RMAN> alter database mount;
5、修复数据库(这里是源路径修复,如果你要恢复的文件地址与源库地址不同的话,
需要通过SET NEWNAME FOR DATAFILE命令来为数据文件重新设定路径。)
RMAN> restore database;
6、恢复数据库
RMAN> recover database until time '2009-06-09 14:00:00'
7、通过open resetlogs方式打开数据库
RMAN> alter database open resetlogs;
测试,OK,到此使用RMAN异机恢复成功,剩下的就是其他简单的工作了。

三、ORA-01861: literal does not match format string
RMAN中使用recover database until time '2009-06-09 14:00:00'
命令进行基于时间点的恢复时报的一个错误。
这个错误是由于时间日期格式设置的问题,设置正确的时间格式:
$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'

四、ORA-06553: PLS-801: internal error [56319]错误
目标数据库恢复成功,登陆进去,一切正常。结果在执行最简单的sql查询的时候报这个错,执行多个不同的sql反复报这个错误,但sql语句可以正常执行。rapidsql等工具无法连接。
原因:源库是64位的OS,目标库是32位(9.36安装64位10g,5.186安装32位10g)
解决方法:
1、Shutdown immediate
2、startup migrate
注:startup migrate表示降级,在9i,无论升级/降级 数据库都是startup migrate;10g后增加了upgrade参数,升级可直接用startup upgrade,降级仍是startup migrate.
3、@$ORACLE_HOME/rdbms/admin/utlirp.sql;
4、Shutdown immediate
5、Startup
6、@$ORACLE_HOME/rdbms/admin/utlrp.sql;
7、Shutdown immediate
8、Startup

五、一个小技巧
SQL*Plus允许建立一个login.sql文件,每次启动SQL*Plus时都会执行这个脚本。另外,还允许设置一个环境变量SQLPATH,这样不论这个login.sql脚本具体在哪个目录中,SQL*Plus都能找到它。
login.sql内容可以根据自己的需要添加,下面是Thomas Kyte的例子:
define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1,decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on
一些sqlplus的设置,从define gname=idle这句以下,用于建立SQL*Plus提示符,sqlplus登录后的提示符形如:
sys@CMS>
很清楚的可以看到当前登录用户和实例名,在多实例的服务器上,相当好使,可以避免因忘记export ORACLE_SID= 引起的莫名的问题。


自己的例子:
获得旧机的DBID

SQL> select dbid from v$database;

      DBID
----------
2061567322

oracle@SAPIDES:~> export ORACLE_SID=msgplus
oracle@SAPIDES:~> rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 11 14:14:18 2013

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

connected to target database (not started)

RMAN> set dbid 657921230

executing command: SET DBID

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/oracle/product/11g/dbs/initmsgplus.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2211448 bytes
Variable Size                 92275080 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5455872 bytes

RMAN> restore spfile to pfile '/oracle/P58/112_64/dbs/spfileP58.ora' from '/oracle/oradata/backup/P58/c-657921230-20130710-00';

Starting restore at 11-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oracle/oradata/backup/P58/c-657921230-20130710-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 11-JUL-13


RMAN> startup force nomount pfile='/oracle/P58/112_64/dbs/spfileP58.ora';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 07/11/2013 14:19:37
RMAN-04014: startup failed: ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_P58'

================
把spfileP58.ora文件中的*.local_listener的值改成,如:
*.local_listener='(ADDRESS_LIST=(Address=(Protocol=tcp) (Host=localhost)(Port=1521)))'
================



RMAN> startup force nomount pfile='/oracle/P58/112_64/dbs/spfileP58.ora';

Oracle instance started

Total System Global Area    1603411968 bytes

Fixed Size                     2213776 bytes
Variable Size                402655344 bytes
Database Buffers            1191182336 bytes
Redo Buffers                   7360512 bytes

RMAN> restore controlfile from '/oracle/oradata/backup/P58/c-657921230-20130710-00';

Starting restore at 11-JUL-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=958 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/oradata/P58/control01.ctl
output file name=/oracle/oradata/P58/control02.ctl
Finished restore at 11-JUL-13


RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 11-JUL-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=958 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oracle/oradata/P58/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/oradata/P58/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/oradata/P58/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/oradata/P58/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oracle/oradata/P58/A2I_A2i_xCat_DBs_Ix.DBF
channel ORA_DISK_1: restoring datafile 00006 to /oracle/oradata/P58/A2I_A2i_xCat_DBs.DBF
channel ORA_DISK_1: restoring datafile 00007 to /oracle/oradata/P58/A2I_SRMMDMBMCatalog10_m000_Ix.DBF
channel ORA_DISK_1: restoring datafile 00008 to /oracle/oradata/P58/A2I_SRMMDMBMCatalog10_m000.DBF
channel ORA_DISK_1: restoring datafile 00009 to /oracle/oradata/P58/A2I_SRMMDMBMCatalog10_Z000_Ix.DBF
channel ORA_DISK_1: restoring datafile 00010 to /oracle/oradata/P58/A2I_SRMMDMBMCatalog10_Z000.DBF
channel ORA_DISK_1: reading from backup piece /oracle/oradata/backup/P58/whole_P58_T_0uoebo4j_820371603_30_1
channel ORA_DISK_1: piece handle=/oracle/oradata/backup/P58/whole_P58_T_0uoebo4j_820371603_30_1 tag=TAG20130710T010003
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 11-JUL-13

RMAN> recover database;

Starting recover at 11-JUL-13
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=779
channel ORA_DISK_1: reading from backup piece /oracle/oradata/backup/P58/arch_P58_T_10oebo55_820371621_32_1
channel ORA_DISK_1: piece handle=/oracle/oradata/backup/P58/arch_P58_T_10oebo55_820371621_32_1 tag=TAG20130710T010021
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oracle/oradata/oraarch/P58arch/1_779_801847121.dbf thread=1 sequence=779
unable to find archived log
archived log thread=1 sequence=780
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/11/2013 14:37:25
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 780 and starting SCN of 16677684

RMAN> run {
2> set until scn=16677684;
3> recover database;
4> alter database open resetlogs;
5> }

executing command: SET until clause

Starting recover at 15-JUL-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 15-JUL-13

database opened

RMAN> quit       


Recovery Manager complete.
oracle@SAPIDES:/oracle/oradata/backup/P58> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 15 12:39:38 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show database;
SP2-0158: unknown SHOW option "database"
SQL> select dbname from V$database;
select dbname from V$database
       *
ERROR at line 1:
ORA-00904: "DBNAME": invalid identifier


SQL> select name from v$database;

NAME
---------
P58

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
P58

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/P58/system01.dbf
/oracle/oradata/P58/sysaux01.dbf
/oracle/oradata/P58/undotbs01.dbf
/oracle/oradata/P58/users01.dbf
/oracle/oradata/P58/A2I_A2i_xCat_DBs_Ix.DBF
/oracle/oradata/P58/A2I_A2i_xCat_DBs.DBF
/oracle/oradata/P58/A2I_SRMMDMBMCatalog10_m000_Ix.DBF
/oracle/oradata/P58/A2I_SRMMDMBMCatalog10_m000.DBF
/oracle/oradata/P58/A2I_SRMMDMBMCatalog10_Z000_Ix.DBF
/oracle/oradata/P58/A2I_SRMMDMBMCatalog10_Z000.DBF

10 rows selected.

<!-- 正文结束 -->

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

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

注册时间:2009-12-22