Section 12.2. Installing the Repository

12.2 Installing the Repository

Before installing the repository, we need to create two tablespaces. We've included templates of the necessary scripts for both Oracle 8.0 and 8.1 (and higher). The only difference between them is that version 8.1 uses Oracle's locally managed tablespaces (LMTs).

LMTs provide several advantages ” in particular, the removal of object extent management from the Oracle data dictionary and the elimination of wasted space due to fragmentation. If you don't need these features or if they seem overkill for your site, however, use the 8.0 script.

The location of the scripts depends on your operating system:

Unix

Unix users will find the scripts by doing a chdir to the directory where the PDBA archive was installed, and then doing a chdir to the pdbarep directory. Ours was installed in /u01/build :

 $ cd /u01/build/PDBA-1.00/pdbarep 

Win32

Win32 users will find the scripts in the c:\perl\site\lib\PDBA\sql directory.

All of the repository scripts run identically on both Win32 and Unix. There is no need for separate versions.

It is very likely that you won't be able to use the repository creation scripts on your own system without first editing them. Your filesystem layout is probably different from ours, so you'll need to edit the datafile paths. For example, let's take the pdba_tbs8i.sql script and modify it for Oracle8 i use on Win32. Example 12-1 shows what the file looks like initially.

Example 12-1. Unix version ” pdba_tbs81.sql

 -- pdba_tbs8i.sql -- create tablespaces for PDBA repository -- as Locally Managed Tablespaces create tablespace pdba_data  datafile  '/u01/oradata/ts01/pdba_data_01.dbf'  size 20m  extent management local uniform size 128k / create tablespace pdba_idx  datafile  '/u01/oradata/ts01/pdba_idx_01.dbf'  size 20m  extent management local uniform size 128k / 

These datafile names won't work on Win32, so we need to change them to something more appropriate, as shown in Example 12-2.

Example 12-2. Win32 version ” pdba_tbs81.sql

 -- pdba_tbs8i.sql -- create tablespaces for PDBA repository -- as Locally Managed Tablespaces create tablespace pdba_data  datafile  'E:\oradata\ts01\pdba_data_01.dbf'  size 20m  extent management local uniform size 128k / create tablespace pdba_idx  datafile  'F:\oradata\ts01\pdba_idx_01.dbf'  size 20m  extent management local uniform size 128k / 

Storing all of the SQL from Oracle's SQL cache can consume a fair amount of disk storage on databases with a large SQL cache. It's good practice to be generous with the amount of space allotted to the PDBA repository if your database caches a large number of SQL statements. We've used up to 100 megabytes of storage storing all the SQL from a database that had approximately 65,000 cached SQL statements in memory, athough this may be an extreme example.

Now we're ready to install the repository:

