12c new feature

关于12c Pluggable database注册listener问题

August 9, 2013 12c, network, oracle No comments

演示一下12c中关于pdb的listener register,unregister问题

[oracle@db-42 ~]$ lsnrctl status listener1

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2013 14:53:56

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-42)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-AUG-2013 21:04:57
Uptime                    0 days 17 hr. 48 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app2/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/app2/oracle/diag/tnslsnr/db-42/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "testcdb" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdbXDB" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
The command completed successfully

——注册pdb到listener1

[oracle@db-42 ~]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 9 14:54:20 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, OLAP, Advanced Analytics and Real Application Testing options

SQL> desc v$pdbs;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CON_ID 					    NUMBER
 DBID						    NUMBER
 CON_UID					    NUMBER
 GUID						    RAW(16)
 NAME						    VARCHAR2(30)
 OPEN_MODE					    VARCHAR2(10)
 RESTRICTED					    VARCHAR2(3)
 OPEN_TIME					    TIMESTAMP(3)
 CREATE_SCN					    NUMBER
 TOTAL_SIZE					    NUMBER

SQL> select con_id,name from v$pdbs;

    CON_ID NAME
---------- ------------------------------
	 2 PDB$SEED
	 3 CON_CDB_NEW

SQL> alter session set container=CON_CDB_NEW;

Session altered.

SQL> exec dbms_service.start_service('CON_CDB_NEW');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status listener1

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2013 14:55:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-42)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-AUG-2013 21:04:57
Uptime                    0 days 17 hr. 50 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app2/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/app2/oracle/diag/tnslsnr/db-42/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "con_cdb_new" has 1 instance(s). ------------------> new pdb service register in listener
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdb" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdbXDB" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
The command completed successfully


——- unregister pdb service

SQL> exec dbms_service.stop_service('CON_CDB_NEW');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status listener1;

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2013 14:59:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-42)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-AUG-2013 21:04:57
Uptime                    0 days 17 hr. 54 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app2/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/app2/oracle/diag/tnslsnr/db-42/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "testcdb" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdbXDB" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
The command completed successfully


SQL> ! sqlplus 'sys/oracle@TESTCDB as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 9 15:00:04 2013

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


同理在12c之前包括12c中可以管理多个service_name

[oracle@db-42 ~]$ source ~/.bash_profile_11 
[oracle@db-42 ~]$ ora si 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 9 15:01:13 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> !lsnrctl status  

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:01:19

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 31 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "liuyang" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter service_names;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 testdb, liuyang
SQL> exec dbms_service.stop_service('liuyang');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:01:47

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 32 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> exec dbms_service.start_service('liuyang');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:02:12

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 32 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "liuyang" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> exec dbms_service.stop_service('testdb');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status 

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:02:41

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 32 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "liuyang" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

注意无法直接停止testdb service,只能对service ‘liuyang’进行操作。

Summary of DBMS_SERVICE Subprograms

Table 140-5 DBMS_SERVICE Package Subprograms

Subprogram Description

CREATE_SERVICE Procedure

Creates service

DELETE_SERVICE Procedure

Deletes service

DISCONNECT_SESSION Procedure

Disconnects sessions running under this service

MODIFY_SERVICE Procedure

Modifies service

START_SERVICE Procedure

Activates service

STOP_SERVICE Procedure

Stops service

[12c new feature] 针对 pluggable database的操作

1. create Pluggable database from NON_CDB database.

[oracle@db-42 ~]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 13:07:08 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, OLAP, Advanced Analytics and Real Application Testing options


SQL> SQL> select CDB from v$database;

CDB
---
NO

use DBMS_PDB.DESCRIBE to create an XML file to describe the database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4.0486E+10 bytes
Fixed Size		    4658128 bytes
Variable Size		 5905583152 bytes
Database Buffers	 3.4494E+10 bytes
Redo Buffers		   82210816 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL> 
BEGIN
 DBMS_PDB.DESCRIBE(
  pdb_descr_file => '/tmp/conv_cdb.xml');
 END;
/ SQL>   2    3    4    5  

PL/SQL procedure successfully completed.

SQL> ! cat /tmp/conv_cdb.xml |more
<?xml version="1.0" encoding="UTF-8"?>
<PDB>
  <pdbname>nocdb</pdbname>
  <cid>0</cid>
  <byteorder>1</byteorder>
  <vsn>202375168</vsn>
  <dbid>3060397207</dbid>
  <cdbid>3060397207</cdbid>
  <guid>E0BE4CA142ED4DFCE0432A00000A62D7</guid>
  <uscnbas>1732161</uscnbas>
  <uscnwrp>0</uscnwrp>
  <rdba>4194824</rdba>
  <tablespace>
    <name>SYSTEM</name>
    <type>0</type>
    <tsn>0</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/data/app2/oracle/oradata/nocdb/system01.dbf</path>
      <afn>1</afn>
      <rfn>1</rfn>
      <createscnbas>7</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>98560</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>202375168</vsn>
      <fdbid>3060397207</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>1732160</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>1720082</frlsb>
      <frlt>819985944</frlt>
    </file>

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

plug NOCDB into a existing CDB database “oracle12c”

[oracle@db-42 pdb_12c2]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 13:12:17 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, OLAP, Advanced Analytics and Real Application Testing options

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID	 DBID NAME			     OPEN_MODE
---------- ---------- ------------------------------ ----------
	 2 4061728203 PDB$SEED			     READ ONLY
	 3  430324419 LIUYANG			     READ WRITE
	 4 1674643208 YANG			     READ WRITE
	 5 3060388583 TT			     READ WRITE


SQL> !mkdir -p  /data/app2/oracle/oradata/CON_CDB/datafile
SQL> CREATE PLUGGABLE DATABASE con_cdb USING '/tmp/conv_cdb.xml' move file_name_convert=('/data/app2/oracle/oradata/nocdb/','/data/app2/oracle/oradata/CON_CDB/datafile/') ;

Pluggable database created.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID	 DBID NAME			     OPEN_MODE
---------- ---------- ------------------------------ ----------
	 2 4061728203 PDB$SEED			     READ ONLY
	 3  430324419 LIUYANG			     MOUNTED
	 4 1674643208 YANG			     MOUNTED
	 5 3060388583 TT			     MOUNTED
	 6 3060397207 CON_CDB			     MOUNTED
	 


SQL> set serveroutput on;
SQL>  declare
  2         compat boolean := FALSE;
    begin
       compat := dbms_pdb.check_plug_compatibility(pdb_descr_file => '/tmp/noncdb.xml');
        if compat
        then
               dbms_output.put_line('Yes');
       else
               dbms_output.put_line('No');
      end if;
   end;
   /  3    4    5    6    7    8    9   10   11   12  
No

PL/SQL procedure successfully completed.


SQL> alter session set container=CON_CDB
  2  ;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> 
SQL> WHENEVER SQLERROR EXIT;
SQL> 
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
.....


SQL> alter session set container = "&pdbname";

Session altered.

SQL> 
SQL> -- leave the PDB in the same state it was when we started
SQL> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.


SQL> WHENEVER SQLERROR CONTINUE;


SQL>  select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4061728203 PDB$SEED                       READ ONLY
         3  430324419 LIUYANG                        MOUNTED
         4 1674643208 YANG                           MOUNTED
         5 3060388583 TT                             MOUNTED
         6 3060397207 CON_CDB                        MOUNTED   -----added
         
         
5 rows selected.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4061728203 PDB$SEED                       READ ONLY
         3  430324419 LIUYANG                        READ WRITE
         4 1674643208 YANG                           READ WRITE
         5 3060388583 TT                             READ WRITE
         6 3060397207 CON_CDB                        READ WRITE

5 rows selected.

2. Clone Pluggable database from the same database

[oracle@db-42 pdb_12c2]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 14:11:02 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, OLAP, Advanced Analytics and Real Application Testing options


SQL> alter pluggable database  CON_CDB close immediate;

Pluggable database altered.

SQL> alter pluggable database  CON_CDB open read only;

Pluggable database altered.

SQL> CREATE PLUGGABLE DATABASE newpdb FROM CON_CDB
  FILE_NAME_CONVERT = ('/data/app2/oracle/oradata/CON_CDB/datafile/', '/data/app2/oracle/oradata/newpdb/datafile/')
  PATH_PREFIX = '/data/app2/oracle/oradata/newpdb/datafile';  2    3  

Pluggable database created.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID	 DBID NAME			     OPEN_MODE
---------- ---------- ------------------------------ ----------
	 2 4061728203 PDB$SEED			     READ ONLY
	 3  430324419 LIUYANG			     READ WRITE
	 4 1674643208 YANG			     READ WRITE
	 5 3060388583 TT			     READ WRITE
	 6 3060397207 CON_CDB			     READ ONLY
	 7 1613018658 NEWPDB			     MOUNTED

6 rows selected.

SQL> alter pluggable database  NEWPDB open ;

Pluggable database altered.

SQL> alter pluggable database  CON_CDB close immediate;

Pluggable database altered.

SQL> alter pluggable database  CON_CDB open;

Pluggable database altered.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID	 DBID NAME			     OPEN_MODE
---------- ---------- ------------------------------ ----------
	 2 4061728203 PDB$SEED			     READ ONLY
	 3  430324419 LIUYANG			     READ WRITE
	 4 1674643208 YANG			     READ WRITE
	 5 3060388583 TT			     READ WRITE
	 6 3060397207 CON_CDB			     READ WRITE
	 7 1613018658 NEWPDB			     READ WRITE

6 rows selected.

3. Migrate PDB from one CDB to another CDB

(from database oracle12 -> testcdb)

[oracle@db-42 pdb_12c2]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 14:45:41 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, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter pluggable database NEWPDB close immediate;

Pluggable database altered.


SQL> alter  pluggable database NEWPDB UNPLUG into '/tmp/newpdb.xml';

Pluggable database altered.

SQL> SELECT NAME,CDB FROM  V$DATABASE;

NAME	  CDB
--------- ---
ORACLE12  YES


SQL> ! mkdir -p /data/app2/oracle/oradata/newpdb_new/datafile/

login to another CDB (testcdb) copy datafile from source database(oracle12)

[oracle@db-42 ~]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 14:44:23 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, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT NAME,CDB FROM  V$DATABASE;

NAME	  CDB
--------- ---
TESTCDB   YES

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID	 DBID NAME			     OPEN_MODE
---------- ---------- ------------------------------ ----------
	 2 4062640301 PDB$SEED			     READ ONLY
	 

SQL> CREATE PLUGGABLE DATABASE newpdb_new
  USING '/tmp/newpdb.xml'
  FILE_NAME_CONVERT =
    ('/data/app2/oracle/oradata/newpdb/datafile/', '/data/app2/oracle/oradata/newpdb_new/datafile/')
  COPY
  STORAGE (MAXSIZE 2G)
  TEMPFILE REUSE;
  2    3    4    5    6    7  

Pluggable database created.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID	 DBID NAME			     OPEN_MODE
---------- ---------- ------------------------------ ----------
	 2 4062640301 PDB$SEED			     READ ONLY
	 3 1613018658 NEWPDB_NEW		     MOUNTED

SQL> alter  pluggable  database  NEWPDB_NEW open;

Pluggable database altered.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID	 DBID NAME			     OPEN_MODE
---------- ---------- ------------------------------ ----------
	 2 4062640301 PDB$SEED			     READ ONLY
	 3 1613018658 NEWPDB_NEW		     READ WRITE

Also you can use “SOURCE_FILE_NAME_CONVERT” to swith datafile

first UNPLUG pluggable database CON_CDB

SQL> alter  pluggable database  CON_CDB close immediate;

Pluggable database altered.

SQL> alter  pluggable database CON_CDB  UNPLUG into '/tmp/CON_CDB.xml';

Pluggable database altered.

move datafile to new directories

eg:

/data/app2/oracle/oradata/CON_CDB/datafile/system01.dbf–>/data/app2/oracle/oradata/CON_CDB/datafile1/system01.dbf
..
all of datafile moved,than you can tell oracle where are datafiles by “SOURCE_FILE_NAME_CONVERT” parameter

[oracle@db-42 datafile]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 15:11:41 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, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE PLUGGABLE DATABASE CON_CDB_NEW
  2    USING '/tmp/CON_CDB.xml'
    SOURCE_FILE_NAME_CONVERT =('/data/app2/oracle/oradata/CON_CDB/datafile/','/data/app2/oracle/oradata/CON_CDB/datafile1/')
    MOVE
  FILE_NAME_CONVERT = ('/data/app2/oracle/oradata/CON_CDB/datafile1/', '/data/app2/oracle/oradata/CON_CDB_NEW/datafile/')
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M);  3    4    5    6  

Pluggable database created.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID	 DBID NAME			     OPEN_MODE
---------- ---------- ------------------------------ ----------
	 2 4062640301 PDB$SEED			     READ ONLY
	 3 3060397207 CON_CDB_NEW		     MOUNTED

SQL> alter pluggable database  CON_CDB_NEW open ;

Pluggable database altered.

[12c new feature] slow IO tracing by views

根据JL,Glen Fawcett 的文章 oracle 从12c开始提供了3个视图来探测慢速的IO

