Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Note: These tips were based on upgrades from 11.2.0.4 to 12.1.0.2 (Linux and Solaris ONLY)
OS user grid was the “owner” of the Grid infrastructure sw and OS user oracle was the “owner” of the database sw.
The following environment variables were created prior to upgrade:
1 2 3 4 | $GRID_ORACLE_HOME_11G $DATABASE_ORACLE_HOME_11G $GRID_ORACLE_HOME_12c $DATABASE_ORACLE_HOME_12c |
Tip #1.
Run cluster verify for grid/database and fix issues that cannot be ignored. The importance of cluster verify utility (cluvfy) cannot be overstated. Always pipe the results to a file so you can see all results in case the emulator window buffer you are using doesn’t retain it all.
Examples of cluster verify for Grid infrastructure:
1 2 | runcluvfy.sh stage -pre crsinst -n [node 1],[node 2] -verbose>/tmp/cluvfy_crsinst_12c.log runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome [11g grid OH] -dest_crshome [12c grid OH] -dest_version 12.1.0.2.0 -verbose>/tmp/cluvfy_additionalInfo_12c.log |
Examples of cluster verify for Oracle database:
1 | runcluvfy.sh stage -pre dbinst -n [host1],[host2] -verbose > /tmp/cluvfy_database_12c.log |
Tip #2.
Important OS parameters (Linux only)
These parameters were not sufficient for upgrade in the Linux upgrades I performed. I only assisted on the Solaris upgrade, after OS parameters had already been adjusted, so they are not remarked upon in this article.
1 2 3 4 5 6 7 8 9 | IN: /etc/security/limits.conf Added: oracle soft memlock unlimited oracle hard memlock unlimited IN: /etc/sysctl.conf Add/modify: kernel.shmmax=[number presented in cluster verify] kernel.shmall=[number presented in cluster verify] |
Tip #3.
Run the ORACHK utility to determine database upgrade readiness:
1 | orachk -u -o pre Verbose |
Tip #4.
If possible, use datapump to take a FULL logical backup of the database. The size of your database may make this unfeasible.
Use RMAN to take a full physical COLD backup. This is NON-optional. If you don’t do this, a catastrophic error that aborts the upgrade will leave you with a corrupted database and NO WAY to recover.
Tip #5.
Disable scheduled jobs (Only has to be done on single node)
Tip #6.
Disable ALL database level triggers BEFORE running dbua.
Tip #7 (Linux ONLY).
Reclaim memory (ALL nodes) before starting upgrade:
AS ROOT:
1 2 3 | free -m sync && echo 1 > /proc/sys/vm/drop_caches free -m |
Tip #8.
Copy important files from 11g Oracle home to 12c Oracle home as oracle user.
This is, quite possibly, the most important step. You MUST copy these files from the 11g OH to the 12c OH BEFORE invoking dbua.
ON [ALL NODES], perform the following list/copy comands:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | ls -l $DATABASE_ORACLE_HOME_11g/network/admin ls -l $DATABASE_ORACLE_HOME_12c/network/admin cp $DATABASE_ORACLE_HOME_11g/network/admin/tnsnames.ora $ORACLE_HOME_12c/network/admin cp $DATABASE_ORACLE_HOME_11g/network/admin/sqlnet.ora $ORACLE_HOME_12c/network/admin ls -l $DATABASE_ORACLE_HOME_11g/dbs ls -l $DATABASE_ORACLE_HOME_12c/dbs cp $DATABASE_ORACLE_HOME_11g/dbs/orapw* $ORACLE_HOME_12c/dbs/ cp $DATABASE_ORACLE_HOME_11g/dbs/*.dat $ORACLE_HOME_12c/dbs/ ls -l $DATABASE_ORACLE_HOME_12c/network/admin ls -l $DATABASE_ORACLE_HOME_12c/dbs |
Tip #9.
Empty the recycle bin and recompile invalid objects BEFORE invoking dbua.
Tip #10.
If your database is in archivelogmode, make sure the parameter db_recovery_file_dest_size is large enough to support all the archive log files that will be generated by the upgrade process.
Tip #11.
During the upgrade process I executed, the permissions of the Oracle OH were changed. This made access to the database problematic using the listener. It is possible that your upgrade may not do this. A simple way to test it is to copy the tnsnames.ora file from the Oracle OH to the Gris OH and try to connect to a user using sqlnet. If this works, then ignore the rest of this tip. To re-mediate this, I made a very simple change to the permissions. NOTE: This change also was required by a STIG.
— as root
1 | chmod 770 [Two (2) logical levels below $ORACLE_HOME_12c] |
— as oracle
1 2 3 4 5 | sudo su - su - oracle chmod 770 $ORACLE_HOME_12c chmod 770 [One (1) logical levels below $ORACLE_HOME_12c] chmod 770 $ORACLE_HOME_12c/network/admin/tnsnames.ora |
— as grid
1 2 3 | sudo su - su - grid cp $ORACLE_HOME_12c/network/admin/tnsnames.ora $GRID_HOME_12c/network/admin/ |
Tip #12.
UNDER NO CIRCUMSTANCES ARE YOU EVER TO RECURSIVELY CHANGE PERMISSIONS ON ANY ORACLE HOME.
Tip #13.
After successful database upgrade, the compatible parameter is left at the 11g level. This is to provide testing of any application to see if there are negative effects from the upgrade. You will not be able to take advantage of 12c features until you advance the parameter to 12c level. It is possible to advance the parameter forward without bouncing the database. However, it is NOT possible to reverse the process the same way. If you find that you must revert back to the 11g compatible level, you must first have performed a FULL hot backup at the 11g level. Reverting is done by using RMAN to restore/recover to the point in time BEFORE the compatible parameter was advanced.
Tip #14.
Re-enable scheduled jobs and database triggers and TEST.