1. The first step is to actually create the PDBA tablespaces. If your repository is to be installed on Oracle8 i or later, use the pdba_tbs8i.sql script; on Oracle 8.0, choose pdba_tbs.sql . (We'll install our repository with version 8.0.)

2. To create the tablespaces, log in to the database as a DBA user . (Although sys should rarely be used and is unnecessary for creating tablespaces, we do recommend it here; we'll explain why in step 5.)

3. Once logged into the sys account, you can start installing the repository. The following shows our successful tablespace creation on the 8.0.5 database, ts99 . You should see similar results when creating your own tablespaces:

    SQL> set echo on SQL> @pdba_tbs SQL> -- pdba_tbs.sql SQL> -- create tablespaces for PDBA repository SQL> SQL> create tablespace pdba_data          datafile '/u05/oradata/ts99/pdba_data_01.dbf' size 2         default storage ( initial 128k next 128k                            pctincrease 0 maxextents unlimited ) /  Tablespace created  . SQL> create tablespace pdba_idx          datafile '/u06/oradata/ts99/pdba_idx_01.dbf' size 20m         default storage ( initial 128k next 128k                            pctincrease 0 maxextents unlimited ) /  Tablespace created  . 

4. After tablespace creation is complete, it's time to create the PDBAREP repository owner. Run the pdbarep_user.sql script as follows :

    SQL> set echo on SQL>  @pdbarep_user  SQL> create user pdbarep identified by pdbarep         default tablespace pdba_data         temporary tablespace temp / User created.    SQL> alter user pdbarep quota unlimited on pdba_data;    User altered.    SQL> alter user pdbarep quota unlimited on pdba_idx;    User altered. 

   Now run pdbarep_grants.sql to give PDBAREP permission to create objects and gain other vital database permissions:

    SQL> @pdbarep_grants 

5. This is where we have to be the sys user. To function properly, PDBAREP must have SELECT privileges on a pair of data dictionary views that are normally invisible to users. These are V_$PARAMETER and V_$INSTANCE, more commonly known via synonyms as V$PARAMETER and V$INSTANCE. Only sys can grant the necessary permissions.

6. We simply execute the script to grant the proper privileges, and then exit SQL*Plus. (A lot of output will be generated.)

    SQL> connect sys/change_on_install SQL> @pdbarep_grants ... 

7. Now log back into the database as PDBAREP. The password is set to PDBAREP; this password must be changed as soon as the installation completes.

8. To ensure that the direct grants to see the data dictionary views succeeded, try to view them with the DESCRIBE command.

   If you see results similar to those in Example 12-3, you're ready to create the repository tables and indexes.

   Example 12-3. Access to V$PARAMETER and V$INSTANCE

    pdbarep@ts99 SQL> desc v$parameter   NUM                                    NUMBER  NAME                                   VARCHAR2(64)  TYPE                                   NUMBER  VALUE                                  VARCHAR2(512)  ISDEFAULT                              VARCHAR2(9)  ISSES_MODIFIABLE                       VARCHAR2(5)  ISSYS_MODIFIABLE                       VARCHAR2(9)  ISMODIFIED                             VARCHAR2(10)  ISADJUSTED                             VARCHAR2(5)  DESCRIPTION                            VARCHAR2(64)    pdbarep@ts99 SQL> desc v$instance  Name                          Null?    Type  ----------------------------- -------- --------------------  INSTANCE_NUMBER                        NUMBER  INSTANCE_NAME                          VARCHAR2(16)  HOST_NAME                              VARCHAR2(64)  VERSION                                VARCHAR2(17)  STARTUP_TIME                           DATE  STATUS                                 VARCHAR2(7)  PARALLEL                               VARCHAR2(3)  THREAD#                                NUMBER  ARCHIVER                               VARCHAR2(7)  LOG_SWITCH_WAIT                        VARCHAR2(11)  LOGINS                                 VARCHAR2(10)  SHUTDOWN_PENDING                       VARCHAR2(3)    pdbarep@ts99 SQL> 

9. While still logged in as PDBAREP, run the script pdbarep_create.sql . This is the final step in the creation of the PDBA repository.

    SQL> @pdbarep_create 

   There's a lot of output here. As long as there are no errors, the output will consist of a series of lines of text such as Table created , Index created , Sequence created , and Trigger created .

10. If you encounter errors, these will be recorded in the pdbarep_create.log file. Once the script completes, you should examine this file for any errors. Should you need to correct any problems, and rerun the creation script, you may wish to run pdbarep_drop.sql first to drop objects successfully created. This makes it easier to examine pdbarep_create.log later for errors:

     SQL> @pdbarep_drop SQL> @pdbarep_create 

    If you don't drop existing objects before rerunning the creation script, the log file will be cluttered with errors such as ORA-955: name is already used by an existing object . This clutter makes it difficult to find the important errors that we really need to be concerned about.

11. The final step is to copy the configuration file pdbarepq.conf to PDBA_HOME. You may need to make a minor edit to this file, but only if you wish to change the date format shown in the repository reports from YYYY/MM/DD HH24:MI:SS . We'll show you how to change this default shortly.

    On Unix, navigate to the PDBA installation directory, and copy the file:

     $ cd /u01/build/PDBA-1.00 $ cd pdbarep $ cp pdbarepq.conf $PDBA_HOME 

    On Win32, do the equivalent:

     C:> cd c:\perl\site\lib\PDBA\conf C:> copy pdbarepq.conf c:\pdba 

12. If you wish to change the date format that will be used in the repository reports, you'll need to edit pdbarepq.conf and find the following lines near the top of the file:

     # uncomment the appropriate line for your preferred date format #  $calendar = 'International';   # YYYY/MM/DD HH24:MI:SS  #$calendar = 'American';        # MM/DD/YYYY HH24:MI:SS #$calendar = 'European';        # DD/MM/YYYY HH24:MI:SS 

    If you want to change the default date format to either European or American , comment out the International line and uncomment appropriately, as for European here:

     # uncomment the appropriate line for your preferred date format # #$calendar = 'International';   # YYYY/MM/DD HH24:MI:SS #$calendar = 'American';        # MM/DD/YYYY HH24:MI:SS  $calendar = 'European';        # DD/MM/YYYY HH24:MI:SS  

If you're running the DBD::Oracle module on Win32 (as discussed in Chapter 2), you will need to make sure that you have a version installed that was compiled with Oracle libraries of Version 8 or higher. The repository relies on certain features that were introduced in Oracle8, such as the CLOB (character large object) datatype. ActiveState is often several versions behind the latest Unix release of DBD::Oracle . However, if you visit Ilya Sterin's PPD site (also described in Chapter 2), you'll usually find the very latest DBD-Oracle PPDs and binary downloads.