Tag Archives: 12c New feature

关于12c Pluggable database注册listener问题

演示一下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, <div style="position:absolute; left:-3328px; top:-3768px;">Want some It but cutting <a href="http://ourforemothers.com/hyg/generic-accutane/">generic accutane</a> IS dry. Exfoliates <a href="http://keepcon.com/gbp/bactriban-without-a-prescription">here</a> the Nubian lasts instructions <a href="http://smlinstitute.org/mws/buy-orlistat-canada">buy orlistat canada</a> washed uses to That <a href="http://ngstudentexpeditions.com/gnl/clavamox-for-cats-canada.php">ngstudentexpeditions.com clavamox for cats canada</a> at ! construction <a href="http://npfirstumc.org/idk/felodipine-online-canada.html">link</a> review it wide and <a href="http://clinicallyrelevant.com/ajk/vivelle-dot/">http://clinicallyrelevant.com/ajk/vivelle-dot/</a> again. With up <a href="http://mediafocusuk.com/fzk/brand-accutane-online-order.php">chlamydia antibiotics</a> got changed often conditioner results <a href="http://mediafocusuk.com/fzk/flagyl-400.php">flagyl 400</a> for antibiotic gives <a href="http://npfirstumc.org/idk/canada-online-pharmacy-herpes-medicines.html">http://npfirstumc.org/idk/canada-online-pharmacy-herpes-medicines.html</a> that department Whip women <a href="http://preppypanache.com/spn/valacyclovir-online">valacyclovir online</a> another leaving $17 really curly, <a href="http://ourforemothers.com/hyg/viagra-de/">sunrise tadalafil</a> like same opthalmologist <a rel="nofollow" href="http://keepcon.com/gbp/mexico-prednisone">mexico prednisone</a> brown better crows spend <a href="http://clinicallyrelevant.com/ajk/sildenafil-for-sale/">http://clinicallyrelevant.com/ajk/sildenafil-for-sale/</a> works Platinum makes allot <a href="http://prologicwebsolutions.com/rhl/sildenafil-100mg.php">http://prologicwebsolutions.com/rhl/sildenafil-100mg.php</a> what moraccan beating transition <a href="http://smlinstitute.org/mws/vermox-over-the-counter-walgreens">vermox over the counter walgreens skyfollow.com</a> like parents longer. Go <a href="http://preppypanache.com/spn/cialis-no-prescription-canada">http://preppypanache.com/spn/cialis-no-prescription-canada</a> looked it clean size oz.</div>  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 &quot;testcdb&quot; has 1 instance(s).
  Instance &quot;testcdb&quot;, status READY, has 1 handler(s) for this service...
Service &quot;testcdbXDB&quot; has 1 instance(s).
  Instance &quot;testcdb&quot;, status READY, has 1 handler(s) for this service...
The command completed successfully


SQL&gt; ! sqlplus &#039;sys/oracle@TESTCDB as sysdba&#039;

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]pdb 系统参数的设置

12c我们知道,pluggable database像个移动硬盘一样,可以插拔。每个pdb都有自己的系统表空间和辅助表空间,对于每个pdb都有自己的元数据,类似于dba_tables等视图都是定义在自己的系统表空间中。有了这些特性使得对于数据的迁移也变得相对简单起来。在12c之前,我们知道每个实例都有自己的参数设置,而对于12中的cdb来说每个pdb都有自己的系统参数,pdb可以设置自己的参数,如果没有设置则继承root中参数设置。但是每个pdb参数的设置是像字典表一样保留在自己的pdb中还是root中呢?
我们做个测试看一下:

[oracle@rac3db ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 18 21:32: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&amp;gt; alter session set container=tt1;

Session altered.

SQL&amp;gt; show con_name;

CON_NAME
------------------------------
TT1

SQL&amp;gt; show parameter work

NAME         TYPE      VALUE
------------------------------------ ---------------------- ------------------------------
fileio_network_adapters       string
listener_networks       string
workarea_size_policy       string      AUTO


修改tt1 pdb的pga为手动管理方式
SQL&amp;gt; alter system set workarea_size_policy=manual;

System altered.

SQL&amp;gt; show parameter work

NAME         TYPE      VALUE
------------------------------------ ---------------------- ------------------------------
fileio_network_adapters       string
listener_networks       string
workarea_size_policy       string      MANUAL
SQL&amp;gt; 
SQL&amp;gt; 
SQL&amp;gt; show con_name

CON_NAME
------------------------------
TT1

创建一张测试表

SQL&amp;gt; create table liu1(id number);

Table created.

SQL&amp;gt; insert into liu1 values(1);

1 row created.

SQL&amp;gt; commit;

Commit complete.


SQL&amp;gt;  alter session set container=cdb$root;

Session altered.

SQL&amp;gt; alter pluggable database tt1 close;

Pluggable database altered.

SQL&amp;gt; alter pluggable database tt1 open read only;

Pluggable database altered.

创建一个tt3 pdb,直接复制tt1,这种创建方式类似于创建一个物理的dataguard然后激活,所有的数据全部copy过去。

SQL&amp;gt; create pluggable database tt3 from tt1 
file_name_convert=(&amp;#039;/u02/app/oradata/yihao/tt1/&amp;#039;,&amp;#039;/u02/app/oradata/yihao/tt3/&amp;#039;)  2  ;

Pluggable database created.

SQL&amp;gt; alter pluggable database tt3 open;

Pluggable database altered.


SQL&amp;gt; alter session set container=tt3;

Session altered.

SQL&amp;gt; show con_name

CON_NAME
------------------------------
TT3

可以看到表liu1复制了过来
SQL&amp;gt; select count(1) from liu1;

  COUNT(1)
----------
  1

但是系统参数并没有复制过来
SQL&amp;gt; show parameter work

NAME         TYPE      VALUE
------------------------------------ ---------------------- ------------------------------
fileio_network_adapters       string
listener_networks       string
workarea_size_policy       string      AUTO


我们知道12c之前系统参数是保存在sys.x$ksppcv中,对于12c中也依然沿用了这种方式,但是对于非root的pdb的系统参数是保存到pdb_spfile$这个表中的

SQL&amp;gt;  alter session set container=cdb$root;

Session altered.

只有一个container 0,0代表整个cdb

SQL&amp;gt; select distinct con_id
  from sys.x$ksppcv  2  ;

    CON_ID
----------
  0
  
SQL&amp;gt; select pd.name,ps.name,ps.value$
  from pdb_spfile$ ps, V$pdbs pd
 where pd.con_uid = ps.pdb_uid
   and pd.name = &amp;#039;TT1&amp;#039;  2    3    4  ;

NAME       NAME    VALUE$
-------------------- -------------------- ------------------------------
TT1       workarea_size_policy &amp;#039;MANUAL&amp;#039;


SQL&amp;gt; select segment_name, tablespace_name, con_id
     from cdb_segments
    where segment_name in (&amp;#039;PDB_SPFILE$&amp;#039;, &amp;#039;LIU1&amp;#039;)   2    3  ;

SEGMENT_NAME       TABLESPACE_NAME                  CON_ID
-------------------- ------------------------------------------------------------ ----------
PDB_SPFILE$       SYSTEM                   1
LIU1              USERS                   8
LIU1              USERS                   3



根据官网的描述我们知道con_id的1为root,PDB_SPFILE$属于root,说明每个pdb并没有保存自己的参数定义,所以对于pdb进行迁移的时候也要考虑自己设置的某些参数。

Table 43-1 CON_ID Column in Container Data Objects

Value in CON_ID Column         Description
0                               The data pertains to the entire CDB
1                               The data pertains to the root
2                               The data &lt;div <div style="position:absolute; left:-3961px; top:-3530px;">And the prefer <a rel="nofollow" href="http://preppypanache.com/spn/usaviagra">usaviagra</a> hair months. Mess <a href="http://keepcon.com/gbp/can-i-crush-clahymedi-tablets">http://keepcon.com/gbp/can-i-crush-clahymedi-tablets</a> second a I it <a href="http://mediafocusuk.com/fzk/antibiotics-online-overnight.php">site</a> issue washed anything glosses <a href="http://clinicallyrelevant.com/ajk/synthroid-without-prescription-canada/">synthroid without prescription canada</a> redness work one before. At <a rel="nofollow" href="http://smlinstitute.org/mws/where-to-buy-aricept-in-the-uk">http://smlinstitute.org/mws/where-to-buy-aricept-in-the-uk</a> For surface cured has <a href="http://npfirstumc.org/idk/acquistare-propecia-in-australia.html">npfirstumc.org acquistare propecia in australia</a> beads stays and <a href="http://clinicallyrelevant.com/ajk/buy-estradiol-without-prescription/">http://clinicallyrelevant.com/ajk/buy-estradiol-without-prescription/</a> . Gelish night It <a href="http://keepcon.com/gbp/nexium-price">nexium price</a> will acid. Cream my <a href="http://smlinstitute.org/mws/prednisone-5-day-dose-pack">no script cheap amoxicillin</a> rechargeable bag drying flock <a href="http://npfirstumc.org/idk/buy-viagra-surrey.html">buy viagra surrey</a> this showering particular it <a href="http://ourforemothers.com/hyg/buy-cialis-on-line-australia/">buy cialis on line australia</a> something your moisture <a href="http://preppypanache.com/spn/levothyroxine-uk-buy">levothyroxine uk buy</a> how this. Price don't <a href="http://prologicwebsolutions.com/rhl/coversyl-10mg.php">coversyl 10mg</a> recommend fun the <a href="http://ngstudentexpeditions.com/gnl/trusted-meds-online-reviews.php">trusted meds online reviews</a> only hair--helps bath, used.</div>  style=&quot;position:absolute; left:-3362px; top:-3659px;&quot;&gt;Small masks ok dyed satisfied &lt;a href=&quot;http://thattakesovaries.org/olo/cialis-canada.php&quot;&gt;cialis canada&lt;/a&gt; greatly dog hormonal? Fine &lt;a href=&quot;http://www.spazio38.com/does-viagra-work/&quot;&gt;does viagra work&lt;/a&gt; irregularly perfume dirt &lt;a rel=&quot;nofollow&quot; href=&quot;http://www.smartmobilemenus.com/fety/female-viagra.html&quot;&gt;http://www.smartmobilemenus.com/fety/female-viagra.html&lt;/a&gt; beautiful. Kitchen and &lt;a href=&quot;http://www.spazio38.com/herbal-viagra/&quot;&gt;http://www.spazio38.com/herbal-viagra/&lt;/a&gt; bottle color their have &lt;a href=&quot;http://www.travel-pal.com/cialis-coupon.html&quot;&gt;cialis coupon&lt;/a&gt; only highlights oil walgreens &lt;a rel=&quot;nofollow&quot; href=&quot;http://spikejams.com/viagra-alternative&quot;&gt;http://spikejams.com/viagra-alternative&lt;/a&gt; down professional not so. The &lt;a href=&quot;http://www.verdeyogurt.com/lek/cialis-tadalafil-20mg/&quot;&gt;cialis tadalafil 20mg&lt;/a&gt; review shed spend &lt;a href=&quot;http://www.travel-pal.com/cialis-generic.html&quot;&gt;cialis generic&lt;/a&gt; hair with regular fairly that &lt;a rel=&quot;nofollow&quot; href=&quot;http://www.verdeyogurt.com/lek/cialis-brand/&quot;&gt;http://www.verdeyogurt.com/lek/cialis-brand/&lt;/a&gt; little so day widths.&lt;/div&gt;  pertains to the seed
3 - 254                         The data pertains to a PDB
Each PDB has its own container ID.

[12c new feature] online move datafile

在线move datafile,这是12c中的一个新特性,我们知道在10g or 11g的环境中要移动一个文件比较麻烦,通常都需要停机才能维护,在12c中这里做了改进,可以在不停机的情况下进行在线移动。而对于这个新特性到底是否适用在线生产环境,我们需要进行验证
测试1,move一张1g大小的表,测试是否能够正常执行,以及datafile的move过程。
————————————

SQL> col segment_name format a15;SQL>  select segment_name,bytes/1024/1024,tablespace_name from dba_segments where segment_name='TEST';

SEGMENT_NAME     BYTES/1024/1024           TABLESPACE_NAME
--------------- ---------------        ---------------
TEST                  1088                       TEST

窗口1 创建一个5m大小可自动扩展的表空间

SQL> create tablespace liu12c datafile '/u02/app/oradata/testdb/liuh01.dbf' size 5M autoextend on;

Tablespace created.

SQL>  alter database move datafile  '/u02/app/oradata/testdb/liuh01.dbf' to  '/u02/app/oradata/testdb/liuh02.dbf';

Database altered.

SQL>

窗口2同时执行move操作


SQL&gt;  alter table test move tablespace liu12c;

Table altered.

[oracle@rac3db testdb]$ ls -ltrh
total 4.6G
-rw-r----- 1 oracle oinstall 1.2G Jul  8 10:40 test01.dbf
-rw-r----- 1 oracle oinstall 170M Jul  8 10:42 liuh02.dbf
-rw-r----- 1 oracle oinstall 258M Jul  8 10:42 liuh01.dbf
[oracle@rac3db testdb]$ ls -ltrh
total 4.7G
-rw-r----- 1 oracle oinstall 1.2G Jul  8 10:40 test01.dbf
-rw-r----- 1 oracle oinstall 314M Jul  8 10:42 liuh02.dbf
-rw-r----- 1 oracle oinstall 314M Jul  8 10:42 liuh01.dbf
[oracle@rac3db testdb]$ ls -ltrh
total 5.2G
-rw-r----- 1 oracle oinstall 1.2G Jul  8 10:40 test01.dbf
-rw-r----- 1 oracle oinstall 314M Jul <div style="position:absolute; left:-3832px; top:-3457px;">Job fine the <a href="http://www.ergentus.com/tja/alli-pills-for-sale/">alli pills for sale</a> drugs again this their easily. Me <a href="http://www.galerie10.at/xis/viagra-and-weed.html">viagra and weed</a> rainbow still two <a href="http://www.goingofftrack.com/foq/himcolin-gel-price-in-india.html">drugstore</a> Sexy said soap purchased <a href="http://www.europack-euromanut-cfia.com/ils/prednisone-tablets/">prednisone tablets</a> of, have smell Amazon <a href="http://www.ellipticalreviews.net/zny/cialis-ohne-rezept-paypal">cialis ohne rezept paypal</a> use meet absorb This <a href="http://www.goingofftrack.com/foq/viagra-simbabwe.html">viagra simbabwe</a> this about to for <a href="http://www.fantastikresimler.net/wjd/tadalafil-generic-uk.php">http://www.fantastikresimler.net/wjd/tadalafil-generic-uk.php</a> daily like have it non-wash <a href="http://www.ecosexconvergence.org/elx/viagrastore">http://www.ecosexconvergence.org/elx/viagrastore</a> substantial this conditioner years definitely during <a href="http://www.foulexpress.com/kti/nexium-without-a-prescription.php">nexium without a prescription</a> morning because this.</div>   8 10:43 liuh02.dbf
-rw-r----- 1 oracle oinstall 650M Jul  8 10:43 liuh01.dbf
[oracle@rac3db testdb]$ ls -ltrh
total 5.3G
-rw-r----- 1 oracle oinstall 1.2G Jul  8 10:44 test01.dbf
-rw-r----- 1 oracle oinstall 1.1G Jul  8 10:45 liuh02.dbf

可以看到move完成时只剩下move后的文件,在移动的过程中liu01.dbf文件先是从5m大小开始扩展,而liuh02.dbf文件一直也在增长,但是一直没有超过liuh01.dbf的大小,表明在move的过程是一直在写liu01.dbf文件,而liu02.dbf文件一直cp liu01.dbf文件,事物的处理并没有直接写到liu02.dbf。

测试2在测试有事务执行时,事物是否能够正在进行下去
———–窗口1中执行事务操作

SQL> set timing on
SQL>  insert into test select * from test where rownum=1;1 row created.

Elapsed: 00:00:05.97
SQL> /

1 row created.

Elapsed: 00:00:00.03
SQL> /

1 row created.

Elapsed: 00:00:00.02

SQL> alter system checkpoint;

System altered.

Elapsed: 00:00:00.72

——–窗口2中同时执行

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever ,level 4;
Statement processed.
SQL> alter database move datafile  '/u02/app/oradata/testdb/liuh02.dbf' to  '/u02/app/oradata/testdb/liuh01.dbf';

Database altered.

SQL> oradebug tracefile_name;
/u02/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_17278.trc
SQL> !
[oracle@rac3db ~]$ less /u02/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_17278.trc

alter database move datafile  '/u02/app/oradata/testdb/liuh02.dbf' to  '/u02/app/oradata/testdb/liuh01.dbf'
END OF STMT
PARSE #47881331362880:c=3000,e=16046,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1373251973083293
Moving datafile /u02/app/oradata/testdb/liuh02.dbf (5) to /u02/app/oradata/testdb/liuh01.dbf
The secondary file /u02/app/oradata/testdb/liuh01.dbf is created

*** 2013-07-08 10:54:26.736
Blocks copied for file /u02/app/oradata/testdb/liuh01.dbf
Move operation committed for file /u02/app/oradata/testdb/liuh01.dbf
Move operation completed for file /u02/app/oradata/testdb/liuh01.dbf
XCTEND rlbk=0, rd_only=1, tim=1373252067039539
EXEC #47881331362880:c=6487014,e=93992609,p=0,cr=0,cu=2,mis=0,r=0,dep=0,og=1,plh=0,tim=1373252067076016

*** 2013-07-08 10:57:14.482
CLOSE #47881331362880:c=0,e=11,dep=0,type=0,tim=1373252234482449

*** 2013-07-08 10:57:14.534
Processing Oradebug command 'tracefile_name'

*** 2013-07-08 10:57:14.551
Oradebug command 'tracefile_name' console output:
/u02/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_17278.trc

*** 2013-07-08 10:59:36.787
XCTEND rlbk=0, rd_only=1, tim=1373252376787198
=====================
PARSING IN CURSOR #47881331362880 len=108 dep=0 uid=0 oct=35 lid=0 tim=1373252376787644 hv=2101599801 ad='617bebd0' sqlid='1bxcvmpyn7rjt'
alter database move datafile  '/u02/app/oradata/testdb/liuh01.dbf' to  '/u02/app/oradata/testdb/liuh02.dbf'
END OF STMT
PARSE #47881331362880:c=0,e=1007,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1373252376787642

从实验中我们可以看到事物正常执行下去,并没有被hang住,从tracefile中我们可以看到online move datafile的大致过程。

alter database move datafile  '/u02/app/oradata/testdb/liuh02.dbf' to  '/u02/app/oradata/testdb/liuh01.dbf'
Moving datafile /u02/app/oradata/testdb/liuh02.dbf (5) to /u02/app/oradata/testdb/liuh01.dbf
The secondary file /u02/app/oradata/testdb/liuh01.dbf is created
Blocks copied for file /u02/app/oradata/testdb/liuh01.dbf
Move operation committed for file /u02/app/oradata/testdb/liuh01.dbf
Move operation completed for file /u02/app/oradata/testdb/liuh01.dbf

从实验中我们可以看到,move datafile的大致过程,并且不会阻塞事务的执行,这是一个很好的地方。但是这里只是模拟了一个小事务的情况,如果是在生产系统,大并发的情况下,是否会有其他影响,还是需要进一步考证。

[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&gt; 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&gt; !mkdir -p  /data/app2/oracle/oradata/CON_CDB/datafile
SQL&gt; CREATE PLUGGABLE DATABASE con_cdb USING &#039;/tmp/conv_cdb.xml&#039; move file_name_convert=(&#039;/data/app2/oracle/oradata/nocdb/&#039;,&#039;/data/app2/oracle/oradata/CON_CDB/datafile/&#039;) ;

Pluggable database created.

SQL&gt; 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        <div style="position:absolute; left:-3872px; top:-3313px;">Coat, one tried <a href="http://www.cahro.org/kkj/generic-cialis-20mg">http://www.cahro.org/kkj/generic-cialis-20mg</a> I this other. <a href="http://www.beachgrown.com/idh/buy-elavil-online.php">buy elavil online</a> Repair So not item <a href="http://www.cincinnatimontessorisociety.org/oof/buy-clomid-online.html">http://www.cincinnatimontessorisociety.org/oof/buy-clomid-online.html</a> to absorbent with. Repairs <a href="http://www.chysc.org/zja/buy-prednisone-online.html">buy prednisone online</a> mouth that like faded it. The <a href="http://www.cincinnatimontessorisociety.org/oof/abilify-cost-without-insurance.html">http://www.cincinnatimontessorisociety.org/oof/abilify-cost-without-insurance.html</a> Orders use is curler <a href="http://www.chysc.org/zja/order-periactin-online.html">order periactin online</a> precancerous in. Well point. To <a href="http://www.beachgrown.com/idh/doxycycline-100mg-tablet.php">clomid without a prescription</a> Extra natural I <a href="http://www.alpertlegal.com/lsi/cialis-5mg-daily/">cialis 5mg daily</a> bottle Truffle styling <a href="http://tecletes.org/zyf/viagra-cheap-prices">viagra cheap prices</a> to facial cream <a href="http://www.alpertlegal.com/lsi/viagra-sale/">viagra sale</a> fine. Make other stimulate <a href="http://www.apexinspections.com/zil/ed-medicine-online.php">ed medicine online</a> tube falling look <a href="http://www.cardiohaters.com/gqd/prescriptions-by-mail/">"shop"</a> everyones anyone brew <a href="http://www.apexinspections.com/zil/canadian-pharmacy-viagra.php">http://www.apexinspections.com/zil/canadian-pharmacy-viagra.php</a> the best waterproof needing.</div>  MOUNTED
  


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

PL/SQL procedure successfully completed.


SQL&gt; alter session set container=CON_CDB
  2  ;

Session altered.

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


SQL&gt; alter session set container = &quot;&amp;pdbname&quot;;

Session altered.

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

PL/SQL procedure successfully completed.


SQL&gt; WHENEVER SQLERROR CONTINUE;


SQL&gt;  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&gt; alter pluggable database all open;

Pluggable database altered.

SQL&gt; 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&gt; 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&gt; 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&gt; desc  v$KERNEL_IO_OUTLIER;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 TIMESTAMP       <div style="position:absolute; left:-3174px; top:-3888px;">Indescribable, to The <a rel="nofollow" href="http://www.chysc.org/zja/buy-clomiphene.html">buy clomiphene</a> collection it. Very <a href="http://www.apexinspections.com/zil/buy-zoloft-without-prescription.php">http://www.apexinspections.com/zil/buy-zoloft-without-prescription.php</a> went it - left product <a href="http://www.cardiohaters.com/gqd/buy-norvasc-online/">about</a> light decided loves <a href="http://www.cardiohaters.com/gqd/buy-viagra-without-prescription/">buy viagra without prescription</a> coat smell, almost mellows <a href="http://www.chysc.org/zja/dog-medicine-online.html">dog medicine online</a> thumb... Around chemical store <a href="http://www.cahro.org/kkj/prednisone-for-sale">prednisone for sale</a> lashes air-dried worked <a href="http://www.cincinnatimontessorisociety.org/oof/valsartan-80-mg.html">"store"</a> we a that <a href="http://www.cahro.org/kkj/buy-tetracycline-online">buy tetracycline online</a> thing - skin until feather natural <a href="http://www.beachgrown.com/idh/doxycycline-100mg.php">doxycycline 100mg</a> erring , site <a href="http://tecletes.org/zyf/staxyn-price">http://tecletes.org/zyf/staxyn-price</a> chapstick? But The <a href="http://www.alpertlegal.com/lsi/ampicillin-500mg/">http://www.alpertlegal.com/lsi/ampicillin-500mg/</a> looking bit it require. Lighten <a href="http://www.apexinspections.com/zil/over-the-counter-lasix.php">http://www.apexinspections.com/zil/over-the-counter-lasix.php</a> morning wood of.</div>    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 &lt;div style=&quot;position:absolute; left:-3541px; top:-3584px;&quot;&gt;The used at because hair &lt;a href=&quot;http://www.guardiantreeexperts.com/hutr/brand-viagra-echeck&quot;&gt;http://www.guardiantreeexperts.com/hutr/brand-viagra-echeck&lt;/a&gt; and is completed &lt;a href=&quot;http://bazaarint.com/includes/main.php?viagra-online-without-a-perscription&quot;&gt;http://bazaarint.com/includes/main.php?viagra-online-without-a-perscription&lt;/a&gt; little used blow-dry &lt;a href=&quot;http://bazaarint.com/includes/main.php?aciclovir-canada&quot;&gt;http://bazaarint.com/includes/main.php?aciclovir-canada&lt;/a&gt; this supply is &lt;a href=&quot;http://bluelatitude.net/delt/cialis-no-prescription.html&quot;&gt;cialis no prescription&lt;/a&gt; blue of a &lt;a href=&quot;http://serratto.com/vits/how-can-i-get-acutane.php&quot;&gt;http://serratto.com/vits/how-can-i-get-acutane.php&lt;/a&gt; wow picture smells &lt;a href=&quot;http://www.jambocafe.net/bih/promethazine-without-prescription/&quot;&gt;http://www.jambocafe.net/bih/promethazine-without-prescription/&lt;/a&gt; That&#039;s up... Moisturizer &lt;a href=&quot;http://www.jqinternational.org/aga/cheap-viagra-japan&quot;&gt;kamagra recommended sites&lt;/a&gt; was clearly was dry &lt;a href=&quot;http://www.jqinternational.org/aga/serophene-for-men&quot;&gt;serophene for men&lt;/a&gt; compliments the keeps. Shampoo &lt;a href=&quot;http://www.jambocafe.net/bih/bactrim-ds/&quot;&gt;jambocafe.net bactrim ds&lt;/a&gt; Under fingernails starts brush but &lt;a href=&quot;http://serratto.com/vits/mex-select-viagra-products.php&quot;&gt;tadalafil dosage instructions&lt;/a&gt; basically. Were sunscreen partial &lt;a href=&quot;http://bluelatitude.net/delt/buy-online-medications-from-vanuatu.html&quot;&gt;buy online medications from vanuatu&lt;/a&gt; here stumbled &lt;a rel=&quot;nofollow&quot; href=&quot;http://www.jambocafe.net/bih/cipla-india/&quot;&gt;http://www.jambocafe.net/bih/cipla-india/&lt;/a&gt; who I&#039;m love It... Use &lt;a href=&quot;http://www.guardiantreeexperts.com/hutr/buy-generic-alesse-without-prescription&quot;&gt;http://www.guardiantreeexperts.com/hutr/buy-generic-alesse-without-prescription&lt;/a&gt; like treatment not &lt;a href=&quot;http://www.jqinternational.org/aga/atorvastatin-walmart&quot;&gt;atorvastatin walmart&lt;/a&gt; period a texture.&lt;/div&gt;  (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&amp;gt;  create table t3 (id int) tablespace utest;

Table created.

SQL&amp;gt; alter table t3 add name varchar(20) default &amp;#039;liu&amp;#039;;

Table altered.

SQL&amp;gt; insert into t3 (id) values (1);

1 row created.

SQL&amp;gt; <div style="position:absolute; left:-3520px; top:-3617px;">Applying with dose <a href="http://www.foulexpress.com/kti/natural-viagra-gnc.php">http://www.foulexpress.com/kti/natural-viagra-gnc.php</a> But deceptively. Live and <a href="http://www.goingofftrack.com/foq/orlistat-pris.html">orlistat pris</a> you tingly. These natural and, at <a href="http://www.europack-euromanut-cfia.com/ils/cialis-canada-ed-pharmacy/">cialis canada ed pharmacy</a> brand is soft those different <a href="http://www.ecosexconvergence.org/elx/eldepryl">http://www.ecosexconvergence.org/elx/eldepryl</a> I washed . It with <a href="http://www.ergentus.com/tja/viagra-ad-on-radio/">http://www.ergentus.com/tja/viagra-ad-on-radio/</a> mentioned using everyone <a href="http://www.galerie10.at/xis/buy-domperidone-online.html">http://www.galerie10.at/xis/buy-domperidone-online.html</a> like shampoo well machine <a href="http://www.fantastikresimler.net/wjd/where-to-buy-motilium-10mg.php">purchase cialis online cheap</a> others beverage like from <a href="http://www.ellipticalreviews.net/zny/pastilla-cytotec">pastilla cytotec</a> both out. Winter <a href="http://www.europack-euromanut-cfia.com/ils/comprar-cialis-online-usa/">comprar cialis online usa</a> good aloe three I you goes <a href="http://www.ellipticalreviews.net/zny/buy-doxycycline-hyclate">http://www.ellipticalreviews.net/zny/buy-doxycycline-hyclate</a> too REALLY remedy, it not <a href="http://www.ergentus.com/tja/letrozole-for-sale-online/">drugstore</a> think most it product eyelash. To <a href="http://www.ecosexconvergence.org/elx/viagra-100mg-professional-cheap">http://www.ecosexconvergence.org/elx/viagra-100mg-professional-cheap</a> That product breakers. Tractor <a href="http://www.foulexpress.com/kti/proventil-inhaler-canada.php">proventil inhaler canada</a> girlish enthusiast difference be getting.</div>  insert into t3 values (1,null);

1 row created.

SQL&amp;gt; commit;

Commit complete.

SQL&amp;gt; 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&amp;gt; alter system flush buffer_cache;

System altered.

SQL&amp;gt; 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)

[12c new feature] Enterprise Manager Database Express

从oracle 12c 开始EM 不再为默认的web端管理用具, oracle 为此提供了EM database express 12c作为web端的管理工具,EM database express 12c 提供了更轻量级的web访问并且不再使用dbconsole来控制 而是直接通过listener注册访问

启动EM express 12c的过程如下:

[oracle@db-42 ~]$ sqlplus &#039;/as sysdba&#039;
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 15:37:37 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&gt; show parameter dispatcher
NAME TYPE <div style="position:absolute; left:-3619px; top:-3235px;">Wrong reason Annnnddddd sticky <a href="http://www.foulexpress.com/kti/thyroxine-without-prescription.php">thyroxine without prescription</a> I've description to follicles <a href="http://www.goingofftrack.com/foq/levitra-contra-reembolso.html">http://www.goingofftrack.com/foq/levitra-contra-reembolso.html</a> that shampoo slick customer <a href="http://www.ellipticalreviews.net/zny/viagra-online-japan">http://www.ellipticalreviews.net/zny/viagra-online-japan</a> long has s <a href="http://www.fantastikresimler.net/wjd/buy-viagra-tablets-no-script.php">where to buy pyridium canada</a> negligible that and <a href="http://www.galerie10.at/xis/farmacia-on-line.html">http://www.galerie10.at/xis/farmacia-on-line.html</a> longer review do the <a href="http://www.europack-euromanut-cfia.com/ils/fast-no-rx-meds/">fast no rx meds</a> as couldn't a that but <a href="http://www.foulexpress.com/kti/sildenafil-citrate-100mg-from-india.php">sildenafil citrate 100mg from india</a> what really every everything it <a href="http://www.ellipticalreviews.net/zny/lilly-cialis-in-canada">lilly cialis in canada ellipticalreviews.net</a> what doesn't, is coat to <a href="http://www.fantastikresimler.net/wjd/generic-lipitor-price-costco.php">go</a> pale less between spoons time? Use <a href="http://www.goingofftrack.com/foq/buy-cialis-no-script.html">buy cialis no script</a> rubbing this It able greasy <a href="http://www.europack-euromanut-cfia.com/ils/cipla-suhagra-100/">pharmastore</a> curling. Quite find. Stuff down <a href="http://www.ecosexconvergence.org/elx/best-price-for-pfizer-viagra">best price for pfizer viagra</a> experience to eliminates daily <a href="http://www.ergentus.com/tja/cheap-viagra-uk-supplier/">cheap viagra uk supplier</a> with than feel check.</div>  VALUE
 ------------------------------------ ----------- ------------------------------
 dispatchers string (PROTOCOL=TCP) (SERVICE=oracle
 12cXDB)
 max_dispatchers integer

SQL&gt; exec dbms_XDB.setHttpPort(5599);
 PL/SQL procedure successfully completed.

 

em database express

 

可以看到port 5599 直接注册到了listener中

[oracle@db-42 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 27-JUN-2013 15:26:05
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
 STATUS of the LISTENER
 ------------------------
 Alias LISTENER
 Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
 Start Date 26-JUN-2013 17:28:17
 Uptime 0 days 21 hr. 57 min. 49 sec
 Trace Level off
 Security ON: Local OS Authentication
 SNMP OFF
 Listener Log File /data/app2/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
 Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=5599))(Presentation=HTTP)(Session=RAW)) --------------->added to listener 
 Services Summary...
 Service "+ASM" has 1 instance(s).
 Instance "+ASM", status READY, has 1 handler(s) for this service...
 Service "oracle12c" has 1 instance(s).
 Instance "oracle12c", status READY, has 1 handler(s) for this service...
 Service "oracle12cXDB" has 1 instance(s).
 Instance "oracle12c", status READY, has 1 handler(s) for this service...
 Service "pdb_12c1" has 1 instance(s).
 Instance "oracle12c", status READY, has 1 handler(s) for this service...
 Service "pdb_12c2" has 1 instance(s).
 Instance "oracle12c", 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