Redo 日志管理
redo日志组中的日志文件互为镜像，他们存放相同的内容，可以起到备份的作用，为了起到备份的作用，可以把redo日志组的每个日志设置到不同的路径。
１．添加日志组
-
添加redo日志组
SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/ORCL/onlinelog/group_4_1.log' size 200M; Database altered. SQL> -
添加redo日志文件
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_REC CON_ID ------ ---------- ---------- ---------------------------------------------------------------------- ------ ---------- 3 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_kq1wb03p_.log NO 0 2 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_kq1wb023_.log NO 0 1 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_kq1w9zyc_.log NO 0 4 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/group_4_1.log NO 0 -
查看redo日志组信息
SQL> select group#, THREAD#, BYTES/1024/1024, STATUS from v$log; GROUP# THREAD# BYTES/1024/1024 STATUS ------ ---------- --------------- ---------- 1 1 200 INACTIVE 2 1 200 CURRENT 3 1 200 INACTIVE 4 1 200 UNUSED
2. 添加日志组成员
-
添加redo日志组成员
SQL> alter database add logfile member '/u01/app/oracle/oradata/ORCL/onlinelog/group_1_2.log' to group 1; Database altered. SQL> -
查看redo日志组成员
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_REC CON_ID ------ ---------- ---------- ---------------------------------------------------------------------- ------ ---------- 3 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_kq1wb03p_.log NO 0 2 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_kq1wb023_.log NO 0 1 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_kq1w9zyc_.log NO 0 4 ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/group_4_1.log NO 0 1 INVALID ONLINE /u01/app/oracle/oradata/ORCL/onlinelog/group_1_2.log NO 0 SQL>
3. 切换日志组
-
连续切换日志组
SQL> select group#, THREAD#, BYTES/1024/1024, STATUS from v$log; GROUP# THREAD# BYTES/1024/1024 STATUS ------ ---------- --------------- ---------- 1 1 200 INACTIVE 2 1 200 ACTIVE 3 1 200 INACTIVE 4 1 200 CURRENT SQL>SQL> select group#, THREAD#, BYTES/1024/1024, STATUS from v$log; GROUP# THREAD# BYTES/1024/1024 STATUS ------ ---------- --------------- ---------- 1 1 200 INACTIVE 2 1 200 ACTIVE 3 1 200 CURRENT 4 1 200 ACTIVE SQL>SQL> select group#, THREAD#, BYTES/1024/1024, STATUS from v$log; GROUP# THREAD# BYTES/1024/1024 STATUS ------ ---------- --------------- ---------- 1 1 200 CURRENT 2 1 200 ACTIVE 3 1 200 ACTIVE 4 1 200 ACTIVE SQL>
4.删除日志组
要删除的日志组必须要归档完毕，才可以删除，即STATUS为： INACTIVE， （如果STATUS为ACTIVE, 但是没有记录任何操作，直接删除该日志组也会删除）。
删除的日志组只是在数据库层面上的删除，实际上该日志组中的日志文件还是存在我们的文件系统上。
-
执行强制检查点
SQL> alter system checkpoint; System altered. SQL> -
删除日志组
SQL> alter database drop logfile group 4; Database altered. SQL> -
查看日志组信息
SQL> select group#, THREAD#, BYTES/1024/1024, STATUS from v$log; GROUP# THREAD# BYTES/1024/1024 STATUS ------ ---------- --------------- ---------- 1 1 200 CURRENT 2 1 200 INACTIVE 3 1 200 INACTIVE SQL> -
查看删除的日志组所在目录文件信息
可以看到删除的redo日志组的日志文件还是存在文件系统上的。
如果再次创建redo日志组4,并且执行的日志文件路径和名称与之前的一样的话，会报该文件已经存在。
[oracle@oracle ~]$ cd /u01/app/oracle/oradata/ORCL/onlinelog/ [oracle@oracle onlinelog]$ ls group_1_2.log group_4_1.log o1_mf_1_kq1w9zyc_.log o1_mf_2_kq1wb023_.log o1_mf_3_kq1wb03p_.log [oracle@oracle onlinelog]$ -
再次创建redo日志组4
再次创建报以下错误
SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/ORCL/onlinelog/group_4_1.log' size 200M; alter database add logfile group 4 '/u01/app/oracle/oradata/ORCL/onlinelog/group_4_1.log' size 200M * ERROR at line 1: ORA-00301: error in adding log file '/u01/app/oracle/oradata/ORCL/onlinelog/group_4_1.log' - file cannot be created ORA-27038: created file already exists Additional information: 1
-
删掉旧redo日志文件
[oracle@oracle onlinelog]$ rm -rf /u01/app/oracle/oradata/ORCL/onlinelog/group_4_1.log
再次创建redo日志组,创建成功
SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/ORCL/onlinelog/group_4_1.log' size 200M;
Database altered.
SQL> select group#, THREAD#, BYTES/1024/1024, STATUS from v$log;
GROUP# THREAD# BYTES/1024/1024 STATUS
------ ---------- --------------- ----------
1 1 200 CURRENT
2 1 200 INACTIVE
3 1 200 INACTIVE
4 1 200 UNUSED
SQL>ag-0-1girr8d4jag-1-1girr8d4j