Upgrading Databases with Oracle Data Guard Standbys

3 Upgrading Databases with Oracle Data Guard Standbys

When you upgrade a database to a new release that uses one or more Oracle Data Guard Standby databases, you use the redo logs from the primary database.

This scenario assumes you are using Oracle Data Guard broker.

Preparing for Upgrades of Databases Using Oracle Data Guard
In this scenario, you perform your upgrade using Oracle Data Guard with Data Guard broker, and move the Data Guard broker configuration files before starting your upgrade.
Manual Non-CDB to PDB Conversion and Upgrade with Data Guard Standby
Upgrades with Oracle Data Guard where you want to uprade a non-CDB to a PDB, and reuse the non-CDB stadnby datafiles require a special procedure.
Before You Patch or Upgrade the Oracle Database Software
Before you patch or upgrade your Oracle Database software, review the prerequisites for different use case scenarios.
Recovering After the NOLOGGING Clause Is Specified
Some SQL statements allow you to specify a NOLOGGING clause so that the operation is not logged in the online redo log file.
Enable an Appropriate Logging Mode
As part of preparing the primary database for standby database creation, you must enable a logging mode appropriate to the way you plan to use the Oracle Data Guard configuration.
Creating a Physical Standby Task 1: Create a Backup Copy of the Primary Database Data Files
You can use any backup copy of the primary database to create the physical standby database, as long as you have the necessary archived redo log files to completely recover the database.
Creating a Physical Standby Task 2: Create a Control File for the Standby Database
Create the control file for the standby database. The primary database does not have to be open, but it must at least be mounted.
Creating a Physical Standby Task 3: Create a Parameter File for the Standby Database
Create a parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database.
Upgrading Oracle Database with a Physical Standby Database in Place
These steps show how to upgrade to Oracle Database when a physical standby database is present in the configuration.
Creating a Physical Standby Task 4: Copy Files from the Primary System to the Standby System
Ensure that all required directories are created. Use an operating system copy utility to copy binary files from the primary system to their correct locations on the standby system.
Creating a Physical Standby Task 5: Set Up the Environment to Support the Standby Database
Set up the environment by creating a Windows-based service, a password file, and an SPFILE, and then setting up the Oracle Net environment.
Creating a Physical Standby Task 6: Start the Physical Standby Database
These are the steps to start the physical standby database and Redo Apply.
Creating a Physical Standby Task 7: Verify the Physical Standby Database Is Performing Properly
After you create the physical standby database and set up redo transport services, you may want to verify database modifications are being successfully transmitted from the primary database to the standby database.

Preparing for Upgrades of Databases Using Oracle Data Guard

In this scenario, you perform your upgrade using Oracle Data Guard with Data Guard broker, and move the Data Guard broker configuration files before starting your upgrade.

The default location for the DB_BROKER_CONFIG files is in the dbs directory in the earlier release Oracle Database Oracle home. When you perform a rolling upgrade of database instances using Oracle Data Guard, you must move the DG_BROKER_CONFIG files to a mount point location outside of the earlier release Oracle home. Also ensure that the DG_BROKER_CONFIG_FILEn parameters specify that location, instead of a location in the earlier release Oracle home. During database upgrade, don't migrate the listener. After the upgrade is complete, stop the listener, shut down the database, copy over the listener.ora and tnsnames.ora from the earlier source Oracle Database release environment to the new Oracle Database release environment, and start the listener and database

Tasks Before Starting Your Upgrade

To enable access to the DB_BROKER_CONFIG files during a rolling upgrade, you must complete the following tasks before starting the upgrade

Before you start the upgrade, if you are not using Oracle Automatic Storage Management (Oracle ASM) for storage, then set the Oracle Data Guard files DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2 to a separate mount point on your server that is outside of the Oracle home path for either the source or target Oracle Database Oracle homes.

Note:

Prior to Oracle Database 21c, the default ORACLE_HOME layout combined ORACLE_HOME, ORACLE_BASE_HOME and ORACLE_BASE_CONFIG into a single location. Starting with Oracle Database 21c, the only available configuration is a read-only ORACLE_HOME where ORACLE_BASE_HOME and ORACLE_BASE_CONFIG are located separately from ORACLE_HOME. Files such as the Oracle Data Guard Files, which were previously located in the folder dbs, are now located in ORACLE_BASE_CONFIG/dbs.
Complete a successful upgrade of your earlier release Oracle home to the new Oracle Database release.

Tasks During the Upgrade

Do not migrate the listener during the upgrade.

Oracle recommends that you use AutoUpgrade to complete the upgrade.

See:

AutoUpgrade and Oracle Data Guard

Tasks After Completing Your Upgrade

Stop the listener for the new release Oracle Database.
Shut down the new release Oracle Database.
Copy over the listener.ora and tnsnames.ora files from the earlier release Oracle Database to the new release Oracle Database.
Start the listener and new release Oracle Database

Refer to Oracle Data Guard Broker for information about moving your Data Guard broker configuration files.

See a Demonstration of how to upgrade with AutoUpgrade and Oracle Data Guard

Daniel Overby Hansen provides you with an overview of how you can use AutoUpgrade to perform upgrades of Oracle Databases using Oracle Data Guard standby databases.

How to Upgrade with AutoUpgrade and Data Guard

Related Topics

Renaming the Broker Configuration Files

Parent topic: Upgrading Databases with Oracle Data Guard Standbys

Manual Non-CDB to PDB Conversion and Upgrade with Data Guard Standby

Upgrades with Oracle Data Guard where you want to uprade a non-CDB to a PDB, and reuse the non-CDB stadnby datafiles require a special procedure.

When you have an Oracle Data Guard deployment on your non-CDB source Oracle Database configuration, and you want to reuse the source standby database files on your target PDB after you plug the non-CDB in as a PDB into the primary database of an Oracle Data Guard configuration, there are particular steps required that you must perform manually. For the current release of AutoUpgrade, this functionality is not supported.

For instructions on how to complete an upgrade with this scenario, refer to Reusing the Source Standby Database Files When Plugging a non-CDB as a PDB into the Primary Database of a Data Guard Configuration (Doc ID 2273304.1)

Related Topics

Reusing the Source Standby Database Files When Plugging a non-CDB as a PDB into the Primary Database of a Data Guard Configuration (Doc ID 2273304.1)

Parent topic: Upgrading Databases with Oracle Data Guard Standbys

Before You Patch or Upgrade the Oracle Database Software

Before you patch or upgrade your Oracle Database software, review the prerequisites for different use case scenarios.

If you are using the Oracle Data Guard broker to manage your configuration, follow the instructions in Oracle Data Guard Broker
Use procedures described in these topics in conjunction with other upgrade procedures and guidelines provided in Oracle Database Upgrade Guide.
Check for NOLOGGING operations. If NOLOGGING operations have been performed then you must update the standby database.

See Recovering After the NOLOGGING Clause Is Specified
Make note of any tablespaces or data files that need recovery due to OFFLINE IMMEDIATE. Before starting an upgrade, tablespaces or data files should be recovered, and either online or offline.
In an Oracle Data Guard configuration, all physical and snapshot standby databases must use a copy of the password file from the primary database. Password file changes done on the primary database are automatically propagated to standby databases. Password file changes are events such as when an administrative privilege (SYSDG, SYSOPER, SYSDBA, and so on) is granted or revoked, and when the password of any user with administrative privileges is changed.

Far sync instances are an exception to the automatic updating feature. Updated password files must still be manually copied to far sync instances, because far sync instances receive redo, but do not apply it. When a password file is manually updated at a far sync instance, the redo containing the same password changes from the primary database is automatically propagated to any standby databases that are set up to receive redo from that far sync instance. The password file is updated on the standby when the redo is applied.

Note:

If there are cascaded standbys in your configuration, then those cascaded standbys must follow the same rules as any other standby, but should be shut down last, and restarted in the new home first.

Related Topics

Oracle Data Guard Broker Upgrading and Downgrading

Parent topic: Upgrading Databases with Oracle Data Guard Standbys

Recovering After the NOLOGGING Clause Is Specified

Some SQL statements allow you to specify a NOLOGGING clause so that the operation is not logged in the online redo log file.

In actuality, when you specify NOLOGGING, a redo record is still written to the online redo log file, but there is no data associated with the record. This specification can result in log application or data access errors at the standby site. Manual recovery might be required to resume applying log files. Depending on whether you have a logical standby or physical standby, you can avoid these errors by doing the following:

Logical standbys

Specify the FORCE LOGGING clause in the CREATE DATABASE or ALTER DATABASE statements.
Physical standbys

Specify a logging mode that is appropriate to the way in which you plan to use your Data Guard configuration.

See Enable an Appropriate Logging Mode.

You can see the current logging mode in the V$DATABASE.FORCE_LOGGING column (for CDBs), or the DBA_PDBS.FORCE_LOGGING column (for PDBs).

Parent topic: Upgrading Databases with Oracle Data Guard Standbys

Enable an Appropriate Logging Mode

As part of preparing the primary database for standby database creation, you must enable a logging mode appropriate to the way you plan to use the Oracle Data Guard configuration.

The default logging mode of a database that is not part of an Oracle Data Guard configuration allows certain data loading operations to be performed in a nonlogged manner. This default mode is not appropriate to a database with a standby, because it leads to the loaded data being missing from the standby, which requires manual intervention to fix.

In addition to the default logging mode, there are three other modes that are appropriate for a primary database:

FORCE LOGGING mode prevents any load operation from being performed in a nonlogged manner. This mode can slow down the load process, because the loaded data must be copied into the redo logs. FORCE LOGGING mode is enabled using the following command:
```
SQL> ALTER DATABASE FORCE LOGGING;
```
STANDBY NOLOGGING FOR DATA AVAILABILITY mode causes the load operation to send the loaded data to each standby through its own connection to the standby. The commit is delayed until all the standbys have applied the data as part of running managed recovery in an Active Data Guard environment. It is enabled with the following command:
```
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;
```
STANDBY NOLOGGING FOR LOAD PERFORMANCE is similar to the previous mode except that the loading process can stop sending the data to the standbys if the network cannot keep up with the speed at which data is being loaded to the primary. In this mode it is possible that the standbys may have missing data, but each standby automatically fetches the data from the primary as a normal part of running managed recovery in an Active Data Guard environment. It is enabled with the following command:
```
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;
```

When you issue any of these statements, the primary database must at least be mounted (and it can also be open). The statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.

Note:

When you enable STANDBY NOLOGGING FOR DATA AVAILABILITY or STANDBY NOLOGGING FOR LOAD PERFORMANCE on the primary database, any standbys that are using multi-instance redo apply functionality will stop applying redo with the error ORA-10892. You must first restart redo apply and allow the affected standbys to progress past the NOLOGGING operation period and then enable multi-instance redo apply.

Related Topics

Specifying FORCE LOGGING Mode

Creating a Physical Standby Task 1: Create a Backup Copy of the Primary Database Data Files

You can use any backup copy of the primary database to create the physical standby database, as long as you have the necessary archived redo log files to completely recover the database.

You can use any backup copy of the primary database to create the physical standby database, as long as you have the necessary archived redo log files to completely recover the database. Oracle recommends that you use the Recovery Manager utility (RMAN).

Related Topics

Backing Up the Database, Oracle Database Backup and Recovery User’s Guide

Parent topic: Upgrading Databases with Oracle Data Guard Standbys

