Today we are going to learn about the most important element of Oracle Database which come’s much important after it’s backbone.Being Oracle DBA we must aware of Archive logs.Now we are going to have look at the technical definition of archive log.
An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group.
As we work as DBA professionals we must know it that our DB is in archivelog or not.Let’s have look at it. The given command helps us to know about the mode of DB.
Check the Database in an Archive Log Mode or Not
- ARCHIVE LOG LIST
We have ways to change the database into archivelog mode. We have mentioned the steps which we use to change the mode of DB.
Changing the Archiving Mode
- SHUTDOWN IMMEDIATE
- STARTUP MOUNT
- ALTER DATABASE ARCHIVELOG
- ALTER DATABASE OPEN
If you want to know how to Oracle RAC database in Archive log mode : Click Here
The given command helps us to know about the archive destination.
Automatic and Manual Archiving
- SHOW PARAMETER DB_RECOVERY_FILE_DEST
Automatic Archiving:
- ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;
- STARTUP FORCE;
Manual Archiving:
- ALTER SYSTEM SET LOG_ARCHIVE_START=FALSE SCOPE=SPFILE;
- STARTUP FORCE;
Here we can see that we can start and stop the process.
Stop or Start Additional Archive Processes
- ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 SCOPE=SPFILE;
- STARTUP FORCE;
Manually Archiving Online Redo Log Files
- ALTER SYSTEM ARCHIVE LOG CURRENT;
Specify Multiple Archive Log Destinations
Use LOG_ARCHIVE_DEST_ n to specify up to ten archival destinations, which can be on either Local Disk or Remote Location
- ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=”LOCATION=C:\oracle\product\11.2.0\orcl\Location_1″ SCOPE=SPFILE;
- STARTUP FORCE;
LOG_ARCHIVE_DEST_n Options
- ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=”LOCATION=C:\oracle\product\11.2.0\orcl\Location_2 MANDATORY REOPEN” SCOPE=SPFILE;
- ALTER SYSTEM SET LOG_ARCHIVE_DEST_3=”LOCATION=C:\oracle\product\11.2.0\orcl\Location_2 OPTIONAL REOPEN=200″ SCOPE=SPFILE;
- STARTUP FORCE;
Note: Default REOPEN is 300 seconds.
Specifying a Minimum Number of Local Destinations
- ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST=2 SCOPE=SPFILE;
Controlling Archiving to a Destination
Archiving to a destination can be disabling
- ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
Archiving to a destination can be enabling
- ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;
Dynamic Views
- V$ARCHIVED_LOG: Displays archived log information from the control file;
- V$ARCHIVE_DEST: For the Current instance, describes all archive log destinations, the current value and status
- V$LOG_HISTORY: Contains log file information from the control file.
- V$ARCHIVE_PROCESSES: Provides information about the state of the various ARCH processes for the instance.
Stay tuned for More articles on Oracle
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel : https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp