rman 异机指定时间点不完全恢复

最新推荐文章于 2023-03-02 17:15:26 发布

cnk45946

最新推荐文章于 2023-03-02 17:15:26 发布

阅读量212

点赞数

文章标签：数据库操作系统运维

一环境

OS:

源
$cat /etc/redhat-release
uname -aCentOS release 5.4 (Final)
$uname -a
Linux dbserver 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

目的:
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
$ uname -a
Linux appserver1 2.6.18-164.el5PAE #1 SMP Tue Aug 18 15:59:11 EDT 2009 i686 i686 i386 GNU/Linux

DB:

源:
$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 28 14:10:52 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

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

SQL> set lines 180;
SQL>
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  10.2.0.4.0      Production
Oracle Database 10g Enterprise Edition                  10.2.0.4.0      64bi
PL/SQL                                                  10.2.0.4.0      Production
TNS for Linux:                                          10.2.0.4.0      Production

目的:
$ export ORACLE_SID=testdb1
[oracle10@appserver1 oradata]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 28 14:11:38 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL>

二描述

源库由于误运行某数据库user重建脚本drop table,drop index,drop sequence.需找回丢失的用户数据及状态.由于不能影响数据库其他user的使用.所以采用rman基于时间点的不完全恢复恢复用户状态.恢复后用exp/imp做用户恢复.最初构想用源DB的rman备份文件在源库OS上恢复成另一个instance name的数据库.rman恢复pfile,修改pfile中的数据文件位置,从rman备份文件中恢复control file,然后start mount的时候报数据库在排他模式.看来用rman将备份恢复在同一个系统不能成功.修改恢复方案为用rman将备份恢复到另一台目的OS上.目的OS上已装有oracle software.且无与源库相同dbname在运行.恢复思想如下:
1.检查源,目的两端OS与DB版本.
2.在目的库OS上创建相应源库的备份存储目录.新数据文件的存储目录.将源库archive log传输到目的库archive_dest参数的目录下.将源库rman备份文件传输到目的OS上.访问路径要与源OS存储路径相同.
3.恢复pfile.
4.恢复control file.
5.运行rman恢复脚本.
6.alter database open resetlogs
7.exp
8.imp

可参考文档: "standby全库rman备份文件恢复到异机" http://space.itpub.net/11780477/viewspace-631564

三结构

源OS IP: 192.168.1.152
目的OS IP: 192.168.1.151

源DB数据库系统用户,用户组: oracle,dba
目的DB数据库系统用户,用户组: oracle10,dba

源DB数据文件目录: /oracle/database/oradata/testdb1/
源DB归档日志文件目录: /oracle/database/arch/testdb1
源DB rman 备份文件目录: /oracle/database/backup/rman/testdb1/
目的DB数据文件目录: /home/oracle10/oradata/testdb1
目的DB归档日志文件目录: /home/oracle10/oradata/testdb1/arch

四步骤

1.目的库OS创建相应目录:

mkdir -p /oracle/database/backup/rman/testdb1/
chown -R oracle10:dba /oracle/
mkdir -p /home/oracle10/admin/testdb1/adump
mkdir -p /home/oracle10/admin/testdb1/bdump
mkdir -p /home/oracle10/admin/testdb1/cdump
mkdir -p /home/oracle10/admin/testdb1/udump
mkdir -p /home/oracle10/oradata/testdb1/arch
传输源归档日志文件到目的库OS上.(源OS操作)
tar -zcvf - ./* | ssh oracle10@192.168.1.151 "tar -zxf - -C ' /oracle/database/arch/testdb1'"
传输源rman备份文件到目的库OS上.(源OS操作)
cd /oracle/database/backup/rman/testdb1/
tar -zcvf - ./* |ssh oracle10@192.168.1.151 "tar -zxf - -C '/oracle/database/backup/rman/testdb1/'"

2.恢复pfile

export ORACLE_SID=testdb1
rman target /
set dbid 455988423
startup nomount;
restore spfile to pfile '/home/oracle10/database/10.2.0/db_1/dbs/inittestdb1.ora' from '/home/oracle10/backup/rman/rtestdb1/ctl_c-455988423-20111025-00';

3.修改生成的inittestdb1.ora文件中目录位置.

4.用新pfile启动数据库

startup force nomount pfile='/home/oracle10/database/10.2.0/db_1/dbs/inittestdb1.ora';

5.从备份文件中恢复control file

restore controlfile from '/home/oracle10/backup/rman/rtestdb1/ctl_c-455988423-20111025-00';

6.使用新control file

alter database mount;

7.另开一terminal 进入sqlplus 生成rman恢复脚本

export ORACLE_SID=testdb1
sqlplus / as sysdba
set head off
set lines 100
set pages 500
col name for a60
set feedback off
spool /home/oracle10/spool_datafile#.txt
select 'set newname for datafile '||file#||' to '||''''||name||''''||';' from v$datafile;
spool off

8.编辑上步中的 spool_datafile#.txt （完善上一步，将源库目录结构改变成异机的目录结构）.完善后的spool_datafile#.txt 文件部分内容.

$ cat spool_datafile#.txt
run{
set newname for datafile 1 to '/home/oracle10/oradata/testdb1/system01.dbf';
set newname for datafile 2 to '/home/oracle10/oradata/testdb1/undotbs01.dbf';
set newname for datafile 3 to '/home/oracle10/oradata/testdb1/sysaux01.dbf';
... ...
set newname for datafile 186 to '/home/oracle10/oradata/testdb1/drugusr_test01.dbf';
set newname for datafile 187 to '/home/oracle10/oradata/testdb1/cmp_ras_bak01.dbf';
set newname for datafile 188 to '/home/oracle10/oradata/testdb1/logmnr01.dbf';
restore database;
switch datafile all;
recover database until time '2011-10-27 15:38:00';
}

9.执行spool_datafile#.txt 恢复文件

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export ORACLE_SID=testdb1
rman target /
@/home/oracle10/spool_datafile#.txt

10.open 数据库

alter database open resetlogs;

注:我在open DB的时候报错 (alert.log中的一段)
alter database open resetlogs
Thu Oct 27 20:02:54 2011
Errors in file /home/oracle10/admin/testdb1/bdump/testdb1_j001_14268.trc:
ORA-12012: error on auto execute of job 42781
ORA-06553: PLS-ORA-06553: PLS-801: internal error [56319]

在 http://www.cnblogs.com/ggbbeyou/archive/2008/12/17/1356919.html 找到解决办法. 注意执行顺序.
SQL>ALTER PACKAGE STANDARD COMPILE;
SQL>ALTER PACKAGE DBMS_STANDARD COMPILE;
SQL>@?\rdbms\admin\catalog.sql
SQL>@?\rdbms\admin\CATPROC.SQL
SQL>@?\rdbms\admin\DBMSSUTIL.SQL