oracle用户管理的完全恢复6：控制文件损坏（控制文件前后内容改变）-白红宇

oracle用户管理的完全恢复6：控制文件损坏（控制文件前后内容改变）

阅读量：6250 次

发布时间：2019-06-22

本文共 7885 字，大约阅读时间需要 26 分钟。

场景描述：控制文件损坏（控制文件内容变化）

1.1.备份

1 SQL> alter database backup controlfile to '/u01/admin/sun/udump/c.bin';2 3 Database altered.4 5 SQL>

1.2.查看当前表空间

1 SQL> select ts#,name from v$tablespace order by 1; 2  3        TS# NAME 4 ---------- ------------------------------ 5          0 SYSTEM 6          1 UNDOTBS1 7          2 SYSAUX 8          3 TEMP 9          4 USERS10          6 EXAMPLE11          7 SUN0112          8 SUN0213 14 8 rows selected.15 16 SQL>

2模拟场景

2.1.创建表空间sun03 (控制文件能容将被修改)

1 SQL> create tablespace sun03 datafile '/u01/oradata/sunbak/sun03_1.dbf' size 5M2   2  extent management local uniform3   3  segment space management auto;4 5 Tablespace created.6 7 SQL>

2.2在sun03上建表插入数据

1 SQL> conn user1/user1 2 Connected. 3 SQL> create table t4 (id int,name varchar2(16)) tablespace sun03; 4  5 Table created. 6  7 SQL> insert into t4 values(0,'oracle'); 8  9 1 row created.10 11 SQL> commit;12 13 Commit complete.14 15 SQL>

2.3损坏当前控制文件

1 -bash-3.00$ pwd 2 /u01/oradata/sunbak 3 -bash-3.00$ ls -l 4 total 2195216 5 -rw-r-----   1 oracle   oinstall 7389184 Jan 25 19:14 control01.ctl 6 -rw-r-----   1 oracle   oinstall 7389184 Jan 25 19:14 control02.ctl 7 -rw-r-----   1 oracle   oinstall 7389184 Jan 25 19:14 control03.ctl 8 -rw-r-----   1 oracle   oinstall 104865792 Jan 25 18:33 example01.dbf 9 -rw-r-----   1 oracle   oinstall 52429312 Jan 25 18:17 redo01.log10 -rw-r-----   1 oracle   oinstall 52429312 Jan 25 19:13 redo02.log11 -rw-r-----   1 oracle   oinstall 52429312 Jan 25 18:17 redo03.log12 -rw-r-----   1 oracle   oinstall 10493952 Jan 25 18:33 sun01_1.dbf13 -rw-r-----   1 oracle   oinstall 10493952 Jan 25 18:33 sun02_1.dbf14 -rw-r-----   1 oracle   oinstall 5251072 Jan 25 19:09 sun03_1.dbf15 -rw-r-----   1 oracle   oinstall 251666432 Jan 25 19:13 sysaux01.dbf16 -rw-r-----   1 oracle   oinstall 503324672 Jan 25 19:13 system01.dbf17 -rw-r-----   1 oracle   oinstall 20979712 Jan 23 04:03 temp01.dbf18 -rw-r-----   1 oracle   oinstall 31465472 Jan 25 19:13 undotbs01.dbf19 -rw-r-----   1 oracle   oinstall 5251072 Jan 25 18:33 users01.dbf20 -bash-3.00$ rm -f control*21 -bash-3.00$

3.关闭数据库

1 SQL> shutdown abort2 ORACLE instance shut down.3 SQL>

4恢复

4.1.将备份的控制文件拷贝过来

1 -bash-3.00$ pwd2 /u01/oradata/sunbak3 -bash-3.00$ 4 -bash-3.00$ cp /u01/admin/sun/udump/c.bin control01.ctl5 -bash-3.00$ cp /u01/admin/sun/udump/c.bin control02.ctl6 -bash-3.00$ cp /u01/admin/sun/udump/c.bin control03.ctl7 -bash-3.00$

