手动修改数据库字典是很危险的事情，通常会导致数据库无法启动。本文就修改global_name进行测试。
数据库版本
www.ads1st.com select * from v$version;
NLSRTL Version 11.2.0.1.0 – Production
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 Linux: Version 11.2.0.1.0 – Production
Elapsed: 00:00:00.02
先做好备份
RMAN> backup as compressed backupset
2> incremental level 0 database;Starting backup at 03-SEP-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/oradata/r11g/R11G/datafile/o1_mf_system_5jpr7oxg_.dbf
input datafile file number=00002 name=/oracle/oradata/r11g/R11G/datafile/o1_mf_sysaux_5jpr80b2_.dbf
input datafile file number=00003 name=/oracle/oradata/r11g/R11G/datafile/o1_mf_sys_undo_5jpr88px_.dbf
input datafile file number=00004 name=/oracle/oradata/r11g/R11G/datafile/o1_mf_test_5jpt1yrb_.dbf
channel ORA_DISK_1: starting piece 1 at 03-SEP-10
channel ORA_DISK_1: finished piece 1 at 03-SEP-10
piece handle=/oracle/11g/dbs/01lmudjn_1_1 tag=TAG20100903T030527 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting compressed incremental level 0 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 03-SEP-10
channel ORA_DISK_1: finished piece 1 at 03-SEP-10
piece handle=/oracle/11g/dbs/02lmudn2_1_1 tag=TAG20100903T030527 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-SEP-10</pre>
做测试之前，先看一下global_name相关的信息。首先，global_name是一个基于sys.props$的视图！
www.ads1st.com select owner,object_type,object_id from dba_objects where object_name=’GLOBAL_NAME’;
OWNER OBJECT_TYPE OBJECT_ID
———— ——————- ———-
SYS VIEW 3327
PUBLIC SYNONYM 3328www.ads1st.com set long 2000;
www.ads1st.com select owner,view_name,text from dba_views where owner=’SYS’ and view_name=’GLOBAL_NAME’;OWNER VIEW_NAME TEXT
———— —————————— ——————————————————————————–
SYS GLOBAL_NAME select value$ from sys.props$ where name = ‘GLOBAL_DB_NAME’www.ads1st.com select owner,object_name,object_id from dba_objects where object_name=’PROPS$’ and owner=’SYS’;
OWNER OBJECT_NAME OBJECT_ID
———— ——————– ———-
SYS PROPS$ 98
其实，从10046事件也可以看出，执行update时实际操作的是sys.props$表
PARSING IN CURSOR #6 len=37 dep=0 uid=0 oct=6 lid=0 tim=1283454552532974 hv=1279987886 ad=’37ab6428′ sqlid=’7yss34964q45f’
update global_name set global_name=”
END OF STMT
PARSE #6:c=19998,e=1106453,p=7,cr=48,cu=0,mis=1,r=0,dep=0,og=1,plh=2065329992,tim=1283454552532972
EXEC #6:c=1000,e=51151,p=0,cr=3,cu=3,mis=0,r=1,dep=0,og=1,plh=2065329992,tim=1283454552584199
STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op=’UPDATE PROPS$ (cr=3 pr=0 pw=0 time=0 us)’
STAT #6 id=2 cnt=1 pid=1 pos=1 obj=98 op=’TABLE ACCESS FULL PROPS$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=28 card=1)’
WAIT #6: nam=’SQL*Net message to client’ ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1283454552584505
WAIT #6: nam=’SQL*Net message from client’ ela= 281 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1283454552584828
再看一下当前的日志信息
www.ads1st.com select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
——- ——– ———- ———- ———- ——– — ———— ————- ——————- ———— ———
1 1 42 104857600 512 2 YES INACTIVE 1199476 2010-09-02 15:19:18 1220513 2010-09-8
2 1 41 104857600 512 2 YES INACTIVE 1178851 2010-09-01 19:02:50 1199476 2010-09-8
4 1 43 10485760 512 2 NO CURRENT 1220513 2010-09-03 03:02:18 2.8147E+14Elapsed: 00:00:00.04
www.ads1st.com select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_
——- ———— ——- ——————————————————————– —
1 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_1_5jpr6ss5_.log NO
1 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_1_5jpr6xcl_.log NO
2 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_2_5jpr75wf_.log NO
2 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_2_5jpr7d8k_.log NO
4 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_4_66210k8y_.log NO
4 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_4_66210kb3_.log NO6 rows selected.
准备工作作到这里，就可以开始惊魂之旅了（重要：绝对危险动作，千万不能在生产环境使用，造成任何后果与本文作者无任何关系！）
首先，update global_name set global_name=”，实例立马宕掉，重启后报ORA-00600[18062]错误
www.ads1st.com update global_name set global_name=”;
1 row updated.
Elapsed: 00:00:00.01
www.ads1st.com commit;Commit complete.
Elapsed: 00:00:00.01
Fri Sep 03 03:25:21 2010
Errors in file /oracle/diag/rdbms/r11g/r11g/trace/r11g_ora_2717.trc (incident=57825):
ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/r11g/r11g/incident/incdir_57825/r11g_ora_2717_i57825.trc
Fri Sep 03 03:25:27 2010
Sweep [inc][57825]: completed
Sweep [inc2][57825]: completed
Fri Sep 03 03:25:33 2010
Errors in file /oracle/diag/rdbms/r11g/r11g/trace/r11g_ora_2732.trc (incident=57826):
ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/r11g/r11g/incident/incdir_57826/r11g_ora_2732_i57826.trc
Fri Sep 03 03:25:34 2010
Sweep [inc][57826]: completed
Sweep [inc2][57826]: completed
Fri Sep 03 03:26:33 2010
PMON (ospid: 2527): terminating the instance due to error 469
Instance terminated by PMON, pid = 2527
出现这种情况怎么恢复呢，如果数据库没有备份，在此时肯定欲哭无泪。。。。不过，对于大型数据库，即使有备份也需要耗费很长时间完成恢复。本文实验另一种恢复方法，即找到更改前的SCN做基于SCN的不完全恢复。这样可以在很短的时间内解决问题。
前面我们看了日志信息，在此时可以派上用场了。首先，作日志文件的11.5dump，找到sys.props$发生更改的最早SCN
www.ads1st.com startup mount
ORACLE instance started.Total System Global Area 472907776 bytes
Fixed Size 1337240 bytes
Variable Size 234883176 bytes
Database Buffers 230686720 bytes
Redo Buffers 6000640 bytes
Database mounted.www.ads1st.com select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
——- ——– ———- ———- ———- ——– — ———— ————- ——— ———— ———
1 1 42 104857600 512 2 YES INACTIVE 1199476 02-SEP-10 1220513 03-SEP-10
4 1 43 10485760 512 2 NO CURRENT 1220513 03-SEP-10 2.8147E+14
2 1 41 104857600 512 2 YES INACTIVE 1178851 01-SEP-10 1199476 02-SEP-10Elapsed: 00:00:00.02
www.ads1st.com select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_
——- ———— ——- ——————————————————————– —
1 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_1_5jpr6ss5_.log NO
1 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_1_5jpr6xcl_.log NO
2 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_2_5jpr75wf_.log NO
2 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_2_5jpr7d8k_.log NO
4 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_4_66210k8y_.log NO
4 ONLINE /oracle/oradata/r11g/R11G/onlinelog/o1_mf_4_66210kb3_.log NO6 rows selected.
Elapsed: 00:00:00.01
www.ads1st.com alter system dump logfile ‘/oracle/oradata/r11g/R11G/onlinelog/o1_mf_4_66210k8y_.log’ layer 11 opcode 5;System altered.
Elapsed: 00:00:00.79
www.ads1st.com oradebug setmypid
Statement processed.
www.ads1st.com oradebug tracefile_name;
/oracle/diag/rdbms/r11g/r11g/trace/r11g_ora_2800.trc
www.ads1st.com ho grep “OBJ:98″ /oracle/diag/rdbms/r11g/r11g/trace/r11g_ora_2800.trc
CHANGE #1 TYP:0 CLS:1 AFN:1 DBA:0×00400321 OBJ:98 SCN:0×0000.0012a112 SEQ:1 OP:11.5 ENC:0 RBL:0
CHANGE #1 TYP:0 CLS:1 AFN:1 DBA:0×00400321 OBJ:98 SCN:0×0000.0012a113 SEQ:1 OP:11.5 ENC:0 RBL:0
此处，我们以SCN 12a112为标准，再向前移一点，对数据库做基于SCN的恢复
www.ads1st.com select to_number(’0012a112′,’xxxxxxxx’) from dual;
TO_NUMBER(’0012A112′,’XXXXXXXX’)
——————————–
1220882Elapsed: 00:00:00.00
www.ads1st.com show parameter allow
www.ads1st.com show parameter corr
www.ads1st.com alter system set “_allow_resetlogs_corruption”=true scope=spfile;System altered.
Elapsed: 00:00:00.01
www.ads1st.com alter system set “_allow_terminal_recovery_corruption”=true scope=spfile;System altered.
Elapsed: 00:00:00.01
www.ads1st.com shutdown immediate;
ORA-01109: database not openDatabase dismounted.
ORACLE instance shut down.
www.ads1st.com recover database until change 1220880;
ORA-01034: ORACLE not available
Process ID: 2800
Session ID: 16 Serial number: 3www.ads1st.com connect / as sysdba
Connected to an idle instance.
www.ads1st.com startup mount;
ORACLE instance started.Total System Global Area 472907776 bytes
Fixed Size 1337240 bytes
Variable Size 234883176 bytes
Database Buffers 230686720 bytes
Redo Buffers 6000640 bytes
Database mounted.www.ads1st.com recover database until change 1220880;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/oracle/oradata/r11g/R11G/datafile/o1_mf_system_5jpr7oxg_.dbf’www.ads1st.com alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [1220891], [0], [1221044], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [1220890], [0], [1221044], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1220888], [0], [1221044], [12583040], [], [], [], [], [], []
Process ID: 2842
Session ID: 1 Serial number: 5
恢复成功，但是打开数据库时报ORA-00600[2662]错误，这主要是由于SCN号不一致引起的，需要ADJUST_SCN
www.ads1st.com oradebug event 10015 trace name adjust_scn level 100;
Statement processed.
www.ads1st.com alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recoveryElapsed: 00:00:00.04
www.ads1st.com alter database open;Database altered.
Elapsed: 00:00:15.97
www.ads1st.com select status from v$instance;STATUS
————
OPENElapsed: 00:00:00.00
打开数据库后不久，实例又宕掉，查看警告日志文件，报告ORA-00600[4194]错误，这是由于回滚段问题引起的
oracle@ www.ads1st.com $tail -f /oracle/diag/rdbms/r11g/r11g/trace/alert_r11g.log
Doing block recovery for file 3 block 1277
Resuming block recovery (PMON) for file 3 block 1277
Block recovery from logseq 3, block 52 to scn 1261052
Recovery of Online Redo Log: Thread 1 Group 4 Seq 3 Reading mem 0
Mem# 0: /oracle/oradata/r11g/R11G/onlinelog/o1_mf_4_67zyymh2_.log
Mem# 1: /oracle/oradata/r11g/R11G/onlinelog/o1_mf_4_67zyymxo_.log
Block recovery completed at rba 3.94.16, scn 0.1261054
Errors in file /oracle/diag/rdbms/r11g/r11g/trace/r11g_pmon_2920.trc (incident=60834):
ORA-00600: internal error code, arguments: [4194], [], [
Incident details in: /oracle/diag/rdbms/r11g/r11g/incident/incdir_60834/r11g_pmon_2920_i60834.trc
Errors in file /oracle/diag/rdbms/r11g/r11g/trace/r11g_pmon_2920.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 2920): terminating the instance due to error 472
Instance terminated by PMON, pid = 2920Block recovery completed at rba 2.55.16, scn 0.1240934
Errors in file /oracle/diag/rdbms/r11g/r11g/trace/r11g_smon_2882.trc:
ORA-01595: error freeing extent (2) of rollback segment (2))
ORA-00600: internal error code, arguments: [4194], [], [^B^@], [], [], [], [], [], [], [], [], []
警告日志显示，执行2号回滚段的恢复失败，实例强制宕机。找到这个回滚段后，修改相关参数禁用该回滚段，数据库恢复正常
oracle@ www.ads1st.com $strings o1_mf_system_5jpr7oxg_.dbf | grep _SYSSMU2 | cut -d $ -f 1 | sort -u
_SYSSMU2_82951951
www.ads1st.com alter system set “_corrupted_rollback_segments”=’_SYSSMU2_82951951′ scope=spfile;
www.ads1st.com shutdown abort;
ORACLE instance shut down.
www.ads1st.com startup
ORA-00000: normal, successful completion
www.ads1st.com connect / as sysdba
Connected to an idle instance.
www.ads1st.com startup
ORACLE instance started.Total System Global Area 472907776 bytes
Fixed Size 1337240 bytes
Variable Size 234883176 bytes
Database Buffers 230686720 bytes
Redo Buffers 6000640 bytes
Database mounted.
Database opened.www.ads1st.com show parameter cor
NAME TYPE VALUE
———————————— ———– ——————————————————————————–
_allow_resetlogs_corruption boolean TRUE
_allow_terminal_recovery_corruption boolean TRUE
_corrupted_rollback_segments string _SYSSMU2_82951951
background_core_dump string partial
control_file_record_keep_time integer 7
core_dump_dest string /oracle/diag/rdbms/r11g/r11g/cdump
shadow_core_dump string partialwww.ads1st.com select startup_time,sysdate Now from v$instance;
STARTUP_TIME NOW
——————- ——————-
2010-09-03 03:43:56 2010-09-03 04:24:28Elapsed: 00:00:00.01
删除有问题的2号回滚段
Related Postswww.ads1st.com drop rollback segment “_SYSSMU2_82951951″;
Rollback segment dropped.
Elapsed: 00:00:00.34