Database Rolling Upgrade using Data Guard SQL Apply

Introduction

This guide provides best practices for leveraging Data Guard SQL Apply to perform a rolling Oracle database upgrade with minimal downtime. Total database downtime is limited to the time it takes to execute a Data Guard switchover, compared to the longer downtime required for a conventional database upgrade.

A rolling database upgrade eliminates lengthy downtime associated with the conventional database upgrade and can also provide an efficient fail back method in case of an emergency during the upgrade. A rolling upgrade also eliminates application downtime due to recompilation (timings depend on the number of invalid objects). The only database downtime required by the entire rolling upgrade process is the time it takes to perform a Data Guard switchover and the time for applications and services to be re-activated which can occur in a matter of a minute or two.

A rolling upgrade can be performed using either SQL Apply or the Transient Logical Standby method. The two rolling upgrade methods are fairly similar where the primary difference is the state of the standby database before and after completing the upgrade. Using the SQL Apply method, the upgrade starts with one node as a logical standby and concludes with one node continuing to be a logical standby while with the transient logical standby, one node will start as a physical standby, temporarily convert to a logical standby, and will revert back to a physical standby after the upgrade.

In this guide, a rolling database migration using Data Guard SQL Apply will be deployed to migrate from Oracle Database 10g Release 2 (10.2.0.5) to Oracle Database 11g Release 2 (11.2.0.3). This same guide can also be used to perform rolling database upgrades using Data Guard SQL Apply. For example, upgrading Oracle Database 11g Release 2 (11.2.0.1) to (11.2.0.3).

Although the example in this guide demonstrates a rolling database migration, the word upgrade will be used interchangeably with migration.

To learn more about important patches and workarounds for Oracle 10g rolling database upgrades using Data Guard SQL Apply (Logical Standby), see [ID 300479.1] on the My Oracle Support web site.

Illustrations and Requirements

Starting with Oracle Database 10g Release 1 (10.1.0.3), you can use Oracle Data Guard SQL Apply on a logical standby database to perform a rolling upgrade. For example, you can upgrade the Oracle Database software from patch set release 10.1.0.n to the next database 10.1.0.(n+1) patch set release, or migrate Oracle Database 10g Release 1 (10.1) to Oracle Database 11g Release 2 (11.2).

This section illustrates the steps and requirements that are used to perform a rolling upgrade (or rolling migration) using SQL Apply. Performing an upgrade using SQL Apply will be covered in the next section.

Data Guard Configuration Before Rolling Upgrade

The example Data Guard configuration used in this guide will consist of a primary database, a logical standby database, and an optional archived redo log repository that will receive redo during the logical standby upgrade phases as shown in Figure 1. The logical standby may already exist, or you may create it to use on a temporary basis for a rolling database upgrade. An optional archived redo log repository will be created on the same machine running the logical standby database.

Creating an archived redo log repository for the rolling upgrade ensures that you can meet the Recovery Point Objectives (RPO) if the primary site fails during the upgrade of the logical standby database. There are two occasions during a SQL Apply rolling upgrade where a logical standby site is being upgraded and not receiving redo from the primary. Without an archived redo log repository, the primary database is unprotected while the logical standby is being upgraded. During these times, it is highly recommended to use an archived redo log repository to protect the primary database. To learn more about creating an archived redo log repository, visit Data Guard Archived Redo Log Repository or [ID 887471.1] on the My Oracle Support web site.


Figure 1: Data Guard Configuration Before Rolling Upgrade

Upgrade the Logical Standby Database

Upgrade the database on the logical standby site to database release Y.

While the logical standby database is being upgraded, it will not accept redo data from the primary database. In order to protect the primary database, redo will continue to be sent to the archived redo log repository running at the logical standby site.

Figure 2 shows the primary database running database release X and the logical standby database running database release Y. During the upgrade, redo data accumulates on the primary system and also sent to the archived redo log repository.

Why is it recommended to create an archived redo log repository?

If the primary site fails while performing the upgrade on the logical standby, all of the archived redo log files that were transferred to the archived redo log repository during that time can be registered with the logical standby database and a failover to the logical standby would need to occur. This significantly reduces data loss when failing over to the logical standby.

See section Recover Production from Primary Site Failure during Upgrade for more information.


Figure 2: Upgrade the Logical Standby Database

Restart SQL Apply and Run in Mixed Mode

Restart SQL Apply and operate with database release X on the primary and database release Y on the logical standby.

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 database releases shown inFigure 3 for an arbitrary period while you verify that the upgraded Oracle Database software release is running properly in the production environment.


Figure 3: Running in Mixed Mode

Perform a Switchover

When you are satisfied that the upgraded database software is operating properly, perform a switchover to reverse the database roles and activate the user applications and services on the database now running in the primary database role.

Immediately after the switchover, disable the archived redo log repository on the new primary site, create a new archived redo log repository on the former primary site using the upgraded database release Y software, update thetnsnames.ora file to reflect the change, and configure it to receive redo data from the new primary database as shown in Figure 4.


Figure 4: After First Switchover

Upgrade the Former Primary Database

The former primary database is still running database release X and cannot apply redo data from the new primary until you upgrade it and start SQL Apply.

Upgrade the database on the former primary site to database release Y. This will become the new logical standby database.

Also upgrade any additional physical standby databases that may be part of the Data Guard configuration. Any additional physical standby databases will remain down and not brought online until the final switchover where the original primary site is running in the primary role with the new database release. Support for multi-standby configurations will be discussed later in this guide.

While the new logical standby database (former primary) is being upgraded, it will not accept redo data from the new primary database. In order to protect the new primary database, redo will be sent to the archived redo log repository that has been upgraded to database release Y as shown in Figure 5.


Figure 5: Upgrade Former Primary Database

Both Databases Upgraded

After upgrading the former primary site (which is now the new logical standby), restart SQL Apply on the new logical standby database. Both databases are now upgraded and running database release Y as shown in Figure 6.


Figure 6: Both Databases Upgraded

Perform Another Switchover (Optional)

Optionally, perform another switchover to reverse the database roles to their original configuration (the primary/logical configuration prior to starting the rolling upgrade). Activate the user applications and services on the original primary database now running the upgraded database release Y.

If the original Data Guard configuration included any additional physical standby databases, enable log transport services on the original primary for those now upgraded physical standby databases. Support for multi-standby configurations will be discussed later in this guide.

After completing the rolling upgrade, you can remove or keep the archived redo log repository.


Figure 7: Switchover Back to Original Configuration

Requirements to Perform a Rolling Upgrade Using SQL Apply

For the purpose of this guide, it is assumed that Oracle Database 10g Release 2 and any patchsets have been installed on all nodes in the Data Guard configuration.
A logical standby database already exists that will be used to perform a rolling upgrade of the Oracle Database. Click here for instructions on how to create a logical standby database from a primary database using Oracle Database 10g Release 2 (10.2) operating in maximum performance protection mode.
The Data Guard protection mode must be set to either maximum availability or maximum performance. The Data Guard protection mode cannot be set to maximum protection during the rolling upgrade.
The databases must not be part of a Data Guard Broker configuration. Data Guard Broker configurations are not supported during a rolling upgrade. If Data Guard Broker is being used, it will need to be disabled on both the primary and standby. Data Guard Broker can be re-enabled after completing the rolling upgrade.
To ensure the primary database can proceed while the logical standby database is being upgraded, the LOG_ARCHIVE_DEST_n initialization parameter for the logical standby database destination must be set to OPTIONAL(not MANDATORY).
The COMPATIBLE initialization parameter must match the software release prior to the upgrade. That is, a rolling upgrade from database release X to database release Y requires that the COMPATIBLE initialization parameter be set to database release X on both the primary and standby databases throughout the rolling upgrade process. The COMPATIBLE parameter will be set to the new Oracle version after completing the rolling upgrade when both databases have been upgraded and you are satisfied with the new Oracle version.
Rolling database upgrade is not supported by Oracle Enterprise Manager (OEM) Grid Control.

Since the SQL Apply rolling upgrade method uses a logical standby, identify and resolve any unsupported data types for the logical database. Consider the following options for using a rolling upgrade when unsupported data types exist.

Suspend or prohibit changes to the unsupported tables for the period of time it takes to perform the upgrade procedure. This option requires that you prevent users from modifying any unsupported tables 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.

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

The DBA_LOGSTDBY_EVENTS view can be thought of as a circular log containing the most recent interesting events that occurred in the context of SQL Apply. By default the last 100 events are remembered in the event view. Execute the following on the logical standby database to increase the number of recorded events from its default of 100 to its maximum (2,000,000,000 in 10.2), capture and record unsupported operations, and to record events in the DBA_LOGSTDBY_EVENTS table and the alert.log.

SQL> alter database stop logical standby apply;

Database altered.

SQL> exec dbms_logstdby.apply_set('MAX_EVENTS_RECORDED',DBMS_LOGSTDBY.MAX_EVENTS)

PL/SQL procedure successfully completed.

SQL> exec dbms_logstdby.apply_set('RECORD_UNSUPPORTED_OPERATIONS','TRUE')

PL/SQL procedure successfully completed.

SQL> exec dbms_logstdby.apply_set('EVENT_LOG_DEST','DEST_ALL')  -- 11.1 or higher

PL/SQL procedure successfully completed.

SQL> alter database start logical standby apply immediate;

Database altered.

Another option is to use Extended Datatype Support (EDS) which allows SQL Apply to replicate changes to tables that contain some data types not natively supported from one database to another. Starting with Oracle Database 10g Release 2 (10.2.0.4), SQL Apply (and Oracle Streams) supports the ability to fire triggers on the logical standby for tables with unsupported data types.

For more information, please see:
- Maximum Availability Architecture (MAA) white paper Extended Datatype Support (EDS) for Oracle Data Guard SQL Apply and Oracle Streams
- My Oracle Support [ID 559353.1] - (Pre 11.2)
- My Oracle Support [ID 949516.1] - (11.2+)

Perform a Rolling Upgrade Using SQL Apply

This section includes the steps required to perform a rolling database migration from Oracle Database 10g Release 2 (10.2.0.5) to Oracle Database 11g Release 2 (11.2.0.3) using Data Guard SQL Apply. As mentioned in the introduction to this guide, the word upgrade will be used interchangeably with migration.

As illustrated in the previous section, the example Data Guard configuration used in this guide will consist of a primary database, a logical standby database, and an optional archived redo log repository. A logical standby may already exist in your environment, or you may create it for the purpose of executing a rolling database upgrade.

Special Instructions for Multi-Standby Configurations

Although not used in this example, you may also have one or more physical standby databases in the Data Guard configuration being used for disaster recovery. If this is the case, you must also perform the upgrade of any physical standby databases in your Data Guard configuration as part of the rolling upgrade.

The upgrade of additional physical standby databases can be done when you are at the point in the rolling upgrade process after the first switchover when the original logical standby database has been upgraded to the new database release and is running in the primary role. It is important to understand that at this phase of the SQL Apply rolling upgrade that the additional physical standby databases will not be configured as standby databases of the original logical standby when it becomes the primary. You are only installing the new Oracle software on the machines and preparing the additional physical standby databases to operate under the new Oracle database software release. At no time will they operate as physical standby databases of the original logical standby when it becomes the primary (after the first switchover). The physical standby databases will operate as physical standbys of the original primary site after the rolling upgrade (the second switchover) when it is back running in the primary role with the new database release.

Unlike upgrading the primary and logical standby databases involved in the SQL Apply rolling upgrade, you will not be running the Database Upgrade Assistant (DBUA) on the additional physical standby databases. Only the the listener, /etc/oratab file, and initialization parameters will need to be updated to prepare the physical standby database to be started with the new database software release. Once the physical standby database is mounted and has caught up with the original primary, its database objects will be upgraded automatically by Redo Apply.

Make certain that you retain all archived redo log files generated by the original primary database after it and the additional physical standby databases are shutdown for the first switchover. These log files will be needed to catch up the physical standby databases at the end of the rolling upgrade when they are brought back into the Data Guard configuration as physical standbys of the original primary site.

Throughout this section, I will indicate what to do if you are supporting additional physical standby databases in addition to the logical standby being used for the rolling upgrade.

Note that all standby databases must be configured as destinations of the primary (no cascading standbys).

Oracle Configuration

Primary Database
Oracle Release	Oracle 10g Release 2 — (10.2.0.5)
Host Name	vmlinux1.idevelopment.info — (192.168.1.160)
Operating System	Red Hat Linux 5 — (CentOS 5.7)
Database Configuration	Single Instance, non-ASM, Oracle Managed Files (OMF), Flash Recovery Area (FRA), SPFILE
Oracle Home	/u01/app/oracle/product/10.2.0/db_1
Database Name (db_name)	modesto
Database Domain (db_domain)	idevelopment.info
Compatibility Level (compatible)	10.2.0.5.0
Oracle SID	modesto
Database Unique Name (db_unique_name)	modesto
TNS Alias	modesto.idevelopment.info
Service Names	modesto.idevelopment.info, modesto
Database Files - (db_create_file_dest)	/u02/oradata
Flash Recovery Area - (db_recovery_file_dest)	/u03/flash_recovery_area
Data Guard Configuration - (log_archive_config)	dg_config=(modesto,turlock,alrepos)
Local Redo Log Files - (log_archive_dest_1)	location=use_db_recovery_file_dest valid_for=(online_logfiles,all_roles) db_unique_name=modesto
Logical Standby Destination - (log_archive_dest_2)	service=turlock.idevelopment.info valid_for=(online_logfiles,primary_role) db_unique_name=turlock
Logical Standby Logs - (log_archive_dest_3)	location=/u04/oracle/oraarch/MODESTO valid_for=(standby_logfiles,standby_role) db_unique_name=modesto
Archived Redo Log Repository - (log_archive_dest_4)	service=alrepos.idevelopment.info lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=alrepos

Logical Standby Database
Oracle Release	Oracle 10g Release 2 — (10.2.0.5)
Host Name	vmlinux2.idevelopment.info — (192.168.1.162)
Operating System	Red Hat Linux 5 — (CentOS 5.7)
Database Configuration	Single Instance, non-ASM, Oracle Managed Files (OMF), Flash Recovery Area (FRA), SPFILE
Oracle Home	/u01/app/oracle/product/10.2.0/db_1
Database Name (db_name)	TURLOCK
Database Domain (db_domain)	idevelopment.info
Compatibility Level (compatible)	10.2.0.5.0
Oracle SID	turlock
Database Unique Name (db_unique_name)	turlock
TNS Alias	turlock.idevelopment.info
Service Names	turlock.idevelopment.info, turlock
Database Files - (db_create_file_dest)	/u02/oradata
Flash Recovery Area - (db_recovery_file_dest)	/u03/flash_recovery_area
Data Guard Configuration - (log_archive_config)	dg_config=(modesto,turlock,alrepos)
Local Redo Log Files - (log_archive_dest_1)	location=use_db_recovery_file_dest valid_for=(online_logfiles,all_roles) db_unique_name=turlock
Logical Standby Destination - (log_archive_dest_2)	service=modesto.idevelopment.info valid_for=(online_logfiles,primary_role) db_unique_name=modesto
Logical Standby Logs - (log_archive_dest_3)	location=/u04/oracle/oraarch/TURLOCK valid_for=(standby_logfiles,standby_role) db_unique_name=turlock
Archived Redo Log Repository - (log_archive_dest_4)	service=alrepos.idevelopment.info lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=alrepos

Archived Redo Log Repository - (Optional)
Oracle Release	Oracle 10g Release 2 — (10.2.0.5)
Host Name	vmlinux2.idevelopment.info — (192.168.1.162)
Operating System	Red Hat Linux 5 — (CentOS 5.7)
Database Configuration	Single Instance, non-ASM, Oracle Managed Files (OMF), Flash Recovery Area (FRA), SPFILE
Oracle Home	/u01/app/oracle/product/10.2.0/db_1
Database Name (db_name)	modesto
Database Domain (db_domain)	idevelopment.info
Compatibility Level (compatible)	10.2.0.5.0
Oracle SID	alrepos
Database Unique Name (db_unique_name)	alrepos
TNS Alias	alrepos.idevelopment.info
Service Names	alrepos.idevelopment.info, alrepos
Flash Recovery Area - (db_recovery_file_dest)	/u03/flash_recovery_area
Data Guard Configuration - (log_archive_config)	dg_config=(modesto,turlock,alrepos)
Local Redo Log Files - (log_archive_dest_1)	location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=alrepos
Standby Control File - (control_files)	/u03/flash_recovery_area/ALREPOS/controlfile/control01.ctl
Archived Redo Log Repository Location - (Flash Recovery Area)	/u03/flash_recovery_area/ALREPOS/archivelog

tnsnames.ora

MODESTO.IDEVELOPMENT.INFO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux1.idevelopment.info)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = modesto.idevelopment.info)
    )
  )

TURLOCK.IDEVELOPMENT.INFO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux2.idevelopment.info)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = turlock.idevelopment.info)
    )
  )

ALREPOS.IDEVELOPMENT.INFO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux2.idevelopment.info)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alrepos.idevelopment.info)
    )
  )

Rolling Upgrade using a Logical Standby Database

Enable Flashback Database on both the primary and standby database. If not enabled, it will require a brief outage (shutdown and mount) to enable it.

Starting with Oracle Database 11g Release 2 (11.2), Flashback Database can now be enabled while the database is open. A database outage is not required to enable the Flashback Database option starting with 11.2.

Primary

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  1273420 bytes
Variable Size             352321972 bytes
Database Buffers          872415232 bytes
Redo Buffers               15503360 bytes
Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Standby

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database stop logical standby apply;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  1273420 bytes
Variable Size             352321972 bytes
Database Buffers          872415232 bytes
Redo Buffers               15503360 bytes
Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Perform database and software backups on the primary and the standby databases prior to starting the upgrade process. The software backups should include the ORACLE_HOME and oraInventory directories.
Install the upgraded Oracle Database software to a new ORACLE_HOME, which is separate from the current ORACLE_HOME, to all nodes in the Data Guard configuration.

In this guide, the current database version is Oracle Database 10g Release 2 (10.2.0.5) with ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1. The Oracle Universal Installer (OUI) will be used to install Oracle Database 11g Release 2 (11.2.0.3) to ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1.

When installing the new Oracle Database software, make sure to choose the installation option "Install database software only". Do not choose to create and configure a database.

After installing the new Oracle Database release software, copy the listener.ora, tnsnames.ora, and optionally the sqlnet.ora file from the current ORACLE_HOME/network/admin to the new 11gR2ORACLE_HOME/network/admin on both the primary and standby site.

Modify the ORACLE_HOME value in listener.ora to point to the new 11gR2 home.

Primary

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux1.idevelopment.info)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

Standby

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux2.idevelopment.info)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

Also modify the the sqlnet.ora file to reflect the new 11gR2 home.

Do not start the listener in the new 11gR2 home at this time. This will be performed during the switchover later in this guide.

Apply any important patches and workarounds for Oracle 10g rolling database upgrades using Data Guard SQL Apply (Logical Standby) as noted in [ID 300479.1] on the My Oracle Support web site.

The example database used in this guide is Oracle Database 10g Release 2 (10.2.0.5) and therefore does not require any patches; however, I did implement the workaround for OEM in Bug 5236922. This will ensure that there are no Enterprise Manager components installed on the logical standby.

Bug 5287523: ORA-16216 STARTING SQL APPLY AFTER ROLLING UPGRADE COMPLETION. This bug exists only in version 10.2.0, and is fixed in patchset 10.2.0.4. A one-off patch is available for 10.2.0.1, 10.2.0.2, and 10.2.0.3. There is no workaround available.
Bug 5593617: 10G JOB SCHEDULER DOES NOT RUN AFTER 10.2 UPGRADE ORA-02800. This bug exists only in 10.2.0, and is fixed in patchset 10.2.0.4. A one-off patch is available for 10.2.0.2 and 10.2.0.3. If you are using the job scheduler, then you should apply this patch.
Bug 5496611: QUERYING V$RESTORE_POINT GIVES ORA-00600 [KRFG_GET_KEEPSEQ_2], [1], [0], [1]. This bug exists only in 10.2.0, and is fixed in patchset 10.2.0.3. A one-off patch is available for 10.2.0.2. A manual workaround is to create the guaranteed restore point while the database is mounted rather than open.

Bug 5236922: CATUPGRD.SQL FAILS ON LOGICAL STANDBY DATABASE WHEN RUNNING JOBS. This bug can cause catupgrd.sql to fail if you have Enterprise Manager components installed on the logical standby. To avoid this bug, complete these steps prior to executing the catupgrd.sql script:

1. Identify the file containing the Enterprise Manager patch script:

SQL> SELECT dbms_registry_sys.patch_script('EM') AS PATCH_FILE FROM DUAL;

PATCH_FILE
--------------------------------------------------------------------------------
?/sysman/admin/emdrep/sql/empatch.sql

2. Comment out this line in that PATCH_FILE under the ORACLE_HOME where catupgrd.sql will be run. Change to:

-- @&EM_SQL_ROOT/core/latest/admin/admin_submit_dbms_jobs.sql

Make sure you capture information about transactions running on the primary database that will not be supported by a logical standby database. Run the following procedures on the logical standby database to capture and record the information as events in the DBA_LOGSTDBY_EVENTS view.

SQL> alter database stop logical standby apply;

Database altered.

SQL> exec dbms_logstdby.apply_set('MAX_EVENTS_RECORDED',DBMS_LOGSTDBY.MAX_EVENTS)

PL/SQL procedure successfully completed.

SQL> exec dbms_logstdby.apply_set('RECORD_UNSUPPORTED_OPERATIONS','TRUE')

PL/SQL procedure successfully completed.

SQL> exec dbms_logstdby.apply_set('EVENT_LOG_DEST','DEST_ALL')  -- 11.1 or higher

PL/SQL procedure successfully completed.

SQL> alter database start logical standby apply immediate;

Database altered.

Periodically, query the DBA_LOGSTDBY_EVENTS view on the logical standby to determine if there are any DDL or DML statements that have not been applied to the logical standby database. Information returned by the following query can be used to alert you to differences between the primary and logical standby and whether or not any action is required to manually synchronize any skipped user data.

SQL> set long 1000
SQL> set pagesize 180
SQL> set linesize 80

SQL> select event_timestamp, event, status
  2  from dba_logstdby_events
  3  order by event_timestamp;

EVENT_TIMESTAMP
----------------------------------------------------------
EVENT
----------------------------------------------------------
STATUS
----------------------------------------------------------
25-MAY-12 01.19.13.478058 PM
ALTER DATABASE OPEN
ORA-16226: DDL skipped due to lack of support

...

25-MAY-12 01.21.36.673051 PM
create pfile from spfile
ORA-16226: DDL skipped due to lack of support

...

25-MAY-12 03.12.01.278268 PM
DML on "SYSMAN"."MGMT_SYSTEM_PERFORMANCE_LOG"
ORA-16129: unsupported dml encountered

25-MAY-12 03.12.01.378676 PM
DML on "SYSMAN"."MGMT_METRIC_COLLECTIONS"
ORA-16129: unsupported dml encountered

...

25-MAY-12 03.12.02.266377 PM
TRUNCATE TABLE utl_recomp_sorted
ORA-16247: DDL skipped on internal schema

25-MAY-12 03.12.02.549499 PM
CREATE SEQUENCE utl_recomp_seq START WITH 0 MINVALUE 0 ORDER NOCACHE
ORA-16247: DDL skipped on internal schema

...

25-MAY-12 03.12.17.909027 PM
DML on "OLAPSYS"."OLAP_IRID"
ORA-16129: unsupported dml encountered

25-MAY-12 03.12.17.948123 PM
DML on "OLAPSYS"."CWM2$DIMENSION"
ORA-16129: unsupported dml encountered

25-MAY-12 03.12.17.958946 PM
DML on "OLAPSYS"."CWM2$OLAPMANAGERTABLE"
ORA-16129: unsupported dml encountered

...

25-MAY-12 10.01.00.816260 PM
create global temporary table sys.ora_temp_1_ds_20068 on commit preserve rows ca
che noparallel as select /*+ no_parallel(t) no_parallel_index(t) dbms_stats curs
or_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */"OBJ#","
COL#","ROW#","BUCKET","ENDPOINT","INTCOL#","EPVALUE","SPARE1","SPARE2" from "SYS
"."HISTGRM$" sample ( 12.6381580459) t  where 1 = 2
ORA-16247: DDL skipped on internal schema

The example output above indicates that not all changes that occurred on the primary database have been applied to the logical standby database. These specific errors are fairly common and can be ignored as they indicate DML and DDL executing under internal schemas (SYS, SYSMAN, OLAPSYS, etc.) and specific database configuration commands like ALTER DATABASE OPEN and create pfile from spfile that are known to be ignored by logical standby database. These errors are nothing more than informational statements provided to record the event for diagnostic purposes. For a complete list of internal schemas ignored by logical standby, execute the following query:

SQL> select owner from dba_logstdby_skip where statement_opt = 'INTERNAL SCHEMA' order by owner;

OWNER
------------------------------
ANONYMOUS
BI
CTXSYS
DBSNMP
DIP
DMSYS
EXFSYS
MDDATA
MDSYS
MGMT_VIEW
OLAPSYS
ORACLE_OCM
ORDPLUGINS
ORDSYS
OUTLN
SI_INFORMTN_SCHEMA
SYS
SYSMAN
SYSTEM
WKPROXY
WKSYS
WK_TEST
WMSYS
XDB

If the DBA_LOGSTDBY_EVENTS view returns differences with user objects and data (non-internal), you need to decide whether or not to continue with the upgrade procedure. If you are certain that the differences between the logical standby database and the primary database are acceptable, then continue with the upgrade procedure. If not, discontinue and re-instantiate the original configuration and perform the upgrade procedure at another time after resolving the issues.

Data Guard Broker configurations are not supported during a rolling upgrade. If Data Guard Broker is being used, it will need to be disabled on both the primary and standby. Data Guard Broker can be re-enabled after completing the rolling upgrade.
SQL> alter system set dg_broker_start=false scope=both; System altered.
The Data Guard protection mode must be set to either maximum availability or maximum performance. The Data Guard protection mode cannot be set to maximum protection during the rolling upgrade.
SQL> select protection_mode from v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE

To ensure the primary database can proceed while the logical standby database is being upgraded, the LOG_ARCHIVE_DEST_n initialization parameter for the logical standby database destination must be set to OPTIONAL(not MANDATORY).

Primary

SQL> alter system set log_archive_dest_2 =
  2  'service=turlock.idevelopment.info optional valid_for=(online_logfiles,primary_role) db_unique_name=turlock'
  3  scope=both;

System altered.

Standby

SQL> alter system set log_archive_dest_2 =
  2  'service=modesto.idevelopment.info optional valid_for=(online_logfiles,primary_role) db_unique_name=modesto'
  3  scope=both;

System altered.

The COMPATIBLE initialization parameter must match the software release prior to the upgrade. In this guide, we are upgrading from 10.2.0.5 to 11.2.0.3 so COMPATIBLE will need to be set to 10.2.0.5.

Primary

SQL> select name, value from v$spparameter where name = 'compatible';

NAME            VALUE
--------------- ---------------
compatible      10.2.0.5.0

Standby

SQL> select name, value from v$spparameter where name = 'compatible';

NAME            VALUE
--------------- ---------------
compatible      10.2.0.5.0

Archived Redo Log Repository

SQL> select name, value from v$spparameter where name = 'compatible';

NAME            VALUE
--------------- ---------------
compatible      10.2.0.5.0

If the recycle bin on the logical standby database contains objects, it is required that the recycle bin is empty prior to upgrading the database. Run the PURGE DBA_RECYCLEBIN command before continuing with upgrading the logical standby. Although not documented by Oracle, I also purge the recyclebin of the primary database as well.
SQL> purge dba_recyclebin; DBA Recyclebin purged.

Compile invalid objects.

Ensure all SYS objects have been successfully compiled before upgrading the logical standby database.

SQL> @utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2012-05-30 19:46:07

...

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error

PL/SQL procedure successfully completed.

Optional — If database GUARD is enabled and set to ALL, it prevents all non-sys users from making changes to the logical standby and as a result, the OEM Database Control configuration assistant will fail during the upgrade.
SQL> select guard_status from v$database; GUARD_STATUS ------------------------- ALL
If you intend to configure Oracle Enterprise Manager (OEM) Database Control on the logical standby during the database upgrade, make certain that the database GUARD is set to either STANDBY or NONE before running the Database Upgrade Assistant (DBUA).
SQL> alter database guard standby; Database altered.
Note that this is not a fatal error that will cause the entire upgrade to fail and can be safely ignored by the DBUA. If you plan on configuring OEM Database Control after the upgrade, manually restart the OEM Database Control configuration assistant at a later time after modifying database GUARD to either STANDBY or NONE.

It is highly recommended to create a guarantee restore point on the logical standby database prior to the upgrade which can be used in case issues are encountered with the upgrade process and fallback recover is required. In the event of any issues, this will facilitate quick restoration of the database to it's pre-upgrade state via flashback database rather than having to use the downgrade procedure which can take as long as the upgrade procedure. Instructions for performing fallback recover is discussed in section Flashing Back the Database after a Failed Upgrade.

SQL> create restore point pre_logical_upgrade guarantee flashback database;

Restore point created.

SQL> select name, scn, time, guarantee_flashback_database as "GUARANTEE"
  2  from v$restore_point;

NAME                          SCN TIME                                GUARANTEE
---------------------- ---------- ----------------------------------- ------------
PRE_LOGICAL_UPGRADE        712199 30-MAY-12 07.50.24.000000000 PM     YES

If you are using Oracle Database 10g Release 1 (10.1) then guaranteed restore point cannot be used. Instead, capture the current SCN from the V$DATABASE view and designate this SCN as the flashback point.

SQL> select current_scn from v$database;

From the primary, stop redo transfer to the logical standby database.

SQL> alter system set log_archive_dest_state_2='defer' scope=both;

System altered.

The database upgrade will be performed on the logical standby; therefore log apply will need to be cancelled on the logical standby database.
SQL> alter database stop logical standby apply; Database altered.
Shut down the logical standby database.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
If using an archived redo log repository, it should remain running and receiving redo data from the current primary database.

Stop the listener running out of the old 10gR2 home on the logical standby site.

[oracle@vmlinux2 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 30-MAY-2012 19:52:55

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmlinux2.idevelopment.info)(PORT=1521)))
The command completed successfully

Set the ORACLE_HOME and other related Oracle environment variables to the new software location on the logical standby site.

[oracle@vmlinux2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@vmlinux2 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@vmlinux2 ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
[oracle@vmlinux2 ~]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@vmlinux2 ~]$ export ORA_NLS11=$ORACLE_HOME/nls/data
[oracle@vmlinux2 ~]$ unset ORA_NLS10

Confirm the correct binaries are being used.

[oracle@vmlinux2 ~]$ which dbua
/u01/app/oracle/product/11.2.0/dbhome_1/bin/dbua

Start the modified listener in the new 11gR2 home.

If the TNS_ADMIN environment variable is defined in your SHELL, ensure it is pointing to the new 11gR2 Oracle home and not the 10gR2 one.

[oracle@vmlinux2 ~]$ echo $TNS_ADMIN
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

[oracle@vmlinux2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-MAY-2012 19:55:49

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/tnslsnr/vmlinux2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlinux2.idevelopment.info)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmlinux2.idevelopment.info)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                30-MAY-2012 19:55:50
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/tnslsnr/vmlinux2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlinux2.idevelopment.info)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

As shown in Figure 2, upgrade the logical standby database using either the Database Upgrade Assistant utility (DBUA) or the manual upgrade method with catupgrd.sql. It is highly recommended to use the DBUA utility for all upgrade scenarios because it prevents errors, handles new and deprecated initialization parameters, upgrades all database components, and takes care of several post-upgrade tasks like updating the /etc/oratab.

While the upgrade is occurring on the logical standby database, the primary database remains open for use and there is no down time required. The primary database will be protected from disaster by sending its redo logs to the archived redo log repository.

If the primary site fails while performing the upgrade on the logical standby, all of the archived redo log files that were transferred to the archived redo log repository during that time can be registered with the logical standby database being upgraded and a failover to the logical standby would need to occur. This significantly reduces data loss when failing over to the logical standby.

See section Recover Production from Primary Site Failure during Upgrade for more information.

With the logical standby database shutdown, run the Database Upgrade Assistant (DBUA) using the new 11gR2 Oracle home. The DBUA will startup the logical standby database in MIGRATE mode (or UPGRADE mode if this is an upgrade rather than a migration) using the new Oracle software release, gather some database information, prompt for any upgrade options, and finally upgrade the database to the new software release.

[oracle@vmlinux2 ~]$ dbua &


Figure 8: Database Upgrade Assistant - Welcome Screen


Figure 9: Database Upgrade Assistant - Select Database


Figure 10: Database Upgrade Assistant - Pre-upgrade Checks


Figure 11: Database Upgrade Assistant - Upgrade Options


Figure 12: Database Upgrade Assistant - Archive Log and Guarantee Space Warnings


Figure 13: Database Upgrade Assistant - Move Database Files


Figure 14: Database Upgrade Assistant - Recovery and Diagnostic Locations


Figure 15: Database Upgrade Assistant - Management Options


Figure 16: Database Upgrade Assistant - Database Credentials


Figure 17: Database Upgrade Assistant - Summary


Figure 18: Database Upgrade Assistant - Progress


Figure 19: Database Upgrade Assistant - OEM Configuration Error

Note: If the database GUARD is enabled and set to ALL, it prevents all non-sys users from making changes to the logical standby and as a result, the OEM Database Control configuration assistant will fail. If you intend to configure OEM Database Control, set the database GUARD to either STANDBY or NONE and restart the OEM Database Control Configuration Assistant.


Figure 20: Database Upgrade Assistant - Results

Optional — If an archived redo log repository was being used during the exposure period, you can optionally register any archived redo log files that have already been transferred to the archived redo log repository while the logical standby was being upgraded in order to avoid resending those logs. Use the V$ARCHIVED_LOG view from the primary database to determine the SEQUENCE# and identify which log files were transmitted to the archived redo log repository (DEST_ID=4) and not to the logical standby database (DEST_ID=2).

SQL> select sequence# from v$archived_log where dest_id = 4
  2  minus
  3  select sequence# from v$archived_log where dest_id = 2;

 SEQUENCE#
----------
        98 <---- font="" from="" here="" registering="" start="">
        99
       100
       101
       102
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
       114
       115
       116
       117

20 rows selected.

This is an optional step that would typically only be used if you had a significant amount of redo data to transfer from the primary to the upgraded logical standby. This can occur if there was a large amount of data being modified on the primary and the logical standby was out of sync for an extended period of time. If the amount of data to transfer is negligible, then it may be sufficient to skip this step and allow the logical standby to catch up automatically by having the primary resend the redo data.

[oracle@vmlinux2 ~]$ sqlplus sys/MySysPassword@turlock as sysdba

SQL> alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_05_30/o1_mf_1_98_7wfds251_.arc';

Database altered.

SQL> alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_05_30/o1_mf_1_99_7wffo7ss_.arc';

Database altered.

...  ...

SQL> alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_05_30/o1_mf_1_117_7wftjhtd_.arc';

Database altered.

If the log files are stored in Oracle ASM (which is not the case in this guide) and the archived redo log repository is on the same node as the logical standby, you could use the RMAN CATALOG command to catalog and register the logs in the archived redo log repository destination rather than manually registering as shown above.

RMAN> catalog start with '/u03/flash_recovery_area/ALREPOS/archivelog';

Restart logical apply on the upgraded logical standby database.
SQL> alter database start logical standby apply immediate; Database altered.
Check the alert.log on the logical standby for any warnings or errors. The alert.log for Oracle Database 11g is located in the directory specified using the diagnostic_dest initialization parameter. For example,
/diag/rdbms///trace/alert_.log

From the primary, restart log transport services for redo to the logical standby database.

SQL> alter system set log_archive_dest_state_2=enable scope=both;

System altered.

At this point, the primary is running the lower database release (10gR2) and the logical standby is running the upgraded database release (11gR2). See Figure 3.

Verify that any redo data that accumulated on the primary system during the upgrade has been applied to the newly upgraded logical standby and that the logical standby is keeping in sync with the primary. Any redo data that was accumulating on the primary and not applied to the logical standby will be automatically transmitted and applied on the newly upgraded logical standby database. Use the V$LOGSTDBY_PROGRESS view to monitor how quickly the logical standby is applying redo.

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate, applied_time from v$logstdby_progress;

SYSDATE              APPLIED_TIME
-------------------- --------------------
31-MAY-2012 00:11:53 31-MAY-2012 00:10:32

The Data Guard configuration can run the mixed database releases for an arbitrary period while you verify that the upgraded Oracle Database software release is running properly on the logical standby.

In the steps that follow, a switchover will be performed to reverse the database roles so the same upgrade procedures can be run on the current primary.

Before performing the switchover, review the DBA_LOGSTDBY_EVENTS view on the logical standby to determine if there were any user DDL or DML statements that have not been applied to the logical standby database. If unsupported DML statements were issued on the primary, import the latest version of those tables using an import utility such as Oracle Data Pump.

For example, the following import command truncates the SCOTT.EMP table and populates it with data matching the former primary database (modesto in this example):
[oracle@vmlinux2 ~]$ impdp system network_link=modesto tables=scott.emp table_exist_action=truncate
If the logical standby site was configured with an archived redo log repository to protect the primary database during the logical standby database upgrade, it can be shutdown.

Disable log transport services from the primary to the archived redo log repository destination.
SQL> alter system set log_archive_dest_state_4='defer' scope=both; System altered.
Shutdown the archived redo log repository instance.
SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.
If the Data Guard configuration contains any addition physical standby databases, disable log transport services from the primary and shutdown the physical standby databases. The physical standby databases will not take part in the rolling upgrade or switchover phases.

Disable log transport services from the primary to any additional physical standby databases in the Data Guard configuration.
alter system set log_archive_dest_state_[n]='defer' scope=both;
Shutdown the physical standby databases.
shutdown immediate
See Special Instructions for Multi-Standby Configurations, for more information on supporting addition physical standby databases.

This is the step where the switchover occurs and downtime is required on the production database (not counting the downtime needed for enabling flashback logging at the beginning of this section). The switchover is quick and should take no longer than a minute to perform.

On the current primary, before proceeding with the switchover, query the V$DATABASE view for the SWITCHOVER_STATUS. If the status indicates TO STANDBY, then it is safe to proceed with the switchover.

If the status returns SESSIONS ACTIVE, then ensure it's OK to shut down those active sessions. If all user sessions are logged out, OEM is shutdown, and the switchover status is still returning SESSIONS ACTIVE, check if the DBMS jobs scheduler is enabled and running. If so, disable it.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE


SQL> select sid, process, program from v$session
  2  where type = 'USER' and sid <> (select distinct sid from v$mystat);

       SID PROCESS      PROGRAM
---------- ------------ ------------------------------------------------
       132 10656        oracle@vmlinux1.idevelopment.info (J000)   <---- dbms="" font="" jobs="" scheduler="">


SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
job_queue_processes                  integer     10


