Open / close the archive, increase the archive space and delete the archive (the archive is full)

1. Open archiving

Shut down the database first
Start up Mount
Change to archive mode: alter database archive;  
View results: archive log list
Start database: alter database open start database

2. Close the archive

1. Log in to the database: execute alter system set cluster_database = false scope=spfile;
2. Close node1 and node2 databases: shutdown immediate;
3. Start node1 to mount status: startup mount;
4. Execute close Archive: alter database noarchivelog;
5. Open database: alter database open;
6. Execute: alter system set cluster on node1_ database=true scope=spfile;
7. Close the database: shutdown immediate;
8. Start node1 database: startup
9. Start node2 database: startup
 

3. Increase archive log space

1. View archiving
sql>archive log list;

 

SYS@DBDJ1> set linesize 1000;
SYS@DBDJ1> set pagesize 1000;
SYS@DBDJ1> show parameter db_recovery_file_dest;
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest                string                 /u01/app/oracle/fast_recovery_
                                                           area
db_recovery_file_dest_size           big integer            4122M

 

 

 2. Increased to 50G

SQL>ALTER SYSTEM SET db_recovery_file_dest_size=50g scope=both;

3. Restart the database

SQL>shutdown immediate;
SQL>startup

4. View test results

SQL>show parameter db_recovery_file_dest --50G
SQL>sqlplus zz_**/password@10.126.29.241/jw

4. Delete archive logs regularly

It is recommended to use the command provided by RMAN to handle it. Because rm and find delete the actual archive log and free up space, but the corresponding archive information stored in the control file is not completely cleared.
 

1. How to clear archive logs

a. Delete manually
Use rm or find to delete. After deletion in this way, you can check whether the archive is invalid through crosscheck archive all in RMAN, as follows:
       rm -rf arch_816906485_1_10.arc 
    find /u02/database/GOBO1/archive/ -ctime +0 -delete
    RMAN> crosscheck archivelog all

b. Clear using RMAN
RMAN clearing method will automatically clear the archive log files on the disk and release the archive information of the corresponding archive log in the control file.
Archive logs can be cleared based on different conditions, such as SCN, SEQUENCE and TIME.
The above three methods can be combined with from, until, between and .. And so on clause to limit the scope, the way is flexible.
The following commands are used to verify the validity of archive logs, list invalid archive logs and how to clear archive logs, and list several common:
                 crosscheck archivelog all; --- > verify the availability of logs
          list expired archivelog all; --- > list all invalid archive logs
          delete archivelog until sequence 16; --- > delete all archived logs whose log sequence is 16 and before 16
          delete archivelog all completed before 'sysdate-7'; --- > delete the archive log 7 days before the system time, and the valid archive log in the flashback area will not be deleted
          delete archivelog all completed before 'sysdate - 1'; --- > ditto, 1 day ago
          delete archivelog from time 'sysdate-1'; --- > pay attention to this command and delete the archived logs from the system time within 1 day to the present
          delete noprompt archivelog all completed before 'sysdate'; --- > this command clears all archive logs
          delete noprompt archivelog all; --- > same as the previous command
 
2. Walkthrough using RMAN to clear archive logs
robin@SZDB:~> export ORACLE_SID=GOBO1
robin@SZDB:~> rman target /
 
Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jul 11 17:07:00 2013
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
connected to target database: GOBO1 (DBID=733951103)
 
RMAN> host;
 
robin@SZDB:~> cd /u02/database/GOBO1/archive/
robin@SZDB:/u02/database/GOBO1/archive> ls      
arch_816906485_1_10.arc      arch_816906485_1_12.arc  
arch_816906485_1_11.arc      arch_816906485_1_13.arc  
    ............
 
robin@SZDB:/u02/database/GOBO1/archive> rm -rf arch_816906485_1_10.arc arch_816906485_1_11.arc arch_816906485_1_12.arc
robin@SZDB:/u02/database/GOBO1/archive> exit;
exit                                         
host command complete                        
                                             
RMAN> crosscheck archivelog all;                 
released channel: ORA_DISK_1                                                                       
allocated channel: ORA_DISK_1                                                                      
channel ORA_DISK_1: sid=1075 devtype=DISK                                                          
archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_9.arc recid=2085 stamp=817211151 
validation failed for archived log                                                                 
archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_10.arc recid=2086 stamp=817250793
      ..............
validation succeeded for archived log                                                              
archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_90.arc recid=2166 stamp=820458049
Crosschecked 83 objects                    
 
RMAN> list expired archivelog all;
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time          Name
------- ---- ------- - ----------------- ----
2086    1    10      X 20130604 11:05:51 /u02/database/GOBO1/archive/arch_816906485_1_10.arc
2087    1    11      X 20130604 22:06:17 /u02/database/GOBO1/archive/arch_816906485_1_11.arc
2088    1    12      X 20130605 19:30:53 /u02/database/GOBO1/archive/arch_816906485_1_12.arc
 
RMAN> delete archivelog until sequence 16;
 
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1075 devtype=DISK
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time          Name
------- ---- ------- - ----------------- ----
2084    1    8       A 20130604 09:53:17 /u02/database/GOBO1/archive/arch_816906485_1_8.arc
                        .................
2092    1    16      A 20130607 22:03:23 /u02/database/GOBO1/archive/arch_816906485_1_16.arc
 
Do you really want to delete the above objects (enter YES or NO)? yes
          ...............
deleted archive log
archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_16.arc recid=2092 stamp=817516861
Deleted 9 objects
 
