Saturday, 22 August 2015

Upgrade the Oracle database to Oracle Database

Database SID: UPGR is database version is running and ready for upgrade. The new preupgrade check script preupgrd.sql which will examine UPGR database. This script is shipped with the new Oracle 12c home in /u01/app/oracle/product/
In this method the database will stay in place and doesn’t get moved to another location.

***  Steps to Upgrade from Oracle to Oracle  ***

1. Open Terminal and set  environment to 11g . I have . upgr script to set necessary environment for database.
$ . upgr                           
$ sqlplus / as sysdba

2. Run the new preupgrade check script preupgrd.sql, in environment – it will generate 3 files:
SQL> @/u01/app/oracle/product/

3. Verify the preupgrade.log and make necessary changes
$ less /u01/app/oracle/cfgtoollogs/UPGR/preupgrade/preupgrade.log

The database has the OLAP Catalog AMD component installed, and this component is no longer included in Oracle Database starting with Oracle Database 12c.
Remove the OLAP Catalog AMD component using the script from the Oracle Home:
SQL> commit;

The preupgrade log includes a message about moving audit data from system.aud$ to sys.aud$ because Oracle Label Security is installed.Move the AUD$ table now using the olspreupgrade.sql script from the Oracle Database 12c home from SYSTEM to SYS:

Prepare spfile for the 12c upgrade according to the output from preupgrade.log:
(Please note: Best Practice would be to edit the init.ora for the upgrade manually. You could do so – the way i propose here is just a shortcut avoiding manual edit steps)
SQL> create pfile from spfile;
SQL> alter system set processes=300 scope=spfile;

4. Gather dictionary stats prior to the upgrade:
SQL> EXECUTE dbms_stats.gather_dictionary_stats;

5. Execute the preupgrade_fixups.sql – it was created by preupgrd.sql in directory /u01/app/oracle/cfgtoollogs/UPGR/preupgrade

Please note that the preupgrade_fixups.sql script will still complain about PROCESSES being set too low. This is because I have used the ALTER SYSTEM command to adjust the parameter, but that adjustment will not take effect until the database is shutdown and restarted. Because I specified SCOPE=SPFILE, this parameter will be set correctly for the upgrade.

6. Shutdown the UPGR database:
SQL >shutdown immediate
SQL >exit

7. Copy spfile and password file into the Oracle 12c home’s dbs directory:
$ cp /u01/app/oracle/product/11.2.0/dbs/spfileUPGR.ora /u01/app/oracle/product/
$ cp /u01/app/oracle/product/11.2.0/dbs/orapwUPGR /u01/app/oracle/product/

Now  upgrade UPGR database to Oracle Database 12c using the new parallel upgrade scripts. Furthermore recompile and check for invalid objects before/after the upgrade.

8. Open terminal and set the environment point to 12. I have . upgr12 script to set necessary environment for database.
$ . upgr12
$ sqlplus / as sysdba

9. Bring the UPGR database into UPGRADE mode
SQL> startup upgrade
SQL> exit

10. Upgrade the UPGR database with the parallel upgrade script.Start the new parallel upgrade – it will be driven by a PERL script outside of SQL*Plus and execute in 4 parallel threads – in maximum (You could run with 8 parallel threads by specifying the parameter option -n 8).

$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catupgrd.sql

I have seen that as many as 73 phases listed – some can act in parallel, other require serial execution. This will take some time depending on your system. If you wonder about the RESTART phases: those happen if timing dependencies make it necessary to rerun a certain action. The logfiles will be written by default into the directory from which you started, $ORACLE_HOME/rdbms/admin
Once the upgrade is finished it will shutdown the database and in the next phase restart it in normal mode.

11. Finalize the upgrade with all required post upgrade steps. During this part finalize the upgrade with recompilation, postupgrade_fixups.sql and the time zone adjustment to TZ V18. Startup the database – post upgrade it is shutdown:
$ . upgr12
$ sqlplus / as sysdba

Startup the UPGR database and recompile everything:
SQL> startup

Execute the postupgrade_fixups.sql:
Adjust Time Zone settings – you may look into the scripts taken from MOS Note: 1509653.1 before executing them:

And database Upgrade done successfully.

1 comment :