Tales From A Lazy Fat DBA

Loves all databases! – Its all about performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Archive for November, 2012

10g VS 11g – Differences i’ve noticed so far.

Posted by FatDBA on November 29, 2012


1. by default datafiles and flash recovery options are located one level beloe the ORACLE BASE location but in 10g it is used to be saved under oracle home directory.

2. ADR (Automatic Diagnostic Repository) – is a single directory location for all error and trace data in the database. disgnostic directories such as bdump, cdump and udump is replaced with this option in 11g.

3. Deprecated: iSQL*Plus, Oracle Enterprise Manager Java Console.

4. New Paramaters: Memory_max_target –> sets the maximum value for Memory_Target parameter.
SEC_CASE_SENSITIVE_LOGON: enable or disable password case sensitivity Default: True.
SEC_MAX_FAILED_LOGIN_ATTEMPTS: drops the connection after specified number of login attempts     fail for any user (Default – 10)

5. Oracle SQL Developer.

6. Oracle Database Vault: enabled you to secure business data even from DBA’s.

7. Oracle Wrehouse Builder: BI tool.

8. New privelage SYSASM – Should be granted to user’s who perform ASM related tasks.
The CONNECT command in Oracle 11g SQL*Plus is enhanced to allow connecting as ASM administrator.
CONN[ECT] [{logon | / } [AS {SYSOPER | SYSDBA | SYSASM}]]

SYSASM Privilege and OSASM Group
This feature introduces a new SYSASM privilege that is specifically intended for performing ASM administration tasks.

9. ‘Connect‘ role has now only create CREATE SESSION provilege.

Oracle 9i


GRANTEE                        PRIVILEGE                                ADM
—————————— —————————————- —
CONNECT                        CREATE VIEW                              NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE DATABASE LINK                     NO
Oracle 11g/10g


GRANTEE                        PRIVILEGE                                ADM
—————————— —————————————- —
CONNECT                        CREATE SESSION                           NO

For security consideration, Oracle change right scheme of CONNECT in oracle 11g (The same was available in 10g as well) 

10. Use ALTER FLASHBACK command to alter a flashback archive object. Below are examples of using this command:

11. Read-Only Tables
In Oracle 11g, you can set a table to be read only, i.e. users can only query from the table but no DML statement is allowed on     the table. Following are the commands to achieve this:

12. Shrinking Temporary Tablespaces and Tempfiles
In Oracle 11g, you can shrink temporary tablespaces and tempfiles. Following are the commands to achieve that:
ALTER TABLESPACE temp SHRINK SPACE KEEP 100M; — the KEEP keyword is optional.
ALTER TABLESPACE temp SHRINK TEMPFILE ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’;

13. Creating an Initialization Parameter File from Memory
In Oracle 11g, you can create a pfile or spfile from the current values of the initialization parameters (active in the  instance). Following is the code example to do so:

14. Restore Point Enhancements
Creating Restore Point “as of” an SCN or a Timestamp
With Oracle 11g, you can create a restore point for a specific SCN in the past or a past point in time.

15. When a client process or thread connects to the database, the connection is established between the client and the connection broker (it is a new background process named as connection monitor CMON) in Oracle database.

16. SQL*Plus Error Logging
In Oracle 11g SQL*Plus, the new command SET ERRORLOGGIN can be used to store all errors resulting from executed SQL, PL/SQL and     SQL*Plus commands in special error logging table (by default it is SPERRORLOG).
Following are the commands to enable and disable error logging in SQL*Plus:

The SET ERRORLOGGIN ON command creates the error log table. To view the error stored in the table, use a query like the following:

17. Oracle Database 11g has new option named as Oracle Advanced Table Compression option which aims at reducing space occupied by     data for both OLTP and warehouse databases.
<<<Using Advanced Compression Option in Oracle 11g requires a separate license from Oracle.>>>
Compression in RMAN Backups
The Oracle Advanced Compression option reduces the compression ratio in RMAN backup (by 20%) and increases the backup performance (by 40%) than it does in Oracle 10g.

18. Enhanced TRUNCATE Statement
DROP ALL STORAGE is a new option added to TRUNCATE statement in Oracle 11g R2. It drops the whole segment. In addition to the     TRUNCATE TABLE statement, DROP ALL STORAGE also applies to the ALTER TABLE TRUNCATE (SUB)PARTITION statement.

19. Default Retention of AWR Snapshots Changed
By default, Oracle Database 11g will now retain eight days of AWR snapshot information (as opposed to seven in Oracle 10g).

20. Stronger Password Hash Algorithm
In Oracle Database 11g, the SHA-1 standard became the new algorithm for password hashing.

21. Case-Sensitive Passwords
Oracle 11g introduces case-sensitive passwords for databases created with the default Oracle Database 11g enhanced security.
The SEC_CASE_SENTITIVE_LOGON parameter must be set to TRUE to enable case-sensitive database passwords.

22. Enhanced Block Media Recovery
In Oracle Database 11g, there is a new command to perform block media recovery, named the recover … block command replacing the old blockrecover command. The new command is more efficient since because it searches the flashback logs for older uncorrupted     versions of the corrupt blocks. This requires the database to work in archivelog mode and has the Database Flashback enabled.
Following are examples of using the new command:

23. New RMAN Configuration Parameters
The COMPRESSION ALGORITHM and ARCHIVELOG DELETION POLICY parameters are new RMAN configuration parameters in the Oracle Database 11g.

You can use RMAN to create a persistent configuration that controls when archived redo logs are eligible for deletion from disk or tape. This deletion policy applies to all archiving destinations, including the flash recovery area. When
Following are examples:

— archived redo logs are eligible for deletion when there are at least two backups of them on the tape

24. VALIDATE Command
You can use the new command VALIDATE to manually check for physical and logical corruptions in datafiles, backup sets, and even     individual data blocks. The command by default checks for physical corruption. You can optionally specify CHECK LOGICAL.     Corrupted blocks are reported in V$DATABASE_BLOCK_CORRUPTION.
Following are examples of some VALIDATE command options:
validate [CHECK LOGICAL] database;
validate SKIP INACCESSIBLE database;
validate copy of database;
validate tablespace hr_tbs;
validate copy of tablespace hr_tbs;
validate backupset 17,18;
validate datafile 3 block 24;

25. Active Database Duplication
In Oracle Database 11g, you can directly duplicate a database over the network without having to back up and provide the source  database files. This direct database duplication is called active database duplication. It can be done either with Database  Control or through RMAN.

Create a password file in the destination server with the same SYSDBA password as the source database. You can create the password file manually or by specifying the PASSWORD FILE option on the DUPLICATE command.

— use PASSWORD FILE option

— duplicating a database to a remote host with the same directory structure
TO dupdb
PASSWORD FILE — RMAN should duplicate the password file from the source

26. Data Pump Utilities
Compression Enhancement
In Oracle Database 11g, Oracle provides the mechanism to compress both data and metadata during the extract operation. The     available options for the COMPRESSION parameter are as follows:
compression={all | data_only | metadata_only | none}

27. Reusing a Dump File
In Oracle 11g data pump export utility, the new parameter REUSE_DUMPFILES enables you to overwrite a preexisting dump file.
Following is an example:
expdp hr DIRECTORY=dp_dir DUMPFILE=hr.dmp TABLES=employees REUSE_DUMPFILES=y

Posted in Basics | Tagged: | Leave a Comment »

Faster Incremental Backup’s: Enable Change Tracking (Block Change Tracking)

Posted by FatDBA on November 26, 2012

Prior to version 10g, however, incremental backups required a scan of an entire datafile to detect which blocks in it had changed. For very large databases, this made the time required for incrementals not much better than for full backups, even though space was saved.

Change Tracking, sometimes called Block Change Tracking, provides a solution by recording to a separate Change Tracking file which blocks have changed. The changes are written in real time by a background process CTWR (Change Tracking Writer).

The first level 0 incremental backup still requires a complete scan. Thereafter, the Change Tracking file is consulted during backups and the required blocks are accessed directly.

To create the Change Tracking file and enable Change Tracking, make sure the database is either open or mounted. Then issue an ALTER DATABASE command:

USING FILE <pathname>;

