Skip Headers
Oracle® Data Guard Concepts and Administration
10g Release 2 (10.2)

Part Number B14239-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

11 Using SQL Apply to Upgrade Oracle Database

Starting with Oracle Database 10g release 1 (10.1.0.3), you can use SQL Apply on a logical standby database to perform a rolling upgrade of Oracle Database 10g software. During a rolling upgrade, you can run different releases of an Oracle database on the primary and logical standby databases while you upgrade them, one at a time, incurring minimal downtime on the primary database.

The instructions in this chapter describe how to minimize downtime while upgrading an Oracle database. This chapter provides the following topics:

11.1 Benefits of a Rolling Upgrade Using SQL Apply

Performing a rolling upgrade with SQL Apply provides several advantages:

11.2 Requirements to Perform a Rolling Upgrade Using SQL Apply

The rolling upgrade procedure requires the following:

11.3 Figures and Conventions Used in the Upgrade Instructions

Figure 11-1 shows a Data Guard configuration before the upgrade begins, with the primary and logical standby databases both running the same Oracle Database software release.

Figure 11-1 Data Guard Configuration Before Upgrade

Description of sbydb023.gif follows
Description of the illustration sbydb023.gif

During the upgrade process, the Data Guard configuration operates with mixed database releases at several points in this process. Data protection is not available across releases. During these steps, consider having a second standby database in the Data Guard configuration to provide data protection.

The steps and figures describing the upgrade procedure refer to the databases as "Database A" and "Database B" rather than as the "primary database" and "standby database." This is because the databases switch roles during the upgrade procedure. Initially, Database A is the primary database and Database B is the logical standby database, as shown in Figure 11-1.

11.4 Prepare to Upgrade

Perform the following steps to prepare the primary and standby databases for upgrading.


Step 1 Set the COMPATIBLE initialization parameter

On Database A, ensure the COMPATIBLE initialization parameter specifies the release number for Oracle Database software running on the primary database prior to the upgrade. For example, if the primary database is running release 10.1, then the COMPATIBLE initialization parameter must be set to 10.1.

Step 2 Obtain information about unsupported tables

On Database A, use DBMS_LOGSTDBY PL/SQL procedure to capture information about transactions running on the primary database that will not be supported by a logical standby database. Run the following procedures to capture and record the information as events in the DBA_LOGSTDBY_EVENTS table:

EXEC DBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED',DBMS_LOGSTDBY.MAX_EVENTS);
EXEC DBMS_LOGSTDBY.APPLY_SET('RECORD_UNSUPPORTED_OPERATIONS', 'TRUE');


Note:

In Oracle Database 10g release 1 (10.1), the DBMS_LOGSTDBY.MAX_EVENTS constant was called DBMS_LOGSTDBY_PUBLIC.MAX_EVENTS. The effect of the two constants is the same, but in release 2 (10.2) the DBMS_LOGSTDBY_PUBLIC package has been eliminated and the definition of the constant moved to the DBMS_LOGSTDBY package.

Even though you run these PL/SQL procedures on Database A, they do not affect the primary database. However, by running these procedures on the primary database before creating the logical standby database (and the logical standby database control file), the settings are automatically transferred when you create the physical standby database in step 4.

If a logical standby database already exists (Database B) that can be used for the upgrade procedure, issue the DBMS_LOGSTDBY PL/SQL commands on both databases, and then skip to step 3.


See Also:

Oracle Database PL/SQL Packages and Types Reference for complete information about the DBMS_LOGSTDBY procedure

Step 3 Identify unsupported data types and storage attributes

