联机日志、控制文件丢失

1、连接到数据库，然后非法关闭数据库shutdown abort模拟故障
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Antiper>sqlplus " / as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 10月 21 09:29:53 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database backup controlfile to trace as 'c:\control.sql';
数据库已更改。
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
2、在操作系统层删除所有的控制文件以及日志文件。
3、重新连接数据库，发现数据库无法打开
C:\Documents and Settings\Antiper>sqlplus " / as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 10月 21 09:33:26 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area  444596224 bytes
Fixed Size                  1249464 bytes
Variable Size             113250120 bytes
Database Buffers          322961408 bytes
Redo Buffers                7135232 bytes
ORA-00205: ?????????, ??????, ???????

4、利用备份的脚本去创建控制文件
SQL> CREATE CONTROLFILE REUSE DATABASE "CALM" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'E:\ORADATA_10G\CALM\REDO01.LOG'  SIZE 50M,
  9    GROUP 2 'E:\ORADATA_10G\CALM\REDO02.LOG'  SIZE 50M,
 10    GROUP 3 'E:\ORADATA_10G\CALM\REDO03.LOG'  SIZE 50M
 11  DATAFILE
 12    'E:\ORADATA_10G\CALM\SYSTEM01.DBF',
 13    'E:\ORADATA_10G\CALM\UNDOTBS01.DBF',
 14    'E:\ORADATA_10G\CALM\SYSAUX01.DBF',
 15    'E:\ORADATA_10G\CALM\USERS01.DBF'
 16  CHARACTER SET ZHS16GBK;
控制文件已创建。
SQL> select status from v$instance;
STATUS
------------
MOUNTED

5、打开数据库出错，提示系统表空间需要恢复
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01194: ?? 1 ?????????????
ORA-01110: ???? 1: 'E:\ORADATA_10G\CALM\SYSTEM01.DBF'

6、出现上述原因是因为，在数据库打开过程中需要检验数据文件、控制文件以及相关日志文件的一致性。因为
目前日志文件丢失，因此这个检验是无法通过的。需要设置隐含参数，让数据库跳过这些检验，从而可以
打开数据库。
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
系统已更改。
SQL> show parameter _allow;
SQL> startup mount force
ORACLE 例程已经启动。
Total System Global Area  444596224 bytes
Fixed Size                  1249464 bytes
Variable Size             113250120 bytes
Database Buffers          322961408 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
SQL> show parameter _allow;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption          boolean     TRUE

7、resetlogs打开数据库
SQL> alter database open resetlogs;
数据库已更改。
SQL> select instance_name,status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
calm             OPEN
8、注意如果此时无法打开数据库，则需要ADJUST SCN。
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
bdump 报 ORA-00600: internal error code, arguments: [4000], [2], [], [], [], [], [], []
有时候dump还会报如下错误：ORA-00600 [2662]错误
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  134217728 bytes                                      
Fixed Size                   787808 bytes                                      
Variable Size              57408160 bytes                                      
Database Buffers           75497472 bytes                                      
Redo Buffers                 524288 bytes                                      
Database mounted.
SQL> alter session set events '10015 trace name adjust_scn level 1';
Session altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  134217728 bytes                                      
Fixed Size                   787808 bytes                                      
Variable Size              57408160 bytes                                      
Database Buffers           75497472 bytes                                      
Redo Buffers                 524288 bytes                                      
Database mounted.

SQL> recover database;
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> alter database open;
Database altered.
多尝试几次可能会打开数据库的。
SQL> show parameter allow;
NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption          boolean     TRUE 

9、重建控制文件后，需要添加临时文件
SQL> select * from dba_temp_files;
未选定行
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORADATA_10G\CALM\TEMP01.DBF'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
    表空间已更改。