Upgrade Oracle 11.2.0.3 Database to Oracle 11.2.0.4 Database

Request Demo

As we all know, 11g is officially out of support with no critical severity 1 patches being available without purchasing extended support. Luckily, 11.2.0.4 is still available on Oracle Cloud and Vast Edge has been migrating it's customers to OCI to help them take advantage of the extended support for 11.2.0.4 till Dec 2022. As we all know, 11g to 12c is a major upgrade that requires the database architecture to be transformed from regular databases to containers (CDB) and pluggable databases (PDB's). Although this multitenant architecture is great, it requires efforts to do the transformation without disrupting your business.

Vast Edge offers one of the most sophisticated and secures database migration, upgrade, and updates (ongoing patch management and minor updates). Vast Edge is a Global Oracle Cloud Partner and has been mentioned in Forbes for successfully migrating businesses to the cloud. Here is how Vast Edge performs the upgrades:

As we all know, Oracle does not support a direct upgrade from 11.2.0.3 to 12c, 18c, 19c, or the latest 20c versions (including autonomous databases). You have to be on 11.2.0.4 or higher to migrate to Oracle database 12c onwards. In addition to this 12.1 and lower versions only support offline encryption as online encryption was introduced by Oracle from 12.2 onwards. Hence, this is another bottleneck to do direct upgrades as Oracle cloud database requires all data to be encrypted for security reasons. In addition to this, the autonomous database takes security to another level where data is encrypted at transit and at rest.

There are multiple upgrade options available including golden gate (available for free on OCI marketplace), Oracle's zero downtime upgrade software tool, applying in-place patches for upgrade and selecting a new home to perform an install and then migrate data for a safer and mitigate risks while performing the upgrade. There's also an option to combine classic and integrated capture with golden gate gateway to go from 11.2.0.3 directly to 12c+ database versions. However, this process is more complex and requires higher technical skills and hours to perform the upgrade. In the following section, we are going to share with you some simple commands to upgrade from 11.2.0.3 to 11.2.0.4 (the least effort path to perform an upgrade that's compatible with Oracle Cloud). We advise that you perform this upgrade on prem as 11.2.0.3 can only be installed manually on OCI IaaS (compute VM's). Always remember one key point, i.e. Oracle cloud requires db to be encrypted and converting data to encrypted form is a time consuming job. Hence, we would advise that you perform the encryption on prem prior to migrating your 11.2.0.3 db. If you are already on 11.2.0.4, you can setup data guard to have your standby database to be encrypted on the cloud. Businesses can also accomplish this proven method of migration by leveraging golden gate to enable near zero downtime migration.

Our experts install the software in a separate home directory while creating a pfile from spfile. The specialists at Vast Edge runs a pre-upgrade tool to check the pre-requisites before running the actual upgrade so that no setback occurs in the later stages of the upgrade. On top of that, we create a restore point in case of any crisis. Further, to avoid any kind of disaster Vast Edge also creates a Guaranteed Restore Point.

Restore Oracle 11.2.0.3 Database to Oracle 11.2.0.4 Database and then Upgrade.

export ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_3
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID

Step 1: Create pfile according to your environment.

Step 2: Start database in no mount state.
SQL> startup nomount pfile='Location of parameter file';

Step 3: Restore the Control Files Connect with RMAN and restore control file from backup.
RMAN> restore controlfile from 'location';

Step 4: Mount the database and catalog the backup pieces from new location
RMAN> alter database mount;
RMAN> catalog start with 'location of backup';
RMAN> Report schema;

Step 5: Generate the SET NEWNAME FOR DATAFILE command.
For example: - SET NEWNAME FOR DATAFILE 1 TO 'LOCATION OF DATAFILE TO BE RESTORED';

Step 6: Restore the database
RMAN> Run{
ALLOCATE CHANNEL c1 device type disk; -----------------------? Allocate multiple channels
SET NEWNAME FOR DATAFILE 1 TO '/u01/oracle/oradata/prod/system.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/oracle/oradata/prod/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/u01/oracle/oradata/prod/sysaux.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/u01/oracle/oradata/prod/users.dbf';
restore database;
switch datafile all;
release channel c1;}

Note: We can do a recovery preview to see the minimum scn we need to do recovery will get the minimum SCN needs to me recover.
rman >recover database preview;

Step 7: Start the recover based the scn you got.
rman> recover database until scn 23207925635;

Step 8: Connect through sqlplus and change the location of redo logs.
SQL>Alter database rename file 'old location' TO 'new location';

Step 9: Shutdown database;
edit undo PARAMETER
UNDO_MANAGEMENT='MANUAL'
alter database open reset log upgrade;

Step 10.1: Create undo tablespace undotbs datafile '+DATAC1/undotbs.dbf' size 5G AUTOEXTEND ON NEXT 5m MAXSIZE 31G;
CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '+DATAC1/tempnew_01.dbf' SIZE 5g autoextend on next 10m maxsize unlimited;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
DROP TABLESPACE TEMP including contents and datafiles;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Step 10.2: Shutdown immediate;

Edit pfile and make changes to parameter

UNDO_MANAGEMENT='AUTO'
UNDO_TABLESPACE='UNDOTBS'

Step 11: START Database
Alter database open upgrade;
SQL> select name,open_mode,log_mode,flashback_on,switchover_status from v$database; STEPS TO UPGRADE DATABASE

Step 12: Need to run precheck script before upgrade and fix the issues.
SQL> @$ORACLE_HOME/RDBMS/ADMIN/utlu112i.sql

Step 13: Run the ungrade script. (refer upgrade.log)
SQL> @$ORACLE_HOME/RDBMS/ADMIN/catupgrd.sql

Step 14: To check the upgrade (refer check_upgrade.log)
SQL> startup;
SQL> @$ORACLE_HOME/RDBMS/ADMIN/utlu112s.sql

Step 15: Need to run below script and database no need to be in upgrade mode.
SQL> @$ORACLE_HOME/RDBMS/ADMIN/catuppst.sql

Step 16: To recompile all the invalid objects after upgrade.
SQL> @$ORACLE_HOME/RDBMS/ADMIN/utlrp.sql Check Database Components:
col action_time for a30
col BUNDLE_SERIES for a15
col NAMESPACE for a10
col comments for a30
select * from dba_registry_history;
col comp_id for a10
col comp_name for a40
col version for a12
col status for a12
select comp_id, comp_name, version, status from dba_registry;

About Vast Edge - Simplified Cloud Solutions

Founded in the year 2004, Vast Edge is a leading IT Consulting Company and Global Service provider of Business Intelligence, big data analytics, cloud ERP, IoT platform, enterprise backup, and disaster recovery, Blockchain, Cassandra, AI/ML, and Integration solutions. We perform Application modernization, follow continuous change management, and implement advanced practices while working on the key areas of enhancing methodologies of project development.

Get a Free Assessment from our cloud experts.

chat
Hello! 👋 How can we help you today?