Martins Blog

Trying to explain complex things in simple terms

Archive for August, 2013

Support for Pluggable Databases in Enterprise Manager

Posted by Martin Bach on August 29, 2013

Currently there is an interesting thread on the oracle-l mailing list about OEM 12c support for database 12c Release. Unlike previous OEM generations this time OEM was not lagging behind. I am using OEM 12.1.0.2.0 with the database plugin 12.1.0.3.0 and yes, I can see PDBs!

pdbs-001

The above snapshot is from the database targets overview page. As you can see there is a Container Database (CDB1) and it has exactly 1 PDB. When you click on the CDB you get to the main page:

pdbs-002

This is pretty cool because it allows you to view the resources consumed by the CDB broken down into the CDB$ROOT as well as the PDBs. When you click on the PDB name it takes you to another screen from where you manage the PDB. This is done in more or less the same way as you’d manage a non-CDB. Differences exist of course when it comes to visibility of information. You can expect to be presented only with the information you are allowed to see within the context of the PDB!

And it works with emcli too!

Enterprise Manager Command Line Interface (emcli) has support for PDBs as well, you can create them, plug and unplug etc. Here is an example how to create a PDB from a clone. If you refer back to the output of the last print screen you see a PDB named PDB1 in container database CDB1 on server2.example.com. I wanted to clone PDB1 to swingbench1 using the command line interface. The documentation is a bit sketchy at the moment and it is not terribly clear how to create a PDB from a “dbca-template” (at least to me) but I seem to have managed to initialise the creation of a PDB from a clone. On the OMS I ran the command:

[oracle@oem12oms1 ~]$ emcli create_pluggable_database \
>   -cdbTargetName=CDB1 \
>   -cdbTargetType=oracle_database \
>   -cdbHostCreds=ORACLE_HOST \
>   -pdbName=swingbench1 \
>   -sourceType=CLONE \
>   -cdbTargetCreds=LAB_SYS \
>   -sourcePDBName=CDB1_PDB1 \
>   -sourceCDBCreds=LAB_SYS \
>   -sameAsSource
Successfully submitted create database job. Procedure execution ID is: E5134C261D1127FBE043DE64A8C0D388

The execution of this requires the definition of named credentials (setup->security->named credentials). In this case I am using my host credentials ORACLE_HOST, and LAB_SYS to connect as sysdba. If you are unsure about the target names use emcli get_targets -targets=”oracle_database” followed by emcli get_targets -targets=”oracle_pdb” to list the databases as well as the PDBs.

After a little while the PDB will be created. Check enterprise->provisioning and patching->procedure activity for your job. Clicking on it gives you more information on what’s happening. In this case the procedure completed successfully:

pdbs-004

This is nice because it’s repeatable, scriptable and does NOT require a password to be stored in the script!

[oracle@server2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 29 05:42:01 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name,open_mode from v$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDB1			       READ WRITE
SWINGBENCH1		       READ WRITE

SQL>

Summary

Certain particularities such as CDB and PDB resource plans are well managed already. I can only recommend OEM 12c over 11.1, it feels so much better and the user interface is more modern too. Happy consolidation!

License Warning

I have no idea about licensing these components-before you implement them make sure you are appropriately licensed for the feature. Which is actually true for all the blog posts here…

Posted in 12c Release 1, Linux | Leave a Comment »

DBMS_FILE_TRANSFER potentially cool but then it is not

Posted by Martin Bach on August 24, 2013

This post is interesting for all those of you who plan to transfer data files between database instance. Why would you consider this? Here’s an excerpt from the official 12.1 package documentation:

The DBMS_FILE_TRANSFER package provides procedures to copy a binary file within a database or to transfer a binary file between databases.

But it gets better:

The destination database converts each block when it receives a file from a platform with different endianness. Datafiles can be imported after they are moved to the destination database as part of a transportable operation without RMAN conversion.

So that’s a way not only to copy data files from one database to another but it also allows me to get a file from SPARC and make it available on Linux!

Before you are getting too excited though like I did here’s a catch. If the source file is in ASM you are kind of stuffed. Consider the below (11.2.0.3.0) example, created with a bog standard dbca “General Purpose” database. I have created the directories and db link and now want to get the files from database “source” to database “dbmsft”

begin
 dbms_file_transfer.get_file('dbmsft_src1','system.256.823861409','source','dbmsft_dst1','system.256.823861409');
 dbms_file_transfer.get_file('dbmsft_src1','sysaux.257.823861409','source','dbmsft_dst1','sysaux.257.823861409');
 dbms_file_transfer.get_file('dbmsft_src1','undotbs1.258.823861411','source','dbmsft_dst1','undotbs1.258.823861411');
 dbms_file_transfer.get_file('dbmsft_src1','users.259.823861411','source','dbmsft_dst1','users.259.823861411');
 dbms_file_transfer.get_file('dbmsft_src2','nonomf_01.dbf','source','dbmsft_dst2','nonomf_01.dbf');
end;
  8  /
begin
*
ERROR at line 1:
ORA-19504: failed to create file
"+DATA/dbmsft/datafile/system.256.823861409"
ORA-17502: ksfdcre:4 Failed to create file
+DATA/dbmsft/datafile/system.256.823861409
ORA-15046: ASM file name '+DATA/dbmsft/datafile/system.256.823861409' is
not in single-file creation form
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 37
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 132
ORA-06512: at line 2

And yes, that’s not a bug, it’s a feature, the same as with creating tablespaces. You simply cannot specify a fully qualified ASM file name when creating a file. You can of course use an alias name but that’s not pretty, is it? I hate mixing non OMF and OMF file names in ASM.

Need to check if that’s possible in 12c…

Posted in 11g Release 2, Automatic Storage Management, Linux | 1 Comment »

Creating a 12c Container Database from scripts

Posted by Martin Bach on August 21, 2013

If you are curious how to create a CDB without the help of dbca then the “generate scripts” option is exactly the right approach! I am a great fan of creating databases with the required options only-the default template (General Purpose) is dangerous as it creates a database with options you may not be licensed for and additionally opens security risk.

The best^H^H^Heasiest way to understand how a Container Database (CDB from now on) is created is to let dbca create the scripts. The process is the same as with an interactive installation except that at the very end you do NOT create the database but tick the box to generate the scripts.

Read the rest of this entry »

Posted in 12c Release 1, Linux | 2 Comments »

Adding a node to a 12c RAC cluster

Posted by Martin Bach on August 15, 2013

This post is not in chronological order, I probably should have written something about installing RAC 12c first. I didn’t want to write the tenth installation guide for Clusterware so I’m focusing on extending my two node cluster to three nodes to test the new Flex ASM feature. If you care about installing RAC 12c head over to RAC Attack for instructions, or Tim Hall’s site. The RAC Attack instructions are currently being worked at for a 12c upgrade, you can follow/participate the work on this free mailing list.

The cluster I installed is based on KVM on my lab server. I have used Oracle Linux 6.4 with UEK2 for the host OS. It is a standard, i.e. not a Flex Cluster but with Flex ASM configured. My network configuration is as shown:

  • public network on 192.168.100/24
  • private network on 192.168.101/24
  • ASM network on 192.168.1/24

As you can see here:

[grid@rac12node1 ~]$ oifcfg getif
eth0  192.168.100.0  global  public
eth1  192.168.101.0  global  cluster_interconnect
eth2  192.168.1.0    global  asm

Grid and oracle are the respective software owners, and I am making use of the new separation of duties on the RDBMS layer. Read the rest of this entry »

Posted in 12c Release 1, Automatic Storage Management, KVM, Linux, RAC | 2 Comments »