At ORACLE10g and 11g versions, the default log archive path for ORACLE is the flash-back recovery area ($ORACLE_BASE/flash_recovery_area).ORACLE has a limitation on this path: it only has 2G of space by default, and it is not only the default path for archive logs, but also the default address for backup files and flash back logs, so that archive log locks use less than 2G of space. Without this path size set, many systems experience archive logs that are full and cannot be archived.The following SQL statement can be used to view the archive information, which causes the database to tamp.
1. When the database is open
Check if archiving is turned on
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 33 Current log sequence 35 SQL> select log_mode from v$database;---If is ARCHIVELOG Indicates that the archive has been successfully archived LOG_MODE ------------ NOARCHIVELOG
View, Disabled indicates that the archive is not open
2. Turn on archiving mode
2.1 Restart pending state
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. --If multiple libraries are installed, errors will occur and handles will not be found --exit Reuse Administrator Entry Total System Global Area 1258291200 bytes Fixed Size 1219160 bytes Variable Size 318768552 bytes Database Buffers 922746880 bytes Redo Buffers 15556608 bytes Database mounted.
This is where the real problem arises.mount keeps startup down countless times, meaning listeners can't listen to your current routine.Of course, if the listener is properly configured, this type of problem will not occur here.Now that there is a problem with the listener, start here.In the installation directory of the Oracle database (path may be oracle\product\10.2.0\db_1\NETWORK\ADMIN), you can find a parameter file such as listener.ora (hint is a problem with listening, but not necessarily, in the actual process, just turn off flickerback and archiving)
2.2 Modify Startup Archiving Mode
SQL> alter database archivelog; Database altered. --Open database SQL> alter database open; Database altered. --Check to see if it's turned on SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 15 Next log sequence to archive 17 Current log sequence 17
2.3 Modify the archive log directory and size
Looking at the parameter db_recovery_file_dest, the archive endpoint USE_DB_RECOVERY_FILE_DEST (which can be understood as USE DB_RECOVERY_FILE_DEST) defaults to the flashback recovery area ($ORACLE_BASE/flash_recovery_area), you can view the information of the flashback recovery area using the following SQL.
SQL> show parameter db_recover NAME TYPE VALUE -------------------------- ----------- ---------------------------- db_recovery_file_dest string oracle\flash_recovery_area db_recovery_file_dest_size big integer 2G
By default, the archive logs are stored in the flash_recovery_area (oracleflash_recovery_area). If the flash-back recovery area is already 2G, the archive logs may not be able to continue to archive. The database is tampered with. The usual solution is to increase the flash-back recovery area, which can be implemented with the following SQL.
There's something called spfile inside Oracle, a dynamic parameter file that sets Oracle's parameters.Dynamic means that you can change database parameters and record them in spfile without closing the database. Syntax: alter system set parameter = value scope=spfile; Note: The difference between SCOPE=SPFILE/MEMORY/BOTH in ALTER SYSTEM: SCOPE = SPFILE: This change is written to the initialization parameter file and will take effect the next time you start.Dynamic parameters are as good as static parameters.It is also the only way static parameters can be used. SCOPE = MEMORY: Modifications are made in memory only and take effect immediately, but will not take effect after restart because they are not written to the initialization parameter file.Dynamic parameters only, static parameters not allowed SCOPE = BOTH: The default option, which is written to the initialization parameter file and modified in memory, takes effect immediately.The same applies only to dynamic parameters, not static parameters
SQL> alter system set db_recovery_file_dest_size=3G; The system has changed.
Even if the current urgency is resolved in this way, although the flash-back recovery ORACLE will be automatically managed and the useless data will be cleaned up if the flash-back recovery area is insufficient, if the backup strategy is not perfect and the database is very busy, this situation may still be encountered. It is usually necessary to modify the path of the archive log and put the archive log in other places where it is not.Restricted paths solve this problem by modifying the path where archive logs are stored using the following SQL.
SQL> alter system set log_archive_dest_1='location=oracle\log1\archive_log'; //The system has changed. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination oracle\log1\archive_log Oldest online log sequence 30 Next log sequence to archive 32 Current log sequence 32
In fact, starting with Oracle 10g, you can generate multiple identical logs and save multiple locations in case of unexpected events, as follows
SQL>alter system set log_archive_dest_2='location=oracle\log2\archive_log'; SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination oracle\log1\archive_log Oldest online log sequence 30 Next log sequence to archive 32 Current log sequence 32
2.4 Modify the log file naming format:
//Set the number of archiving processes SQL> alter system set log_archive_max_processes = 5; The name of the archive log is restricted by the log_archive_format parameter and can be viewed by the following command. SQL> show parameter log_archive_format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf SQL> alter system set log_archive_format = "archive_%t_%s_%r.log" scope=spfile;
2.5 Archive the current redo log
Redo Archive
alter system archive log current;
View archive log
select name from v$archived_log;
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/oracle/archivelog1/archive_log1_5_927044427.dbf
Note:
<
The alter system switch logfile is a forced log switch and does not necessarily archive the current redo log file (if the automatic archive is opened, the redo log before the archive is opened, and if the automatic archive is not opened, the current redo log is not archived.)
alter system archive log current is an archive of the current redo log file, whether or not automatically archived.
The main difference is:
alter system switch logfile performs log switching on the current instance in a single instance database or RAC;
alter system archive log current performs log switching on all instances in the database.
>2.6 Restart the database
shutdown immediate; startup SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/app/oracle/archivelog2/archive_log Oldest online log sequence 9 Next log sequence to archive 11 Current log sequence 11
3. Modify to non-filing mode
3.1 Start the database in mount state
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2213776 bytes Variable Size 973080688 bytes Database Buffers 620756992 bytes Redo Buffers 7360512 bytes Database mounted.
3.2 Archive the current redo log
lter system archive log current;
3.3 Start the database in mount state
3.4 Change the archive mode to non-archive mode
SQL> alter database noarchivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /oracle/archivelog2/archive_log Oldest online log sequence 9 Current log sequence 11
IV. Archive Log Check
4.1 First look at the location of the archiv log
) Most likely, the archive log location is not in the flash-back recovery zone
SQL> show parameter log_archive_dest; --You'll see something like this NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest string log_archive_dest_1 string log_archive_dest_10 string --If at this time value Is empty, can try SQL> archive log list;
If archiving is turned on, you can see the log path in the Value column, but there may be multiple copies of the log. Note that
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest string log_archive_dest_1 string location=/oracle/archivelog1/archive_log NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string location=/oracle/archivelog2/archive_log
4.2 Usage
--- Here you can see the space usage in the flash-back recovery zone:
SQL> select * From v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES ------------ ------------------ ------------------------- --------------- CONTROLFILE .13 0 1 ONLINELOG 2.93 0 3 ARCHIVELOG 99.72 0 122 BACKUPPIECE 0 0 0 IMAGECOPY 0 0 0 FLASHBACKLOG 0 0 0
This is just a case, and if it's almost full, more than 99% of the log space is used
--- Note that this step is really a step toward viewing the actual usage of archive space
-- I haven't tested it, and I'll verify it later, as I said in the text
SQL> select * from v$recovery_file_dest; NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ----------- ---------- ----------------- ---- --------------- ------------------- /oracle/flash_recovery_area 2147483648 1804771840 0 51
5. Full disposal
Full archive log space may prevent remote login, so after ssh arrives at the server, use sqlplus sys/pass as sysdba to login to oracle.
Use the backup recovery tool RMAN that comes with ORACLE here
5.1 Setting environment variable ORACLE_SID
Before connecting to a local database using RMAN, you must first set the operating system environment variable ORACLE_SID and specify that the value is equal to the instance name of the target database.If the local library has only one instance and the ORACLE_SID environment variable has been set, you do not need to specify the ORACLE_SID.RMAN automatically connects to the default instance.
SET ORACLE_SID =jssbook
5.2 Start RMAN Connection Database
If you connect to a local database, use
RMAN TARGET / Logon Administrator Account
If you connect remotely, use RMAN TARGET SYS/CHANGE_ON_INSTALL@TESTDB, for example: RMAN TARGET SYS/sysadmin@orcl
A valid network service name (Net Service Name) must be specified when establishing a connection, and the correct configuration of the network service name must have been established in the local tnsname.ora file
5.3 View archive log status
$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Nov 7 17:26:27 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: jssbook(DBID=1314906249)
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name WETALK
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 5 A 04-NOV-16
Name: /oracle/archivelog1/archive_log1_5_927044427.dbf
1 5 A 04-NOV-16
Name: /oracle/archivelog2/archive_log1_5_927044427.dbf5.3 Delete archive logs manually
Method 1:
Delete the physical archive log first, then execute the following command
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; Explain: SYSDATA-7, indicating that 7 days before the current system time, the before keyword indicates the archive log 7 days ago. If the flickerback function is used, the flickered back data will also be deleted. Similarly, you can delete all logs from 7 days ago to now, but this command needs to be considered clearly. To complete this deletion, it is best to make a full backup of the database immediately DELETE ARCHIVELOG from TIME'SYSDATE-7'; Delete all logs from 7 days ago to now, with caution
Method 2:
Under UNIX/LINUX, you can also find archived data 7 days ago by FIND and delete it by using EXEC sub-operation.
find /oracle/oraarchive -xdev -mtime +7 -name "*.dbf" -exec rm -f {} ;
or
find /mnt/oradb/archivelog -type f -mtime +1 -exec rm {} \;
Doing so will still leave unmanaged archives in RMAN
You still need to execute the following two commands in RMAN
crosscheck archivelog all;
delete expired archivelog all;
Finally, type crosscheck archivelog all again; that's all[Reference text] http://blog.itpub.net/26508908/viewspace-1663566/
[Reference text] http://www.linuxidc.com/Linux/2014-02/96188.htm
[Reference text] http://blog.itpub.net/14293828/viewspace-1425794/