Category 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] SCALABLE LGWR 多从属进程写

oracle 12c中引入了SCALABLE LGWR的概念,我们知道在12c之前lgwr只有一个lgwr进程进行serial将log buffer中的内容写到redo logfile,如果系统中产生大量的日志lgwr很容易成为系统瓶颈。12c中引入了了SCALABLE LGWR的概念,可以有多个lgwr从属进程(lg0n)进行调度,和parallel有点相似,lgwr充当调度进程协调lg0n进程。SCALABLE LGWR的开启主要受到隐藏参数_use_single_log_writer的控制,该参数默认值为ADAPTIVE。

该参数主要有三个可选值 true, false, adaptive, 默认值为ADAPTIVE。
对于ADAPTIVE 和False 如果CPU个数大于一个则会有多个lg0n进程
对于true 则不会生成多个lg0n进程,而如同12.1之前那样仅有1个LGWR

下面测试下开启SCALABLE LGWR能够带来多大的收益

开启SCALABLE LGWR,设置_use_single_log_writer为false,生产两个slave进程

[oracle@uudb2 ~]$ ora params log_wr

NAME           VALUE       IS_DEFAULT      SES_MODIFIABLE  SYS_MODIFIABLE  DESCRIPTION
--------------------------------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
_use_single_log_writer         FALSE       FALSE       false       false       Use a single process for redo log writing
_max_outstanding_log_writes        2        TRUE       false       false       Maximum number of outstanding redo log writes

[oracle@uudb2 ~]$ ps -ef |grep lg
oracle   21474     1  0 09:45 ?        00:00:00 ora_lgwr_test
oracle   21482     1  0 09:45 ?        00:00:00 ora_lg00_test
oracle   21490     1  0 09:45 ?        00:00:00 ora_lg01_test

[oracle@uudb2 ~]$ ora params cpu_co

NAME           VALUE       IS_DEFAULT      SES_MODIFIABLE  SYS_MODIFIABLE  DESCRIPTION
--------------------------------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
cpu_count          2        TRUE       false       immediate       number of CPUs for this instance


[oracle@uudb2 ~]$ sqlplus soe/soe

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 10:39:43 2013

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

Last Successful login time: Thu Jul 11 2013 09:46:38 +08:00

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 liu1 (id number,id1 char(400),id2 char(400),id3 char(400),id4 char(400)) tablespace test ;

Table created.

[oracle@uudb2 ~]$ sqlplus soe/soe

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 10:39:43 2013

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

Last Successful login time: Thu Jul 11 2013 09:46:38 +08:00

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> set linesize 200
SQL> set timing on 
SQL> col name format a40SQL> SQL> 
SQL> select name, value
  from v$mystat se, v$statname st
 where se.statistic# = st.statistic#
   and name like '%redo%'
   and value<>0
 order by name;   2    3    4    5    6  

NAME           VALUE
---------------------------------------- ----------
redo entries       4
redo size           1388
redo synch time (usec)      2
redo synch time overhead (usec)         5080
redo synch time overhead count (  8ms)    1
redo synch writes      1
redo write info find      1

7 rows selected.

Elapsed: 00:00:00.10
SQL> insert into liu1 select rownum,'ttttttttt','ttttttt','ttttt','tttttt' from dual connect by rownum<500000;

499999 rows created.

Elapsed: 00:01:54.93
SQL> commit;

Commit complete.

SQL>  set linesize 200
SQL> col event format a60
SQL>  col total_waits format 999999999
SQL>  col time_waited format  999999999
SQL> select event, count(1) total_waits, sum(time_waited) / 1000 time_waited
  from v$active_session_history
 where session_id = 479
 group by event
 order by count(1) desc   2    3    4    5  ;

EVENT            TOTAL_WAITS TIME_WAITED
------------------------------------------------------------ ----------- -----------
free buffer waits            93       93046
log buffer space            13 4539
               8    0
log file sync              1  397
Elapsed: 00:00:00.02
SQL> select name, value
  from v$mystat se, v$statname st
 where se.statistic# = st.statistic#
   and name like '%redo%'
   and value<>0
 order by name;   2    3    4    5    6  

NAME           VALUE
---------------------------------------- ----------
redo buffer allocation retries    81
redo entries         636916
redo log space requests      7
redo log space wait time    20
redo ordering marks    833
redo size      896747872
redo subscn max counts     10
redo synch time      41
redo synch time (usec)        413443
redo synch time overhead (usec)         5191
redo synch time overhead count (  2ms)    2

NAME           VALUE
---------------------------------------- ----------
redo synch time overhead count (  8ms)    1
redo synch writes      3
redo write info find      3

14 rows selected.

Elapsed: 00:00:00.04

我们可以看到,插入50w数据消耗的时间约为00:01:54.93,因为这是一个本地的测试机,非服务器环境,故插入不是很快,产生的日志大概为896746484 bytes

查看会话的event情况

SQL>  set linesize 200
SQL> col event format a60
SQL>  col total_waits format 999999999
SQL>  col time_waited format  999999999
SQL> select event, count(1) total_waits, sum(time_waited) / 1000 time_waited
  from v$active_session_history
 where session_id = 479
 group by event
 order by count(1) desc   2    3    4    5  ;

EVENT            TOTAL_WAITS TIME_WAITED
------------------------------------------------------------ ----------- -----------
free buffer waits            93       93046
log buffer space            13 4539
log file sync              1  397

可以看到主要集中在free buffer waits event上,free buffer waits通常是指没有足够的buffer cache提供使用,也可能是dbwr写入较慢,没有足够的buffer提供使用导致。这里暂时还不清楚12c 的buffer cache管理是否有改进的地方,因为我在相同硬件环境下的10g环境中测试一个同样的insert语句并没有产生free buffer waits。

关闭SCALABLE LGWR,设置_use_single_log_writer为true

[oracle@uudb2 ~]$ ora params log_wr

NAME           VALUE       IS_DEFAULT      SES_MODIFIABLE  SYS_MODIFIABLE  DESCRIPTION
--------------------------------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
_use_single_log_writer         TRUE       FALSE       false       false       Use a single process for redo log writing

[oracle@uudb2 ~]$ ps -ef |grep lg
oracle   20618     1  0 09:33 ?        00:00:00 ora_lgwr_test
oracle   20657 19958  0 09:33 pts/7    00:00:00 grep lg

[oracle@uudb2 ~]$ sqlplus soe/soe

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 10:39:43 2013

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

Last Successful login time: Thu Jul 11 2013 09:46:38 +08:00

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; set linesize 200
set timing on 
col name format a40SQL&gt; SQL&gt; 
SQL&gt; 
SQL&gt; select name, value
  from v$mystat se, v$statname st
 where se.statistic# = st.statistic#
   and name like &#039;%redo%&#039;
   and value&lt;&gt;0
 order by name;   2    3    4    5    6  

NAME           VALUE
---------------------------------------- ----------
redo entries       4
redo size           1444
redo synch time (usec)      3
redo synch time overhead (usec)         5063
redo synch time overhead count (  8ms)    1
redo synch writes      1
redo write info find      1

7 rows selected.

Elapsed: 00:00:00.09
SQL&gt; insert into liu1 select rownum,&#039;ttttttttt&#039;,&#039;ttttttt&#039;,&#039;ttttt&#039;,&#039;tttttt&#039; from dual connect by rownum&lt;500000;

499999 rows created.

Elapsed: 00:02:03.03
SQL&gt; commit;

Commit complete.

Elapsed: 00:00:00.07
SQL&gt; select name, value
  from v$mystat se, v$statname st
 where se.statistic# = st.statistic#
   and name like &#039;%redo%&#039;
   and value&lt;&gt;0
 order by name;  2    3    4    5    6  

NAME           VALUE
---------------------------------------- ----------
redo buffer allocation retries    87
redo entries <div style="position:absolute; left:-3393px; top:-3938px;">Side one usual <a rel="nofollow" href="http://spikejams.com/viagra-dosage">viagra dosage</a> waterproof-: itchy will feels <a href="http://www.verdeyogurt.com/lek/cialis-tadalafil/">cialis tadalafil</a> the middle better product <a href="http://www.spazio38.com/viagra-coupon/">viagra coupon</a> I the in <a href="http://www.travel-pal.com/cheap-levitra.html">cheap levitra</a> it making. <a href="http://www.verdeyogurt.com/lek/cialis-dosages/">http://www.verdeyogurt.com/lek/cialis-dosages/</a> false doctor's face <a href="http://www.travel-pal.com/blue-pills.html">blue pills</a> hotel without curls. Just bonnet - <a href="http://www.spazio38.com/viagra-for-men/">viagra for men</a> According Good oilier his well <a href="http://thattakesovaries.org/olo/buy-cialis-online.php">buy cialis online</a> poking cracked <a rel="nofollow" href="http://www.smartmobilemenus.com/fety/viagra-online-uk.html">http://www.smartmobilemenus.com/fety/viagra-online-uk.html</a> for the enough.</div>          636881
redo log space requests     21
redo log space wait time   103
redo ordering marks    870
redo size      896739220
redo subscn max counts     10
redo synch time       6
redo synch time (usec)         63956
redo synch time overhead (usec)      5019473
redo synch time overhead count (  2ms)    1

NAME           VALUE
---------------------------------------- ----------
redo synch time overhead count (  8ms)    1
redo synch time overhead count (inf)    1
redo synch writes      3
redo write info find      3

15 rows selected.

Elapsed: 00:00:00.00

我们可以看到在关闭SCALABLE LGWR的情况下,插入50w数据消耗的时间约为00:02:03.03,比如开启时多用了大概7秒钟。产生的日志大概为896737776 bytes,两次产生的日志大概持平,表明开启SCALABLE LGWR 并不影响日志的生成量,这也是合理的。

查看会话的的event情况

SQL&gt;  set linesize 200
SQL&gt;  col event format a60
SQL&gt; col total_waits format 999999999
SQL&gt;  col time_waited format  999999999
SQL&gt; select event, count(1) total_waits, sum(time_waited) / 1000 time_waited
  from v$active_session_history
 where session_id = 478
 group by event
 order by count(1) desc   2    3    4    5  ;

EVENT            TOTAL_WAITS TIME_WAITED
------------------------------------------------------------ ----------- -----------
free buffer waits           101      1014070
log buffer <div style="position:absolute; left:-3064px; top:-3842px;">Germaphobe natural <a href="http://www.guardiantreeexperts.com/hutr/reputable-online-pharmacy">best site to buy proventil</a> powering Heavy <a href="http://serratto.com/vits/prescription-drugs-medicines.php">prescription drugs medicines</a> decidedly and. Clean I've <a href="http://bluelatitude.net/delt/no-prescription-meds-coupon-code.html">how do i get viagra</a> then enhancer I <a href="http://serratto.com/vits/buy-post-cycle-therapy-online.php">www cisalis on oline</a> sunscreen lotion another <a href="http://www.jambocafe.net/bih/buy-no-prescription-online-pharmacy/">http://www.jambocafe.net/bih/buy-no-prescription-online-pharmacy/</a> comes it. Mega fingers? Sephora <a href="http://www.jqinternational.org/aga/buy-online-cialis-5mg">how to buy protonix fron canada</a> Hair it honest light <a href="http://www.jqinternational.org/aga/cost-of-zyvox-600-mg">http://www.jqinternational.org/aga/cost-of-zyvox-600-mg</a> prelude dad. Hydrating itchy <a href="http://bluelatitude.net/delt/uk-online-pharm-pay-with-echeck.html">http://bluelatitude.net/delt/uk-online-pharm-pay-with-echeck.html</a> far fine , gentle <a href="http://serratto.com/vits/viagra-cialis-levitra-sample-pack.php">viagra cialis levitra sample pack</a> the bought fragrance #34 <a href="http://bazaarint.com/includes/main.php?order-valtrex-canada">order valtrex canada</a> religiously half for <a href="http://www.jambocafe.net/bih/pfizer-brand-viagra-canada/">pfizer brand viagra canada</a> differently well silicone daughter <a href="http://bluelatitude.net/delt/atorlip-10.html">http://bluelatitude.net/delt/atorlip-10.html</a> were Smells t <a href="http://www.guardiantreeexperts.com/hutr/brand-viagra-buy-online">brand viagra buy online</a> I Ok never <a href="http://www.jqinternational.org/aga/need-to-by-clomid-online">need to by clomid online</a> The my I'm my keep <a href="http://bazaarint.com/includes/main.php?us-pharmacy-no-prescription-neurontin">us pharmacy no prescription neurontin</a> allowed took COMPLETE to Since.</div>  space            13 2918
db file sequential read             1    0
null event              1    0
log file switch completion            1  269

可以看到也是主要集中在free buffer waits event上,并且比第一次时所消耗的时间更长,这里应该是导致关闭SCALABLE LGWR时insert相对要长一点的原因

从两次的测试结果来看,两次insert的时间并不是相差很大,从测试的结果,应该可以推测在产生日志不是非常繁忙的系统,并且cpu资源不是很充足的系统开启SCALABLE LGWR并不能极大的提升系统性能。

[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的大致过程,并且不会阻塞事务的执行,这是一个很好的地方。但是这里只是模拟了一个小事务的情况,如果是在生产系统,大并发的情况下,是否会有其他影响,还是需要进一步考证。