Database Upgrade from 12.2.0.1 to 19.17.0.0
Environment Summary :
12c Database running on Unix Environment(HP-UX) ,PROD-Active Node-EASYDBSERVER And Passive Node-EASYDBFAILVERSERVER with Standby Database running on server Standby-EASYDBSTDSERVER
EASYDB Production Database Upgrade (12.2.0.1 --> 19.17.0.0 ) Servers:-EASYDBSTDSERVER
Phase - I Downtime Required: No
--------------------------------
1. Download Oracle 19C Binaries along with Release Database Release Update: 19.17.0.0.221018 (34419443) for HP_UX IA64 and Ensure checksum of downloaded file matches with Oracle Support site.
2. Check Oracle Inventory (oraInventory) and OINSTALL Group Requirements (For upgrades, Oracle Universal Installer (OUI) detects an existing oraInventory directory from the /var/opt/oracle/oraInst.loc file, and uses the existing oraInventory).
3. Check Configure the Oracle Software Owner Oracle) Set the default file mode creation mask (umask) to 022 in the shell startup
4. Check user and group -->Oracle recommends that you create groups and user accounts required for your security plans before starting installation. Installation owners have resource limits settings and other requirements. Group and usernames must use only ASCII characters.
5. Check the Xclock setup and display setting to execute run installer.
6. Check upgrades your existing database to a higher version or release --> ./orachk -u -o pre.
7. Ensure cron jobs do not run during installation.
8. Check shared memory kernel parameters (shmmni, shmseg, and shmmax) determine if any changes are necessary for your system
# kctune -v shmmni
# kctune -v shmseg
# kctune -v shmmax (refer Kernal parameter sheet)".
9. Identify Deprecated parameters and features in 19c, review whether it is in use for current DB and prepare mititgation plan (refer Obsolete parameter).
10. Identify Desupported parameters and features in 19c, review whether it is in use for current DB and prepare mitigation plan.
11. Identify default values changes for DB parameters in 19c, review whether it is in use for current DB and prepare mitigation plan.
12. Check for the SQLNET.ALLOWED_LOGON_VERSION Parameter and modify the value to 10 before Upgrade. (Tested in UAT).
13. Identify Password version for all users and take appropriate action if required ( No Action performed in UAT) (10,11,12).
14. Review Minimum and Maximum for Parallel Execution Servers and prepare Post Upgrade plan
15. Check Dependencies on Network Utility Packages.( Application team to come back after testing ).
16. Check any tables dependent on Oracle Maintained Types and prepare post upgrade plan.
17. Check whether there are tables with SDO_GEOMETRY ( If yes Apply patch Patch 25293022). ( SELECT * from MDSYS.SDO_DIST_UNITS;
SELECT * from MDSYS.SDO_AREA_UNITS;
18. Check any triggers enabled for DDL and prepare steps for disable before upgrade.
19. Enable parameter (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE) to capture SQL plan baselines.
20. Ensure OS and Patches requirements are met ( Refer OS Requirements sheet).
21. Ensure Kernel Parameter requirements are met ( Refer Kernel Parameters sheet).
22. Ensure SWAP is set to 16 GB.
23. Ensure /tmp has alteast 1 GB space.
24. Check mount point for oracle19c Binaries /oracle19c_EASYDB 150G.
25. Unset ORACLE_HOME, ORACLE_BASE, ORACLE_SID, TNS_ADMIN, ORA_CRS_HOME and ORA_NLS10 after logging in as oracle.
26. Install Oracle 19c in in New Home ( ORACLE_HOME=/oracle19c_EASYDB/app/19c/EASYDB_home) in DR .
27. Appy Patch 19.17.0.0.221018 (34419443) Oracle Home DR -EASYDBSTDSERVER.
28. Prepare Oracle 19c Home for Upgrade in DR -EASYDBSTDSERVER.
29. Prepare EASYDB_19c.env file for Oracle 19c Home.
30. Evolve SQL Plan Baseline and Disable parameter (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE).
31. Run the Pre-Upgrade tool and perform appropriate actions as per the output.
32. Run dbupgdiag.sql and verify the output.
33. Modify the wallet as "Auto-Login" using Oracle Wallet Manager. if wallet is configured
34. Gather Dictionary stats (EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;), One day in Advance.
35. Disable backup schedule for the duration of DB Upgrade.
36. Disable Anti Virus scan for the duration of DB Upgrade.
38. Verify Materialized View Refreshes are Complete Before Upgrade.
41. Resolve Outstanding Distributed Transactions Before Upgrade.
42. Purge DBA Recyclebin.
43. Ensure Recovery file size=100G must be defined (alter system set db_recovery_file_dest_size=100g scope=both;).
44. Ensure Recovery file Destination must be defined (alter system set db_recovery_file_dest=' ' scope=both;).
45. Ensure Db flashback Retention Period=1440 (alter system set db_flashback_retention_target=1440;).
46. Database must be in Archive mode.
49. Database Components (all component must be valid) select comp_name, version, status from dba_registry;.
Phase - I Preupgrade Task D-day before upgrade.(Standby Database)
1.Bring down the Standby Database
recover managed standby database cancel (before start database back need to stop MRP).
2. Database Backup on File System .
Phase - II Downtime for PRODUCTION STANDBY SYNC only, no impact on operational Activity : Activity at DR only ( Physical standby Convert to Snapshot Mode) Total : 4:30 Hours.
1. select status,instance_name,database_role,open_mode from v$database,v$Instance;.
2. select thread#,max(sequence#) from v$archived_log group by thread#;
3. show parameter db_recovery_file_dest.
4. select flashback_on from v$database;c(database must be in flashback mode);
alter database flashback on;
5. recover managed standby database cancel;
6. shutdown database;
7. startup mount.
8. alter database convert to snapshot standby;
9. alter database open;
10. select status,instance_name,database_role,open_mode from v$database,v$Instance;
11. Database will be open in read,write mode.
Phase - II Downtime Required : activity At Production only
1. Bring down the Application
2. Bring down listeners.
3. Database must be in Archive mode.
4. Create Guaranteed Restore point
Create restore point before_upgrade_19c guarantee flashback database;
5. Check restore point Status --> select scn, storage_size, time, name from v$restore_point;
6. Recompile Invalid Objects "$ORACLE_HOME/rdbms/admin/utlrp.sql".
7. Check Invalid Objects " select count(*),status,owner from dba_objects where status='INVALID' group by status,owner;"
8. Generate Pre-upgrade logs "/oraeasydb/easydb/12.2.0.1/easydb_home1/jdk/bin/java -jar /oracle19c_easydb/app/19c/easydb_home/rdbms/admin/preupgrade.jar -DIR /backup/FRA".
Phase - II-A Downtime Required : execute script as suggested by pre-upgrade log.
1. Recompile Invalid Objects "$ORACLE_HOME/rdbms/admin/utlrp.sql".
2. Execute Gather state " EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;"
3. Eexute Pre-upgrade fixup--> "/backup/FRA/preupgrade/preupgrade_fixups.sql".
4. Check Invalid Objects " select count(*),status,owner from dba_objects where status='INVALID' group by status,owner;"
5. Disable trigger (take active list of triggers).
6. Stop EASYDB Database "sqlplus / as sysdba " shut immediate".
7. copy pfile/spfile,passwordfile along with sqlnet,listener.ora and tnsname.ora from old home to new home. DB Team.
8. change listener.ora Oracle home Accordingly.
Phase - II-B Downtime Required: (Upgrade Activity)
1. Open New puttly for New Oracle Home (19c).
2. Set new ORACLE_HOME and ORACLE_SID using easydb_19c.env. (sh easydb_19c.env)
3. startup upgrade ( from 19c Oracle Home) "sqlplus / as sysdba" after sql prompt " startup upgrade)
4. Check database Stataus must be Open Migrate -->select instance_name,status from v$instance;
5. select * from v$encryption_wallet;
6. make directory for upgrade logs "mkdir -p /oracle19c/whilwupgsbcg" .
7. Check process to optimize upgrade process, if process is below 400, need to update--> alter system set processes=400 scope=spfile; change to Original after upgrade.
8. nohup /oracle19c/app/oraeasyt/19c/dbhome/perl/bin/perl /oracle19c/app/orauat/19c/dbhome/rdbms/admin/catctl.pl -l /oracle19c/whilwupgsbcg/ -n 8.
Phase - II-C Downtime Required : (After Upgrade Pre-Checks)
1. Open New puttly for New Oracle Home (19c).
2. Check Database Upgrade Components -->cat upg_summary.log;
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;.
3. Check Invalid Objects
select count(*),status,owner from dba_objects where status='INVALID' group by status,owner;
4. Compile Invalid Objects "$ORACLE_HOME/rdbms/admin/utlrp.
5. after Compile again check Invalid Objects " select count(*),status,owner from dba_objects where status='INVALID' group by status,owner;"
6. Create Guaranteed Restore point after upgrade
Create restore point post_upgrade_19c guarantee flashback database;
7. Run Post Upgrade Fixup--> "/backup/FRA/preupgrade/postupgrade_fixups.sql".
Phase - II-D Downtime Required: (Upgrade time Zone)
1. Check existing time Zone:- "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;
2. "Scrip to be run Sequenly to upgrade time zone 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".
3. Check time Zone after upgrade putput sould be 32 :-
SELECT version FROM v$timezone_file;
4. Post Upgrade Script dba_registry_log and displays the upgrade results-->
?/rdbms/admin/utlusts.sql TEXT.
5. Post Upgrade script post-upgrade script performs remaining upgrade actions-->
?/rdbms/admin/catuppst.sql.
6. ?/rdbms/admin/utlrp.sql.
7. Check Database Components and sqlplatches Status -->
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;".
8. Patch Registry-->
$ORACLE_HOME/OPatch/datapatch -verbose.
9. Password parameter must be exclusive -->
show parameter password.
10. Handover Application for Complete testing.
11. Both Restore point Need to be drop After Successful Upgrade after consulting with Customer.
21. Drop Restore points -->
drop restore point before_upgrade_19c" ,
drop restore point post_upgrade_19c;
Phase - III (Rollback Plan - Part 11) Revert snapshot database into Physical standby (Original stage).
1. login Oracle 19c Home as sysdba --> shutdown database .
2. Open New puttly .
3. run evironment script for Old Home 12c --> .
4. database open in mount stage in Old Home (12) startup mount .
5. database convert to physical standby mode ----> alter database convert to physical standby;.
6. shutdown database again and starup with mount mode.
7. select status,instance_name,database_role,open_mode from v$database,v$Instance;
8. database role must be physical standby mode.
9. start MRP to recover pending archive logs --> alter database recover managed standby database disconnect from session;
10. Check database SYNC Status (DC and DR must be SYNC) after above activity.
11. Mock Upgrade Activity Completed successfully.
Phase - IV (Rollback Plan - Part 11) if Issue in revert to physical standby.
1. Login in 19c Oracle home.
2. startup mount in exclusive mode.
3. copy spfile/pfile as backup.
4. drop database.
5. startup database in nomount.
6. restore controlfile.
7. mount database.
8. restore database.
9. recover database ;
10. Primary and Standby database SYNC.
=============Obsoleted parameters=================
Remove initialization parameters that Oracle has Deprecated/obsoleted or removed.
O7_DICTIONARY_ACCESSIBILITY
optimizer_adaptive_features
utl_file_dir
Desupported
1. The MAX_CONNECTIONS attribute of the LOG_ARCHIVE_DEST_n initialization parameter,for Oracle Data Guard redo transport is Desupported.
2. The EXAFUSION_ENABLED initialization parameter is Desupported.
3. Oracle Streams is Desupported.
Rollback Plan-A-Failure During Upgrade OR Facing Issue in Upgraded Version Before Releasing APP TEAM
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. This is only practical when only the database upgrade has been done and no application data changes have occurred.
1) Set New Home environment
cd /oracle19c/app/product/19c/dbhome
Shutdown immediate
startup mount
2) restore guaranteed point
flashback database to restore point before_upgrd19c;
Shutdown immediate
3) Set OLD home environment
cd /oracle/app/product/12.2.0.1/dbhome_2
startup mount
4) Open database using reset log
alter database open resetlogs;
5) Validate DBA Registery 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
6) Start Listener Services
Listener start for Application servers
7) Database Handover Application Team
Application Services start
8) start archive log sync
start archive log sync
No comments:
Post a Comment