SQL> alter system set job_queue_processes=0 scope=both;

System altered.


-- Note that it may take a minute to clear the J000 process


SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Once the switchover status returns TO STANDBY, perform a switchover on the current primary to convert it to a logical standby.

SQL> alter database commit to switchover to logical standby;

Database altered.

This statement must wait for existing transactions to complete. To minimize the time it takes to complete the switchover, users still connected to the database should log off immediately.

According to Oracle Maximum Availability Architecture White Paper:

Database Rolling Upgrade Using Data Guard SQL Apply Oracle Database 11g and 10gR2

if the switchover status returns SESSIONS ACTIVE then SWITCHOVER TO LOGICAL STANDBY cannot be used with the WITH SESSION SHUTDOWN clause to kill sessions. Sessions need to be manually shut down.

Notice that the usual two-phased prepared switchover used in a normal Data Guard switchover operation with the ALTER DATABASE PREPARE TO SWITCHOVER statement cannot be used with a rolling upgrade 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 single-phased unprepared switchover should only be used in the context of a rolling upgrade using a logical standby database.

On the logical standby, before proceeding with the switchover operation, query the V$DATABASE view for the SWITCHOVER_STATUS. If the status indicates TO PRIMARY, then it is safe to proceed with the switchover to transition the upgraded logical standby to the new primary database role.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY


SQL> alter database commit to switchover to primary;

Database altered.

As discussed in the previous switchover, the usual two-phased prepared switchover used in a normal Data Guard switchover operation with the ALTER DATABASE PREPARE TO SWITCHOVER statement cannot be used due to mixed database versions running.

After successfully reversing the database roles with the switchover, activate the user applications and services on the database now running in the primary database role as shown in Figure 4.

If the new primary database can adequately serve as the primary database and your business does not require a logical standby database to support the primary database, and there were no additional physical standby databases in the Data Guard configuration, then you have successfully completed the rolling upgrade process. The former primary site can be discarded while users continue to work on the new upgraded primary database. Otherwise, continue with the remaining steps to upgrade the former primary and reverse the roles back to the original configuration.

The former logical standby is now the primary database and also running the latest upgraded database release software. The new logical standby (former primary) cannot receive or apply redo because it is running a lower database version than the new primary database; therefore, you must disable redo transport on the new primary.
SQL> alter system set log_archive_dest_state_2='defer' scope=both; System altered.

We now turn our focus to the former primary (new logical standby) site.

It is highly recommended to create a guarantee restore point on the new logical standby database prior to the upgrade which can be used in case issues are encountered with the upgrade process and fallback recover is required. In the event of any issues, this will facilitate quick restoration of the database to it's pre-upgrade state via flashback database rather than having to use the downgrade procedure which can take as long as the upgrade procedure. Instructions for performing fallback recover is discussed in section Flashing Back the Database after a Failed Upgrade.

SQL> create restore point pre_logical_upgrade guarantee flashback database;

Restore point created.

SQL> select name, scn, time, guarantee_flashback_database as "GUARANTEE"
  2  from v$restore_point;

NAME                          SCN TIME                                GUARANTEE
---------------------- ---------- ----------------------------------- ------------
PRE_LOGICAL_UPGRADE        710975 31-MAY-12 12.31.34.000000000 PM     YES

SQL> select current_scn from v$database;

Optional — If database GUARD is enabled and set to ALL, it prevents all non-sys users from making changes to the new logical standby and as a result, the OEM Database Control configuration assistant will fail during the upgrade.
SQL> select guard_status from v$database; GUARD_STATUS ------------------------- ALL
If you intend to configure Oracle Enterprise Manager (OEM) Database Control on the new logical standby during the database upgrade, make certain that the database GUARD is set to either STANDBY or NONE before running the Database Upgrade Assistant (DBUA).
SQL> alter database guard standby; Database altered.
Note that this is not a fatal error that will cause the entire upgrade to fail and can be safely ignored by the DBUA. If you plan on configuring OEM Database Control after the upgrade, manually restart the OEM Database Control configuration assistant at a later time after modifying database GUARD to either STANDBY or NONE.

Shut down the former primary database (which is now the new logical standby database).

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Stop the listener running out of the old 10gR2 home on the former primary (new logical standby) site.

[oracle@vmlinux1 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 31-MAY-2012 12:33:52

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmlinux1.idevelopment.info)(PORT=1521)))
The command completed successfully

Set the ORACLE_HOME and other related Oracle environment variables to the new software location on the new logical standby site.

[oracle@vmlinux1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@vmlinux1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@vmlinux1 ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
[oracle@vmlinux1 ~]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@vmlinux1 ~]$ export ORA_NLS11=$ORACLE_HOME/nls/data
[oracle@vmlinux1 ~]$ unset ORA_NLS10

Confirm the correct binaries are being used.

[oracle@vmlinux1 ~]$ which dbua
/u01/app/oracle/product/11.2.0/dbhome_1/bin/dbua

Start the modified listener in the new 11gR2 home.

If the TNS_ADMIN environment variable is defined in your SHELL, ensure it is pointing to the new 11gR2 Oracle home and not the 10gR2 one.

[oracle@vmlinux1 ~]$ echo $TNS_ADMIN
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

[oracle@vmlinux1 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 31-MAY-2012 12:35:18

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/vmlinux1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlinux1.idevelopment.info)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmlinux1.idevelopment.info)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                31-MAY-2012 12:35:19
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/vmlinux1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlinux1.idevelopment.info)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

At this point there is no disaster recovery as the new logical standby database (former primary) cannot receive or apply redo from the new primary because, as previously mentioned, it is running a lower database version than the new primary database.

Create a new archived redo log repository on the former primary site using the upgraded database release software (11gR2). Update the tnsnames.ora file to reflect the hostname change, and configure it to receive redo data from the new primary database as shown in Figure 4.

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initalrepos.ora

audit_file_dest             = '/u01/app/oracle/admin/alrepos/adump'
compatible                  = '10.2.0.5.0'
control_files               = '/u03/flash_recovery_area/ALREPOS/controlfile/control01.ctl'
db_block_size               = 8192
db_name                     = turlock
db_recovery_file_dest       = '/u03/flash_recovery_area'
db_recovery_file_dest_size  = 32G
db_unique_name              = alrepos
diagnostic_dest             = '/u01/app/oracle'
log_archive_config          = 'dg_config=(turlock,alrepos)'
log_archive_dest_1          = 'location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles)'
log_archive_dest_state_1    = 'enable'
log_archive_max_processes   = 4
log_file_name_convert       = '/TURLOCK/','/ALREPOS/'
remote_login_passwordfile   = exclusive
service_names               = alrepos.idevelopment.info
sga_target                  = 120M

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

... 
ALREPOS.IDEVELOPMENT.INFO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux1.idevelopment.info)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alrepos.idevelopment.info)
    )
  )
...

If the archived redo log repository is not receiving logs from the new primary, look in the alert.log file on the primary for any errors.

...
Thu May 31 13:27:53 2012
Error 1031 received logging on to the standby
PING[ARC3]: Heartbeat failed to connect to standby 'alrepos.idevelopment.info'. Error is 1031.
...

More than likely, this is a problem with the password file between the current primary and archived redo log repository as a result of the database migration.

Generate a new password file on the current primary database and copy it to the logical standby (renaming the file if necessary).

Using the same procedures that were used to upgrade the previous logical standby database, upgrade the former primary database which will become the new logical standby database as shown in Figure 5.

While the upgrade is occurring on the new logical standby database, the new primary database remains open for use and there is no down time required. The new primary database will be protected from disaster by sending its redo logs to the archived redo log repository.

See section Recover Production from Primary Site Failure during Upgrade for more information.

Also upgrade any additional physical standby databases and listeners that may be part of the Data Guard configuration. Any additional physical standby databases will remain down and not brought online until the final switchover where the original primary site is running in the primary role with the new database release.

Do not run the Database Upgrade Assistant on the physical standby database. Upgrade the listener, /etc/oratab file, and prepare the physical standby database to be started with the new database software release. Change any initialization parameters for the new database release any make sure the instance can start. Once the physical standby database is mounted and has caught up with the new primary, its database objects will be upgraded automatically by Redo Apply.

See Special Instructions for Multi-Standby Configurations, for more information on supporting addition physical standby databases.

Optional — If an archived redo log repository was being used during the exposure period, you can optionally register any archived redo log files that have already been transferred to the archived redo log repository while the new logical standby was being upgraded in order to avoid resending those logs. Use the V$ARCHIVED_LOG view from the new primary database to determine the SEQUENCE# and identify which log files were transmitted to the archived redo log repository (DEST_ID=4) and not to the new logical standby database (DEST_ID=2).

SQL> select sequence# from v$archived_log where dest_id = 4
  2  minus
  3  select sequence# from v$archived_log where dest_id = 2;

 SEQUENCE#
----------
       196 <---- font="" from="" here="" registering="" start="">
       197
       198
       199
       200
       201
       202
       203
       204
       205
       206
       207
       208
       209
       210
       211
       212
       213
       214
       215
       216
       217
       218
       219
       220
       221
       222
       223
       224
       225
       226
       227
       228
       229
       230
       231
       232
       233
       234
       235
       236
       237
       238
       239
       240
       241
       242
       243
       244
       245
       246
       247
       248
       249

54 rows selected.

[oracle@vmlinux1 ~]$ sqlplus sys/MySysPassword@modesto as sysdba

SQL> alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_05_31/o1_mf_1_196_7whfnscc_.arc';

Database altered.

SQL> alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_05_31/o1_mf_1_197_7whfnps0_.arc';

Database altered.

...  ...

SQL> alter database register logfile '/u03/flash_recovery_area/ALREPOS/archivelog/2012_05_31/o1_mf_1_249_7wjg6f4k_.arc';

Database altered.

RMAN> catalog start with '/u03/flash_recovery_area/ALREPOS/archivelog';

Restart logical apply on the new upgraded logical standby database.

Before starting logical apply on the new upgraded logical standby database, create a database link from the new logical standby database to the primary.

create database link 
connect to  identified by  using '';

The database user account specified in the CREATE DATABASE LINK statement must have the SELECT_CATALOG_ROLE role granted to it on the primary database. When creating the database link, connect to the logical standby database as a user with SYSDBA privileges or use the ALTER SESSION DISABLE GUARD statement to bypass the database guard and allow modifications to the tables in the logical standby database.

[oracle@vmlinux1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu May 31 23:47:47 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link turlock
  2  connect to system identified by manager using 'turlock.idevelopment.info';

Database link created.

SQL> select count(1) from dba_logstdby_parameters@turlock;

  COUNT(1)
----------
        14

The database link will be used with the NEW PRIMARY clause when starting logical apply because during the previous switchover, we needed to use the single-phased unprepared switchover to turn this former primary into a logical standby database.

SQL> alter database start logical standby apply immediate new primary turlock;

Database altered.

Check the alert.log on the new logical standby for any warnings or errors. The alert.log for Oracle Database 11g is located in the directory specified using the diagnostic_dest initialization parameter. For example,

/diag/rdbms///trace/alert_.log

From the primary, restart log transport services for redo to the new logical standby database.

SQL> alter system set log_archive_dest_state_2=enable scope=both;

System altered.

At this point, both databases are now upgraded and running the new database release (11gR2). See Figure 6.

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate, applied_time from v$logstdby_progress;

SYSDATE              APPLIED_TIME
-------------------- --------------------
31-MAY-2012 23:58:27 31-MAY-2012 23:58:05

Now that both databases have been upgraded, check and modify any login scripts, maintenance jobs, /etc/oratab, etc. to ensure they reflect the new Oracle home.

Drop the restore points created on the primary and logical standby databases. Forgetting to do this will result in filling up your Fast Recovery Area with flashback logs.

New Primary

SQL> select name from v$restore_point;

NAME
-----------------------
PRE_LOGICAL_UPGRADE

SQL> drop restore point pre_logical_upgrade;

Restore point dropped.

New Standby

SQL> select name from v$restore_point;

NAME
-----------------------
PRE_LOGICAL_UPGRADE

SQL> drop restore point pre_logical_upgrade;

Restore point dropped.

Now that all of the database upgrades have been completed, you can optionally discard the archived redo log repository configuration.

First, stop log transport services from the primary to the archived redo log repository destination.

SQL> alter system set log_archive_dest_state_4='defer' scope=both;

System altered.

Next, stop log transport services from the current logical standby to the archived redo log repository destination so that it will not try to enable if you decide to perform a final switchover and convert the logical standby back to the primary database role.

SQL> alter system set log_archive_dest_state_4='defer' scope=both;

System altered.

Finally, shutdown the archived redo log repository instance.

[oracle@vmlinux1 ~]$ sqlplus sys/MySysPassword@alrepos as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 1 00:14:37 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>

Once you are satisfied with the new Oracle database software release, it is recommended to raise the compatibility level to the rolling upgraded version on both databases.

Raise the compatibility level of both databases by setting the COMPATIBLE initialization parameter (i.e. compatible='11.2.0.3.0'). You must raise the compatibility level at the logical standby database before you raise it at the primary database.

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.

Raising the compatibility level can be performed during the next maintenance outage since changing the COMPATIBLE parameter requires the database to be bounced.

Note that raising the COMPATIBLE setting eliminates any ability to downgrade the database. Once you have updated the COMPATIBLE parameter to the target database release, you cannot downgrade to an earlier release with flashback database nor the database downgrade procedure.

Optionally, perform another switchover to reverse the database roles to their original configuration (the primary/logical configuration prior to starting the rolling upgrade) as shown in Figure 7.

Unlike with the previous switchover operation, the primary database and the logical standby database in the Data Guard configuration do not have to be shut down and restarted throughout the switchover process since this is a normal (non-rolling upgrade / two-phased prepared) role transition. In addition, it is not required to use the WITH SESSION SHUTDOWN on either the current primary or on the logical standby database which is targeted to become the new primary database because there is no need to log users off to perform a switchover with a logical standby database.

Verify it is possible to perform a switchover on the primary database.

On the current primary database, query the SWITCHOVER_STATUS column of the V$DATABASE fixed view to verify it is possible to perform a switchover.
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY
A value of TO STANDBY or SESSIONS ACTIVE in the SWITCHOVER_STATUS column indicates that it is possible to switch the primary database to the logical standby role.
Prepare the current primary database for the switchover.

Issue the following SQL statement on the primary database to prepare the current primary database for the logical standby database role.
SQL> alter database prepare to switchover to logical standby; Database altered.
If the preparation operation is successful, the value PREPARING SWITCHOVER is displayed in the SWITCHOVER_STATUS column of the V$DATABASE view indicating that the primary is ready.
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- PREPARING SWITCHOVER
Prepare the target logical standby database for the switchover.

The next step is performed on the current logical standby database which is targeted to become the new primary database and tell it to send the preparation information to the primary in its redo stream. Use the following statement to build a LogMiner Multiversioned Data Dictionary on the current logical standby database that is the target of the switchover.
SQL> alter database prepare to switchover to primary; Database altered.
The SWITCHOVER_STATUS column of the V$DATABASE view on the logical standby database initially shows PREPARING DICTIONARY while the LogMiner Multiversioned Data Dictionary is being recorded in the redo stream. Once this has completed successfully, the SWITCHOVER_STATUS column shows PREPARING SWITCHOVER.
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- PREPARING SWITCHOVER

Ensure the current primary database is ready for the future primary database's redo stream.

Before you can complete the role transition of the primary database to the logical standby role, verify the LogMiner Multiversioned Data Dictionary was received by the primary database by querying theSWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database. Without the receipt of the LogMiner Multiversioned Data Dictionary, the switchover cannot proceed, because the current primary database will not be able to interpret the redo records sent from the future primary database. The SWITCHOVER_STATUS column shows the progress of the switchover.

When the query returns the TO LOGICAL STANDBY value on the current primary, you can proceed with the next step.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO LOGICAL STANDBY

If the SWITCHOVER_STATUS on the current primary does not return TO LOGICAL STANDBY, look in the alert.logfile for the current logical standby for any errors.

Fri Jun 01 01:03:37 2012
Logminer Bld: Lockdown Complete.  DB_TXN_SCN is 0 2167941 LockdownSCN is 2167941
Error 1031 received logging on to the standby
ARC0: Error 1031 Creating archive log file to 'turlock.idevelopment.info'
Archived Log entry 621 added for thread 1 sequence 331 ID 0x7bac8e60 dest 1:
Fri Jun 01 01:03:45 2012
Error 1031 received logging on to the standby
PING[ARC3]: Heartbeat failed to connect to standby 'turlock.idevelopment.info'. Error is 1031.

More than likely, this is a problem with the password file between the current primary and logical standby.

Generate a new password file on the current primary database and copy it to the logical standby (renaming the file if necessary).

Cancel the current switchover (see below) and try again.

At this point, you are not entirely committed to the switchover operation as it is still possible to cancel the prepare phase. The switchover operation can be cancelled by issuing the following statements in the following order.

Cancel switchover on the primary database:

SQL> alter database prepare to switchover cancel;

Cancel the switchover on the logical standby database:
```
SQL> alter database prepare to switchover cancel;
```

The above statements will rewind everything that the prepare has done and put the primary database back into its normal TO STANDBY or SESSIONS ACTIVE state.

Switch the primary database to the logical standby database role.

To complete the role transition of the current primary database to a logical standby database, issue the SQL statement below.
SQL> alter database commit to switchover to logical standby; Database altered.
If many users are performing long running read/write transactions on the the current primary database when you issue the ALTER DATABASE statement above, you may see a significant stall to the end users and the time it takes to complete the switchover operation. This statement waits for all current transactions on the current primary database to end and prevents any new users from starting new transactions, and establishes a point in time where the switchover will be committed.

Executing this statement will also prevent users from making any changes to the data being maintained in the logical standby database. To ensure faster execution, verify the primary database is in a quiet state with no update activity before issuing the switchover statement (for example, have all users temporarily log off the primary database). You can query the V$TRANSACTIONS view for information about the status of any current in-progress transactions that could delay execution of this statement.

The primary database has now undergone a role transition to run in the standby database role.
Ensure all available redo has been applied to the target logical standby database that is about to become the new primary database.

After you complete the role transition of the primary database to the logical standby role and the switchover notification is received by the standby databases in the configuration, you should verify the switchover notification was processed by the current logical standby database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the current standby database. Once all available redo records are applied to the logical standby database, SQL Apply automatically shuts down in anticipation of the expected role transition.

The SWITCHOVER_STATUS value is updated to show progress during the switchover. When the status is TO PRIMARY, you can proceed with the next step.

For example:
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY
Switch the target logical standby database to the primary database role.

On the logical standby database that you want to switch to the primary role, use the following SQL statement to switch the logical standby database to the primary role:
SQL> alter database commit to switchover to primary; Database altered.
Start SQL Apply on the new logical standby database.

On the new logical standby database, start SQL Apply.
SQL> alter database start logical standby apply immediate; Database altered.
Activate Users on New Primary.

Activate the user applications and services on the original primary database now running the upgraded database release.

If the original Data Guard configuration included any additional physical standby databases, they can now be introduced back into the configuration using the new Oracle database software release.

Ensure that the compatibility level on the physical standby databases is set to the same compatibility level as the primary.

Enable log transport services on the original primary for those now upgraded physical standby databases.
alter system set log_archive_dest_state_[n]='enable' scope=both;
If the Data Guard Broker was previously configured, it can now be re-enabled. This needs to be performed on the primary and standby databases.
SQL> alter system set dg_broker_start=true scope=both; System altered.

Flashing Back the Database after a Failed Upgrade

Flashback Database is a method to achieve very fast point-in-time recovery and is most often the quickest way to fall back to the previous release. Note that data from any transactions that occur after the point in time to which the database is recovered are lost. Flashback Database is an excellent tool for backing out the database upgrade on the logical standby before the first switchover in the SQL Apply rolling upgrade process. Using Flashback Database with sufficient space and creating a guaranteed restore point immediately prior to the upgrade is the fastest method to fallback as compared to a restore operation or a database downgrade.

Following switchover, and after the production workload is running on the original standby database, any Flashback operation will result in data loss. Deciding to use Flashback Database versus downgrading the database typically entails a trade-off between the speed at which you need to return the primary database to the previous release and the amount of data loss that you can tolerate.

You can use Flashback Database to fall back to the pre-upgrade release only if you have not changed the COMPATIBLE database parameter to the target release.

The steps for flashing back the database after a failed upgrade (due to a failure while running the catupgrd.sql script or a DBUA failure) are as follows:

Shut down the upgraded database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup mount the database under the new ORACLE_HOME (11gR2).

[oracle@vmlinux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 27 18:45:39 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1238732800 bytes
Fixed Size                  1344624 bytes
Variable Size             369101712 bytes
Database Buffers          855638016 bytes
Redo Buffers               12648448 bytes
Database mounted.

Use Flashback Database to return to the guaranteed restore point that was taken prior to the upgrade.
SQL> flashback database to restore point PRE_LOGICAL_UPGRADE; Flashback complete.

Shut down the database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup mount the database under the old ORACLE_HOME (10gR2).

[oracle@vmlinux2 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1

[oracle@vmlinux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sun May 27 18:52:05 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  1273420 bytes
Variable Size             369099188 bytes
Database Buffers          855638016 bytes
Redo Buffers               15503360 bytes
Database mounted.

Issue the OPEN RESETLOGS command to open the database:

SQL> alter database open resetlogs;

Database altered.

Recover Production from Primary Site Failure during Upgrade

During the phase of a SQL Apply rolling upgrade when the logical standby database is being upgraded, the primary database will be unprotected unless an optional archived redo log repository is configured to continue receiving redo from the primary. If the primary site fails during the upgrade of the logical standby, a failover to the logical standby would need to occur and user applications and services would need to be activated on that database now running in the primary database role. Without an archived redo log repository in the configuration, any transactions that were generated when the logical standby was shutdown for the upgrade would be lost.

This section briefly describes the process of activating the logical standby in case of a failure with the primary site during the upgrade phase of the logical standby database.

Primary Site Failure

First, you must decide whether or not to activate the user applications and services on the new database software release or the prior release. If the logical standby database upgrade completed successfully before the primary site failed, you can perform a database flashback to restore and open the database using the previous database release.


Figure 21: Primary Site Failure During Logical Standby Database Upgrade

Register Logs with Logical Standby

Next, register any archived redo log files with the logical standby database that have been transferred to the archived redo log repository while the logical standby was being upgraded.

alter database register logical logfile '/';


Figure 22: Register Logs with Logical Standby Database

Activate the New Primary Database

Finally, recover the logical standby database with any missing log files, perform a failover to transition the logical standby to the primary database role, and activate user applications and services to the new primary database.

alter database activate logical standby database;


Figure 23: Activate the New Primary Database

Oracle DBA Centre

Main Menu

Database Rolling Upgrade using Data Guard SQL Apply

Contents

Introduction

Illustrations and Requirements

Data Guard Configuration Before Rolling Upgrade

Upgrade the Logical Standby Database

Restart SQL Apply and Run in Mixed Mode

Perform a Switchover

Upgrade the Former Primary Database

Both Databases Upgraded

Perform Another Switchover (Optional)

Requirements to Perform a Rolling Upgrade Using SQL Apply

Perform a Rolling Upgrade Using SQL Apply

Special Instructions for Multi-Standby Configurations

Oracle Configuration

tnsnames.ora

Rolling Upgrade using a Logical Standby Database

Flashing Back the Database after a Failed Upgrade

Recover Production from Primary Site Failure during Upgrade

Primary Site Failure

Register Logs with Logical Standby

Activate the New Primary Database

No comments: