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 Database Rolling Upgrades Using Oracle Data Guard
If you perform your upgrade using Oracle Data Guard to carry out a rolling upgrade, then you must 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
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
tnsnames.ora from the earlier source Oracle Database release
environment to the new Oracle Database release environment, and start the listener and
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_FILE2to 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.
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
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:
Tasks After Completing Your Upgrade
- Stop the listener for the new release Oracle Database.
- Shut down the new release Oracle Database.
- Copy over the
tnsnames.orafiles 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.
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.
NOLOGGINGoperations have been performed then you must update the standby database.
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 (
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.
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.
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:
FORCE LOGGINGclause in the
Specify a logging mode that is appropriate to the way in which you plan to use your Data Guard configuration.
You can see the current logging mode in the
column (for CDBs), or the
DBA_PDBS.FORCE_LOGGING column (for PDBs).
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 LOGGINGmode 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 LOGGINGmode is enabled using the following command:
SQL> ALTER DATABASE FORCE LOGGING;
STANDBY NOLOGGING FOR DATA AVAILABILITYmode 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 PERFORMANCEis 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.
When you enable
STANDBY NOLOGGING FOR DATA AVAILABILITY
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.
See Also:Oracle Database Administrator’s GuideFor more information about the ramifications of specifying
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
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
ALTER DATABASE command designates the database that
you want to operate in the standby role. In this example, that standby database is
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
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.
Creating a Physical Standby Task 3: Create a Parameter File for the Standby Database
Create a parameter file (
PFILE) from the server parameter
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
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 . . .
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
It is always a good practice to use the
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.
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.
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.
Specify the path name and filename location of the primary database
data files, followed by the standby location. The
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.
Specify where the redo data is to be archived. In the example in this topic, the following destinations are specified:
Note: If a fast recovery area was configured (using the
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.
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.
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).
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
no. For example:
upg1.defer_standby_log_shipping=noSee 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
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
OracleServiceSIDon 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.
the standby control file.
Standby database initialization parameter file.
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,
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
ORADIMutility to create a Windows service.
oradim –NEW –SID boston –STARTMODE manual
ORADIMutility 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
- 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 (
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
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
CREATEstatement to create a server parameter file for the standby database from the text initialization parameter file that was edited in Task 3.
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.
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.
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 SESSIONoption, so that Redo Apply runs in a background session.
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
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 slave 0 0 IDLE recovery apply slave 0 0 IDLE managed recovery 0 0 IDLE recovery logmerger 1 9 APPLYING_LOG RFS archive 0 0 IDLE RFS async 1 9 IDLE
recovery logmerger role shows that redo is being
applied at the standby.
V$DATAGUARD_PROCESS view instead of the
V$MANAGED_STANDBY view. V$MANAGED_STANDBY was deprecated in
Oracle Database 12c Release 2 (126.96.36.199) and can be desupported in a future