丢失控制文件和数据文件，但是有冷备份和全部归档文件的恢复

最新推荐文章于 2020-09-29 17:30:50 发布

gltyi99

最新推荐文章于 2020-09-29 17:30:50 发布

阅读量864

点赞数 1

分类专栏： oracle 文章标签：数据库 database 备份 oracle instance

本文链接：https://blog.csdn.net/ora01555/article/details/28886095

版权

oracle 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

一、模拟情景
数据库有全库备份。丢失全部数据文件和控制文件，拥有全部归档日志和在线日志文件，后增加的文件无备份。以下是一种恢复情况。

二、测试环境

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--