12c

关于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)

EM12c <.BEA-141281> unable to get file lock

July 16, 2012 12c, oracle 1 comment

EM12c OMS端在部署新的agent的时候 agent端无法upload文件到oms端

[oracle@yhdem logs]$ netstat |grep 4900 (EM_UPLOAD_PORT=4900)
[oracle@yhdem logs]$

[oracle@yhdem logs]$ vi /u01/middleware_12c/oms/sysman/prov/agentpush/logs/EMAgentPushLogger2012-07-02_10-55-00-AM.log.0

2012-07-16_09-45-10:INFO:Current OMS Host yhdem.yihaodian.com
2012-07-16_09-45-10:INFO:refreshPage called
2012-07-16_09-45-10:INFO:====================PRINTING REFRESH FREQUENCY15000
2012-07-16_09-45-10:INFO:====================PRINTING REFRESH FREQUENCY15000
2012-07-16_09-45-11:INFO:isSSH2 result:false output : SSH-2.0-OpenSSH_4.3

2012-07-16_09-45-11:INFO:Using JSCH PLUGIN
2012-07-16_09-45-21:INFO:Pattern ERROR: found in file /u01/middleware_12c/oms/sysman/prov/agentpush/2012-07-16_09-32-56-AM/logs/10.0.2.15_install.log Line ERROR: The Oracle Management Server (OMS) host and port specified via OMS_HOST and EM_UPLOAD_PORT is not available. Pass in a valid hostname and port number for the OMS to successfully deploy the agent.
2012-07-16_09-45-24:INFO:Retrieved path /tmp for property SCRATCHPATH

重新启动oms之后hang死 日志如下:

[oracle@yhdem ~]$ tail -f /u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out


Java(TM) SE Runtime Environment (build 1.6.0_24-b50)
Java HotSpot(TM) 64-Bit Server VM (build 19.1-b02, mixed mode)
Starting WLS with line:
/u01/middleware_12c/jdk16/jdk/bin/java -server -Xms256m -Xmx512m -XX:MaxPermSize=512m -Dweblogic.Name=EMGC_ADMINSERVER -Djava.security.policy=/u01/middleware_12c/wlserver_10.3/server/lib/weblogic.policy -Dweblogic.ProductionModeEnabled=true -Dweblogic.system.BootIdentityFile=/u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/security/boot.properties -Dweblogic.nodemanager.ServiceEnabled=true
-Djava.security.egd=file:///dev/./urandom -Dweblogic.debug.DebugSecurityAtn=true -Dweblogic.debug.DebugWebAppSecurity=true -Dweblogic.SSL.LoginTimeoutMillis=300000 -Djps.auth.debug=true -Djps.authz=ACC -Djps.combiner.optimize.lazyeval=true -Djps.combiner.optimize=true -Djps.policystore.hybrid.mode=false -Djps.subject.cache.key=5 -Djps.subject.cache.ttl=600000 -Xbootclasspath/p:/u01/middleware_12c/oms/sysman/jlib/diagpatch_bug11725986.jar
-Djdkpatchlog=/u01/middleware_12c/oms/sysman/log/diagpatch_bug11725986.log -Doracle.apm.home=/u01/middleware_12c/oms/apm/ -DAPM_HELP_FILENAME=oesohwconfig.xml -da -Dplatform.home=/u01/middleware_12c/wlserver_10.3 -Dwls.home=/u01/middleware_12c/wlserver_10.3/server -Dweblogic.home=/u01/middleware_12c/wlserver_10.3/server -Dcommon.components.home=/u01/middleware_12c/oracle_common -Djrf.version=11.1.1 -Dorg.apache.commons.logging.Log=org.apache.commons.logging.impl.Jdk14Logger
-Ddomain.home=/u01/middleware_12c/gc_inst/user_projects/domains/GCDomain -Djrockit.optfile=/u01/middleware_12c/oracle_common/modules/oracle.jrf_11.1.1/jrocket_optfile.txt -Doracle.server.config.dir=/u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/servers/EMGC_ADMINSERVER -Doracle.domain.config.dir=/u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/config/fmwconfig -Digf.arisidbeans.carmlloc=/u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/carml
-Digf.arisidstack.home=/u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/arisidprovider -Doracle.security.jps.config=/u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/jps-config.xml -Doracle.deployed.app.dir=/u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/tmp/_WL_user -Doracle.deployed.app.ext=/- -Dweblogic.alternateTypesDirectory=/u01/middleware_12c/oracle_common/modules/oracle.ossoiap_11.1.1,/u01/middleware_12c/oracle_common/modules/oracle.oamprovider_11.1.1
-Djava.protocol.handler.pkgs=oracle.mds.net.protocol -Dweblogic.jdbc.remoteEnabled=false -Doracle.apm.home=/u01/middleware_12c/oms/apm/ -DAPM_HELP_FILENAME=oesohwconfig.xml -Dweblogic.management.discover=true -Dwlw.iterativeDev=false -Dwlw.testConsole=false -Dwlw.logErrorsToConsole=false -Dweblogic.ext.dirs=/u01/middleware_12c/patch_wls1035/profiles/default/sysext_manifest_classpath weblogic.Server

64-Bit Server VM Version 19.1-b02 from Sun Microsystems Inc.>

<.Jul 16, 2012 9:54:30 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:54:40 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:54:50 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:55:00 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:55:10 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:55:20 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:55:30 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:55:40 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:55:50 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:56:00 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:56:10 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:56:20 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:56:30 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 
<.Jul 16, 2012 9:56:40 AM CST> <Info> <Management> <BEA-141281> <unable to get file lock, will retry ...> 

通过mv .lok 文件解决这个问题:

[oracle@yhdem middleware_12c]$ find . -name *.lok
./gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/EMGC_OMS1.lok
./gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/ldap/ldapfiles/EmbeddedLDAP.lok
./gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/tmp/EMGC_ADMINSERVER.lok
./gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/ldap/ldapfiles/EmbeddedLDAP.lok
./gc_inst/user_projects/domains/GCDomain/edit.lok
./gc_inst/user_projects/domains/GCDomain/config/config.lok
[oracle@yhdem middleware_12c]$ cd gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/
[oracle@yhdem tmp]$ ls
EMGC_OMS1.lok WebServiceUtils.ser _WL_internal _WL_user
[oracle@yhdem tmp]$ cp EMGC_OMS1.lok /tmp/
[oracle@yhdem tmp]$ rm EMGC_OMS1.lok
[oracle@yhdem ~]$ cd /u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/ldap/ldapfiles/
[oracle@yhdem ldapfiles]$ ls
EmbeddedLDAP.data EmbeddedLDAP.delete EmbeddedLDAP.index EmbeddedLDAP.lok EmbeddedLDAP.tran EmbeddedLDAP.trpos EmbeddedLDAP.twpos
[oracle@yhdem ldapfiles]$ cp *.lok /tmp/
[oracle@yhdem ldapfiles]$ rm *.lok
[oracle@yhdem ldapfiles]$ ls
EmbeddedLDAP.data EmbeddedLDAP.delete EmbeddedLDAP.index EmbeddedLDAP.tran EmbeddedLDAP.trpos EmbeddedLDAP.twpos
[oracle@yhdem ldapfiles]$ cd /u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/tmp/
[oracle@yhdem tmp]$ ls
EMGC_ADMINSERVER.lok _WL_internal _WL_TEMP_APP_DOWNLOADS _WL_user
[oracle@yhdem tmp]$ cp EMGC_ADMINSERVER.lok /tmp/
[oracle@yhdem tmp]$ rm EMGC_ADMINSERVER.lok
[oracle@yhdem tmp]$ cd /u01/middleware_12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/ldap/ldapfiles/
[oracle@yhdem ldapfiles]$ cp EmbeddedLDAP.lok /tmp
[oracle@yhdem ldapfiles]$ rm EmbeddedLDAP.lok

重新启动oms

[oracle@yhdem bin]$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
Starting WebTier…
WebTier Successfully Started
Starting Oracle Management Server…
Oracle Management Server Successfully Started
Oracle Management Server is Up

[oracle@yhdem bin]$ ./emctl start agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
Starting agent ……………… started.

[oracle@yhdem data]$ netstat |grep 4900
tcp 0 537 yhdem.yihaodian.com:19550 yhdem.yihaodian.com:4900 ESTABLISHED
tcp 0 0 yhdem.yihaodian.com:19549 yhdem.yihaodian.com:4900 ESTABLISHED
tcp 0 0 yhdem.yihaodian.com:4900 yhdem.yihaodian.com:19549 ESTABLISHED
tcp 0 0 yhdem.yihaodian.com:4900 yhdem.yihaodian.com:19550 ESTABLISHED

可以看到 已经成功的添加了主机: