Shulou(Shulou.com)06/01 Report--

A service company inspected the production warehouse of the unit and found that the PSU patch was installed for the database software, but the relevant upgrade script was not running in the database, and a relevant written report was submitted to the unit suggesting that the catbundle.sql psu apply script be run in the database.

Really like the service company said, read the report, the original service company is based on the query registry$history table records to check, their own query, there is really no record, and then go back to carefully look at the PSU readme, if you use custome to build the database is not necessary to run catbundle.sql ah, of course, are all on paper, or start to analyze what actions catbundle.sql psu apply has done.

First, take a look at the catbundle.sql script, which says something like this:

Catbundle.sql will look in $ORACLE_HOME/rdbms/admin for an input XML

File named bundledata_ .xml (i.e. Bundledata_CPU.xml)

For information about which patches in the bundle contain which SQL

Files.

That is, according to the contents of the $ORACLE_HOME/rdbms/admin/bundledata_PSU.xml file to determine which SQL files need to be executed in the PSU, some of the contents of the bundledata_PSU.xml file:

? / rdbms/admin/prvtjob.plb

? / rdbms/admin/dbmsaqds.plb

? / rdbms/admin/prvtaqds.plb

? / rdbms/admin/prvtlmd.plb

? / rdbms/admin/prvtlmc.plb

? / rdbms/admin/prvtbpp.plb

? / rdbms/admin/prvtlsby.plb

? / rdbms/admin/dbmssum.sql

? / rdbms/admin/prvtsum.plb

? / rdbms/admin/prvtsms.plb

? / rdbms/admin/prvtdefr.plb

? / rdbms/admin/prvtbstr.plb

/ rdbms/admin/prvtbcap.plb? / rdbms/admin/initqsma.sql should execute different SQL according to different components of the database installation, and continue to analyze the catbundle.sql script. The last line of the file is as follows:

COLUMN script_file NEW_VALUE sf NOPRINT

SELECT: scriptFile AS script_file FROM dual

@ & sf

That is, if script_file is the script to be executed, make a copy of catbundle.sql, and modify the last few lines as follows:

BEGIN

DBMS_OUTPUT.PUT_LINE ('script. File:' | |: scriptFile)

END

Take a look at the name of the script that needs to be executed.

SQL > @ catbundle_test.sql psu applay

Output script. File: / u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catbundle_PSU_OTEST_APPLY.sql

The original executed SQL file is catbundle_PSU_OTEST_APPLY.sql. Check the contents of the file. The following contents are consistent with those in the bundledata_PSU.xml file:

PROMPT Processing Oracle Database Packages and Types...

ALTER SESSION SET current_schema = sys

@? / rdbms/admin/prvtjob.plb

@? / rdbms/admin/dbmsaqds.plb

@? / rdbms/admin/prvtaqds.plb

@? / rdbms/admin/prvtlmd.plb

@? / rdbms/admin/prvtlmc.plb

@? / rdbms/admin/prvtbpp.plb

@? / rdbms/admin/prvtlsby.plb

@? / rdbms/admin/dbmssum.sql

@? / rdbms/admin/prvtsum.plb

@? / rdbms/admin/prvtsms.plb

@? / rdbms/admin/prvtdefr.plb

@? / rdbms/admin/prvtbstr.plb

@? / rdbms/admin/prvtbcap.plb

@? / rdbms/admin/prvtaqiu.plb

PROMPT Processing Oracle Java Supplied Packages...

ALTER SESSION SET current_schema = sys

@? / rdbms/admin/initqsma.sql

@? / rdbms/admin/initcdc.sql

That is, according to the components installed in the database, you need to execute the above SQL script. Here there are only CATPROC and CATJAVA components, and then insert records into the registry$history table after execution:

PROMPT Updating registry...

INSERT INTO registry$history

(action_time, action

Namespace, version, id

Bundle_series, comments)

VALUES

(SYSTIMESTAMP, 'APPLY'

SYS_CONTEXT ('REGISTRY$CTX','NAMESPACE')

'10.2.0.2'

two,

'PSU'

'PSU 10.2.0.4.2')

COMMIT

Here we focus on analyzing whether those plb files are called when building a library using custome, and write a SHELL script for this:

Mygrep ()

{

Fn=$1 # Save the passed file name

Shift

Msg=$* # saves incoming messages

Fnt= `echo $fn | cut-d. -f 1` # because some SQL files are written as @ @ dbmssum mode, some are written as @ @ prvtjob.plb mode

Ff= `grep "@ @ $fn" * .sql | | grep "@ $fnt" * .sql` # that is, with and without an extension, so you may need to truncate it

If ["$ff"! = "]; then # does not take @ @ for calls in catjava.sql, so what you can enter into the following loop is the catproc.sql

Fn1= `echo $ff | awk-print @'{echo $1}'`

If ["$fn1" = "catproc.sql"]; then

If ["$msg" = ""]; then

Echo "$fn found in $fn1"

Else

Echo "$msg$fn found in $fn1"

Fi

Else

Msg= "$msg$fn found in $fn1\ t"

Mygrep $fn1 $msg

Fi

Else

Grep-Q $fn catjava.sql

If ["$?" = "0"]; then

Echo "$fn found in catjava.sql"

Else

Echo "$fn not found"

Fi

Fi

} for f in `cat objectno order by obj#

The script just COMPILE all the eligible views, which is executed during the process of building the library

In fact, as mentioned in readme, there are only the following situations in which run scripts are required:

? Using DBCA (Database Configuration Assistant) to select a sample database (General, Data Warehouse, Transaction Processing)

? Using a script. That was created by DBCA that creates a database from a sample database

? Cloning a database that was created by either of the two preceding methods, and if Section 2.3.3.1, "Loading Modified .SQL Files into the Database" and Section 2.3.3.2, "Recompiling Views in the Database" were not executed after PSU 10.2.0.4.2 was applied

For view_recompile_jan2008cpu.sql, it is:

Upgraded databases require that you perform. The steps in Section 2.3.3.2, "Recompiling Views in the Database" if these steps have not previously been performed; otherwise, no post-installation steps need to be performed.

That is, if you don't have run before upgrading the database, you need to run it again. If you have run before, you don't need it, just once.

Of course, if your library was built before PSU was installed, catbundle.sql psu apply must be run,view_recompile_jan2008cpu.sql and run is not needed.

In addition, those who have a metalink account can take a look at the Introduction To Oracle Database catbundle.sql [ID 605795.1] article, which contains a paragraph:

Starting with Database 11.2.0.2.0, a dummy catbundle.sql is run at database upgrade and creation time, which creates a dba_registry_history entry with bundle series "PSU" and ID = "0". Reports that query on this view for the PSU series returns a row for every upgraded and newly created database.

That is, starting with 11.2.0.2.0, building a new library or upgrading will run a dummy catbundle.sql so that there are records in the registry$history table and will not cause confusion.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.