4.2 开启到mount状态

1 SQL> startup mount 2 ORACLE instance started. 3  4 Total System Global Area  289406976 bytes 5 Fixed Size                  1279820 bytes 6 Variable Size              92276916 bytes 7 Database Buffers          192937984 bytes 8 Redo Buffers                2912256 bytes 9 Database mounted.10 SQL> 11 #查看当前表空间，此时sun03不存在12 SQL> select ts#,name from v$tablespace order by 1;13 14        TS# NAME15 ---------- ------------------------------16          0 SYSTEM17          1 UNDOTBS118          2 SYSAUX19          3 TEMP20          4 USERS21          6 EXAMPLE22          7 SUN0123          8 SUN0224 25 8 rows selected.26 27 SQL>

4.3 恢复

1 SQL> recover database using backup controlfile; 2 ORA-00279: change 687115 generated at 01/25/2013 18:33:51 needed for thread 1 3 ORA-00289: suggestion : /u01/admin/sun/arch/1_26_805319563.dbf 4 ORA-00280: change 687115 for thread 1 is in sequence #26 5 #此时恢复提示需要归档日志文件1_26_805319563.dbf，到归档目录查看不存在此归档日志文件 6 #说明1_26_805319563.dbf的内容在redo日志文件中，目前不确定在哪一个文件中，只能一个个尝试 7 #-bash-3.00$ ls -ltr 8 #total 111836 9 ..............10 #-rw-r-----   1 oracle   oinstall    3584 Jan 23 04:35 1_23_805319563.dbf11 #-rw-r-----   1 oracle   oinstall   87552 Jan 25 11:26 1_24_805319563.dbf12 #-rw-r-----   1 oracle   oinstall 9251840 Jan 25 18:17 1_25_805319563.dbf13 14 Specify log: {
   
     
      =suggested | filename | AUTO | CANCEL}15 /u01/oradata/sunbak/redo01.log16 ORA-00310: archived log contains sequence 25; sequence 26 required17 ORA-00334: archived log: '/u01/oradata/sunbak/redo01.log'18 19 20 SQL> 21 #说明数据不再redo01.log中

4.4再次执行恢复

1 SQL> recover database using backup controlfile; 2 ORA-00279: change 687115 generated at 01/25/2013 18:33:51 needed for thread 1 3 ORA-00289: suggestion : /u01/admin/sun/arch/1_26_805319563.dbf 4 ORA-00280: change 687115 for thread 1 is in sequence #26 5  6 Specify log: {
   
     
      =suggested | filename | AUTO | CANCEL} 7 /u01/oradata/sunbak/redo02.log 8 ORA-00283: recovery session canceled due to errors 9 ORA-01244: unnamed datafile(s) added to control file by media recovery10 ORA-01110: data file 8: '/u01/oradata/sunbak/sun03_1.dbf'11 12 13 ORA-01112: media recovery not started14 15 16 SQL> 17 #此时/u01/oradata/sunbak/sun03_1.dbf文件名 已经加入到了控制文件中18 #-bash-3.00$ strings control01.ctl |grep sun19 #/u01/oradata/sunbak/sun03_1.dbf

4.5查看alter日志

1 ALTER DATABASE RECOVER    LOGFILE '/u01/oradata/sunbak/redo02.log'   2 Fri Jan 25 19:38:28 2013 3 Media Recovery Log /u01/oradata/sunbak/redo02.log 4 File #8 added to control file as 'UNNAMED00008'. Originally created as: 5 '/u01/oradata/sunbak/sun03_1.dbf' 6 Some recovered datafiles maybe left media fuzzy 7 Media recovery may continue but open resetlogs may fail 8 Fri Jan 25 19:38:32 2013 9 Media Recovery failed with error 124410 ORA-283 signalled during: ALTER DATABASE RECOVER    LOGFILE '/u01/oradata/sunbak/redo02.log'  ...11 Fri Jan 25 19:38:32 201312 ALTER DATABASE RECOVER CANCEL 13 ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

