Oracle's understanding of archiving log files

Today the Data group someone response website can not open normally, after checking Oracle database remote connection is not on, message: Ora-00257:archiver error. Connect internal only, until freed. Maybe the archivelog is full . Before learning SQL only to focus on crud, the log understand very little, although the outage on the generation did not have a bad impact, but also because the business is not familiar with the result, spend a day to learn and record Oracle log archiving function.

• There are two modes of Oracle log operation : ARCHIVELOG,noarchivelog

Connect OracleTerminal

• Windows system: Sqlplus

• Linux system: first login to SSH, switch to Oracle user, and then start sqlplus login Oracle

View current log operation mode

• General Method: SELECT Log_mode from V$database;

• SYS User:

Open Log Archive

• Stop the database before you enable archive logging

shutdown Immediate;

• database starts in mount mode

startup Mount;

• Changing the log mode

• • Enable database archiving

Alter database Archivelog;

• • Close Archive

Alter database Noarchivelog;

• Open Database

Alter Database Open;

• View Archive Log Information

log list;

• To view the default Flash return file storage path

Show parameter db_recovery_file_dest;

oracle11g version, the ORACLE default log archive path is the Flashback recovery area ($ORACLE _base/fast_recovery_area). For this path, Oracle has a limit of only 4 g of space, and not just the default path of the archive log, but also the default address of the backup file and flashback log, so that the archive log lock uses less than 4G of space.

Test Inbound 100wdata, generating archive files

• To see if the flashback space is occupied

Select *  from V$flash_recovery_area_usage;

• To view the number of archived log files

Select  *  from V$recovery_file_dest;

When Ora-00257:archiver error occurs. Connect internal only, until freed error when data is not connected, if the database archive target is use_db_recovery_file_dest and the Db_recovery_file_dest directory is the default flash back space directory

• Method one can be taken to increase the amount of flash back space:

Alter set db_recovery_file_dest_size=8G Scope=both;

• Method Two modify the path of the archive log

Alter set db_recovery_file_dest='location=c:\app\administrator\oracle_log ' scope=both;

• Method three Modify the path of the archive log and place the archive log on an unrestricted path to resolve the problem by following the SQL to modify the archive log storage path

Alter set log_archive_dest_1=' location=C:\app\Administrator\oracle_log ';

See where Archiv log is located

Show parameter log_archive_dest;

log list;

To verify that the archive path is set correctly by toggling logs to see if an archive log is generated under the archive path, the Switch log command is as follows

alter system switch logfile;

Method Two and method San Du is to modify the archive file storage path, the specific difference is what, to understand the specific differences must first understand the archive log path three parameters

• 1, db_recovery_file_dest: Flashback Recovery Zone Path

Show parameter db_recovery_file_dest;

• 2, Log_archive_dest:log_archive_dest: Specify the path of the archive file, the path can only be a local disk, the default is '.

Show parameter log_archive_dest;

• 3, Log_archive_dest_n: Specify the path of archive file storage, Oracle support to archive log files to a place,n from 1 to 31 . The archive address can be a local disk, or a network device.

The three parameters differ as follows

• • 1   If set db_ Recovery_file_dest log_archive_dest, The default archive log is stored in the Flash recovery area specified by db_recovery_file_dest log_archive_dest_n Db_recovery_file_dest log_archive_dest_n settings directory. If you want the archive log to continue to reside in db_recovery_file_dest alter system set LOG_ archive_dest_1= ' location=use_db_recovery_file_dest ';
  • 2   If set Log_ Archive_dest log_archive_dest_n db_recovery_file_dest log_archive_dest_n log_archive_dest log_archive_dest parameters and Db_recovery_file_dest , Log_archive_dest_n db_recovery_file_dest and Log_archive_dest_n
  • 3, log_archive_dest can only coexist with log_archive_duplex_dest . This allows you to set up two archive paths. Log_archive_dest set a primary archive path,log_archive_duplex_dest sets a path from the archive. All archive paths must be local.
  • 4 . if the path set by the log_archive_dest_n is incorrect, Oracle will archive the previous level of the set up directory. such as setting log_archive_dest_1= ' location=c:\archive1 ', and the OS does not archive1 This directory, then Oracle will be in C Disk Archive.

Delete an archive file

• First delete the files on the physical host disk of the archive file
• These archivelog are still recorded in Oracle's controlfile after physical file deletion, and a visualization of the logs in Oracle's OEM manager shows that the next thing to do is to remove the extra archived log file records from the Controlfile

To delete using Rman, the following steps are performed:

Window Client System For example

• Open Rman Connection Database

• View the status of archived logs

All;

• Execute archivelog check command

All;

• Delete a record for a checksum failure

Delete  All

• Select Yes

• View Archive logs

All;

• View archived log files

Select substr (t.name,1) NAME,ROUND(sum(t.blocks*t.block_size)/  1024x768/1024x768fromwhere t.deleted='NO  'group by substr (T.name,1);

Completely correct, the archive file is completely deleted!

Oracle's understanding of archiving log files