日志相关介绍......
查看日志的工作状态信息
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