oracle public redo thread and private redo thread

SQL> l 1* select thread#,status,enabled from v$thread SQL> / THREAD# STATUS ENABLED ---------- ------ -------- 1 OPEN PUBLIC SQL> select group#,thread#,status from v$log; GROUP# THREAD# STATUS ---------- ---------- ---------------- 4 1 INACTIVE 5 1 CURRENT SQL> col member for a60 SQL> select group#,member from v$logfile; GROUP# MEMBER ---------- ------------------------------------------------------------ 4 /home/app/oraten/oradata/oraten/redo04.log 5 /home/app/oraten/oradata/oraten/redo05.log SQL> 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 altered. SQL> select thread#,status,enabled from v$thread; THREAD# STATUS ENABLED ---------- ------ -------- 1 OPEN PUBLIC 2 CLOSED DISABLED SQL> alter database enable thread 2; Database altered. SQL> select thread#,status,enabled from v$thread; THREAD# STATUS ENABLED ---------- ------ -------- 1 OPEN PUBLIC 2 CLOSED PRIVATE SQL> show parameter thread NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_threads_per_cpu integer 2 thread integer 1 SQL> alter system set thread=2 scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 281018368 bytes Fixed Size 2095672 bytes Variable Size 222299592 bytes Database Buffers 50331648 bytes Redo Buffers 6291456 bytes Database mounted. Database opened. SQL> select thread#,status,enabled from v$thread; THREAD# STATUS ENABLED ---------- ------ -------- 1 CLOSED PUBLIC 2 OPEN PRIVATE SQL> select group#,thread#,status from v$log; GROUP# THREAD# STATUS ---------- ---------- ---------------- 4 1 INACTIVE 5 1 CURRENT 6 2 CURRENT 7 2 UNUSED SQL>

[oracle@node1 ~]$ crs_stat -t -v Name Type R/RA F/FT Target State Host ---------------------------------------------------------------------- ora.easy.db application 0/0 0/1 ONLINE ONLINE node1 ora....y1.inst application 0/5 0/0 ONLINE ONLINE node1 ora....y2.inst application 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 application 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 application 0/0 0/0 ONLINE ONLINE node2 [oracle@node1 ~]$ [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Sat Aug 30 17:47:08 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> desc v$thread Name Null? Type ----------------------------------------- -------- ---------------------------- THREAD# NUMBER STATUS VARCHAR2(6) ENABLED VARCHAR2(8) GROUPS NUMBER INSTANCE VARCHAR2(80) OPEN_TIME DATE CURRENT_GROUP# NUMBER SEQUENCE# NUMBER CHECKPOINT_CHANGE# NUMBER CHECKPOINT_TIME DATE ENABLE_CHANGE# NUMBER ENABLE_TIME DATE DISABLE_CHANGE# NUMBER DISABLE_TIME DATE LAST_REDO_SEQUENCE# NUMBER LAST_REDO_BLOCK NUMBER LAST_REDO_CHANGE# NUMBER LAST_REDO_TIME DATE SQL> select thread#,status,enabled from v$thread; THREAD# STATUS ENABLED ---------- ------ -------- 1 OPEN PUBLIC 2 OPEN PUBLIC SQL> show parameter thread NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_threads_per_cpu integer 2 thread integer 1 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options [oracle@node1 ~]$ ssh oracle@node2 Last login: Mon Aug 25 13:09:51 2014 from node3 [oracle@node2 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Sat Aug 30 17:48:27 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> show parameter thread NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_threads_per_cpu integer 2 thread integer 2 SQL> 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 immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options [oracle@node2 ~]$ exit logout Connection to node2 closed. [oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Sat Aug 30 17:55:54 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 205520896 bytes Fixed Size 2095088 bytes Variable Size 121636880 bytes Database Buffers 75497472 bytes Redo Buffers 6291456 bytes Database mounted. Database opened. SQL> show parameter thread NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_threads_per_cpu integer 2 thread integer 2 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options [oracle@node1 ~]$ ssh oracle@node2 Last login: Sat Aug 30 17:54:14 2014 from node1 [oracle@node2 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Sat Aug 30 17:57:00 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 205520896 bytes Fixed Size 2095088 bytes Variable Size 100665360 bytes Database Buffers 96468992 bytes Redo Buffers 6291456 bytes Database mounted. Database opened. SQL> show parameter thread NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_threads_per_cpu integer 2 thread integer 1 SQL> desc gv$log Name Null? Type ----------------------------------------- -------- ---------------------------- INST_ID NUMBER GROUP# NUMBER THREAD# NUMBER SEQUENCE# NUMBER BYTES NUMBER MEMBERS NUMBER ARCHIVED VARCHAR2(3) STATUS VARCHAR2(16) FIRST_CHANGE# NUMBER FIRST_TIME DATE SQL> 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 2 8 rows selected. SQL> show parameter thread NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_threads_per_cpu integer 2 thread integer 1 SQL> select thread#,status,enabled from v$thread 2 ; THREAD# STATUS ENABLED ---------- ------ -------- 1 OPEN PUBLIC 2 OPEN PUBLIC SQL> alter database add logfile thread 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