12.1.0.2 Upgrade
Assumptions
No TDE (Transparant Data Encryption)No ASM (Advanced Storage Management)No GI (Grid Infrastructure)No RAC (Real Application Cluster)No Database VaultNo OLS (Oracle Label Security)No Oracle Spatial (SDO)No Oracle Warehouse Builder (OWB)No JSON-enabled Context search indexesOracle Text is not usedNo Statisics Tables are used (dbms_stats.create_stat_table)The current ORACLE_HOME is correct in /etc/oratabAPEX (if used) is already at 4.2.5 or aboveYou will be upgrading from 11.2.0.3.15 or 11.2.0.4 to 12.1.0.2 (with latest Release Update)Preparation Tasks
Gather Dictionary Stats...
NOTE: Gathering Dictionary Stats should be peformed 24 hours before the upgrade.sqlplus / as sysdba
EXECUTE dbms_stats.gather_dictionary_stats;
exit
Set Variables
Set the ${NEWVER} variable equal to the directory name appearing after /u01/app/oracle/product/ in the new ORACLE_HOME.
NOTE: If you have just installed the new ORACLE_HOME, then this variable is likely already set.export NEWVER=121020
Or...
export NEWVER=121020ORCL
Set the ${CURVER} variable equal to the directory name appearing after /u01/app/oracle/product/ in the old ORACLE_HOME...
export CURVER=$(echo $ORACLE_HOME | cut -f6 -d/)
Pre-requiste tasks
You should address all issues reported by preupgrade.jar before continuing.
Copy config files
Copy the password file
cp $ORACLE_HOME/dbs/orapw* /u01/app/oracle/product/${NEWVER}/dbs
Copy the network config files and update them to reflect new ORACLE_HOME...
export ONA=$ORACLE_HOME/network/admin
export NNA=/u01/app/oracle/product/${NEWVER}/network/admin
cat ${ONA}/listener.ora | sed -e"s/${CURVER}/${NEWVER}/g" >${NNA}/listener.ora
cat ${ONA}/sqlnet.ora | sed -e"s/${CURVER}/${NEWVER}/g" >${NNA}/sqlnet.ora
Check tnsnames.ora manually
Copy externaljob.ora...
Run this as 'oracle'...
echo cp $ORACLE_HOME/rdbms/admin/externaljob.ora /u01/app/oracle/product/${NEWVER}/rdbms/admin/externaljob.ora
...then cut & paste the output to run as 'root'.
Immediately Before Outage
Purge the Recyclebin
PURGE DBA_RECYCLEBIN;
Create a Guaranteed Restore Point
CREATE RESTORE POINT beforeUpgrade GUARANTEE FLASHBACK DATABASE;
Outage
Set OEM Blackout
Set Environment for the NEW (12.1) oracle home
. /home/oracle/.profile${NEWVER}
Set a variable to define the Old ORACLE_HOME and check it isn't the same as the current ORACLE_HOME...
export OLD_HOME=$(cat /etc/oratab | grep ${ORACLE_SID} | cut -f2 -d: )
echo ${OLD_HOME}
echo ${ORACLE_HOME}
if [ ${OLD_HOME} = ${ORACLE_HOME} ]
then
echo ERROR
else
echo Ready
fi
Run the Database Upgrade Assistant
dbua -silent -sid ${ORACLE_SID} -oracleHome ${OLD_HOME} \
-recompile_invalid_objects true \
-upgradeTimezone true -emConfiguration NONE -upgrade_parallelism 1
Check for Invalid Objects and Components
Upgrade Timezone
Upgrade Tables Dependent on Oracle-Maintained Types
To identify tables that need to be upgraded after the database upgrade...
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT DISTINCT owner, table_name
FROM dba_tab_cols
WHERE data_upgraded = 'NO'
ORDER BY 1,2;
Only if any rows are returned by the query above, you should run this...
SET SERVEROUTPUT ON
@utluptabdata.sql
Create SPFILE and PFILE
create spfile from memory;
create pfile from spfile;
shutdown immediate
startup
Ensure CPU_COUNT/PARALLEL_MIN_SERVERS is set to an appropriate value
Immediately After Outage
Post Upgrade Checks
Gather Optimizer Statistics
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;
NOTE: The Gather Stats job should run automatically overnight. If you have gathered baselines and are working in a short outage window it may be an option to skip this step.
DROP Guaranteed Restore Point
DROP RESTORE POINT beforeUpgrade;
Backup the Database
Two Weeks After Upgrade
Perform the following tasks approximately two weeks after the upgrade.
Gather Fixed Object Stats
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;