Oracle-21-联机日志文件管理

日志相关介绍......

查看日志的工作状态信息

select * from v$log;

GROUP#	THREAD#	SEQUENCE#      BYTES	BLOCKSIZE  MEMBERS   ARCHIVED	STATUS	   FIRST_CHANGE#	FIRST_TIME	NEXT_CHANGE#	      NEXT_TIME
1	1	52	      52428800	  512	      2	        NO	CURRENT	     1314820	     2018/5/3 02:38:18	281474976710655 	
2	1	50	      52428800	  512	      2	        NO	INACTIVE     1295249	     2018/5/2 13:56:43	1311962	          2018/5/3 01:01:09
3	1	51	      52428800	  512	      2	        NO	ACTIVE	     1311962	     2018/5/3 01:01:09	1314820	          2018/5/3 02:38:18


Elapsed: 00:00:00.03

查看日志文件物理信息

select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                 IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
         3         ONLINE  +DATA/sundb/onlinelog/group_3.263.974086789                            NO
         3         ONLINE  +FLASH/sundb/onlinelog/group_3.259.974086789                           YES
         2         ONLINE  +DATA/sundb/onlinelog/group_2.262.974086785                            NO
         2         ONLINE  +FLASH/sundb/onlinelog/group_2.258.974086787                           YES
         1         ONLINE  +DATA/sundb/onlinelog/group_1.261.974086783                            NO
         1         ONLINE  +FLASH/sundb/onlinelog/group_1.257.974086785                           YES

6 rows selected.

Elapsed: 00:00:00.01

切换日志文件

alter system switch logfile;

GROUP#	THREAD#	SEQUENCE#	BYTES	BLOCKSIZE  MEMBERS  ARCHIVED	STATUS	  FIRST_CHANGE#	  FIRST_TIME	   NEXT_CHANGE#	        NEXT_TIME
1	  1	  52	      52428800	  512	     2	      NO	INACTIVE     1314820	2018/5/3 2:38:18     1314948	       2018/5/3 2:44:24
2	  1	  53	      52428800	  512	     2	      NO	CURRENT	     1314948	2018/5/3 2:44:24     281474976710655	
3	  1	  51	      52428800	  512	     2	      NO	INACTIVE     1311962	2018/5/3 1:01:09     1314820	       2018/5/3 2:38:18

查看日志切换历史数据

select to_char(first_time,'yyyymmddhh24'),count(1) from v$log_history group by to_char(first_time,'yyyymmddhh24') order by to_char(first_time,'yyyymmddhh24');

