Oracle public redo thread and private redo thread

Review before the study content, to public redo thread and private redo thread of use or is relatively vague, online collection of information is very limited, it seems that some good things do not jumps over is not.

Say less nonsense, knowledge points recorded:

In a RAC environment, each instance has its own redo log, which is called the redo thread. Redo log. This set of concepts also applies to single-instance databases.

Redo thread has two types, private and public, by default, we use the public thread. But if we explicitly specified the thread parameter when creating Redol log, then the redo is private redo.

The experiment is as follows:

Single instance:

sql> l 1* Select thread#,status,enabled from v$threadsql>/thread# status enabled------------------------1 OP    EN publicsql> Select Group#,thread#,status from V$log; group# thread# STATUS------------------------------------4 1 INACTIVE 5 1 currentsql> Col Member for A60sql    > select Group#,member from V$logfile; group# MEMBER----------------------------------------------------------------------4/home/app/oraten/oradata/ Oraten/redo04.log 5/home/app/oraten/oradata/oraten/redo05.logsql> ALTER DATABASE add logfile Thread 2 Group 6 '/home/ App/oraten/oradata/oraten/redo06.log ' size 100m;database altered. sql> ALTER DATABASE Add logfile thread 2 Group 7 '/home/app/oraten/oradata/oraten/redo07.log ' size 100m;database altere   D.sql> select thread#,status,enabled from V$thread; thread# STATUS ENABLED------------------------1 OPEN public 2 CLOSED disabledsql> ALTER DATABASE enable thread 2;D Atabase altered. Sql> Select Thread#,status,enaBled from V$thread; thread# STATUS ENABLED------------------------1 OPEN public 2 CLOSED privatesql> show parameter ThreadName TYP     E VALUE-----------------------------------------------------------------------------parallel_threads_per_cpu Integer 2thread integer 1sql> alter system set thread=2 Scope=spfile; System altered. sql> shutdown immediatedatabase closed. Database dismounted. ORACLE instance shut down. Sql> Startuporacle instance started. Total System Global area 281018368 bytesfixed size 2095672 bytesvariable size 222299592 bytesdatabase buffers 50331 648 Bytesredo buffers 6291456 bytesdatabase mounted. Database opened.   Sql> select thread#,status,enabled from V$thread;  thread# STATUS ENABLED------------------------1 CLOSED Public 2 OPEN privatesql> select Group#,thread#,status from    V$log; group# thread# STATUS------------------------------------4 1 INACTIVE 5 1 Current 6 2 current 7 2 UNUSED  Sql>

