Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Jun 3, 2015

Oracle 12c for Pro Newbies: Plug and Play (Part 2)

What if my organization already has a non-CDB 11g database and now it wants to unleash the potential of 12c pluggable databases?

Oracle 12c Guide

In such a scenario we will not create a database however, instead upgrade the existing one. So, let’s get started with that.

Upgrading a single instance database version 11.1.0.6 to 11.1.0.7 to 12.1.0.1 non-CDB


In the last post, we learnt how to create a 12c CDB and a PDB from the seed database. We also visited some important aspects while working with the pluggable databases. Next question is - what if my organization already has a non-CDB 11g database and now it wants to unleash the potential of 12c pluggable databases? In such a scenario we will not create a database however, instead upgrade the existing one. So, let’s get started with that.

Here is an example: One of my friends, Mr. Ravi Sevta, CIO of The MotleyData Inc. (A Fictitious Company) had to gather information around the pluggable databases as the board members of the company had shown interest in this technology. Ravi reached out to me with a single instance test database called orcl running 11g (11.1.0.6) to be upgraded to a 12c pluggable database.

Ravi’s Question: “Hey Ankur, so this is our machine and a test database. Now help me upgrade it to a pluggable database. First give me some background behind this.”

My Answer: “Hi Ravi, funny you should ask – I’ve been writing a few blogs on this topic that you should check these out, while I check this machine. You can find them on TriCore Solutions website. Oracle 12c for Pro Newbies : The Installation, Oracle 12c for Pro Newbies: Pluggable Database Architecture and  Oracle 12c for Pro Newbies: Plug and Play (Part 1)

Ravi: “Ok, Ankur. I’ll go through this. Give me a couple of hours to get back to you.”

After 3 hours…

Ravi goes through the links. He needs few hours with the test machine he had provided me to implement everything he has learnt so far.

After 3 hours…

Ravi is back with the machine with cdb12ct CDB database and a pdb1t pluggable database attached to it. Ravi says – “Let’s upgrade this orcl and attach it to cdb1t.”

Me: “Roger that!

So Ravi, first we need to check Oracle My Support Doc ID: 1516557.1 (Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA)

Let’s see if our version of database can be directly upgraded to 12c. Oops! We can’t. The only versions which support a direct upgrade to 12c are:”

Source Database

Target Database

10.2.0.5

12.1.x

11.1.0.7

12.1.x

11.2.0.2 and Higher

12.1.x

Ravi: “So what do we do now?”

Me: “Let’s go to the indirect upgrading path for 11.1.0.6”

The path is as below:

Source Database

Upgrade Path

Target Database

11.1.0.6

11.1.0.7

12.1.x

 

Ravi: “Ok, so we will first upgrade our orcl database to 11.1.0.7. Am I right?”

Me: “Yes, and let’s see how to do that.”

“On Oracle My Support, we have Doc ID 800550.1 (11.1.0.7 Patch Set - Linux x86 64bit README) which will help us in doing it. Clause 7.4 of the document has provided with the patch number 6890831 which needs to be downloaded for this upgrade.”

============================================

 

Patch: 6890831 downloaded

 

============================================

 

We will ensure for the version of ORACLE_HOME to be 11.1.0.6 as a prerequisite for this patch by the commands below:

SQL> select banner from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

PL/SQL Release 11.1.0.6.0 - Production

CORE    11.1.0.6.0      Production

TNS for Linux: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 - Production

 

[oracle@12ctst OPatch]$ pwd

/u01/app/oracle/product/11.1.0/db_1/OPatch

[oracle@12ctst OPatch]$ ./opatch lsinventory -all

Invoking OPatch 11.1.0.6.0

 

Oracle Interim Patch Installer version 11.1.0.6.0

Copyright (c) 2007, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.1.0/db_1

Central Inventory : /u01/app/oraInventory

   from           : /etc/oraInst.loc

OPatch version    : 11.1.0.6.0

OUI version       : 11.1.0.6.0

OUI location      : /u01/app/oracle/product/11.1.0/db_1/oui

Log file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2015-03-05_17-32-29PM.log

 

Lsinventory Output file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2015-03-05_17-32-29PM.txt

 

--------------------------------------------------------------------------------

List of Oracle Homes:

  Name          Location

   OraDB12Home1         /u01/app/oracle/product/12.1.0.1/db_1

   OraDb11g_home1         /u01/app/oracle/product/11.1.0/db_1

 

Installed Top-level Products (1):

 

Oracle Database 11g                                                  11.1.0.6.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.


Clause 7.5 asks to check for the timestamp version. If it is 4, no action is required.

SQL> select version from v$timezone_file; 

   VERSION

----------

   4

