oracle基础练习5章 重做日志

最新推荐文章于 2025-11-30 16:29:01 发布
转载 最新推荐文章于 2025-11-30 16:29:01 发布 · 131 阅读 · 0
文章标签：

#数据库 #操作系统

/* 2008/06/12   星期四
＊蒙昭良
*环境：linux5 + Oracle10gR2
*oracle基础练习5章   
＊重做日志
*/
一、LGWR进程什么时候把redo log buffer中的数据写入redofile?
    1、当commit时
    2、当重做日志缓冲区的数据改变记录超过1M时
    3、当重做日志缓冲区的数据容量达到总容量的1／3时
    4、在DBWn进程把DataBase buffer Cache的数据写入数据文件之前
    5、每3秒

二、DBWn进程什么时候把DataBase Buffer Cache的 数据写入数据文件？
    1、当数据库缓冲区的数量超过所设定的限额
    2、当时间到了所设定的时间间隔
    3、当进程需要数据库高速缓冲区而找不到剩余空间时
    4、当发生检查点时
    5、当表被drop或者truncate时
    6、当表空间设置为read only 时
    7、当表空间进行类似的联机备份时，alter tablespace users Begin Backup
    8、当临时表空间设置成脱机offline或者正常normal状态时


SQL> alter system switch logfile;

System altered.

SQL> alter system archive log current;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> show parameter fast_start_mttr_target

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------
fast_start_mttr_target               integer     0                              



SQL> alter system set fast_start_mttr_target=900;

System altered.

SQL> show parameter fast_start_mttr_target;

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------
fast_start_mttr_target               integer     900                            
SQL> select group#,sequence#,members,bytes,status,archived from v$log;

    GROUP#  SEQUENCE#    MEMBERS      BYTES STATUS           ARC                
---------- ---------- ---------- ---------- ---------------- ---                
         1         67          1   52428800 INACTIVE         YES                
         2         68          1   52428800 INACTIVE         YES                
         3         69          1   52428800 CURRENT          NO                 

SQL> desc v$log;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP#                                             NUMBER
 THREAD#                                            NUMBER
 SEQUENCE#                                          NUMBER
 BYTES                                              NUMBER
 MEMBERS                                            NUMBER
 ARCHIVED                                           VARCHAR2(3)
 STATUS                                             VARCHAR2(16)
 FIRST_CHANGE#                                      NUMBER
 FIRST_TIME                                         DATE

SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                               
---------- ------- ------- --------------------------------------------------   
IS_                                                                             
---                                                                             
         2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log              
NO                                                                              
                                                                                
         1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log             
NO                                                                             
                                                                               
         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log             
NO                                                                             
                                                                               

SQL> alter database add logfile
  2  ('/u01/disk1/redo04a.log',
  3  '/u01/disk2/redo04b.log')
  4  size 15M;

Database altered.

SQL> select group#,sequence#,members,bytes/1024/1024 "MB",status,archived
  2  from v$log;

    GROUP#  SEQUENCE#    MEMBERS         MB STATUS           ARC               
---------- ---------- ---------- ---------- ---------------- ---               
         1         67          1         50 INACTIVE         YES               
         2         68          1         50 INACTIVE         YES               
         3         69          1         50 CURRENT          NO                
         4          0          2         15 UNUSED           YES               



                      

SQL> alter database drop logfile group 4;

Database altered.

SQL> select group#,sequence#,members,bytes,status,archived from v$log;

    GROUP#  SEQUENCE#    MEMBERS      BYTES STATUS           ARC               
---------- ---------- ---------- ---------- ---------------- ---               
         1         67          1   52428800 INACTIVE         YES               
         2         68          1   52428800 INACTIVE         YES               
         3         69          1   52428800 CURRENT          NO                

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                              
---------- ------- ------- --------------------------------------------------  
IS_                                                                            
---                                                                            
         2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log             
NO                                                                             
                                                                               
         1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log             
NO                                                                             
                                                                               
         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log             
NO                                                                             
                                                                               

SQL> alter database add logfile member
  2  '/u01/disk1/redo01b.log' to group 1,
  3  '/u01/disk2/redo02b.log' to group 2,
  4  '/u01/disk3/redo03b.log' to group 3;

Database altered.

SQL> select group#,sequence#,members,bytes,status,archived from v$log;

    GROUP#  SEQUENCE#    MEMBERS      BYTES STATUS           ARC               
---------- ---------- ---------- ---------- ---------------- ---               
         1         67          2   52428800 INACTIVE         YES               
         2         68          2   52428800 INACTIVE         YES               
         3         69          2   52428800 CURRENT          NO                

