Summary of Oracle archive logs

Summary of Oracle archive logs

1. Open archive logs

Sqlplus/as sysdba
SQL> shutdown immediate;
SQL> startup mount; # Open the control file instead of the data file
SQL> alter database archivelog; # Switch the database to archive Mode
SQL> alter database open; # open the data file
SQL> archive log list; # Check whether the file is in archive mode.

View log Mode

SQL> select log_mode from v $ database;

Check whether the archive is started

SQL> select archiver from v $ instance;

2. Disable archiving logs

SQL> archive log list; # Check whether the archive mode is used
SQL> alter system set log_archive_start = false scope = spfile;
SQL> shutdown immediate;
SQL> startup mount; # Open the control file instead of the data file
SQL> alter database noarchivelog; # Switch the database to non-archive Mode
SQL> alter database open; # open the data file
SQL> archive log list; # It is in non-archive mode.

3. Check whether the archiving method and path are correct.

SQL> archive log list; # Check whether the archive mode is used

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 98

Next log sequence to archive 100

Current log sequence 100

Search for DB_RECOVERY_FILE_DEST Based on USE_DB_RECOVERY_FILE_DEST.

Sys @ ora10g> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE

----------------------------------------------------------------------

Db_recovery_file_dest string/Oracle/app/oracle/flash_recovery_area

 

4. Modify the path of the archived log

SQL> archive log list; # Check whether the archive mode is used
SQL> alter system set log_archive_start = true scope = spfile; # enable active Archiving
SQL> alter system set log_archive_dest = ''location =/oracle/ora9/oradata/arch'' scope = spfile; # set the archive path
SQL> alter system set log_archive_dest_1 = ''location =/oracle/ora9/oradata/arch1' scope = spfile;
SQL> alter system set log_archive_dest_2 = ''location =/oracle/ora9/oradata/arch2' scope = spfile;
# If the file is archived to two locations, you can use the above method to implement
SQL> alter system set log_archive_format = ''arch _ % d _ % t _ % r _ % s. log'' # set the archive diary style
 
Log Switching
SQL> alter system switch logfile;
This log switch writes the archive to two destination locations,
That is, the above/oracle/ora9/oradata/arch1 and/oracle/ora9/oradata/arch1,

 

5. view the size of the archived logs

Select sum (a. BLOCK_SIZE X a. BLOCKS)/1024/1024 from v $ archived_log a where a. DELETED = 'no ';

6. view the maximum size of archived logs

Show parameter db_recovery_file_dest_size;

7. Set the maximum value of archive logs. The archived logs are full and cannot be started.

Startup nomount;
Alter system set db_recovery_file_dest_size = 4G;
Alter database mount;
Alter database on;

8. view the usage of archived logs

Select PERCENT_SPACE_USED from V $ FLASH_RECOVERY_AREA_USAGE where file_type LIKE 'archived ';

9. Get the usage of the flash back area

Select sum (PERCENT_SPACE_USED) from V $ RECOVERY_AREA_USAGE;