Monthly Archives: July 2013

More about Oracle 12c identity columns

Oracle 12c has introduced the ability to define a column in a database table as a “Identity” Column. This results in the database automatically using a sequence to generate the values that get inserted into this column. The Oracle-Base blog has an article on how to use this functionality and some performance comparisons between this and trigger based methods.

Below is a bit more detail regarding this functionality.

As stated in the Oracle-Base article, oracle auto-creates a sequence and uses it to generate the values. There seems to be a new data dictionary view, USER_TAB_IDENTITY_COLS, which shows you which columns in the table are defined as the Datatype IDENTITY.

The query below can be used to, identify the sequence name, that oracle generated, for a given column, defined as an IDENTITY datatype.

SQL> set long 999999999
SQL> select data_default from user_tab_columns where table_name = 'T1'    
  2  and column_name = 'COL1';

DATA_DEFAULT
--------------------------------------------------------------------------------
"SCOTT"."ISEQ$$_92434".nextval

SEQUENCE_NAME	     INCREMENT_BY CACHE_SIZE
-------------------- ------------ ----------
ISEQ$$_92434			1	  20

The sequence it uses, gets created with a default of 20 for the cache size. This will likely be unacceptable in Real Application Clusters environments. If you try to modify the cache size of the sequence after the creation you will get a ORA-32793 error “cannot alter a system-generated sequence”.

In such cases it would be wise to use the syntax below during table creation, and specify the CACHE_SIZE and other sequence parameters you would want to change.

SQL> create table t1 (col1 number generated as identity (start with 100 increment by 2 cache 1000), col2 varchar2(50));

Table created.

Once the table is re-created, you can check the cache size again to verify.

SQL> select sequence_name,cache_size from user_sequences;

SEQUENCE_NAME	     CACHE_SIZE
-------------------- ----------
ISEQ$$_92436		   1000

Installing and Using the 12c instant client

Now that we have a 12cr1 database Up and running, it is time to connect to the database and start working. I prefer using the oracle instant client and sqlplus from my Ubuntu Linux desktop to connect to the 12cr1 database. Below are the steps to install, configure and use the 12cr1 instant client on a Ubuntu Linux 64 bit desktop and connect to the oracle 12cr1 database.

Download and Install Instant Client 12cr1

From the “Instant Client Downloads for Linux x86-64” page, download the highlighted zip files. This gets you the basic files needed for Sql*Net connection to the 12cr1 database and also the sqlplus executable.

Unzip the files to a directory on your desktop. (In my case i unzipped the files to the directory /u01/Rk/Apps/oracle/instantclient_12_1).

Export the required environment variables

export LD_LIBRARY_PATH=/u01/Rk/Apps/oracle/instantclient_12_1
export ORACLE_HOME=/u01/Rk/Apps/oracle/instantclient_12_1
export PATH=$ORACLE_HOME:$PATH

Invoke Sqlplus to connect to the CDB

sqlplus system/manager@192.168.56.101:1521/rkcdb1

SQL> sho con_id

CON_ID
------------------------------
1
SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

Invoke Sqlplus to connect to the PDB

sqlplus system/manager@192.168.56.101:1521/rkpdb1

SQL> sho con_id

CON_ID
------------------------------
3
SQL> sho con_name

CON_NAME
------------------------------
RKPDB1
SQL>

There are some very helpful tutorials in the Oracle Learning Library to get started with the new 12c database features.

Creating a 12cR1 database on Oracle Linux 6

In the two previous posts, i have shown how to create a Oracle Linux 6 virtual box image and then how to install a Oracle 12c Release 1 rdbms Home on the virtual box image. In this installment of the blog post, i’ll outline the steps to create a Oracle 12c Release 1, Multi Tenant database on this VirtualBox image.

In the following steps, i’ll be using dbca (Database creation assistant) to create a container database (CDB) with one pluggable database (PDB).

The following steps assume that

  • You have successfully installed Oracle Linux 6
  • You have Installed the 12cR1 rdbms binaries
  • You are still setup to redirect your DISPLAY to your desktop.

Create  a Listener

Login as oracle to your virtual host (Redirect your display to your desktop)

Invoke the executable “netca” (Network configuration assistant) from the linux command line. You will see the screen shown above. Click Next

Choose “Add” in the screen above, Click Next.

Choose the default name “LISTENER” for the listener Name, Click Next.

Choose the default, TCP protocol, Click Next.

Choose the default standard port 1521, Click Next.

Choose “No” for configuring another listener, Click Next.

The netca assistant will complete the Listener Configuration and will prompt you to Exit.

Create a New Database

Prior to Oracle 12c all databases were Non-CDB databases (ie a database that is Not a Multi Tenant Container Database). Starting with 12c you can create databases as Non-CDB databases or CDB (MultiTenant Container) databases.

In the following steps i’ll be using dbca (Database Configuration assistant)  to create a CDB with one PDB in it (Pluggable database).

