Written by RaviKumar YV

Introduction:

 

Oracle databases need to be upgraded to latest version to introduce new features and also get required bug fixes released in higher environment. Database upgrade is unavoidable to any organization.

 

Oracle database upgrade has multiple methods with different requirements. Proper method has to be chosen which suits to the business needs. The most hindering part is downtime demanded by any upgrade method. Traditional export/import will stipulate huge downtime and also most commonly used DBUA or Manual upgrade require more downtime to upgrade metadata and also it has need of perfect prerequisites match.

 

To achieve Database upgrade in lesser time, we can make use of Logical standby in upgrade process. Here oracle high availability setup will be created to existing database. User will experience downtime only during switchovers in High availability setup. Logical standby can be removed once upgrade is completed.

 

Serial steps for Database upgrade

 

  • Create physical standby database using Data Guard setup for the existing database. Physical standby database will be same version as Production database version.

  • Convert the Physical standby database to Logical standby database. Physical standby database should be same version as primary database, hence it is converted to Logical standby database.

  • Upgrade the Logical standby database to Oracle 12c (12.1.0.2.0). Production database version will not be changed.

  • Perform switchover the database roles.

  • Production database will be now Logical standby database. Flashback it to physical standby database.

  • Shutdown Production database and start it with Oracle 12c (12.1.0.2.0) database version since Physical standby database require same database version.

  • Recover the database. It will apply the archive logs which got generated during logical standby database upgrade

  • Switchover again. It will make production database to latest version Oracle12c (12.1.0.2.0) as primary database.

 

Environment Setup:

 

  • Source Database version: Oracle 11g (11.2.0.4.0)

  • Target database version: Oracle 12c (12.1.0.2.0)

  • Source database name: v112

  • Temporary Logical standby database name: v112s

 

Step1: Connect to v112 database and set initialization parameters to get role of Primary database

 

Log_archive_config=’dg_config=(v112,v112s)

Log_archive_dest_2=’ service="v112s" LGWR ASYNC db_unique_name="v112s" valid_for=(online_logfile,primary_role)’

Log_archive_dest_state_2=’enable’

Fal_client=v112

Fal_server=v112s

db_recovery_file_dest='/u01/app/oracle/arch'

db_recovery_file_dest_size=7g

 

Step2: Create initialization parameter file for v112s with basic parameter and add below parameters

 

db_file_name_convert='/u01/app/oracle/oradata/v112/','/u01/app/oracle/oradata/v112s/'

db_name='v112'

db_unique_name='v112s'

fal_client='v112s'

fal_server='v112'

log_archive_config='dg_config=(v112,v112s)'

log_archive_dest_1='Location=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch'

log_archive_dest_2='service="v112" LGWR ASYNC db_unique_name="v112" valid_for=(online_logfile,primary_role)'

log_archive_dest_state_1='enable'

log_archive_dest_state_2='defer'

log_file_name_convert='/u01/app/oracle/oradata/v112/','/u01/app/oracle/oradata/v112s/'

db_recovery_file_dest='/u01/app/oracle/standby_log'

db_recovery_file_dest_size=7g

 

Step3: Create new password file for v112s or copy v112 password file and rename it to orapwv112s

 

Step4: Start the source database (v112) instance to nomount state

 

SQL>startup nomount pfile=initv112s.ora

ORACLE instance started.

Total System Global Area 221331456 bytes

Fixed Size 2251856 bytes

Variable Size 163578800 bytes

Database Buffers 50331648 bytes

Redo Buffers 5169152 bytes

 

Step5: Create static listener entry for v112s

 

(SID_DESC =

(GLOBAL_DBNAME = v112s)

(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = v112s)

)

 

Step6: Add tnsnames.ora file for source database (v112) and temporary logical standby database (v112s)

 

V112 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port>))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = v112)

)

)

 

v112s =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port>))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = v112s)

)

)

 

Step7: Perform Active duplicate command to create standby database from v112 (Standby database can be created in multiple ways, we follow here 11g new feature ‘Active database duplication’)

 

rman target sys/sys@v112 auxiliary sys/sys@v112s

connected to target database: V112 (DBID=2404776337)

connected to auxiliary database: V112 (not mounted)

 

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 25-SEP-15

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

.

.

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=891334791 file name=/u01/app/oracle/oradata/v112s/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=891334791 file name=/u01/app/oracle/oradata/v112s/users01.dbf

Finished Duplicate Db at 25-SEP-15

 

Step8: Open the database in read only state

 

Step9: At standby database, create standby redo log file, it will speed up log apply process

 

SQL> alter database add standby logfile '/u01/app/oracle/oradata/v112s/standby01.log' size 50m;

Database altered.

 

SQL> alter database add standby logfile '/u01/app/oracle/oradata/v112s/standby02.log' size 50m;

Database altered.

 

SQL> alter database add standby logfile '/u01/app/oracle/oradata/v112s/standby03.log' size 50m;

Database altered.

 

Step10: Activate media recovery. It will perform log apply at standby

 

SQL> recover managed standby database disconnect;

Media recovery complete.

 

Step11: Enable Flashback on Primary database and standby database

 

SQL> alter database flashback on;

SQL> select flashback_on from v$database;

 

Step12: Create restore point at Primary database and standby database

 

Primary Database:

 

SQL> create restore point before_upgrade guarantee flashback database;

Restore point created.

 

Standby Database:

 

SQL> recover managed standby database cancel;

Media recovery complete.

 

SQL> recover managed standby database disconnect;

Media recovery complete.

 

Conversion steps for Logical standby database

 

Step13: Stop the media recovery at standby database

 

SQL> recover managed standby database cancel;

Media recovery complete.

 

Step14: Build data dictionary at primary database for logical standby database

 

SQL> execute dbms_logstdby.build;

PL/SQL procedure successfully completed.

 

Step15: Convert physical standby database to logical standby database (ensure database is not in open state)

 

SQL> alter database recover to logical standby keep identity;

Database altered.

 

Step16: Restart the database, once it gets started logical standby role will be enabled

 

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

 

SQL>startup

ORACLE instance started.

Total System Global Area 221331456 bytes

Fixed Size 2251856 bytes

Variable Size 163578800 bytes

Database Buffers 50331648 bytes

Redo Buffers 5169152 bytes

Database mounted.

Database opened.

 

SQL> select database_role from v$database;

 

DATABASE_ROLE

----------------------------

LOGICAL STANDBY

 

Step17: Start the log shipping process at logical standby database

 

SQL> alter database start logical standby apply immediate;

Database altered.

 

Logminer process would have been started in the background

LOGMINER: Parameters summary for session# = 1

LOGMINER: Number of processes = 3, Transaction Chunk Size = 201

LOGMINER: Memory Size = 30M, Checkpoint interval = 150M

LOGMINER: SpillScn 0, ResetLogScn 0

Also you can notice archive log sequence got reset

 

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4

 

Step18: Check if the data dictionary build is completed by executing below query

 

SQL> select * from v$logstdby_state where state = 'LOADING DICTIONARY';

no rows selected

 

Upgrade Logical standby database to Oracle 12c (12.1.0.2.0) database version

 

Step19: Perform pre-requisites upgrade check by executing preupgrade.sql script. Implement the recommendations provided by Preupgrade script.

 

SQL> @/u01/app/oracle/product/12.1.0/rdbms/admin/preupgrd.sql

 

Check the preupgrade logs

 

1. Review results of the pre-upgrade checks:

/u01/app/oracle/cfgtoollogs/v112s/preupgrade/preupgrade.log

 

2. Execute in the SOURCE environment BEFORE upgrade:

/u01/app/oracle/cfgtoollogs/v112s/preupgrade/preupgrade_fixups.sql

 

3. Execute in the NEW environment AFTER upgrade:

/u01/app/oracle/cfgtoollogs/v112s/preupgrade/postupgrade_fixups.sql

 

Step20: Upgrade can be done either using DBUA or Manual upgrade. Here we followed manual upgrade shutdown logical standby database at Oracle 11g (11.2.0.4).

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

Step21: Set environment variables to Oracle 12c (12.1.0.2.0) home

 

[oracle@Server dbs]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/

[oracle@Server dbs]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@Server dbs]$ export ORACLE_SID=v112s

 

Step22: Start the database in upgrade mode using Oracle 12c (12.1.0.2.0) home

 

  1. Copy init file to Oracle 12c (12.1.0.2.0) home

     

  2. Create password file for v112s instance at Oracle 12c (12.1.0.2.0) home

     

[oracle@Server bin]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initv112s.ora /u01/app/oracle/product/12.1.0/dbs/

 

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 222298112 bytes

Fixed Size 2922760 bytes

Variable Size 163579640 bytes

Database Buffers 50331648 bytes

Redo Buffers 5464064 bytes

Database mounted.

Database opened.

 

SQL> exit

 

Step23: Start the manual upgrade process using catctl.pl as below

 

[oracle@Server admin]$ /u01/app/oracle/product/12.1.0/perl/bin/perl catctl.pl -n 4 -l /u01/app/oracle/log catupgrd.sql

Alert log entries:

Argument list for [catctl.pl]

SQL Process Count n = 4

.

.

catctl.pl version: 12.1.0.2.0

Oracle Base = /u01/app/oracle

Analyzing file catupgrd.sql

Log files in /u01/app/oracle/log

catcon: ALL catcon-related output will be written to /u01/app/oracle/log/catupgrd_catcon_16472.lst

catcon: See /u01/app/oracle/log/catupgrd*.log files for output generated by scripts

catcon: See /u01/app/oracle/log/catupgrd_*.lst files for spool files, if any

Number of Cpus = 1

SQL Process Count = 4

.

.

SQL> Rem =====================================================================

SQL> Rem Run component status as the last output

SQL> Rem Note: NO DDL STATEMENTS. DO NOT RECOMMEND ANY SQL BEYOND THIS POINT.

SQL> Rem Note: ACTIONS_END must stay here to get the correct upgrade time.