SQL> desc V$LGWRIO_OUTLIER;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 FUNCTION_NAME					    VARCHAR2(18)
 IO_SIZE					    NUMBER
 WAIT_EVENT					    VARCHAR2(64)
 FILE_NAME					    VARCHAR2(513)
 IO_LATENCY					    NUMBER
 DISK1_NAME					    VARCHAR2(255)
 DISK1_LATENCY					    NUMBER
 DISK2_NAME					    VARCHAR2(255)
 DISK2_LATENCY					    NUMBER
 DISK3_NAME					    VARCHAR2(255)
 DISK3_LATENCY					    NUMBER
 CON_ID 					    NUMBER

SQL> desc v$io_outlier;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 FUNCTION_NAME					    VARCHAR2(18)
 IO_SIZE					    NUMBER
 WAIT_EVENT					    VARCHAR2(64)
 FILE_NAME					    VARCHAR2(513)
 IO_LATENCY					    NUMBER
 DISK1_NAME					    VARCHAR2(255)
 DISK1_LATENCY					    NUMBER
 DISK2_NAME					    VARCHAR2(255)
 DISK2_LATENCY					    NUMBER
 DISK3_NAME					    VARCHAR2(255)
 DISK3_LATENCY					    NUMBER
 CON_ID 					    NUMBER


SQL> desc  v$KERNEL_IO_OUTLIER;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TIMESTAMP					    NUMBER
 IO_SIZE					    NUMBER
 IO_OFFSET					    NUMBER
 DEVICE_NAME					    VARCHAR2(513)
 PROCESS_NAME					    VARCHAR2(64)
 TOTAL_LATENCY					    NUMBER
 SETUP_LATENCY					    NUMBER
 QUEUE_TO_HBA_LATENCY				    NUMBER
 TRANSFER_LATENCY				    NUMBER
 CLEANUP_LATENCY				    NUMBER
 PID						    NUMBER
 CON_ID 					    NUMBER

需要注意的是这3个视图仅仅探测(more than 500 ms)的IO,并且”v$KERNEL_IO_OUTLIER”仅仅在Solaris平台适用,因为其调用了Dtrace
但是由于Dtrace已经在oel6中release,目前不确定是否可以在linux平台使用 (Maybe :)_)

[12c new feature] add column enhancement

首先我们看一组测试 数据库版本oracle 12c beta1

SQL> select count(*) from test;

  COUNT(*)
----------
    725888

SQL> set timing on;
SQL> alter table test add name varchar(20) default 'liu';

Table altered.

Elapsed: 00:00:00.02



Elapsed: 00:00:00.01
SQL>  alter table test add name1 varchar(20) default 'liuyang' not null;

Table altered.

Elapsed: 00:00:00.02

在12c oracle再次加强add column操作,在11g中oracle add column with default value 必须加上not null约束,而使得oracle会直接去col$访问default$来得到default value,避免了全表update。而在12c中,oracle再次加强了这一特性。看下面的测试

session 1 run on 12.1.0.1.0

[oracle@db-42 trace]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 16:43:16 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, OLAP, Advanced Analytics and Real Application Testing options


SQL>  create table t3 (id int) tablespace utest;

Table created.

SQL> alter table t3 add name varchar(20) default 'liu';

Table altered.

SQL> insert into t3 (id) values (1);

1 row created.

SQL> insert into t3 values (1,null);

1 row created.

SQL> commit;

Commit complete.

SQL> select   rowid, 
   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
   dbms_rowid.rowid_block_number(rowid)blockno,
   dbms_rowid.rowid_row_number(rowid) rowno
   from t3 ;  2    3    4    5  

ROWID		      REL_FNO	 BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAWeGAAOAAAACDAAA	   14	     131	  0
AAAWeGAAOAAAACDAAB	   14	     131	  1


SQL> alter system flush buffer_cache;

System altered.

SQL> alter system dump datafile 14 block 131;

System altered.

session 2 run on 11.2.0.3

[oracle@db-42 trace]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 16:40:09 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  create table t3 (id int) tablespace utest;

Table created.

SQL> alter table t3 add name varchar(20) default 'liu';

Table altered.

SQL> insert into t3 (id) values (1);

1 row created.

SQL> insert into t3 values (1,null);

1 row created.

SQL> commit;

Commit complete.

SQL> select   rowid, 
  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  dbms_rowid.rowid_block_number(rowid)blockno,
  dbms_rowid.rowid_row_number(rowid) rowno
  from T3 ;  2    3    4    5  

ROWID		      REL_FNO	 BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAToBAAHAAAACDAAA	    7	     131	  0
AAAToBAAHAAAACDAAB	    7	     131	  1


SQL> alter system flush buffer_cache;

System altered.

SQL> alter system dump datafile 7 block 131;

System altered.

12c add column

oracle在block上增加了一个标识位,从而避免了没有not null时的访问矛盾问题(with null value) (oracle will know when to visit dictionary)