1.使用rman备份恢复 --非归档只能在mount阶段能进行备份
2.使用OS备份恢复
1.system丢失
2.undo丢失
3.其他数据文件丢失
2.使用OS备份恢复
1.system丢失
- 关闭数据库。
- 还原备份文件,控制文件,数据文件,日志文件,联机日志文件。
- 通过noredo恢复数据库。
- 如果有联机日志备份不需要恢复。
- 打开数据库,整个数据库恢复到备份时间点,备份后的数据需要人为输入。
2.undo丢失
- 更改undo_management=manual。
- startup force mount;
- alter database datafile <> offline drop;
- alter database open;
- create undo tablespace <>;
- alter system set undo_tablespace=<> scope=spfile;
- alter system set undo_management=auto scope=spfile;
- shutdown immediate;
- startup
3.其他数据文件丢失
- 恢复数据文件到备份状态后利用传输表空间导出丢失的数据文件。
- 在丢失数据文件的状态下,删除丢失表空间,传输表空间导入。
--实验
SYS@PROD2> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 1
SYS@PROD2> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm1 ~]$ rman target/
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Dec 11 22:32:38 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD2 (DBID=1564287740)
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 958341120 bytes
Fixed Size 1348972 bytes
Variable Size 322964116 bytes
Database Buffers 629145600 bytes
Redo Buffers 4882432 bytes
RMAN> backup database --进行备份
2> ;
Starting backup at 11-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 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=00001 name=/u01/app/oracle/oradata/PROD2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD2/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD2/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD2/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-DEC-16
channel ORA_DISK_1: finished piece 1 at 11-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD2/backupset/2016_12_11/o1_mf_nnndf_TAG20161211T223318_d4trxgy6_.bkp tag=TAG20161211T223318 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:07
Finished backup at 11-DEC-16
Starting Control File Autobackup at 11-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD2/autobackup/2016_12_11/o1_mf_n_930349969_d4ts1h91_.bkp comment=NONE
Finished Control File Autobackup at 11-DEC-16
RMAN> alter database open;
database opened
[oracle@ocm1 ~]$ rm /u01/app/oracle/oradata/PROD2/system01.dbf --删除sys文件
[oracle@ocm1 ~]$ rman target/ --无法连接因为系统文件数据字典丢失
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Dec 11 22:42:51 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD2/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@PROD2> shutdown abort
ORACLE instance shut down.
SYS@PROD2> startup mount
ORACLE instance started.
Total System Global Area 958341120 bytes
Fixed Size 1348972 bytes
Variable Size 322964116 bytes
Database Buffers 629145600 bytes
Redo Buffers 4882432 bytes
Database mounted.
SYS@PROD2> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm1 ~]$ rman target/
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Dec 11 22:43:57 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD2 (DBID=1564287740, not open)
RMAN> restore database;
Starting restore at 11-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 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 /u01/app/oracle/oradata/PROD2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD2/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD2/backupset/2016_12_11/o1_mf_nnndf_TAG20161211T223318_d4trxgy6_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD2/backupset/2016_12_11/o1_mf_nnndf_TAG20161211T223318_d4trxgy6_.bkp tag=TAG20161211T223318
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 11-DEC-16
RMAN> recover database noredo;
Starting recover at 11-DEC-16
using channel ORA_DISK_1
Finished recover at 11-DEC-16
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/11/2016 22:47:17
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/11/2016 22:47:24
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD2/system01.dbf'
RMAN> exit
RMAN中无法打开数据库,因为在RMAN中永远都需要日志文件,所以直接sqlplus连接。
Recovery Manager complete.
[oracle@ocm1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 11 22:47:49 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@PROD2> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery
SYS@PROD2> recover database; --恢复数据库
Media recovery complete.
SYS@PROD2> alter database open;
Database altered.