SQL> Rem =====================================================================

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

LOG FILES: (catupgrd*.log)

Grand Total Upgrade Time: [0d:1h:12m:13s]

 

Step24: Check the upgraded database

 

col comp_id format a10

col comp_name format a30

col version format a10

col status format a8

SQL> select substr(comp_id,1,15) comp_id,

substr(comp_name,1,30) comp_name,

substr(version,1,10) version,status

from dba_registry

 

COMP_ID COMP_NAME VERSION STATUS

--------------------------------------------------------------------------------------------------------------------------

XDB Oracle XML Database 12.1.0.2.0 VALID

CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID

CATPROC Oracle Database Packages and T 12.1.0.2.0 VALID

 

Step25: Start the log apply process at Logical standby which is now Oracle 12c (12.1.0.2.0) version. Primary still in Oracle 11g (11.2.0.4.0) version

 

SQL> alter database start logical standby apply;

Database altered.

 

Switchover from Logical standby to Primary

 

Step26: Switchover the roles. Now primary database will become Oracle 11g (11.2.0.4.0) Logical standby and other side will be Oracle 12c (12.1.0.2.0) primary database

 

1. At primary database execute below query

SQL> alter database commit to switchover to logical standby;

Database altered.
 

2. At standby database execute below query
 

SQL> alter database commit to switchover to primary;

Database altered.

 

3. Primary database become Standby database
 

SQL> select database_role from v$database;

 

DATABASE_ROLE

-----------------------------

LOGICAL STANDBY

 

SQL> show parameter db_unique_name

NAME TYPE VALUE

--------------------------------------------------------------------------------------------------

db_unique_name string v112
 

4. Standby database become Primary database
 

SQL> select database_role from v$database;

 

DATABASE_ROLE

---------------------------

PRIMARY

 

SQL> show parameter db_unique_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_unique_name string v112s

 

Step26: We cannot directly convert Logical standby database to physical standby database, hence flashback to guarantee restore point which was created prior to upgrade

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup mount

ORACLE instance started.

Total System Global Area 221331456 bytes

Fixed Size 2251856 bytes

Variable Size 163578800 bytes

Database Buffers 50331648 bytes

Redo Buffers 5169152 bytes

Database mounted.

 

SQL> flashback database to restore point before_upgrade;

Flashback complete.

 

SQL> alter database convert to physical standby;

Database altered.

 

Step27: Now primary database becomes Oracle 11g (11.2.0.4.0) physical standby database. i.e Logical to physical conversion

 

Step28: Shutdown database and start Physical standby database with Oracle 12c (12.1.0.2.0) binary

 

SQL> startup mount pfile=initv112.ora

ORACLE instance started.

Total System Global Area 222298112 bytes

Fixed Size 2922760 bytes

Variable Size 163579640 bytes

Database Buffers 50331648 bytes

Redo Buffers 5464064 bytes

Database mounted.

 

SQL> select database_role from v$database;

 

DATABASE_ROLE

------------------------------

PHYSICAL STANDBY

 

Step29: Enable Log shipping

 

SQL> alter database recover managed standby database using current logfile through next switchover disconnect;

Database altered.

 

All archive logs generated during DB upgrade will be applied to standby

 

Step30: After applying all logs standby database will be upgraded to Oracle 12c (12.1.0.2.0) version Perform switchover from physical standby to primary database

 

Step31: Perform switchover from Primary database to standby database

 

Current Primary database
 

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

 

It will convert primary database to physical standby database and shutdown the database.  It can be used in future.
 

Current standby database
 

SQL> alter database commit to switchover to primary;

Database altered.

 

SQL> alter database open;

Database altered.

 

SQL> select OPEN_MODE, PROTECTION_MODE from v$database;

 

OPEN_MODE PROTECTION_MODE

-----------------------------------------------------------------

READ WRITE MAXIMUM PERFORMANCE

 

Check the DB component status

SQL> col comp_id format a10

SQL> col comp_name format a30

SQL> col version format a10

SQL> col status format a8

SQL> select substr(comp_id,1,15) comp_id,

substr(comp_name,1,30) comp_name,

substr(version,1,10) version,status

from dba_registry;

 

COMP_ID COMP_NAME VERSION STATUS

---------------------------------------------------------------------------------------------------------------------

XDB Oracle XML Database 12.1.0.2.0 VALID

CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID

CATPROC Oracle Database Packages and T 12.1.0.2.0 VALID

 

Database has been upgraded to Oracle 12c (12.1.0.2.0) version and DB components are valid. Client failover settings can be configured in such a way that it will connect to primary always as below. Client can make connections to Logical standby when Primary database is in standby mode.

 

V112 = 
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=<primary database>)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=<Logical standby database>)(PORT=1521))
)
(CONNECT_DATA = (SERVICE_NAME= <service name>))
)

 

Summary:

 

Using temporary logical standby database can be upgraded from Oracle 11g to Oracle 12c in lesser time. Downtime occurred only during switchover from Primary database to Logical standby database and switchback to Primary database role.