oracle sql and dba tutorial logo

Tutorial for Oracle SQL

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions

Oracle DBA Topics

Overview of Oracle Grid Architecture

Difference between cluster and grid

Responsibilities of DBA's

Creating Oracle Database

Creating Database using SQL

Creating DB in 12c using DBCA

Creating DB in 12c using SQL commands

Managing Oracle 12c Container Databases

Creating Users in Oracle 12c

Managing Pluggable Databases in Oracle 12c

Creating Pluggable Database

Cloning Pluggable Database

Unplug and Plug databases in CDB's

Managing Tablespaces and Datafiles

Extending tablespaces

Decreasing size of a tablespace

Coalescing Tablespaces

Taking tablespaces Offline or Online

Making a Tablespace Read only

Renaming Tablespaces

Dropping Tablespaces

Viewing Info about Tablespaces

Renaming Datafiles

Relocating Datafiles

Relocating Datafiles in Multiple Tablespaces

Temporary Tablespace

Changing the size

Tablespace Groups

Creating a Temp. Tablespace Group

Assigning a Tablespace Group

Diagnosing and Repairing LMT

Scenario 1: Fixing Bitmap

Scenario 2: Dropping a Corrupted Segment

Scenario 3: Fixing Bitmap when Overlapped

Scenario 4: Correcting Corruption of Blocks

Scenario 5: Migrating from a Dictionary-Managed to LMT

Transporting Tablespaces

Procedure for transporting tablespaces

Transporting Tablespace Example


Adding New Redo Logfile Group

Adding Members to group

Dropping Members from group

Dropping Logfile Group

Resizing Logfiles

Renaming or Relocating Logfiles


Viewing Info About Logfiles

Managing Control Files

Multiplexing Control File

Changing the Name of a Database

Creating A New Control File

Cloning an Oracle Database


Switching to Automatic Management

Calculate Undo Retention

Altering UNDO Tablespace

Dropping an Undo Tablespace

Switching Undo Tablespaces

Viewing Information

SQL Loader


From Fixed Length file

From MySQL to Oracle

Using WHEN condition

Conventional and Direct Path Load

Direct Path

Restrictions on Using Direct Path Loads

Export and Import

Invoking Export and Import

Parameters of Export tool

Exporting Full Database

Exporting Schemas

Exporting Tables

Exporting Consistent Image of tables

Using Import Utility

Example Importing Tables

Import from One User to another User

Using Pattern Matching

Migrating a Database across platforms


Data Pump Export

Exporting a Full Database

Exporting a Schema

Exporting Individual Tables

Filtering Objects during Export

Filter Rows during Export

Suspending and Resuming Export

Data Pump Import Utility

Importing Full Dump File

Importing Objects between Schemas

Loading Objects between Tablespaces

Generating SQL File of DDL commands

Import objects of a Schema

Importing Only Particular Tables

Interactive Mode

Flash Back Features

Flashback Query

Flashback Version Query

Flashback Table to Past States

Purging Objects from Recycle Bin

Flashback Objects With Same Name

Flashback instead of PIT Recovery

Enabling Flash Back Database

Sizing flash recovery area

How far we can flashback database

Example:Flashing Back Database

Flashback Data Archive (FDA)
(Oracle Total Recall)


Creating FDA tablespace

Creating FDA

Querying historical data

Log Miner

LogMiner Configuration

LogMiner Dictionary Options

Using the Online Catalog

Extracting Dictionary to Log Files

Extracting Dictionary to File

Redo Log File Options

Example: Finding All Modifications

Mining Logfiles in a Time Range


Opening DB in Archivelog Mode

Reverting back to NoArchiveLog mode

Taking Offline (COLD) Backups

Taking Online (HOT) Backups

Recovering from the Loss of a Datafile

When in Noarchivelog Mode

When in Archivelog Mode

Recovering from loss of Control File

Recovery Manager ( RMAN )

Offline Backups using RMAN

Recover DB when in NOARCHIVELOG

Online Backups using RMAN

Backup tablespaces or datafiles

Image Backups in RMAN

Incremental Backup using RMAN

Updating backup copy for fast recovery

View info about RMAN backups

Configuring Retention policy

Configure Options in RMAN

Maintaining RMAN Repository

Recover datafiles (Archivelog mode)

Recover datafiles by renaming

Performing Disaster Recovery










Backup and Recovery in Oracle

Opening or Bringing the database in Archivelog mode.

To open the database in Archive log mode. Follow these steps:

STEP 1: Shutdown the database if it is running.

STEP 2: Take a full offline backup.

STEP 3: Set the following parameters in parameter file.


If you want you can specify second destination also


Step 3: Start and mount the database.


STEP 4: Give the following command


STEP 5: Then type the following to confirm.


STEP 6: Now open the database

SQL>alter database open;

Step 7: It is recommended that you take a full backup after you brought the database in archive log mode.

To again bring back the database in NOARCHIVELOG mode. Follow these steps:

STEP 1: Shutdown the database if it is running.

STEP 2: Comment the following parameters in parameter file by putting " # " .

# LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
# LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”
# LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc

 STEP 3: Startup and mount the database.


STEP 4: Give the following Commands


STEP 5: Shutdown the database and take full offline backup.


Shutdown the database if it is running. Then start SQL Plus and connect as SYSDBA.

$ sqlplus
SQL> connect / as sysdba
SQL> Shutdown immediate
SQL> Exit

After Shutting down the database. Copy all the datafiles, logfiles, controlfiles, parameter file and password file to your backup destination.


To identify the datafiles, Logfiles query the data dictionary tables V$DATAFILE and V$LOGFILE before shutting down.

Lets suppose all the files are in "/u01/ica" directory. Then the following command copies all the files to the backup destination /u02/backup.

$ cd /u01/ica

$ cp * /u02/backup/

Be sure to remember the destination of each file. This will be useful when restoring from this backup. You can create text file and put the destinations of each file for future use. Now you can open the database.


To take online backups the database should be running in Archivelog mode. To check whether the database is running in  Archivelog mode or Noarchivelog mode. Start sqlplus and then connect as SYSDBA.

After connecting give the command "archive log list" this will show you the status of archiving.

$ sqlplus
Enter User:/ as sysdba

If the database is running in archive log mode then you can take online backups.

Let us suppose we want to take online backup of  "USERS" tablespace. You can query the V$DATAFILE view to find out the name of datafiles associated with this tablespace. Lets suppose the file is 

"/u01/ica/usr1.dbf ".

Give the following series of commands to take online backup of USERS tablespace.

$ sqlplus
Enter User:/ as sysdba
SQL> alter tablespace users begin backup;
SQL> host cp /u01/ica/usr1.dbf   /u02/backup
SQL> alter tablespace users end backup;
SQL> exit;


Option 1: When you don’t have a backup.

If you have lost one datafile and if you don't have any backup and if the datafile does not contain important objects then, you can drop the damaged datafile and open the database. You will loose all information contained in the damaged datafile.

The following are the steps to drop a damaged datafile and open the database.


STEP 1: First take full backup of database for safety.

STEP 2: Start the sqlplus and give the following commands.

$ sqlplus
Enter User:/ as sysdba
SQL> ALTER DATABASE DATAFILE  '/u01/ica/usr1.dbf '  offline drop;
SQL> alter database open;

Option 2: When you have the Backup

If the database is running in Noarchivelog mode and if you have a full backup. Then there are two options for you.

i . Either you can drop the damaged datafile, if it does not contain important information which you can  
  afford to loose.

ii . Or you can restore from full backup. You will loose all the changes made to the database since last full     

To drop the damaged datafile follow the steps shown previously.

To restore from full database backup. Do the following.

STEP 1: Take a full backup of current database.

STEP 2: Restore from full database backup i.e. copy all the files from backup to their original locations.


Suppose the backup is in  "/u2/oracle/backup" directory. Then do the following.

$ cp /u02/backup/*  /u01/ica

This will copy all the files from backup directory to original destination. Also remember to copy the control files to all the mirrored locations.


If you have lost the control file and if it is mirrored. Then simply copy the control file from mirrored location to the damaged location and open the database

If you have lost all the mirrored control files and all the datafiles and logfiles are intact. Then you can recreate a control file.

If you have already taken the backup of control file creation statement by giving this command. " ALTER DATABASE BACKUP CONTROLFILE TO TRACE; " and if you have not added any tablespace since then, just create the controlfile by executing the saved statement

Buf If you have added any new tablespace after generating create controlfile statement. Then you have to alter the script and include the filename and size of the file in script file.

If your script file containing the control file creation statement is "CR.SQL"

Then just do the following.

STEP 1: Start sqlplus

STEP 2: connect / as sysdba

STEP 3: Start and do not mount a database like this.


STEP 4: Run the "CR.SQL" script file.

STEP 5: Mount and Open the database.

SQL> alter database mount;
SQL> alter database open;

If you do not have a backup of Control file creation statement. Then you have to manually give the CREATE CONTROL FILE statement. You have to write the file names and sizes of all the datafiles. You will lose any datafiles which you do not include.

Refer to "Managing Control File" topic for the CREATE CONTROL FILE statement.

Recovering Database when the database is running in ARCHIVELOG Mode

Recovering from the lost of Damaged Datafile

If you have lost one datafile. Then follow the steps shown below.

STEP 1. Shutdown the Database if it is running.

STEP 2. Restore the datafile from most recent backup.

STEP 3. Then Start sqlplus and connect as SYSDBA.

$ sqlplus
Enter User:/ as sysdba
SQL> Startup mount;
SQL> Set autorecovery on;
SQL> alter database recover;

 If all archive log files are available then recovery should go on smoothly. After you get the "Media Recovery Completely" statement. Go on to next step.

STEP 4. Now open the database

SQL>alter database open;

Recovering from the Lost Archived Files:

If you have lost the archived files. Then Immediately shutdown the database and take a full offline backup.


Suppose a user has a dropped a crucial table accidentally and you have to recover the dropped table.

You have taken a full backup of the database on Monday 13-Aug-2007 and the table was created on Tuesday 14-Aug-2007 and thousands of rows were inserted into it. Some user accidently drop the table on Thursday 16-Aug-2007 and nobody notice this until Saturday.

Now to recover the table follow these steps.

STEP 1. Shutdown the database and take a full offline backup.

STEP 2. Restore all the datafiles, logfiles and control file from the full offline backup which was taken on Monday.

STEP 3. Start SQLPLUS and start and mount the database.

STEP 4. Then give the following command to recover database until specified time.

SQL> recover database until time '2007:08:16:13:55:00' using backup controlfile;

STEP 5. Open the database and reset the logs. Because you have performed a Incomplete Recovery, like this

SQL> alter database open resetlogs;

STEP 6. After database is open. Export the table to a dump file using Export Utility.

STEP 7. Restore from the full database backup which you have taken on Saturday.

STEP 8. Open the database and Import the table.

Note: In Oracle 10g / 11g you can easily recover drop tables by using Flashback feature. For further information please refer to Flashback Features Topic in this book.




HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats

Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved