Category Archives: Oracle

回收dba中alter system处理方法

Posted on by

今天在pub上看到一个问题，一个朋友想回收dba的alter system权限，直接回收这个系统权限从dba的做法是不推荐使用，因为修改了系统默认的dba角色所具有的系统权限，可能会导致未知的后果。好的做法是创建新的角色，使其有dba中除alter system之外的所有权限。
1、数据库版本

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

2、创建角色并授权

SQL> create role xifenfei;

角色已创建。

SQL> declare
  2  cursor cur is select privilege from role_sys_privs where role='DBA'
  3  AND PRIVILEGE NOT IN ('ALTER SYSTEM','ADMINISTER RESOURCE MANAGER');
  4  BEGIN
  5    FOR C in cur loop
  6    EXECUTE IMMEDIATE  'grant '||c.privilege||' to xifenfei';
  7    END loop;
  8  END;
  9  /

PL/SQL 过程已成功完成。

SQL> SELECT PRIVILEGE FROM ROLE_SYS_PRIVS
  2  WHERE ROLE ='DBA' AND PRIVILEGE NOT IN(
  3  SELECT PRIVILEGE FROM ROLE_SYS_PRIVS
  4  WHERE ROLE ='XIFENFEI'
  5  );

PRIVILEGE
----------------------------------------
ALTER SYSTEM
ADMINISTER RESOURCE MANAGER

说明：授予创建角色出ALTER SYSTEM和ADMINISTER RESOURCE MANAGER系统权限之外的所有权限

3、创建用户并授权角色

SQL> create user chf_xff identified by xifenfei;

用户已创建。

SQL> grant xifenfei to chf_xff;

授权成功。

4、单独授予ADMINISTER RESOURCE MANAGER权限

SQL> exec dbms_resource_manager_privs.grant_system_privilege(
   2 grantee_name => 'CHF_XFF',admin_option => false);

PL/SQL 过程已成功完成。

SQL> CONN chf_xff/xifenfei
已连接。

SQL> SELECT * FROM SESSION_PRIVS WHERE
  2  PRIVILEGE ='ADMINISTER RESOURCE MANAGER';

PRIVILEGE
----------------------------------------
ADMINISTER RESOURCE MANAGER

说明:
1)通过授权xifenfei角色和ADMINISTER RESOURCE MANAGER权限，完成回收dba中的alter system权限要求。
2)如果只有个别用户有这样的需求，那么可以直接生成批量授权语句实现，而不用建立类此xifenfei这样的角色。

5、为何单独授予ADMINISTER RESOURCE MANAGER权限

SQL> CONN / AS SYSDBA
已连接。
SQL> GRANT ADMINISTER RESOURCE MANAGER TO CHF_XFF;
GRANT ADMINISTER RESOURCE MANAGER TO CHF_XFF
      *
第 1 行出现错误:
ORA-00990: 权限缺失或无效

说明：ADMINISTER RESOURCE MANAGER这个系统权限在10g及其以后版本中，就不能直接使用GRANT/REVOKE直接授权/回收权限,而必面使用dbms_resource_manager_privs.grant_system_privilege和revoke_system_privilege过程进行处理。

创建视图提示ORA-01031

Posted on by

1、重现问题

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select table_name from user_tables where table_name in('X_T','DEPT');

TABLE_NAME
------------------------------
X_T

SQL> create view  v_xff1
  2  as 
  3  select * from X_T;

View created.


SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;
SELECT * FROM SCOTT.DEPT
                    *
ERROR at line 2:
ORA-01031: insufficient privileges


SQL> SELECT COUNT(*) FROM SCOTT.DEPT;

  COUNT(*)
----------
         4

通过上面的试验证明：
1）在同一个schema下，有查询权限，就可以创建视图
2）在不同schema下，即使有了查询权限，创建视图，还是会提示ORA-01031

2、查看相关权限情况

SQL> select * from SESSION_PRIVS where 
  2  PRIVILEGE in('SELECT ANY TABLE','CREATE ANY VIEW','CREATE VIEW');

PRIVILEGE
----------------------------------------
SELECT ANY TABLE
CREATE VIEW
CREATE ANY VIEW

SQL> SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE NOT IN(
  2  SELECT PRIVILEGE
  3    FROM ROLE_SYS_PRIVS
  4   WHERE ROLE IN(SELECT * FROM SESSION_ROLES));

