Environment Summary :
12c Database running on Unix Enviroment(HP-UX) ,PROD-Active Node-EASYDBSERVER And Passive Node-EASYDBFAILVERSERVER with Standby Database running on server Standby-EASYDBSTANDBYRSERVER
Upgrade Oracle DB from 11.2.0.2 10 11.2.0.4 (HP-UX) EASYDB
Complete Checklist for out-of-Place Manual Upgrade from Previous 11.2.0.N Version to the Latest 11.2.0.N Patchset (Doc ID 1276368.1)
Pre-Requiste Database Upgrade on EASYDBSERVER (Active Node-PROD)
--------------------------------------------------------
1) Space Required Space Required for New Oracle Binary (11.2.0.4 )- Installation on EASYDBSERVER mount /u01 - 100GB
3) Space Required Space Required for Backup 3 TB (temporary)
mount point as /backup ( EASYDBSERVER)
4) Space Required Extend Archive file System /EASYDBARCH=600G (in PROD and STANDBY)
5) Download Oracle Binaries download 11.2.0.4 patchset
6) create New Home Create new home for 11.2.0.4
7) unzip Binaries unzip Oracle Binaries at new Home 11.2.0.4
8) Install Binaries Install 11.2.0.4 in New Oracle Home 11.2.0.4
9) Apply patches Apply Latest availabe patches oct-21 11.2.0.4.220118 (33477185)
10) copy exiting listener ,tns and sqlnet to New Home cp tns and listner and spfile from old oracle Home to New 11.2.0.4 home
11) Need to Validate NLS Characterset Check that National Characterset ->
select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
12) Stats gather required for SYS and SYSTEM schema objects Execute Gather
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Pre-Requisite Database Upgrade on EASYDBFAILVERSERVER (Passive Node-PROD)
------------------------------------------------------------
1) Space Required Space Required for New Oracle Binary (11.2.0.4) - Installation on EASYDBFAILVERSERVER mount /u01 - 100GB)
3) Space Required Extend Archive file System / EASYDBARCH=600G (in PROD and STANDBY)
4) Download Oracle Binaries download 11.2.0.4 patchset
5) create New Home Create new home for 11.2.0.4
6) unzip Binaries unzip Oracle Binaries at new Home 11.2.0.4
7) Install Binaries Install 11.2.0.4 in New Oracle Home 11.2.0.4
8) Apply patches Apply Latest availabe patches oct-21 11.2.0.4.220118 (33477185)
9) copy exiting listener ,tns and sqlnet to New Home cp tns and listner and spfile from old oracle Home to New 11.2.0.4 home
10) Need to Validate NLS Characterset Check that National Characterset
select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
Pre-Requisite Database Upgrade on EASYDBSTANDBYRSERVER (Standby Node-STANDBY)
----------------------------------------------------------------
1) Space Required Space Required for New Oracle Binary (11.2.0.4) - Installation on EASYDBSTANDBYRSERVER mount /u01 - 100GB)
2) Space Required Space Required for Backup 3 TB (temporaty) mount point as /backup ( EASYDBSERVER)
3) Space Required Extend Archive file System /EASYDBARCH=600G (in PROD and STANDBY)
4) Download Oracle Binaries download 11.2.0.4 patchset
5) create New Home Create new home for 11.2.0.4
6) unzip Binaries unzip Oracle Binaries at new Home 11.2.0.4
7) Install Binaries Install 11.2.0.4 in New Oracle Home 11.2.0.4
8) Apply patches Apply Latest availabe patches oct-21 11.2.0.4.220118 (33477185)
9) copy exiting listener ,tns and sqlnet to New Home cp tns and listner and spfile from old oracle Home to New 11.2.0.4 home
10) Need to Validate NLS Characterset Check that National Characterset
select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET'; N DBA Scheduled
11) Initiate Backup at STANDBY location before upgrade activity
Complete back at STANDBY Side Before activity started no downtime required.
List of Activities-Before Upgrade
-------------------------------
1) Copy gathering script( utlu112i.sql and utltzuv2.sql )
cp $NEWORACLE_HOME/rdbms/admin/utlu112i.sql /tmp
cp $NEWORACLE_HOME/rdbms/admin/utltzuv2.sql /tmp
2) login as sysdba on current home
sqlplus / as sysdba
3) execute pre-script script to understand what else pending to do before upgrade
sqlplus / as sysdba ;
SQL> spool upgrade_info.log ;
SQL> @utlu112i.sql
SQL>spool off
4) Pre-upgrade logs output
run script which is recommended in pre-upgrade log to complete condition for pre-upgrade
5) Validate objects Check invalid objects login as
sqlplus / as sysdba ;
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
6) Check invalid objects
select owner,object_name,status from dba_objects where status='INVALID';
7) Stats gather required for SYS and SYSTEM schema objects Execute Gather State
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
8) Check database components (must be valid before upgrade)
SELECT comp_id, comp_name, version, status FROM dba_registry;
9) check PROD and STANDBY SYNC status before proceed upgrade activity, PROD and STANDBY database must be sync
10) STOP MRP on STANDBY Server
Downtime Start from Here onward
--------------------------------
1) After successful preupgrade scripts
stop listener on current home and copy create pfile for new oracle home
2) instance down on current version
sqlplus / as sysdba ;
SQL>shutdown immediate;
3) Cold Backup Proceed before Upgrade
Copy Datafile, Controlfile and pfile backup at /backup location
4) Change in pfile
Backup of PFILE and DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST
5) set new Oracle for make script
Set Environment Variable according to New Home:-
$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=
Note:- If ORACLE_BASE is not known, after setting Path towards 11g Oracle Home, execute 'orabase', which will point the location of base.
$ orabase
update the oratab entry according to new Oracle Home N Scheduled
Upgrade Activities On Production
-------------------
1) set New Oracle Home(11.2.0.4)
run @ora_11.2.4.env
and defer/stop replication
alter system set log_archive_dest_state_2=defer;
2) set script path
cd $ORACLE_HOME/rdbms/admin
3) startup database with upgrade option on new Oracle Home(11.2.0.4)
sqlplus / as sysdba ;
SQL> startup upgrade;
4) check database mode and Role before upgrade activity
select name,open_mode,database_role from v$database;
5) start upgrade process sqlplus / as sysdba
SQL>set echo on;
SQL>spool upgrade.log;
SQL>@catupgrd.sql ;
SQL>-spool off
6) Validate log after upgrade (go through with log output)
check if any error in logfile
7) Check invalid objects status
select count(1),owner,status from dba_objects where status='INVALID' group by owner,status;
Post Upgrade Activities
----------------------
1) start database after post upgrade
sqlplus /as sysdba
startup;
2) set Oracle script path
cd $ORACLE_HOME/rdbms/admin;
SQL> @utlu112s.sql
3)`validate Invalid Object Recompile Invalid Objects;
SQL>@utlrp.sql
4) start listener on new oracle home(11.2.0.4)
Configure and Start Listener.ora
5) check environment detail Crosscheck Environment Variables
6) create spfile create spfile from pfile
7) shutdown database and startup with pfile shutdown database ;
startup database using spfile
8) check database components database component status
set line 120;
column COMP_NAME format a35;
column VERSION format a10;
column status format a10;
select comp_id,comp_name,status, version, modified from dba_registry;
9) Database ready for Application testing
10) after confirmation from App team (all running good) Start MRP at STANDBY database
startup mount
select name,db_unique_name,database_role from v$database;
ALTER DATABASE RECOVER managed standby database disconnect from session
11) Verify PROD STANDBY Sync status .run it on production Database
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
Rollback Plan
--------------
1) Set Oracle New Home
echo $ORALCE_HOME,
Echo $ORALCE_SID
2) login as sys dba
sqlplus / as sysdba
3) shut down database
shut immediate
4) Copy backup to current datafile location
Copy Datafile, Controlfile and pfile backup from /backup location
5) Set Oracle OLD Home
Oracle Home need to set as 11.2.0.2
6) login as sys dba
sqlplus / as sysdba
7) startup database
startup
8) check database status on production
select name,open_mode,database_role from v$database;
9) listener start Start Listener Services
10) Handonve to Application for testing Application login testing along with menus
11) enable log archive dest (Archive shipment start) on production database and check sync status
alter system set log_archive_dest_state_2=enable;
To check Sync status
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
12) Set Oracle OLD Home as Oracle Home need to set as 11.2.0.2 and Start MRP and check archive appy successfully
startup mount
select name,db_unique_name,database_role from v$database;
ALTER DATABASE RECOVER managed standby database disconnect from session
