oracle重做日志组相关操作

最新推荐文章于 2023-10-10 12:12:51 发布

黄宝康

最新推荐文章于 2023-10-10 12:12:51 发布

阅读量1.4k

点赞数 2

分类专栏： oracle 数据库文章标签： oracle重做日志组相关操作

本文链接：https://blog.csdn.net/huangbaokang/article/details/90292877

版权

oracle 同时被 2 个专栏收录

217 篇文章 7 订阅

订阅专栏

数据库

87 篇文章 0 订阅

订阅专栏

1、查询数据库中的重做日志组

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
	 1	    1	      64   52428800	   512		1 NO  CURRENT		     1522229 17-MAY-19	 2.8147E+14
	 2	    1	      62   52428800	   512		1 NO  INACTIVE		     1502072 16-MAY-19	    1515210 17-MAY-19
	 3	    1	      63   52428800	   512		1 NO  INACTIVE		     1515210 17-MAY-19	    1522229 17-MAY-19

2、具体查看日志文件详情（位置）

SQL> select * from v$logfile;

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

3、创建重做日志组

SQL> alter database add logfile ('/u01/app/oraData/orcl/hbk01.rdo','/u01/app/oraData/orcl/hbk02.rdo') size 5M;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER					      IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ------------------------------
	 3	   ONLINE  /u01/app/oraData/orcl/redo03.log		      NO
	 2	   ONLINE  /u01/app/oraData/orcl/redo02.log		      NO
	 1	   ONLINE  /u01/app/oraData/orcl/redo01.log		      NO
	 4	   ONLINE  /u01/app/oraData/orcl/hbk01.rdo		      NO
	 4	   ONLINE  /u01/app/oraData/orcl/hbk02.rdo		      NO

可以看到多了两个重做日志文件hbk01.rdo,hbk02.rdo，组的编号为4,查询v$log可以知道4号组的status为UNUSED，表示新建状态，未使用。

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
	 1	    1	      64   52428800	   512		1 NO  CURRENT		     1522229 17-MAY-19	 2.8147E+14
	 2	    1	      62   52428800	   512		1 NO  INACTIVE		     1502072 16-MAY-19	    1515210 17-MAY-19
	 3	    1	      63   52428800	   512		1 NO  INACTIVE		     1515210 17-MAY-19	    1522229 17-MAY-19
	 4	    1	       0    5242880	   512		2 YES UNUSED			   0			  0

可以看出我们创建重做日志组的编号默认系统产生
我们也可以知道组编号

SQL> alter database add logfile group 8 ('/u01/app/oraData/orcl/hbk10.rdo') size 5M;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER					      IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ------------------------------
	 3	   ONLINE  /u01/app/oraData/orcl/redo03.log		      NO
	 2	   ONLINE  /u01/app/oraData/orcl/redo02.log		      NO
	 1	   ONLINE  /u01/app/oraData/orcl/redo01.log		      NO
	 4	   ONLINE  /u01/app/oraData/orcl/hbk01.rdo		      NO
	 4	   ONLINE  /u01/app/oraData/orcl/hbk02.rdo		      NO
	 8	   ONLINE  /u01/app/oraData/orcl/hbk10.rdo		      NO

6 rows selected.

以上方式都是新创建重做日志组，并添加重做日志文件（成员），有的时候我们需要在原来的组上添加日志文件（成员），可以使用如下 to group语法，有一个知识点：同一个重做日志组中的日志文件（成员）大小都必须相同，所以在原有重做日志组中添加日志成员，不用指定大小。

SQL> alter database add logfile member '/u01/app/oraData/orcl/hbk8.rdo' to group 8;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER					      IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ------------------------------
	 3	   ONLINE  /u01/app/oraData/orcl/redo03.log		      NO
	 2	   ONLINE  /u01/app/oraData/orcl/redo02.log		      NO
	 1	   ONLINE  /u01/app/oraData/orcl/redo01.log		      NO
	 4	   ONLINE  /u01/app/oraData/orcl/hbk01.rdo		      NO
	 4	   ONLINE  /u01/app/oraData/orcl/hbk02.rdo		      NO
	 8	   ONLINE  /u01/app/oraData/orcl/hbk10.rdo		      NO
	 8 INVALID ONLINE  /u01/app/oraData/orcl/hbk8.rdo		      NO

7 rows selected.

在原来存在的日志组中添加日志成员，将显示INVALID ONLINE（不可访问）

4、删除重做日志组
删除重做日志组只是更新了控制文件，被删除的组所拥有的重做日志文件并没有从操作系统中删除，需要手工删除，而且只能删除状态是INAVTIVE的重做日志组，如果想删除当前（CURRENT）的重做日志组，必须手工切换，可以使用alter system switch logfile,而且每个数据库至少需要有两组重做日志组，因为要循环使用。
删除之前先查询下

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
	 1 CURRENT
	 2 INACTIVE
	 3 INACTIVE
	 4 UNUSED
	 8 UNUSED

像组2和组3都可以删，而组1为当前使用，不能删，除非切换使用日志组。删除之前先记下文件位置，方便手动执行操作系统rm删除命令

