Oracle Tips by Burleson

Installing Remote DBA_UTILITIES Package

The Remote DBA_UTILITIES package is dependent upon a set of grants and a set of tables and views. I suggest  that you create a separate monitoring user with its own tablespace (about 10 megabytes in size) and its own index area of about 5 meg in size (not critical, but nice) to hold the Remote DBA_UTILITIES package definition and support tables. This set of tablespaces will be created if the cre_Remote DBAutil_tbsp.sql script is executed from the SYS user. (Note: It is assumed monitoring will be done using my scripts through a Windows-compliant workstation running SQLPLUS. See section 13.9, “Evaluating the Results of the Ault Status.sql Reports” later in this chapter) The grants required by the Remote DBA_UTILITIES (and other scripts) are contained in the Remote DBAUTIL_GRANTS.SQL script. The tables and views are created via the CREA_Remote DBAUTIL_TAB.SQL script. The general procedure for installing the package is: 

1. Install the files from the Remote DBAUTIL.ZIP file into an SQL_SCRIPTS directory.

2. Verify or install SQLPLUS and NET8 on the workstation where the scripts reside.

3. Create the Remote DBAUTIL_DATA and Remote DBAUTIL_INDEX tablespaces (10 and 5 meg, respectively) using the cre_Remote DBAutil_tbsp.sql procedure.

4. From the SYS user on the HOST machine, verify that the DBMS_SHARED_POOL package (dbmspool.sql and prvtpool.plb in ORACLE_HOME/rdbms/admin), the DBMS_REPAIR (dbmsrpr.sql and prvtrpr.plb in ORACLE)HOME/rdbms/admin), and the CATBLOCK.SQL  script are installed on your database. Create a public synonym on the DBMS_SHARED_POOL and DBMS_REPAIR packages.

5. From the SYS or INTERNAL Oracle user, create the monitoring user (usually called Remote DBAUTIL), give it the CONNECT role, with the Remote DBAUTIL_DATA tablespace as a default tablespace, and unlimited quota on Remote DBAUTIL_DATA and Remote DBAUTIL_INDEX tablespaces. Ensure that a suitable temporary tablespace is also assigned. This is done using the cre_Remote DBAutil_user.sql.

6. From the SYS or INTERNAL user, run the Remote DBAutil_grants.sql script. (Note: If you’re using versions prior to 8i, email me at mikerault@earthlink.net for the proper scripts; I may still have them.)  NOTE: I suggest making a copy of the SQLPLUS icon and changing its “start in” directory, via the PROPERTIES-SHORTCUT menus, to the location of the SQL scripts.

7. Once the Remote DBAutil_grants.sql script has been run against the user created in step 4, log in to the instance as that user and run the crea_Remote DBAutil_tab.sql script to create the required support tables.

8. Run the Remote DBA_utilities9.sql script to create the Remote DBA_UTILITIES package. (This should work for all versions from 7.3.4 on up).

NOTE  

As supplied, the version of Remote DBA_UTILITIES may differ and may not run on the latest version of Oracle; if this is the case, contact me at mikerault@earthlink.net and a proper version will be emailed to you. Once a successful compilation of the Remote DBA_UTILITIES script is completed, you are ready to begin using the package and related scripts to perform monitoring and tuning.

See Code Depot

www.oracle-script.com