Manual Database Upgrade from 11.2.0.4 to 19c(19.16.0.0.0) step by step- Real Time
Summary
We will demonstrate an overview on manually upgrading Oracle database from 11.2.0.4 to 19.16.0.0.0 on Oracle Linux 8 64bit platform.
In this example, the source database version is 11.2.0.4. So we can go for a direct upgrade to
1) Installing Oracle 19.3.0.0 binaries
2) Executing the preupgrade jar tool
3) Performing the preupgrade actions
4) Upgrade the database
5) Perform the postupgrade actions
Note : There are lot of changes in 19c like – utl_file_dir, streams, oracle multimedia are de-supported,Please do check with your application team before going ahead with your upgrade plan.
Pre-Requisite for DB Upgrade 19c
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 Activity Before Upgrade
Select name,open_mode from v$database
Select banner from v$version;
col comp_name for a45
select comp_id,comp_name,status, version, modified from
dba_registry;
Show parameter recovery
Show parameter target
Set Recovery file Destination
alter system set db_recovery_file_dest='/backup/arch/fra' scope=both;
Set Recovery file Size
alter system set db_recovery_file_dest_size=25G scope=both;
set Retention period
alter system set db_flashback_retention_target=1440;
show parameter java_pool_size
alter system set java_pool_size = 150m scope=spfile;
show parameter processes
alter system set processes=350 scope=spfile;
archive log list
$ORACLE_HOME/rdbms/admin/emremove.sql script
from the target from 19c home
@/oracle/app/19c/dbhome/rdbms/admin/emremove.sql
flashback on
select flashback_on from v$database;
alter database flashback on;
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,owner from dba_objects where
status='INVALID' group by status,owner;
select owner,object_name,object_type,status from dba_objects
where status ='INVALID' and owner='SYS';
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;
check Java component is valid or not
if not Please follow below steps
select dbms_java.longname('TEST') from dual;
ORA-29516: Aurora
assertion failure: Assertion failure at eox.c:35 ;
execute
sys.dbms_registry.loaded('JAVAVM');
execute sys.dbms_registry.valid('JAVAVM');
alter system set "_system_trig_enabled" = false
scope=memory;
alter system set java_jit_enabled=false;
create or replace java system /
/oracle/app/oracle/product/11.2/dbhome_1/jdk/bin/ java -jar /oracle/app/19c/dbhome/rdbms/admin/preupgrade.jar -DIR /FRA
/oracle/app/product/11.2.0/dbhome/jdk/bin/
java -jar /oracle/app/19c/dbhome/rdbms/admin/preupgrade.jar -DIR /finarch_inst1/FRA
Steps to be follow as per Outcome from pre-upgrade log
1)
purge recycle bin "purge recyclebin;
2)
purge dba_recylebin;"
3)
Move system.aud$ table to other tablespace or
truncate sys.aud$
4)
SELECT count(*) FROM sys.aud$;
5)
truncate table sys.aud$;
Select owner,object_name ,object_type from dba_objects where
object_name like ‘AUD$%’;
JOB Queue Cleanup
alter system set JOB_QUEUE_PROCESSES=0 scope=both;
select count(*) from APEX_030200.WWV_FLOWS where id = 4000;
Select owner,object_name,object_type,status from dba_objects
where owner like ‘APEX’;
Select count(1) from apex_0302200.WWV_FLOWS WHERE ID =4000;
Unzip download apex and run below command
1. Download Oracle APEX
2. Unzip
3. Go into the directory
4. Connect to the Oracle Database with SQLcl or SQLPlus
5. Run 1 command:
6. CREATE TABLESPACE apex DATAFILE '/path/to/datafiles/apex01.dbf' SIZE 100M AUTOEXTEND ON NEXT 1M;
8. @apexins.sql APEX APEX TEMP /i/
select comp_name,version,status from dba_registry
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 Objects
?/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 11.2 database
shut immediate
Upgrade Activities- Implementation
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 Directory for upgrade
mkdir -p /oracle19c/WHILEUPGRADE1/
Database startup upgrade from 19c Binary
SQL>startup upgrade
select instance_name,status from v$instance;
Check status of database
select instance_name,status from v$instance;
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 &
or
Nohup /oracle/app/19c/dbhome/perl/bin /oracle/app/19c/dbhome/rdbms/admin/catctl.pl
-l /oracle/app/19c/whileupd -n 8
/oracle/app/19c/dbhome/rdbms/admin/catupgrd.sql &
Post Upgrade Activities
---------------------
startup database
sqlplus / as sysdba
startup
1) check invalid objects
select count(*),status from dba_objects where status='INVALID' group by status;
@?/rdbms/admin/utlrp.sql
select count(*),status from dba_objects group by status;
2) 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;
3) Run Postupgrade fixups sql ;
/FRA/preupgrade/postupgrade_fixups.sql
1) 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 !
@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
?/rdbms/admin/utlusts.sql TEXT
?/rdbms/admin/catuppst.sql
Recompile Invalid Objects
?/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, version, modified from dba_registry;
SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN comments FORMAT A30
COLUMN description FORMAT A60
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS
action_time,action,status,description,patch_id FROM sys.dba_registry_sqlpatch ORDER by
action_time;
No comments:
Post a Comment