Monish's DBA Blog

Hi All,

After a long time I’m posting my blog. Actually I was very busy in my life. Literally did not get much time. At the same time change the company. New company, new environment!

Anyways before coming to the main topic of the post I want to share the story behind this issue.

One day I got a request to compile the invalid objects on the production database. A very basic activity, so I ran the utlrp.sql to compile them. But wow all remain same. So I ran again, still same. I got amazed. Never faced such issue. Ok so went to look which are Invalid and who are the owner. Found most of them belongs to APPS schema.

So I tried with dbms_utility.compile_schema to compile the Apps schema. And BANG!! You wont believe within 15 min the Invalid object count reached to 20K. Then and there all the users and the client start complaining that the application is not working. Nothing is working. That time I felt like standing in the marriage ceremony NAKED! I aborted the utility, and checked all 20K invalid objects belong to APPS schema. Asked so many seniors, but nobody had any clue, utlrp.sql was useless.

We start looking all possible aspects, and found the PLSQL_CODE_TYPE is set as NATIVE. That’s cool, its me plsql will use the C compiler to compile. Here is he story, there was no C compiler on the server.

It was a huge issue, coz it was weekday and pure business hour, and the business is down because of invalid objects.

We did not had time to install the C compiler because it was a Solaris 64bit server and the compiler comes with the CD from vendor, the customer told he has 30 CDs and don’t know which one have the software.

So we change the   PLSQL_CODE_TYPE parameter to INTERPRETED and tried to compile them manually. We prepared a script and starts compile them. It took really long time but worked.

SQL>select ‘alter package ‘||object_name||’ compile body;’ from user_objects where status=’INVALID’ and object_type=’PACKAGE BODY’ AND ROWNUM<1001;

Anyways the database came up. And the business started.

Now, the main thing, find the ROOT CAUSE. After doing all sorts of research found that long time back someone change the plsql_code_type parameter from interpreted to native without the C compiler and the proper steps. So when we tried to compile the invalid object, it was looking for the C compile but there was none.

NATIVE is faster than INTERPRETED. Though the issue was resolved but there was still performance issue. So we had to make it NATIVE. So, following are the steps.

The Steps: –

1. Ensure that following are properly configured to support native compilation:

* A supported C compiler is installed in the database environment and that the spnc_commands file has the correct command templates for this compiler.
* The PLSQL_NATIVE_LIBRARY_DIR is set. This is a required system-level only parameter that specifies the full path and directory name of the location of the shared libraries that contain natively compiled PL/SQL code. The value must be explicit and point to an existing, accessible directory; the path cannot contain a variable such as ORACLE_HOME. Use the ALTER SYSTEM command or update the initialization file to set the parameter value.
* The PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT is set correctly for the number of natively compiled units after conversion.
* A test PL/SQL unit can be compiled

2. Shut down application services, the listener, and the database.

* Shut down all of the Application services including the Forms Processes, Web Servers, Reports Servers, and Concurrent Manager Servers. After shutting down all of the Application services, ensure that all of the connections to the database have been terminated.
* Shut down the TNS listener of the database to ensure that no new connections are made.
* Shut down the database in normal or immediate mode as the user SYS.

3. Set PLSQL_CODE_TYPE to NATIVE in the initialization parameter file. If the database is using a server parameter file, then set this after the database has started. See “PLSQL_CODE_TYPE Initialization Parameter”.

The value of PLSQL_CODE_TYPE does not affect the conversion of the PL/SQL units in these steps. However, it does affect all subsequently compiled units and it should be explicitly set to the compilation type that you want.

4. Start up the database in upgrade mode, using the UPGRADE option.

5. Execute the following code to list the invalid PL/SQL units. You can save the output of the query for future reference with the SQL SPOOL command.

REM To save the output of the query to a file: SPOOL pre_update_invalid.log
SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE
FROM DBA_OBJECTS o, DBA_PLSQL_OBJECT_SETTINGS s
WHERE o.OBJECT_NAME = s.NAME AND o.STATUS=’INVALID’;
REM To stop spooling the output: SPOOL OFF

If any Oracle supplied units are invalid, try to validate them. For example:

ALTER PACKAGE OLAPSYS.DBMS_AWM COMPILE BODY REUSE SETTINGS;

6. Execute the following query to determine how many objects are compiled NATIVE and INTERPRETED. Use the SQL SPOOL command if you want to save the output.

SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE PLSQL_CODE_TYPE IS NOT NULL
GROUP BY TYPE, PLSQL_CODE_TYPE
ORDER BY TYPE, PLSQL_CODE_TYPE;

Any objects with a NULL plsql_code_type are special internal objects and can be ignored.

7. Run the $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script as the user SYS to update the plsql_code_type setting to NATIVE in the dictionary tables for all PL/SQL units. This process also invalidates the units. Use TRUE with the script to exclude package specifications; FALSE to include the package specifications.
@$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql TRUE

This update must be done when the database is in UPGRADE mode. The script is guaranteed to complete successfully or rollback all the changes.

8. Shut down the database and restart in NORMAL mode.

9. Before you run the utlrp.sql script, Oracle recommends that no other sessions are connected to avoid possible problems. You can ensure this with:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

10. Run the $ORACLE_HOME/rdbms/admin/utlrp.sql script as the user SYS. This script recompiles all the PL/SQL modules using a default degree of parallelism. See the comments in the script for information on setting the degree explicitly.

If for any reason the script is abnormally terminated, rerun the utlrp.sql script to recompile any remaining invalid PL/SQL modules.

11. After the compilation completes successfully, verify that there are no new invalid PL/SQL units using the query in point 5. You can spool the output of the query to the post_upgrade_invalid.log file and compare the contents with the pre_upgrade_invalid.log file, if it was created previously.

12. Re-execute the query in point 6. If recompiling with dbmsupgnv.sql, confirm that all PL/SQL units, except TYPE specifications and package specifications if excluded, are NATIVE. If recompiling with dbmsupgin.sql, confirm that all PL/SQL units are INTERPRETED.

13. Disable the restricted session mode for the database, then start the services that you previously shut down. To disable restricted session mode:

ALTER SYSTEM DISABLE RESTRICTED SESSION;