Cross platform Oracle Database Migration and upgradation from 12c to 19c

 Cross platform Oracle Database Migration and upgradation from 12c to 19c on New Build Server

This note describes the procedure used to perform a minimal downtime platform migration from existing HP-UX to Linux-Red Hat Enterprise Linux 8 (New Build Server) as well as a database upgrade from Oracle 12c to Oracle 19c .

Database (12.1.0.2)  Migration(Cross Platform)   from HPUX to RHEL   -Pre requisite 

Pre-Requisite  Phase-1

Red Hat Enterprise Linux 8 (Red Hat Enterprise Linux 8: 4.18.0-80.el8.x86_64 or later)

SSH Requirement (OpenSSH is installed on servers)

Check Kernel parameters using cat /etc/sysctl.conf  

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500 #/sbin/sysctl -p (to apply above parameter)

vi /etc/security/limits.conf 

oracle soft nofile 1024                                                                      

oracle hard nofile 65536                                                                     

oracle soft nproc 2047                                                                      

oracle hard nproc 16384                                                                     

oracle soft stack 10240                                                                    

 oracle hard stack 32768

install Linux packages which are required for ORACLE installation 

bc,binutils,elfutils-libelf,elfutils-libelf-devel

,fontconfig-devel,glibc,glibc-devel,ksh,libaio

,libaio-devel,libXrender,libX11,libXau,libXi,libXtst,libgcc,libnsl

,librdmacm,libstdc++,libstdc++-devel,libxcb,libibverbs,make,policycoreutils

,policycoreutils-python-utils,smartmontools,sysstat

User and group                                                                                                                                                            

groupadd -g 1001 oinstall
groupadd -g 1002 dba
groupadd -g 1003 oper
useradd -u 1001 -g oinstall -G dba,oper oracle                                                                                                     

set Oracle user password=passwd oracle

Set SELinux = permissive and Disable firewall                                                                                                     

vi /etc/selinux/config
SELINUX=permissive                                                                                                                                                       

# systemctl disable firewalld

Mount point for Oracle Binaries 12.1.0.2   and  19c     /oracle02   100G

Mount point for Datafile    /oradata   3.3 TB, /femarch_12c  200G ,  /femredo_12c 30G ,  /backup  800G   

Create Oracle Home Directory,Datafile  and grant Permission                                                                               

mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1                                                                             

chown -R oracle:oinstall /oracle02 

chmod -R 775 /oracle02                                                                                     

chown -R oracle:oinstall /oradata 

chmod -R 775 /oradata    

Set Profile for Oracle setup

download Oracle Binaries for 12.1.0.2 (Linux 64 bit) from edelivery.oracle.com

Copy Binary at RHEL mount point /u01/database

Install Oracle Binaries using Oracle Installer 

Apply Latest availabe patches Patch 34057742 jul-22

Migration Activity Phase-1 ( offline Activity In  Downtime Required)

1) Shutdown Database ,startup database in read only mode

2) RMAN backup (full) along with controlfile

3) Transfer RMAN backup from source to target database (NFS Mount point Required at both end)

4) Restore controfile file

5) Catalog RMAN Backup

6) Restore database

7) Cross platform Conversion rollback segment(SYSTEM and UNDO datafiles)

8) startup mount

9) alter database open reset logs;

10) Post migration Validate  database objects and  components

11) Listener start for application

12) Handover database to Application team for testing

Database Upgrade  from 12.1.0.2 to 19c  Phase-2

Pre-Requisite  Phase-2

1) download Oracle Binaries for 19c  (Linux 64 bit) from edelivery.oracle.com

2) Create new home for 19c

3) Oracle 19c Binary copy at New Oracle Home  

4) cp tns and listner and spfile from old oracle Home to New 19c home

5) Install 19c Binaries

6)Apply Latest patch on 19c (19.16.0.0.0)  N-1

List of Activities-Before Upgrade

Set Recovery file Destination  alter system set db_recovery_file_dest='/u01/fra' scope=both;

Set Recovery file Size alter system set db_recovery_file_dest_size=500g scope=both;

set Retantion period alter system set db_flashback_retention_target=1440;

flashback on alter database flashback on;

create restore point create restore point before_upgrade_19c guarantee flashback database;

Check restore point Status select scn, storage_size, time, name from v$restore_point;

Object Status select count(*),status from dba_objects group by status;

Materilized if any must be refreshed completed SELECT o.name FROM sys.obj$ o, 

sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; 

generate preupgrade log /oracle/app/oracle/product/12.2.0/dbhome_1/jdk/bin/

