[20141218]关于dual.txt

[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;。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1371706/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1371706/