首页 > 代码库 > 冷备手工完全恢复（recover database，recover tablespace，recover datafile）
冷备手工完全恢复（recover database，recover tablespace，recover datafile）
冷备手工完全恢复
1. 手工完全恢复三种级别：
recover database： 所有或大部分datafile丢失，一般是在mount状态完成。
recover tablespace： 非关键表空间损坏，表空间下某些数据文件不能访问，一般是在open下完成。
recover datafile： 单一或少数数据文件损坏，可以在mount或open 状态完成。
四个关键文件：1）system01.dbf, 2) undo tablespace，3）control file 4）current log file
2. 手工完全恢复前提: 1）有一套datafile全备, 2）使用当前控制文件, 3）自上次备份以来的归档日志和当前联机日志是完整的
3. 实验1：（recover database ）
3.1 查看数据库当前状态，准备好冷备。
SQL> select * from andy;
ID
----------
1
--生成冷备脚本
SQL> select ‘ho cp ‘ || name || ‘ /home/oracle/coldbak‘ from v$controlfile;
‘HOCP‘||NAME||‘/HOME/ORACLE/COLDBAK‘
--------------------------------------------------------------------------------
ho cp /home/oracle/app/oradata/orcl/control01.ctl /home/oracle/coldbak
ho cp /home/oracle/app/flash_recovery_area/orcl/control02.ctl /home/oracle/coldbak
SQL> select ‘ho cp ‘ || name || ‘ /home/oracle/coldbak‘ from v$datafile;
‘HOCP‘||NAME||‘/HOME/ORACLE/COLDBAK‘
--------------------------------------------------------------------------------
ho cp /home/oracle/app/oradata/orcl/system01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/sysaux01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/undotbs01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/users01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/tbtb01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/ogg01.dbf /home/oracle/coldbak
6 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--冷备
SQL> ho cp /home/oracle/app/oradata/orcl/control01.ctl /home/oracle/coldbak
SQL> ho cp /home/oracle/app/flash_recovery_area/orcl/control02.ctl /home/oracle/coldbak
SQL> ho cp /home/oracle/app/oradata/orcl/system01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/sysaux01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/undotbs01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/users01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/tbtb01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/ogg01.dbf /home/oracle/coldbak
--检查冷备
[oracle@11g coldbak]$ ll
total 1997776
-rw-r-----. 1 oracle oinstall 9748480 Dec 10 06:22 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 Dec 10 06:25 control02.ctl
-rw-r-----. 1 oracle oinstall 408748032 Dec 10 06:32 ogg01.dbf
-rw-r-----. 1 oracle oinstall 639639552 Dec 10 06:31 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 734011392 Dec 10 06:31 system01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Dec 10 06:32 tbtb01.dbf
-rw-r-----. 1 oracle oinstall 99622912 Dec 10 06:32 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 39329792 Dec 10 06:32 users01.dbf
SQL> startup;
ORACLE instance started.
SQL> insert into andy values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from andy;
ID
----------
1
2
3.2 模拟介质失败，所有数据文件丢失
[oracle@11g orcl]$ rm -rf *.dbf //数据库在打开的情况下就删掉
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 729812824 bytes
Database Buffers 331350016 bytes
Redo Buffers 5554176 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: ‘/home/oracle/app/oradata/orcl/system01.dbf‘
3.3 完全恢复流程
--查看需要恢复的文件
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 FILE NOT FOUND
2 FILE NOT FOUND
3 FILE NOT FOUND
4 FILE NOT FOUND
5 FILE NOT FOUND
6 FILE NOT FOUND
6 rows selected.
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1969481
2 1969481
3 1969481
4 1969481
5 1969481
6 1969481
6 rows selected.
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 0
2 0
3 0
4 0
5 0
6 0
6 rows selected.
a 首先还原所有数据文件
[oracle@11g orcl]$ cp /home/oracle/coldbak/*.dbf /home/oracle/app/oradata/orcl/
b 恢复database
SQL> recover database;
Media recovery complete.
c 打开数据库
SQL> alter database open;
Database altered.
d 验证
SQL> select * from andy;
ID
----------
1
2
实验2： recover tablespace （状态：database open)
说明：针对的是非关键表空间的损坏，基于表空间的完全恢复实际上还是对其下的datafile的恢复
模拟这种情形非常实用，通常是某个非关键表空间下的数据文件受损，但并没有造成Oracle崩溃, 我们只需针对个别有问题的tablespace去做单独的在线恢复操作，也就是说恢复时数据库整体是online的,而局部表空间是offline的，数据库不需要shutdown。
1)了解一下当前状态，有个 LZY 表空间
SQL> col file_name for a60
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ------------------------------------------------------------ -------------------------
4 /home/oracle/app/oradata/orcl/users01.dbf USERS
3 /home/oracle/app/oradata/orcl/undotbs01.dbf UNDOTBS1
2 /home/oracle/app/oradata/orcl/sysaux01.dbf SYSAUX
1 /home/oracle/app/oradata/orcl/system01.dbf SYSTEM
5 /home/oracle/app/oradata/orcl/tbtb01.dbf LZY
6 /home/oracle/app/oradata/orcl/ogg01.dbf GOLDGATE
6 rows selected.
2）准备实验数据
SQL> create table andy.andydemo(id int) tablespace lzy;
Table created.
SQL> inset into andy.andydemo values(1);
SP2-0734: unknown command beginning "inset into..." - rest of line ignored.
SQL> insert into andy.andydemo values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from andy.andydemo;
ID
----------
1
3）模拟表空间损坏，数据库open下，直接删除表空间下的数据文件
SQL> ho rm -rf /home/oracle/app/oradata/orcl/tbtb01.dbf
4）查证该表空间上的表不可访问了
SQL> alter system flush buffer_cache; --清除data buffer
System altered.
SQL> select * from andydemo;
select * from andydemo
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: ‘/home/oracle/app/oradata/orcl/tbtb01.dbf‘
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
5）看看scn的情况
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1969484
2 1969484
3 1969484
4 1969484
5 1969484
6 1969484
6 rows selected.
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1969484
2 1969484
3 1969484
4 1969484
5 0
6 1969484
6 rows selected.
说明：
v$datafile是查看来自控制文件里记录的数据文件scn信息。
v$datafile_header是查看数据文件头中数据文件scn信息。
6）表空间offline --immediate使表空间能立即脱机，不等Oracle对任何数据文件做检查
SQL> alter tablespace lzy offline immediate;
Tablespace altered.
7）数据库open下，使用备份还原这个表空间下的所有数据文件。
SQL> ho cp /home/oracle/coldbak/tbtb01.dbf /home/oracle/app/oradata/orcl/
8）恢复tablespace
SQL> recover tablespace lzy;
Media recovery complete.
9）使表空间online
SQL> alter tablespace lzy online;
Tablespace altered. //注意：此时数据库状态一直是open的。
10) 检验
SQL> select * from andydemo;
ID
----------
1
实验3： （recover datafile，database mount或open状态）
恢复datafile, 同实验2不同的是模拟UNDO文件损坏: 因UNDO数据文件也是关键文件，所以只能在mount状态下恢复。
1） 模拟环境：
SQL> delete andy.andy; //注意：删掉了andy并提交，老值在UNDO里。
3 rows deleted.
SQL>commit；
2）在open 状态下删除datafile
SQL> ho rm -rf /home/oracle/app/oradata/orcl/undotbs01.dbf
SQL> insert into andy.andy values(4); /undo文件已经不在了，dbwr未来得及刷新，此条记录在redo里
SQL>commit；
3）关闭数据库
SQL> shutdown abort;
ORACLE instance shut down. //abort埋下伏笔，等到完全恢复时会做UNDO回滚。
4) 启动数据库mount
SQL> startup;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 729812824 bytes
Database Buffers 331350016 bytes
Redo Buffers 5554176 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: ‘/home/oracle/app/oradata/orcl/undotbs01.dbf‘
5）还原并恢复UNDO数据文件
SQL> ho cp /home/oracle/coldbak/undotbs01.dbf /home/oracle/app/oradata/orcl/undotbs01.dbf
SQL> recover datafile 3
Media recovery complete.
6）打开数据库（会完成UNDO表空间数据的回滚）
SQL> alter database open;
Database altered.
7) 验证
SQL> select * from andy;
ID
----------
4
总结：有全冷备条件下三种级别恢复流程。 （相应文件名视自己的环境而定）
recover database:
SQL> select file#,error from v$recover_file;
SQL> select file#,checkpoint_change# from v$datafile;
SQL> select file#,checkpoint_change# from v$datafile_header;
SQL> ho cp /home/oracle/coldbak/*.dbf /home/oracle/app/oradata/orcl/
SQL> recover database;
SQL> alter database open;
recover tablespace:
SQL> select file#,error from v$recover_file;
SQL> select file_id,file_name,tablespace_name from dba_data_files;
SQL> select file#,checkpoint_change# from v$datafile;
SQL> select file#,checkpoint_change# from v$datafile_header;
SQL> alter tablespace lzy offline immediate;
SQL> ho cp /home/oracle/coldbak/tbtb01.dbf /home/oracle/app/oradata/orcl/
SQL> recover tablespace lzy;
SQL> alter tablespace lzy online;
recover datafile:
SQL> select file#,error from v$recover_file;
SQL> select file#,checkpoint_change# from v$datafile;
SQL> select file#,checkpoint_change# from v$datafile_header;
SQL> ho cp /home/oracle/coldbak/undotbs01.dbf /home/oracle/app/oradata/orcl/undotbs01.dbf
SQL> recover datafile 3
SQL> alter database open;
OK，转载请标明出处。
冷备手工完全恢复（recover database，recover tablespace，recover datafile）