笔者的工作环境中有多台oracle 10G 数据库,都是清一色的64bit的,笔者所维护的oracle,如果非必要一般不会重启服务器,如果重启服务器,必须先要正确关闭oracle,然后在重启。
人无完人,总有人会犯错误,其中一台线上oracle维护者,失误,直接将oracle服务器重启了,重启之后,直接造成了,数据库提示ora-00603: ORACLE server session terminated by fatal error,
用sqlplus 登录oracle;
su - oracle alter system set events '10046 trace name context off'; alter system set timed_statistics=false;
然后重启oracle,最后发现还是报错,
去了oracle 的日志目录并查找trace文件
cd /data0/oracle/admin/dzinfoiims/bdump/
cat dzinfoiims_smon_6401.trc SMON: following errors trapped and ignored: ORA-01595: error freeing extent (4) of rollback segment (1)) ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [62], [32], [], [], [], [], []
最后几行显示 ora-01595,按照提示因为不正确关闭oracle导致回滚段失败,基本上可以断定undo表空间损坏,但是可以通过重建undo文件。
1)生成pfile
SQL> create pfile from spfile
修改pfile参数:
#*.undo_management='AUTO' *.undo_management='MANUAL' _allow_resetlogs_corruption=true
2)以pfile启动数据库
SQL> startup mount pfile='/data0/oracle/product/10.2.0/db_1/dbs/initdzinfoiims.ora'; ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 2085872 bytes Variable Size 373296144 bytes Database Buffers 230686720 bytes Redo Buffers 6299648 bytes Database mounted. SQL> show parameter undo NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> alter database open; Database altered.
3)新建undo表空间undotbs2
SQL> create undo tablespace undotbs2 datafile '/data0/oracle/oradata/dzinfoiims/undotbs02.dbf' size 1G; table space created. SQL> drop tablespace undotbs1; The deleted table space . SQL> alter tablespace undotbs2 rename to undotbs1; table space was alted.
4)以spfile重启数据库
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 5016387584 bytes Fixed Size 2027640 bytes Variable Size 671092616 bytes Database Buffers 4328521728 bytes Redo Buffers 14745600 bytes Database mounted.
本以为至此结束,但是有两个表查询,竟然提示ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []
这是oracle的一个bug
可以通过alter日志,查看是那个表有问题
可以在dba状态下
execute dbms_stats.delete_table_stats('iims','bbs_message_map'); PL/SQL procedure successfully completed.
接下该表,就select就没事了。