Using a transaction log mirror

User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 20. Backup and Data Recovery

Using a transaction log mirror

A transaction log mirror is an identical copy of the transaction log, maintained at the same time as the transaction log. Transaction log mirrors are used to allow complete recovery in the case of media failure on the log device.

Every time a database change is written to the transaction log, it is also written to the transaction log mirror file. By default, a mirrored transaction log is not used, but you can choose to use one when creating a database or you can make an existing database use a mirrored transaction log.

Why use a transaction log mirror?

A mirrored transaction log provides extra protection of critical data. For example, at a consolidated database in a SQL Remote setup, replication relies on the transaction log, and if the transaction log is damaged or becomes corrupt, data replication can fail.

A mirrored transaction log carries out automatic validation of the transaction log on database startup.

There is a performance penalty for using a mirrored log, as each database log write operation must be carried out twice. The performance penalty depends on the nature and volume of database traffic and on the physical configuration of the database and logs.

If you are using a mirrored transaction log, and get an error while trying to write to one of them (for example, if the disk is full), the server or server stops. The purpose of a transaction log mirror is to ensure complete recoverability in the case of media failure on either log device; this purpose would be lost if the server continued with a single log.

Where to store the transaction log mirror

A transaction log mirror should be kept on a separate device from the transaction log, so that if either device fails, the other copy of the log keeps the data safe for recovery.

Creating and dropping a transaction log mirror

Transaction log mirrors can be created at the following times:

When you create a database, using the Initialization utility.
At any other time that the database is not running, using the Transaction Log utility (dblog).
A mirror for a write file transaction log can be created along with the write file using the write file utility (mirroring can be added later, using the transaction log utility).

Notes

You cannot choose to use a transaction log mirror without using a transaction log.
The default file extension for transaction log mirrors is .MLG.

Creating a database with a transaction log mirror

You can choose to maintain a transaction log mirror when you create a database. This option is available either from the CREATE DATABASE statement, from Sybase Central or from the dbinit command-line utility.

From Sybase Central, the transaction log mirror option is part of the Create Database utility.

For more information, see the Sybase Central online Help.
The following command line (which should be entered on one line) initializes a database named company.db, with a transaction log kept on a different device and a mirror on a third device.
```
dbinit -t d:\log_dir\company.log -m 
e:\mirr_dir\company.mlg c:\db_dir\company.db
```
By default, a transaction log is used but no transaction log mirror is created.

For Info For a full description of initialization options, see Initialization utility options.

Starting a transaction log mirror for an existing database

You can choose to maintain a transaction log mirror for an existing database any time the database is not running, by using the transaction log utility. This option is available from either Sybase Central or the dblog command-line utility.

From Sybase Central, the transaction log mirror option is part of the Change Log File utility.

For more information, see the Sybase Central online Help.
The following command line starts a transaction log mirror for a database named company.db, which is already using one transaction log.
```
dblog -m e:\mirr_dir\company.mlg c:\db_dir\company.db
```
The following command line stops the company.db database from using a transaction log mirror, but continues maintaining a transaction log:
```
dblog -r c:\db_dir\company.db
```
The following command line stops the company.db database from using a transaction log mirror or a transaction log:
```
dblog -n c:\db_dir\company.db
```

With the transaction log utility you can also alter the name or directory of the transaction log and mirror.

For Info For a full description of dblog command-line options, see Transaction log utility options.

Starting a transaction log mirror for a write file

You can choose to maintain a transaction log mirror for a write file when you create the write file using the Write File utility, or at a later time using the Transaction Log utility.

The option to create a transaction log mirror when creating a write file is available from the CREATE WRITEFILE statement, from Sybase Central, or from the dbwrite command-line utility.

From Sybase Central, the transaction log mirror option is part of the Create Write File utility.

For more information, see the Sybase Central online Help.
The following command line (which should be entered on one line) creates a write file for a database named company.db, which is already using a transaction log. The write file has default extension .WRT, the write file transaction log has the default extension .WLG, and the write file transaction log mirror has the default extension .WML.
```
dbwrite -c -t d:\log_dir\company.wlg -m 
c:\mirr_dir\company.wml c:\db_dir\company.db
c:\db_dir\company.wrt
```

For Info For a full description of dbwrite command-line options, see Write file utility options.

You can change the transaction log and log mirror settings of a write file using the transaction log utility, in exactly the same way as described above for a standard database file.

Erasing transaction log mirrors

You can erase transaction log mirrors using the Erase utility in Sybase Central or the dberase command-line utility.

The Erase utility is available in Sybase Central as the Erase Database utility or from the command line as the dberase utility.

To delete a mirror log file only:

Enter the following command:
```
dberase e:\mirr_dir\company.wml
```

To delete a transaction log file but not its mirror:

Enter the following command:
```
dberase e:\log_dir\company.log
```

Validating the transaction log on database startup

When a database that is using a mirror starts up, the server carries out a series of checks and automatic recovery operations to confirm that the transaction log and its mirror are not corrupted, and to correct some problems if corruption is detected.

On startup, the server checks that the transaction log and its mirror are identical by carrying out a full comparison of the two files; if they are identical, the database starts as usual. The comparison of log and mirror adds to database startup time.

If the database stopped because of a system failure, it is possible that some operations were written into the transaction log but not into the mirror. If the server finds that the transaction log and the mirror are identical up to the end of the shorter of the two files, the remainder of the longer file is copied into the shorter file. This produces an identical log and mirror. After this automatic recovery step, the server starts as usual.

If the check finds that the log and the mirror are different in the body of the shorter of the two, one of the two files is corrupt. In this case, the database does not start, and an error message is generated saying that the transaction log or its mirror is invalid.

Recovering from a corrupt transaction log or mirror

When a server detects a difference between the transaction log and its mirror in the body of the file, the server does not start. In this case, you must take the following steps before starting the server:

Identify which of the two files is corrupt.
Copy the correct file over the corrupt file so that you have two identical files again.
Restart the server.

When a server detects a difference between the transaction log and its mirror, it has no means of knowing which is intact and which is corrupt.

To identify which file is corrupt using the database utilities:

Make a copy of the backup of your database file taken at the time the transaction log was started.
Run the log translation utility on the transaction log and on its mirror, to see which one generates an error message. (The log translation utility is accessible from Sybase Central or as the dbtran command-line utility.)
The following command-line translates a transaction log named asademo.log, placing the translated output into asademo.sql:
```
dbtran asademo.log
```
The translation utility properly translates the intact file, and will report an error while translating the corrupt file.
If the dbtran test does not identify the incorrect log, you may want to compare the two translated logs (SQL files) to see which one contains an error, or use a disk utility to inspect the two files and detect which is corrupt.
Once you have identified the corrupt file, you can copy the intact log file over the corrupt file, and restart the production server.