Login as oracle to the Oracle 6 Linux, virtual box image, and invoke dbca from the o/s prompt, you will see the banner first and then the screen below.

Choose “Create Database” and Click Next.

Choose “Advanced Mode” and Click Next.

Choose “General Purpose Or Transaction Processing” and Click Next.

Provide your chosen “Global Database Name”, SID Name, Check the box against “Create As Container Database”, Choose the Radio Button “Create a Container Database with one or More PDB’s” , Provide a “PDB Name” and Click Next.

Check the Box to Configure “Em Express”, Click Next.

Enter the Credentials for the Administrative Users, Click Next.

Choose the default Listener “LISTENER” and Click Next.

Choose “Use Common Location for all database files”, Provide the location of the datafiles (I provided “/u02/oradata”)  and Uncheck “Specify Fast Recovery Area” (This being a test database, i am not setting it up to be in archivelog mode). Click Next.

Choose to install “Sample Schema’s”, Click Next.

Choose to use “Automatic Memory Management”, Click on “Character Sets” and choose “AL32UTF8”, Click Next.

Check the boxes for “Create Database”, “Save as Database Template” (For future reuse), “Generate Database Creation Scripts”, Click Next.

In the screen above, Click “Finish” and the database installation will start.

If all goes well, the installation will complete, and dbca will prompt you to exit.

Congratulations !!! Now you have a brand new 12cr1 database. Now it is time to connect and start exploring the Ground Breaking Innovations in the Oracle 12c database.

 

 

Installing Oracle Rdbms 12c Release 1 on Oracle Linux 6

In this previous post, i showed how to build a Virtual Box image, that runs the Oracle Linux 6 Update 4 x86-64 bit operating system. I set the image up with all the pre-requisites for installing the Oracle Rdbms 12c Release1. In this blog post i’ll describe the steps i executed to install a single instance, oracle 12c Oracle Home on Oracle Linux 6.

Download the Oracle 12c Software

 

Download the above two files to your desktop.

Scp them to the Oracle Linux 6 server.

I copied the zip files into a directory named /u02/sw (Owned by oracle) and unzipped both of them in that directory.

Directory structure

Since this is a test install, i am not really following the oracle best practices on separating the Oracle Base, Oracle Inventory location and Oracle Home. In my installation all these are going into /u01/12cr1

Setting up the X Display

Since the installer is a graphical user interface, we need to redirect the display to the client (Unless you are installing using a Vnc client, or logged into the Oracle Linux 6 virtual machine using a Gui).

I am running this on ubuntu linux, so my ubuntu linux desktop is the X client.

I login to the Oracle Linux 6 virtual machine using the following command, which redirects the display to my desktop.

ssh -X -l oracle 192.168.56.101

Install Oracle 12c Binaries

Login as the oracle user to the OEL6 virtual machine

cd /u02/sw/database

Invoke runInstaller as shown in the screen above

The 12c Logo displays

 

Click Next on the above 2 screens.

In the screen shown above, choose “Install database software only”. This way we install just the rdbms binaries and then we’ll create the database afterwards.

In the screen above choose “Single Instance database installation”.

In the Screen above choose “English” as the language.

In the Screen above, chooose “Enterprise Edition”

In the Screen above, choose the Oracle Base as “/u01/12cr1” and the Oracle Home as “/u01/12cr1/dbh1”

In the Screen above Choose the location of the Oracle Inventory as “/u01/12cr1/oraInventory”

In the above Screen, choose the defaults

In the screen above, the installer complains about the physical memory, shmall and shmmax.

In my case shmall and shmmax are set much higher than the values that oracle recommends.

If you have set the memory for the virtual machine to 2Gb as i mentioned in my previous post, the warning about the physical memory should not be present. So it is ok to ignore these warnings and proceed with the installation, as shown in the picture above.

The installer displays the installation locations. Click on “Install” to start the installation.

The installation proceeds as shown in the figure above.

After the binary installation, the installer prompts you to run two scripts.

Login as root, to the Oracle 6 virtual machine, from another screen.

Then click Ok on the screen above.

Click “Close”  in the screen above to complete the rdbms binary installation.

In the next blog post, i will document the steps to create and start a Container database and Pluggable database.

Creating an Oracle Linux Release 6 VirtualBox image

Oracle Database 12c was recently announced (On June 1 2013) to be Generally Available for customers to use. I created a new Oracle Linux 6 virtual box, virtual machine and installed Oracle 12c Release 1 on it. So i’ll blog about the steps i used, with screenshots, in a 3 part blog series. This first one is this post, where i’ll document the steps i followed to create the Oracle Linux 6 – 64 bit virtual machine (In the next 2 posts i’ll document the process used to install the rdbms binaries and create the database).

Download Oracle Linux 6 Update 4

I downloaded Oracle Linux from https://edelivery.oracle.com/linux

 

GetOEL

GetOEL

Create a New VirtualBox Image

Start the Oracle VM VirtualBox Manager from your program Menu

Click on “New”, you get the screen Below

