:

Archive Log Mode – The Details

In this article I would like to discuss the archiving of REDO logs within the Oracle database. REDO generation is one of the fundamental ways in which your Oracle database is able to recover from failures, use Data Guard, Change Data Capture and many other functions which rely on tracking and using all changes made within the database.

So, before we look at how to change the logging mode of your database let’s first take a look at what archive log mode is, when it’s useful and why you would use it.

What is Archive Log Mode?

Archive log mode enables your database to write out a record of every transaction to a file in order to keep track of those changes so they can be used in the future. In short, it’s a massive transaction log. There are lots of details about how it works but the basics are that every time you make a change in your database it is recorded in a small area of memory. When you commit those changes the record of them happening is written to the online REDO logs. As these logs are filled, if your database is in archive log mode, it will copy the full online REDO logs to another location on disk into ARCHIVED REDO logs. There are a finite number of REDO logs which are written to in a cyclical fashion, but the ARCHIVED REDO logs are never overwritten (unless there is a user error!).

Why use Archive Log Mode?

To put it simply, for safety, ease of backing up and point in time recovery. If you put your database in archivelog mode you can take hot backups using Recovery Manager (RMAN) which means no downtime and users can continue using the database while the backup is being taken. In addition, you can rest assured that you can restore to any point in time providing you have a valid backup and all the archived redo logs since that backup. It gives you a lot of confidence that should a disaster happen you can easily restore your database to the point in time before the disaster.

Is my Database in archivelog Mode?

To find out if your database is running in archive log mode you can use the following query:

SELECT log_mode
FROM v$database;

It will either state ARCHIVELOG or NOARCHIVELOG and there is your answer!

Enabling Archive Log Mode

If you want to enable archive log mode in your database it’s very simple, but you will need to restart the database to make this change. Here is a brief summary of how to enable it, but if you want more details please check out my other article on how to enable archivelog mode in your database

SHUTDOWN IMMEDIATE;
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Check that your database is archiving logs by switching log files and checking the physical location specified by the log_archive_dest_n parameter.

ALTER SYSTEM SWITCH LOGFILE;

After running this command you should see a new file created on the file system. If you don’t and your database is in archive log mode there is a risk that the database will stop processing if it cannot write out the changes from the REDO logs to the archived REDO logs. The database will stop working in order to preserve the transactions. The REDO logs are limited in number, you see, so they are written to in a cyclical fashion. For example, there could be 5 REDO logs which are 1GB each in size. The database will write to log 1 until full, then 2, then 3 and so on. Once it has filled log 5 it will start from log 1 again but if log 1 has not been archived, meaning that the contents of it has not been copied from the REDO log to the ARCHIVED REDO log, the database will stop all transactions.

So what about when you want to disable archive log mode on your database?

How to Disable Archive Log

Again, it’s very simple. It’s just the reverse of the above commands which were used to enable archivelog mode:

SHUTDOWN IMMEDIATE
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;

Now a log switch should not produce any physical logfiles on the file system.

As we mentioned earlier on you can use the ARCHIVE REDO logs to perform database recovery to any point in time. Therefore, you want to make sure that you back up your archive logs just in case there is a media failure on the disk you are storing them on.

Archive Log Backup

I use RMAN as my preferred method for backing up my database. Oracle also recommend this way to their customers. It’s very easy to use, you can have scripts and deals with a lot of the difficulties for you. This post isn’t going to go into a full RMAN backup strategy, I will leave that for a future one, but my basic backup strategy is:

  • Level 0 once a week
  • Level 1 every night apart from the night of the level 0
  • Archive log backups every 12 hours

These RMAN backups are then written to tape and stored off-site for added protection. What’s the RMAN syntax for backing up the archive REDO logs?

run
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT 'D:\oradata\rman_archlogs\%d_%T_%s_%t';
ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT 'D:\oradata\rman_archlogs\%d_%T_%s_%t';
ALLOCATE CHANNEL c3 DEVICE TYPE DISK FORMAT 'D:\oradata\rman_archlogs\%d_%T_%s_%t';
ALLOCATE CHANNEL c4 DEVICE TYPE DISK FORMAT 'D:\oradata\rman_archlogs\%d_%T_%s_%t';
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
}

Here I am allocating 4 channels (just another way to say a parallelism of 4) which will backup any archive logs which have not been backup up once before and will compress them. Compressing in RMAN uses more CPU power and the files will be around 20% of their original size.

OK, so what else might you want to do with archive logs? Well, if you have a Data Guard database configured you will want to ship archive REDO logs to your physical or logical standby database. There are many ways to configure your standby database, some of which actually ship the REDO directly and don’t use the archive redo logs unless there is a delay, but I’m not going to go into all the different permutations here. You should just be aware that the shipping of the logs is possible and some of the parameters which you need to configure:

  • log_archive_config – list the db_unique_names for the DBs which are in the allowable data guard configuration
  • LOG_ARCHIVE_DEST_1 – should be set to the local location
  • LOG_ARCHIVE_DEST_2 – should be set to the service of where you are shipping to, here is an example of what you might have set
  • LOG_ARCHIVE_DEST_2=’SERVICE=TESTDG ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDG’ – this ships to a service called TESTDG asynchronously, meaning using archive logs and not directly from the log writer.
  • STANDBY_FILE_MANAGEMENT=AUTO¬† – set so that file adding and deletion is managed automatically
  • DB_FILE_NAME_CONVERT=’TESTDG’,’TEST’ – converts TESTDG to TEST for the files names

Anyway, I digress a little. Above are just some of the parameters that you would configure but there are others and you should read through the Oracle documentation thoroughly first. The above was just to show you a way to ship your archive logs to another database using a Data Guard configuration. But what about if you want to stop shipping logs to your physical standby database? Let me explain…

Defer Archive Log Shipping

It’s very simple to do this. All you have to do is set the associated log_archive_dest_state_n initialisation parameter for the log_archive_dest that you want to stop shipping to so say DEFER instead of ENABLE. For example, imagine that we have the following set in our database:

log_archive_dest_2=’SERVICE=DW3QA ARCH OPTIONAL NOREGISTER TEMPLATE=O:\Oradata\DW3QA\Archlogs\%t_%s_%r.arc’ log_archive_dest_state_2=’ENABLE’

We can easily run in one command to no longer ship archive logs to this destination:
ALTER SYSTEM SET log_archive_dest_state_2=’DEFER’ SCOPE=both;

If you want to start shipping logs again you can just change the ‘DEFER’ part of the command to ‘ENABLE’ and it will start shipping again from where it left off. Providing that your Data Guard DB has been configured properly it should automatically resolve any archive log gaps.

So, there you have it. Hopefully this has been helpful to you and there is some information in this article which will help you to deal with the most common situations you come across when dealing with archive logs on your databases.

Like it, share it...

Category: Articles


Related Posts

Comments (0)

Trackback URL | Comments RSS Feed

  1. sujit says:

    good one…sir
    it’s really beneficial for new one.

  2. sujit says:

    and some part helped me as well…

Leave a Reply

Your email address will not be published. Required fields are marked *