Oracle 12.1 upgrade

Oracle Remote Database Administration,
Monitoring and Support

  • SATS Remote DBAis dedicated to optimizing database uptime and performance, and to reducing the cost of IT ownership. Our Like Clockwork RDBA service saves you time, recourses, continuity, and helps you to ally IT tasks with the business goals of your organization. Many other companies chose SATS for our dependability, cost-effectiveness, and expert technological support. SATS RDBA advantages help our customers achieve costs savings of up to 75% while increasing the quality of DBA coverage.
  • SATS Remote DBA could be a one stop service provider for all this platforms:
  • Oracle, PostgreSQL, Hadoop, MS SQL, MySQL, Mongo DB

For specific inquiries please contact SATS RDBA Services or call 650 288-1701.

PRACTICAL UPGRADE GUIDE to ORACLE 12c (12.1) Version

PROBLEM CASE

As of October 2013, production error-free upgrades to Oracle 12c are becoming hot projects among SATS clients' community. This document goes over most common venue of upgrading which takes minimal time and is repeatable with positive results. It doesn't cover upgrade to pluggable database type and actually plugging into new Container-type database in 12c. We speak about doing normal upgrade for existing 11.2 database. There other upgrade scenarios from 8i, 9i, 10g, 11.1 are also available but are still barried down inside my technical notes. I will put them up at some point as needed. Database taken for upgrade was running 11.2.0.3 on Linux Red Hat 6.2. It did not have Java and XML configured, as it was not needed by business. Size of the database was roughly 8TB. Hardware - Dell box with 24 CPUs and 1TB or RAM. This numbers are important because upgrade took 1 hour of CPU time exactly, with database being down. Plan for 2 hours with post/pre upgrade steps. Time doesn't include the creation or Oracle media tarball - this was done outside of the scope.

1.Prepare Oracle installed tarball:

Simple step - may use any low-key box with Xwin access and follow general guidance of running.
Oracle Installer to create a Software Only option installation.
So we got the 2.7GB tarball file: 2717660328 ora1201_x64_norac.tar.gz
Copy this tarball into /home/oracle (which was our $ORACLE_BASE).

2. Preparation steps

While in 11g version, increase sysaux to 1GB (500MB will be fully used!)
alter database datafile '/db/oradata/sysaux01.dbf' resize 1000M;
run @?/rdbms/admin/utlrp.sql - this to fix any invalids in data dictionary.
shutdown all running oracle dbs which are to be upgraded and which use the same $ORACLE_HOME. oracle (/home/oracle/db11g)

3. Set binaries and clone configuration

As root:


    make file /etc/oraInst.loc with this contents:
inventory_loc=/home/oracle/oraInventory
inst_group=dba
also do: chmod 777 /etc/oraInst.loc if/when created one.

