Oracle 11.2.0.4 to 19c DB Upgrade

 

Step0: Linux should be minimum 7.9 , AIX should be minimum 7.1

Step 1: Check archive log generation size and request enough space to for archive location to keep archives atleast 7 days 

Step2:  request filesystem should be minimum 50GB

Step3: Install 19c software only and apply recent PSU patches.

Step4: Download preupgrade.jar from MOS 884522.1

 @19C_HOME/jdk/bin/java -jar $19C_HOME/rdbms/admin/preupgrade.jar TERMINAL TEXT

Log: @19C_HOME/cfgtoollogs/<ORACLE_SID>/preupgrade/preupgrade.log

take actions on recommend:

1. parameters manaully set

2. tablespace size increase

3. remove EM DB console config

4. remove OLAP catalog - SQL> @19C_HOME/olap/admin/catnoamd.sql

5. ignore APEX upgrade if APEX is not using

6. 

SQL>execute dbms_stats.gather_fixed_objects_stats;

SQL>execute dbms_stats.gather_dictionary_stats;

7. recompile invalid objects

SQL>@19C_HOME/rdbms/admin/utlrp.sql

SQL>set serveroutput on;

SQL>execute dbms_preup.invalid_objects;

8. empty recyclebin

SQL> purge dba_recyclebin;

9. Run pre-upgrade-fixups.sql script

SQL> @19C_BASE/cfgtoollogs/<ORACLE_SID>/preupgrade/preupgrade_fixups.sql

Step5: Create Flashback Guarantee restore point

SQL> select flashback_on from v$database; -- no need flashback enable ( some DB's required )

SQL> select name,open_mode,log_mode from v$database; -- archive enabled

set below parameter values:

SQL> show parameter recovery

NAME                                        TYPE            VALUE

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

db_recovery_file_dest                string        

db_recovery_file_dest_size        big integer        0


SQL> select * from v$restore_point; -- no rows

SQL> create restore point pre_upgrade guarantee flashback database;

SQL> col name for a20

 col guarantee_flashback_database

col time for a60

set lines 300

select name,guarantee_flashback_database,time from v$restore_point;


###################### UPGRADE TASKS ####################

Step6: Copy sqlnet.ora, listener.ora, tnsnames.ora, spfile files from 11G_HOME to 19C_HOME

Step7: update with 19C_HOMEpaths and set below parameter to 11 ( to allow old passwords ) in sqlnet.ora file.

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Step8: update the 19C_HOME path in listener.ora

Step9: Bring down the 11g database.

Step10: start the upgrade from 19C_HOME

SQL> startup upgrade

SQL> select name,open_mode,cdb,version,status from v$database,v$instance; -- status shows "open migrate"

SQL> exit;

Step11: Upgrade the DB components

@19C_HOME/perl catctl.pl -1 /home/oracle -n 8 catupgrd.sql & -- will run in background

output: 108 phases will complete.

monitor Logs: $ORACLE_BASE/cfgtoollogs/<ORACLE_SID>/upgrade* or $ORACLE_HOME/cfgtoollogs/<ORACLE_SID>/upgrade*

NOTE: 19C DB will shutdown after upgrade command completes.

Step12: Start the DB from 19C_HOME and check components.

SQL> startup;

SQL> select name,open_mode,cdb,version,status from v$database,v$instance; -- status shows OPEN

SQL> col comp_id for a10

col comp_name for a40

col version for a15

set lines 100

set pages 999

select comp_id,comp_name,version,status from dba_registry; -- status shows UPGRADED

Step13: Check the invalid objects and recompile

SQL> select count(*) from dba_objects where status='INVALID';

SQL> @?/rdbms/admin/utlrp.sql

SQL>select count(*) from dba_objects where status='INVALID';

Step14: Run postupgrade_fixups.sql

SQL> @19C_BASE/cfgtoollogs/<ORACLE_SID>/preupgrade/postupgrade_fixups.sql


###################### POST UPGRADE TASKS ####################

Step15: Check the timezone is 32 ( latest 42 )

SQL> select version from v$timezone_file;

NOTE: most of the time timezone is less than 32 for manuall upgrade steps. run below commands to make timezone 32 ( latest 42 )

SQL> shut immediate;

SQL> startup upgrade;

SQL> @?/rdbms/admin/utltz_upg_check.sql

SQL> @?/rdbms/admin/utltz_upg_apply.sql --> it will start DB automatically upgrade mode and after apply will restart in normal mode

check the timezone is now 32 or 42

SQL> select version from v$timezone_file;

Step16: run utluts.sql to check database components status from view dba_registry_log

SQL> @?/rdbms/admin/utlusts.sql TEXT --> sometimes java server and spatial will show invalid, continue below steps, after invalid recompile check once again component status

Step17: Run catuppst.sql to keep good performance

SQL> @?/rdbms/admin/catuppst.sql

Step18: Re-Run postupgrade_fixups.sql

SQL> @19C_BASE/cfgtoollogs/<ORACLE_SID>/preupgrade/postupgrade_fixups.sql

Step19: Check the invalid objects and recompile once again if found

SQL> select count(*) from dba_objects where status='INVALID';

SQL> @?/rdbms/admin/utlrp.sql

SQL>select count(*) from dba_objects where status='INVALID';

Step20: Re-run utluts.sql to check database components status from view dba_registry_log

SQL> @?/rdbms/admin/utlusts.sql TEXT  --> should shows all components are in VALID state.


###################### AFTER  UPGRADE  ####################

Step21: update 19C_HOME path in /etc/oratab

Step22: start the listener from 19C_HOME

Step23: Take rman full backup


###################### AFTER  1 week  ####################

Step24: Drop restore point

Step25: change parameter COMPATIBLE to '19.0.0'


###################### ROLLBACK ACTIONS ####################

Step21: 19C_HOME, shut down DB

SQL> shut immediate;

SQL>startup mount;

SQL> flashback database to restore point pre_upgrade;

SQL> shut immediate;


Step22: 11G_HOME, set 11g DB 

SQL> startup mount;

SQL> alter database open resetlogs;

SQL> drop restore point pre-upgrade;







Comments

Post a Comment

Popular Posts

AWR Report Analysis

Image
AWR report analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main things we general look to figure out issue. 1. 2.  3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.
Read more

Goldengate 19c Installation with issue resolution

Image
  Basic Installation of GoldenGate 19c in OEL6. Tested with below requirements: Step1: Vmware requirements:   For installing OEL6.8 and Oracle DB 12c  installation, please refer URL https://venuoracledba.blogspot.com/2020/08/oracle-12c-2-node-rac-setup-in-vmware.html     Step2: After OEL6.8 installation finished. We will use /opt for goldengate installation.   Step3: unzip GG software in folder. Here unzipped in gg_software folder.     Folder fbo_ggs_Linux_x64_shiphome will create. Step4:  Enable GUI by issuing x host + command in root user. and keep open this root terminal.   Step5:  Enable GUI by issuing x host + command in oracle user.   Move to software path   execute ./runInstaller and select database version. Selected 12c version for my environment. and click Next     provide GG home, oracle home and port number and click Next.     After verifying paths and information, Click Install   ...
Read more

Oracle 11g Physical Standy Setup

  Step1: take Binaries tar backup from Primary DB server tar -cvf /path/11.2.0.4.tar 11.2.0.4 Step2: untar Binaries tar back on Standby DB server tar -xvf /path/11.2.0.4.tar -C /path ###################### Binaries Cloning ################### Step3: run clone command on Standby DB server export ORACLE_HOME=<PATH> cd $ORACLE_HOME/clone/bin perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE='<PATH>' ORACLE_HOME='<PATH>' ORACLE_HOME_NAME='<ANY_NAME>' ###################### POST Binaries Cloning ################### Step4: run root scripts $ORACLE_BASE/oraInventory/orainstRoot.sh $ORACLE_HOME/root.sh ###################### Pre-requisites ################### Standby: Step5: update hostnames in listener and tnsnames files Step6: start the listener in standby Server Primary: Step7: check force logging enabled -- to make sys activities will logged in redo logs SQL> select force_logging from v$database; - NO ( default ) to enable force logging - S...
Read more

Oracle 12c 2-node RAC setup in VMware Player 15 -- Part01

Image
Tried to install Oracle 12c 2-node RAC for testing purpose but due to low Hardware configuration, ended up with Single Node RAC. I got lot of issues during Installation and tried to resolve them with my best knowledge. Please find below installation steps and troubleshooting. Main issues started from Step90. you can go through quickly. Requirements Most of the times, bridge option in VMWARE will not connect with host machine. I tried below option to use internet connection from host machine. We need to cross verify vmnet8 is NAT or not. Installation Steps Step1 launch VMware and click on New Virtual Machine Step2   select OEL iso Image Step3 Provide New Virtual Machine Name Step4 Provide HDD size and select single virtual disk. Step5  Select host setting for monitor and Network as NAT Step6 New Virtual Machine is ready for launch. Step7 OEL Installation starts automatically. Press Enter Step8 Skip Disk Checking Step9 Click Next Step10   Select langu...
Read more

Oracle to Postgres

Image
  Round 1: Created metadata for tables, sequences, packages Round 2: Created indexes Round 3: freeze column name to freezes to_date function updated GEOMETRY datatype , installed POSTGIS package to support this datatype Round 4: created triggers created Functions Migration: bash-4.2$ pwd /usr/edb/migrationtoolkit/etc bash-4.2$ cat toolkit.properties SRC_DB_URL=jdbc:oracle:thin:@localhost:1521:orcl SRC_DB_USER=hr SRC_DB_PASSWORD=hr TARGET_DB_URL=jdbc:edb://postoffline:5444/dev TARGET_DB_USER=enterprisedb TARGET_DB_PASSWORD=password bash-4.2$  -bash-4.2$ pwd /usr/edb/migrationtoolkit/bin -bash-4.2$ ls -lrt total 724 -rwxr-xr-x. 1 root root   2439 Dec 11 13:29 runJavaApplication.sh -rwxr-xr-x. 1 root root    544 Dec 11 13:33 runMTK.sh -rwxr-xr-x. 1 root root 729258 Dec 11 13:33 edb-migrationtoolkit.jar -bash-4.2$  ./runMTK.sh HR OUTPUT: Running EnterpriseDB Migration Toolkit (Build 55.7.0) ... Source database connectivity info... conn =jdbc:oracle:thin:@o...
Read more

Execution_Explain Plan

Whenever we get request for application slowness or sql query then we need to identify sql_id and to analyze sql_id, we need to check SQL execution/explain plan. 1. EXECUTION_PLAN: select id,plan_hash_value,operation, options,object_name,cardinality,cost,bytes,cpu_cost,io_cost,time,partition_start,partition_stop, object_node,other_tag,qblock_name,other_xml from dba_hist_sql_plan where sql_id='<SQL_ID>'; or select * from table(dbms_xplan.display_awr('<SQL_ID>')); EXPLAIN_PLAN: explain plan for <SQL_QUERY>; select * from table(dbms_xplan.display);
Read more

Active Session History Analysis

Image
ASH  analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main events we general look to figure out issue. RAC / Cloud RDS: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. Redo Generation: 14. 15. 16. 17. 18. 19.
Read more

General Queries

                                                                              Tablespace growth Size We can use view to check tablespace size greater than 80% RAC / NON-RAC / Cloud RDS: SELECT tbm.TABLESPACE_NAME,   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) as USED_SPACE_GB,   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_SIZE_GB,   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_FREE_SIZE_GB,   round(tbm.USED_PERCENT) as PERCENT_USEDFROM dba_tablespace_usage_metrics tbm      join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME w...
Read more

Fragmentation & Re-organisation

Total  Savings: set verify off column file_name format a50 word_wrapped column smallest format 999,990 heading "Smallest|Size|Poss." column currsize format 999,990 heading "Current|Size" column savings format 999,990 heading "Poss.|Savings" break on report compute sum of savings on report column value new_val blksize select value from v$parameter where name = 'db_block_size' / select file_name, ceil( (nvl(hwm,1)*&amp;&amp;blksize)/1024/1024 ) smallest, ceil( blocks*&amp;&amp;blksize/1024/1024) currsize, ceil( blocks*&amp;&amp;blksize/1024/1024) - ceil( (nvl(hwm,1)*&amp;&amp;blksize)/1024/1024 ) savings from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) order by savings desc / HWM query for all datafiles: col data_file_name for a50 col tablespace_name for a20 set lines 300 select tablespace_name, file_id, file_name DATA_FILE_NAME, Al...
Read more

OEM patches & vulnerabilities

Image
  Tools: Agent: agentpatcher OMS: omspatcher coherence/weblogic: optach 1. Agent JDK update. please refer the below oracle reference page Supported Java SE Downloads on MOS (oracle.com) 2. Removing log4j-core-2.8.2.jar validate jar file exist cd $OEM_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib $OEM_BASE/agent_13.5.0.0.0/oracle_common/jdk/bin/jar tvf log4j-core-2.8.2.jar | grep JndiLookup.class output 2974 Sun Apr 02 15:24:22 AEST 2017 org/apache/logging/log4j/core/lookup/JndiLookup.class stop OMS  $OEM_BASE/middleware/bin/emctl status oms -details  $OEM_BASE/middleware/bin/emctl stop oms -all remove log4j zip -q -d log4j-core-2.8.2.jar org/apache/logging/log4j/core/lookup/JndiLookup.class validate jar file exist $OEM_BASE/agent_13.5.0.0.0/oracle_common/jdk/bin/jar tvf log4j-core-2.8.2.jar | grep JndiLookup.class start OMS  $OEM_BASE/middleware/bin/emctl status oms -details ...
Read more