Ora-04023 and database software disk space
The customer reported on March 25 that the LV space utilization rate of Oracle 11.2.0.4 version backup database software located on HPUX host increased rapidly. At first, I didn't realize how big a problem it was. This backup library has a lot of reading business, the customer is worried that this problem will affect the normal business, so I went to the customer site to investigate.
First of all, through the statistics folder size, located to the higher disk space occupation root lies in the database trace directory. Sorting trace files according to time, we found that there are many large files, and the trace files generated in only three days are much more than normal time period, reaching nearly 70G. Many trace files are large, at least 10M, more than 40 M, and the number is also large, up to several thousand, which is enough to show that there should be problems in the database.
After sampling several large trace file headers, I noticed that the module of the session associated with these trace files is an exe (the client is a hospital, and many programs are C/S architecture). Use select sid, serial#, machine, module, terminal from v$session where module ='***.exe' and select s.sid, s.serial#, s.machine, s.module, s.terminal from v$session s, v_process p where s.module ='***.exe' and s.paddr=p.addr to locate the host of the session source and the corresponding server process respectively, and then find the nearest trace according to the process number. The trace file was also found to be swiping messages like ksfbc: entering parse diagnosis mode for xsc:******. At the end of the Trace file is also recorded the SQL that went wrong. ORA-04023: Object could not be validated or authorized. Master library execution can get normal results. It seems that this problem is most likely a bug in the backup library.
Customers log in to the host where the app they just found resides and find a large number of ORA-04023 errors in the app log file, starting as early as March 17. Bug 16713938 : SELECT ON VIEW FAILS WITH ORA-04023 ON ADG FROM VIEW OWNER SCHEMA. This bug does not give a patch to fix, work around is: alter system flush shared_pool, flush shared pool of database instances. This problem may be caused by the library cache in the shared pool of the backup library not being updated to reflect the state change of the main library after the state change of the view on the main library side.
After executing alter system flush shared_pool, there are no more errors in executing SQL. Check the application log again, and no similar errors are seen. The trace file size of the database has also returned to normal.
From this diagnostic process, it can be seen that Oracle's active data guard supports read only, which is not a simple matter. How to refresh the shared pool to ensure that the state of the object is consistent with that of the main database when redo is applied to the backup database is a troublesome problem.
In addition, there are also major problems in customer application operation and maintenance. It turned out that the app was not used by many people now, so even if the app was wrong, no one cared about it without data. It was not until the database finally had a problem that the application error was finally discovered.