TO_CHAR(FI   COUNT(1)
---------- ----------
2018042203          3
2018042205          1
2018042301          1
2018042723          1
2018050122          1
2018050212         10
2018050213         33
2018050301          1
2018050302          1

9 rows selected.

Elapsed: 00:00:00.01

Oracle建议日志切换时间为15分钟到30分钟

调整日志切换频率：增加组的数量、增加成员的大小

  查看日志成员

select * from v$logfile;

GROUP#	STATUS	TYPE	               MEMBER	                        IS_RECOVERY_DEST_FILE
3		ONLINE	+DATA/sundb/onlinelog/group_3.263.974086789	        NO
3		ONLINE	+FLASH/sundb/onlinelog/group_3.259.974086789	        YES
2		ONLINE	+DATA/sundb/onlinelog/group_2.262.974086785	        NO
2		ONLINE	+FLASH/sundb/onlinelog/group_2.258.974086787	        YES
1		ONLINE	+DATA/sundb/onlinelog/group_1.261.974086783	        NO
1		ONLINE	+FLASH/sundb/onlinelog/group_1.257.974086785	        YES

alter database add logfile '+DATA/' size 100m;

select * from v$log;

GROUP#	THREAD#	SEQUENCE#	BYTES	BLOCKSIZE  MEMBERS   ARCHIVED	STATUS	FIRST_CHANGE#	  FIRST_TIME	    NEXT_CHANGE#	NEXT_TIME
1	1	52	      52428800	  512	     2         NO      INACTIVE	1314820	       2018/5/3 2:38:18	      1314948	      2018/5/3 2:44:24
2	1	53	      52428800	  512	     2         NO      CURRENT	1314948	       2018/5/3 2:44:24	  281474976710655	
3	1	51	      52428800	  512	     2         NO      INACTIVE	1311962	       2018/5/3 1:01:09	      1314820	      2018/5/3 2:38:18
4	1	0	      104857600	  512	     1         YES      UNUSED	0		      0	

alter database add logfile member '+FLASH/' to group 4;

select * from v$logfile;

GROUP#	STATUS	TYPE	              MEMBER	                      IS_RECOVERY_DEST_FILE
3		ONLINE	+DATA/sundb/onlinelog/group_3.263.974086789	     NO
3		ONLINE	+FLASH/sundb/onlinelog/group_3.259.974086789	     YES
2		ONLINE	+DATA/sundb/onlinelog/group_2.262.974086785	     NO
2		ONLINE	+FLASH/sundb/onlinelog/group_2.258.974086787	     YES
1		ONLINE	+DATA/sundb/onlinelog/group_1.261.974086783	     NO
1		ONLINE	+FLASH/sundb/onlinelog/group_1.257.974086785	     YES
4		ONLINE	+DATA/sundb/onlinelog/group_4.275.975121115	     NO
4	INVALID	ONLINE	+FLASH/sundb/onlinelog/group_4.260.975121483	     NO

删除成员

alter database drop logfile member '+FLASH/sundb/onlinelog/group_4.260.975121483';

GROUP#	STATUS	TYPE	              MEMBER	                      IS_RECOVERY_DEST_FILE
3		ONLINE	+DATA/sundb/onlinelog/group_3.263.974086789	     NO
3		ONLINE	+FLASH/sundb/onlinelog/group_3.259.974086789	     YES
2		ONLINE	+DATA/sundb/onlinelog/group_2.262.974086785	     NO
2		ONLINE	+FLASH/sundb/onlinelog/group_2.258.974086787	     YES
1		ONLINE	+DATA/sundb/onlinelog/group_1.261.974086783	     NO
1		ONLINE	+FLASH/sundb/onlinelog/group_1.257.974086785	     YES
4		ONLINE	+DATA/sundb/onlinelog/group_4.275.975121115	     NO

删除日志组

alter database drop logfile group 4;

GROUP#	STATUS	TYPE	              MEMBER	                      IS_RECOVERY_DEST_FILE
3		ONLINE	+DATA/sundb/onlinelog/group_3.263.974086789	     NO
3		ONLINE	+FLASH/sundb/onlinelog/group_3.259.974086789	     YES
2		ONLINE	+DATA/sundb/onlinelog/group_2.262.974086785	     NO
2		ONLINE	+FLASH/sundb/onlinelog/group_2.258.974086787	     YES
1		ONLINE	+DATA/sundb/onlinelog/group_1.261.974086783	     NO
1		ONLINE	+FLASH/sundb/onlinelog/group_1.257.974086785	     YES

删除成员的限制：当前组不能删，每组最后一个成员不能删，

删除组的限制：当前组不能删，活动组不能删，如果数据库只剩下两组日志，任何一组都不能被删除

由于redo log在切换的时候就会把数据覆盖掉，如果想把数据库的改变记录下来，可以使用数据库的归档模式，在归档模式下，当联机日志进行切换的时候，当前组的内容都会被归档进程做一个镜像备份并生成归档文件。

将数据库变为归档模式

查看当前数据库归档信息

archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     51
Current log sequence           53

正常关闭数据库

shut immediate

启动数据库到MOUNT状态

startup mount

将数据库更改为归档模式

alter database archivelog;

打开数据库

alter database open；

查看归档信息

archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     51
Next log sequence to archive   53
Current log sequence           53

切换日志文件，生成归档文件

alter system switch logfile;

select sequence#,name from v$archived_log;

SEQUENCE#	                         NAME
53	    +FLASH/sundb/archivelog/2018_05_03/thread_1_seq_53.260.975122575