oracle 根据控制与参数文件恢复
技术原理：控制文件记录数据库的物理结构
控制文件还记录了数据文件的元数据信息
所以，可以根据控制文件，将这个损坏的数据文件重建；
但是重建之后的数据文件，没有数据的记载；
再根据日志（归档日志和在线重做日志）来recover这个数据文件；
技术应用限制：
1.数据文件创建之后，控制文件没有变化过；
2.自数据文件创建之后的所有的日志文件，需要全部存在；
3.数据库建库是就生成的数据文件不能应用这个技术；
--新建一个表空间
SYS@orcl11g> create tablespace tbs04
2datafile '/u01/app/oracle/oradata/orcl11g/tbs04.dbf'
3size 50m;
Tablespace created.
--在该表空间上存放数据
SYS@orcl11g> alter user hr quota unlimited on tbs04;
HR@orcl11g> create table e tablespace tbs04 as select * from hr.employees;
HR@orcl11g> create table d tablespace tbs04 as select * from hr.departments;
--切换日志
SYS@orcl11g> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 78
Next log sequence to archive 80
Current log sequence 80
SYS@orcl11g> alter system switch logfile;
SYS@orcl11g> alter system switch logfile;
--更新表空间上的数据
SYS@orcl11g> update hr.e set salary=salary+1;
SYS@orcl11g> commit;
SYS@orcl11g> alter system switch logfile;
SYS@orcl11g> update hr.d set department_id=department_id+1;
SYS@orcl11g> commit;
SYS@orcl11g> alter system switch logfile;
--模拟破坏数据文件
SYS@orcl11g> host cp /etc/passwd /u01/app/oracle/oradata/orcl11g/tbs04.dbf
--让数据库发现数据文件损坏
SYS@orcl11g> alter system flush buffer_cache; -- 清空缓冲区
--因为ckpt进程，检查点无法完成，所以实例被中断；
--重新启动，发现问题
SYS@orcl11g> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area422670336 bytes
Fixed Size 1345380 bytes
Variable Size 318769308 bytes
Database Buffers 96468992 bytes
Redo Buffers 6086656 bytes
Database mounted.
SYS@orcl11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl11g/tbs04.dbf'
SYS@orcl11g> col error for a20
SYS@orcl11g> l
1* select * from v$recover_file
SYS@orcl11g> /
FILE# ONLINEONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- -------------------
10 ONLINEONLINEFILE NOT FOUND 0 (null)
--根据控制文件的信息，创建一个新的数据文件
SYS@orcl11g> alter database create datafile '/u01/app/oracle/oradata/orcl11g/tbs04.dbf'
as '/u01/app/oracle/oradata/orcl11g/tbs04.dbf';
SYS@orcl11g> select * from v$recover_file;
FILE# ONLINEONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- -------------------
10 ONLINEONLINE(null) 1790076 2013-06-24 09:43:56
SYS@orcl11g> select checkpoint_change#,last_change# from v$datafile
2* where file#=10
SYS@orcl11g> /
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
1790559 (null)
SYS@orcl11g> select checkpoint_change# from v$datafile_header where file#=10
SYS@orcl11g> /
CHECKPOINT_CHANGE#
------------------
1790076
--文件的检查点信息比控制文件的检查点信息旧，需要提升数据文件的检查点状态
--通过日志修复
SYS@orcl11g> select * from v$recovery_log;
THREAD#SEQUENCE# TIME
---------- ---------- -------------------
ARCHIVE_NAME
--------------------------------------------------------------------------------
1 80 2013-06-21 16:10:04
/u01/app/oracle/arch/1_80_816622368.dbf
1 81 2013-06-24 09:46:48
/u01/app/oracle/arch/1_81_816622368.dbf
--需要以上两个归档日志和在线重做日志
SYS@orcl11g> recover datafile 10;
ORA-00279: change 1790076 generated at 06/24/2013 09:43:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_80_816622368.dbf
ORA-00280: change 1790076 for thread 1 is in sequence #80
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1790550 generated at 06/24/2013 09:46:48 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_81_816622368.dbf
ORA-00280: change 1790550 for thread 1 is in sequence #81
Log applied.
Media recovery complete.
--查看文件的状态
SYS@orcl11g> select checkpoint_change# from v$datafile_header
2where file#=10;
CHECKPOINT_CHANGE#
------------------
1810656
SYS@orcl11g> select checkpoint_change#,last_change# from v$datafile
2where file#=10;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
1810656 1810656
--打开数据库
SYS@orcl11g> alter database open;
Database altered.
--检查数据情况
页:
[1]