sqlplus / as sysdba
EXECUTE dbms_stats.gather_dictionary_stats;
exit
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/)
You should address all issues reported by preupgrade.jar before continuing.
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'.
PURGE DBA_RECYCLEBIN;
CREATE RESTORE POINT beforeUpgrade GUARANTEE FLASHBACK DATABASE;
. /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
dbua -silent -sid ${ORACLE_SID} -oracleHome ${OLD_HOME} \
-recompile_invalid_objects true \
-upgradeTimezone true -emConfiguration NONE -upgrade_parallelism 1
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 from memory;
create pfile from spfile;
shutdown immediate
startup
Ensure CPU_COUNT/PARALLEL_MIN_SERVERS is set to an appropriate value
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;
DROP RESTORE POINT beforeUpgrade;
Perform the following tasks approximately two weeks after the upgrade.
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;