No action required for this timestamp version.

It is always advisable to have a contingency plan.

So, before we proceed for this patching, always ensure that we have a full backup of the database, existing ORACLE_HOME, and existing inventory. We are already having a full database backup. Let’s backup our ORACLE_HOME. These backups will give us a way to go back to the current state if patching fails mid-way.

tar cvfpz ohomebkp11g.tar.gz $ORACLE_HOME > ohomebkp11g.err

tar cvfpz oinvebkp11g.tar.gz /u01/app/ oraInventory > oinvebkp11g.err

Let’s unzip the patch p6890831_111070_Linux-x86-64.zip. This will create a directory Disk1 on the unzip location with all the patch contents. runInstaller script in this Disk1 will help us install this patch. Although you can install the patch in interactive mode, here I’m demonstrating silent install.

[oracle@12ctst Disk1]$./runInstaller -silent -responseFile /home/oracle/Desktop/Disk1/response/orclupgrade.rsp -force

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB.   Actual 921 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 2502 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-03-05_08-01-24PM. Please wait ...[oracle@12ctst Disk1]$ Oracle Universal Installer, Version 11.1.0.7.0 Production

Copyright (C) 1999, 2008, Oracle. All rights reserved.

You can find the log of this install session at:

 /u01/app/oraInventory/logs/installActions2015-03-05_08-01-24PM.log

.................................................................................................... 100% Done.

Response file contents:

RESPONSEFILE_VERSION=2.2.1.0.0

UNIX_GROUP_NAME=oinstall

FROM_LOCATION=/home/oracle/database/stage/products.xml

ORACLE_HOME=”/u01/app/oracle/product/11.1.0/db_1”

ORACLE_HOME_NAME=ORCLHOME

TOPLEVEL_COMPONENT={"oracle.patchset.db","11.1.0.7.0"}

SHOW_INSTALL_PROGRESS_PAGE=true

SHOW_REQUIRED_CONFIG_TOOL_PAGE=false

RESTART_SYSTEM=false

OUI_HOSTNAME=12ctst.motleydata.com

COMPONENT_LANGUAGES={"en"}

##Added in Response File due to Error 1 below##

METALINK_USERNAME=”pseudomail@gmail.com”

METALINK_PASSWORD=”pseudomail”

 

But we faced several errors while doing this. Description of the same is as below:

Error 1: SEVERE: Values for the following variables could not be obtained from the command line or response file(s):

  METALINK_USERNAME (MetaLinkUsername)

Silent install cannot continue.

Solution: Refer Metalink Doc ID 762790.1 (Silent Install of Patch Set 11.1.0.7 Fails Requiring A METALINK_USERNAME) which advises to add bogus METALINK_USERNAME and password to avoid this error.

Error 2: SEVERE: OUI-10029: You have specified a non-empty directory to install this product. It is recommended to specify either an empty or a non-existent directory. You may, however, choose to ignore this message if the directory contains Operating System generated files or subdirectories like lost+found.

Solution: Use –force while running the installer.

 

Error 3: SEVERE: OUI-10136: An Oracle Home with name OraDb11g_home1 already exists at location /u01/app/oracle/product/11.1.0/db_1. Please specify another location for Oracle Home.

Solution: Remove the ORACLE_HOME entry from inventory.xml else face error

This brings our home upgraded to 11.1.0.7.

Ravi: Great! Let’s open the database now.

And before I could make my point Ravi started the database…

[oracle@12ctst Disk1]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 5 21:28:01 2015

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 609624064 bytes

Fixed Size                  2162120 bytes

Variable Size             461374008 bytes

Database Buffers          142606336 bytes

Redo Buffers                3481600 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

Process ID: 15884

Session ID: 170 Serial number: 5

 

“What happened!” says Ravi.

Me: “You can’t start the database here. You have just upgraded the ORACLE_HOME and not the database. So, wait… we need to get the database upgraded. And then I shut down the database and started it in STARTUP UPGRADE mode.”