To identify unsupported database objects on the primary database and decide how to handle them, follow these steps:

  1. Identify unsupported data types and storage attributes for tables:

    • Review the list of supported data types and storage attributes provided in Appendix C, "Data Type and DDL Support on a Logical Standby Database".

    • Query the DBA_LOGSTDBY_UNSUPPORTED and DBA_LOGSTDBY_SKIP views on the primary database. Changes that are made to the listed tables and schemas on the primary database will not be applied on the logical standby database. The following query shows an example of a list of unsupported tables:

      SQL> SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;
      OWNER      TABLE_NAME
      ---------- -----------------
      OE         CATEGORIES_TAB
      OE         CUSTOMERS
      OE         WAREHOUSES
      PM         ONLINE_MEDIA
      PM         PRINT_MEDIA
      SCOTT      MYCOMPRESS
      SH         MVIEW$_EXCEPTIONS
      7 rows selected.
      
      SQL> 
      SQL> SELECT OWNER FROM DBA_LOGSTDBY_SKIP
        2   WHERE STATEMENT_OPT = 'INTERNAL SCHEMA';
      
      OWNER
      ------------------------------
      CTXSYS
      DBSNMP
      DIP
      ORDPLUGINS
      ORDSYS
      OUTLN
      SI_INFORMTN_SCHEMA
      SYS
      SYSTEM
      WMSYS
      10 rows selected.
      
      
  2. Decide how to handle unsupported tables.

    If unsupported objects are being modified on your primary database, it might be possible to perform the upgrade anyway using any of the following methods:

    • Temporarily suspend changes to the unsupported tables for the period of time it takes to perform the upgrade procedure.

      If you can prevent changes to unsupported changes, then using SQL Apply might still be a viable way to perform the upgrade procedure. This method requires that you prevent users from modifying any unsupported table from the time you create the logical standby control file to the time you complete the upgrade. You can monitor transaction activity in the DBA_LOGSTDBY_EVENTS view and discontinue the upgrade (if necessary) up to the time you perform the first switchover.

    • Perform the upgrade at a time when users will not be making changes to the unsupported tables.

      For logical standby databases that support multiple departments with different requirements, using SQL Apply to perform an upgrade is still possible if you know how users access tables in the database. For example, assume that the Payroll department updates an object table, but that department updates the database only Monday through Friday. However, the Customer Service department requires database access 24 hours a day, 7 days a week, but uses only supported data types and tables. In this scenario, you could perform the upgrade over a weekend.

    If you cannot prevent changes to unsupported tables during the upgrade, any unsupported transactions that occur are recorded in the DBA_LOGSTDBY_EVENTS table on the logical standby database. After the upgrade is completed, you might be able to use Oracle Data Pump or the Export/Import utility to import the changed tables to the upgraded databases.

    The size of the changed tables will determine how long database operations will be unavailable, so you must decide if a table is too large to export and import its data into the standby database. For example, a 4-terabyte table is not a good candidate for the export/import process.


Note:

If you cannot use a logical standby database because the data types in your application are unsupported, then perform the upgrade as documented in Oracle Database Upgrade Guide.

Step 4 Create a logical standby database

To create a logical standby database, follow the instructions in Chapter 4.

Oracle recommends configuring a standby redo log on the logical standby database to minimize downtime.


Note:

If a logical standby database already exists, go to Section 11.5, "Upgrade the Databases" to begin the upgrade procedure.

11.5 Upgrade the Databases

This section provides a step-by-step procedure for upgrading the logical standby database and the primary database. Table 11-1 lists the steps.


Note:

If your business does not require a logical standby database to support the primary database, you can skip steps 7 through 11.


Step 1 Stop SQL Apply and upgrade the logical standby database

To begin the upgrade, stop SQL Apply and upgrade Oracle database software on the logical standby database (Database B) to release y. To stop SQL Apply, issue the following statement on Database B:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

To upgrade Oracle database software, refer to the Oracle Database Upgrade Guide for the applicable Oracle Database release.

Figure 11-2 shows Database A running release x, and Database B running release y. During the upgrade, redo data accumulates on the primary system.

Figure 11-2 Upgrade the Logical Standby Database Release

Description of sbydb026.gif follows
Description of the illustration sbydb026.gif

Step 2 Restart SQL Apply

Restart SQL Apply and operate with release x on Database A and release y on Database B. To start SQL Apply, issue the following statement on Database B:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

The redo data that was accumulating on the primary system is automatically transmitted and applied on the newly upgraded logical standby database. The Data Guard configuration can run the mixed releases shown in Figure 11-3 for an arbitrary period while you verify that the upgraded Oracle Database software release is running properly in the production environment.

Figure 11-3 Running Mixed Releases

Description of sbydb024.gif follows
Description of the illustration sbydb024.gif

To monitor how quickly Database B is catching up to Database A, query the V$LOGSTDBY_PROGRESS view on Database B. For example:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.

SQL> SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;

SYSDATE            APPLIED_TIME
------------------ ------------------
27-JUN-05 17:07:06 27-JUN-05 17:06:50

Step 3 Monitor events on the upgraded standby database

You should frequently query the DBA_LOGSTDBY_EVENTS view to learn if there are any DDL and DML statements that have not been applied on Database B. Example 11-1 demonstrates how monitoring events can alert you to potential differences in the two databases.

Example 11-1 Monitoring Events with DBA_LOGSTDBY_EVENTS

SQL> SET LONG 1000
SQL> SET PAGESIZE 180
SQL> SET LINESIZE 79
SQL> SELECT EVENT_TIMESTAMP, EVENT, STATUS FROM DBA_LOGSTDBY_EVENTS
ORDER BY EVENT_TIMESTAMP;

EVENT_TIMESTAMP
---------------------------------------------------------------------------
EVENT
--------------------------------------------------------------------------------
STATUS
--------------------------------------------------------------------------------
…
24-MAY-05 05.18.29.318912 PM
CREATE TABLE SYSTEM.TST (one number)
ORA-16226: DDL skipped due to lack of support
 
24-MAY-05 05.18.29.379990 PM
"SYSTEM"."TST"
ORA-16129: unsupported dml encountered

In the preceding example:

