The Purpose of running CATPROC.SQL script

Another critical script that you need to run is CATPROC.SQL. You will find it in the same directory in which you found CATALOG. CATPROC.SQL's purpose is centered around PL/SQL. It creates a number of PL/SQL-related data dictionary views, and an innumerable (well almost) number of built-in PL/SQL packages. Some of the highlights of what CATPROC.SQL creates are:

1. The DBMS_STANDARD PL/SQL package
2. PL/SQL related views such as DBA_SOURCE, USER_TRIGGERS, and ALL_TRIGGER_COLS
3. A myriad of other built-in PL/SQL packages such as DBMS_SESSION, DBMS_UTILITY, and DBMS_SYSTEM
4. Public synonyms for the above views and packages

How to run the script If you look in the CATPROC.SQL file, you will see that it mostly just calls a bunch of other script files. Some of those, DBMSUTIL.SQL for example, call other files in turn. You run CATPROC.SQL by using SQL*Plus.

In Oracle 19c, the connect internal command has been deprecated and replaced by the use of `sqlplus / as sysdba` for connecting as a privileged user.
Here's how you can initiate the equivalent process in SQL*Plus for Oracle 19c:
Steps to Run the Script:

1. Log in to SQL*Plus as SYSDBA Use the sqlplus command to connect as the SYSDBA user:
   

   sqlplus / as sysdba
   

   This is equivalent to the old connect internal command.
2. Run the Script (@catalog) After connecting, run the @catalog script by specifying its full path if not in the current directory. For example:
   

   @?/rdbms/admin/catalog.sql
   

   Here:
   • ? is an Oracle substitution variable that refers to the value of the ORACLE_HOME environment variable.
   • The script catalog.sql is typically located in the $ORACLE_HOME/rdbms/admin directory.

Notes:

1. Purpose of the catalog.sql Script: The catalog.sql script creates the Oracle data dictionary views and other necessary database components. This is typically done during database creation.
2. Ensure SYSDBA Privileges: Only users with SYSDBA privileges can execute this script. Attempting to run it without appropriate privileges will result in an error.
3. Verify the Database State: Before running catalog.sql, ensure the database is in an open state:
   

   SELECT status FROM v$instance;
   

   
   If it's not open, start it:
   

   STARTUP;
   

4. Compatibility with Oracle 19c: The process of running scripts like catalog.sql remains largely unchanged across versions but always ensure you're using the correct script paths specific to Oracle 19c.

Complete Example Workflow

$ sqlplus / as sysdba
SQL> STARTUP;
SQL> @?/rdbms/admin/catalog.sql

This will successfully initialize the catalog script in Oracle 19c.

Here's a systematic approach to handling the execution of CATPROC.SQL in an Oracle database environment:

Steps to Execute CATPROC.SQL:

1. Backup Your Database:

   Before running any significant operations like CATPROC.SQL, it's crucial to ensure you have a recent backup. This allows you to recover if something goes wrong.

   RMAN> BACKUP DATABASE;

2. Ensure Sufficient Resources:

   Since you mentioned having systems with ample memory and fast SCSI disks, ensure these resources are optimally configured for the operation. Check resource allocation if your database server is shared.

3. Check Database Compatibility:

   Verify that the version of CATPROC.SQL matches your Oracle database version. Running an incompatible script might lead to errors or incomplete procedures.

4. Plan for Downtime:

   Given CATPROC.SQL can take over an hour to run, plan this maintenance during a time of low system load or scheduled maintenance windows to minimize impact on users or applications.

5. Execute CATPROC.SQL:

   Connect to your database as a user with appropriate permissions (usually SYSDBA) and run the script:

   SQL> CONNECT / AS SYSDBA
   SQL> @?/rdbms/admin/catproc.sql

6. Monitor Execution:

   Keep an eye on the process. You can use SQL*Plus or another SQL client to monitor progress or errors:

   SQL> SELECT * FROM V$SESSION WHERE PROGRAM LIKE '%catproc%';

   Check alert logs for any issues:

   tail -f $ORACLE_BASE/diag/rdbms/<your_db_name>/<your_db_instance>/trace/alert_<your_db_instance>.log

7. Post-Execution Checks:

   After the script completes, check for any errors or warnings in the SQL*Plus output or the alert log.

   Validate that all expected packages, procedures, and functions are now available:

   
   

   SQL> SELECT object_name, status FROM all_objects 
   WHERE object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION') 
   AND status = 'INVALID';
   

   Recompile any invalid objects if necessary:

   
   

   SQL> EXECUTE DBMS_UTILITY.compile_schema(schema => 'SYS', 
   compile_all => TRUE);
   

8. Performance Tuning Post-Run:

   If you notice significant performance degradation after running CATPROC.SQL, consider gathering new statistics or adjusting parameters:

   SQL> EXECUTE DBMS_STATS.gather_schema_stats(ownname => 'SYS');

Remember, CATPROC.SQL updates the database's PL/SQL packages and procedures, which is vital for maintaining database functionality, especially after upgrades or patch installations. Always ensure you have a rollback plan in case something goes awry.