SCN Explained

How Oracle transaction data change is written to a datafile

1. Transaction starts
2. Required data blocks are found in the buffer cache. If not found in the buffer cache, then found in the datafile
3. Transaction modifies the data blocks in buffer cache, identified as "dirty data" and is written to the log buffer
4. Transaction is committed and LGWR process writes the dirty data in the log buffer to the redo log file
5. CKPT process updates the headers of all the datafiles and DBWn process writes the dirty data from Buffer Cache to the datafile

SCN - system change number. the SCN is an internal number maintained by the database to log the changes made. it is sort of an Oracle clock - every time transaction commits, it increments. There are a total of 4 SCN : System checkpoint SCN, the datafile checkpoint SCN, Start SCN and Stop SCN.

System checkpoint SCN - when a checkpoint operation is cimpleted, the Oracle stores the system checkpoint SCN in the control file

SELECT checkpoint_change# FROM V$DATABASE

It is for the entire database.

Datafile checkpoint SCN - when checkpoint operation is completed, Oracle stores it separately in the control file

SELECT name, checkpoint_change# FROM V$DATAFILE

Start SCN - checkpoint SCN stored in the file header of each datfile. it is called start SCN because it is used to start the database instance

SELECT name, checkpoint_change# FROM V$DATAFILE_HEADER

Stop SCN - SCN termination of each datafile header are stored in the control file

SELECT name, last_change# FROM V$DATAFILE

During the normal operation, stop SCN (last_change# field) is an infinite number or is NULL.

Current SCN is retrieved using:

select dbms_flashback.get_system_change_number from dual;

Whenever a transaction is comitted, the log writer process (LGWR) writes the records from the redo log buffers in SGA to the online redo logs on disk. LGWR also writes the SCN for the transaction to that online redo log file.

When all modified database buffers are written out to datafiles byt the database writer (DBWn) process, a checkpoint occurs. Checkpoint (CKPT) updates all database files and control files with the SCN at the time of the checkpoint. A successful checkpoint guarantees that all database changes up to that SCN have been recorded in the datafiles. As a result, only those changes that occurred afetr the checkpoint, need to be applied during recovery.

Checkpoint typically occurs at intervals specified in LOG_CHECKPOINT_INTERVAl parameter or whenever redo log switch takes place. Oracle stores SCN associated with that checkpoint in 4 places: 3 in control files and 1 in the datafile header for each datafile.

How to determine whether clean shutdown has occurred?

Run

select name,checkpoint_change# from v$datafile_header where name like '%datafilenamehere%';

If a clean shutdown has occurred, the CHECKPOINT_CHANGE# and LAST_CHANGE# will match for each datafile. if an instance crashes at shutdown, the numbers will be different and instance crash recovery will be required at the next instance start up.

Upon the next instance startup Oracle will detect that the stop SCN for datafiles was not set in the control file during startup. So during crash recovery oracle will apply all the redo log records from online redo logs in a process of rolling forward - to ensure that all transactions committed before the crash are applied to the datafiles. Also active transactions that did not commit before the crash will be rolled back.

Instance recovery happens automatically at startup without DBA's intervention.

While the database is up and open for use, the system checkpoint in the control file, the datafile checkpoint SCN in the control file, and the start SCN in each datafile header all match.

During a clean shutdown, a checkpoint is performed and the stop SCN for each datafile is set to the start SCN from the datafile header. Upon startup, Oracle checks the start SCN in the file header with the datafile checkpoint SCN. If they match, Oracle checks the start SCN in the datafile header with the datafile stop SCN in the control file. If they match, the database can be opened because all block changes have been applied, no changes were lost on shutdown, and therefore no recovery is required on startup. After the database is opened, the datafile stop SCN in the control file once again changes to NULL to indicate that the datafile is open for normal use.

System crash - For database after a system crash, upon startup, the stop SCN is not set (LAST_CHANGE#) and thus the Oracle will know the instance crashed because the checkpoint on a shutdown was not performed. If it had been performed, the CHECKPOINT_CHANGE# and LAST_CHANGE# values would match for each datafile as they do during clean shutdown.

During crash recovery, Oracle applies redo log records from the online redo logs in a process referred to as roll forward to ensure that all transactions committed before the crash are applied to the datafiles. Following roll forward, active transactions that did not commit are identified from the rollback segments and are undone before the blocks involved in the active transactions can be accessed. This process is referred to as roll back.

Media failure - In case of a media failure, the datafile checkpointSCN and start SCN in the datafile header won't match at instance startup.

SQL Server: Managing transaction logs

Today I learned something new - if truncating and shrinking transaction log does not seem to have any effect on transaction log's size, and the recovery mode is Full, one needs to change the mode to Simple, then shirnk the transaction log and change the mode back to Full.

The thing is that sometimes, when the database in Full recovery mode, the transactional log does not shrink, so this is the workaround.

This can also be done in production when necessary, but mirroring must be removed first and the reconfigured after everything is done.

Control file recovery

Control file needs recovery when

• Any one control file specified in a parameter file is lost due to media failure or corrupted
• New control file is needed when changing the database

When one control file becomes corrupted, oracle database automatically stops running. One can determine which control file is causing a problem by looking into alert log file. Then remove the corrupted control file and copy good control file to the corrupted location

1. rm -f /u10/oradata/controlfile01.ctl
   

2. cp /u20/oradata/controlfile02.ctl /u10/oradata/controlfile01.ctl
   

3. Then mount the database

   STARTUP MOUNT;
   

4. and open it

   ALTER DATBASE OPEN;
   

When a control file is lost, copy a good control file to the new location, modify control_file parameter by removing lost location and add the new location to it:

1. Shutdown the database:

   SHUTDOWN ABORT;
   

2. cp /u20/oradata/controlfile02.ctl /u10/oradata/controlfile04.ctl
   

3. modify PFILE:

   CONTROL_FILE='/u20/oradata/controlfile02.ctl',
   '/u21/oradata/controlfile03.ctl',
   '/u30/oradata/controlfile04.ctl'
   

4. Then mount and open the database:

   STARTUP MOUNT;
   ALTER DATBASE OPEN;
   

ORA-01180: can not create datafile 1

RMAN-03002: failure of restore command at 01/24/2013 16:44:05
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u01/oradata/pubs/system01.dbf'

Occurred when running database restore.

I ran

list incarnation;

the result was

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DBName     707574411        PARENT  1          23-JAN-13
2       2       DBName     707574411        CURRENT 799793     25-JAN-13

Cause: Incarnation error

Solution: Reset incarnation

RESET DATABASE TO INCARNATION 1;

What is an incarnation?

A database incarnation is created whenever you open the database with the RESETLOGS option.

The Current incarnation is the one in which the database is running at present

The incarnation from which the current incarnation branched after a ALTER DATABASE OPEN RESETLOGS was performed is called the Parent incarnation.

If you want to go back to some SCN which is not part of the Current database incarnation, we need to use the RESET DATABASE TO INCARNATION command as shown in the example below

The purpose of incarnations

An incarnation helps to identify redo streams which have the same SCN, but occurred at different points in time. This prevents the possibility of applying the wrong archive log file from a previous incarnation which could corrupt the database.

Suppose we are at incarnation 1 and are at SCN 100 in the database. I do a resetlogs and now the incarnation of the database becomes 2. Suppose we do another resetlogs and it so happens that the SCN at the time we did the resetlogs was also 100. Somewhere down the line later we want to do a point in time recovery and want to recover the database until SCN 100. How does Oracle know which is the correct SCN and how far back in time should the database be rolled back to and which archive logs need to be applied?

This is where incarnations come in and we will see how we can set the database to a particular incarnation to enable RMAN to do a point in time recovery.

How to recover a dropped table in Oracle 11g when flashback mode is off

Recovering dropped table is easy in Oracle, provided that the table was not dropped with PURGE option. In case the table is dropped and space occupied by the table is released and the table does not get moved into the recycle bin. But if table is dropped without PURGE option, Oracle has this very neat feature - Recycle bin, similar to the recycle bin in Windows. There are two recyle bin views in Oracle: USER_RECYCLEBIN and DBA_RECYCLEBIN, Synonym RECYCLEBIN points to your USER_RECYCLEBIN.

The recycle bin can be turned on and off with RECYCLEBIN initialization parameter. When table is dropped, it get rename to system-generated name preceeded with BIN and stored in recycle bin. The important thing to know is that after table has been dropped, it's only been renamed, the table segmants are still in the tablespace, unchanged. the space occupied by the table will not be reclaimed until the table has been purged from the recycle bin.

While in the recycle bin, the table can even be queried using the newly generated name that starts qwith BIN$.

The table can easily be recovered from the recycle bin using flashback drop, which will rename the table to its original name.

You can check flashback mode by running

SELECT FLASHBACK_ON FROM V$DATABASE; 

First check the parameter Recyclebin is set to true. Recycle bin is a data dictionary table that contains information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

SHOW PARAMETER RECYCLEBIN;

if recyclebin is set to off, perform the following steps:

1. run

   ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=SPFILE;
   

2. verify that recyclebin parameter is now set to ON
3. shutdown the database

   SHUTDOWN IMMEDIATE
   

4. Restart the database

   STARTUP
   

then run

SELECT * FROM RECYCLEBIN;

and see if your table is in there. If it is, use the following quesry to restore it:

FLASHBACK TABLE  TO BEFORE DROP;

Then check if the table is back:

SELECT * FROM USER_TABLES WHERE TABLE_NAME=;

How to determine the type of a backup file based on the filename

Let's say you backed up your database and fast_recovery_area contains a number of backup files and you don't know which ones to use for recovery, which ones are full backup, which are incremental, and which are level 0 or level1. First get the backup piece id by running:

list backuppiece '/u01/fast_recovery_area/DBName/backupset/backup_filename.bkp'

The oputput should look something liek this:

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
5       5       1   1   AVAILABLE   DISK        /u01/fast_recovery_area/DBName/backupset/backup_filename.bkp

Then run

list backupset 5;

where 5 is the backup piece id of your backup file The output will look something like this:

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Incr 0  680.12M    DISK        00:00:06     18-JAN-13
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20130118T141414
        Piece Name: /u01/fast_recovery_area/DBName/backupset/backup_filename.bkp
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 1256801    18-JAN-13 /u01/oradata/DBName/system01.dbf
  2    0  Incr 1256801    18-JAN-13 /u01/oradata/DBName/sysaux01.dbf
  3    0  Incr 1256801    18-JAN-13 /u01/oradata/DBName/undotbs01.dbf
  4    0  Incr 1256801    18-JAN-13 /u01/oradata/DBName/users01.dbf
  5    0  Incr 1256801    18-JAN-13 /u01/oradata/DBName/tbspace1_01.dbf

Field Type lists the backup type, in this case Incremental. The field LV lists the level of the backup - 0.

Undo data vs. Redo log

The main difference is that UNDO data can make the change go away, and REDO can make the change happen again if necessary. UNDO holds a copy of previous data untill the transaction commits and for some time afterwards (depending on undo retention period specified), thus providing read consistence, since the user can read the data as it is at the time a transaction started, while transaction is still in the process. Capturing the undo data enables Oracle to roll back the uncommitted data. Undo data is used for both read consistency and to recover from failed transactions.

UNDO segments store BEFORE images of a record, while REDO logs record redo entries.

For example the user adds a record to the database - an employee named John Doe with ID of 3. Currently database contains two records - with IDs of 1 and 2. So UNDO data captures those two records in case we need to rollback the insertion of record with ID 3.

Meanwhile REDO log records all the changes to the database as they happen. If the database crashes, Oracle will first read redo logs and apply all committed changes 9the ones that didn't end up in data files yet, due to the crash) before opening the database for regular use.

Here is a simple differences between the two:

Undo	Redo
Makes a change go away	Reproduces a change
Used for rollback and read consistency	Used for rolling forward the changes
Protects the database from inconsistent reads	Protects from data loss
Data stored in Undo segments in Undo tablespace	Logs stored in SGA, in memory