alter database drop  logfile group 2;

5、删除日志成员
需要注意的是，一个日志组中至少需要保留一个成员，而且只能删除状态不是ONLINE或CURRENT的日志成员。

SQL>  select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER					      IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ------------------------------
	 3	   ONLINE  /u01/app/oraData/orcl/redo03.log		      NO
	 2	   ONLINE  /u01/app/oraData/orcl/redo02.log		      NO
	 1	   ONLINE  /u01/app/oraData/orcl/redo01.log		      NO
	 4	   ONLINE  /u01/app/oraData/orcl/hbk01.rdo		      NO
	 4	   ONLINE  /u01/app/oraData/orcl/hbk02.rdo		      NO
	 8	   ONLINE  /u01/app/oraData/orcl/hbk10.rdo		      NO
	 8 INVALID ONLINE  /u01/app/oraData/orcl/hbk8.rdo		      NO
	 2 INVALID ONLINE  /u01/app/oraData/orcl/redo02_new.log 	      NO
	 1 INVALID ONLINE  /u01/app/oraData/orcl/redo01_new.log 	      NO

9 rows selected.

SQL> alter database drop logfile member '/u01/app/oraData/orcl/hbk10.rdo';
alter database drop logfile member '/u01/app/oraData/orcl/hbk10.rdo'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 8
ORA-01517: log member: '/u01/app/oraData/orcl/hbk10.rdo'


SQL> alter database drop logfile member '/u01/app/oraData/orcl/hbk8.rdo';

Database altered.

6、日志组切换状态
如果重做日志组的状态是CURRENT,则执行alter system switch logfile将变成其他状态。

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
	 1	    1	      64   52428800	   512		2 NO  CURRENT		     1522229 17-MAY-19	 2.8147E+14
	 2	    1	      62   52428800	   512		2 NO  INACTIVE		     1502072 16-MAY-19	    1515210 17-MAY-19
	 3	    1	      63   52428800	   512		1 NO  INACTIVE		     1515210 17-MAY-19	    1522229 17-MAY-19
	 4	    1	       0    5242880	   512		2 YES UNUSED			   0			  0
	 8	    1	       0    5242880	   512		1 YES UNUSED			   0			  0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
	 1	    1	      64   52428800	   512		2 NO  ACTIVE		     1522229 17-MAY-19	    1528474 17-MAY-19
	 2	    1	      62   52428800	   512		2 NO  INACTIVE		     1502072 16-MAY-19	    1515210 17-MAY-19
	 3	    1	      63   52428800	   512		1 NO  INACTIVE		     1515210 17-MAY-19	    1522229 17-MAY-19
	 4	    1	      65    5242880	   512		2 NO  CURRENT		     1528474 17-MAY-19	 2.8147E+14
	 8	    1	       0    5242880	   512		1 YES UNUSED			   0			  0

一开始是组1为CURRENT，切换后，组4为CURRENT，可以多次执行这个命令，只有一个组会变成CURRENT，关于哪个组会变成下一个CURRENT,我也不知道，应该有算法吧。

如果重做日志组的状态是ACTIVE，执行如下命令，会把状态变成INACTIVE

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
	 1	    1	      64   52428800	   512		2 NO  INACTIVE		     1522229 17-MAY-19	    1528474 17-MAY-19
	 2	    1	      62   52428800	   512		2 NO  INACTIVE		     1502072 16-MAY-19	    1515210 17-MAY-19
	 3	    1	      63   52428800	   512		1 NO  INACTIVE		     1515210 17-MAY-19	    1522229 17-MAY-19
	 4	    1	      65    5242880	   512		2 NO  ACTIVE		     1528474 17-MAY-19	    1528568 17-MAY-19
	 8	    1	      66    5242880	   512		1 NO  CURRENT		     1528568 17-MAY-19	 2.8147E+14

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC STATUS	       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
	 1	    1	      64   52428800	   512		2 NO  INACTIVE		     1522229 17-MAY-19	    1528474 17-MAY-19
	 2	    1	      62   52428800	   512		2 NO  INACTIVE		     1502072 16-MAY-19	    1515210 17-MAY-19
	 3	    1	      63   52428800	   512		1 NO  INACTIVE		     1515210 17-MAY-19	    1522229 17-MAY-19
	 4	    1	      65    5242880	   512		2 NO  INACTIVE		     1528474 17-MAY-19	    1528568 17-MAY-19
	 8	    1	      66    5242880	   512		1 NO  CURRENT		     1528568 17-MAY-19	 2.8147E+14

当前组4是ACTIVE状态，执行命令后变成INACTIVE状态了。
7、查看重做日志是否归档

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

    GROUP# STATUS	    ARC
---------- ---------------- ---
	 1 INACTIVE	    NO
	 2 INACTIVE	    NO
	 3 INACTIVE	    NO
	 4 INACTIVE	    NO
	 8 CURRENT	    NO

NO表示未归档，YES表示已归档。
因为我的没有开启归档模式，所以都显示NO

SQL> select name,log_mode from v$database;

NAME	  LOG_MODE
--------- ------------
ORCL	  NOARCHIVELOG

黄宝康

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录