Overview of Database Upgrade Assistant

In case you are planning to upgrade an existing database, Oracle provides a tool called Database Upgrade Assistant (DBUA). It interactively steps you through the upgrade process and configures the database for the new Oracle Database 10g release.

It can be invoked by the dbua command from the UNIX/Linux prompts or from Database Migration Assistant under Configuration and Migration Tools in a Windows environment. The DBUA shows a list of databases and prompts you to choose. After you choose a database for upgrade, it gathers database details (see Figure 2.6). DBUA will also interactively verify whether the database is backed up and start the upgrade process. Please see Oracle Database Upgrade Guide for more details.

Figure 2.6. Choosing a database for upgrade with DBUA.

Because upgrading a single standalone database to 10g is much simpler than upgrading an environment where there is a primary database and one or more standby databases, we will focus our discussion on standby databases. Most organizations run Oracle 9i Data Guard environment for standby environments (logical or physical). If there are multiple standby databases in your (Data Guard) environment, you have to do the upgrade process discussed next for each standby database.

Upgrade Path for Standby Environments

The preliminary things to be done before any upgrade are checking for nologging operations on standby databases and the recovery of tablespaces or datafiles brought offline.

The common steps in any database-upgrade process for standby configuration are as follows:

1.	Install the new Oracle database release on production (primary) site(s), apply redo logs to standby databases, and complete the upgrade on primary database as per instructions.
2.	After upgrading the primary, switch log files to archive any remaining redo logs.
3.	Copy archive logs on the upgraded primary site to the archive destination on the standby host.
4.	Shut down the standby database and related processes (listener and so on).
5.	Start and mount the standby database.
6.	Keep the standby database in managed recovery mode. Apply the archive logs created during the upgrade process.
7.	Ensure that the standby database has recovered up to the last (copied) log from primary. Update archive log gaps between the primary and the standby.
8.	Restart remote archiving on the primary database.
9.	Place the standby database back into the recovery state.

The following sections look at more details of upgrading a physical standby and a logical standby configuration.

Database Upgrade Assistant in Silent Mode

Database Upgrade Assistant (DBUA) can be invoked in silent mode similar to in silent installation. For this, use the silent option along with dbua command from the command line. In silent mode, you will not see any user interface; check for the upgrade status (messages, alerts, errors, and so on) from the log file. See the Oracle Database Upgrade Guide for more details.

Using DBUA with Oracle 9i Physical Standby Configuration

A Word on the Oracle Companion CD and Patches

For any Oracle Database 10g installation during upgrade process, install the software into a new ORACLE_HOME using Oracle Universal Installer as explained earlier in this chapter. Download the companion CD, extract it, and do a similar installation using OUI. Do not forget to apply any available patches for the version of the Oracle software and platform you are working with.

Now that you have learned about the Database Upgrade Assistant, we will briefly discuss the upgrade path from an Oracle 9i database in a Data Guard configuration. For more details and SQL commands involved in each step, refer to Oracle Data Guard Broker 10g Guide, Oracle Database Upgrade Guide10g, and MetaLink Note #278521.1.

1.	Log in to the primary and standby databases in separate windows as oracle or as the owner of the Oracle software directory. Set the environment to the existing 9.2.0 installation.
2.	On the primary database, stop all user activity. If you are using RAC, shut down (normal or immediate) every instance except the primary database instance.
3.	On the active database instance, archive the current log file: SQL> alter system archive log current;
4.	On the active primary database instance, note the current log thread number and sequence number: SQL> select thread#, sequence# from v$log where status='CURRENT';
5.	Archive the current log: SQL> alter system archive log current;
6.	Shut down the active primary database instance cleanly (normal or immediate). Stop the Oracle processes running against the ORACLE_HOME.
7.	Place the standby database instance in managed recovery mode. If you are using RAC on standby, shut down all other instances cleanly (normal or immediate) except this standby database instance.
8.	Verify that each log file archived in the primary has been applied by querying the v$log_history: SQL> select max(sequence#) from v$log_history;
9.	After all logs have been applied to this standby database, stop managed recovery, shut down the standby database, and stop all agents/listeners against the 9i installation: SQL> alter database recover managed standby database cancel; SQL> shutdown immediate;
10.	On the standby host, install 10.1.0.2 into a new ORACLE_HOME using OUI as explained earlier in this chapter. Don't forget to install the Oracle companion CD. Copy the initialization parameter file (spfile), password file, and networking files from the 9i ORACLE_HOME/dbs directory into the new 10g ORACLE_HOME/dbs directory. Make sure that this standby database has remote_login_exclusive = shared (or exclusive), that a password file exists, and that the SYS password is the same as the SYS password on the primary.
11.	On the primary host, install 10.1.0.2 into its own ORACLE_HOME using OUI and install the (platform-specific) Oracle companion CD. Add the Oracle Net Service name in the tnsnames.ora file that resides in the 10g ORACLE_HOME. After 10g is installed, with the environment still set to the 9.2 installation, start up the primary database: SQL> startup migrate;
12.	Keep the archive log mode set to true during the upgrade process to validate the standby after upgrade. Then set DisableArchiveLogMode="false" in $ORACLE_HOME/rdbms/admin/utlu101x.sql.
13.	From the 10.1.0.2 ORACLE_HOME, start the Database Upgrade Assistant and upgrade the primary database. Ignore the errors in the alert log noting that the primary database is unable to connect to the standby.
14.	When the upgrade process has started on the primary database, start the standby listener on the standby database (10g).
15.	Set the ORACLE_HOME to 10g environment and bring the standby to the nomount state. Check to verify that STANDBY_FILE_MANAGEMENT, FAL_SERVER, and FAL_CLIENT are set properly as in the upgrade guide: SQL> startup nomount; SQL> alter system set standby_file_management=auto scope=both; SQL> alter system set fal_server=<OracleNet_ServiceName_of_Standby database> scope=both; SQL> alter system set fal_client=<OracleNet_ServiceName_of_Primary database>_scope=both;
16.	Mount the standby database and start managed recovery: SQL> alter database mount standby database; SQL> recover managed standby database disconnect;
17.	When the Database Upgrade Assistant has completed on the primary, set the ORACLE_HOME to the new 10g home and connect to the primary database. Identify and record the current log thread and sequence number. Then, archive the current log: SQL> select thread#, sequence# from v$log where status='CURRENT'; SQL> alter system archive log current;
18.	On the standby database instance, verify that each log file archived in the primary has applied by querying the v$log_history: SQL> select max(sequence#) from v$log_history;
19.	Start using the primary database on Oracle Database 10g.

Using DBUA with Oracle 9i Logical Standby Configuration

This section discusses the upgrade path from Oracle 9i database in a logical standby Data Guard configuration. For more details and SQL commands involved, refer to Oracle Data Guard Broker 10g Guide, Oracle Database Upgrade Guide10g, and MetaLink Note #278108.1. Please note that only important SQL commands are given here; we assume that you are familiar with common database-administration tasks.

1.	Log in to the primary and standby databases on separate windows as oracle or as the owner of the Oracle software directory. Set the environment to the existing 9.2.0 installation.
2.	On the primary database, stop all user activity. If you are using RAC, shut down (normal or immediate) every instance except the primary database instance.
3.	On the active database instance, archive the current log file: SQL> alter system archive log current;
4.	On the active primary database instance, note the current log thread number and sequence number: SQL> select thread#, sequence# from v$log where status='CURRENT';
5.	Archive the current log: SQL> alter system archive log current;
6.	If you are using RAC, shut down all other instances cleanly (normal or immediate) except this standby database instance.
7.	Verify that each log file archived in the primary has applied by querying dba_logstdby_log (get x and y from the primary database): SQL> select file_name from dba_logstdby_log where thread#=x and sequence#=y;
8.	Stop the SQL apply operations on this standby database: SQL> alter database stop logical standby apply;
9.	Shut down the standby database and stop all agents and listeners against the 9i installation.
10.	Shut down the active primary database instance cleanly (normal or immediate). Stop the Oracle processes running against the ORACLE_HOME.
11.	On the primary host, install 10.1.0.2 into a new ORACLE_HOME using OUI as explained earlier in this chapter. Don't forget to install Oracle companion CD and patches, if any.
12.	After 10g is installed, with the environment still set to the 9.2 installation, start up the primary database and disable remote archiving: SQL> startup migrate; SQL> alter system set log_archive_dest_state_2=defer scope=both;
13.	From the 10.1.0.2 ORACLE_HOME, start the Database Upgrade Assistant and upgrade the primary database.
14.	When the upgrade process has been completed on the primary database, point your environment to the new 10g installation, shut down the primary database instance, and restart the agent and listener.
15.	Start the primary database instance and enable restricted session. Open the primary database and build the logminer dictionary: SQL> alter system enable restricted session; SQL> alter database open; SQL> execute dbms_logstdby.build;
16.	Disable the restricted session and archive the current log file: SQL> alter system disable restricted session; SQL> alter system archive log current;
17.	Execute the following scripts to get the name of the latest dictionary build log file (file_name.arc). Note the name of the log file for later reference. [View full width] SQL> select name from v$archived_log where (sequence#=(select max(sequence#) from v$archived_log where dictionary_begin = 'YES' and standby_dest= 'NO'));
18.	On the standby host, install 10.1.0.2 into a new ORACLE_HOME home. With the environment still set to Oracle 9i, start up the logical standby database, activate it, and disable remote archiving: SQL> startup migrate; SQL> alter database activate logical standby database; SQL> alter system set log_archive_dest_state_2=defer scope=both;
19.	From the 10g ORACLE_HOME, start the Database Upgrade Assistant and upgrade the logical standby database. After upgrading the logical standby database, shut down the instance and restart the agent and listener.
20.	Copy the latest dictionary build log file from the primary system to the standby system. Start the logical standby database instance. Turn on the database guard to prevent all users from updating any objects: SQL> startup mount; SQL> alter database guard all; SQL> alter database open;
21.	Register the copied log file on the logical standby: SQL> alter database register logical logfile 'file_name.arc';
22.	Begin SQL apply operations on the standby database. On a RAC setup, you can start the remaining standby database instances: SQL> alter database start logical standby apply initial;
23.	Begin remote archiving on the primary database. On a RAC setup, you can start the remaining primary database instances: SQL> alter system set log_archive_dest_state_2=enable;
24.	Start using the primary database on Oracle Database 10g.