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.

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).

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.

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).

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

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

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

1. 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;
   

2. 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.

.
.
.
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
.
.
.

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

1. Review and perform the standard preupgrade preparation tasks described in Oracle Database Upgrade Guide.
2. 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
3. Shut down the primary database.
4. Shut down physical standby databases.
5. 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.
6. In the new Oracle home (Target Oracle home), restart all listeners, agents, and other processes that you stopped in the source Oracle home
7. 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.

8. 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.
9. 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.
10. Open the upgraded primary database.
11. If Oracle Active Data Guard was being used before the upgrade, then you must reenable it after upgrading.

    See Real-time query

12. (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.

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.

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

1. 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.

2. 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.

3. 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.

4. 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.

5. 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';
   

6. 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.

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

1. On the standby database, issue the following SQL statement to start and mount the database:

   SQL> STARTUP MOUNT;
   

2. Restore the backup of the data files taken from the primary database data files, and copied to the standby system.
3. 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.

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.

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