本文共 11935 字，大约阅读时间需要 39 分钟。
[20171127]dual.txt
--//我曾经提到如果在nomount,mount阶段,desc dual,启动到open阶段时,数据库会直接崩溃.
--//链接:SYS@test> desc dual
ERROR: ORA-04043: object dual does not existSYS@test> alter database open;
alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00942: table or view does not exist Process ID: 14360 Session ID: 5 Serial number: 3--//昨天别人在windows的12c做了测试,不会出现上面的情况.我自己也测试看确实这样.我自己google发现
--//可能是bug ORA-4043 On DBA_* Views If They Are Described In Mount Stage (Doc ID 296235.1)ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1]
Available workarounds are: 1) Don't describe the dba_* views at mount stage. OR 2) If you issue DESC of any DBA_*views at mount stage, then shutdown and restart the DB instance. OR 3) Flush the shared pool. SQL> Alter system flush shared_pool; and then reissue the failing command.--//而dual表非常特殊open阶段就使用到,也是这个原因导致11.X版本都存在这个问题.
--//而实际上在nomount阶段实际上可以访问到dual表,不过此dual非真实的表,通过例子说明:1.启动到nomount阶段:
SYS@book> startup nomount
ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytesSYS@book> select * from v$version;
BANNER ---------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production--//实际上这个阶段仅仅加载实例,而数据库内的表是无法访问的,而一些X$表已经加载.
SYS@book> select STARTUP_TIME from v$instance; STARTUP_TIME ------------------- 2017-11-27 09:21:02SYS@book> select open_mode from v$database;
select open_mode from v$database * ERROR at line 1: ORA-01507: database not mounted --//v$instance可以访问,而v$database还不行,这个到mount阶段才ok.SYS@book> select * from dual ;
ADDR INDX INST_ID DU ---------------- ---------- ---------- -- 000000000A6225A0 0 1 XSYS@book> select * from x$dual ;
ADDR INDX INST_ID DU ---------------- ---------- ---------- -- 000000000A6225A0 0 1 XSYS@book> select owner,name,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name='DUAL';
no rows selectedSYS@book> select owner,name,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name='X$DUAL';
OWNER NAME NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS FULL_HASH_VALUE HASH_VALUE ------ -------------------- -------------------- ---------- ------------ ---------- ---------- -------------------------------- ---------- SYS X$DUAL TABLE/PROCEDURE TABLE 4704 1 0 e8977ba36b4cd7fe1a894be6e9beed3f 3921603903--//奇怪查询v$db_object_cache无法查到DUAL,而可以查询X$DUAL.oracle如何知道这个dual对象呢?
SYS@book> @ &r/sharepool/shp4 aa 3921603903
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2 new 18: WHERE kglobt03 = 'aa' or kglhdpar='aa' or kglhdadr='aa' or KGLNAHSH= 3921603903 TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ------- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- -------------------------- ---------- ??????? 000000007E2307C0 000000007E2307C0 X$DUAL 0 000000007E230708 00 4704 0 0 4704 4704 3921603903 0 --//前面乱码是中文,因为数据库没启动mount或者open的原因无法正常显示.SYS@book> alter system flush shared_pool;
System altered.SYS@book> select owner,name,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name='X$DUAL';
no rows selectedSYS@book> select * from dual ;
ADDR INDX INST_ID DU ---------------- ---------- ---------- -- 000000000A6225A0 0 1 XSYS@book> select owner,name,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name='X$DUAL';
OWNER NAME NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS FULL_HASH_VALUE HASH_VALUE ------ -------------------- -------------------- ---------- ------------ ---------- ---------- -------------------------------- ---------- SYS X$DUAL TABLE/PROCEDURE TABLE 4704 1 0 e8977ba36b4cd7fe1a894be6e9beed3f 3921603903SYS@book> @ &r/sharepool/shp4 aa 3921603903
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2 new 18: WHERE kglobt03 = 'aa' or kglhdpar='aa' or kglhdadr='aa' or KGLNAHSH= 3921603903 TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ------ -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- -------------------------- ---------- ??????? 000000007E19FD88 000000007E19FD88 X$DUAL 0 000000007E1AD970 00 4704 0 0 4704 4704 3921603903 02.启动到mount阶段:
SYS@book> alter database mount ; Database altered.SYS@book> select open_mode from v$database;
OPEN_MODE -------------------- MOUNTED--//这个时候已经读控制文件,可以访问v$database视图.
SYS@book> select owner,name,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name in ('DUAL','X$DUAL');
OWNER NAME NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS FULL_HASH_VALUE HASH_VALUE ------ -------------------- -------------------- ---------- ------------ ---------- ---------- -------------------------------- ---------- SYS X$DUAL TABLE/PROCEDURE TABLE 0 1 0 e8977ba36b4cd7fe1a894be6e9beed3f 3921603903SYS@book> select * from x$dual ;
ADDR INDX INST_ID D ---------------- ---------- ---------- - 000000000A6225A0 0 1 XSYS@book> select * from dual ;
ADDR INDX INST_ID D ---------------- ---------- ---------- - 000000000A6225A0 0 1 X--//依旧访问的是X$表.
SYS@book> alter database open ;
Database altered.SYS@book> select owner,name,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name in ('DUAL','X$DUAL');
OWNER NAME NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS FULL_HASH_VALUE HASH_VALUE ------ -------------------- -------------------- ---------- ------------ ---------- ---------- -------------------------------- ---------- SYS X$DUAL TABLE/PROCEDURE TABLE 0 1 0 e8977ba36b4cd7fe1a894be6e9beed3f 3921603903 PUBLIC DUAL TABLE/PROCEDURE SYNONYM 4728 1 0 d0f2742ddad46b95c8c42d4ecfc2fe2e 3485662766 MDSYS DUAL TABLE/PROCEDURE CURSOR 0 1 0 805c59b046ca614f8e607ca3ab670e9e 2875657886 SYS DUAL TABLE/PROCEDURE TABLE 4688 1 0 4be43e009046201adccd69037e798f93 2121895827--//启动后,可以发现oracle加载了真实的"dual"表.
SYS@book> @ &r/sharepool/shp4 aa 2121895827
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2 new 18: WHERE kglobt03 = 'aa' or kglhdpar='aa' or kglhdadr='aa' or KGLNAHSH= 2121895827 TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ----- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 父游标句柄地址 000000007DBD2100 000000007DBD2100 DUAL 0 000000007DBD1C18 00 4688 0 0 4688 4688 2121895827 0 --//中文显示正常了.SYS@book> select * from dual ;
D - X --//现在访问的是真实的sys.dual表了.SYS@book> select * from x$dual ;
ADDR INDX INST_ID D ---------------- ---------- ---------- - 000000000A6225A0 0 1 X--//我的疑问是在nomount,mount阶段,oracle访问dual,如何切到访问x$dual呢?先把这个问题放一放.
--//正是在mount,nomount阶段访问dual,x$kglob无法加载dual对象,这样启动可以正常.如果在mount阶段,重启到mount:
SYS@book> shutdown immediate ; Database closed. Database dismounted. ORACLE instance shut down. SYS@book> startup mount ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes Database mounted. SYS@book> select owner,name,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name in ('DUAL','X$DUAL'); OWNER NAME NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS FULL_HASH_VALUE HASH_VALUE ------ -------------------- -------------------- ---------- ------------ ---------- ---------- -------------------------------- ---------- SYS X$DUAL TABLE/PROCEDURE TABLE 0 0 0 e8977ba36b4cd7fe1a894be6e9beed3f 3921603903SYS@book> desc dual
ERROR: ORA-04043: object dual does not existSYS@book> select owner,name,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name in ('DUAL','X$DUAL');
OWNER NAME NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS FULL_HASH_VALUE HASH_VALUE ------ -------------------- -------------------- ---------- ------------ ---------- ---------- -------------------------------- ---------- SYS X$DUAL TABLE/PROCEDURE TABLE 0 0 0 e8977ba36b4cd7fe1a894be6e9beed3f 3921603903 PUBLIC DUAL TABLE/PROCEDURE CURSOR 0 1 0 d0f2742ddad46b95c8c42d4ecfc2fe2e 3485662766 SYS DUAL TABLE/PROCEDURE CURSOR 0 1 0 4be43e009046201adccd69037e798f93 2121895827 --//可以发现sys.dual的type=CURSOR.不是table.PUBLIC.DUAL的type=CURSOR,也不是SYNONYM.SHARABLE_MEM=0SYS@book> @ &r/sharepool/shp4 aa 2121895827
old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2 new 18: WHERE kglobt03 = 'aa' or kglhdpar='aa' or kglhdadr='aa' or KGLNAHSH= 2121895827 TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 -------------- ---------------- ---------------- ----- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- 父游标句柄地址 000000007E1733D8 000000007E1733D8 DUAL 0 00 00 0 0 0 0 0 2121895827 0--//这样在open阶段就报错了.而如果你alter system flush shared_pool;就可以正常启动.
SYS@book> alter database open ; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00942: table or view does not exist Process ID: 60170 Session ID: 274 Serial number: 3--//还是有一个疑问,在nomount,mount阶段,访问dual实际上执行x$dual,oracle是如何实现的呢? 代码写死的吗?不知道.
--//如果你在mount阶段,访问一些无法访问的视图,这样在open阶段,再次访问就出现问题,解决也很简单,刷新共享池就ok了. --//而实际上在mount阶段,select * from dba_*不会加载到x$kglob. SYS@book> startup mount ; ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes Database mounted.SYS@book> select * from dba_tables;
select * from dba_tables * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views onlySYS@book> select owner,name,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name in ('DUAL','X$DUAL','DBA_TABLES');
OWNER NAME NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS FULL_HASH_VALUE HASH_VALUE ------ -------------------- -------------------- ---------- ------------ ---------- ---------- -------------------------------- ---------- SYS X$DUAL TABLE/PROCEDURE TABLE 4704 1 0 e8977ba36b4cd7fe1a894be6e9beed3f 3921603903SYS@book> desc DBA_TABLES
ERROR: ORA-04043: object DBA_TABLES does not existSYS@book> select owner,name,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name in ('DUAL','X$DUAL','DBA_TABLES');
OWNER NAME NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS FULL_HASH_VALUE HASH_VALUE ------ -------------------- -------------------- ---------- ------------ ---------- ---------- -------------------------------- ---------- SYS X$DUAL TABLE/PROCEDURE TABLE 4704 1 0 e8977ba36b4cd7fe1a894be6e9beed3f 3921603903 PUBLIC DBA_TABLES TABLE/PROCEDURE CURSOR 0 1 0 957afc3fea7b62b75295174b933b3097 2470129815 SYS DBA_TABLES TABLE/PROCEDURE CURSOR 0 1 0 49cee0228e06f7df43c425fab739cb70 3074018160--//只有desc DBA_TABLES才会加载到x$kglob中,而这些对象是错误的实际上.这样open后并会报错.
--//实际上这里还暴露一些软件设计上的一个错误,你可以发现在nomount,mount阶段,访问dual是正常的.这样一些大量的软件通过 --//select 1 from dual;是低端错误的.这样并不代表oracle 数据库正常打开.转载地址：http://zsuml.baihongyu.com/