|
如果非必要一般不会重启服务器，如果重启服务器，必须先要正确关闭oracle，然后在重启。
人无完人，总有人会犯错误，其中一台线上oracle维护者，失误，直接将oracle服务器重启了，重启之后，直接造成了，数据库提示ora-00603: ORACLE server session terminated by fatal error,
用sqlplus 登录oracle;
1 2 3 | su - oracle
alter system set events '10046 trace name context off';
alter system set timed_statistics=false;
|
然后重启oracle，最后发现还是报错，
去了oracle 的日志目录并查找trace文件
1 2 3 4 5 6 | 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
1 | SQL> create pfile from spfile
|
修改pfile参数：
1 2 3 | #*.undo_management='AUTO'
*.undo_management='MANUAL'
_allow_resetlogs_corruption=true
|
2）以pfile启动数据库
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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
1 2 3 4 5 6 7 | 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重启数据库
01 02 03 04 05 06 07 08 09 10 11 12 | 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就没事了。
|
