oracle public redo thread and private redo thread，oracleredo

oracle public redo thread and private redo thread，oracleredo

複習之前的學習內容，對public redo thread 和 private redo thread 的用處還是比較模糊，網上搜集的資料非常有限，看來有些好東西不跳牆是不行的。

廢話少說，知識點記錄下來：

在rac環境下，每個執行個體都有自己的redo log，這套redo log稱為redo thread。這套概念同樣適用於單一實例資料庫。

redo thread有兩種，private 和 public ，在預設情況下，我們使用的是public thread。但是如果我們在建立redol log時明確指定了thread參數，那麼該redo為private redo。

實驗如下：

單一實例：

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環境下：

 [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

結論：如果我們使用add logifle 語句時，指定的thread大於instancde_number,就會產生private thread(個人猜測),只有private thread的作用，目前尚未發現有特別的用處

由於突然斷電，oracle 資料庫不可以啟動 提示ORA-01092：oracle執行個體終止強制中斷連線 然後查看alter日誌如

SQL>conn / as sysdba;
SQL>shutdown immediate;
SQL>startup mount;
SQL>show parameter undo;(這裡可以查看undo的資料表空間管理方式應該是auto)
SQL>alter system set undo_management=manualscope=spfile;
SQL>shutdown immediate;
SQL>startup;
我們的思路是建立一個undo資料表空間,替換原來損壞的資料表空間.刪除掉原來的壞的undo資料表空間.如果不放心,還可以建個和以前損壞的資料表空間同名的undo資料表空間再替換回來.這裡設建立undo資料表空間名為undo2,原來環掉的資料表空間名為undotbs1.
SQL>create undo tablespace undo2 datafile'd:\oracle\oradata\orcl\undo2.dbf' size 200M;
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 200M;
SQL>alter system set undo_tablespace=undotbs1scope=spfile;
SQL>drop tablespace undo2 including contents anddatafiles;
到這裡原來的undo資料表空間就恢複了.記得把管理方式設定為auto.很簡單不再羅嗦.
 
oracle redo 與undo

1、記錄資料庫的變化，比如update、insert、delete等動作。
2、並非直接寫入undo資料表空間，只有當髒資料區塊達到一定程度才寫入。