奇怪的ORA-04043错误及解决方法

           在查询数据字典表,DBA_DATA_FILES时,遇见一则有趣的ORA-04043错误,错误重现及解决办法见下述:

           1数据库版本,正常情况下,可以查看DBA_DATA_FILES数据字典表:

SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             398462968 bytes
Database Buffers          218103808 bytes
Redo Buffers                7544832 bytes
Database mounted.
Database opened.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> 

           2重现错误:

SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             398462968 bytes
Database Buffers          218103808 bytes
Redo Buffers                7544832 bytes
Database mounted.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


SQL> 

           3解决办法:

SQL> show user;
USER is "SYS"
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


SQL> alter system flush shared_pool;

System altered.

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> 

           或者,先shutdown,然后startup,也可以避免ORA-04043错误

           描述:当数据库处于MOUNT状态时,如果试图去访问DBA_之类的数据字典表时,数据库会报出ORA-04043错误!即使,接下来,ALTER DATABASE OPEN,将数据库置于OPEN状态,再尝试去查看DBA_之类的数据字典表时,数据库依然会报出ORA-04043错误!解决的办法有两个:

  •           ① 重启数据库;
  •           ② 刷出共享池:ALTER SYSTEM FLUSH SHARED_POOL

          补充,经测试,在数据库MOUNT状态尝试去查看SYS用户的普通表的话,也会报出同样的错误,看来不单是数据字典表了,具体见下述:

SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             398462968 bytes
Database Buffers          218103808 bytes
Redo Buffers                7544832 bytes
Database mounted.
SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


SQL> desc test_04043
ERROR:
ORA-04043: object test_04043 does not exist


SQL> desc hr.t;
ERROR:
ORA-04043: object hr.t does not exist


SQL> alter database open;

Database altered.

SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


SQL> desc test_04043
ERROR:
ORA-04043: object test_04043 does not exist


SQL> desc hr.t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> alter system flush shared_pool;

System altered.

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> desc test_04043
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(30)

SQL> desc hr.t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> select * from test_04043;

        ID NAME
---------- ------------------------------
         1 The F**king ORA-04043 Error.
         2 WWW.OracleOnLinux.CN

SQL> 

                  从上,可以看出,在数据库MOUNT状态下,试图去查看数据字典表和SYS用户下的普通表,会报ORA-04043错误,将数据库OPEN之后,再去查看这些对象时,依然会报错,而普通用户的对象却不会出现这个错误!

                  究其原因,这是个Oracle的bug,Bug 2365821,可见Metalink上的ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1],受影响的数据库版本:Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 11.2.0.1 – Release: 8.1.7 to 11.2,This problem can occur on any platform.

How to resolve ksvcreate: Process(m000) creation failed

在一套10.2.0.1.0的一主一备dataguard测试环境中,physical standby database的告警日志文件里,经常报出下述错误信息:

Thu Dec  1 08:47:59 2011
alter database open
Thu Dec  1 08:48:00 2011
SMON: enabling cache recovery
Thu Dec  1 08:48:06 2011
Physical standby database opened for read only access.
Completed: alter database open
Thu Dec  1 08:48:11 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:48:38 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:49:38 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:50:39 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:51:39 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:52:39 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:53:39 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:54:39 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:55:39 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:56:39 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:57:39 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:58:40 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 08:59:40 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 09:00:40 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 09:01:41 2011
ksvcreate: Process(m000) creation failed
Thu Dec  1 09:02:41 2011
ksvcreate: Process(m000) creation failed

后经查找MetaLink:
‘Ksvcreate: Process(m000) creation failed’ after Standby Database Open Read Only Multiple Times [ID 418553.1]

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.1 – Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 15-APR-2011***
Symptoms

Switching a Physical Standby Database multiple to READ ONLY Mode will report the following Errors in the ALERT.LOG:

ksvcreate: Process(m000) creation failed

Changes

Switch Physical Standby from READ ONLY to apply and back to READ ONLY.
Cause

The Cause of this Problem has been identified in Bug 5583049.
Solution

There are two Workarounds available:
Restart the Instance..
or
Disable ADDM – Should be re-enabled if Standby takes up the Primary Role
* Set SGA_TARGET=0 and set shared_pool_size, db_cache_size, etc if using
Automatic SGA Memory Management (ASMM)

* Set STATISTICS_LEVEL=BASIC to disable statistics gathering
References

BUG:5583049 – ‘KSVCREATE: PROCESS(M000) CREATION FAILED’ AFTER STANDBY OPEN RO MULTIPLE TIMES

原来是oracle在10g上的bug,原因是physical standby database在没有完全关闭的情况下多次以read only方式打开。照着Oracle给出的解决方案1,重启Physical standby实例,暂且规避了该错误信息