no rows selected

通过上面权限查询得出：用户所具有的select 其他用户表的权限是用过role授权

3、单独授于select权限

SQL> conn / as sysdba
Connected.
SQL> grant select on SCOTT.DEPT to xff;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;

View created.

SQL> select view_name from user_views;

VIEW_NAME
------------------------------
V_XFF
V_XFF1

4、产生问题原因
In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects
ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role

删除Oracle数据文件/临时文件

Posted on by

有些时候，想删除一个数据文件(临时文件),在10g之前的版本，要删除一个数据文件，必须删除该数据文件所属的表空间(特殊处理方法除外)。不太懂数据库的朋友直接os级别删除数据文件，导致数据库不能正常启动;稍微等点数据库的朋友，会先offline数据文件，然后os级别删除，但是这条数据文件的记录还保留在数据字典中，不爽(特殊处理办法见:清除离线数据文件记录)。在10g及其以后版本中，oracle提供了alter tablespace talbespace_name drop datafile/tempfile path/file_id进行删除某个数据文件

一、删除数据文件

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 8 11:22:38 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col tablespace_name for a20
SQL> col file_name for a50
SQL> set line 120
SQL> select file_id,file_name,tablespace_name 
   2 from dba_data_files order by tablespace_name;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         9 /opt/oracle/oradata/test/odu01.dbf                 ODU
        10 /opt/oracle/oradata/test/odu03.dbf                 ODU
         8 /opt/oracle/oradata/test/odu02.dbf                 ODU
         3 /opt/oracle/oradata/test/sysaux01.dbf              SYSAUX
         1 /opt/oracle/oradata/test/system01.dbf              SYSTEM
         2 /opt/oracle/oradata/test/undotbs01.dbf             UNDOTBS1
         5 /opt/oracle/oradata/test/user32g.dbf               USERS
         7 /opt/oracle/oradata/test/user02.dbf                USERS
         4 /opt/oracle/oradata/test/users01.dbf               USERS
         6 /opt/oracle/oradata/test/xifenfei01.dbf            XFF
        11 /opt/oracle/oradata/test/xifenfei03.dbf            XFF

11 rows selected.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 12:36 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:45 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:45 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 00:05 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 00:05 /opt/oracle/oradata/test/xifenfei01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/xifenfei03.dbf

SQL> alter tablespace xff drop datafile 11;
alter tablespace xff drop datafile 11
*
ERROR at line 1:
ORA-03262: the file is non-empty

SQL> col segment_name for a20
SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
  2  where file_id=11;

OWNER                          SEGMENT_NAME            FILE_ID     BLOCKS
------------------------------ -------------------- ---------- ----------
CHF                            XFF_TEST                     11          8
CHF                            XFF_TEST                     11        128
CHF                            XFF_TEST                     11        128
CHF                            T_XFF                        11        128
CHF                            T_XFF                        11        128

SQL> alter table chf.xff_test move tablespace users;

Table altered.

SQL> alter table chf.t_xff move tablespace users;

Table altered.

SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
  2  where file_id=11;

no rows selected

SQL> alter tablespace xff drop datafile 11;

Tablespace altered.

SQL> select file_id,file_name,tablespace_name 
  2  from dba_data_files order by tablespace_name;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         9 /opt/oracle/oradata/test/odu01.dbf                 ODU
        10 /opt/oracle/oradata/test/odu03.dbf                 ODU
         8 /opt/oracle/oradata/test/odu02.dbf                 ODU
         3 /opt/oracle/oradata/test/sysaux01.dbf              SYSAUX
         1 /opt/oracle/oradata/test/system01.dbf              SYSTEM
         2 /opt/oracle/oradata/test/undotbs01.dbf             UNDOTBS1
         4 /opt/oracle/oradata/test/users01.dbf               USERS
         7 /opt/oracle/oradata/test/user02.dbf                USERS
         5 /opt/oracle/oradata/test/user32g.dbf               USERS
         6 /opt/oracle/oradata/test/xifenfei01.dbf            XFF

10 rows selected.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 12:36 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:45 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:45 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:52 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:52 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:52 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:52 /opt/oracle/oradata/test/xifenfei01.dbf

二、删除临时表空间文件

SQL> alter  tablespace temp add tempfile '/opt/oracle/oradata/test/xff_temp02.dbf' size 10m;

Tablespace altered.

SQL> select file_id,file_name,tablespace_name from dba_temp_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         2 /opt/oracle/oradata/test/xff_temp02.dbf            TEMP
         1 /opt/oracle/oradata/test/temp01.dbf                TEMP

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 13:00 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:58 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:57 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:57 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 13:00 /opt/oracle/oradata/test/xff_temp02.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:57 /opt/oracle/oradata/test/xifenfei01.dbf

SQL> alter tablespace temp drop tempfile 2;

Tablespace altered.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 13:00 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:58 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:57 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:57 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:57 /opt/oracle/oradata/test/xifenfei01.dbf

三、注意事项
1、Oracle Database 10g Release 2及其以上版本
2、删除数据文件从数据库和系统
3、数据文件必须为空
4、数据文件不是对应表空间的最后一个
5、不能删除只读表空间中数据文件
6、不能删除system中数据文件
7、不能删除本地管理的offline表空间数据文件

TNS-12525 TNS-12535 TNS-12606

Posted on by

一、发现TNS-12525错误
检查公司服务器listener日志中周期性发现如下错误提示：

08-DEC-2011 02:25:34 * (CONNECT_DATA=(SID=ora9i)) * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=4877)) * establish * ora9i * 0
08-DEC-2011 02:25:35 * (CONNECT_DATA=(SID=ora9i)) * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=4879)) * establish * ora9i * 0
08-DEC-2011 02:25:43 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=2486)) * establish * <unknown sid> * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred
08-DEC-2011 02:26:02 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=4876)) * establish * <unknown sid> * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred

继续检查日志发现，只有211.155.236.234是外网ip，其他都是通过内网ip访问，都未报类此错误。初步怀疑是公司和运营商网络之间的防火墙导致

二、数据库版本

SQL> 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 Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

三、MOS说明

Symptoms
At regular intervals (every three hours), you see new connections rejected with TNS-12525 "listener has not received client's request in time allowed".
The listener log shows the following error stack :
10-APR-2008 17:39:15 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=10.221.50.31)(PORT=4184)) * establish * <unknown sid> * 12525 
  TNS-12525: TNS:listener has not received client's request in time allowed 
    TNS-12535: TNS:operation timed out 
      TNS-12606: TNS: Application timeout occurred

At the same time the firewall log reports :
"TCP packet out of state: First packet isn't SYN; tcp_flags: PUSH-ACK"

Wireshark (Ethereal) traces show the TCP 3-way handshake never completes for those connections.

Changes
Value of idle timer was recently changed at firewall level.

Cause
Firewall closes ports not allowing the 3-way handshake to complete at TCP level.

Solution
Increase the firewall's idle timeout interval.

New Connections Intermittently Rejected with TNS-12525

异常断电导致current redo损坏处理

Posted on by

一、数据库启动异常
网友因异常断电导致数据库不能正常启动，处理过程记录
1、sqlplus中提示

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 12月 7 12:57:02 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup
ORACLE 例程已经启动。

Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
数据库装载完毕。
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG'

2、alert日志文件

ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc  (incident=214262):
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214262\hzyl_ora_2960_i214262.trc
Aborting crash recovery due to error 368
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc:
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
ORA-368 signalled during: ALTER DATABASE OPEN...
Wed Dec 07 12:57:17 2011
Trace dumping is performing id=[cdmp_20111207125717]
Wed Dec 07 12:57:17 2011
Sweep Incident[214262]: completed
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_m000_2400.trc  (incident=214326):
ORA-00353: log corruption near block 12014 change 9743799889 time 12/05/2011 09:21:11
ORA-00312: online log 3 thread 1: 'R:\ORADATA\HZYL\REDO03.LOG'
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214326\hzyl_m000_2400_i214326.trc
Trace dumping is performing id=[cdmp_20111207125718]

二、查询是损坏日志组

SQL> col member for a35
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b 
   2 where a.group#=b.group#;

    GROUP# STATUS           MEMBER
---------- ---------------- -----------------------------------
         3 CURRENT          R:\ORADATA\HZYL\REDO03.LOG
         2 INACTIVE         R:\ORADATA\HZYL\REDO02.LOG
         1 INACTIVE         R:\ORADATA\HZYL\REDO01.LOG

确定是因为断电导致当前redo 损坏，需要使用隐含参数+不完全恢复处理(经验)

三、不完全恢复

SQL> recover database until cancel;
ORA-00279: 更改 9743796718 (在 12/05/2011 09:07:40 生成) 对于线程 1 是必需的
ORA-00289: 建议: S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC
ORA-00280: 更改 9743796718 (用于线程 1) 在序列 #15114 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: 无法打开归档日志
'S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。


ORA-00308: 无法打开归档日志
'S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。


ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'R:\ORADATA\HZYL\SYSTEM01.DBF'

四、启用隐含参数，尝试打开数据库

SQL> create pfile='d:/1.txt' from spfile;

文件已创建。

#############添加隐含参数##################
*._allow_resetlogs_corruption=TRUE
*._allow_error_simulation=TRUE
##########################################

SQL> shutdown immediate
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile ='d:\1.txt';
ORACLE 例程已经启动。

Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
数据库装载完毕。
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项


SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00603: ORACLE 服务器会话因致命错误而终止
进程 ID: 3388
会话 ID: 335 序列号: 5

五、查看alert日志

Wed Dec 07 13:02:49 2011
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216664):
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216664\hzyl_ora_3388_i216664.trc
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc:
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 3388): terminating the instance due to error 600
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mmnl_3884.trc  (incident=216640):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216640\hzyl_mmnl_3884_i216640.trc
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_diag_4020.trc  (incident=216552):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216552\hzyl_diag_4020_i216552.trc
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dia0_2896.trc  (incident=216584):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216584\hzyl_dia0_2896_i216584.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_pmon_2964.trc  (incident=216536):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216536\hzyl_pmon_2964_i216536.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mmon_3764.trc  (incident=216632):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216632\hzyl_mmon_3764_i216632.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dbrm_2060.trc  (incident=216560):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_psp0_1476.trc  (incident=216568):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:52 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mman_3040.trc  (incident=216576):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:52 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dbw0_2116.trc  (incident=216592):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_lgwr_2624.trc  (incident=216600):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_smon_592.trc  (incident=216616):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ckpt_3052.trc  (incident=216608):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Instance terminated by USER, pid = 3388
ORA-1092 signalled during: alter database open resetlogs...
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216665):
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216666):
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc:
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Wed Dec 07 13:02:53 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=218920):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_218920\hzyl_ora_3388_i218920.trc
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_218920\hzyl_ora_3388_i218920.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []

这里看到熟悉的ora-600 [2662]，处理方法增加scn，参考ORA-00600 [2662]

六、增加SCN，打开数据库

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 12月 7 13:04:51 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup pfile ='d:\1.txt' mount;
ORACLE 例程已经启动。

Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
数据库装载完毕。
SQL> alter session set events '10015 trace name adjust_scn level 1';

会话已更改。


SQL> alter database open;

数据库已更改。

SQL> select status from v$instance;

STATUS
------------
OPEN

七、启动过程中，alert日志现ORA-00600[2256]

Completed: ALTER DATABASE   MOUNT
Wed Dec 07 13:06:07 2011
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 1 redo blocks read, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 1, block 2, scn 9743796722
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: R:\ORADATA\HZYL\REDO01.LOG
Completed redo application
Completed crash recovery at
 Thread 1: logseq 1, block 3, scn 9743816724
 0 data blocks read, 0 data blocks written, 1 redo blocks read
Wed Dec 07 13:06:08 2011
Thread 1 advanced to log sequence 2
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: R:\ORADATA\HZYL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 07 13:06:08 2011
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3096.trc  (incident=219072):
ORA-00600: 内部错误代码, 参数: [2256], [0], [1073741824], [2], [1153882138], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_219072\hzyl_ora_3096_i219072.trc
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Wed Dec 07 13:06:09 2011
Trace dumping is performing id=[cdmp_20111207130609]
Opening with internal Resource Manager plan
Wed Dec 07 13:06:10 2011
Sweep Incident[219072]: completed
Starting background process FBDA
Starting background process SMCO
Wed Dec 07 13:06:10 2011
FBDA started with pid=26, OS id=2436 
Wed Dec 07 13:06:10 2011
SMCO started with pid=27, OS id=2876 
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Dec 07 13:06:11 2011
QMNC started with pid=28, OS id=2812 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Wed Dec 07 13:06:15 2011
db_recovery_file_dest_size of 10000 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Dec 07 13:06:25 2011
Completed: alter database open

打开过程中，出现ORA-00600[2256]，但是数据库还是正常打开，具体原因待研究