[基础知识] 重做日志相关操作

1. 添加 redo 日志组
alter database add logfile group 1 ('/data/redo02.log','/data/redo02.log') size 5m;

2. 添加 redo 日志文件到组
alter database add logfile member ('/data/redo01.log','/data/redo02.log') to group 1;

3. redo 日志成员重命名
shutdown immediate
startup mount
cp /old/data/redo01.log /new/data/redo01.log
alter database rename file '/old/data/redo01.log' to '/new/data/redo01.log';
alter database open;

4. 调整日志文件大小
将待调整的日志文件组置为 inactive，必要时候 alter system chekopoint;
alter system switch logfile;
alter database drop logfile gorup 1;
alter database add logfile group 1 ('/data/redo01.log','/data/redo02.log') size 10m reuse;
alter system switch logfile;

5. 归档 redo 日志
alter system archive log current;
alter system archive log all;

6. 清除已归档的日志组
alter database clear logfile group 1;

7. 清除未归档的日志组
alter database clear unarchived logfile group 1;

8. 相关视图
select * from v$log;
select * from v$logfile;
select * from v$log_history; -- 控制文件中日志的历史信息, 切换的时间应该尽可能的不低于 10-20 分钟
select * from v$archived_log; -- 从控制文件中获得归档的相关信息
select * from v$archive_dest; -- 归档路径及状态
select * from v$database; -- 查看数据库是否处于归档状态
select * from v$archive_processes; -- 归档相关的后台进程信息
select * from v$sgainfo where name in ('Fixed SGA Size','Redo Buffers','Granule Size'); -- 'Fixed SGA Size'+ 'Redo Buffers'是 granule size 的整数倍

9. 修改/查看 redo 归档/非归档模式
alter database archivelog
alter database noarchivelog
archive log list
SELECT log_mode FROM v$database

10. 设置归档日志参数
alter system set log_archive_max_processes=3;
alter system set log_archive_dest = '/u01/app/oracle/archivelog1' scope = spfile;
alter system set log_archive_duplex_dest = '/u01/app/oracle/archivelog2' scope = spfile;
alter system set log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelog3';
alter system set log_archive_dest_2 = 'SERVICE=standby1';
alter system set log_archive_format = 'arch_%t_%s_%r.arc';

11. LGWR触发条件
11-1. 用户提交
11-2. 有 1/3 重做日志缓冲区未被写入磁盘
11-3. 有大于 1M 的重做日志缓冲区未被写入磁盘
11-4. 每隔 3 秒钟
11-5. DBWR 需要写入的数据的 SCN 大于 LGWR 记录的 SCN, DBWR 触发 LGWR 写入