If you are a DBA or an Aspiring DBA then setting up Archive log mode must be on the TOP of your list. But there are so many things that you need to do beforehand. Setting up Archive log Destination Directory is one of them.
You need to configure a location where your Oracle Server can archive your redo logs. There are two ways of doing that. These are –
- Configure the Fast Recovery Area, and
- Configure the Archive log Destination Directory.
I have already done a tutorial on Fast Recovery Area. There I’ve discussed how to configure it. What are its advantages and why you should setup a FRA. You can click here to check out that tutorial.
Now comes the second option which is, setting up Archive log destination Directory. That’s what I am going to cover in today’s Oracle Backup and Recovery Tutorial.
Archive Log Destination Directory
Archive log destination directory, As the name suggests is a location which Oracle Uses to save the Archived copies of the Online Redo log files during backup operations.
How To Setup Archive Log Destination Directory.
There are two main parameters that we need to configure to setup Archive log destination directory. These are –
- LOG_ARCHIVE_DEST_n, and
Log_Archive_Dest_n parameter is used to set a location for Archive Log Destination Directory. Here are a few things about this parameter –
- The prefix n is a number in range of 1 to 10.
- Using LOG_ARCHIVE_DEST_n we can define up to 10 destinations for saving the archived logs. if you have more that one location setup then you will have redundant copies of archive logs created by the Oracle Database server. It will occupy some extra storage space but from backup perspective it’s always better to have multiple backups.
- By default there is no Archive log destination directory setup in Oracle Database. Which means if you want to use one, then you have to set it up first.
- Using LOG_ARCHIVE_DEST_n you can setup a location which could either be a
- Local folder created on your hard drive somewhere on your system.
- A network directory
- A NAS location
- Service Name, if you have a data guard setup.
How To Use Log_Archive_Dest_n Parameter
Step 1: Create a Directory at your prefer location.
C:/> mkdir D:\ArchLog
For the demonstration I will be using a directory named ‘ArchLog’ which I have created in my D:/ drive.
Step 2: Issue an ALTER SYSTEM Command
Once you have decided a location that you want to use as your Archive log destination then issue an ALTER SYSTEM command to register that directory in Oracle Server.
SQL> ALTER SYSTEM SET log_archive_dest_1 = 'location = D:\ArchLog';
On execution of this statement – We configure our 1st directory. This directory is the OPTIONAL Archive log directory.
Mind here I emphasize on the keyword OPTIONAL . Let’s talk more about it.
Optional & Mandatory Archive log directory.
Any location set as Archive log directory using LOG_ARCHIVE_DEST_n parameter can either be an Optional or Mandatory location.
By default any location set using LOG_ARCHIVE_DEST_n parameter is always optional.
What does that mean –
Let’s say you have two archive log directories created and both these directories are optional in Nature. Then the Archiver Process (The background process that is responsible for archiving your redo logs) does not guarantee that in which directory your redo logs will get written or even get written all together.
How To Create Mandatory Archive Log Directory
Creating a Mandatory Archive log directory in Oracle Database is actually very simple. We just have to add the Keyword
Mandatory to the Alter system statement that we wrote previously. Like this –
SQL> ALTER SYSTEM SET log_archive_dest_1 = 'location=c:\oracle\app MANDATORY'
That’s all you have to do.
Now the question is – if by default archive log directories are optional then How and When can we consider Online Redo logs archived successfully?
The answer to this question is – our second parameter LOG_ARCHIVE_MIN_SUCCEED_DEST. Let’s find out.
LOG_ARCHIVE_MIN_SUCCEED_DEST parameter sets the total number of Archive Log Destination Directories that need to have successful copies of the online redo logs in order to be considered as successfully archived.
By default the value of the LOG_ARCHIVE_MIN_SUCCEED_DEST is set to 1. That means – Online Redo Logs are considered successfully archived, only and only when, the archiver process (ARCH) creates a valid copy of Online Redo logs, into at least one Archive log destination directory.
How To Alter LOG_ARCHIVE_MIN_SUCCEED_DEST.
You can change the value of LOG_ARCHIVE_MIN_SUCCEED_DEST using ALTER SYSTEM command. You can update its value to any number between 1 to 10. like this –
SQL> ALTER SYSTEM SET log_archive_min_succeed_dest = 2;
On successful execution – the number of directories that needs to have a valid copy of online redo logs to be called successfully archived will be 2.
That’s it for this tutorial. Hope you find it helpful. For more details watch the video.
Thanks and have a great day.