[oracle@12ctst dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Mar 6 11:19:08 2015

 

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup upgrade

ORACLE instance started.

 

Total System Global Area 609624064 bytes

Fixed Size                  2162120 bytes

Variable Size             461374008 bytes

Database Buffers          142606336 bytes

Redo Buffers                3481600 bytes

Database mounted.

Database opened.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Let’s run the Database Upgrade Assistant (dbua)

[oracle@12ctst dbs]$ dbua

Database upgrade has been completed successfully, and the database is ready to use.

The following document describes important behavioral changes from previous database releases:

/u01/app/oracle/product/11.1.0/db_1/assistants/dbua/doc/DefaultBehaviorChanges.html


And this upgrades our orcl database to 11.1.0.7. But we faced an error ORA-00845 during this upgrade. The error is in screen shot below.


Oracle 12c Guide

To troubleshoot this error, we added some space to the temp file system as below:

[root@12ctst]# mount -t tmpfs shmfs -o size=2048m /dev/shm/

You have new mail in /var/spool/mail/root

 

[root@12ctst]# cat /etc/fstab

LABEL=/                 /                       ext3    defaults        1 1

LABEL=/boot             /boot                   ext3    defaults        1 2

tmpfs                   /dev/shm                tmpfs   defaults        0 0

devpts                  /dev/pts                devpts  gid=5,mode=620  0 0

sysfs                   /sys                    sysfs   defaults        0 0

proc                    /proc                   proc    defaults        0 0

LABEL=SWAP-sda3         swap                    swap    defaults        0 0

shmfs                   /dev/shm                tmpfs   size=2048m      0 0

Restart the system after this.

[oracle@12ctst]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Mar 6 12:06:59 2015

 

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select open_mode from v$database;

 

OPEN_MODE

----------

READ WRITE

 

SQL> select banner from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

PL/SQL Release 11.1.0.7.0 - Production

CORE    11.1.0.7.0      Production

TNS for Linux: Version 11.1.0.7.0 - Production

NLSRTL Version 11.1.0.7.0 - Production

So, this is our upgraded database version 11.1.0.7.0. Let’s proceed to 12c upgrade and revisit Doc ID 1516557.1.

First run the pre-upgrade installation tool by copying the scripts preupgrd.sql and utluppkg.sql from $ORACLE_HOME/rdbms/admin/ to a local destination and run on 11g database. 

SQL> @/home/oracle/preupgrd.sql 

Please go through the recommendations from this script log and act accordingly. Run the preupgrade fixup script (path in the log) for helping you get some things done automatically. E.g. In our case, the fixup script helped in getting the recycle bin empty while MANUAL ACTION SUGGESTED was for dictionary statistics. So, I ran the fixup script and manually gathered the statistics.

SQL> @/home/oracle/preupgrade_fixups_1.sql
SQL> EXECUTE dbms_stats.gather_dictionary_stats;

Similarly, in Oracle 12c since the OEM DB Console is no longer used, you should remove its components before upgrading to 12c. Run emremove.sql after copying it from the 12c ORACLE_HOME.

-bash-3.2$ cd /u01/app/oracle/product/12.1.0.1/db_1/rdbms/admin/

-bash-3.2$ cp emremove.sql /home/oracle/emremove.sql

SQL> @/home/oracle/emremove.sql

old  69:     IF (upper('&LOGGING') = 'VERBOSE')

new  69:     IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

Check the TIMESTAMP WITH TIMEZONE datatype version of existing database and see if any action is required. In our case the current version is 4 and DB version being 11.1.0.7, no manual intervention is required. It will be upgraded as a part of database upgrade. In 12c, the version for this datatype is 18.

11g:

SQL> SELECT version FROM v$timezone_file;

 

   VERSION

----------

         4

12c:

SQL> SELECT version FROM v$timezone_file;

 

   VERSION

----------

        18

 

Set the ORACLE_HOME to 12c home and run dbua. 


Oracle 12c Plug and Play

Ravi: “Let’s have a cup of coffee till the time this finishes.”

Me: “Ok, let’s go…

Till the time we are back, the readers are requested to scroll through the following snapshots.

Oracle 12c Help

 

Oracle 12c For Pro Newbies

Oracle 12c For Pro Newbies

Oracle 12c For Pro Newbies

Oracle 12c For Pro Newbies

Oracle 12c For Pro Newbies

Oracle 12c For Pro Newbies

 

-bash-3.2$ cat /etc/oratab|grep orcl

orcl:/u01/app/oracle/product/12.1.0.1/db_1:N

SQL> select DBID, NAME, CDB, CON_ID from V$DATABASE;

 

      DBID NAME      CDB     CON_ID

---------- --------- --- ----------

1401308063 ORCL      NO           0

 

SQL> SELECT version FROM v$timezone_file;

 

   VERSION

----------

        18

 

So, we have gone a long way and now our database is 12c finally. But it is still a non-CDB as shown in the query from v$database. Thus we need some more effort in plugging it to our CDB as a PDB.

Ravi: So, can we do this tomorrow?

Me: Yes, seems we worked a lot today… let’s do this tomorrow.

And readers are requested to stay tuned!


References:

My Oracle Support: https://support.oracle.com

Oracle® Database Administrator's Guide 12c Release 1 (12.1)

Oracle® Database Reference 12c Release 1 (12.1)

 

Oracle R12