首先在归档模式，通过rman对数据库进行了全库备份。
一、用户文件丢失
模拟丢失文件
>rm -rf
/u01/app/oracle/oradata/ORCL/newtest01.dbf
更新scn（让数据库知道文件丢失）
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication
channel
Process ID: 45510
Session ID: 470 Serial number: 27117
启动数据库（文件丢失，数据库只能启动到mount状态）
SQL> startup;
ORACLE instance started.
Total System Global Area 536868920 bytes
Fixed Size 8898616 bytes
Variable Size 461373440 bytes
Database Buffers 62914560 bytes
Redo Buffers 3682304 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5
- see DBWR trace file
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ORCL/newtest01.dbf'
将丢失文件置于offline，open数据库
SQL> alter database datafile 5 offline;
Database altered.
SQL> alter database open;
Database altered.
进入rman平台，通过DRA恢复数据
|
[oracle@host01
~]$ rman target / RMAN> list failure; using target
database control file instead of recovery catalog Database Role:
PRIMARY List of Database
Failures ========================= Failure ID
Priority Status Time Detected
Summary ----------
-------- --------- ------------- ------- 2 HIGH OPEN
10-OCT-24 One or more
non-system datafiles are missing RMAN> advise failure; Database Role:
PRIMARY List of Database
Failures ========================= Failure ID
Priority Status Time Detected
Summary ----------
-------- --------- ------------- ------- 2 HIGH OPEN
10-OCT-24 One or more
non-system datafiles are missing analyzing automatic
repair options; this may take some time allocated
channel: ORA_DISK_1 channel
ORA_DISK_1: SID=469 device type=DISK analyzing
automatic repair options complete Mandatory Manual
Actions ======================== no manual
actions available Optional Manual
Actions ======================= 1. If file
/u01/app/oracle/oradata/ORCL/newtest01.dbf was unintentionally renamed or
moved, restore it Automated Repair
Options ======================== Option Repair
Description ------
------------------ 1 Restore and recover datafile 5 Strategy: The repair includes complete
media recovery with no data loss Repair script:
/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_697897703.hm RMAN> repair failure; Strategy: The
repair includes complete media recovery with no data loss Repair script:
/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_697897703.hm contents of
repair script: # restore and recover datafile sql 'alter database datafile 5 offline'; restore ( datafile 5 ); recover datafile 5; sql 'alter database datafile 5 online'; Do you really
want to execute the above repair (enter YES or NO)? yes executing repair
script sql statement:
alter database datafile 5 offline Starting restore
at 10-OCT-24 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 00005 to
/u01/app/oracle/oradata/ORCL/newtest01.dbf channel
ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/autobackup/0637691f_1_1_ORCL_6.bak channel
ORA_DISK_1: piece
handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/0637691f_1_1_ORCL_6.bak
tag=TAG20241009T234343 channel
ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1:
restore complete, elapsed time: 00:00:02 Finished restore
at 10-OCT-24 Starting recover
at 10-OCT-24 using channel
ORA_DISK_1 starting media
recovery archived log for
thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_10_10/o1_mf_1_1_mjgzd78j_.arc archived log for
thread 1 with sequence 2 is already on disk as file
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_10_10/o1_mf_1_2_mjh0p7rb_.arc archived log for
thread 1 with sequence 3 is already on disk as file
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_10_10/o1_mf_1_3_mjh1f227_.arc archived log for
thread 1 with sequence 4 is already on disk as file
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_10_10/o1_mf_1_4_mjk2fz3c_.arc archived log
file
name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_10_10/o1_mf_1_1_mjgzd78j_.arc
thread=1 sequence=1 archived log
file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_10_10/o1_mf_1_2_mjh0p7rb_.arc
thread=1 sequence=2 media recovery
complete, elapsed time: 00:00:01 Finished recover
at 10-OCT-24 sql statement:
alter database datafile 5 online repair failure
complete |
二、系统文件丢失
系统文件丢失，损坏和用户文件丢失恢复方法类似，唯一不同就是系统文件只能在mount状态进行修复
模拟文件丢失、损坏
[root@host01 ~]# rm -rf
/u01/app/oracle/oradata/ORCL/system01.dbf
查看数据库状态
通过DRA修复数据库
RMAN>list failure;
RMAN>advise failure;
RMAN>repair failure;
三、参数文件丢失
1、参数文件可以通过pfile进行创建
可以通过提前创建pfile文件
create pfile from spfile;
等数据库的spfile文件损坏或者丢失时，通过执行
2、可以通过rman的备份文件对spfile进行恢复
将数据库运行到nomount状态
RMAN>
startup nomount;
还原spfile文件
RMAN>
restore spfile from
'/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2024_10_10/o1_mf_s_1182023193_mjk4d9xs_.bkp';
启动数据库
SQL>
shutdown immediate;
SQL>
startup;
四、 控制文件丢失
模拟控制文件丢失
[oracle@host01
dbs]$ mv /u01/app/oracle/oradata/ORCL/control01.ctl /u01/app/oracle/oradata/ORCL/control01.ctl_bak
[oracle@host01
dbs]$ mv /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl_bak
强制关闭数据库
SQL>
shutdown abort;
ORACLE
instance shut down.
通过rman将数据库运行到nomount状态
RMAN>
startup nomount;
RMAN>restore
controlfile from autobackup;
RMAN>sql
'alter database mount';
恢复并打开数据库
RMAN>recover
database;
RMAN>sql
'alter database open resetlogs‘’;
五、恢复误删除的表空间（不完整恢复）
被删除的表空间已在rman中做了备份
恢复误删除表空间，RMAN 必须通过备份的控制文件（即含有删除的表空间结构的老
控制文件）进行恢复。
1.删除表空间
SQL> drop
tablespace newtest including contents and datafiles;
2.打开告警日志，查看drop tablespace时间点
3. 删除所有控制文件和数据文件
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
SQL> !rm -rf
/u01/app/oracle/oradata/ORCL/*.dbf
SQL> show parameter control
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/ORCL/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/ORCL/contro
l02.ctl
SQL> !rm
/u01/app/oracle/oradata/ORCL/control01.ctl
SQL> !rm
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
4. 停止数据库
SQL>shutdown immediate;
5. 利用 RMAN 做不完全恢复
run{
startup force nomount;
set dbid=1608807846;
restore controlfile from autobackup
until time "to_date('2024-10-14 19:18:00','yyyy-mm-dd hh24:mi:ss')";
alter database mount;
set until time
"to_date('2024-10-14 19:18:00','yyyy-mm-dd hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}
6.验证表空间是否恢复
6.验证表空间是否恢复
六、恢复误删除的表
Oracle 为每个用户（模式）“分配”一个回收站，但这个回收站实际实际并不会开辟空间（只是个逻辑容器），当 Drop Table 时（非 Purge），原来的表所使用的段中的数据并没有真正的删除，而是把 Table 的段名以回收站方式重命名。该段所在表空间不足需要扩展时，回收站中的信息会被自动清除。官方文档：回收站所使用的空间永远不会自动回收，除非有空间的压力。
System 表空间的对象没有回收站，所以在 sys 下缺省使用 System 表空间时，Drop
Table 会直接删除对象。
数据库在开启闪回功能的情况下，可以直接通过闪回技术对表进行恢复。
1、 查看是否开启闪回
2、 查看表和回收站（recyclebin）
3、 删除表，查看回收站
4、 闪回删除的表
flashback table tt to before drop（rename
to tt_old）;
