oracle undo数据文件坏,oracle的控制文件和数据文件损坏

刺骨的言语ヽ痛彻心扉 2023-01-17 15:57 155阅读 0赞

控制文件损坏

一、使用trace文件恢复

SQL> alter database backup controlfile to trace; ——将控制文件备份到trace文件

Database altered.

SQL> @gettrace  --得到trace文件的路径

TRACE\_FILE\_NAME

\--------------------------------------------------------------------------------

/Oracle/app/admin/ora10g/udump/ora10g\_ora\_31270.trc

SQL> !

\[oracle@www.linuxidc.com ~\]$ more /oracle/app/admin/ora10g/udump/ora10g\_ora\_31270.trc

/oracle/app/admin/ora10g/udump/ora10g\_ora\_31270.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

ORACLE\_HOME = /oracle/app/oracle/product/10.2.0/db\_1

System name:    Linux

Node name:      www.linuxidc.com

Release:        2.6.18-164.el5

\--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/oradata/ora10g/redo01.log'  SIZE 50M,

GROUP 2 '/oradata/ora10g/redo02.log'  SIZE 50M,

GROUP 3 '/oradata/ora10g/redo03.log'  SIZE 50M

\-- STANDBY LOGFILE

DATAFILE

'/oradata/ora10g/system01.dbf',

'/oradata/ora10g/undotbs01.dbf',

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora10g/temp01.dbf'

SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

\-- End of tempfile additions.

\--

\[oracle@www.linuxidc.com udump\]$  cd

\[oracle@www.linuxidc.com ~\]$ vim recontro.sql ——拷贝trace文件中有用的语句重建控制文件。如果在之前没有备份的trace文件，我们可以从init\{SID\}.ora文件中得到数据文件、日志文件、数据库表空间等信息，把init\{SID\}.ora文件中的内容按以下格式创建重构控制文件脚本

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/oradata/ora10g/redo01.log'  SIZE 50M,

GROUP 2 '/oradata/ora10g/redo02.log'  SIZE 50M,

GROUP 3 '/oradata/ora10g/redo03.log'  SIZE 50M

DATAFILE

'/oradata/ora10g/system01.dbf',

'/oradata/ora10g/undotbs01.dbf',

'/oradata/ora10g/sysaux01.dbf',

'/oradata/ora10g/users01.dbf'

CHARACTER SET ZHS16GBK

;

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS\_BACKUP\_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/oradata/ora10g/redo01.log'  SIZE 50M,

GROUP 2 '/oradata/ora10g/redo02.log'  SIZE 50M,

GROUP 3 '/oradata/ora10g/redo03.log'  SIZE 50M

DATAFILE

'/oradata/ora10g/system01.dbf',

'/oradata/ora10g/undotbs01.dbf',

'/oradata/ora10g/sysaux01.dbf',

'/oradata/ora10g/users01.dbf'

CHARACTER SET ZHS16GBK

;

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS\_BACKUP\_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');

RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS;

SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

"recontro.sql" 49L, 1641C 已写入

\[oracle@www.linuxidc.com ~\]$ sqlplus / as sysdba

SQL\*Plus: Release 10.2.0.1.0 - Production on Tue Jul 19 03:30:34 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> set echo on       ——打开显示输出

SQL> @recontro        ——使用刚建立的重构控制文件的脚本重建控制文件

SQL> STARTUP NOMOUNT

ORACLE instance started.

Total System Global Area  209715200 bytes

Fixed Size                  1218556 bytes

Variable Size              75499524 bytes

Database Buffers          130023424 bytes

Redo Buffers                2973696 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS     ARCHIVELOG

2      MAXLOGFILES 16

3      MAXLOGMEMBERS 3

4      MAXDATAFILES 100

5      MAXINSTANCES 8

6      MAXLOGHISTORY 292

7  LOGFILE

8    GROUP 1 '/oradata/ora10g/redo01.log'     SIZE 50M,

9    GROUP 2 '/oradata/ora10g/redo02.log'     SIZE 50M,

10    GROUP 3 '/oradata/ora10g/redo03.log'     SIZE 50M

11  DATAFILE

12    '/oradata/ora10g/system01.dbf',

13    '/oradata/ora10g/undotbs01.dbf',

14    '/oradata/ora10g/sysaux01.dbf',

15    '/oradata/ora10g/users01.dbf'

16  CHARACTER SET ZHS16GBK

17  ;

Control file created.

SQL> VARIABLE RECNO NUMBER;

SQL> EXECUTE :RECNO := SYS.DBMS\_BACKUP\_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');

PL/SQL procedure successfully completed.

SQL> RECOVER DATABASE

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora10g/temp01.dbf'

