Section 12.3. Loading the Repository with Data

12.3 Loading the Repository with Data

It won't be long until we see the fruits of our labors. Once you install the repository and build the tables, you'll be all set to track database changes. The next time someone changes a column or an index outside of your established change control procedures, you'll know what the database looked like before inside the original Oracle data dictionary.

In this section we'll demonstrate how to load baseline data dictionary data into the PDBA repository using the baseline.pl script, and we'll run some tests on that data. As changes are made to the database objects and new baseline data is collected from the data dictionary, we'll show the effects of running several reports comparing current objects with the previous incarnations of those objects. We'll pay special attention to the repository reporting script spdrvr.pl (a unique hybrid of Perl and Oracle's SQL*Plus).

12.3.1 Collecting Baseline Data

To kick things off, we need to collect our first baseline set of data. We'll collect it from the data dictionary in database ts99 on server watson using the baseline.pl script, and we'll place it in our repository. The command-line options for baseline.pl are listed in Table 12-3.

Table 12-3. Command-line options ” baseline.pl

Option	Description
-machine	Target database server
-database	Target database
-username	DBA account user name
-password	DBA's password (optional if password server in use)
-rep_machine	Repository database server
-rep_database	Repository database
-rep_username	Repository owner
-rep_password	Repository owner's password (optional if Password server in use)

Invoke the baseline.pl script as follows :

 $ baseline.pl -machine watson -database ts99 -username system \   -rep_machine sherlock -rep_database ts01 

Notice the absence of passwords in this example. Here we're making use of the password server to fill in the blanks for us. Otherwise, we would have needed the -password and -rep_password options, with the appropriate passwords. When you run baseline.pl , the output should be similar to that shown in Example 12-4.

We strongly encourage you to make use of the password server for use with the repository. While this server is optional for collecting data dictionary information for insertion into the repository, it will be required for scripts that parse and store SQL from the V$SQLTEXT system view.

Example 12-4. Output from baseline.pl

 %oramon>  baseline.pl -machine sherlock -database ts01 -username system \  . Working on Baseline for Table: PDBA_ROLE_PRIVS .. Working on Baseline for Table: PDBA_ROLES . Working on Baseline for Table: PDBA_PARAMETERS ... Working on Baseline for Table: PDBA_TAB_COLUMNS .............................................................................. ................  Working on Baseline for Table: PDBA_TABLESPACES . Working on Baseline for Table: PDBA_SYS_PRIVS ..... Working on Baseline for Table: PDBA_TAB_PRIVS ....................................................................... Working on Baseline for Table: PDBA_USERS. Working on Baseline for Table: PDBA_TABLES ...... Working on Baseline for Table: PDBA_INDEXES ................. Working on Baseline for Table: PDBA_SEQUENCES. Working on Baseline for Table: PDBA_IND_COLUMNS ........................... 

12.3.2 Viewing Repository Data

We can now view some of our collected data via the spdrvr.pl ^[1] script. Enter the following command to produce a report showing the table information collected from the data dictionary:

^[1] The name spdrvr.pl is shorthand for SQL*Plus Driver.

 $ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \      -rep_report table_rpt  -rep_instance ts99%  -rep_shema scott 

Notice the use of the SQL wildcard % for the -rep_instance argument. The argument in this case refers to the database's global name, so we used the wildcard character instead of a full global name. Alternatively, if the database global name had been TS99.OREILLY.COM , we could have specified this command instead:

 $ spdrvr.pl -machine sherlock -database ts01 -username pdbarep \      -rep_report table_rpt  -rep_instance ts99.oreilly.com  -rep_shema scott 

The spdrvr.pl script will take care of converting the name to the correct case. The output should be similar to that shown in Example 12-5.

Example 12-5. Output from the initial table report

 %oramon> spdrvr.pl  -machine sherlock -database ts01 -username pdbarep \ RPT: start pk    1000000    PDBAREP Table report for                                Page:          1 TS99.JKS.COM                                                                   NUMBER OWNER           TABLE NAME   SNAPSHOT DATE              BLOCKS OF ROWS --------------- ------------ -------------------- ------------ ----------- SCOTT           BONUS        2001/10/05 18:29:05                    DEPT         2001/10/05 18:29:05                    DUMMY        2001/10/05 18:29:05                    EMP          2001/10/05 18:29:05                    SALGRADE     2001/10/05 18:29:05    5 rows selected.