4.6 继续恢复（此步骤可忽略）

1 SQL> recover database using backup controlfile; 2 ORA-00283: recovery session canceled due to errors 3 ORA-01111: name for data file 8 is unknown - rename to correct file 4 ORA-01110: data file 8: '/u01/oracle/dbs/UNNAMED00008' 5 ORA-01157: cannot identify/lock data file 8 - see DBWR trace file 6 ORA-01111: name for data file 8 is unknown - rename to correct file 7 ORA-01110: data file 8: '/u01/oracle/dbs/UNNAMED00008' 8  9 SQL> 10 11     报错显示：不能识别8号文件，需要重命名.现在查看两个视图：12 SQL> select * from v$recover_file;13 14      FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME15 ---------- ------- ------- -------------------- ---------- ---------16          8 ONLINE  ONLINE  FILE MISSING                  017 18 SQL>19 #8号文件是需要被恢复的20 SQL> select file#,name from v$datafile; 21 22      FILE# NAME23 ---------- ----------------------------------------24          1 /u01/oradata/sunbak/system01.dbf25          2 /u01/oradata/sunbak/undotbs01.dbf26          3 /u01/oradata/sunbak/sysaux01.dbf27          4 /u01/oradata/sunbak/users01.dbf28          5 /u01/oradata/sunbak/example01.dbf29          6 /u01/oradata/sunbak/sun01_1.dbf30          7 /u01/oradata/sunbak/sun02_1.dbf31          8 /u01/oracle/dbs/UNNAMED0000832 33 8 rows selected.34 35 SQL> 36 #8号文件为/u01/oracle/dbs/UNNAMED00008

4.7重命名8号文件

1 SQL> alter database rename file '/u01/oracle/dbs/UNNAMED00008' to '/u01/oradata/sunbak/sun03_1.dbf';2 3 Database altered.4 5 SQL>

4.8 继续恢复

1 SQL> recover database using backup controlfile ; 2 ORA-00279: change 688614 generated at 01/25/2013 19:09:32 needed for thread 1 3 ORA-00289: suggestion : /u01/admin/sun/arch/1_26_805319563.dbf 4 ORA-00280: change 688614 for thread 1 is in sequence #26 5  6  7 Specify log: {
   
     
      =suggested | filename | AUTO | CANCEL} 8 /u01/oradata/sunbak/redo01.log 9 ORA-00310: archived log contains sequence 25; sequence 26 required10 ORA-00334: archived log: '/u01/oradata/sunbak/redo01.log'11 12 13 SQL> recover database using backup controlfile ;14 ORA-00279: change 688614 generated at 01/25/2013 19:09:32 needed for thread 115 ORA-00289: suggestion : /u01/admin/sun/arch/1_26_805319563.dbf16 ORA-00280: change 688614 for thread 1 is in sequence #2617 18 19 Specify log: {
   
      
       =suggested | filename | AUTO | CANCEL}20 /u01/oradata/sunbak/redo02.log21 Log applied.22 Media recovery complete.23 SQL>

5.打开数据库

1 SQL> alter database open resetlogs;2 3 Database altered.4 5 SQL>

转载地址：http://foysa.baihongyu.com/

你可能感兴趣的文章

linux下mysql的root密码忘记解决方法

查看>>

php for Linux之mysql扩展模块安装与配置

查看>>

【Jenkins】在Redhat版本系统安装Jenkins服务

查看>>

使用官方Aansible部署Kubernetes

查看>>

Redis 集群解决方案 Codis

查看>>

SUSE Manager – 管理、置备和监控您的 SUSE Linux Enterprise Server

电脑系统丢失MAC地址导致无法上网的解决办法

查看>>

martian source packets(ll header)

（20120722）（笔记001）android开发基础

查看>>