In this post we will discuss how oracle does crash recovery or instance recovery. Crash recovery is when oracle database is restarted after crashing while running due to any reason(power gone,resource shortage etc.). Oracle database has to return to a consistent state after the restart. Before the crash many transactions may be inflight which were not committed yet and some of the transactions might have been committed but because the crash happens before the check pointing the data files on disk and the data blocks related to those transactions may not have been written to by DBWR process. So after the restart oracle db should have only the committed transactions before the crash and any uncommitted transactions should be discarded. The committed transaction details must have made it to the disk but there may be uncommitted transaction changes also present on disk. the uncommitted block being present on disk is not very obvious and we will try to demystify this phenomenon in this post. For understanding this we would have to delve into the oracle db architecture. Three background processes are at play here lgwr,dbwr and ckpt.
1. LGWR ? Whenever any transaction is committed writing to oracle data files synchronously can be very costly as oracle would have to find the actual blocks which have to be written to on the disk and these blocks may be scattered and random read/writes on disk is very expensive. So oracle writes all the changes done to it in a file which is only in append mode and is written sequentially. This file is called redo log and any changes done to the db can be replayed from it. LGWR is the process responsible for writing to the red logs from redo log buffers. Redo log buffers are circular in nature and they are reused. As soon as any transaction is committed LGWR persists the data in redo logs and signals the db after which user receives the message of commit successful. Sometimes during period of high db activity even before commit the redo log buffers can be written to log files in case buffer is filled more then 1/3 rd. This way uncommitted data also makes it way to the disk.
2.CKPT- Check pointing is the activity which modifies controlfile and datafile headers indicating the SCN upto which redo logfile data have been synchronized between db buffer cache and disk and signals DBWR process to flush the dirty blocks to disk.
3.DBWR ? Whenever any block is requested from oracle db it is fetched into the db buffer cache. All the dml activities happen in db buffer cache(in memory) and it is asynchronously synchronized with disk by DBWR process on being signaled by CKPT process. when any block in buffer cache is updated and has not been synchronized yet it is called dirty block or buffer. Before DBWR can write to disk all the corresponding redo entries must have been written to redo log files. sometimes when long running transactions are at play the number of buffers getting dirty increases. when clean buffers are required and not available the dirty buffers on cold end of buffer list i.e least recently used blocks are flushed to disk even if the commit instructions have not been received. When DBWR flush happens corresponding redo log buffers are also force flushed to redo log files by lgwr if it had not happened yet by then.

So from above explanation we see two possibilities of uncommitted transactions being made to disk
?When redo log buffers get filled at a high speed lgwr is forced to write uncommitted changes also to redo log files
?When dirty buffers increase a lot in buffer cache due to long running transactions accompanied with high db activity and clean buffers are required for any processing forcing dbwr to write uncommitted changes to the disk.

If we want to have quick crash recovery then frequent checkpoints should happen. As checkpoints mark the point in redo log file from where the roll forward process should start as prior to that the redo log changes and datafile are in sync. In the roll forward stage oracle replays all the redo files required which make the oracle database up to date till the time crash happened with all the committed and uncommitted transactions. After that roll backward process is started which removes all the uncommitted transactions form the db using the undo segments and makes the database consistent as of point in time before crash.

In today’s world insider threat is a major issue and the one which is harder to get noticed or surfaced in any IT systems.


It assumes even greater importance when the threat is to our information assets. Data is a very strategic asset for any organisation as it can reveal all the proprietary knowledge of an organisation to the world and in some cases corrodes the trust reposed in the organisation by its customers if the customer’s data is compromised. For the uninitiated “insider threat” is defined as the fraudulent or sabotaging activities carried out by the very people who are supposed the keep the system safe and sound. They are the most powerful users in terms of the privileges available to them and can wreak havoc with the systems if start working with a malefic intent. For instance DBA is the most powerful user of the database. If the DBA has business knowledge of the system she can change the data to her own advantage and can get away with easily. For example in a financial system money can be transferred to anyone’s account by changing the account information. How can it be prevented from happening? How can we restrict the ability of power users of the system to tweak the system to their ow advantage? Traditionally we would have had to rely on the business processes and the auditing of user activities and its more of a reactive approach. However Oracle provides a tool called oracle data vault whose sole purpose is to prevent from insider threats proactively.

  • Data Vault enables us to apply fine-grained access control to your sensitive data in a variety of ways. It hardens your Oracle Database instance and enforces industry standard best practices in terms of separating duties from traditionally powerful users. Most importantly, it protects your data from super-privileged users but still allows them to maintain our Oracle databases.
  • It addresses the most difficult security problems remaining today: protecting against insider threats, meeting regulatory compliance requirements, and enforcing separation of duty.

Data vault has the following main components

  • Realms– A realm is a functional grouping of database schemas, objects, and roles that must be secured. For example, we can group a set of schemas, objects, and roles that are related to accounting, sales, or human resources. After we have grouped these into a realm, we can use the realm to control the use of system privileges to specific accounts or roles.


  • Command rules – A command rule is a special rule that we can create to control how users can execute almost any SQL statement, including SELECT, ALTER SYSTEM, database definition language (DDL), and data manipulation language (DML) statements
  • Rule sets –A rule set is a collection of one or more rules that we can associate with a realm authorization or command rule. The rule set evaluates to true or false based on the evaluation of each rule it contains and the evaluation type (All True or Any True). The rule within a rule set is a PL/SQL expression that evaluates to true or false. We can have the same rule in multiple rule sets

Once a realm is created then only the realm owner or realm participant has access to all the objects inside a realm. Even the system and sys users cannot access these objects unless authorized. Data vault provides security by providing separation of duty.DBA should only be able to monitor an maintain the system and it should not have access to business related objects and data. Separation of duty is accomplished by creating another set of powerful users in database when vault is installed and enabled. The SOD is provided as below

Account management. Account management entails creating, modifying, and dropping user accounts. The DV_ACCTMGR role provides these privileges.

  • Security administration. Security administration covers basic security tasks such as creating realms and command rules, setting security policies for database users’ access, and authorizing database users for jobs they are allowed to perform. Security administrators also run security audit reports. The DV_OWNER and DV_ADMIN roles provide these privileges. Optionally, you can consolidate the account management and security administrative responsibilities.
  • Resource management. Resource management refers to managing the database system but not accessing business data. It includes the following operations:
  • Backup operations require a predefined time to perform the backup using predefined tools.
  • Tuning and monitoring operations require ongoing performance monitoring and analysis.
  • Patching operations require temporary access only during the time the patching takes place

For resource management, you should create a named account and a backup account for each of these tasks. Add these accounts as owners of the Data Dictionary realm. Use these accounts as the primary resource managers in the database.