19C_Upgrade_from_ 12c_TESTDB
Environment Summary : 12c Database running on Unix Enviroment(HP-UX) ,PROD-Active Node-EASYDB01 And Passive Node-EASYDB02 with Standby Database running on server Standby-EASYDB01 .Database is configured with wallet .So Database need to start with wallet key only
19c Database Binaries & Patches(PROD-Active Node-EASYDB01) -Pre requisite
-----------------------------------------------------------------
Download Oracle Binaries for 19c (HP-UX 64 bit) from edelivery.oracle.com
1 Required New mount for for oracle 19c binaries (150G) /U01
3 Create new home for 19c
mkdir -p /U01/app/product/19c/dbhome
4 Oracle 19c Binary copy at New Oracle Home
5 Extract 19c Binary at Oracle Home location
6 copy listener.ora,sqlnet.ora and tnsnames.ora from existing Home to new Oracle 19c Home
7 Compatable Version(Dont change compatibility parameter to higher version while upgrade)
8 install 19c with new binary with (19.17) patchset
cd /U01/app/product/19c/dbhome
./runInstaller" Online
9 Stats gather of Dictionary (SYS,SYSTEM)
to Minimize downtime during upgrade (it's take long time) it's online activity
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
19c Database Binaries & Patches(PROD-Passive Node-EASYDB02) -Pre requisite
-------------------------------------------------------------------
1 Required mount for for oracle 19c binaries (150G) /U01
2 Create new home for 19c
mkdir -p /U01/app/product/19c/dbhome
3 Oracle 19c Binary copy at New Oracle Home
4 Extract 19c Binary at Oracle Home location
5 copy listener.ora,sqlnet.ora and tnsnames.ora from existing Home to new Oracle 19c Home
6 Compatable Version(Dont change compatibility parameter to higher version while upgrade)
7 Install 19c with new binary
"cd /U01_bc/app2/product/19c/dbhome_1
./runInstaller"
19c Database Binaries & Patches(Standby-EASYDB01) -Pre requisite
------------------------------------------------------------
1 Required mount for for oracle 19c binaries 150G) /U01
2 Create new home for 19c
mkdir -p /U01/app/product/19c/dbhome
3 Oracle 19c Binary copy at New Oracle Home
Extract 19c Binary at Oracle Home location
4 "cp /oracle/app/product/12.2.0.1/dbhome_2`/network/admin tnsnames.ora listener.ora
/U01/app/product/19c/dbhome/network/admin"
5 install 19c with new binary with (19.17) patchset
"cd /U01/app/product/19c/dbhome
./runInstaller"
Pre-Requisite D-Day (Downtime Start Now) Total downtime 3:30 Hours
-------------------------------------------------------------------
1 Daily Routine activity must be completed before upgrade start
2 Full Database backup on tape/file system
(Before start Upgradaton activity full backup must be taken)
3 Production Database and Standby must be sync before start Activity
4 MRP must be stop before start upgradation activity (Standby database Intact)
5 Listerner stop at Old 12c home before startting upgrade activity
List of Activities-Before Upgrade
-----------------------------------
1 Set Recovery file Destination
(alter system set db_recovery_file_dest=' ' scope=both;)
2 Set Recovery file Size
(alter system set db_recovery_file_dest_size=100g scope=both;)
3 set Retantion period
(alter system set db_flashback_retention_target=1440;)
4 Set Recovery file Destination (Standby)
(alter system set db_recovery_file_dest=' ' scope=both;)
5 Set Recovery file Size (Standby)
(alter system set db_recovery_file_dest_size=100g scope=both;)
6 set Retantion period (Standby)
(alter system set db_flashback_retention_target=1440;)
7 Flashback must be on (Standby Database)
(alter database flashback on;)
8 create restore point First at (Standby) will help any rollback
create restore point before_upgrade_19c guarantee flashback database;
9 Database Components (all component must be valid) (select comp_name, version, status from dba_registry;)
10 Check materized View (need to copy script and drop)
"select distinct o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
DROP MATERIALIZED VIEW EASYUSER.AC_VIEW;
DROP MATERIALIZED VIEW EASYUSER.EASY_SILL"
11 Clear Audit Logs
Move system.aud$ table to other tablespace or truncate sys.aud$ SELECT count(*) FROM sys.aud$;
truncate table sys.aud$;
12 purge recycle bin
purge recyclebin;purge dba_recylebin;
13 Flashback must be on
alter database flashback on;
14 create restore point
create restore point before_upgrade_19c guarantee flashback database;
15 Check restore point Status
select scn, storage_size, time, name from v$restore_point;
16 Recompile Invalid Object ($ORACLE_HOME/rdbms/admin/utlrp.sql)
17 Object Status(select count(*),status,owner from dba_objects where status='INVALID' group by status,owner;)
18 generate preupgrade log
/U01/app/oraeasy/product/12.2.0.1/easyDB_home/jdk/bin/java -jar /U01_bc/app2/product/19c/dbhome_1/rdbms/admin/preupgrade.jar -DIR /backup/FRA
Follow-up step as suggested by preupgrade log
----------------------------------------------------------
13 Recompile Invalid Object $ORACLE_HOME/rdbms/admin/utlrp.sql
17 Execute Gather State
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
18 Run Preupgrade fixup
sql @/FRA/preupgrade/preupgrade_fixups.sql
18 Check Object Status
select count(*),status from dba_objects group by status;
19 shutdown database at old Oracle Home
sqlplus / as sysdba shutdown immediate
19C Upgrade Activity
-------------------
1 set Oracle NEW HOME
export ORACLE_HOME=/U01/app/product/19c/dbhome
2 set PATH
export PATH=$ORACLE_HOME/bin:$PATH
3 Create Directory for upgrade
mkdir -p /U01/upgrade_status/
5 database starup upgrade from 19c Binary
startup upgrade
or
startup mount
alter system set encryption wallet open identified by 'easydb4443343'
alter database open upgrade
6 check status of database select instance_name,status from v$instance;
7 wallet status (Wallet must be open during upgrade)
select * from v$encryption_wallet;
8 exit from sqlprompt sql>exit
8 start upgrade using catupgrd and tail logfile to monitor upgrade
nohup /U01/app/oraeasy/19c/dbhome/perl/bin/perl /U01/app/oraeasy/19c/dbhome/rdbms/admin/catctl.pl -l /U01/upgrade_status/ -n 8 /U01/app/oraeasy/19c/dbhome/rdbms/admin/catupgrd.sql &
Post Upgrade Activity
---------------------
1 startup database
"sqlplus ""/ as sysdba""
STARTUP MOUNT;
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "easydb1d5678";
ALTER DATABASE OPEN;
set pagesize 24 linesize 87
col name format a12
col STATUS format a12
col WRL_PARAMETER format a40
select name, STATUS, WRL_PARAMETER from v$database,v$encryption_wallet;
2 Check invalid object
select count(*),status from dba_objects group by status;
@?/rdbms/admin/utlrp
select count(*),status from dba_objects group by status;"
3 Create post upgrade restorepoint
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;"
4 Run Postupgrade fixups sql
/FRA/preupgrade/postupgrade_fixups.sql
6 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"
7 Post upgrade scripts " sta ?/rdbms/admin/utlusts.sql TEXT
sta ?/rdbms/admin/catuppst.sql"
8 Recompile Invali Object sta ?/rdbms/admin/utlrp.sql
Post upgrade checks
--------------------
1 Invalid Object Check
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');"
2 Check Database Component
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;"
3 Patch registry $ORACLE_HOME/OPatch/datapatch -verbose
4 Backup after Complete Activity -Full backup snap before handover to application
5 Database handover to Application team
6 check point call with customer
7 drop Restore Point
drop restore point before_upgrade_19c
8 CIS Hardening will do CIS Hardening as tested in NONPROD environment
Standby Database Startup (NEW ORACLE HOME)
-------------------------------------
1 set new Oracle Home Environment
export ORACLE_HOME=/U01/app/product/19c/dbhome
2 set PATH export
PATH=$ORACLE_HOME/bin:$PATH
3 set ORACLE SID
export ORACLE_SID=TESTDB
4 mount Standby database
startup mount
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "easydb1938678";
set pagesize 24 linesize 87
col name format a12
col STATUS format a12
col WRL_PARAMETER format a40
select name, STATUS, WRL_PARAMETER from v$database,v$encryption_wallet;
6 after confirmation from Customer team (Operation Started on production successfully)
Listener & MRP start on Standby to SYNC database
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Node Failover Testing
----------------------
2 su - oracle -c "/home/easyDB/pkgscript/stop_easyDB.sh" Stop database on Active Node
2 su - oracle -c "/home/easyDB/pkgscript/start_easyDB.sh" Start Database on Passive Node
Rollback Plan
-------------
1 Downgrading database to previous version using Flashback database (FALLBACK PLAN)
Note that data from any transactions that occur after the point in time to which the database is recovered are lost.
2 This is only practical when only the database upgrade has been done and no application data changes have occurred
3 Set New Home environment
" /U01/app/product/19c/dbhome
Shutdown immediate
startup mount
4 restore guaranteed point
flashback database to restore point before_upgrd19c;
Shutdown immediate
5 Set OLD home environment
/oracle/app/product/12.2.0.1/dbhome_2
startup mount
6 Open database using reset log
alter database open resetlogs;
7 Validate Registry View
select comp_name, version, status from dba_registry;
Oracle Database Catalog Views 12.2.0.0.1 Valid
Oracle Database Packages and Type 12.2.0.0.1 valid
8 Start Listener Services Listerner start for Application servers
9 Database Handover Application Team
No comments:
Post a Comment