where <pathname> is the operating system pathname to the location where you want the Change Tracking file to be stored. (If you have Oracle Managed Files enabled, the USING clause is optional, and the file will be created in the directory pointed to by DB_CREATE_FILE_DEST.)

Posted in Advanced | Tagged: | Leave a Comment »

UGA (User Global Area) & PGA (Program Global Area)

Posted by FatDBA on November 18, 2012

Before i start explain UGA & PGA areas of Oracle Server one should always has better understanding about Shared Server (Multi Threaded Server – MTS) and Dedicated Server architecture.Let’s start with the basic knowledge about MTS and Dedicated Server Architecture.

Each user process is associated to a single server process. The server process can be Dedicated or Shared. A dedicated process has one to one relationship with user process. A dedicated process occupies certain amount of memory. In oracle database 10g, a dedicated server process consumes about 5 MB of memory. In large scale application, memory requirements for dedicated server can be a serious issue. On the other hand, a shared server process is shared by multiple user processes. We do not need a dedicated server process for every user process, so less memory is required and performance increases. At a given time, Shared server process can serve only one user process. Multiple shared server processes are configured to handle multiple user processes.

Shared server architecture consists of Listener Process, Dispatcher Process, Request Queue, Shared server process and Response Queue.

After deciding MTS is right for your environment, it is now time to dig into the implementation details. The DBA will need to consider the following two issues: User Session-Specific Data (UGA) and Routing Client Requests to Shared Server Processes.

User Session-Specific Data (UGA)
Every connection to the Oracle database has a “session-specific” memory associated with it. This memory is referred to as the User Global Area (UGA) and is used to hold the values of PL/SQL variables, the values of bind variables and other items specific to a session.

With a dedicated server connection, the UGA is stored within the dedicated server process’s Program Global Area (PGA). When an MTS connection is made though, the UGA is stored in either the “LARGE POOL” or the “SHARED POOL”.

In an MTS environment, the UGA needs to be moved into a common memory structure like the large pool or shared pool since it contains “session-specific” data.

Posted in Advanced | Tagged: | 3 Comments »

Background Processes – Revisited

Posted by FatDBA on November 18, 2012

The System Monitor carries out a crash recovery when a crashed instance is started up again. It also cleans temporary segments. SMON checks the SCN in all datafile headers when the database is started. Everything is OK if all of these SCNs matches the SCN found in the controlfile. If the SCNs don’t match, the database is in an inconsistent state. SCN Number is the System Change Number provided by Oracle for each committed transaction. When the database in inconsistent state while the database is opened, crash recovery is done. Crash recovery is the recovery of a database in a single-instance configuration or an Oracle Real Application Clusters configuration in which all instances have crashed. In contrast, instance recovery is the recovery of one failed instance by a live instance in an Oracle Real Application Clusters configuration. SMON wakes up every 5 minutes to perform housekeeping activities. SMON must always be running for an instance. If not, the instance will terminate.

SMON_SCN_TIME is the metadata table populated for every five minutes by SMON, If column_tracking_level is set to 1 SMON also updates sys.col_usage$ another metadata table.

The Process Monitor checks if a user process fails and if so, do all cleaning up of resources that the user process has acquired. In the case of shared server configuration, PMON checks dispatchers and server processes and restarts them if they have failed. It also does another important thing. During service registration, PMON provides the listener with Names of the database services provided by the database. However, if there is no listener at startup, PMON can obviously not register those information. Therefore, PMON tries then periodically to register with the listener, which might take up to 60 seconds. During service registration it provides listener with the following information:

– Name of the associated instance
– Current load and maximum load on instance
– Names of DB services provided by database.
– Information about dedicated servers and dispatchers (depends on database server mode i.e dedicated/shared server mode)

PMON process wakes up at every 60 seconds and provide information to the listener. If any problem arises and your PMON process fails then it’s not possible to register information to listener periodically. In this case you can do ‘Manual service registration’ using command:


This command forces the registration of database information to the listener.

MMAN (Memory Manager) is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases.
This process was introduced in Oracle 10g.


The Database Writer writes dirty blocks from the database buffer to the datafiles. is determined by the Initialization parameter Initialization parameter DB_WRITER_PROCESSES determines on how many DBWn Processes are started . DBWR also writes the actual SCN with the Block and flushes datafile blocks.

The Log Writer writes the redo log buffer from the SGA to the online redo log file. It wakes up for every three seconds to flush from the redo log buffer to the online redo logs or Whenever the redo log buffer is filled up to _log_io_size defaults to 1/3 of the log buffer size) and LGWR is not currently writing. This parameter is defined in units measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle. This is called X$ table owned by SYS. The entire X$ tables are memory structures generated by Oracle when instance is spawned. They die with Instance. This is a separate discussion which we do later at another point of time.

The Distributed Transaction Recovery Process finds pending (distributed) transaction and resolves them.

The Checkpoint Process regularly initiates a checkpoint, which uses DBWR to write all dirty blocks back to the datafiles, thus ensuring a consistent state to the database. Since a Checkpoint records the current SCN, in a recovery only redo records with a SCN higher than that of the last checkpoint need to be applied. The ckpt process also updates the datafile headers. The ckeckpoint process was optional in Oracle 7 (set CHECKPOINT_PROCESS=TRUE) and could be enabled to speed-up checkpointing on databases with a large number of files. Starting with Oracle 8i, the checkpoint process is automatically started with the other Oracle processes at instance startup.

The Archiver Process archives redo log files if ARCHIVELOG is enabled. This means you have the capability of Media Recovery in case of failure.
The Dispatcher Process is used in a shared server environment. Dispatcher Processes, suppor multi-threaded configuration by allowing user processes to share limited number of server processes.

The Shared Server Process is used in a shared server environment. shared Server Processes serve multiple client requests in the multi-threaded server configuration

The lock manager.

AQ Time Manager

Trace writer. The System Tracking process.

The wakeup monitor process.

Inter-instance locking process.

The snapshot process.

Qmnn (Queue Monitor)
Optional for Oracle Advanced Queuing which monitors the message queues. Used by Oracle Streams Advanced Queuing.
It makes snapshots of the database health (statistics) and stores this information in the automatic workload repository. MMON is also responsible for issuing alerts for metrics that exceed their thresholds.  This process was introduced in Oracle 10g.

Automated Storage Management ASM Instance Background Processes(10g)
Below provided are some new background processes added for an ASM instance

This processes coordinates re-balance activity for disk groups. In an ASM instance coordinated rebalancing operations. In a DB instance, opens and mount diskgroups from the local ASM instance.

ARBx – Oracle backgroud processes. In an ASM instance, a slave for rebalancing operations

GMON – Oracle backgroud processes. In an ASM instance, diskgroup monitor.

ASMB – Oracle backgroud process. In an DB instance, keeps a (bequeath) persistent DB connection to the local ASM instance. Provides hearthbeat and ASM statistics. During a diskgroup rebalancing operation ASM communicates to the DB AU changes via this connection.

list of the new background processes in Oracle 11g:

The virtual keeper of time provides wall-clock time and reference time for other sessions/processes.

The flashback data archiver writes old row-versions of tables with ‘flashback archive’ enabled into flashback data archives on commit.

The diagnosibility process (DIAG) runs oradebug commands and triggers diagnostic dumps as part of the new ADR (automatic diagnostic repository) feature.

The “other” diagnosibility process detects hangs then triggers DIAG do take diagnostics.

The space management coordinator and slaves perform space allocation and reclamation.

There are many more. Please check below link to check various other back-end processes.



Posted in Basics | Tagged: | 1 Comment »


Posted by FatDBA on November 18, 2012

Both ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVELOG CURRENT will force a log switch, but they do it in different ways.