SQL> col member for a50
SQL> set line 100
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_                  
---------- ------- ------- -------------------------------------------------- ---                  
         2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log            NO                   
         1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log            NO                   
         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log            NO                   
         1 INVALID ONLINE  /u01/disk1/redo01b.log                             NO                   
         2 INVALID ONLINE  /u01/disk2/redo02b.log                             NO                   
         3 INVALID ONLINE  /u01/disk3/redo03b.log                             NO                   

6 rows selected.


SQL> alter database drop logfile member
  2  '/u01/disk2/redo02b.log';

Database altered.

SQL> select group#,sequence#,members,bytes,status,archived from v$log;

    GROUP#  SEQUENCE#    MEMBERS      BYTES STATUS           ARC                                   
---------- ---------- ---------- ---------- ---------------- ---                                   
         1         67          2   52428800 INACTIVE         YES                                   
         2         68          1   52428800 INACTIVE         YES                                   
         3         69          2   52428800 CURRENT          NO                                    

SQL> select * from v$controlfile
  2  ;

STATUS                                                                                             
-------                                                                                            
NAME                                                                                               
----------------------------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS                                                                      
--- ---------- --------------                                                                      
                                                                                                   
/u01/app/oracle/oradata/orcl/control01.ctl                                                         
NO       16384            450                                                                      
                                                                                                   
                                                                                                   
/u01/control02/control02.ctl                                                                       
NO       16384            450                                                                      

STATUS                                                                                             
-------                                                                                            
NAME                                                                                               
----------------------------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS                                                                      
--- ---------- --------------                                                                      
                                                                                                   
                                                                                                   
/u01/control03/control03.ctl                                                                       
NO       16384            450                                                                      
                                                                                                   

SQL> col name for a55;
SQL> select * from v$controlfile;