Creating a Physical Standby Task 2: Create a Control File for the Standby Database

Create the control file for the standby database. The primary database does not have to be open, but it must at least be mounted.

You must create a control file for the standby database. You cannot use a single control file for both the primary and standby databases. They each must have their own file.

Example 3-1 Creating the Control File for the Standby Database

The ALTER DATABASE command designates the database that you want to operate in the standby role. In this example, that standby database is named boston:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';

Note:

If a control file backup is taken on the primary, and restored on a standby (or vice-versa), then the location of the snapshot control file on the restored system is configured to be the default. The default value for the snapshot control file name is platform-specific, and dependent on the Oracle home. Manually reconfigure it to the correct value by using the RMAN command CONFIGURE SNAPSHOT CONTROLFILE.

Parent topic: Upgrading Databases with Oracle Data Guard Standbys

Creating a Physical Standby Task 3: Create a Parameter File for the Standby Database

Create a parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database.

To create a parameter file for the standby database, perform the following steps:

On the primary database, issue a SQL statement to create a copy of the primary database parameter file.
In the following example,
```
SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;
```
Modify the parameter values in the copy parameter file as needed to use this copy as the parameter file for the standby database.

Although most of the initialization parameter settings in the parameter file are also appropriate for the physical standby database, some modifications must be made.

Example 3-2 Modifying Initialization Parameters for a Physical Standby Database

This example shows the parameters created earlier on the primary that must be changed. The parameters that you must change are in bold typeface.

.
.
.
DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'
DB_FILE_NAME_CONVERT='/chicago/','/boston/'
LOG_FILE_NAME_CONVERT='/chicago/','/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
 'SERVICE=chicago ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=chicago'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
.
.
.

Ensure the COMPATIBLE initialization parameter is set to the same value on both the primary and standby databases. If the values differ, then redo transport services may be unable to transmit redo data from the primary database to the standby databases.

It is always a good practice to use the SHOW PARAMETERS command to verify that no other parameters need to be changed.

The following table provides a brief explanation about the parameter settings shown in that have different settings from the primary database.

Parameter	Recommended Setting
`DB_UNIQUE_NAME`	Specify a unique name for this database. This name uniquely identifies this database, and does not change even if the primary and standby databases reverse roles.
`CONTROL_FILES`	Specify the path name for the control files on the standby database. The example in this topic shows how to specify the path name for two control files. Oracle recommends that you ensure a copy of the control file is available, so that if a control file is corrupted, an instance can be easily restarted after copying the good control file to the location of the bad control file.
`DB_FILE_NAME_CONVERT`	Specify the path name and filename location of the primary database data files, followed by the standby location. The `CONTROL_FILES` parameter converts the path names of the primary database data files to the standby data file path names.
`LOG_FILE_NAME_CONVERT`	Specify the location of the primary database online redo log files followed by the standby location. This parameter converts the path names of the primary database log files to the path names on the standby database.
`LOG_ARCHIVE_DEST_n`	Specify where the redo data is to be archived. In the example in this topic, the following destinations are specified: `LOG_ARCHIVE_DEST_1` archives redo data received from the primary database to archived redo log files in `/arch1/boston/`. `LOG_ARCHIVE_DEST_2` is currently ignored, because this destination is valid only for the primary role. If a switchover occurs, and this instance becomes the primary database, then this parameter specification provides the path to transmit redo data to the remote Chicago destination. Note: If a fast recovery area was configured (using the `DB_RECOVERY_FILE_DEST` initialization parameter), and you have not explicitly configured a local archiving destination with the `LOCATION` attribute, then Oracle Data Guard automatically uses the `LOG_ARCHIVE_DEST_1` initialization parameter (if it has not already been set) as the default destination for local archiving.
`FAL_SERVER`	Specify the Oracle Net service name of the FAL (fetch archive log) server for a standby database. Typically, this service name is for the database running in the primary role. When the Boston database is running in the standby role, it uses the Chicago database as the FAL server from which to fetch (request) missing archived redo log files, if Chicago is unable to automatically send the missing log files.

Note:

Review the initialization parameter file for additional parameters that may need to be modified. For example, you may need to modify the dump destination parameters if the directory location on the standby database is different from those specified on the primary database.

Related Topics

LOG_ARCHIVE_DEST_n Parameter Attributes

Parent topic: Upgrading Databases with Oracle Data Guard Standbys

Upgrading Oracle Database with a Physical Standby Database in Place

These steps show how to upgrade to Oracle Database when a physical standby database is present in the configuration.

Note:

If the database being upgraded is a member of an Oracle Data Guard broker configuration, then before proceeding, you must disable fast-start failover and shut down the broker. For information about how to do this, see Oracle Data Guard Broker .

Review and perform the standard preupgrade preparation tasks described in Oracle Database Upgrade Guide.
Install the new release of the Oracle software into a new Oracle home on the physical standby database and primary database systems, as described in Oracle Database Upgrade Guide
Shut down the primary database.
Shut down physical standby databases.
Stop all listeners, agents, and other processes running in the Oracle homes that you want to upgrade (Source Oracle homes). Perform this step on all nodes in an Oracle Real Application Clusters (Oracle RAC) environment.
In the new Oracle home (Target Oracle home), restart all listeners, agents, and other processes that you stopped in the source Oracle home
Mount physical standby databases on the target Oracle home (upgraded version).

Caution:

Do not open standby databases until the primary database upgrade is completed.

See Start the Physical Standby Database for information on how to start a physical standby database.
Start Redo Apply on the physical standby databases.

Note:
By default, AutoUpgrade disables log shipping. If you have modified your AutoUpgrade configuration file to enable log shipping, then modify your AutoUpgrade configuration file to set the AutoUpgrade locally modifiable global parameter defer_standby_log_shipping to no. For example: upg1.defer_standby_log_shipping=no

See Start the Physical Standby Database for information on how to start Redo Apply.
Upgrade the primary database. Physical standby databases are upgraded when the redo generated by the primary database as it is upgraded is applied to standbys.
Open the upgraded primary database.
If Oracle Active Data Guard was being used before the upgrade, then you must reenable it after upgrading.

See Real-time query
(Optional) When ready. modify the COMPATIBLE initialization parameter.

Note:

On Microsoft Windows platforms, it is necessary to use the ORADIM utility to delete the database service (for the old database version), and to create a new database service for the new database version. You must replace the OracleServiceSID on both the primary and standby servers.

Creating a Physical Standby Task 4: Copy Files from the Primary System to the Standby System

Ensure that all required directories are created. Use an operating system copy utility to copy binary files from the primary system to their correct locations on the standby system.

Copy these binary files to the correct locations on the standby system:

The primary Oracle Database backup.

See Create a Backup Copy of the Primary Database Data Files
the standby control file.

See Create a Control File for the Standby Database
Standby database initialization parameter file.

See Create a Parameter File for the Standby Database

Parent topic: Upgrading Databases with Oracle Data Guard Standbys

Creating a Physical Standby Task 5: Set Up the Environment to Support the Standby Database

Set up the environment by creating a Windows-based service, a password file, and an SPFILE, and then setting up the Oracle Net environment.

To set up the environment, perform the following steps:

If the standby database is going to be hosted on a Windows system, then use the ORADIM utility to create a Windows service.
For example:
```
oradim –NEW –SID boston –STARTMODE manual
```
The ORADIM utility automatically determines the username for which this service should be created and prompts for a password for that username (if that username needs a password).

See Oracle Database Administrator’s Reference for Microsoft Windows for more information about using the ORADIM utility.
Copy the remote login password file from the primary database system to the standby database system.

This step is optional if operating system authentication is used for administrative users, and if SSL is used for redo transport authentication. If that is not the case, then copy the remote login password file from the primary database to the appropriate directory on the physical standby database system.

Any subsequent changes to the password file on the primary are automatically propagated to the standby. Changes to a password file can include when administrative privileges (SYSDG, SYSOPER, SYSDBA, and so on) are granted or revoked, and when passwords of any user with administrative privileges is changed. Updated password files must still be manually copied to far sync instances because far sync instances receive redo, but do not apply it. Once the password file is up-to-date at the far sync instance, the redo containing the password update at the primary is automatically propagated to any standby databases that are set up to receive redo from that far sync instance. The password file is updated on the standby when the redo is applied.
Configure and start a listener on the standby system if one is not already configured.

See Configuring and Administering Oracle Net Listener in Oracle Database Net Services Administrator's Guide.
Create Oracle Net service names.

On both the primary and standby systems, use Oracle Net Manager to create a network service name for the primary and standby databases that are to be used by redo transport services. The Net service names in this example are chicago and boston.

The Oracle Net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and service that you specified when you configured the listeners for the primary and standby databases. The connect descriptor must also specify that a dedicated server be used.

See Understanding Database Services in Oracle Database Net Services Administrator's Guide for more information about service names.
On an idle standby database, use the SQL CREATE statement to create a server parameter file for the standby database from the text initialization parameter file that was edited in Task 3.
For example:
```
SQL> CREATE SPFILE FROM PFILE='initboston.ora';
```
If the primary database has a database encryption wallet, then copy it to the standby database system and configure the standby database to use this wallet.

Note:

The database encryption wallet must be copied from the primary database system to each standby database system whenever the master encryption key is updated.

Encrypted data in a standby database cannot be accessed unless the standby database is configured to point to a database encryption wallet or hardware security module that contains the current master encryption key from the primary database.

Parent topic: Upgrading Databases with Oracle Data Guard Standbys

Creating a Physical Standby Task 6: Start the Physical Standby Database

These are the steps to start the physical standby database and Redo Apply.

On the standby database, issue the following SQL statement to start and mount the database:
```
SQL> STARTUP MOUNT;
```
Restore the backup of the data files taken from the primary database data files, and copied to the standby system.
On the standby database, issue the following command to start Redo Apply:
```
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE - 
> DISCONNECT FROM SESSION;
```
The statement includes the DISCONNECT FROM SESSION option, so that Redo Apply runs in a background session.

Parent topic: Upgrading Databases with Oracle Data Guard Standbys

Creating a Physical Standby Task 7: Verify the Physical Standby Database Is Performing Properly

After you create the physical standby database and set up redo transport services, you may want to verify database modifications are being successfully transmitted from the primary database to the standby database.

To verify that redo is being transmitted from the primary database and applied to the standby database, connect to the standby database, and query the V$DATAGUARD_PROCESS view.

Example 3-3 Querying V$DATAGUARD_PROCESS to Verify Redo Transmission from Primary to Secondary Database

SQL> SELECT ROLE, THREAD#, SEQUENCE#, ACTION FROM V$DATAGUARD_PROCESS;


ROLE                        THREAD#  SEQUENCE# ACTION
------------------------ ---------- ---------- ------------
RFS ping                          1          9 IDLE
recovery apply applier            0          0 IDLE
recovery apply applier            0          0 IDLE
managed recovery                  0          0 IDLE
recovery logmerger                1          9 APPLYING_LOG
RFS archive                       0          0 IDLE
RFS async                         1          9 IDLE

The recovery logmerger role shows that redo is being applied at the standby.

Note:

Use the V$DATAGUARD_PROCESS view instead of the V$MANAGED_STANDBY view. V$MANAGED_STANDBY was deprecated in Oracle Database 12c Release 2 (12.2.0.1) and can be desupported in a future release.

Parent topic: Upgrading Databases with Oracle Data Guard Standbys