ARCHIVELOG CURRENT waits for the writing to complete.  This can take several minutes for multi-gigabyte redo logs.  Conversely, the ALTER SYSTEM SWITCH LOGFILE command is very fast and returns control to the caller in less than a second while ALTER SYSTEM ARCHIVELOG CURRENT pauses.

  • ALTER SYSTEM  SWITCH LOGFILE is asynchronous:  This command is fast to return to the invoking program because the writing of the redo log to the OS filesystem is done in the background.  There is a very small risk in cases where the ARCH process cannot complete writing the redo log, such as cases where the OS archivelog file directory is out of space.  It is also risky because the calling script may move on to a subsequent step, assuming that the redo has been written.  Some scripts will place a SLEEP 60 command in their backup script to allow time for the redo to complete writing, but this is not a best practice.
  • ALTER SYSTEM ARCHIVELOG CURRENT is synchronous:  This is faster to return because this command waits until the online redo log has completed the writing of the redo log file to the filesystem.  This command is safer because it waits for the OS to acknowledge (ACK) that the redo log has been successfully written.  Hence, ALTER SYSTEM ARCHIVELOG CURRENT is the best practice for production backup scripts with RMAN.
  • ALTER SYSTEM ARCHIVE LOG ALL triggers an archive process if your DB (System) has archive logs that needs to be apply if still you try using the command you’ll receive below provided error message.

ERROR at line 1:
ORA-00271: there are no logs that need archiving

Posted in Advanced | Tagged: | 1 Comment »

SQLPLUS: Command Prompt customizied.

Posted by FatDBA on November 15, 2012

Some tips and tricks for SQLPLUS terminal:

SQL Prompt With Time
SQL> set time on
09:50:16 SQL>
09:50:19 SQL> select sysdate from dual;


09:50:34 SQL>

SQL Prompt With USER
SQL> set sqlprompt “_USER> ”
SCOTT> select sysdate from dual;



SQL Prompt With SID
SQL> set sqlprompt “_CONNECT_IDENTIFIER> ”
mydb> select sysdate from dual;



SQL Prompt With USER@SID
SQL> set sqlprompt “_USER’@’_CONNECT_IDENTIFIER SQL> ”
SCOTT@mydb SQL> select sysdate from dual;



SQL Prompt With DATE
SQL> set sqlprompt “_DATE SQL> ”
20-APR-11 SQL>
20-APR-11 SQL>
20-APR-11 SQL> select sysdate from dual;


20-APR-11 SQL>

Many More Soon!!!

Posted in Basics | Tagged: | Leave a Comment »

ORA-01555: Snapshot Too Old

Posted by FatDBA on November 7, 2012

The ORA-01555 error can occur when a long read only transaction is run against database
and there are many DML transactions being executed on database (on same data).
The longer query runs, there are more chances of encountering ORA-01555 exception.

The ORA-01555 is caused by Oracle “Read Consistency Mechanism”.
Oracle provides read consistency by reading the “before image” of updated data
from “Online UNDO Segments”. If there are lots of updates, long running read-only SQL
and a small UNDO, the ORA-01555 error may encounter.

ORA-01555 can be avoided by following precautions:
1. Database should be in Automatic Undo Management mode.
2. Use a large optimal value for undo_retention.
3. Use a large optimal size of rollback segment (undo) size.
4. Should Commit less often at the time of long running query, to reduce Undo Segment slot reuse.
5. Try to run long running queries on off peak hours, when there is less DML transactions

You can use the retention guarantee parameter (introduced in Oracle 10g) of the create UNDO Tablespace or alter UNDO Tablespace command, as shown in these examples:

-- Assumes OMF is configured.
Create UNDO Tablespace undotbs01
size 200m autoextend on
retention guarantee;

alter tablespace undotbs01 retention guarantee;

Use the alter tablespace retention noguarantee command to reset an UNDO Tablespace so that undo retention is no longer guaranteed.


— Reset the undo low threshold.

— Guarantee the minimum threshold is maintained.

SELECT tablespace_name, retention FROM dba_tablespaces;

—————————— —————————————————————–
SYSTEM                             NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                            NOT APPLY
TEMP                               NOT APPLY
USERS                             NOT APPLY

5 rows selected.

— Switch back to the default mode.

—————————— —————————————————————-
SYSTEM                            NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                            NOT APPLY
TEMP                               NOT APPLY
USERS                             NOT APPLY

5 rows selected.

Posted in Advanced | Tagged: | Leave a Comment »

Recovery: Types ?

Posted by FatDBA on November 5, 2012

What Are the Types of Recovery?

There are three basic types of recovery: instance recovery, crash recovery, and media recovery. Oracle performs the first two types of recovery automatically at instance startup; only media recovery requires you to issue commands.

