Scenarios

• 11.2.0.3/4 to 12.1.0.2 - Use physru.sh

• 12.1.0.2 to 12.2.0.1+ - Use DBMS_ROLLING (if you have Active Data Guard licenses), otherwise use physru.sh

physru.sh

Notes

• Active Data Guard standby databases involved in the upgrade cannot be used as a read-only source during the execution of this process.

Assumptions

• No Oracle Label Security

• No Oracle E-Business Suite

• No Data Vault

• No RAC

• DBUA will be used for the database upgrades

• An existing Physical Standby with Forced Logging enabled. This query should return zero rows:

select NAME from V$DATAFILE where UNRECOVERABLE_CHANGE#>0;

• You do not have multiple Standby databases

• You have a Fast Recovery Area (FRA) configured

• The COMPATIBLE initialization parameter matches the software release prior to the upgrade

• You are using Oracle Managed Files (OMF).

• There are no existing Guaranteed Restore Points.

• It is assumed there is a suitable static Service to the LISTENER.ORA for each Instance (Primary and Standby).

• It is assumed there is a TNS alias entry in TNSNAMES.ORA to connect to the static Service (Primary and Standby)

Check Parameters

• The PROCESSES parameter must be greater than PARALLEL_MAX_SERVERS+100 on the initial standby database

SHOW PARAMETER processes

SHOW PARAMETER parallel_max_servers

If necessary...

ALTER SYSTEM SET processes=&new_value SCOPE=SPFILE;

• LOG_ARCHIVE_DEST_n for the standby database must be OPTIONAL

This is true as long as the word MANDATORY does not appear in the LOG_ARCHIVE_DEST_2 parameter.

SHOW PARAMETER LOG_ARCHIVE_DEST_2

Patch 14174798

• Apply Patch 14174798.

Preparation Tasks

• Complete all Upgrade 'Preparation Tasks' on the Primary and Standby

Data Type Restrictions

To identify tables that will be a problem use...

SELECT owner, table_name

FROM dba_logstdby_unsupported;

You can use the following SQL to retrieve the DDL of the table in order to help identify the specific issue...

SET LONG 20000

SELECT dbms_metadata.get_ddl('TABLE','&TABLE','&OWNER') FROM dual;

Data type restrictions (11.2)

• BFILE

• Collections (including VARRAYS and nested tables)

• Multimedia data types (including Spatial, Image, and Oracle Text)

• ROWID, UROWID

Data type restrictions (12.1)

• BFILE

• ROWID, UROWID

• Collections (including VARRAYs and nested tables)

• Objects with nested tables and REFs

• The following Spatial types are not supported: • MDSYS.SDO_GEORASTER • MDSYS.SDO_TOPO_GEOMETRY

• Identity columns

User-defined types Extended Datatype Support can be utilized to mitigate data type restrictions. See My Oracle Support Note 949516.1

• Ensure table rows in the primary database can be uniquely identified

SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE

WHERE (OWNER, TABLE_NAME) NOT IN

(SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)

AND BAD_COLUMN = 'Y';

Prerequisites

• Disable Broker

• Data Guard protection mode must NOT be MAXIMUM PROTECTION

• FAL_SERVER and FAL_CLIENT must be set for all instances.

SHOW PARAMETER FAL

• STANDBY_FILE_MANAGEMENT must be AUTO

SHOW PARAMETER STANDBY_FILE_MANAGEMENT=AUTO

physru - run #1

You can run physru on either the Primary or the Standby. Set the variables below and run all subsequent runs of physru on the same server. My recommendation is to

export SYS_DBA=SYS # A user with SYSDBA privs

export PRIMTNS=ORCL1 # TNS alias for static Service on Primary

export STANTNS=ORCL2 # TNS alias for static Service on Standby

export PRIMUNQ=ORCL1 # DB_UNIQUE_NAME of Primary

export STANUNQ=ORCL2 # DB_UNIQUE_NAME of Standby

export TARGETV=12.1.0.2 # Target Version

The first run of Physru:

• Validates the environment before proceeding with the remainder of the script.

• Creates control file backups for both the primary and the target physical standby database.

• Creates Guaranteed Restore Points (GRP) on both the primary database and the physical standby database that enable fallback to the beginning of the process or to intermediate steps along the way.

• Converts a physical standby into a transient logical standby database.

./physru ${SYS_DBA} ${PRIM_TNS} ${STANTNS} ${PRIMUNQ} ${STANUNQ} ${TARGETV}

(Optional) Testing

You can take this opportunity to perform testing on the Logical Standby...

• Create a Guaranteed Restore Point

• Perform Testing

• Flashback to Guaranteed Restore Point

• Drop Guaranteed Restore Point

Start Logical Standby Apply

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

physru - run #2

The second run of Physru:

• Ensures the transient logical standby database has been upgraded to the target version and is not started in OPEN MIGRATE mode. This could take some time, monitor the DBA_LOGSTBY_LOG view for progress

• Ensures the transient logical standby database is current with the primary. This includes applying all changes that occurred to the primary database while the transient logical standby was being upgraded.

• Performs a switchover to the upgraded transient logical standby database. The script will not attempt the switchover until SQL Apply on the standby database lags the primary database by 30 seconds or less.

• After the switchover completes the database services can be started and the applications can reconnect.

• Performs a flashback of the original primary database to the initial Guaranteed Restore Point that was created earlier.

• Converts the original primary database into a physical standby database.

• Shuts down the new physical standby database.

./physru ${SYS_DBA} ${PRIM_TNS} ${STANTNS} ${PRIMUNQ} ${STANUNQ} ${TARGETV}

Mount the new Physical Standby database

Set the environment to point to the new ORACLE_HOME (installed ealier as part of 'Preparation Tasks')

sqlplus / as sysdba

startup mount

physru - run #3

The third and final run of Physru:

• Starts Redo Apply on the new physical standby database (the original primary database) to apply all redo that has been generated during this process, including any SQL statements that have been executed on the transient logical standby as part of the upgrade

• Waits until the physical standby database has been synchronized with the primary database, providing a periodic status of the progress. If the script times out simply restart the script

• When synchronized, the script offers the option of performing a final switchover to return the databases to their original roles, now on the updated software. This script will not attempt the switchover until:

• Redo Apply starts successfully and applies all redo through the upgrade process

• Redo Apply on the standby database lags the primary database by 30 seconds or less

• Removes all Guaranteed Restore Points created by physru.

Enable Broker

Licensing

Use of DBMS_ROLLING requires Active Data Guard

Bibliography