[20141218]关于dual.txt
--昨天在群里有人问,数据库在mount状态下,打入什么命令,可以导致在后续执行alter database open的时候,数据库crash。
--实际上最简单的方法就是desc dual就可以出现这种现象,就是ora-4043错误。
--做一个测试:
SYS@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 176160856 bytes
Database Buffers 285212672 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> desc dual
ERROR:
ORA-04043: object dual does not exist
SYS@test> host oerr ora 4043
04043, 00000, "object %s does not exist"
// *Cause: An object name was specified that was not recognized by the system.
// There are several possible causes:
// - An invalid name for a table, view, sequence, procedure, function,
// package, or package body was entered. Since the system could not
// recognize the invalid name, it responded with the message that the
// named object does not exist.
// - An attempt was made to rename an index or a cluster, or some
// other object that cannot be renamed.
// *Action: Check the spelling of the named object and rerun the code. (Valid
// names of tables, views, functions, etc. can be listed by querying
// the data dictionary.)
SYS@test> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SYS@test> select open_mode from v$database ;
ERROR:
ORA-03114: not connected to ORACLE
--这个是因为在mount状态下,仅仅少量的视图可以访问,当访问到不存在的表与视图时,这部分信息已经加载到shared pool。
--开机以后,访问到这些对象被认为是不正常的,而dual表在open状态要使用,其结果直接导致实例crash。
--换成这样,就可以正常启动。
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 176160856 bytes
Database Buffers 285212672 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> desc dual
ERROR:
ORA-04043: object dual does not exist
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> alter database open ;
Database altered.
--可以测试别的视图看看,比如dba_tables看看。
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 176160856 bytes
Database Buffers 285212672 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> select count(*) from dba_tables;
select count(*) from dba_tables
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SYS@test> alter database open ;
Database altered.
SYS@test> select count(*) from dba_tables;
COUNT(*)
------------
1595
--这样没有问题。如果执行desc dba_tables看看。
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 176160856 bytes
Database Buffers 285212672 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> desc dba_tables
ERROR:
ORA-04043: object dba_tables does not exist
SYS@test> alter database open ;
Database altered.
SYS@test> select count(*) from dba_tables;
select count(*) from dba_tables
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@test> alter system flush shared_pool;
System altered.
SYS@test> select count(*) from dba_tables;
COUNT(*)
------------
1595
--总之,如果在mount后出现,ora-04043错误,最简单的方法就是在open前,执行一次alter system flush shared_pool;。