Cerberus ® - business approach to monitoring
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 889-5533.
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.
-
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
- Check:
IF you see just libodm12.soTHEN DO THIS:ls -l /home/oracle/db12c/lib/libodm12.so
ELSE if you see /home/oracle/db12c/lib/libodm12.so -> libnfsodm11.socd $ORACLE_HOME/lib
cp libodm12.so libodm12.so_stub
cp libnfsodm12.so libnfsodm12.so_stub
rm libodm12.so
ln -s libnfsodm12.so libodm12.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.
-
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.
-
To install XML into SYSAUX tablespace follow these:
Set into init.ora:
And bounce DB.java_pool_size =150M
dispatchers="(PROTOCOL=TCP) (SERVICE=<ORACLE_SID>XDB)"
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.