OracleToSQL: Database Logging


Description
Redo logs are transaction journals. Each transaction is recorded in the redo logs. Though redo generation is expensive operation, Oracle uses online redo logs as hot backups in case of instance crashes to ensure recoverability to a consistent state. The online redo log files contain the information necessary to replay a transaction, committed or not. Even uncommitted transactions can be written to the online redo log files. Before a commit is complete, the transaction information is written to the online redo log files.

And changes to your rollback or undo segments are also written to the online redo log files. In that sense, they also contain the information to undo a transaction.

How to find feature enablement?
Oracle gave users the ability to limit redo generation on tables and indexes for better performance by setting them in NOLOGGING mode. Be careful never to use NOLOGGING option under Data guard setup. DB replication relies on redo logs.

On the other hand, FORCE LOGGING can be used on tablespace or database level to force logging of changes to the redo. This may be required for sites that are mining log data, using Oracle Streams or using Data Guard (standby databases).

SELECT force_logging FROM v$database;

SELECT tablespace_name, force_logging FROM dba_tablespaces;

SELECT * FROM v$logfile;

To create a table in NOLOGGING mode:
CREATE TABLE t1 (c1 NUMBER) NOLOGGING;

To enable NOLOGGING for a table/database:
ALTER TABLE t1 NOLOGGING;
ALTER DATABASE force logging;

Recommendation
Feature Description:
SQL Server requires a transaction log in order to function. That said there are two modes of operation for the transaction log: Simple and Full. In Full mode, the transaction log keeps growing until you back up the database. In Simple mode: space in the transaction log is ‘recycled’ every Checkpoint.

SQL Server ensures data durability and recovery capabilities using Write-Ahead Logging, hardening a log record before a transaction begins. SQL Server can write log records describing a DB modification before it writes the actual change to the data or object. If SQL Server can’t write log records, it won’t commit. For this reason, its recommended leaving log auto-growth enabled.

Log file: C:\Program Files\Microsoft SQL Server\MSSWL\Data\MyDB.Idf

Feature Comparison:
Like Oracle redo logging, SQL Server records database transactions in transaction logs. Each transaction record contains the undo and redo image of the transaction. Database logging in SQL Server is typically sent through a single log .ldf file. On the surface, this appears to be much different from oracle where logs are broken up into groups of logs called Redo Log Groups, but both architectures are very similar when look at the structure of the .LDF. Each physical .LDF file is a group of Virtual Log Files, (VLFs), that behave much like a Redo Log Group does in Oracle.

VLFs can be viewed by running DBCC LOGINFO;

Archiving is controlled via a periodic BACKUP LOG job in SQL Server. VLFs are compressed and set to .TRN files.
After backup, VLF is cleared and can be reused.

This differs from Oracle where they ARC internal process automatically moves full log files to an archive directory as they fill up, not on a reoccurring schedule. These files typically have a .ARC extension in Oracle are just copied/renamed right from the Redo Log Group.

Migration Approach
Migrating Transaction Logs

In Oracle, information on transactions and the changes they make is recorded in REDO logs. The redo logs are common to the entire instance.

In SQL Server, transactional changes are logged in the transaction log for the database whose objects are involved in the transaction. A database is created with a single default transaction log. The default transaction log has to be sized or new ones added based on the update activity against the database.

To add a transaction log to a database using T-SQL, use the following syntax:
ALTER DATABASE database
{ ADD LOG FILE < filespec > [ ,…n ]
where ::=
( NAME = logical_file_name
[ , FILENAME = ‘os_file_name’ ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )

Database Logging is enabled by default in SQL Server.

Logging is based on three recovery models: simple, full, and bulk-logged. The recovery model for new databases is taken from the Model database. After the creation of the new database, you can change the recovery model using SSMS or following T-SQL:
To set the Recovery Model:
USE master ;
ALTER DATABASE model SET RECOVERY FULL ;

References

  • http://searchoracle.techtarget.com/answer/What-information-do-redo-log-files-contain
  • http://www.databases-la.com/?q=node/33
  • http://www.dba-oracle.com/concepts/archivelog_archived_redo_logs.htm
  • http://users.wfu.edu/rollins/oracle/archive.html
  • https://msdn.microsoft.com/en-us/library/ms190925.aspx
  • http://www.sqlshack.com/beginners-guide-sql-server-transaction-logs/

Comments (0)

Skip to main content