In the RAC Environment:

 [[email protected] ~]$ crs_stat-t-vname Type r/ra f/ft Target State Host--- -------------------------------------------------------------------ora.easy.db application 0/0 0/1 ONLINE O NLINE node1 ora....y1.inst application 0/5 0/0 online online node1 ora....y2.inst applicatio n 0/5 0/0 online online node2 ora .... SM1.ASM application 0/5 0/0 online online node1 ora ....    E1.lsnr application 0/5 0/0 Online online node1 ora.node1.gsd application 0/5 0/0 ONLINE Online node1 ora.node1.ons application 0/3 0/0 online online node1 ora.node1.vip applicat Ion 0/0 0/0 Online online node1 ora .... SM2.ASM application 0/5 0/0 online online node2 ora .... E2.lsnr application 0/5 0/0 Online online node2 ora.node2.gsd application 0/5 0/0 ONLINE   Online node2 ora.node2.ons application 0/3 0/0 online online node2 ora.node2.vip applic ation 0/0 0/0 Online online node2 [[email protected] ~]$ [[email protected] ~]$ sqlplus/a  S sysdbasql*plus:release 10.2.0.5.0-production on Sat 17:47:08 2014Copyright (c) 1982, Oracle. All rights reserved.connected to:oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit productionwith the part Itioning, real application Clusters, OLAP, Data miningand Real application Testing optionssql> desc v$thread Name Nul    L? Type-----------------------------------------------------------------------------thread# number STATUS VARCHAR2 (6) ENABLED VARCHAR2 (8) GROUPS number INSTANCE VARCHAR2 open_time DATE current_group# number SEQUENCE # number checkpoint_change# number Checkpoint_time date enable_change# number Enable_time date Disable_cha nge# number Disable_time   DATE last_redo_sequence# number Last_redo_block number last_redo_change# number Last_redo_time datesql>   Select thread#,status,enabled from V$thread;  thread# STATUS ENABLED------------------------1 Open Public 2 open publicsql> show parameter ThreadName TYPE     VALUE-----------------------------------------------------------------------------parallel_threads_per_cpu Integer 2thread integer 1sql> exitdisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64b It productionwith the partitioning, real application Clusters, OLAP, Data miningand Real application Testing Options[[emai  L protected] ~]$ ssh [email protected]last login:mon 13:09:51 from node3[[email protected] ~]$   Sqlplus/as sysdbasql*plus:release 10.2.0.5.0-production on Sat 17:48:27 2014Copyright (c) 1982,, Oracle. All rights reserved.connected to:oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit produCtionwith the partitioning, real application Clusters, OLAP, Data miningand Real application testing optionssql> show P Arameter threadname TYPE VALUE----------------------------------------------------------------------------- Parallel_threads_per_cpu integer 2thread integer 2sql> alter system set thread=1 scope=spfile sid= ' easy2 '; System altered. Sql> alter system set thread=2 scope=spfile sid= ' easy1 '; System altered. sql> shutdown immediatedatabase closed. Database dismounted. ORACLE instance shut down. Sql> exitdisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit productionwith the Partiti Oning, real application Clusters, OLAP, Data miningand Real application testing options[[email protected] ~]$ Exitlog Outconnection to Node2 closed. [[email protected] ~]$ sqlplus/as sysdbasql*plus:release 10.2.0.5.0-production on Sat 17:55:54 2014Copyri  Ght (c) 1982, Oracle. All rights reserved.connected To:oracle DatabaSe 10g Enterprise Edition Release 10.2.0.5.0-64bit productionwith The partitioning, Real application Clusters, OLAP, Dat A Miningand Real application testing optionssql> shutdown Immediatedatabase closed. Database dismounted. ORACLE instance shut down. Sql> Startuporacle instance started. Total System Global area 205520896 bytesfixed size 2095088 bytesvariable size 121636880 bytesdatabase buffers 75497 472 Bytesredo buffers 6291456 bytesdatabase mounted. Database opened. Sql> Show parameter ThreadName TYPE VALUE----------------------------------------------------------------------- ------parallel_threads_per_cpu integer 2thread integer 2sql> exitdisconnected from Oracle Database 10g Enterpri Se Edition Release 10.2.0.5.0-64bit productionwith The partitioning, Real application Clusters, OLAP, Data Miningand Rea L Application Testing options[[email protected] ~]$ ssh [email protected]last login:sat-17:54:14 fr Om Node1[[email protecTed] ~]$ sqlplus/as sysdbasql*plus:release 10.2.0.5.0-production on Sat 17:57:00 2014Copyright (c) 1982, 2010,  Oracle. All rights reserved.connected to an idle instance. Sql> Startuporacle instance started. Total System Global area 205520896 bytesfixed size 2095088 bytesvariable size 100665360 bytesdatabase buffers 96468 992 Bytesredo buffers 6291456 bytesdatabase mounted. Database opened. Sql> Show parameter ThreadName TYPE VALUE-----------------------------------------------------------------------    ------parallel_threads_per_cpu integer 2thread integer 1sql> desc gv$log Name Null? Type-----------------------------------------------------------------------------inst_id number group# number T hread# number sequence# number BYTES number Members number archived VARCHAR2 (3) STATUS VARCHAR2 (+) FIR   st_change# number First_time datesql> select inst_id,group#,thread# from Gv$log; inst_id group# thread#------------------------------1 1 1 1 2 1 1 3 2 1 4 2 2 1 1 2 2 1 2 3 2 2 4 rows selected. Sql> Show parameter ThreadName TYPE VALUE-----------------------------------------------------------------------   ------parallel_threads_per_cpu integer 2thread integer 1sql> select thread#,status,enabled from V$thread 2; thread# STATUS ENABLED------------------------1 Open public 2 open publicsql> ALTER DATABASE add logfile Threa D 3 Group 5 ' +dg4 ' size 50m;database altered. sql> ALTER DATABASE Add logfile member ' +DG4 ' to group 5;database altered. sql> ALTER DATABASE Add logfile thread 3 Group 6 ' +dg4 ' size 50m;database altered. sql> ALTER DATABASE Add logfile member ' +DG4 ' to group 6;database altered. sql> ALTER DATABASE enable thread 3;database altered.   Sql> select thread#,status,enabled from V$thread; thread# STATUS ENABLED------------------------1 Open Public 2 open  Public 3 CLOSED PRIVATE 

Conclusion: If we use the Add Logifle statement, the specified thread is greater than instancde_number, it will produce private thread (personal guess), only the role of private thread, has not been found to have special use

Oracle public redo thread and private redo thread