oracle基础练习5章 重做日志

最新推荐文章于 2024-08-20 10:54:35 发布
最新推荐文章于 2024-08-20 10:54:35 发布
阅读量88 收藏
点赞数
文章标签： 数据库 操作系统
/* 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
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle怎么查看redo大小,Oracle调整redo log日志大小
weixin_42501035的博客
04-02 3364
最近发现数据的库存在日志频繁切换，查看了才发现redo log日志太小，进行在线调整。1.查看当前日志组成员SQL> select member from v$logfile;MEMBER------------------------------------------------------/u01/oracle/oradata/orcl/redo03.log/u01/oracle/or...
oracle日志重建,Oracle重做日志文件相关概念
weixin_30470785的博客
04-03 427
重做日志文件(redo log file)对于Oracle数据库至关重要。它们是数据库的事务日志。通常只用于恢复，不过也可以用于以下工作：q 系统崩溃后的实例恢复q 通过备份恢复数据文件之后恢复介质q 备用(standby)数据库处理q 输入到流中，这是一个重做日志挖掘过程，用于实现信息共享(这也是一种奇特的复制)重做日志文件的主要目的是，万一实例或介质失败，重做日志文件就能派上用场，或者可以作为...
Oracle数据库经常使用的一些命令
小范的技术空间
06-22 975
        1、  查看数据库的名字和归档状态 　　SQL> select name,log_mode from v$database; 　　2、  查看数据库的instance名字和状态 　　SQL> select instance_name,status from v$instance; 　　3、查看表空间名称、状态和
oracle常见命令大全,ORACLE常用命令大全
weixin_31893893的博客
04-06 162
检查数据库连通TNSPING数据库侦听lsnrctl查看SIDSQL> SELECT * FROM V$INSTANCE;SQL> SELECT * FROM V$DATABASE;SQL> / 执行上个操作命令SQL> EDIT读取命令缓存区查看语言配置SQL> show parameters nlsSQL> select * from V$NLS_PARAM...
oracle 重做日志详解
温情亿度网络科技
07-19 5174
重做日志文件（redologfile）对于Oracle数据库至关重要。它们是数据库的事务日志。通常只用于恢复，不过也可以用于以下工作： oracle视频详解教程视频地址 q系统崩溃后的实例恢复 q通过备份恢复数据文件之后恢复介质 q备用（standby）数据库处理 q输入到流中，这是一个重做日志挖掘过程，用于实现信息共享（这也是一种奇特的复制） 重做日志文件的主要目的是，万一实例...
oracle数据库基础练习题
11-03
本资料包含“Oracle数据库基础练习题”，旨在帮助初学者和进阶者巩固对Oracle基础知识的理解，同时也适合正在准备Oracle相关认证考试的人群。 练习题可能涵盖了以下几个核心领域： 1. **SQL语言基础**：这部分可能...
oracle 练习及答案 达内培训全五天
03-27
其中，SGA（System Global Area）是数据库的主要内存区域，包含数据缓冲区、重做日志缓冲区、共享池等关键组件。 2. SQL语言：SQL是与Oracle交互的主要工具，用于查询、插入、更新和删除数据。理解DML（Data ...
Oracle数据库基础知识(华为培训资料).pdf
03-27
- **物理结构**：阐述Oracle数据库在磁盘上的组织形式，包括数据文件、控制文件、重做日志文件等组成部分。 - **系统全局区（SGA）**：解释SGA的作用，它是数据库共享内存结构，包含数据缓冲区、重做日志缓冲区、...
oracle基础知识考试题_答案.zip
02-26
"oracle基础知识考试题_答案.docx"文档可能包含这些主题的练习题和解答，帮助学习者检验自己的理解和掌握程度。通过深入学习和实践，可以有效地掌握Oracle数据库的基础知识，为后续的进阶学习和实际工作打下坚实基础...
ORACLE重做日志文件基础知识
cc_0101的博客
05-24 3820
注：对重做日志文件的操作都需要以sys或者system用户登陆1、引入重做日志的目的为了数据库的恢复2、什么是重做日志组重做日志组是逻辑上的概念，由重做日志文件组成的。要保证oracle数据库正常工作，至少需要2个重做日志组，多数oracle版本默认3个重做日志组3、什么是重做日志成员重做日志组中的每个重做日志文件叫做重做日志成员。oracle默认每个重做日志组只有一个成员，但是oracle建议每...
Oracle修改redo log大小的方法
weixin_34355715的博客
08-08 397
目的:修改当前在线日志从默认50M增加至512M。 1.查看当前日志组的状态 SQL> select group#,members,bytes/1024/1024,status from v$log; GROUP# MEMBERS BYTES/1024/1024 STATUS ---------- ---------- -...
设置oracle数据库redo日志大小
一名小白的博客
06-12 817
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怎么查看redo大小,Oracle在线调整redo日志组数及组成员
weixin_39884492的博客
04-02 1752
Oracle在线调整redo日志组数及组成员一、调整redo日志组大小操作原因:redo日志一般设置让日志转换时间为10-20分钟,转换太频繁会影响性能。通常情况下每小时不要超过6次!如果AWR(Automated Workload Repository 自动负载信息库)report中log file switch (checkpoint incomplete)比较大,说明redo log之间的切...
oracle日志重建,在线重建redo log日志组
weixin_42110038的博客
04-03 490
-------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------...
日志组的管理
cuiduozhu1503的博客
12-16 94
--#日志组的添加 --查看日志组个数、成员、大小 SQL> select group#,members,bytes/1024/1024 m ,status from v$log; GROUP# M...
【达梦数据库】导入导出过程中get DDL复现过程
最新发布
weixin_47686079的博客
08-20 318
在处理问题的过程中，客户反馈在达梦数据库中导入导出分区表，get 表的DDL，不会出现表的所有分区信息，ORACLE数据库却会出现所有分区表，事实真的如此吗？
linux安装达梦数据库
HAN_789的博客
08-19 226
如果无法创建dmdba 文件夹，需要进入root用户下 在管理员用户下，这种安装方式一般只用于验证，开发，测试，生产最好还是安装传统的方式安装。在 home/dmdba/dmdbms/bin 目录下执行。（1）将文件上传到 /home/dmdba/ 目录下。（2）加压：tar -xzvf dmdb.tar.gz。进入到root就可以创建dmdba 文件夹了。1、后台启动，一般建议用后台启动。
Oracle数据库基础练习：修改列与SQL操作
- 选项`C`提及的“以循环方式写入”很可能指的是在Oracle数据库中使用循环（如`LOOP`）来处理重做日志文件，因为它们记录事务的更改并定期刷新。 8. **PL/SQL存储过程**： - 题目要求编写一个存储过程批量插入...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值