Instance Recovery
Instance recovery, which is only possible in an OPS (Oracle Parallel Server) configuration, occurs in an open database when one instance discovers that another instance has crashed. A surviving instance automatically uses the redo log to recover the committed data in the database buffers that was lost when the instance failed. Further, Oracle undoes any transactions that were in progress on the failed instance when it crashed and then clears any locks held by the crashed instance after recovery is complete.
Crash Recovery
Crash recovery occurs when either a single-instance database crashes or all instances of a multi-instance database crash. In crash recovery, an instance must first open the database and then execute recovery operations. In general, the first instance to open the database after a crash or SHUTDOWN ABORT automatically performs crash recovery.
Media Recovery
Unlike crash and instance recovery, media recovery is executed on your command. In media recovery, you use online and archived redo logs and (if using RMAN) incremental backups to make a restored backup current or to update it to a specific time. It is called media recovery because you usually perform it in response to media failure. Media recovery is a process of restoring the physical backups and making all the restored datafiles consistent with each other by having same SCN’s in their header’s.

If you do not use Recovery Manager (RMAN) to perform recovery, then you should use the SQL*Plus RECOVER command. It is also possible to use the SQL statement ALTER DATABASE RECOVER, but it is highly recommended that you use the SQL*Plus RECOVER command instead.

When using SQL*Plus to perform media recovery, the easiest strategy is to perform automatic recovery. Automatic recovery initiates recovery without manually prompting SQL*Plus to apply each individual archived log.

When using SQL*Plus, you have two options for automating the application of the default filenames of archived redo logs needed during recovery:

  • Specifying the AUTOMATIC keyword as an option of the RECOVER command   – RECOVER AUTOMATIC DATABASE


what are the types of media recovery in oracle database?

  1. Complete recovery
  2. Incomplete recovery

Though it is complete recovery or incomplete recovery, the recovery types classified based on what type of failure we are going to recover.

  1. Full database recovery
  2. Tablespace recovery
  3. Datafile recovery
Types of Media recovery
Complete Recovery Incomplete Recovery
Complete Recovery involves recovery of database with out any data loss i.e full recovery. Making all datafiles consistent with each other i.e having latest SCN and opening database normally with out resetlogs option. Incomplete Recovery means recovering database to a specified time, SCN no, log sequence no or using cancel. In this type of recovery the database can open only with RESETLOG option and database can not open normally.

What is complete media recovery?

Complete media recovery is process of restoring full database, a tablespace or a datafile from backup based upon media failure and applying the redo log files to the most current time with of loosing any data’s.

To determine which files to recover:

SQL> SELECT * FROM v$recover_file;

FILE#      ONLINE  ERROR              CHANGE#    TIME     
---------- ------- ------------------ ---------- ---------
        14 ONLINE                              0          
        15 ONLINE  FILE NOT FOUND              0          
        21 OFFLINE OFFLINE NORMAL              0

When you perform complete recovery, you can either recover the whole database at once or recover individual tablespaces or datafiles. Because you do not have to open the database with the RESETLOGS option after complete recovery as you do after incomplete recovery, you have the option of recovering some datafiles at one time and the remaining datafiles later. Types of recoveries comes under Complete Media Recovery:

  • Performing Closed Database Recovery
  • Performing Open Database Recovery

How complete full database media recovery are made (Performing CLOSED DATABASE Recovery)?

Following way of complete full database recovery is used in both operating system command along with sqlplus recovery command or RMAN utility.

    • Shutdown your database using SHUTDOWN ABORT option
    • Mount the Database using STARTUP MOUNT
    • Restore all the datafiles from the backup and make all datafiles online.
    • Apply online redo log files or archived redo log files or both.
    • open the database with below command.

sql>alter database open;

How complete tablespace or datafile recovery made (Performing  OPEN DATABASE recovery) ?

Following methods applies to both operating system commands along with Sqlplus RECOVERY command or RMAN utility method to recover datafile or tablespace.

    • During database open take tablespace or datafile which need recovery offline.
    • Restore the backup tablespace or datafile.
    • Apply the online redo log files or archive redo log files or both.
    • Open the database with below command.

