今天去省公安厅部署系统，那边没有网络，不用使用自己的电脑，突然发现原来网络是那么的美好，原来自己电脑上的资料是那么的珍贵；也发现一个问题，做技术的要坦实，是实实在在的懂，而不是说我有个了解，然后google下就可以了。今天就遇到有Linux服务器root密码忘记了，因为sa没有过去，开发不懂，只能我去重设，因为以前这些活都是sa做的，自己有所了解但是记忆不深，今天在自己的大脑中拼命的搜索，不断的尝试，终于修改成功了。晚上回家在vm中做了linux 4/5/6三个版本的修改密码方法截，给自己留个记录，也供大家参考，免得临时手忙脚乱
Linux重设root密码
模拟跨resetlogs恢复
一、模拟跨越resetlog恢复环境
[oracle@node1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:09:29 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA11G (DBID=4162194039) RMAN> list incarnation; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ORA11G 4162194039 PARENT 1 2011-09-17 09:46:04 2 2 ORA11G 4162194039 CURRENT 995548 2011-10-31 16:05:14 RMAN> backup database format '/tmp/ora11g_%U.rman'; Starting backup at 2011-12-12 13:10:49 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=161 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/opt/oracle/oradata/ora11g/sysaux01.dbf input datafile file number=00001 name=/opt/oracle/oradata/ora11g/system01.dbf input datafile file number=00005 name=/opt/oracle/oradata/ora11g/example01.dbf input datafile file number=00004 name=/opt/oracle/oradata/ora11g/users01.dbf input datafile file number=00003 name=/opt/oracle/oradata/ora11g/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:10:50 channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:13:17 piece handle=/tmp/ora11g_01mu1aqq_1_1.rman tag=TAG20111212T131049 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:27 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:13:20 channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:13:22 piece handle=/tmp/ora11g_02mu1avd_1_1.rman tag=TAG20111212T131049 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 2011-12-12 13:13:22 RMAN> shutdown immediate using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down RMAN> startup mount; connected to target database (not started) Oracle instance started database mounted Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 1493174472 bytes Database Buffers 637534208 bytes Redo Buffers 4947968 bytes RMAN> recover database; Starting recover at 2011-12-12 13:14:56 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 2011-12-12 13:14:58 RMAN> alter database open resetlogs; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 12/12/2011 13:15:14 ORA-01139: RESETLOGS option only valid after an incomplete database recovery RMAN> exit Recovery Manager complete. [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:16:02 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options [oracle@node1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:17:47 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA11G (DBID=4162194039) RMAN> list incarnation; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ORA11G 4162194039 PARENT 1 2011-09-17 09:46:04 2 2 ORA11G 4162194039 PARENT 995548 2011-10-31 16:05:14 3 3 ORA11G 4162194039 CURRENT 12881971 2011-12-12 13:17:30 [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 13:18:34 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options [oracle@node1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:18:53 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA11G (DBID=4162194039) RMAN> backup database format '/tmp/ora11g_new_%U'; Starting backup at 2011-12-12 13:19:30 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=160 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/opt/oracle/oradata/ora11g/sysaux01.dbf input datafile file number=00001 name=/opt/oracle/oradata/ora11g/system01.dbf input datafile file number=00005 name=/opt/oracle/oradata/ora11g/example01.dbf input datafile file number=00004 name=/opt/oracle/oradata/ora11g/users01.dbf input datafile file number=00003 name=/opt/oracle/oradata/ora11g/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:19:31 channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:20:56 piece handle=/tmp/ora11g—_new_03mu1bb3_1_1 tag=TAG20111212T131931 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 2011-12-12 13:20:57 channel ORA_DISK_1: finished piece 1 at 2011-12-12 13:20:58 piece handle=/tmp/ora11g—_new_04mu1bdo_1_1 tag=TAG20111212T131931 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2011-12-12 13:20:58 RMAN> sql 'alter system switch logfile'; sql statement: alter system switch logfile RMAN> sql 'alter system switch logfile'; sql statement: alter system switch logfile --备份成功后还切换日志，为了证明可以利用这些日志恢复 RMAN> shutdown immediate using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down RMAN> exit Recovery Manager complete. [oracle@node1 ~]$ cd /opt/oracle/oradata/ora11g/ [oracle@node1 ora11g]$ ll 总计 2568524 -rw-r----- 1 oracle oinstall 9846784 12-12 13:27 control01.ctl -rw-r----- 1 oracle oinstall 362422272 12-12 13:22 example01.dbf -rw-r----- 1 oracle oinstall 52429312 12-12 13:22 redo01.log -rw-r----- 1 oracle oinstall 52429312 12-12 13:22 redo02.log -rw-r----- 1 oracle oinstall 52429312 12-12 13:26 redo03.log -rw-r----- 1 oracle oinstall 828383232 12-12 13:22 sysaux01.dbf -rw-r----- 1 oracle oinstall 765468672 12-12 13:22 system01.dbf -rw-r----- 1 oracle oinstall 235937792 12-11 18:05 temp01.dbf -rw-r----- 1 oracle oinstall 99622912 12-12 13:22 undotbs01.dbf -rw-r----- 1 oracle oinstall 169091072 12-12 13:22 users01.dbf [oracle@node1 ora11g]$ rm redo0* [oracle@node1 ora11g]$ rm *.dbf [oracle@node1 ora11g]$ ll 总计 9636 -rw-r----- 1 oracle oinstall 9846784 12-12 13:31 control01.ctl --删除除控制文件外的所有文件 --今天产生的归档日志，从这个里面也可以看到resetlogs操作的界限 [oracle@node1 ora11g]$ ll /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/ 总计 152120 -rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_104_7gc3cogp_.arc -rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_105_7gc3co97_.arc -rw-r----- 1 oracle oinstall 47702528 12-12 13:13 o1_mf_1_106_7gc3cv1w_.arc -rw-r----- 1 oracle oinstall 11425792 12-12 13:17 o1_mf_1_107_7gc3mbpr_.arc -rw-r----- 1 oracle oinstall 984576 12-12 13:18 o1_mf_1_1_7gc3ojqw_.arc -rw-r----- 1 oracle oinstall 1024 12-12 13:18 o1_mf_1_2_7gc3okx8_.arc -rw-r----- 1 oracle oinstall 4608 12-12 13:18 o1_mf_1_3_7gc3onnq_.arc -rw-r----- 1 oracle oinstall 1536 12-12 13:22 o1_mf_1_4_7gc3wnvf_.arc -rw-r----- 1 oracle oinstall 1024 12-12 13:22 o1_mf_1_5_7gc3wt48_.arc
二、跨越resetlogs恢复
[oracle@node1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 12 13:22:50 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 1493174472 bytes Database Buffers 637534208 bytes Redo Buffers 4947968 bytes RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ORA11G 4162194039 PARENT 1 2011-09-17 09:46:04 2 2 ORA11G 4162194039 PARENT 995548 2011-10-31 16:05:14 3 3 ORA11G 4162194039 CURRENT 12881971 2011-12-12 13:17:30 --指定incarnation恢复 RMAN> reset database to incarnation 2; database reset to incarnation 2 RMAN> restore database; Starting restore at 2011-12-12 13:33:25 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=156 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/ora11g/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/ora11g/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ora11g/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ora11g/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/ora11g/example01.dbf channel ORA_DISK_1: reading from backup piece /tmp/ora11g_01mu1aqq_1_1.rman channel ORA_DISK_1: piece handle=/tmp/ora11g_01mu1aqq_1_1.rman tag=TAG20111212T131049 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:25 Finished restore at 2011-12-12 13:34:51 --证明恢复会使用incarnation 2对应的备份集 RMAN> recover database; Starting recover at 2011-12-12 13:35:13 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 106 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc archived log for thread 1 with sequence 107 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc thread=1 sequence=106 archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc thread=1 sequence=107 media recovery complete, elapsed time: 00:00:03 Finished recover at 2011-12-12 13:35:18 --incarnation 2数据还原恢复完成 --指定恢复incarnation 3归档日志 RMAN> reset database to incarnation 3; database reset to incarnation 3 RMAN> recover database; Starting recover at 2011-12-12 13:49:36 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 1 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc archived log for thread 1 with sequence 2 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc archived log for thread 1 with sequence 3 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc archived log for thread 1 with sequence 4 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc archived log for thread 1 with sequence 5 is already on disk as file /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc thread=1 sequence=1 archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc thread=1 sequence=2 archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc thread=1 sequence=3 archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc thread=1 sequence=4 archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc thread=1 sequence=5 unable to find archived log archived log thread=1 sequence=6 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 12/12/2011 13:49:39 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 12882851 --缺少归档日志，恢复完seq=5的归档日志，属于正常情况 --因为没有redo，进行的是不完全恢复，使用resetlogs开打数据库 RMAN> alter database open resetlogs; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 12/12/2011 14:06:04 ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], [] Process ID: 26406 Session ID: 96 Serial number: 7 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== ORA-03114: not connected to ORACLE RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 12/12/2011 14:06:04 ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], [] Process ID: 26406 Session ID: 96 Serial number: 7 --发现意外出现ORA-00600[2898]错误，打开数据库终止
三、查看alert日志
Mon Dec 12 14:05:59 2011 SMON: enabling cache recovery [26406] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:1208679594 end:1208679684 diff:90 (0 seconds) Dictionary check beginning File #5 is offline, but is part of an online tablespace. data file 5: '/opt/oracle/oradata/ora11g/example01.dbf' Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc (incident=4953): ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], [] Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_4953/ora11g_ora_26406_i4953.trc Mon Dec 12 14:06:02 2011 Dumping diagnostic data in directory=[cdmp_20111212140602], requested by (instance=1, osid=26406), summary=[incident=4953]. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc: ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], [] Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_26406.trc: ORA-00600: internal error code, arguments: [2898], [5], [12], [], [], [], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 26406): terminating the instance due to error 600 Mon Dec 12 14:06:03 2011 Instance terminated by USER, pid = 26406 ORA-1092 signalled during: alter database open resetlogs... opiodr aborting process unknown ospid (26406) as a result of ORA-1092 Mon Dec 12 14:06:04 2011 ORA-1092 : opitsk aborting process
发现ORA-00600[2898]错误，导致数据库abort，因为这个错误暂时未找到权威说明。初步怀疑是因为在resetlogs时候，遇到File #5 is offline, but is part of an online tablespace导致
四、重新开启数据库
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 12 14:08:28 2011
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1493174472 bytes
Database Buffers 637534208 bytes
Redo Buffers 4947968 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
5 OFFLINE 12881970
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECK
---------- -------------
1 12902896
2 12902896
3 12902896
4 12902896
5 0
SQL> select FILE#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;
FILE# TO_CHAR(CHECK
---------- -------------
1 12902896
2 12902896
3 12902896
4 12902896
5 12881970
意外的发现数据库竟然open成功，从这里可以看到datafile 5处于offline状态，而且其数据文件头部scn比其他文件小，怀疑没有恢复到一致状态
五、查看重启数据库后alert日志
Archived Log entry 109 added for thread 1 sequence 1 ID 0xf84e7829 dest 1: File #5 is offline, but is part of an online tablespace. data file 5: '/opt/oracle/oradata/ora11g/example01.dbf' Mon Dec 12 14:09:01 2011 Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_28180.trc: ORA-01157: ????/?????? 201 - ??? DBWR ???? ORA-01110: ???? 201: '/opt/oracle/oradata/ora11g/temp01.dbf' ORA-27037: ???????? Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_dbw0_28180.trc: ORA-01186: ?? 201 ?????? ORA-01157: ????/?????? 201 - ??? DBWR ???? ORA-01110: ???? 201: '/opt/oracle/oradata/ora11g/temp01.dbf' File 201 not verified due to error ORA-01157 Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Re-creating tempfile /opt/oracle/oradata/ora11g/temp01.dbf ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Database Characterset is ZHS16GBK No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28225.trc (incident=6153): ORA-25319: Queue table repartitioning aborted Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6153/ora11g_ora_28225_i6153.trc error 25319 happened during Queue table repartitioning Starting background process QMNC Mon Dec 12 14:09:03 2011 QMNC started with pid=31, OS id=28288 LOGSTDBY: Validating controlfile with logical metadata Mon Dec 12 14:09:04 2011 Dumping diagnostic data in directory=[cdmp_20111212140904], requested by (instance=1, osid=28225), summary=[incident=6153]. LOGSTDBY: Validation complete Completed: alter database open Mon Dec 12 14:09:04 2011 Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc (incident=6121): ORA-25319: 队列表重新分区已中止 Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6121/ora11g_mmon_28190_i6121.trc error 25319 happened during Queue table repartitioning Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc (incident=6122): ORA-25319: 队列表重新分区已中止 Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6122/ora11g_mmon_28190_i6122.trc Dumping diagnostic data in directory=[cdmp_20111212140906], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6121]. error 25319 happened during Queue table repartitioning Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_mmon_28190.trc (incident=6123): ORA-25319: 队列表重新分区已中止 Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_6123/ora11g_mmon_28190_i6123.trc Dumping diagnostic data in directory=[cdmp_20111212140907], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6122]. error 25319 happened during Queue table repartitioning Dumping diagnostic data in directory=[cdmp_20111212140908], requested by (instance=1, osid=28190 (MMON)), summary=[incident=6123]. Mon Dec 12 14:09:08 2011 db_recovery_file_dest_size of 4122 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. Mon Dec 12 14:09:08 2011 Starting background process CJQ0 Mon Dec 12 14:09:08 2011 CJQ0 started with pid=32, OS id=28308 Mon Dec 12 14:09:39 2011 Sweep [inc][6153]: completed Sweep [inc][6123]: completed Sweep [inc][6122]: completed
因为第一个resetlogs没有成功，所以temp文件没有创建，这里先创建了temp文件.还有个ORA-25319的错误，和datafile 5 offline有关系
六、trace文件中内容
*** 2011-12-12 14:17:46.627 Started Serial Media Recovery Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea Media Recovery apply resetlogs offline range for datafile 5, incarnation : 1 Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea Dumping database incarnation table: Resetlogs 0 scn and time: 0x0000.00c493a4 12/12/2011 14:05:53 Resetlogs 1 scn and time: 0x0000.00c49033 12/12/2011 13:17:30 Recovery target incarnation = 4, activation ID = 0 Influx buffer limit = 37449 min(50% x 74898, 100000) Start recovery at thread 1 ckpt scn 12881971 logseq 1 block 2 Initial buffer sizes: read 1024K, overflow 832K, change 805K *** 2011-12-12 14:17:46.725 Media Recovery add redo thread 1 *** 2011-12-12 14:18:47.348 Media Recovery Log 2011_12_12/o1_mf_1_1_7gc3ojqw_.arc *** 2011-12-12 14:19:00.198 Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc Log read is SYNCHRONOUS though disk_asynch_io is enabled! Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea *** 2011-12-12 14:19:15.911 Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_2_7gc3okx8_.arc Log read is SYNCHRONOUS though disk_asynch_io is enabled! *** 2011-12-12 14:19:22.638 Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_3_7gc3onnq_.arc Log read is SYNCHRONOUS though disk_asynch_io is enabled! *** 2011-12-12 14:19:31.007 Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_4_7gc3wnvf_.arc Log read is SYNCHRONOUS though disk_asynch_io is enabled! Datafile 5 belongs to incarnation with resetlogs SCN : 12881971, timestamp: 2de0acea *** 2011-12-12 14:19:37.116 Media Recovery Log /opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc Log read is SYNCHRONOUS though disk_asynch_io is enabled! Initial buffer sizes: read 1024K, overflow 832K, change 805K Thread 1 initialized for new incarnation 1 at scn 12882852 Media Recovery current incarnation depth : 0 File 5 (stop scn 12882852) completed recovery at checkpoint scn 12882852
从这里可以知道，datafile 5，没有应用o1_mf_1_5_7gc3wt48_.arc日志恢复，那么恢复datafile 5 需要o1_mf_1_5_7gc3wt48_.arc日志文件
七、恢复数据文件5，打开数据库
SQL> recover datafile 5 ;
ORA-00279: change 12881971 generated at 12/12/2011 13:14:05 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.
arc
ORA-00280: change 12881971 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_5_7gc3wt48_.arc
Log applied.
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1493174472 bytes
Database Buffers 637534208 bytes
Redo Buffers 4947968 bytes
Database mounted.
Database opened.
SQL>
kkjcre1p: unable to spawn jobq slave process, error 1089
闲着无聊，看历史的alert文件，发现kkjcre1p: unable to spawn jobq slave process, error 1089警告
Thu Jul 8 21:23:39 2010 Starting background process CJQ0 CJQ0 started with pid=18, OS id=9019 Thu Jul 8 21:23:39 2010 Shutting down instance: further logons disabled Thu Jul 8 21:23:42 2010 kkjcre1p: unable to spawn jobq slave process, error 1089 ……………… Thu Jul 8 21:23:57 2010 kkjcre1p: unable to spawn jobq slave process, error 1089 Thu Jul 8 21:23:59 2010 Stopping background process QMNC Thu Jul 8 21:23:59 2010 Stopping background process CJQ0 Thu Jul 8 21:24:01 2010 Stopping background process MMNL Thu Jul 8 21:24:02 2010 Stopping background process MMON Thu Jul 8 21:24:03 2010 Shutting down instance (immediate) License high water mark = 1 Waiting for dispatcher 'D000' to shutdown All dispatchers and shared servers shutdown Thu Jul 8 21:24:05 2010 ALTER DATABASE CLOSE NORMAL Thu Jul 8 21:24:05 2010 SMON: disabling tx recovery SMON: disabling cache recovery Thu Jul 8 21:24:05 2010 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Thread 1 closed at log sequence 2 Successful close of redo thread 1 Thu Jul 8 21:24:05 2010 Completed: ALTER DATABASE CLOSE NORMAL Thu Jul 8 21:24:05 2010 ALTER DATABASE DISMOUNT Completed: ALTER DATABASE DISMOUNT ARCH: Archival disabled due to shutdown: 1089 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active ARCH: Archival disabled due to shutdown: 1089 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active
警告原因
If a job is about to be spawned when shutdown of database is in progress, you will see these errors in the alert log file and this is perfectly valid.
解决方法
1、这个警告可以安全的忽略
There is no harm at all because of this warning being logged to the alert.log The Error can be safely ignored as the job coordinator process tried to spawn a job slave when the Shutdown was in progress.
2、设置_JOB_QUEUE_INTERVAL更大值，减少出现该警告概率
One workaround that we can suggest is to set an underscore parameter
_JOB_QUEUE_INTERVAL=120 or greater value
The default value is 60 but when we change to 120 there are less chances of getting the above warnings in the alert log file.
Kkjcre1p: Unable To Spawn Jobq Slave Process, Error 1089 [ID 344275.1]
ORA-00600[4194]故障解决
朋友数据库因为断电，导致数据库正常启动片刻之后，自动down掉
一、alert日志
Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 2 Autotune of undo retention is turned on. IMODE=BR ILAT =18 LICENSE_MAX_USERS = 0 SYS auditing is disabled ksdpec: called for event 13740 prior to event group initialization Starting up ORACLE RDBMS Version: 10.2.0.1.0. System parameters with non-default values: processes = 150 __shared_pool_size = 58720256 __large_pool_size = 4194304 __java_pool_size = 4194304 __streams_pool_size = 4194304 nls_date_format = yyyy-mm-dd hh24:mi:ss sga_target = 335544320 control_files = /u02/ezhou/control01.ctl db_block_size = 8192 compatible = 10.2.0.1.0 log_archive_dest = /u02/arch log_archive_max_processes= 10 db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOTBS1 remote_login_passwordfile= EXCLUSIVE db_domain = dispatchers = (PROTOCOL=TCP) (SERVICE=ezhouXDB) job_queue_processes = 10 background_dump_dest = /u01/pp/oracle/admin/ezhou/bdump user_dump_dest = /u01/pp/oracle/admin/ezhou/udump core_dump_dest = /u01/pp/oracle/admin/ezhou/cdump audit_file_dest = /u01/pp/oracle/admin/ezhou/adump db_name = ezhou open_cursors = 400 sql_trace = TRUE pga_aggregate_target = 94371840 MMAN started with pid=4, OS id=5539 PMON started with pid=2, OS id=5535 DBW0 started with pid=5, OS id=5541 LGWR started with pid=6, OS id=5543 SMON started with pid=8, OS id=5547 CJQ0 started with pid=10, OS id=5577 RECO started with pid=9, OS id=5575 Sat Dec 10 17:15:40 2011 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))' MMNL started with pid=12, OS id=5581 MMON started with pid=11, OS id=5579 Sat Dec 10 17:15:40 2011 starting up 1 shared server(s) ... PSP0 started with pid=3, OS id=5537 CKPT started with pid=7, OS id=5545 Sat Dec 10 17:15:42 2011 ALTER DATABASE MOUNT Sat Dec 10 17:15:46 2011 Setting recovery target incarnation to 3 Sat Dec 10 17:15:47 2011 Successful mount of redo thread 1, with mount id 4055654398 Sat Dec 10 17:15:47 2011 Database mounted in Exclusive Mode Completed: ALTER DATABASE MOUNT Sat Dec 10 17:15:47 2011 ALTER DATABASE OPEN Sat Dec 10 17:15:47 2011 Beginning crash recovery of 1 threads Sat Dec 10 17:15:47 2011 Started redo scan Sat Dec 10 17:15:48 2011 Completed redo scan 319 redo blocks read, 98 data blocks need recovery Sat Dec 10 17:15:50 2011 Started redo application at Thread 1: logseq 24, block 3 Sat Dec 10 17:15:50 2011 Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo03.log Sat Dec 10 17:15:50 2011 Completed redo application Sat Dec 10 17:15:51 2011 Completed crash recovery at Thread 1: logseq 24, block 322, scn 6168722 98 data blocks read, 98 data blocks written, 319 redo blocks read Sat Dec 10 17:15:51 2011 LGWR: STARTING ARCH PROCESSES ARC1 started with pid=17, OS id=5645 ARC0 started with pid=16, OS id=5643 ARC3 started with pid=19, OS id=5649 ARC4 started with pid=20, OS id=5651 ARC2 started with pid=18, OS id=5647 ARC6 started with pid=22, OS id=5655 ARC7 started with pid=23, OS id=5657 ARC5 started with pid=21, OS id=5653 ARC8 started with pid=24, OS id=5659 Sat Dec 10 17:15:52 2011 ARC0: Archival started ARC1: Archival started ARC2: Archival started ARC3: Archival started ARC4: Archival started ARC5: Archival started ARC6: Archival started ARC7: Archival started ARC8: Archival started ARC9: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC9 started with pid=25, OS id=5661 Sat Dec 10 17:15:52 2011 Thread 1 advanced to log sequence 25 Sat Dec 10 17:15:53 2011 ARC2: STARTING ARCH PROCESSES Sat Dec 10 17:15:53 2011 ARC6: Becoming the 'no FAL' ARCH ARC6: Becoming the 'no SRL' ARCH Sat Dec 10 17:15:53 2011 ARC3: Becoming the heartbeat ARCH Sat Dec 10 17:15:53 2011 Thread 1 opened at log sequence 25 Current log# 1 seq# 25 mem# 0: /u02/ezhou/redo01.log Current log# 1 seq# 25 mem# 1: /u02/ezhou/redo01a.rdo Successful open of redo thread 1 Sat Dec 10 17:15:53 2011 SMON: enabling cache recovery Sat Dec 10 17:15:54 2011 ARCa: Archival started ARC2: STARTING ARCH PROCESSES COMPLETE ARCa started with pid=26, OS id=5663 Sat Dec 10 17:15:57 2011 Successfully onlined Undo Tablespace 1. Sat Dec 10 17:15:57 2011 SMON: enabling tx recovery Sat Dec 10 17:15:57 2011 Database Characterset is AL32UTF8 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC QMNC started with pid=27, OS id=5666 Sat Dec 10 17:16:13 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_smon_5547.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:16:17 2011 Completed: ALTER DATABASE OPEN Sat Dec 10 17:16:27 2011 Doing block recovery for file 2 block 4124 Block recovery from logseq 25, block 68 to scn 6168829 Sat Dec 10 17:16:27 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery stopped at EOT rba 25.126.16 Block recovery completed at rba 25.126.16, scn 0.6168829 Doing block recovery for file 2 block 73 Block recovery from logseq 25, block 68 to scn 6168786 Sat Dec 10 17:16:28 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery completed at rba 25.69.16, scn 0.6168789 Sat Dec 10 17:16:28 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_smon_5547.trc: ORA-01595: error freeing extent (2) of rollback segment (5)) ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:16:30 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j002_5690.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:16:37 2011 Doing block recovery for file 2 block 4124 Block recovery from logseq 25, block 68 to scn 6168829 Sat Dec 10 17:16:37 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery completed at rba 25.126.16, scn 0.6168830 Doing block recovery for file 2 block 73 Block recovery from logseq 25, block 68 to scn 6168841 Sat Dec 10 17:16:37 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery completed at rba 25.149.16, scn 0.6168843 Sat Dec 10 17:16:37 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j002_5690.trc: ORA-12012: error on auto execute of job 8886 ORA-00607: Internal error occurred while making a change to a data block Sat Dec 10 17:16:41 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j003_5692.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:16:42 2011 DEBUG: Replaying xcb 0x32a2b17c, pmd 0x32bdbd24 for failed op 8 Doing block recovery for file 2 block 4124 Block recovery from logseq 25, block 68 to scn 6168829 Sat Dec 10 17:16:42 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery completed at rba 25.126.16, scn 0.6168830 Sat Dec 10 17:16:43 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j003_5692.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:16:46 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_j003_5692.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:17:46 2011 DEBUG: Replaying xcb 0x32a2b17c, pmd 0x32bdbd24 for failed op 8 Doing block recovery for file 2 block 4124 Block recovery from logseq 25, block 68 to scn 6168829 Sat Dec 10 17:17:46 2011 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0 errs 0: /u02/ezhou/redo01.log Mem# 1 errs 0: /u02/ezhou/redo01a.rdo Block recovery completed at rba 25.126.16, scn 0.6168830 Sat Dec 10 17:17:48 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_pmon_5535.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] Sat Dec 10 17:17:49 2011 Errors in file /u01/pp/oracle/admin/ezhou/bdump/ezhou_pmon_5535.trc: ORA-00600: internal error code, arguments: [4194], [30], [27], [], [], [], [], [] PMON: terminating instance due to error 472 Instance terminated by PMON, pid = 5535
二、MOS记录
ERROR: ORA-600 [4194] [a] [b] VERSIONS: versions 6.0 to 10.1 DESCRIPTION: A mismatch has been detected between Redo records and rollback (Undo) records. We are validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block. This error is reported when the validation fails. ARGUMENTS: Arg [a] Maximum Undo record number in Undo block Arg [b] Undo record number from Redo block
三、解决办法
1、修改参数
undo_management= MANUAL
undo_tablespace= SYSTEM
2、打开数据库，删除当前undo空间，重建新undo空间
3、修改参数
undo_management= AUTO
undo_tablespace= UNDOTBSNEW
4、重新启动数据库
in/exists和not in/not exists语意探讨
本篇只讨论in/exists和not in/not exists语意，不涉及这些写法的执行效率问题，至于效率问题请见:in/exists和not in/not exists执行效率
1、准备实验环境
C:\Users\XIFENFEI>sqlplus chf_xff/xifenfei
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 12月 10 14:55:14 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create table t1 (c1 number,c2 number);
表已创建。
SQL> create table t2 (c1 number,c2 number);
表已创建。
SQL> insert into t1 values (1,2);
已创建 1 行。
SQL> insert into t1 values (1,3);
已创建 1 行。
SQL> insert into t2 values (1,2);
已创建 1 行。
SQL> insert into t2 values (1,null);
已创建 1 行。
SQL> insert into t1 values (1,null);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from t1;
C1 C2
---------- ----------
1 2
1 3
1
SQL> select * from t2;
C1 C2
---------- ----------
1 2
1
Note:t1和t2表都有null，且t1比t2多一条记录
2、t2做内部表
SQL> select * from t1 where c2 in (select c2 from t2 );
C1 C2
---------- ----------
1 2
SQL> select * from t1 where exists (select c2 from t2 where t1.c2=t2.c2);
C1 C2
---------- ----------
1 2
SQL> select * from t1 where c2 not in (select c2 from t2 );
未选定行
SQL> select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
C1 C2
---------- ----------
1 3
1
3、t1为内部表
SQL> select * from t2 where c2 in (select c2 from t1 );
C1 C2
---------- ----------
1 2
SQL> select * from t2 where exists (select c2 from t1 where t1.c2=t2.c2);
C1 C2
---------- ----------
1 2
SQL> select * from t2 where c2 not in (select c2 from t1 );
未选定行
SQL> select * from t2 where not exists (select 1 from t1 where t1.c2=t2.c2);
C1 C2
---------- ----------
1
3、结论
in和exists结果相同(都会排除掉null,无论内部表中有无null)
not in会过滤掉外部表中的null(即使内部表中无null)
not exists不会过滤掉外部表的null(即使内部表有null)
由于篇幅关系,括号中的部分实验过程未展现出来
回收dba中alter system处理方法
今天在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
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数据文件/临时文件
有些时候，想删除一个数据文件(临时文件),在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
一、发现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.
异常断电导致current redo损坏处理
一、数据库启动异常
网友因异常断电导致数据库不能正常启动，处理过程记录
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]，但是数据库还是正常打开，具体原因待研究