12 Using SQL Apply to Upgrade the Oracle DatabaseStarting with Oracle Database 10g release 1 (10.1.0.3), you can use 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. Note: This chapter describes an alternative to the usual upgrade procedure involving longer downtime, as described in Appendix B, "Upgrading Databases in a Data Guard Configuration". Do not attempt to combine steps from the method described in this chapter with steps from Appendix B.The instructions in this chapter describe how to minimize downtime while upgrading an Oracle database. This chapter provides the following topics:
12.1 Benefits of a Rolling Upgrade Using SQL ApplyPerforming a rolling upgrade with SQL Apply provides several advantages:
12.2 Requirements to Perform a Rolling Upgrade Using SQL ApplyThe rolling upgrade procedure requires the following:
12.3 Figures and Conventions Used in the Upgrade InstructionsFigure 12-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 12-1 Data Guard Configuration Before Upgrade ![]() Description of "Figure 12-1 Data Guard Configuration Before Upgrade" 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 12-1. The following sections describe scenarios in which you can use the SQL Apply rolling upgrade procedure: 12.4 Performing a Rolling Upgrade By Creating a New Logical Standby DatabaseThis scenario assumes that you do not have an existing Data Guard configuration, but you are going to create a logical standby database solely for the purpose of performing a rolling upgrade of the Oracle Database. Perform the following steps to prepare the primary and standby databases for upgrading. Step 1 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:
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 2 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. Step 3 Perform a rolling upgrade Now that you have created a logical standby database, you can follow the procedure described in Section 12.5, "Performing a Rolling Upgrade With an Existing Logical Standby Database", which assumes that you have a logical standby running the same Oracle software. 12.5 Performing a Rolling Upgrade With an Existing Logical Standby DatabaseThis section provides a step-by-step procedure for upgrading the logical standby database and the primary database. Table 12-1 lists the steps. Step 1 Prepare for rolling upgrade Follow these steps to prepare to perform a rolling upgrade of Oracle Software:
Step 2 Upgrade the logical standby database Upgrade Oracle database software on the logical standby database (Database B) to release y. While the logical standby database is being upgraded, it will not accept redo data from the primary database. Step 3 Obtain information about unsupported tables Oracle recommends that you use the EXEC DBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED',DBMS_LOGSTDBY.MAX_EVENTS); EXEC DBMS_LOGSTDBY.APPLY_SET('RECORD_UNSUPPORTED_OPERATIONS', 'TRUE'); To upgrade Oracle Database software, refer to the Oracle Database Upgrade Guide for the applicable Oracle Database release. Figure 12-2 shows Database A running release x, and Database B running release y. During the upgrade, redo data accumulates on the primary system. Figure 12-2 Upgrade the Logical Standby Database Release ![]() Description of "Figure 12-2 Upgrade the Logical Standby Database Release" See Also:
Step 4 Restart SQL Apply on the upgraded logical standby database 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 12-3 for an arbitrary period while you verify that the upgraded Oracle Database software release is running properly in the production environment. To monitor how quickly Database B is catching up to Database A, query the 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 5 Monitor events on the upgraded standby database You should frequently query the Example 12-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 not all of the changes that occurred on Database A have 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 reinstantiate Database B and perform the upgrade procedure at another time. 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 statement must wait for existing transactions to complete. To minimize the time it takes to complete the switchover, users still connected to Database A should log off immediately and reconnect to Database B. Note: The usual two-phased prepared switchover described in 7.3.1 cannot be used because it requires both primary and standby databases to be running the same version of the Oracle software and at this point, the primary database is running a lower version of the Oracle software. Instead, the single-phased unprepared switchover procedure documented above is used. The unprepared switchover should only be used in the context of a rolling upgrade using logical standby database.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 7 Import any tables that were modified during the upgrade Step 5 "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 12-1), import the latest version of those tables using an import utility such as Oracle Data Pump. For example, the following import command truncates the IMPDP SYSTEM NETWORK_LINK=DATABASEA TABLES=SCOTT.EMP TABLE_EXIST_ACTION=TRUNCATE Note that this command will prompt you for the Step 8 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:
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 12-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 9. Step 9 Upgrade the old 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 12-5 shows the system after both databases have been upgraded. Step 10 Start SQL Apply on the old primary database 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: You will need to create a database link (if one has not already been set up) and to use theNEW PRIMARY clause, because in Step 4 the single-phased unprepared switchover was used to turn Database A into a standby database.
You will need to connect as 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 11 Optionally, raise the compatibility level on both databases Raise the compatibility level of both databases by setting the Step 12 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 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 13 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 12-1). Note: You will use the two-phased prepared switchover described in 7.3.1 since at this time, both Database A and Database B are running the same version of the Oracle software.12.6 Performing a Rolling Upgrade With an Existing Physical Standby DatabaseThe steps in this section show you how to perform a rolling upgrade of Oracle software and then get back to your original configuration in which A is the primary database and B is the physical standby database, and both of them are running the upgraded Oracle software. Note: The steps in this section assume that you have a primary database (A) and a physical standby database (B) already set up and using Oracle Database release 11.1 or later.Table 12-2 summarizes the steps involved. Table 12-2 Steps to Perform a Rolling Upgrade With an Existing Physical Standby Step 1 Prepare the primary database for a rolling upgrade (perform these steps on Database A)
Step 2 Convert the physical standby database into a logical standby database (perform these steps on Database B)
Note: You should not delete any remote archived logs processed by the logical standby database (Database B). These remote archived logs are required later during the rolling upgrade process. If you are using the recovery area to store the remote archived logs, you must ensure that it has enough space to accommodate these logs without interfering with the normal operation of the logical standby database.Step 3 Upgrade the logical standby database and catch up with the primary database (perform these steps on Database B) You can now follow Steps 1 through 6 as described in Section 12.5, "Performing a Rolling Upgrade With an Existing Logical Standby Database". At the end of these steps, Database B will be your primary database running the upgraded version of the Oracle software, and Database A has become your logical standby database. Move on to the next step to turn Database A into the physical standby for Database B. Step 4 Flashback Database A to the guaranteed restore point (perform these steps on Database A) SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> FLASHBACK DATABASE TO RESTORE POINT pre_upgrade; SQL> SHUTDOWN IMMEDIATE; Step 5 Bring up Database A as a logical standby to Database B using the new version of Oracle software At this point, you should switch the Oracle binary at Database A to use the higher version of the Oracle software. You will not run the upgrade scripts, since Database A will be turned into a physical standby, and will be upgraded automatically as it applies the redo data generated by Database B. Mount Database A, as follows: SQL> STARTUP MOUNT; Step 6 Convert Database A back to a physical standby SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; SQL> SHUTDOWN IMMEDIATE; Step 7 Start managed recovery on Database A Database A will be upgraded automatically as it applies the redo data generated by Database B. Managed recovery will wait until the new incarnation branch from the primary is registered before it starts applying redo. SQL> STARTUP MOUNT; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Step 8 Perform a switchover to make Database A the primary database At this point, Database B is your primary database and Database A is your physical standby, both running the higher version of the Oracle software. To make Database A the primary database, follow the steps described in Section 8.2.1, "Performing a Switchover to a Physical Standby Database". |
|
来自: 浸心阁 > 《dataguard》