RMAN> delete archivelog all completed before 'sysdate-7';
 
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1075 devtype=DISK
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time          Name
------- ---- ------- - ----------------- ----
2093    1    17      A 20130608 00:01:00 /u02/database/GOBO1/archive/arch_816906485_1_17.arc
2094    1    18      A 20130608 18:00:17 /u02/database/GOBO1/archive/arch_816906485_1_18.arc
            ...........
deleted archive log
archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_72.arc recid=2148 stamp=819847035
Deleted 56 objects            
 
RMAN> list copy of database archivelog all;
 
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time          Name
------- ---- ------- - ----------------- ----
2149    1    73      A 20130703 23:17:13 /u02/database/GOBO1/archive/arch_816906485_1_73.arc
2150    1    74      A 20130704 22:00:19 /u02/database/GOBO1/archive/arch_816906485_1_74.arc
2151    1    75      A 20130704 22:04:40 /u02/database/GOBO1/archive/arch_816906485_1_75.arc
                       ...............
2164    1    88      A 20130709 23:19:34 /u02/database/GOBO1/archive/arch_816906485_1_88.arc
2165    1    89      A 20130710 13:00:34 /u02/database/GOBO1/archive/arch_816906485_1_89.arc
2166    1    90      A 20130710 22:02:44 /u02/database/GOBO1/archive/arch_816906485_1_90.arc
 
RMAN> delete archivelog from time 'sysdate-1';
 
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1075 devtype=DISK
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time          Name
------- ---- ------- - ----------------- ----
2165    1    89      A 20130710 13:00:34 /u02/database/GOBO1/archive/arch_816906485_1_89.arc
2166    1    90      A 20130710 22:02:44 /u02/database/GOBO1/archive/arch_816906485_1_90.arc
 
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_89.arc recid=2165 stamp=820447373
deleted archive log
archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_90.arc recid=2166 stamp=820458049
Deleted 2 objects
 
RMAN> delete archivelog all completed before 'sysdate - 1';
 
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1075 devtype=DISK
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time          Name
------- ---- ------- - ----------------- ----
2149    1    73      A 20130703 23:17:13 /u02/database/GOBO1/archive/arch_816906485_1_73.arc
              .......................
2164    1    88      A 20130709 23:19:34 /u02/database/GOBO1/archive/arch_816906485_1_88.arc
 
Do you really want to delete the above objects (enter YES or NO)? yes
     ................
archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_88.arc recid=2164 stamp=820414835
Deleted 16 objects
 
RMAN> sql " alter system archive log current";
 
sql statement:  alter system archive log current
 
RMAN> list copy of archivelog all;
 
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time          Name
------- ---- ------- - ----------------- ----
2167    1    91      A 20130711 01:00:48 /u02/database/GOBO1/archive/arch_816906485_1_91.arc
 
RMAN> delete noprompt archivelog all completed before 'sysdate';
 
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1075 devtype=DISK
 
List of Archived Log Copies
Key     Thrd Seq     S Low Time          Name
------- ---- ------- - ----------------- ----
2167    1    91      A 20130711 01:00:48 /u02/database/GOBO1/archive/arch_816906485_1_91.arc
deleted archive log
archive log filename=/u02/database/GOBO1/archive/arch_816906485_1_91.arc recid=2167 stamp=820517964
Deleted 1 objects
 

3. Simple shell script for clearing archive logs

 

 1 #For RAC environment or ASM that needs to clear the archive, it is more appropriate to use shell script to call RMAN
 2 #Secondly, if your archive is located in the flashback area, formulate a reasonable retention policy, which can also let Oracle automatically age useless archive logs
 3 robin@SZDB:~/dba_scripts/custom/bin> more clean_arch.sh 
 4 # +-------------------------------------------------------+
 5 # +    Clean archived log as specified time               |
 6 # +    Author : Robinson                                  |
 7 # +    Blog   : http://blog.csdn.net/robinson_0612        |
 8 # +    Usage  :                                           | 
 9 # +         clean_arch.sh $ORACLE_SID                     |
10 # +-------------------------------------------------------+
11 #
12 #!/bin/bash 
13 # --------------------
14 # Define variable
15 # --------------------
16  
17 if [ -f ~/.bash_profile ]; then
18 . ~/.bash_profile
19 fi
20  
21 if [ -z "${1}" ];then
22     echo "Usage: "
23     echo "      `basename $0` ORACLE_SID"
24     exit 1
25 fi
26  
27 ORACLE_SID=$1;                 export ORACLE_SID 
28 $ORACLE_HOME/bin/rman log=/users/robin/log/rman.log <<EOF   
29 connect target /
30 run{
31 crosscheck archivelog all;
32 delete noprompt expired archivelog all;
33 delete noprompt archivelog all completed before 'sysdate - 1';
34 }
35 exit;
36 EOF
37 exit 
38  

 

4. Summary
a. The best way to clear the archive log is to complete it in RMAN mode, which is the most thorough way
b. For the production environment, it should be considered to clear the archive log during RMAN backup, such as using delete input and delete all input to clear the archive log during backup archive all
c. If the archive log is not cleared during backup, arch will be very large, causing the archive disk to be full and the archive will fail. It is recommended to delete or consider storing it in the flashback area
d. delete noprompt archivelog all completed before 'sysdate - n' is used if you want to clear most and keep the latest
e. Detailed clear archive log syntax: http://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmsynta008.htm#RCMRF106

 

 
 
 
 
 
 
 
 
 
 
 
 

 

Posted by ramas on Wed, 04 May 2022 06:14:58 +0300