oracle rman恢复表空间,Oracle RMAN 表空间的完全恢复

如果数据库打开，有部分数据文件损坏，可以在不影响其他数据文件的情况下，对损坏的数据文件进行恢复。

--对数据库进行备份

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;

Starting backup at 2016-01-18 23:33:13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=8 RECID=4 STAMP=901495908

input archived log thread=1 sequence=9 RECID=5 STAMP=901495932

input archived log thread=1 sequence=10 RECID=6 STAMP=901495947

input archived log thread=1 sequence=11 RECID=7 STAMP=901496002

channel ORA_DISK_1: starting piece 1 at 2016-01-18 23:33:25

channel ORA_DISK_1: finished piece 1 at 2016-01-18 23:33:40

piece handle=/u01/app/oracle/flash_recovery_area/NEO/backupset/2016_01_18/o1_mf_annnn_TAG20160118T233324_c9t1g580_.bkp tag=TAG20160118T233324 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_8_c9t1c26j_.arc RECID=4 STAMP=901495908

archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_9_c9t1crdx_.arc RECID=5 STAMP=901495932

archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_10_c9t1d6ym_.arc RECID=6 STAMP=901495947

archived log file name=/u01/app/oracle/flash_recovery_area/NEO/archivelog/2016_01_18/o1_mf_1_11_c9t1g2w2_.arc RECID=7 STAMP=901496002

Finished backup at 2016-01-18 23:33:40

--查看数据文件

[oracle@localhost neo]$ ls -trl

总计 1728264

-rw-r----- 1 oracle oinstall  10493952 01-18 22:30 users02.dbf_bk

-rw-r----- 1 oracle oinstall  30416896 01-18 23:23 temp01.dbf

-rw-r----- 1 oracle oinstall  52429312 01-18 23:33 redo02.log

-rw-r----- 1 oracle oinstall  52429312 01-18 23:35 redo03.log

-rw-r----- 1 oracle oinstall  10493952 01-18 23:35 users02.dbf

-rw-r----- 1 oracle oinstall 145498112 01-18 23:35 users01.dbf

-rw-r----- 1 oracle oinstall 104865792 01-18 23:35 example01.dbf

-rw-r----- 1 oracle oinstall  89137152 01-19 00:05 undotbs01.dbf

-rw-r----- 1 oracle oinstall 713039872 01-19 00:05 system01.dbf

-rw-r----- 1 oracle oinstall 503324672 01-19 00:05 sysaux01.dbf

-rw-r----- 1 oracle oinstall  52429312 01-19 00:07 redo01.log

-rw-r----- 1 oracle oinstall   9748480 01-19 00:07 control01.ctl

--将 USERS 表空间中的一个数据文件改名

[oracle@localhost neo]$ mv users02.dbf users02.dbf_bk

--验证数据文件

RMAN> validate datafile 6;

Starting validate at 2016-01-19 00:09:47

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of validate command on ORA_DISK_1 channel at 01/19/2016 00:09:47

ORA-01122: database file 6 failed verification check

ORA-01110: data file 6: '/ORADATA/neo/users02.dbf'

ORA-01565: error in identifying file '/ORADATA/neo/users02.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

--将损坏的表空间离线

RMAN> SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE";

sql statement: ALTER TABLESPACE users OFFLINE IMMEDIATE

--查看损坏的数据文件

select name,status from v$datafile;

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

/ORADATA/neo/system01.dbf                                         SYSTEM

/ORADATA/neo/sysaux01.dbf                                         ONLINE

/ORADATA/neo/undotbs01.dbf                                        ONLINE

/ORADATA/neo/users01.dbf                                          RECOVER

/ORADATA/neo/example01.dbf                                        ONLINE

/ORADATA/neo/users02.dbf                                          RECOVER

--进行表空间恢复

RMAN> RESTORE TABLESPACE users;

Starting restore at 2016-01-19 00:11:23

using channel ORA_DISK_1

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 00004 to /ORADATA/neo/users01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /ORADATA/neo/users02.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/NEO/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T233340_c9t1gsmg_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/NEO/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T233340_c9t1gsmg_.bkp tag=TAG20160118T233340

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 2016-01-19 00:11:48

RMAN>  RECOVER TABLESPACE users;

Starting recover at 2016-01-19 00:12:03

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 2016-01-19 00:12:04

--将表空间置为在线状态

RMAN> SQL "ALTER TABLESPACE users ONLINE";

sql statement: ALTER TABLESPACE users ONLINE

--查看数据文件的状态

select name,status from v$datafile;

NAME                                                              STATUS

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

/ORADATA/neo/system01.dbf                                         SYSTEM

/ORADATA/neo/sysaux01.dbf                                         ONLINE

/ORADATA/neo/undotbs01.dbf                                        ONLINE

/ORADATA/neo/users01.dbf                                          ONLINE

/ORADATA/neo/example01.dbf                                        ONLINE

/ORADATA/neo/users02.dbf                                          ONLINE

--下面是告警日志中的信息

[oracle@localhost trace]$ tailf alert_neo.log

Mon Jan 18 23:23:06 2016

Errors in file /u01/app/oracle/diag/rdbms/neo/neo/trace/neo_m001_3398.trc:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/ORADATA/neo/users02.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Mon Jan 18 23:25:09 2016

ALTER TABLESPACE users OFFLINE IMMEDIATE

Completed: ALTER TABLESPACE users OFFLINE IMMEDIATE

Mon Jan 18 23:27:17 2016

Checker run found 2 new persistent data failures

Mon Jan 18 23:27:18 2016

Full restore complete of datafile 6 /ORADATA/neo/users02.dbf.  Elapsed time: 0:00:00

checkpoint is 846683

Full restore complete of datafile 4 /ORADATA/neo/users01.dbf.  Elapsed time: 0:00:01

checkpoint is 846683

Mon Jan 18 23:27:46 2016

alter database recover datafile list clear

Completed: alter database recover datafile list clear

alter database recover if needed

tablespace USERS

Media Recovery Start

Serial Media Recovery started

Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0

Mem# 0: /ORADATA/neo/redo01.log

Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0

Mem# 0: /ORADATA/neo/redo02.log

Media Recovery Complete (neo)

Completed: alter database recover if needed

tablespace USERS

Mon Jan 18 23:28:42 2016

ALTER TABLESPACE users ONLINE

Completed: ALTER TABLESPACE users ONLINE

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