sql>alter database open;

How incomplete recovery done in oracle database?

Incomplete recovery in oracle database are done using following option when applying archived redo log files and online redo log files.

  1. Time based recovery
  2. Cancel based recovery
  3. Change based recovery
  4. Log sequence recovery

The recover database until  command supports three clauses that will abort the recovery process at a specified point:

  • recover database until time  ‘YYYY-MM-DD:HH24:MI:SS’ recovers the database to a specified point in time.
  • recover database until cancel recovers the database applying one log file at a time until the recovery process is manually canceled
  • recover database until change <scn>recovers the database to a specified system change number ( SCN)

After using any one option open the database with following commands,

sql>alter database open resetlogs;

The above statement reset the log sequence to 0 and this command is used when we need incomplete recovery.

What are the methods of media recovery available in oracle database?

  1. Recovery using operating system.
  2. RMAN Recovery Manager.
Methods of Media recovery
Using Operating system commands and sqlplus RECOVERY command RMAN (Recovery Manager)
This old traditional method of physical backup and recovery available in oracle. From oracle 9i onwards oracle recommends to use RMAN which overcomes the disadvantages in backup and recovery using operating system commands. Since backup taken with OS commands can be used with RMAN utility. But backup taken with RMAN cannot used with OS commands for media recovery directly. RMAN means recovery manager to define it in simple words like logical utility (import,impdp) this is physical backup and recovery utility comes along with oracle database software. This makes oracle database server itself to take backup and recovery it during failure. This backup information are stored in target(backup) database control file and catalog database scheme(optional one if it is used).

Posted in Advanced | Tagged: | Leave a Comment »

Oracle Big Data: What’s the Hype …

Posted by FatDBA on November 5, 2012

“Oracle Big Data: A revolution that has already begun … ”

Read About It:





Posted in Advanced | Tagged: , | Leave a Comment »

Point-In-Time Recovery Tablespaces (Using RMAN)

Posted by FatDBA on November 4, 2012

Point In Time recovery for Tablespaces also known as TSPITR allows you to recover one of more tablespaces to any past time.

Deciding When to Use TSPITR
Like a table import, RMAN TSPITR enables you to recover a consistent data set; however, the data set recovered includes an entire tablespace rather than one object.

RMAN TSPITR is most useful for situations such as these:

  • Recovering data lost after an erroneous TRUNCATE TABLE statement;
  • Recovering from logical corruption of a table;
  • Recovering a logical schema to a point different from the rest of the physical database, when multiple schemas exist in separate tablespaces of one physical database.

Note that, as with database point-in-time recovery (DBPITR), you cannot perform TSPITR if you do not have your archived redo logs. For databases running in NOARCHIVELOG mode, you cannot perform TSPITR. You can only restore your entire database from a consistent backup.

Limitations of TSPITR
There are a number of situations which you cannot resolve by using TSPITR.

  • You cannot recover dropped tablespaces.
  • You cannot recover a renamed tablespace to a point in time before it was renamed. If you try to perform a TSPITR to an SCN earlier than the rename operation, RMAN cannot find the new tablespace name in the repository as of that earlier SCN (because the tablespace did not have that name at that SCN).
  • You cannot recover tables without their associated constraints, or constraints without the associated tables.
  • Tablespaces that contain objects owned by SYS, including rollback segments..

RMAN > Recover tablespace DIXIT until time
“to_date(‘2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS’)”
auxiliary destination ’/opt/oracle/temp’;

Once the above statement is executed, RMAN does the following for us:

  • Creates auxiliary instance (including the pfile etc..)
  • Mounts the auxiliary instance
  • Makes the candidate tablespace into OFFLINE
  • Restores the basic tablespace UNDO, SYTEM,SYSAUX and then the required tablespace
  • Applies archives (completes recovery)
  • Opens the database
  • Performs an export of the objects residing in the tablespace
  • Shutdown aux instance
  • Import the objects into the target database
  • Remove aux instance and cleanup


That’s all and you now have all the objects back in the tablespace lost!.

Posted in Advanced | Tagged: | Leave a Comment »

%d bloggers like this: