联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-00704 ORA-00604 ORA-01406故障分析
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
根据客户反馈系统运行的是9.2.0.8版本,但是服务器上面安装有10.2.0.1服务端,由于使用过10.2启动过数据库导致现在9.2.0.8无法启动.
数据库启动报错
Starting up ORACLE RDBMS Version: 9.2.0.8.0. System parameters with non-default values: ………… db_cache_size = 209715200 compatible = 9.2.0.0.0 db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 ………… Thread 1 advanced to log sequence 7517 Thread 1 opened at log sequence 7517 Current log# 1 seq# 7517 mem# 0: F:\ORACLE\ORADATA\ORACLE\REDO01.LOG Successful open of redo thread 1 Tue Apr 25 21:16:20 2017 SMON: enabling cache recovery Tue Apr 25 21:16:20 2017 Errors in file f:\oracle\admin\oracle\udump\oracle_ora_3908.trc: ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 层 1 出现错误 ORA-01406: 读取的列值被截断 Tue Apr 25 21:16:20 2017 Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Tue Apr 25 21:16:20 2017 Errors in file f:\oracle\admin\oracle\bdump\oracle_pmon_2124.trc: ORA-00704: bootstrap process failure Tue Apr 25 21:16:20 2017 Errors in file f:\oracle\admin\oracle\bdump\oracle_reco_2556.trc: ORA-00704: bootstrap process failure Tue Apr 25 21:16:20 2017 Errors in file f:\oracle\admin\oracle\bdump\oracle_smon_628.trc: ORA-00704: bootstrap process failure Tue Apr 25 21:16:21 2017 Errors in file f:\oracle\admin\oracle\bdump\oracle_ckpt_2212.trc: ORA-00704: bootstrap process failure Tue Apr 25 21:16:21 2017 Errors in file f:\oracle\admin\oracle\bdump\oracle_lgwr_2756.trc: ORA-00704: bootstrap process failure Tue Apr 25 21:16:21 2017 Errors in file f:\oracle\admin\oracle\bdump\oracle_dbw0_1756.trc: ORA-00704: bootstrap process failure Instance terminated by USER, pid = 3908 ORA-1092 signalled during: ALTER DATABASE OPEN...
错误比较明显bootstrap$的相关sql在递归的时候报错(ORA-01406)
我们分析alert日志
---9.2.0.1版本运行了很长时间 Mon Apr 13 20:44:29 2009 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 2 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.1.0. --然后升级到9.2.0.8 Thu Jun 18 17:32:09 2015 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 2 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.8.0. System parameters with non-default values: ………… Thu Jun 18 17:32:18 2015 SMON: enabling cache recovery Thu Jun 18 17:32:19 2015 Successfully onlined Undo Tablespace 1. Thu Jun 18 17:32:19 2015 SMON: enabling tx recovery Thu Jun 18 17:32:19 2015 Database Characterset is ZHS16GBK Updating 9.2.0.1.0 NLS parameters in sys.props$ -- adding 9.2.0.8.0 NLS parameters. replication_dependency_tracking turned off (no async multimaster replication found) Completed: alter database open ………… Thu Jun 18 17:38:32 2015 Database Characterset is ZHS16GBK Thu Jun 18 17:38:33 2015 ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY; Thu Jun 18 17:38:33 2015 ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY; Thu Jun 18 17:38:33 2015 ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY; replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN MIGRATE --再升级到10.2.0.1 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 timed_statistics = TRUE sga_max_size = 1610612736 shared_pool_size = 209715200 large_pool_size = 8388608 java_pool_size = 159383552 streams_pool_size = 50331648 control_files = F:\ORACLE\ORADATA\ORACLE\CONTROL01.CTL db_block_size = 8192 db_cache_size = 209715200 compatible = 9.2.0.0.0 ………… Thu Jun 18 19:43:30 2015 Database Characterset is ZHS16GBK Updating 9.2.0.8.0 NLS parameters in sys.props$ -- adding 10.2.0.1.0 NLS parameters. ………… Thu Jun 18 19:43:44 2015 ALTER SYSTEM enable restricted session; MMNL started with pid=12, OS id=5212 Thu Jun 18 19:43:44 2015 ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY; Thu Jun 18 19:43:44 2015 ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY; Thu Jun 18 19:43:44 2015 ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY; Thu Jun 18 19:43:44 2015 replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN MIGRATE
这里很明显数据库从2009年4月开始9.2.0.1版本开始运行,然后到2015年6月18日升级到9.2.0.8版本,紧接着升级到10.2.0.1(升级8.2.0.8是为升级10.2.0.1的中间过度操作).然后这个库一直使用10.2.0.1版本运行,这次重启不知道什么原因客户以为是9.2.0.8的数据库版本,然后不管怎么样也无法启动成功(这里不知道什么原因win 服务中使用的9.2.0.8的软件,估计被人误操作了).解决该问题,就是把服务切换成10.2.0.1版本数据库正常启动.
再次提醒大家，在oracle恢复的过程中,需要仔细分析日志,日志不会骗人,不要轻信客户的现场描述