度量快速开发平台-专业、快速的软件定制快开平台
标题: Oracle redo log 大小及组数的设置 [打印本页]
作者: 陈晓龙 时间: 2020-6-3 21:39
标题: Oracle redo log 大小及组数的设置
Oracle redo log 大小及组数的设置（在线修改日志组数目及组成员个数） 1.查看当前系统现有的redo log状况（组数/大小/名称/状态）
SYS@ORCL>desc v$log
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
1 50 INACTIVE YES
2 50 INACTIVE YES
3 50 CURRENT NO
SYS@ORCL>desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SYS@ORCL>select MEMBER from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_b0fh9w87_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_b0fh9wqw_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_b0fh9nk4_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_b0fh9o8x_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_b0fh9drr_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_b0fh9dxx_.log
6 rows selected. --查看日志文件的路径
2.添加3组大小为100M的日志组；
SYS@ORCL>alter database add logfile group 4 '/u01/app/oracle/oradata/ORCL/onlinelog/redo04.log'size 100m;
Database altered.
SYS@ORCL>alter database add logfile group 5'/u01/app/oracle/oradata/ORCL/onlinelog/redo05.log'size 100m;
Database altered.
SYS@ORCL>alter database add logfile group 6'/u01/app/oracle/oradata/ORCL/onlinelog/redo06.log'size 100m;
Database altered.
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
1 50 INACTIVE YES
2 50 INACTIVE YES
3 50 CURRENT NO
4 100 UNUSED YES
5 100 UNUSED YES
6 100 UNUSED YES
6 rows selected.
3.删除原有的日志组；（只操作状态为inactive的日志组）
若要操作日志组为current时，需先进行日志切换：alter system switch logfile;
若为active时，则可强制进行检查点：alter system checkpoint;
SYS@ORCL>alter system switch logfile;
System altered.
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
1 50 INACTIVE YES
2 50 INACTIVE YES
3 50 ACTIVE YES
4 100 CURRENT NO
5 100 UNUSED YES
6 100 UNUSED YES
6 rows selected.
SYS@ORCL>alter database drop logfile group 1;
Database altered.
SYS@ORCL>alter database drop logfile group 2;
Database altered.
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
3 50 ACTIVE YES
4 100 CURRENT NO
5 100 UNUSED YES
6 100 UNUSED YES
SYS@ORCL>alter system switch logfile;
System altered.
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
3 50 ACTIVE YES
4 100 ACTIVE YES
5 100 CURRENT NO
6 100 UNUSED YES
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
3 50 ACTIVE YES
4 100 ACTIVE YES
5 100 CURRENT NO
6 100 UNUSED YES
SYS@ORCL>alter system checkpoint; --强制执行检查点
System altered.
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
3 50 INACTIVE YES
4 100 ACTIVE YES
5 100 CURRENT NO
6 100 UNUSED YES
SYS@ORCL>alter database drop logfile group 3;
Database altered.
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
4 100 ACTIVE YES
5 100 CURRENT NO
6 100 UNUSED YES
4.向新创建的日志组添加成员
注意： 若在生产库中，则将新创建的成员放到不同的磁盘上
SYS@ORCL>alter system switch logfile;
System altered.
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
4 100 ACTIVE YES
5 100 ACTIVE YES
6 100 CURRENT NO
SYS@ORCL>alter system switch logfile;
System altered.
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
4 100 CURRENT NO
5 100 ACTIVE YES
6 100 ACTIVE YES
SYS@ORCL>alter database add logfile member'/u01/app/oracle/oradata/ORCL/onlinelog/redo06_b.log' to group 6;
Database altered.
SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
4 1 100 CURRENT NO
5 1 100 INACTIVE YES
6 2 100 ACTIVE YES
SYS@ORCL>alter database add logfile member '/u01/app/oracle/oradata/ORCL/onlinelog/redo05_b.log' to group 5;
Database altered.
SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
4 1 100 CURRENT NO
5 2 100 INACTIVE YES
6 2 100 ACTIVE YES
SYS@ORCL>alter system switch logfile;
System altered.
SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
4 1 100 ACTIVE YES
5 2 100 CURRENT NO
6 2 100 INACTIVE YES
SYS@ORCL>alter database add logfile member '/u01/app/oracle/oradata/ORCL/onlinelog/redo04_b.log' to group 4;
Database altered.
SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
4 2 100 ACTIVE YES
5 2 100 CURRENT NO
6 2 100 INACTIVE YES
SYS@ORCL>alter system checkpoint;
System altered.
SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;
GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
4 2 100 INACTIVE YES
5 2 100 CURRENT NO
6 2 100 INACTIVE YES
SYS@ORCL>select MEMBER from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/redo06_b.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo05_b.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo04_b.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo04.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo05.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo06.log
6 rows selected.
作者: 万望 时间: 2020-6-4 13:49
重做日志文件，俗称：redo log。在redolog中又分为两种：在线重做日志与归档日志。
ONLINE Redo log
在线重做日志(online redo log )主要用于：Oracle数据库所在服务器突然掉电、突然重启或者执行shutdown abort等命令使得在服务器重新启动之后，Oracle数据库没有办法正常的启动实例。此时，在线重做日志就派上了用场，Oracle会使用在线重做日志，把数据库恢复到服务器掉电前的那一个时刻，从而使得数据库能正常的启动起来 。
在Oracle数据库中，默认情况下，至少会有两个重做日志组，而且每个组里面至少包含了一个重做日志文件。日志组不会自动增加，在一个写满之后，会自动去写下一个。在下一个被写满之后会又从第一个开始写起。
作者: 万望 时间: 2020-6-4 13:50
Archive redo log
归档日志(archive log)主要用于硬件级别的错误：磁盘的坏道导致无法读写、写入的失败、磁盘受损导致数据库数据丢失。这就要使用归档日志文件，通过归档日志文件，把数据库恢复到归档日志所在的时间点上然后再通过在线重做日志文件把数据库恢复到当前的时间点上。
对于归档日志文件，可以理解为在线重做日志文件的备份。即当一个重做日志文件被填满了之后，归档日志文件就会把其备份保留一份。（因为上面说了，在线重做日志文件会自动的覆盖）所以，归档日志文件就是旧的在线日志文件的备份。
作者: 万望 时间: 2020-6-4 13:51
Archive redo log
归档日志(archive log)主要用于硬件级别的错误：磁盘的坏道导致无法读写、写入的失败、磁盘受损导致数据库数据丢失。这就要使用归档日志文件，通过归档日志文件，把数据库恢复到归档日志所在的时间点上然后再通过在线重做日志文件把数据库恢复到当前的时间点上。
对于归档日志文件，可以理解为在线重做日志文件的备份。即当一个重做日志文件被填满了之后，归档日志文件就会把其备份保留一份。（因为上面说了，在线重做日志文件会自动的覆盖）所以，归档日志文件就是旧的在线日志文件的备份。
作者: 万望 时间: 2020-6-4 13:54
伟大的楼主你贴了一大把硬货，都不说说这是啥嘛？
作者: 陈晓龙 时间: 2020-6-4 21:59
看来你已经深入理解了
作者: 陈晓龙 时间: 2020-6-4 22:01
| 欢迎光临 度量快速开发平台-专业、快速的软件定制快开平台 (http://bbs.delit.cn/) |
Powered by Discuz! X3.2 |