Management of database Archiving mode

Oracle will generate a lot of archivelog files in the specified archive directory after the archive mode is turned on.

And the default is not cleaned up regularly, the time is long, the folder will occupy a lot of space.
Once the database has been turned on, the integrity of the data can be guaranteed, but the system performance is somewhat consumed.

Another key point is to increase the cost of maintenance, and therefore need to do a good job of management, the actual production environment has many due to the opening of the log archive, did not do a good job of management led to a variety of failures.
Many people delete files directly in the archive directory, which in fact does not achieve the effect of deleting file records in an Oracle CLF file.

There are two common methods of management:

1. Configure Rman to automatically manage Archivelog. You can also back up data to a backup server or tape in Rman, and then delete the expired archivelog;
Delete Archive script:/home/oracle/backup/rman_backup/rman_script/deletearch.sh

 rman_log_file=/home/oracle/backup/rman_backup/rman_ Script/deletearch.out if [-F "$RMAN _log_file"] then Rm-f "$RMAN _log_file" fi echo >> $RMAN _log _file chmod 666 $RMAN _log_file echo Script $ >> $RMAN _log_file echo = started on ' date ' = = >> $ Rman_log_file echo >> $RMAN _log_file export oracle_base=/home/oracle/u01/app/oracle export oracle_home= ${oracle_base}/product/11.2.0/dbhome_1 Export Oracle_path=${oracle_home}/rdbms/admin Export ORACLE_SID=ORCL Expor T path=${path}:${oracle_home}/bin::~/local/bin oracle_user=oracle $ORACLE _home/bin/rman target/log $RMAN _log_file append<< all; Archivelog crosscheck? sysdate-7?; Time until noprompt delete {run>  

2, can be processed by hand, the steps are as follows
(1) Move the relevant Archivelog log file under/oracle to another file system (keep the Archivelog log for a period of time, and the other can be removed and removed with the system command MV). You can then package, compress, and back up to the media, and delete the moved files. Note: Do not pack in the original/oracle, otherwise the space is full of some trouble.
(2) Login with Oracle user, execute Rman target/. If more than one instance executes the Rman target username/password @ instance name at this time, enter Rman
(3) Execute in Rman

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

###################
Useful Query Scripts:
1. Query the daily archive backup size situation:

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;

2. View Archive space usage:

SQL> col name for a60SQL> select NAME,SPACE_LIMIT/1024/1024/1024,SPACE_USED/1024/1024/1024,NUMBER_OF_FILES from V$RECOVERY_FILE_DEST;NAME                                 SPACE_LIMIT/1024/1024/1024 SPACE_USED/1024/1024/1024 NUMBER_OF_FILES------------------------------------------------------------ -------------------------- ------------------------- ---------------/home/oracle/u01/app/oracle/account_flashback_area                      5         .09765625        2

3. What set of log files is used by the query system:

SQL> select * from v$log; GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME      NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------     1        1           7   52428800       512        1 YES INACTIVE              979398 2017-06-30 14:20:02       980053 2017-06-30 14:29:07     2        1           8   52428800       512        1 YES INACTIVE              980053 2017-06-30 14:29:07       990274 2017-06-30 17:38:43     3        1           9   52428800       512        1 NO  CURRENT              990274 2017-06-30 17:38:43   2.8147E+14

4. Query the log file for the group you are using:

SQL> col MEMBER for a60SQL> select * from v$logfile;    GROUP# STATUS  TYPE    MEMBER                            IS_---------- ------- ------- ------------------------------------------------------------ ---     3       ONLINE  /home/oracle/u01/app/oracle/oradata/orcl/redo03.log        NO     2       ONLINE  /home/oracle/u01/app/oracle/oradata/orcl/redo02.log        NO     1       ONLINE  /home/oracle/u01/app/oracle/oradata/orcl/redo01.log        NO

5. Force Log Switchover:

alter system switch logfile;

6. Query history log:

Sql> select * from V$log_history; RECID STAMP thread# sequence# first_change# first_time next_change# resetlogs_change# resetlogs_time----- ----- ---------- ---------- ---------- ------------- ------------------- ------------ ----------------- --------------- ----1 947091134 1 1 925702 2017-06-19 16:50:54 925918 925702 2017-06-19 16:50:5     4 2 947091237 1 2 925918 2017-06-19 16:52:14 930555 925702 2017-06-19 16:50:54     3 947091304 1 3 930555 2017-06-19 16:53:56 949834 925702 2017-06-19 16:50:54  4 947091545 1 4 949834 2017-06-19 16:55:03 962092 925702 2017-06-19 16:50:54 5 947092728 1 5 962092 2017-06-19 16:59:05 972833 925702 2017-06-19 16:50:54 6 948 032402 1 6 972833 2017-06-19 17:18:47 979398 925702 2017-06-19 16:50:54     7 948032947 1 7 979398 2017-06-30 14:20:02 980053 925702 2017-06-19 16:50:54  8 948044323 1 8 980053 2017-06-30 14:29:07 990274 925702 2017-06-19 16:50:548 rows Selected.

7. The archive mode of the query log:

SQL> select dbid,name,created,log_mode from v$database;      DBID NAME                             CREATED         LOG_MODE---------- ------------------------------------------------------------ ------------------- ------------1474871786 ORCL                             2017-06-19 16:50:50 ARCHIVELOG

8. Query the archive log for information:

SQL> col name for a100SQL> select recid,stamp,thread#,sequence#,name from v$archived_log;     RECID    STAMP     THREAD#  SEQUENCE# NAME---------- ---------- ---------- ---------- ----------------------------------------------------------------------------------------------------     1  948032402           1      6 /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf     2  948032947           1      7 /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/archarchive_1_7_947091054.log     3  948044324           1      8 /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch_1_8_947091054.log

9. Adding and deleting log file groups

alter database add logfile group 1 (‘/home1/oracle/oradata/ora8i/log1a.log‘),‘/home2/oracle/oradata/ora8i/log1b.log‘) size 100M;alter database drop logfile group 1;

10. Adding and deleting log members

alter database add logfile member ‘/home1/oracle/oradata/ora8i/log1a.log‘ to group 1,‘/home1/oracle/oradata/ora8i/log2a.log‘ to group 2;alter database drop logfile member ‘/home1/oracle/oradata/ora8i/log1a.log‘ ;

11. log File Movement

alter database rename file ‘/home1/oracle/oradata/ora8i/log1a.log‘ to ‘/home2/oracle/oradata/ora8i/log1a.log‘;

You must ensure that the log file is physically moved to a new directory before executing the command
12. Clear the Log file

alter database clear logfile ‘/home1/oracle/oradata/ora8i/log1a.log‘;

This command cannot be used when deleting a log with the delete Group and Group members command

Management of database Archiving mode