STATUS  NAME                                                    IS_ BLOCK_SIZE FILE_SIZE_BLKS      
------- ------------------------------------------------------- --- ---------- --------------      
        /u01/app/oracle/oradata/orcl/control01.ctl              NO       16384            450      
        /u01/control02/control02.ctl                            NO       16384            450      
        /u01/control03/control03.ctl                            NO       16384            450      

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host cp /u01/app/oracle/oradata/orcl/*.*  /u01/backup

SQL> host cp /u01/app/oracle/product/10.2.0/db_1/dbs  /u01/backup/dbs

SQL> host cp /u01/app/oracle/product/10.2.0/db_1/dbs/*.*  /u01/backup/dbs

SQL> startup
ORACLE instance started.

Total System Global Area  268435456 bytes                                                          
Fixed Size                  1218868 bytes                                                          
Variable Size              88082124 bytes                                                          
Database Buffers          171966464 bytes                                                          
Redo Buffers                7168000 bytes                                                          
Database mounted.
Database opened.
SQL> spool off






SQL> alter database clear logfile group 1;

Database altered.


SQL> l
  1* select group#,sequence#,members,bytes,status,archived from v$log
SQL> /

    GROUP#  SEQUENCE#    MEMBERS      BYTES STATUS           ARC                                   
---------- ---------- ---------- ---------- ---------------- ---                                   
         1          0          2   52428800 UNUSED           YES                                   
         2         68          1   52428800 INACTIVE         YES                                   
         3         69          2   52428800 CURRENT          NO                                    

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,members,bytes,status,archived from v$log;

    GROUP#  SEQUENCE#    MEMBERS      BYTES STATUS           ARC                                   
---------- ---------- ---------- ---------- ---------------- ---                                   
         1         70          2   52428800 CURRENT          NO                                    
         2         68          1   52428800 INACTIVE         YES                                   
         3         69          2   52428800 ACTIVE           YES                                   

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_                  
---------- ------- ------- -------------------------------------------------- ---                  
         2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log            NO                   
         1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log            NO                   
         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log            NO                   
         1         ONLINE  /u01/disk1/redo01b.log                             NO                   
         3 INVALID ONLINE  /u01/disk3/redo03b.log                             NO                   


SQL>
SQL> show parameter db_create_online_log_dest

NAME                                 TYPE        VALUE                                             
------------------------------------ ----------- ------------------------------                    
db_create_online_log_dest_1          string                                                        
db_create_online_log_dest_2          string                                                        
db_create_online_log_dest_3          string                                                        
db_create_online_log_dest_4          string                                                        
db_create_online_log_dest_5          string                                                        
SQL> alter system set db_create_online_log_dest_1='/u01/disk1';

System altered.

SQL> alter system set db_create_online_log_dest_2='/u01/disk2';

System altered.

SQL> alter system set db_create_online_log_dest_3='/u01/disk3';

System altered.

SQL> alter database add logfile;

Database altered.

SQL> select group#,sequence#,members,bytes,status,archived from v$log;

    GROUP#  SEQUENCE#    MEMBERS      BYTES STATUS           ARC                                   
---------- ---------- ---------- ---------- ---------------- ---                                   
         1         70          2   52428800 CURRENT          NO                                    
         2         68          1   52428800 INACTIVE         YES                                   
         3         69          2   52428800 ACTIVE           YES                                   
         4          0          3  104857600 UNUSED           YES                                   

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_                  
---------- ------- ------- -------------------------------------------------- ---                  
         2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log            NO                   
         1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log            NO                   
         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log            NO                   
         1         ONLINE  /u01/disk1/redo01b.log                             NO                   
         4         ONLINE  /u01/disk1/ORCL/onlinelog/o1_mf_4_450v238t_.log    NO                   
         3 INVALID ONLINE  /u01/disk3/redo03b.log                             NO                   
         4         ONLINE  /u01/disk2/ORCL/onlinelog/o1_mf_4_450v23tg_.log    NO                   
         4         ONLINE  /u01/disk3/ORCL/onlinelog/o1_mf_4_450v272g_.log    NO                   

8 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,members,bytes,status,archived from v$log;

    GROUP#  SEQUENCE#    MEMBERS      BYTES STATUS           ARC                                   
---------- ---------- ---------- ---------- ---------------- ---                                   
         1         70          2   52428800 ACTIVE           YES                                   
         2         72          1   52428800 ACTIVE           YES                                   
         3         73          2   52428800 CURRENT          NO                                    
         4         71          3  104857600 ACTIVE           YES                                   



SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,members,bytes,status,archived from v$log;

    GROUP#  SEQUENCE#    MEMBERS      BYTES STATUS           ARC                                   
---------- ---------- ---------- ---------- ---------------- ---                                   
         1         74          2   52428800 ACTIVE           YES                                   
         2         72          1   52428800 INACTIVE         YES   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-343444/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-343444/

css48419
关注
oracle数据库基础练习题
11-03
本资料包含“Oracle数据库基础练习题”，旨在帮助初学者和进阶者巩固对Oracle基础知识的理解，同时也适合正在准备Oracle相关认证考试的人群。 练习题可能涵盖了以下几个核心领域： 1. **SQL语言基础**：这部分可能...
oracle日志重建,Oracle重做日志文件相关概念
weixin_30470785的博客
04-03 511
重做日志文件(redo log file)对于Oracle数据库至关重要。它们是数据库的事务日志。通常只用于恢复，不过也可以用于以下工作：q 系统崩溃后的实例恢复q 通过备份恢复数据文件之后恢复介质q 备用(standby)数据库处理q 输入到流中，这是一个重做日志挖掘过程，用于实现信息共享(这也是一种奇特的复制)重做日志文件的主要目的是，万一实例或介质失败，重做日志文件就能派上用场，或者可以作为...
修改 Oracle 的 redo log 日志大小来缓解频繁写入 Oracle 数据造成的 io 压力
weixin_40715715的博客
07-12 755
从如上信息可以看出，目前数据库有三个成员，1 为 CURRENT，2，3 为 INACTIVE，大小为 50m，现在修改成 200m,500m 更好看需要。b. ACTIVE 是指活动的非当前日志，在进行实例恢复时会被用到。根据导出的 oracle 的 awr 报告分析发现数据的库存在日志频繁切换，经过排查发现 redo log 日志太小，所以进行如下调整。c. INACTIVE 是非活动日志，在实例恢复时不再需要，但在介质恢复时可能需要。a. CURRENT 指当前的日志文件，在进行实例恢复时是必须的；
oracle日志重建,在线重建redo log日志组
weixin_42110038的博客
04-03 643
-------8月在gx行做的，按照行内标准，实现了将原50M每组共5个redolog日志组重建成100M每组共10组。1 查看目前的redolog日志组，得知在用的为group4SQL> select group#,members,bytes/1024/1024,status from v$log;GROUP# MEMBERS BYTES/1024/1024 STATUS------...
Oracle数据库经常使用的一些命令
小范的技术空间
06-22 1046
        1、  查看数据库的名字和归档状态 　　SQL> select name,log_mode from v$database; 　　2、  查看数据库的instance名字和状态 　　SQL> select instance_name,status from v$instance; 　　3、查看表空间名称、状态和
oracle怎么查看redo大小,Oracle调整redo log日志大小
weixin_42501035的博客
04-02 4044
最近发现数据的库存在日志频繁切换，查看了才发现redo log日志太小，进行在线调整。1.查看当前日志组成员SQL> select member from v$logfile;MEMBER------------------------------------------------------/u01/oracle/oradata/orcl/redo03.log/u01/oracle/or...
Oracle基础练习与知识点总结
11. **数据库结构**：Oracle数据库包括物理结构（如数据文件、控制文件、重做日志文件等）和逻辑结构（如表、索引、视图等）。 12. **表空间管理**：表空间是逻辑数据组织，SYSTEM表空间是每个数据库的最小配置，...
Oracle数据库基础练习题解析
首先，在 Oracle 数据库的物理结构方面，题目明确指出其主要包括三种关键文件：数据文件（Data Files）、控制文件（Control Files）和重做日志文件（Redo Log Files）。这些文件共同构成了 Oracle 实例持久化存储的...
Oracle基础教程13章完整学习资料
《Oracle基础教程》是一套系统性讲解Oracle数据库技术的入门级学习资料，共包含13章内容（尽管子文件只列出了10个PDF文档，编号从2到12，缺少第1章和第9章可能为遗漏或合并处理），适用于初学者、IT技术人员以及希望...
Oracle数据库基础练习与知识点详解
5. 联机重做日志组：至少需要2个联机重做日志组，以实现日志切换，保证数据库的正常运行和恢复。 6. Oracle实例组成部分：Oracle实例由内存结构（如SGA）和后台进程组成，其中SGA包括数据缓冲区高速缓存、PGA等，`...
oracle常见命令大全,ORACLE常用命令大全
weixin_31893893的博客
04-06 219
检查数据库连通TNSPING数据库侦听lsnrctl查看SIDSQL> SELECT * FROM V$INSTANCE;SQL> SELECT * FROM V$DATABASE;SQL> / 执行上个操作命令SQL> EDIT读取命令缓存区查看语言配置SQL> show parameters nlsSQL> select * from V$NLS_PARAM...
日志组的管理
cuiduozhu1503的博客
12-16 134
--#日志组的添加 --查看日志组个数、成员、大小 SQL> select group#,members,bytes/1024/1024 m ,status from v$log; GROUP# M...
设置oracle数据库redo日志大小
一名小白的博客
06-12 895
1、查看当前日志组成员 sql> select member from v$logfile; 2、查看当前日志组状态 sql> select group#,members,bytes/1024/1024.status from v$log; 3、增加日志组 sql> alter database add logfile group 4 ('/opt/oracle/oradata/oral/redo04.log') size 200M; sql> alter database add
ORACLE重做日志文件基础知识
cc_0101的博客
05-24 4188
注：对重做日志文件的操作都需要以sys或者system用户登陆1、引入重做日志的目的为了数据库的恢复2、什么是重做日志组重做日志组是逻辑上的概念，由重做日志文件组成的。要保证oracle数据库正常工作，至少需要2个重做日志组，多数oracle版本默认3个重做日志组3、什么是重做日志成员重做日志组中的每个重做日志文件叫做重做日志成员。oracle默认每个重做日志组只有一个成员，但是oracle建议每...
Oracle修改redo log大小的方法
weixin_34355715的博客
08-08 462
目的:修改当前在线日志从默认50M增加至512M。 1.查看当前日志组的状态 SQL> select group#,members,bytes/1024/1024,status from v$log; GROUP# MEMBERS BYTES/1024/1024 STATUS ---------- ---------- -...
oracle怎么查看redo大小,Oracle在线调整redo日志组数及组成员
weixin_39884492的博客
04-02 1978
Oracle在线调整redo日志组数及组成员一、调整redo日志组大小操作原因:redo日志一般设置让日志转换时间为10-20分钟,转换太频繁会影响性能。通常情况下每小时不要超过6次!如果AWR(Automated Workload Repository 自动负载信息库)report中log file switch (checkpoint incomplete)比较大,说明redo log之间的切...
oracle 重做日志详解
温情亿度网络科技
07-19 5297
重做日志文件（redologfile）对于Oracle数据库至关重要。它们是数据库的事务日志。通常只用于恢复，不过也可以用于以下工作： oracle视频详解教程视频地址 q系统崩溃后的实例恢复 q通过备份恢复数据文件之后恢复介质 q备用（standby）数据库处理 q输入到流中，这是一个重做日志挖掘过程，用于实现信息共享（这也是一种奇特的复制） 重做日志文件的主要目的是，万一实例...
MySQL数据库的数据文件保存在哪？MySQL数据存在哪里
最新发布
2509_94083514的博客
11-30 779
在安装好MySQL数据库使用一段时间后，会产生许多的数据库和数据。那这些数据库的数据文件存放在本地文件夹的什么位置呢一、默认位置MySQL创建的数据库实际上存储在文件系统的一个特定目录中，该目录通常为MySQL的数据目录。这个目录的默认位置依据操作系统和MySQL的安装方式不同而有所差异。、这个目录通常位于，它可能位于是个隐藏文件夹）**其中“X.X”是MySQL的版本号。目录（每个数据库都有一个与其同名的文件夹，而这个文件夹中包含了该数据库的所有表的文件，而其中的文件代表了数据库中的表。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值