Upgrade 11.2.0.4 -> 12.1.0.1

ORACLE DATABASE UPGRADE 11204 TO 12101 VIA MANUAL METHOD

PREPARE FOR UPGRADE

1. Take DB backup2. Take ORACLE_HOME (O_H), OraInventory backup via OS level copy3. Set the old O_H path and run the utlrp.sql script to validate the invalid objects4. exec dbms_stats.gather_database_stats;5. exec dbms_stats.gather_dictionary_stats; ** To decrease the amount of downtime during upgrade, run gather optimizer statistics 6. exec dbms_stats.gather_fixes_objects_stats;

INSTALL 12101

1. Download and install 12101 into a different Oracle Home Location -> Once you unzip the 12101, go to database folder -> Create a new O_H location as this will be used in installation -> ./runInstaller -> In "Installation Option" use, "Install database software only" -> In "Installation Location" give thew new O_H path in "Software Location". Oracle home can be outside or inside this Oracle base
2. Run the pre-upgrade utility which reside under 12101 HOME -> Set old O_H -> Login as sysdba -> @12101_HOME/rdbms/admin/preupgrade.sql -> Resolve all "ERRORS", and take judicious decision to ignore "WARNINGS" in the log file generated for the above script -> Common is PURGE recycle bin before upgrade -> EXECUTE dbms_preup.purge_recyclebin_fixup; -> Increase PROCESSES to a value to atleast 300 -> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;
Restart the database and re-run the pre-upgrade utility to see zero "ERRORS" in log.
Run "preupgrade_fixups.sql" is available
3. Review and fix all the issues reported via above script4. Shutdown the DB, listener and if any EMC

START THE UPGRADE

1. Copy the DBS and NETWORK folder from old O_H to new O_H2. Set the environment variable O_H to new O_H location -> Add new O_H in the $PATH variable3. Startup the database in STARTUP UPGRADE mode -> In 12c the upgrade utility is run out of SQL prompt, so exit from the sql prompt4. Run the upgrade utility whihc upgrades the database -> cd $ORACLE_HOME/rdbms/admin -> $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql ** It can also be rnu with option -n 3 where "3" denotes the parallel degree ( maximum of 8 parallel threads) e.g. $ORACLE_HOME/perl/bin/perl catctl.pl -n 3 catupgrd.sql5. The database will be shutdown due to the above step.6. Cross check the new environmental variable7. Login as sysdba8. Startup normally

POST UPGRADE PART-1

1. The catuppst.sql script
The catuppst.sql script is run as part of the upgrade unless the upgrade returns errors during the process. Check the log file for "Rem BEGIN catuppst.sql" to verify that catuppst.sql ran during the upgrade process.
If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step. Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.
Run catuppst.sql as follows:
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catuppst -d '''.''' catuppst.sql
The log file catuppst0.log will be generated with the results of the post upgrade.

2. Recompile any remaining stored PL/SQL and Java code
Run catcon.pl to invoke utlrp.sql to recompile any remaining stored PL/SQL and Java code. Use the following syntax:
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
The log file utlrp0.log is generated with the results of the recompilations.
3. Run the post upgrade script -> SPOOL postupgrade.log -> @postupgrade_fixups.sql -> SPOOL OFFThe postupgrade_fixups.sql script generates three categories of information for your upgraded database: general warnings, errors, and informational recommendations.
4. Run utlu121s.sql to verify that all issues have been fixed.
-> @?/rdbms/admin/utlu121s.sqlThe log file utlu121s0.log will be generated with the upgrade results.
5. Verify that all expected packages and classes are valid. -> $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utluiobj -d '''.''' utluiobj.sql The log file utluiobj0.log will be generated with the results of the verification.
6. Exit SQL*Plus if you are still in it.
Note:
If you are upgrading a cluster database from releases 10.2, 11.1, or 11.2, then upgrade the database configuration in Oracle Clusterware using the following command:
$ srvctl upgrade database -d db-unique-name -o oraclehomewhere db-unique-name is the database name assigned to it (not the instance name), and oraclehome is the Oracle home location in which the database is being upgraded.
7. Your database is now upgraded to Oracle Database 12c.

POST UPGRADE PART-2

Reference: https://docs.oracle.com/database/121/UPGRD/afterup.htm#UPGRD60033

1. Check component statusspool /tmp/regInvalid.out set echo onset lines 80 pages 100select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status from dba_registry order by modified;
To query invalid objects, execute a SQL query similar to:select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status from dba_objects where status <> 'VALID'order by owner, type;
2. Change the compatibility parameter to 12101 -> alter syste, set compatible='12.1.0.1' scope=spfile;
3. Check the version details post upgrade -> select comp_name, version , status from dba_registry;
4. Bounce the database
5. Update the new O_H under /etc/oratab file
6.DST updates
If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade, then use the DBMS_DST PL/SQL package to upgrade the time zone file.
Oracle 12.1.0.1 has by default all RDBMS DST updates from DSTv1 to DSTv18 included in the software installation.
Follow steps in https://docs.oracle.com/database/121/NLSPG/ch4datetime.htm#NLSPG261 to update DST

Common issues in upgrading the database to 12.1.0.1

Reference : https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/troubleshooting-oracle-database-upgrade.html#GUID-474BFAA2-0CD6-4672-8A7A-BF640F4FBFB2

1. Error ORA-00020 Maximum Number of Processes Exceeded When Running utlrp.sql
2. If you do not run the Pre-Upgrade Information Tool before starting the upgrade, then the catctl.pl and catupgrd.sql scripts terminate with errors as follows:
ORA-00942: table or view does not existORA-00904: "TZ_VERSION": invalid identifierORA-01722: invalid number
3. ORA-01650: unable to extend rollback segment string by string in tablespace stringORA-01651: unable to extend save undo segment by string for tablespace stringORA-01652: unable to extend temp segment by string in tablespace string
To avoid these errors, set AUTOEXTEND ON MAXSIZE UNLIMITED for the SYSTEM and SYSAUX tablespaces

Page updated

Google Sites

Report abuse