2       SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

SQL> STARTUP NOMOUNT

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS  ARCHIVELOG

2      MAXLOGFILES 16

3      MAXLOGMEMBERS 3

4      MAXDATAFILES 100

5      MAXINSTANCES 8

6      MAXLOGHISTORY 292

7  LOGFILE

8    GROUP 1 '/oradata/ora10g/redo01.log'     SIZE 50M,

9    GROUP 2 '/oradata/ora10g/redo02.log'     SIZE 50M,

10    GROUP 3 '/oradata/ora10g/redo03.log'     SIZE 50M

11  DATAFILE

12    '/oradata/ora10g/system01.dbf',

13    '/oradata/ora10g/undotbs01.dbf',

14    '/oradata/ora10g/sysaux01.dbf',

15    '/oradata/ora10g/users01.dbf'

16  CHARACTER SET ZHS16GBK

17  ;

CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS  ARCHIVELOG

SQL> VARIABLE RECNO NUMBER;

SQL> EXECUTE :RECNO := SYS.DBMS\_BACKUP\_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');

PL/SQL procedure successfully completed.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE

SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora10g/temp01.dbf'

2       SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora10g/temp01.dbf'

Tablespace altered.

SQL> select open\_mode from v$database;

OPEN\_MODE

数据文件损坏，介质恢复

先物理冷备份数据库文件，

v$recover\_file与v$recovery\_log

这是两个动态性能视图，可以在mount下查看，通过这两个视图，你可以了解详细的需要恢复的数据文件与需要使用到的归档日志。

给你个例子：

SQL>   startup

ORACLE   instance   started.

Total   System   Global   Area     102020364   bytes

Fixed   Size                                         70924   bytes

Variable   Size                             85487616   bytes

Database   Buffers                       16384000   bytes

Redo   Buffers                                     77824   bytes

Database   mounted.

ORA-01157:   cannot   identify/lock   data   file   3   -   see   DBWR   trace   file

ORA-01110:   data   file   3:   'D:\\ORACLE\\ORADATA\\TEST\\USERS01.DBF '

还可以查看报警文件(见上一个恢复案例)或动态视图v$recover\_file

如SQL>   select   \*   from   v$recover\_file;

FILE\#   ONLINE     ERROR                                   CHANGE\#   TIME

\----------   -------   ------------------   ----------   -----------

3   ONLINE                                                 1013500   2003-05-07

脱机数据文件

SQL>   alter   database   datafile   3   offline   drop;

Database   altered.

打开数据库，拷贝备份回来(restore)，恢复(recover)该数据文件，并联机

SQL>   alter   database   open;

Database   altered.

拷贝备份从备份处

copy   d:\\databak\\   users01.dbf   d:\\oracle\\oradata\\test;

恢复该数据文件

SQL>   recover   datafile   3;

ORA-00279:   change   1053698   generated   at   05/07/2003   17:51:26   needed   for   thread   1

ORA-00289:   suggestion   :   D:\\ORACLE\\ORADATA\\TEST\\ARCHIVE\\TESTT001S00304.ARC

ORA-00280:   change   1053698   for   thread   1   is   in   sequence   \#304

Specify   log:   \{ =suggested   |   filename   |   AUTO   |   CANCEL\}

AUTO

ORA-00279:   change   1053701   generated   at   05/07/2003   17:51:39   needed   for   thread   1

ORA-00289:   suggestion   :   D:\\ORACLE\\ORADATA\\TEST\\ARCHIVE\\TESTT001S00305.ARC

ORA-00280:   change   1053701   for   thread   1   is   in   sequence   \#305

ORA-00278:   log   file   'D:\\ORACLE\\ORADATA\\TEST\\ARCHIVE\\TESTT001S00304.ARC '   no

longer   needed   for   this   recovery

Log   applied.

Media   recovery   complete.

恢复成功，联机该数据文件

SQL>   alter   database   datafile   3   online;

Database   altered.

检查数据库的数据(完全恢复)

SQL>   select   \*   from   test;

\---------------------------------------

oracle undo数据文件坏,oracle的控制文件和数据文件损坏

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle 迁移数据文件

相关 oracle回收数据文件大小,Oracle 数据文件回收

相关 oracle undo数据文件坏,oracle的控制文件和数据文件损坏

相关 oracle 数据文件管理

相关 Oracle 控制文件损坏解决方案

相关 Oracle 控制文件

相关 [转]oracle查看数据文件, 控制文件, 及日志文件命令

相关 [Oracle] oracle修改数据文件名称

相关 Oracle数据文件和临时文件的管理

相关 Oracle的数据泵文件