These types of errors indicate that all of the changes that occurred on Database A have not been applied to Database B. At this point, you must decide whether or not to continue with the upgrade procedure. If you are certain that this difference between the logical standby database and the primary database is acceptable, then continue with the upgrade procedure. If not, discontinue and discard Database B and perform the upgrade procedure at another time.

Step 4 Begin a switchover

When you are satisfied that the upgraded database software is operating properly, perform a switchover to reverse the database roles by issuing the following statement on Database A:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

This may take only a few seconds depending on whether or not the statement must wait for existing transactions to complete. Users still connected to Database A should log off immediately and reconnect to Database B.


Note:

If you suspended activity to unsupported tables or packages on Database A when it was the primary database, you must continue to suspend the same activities on Database B while it is the primary database if you eventually plan to switch back to Database A.

Step 5 Determine if unsupported objects were modified during the upgrade

Step 3 "Monitor events on the upgraded standby database" described how to list unsupported tables that are being modified. If unsupported DML statements were issued on the primary database (as described in Example 11-1), import the latest version of those tables using an import utility such as Oracle Data Pump.


Note:

The tables you import must meet the data type requirements stated in Section 11.4, "Prepare to Upgrade", step 3.

For example, the following import command truncates the scott.emp table and populates it with data matching the former primary database (A):

IMPDP SYSTEM/MANAGER NETWORK_LINK=DATABASEA TABLES=SCOTT.EMP TABLE_EXIST_ACTION=TRUNCATE

Step 6 Complete the switchover and activate user applications

When you are satisfied that the upgraded database software is operating properly, complete the switchover to reverse the database roles:

  1. On Database B, query the SWITCHOVER_STATUS column of the V$DATABASE view, as follows:

    SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
    
    SWITCHOVER_STATUS
    --------------------
    TO PRIMARY
    
    
  2. When the SWITCHOVER_STATUS column displays TO PRIMARY, complete the switchover by issuing the following statement on Database B:

    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL PRIMARY;
    
    
  3. Activate the user applications and services on Database B, which is now running in the primary database role.

After the switchover, you cannot send redo data from the new primary database (B) that is running the new database software release to the new standby database (A) that is running an older software release. This means the following:

Figure 11-4 shows Database B, the former standby database (running release y), is now the primary database, and Database A, the former primary database (running release x), is now the standby database. The users are connected to Database B.

If Database B can adequately serve as the primary database and your business does not require a logical standby database to support the primary database, then you have completed the rolling upgrade process. Allow users to log in to Database B and begin working there, and discard Database A when it is convenient. Otherwise, continue with step 7.

Figure 11-4 After a Switchover

Description of sbydb027.gif follows
Description of the illustration sbydb027.gif

Step 7 Upgrade the former primary database

Database A is still running release x and cannot apply redo data from Database B until you upgrade it and start SQL Apply.

For more information about upgrading Oracle Database software, see the Oracle Database Upgrade Guide for the applicable Oracle Database release.

Figure 11-5 shows the system after both databases have been upgraded.

Figure 11-5 Both Databases Upgraded

Description of sbydb025.gif follows
Description of the illustration sbydb025.gif

Step 8 Start SQL Apply

Issue the following statement to start SQL Apply on Database A and, if necessary, create a database link to Database B:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE NEW PRIMARY db_link_to_b;

Note:

Create a database link prior to issuing this statement only if a database link has not already been set up.

When you start SQL Apply on Database A, the redo data that is accumulating on the primary database (B) is sent to the logical standby database (A). The primary database is protected against data loss once all the redo data is available on the standby database.

Step 9 Optionally, raise the compatibility level on both databases

Raise the compatibility level of both databases by setting the COMPATIBLE initialization parameter. Set the COMPATIBLE parameter on the standby database before you set it on the primary database. See Oracle Database Reference for more information about the COMPATIBLE initialization parameter.

Step 10 Monitor events on the new logical standby database

To ensure that all changes performed on Database B are properly applied to the logical standby database (A), you should frequently query the DBA_LOGSTDBY_EVENTS view, as you did for Database A in step 3. (See Example 11-1.)

If changes were made that invalidate Database A as a copy of your existing primary database, you can discard Database A and create a new logical standby database in its place. See Chapter 4, "Creating a Logical Standby Database" for complete information.

Step 11 Optionally, perform another switchover

Optionally, perform another switchover of the databases so Database A is once again running in the primary database role (as shown in Figure 11-1).

  1. To begin the switchover, issue the following statement on Database B:

    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;
    
    

    This command waits for existing transactions to complete. After existing transactions have completed, users still connected to Database B will get errors when they try to run transactions on Database B. They should log off immediately and reconnect to Database A.

  2. On Database A issue the following query:

    SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
    
    SWITCHOVER_STATUS
    --------------------
    TO PRIMARY
    
    
  3. When the query returns TO PRIMARY, complete the switchover by issuing the following statement on Database A:

    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL PRIMARY;
    
    

    Database A is again running in the primary database role. Users can log in to Database A and begin their work.

  4. Start SQL Apply by issuing the following statement on Database B:

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;