一、模拟情景
数据库有全库备份。丢失全部数据文件和控制文件,拥有全部归档日志和在线日志文件,后增加的文件无备份。以下是一种恢复情况。
三、做数据库冷备
四、新建数据文件和测试表
五、模拟环境,丢失全部数据文件和控制文件
六、恢复
此案例丢失控制文件,从备份控制文件进行恢复,在进行恢复中,会向控制文件中以缺省规则增加文件,我们可以通过CREATE AS方式修改控制文件中记录.继续应用所有归档和日志文件可以完成恢复.
--END--
数据库有全库备份。丢失全部数据文件和控制文件,拥有全部归档日志和在线日志文件,后增加的文件无备份。以下是一种恢复情况。
二、测试环境
SYS@testdb>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SYS@testdb>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /ora_data/oraarch
Oldest online log sequence 38
Next log sequence to archive 40
Current log sequence 40
三、做数据库冷备
SYS@testdb>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
tempapp@ora10g[#/ora_data]mkdir bak
tempapp@ora10g[#/ora_data]cd $ORACLE_BASE/oradata
tempapp@ora10g[#/oracle/ora10g/oradata]ll
total 7233286
-rw-r----- 1 ora10g oinstall 7159808 Jun 6 14:51 control01.ctl
-rw-r----- 1 ora10g oinstall 7159808 Jun 6 14:51 control02.ctl
-rw-r----- 1 ora10g oinstall 209723392 Jun 4 14:54 jtitsm.data1
-rw-r----- 1 ora10g oinstall 52436992 Jun 6 14:51 owi01.dbf
-rw-r----- 1 ora10g oinstall 104858624 Jun 6 14:51 redo01.log
-rw-r----- 1 ora10g oinstall 104858624 Jun 6 10:11 redo02.log
-rw-r----- 1 ora10g oinstall 104858624 Jun 6 10:11 redo03.log
-rw-r----- 1 ora10g oinstall 340795392 Jun 6 14:51 sysaux01.dbf
-rw-r----- 1 ora10g oinstall 340795392 Jun 6 14:51 system01.dbf
-rw-r----- 1 ora10g oinstall 1073750016 Jun 6 14:51 system02.dbf
-rw-r----- 1 ora10g oinstall 209723392 Jun 4 15:18 temptbs01.dbf
-rw-r----- 1 ora10g oinstall 20979712 Jun 6 14:51 testtbs01.dbf
-rw-r----- 1 ora10g oinstall 209723392 Jun 6 14:51 undotbs01.dbf
-rw-r----- 1 ora10g oinstall 1073750016 Jun 6 14:51 users01.dbf
-rw-r----- 1 ora10g oinstall 52436992 Jun 6 14:51 users02.dbf
tempapp@ora10g[#/oracle/ora10g/oradata]
tempapp@ora10g[#/oracle/ora10g/oradata]cp *.ctl /ora_data/bak/
tempapp@ora10g[#/oracle/ora10g/oradata]cp *.dbf /ora_data/bak/
四、新建数据文件和测试表
SYS@testdb>create tablespace test1 datafile '/oracle/ora10g/oradata/test1.dbf' size 20M;
Tablespace created.
SYS@testdb>alter system switch logfile;
System altered.
SYS@testdb>create table test01 tablespace test1 as select * from dba_users;
Table created.
五、模拟环境,丢失全部数据文件和控制文件
SYS@testdb>shutdown abort;
ORACLE instance shut down.
tempapp@ora10g[#/oracle/ora10g/oradata]mkdir bak2
tempapp@ora10g[#/oracle/ora10g/oradata]mv *.ctl ./bak2
tempapp@ora10g[#/oracle/ora10g/oradata]mv *.dbf ./bak2
tempapp@ora10g[#/oracle/ora10g/oradata]cp /ora_data/bak/*.* ./
六、恢复
-->将数据库启动到mount状态
SYS@testdb>startup mount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2168928 bytes
Variable Size 530442144 bytes
Database Buffers 1610612736 bytes
Redo Buffers 4259840 bytes
Database mounted.
-->恢复数据库
SYS@testdb>recover database using backup controlfile until cancel;
ORA-00279: change 4644409 generated at 06/06/2014 14:51:30 needed for thread 1
ORA-00289: suggestion : /ora_data/oraarch/1_43_844012905.dbf
ORA-00280: change 4644409 for thread 1 is in sequence #43
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 9: '/oracle/ora10g/oradata/test1.dbf'
ORA-01112: media recovery not started
-->由于使用的是备份的控制文件进行恢复,该文件中不包含后增加的文件,恢复过程中会缺省的赋予一个文件名,本案例时:UNNAMED00009
SYS@testdb>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/ora10g/oradata/system01.dbf
/oracle/ora10g/oradata/undotbs01.dbf
/oracle/ora10g/oradata/sysaux01.dbf
/oracle/ora10g/oradata/users01.dbf
/oracle/ora10g/oradata/users02.dbf
/oracle/ora10g/oradata/testtbs01.dbf
/oracle/ora10g/oradata/owi01.dbf
/oracle/ora10g/oradata/system02.dbf
/oracle/ora10g/product/10.2.0/db_1/dbs/UNNAMED00009
9 rows selected.
-->改文件名称继续恢复
SYS@testdb>alter database create datafile '/oracle/ora10g/product/10.2.0/db_1/dbs/UNNAMED00009' as '/oracle/ora10g/oradata/test1.dbf';
Database altered.
-->继续恢复
SYS@testdb>recover database using backup controlfile until cancel;
ORA-00279: change 4644592 generated at 06/06/2014 14:56:40 needed for thread 1
ORA-00289: suggestion : /ora_data/oraarch/1_43_844012905.dbf
ORA-00280: change 4644592 for thread 1 is in sequence #43
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 4644621 generated at 06/06/2014 14:57:00 needed for thread 1
ORA-00289: suggestion : /ora_data/oraarch/1_44_844012905.dbf
ORA-00280: change 4644621 for thread 1 is in sequence #44
ORA-00278: log file '/ora_data/oraarch/1_43_844012905.dbf' no longer needed for
this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/ora_data/oraarch/1_44_844012905.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3
-->继续,这里输入当前日志文件
SYS@testdb>recover database using backup controlfile until cancel;
ORA-00279: change 4644621 generated at 06/06/2014 14:57:00 needed for thread 1
ORA-00289: suggestion : /ora_data/oraarch/1_44_844012905.dbf
ORA-00280: change 4644621 for thread 1 is in sequence #44
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/ora10g/oradata/redo01.log
ORA-00310: archived log contains sequence 43; sequence 44 required
ORA-00334: archived log: '/oracle/ora10g/oradata/redo01.log'
-->继续,这里输入下一个在线日志
SYS@testdb>recover database using backup controlfile until cancel;
ORA-00279: change 4644621 generated at 06/06/2014 14:57:00 needed for thread 1
ORA-00289: suggestion : /ora_data/oraarch/1_44_844012905.dbf
ORA-00280: change 4644621 for thread 1 is in sequence #44
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/ora10g/oradata/redo02.log
Log applied.
Media recovery complete.
-->已resetlogs方式打开数据库
SYS@testdb>alter database open resetlogs;
Database altered.
此案例丢失控制文件,从备份控制文件进行恢复,在进行恢复中,会向控制文件中以缺省规则增加文件,我们可以通过CREATE AS方式修改控制文件中记录.继续应用所有归档和日志文件可以完成恢复.
七、恢复环境检查
SYS@testdb>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/ora10g/oradata/system01.dbf
/oracle/ora10g/oradata/undotbs01.dbf
/oracle/ora10g/oradata/sysaux01.dbf
/oracle/ora10g/oradata/users01.dbf
/oracle/ora10g/oradata/users02.dbf
/oracle/ora10g/oradata/testtbs01.dbf
/oracle/ora10g/oradata/owi01.dbf
/oracle/ora10g/oradata/system02.dbf
/oracle/ora10g/oradata/test1.dbf
9 rows selected.
SYS@testdb>select count(*) from test01;
COUNT(*)
----------
10
--END--