Choose the Name as “OEL6” (Any name you want) and Type “Linux” and Version “Oracle (64 Bit)” as in the picture above, Click Next.

In the above screen, choose the file type to be “VDI”, Click Next.

Choose the file location and size in the screen above, Click Next.

Add the Oracle Linux ISO as a bootable Drive.

In the Oracle VM VirtualBox Manager, click and Highlight the new virtual Machine “OEL6” that we created.

In the right hand side of the window you will see the details regarding this new image.

Now would be a good time to click on “System” and increase the memory allocated to the Virtual Machine to 2Gb and the Number of Processors to 2.

Then click on “Storage”, which brings you to the screen below.

Click on the icon pointed to by the Red Arrow in the Figure Above, to create a new Dvd Drive.

Choose the Oracle Linux 6 iso image that you just downloaded from edelivery.oracle.com.

This action will result in a new DVD drive being displayed in the storage screen, as in the Figure above. Click OK and return to the “Oracle VM VirtualBox Manager” main screen.

Install Oracle Linux 6 Update 4

In the Left panel of the Main Menu, Highlight your new Virtual Box image “OEL6”, and click on “Start”. It brings up the screen below

Hit Enter, it will Bring you to the screen below

In this screen i chose to skip the test of the media, tab to skip and hit enter.

In this screen i chose “English”, hit Enter

In this screen i chose “U.S English”, hit Enter

Choose “Basic Storage Devices” and in the next screen Choose “Yes Discard Any Data”

In the above screen, i left the hostname as “localhost.localdomain” and did not configure networking. (Configured these manually later).

It autodetects your timezone, click Next.

Enter a password for the root user and click Next

Choose “Use All Space” and click Next, Confirm “Write Changes to Disk”, Click Next.

I chose “Database Server” in the screen above. Keep in mind that once you do this, you do not get a GUI to login to the machine. All access has to be via the command line. If you want a GUI, you can choose “Basic Server”.

The above two screens appear when the installation is in progress, and as soon as the installation is complete.

Congratulations, you now have a OEL6 Update 4, 64 bit Virtual Box image !!!

Configure Networking and Hostname

At this point it would be best if you configure the virtualbox image to use a “Bridged Network”, so that it can communicate with a ULN server, to get the updates and required packages.

Follow the instructions from Setting Up Oracle Linux 6 with public-yum for updates to setup your image to receive packages from the oracle public-yum repository.

Login as root

Edit /etc/sysconfig/network, and change HOSTNAME=burl5vb2.us.oracle.com

Edit /etc/sysconfig/network-scripts/ifcfg-eth0 and add

NETMASK=255.255.255.0
IPADDR=192.168.56.101
BOOTPROTO=none
ONBOOT=yes

This sets up a static IP address and plumbs up eth0 after reboots.

Install Pre-requisite RPM’s (For a 12cR1 oracle installation)

Login as root

Run “yum update” (This will bring any new versions of installed packages that are available on public-yum)

Run “yum install oracle-rdbms-server-12cR1-preinstall” (This will install all the pre-requisite rpm’s, setup the oracle user, setup the oinstall and dba groups, update kernel parameter settings and security settings required for an oracle installation).

Most of the work is done, you are almost there.

Create Additional Hard Disks

I like to separate my binary and database installations into separate hard disks, so now i go ahead and create two additional hard disks.

Create two new hard disks in Virtual Box

From the Oracle VM VirtualBox Manager, click on your Virtual Box Image “OEL6”

Click on Storage in the right hand side panel.

Click on the Hard Disk Icon next to “Controller SATA” pointed to by the Red Arrow

Click on “Create New Disk”

Choose “VDI” for hard disk file type

Choose “Dynamically allocated” for allocation type

Choose the proper File Location and File size as 10GB

Follow the above procedure and add a second hard disk (This one 20Gb) as shown below.

Now you should have two additional hard disks in this Virtual Box Image.

I will be using the disk OEL6-u01 as the device for the /u01 (10Gb) file system and OEL6-u02 as the device for the /u02 (20Gb) file system.

Now when you reboot the Linux virtual machine, OEL6-u01 will show up as /dev/sdb and OEL6-u02 will show up as /dev/sdc.

Partition, Format and Mount the hard disks

Once the devices are created and the VirtualBox Virtual machine has been rebooted, login as root.

Use the fdisk -l commands as shown above, to check that the disks /dev/sdb and /dev/sdc exist and are the correct sizes.

Partition the disks as shown above.

Format the disk /dev/sdb1 as shown above. Repeat the process for /dev/sdc1

Add the entries shown above to /etc/fstab so that the filesystems are mounted everytime you reboot the machine.

At this point, i shut down the virtual machine and switched it from using a “Bridged Network” to using a “Host Only” network.

Now we have a Oracle Linux 6 Virtual Machine that is ready for a Oracle 12c database Release 1 installation.

My next post will walk through the steps to install a single instance Oracle 12c Release 1 Rdbms Environment.