Oracle public redo thread and private redo thread, oracleredo

Oracle public redo thread and private redo thread, oracleredo

The previous study content is still vague for public redo thread and private redo thread, and the information collected on the Internet is very limited. It seems that some good things cannot be skipped.

Record the knowledge points:

In the rac environment, each instance has its own redo log, which is called a redo thread. This concept also applies to single-instance databases.

There are two types of redo threads: private and public. By default, we use public threads. However, if we specify the thread parameter when creating a redol log, 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 OPEN   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 altered.SQL> select thread#,status,enabled from v$thread;   THREAD# STATUS ENABLED---------- ------ -------- 1 OPEN   PUBLIC 2 CLOSED DISABLEDSQL> alter database enable thread 2;Database altered.SQL> select thread#,status,enabled from v$thread;   THREAD# STATUS ENABLED---------- ------ -------- 1 OPEN   PUBLIC 2 CLOSED PRIVATESQL> show parameter threadNAME     TYPE 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   50331648 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 UNUSEDSQL> 

Rac environment:

 [oracle@node1 ~]$ crs_stat -t -vName           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 sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Sat Aug 30 17:47:08 2014Copyright (c) 1982, 2010, 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> 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     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 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options[oracle@node1 ~]$ ssh oracle@node2Last login: Mon Aug 25 13:09:51 2014 from node3[oracle@node2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Sat Aug 30 17:48:27 2014Copyright (c) 1982, 2010, 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 parameter 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 Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options[oracle@node2 ~]$ exitlogoutConnection to node2 closed.[oracle@node1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Sat Aug 30 17:55:54 2014Copyright (c) 1982, 2010, 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> 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   75497472 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 Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options[oracle@node1 ~]$ ssh oracle@node2Last login: Sat Aug 30 17:54:14 2014 from node1[oracle@node2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Sat Aug 30 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   96468992 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 THREAD#    NUMBER SEQUENCE#    NUMBER BYTES    NUMBER MEMBERS    NUMBER ARCHIVED    VARCHAR2(3) STATUS     VARCHAR2(16) FIRST_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       28 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 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

Conclusion: if we use the add logifle statement, if the specified thread is greater than instancde_number, private thread will be generated (personal guess). Only private thread can be used. It has not been found to be of special use yet.

Oracle Database cannot start prompt ORA-01092 due to sudden power failure: oracle instance stops force disconnect and then view alter log as shown in

SQL> conn/as sysdba;
SQL> shutdown immediate;
SQL> startup mount;
SQL> show parameter undo; (here we can see that the tablespace Management Mode of undo should be auto)
SQL> alter system set undo_management = manualscope = spfile;
SQL> shutdown immediate;
SQL> startup;
Our idea is to create an undo tablespace and replace the damaged tablespace. delete the original bad undo tablespace. if you are not at ease, you can create an undo tablespace with the same name as the previously damaged tablespace and then replace it with it. here, the name of the newly created undo tablespace is undo2, and the name of the originally closed tablespace is undotbs1.
SQL> create undo tablespace undo2 datafile 'd: \ oracle \ oradata \ orcl \ undo2.dbf 'size 200 M;
SQL> alter system set undo_tablespace = undo2scope = spfile;
SQL> drop tablespace undotbs1 including contents anddatafiles;
SQL> create undo tablespace undotbs1datafile 'd: \ oracle \ oradata \ orcl \ undotbs01.dbf' size 200 M;
SQL> alter system set undo_tablespace = undotbs1scope = spfile;
SQL> drop tablespace undo2 including contents anddatafiles;
At this point, the original undo tablespace is restored. Remember to set the management mode to auto. It's easy not to worry about it.
 
Oracle redo and undo

1. Record Database changes, such as update, insert, and delete.
2. It is not directly written to the undo tablespace. It is written only when the dirty data block reaches a certain level.