Oracle DBA Interview Questions

In case of hot backup,table space is put into backup mode.At the time ts related datafile header is frozen
because for taking backup with out any data loss.And after that any data is coming,that one going to redolog file for recovery.

We need to backup so that it can be used in case if any failure. We restore it from these backup. Now for the backup to be used for restore it has to be valid backup. Some thing like SCN, Timestamp ha...

Oracle catalog contain the metadata of objects.
RMAN catalog maintain the backup information.
Redo data persistently stored in archive logs,for recovery..

Catalog term is used in 2 ways.Catalog - All tables/views stores information about database and all objects in it.RMAN Catalog - Schema where RMAN executable stores/reads/writes the information about ...

The dbms_advisor package has a procedure called dbms_advisor.quick_tune that allows the DBA to quickly tune a single SQL statement with a single procedure call

Hi , I just reviewed your answer. Anyway thanks for your effort to give us response.But I have a concern .Suppose a user just reported us that his query running slow and we checked the execution plan,...

Start up No mount: instance started with help of parameter file & here control files are located.

Start up mount:database is mounted i.e control files are allocated & data files, redo logs are located.

Open: here allocated all data & redo logs.Now the database is ready to use.

Nomount : Instance started
Mount : control file open for this instance
open : All the files open as described by the control file.

SCN is system change number.it will generate every time when log switch occurred & user commits the transaction.Main thing is scn will generated with every transaction &(or) every one seconds.

SCN-SYSTEM Change number, it will generate automatically while u perform commit.

Database having maximum up to 65536 data files. Each table space having 1022 data files.

You can create maximum 65536 datafiles

Using spfile,change the parameters dynamically.But in pfile,after change the parameters u must restart the database..
spfile is a binary file n it will automatically created with dbca utility(at db creation time).Pfile is a text file n it will edit by any text editor.

Spfile server parameter file it is an substitute for pfile with the benefit of "make the changes in this spfile dynamically means if we made the changes in the spfile there is no need to restart of da...

Materialized views are useful for fast REPLICATION purpose..

For a cold backup :shutdown oracle first and then proceed the cold(offline) back up. once you shutdown the oracle database insert the external tape/disk.pls ensure the tape/disk is in good condition. ...

Some Common issues with Backup. If you are taking backup from no-archive log: then your database doesn't bring down due to some processes are running. If you are taking hot backup then may be newly ...

We can use dbms_redefinition package to achieve this

By creating a dump file of one schema and then exporting it to other schema

Dear friend, it is not RAP but it is WRAP. Using this utility of Oracle, we can hide our PL/SQL code from user.

Materialized view is useful in replication and data warehousing environment where every time data extracting and data loading from another sites,databases. Materialized view also useful for maintaining complex queries because it uses query rewrite feature.

Exactly View is logical identity.Oracle only stores definition of View in metadata. Using view, we can hide some columns of tables from users, we can hide original table name from user. Using view we can perform complex query in single statement.

Assume that the dba is modifying a tables and the same time developer want to use that database a then dba can provide him the view for that tables.

Code
CREATE VIEW VIEW_NAME(SELECT * FROM A);

Logical Structure of Oracle is following.

Tablespace =>Schema => Segments => Extent => Oracle Block. (Remember Oracle block is smallest logical unit).

the logical components of database are tablespaces, segments, extents, datablocks. datablock is the smallest unit of oracle I/0.

Data always copied in buffer cache from data file by only Server Process not DBWR.

Database writer writes data from Buffer to Disk But Server process loads data from Disk to Buffer

DDL lock are exclusive locks. It shared the data. Means anyone can select data from same table.

there dba can break the lock given to table.
but in real time, no dba has the right to break the lock given by the user

Daily Tasks of DBA are following. 1. Disk Space checking for all databases, tablespaces, archive log destinations, dump destinations, and backup destinations. 2. All backup successfully finished or n...

1.Connectivity: Make sure each database is available and accessible both by logging in via user applications, as well as running test scripts. 2.Backups: Check database and log backups, archiving an...

Table is used to store the data in the form of rows and columns

a table is used to store the information in form of rows &columns in the database

datafile is the physical storage location of the database.tablespace is logical.for creation of tablespace one or more datafiles needed.we can define the datafile location with the following parameter

db_create_file_dest

Datafiles are physical data location of oracle architecture. Table space contains no.of datafiles.

Code
SELECT TABLESPACE_NAME, ONLINE_STATUS FROM DBA_DATA_FILES;

Code
SELECT tablespace_name,status FROM dba_tablespaces;