As oracle:


    mv old $ORACLE_HOME out of the way:
    mv db11g db11g.11203
    mv oraInventory oraInventory.11203
    gunzip ora1201_x64_norac.tar.gz
    tar xvf ora1201_x64_norac.tar
    rm /home/oracle/db12c/dbs/*
    cp /home/oracle/db11g.11203/dbs/* /home/oracle/db12c
    

Note there are no changes to init.ora at this step - keep the 11.2 version as is.


    cp /home/oracle/db11g.11203/network/admin/listener.ora /home/oracle/db12c/network/admin
    cp /home/oracle/db11g.11203/network/admin/tnsnames.ora /home/oracle/db12c/network/admin
    cp /home/oracle/db11g.11203/network/admin/sqlnet.ora /home/oracle/db12c/network/admin
    cp .bash_profile .bash_profile.11g

correct .bash_profile to reflect 12c /home/oracle/db12c instead of 11g and reconnect back as oracle.
Now relink binaries as usual:


    /home/oracle/db12c/bin/relink

4.Configure direct NFS if it is being used, if not - skip this step.


  1. Set oradism for dNFS as root:
    
        chown root:dba /home/oracle/db12c/bin/oradism
        chmod 4550 /home/oracle/db12c/bin/oradism
        chmod +g /home/oracle/db12c/bin/oradism
        chmod +s /home/oracle/db12c/bin/oradism
        ls -l /home/oracle/db12c/bin/oradism
        
  2. Check:
    
        ls -l /home/oracle/db12c/lib/libodm12.so
    
    
    IF you see just libodm12.soTHEN DO THIS:
    
        cd $ORACLE_HOME/lib
    cp libodm12.so libodm12.so_stub
    cp libnfsodm12.so libnfsodm12.so_stub
    rm libodm12.so
    ln -s libnfsodm12.so libodm12.so
    ELSE if you see /home/oracle/db12c/lib/libodm12.so -> libnfsodm11.so
    THEN DO NOTHING as above has been done already.
    END IF;

5. Upgrade itself:


    SQL> connect / as sysdba
    SQL> startup upgrade
    SQL> exit;

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

Note: upgrade runs for 1 hour exactly. At the end there will be this message, quote:
"*** WARNING: ERRORS FOUND DURING UPGRADE ***
Due to errors found during the upgrade process, the post
upgrade actions in catuppst.sql have not been automatically run.
*** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***
1. Evaluate the errors found in the upgrade logs (*.log) and determine the proper action.
2. Execute the post upgrade script as described in Chapter 3 of the Database Upgrade Guide.
Indeed there will be logs in the local directory (?rdbms/admin)


    -rw-r--r-- 1 oracle dba  5652485 Oct  8 22:41 catupgrd3.log
-rw-r--r-- 1 oracle dba 5104443 Oct 8 22:41 catupgrd2.log
-rw-r--r-- 1 oracle dba 4768922 Oct 8 22:41 catupgrd1.log
-rw-r--r-- 1 oracle dba 13657269 Oct 8 22:41 catupgrd0.log

Lets just ignore them for now as they are all due to the missing XDB and Java in the original 11.2 database.
- set compatible to 12.1.0.1 and restart the database into normal mode.
Than run @?/rdbms/admin/catuppst.sql -- takes another 15 min.
than run @utlrp.sql as above.


6. Post-upgrade:

I quote the documentation: "Oracle XML DB is now a mandatory component of Oracle Database. You cannot uninstall it, and there is no option not to include it when you create Oracle Database. It is automatically installed when you create a new database or (if not existing already) when you upgrade an existing database to Oracle Database 12c Release 1 (12.1.0.1)".
I wish so, "automatically installed during upgrade" would be a working promise. But it is not. So lets fix it. This step 5 is totally optional if your original 11g database already had XDB configured and working.

  1. Install Java and XML into the upgraded database:
    For reference I follow the docs [ID 457279.1] and [276457.1] from metalink. No need to look inside though, as here is an essense with some modifcations per 12c. Just run this script to build a java into the database. Run as SYS.
    
        select obj#, name from obj$ 
    where type#=28 or type#=29 or type#=30 or namespace=32;
    @?/javavm/install/initjvm.sql
    select count(*), object_type from all_objects
    where object_type like '%JAVA%' group by object_type;
    @?/xdk/admin/initxml.sql
    select count(*), object_type from all_objects
    where object_type like '%JAVA%' group by object_type;
    @?/xdk/admin/xmlja.sql
    select count(*), object_type from all_objects
    where object_type like '%JAVA%' group by object_type;
    @?/rdbms/admin/catjava.sql
    select count(*), object_type from all_objects
    where object_type like '%JAVA%' group by object_type;
    @?/rdbms/admin/catexf.sql
    select count(*), object_type from all_objects
    where object_type like '%JAVA%' group by object_type;

    Check what we got with this "scriptik" :

    
        col comp_name format a35 heading Comp_name
    col status format a10 heading Status
    col version format a10 heading Version
    select comp_name, status, version from DBA_REGISTRY where comp_name='JServer JAVA Virtual Machine';
    COMP_NAME STATUS VERSION
    ---------------------------- ------ -----------
    JServer JAVA Virtual Machine VALID 12.1.0.1.0

    If anything not like the above - than verify against original metalink documents referenced above. My run was perfect and hence I continue onto installing XDB component.

  2. To install XML into SYSAUX tablespace follow these:
    Set into init.ora:
    
        java_pool_size =150M
    dispatchers="(PROTOCOL=TCP) (SERVICE=<ORACLE_SID>XDB)"
    And bounce DB.

    
        SQL> connect / as sysdba
    SQL> alter user xdb account unlock;
    SQL> alter user ojvmsys account unlock;
    SQL> alter user xdb identified by xdb;
    SQL> @?/rdbms/admin/catqm_int.sql XDB SYSUAX TEMP NO

At this point we are Done. Recheck all components with the "scriptik" above.


    Comp_name                           Status     Version
    ----------------------------------- ---------- ----------
    Oracle Expression Filter            VALID      12.1.0.1.0
    Oracle XML Database                 VALID      12.1.0.1.0
    Oracle Database Catalog Views       VALID      12.1.0.1.0
    Oracle Real Application Clusters    OPTION OFF 12.1.0.1.0
    Oracle Database Packages and Types  VALID      12.1.0.1.0
    JServer JAVA Virtual Machine        VALID      12.1.0.1.0
    Oracle XDK                          VALID      12.1.0.1.0
    Oracle Database Java Packages       VALID      12.1.0.1.0
    

Questions/Comments? - feel free to contact Cerberus (which is me) on RDBA.net forums under Oracle DBA corner.