ITPub博客

首页 > 数据库 > Oracle > [20141218]关于dual.txt

[20141218]关于dual.txt

原创 Oracle 作者:lfree 时间:2014-12-18 08:38:43 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2696
  • 访问量
    6466017