Recently in Administration Category

During the upgrading of an Oracle database from 10g to 10.2.0.4 under Solaris I ran into a number of problems. So this is a log of what went wrong and how to get around the problems. This log refers to a single instance non-Rac database environment. All tasks should be performed under the oracle user except where otherwise stated.

1). You first need to download and unzip the p6810189_10204_Solaris-64.zip to /tmp for example.

2). Shutdown the database instance and check that all oracle processes have terminated.

3). Now start the runInstaller in the unzipped tmp directory in Disk1 ie. /tmp/oracle/Disk1

4). When prompted run the root.sh script as root.

5). Startup the database with sqlplus as sys in upgrade mode "startup upgrade".

6). Execute the upgrade assistant "dbua" and follow the instructions.

Possible problems are:

1). You can get an ORA-01507 during the dbua execution. Under solaris that resulted from a discrepancy between the ORACLE_HOME and the oratab entry. Correcting that was tricky for two reasons. Firstly you need root rights to change the oratab and secondly I found two versions of oratab in /etc and /var/opt/oracle.

2). Because the ORACLE_HOME was not setup correctly I needed to alter the root.sh script. Also the rdbms/install/rootadd.sh and the oraInst.loc in ORACLE_HOME had an incorrect path. Although the paths actually mapped to the same physical directories they used alternate linked paths and where as such not identical this caused the dbua to throw an ORA-01507 saying that the "'Dbua thinks this is a Rerun operation" and the database is not mounted - which was actually all rubbish.

When using Oracle 10g features around the flashback technology it's important to know the differences between the individual options and their respective restrictions. The Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query are all based on undo (or rollback) data option, which primarily provides read consistency and rolling back transactions in Oracle 10g. However, Flashback Database is based on before images in the flashback logs and Flashback Drop is based on another 10g feature called the Recycle Bin, which is there to manage dropped database objects until their space is required to store new data.

Oracle Metalink has some very useful documents relating to these features:

• Note 249319.1 - Configure flashback database
• Note 270060.1 - Use Flashback Table Feature and Resolve errors
• Note 270535.1 - Restrictions on Flashback Table Feature
• Note 317499.1 - 10G Oracle Flashback Transaction Query - Introduction and usage
• Note 369755.1 - Flashback Logs-Space management
• Note:435998.1 - What Do All 10g Flashback Features Rely on and what are their Limitations
  

> sqlplus /nolog

SQL> connect / as sysdba

SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

that should do the trick although you will need to bounce the instance before it will take effect.

You may also need to re-generate the sys password file like this:

SQL> orapwd file=orapwinst password=pwd123 force=y

If you encounter an error "ORA-39700: database must be opened with UPGRADE option" you probably have a database instance which hasn't yet been converted. That is to say, you have more than one instance running on the same database but the instance you want to start hasn't yet been converted to the correct version. This can happen when you restore an old backup to your database server. The steps needed to complete this are very simple:

1. make sure your ORACLE_SID points to the instance you want to convert

2. start sqlplus / nolog

3. connect / as sysdba

3. use "startup upgrade" to boot the instance in resticted upgrade mode

4. run "catupgrd.sql" stored in "$ORACLE_HOME/rdbms/admin"

5. shutdown the instance "shutdown immediate"

6. restart "startup"

7. finally run "utlrp.sql" to compile any invalid objects

... and that should be it!

Using the Oracle package DBMS_SCHEDULE for running internal and external jobs is a great way of controlling what goes on with your database system. However, when running external jobs such as shell scripts or java it is important to understand a couple of things.

1. Oracle Execution Environment
All external jobs are executed by default with a low privileged user called nobody. Oracle uses the definitions found in "$ORACLE_HOME/rdbms/admin/externaljob.ora". By default this user doesn't even have a PATH variable set.

2. Unix Return Value
The exit code should be zero if all is okay. Anything other than zero causes the Job to be displayed as failed. Oracle captures the error output (STDERR) from the job and saves this along with the execution code from each job. This can be a multiline output, so you can do something like this:

echo "Starting $1 at " `date` >&2
...
echo "Finished execution at " `date` >&2
exit 0

3. Parsing Parameters
You can parse parameters (or arguments) to an external program by specifying the "NUMBER_OF_ARGUMENTS" when the job is created. You must also create the job disabled and then add the arguments and set the job to enabled:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (

JOB_NAME => 'MY_TEST',
JOB_TYPE => 'EXECUTABLE',
JOB_ACTION => '/bin/ple_test.sh',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=minutely; byminute=2,12,22,32,42,52;',
END_DATE => NULL,
ENABLED => FALSE,
NUMBER_OF_ARGUMENTS => 1 );

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('MY_TEST',1,'Hello World');
DBMS_SCHEDULER.ENABLE('MY_TEST');
END;

When a table becomes out of sync with reality and you have to perform some open-heart surgery on your database, you might be happy with a good assistant like DBMS_REDEFINITION at your side. This Oracle package will let you change most aspects of a table while the table is still in use. This is done, by creating a copy of the original table and switching the original and duplicate tables once all changes have been completed. The package utilizes the snapshot (or materialized view) technology within the Oracle database. This is a good summary on how to go about reorganizing tables with the DBMS_REDEFINITION package.

Restrictions: Be aware, that this package will not work on tables which already have snapshots (MViews) defined.

This little except from the 10g Top 20 Features for DBA's which shows very neatly, how you can use the built-in Oracle scheduler to control events outside the database as well.

There are some nice examples of how to setup tasks to run via the Oracle dbms_job package.

This is a very useful little document for your PocketPC, Palm or Handheld. It's a Concise Oracle DBA Guide in english which only takes up about 160K on you mobile device. If you don't already have it you will need to install the viewer application called iSilo but that as well is pretty small. The documentation lists command syntax for database server administartion, backup, recovery, tuning an so on. I'd love to see the old Oracle Quick Reference in this format. If I find one, I'll post a link here in my blog.