Andrew Fraser DBA

Oracle 12c new features

Oracle 12c was released yesterday (25-Jun-2013). I’m a little bit underwhelmed by a first look at the new features guide – if that’s all there is, Oracle is going to continue to struggle against Microsoft SQL Server.

Main change with 12c is concept of container databases and pluggable databases:

• Container databases by default hold SGA, redo, undo – and there would typically be one of these per server (in RAC: one per cluster)
• Pluggable databases by default hold users, schemas, tablespaces, tables, temp TS, grants to public, public synonyms, public database links – and there would typically be more than one of these per server (in RAC: more than one per cluster).

Will be good if this encourages DBAs to run just one SGA per host server: running more is resource inefficient.

Useful commands:

• $ sqlplus system/***@locahost/pdb1 –- to sqlplus directly to pluggable database named pdb1.
• SQL> alter session set container = pdb1 ; –- to switch to database pdb1.
• SQL> alter session set container = cdb$root ; –- to switch back to the container database.
• SQL> sho con_name – to see which database you are currently connected to.
• SQL> sho pdbs –- to list pluggable databases.
• SQL> alter pluggable database all open ; — to make pluggable databases open.  Bizarrely, this has to be manually re-run after every startup, so write it into your /etc/init.d startup script or into database trigger as Kirill Loifman scripted here.
• dbca “manage pluggable database” option is used to create pluggable databases, or SQL Developer DBA panel, or SQL as at the end of this article.

Other new features that caught my eye:

• Enterprise Manager Database Express 12c ships with all databases and is configured out of the box, has a ‘performance hub’ page.  Just need a database username/password to login, easier than old EM.  Default URL is https://myserver:5500/em – that’s for the container database, I haven’t worked out the URL for the pluggable databases yet, and according to Suarachart Opun that isn’t supported currently.  He recommends instead using the DBA panel (view > DBA) in SQL*Developer which is good for GUI management of pluggable databases.
• Default values for columns can directly refer to Oracle sequences. Valid entries are sequence.CURVAL and sequence.NEXTVAL.
• Table columns have been enhanced to support the American National Standards Institute (ANSI) SQL keyword IDENTITY – SQL Server 2012 took Oracle’s sequences in addition to their own identities, now Oracle 12c has taken SQL Server’s identities in addition to their own sequences.
• Dynamic stats – Is switched off by default except when a table has no statistics at all (parameter optimizer_dynamic_sampling is set to 2). But if switched on, the optimizer automatically decides if dynamic statistics will be used whenever a query is parsed. That won’t be something that those DBAs who disable stats gathering and directly set stats (in order to get performance stability) will want to use.
• Oracle Enterprise Manager Grid Control has been renamed Oracle Enterprise Manager Cloud Control. It now has ASH Analytics, a new tool to explore ASH data that enables the administrator to roll up, drill down, and slice or dice performance data across various performance dimensions. It also now has a group backup feature to backup multiple databases, Oracle homes, and file systems in one operation.
• You can now define a PL/SQL function in the WITH clause of a subquery and use it as an ordinary function.
• Implcit result sets with jdbc – no need for INTO etc. in PL/SQL. Details here.
• CLONEDB – new tool to clone databases (but is it really easier to use than the old rman method? Especially since rman now has active duplicate)
• SQL*Loader express mode – no need to write SQL*Loader control files now.
• alter index unusable/drop index ONLINE ; — no locks
• alter index visible | invisible ;
• invisible columns – hidden from select *, but appear if explicitly selected.
• move datafile online — no outage needed for moving to new disks
• data guard improved
• rman duplicate improved, and rman command line can now handle sql, selects, describes (so why not merge rman into sqlplus?)
• rman users no longer need sysdba privilege

Scripts for manipulating pluggable databases (can also use dbca ‘modify pluggable databases’ and SQL Developer DBA panel):

-- Create a new pluggable database
create pluggable database dev3
 admin user admin identified by *****
 file_name_convert = ( '/pdbseed/', '/dev3/' ) ;
alter pluggable database dev3 open ;

-- Clone a pluggable database
alter pluggable database dev1 close ;
alter pluggable database dev1 open read only ;
create pluggable database dev4
 from dev1
 file_name_convert = ('/dev1/', '/dev4/') ;
alter pluggable database dev1 close ;
alter pluggable database dev1 open ;
alter pluggable database dev4 open ;

-- Unplug a pluggable database
alter pluggable database dev1 close ;
alter pluggable database dev1
 unplug into '/software/oracle/product/oradata/oracletrain4/dev1/dev1.xml' ;
drop pluggable database dev1 keep datafiles ;

-- Plug it back in
set serverout on
BEGIN
   IF dbms_pdb.check_plug_compatibility ( pdb_descr_file => '/software/oracle/product/oradata/oracletrain4/dev1/dev1.xml' )
   THEN
      dbms_output.put_line ( 'Yes, is compatible ok' ) ;
   ELSE
      dbms_output.put_line ( 'No, is not compatible' ) ;
   END IF ;
END ;
/
create pluggable database dev1
 using '/software/oracle/product/oradata/oracletrain4/dev1/dev1.xml'
 nocopy ;
--move file_name_convert = ('/cdb1/', '/cdb2/') -- if you want to change filenames during this operation
alter pluggable database dev1 open ;

Here is the SQL Developer DBA panel options to do the same:
 

More examples and further reading at: