Oracle ARCHIVE LOG

--ARCHIVE LOG

--开启归档
--示例
--rac开归档
--一个节点上执行
alter system set log_archive_dest_1='location=+DGARCH' sid='*';
--两个节点上执行
shutdown immediate;
startup mount;
alter database archivelog;
archive log list;
alter database open;
archive log list;

--关闭
--首先以grid用户关闭数据库
[grid@rac1 ~]$ srvctl stop database -d bddjprd -o immediate
--启动数据库到mount 
[grid@rac1 ~]$ srvctl start database -d bddjprd -o mount 
--任意节点关闭关闭归档 
alter database noarchivelog; 
--重新启动数据库：
[grid@rac1 ~]$ srvctl stop database -d bddjprd
[grid@rac1 ~]$ srvctl start database -d bddjprd 
--验证结果： 
SQL> archive log list

--参考
--方案1：
--单实例(如果是RAC所有节点都要执行)
--启动归档及修改归档路径
alter system set log_archive_dest_1='location=+ARCHDG' sid='*';
--重启DB
shutdown immediate;
startup mount;
--开启归档模式
alter database archivelog;
archive log list;
--修改闪回区大小及路径
alter database flashback off;
--启库
alter database open;
--查看归档
archive log list;
--查看闪回模式
select flashback_on from V$database;
--所有节点归档当前日志
alter system archive log current;

--方案2：
--修改归档路径
alter system set log_archive_dest_1='location=+ARCHDG' sid='*';
--1.shutdown immediate all database instances
srvctl stop database -d slprd
--2.startup database in mount mode
srvctl start database -d slprd -o mount
--3.enable archvive logging
sqlplus / as sysdba
alter database archivelog;
exit;
--4.stop database
srvctl stop database -d slprd
--5.restart all database instances
srvctl start database -d slprd
--6.verify archiving is enabled by means of:
archive log list;
alter system archive log current;
--这儿如果从归档切换回非归档，按照同样的步骤设置为NOARCHIVELOG即可

--查看闪回模式
select flashback_on from V$database;
--修改闪回区大小及路径
alter database flashback off;
alter system set db_recovery_file_dest_size=5G scope=both;
alter system set db_recovery_file_dest='/home/oracle/arch' scope=both;

--数据库归档模式管理
--登陆
rman target /
--列出所有的归档日志文件
list archivelog all;
-- 与物理归档日志文件保持同步，之前移走了一部分文件，因此执行此命令后会在/oracle目录下找不到的归档日志标记为expired
crosscheck archivelog all;
--列出所有expired（过期）的归档日志文件，此时你就可看到移走的归档日志文件均被标记为expired
list expired archivelog all;
--在oracle中删除所有过期的expired文件
delete noprompt expired archivelog all;
--再列出所有的归档日志文件，就可发现移走的日志文件被删掉了
list archivelog all;
--退出
exit;

--清除归档
方法1：
--进入归档路径，删除归档文件
--直接删除物理文件
--或：find /archivelog -xdev -mtime +3 -name "*.dbf" -exec rm -f {} \;
--再从控制文件中删除记录
rman target /
run {
delete noprompt obsolete;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
}

方法2：
--删除7天前的归档日志
delete archivelog all completed before 'sysdate-7';
--删除2小时前日志
delete archivelog all completed before 'sysdate-2/24';
--也可以直接用一个指定的日期来删除（删除2天前到现在的）
delete noprompt archivelog until time "sysdate -2";

--日常维护
--查询每日归档备份大小
SELECT TRUNC(FIRST_TIME) "日期",
       TRUNC(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024, 2) "大小（GB/DAY）"
  FROM V$ARCHIVED_LOG GROUP BY TRUNC(FIRST_TIME) ORDER BY 1 DESC;
  
--查看归档空间占用情况
col name for a20
select NAME,SPACE_LIMIT/1024/1024/1024,SPACE_USED/1024/1024/1024,NUMBER_OF_FILES from V$RECOVERY_FILE_DEST;

--强制日志切换
alter system switch logfile;

--查询历史日志
select * from v$log_history;

--查询日志的归档模式
archive log list;
select dbid,name,created,log_mode from v$database;

--查询归档日志的信息
set line 333
select recid,stamp,thread#,sequence#,name from v$archived_log;

--归档日志生成量_近一周
select logtime,
       count(*),
       round(sum(blocks * block_size) / 1024 / 1024 / 1024) gbsize
  from (select trunc(first_time, 'dd') as logtime, a.BLOCKS, a.BLOCK_SIZE
          from v$archived_log a
         where a.DEST_ID = 1
           and a.FIRST_TIME > trunc(sysdate - 7))
 group by logtime
 order by logtime desc;

--归档日志生成量_各个实例近一周
select THREAD#,
       logtime,
       count(*),
       round(sum(blocks * block_size) / 1024 / 1024) mbsize
  from (select THREAD#,
               trunc(first_time, 'dd') as logtime,
               a.BLOCKS,
               a.BLOCK_SIZE
          from v$archived_log a
         where a.DEST_ID = 1
           and a.FIRST_TIME > trunc(sysdate - 7))
 group by THREAD#, logtime
 order by THREAD#, logtime desc;

--归档日志生成量_每小时
SELECT
TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'999') "00",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'999') "01",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'999') "02",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'999') "03",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'999') "04",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'999') "05",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'999') "06",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'999') "07",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'999') "08",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'999') "09",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'999') "10",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'999') "11",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'999') "12",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'999') "13",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'999') "14",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'999') "15",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'999') "16",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'999') "17",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'999') "18",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'999') "19",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'999') "20",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'999') "21",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'999') "22",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'999') "23"
FROM
V$LOG_HISTORY
GROUP BY
TO_CHAR(FIRST_TIME,'YYYY-MM-DD')
ORDER BY 1 DESC;