oracle public redo thread and private redo thread
来源：互联网 发布：乐视电视切换网络电视 编辑：程序博客网 时间：2024/06/08 16:24
复习之前的学习内容，对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的作用，目前尚未发现有特别的用处
1 0
- oracle public redo thread and private redo thread
- Oracle Redo and Undo
- oracle redo
- 如何修改thread 为public或private
- ORA-01618: redo thread 2 is not enabled - cannot mount
- RFS[18075]: No standby redo logfiles available for thread 1
- ORA-01618: redo thread 2 is not enabled - cannot mount
- redo
- redo
- redo
- Oracle学习笔记 Oracle IMU及Redo Private Strands技术
- 第二十七讲--Oracle IMU及Redo Private Strands技术
- 09 redo and undo
- Redo and Undo
- Oracle Internals - Redo
- Oracle: 关于 redo log
- Oracle Redo 并行机制
- oracle redo log 分析
- scala学习笔记2（类，继承，抽象类）
- github版本库使用详细教程(命令行及图形界面版)
- Leetcode--Permutation Sequence
- XMPP协议----IQ
- viewstate
- oracle public redo thread and private redo thread
- XMPP协议原理介绍
- 单例的两种创建方式
- 开发成功的oracle数据库
- 排序算法——选择排序(Selection Sort)
- 28 单元测试
- 利用OpenCV的Sobel算子求得图像的梯度信息
- JS删除数组指定对象或者指定下标 删除数组重复值
- vim配置