java -jar /oracle/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar -DIR /FRA

 

Steps to be follow as per Outcome from pre-upgrade log

purge recycle bin "purge recyclebin;

purge dba_recylebin;"

Move system.aud$ table to other tablespace or truncate system.aud$ 

SELECT count(*) FROM system.aud$;    truncate table system.aud$;

JOB Queue Cleanup alter system set JOB_QUEUE_PROCESSES=0 scope=both;

SELECT * FROM sys.dba_2pc_pending; "delete from sys.pending_trans$ where local_tran_id ='';

delete from sys.pending_sessions$ where local_tran_id ='';

delete from sys.pending_sub_sessions$ where local_tran_id ='';

commit;"

Remove OLAP Catalog sqlplus / as sysdba @$ORACLE_HOME/olap/admin/catnoamd.sql

uninstall Label Security sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/catnools.sql

Recompile Invalid Object sta ?/rdbms/admin/utlrp.sql

Execute Gather State EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Run Preupgrade fixup sql /oracle/preupgrade/preupgrade_fixups.sql

Check Object Status select count(*),status from dba_objects group by status;

shutdown 12c database shut immediate

Upgrade Activities

set Oracle NEW HOME export ORACLE_HOME=/oracle/app/oracle/product/19.0.0/dbhome_1

set PATH export PATH=$ORACLE_HOME/bin:$PATH

Create Direcory for upgrade mkdir -p /oracle19c/WHILEUPGRADE1/

database starup upgrade from 19c Binary startup upgrade

check status of database select instance_name,status from v$instance;

exit from sqlprompt sql>exit

start upgrade using catupgrd  and tail logfile to monitor upgrade 

nohup $NEW_ORACLE_HOME/perl/bin/perl $NEW_ORACLE_HOME/rdbms/admin/catctl.pl -l 

/oracle/WHILEUPGRADE1/ -n 8 $NEW_ORACLE_HOME/rdbms/admin/catupgrd.sql &

Post Upgrade Activities

startup database  sqlplus / as sysdba

check invalid objects  

"select count(*),status from dba_objects group by status;

@?/rdbms/admin/utlrp

select count(*),status from dba_objects group by status;"

Create post upgrade restore point ; "select flashback_on from v$database;

create restore point after_upgrade_pre_postfixup guarantee flashback database;

select scn, storage_size, time, name from v$restore_point;"

Run Postupgrade fixups sql ; /FRA/preupgrade/postupgrade_fixups.sql

TIMEZONE Upgrade;   "SELECT version FROM v$timezone_file;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

exit

cd $ORACLE_HOME/rdbms/admin/

@utltz_countstats.sql

cd $ORACLE_HOME/rdbms/admin/

@utltz_countstar.sql

cd $ORACLE_HOME/rdbms/admin/

@utltz_upg_check.sql

cd $ORACLE_HOME/rdbms/admin/

@utltz_upg_apply.sql"

Post upgrade scripts;  "sta ?/rdbms/admin/utlusts.sql TEXT

sta ?/rdbms/admin/catuppst.sql"

Recompile Invalid Object sta ?/rdbms/admin/utlrp.sql

Post Upgrade Checks

check Invalid Objects; "col object_name for a40

set lines 999

set pages 999

col object_name for a40

col owner for a30

select object_name,object_type,status,owner from dba_objects where status='INVALID';

col object_name for a40

set lines 999

set pages 999

col object_name for a40

col owner for a30

select object_name,object_type,status,owner from dba_objects 

where status='INVALID' and owner in('SYS','SYSTEM');"

Check Database Components; "col comp_name for a45

select comp_id,comp_name,status from dba_registry;

select * from dba_registry_sqlpatch;

set serverout on

exec dbms_qopatch.get_sqlpatch_status;"

datapatch ;   $ORACLE_HOME/Opatch/datapatch -verbose

Roll Back Plan

Downgrading database to previous version using Flashback database (FALLBACK PLAN)

Set New Home environment;  /oracle19c_bc/app1/oracle/product/19.0.0/dbhome_1; 

shutdown immediate; startup mount

restore guaranteed point;  flashback database to restore point before_upgrd19c; shutdown immedaite

Set OLD home environment; /oracle/app/oracle/product/12.2.0/dbhome_1; startup mount

Open database using reset log; alter database open resetlogs;

Validate  DBA Registery View; select comp_name, version, status from dba_registry;

Start Listener Services

Database Handover Application Team

Rollback Plan

Rollback Plan