[20171127]dual.txt
--//我曾经提到如果在nomount,mount阶段,desc dual,启动到open阶段时,数据库会直接崩溃.
--//链接:http://blog.itpub.net/267265/viewspace-1246984/
SYS@test> desc dual
ERROR:
ORA-04043: object dual does not exist
SYS@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 bytes
SYS@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:02
SYS@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 X
SYS@book> select * from x$dual ;
ADDR INDX INST_ID DU
---------------- ---------- ---------- --
000000000A6225A0 0 1 X
SYS@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 selected
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';
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 selected
SYS@book> select * from dual ;
ADDR INDX INST_ID DU
---------------- ---------- ---------- --
000000000A6225A0 0 1 X
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';
OWNER NAME NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS FULL_HASH_VALUE HASH_VALUE
------ -------------------- -------------------- ---------- ------------ ---------- ---------- -------------------------------- ----------
SYS X$DUAL TABLE/PROCEDURE TABLE 4704 1 0 e8977ba36b4cd7fe1a894be6e9beed3f 3921603903
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
-------------- ---------------- ---------------- ------ -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- -------------------------- ----------
??????? 000000007E19FD88 000000007E19FD88 X$DUAL 0 000000007E1AD970 00 4704 0 0 4704 4704 3921603903 0
2.启动到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 3921603903
SYS@book> select * from x$dual ;
ADDR INDX INST_ID D
---------------- ---------- ---------- -
000000000A6225A0 0 1 X
SYS@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 3921603903
SYS@book> desc dual
ERROR:
ORA-04043: object dual does not exist
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 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=0
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
-------------- ---------------- ---------------- ----- -------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 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 only
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','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
SYS@book> desc DBA_TABLES
ERROR:
ORA-04043: object DBA_TABLES does not exist
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','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 数据库正常打开.