数据库down机恢复案例一

数据库启动不了，没有备份，没有归档

SQL> startup  只能启动到mount状态，查看alert文件

ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4193], [8177], [8196], [], [], [], [], []

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/db02/app/oracle/oradata/ORCL/system01.dbf'

resetlogs的字面意思是重置重做日志。在创建控制文件的时候如果不需要在线重做日志的话可以用resetlogs选项，第二种情况是在做不完全恢复后，数据文件和重做日志的内容不同步了，这个时候打开数据库一定要用resetlogs选项。resetlogs的时候数据库其实做了很多事，最主要的还是清空重做日志的内容，选定一个重做日志作为当前日志并将日志序列号重置为1，把resetlogs count和resetlogs scn写入控制文件、数据文件头部和重做日志的头部，当然还会做很多其它的事情。

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

准备使用隐含参数_allow_resetlogs_corruption强制启动DB：

SQL> startup mount

Database mounted.

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile

提示：Oracle的隐含参数只应该在测试环境或者在Oracle Support的支持下使用。

SQL>create pfile from spfile

SQL> startup pfile=$ORACLE_HOME/dbs/initbsmp.ora
ORACLE instance started. Total System Global Area  823727904 bytes
Fixed Size                   452384 bytes
Variable Size             285212672 bytes
Database Buffers          536870912 bytes
Redo Buffers                1191936 bytes
Database mounted.
ORA-03113: end-of-file on communication channel

SQL> conn /as sysdba
Connected to an idle instance.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01034: ORACLE not available

SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

SQL> startup
ORACLE instance started.

Total System Global Area 826248288 bytes
Fixed Size                   732256 bytes
Variable Size             503316480 bytes
Database Buffers          318767104 bytes
Redo Buffers                3432448 bytes
Database mounted.
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [53], [43], [], [], [], [],[]

以上强制启动后，产生Ora-600错误了，在预料之中。。。。
SQL> startup force;
ORACLE instance started.

Total System Global Area 826248288 bytes
Fixed Size                   732256 bytes
Variable Size             503316480 bytes
Database Buffers          318767104 bytes
Redo Buffers                3432448 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> !oerr ora 1092
01092, 00000, "ORACLE instance terminated. Disconnection forced"
// *Cause: The instance this process was connected to was terminated
//          abnormally, probably via a shutdown abort. This process
//          was forced to disconnect from the instance.
// *Action: Examine the alert log for more details. When the instance has been
//          restarted, retry action.

由于和undo表空间不同步，那好重建undo tablespace：

启动到mount状态，

SQL> show parameter undo
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

SQL> alter system set undo_management=manual scope=spfile;
SQL> alter database open;

先改成不使用undo，可以启动DB，但没法新建对象：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size                   732256 bytes
Variable Size             503316480 bytes
Database Buffers          318767104 bytes
Redo Buffers                3432448 bytes
Database mounted.
Database opened.

恢复，之后还有一些善后工作别忘了喔，否则准备电话被打爆吧

之前的设置的隐含参数记得删除

SQL> select segment_name,owner,tablespace_name,status
  2  from dba_rollback_segs;

SEGMENT_NAME    OWNER  TABLESPACE_NAME                STATUS
--------------- ------ ------------------------------ ----------------
SYSTEM          SYS    SYSTEM                         ONLINE
_SYSSMU1$       PUBLIC UNDOTBS3                       NEEDS RECOVERY
_SYSSMU2$       PUBLIC UNDOTBS3                       NEEDS RECOVERY
_SYSSMU3$       PUBLIC UNDOTBS3                       NEEDS RECOVERY
_SYSSMU11$      PUBLIC UNDOTBS3                       NEEDS RECOVERY
_SYSSMU12$      PUBLIC UNDOTBS3                       NEEDS RECOVERY
_SYSSMU13$      PUBLIC UNDOTBS3                       NEEDS RECOVERY
_SYSSMU14$      PUBLIC UNDOTBS3                       NEEDS RECOVERY
_SYSSMU15$      PUBLIC UNDOTBS3                       NEEDS RECOVERY
_SYSSMU16$      PUBLIC UNDOTBS3                       NEEDS RECOVERY

SQL> select file_name from dba_data_files
  2  where tablespace_name='UNDOTBS3';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/undo/undotbs3_7.dbf
/oradata/undo/undotbs3_8.dbf
/oradata/undo/undotbs3_1.dbf
/oradata/undo/undotbs3_2.dbf
/oradata/undo/undotbs3_3.dbf
/oradata/undo/undotbs3_4.dbf
/oradata/undo/undotbs3_5.dbf
/oradata/undo/undotbs3_6.dbf

8 rows selected.
SQL> select file#,name from v$datafile where status='OFFLINE';

     FILE#          NAME
----------------------------------------------------------------------
        94     /oradata/undo/undotbs3_8.dbf

       108     /oradata/undo/undotbs3_1.dbf

SQL>  select name,status,file# from v$datafile
     where name like '%undotbs3%'

NAME                                     STATUS       FILE#
---------------------------------------- ------- ----------
/home/oracle/undo/undotbs3_7.dbf         ONLINE           2
/oradata/undo/undotbs3_8.dbf             OFFLINE         94
/oradata/undo/undotbs3_1.dbf             OFFLINE        108
/oradata/undo/undotbs3_2.dbf             ONLINE         109
/oradata/undo/undotbs3_3.dbf             ONLINE         110
/oradata/undo/undotbs3_4.dbf             ONLINE         111
/oradata/undo/undotbs3_5.dbf             ONLINE         112
/oradata/undo/undotbs3_6.dbf             ONLINE         113

11 rows selected.

在参数文件中添加

*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$)

SQL> get 1.sql
  1  select 'alter database datafile ' || file# || ' offline drop; '
  2  from v$datafile
  3* where name like '%undotbs3%'
SQL> /

'ALTERDATABASEDATAFILE'||FILE#||'OFFLINEDROP;'
------------------------------------------------------
alter database datafile 2 offline drop;
alter database datafile 94 offline drop;
alter database datafile 108 offline drop;
alter database datafile 109 offline drop;
alter database datafile 110 offline drop;
alter database datafile 111 offline drop;
alter database datafile 112 offline drop;
alter database datafile 113 offline drop;
SQL> /
SQL>
Database altered.

依次删除status为needs RECOVERY的回滚段

SQL> DROP ROLLBACK SEGMENT "_SYSSMU1$";

SQL> DROP TABLESPACE UNDOTBS3 INCLUDING CONTENTS AND DATAFILES;

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14075938/viewspace-483403/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14075938/viewspace-483403/