rman恢复system表空间

最新推荐文章于 2024-04-22 16:15:40 发布

原创最新推荐文章于 2024-04-22 16:15:40 发布 · 748 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle 11g #rman

oracle rman 专栏收录该内容

9 篇文章

订阅专栏

本文通过模拟Oracle数据库中system表空间丢失的情景，详细记录了使用RMAN工具进行备份及恢复的具体步骤。从创建测试表开始，到完成数据文件的恢复并验证数据一致性。

本文描述：模拟恢复system表空间丢失并恢复的过程

试验如下:

(1)连接sqlplus，并创建一张表

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 6月 13 16:12:50 2017

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

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

SQL> create table temp as select * from scott.emp;

Table created.

(2)使用rman对数据看进行全备或者0级备份(代码为后来补充)

C:\Users\Administrator>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期四 6月 15 13:57:50 2017

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

connected to target database: ORCL (DBID=1468451227)

RMAN> backup database format 'g:\rmanbp\f%d%T%s.bak'; --全备如要备份日志文件则加上plus archivelog

Starting backup at 15-6月 -17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=F:\ORADATA\MATCH_INDEX01.DBF
input datafile file number=00008 name=F:\ORADATA\MATCH_DATA01.DBF
input datafile file number=00006 name=F:\ORADATA\BASE_DATA01.DBF
input datafile file number=00007 name=F:\ORADATA\BASE_INDEX01.DBF
input datafile file number=00010 name=F:\ORADATA\MATCHDETAIL_DATA01.DBF
input datafile file number=00011 name=F:\ORADATA\MATCHDETAIL_INDEX01.DBF
input datafile file number=00001 name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00004 name=F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 15-6月 -17
channel ORA_DISK_1: finished piece 1 at 15-6月 -17
piece handle=G:\RMANBP\FORCL2017061520.BAK tag=TAG20170615T135940 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:56
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 15-6月 -17
channel ORA_DISK_1: finished piece 1 at 15-6月 -17
piece handle=G:\RMANBP\FORCL2017061521.BAK tag=TAG20170615T135940 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-6月 -17

RMAN>

(3)删除system01.dbf(由于使用的是Win7，删除文件用360粉碎机)，查看表temp报错如下

SQL> select * from temp;
select * from temp
              *
ERROR at line 1:
ORA-01115: 从文件  读取块时出现 IO 错误 (块 # )
ORA-01110: 数据文件 1: 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-27070: 异步读取/写入失败
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 6) 句柄无效。

SQL> show user;    --这一步要有，不然rman登录报错(或者)
USER is "SYS"
SQL>

(4)登录rman进行恢复

C:\Users\Administrator>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期三 6月 14 09:40:57 2017

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     430075904 bytes

Fixed Size                     2176448 bytes
Variable Size                322964032 bytes
Database Buffers              96468992 bytes
Redo Buffers                   8466432 bytes

RMAN> run{
2> restore datafile 1;
3> recover datafile 1;
4> sql 'alter database open';
5> }

Starting restore at 14-6月 -17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 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 F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece G:\RMANBP\FDB_ORCL_20170613_14.BAK
channel ORA_DISK_1: piece handle=G:\RMANBP\FDB_ORCL_20170613_14.BAK tag=TAG20170613T123834
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 14-6月 -17

Starting recover at 14-6月 -17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 74 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCH
IVELOG\2017_06_13\O1_MF_1_74_DMYVCR6Q_.ARC
archived log for thread 1 with sequence 75 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCH
IVELOG\2017_06_13\O1_MF_1_75_DMYVTGMD_.ARC
archived log for thread 1 with sequence 76 is already on disk as file F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCH
IVELOG\2017_06_13\O1_MF_1_76_DMZ8PWJ1_.ARC
archived log file name=F:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_06_13\O1_MF_1_74_DMYVCR6Q_.ARC thre
ad=1 sequence=74
media recovery complete, elapsed time: 00:00:03
Finished recover at 14-6月 -17

sql statement: alter database open

RMAN>

(5)登录sqlplus查看结果

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 14 09:46:37 2017

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


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

SQL> select * from temp where rownum=1;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80            800
        20

SQL>

恢复成功！