【练习】增加日志组数至4组，且每组日志成员大小为50M，每组2个成员。
2024-10-18 22:39:22
1.查看日志组成员路径及日志组大小、状态
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
SQL> select group#,bytes/1024/1024,members,status from v$log;
GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
1 50 1 INACTIVE
2 50 1 INACTIVE
3 50 1 CURRENT
2.增加group 4,group 5,group 6,group 7四组日志
SQL> alter database add logfile group 4
2 ('/u01/app/oracle/oradata/ORA11GR2/redo04_a.log',
3 '/u01/app/oracle/oradata/ORA11GR2/redo04_b.log') size 50M; Database altered. SQL> alter database add logfile group 5
2 ('/u01/app/oracle/oradata/ORA11GR2/redo05_a.log',
3 '/u01/app/oracle/oradata/ORA11GR2/redo05_b.log') size 50M; Database altered. SQL> alter database add logfile group 6
2 ('/u01/app/oracle/oradata/ORA11GR2/redo06_a.log',
3 '/u01/app/oracle/oradata/ORA11GR2/redo06_b.log') size 50M; Database altered. SQL> alter database add logfile group 7
2 ('/u01/app/oracle/oradata/ORA11GR2/redo07_a.log',
3 '/u01/app/oracle/oradata/ORA11GR2/redo07_b.log') size 50M; Database altered.
3.查看日志组状态
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
4 /u01/app/oracle/oradata/ORA11GR2/redo04_a.log
4 /u01/app/oracle/oradata/ORA11GR2/redo04_b.log
5 /u01/app/oracle/oradata/ORA11GR2/redo05_a.log
5 /u01/app/oracle/oradata/ORA11GR2/redo05_b.log
6 /u01/app/oracle/oradata/ORA11GR2/redo06_a.log
6 /u01/app/oracle/oradata/ORA11GR2/redo06_b.log
7 /u01/app/oracle/oradata/ORA11GR2/redo07_a.log
7 /u01/app/oracle/oradata/ORA11GR2/redo07_b.log
11 rows selected.
SQL> select group#,bytes/1024/1024,members,status from v$log;
GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
1 50 1 INACTIVE
2 50 1 INACTIVE
3 50 1 CURRENT
4 50 2 UNUSED
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED
7 rows selected.
4.切换日志查看日志组状态
SQL> alter system switch logfile;
System altered.
SQL> select group#,bytes/1024/1024,members,status from v$log;
GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
1 50 1 INACTIVE
2 50 1 INACTIVE
3 50 1 ACTIVE
4 50 2 CURRENT
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED
7 rows selected.
5.手工执行检查点事件
SQL> alter system checkpoint;
System altered.
SQL> select group#,bytes/1024/1024,members,status from v$log;
GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
1 50 1 INACTIVE
2 50 1 INACTIVE
3 50 1 INACTIVE
4 50 2 CURRENT
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED
7 rows selected.
6.删除group 1,group 2,group 3
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select group#,bytes/1024/1024,members,status from v$log;
GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
4 50 2 CURRENT
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED
注释：日志组的四种状态：
unused:表明该日志组从来没有被使用过，我们可以看到重建的日志组就是这个状态。
current:是oracle当前正在使用的日志组，即后台进程LGWR正在写的日志组。
active:当发生日志切换的时候，状态为current的日志组就会变成active状态，表明记录在这个日志组的事务提交所造成的数据块改变，还没有完全从database buffer cache 写入到data file中。当数据库处于archivelog mode时，ARCn就会归档这个日志组。所以处在这个状态的日志组是不允许被覆盖的。崩溃恢复需要该状态，可用于块恢复，可能归档也可能未归档。
inactive:处于这个状态的日志组，表明记录在这个日志组的事务提交所造成的数据块的改变，已经完全从database buffer cache写入到data file中了，实例恢复不再需要这个状态的日志组了，所以这个日志组是可以被覆盖的。
最新文章
- 【XLL API 函数】xlfUnregister (Form 1)
- 【JAVA基本数据类型包装类】
- WiFi破解
- BFGS方法
- 【翻译】Kinect v2程序设计(C++) Color篇
- LeetCode() Search a 2D MatrixII
- HDU 3068 [最长回文子串]
- entityFramework使用 codefirst
- 有关AES加密的问题
- D. DZY Loves Modification
- java中Log4J的使用笔记
- (三)Java工程化--Git起步
- 阿里云各Linux发行版netcore兼容性评估报告---来自大石头的测试
- echarts饼图去除鼠标移入高亮
- mysql数据库修改数据表引擎的方法
- jsp中的el表达式没有解析
- 761. Special Binary String
- Number的Util
- IE下的Firebug——IE WebDeveloper js debug
- 利用自定义 ORM 下使用 flask-login 做登录校验使用笔记