Oracle archiving logs

Oracle archiving logs

Archive Log is a non-active backup of redo logs. archive logs can be used to keep all redo history records. When the database is in ARCHIVELOG mode and the log is switched, the background process ARCH will save the redo log content to the archive log. when a media failure occurs in the database, you can use data file backup, archive logs and redo logs to completely restore the database.

1. Check whether the current database is in archive mode:
Select name, log_mode from v $ database;
Archive log list;

2. Change non-archive mode to archive mode:

Conn/as sysdba -- (connect to the database as a DBA)

Shutdown immediate; -- (close the database immediately)

Startup mount; -- (starts the instance and loads the database, but does not open it) starts the database to the mount status

Alter database archivelog; -- (change the database to archive Mode)

Alter database open; -- (open the database)

Alter system archive log start; -- (enable automatic archiving)
Exit -- (exit)

3. Change the archive mode to non-archive mode:
1) SQL> SHUTDOWN NORMAL/IMMEDIATE;
2) SQL> STARTUP MOUNT;
3) SQL> ALTER DATABASE NOARCHIVELOG;
4) SQL> ALTER DATABASE OPEN;

4. Enable automatic archiving: LOG_ARCHIVE_START = TRUE
In archive mode, the log file group cannot be overwritten. When the log file is full, if no manual archiving is performed, the system suspends until the archiving is complete.
At this time, you can only read but not write.
Close and restart the archiving log process during running
SQL> ARCHIVE LOG STOP
SQL> ARCHIVE LOG START

5. manual archiving: LOG_ARCHIVE_START = FALSE
Archive Current Log File
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Archive log files numbered 052
SQL> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;
Archive all log files
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
Change the log archiving target
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT TO '& path ';

6. Log Archiving

Select group #, sequence #, bytes/1024/1024 byte_mb, members, status from v $ log; -- view the group number, number of files in each group (number of members), file size and status
SELECT group #, member FROM v $ logfile; -- view the group number and file location
Alter database add logfile group 2 ('e: \ APP \ DELL \ ORADATA \ SRITSUB \ REDO02.LOG ') SIZE 100 M; -- ADD a log GROUP and specify the file location and SIZE
Alter database add logfile member 'd: \ Oracle \ PRODUCT \ 10.2.0 \ ORADATA \ TEST \ redo0401.log 'to group 4; -- add log members
Alter system switch logfile; -- SWITCH the log Group
Alter system checkpoint; -- change the CHECKPOINT
Alter database drop logfile group 2; -- delete a log GROUP
Alter database drop logfile member 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ TEST \ redo0401.log '; -- delete a log member

7. Three statuses of oracle redo log groups: current, active, and inactive

Current: indicates that this log group is the current log group, and oracle is using this log group;

Active: when the current redo group switches logs, the status changes to active.
The archive process archives active log groups;
In case of database crash, this log group is also necessary for instance recovery;

Inactive: When the active log group is archived and oracle determines that no instance is required to be restored, it will change its status to inactive, waiting for the next round of use;

Therefore, when the log group is inactive, if the database is in the archive mode, logs must have been archived.