oracle在线添加日志组和日志组成员

最新推荐文章于 2025-01-27 19:45:28 发布

CsongSY

最新推荐文章于 2025-01-27 19:45:28 发布

阅读量1.2w

点赞数 1

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/cscscscsc/article/details/51679023

版权

Oracle 专栏收录该内容

39 篇文章

订阅专栏

1.在线添加日志组成员

SQL> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo01a.log';

Database altered.

SQL> alter database add logfile member '/u01/app/oracle/fast_recovery_area/orcl/redo01a.log' to group 1;

Database altered.

SQL> alter database add logfile member '/u01/app/oracle/fast_recovery_area/orcl/redo02a.log' to group 2;

Database altered.

SQL> alter database add logfile member '/u01/app/oracle/fast_recovery_area/orcl/redo03a.log' to group 3;

Database altered.

SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log','/u01/app/oracle/fast_recovery_area/orcl/redo04a.log') size 100m;

Database altered.

SQL> alter database add logfile group 5 ('/u01/app/oracle/oradata/orcl/redo05.log','/u01/app/oracle/fast_recovery_area/orcl/redo05a.log') size 100m;

Database altered.

我们在切换日志的时候，oracle会去找下一个状态为INACTIVE的日志组：

<pre code_snippet_id="1717292" snippet_file_name="blog_20160615_2_9483808" name="code" class="sql">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	      13  104857600	   512		2 NO  INACTIVE		      394447 15-JUN-16	     403542 15-JUN-16
	 2	    1	      14  104857600	   512		2 NO  ACTIVE		      403542 15-JUN-16	     403931 15-JUN-16
	 3	    1	      17  104857600	   512		2 NO  CURRENT		      403940 15-JUN-16	 2.8147E+14
	 4	    1	      15  104857600	   512		2 NO  ACTIVE		      403931 15-JUN-16	     403934 15-JUN-16
	 5	    1	      16  104857600	   512		2 NO  ACTIVE		      403934 15-JUN-16	     403940 15-JUN-16

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	      18  104857600	   512		2 NO  CURRENT		      403948 15-JUN-16	 2.8147E+14
	 2	    1	      14  104857600	   512		2 NO  ACTIVE		      403542 15-JUN-16	     403931 15-JUN-16
	 3	    1	      17  104857600	   512		2 NO  ACTIVE		      403940 15-JUN-16	     403948 15-JUN-16
	 4	    1	      15  104857600	   512		2 NO  ACTIVE		      403931 15-JUN-16	     403934 15-JUN-16
	 5	    1	      16  104857600	   512		2 NO  ACTIVE		      403934 15-JUN-16	     403940 15-JUN-16

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	      18  104857600	   512		2 NO  ACTIVE		      403948 15-JUN-16	     403958 15-JUN-16
	 2	    1	      19  104857600	   512		2 NO  CURRENT		      403958 15-JUN-16	 2.8147E+14
	 3	    1	      17  104857600	   512		2 NO  ACTIVE		      403940 15-JUN-16	     403948 15-JUN-16
	 4	    1	      15  104857600	   512		2 NO  INACTIVE		      403931 15-JUN-16	     403934 15-JUN-16
	 5	    1	      16  104857600	   512		2 NO  INACTIVE		      403934 15-JUN-16	     403940 15-JUN-16

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	      18  104857600	   512		2 NO  ACTIVE		      403948 15-JUN-16	     403958 15-JUN-16
	 2	    1	      19  104857600	   512		2 NO  ACTIVE		      403958 15-JUN-16	     403962 15-JUN-16
	 3	    1	      17  104857600	   512		2 NO  ACTIVE		      403940 15-JUN-16	     403948 15-JUN-16
	 4	    1	      20  104857600	   512		2 NO  CURRENT		      403962 15-JUN-16	 2.8147E+14
	 5	    1	      16  104857600	   512		2 NO  INACTIVE		      403934 15-JUN-16	     403940 15-JUN-16

2.删除在线日志组和日志组成员

删除日志组成员，如果发现不能删除，应该是状态CURRENT，只要切换日志组就好了。

SQL> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo01a.log';
alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo01a.log'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01a.log'
SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo01a.log';

Database altered.

删除日志组；

ALTER DATABASE DROP LOGFILE GROUP N;--这里的N就是你的日志组编号，如果不能删除，就和上面一样切换日志组。