Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 26 April 2012

DBA Interview Questions with Answers Part3

How to use "ALTER DATABASE BEGIN BACKUP;" command in Oracle 9i.
SQL>alter tablespace <tablespace_name> begin backup;
copy all the datafile redolog file using command prompt
querying v$datafile, v$controlfile to check the file status and path
after backing up end the command.
SQL>alter tablespace <tablespace_name> end backup;
repeat this for all tablespaces

How will you rectify if one of the rollback segments gets corrupted
The only option available is to restore and recover the database followed by opening the database with resetlogs. In this case you will lose the entire prior database backup so must make fresh backup.

How many days, we are going to retain the data after taking the backup.  For example the data which backed up today that will get expire in 90 days. That means, it is 90 days retention policy for backup
You can configure retention policy command to create a persistent and automatic backup retention policy. When a backup retention policy is in effect RMAN considers backups of datafiles and control files as obsolete that is no longer needed for recovery according to criteria that you specify in the CONFIGURE command. You can then use the REPORT OBSOLETE command to view obsolete files and DELETE OBSOLETE to delete them. That means it is 90 days retention policy for backup

Difference Retention Policy of REDUNDANCY/RECOVERY WINDOW Parameters?
RETENTION POLICY: (REDUNDANCY/RECOVERY WINDOW) REDUNDANCY defines a fixed number of backup to be retained. Any backup in excess of this number can be deleted. The default value 1 says as soon as a new backup is created the old one is no longer needed and can be deleted. The other option of retention policy is RECOVERY WINDOW specified in days, to define period of time in which point in time recovery must be possible. Thus it defines how long backup should retain.

What kind of backup you take Physical / Logical? Which one is better and Why?
Logical backup means backing up the individual database objects such as tables, views , indexes using the utility called EXPORT, provided by Oracle. The objects exported in this way can be imported into either same database or into any other database. The backed-up copy of information is stored in a dumpfile, and this file can be read only using another utility called IMPORT. There is no other way you can use this file. In this backup Oracle Export utility stores data in Binary file at OS level.
Physical backups rely on the Operating System to make a copy of the physical files like data files, log files, control files that comprise the database. In this backup physically CRD (datafile, controlfile, redolog file) files are copied from one location to another (disk or tape) 
We don't preferred logical backup. It is very slow and recoveries are almost not possible.

What is Partial Backup?
A   Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down. 
A partial backup is an operating system backup of part of a database. The backup of an individual table space’s data files or the backup of a control file are examples of partial backups. Partial backups are useful only when the database is in ARCHIVELOG ...

What are the name of the available VIEW in oracle used for monitoring database is in backup mode (begin backup).
V$backup : Status column of this view shows whether a tablespace is in hotbackup mode. The status 'ACTIVE' shows the datafile to be in backup mode.
V$datafile_header : The fuzzy column also helps a dba to monitor datafile which are in backup mode.
The fuzzy ‘NO’ indicates that the datafile is in hotbackup 9begin backup) mode.
NOTE : The database doesn't startup when a datafile is in backup mode. So put datafile back in the normal mode before shutting down the database.

What is Tail log backup? Where can we use it?
Tail Log Backup is the log backup taken after data corruption (Disaster). Even though there is file corruption we can try to take log backup (Tail Log Backup). This will be used during point in time recovery.
Consider a scenario where in we have full backup of 12:00 noon one Transactional log backup at 1:00 PM. The log backup is scheduled to run for every 1 hr. If disaster happens at 1:30 PM then we can try to take tail log backup at 1:30 (after disaster). If we can take tail log backup then in recovery first restore full backup of 12:00 noon then 1:00 PM log backup recovery and then last tail backup of 1:30 (After Disaster).

How to check the size of SGA?
SQL> show SGA
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 58721636 bytes
Database Buffers 104857600 bytes
Redo Buffers 2945024 bytes

How to define data block size
The primary block size is defined by the Initialization parameter DB_BLOCK_SIZE.

How can we determine the size of the log files.
SQL>Select sum(bytes)/(1024*1024) “size_in_mb” from v$log;

What do you do when the server cannot start due to a corrupt master database?
If the master database is corrupt then surely others also do have the problems and thus the need of MDF recovery comes to an immediate. However you can try out to rebuild it with rebuild.exe and restore it.

What do you do when temp db is full?
You need to clean up the space and add more space in order to prevent this error in future.
SQL>Alter database tempfile ‘temp01.dbf’ resize 200M;
Use V$TEMP_SPACE_HEADER to check the free space in Tempfile or use the query
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
         FROM     v$sort_segment A,
         SELECT, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY, C.block_size
         ) D
WHERE    A.tablespace_name =
GROUP by A.tablespace_name, D.mb_total;
The above query will displays for each sort segment in the database the tablespace the segment resides in, the size of the tablespace, the amount of space within the sort segment that is currently in use, and the amount of space available. 

What is the frequency of log Updated..?
Whenever commit, checkpoint or redolog buffer is 1/3rd full, Time out occurs (3 sec.), 1 MB of redo log buffer

What are the Possibilities of Logical Backup (Export/Import)
- We can export from one user and import into another within the same database.
- We can export from one database and import into another database (but both source and destination databases
   should be are ORACLE databases)
- When migrating from one platform to another like from windows to sun Solaris then export is the only method
   to transfer the data.
What is stored in Oratab file
"oratab" is a file created by Oracle in the /etc or /var/opt/oracle directory when installing database software. Originally ORATAB was used for SQL*Net V1, but lately it is being used to list the databases and software versions installed on a server.
The Y|N flags indicate if the instance should automatically start at boot time (Y=yes, N=no).
Besides acting as a registry for what databases and software versions are installed on the server, ORATAB is also used for the following purposes:
·   Oracle's "dbstart" and "dbshut" scripts use this file to figure out which instances are to be   start up or shut down (using the third field, Y or N).
·   The "oraenv" utility uses ORATAB to set the correct environment variables.
·   One can also write Unix shell scripts that cycle through multiple instances using the information in the oratab file.
In your database some blocks of particular datafile are corrupted. What statement will you issue to know how many blocks are corrupted?
You can check the " Select * from V$DATABASE_BLOCK_CORRUPTION; " view to determine the corrupted blocks.

What is a flash back query? This feature is also available in 9i. What are the difference between 9i and 10g (related to flash back query).
Oracle 9i flashback 10g enhancement
Flashback query:
·               Flashback version query
·               Flashback_Transactional_query view
10g new Features:
·               Flashback Table
·               Flashback database
Setup for new feature:
·               AUM
·               Flash Recovery Area
Describe the use of %ROWTYPE and %TYPE in PL/SQL
%ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.
How can the problem be resolved if a SYSDBA, forgets his password for logging into enterprise manager?
There are two ways to do that:
1. Login as SYSTEM and change the SYS password by using ALTER USER.
2. Recreate the password file using orapwd and set remote_password_file exclusive and then restart the instance.
3. Also you can enter as ‘/ as sysdba’ and then after change the password “Alter user sys identified by xxx”;
How many maximum number of columns can be part of primary key in a table in 9i and 10g.
You can set primary key in a single table up to 16 columns of table in oracle 9i and 10g.
What is RAC?
RAC stands for Real Application Cluster. In previous versions, it is known as PARALLEL SERVER. RAC is a mechanism that allows multiple instances (on different hosts/nodes) to access the same database. The benefits: It provides more memory resources, since more hosts are being used; If one host gets down, then other host assumes it's work load.
What is Data Pumping?
Data Pumping is a data movement utility. This is a replacement to imp/exp utilities. The earlier imp/exp utilities are also data movement utilities, but they work within the local servers only. Where as, impdp/expdp (Data pumping) are very fast and perform data movements from one database to another database on same as well as different host. In other words, it provides secure transports.
What is Data Migration?
Data migration is actually the translation of data from one format to another format or from one storage device to another storage device. Data migration is necessary when a company upgrades its database or system software, either from one version to another or from one program to an entirely different program. 
What is difference between spfile and init.ora file
init.ora or spfile both are contains Database parameters information. Both are supported by oracle. Every database instance required either any one. If both are present first choice is given to spfile only. init.ora saved in the format of ASCII where as SPFILE saved in the format of binary. init.ora information is read by oracle engine at the time of database instance started only that means any modification made in this those are applicable in the next startup only. But in spfile modifications (through alter system..... command) can applicable without restarting oracle database (restarting instance).
What is SCN? Where the SCN does resides?
SCN - System Change Number - is always getting incremented by Oracle server and will be used to make sure the consistency across the database. The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database. Every time a user commits a transaction. Oracle records a new SCN. You can obtain SCNs in a number of ways for example from the alert log. You can then use the SCN as an identifier for purposes of recovery. For example you can perform an incomplete recovery of a database up to SCN 1030. Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. SCN number will be updated in almost all places of the database.
How to know which query is taking long time?
By testing with the help of these tools tkprof or using explain plan. tkprof is available to DBA Only where as explain plan can run programmer as well as DBA also. As well as tkprof generates complexilty after sucessful execution only where as explain plan can show Oracle internal plan & other details. Even though they are not alternatives for one to another. But both are designed for one purpose only. They are two different tools they are engaged in different useful situations also you can use STATSPACK to take Snaps while running those queries and get the report with details of SQL taking more time to respond otherwise, you can search Top ten sql with the following views:
SQL>SELECT * FROM V$SQL;                     
SQL>SELECT * FROM (SELECT rownum Substr(a.sql_text 1 200) sql_text Trunc(a.disk_reads/Decode(a.executions 0 1 a.executions)) reads_per_execution a.buffer_gets a.disk_reads a.executions a.sorts a.address FROM v$sqlarea a ORDER BY 3 DESC)WHERE rownum < 10;
How can you check which user has which Role.
Sql>Select * from DBA_ROLE_PRIVS order by grantee;
What are clusters
Groups of tables physically stored together because they share common columns and are often used together is called clusters.
Name (init.ora) parameters which affects system performance.
These are the Parameters for init.ora which affect system performance
How do you rename a database?
Prior to the introduction of the DBNEWID (NID) utility alteration of the internal DBID of an instance was impossible and alteration of the DBNAME required the creation of a new controlfile. The DBNEWID utility allows the DBID to be altered for the first time and makes changing the DBNAME simpler.
Steps: Change DBNAME only
  1. Mount the database after clean shutdown.
  2. Invoke the DBNEWID utility (NID) from the command line using sys user.
Assuming the validation is successful the utility prompts for confirmation before performing the actions.
Note: The SETNAME parameter tells the DBNEWID utility to only alter the database name.
  1. clean shutdown the database
Set the DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.
Note:The DBNEWID utility does not change the server parameter file (SPFILE). Therefore, if you use SPFILE to start your Oracle database, you must re-create the initialization parameter file from the server parameter file, remove the server parameter file, change the DB_NAME in the initialization parameter file, and then re-create the server parameter file. Because you have changed only the database name, and not the database ID, it is not necessary to use the RESETLOGS option when you open the database. This means that all previous backups are still usable.
  1. Create a new password file.
orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10
  1. Open the database without Reset logs option
Steps: change DBID only
Repeat the same above procedure
nid TARGET=sys/password@TSH3
Shutdown and open the database with RESETLOGS option
What is the view name where we can get the space for tables or views?
We cannot get the space of view because view does not have its own space it depend on base table.
What background process refreshes materialized views?
Job Queue processes
What view would you use to determine free space in a tablespace?
It is dba_free_space
If CPU is very slow, what can u do to speed?
Use VMSTAT to check the CPU enqueues or use also TOP and SAR commands for CPU load.
What would you use to improve performance on an insert statement that places millions of rows into that table?
Drop the indexes and recreate after insert.
DML Triggers to be DISABLED and then ENABLED once the insert completed.
DISABLE the Clustered Index and then ENABLED once the insert completed.
If Monday take full backup and Tuesday it was cumulative backup and Wednesday we taken incremental backup, Thursday some disaster happen then what type of recovery and how it will take?
Restore the Monday full backup + Tuesday cumulative backup + Wednesday Incremental backup. Becausecumulative and incremental clears the archives every backup
What is the difference between local managed Tablespace & dictionary managed Tablespace ?
The basic diff between a locally managed tablespace and a dictionary managed tablespace is that in the dictionary managed tablespace every time a extent is allocated or deallocated data dictionary is updated which increases the load on data dictionary while in case of locally managed tablespace the space information is kept inside the datafile in the form of bitmaps every time a extent is allocated or deallocated only the bitmap is updated which removes burden from data dictionary. The Tablespaces that record extent allocation/deallocation in the dictionary are called dictionary managed tablespaces and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.

While installing the Oracle 9i ( 9.2) version, automatically system takes the space of approximately 4 GB. That is fine.... Now, if my database is growing up and it is reaching the 4GB of my database space...Now, I would like to extend my Database space to 20 GB or 25 GB... what are the things i have to do?
Following steps can be performed:
1. First check for available space on the server.

2. You can increase the size of the datafiles if you have space available on the server and also you can make auto extend on. So that in future you don't need to manually increase the size.
The alternative better  idea is that make the autoextend off and add more datafiles to the Tablespace. Making a single datafile to a bigger size is risky. By making autoextend off you can monitor the growth of the tablespace schedule a growth monitoring script with a threshold of 85 full.


  1. this question is mssql related...

    What is Tail log backup? Where can we use it?
    Tail Log Backup is the log backup taken after data corruption (Disaster). Even though there is file corruption we can try to take log backup (Tail Log Backup). This will be used during point in time recovery.
    Consider a scenario where in we have full backup of 12:00 noon one Transactional log backup at 1:00 PM. The log backup is scheduled to run for every 1 hr. If disaster happens at 1:30 PM then we can try to take tail log backup at 1:30 (after disaster). If we can take tail log backup then in recovery first restore full backup of 12:00 noon then 1:00 PM log backup recovery and then last tail backup of 1:30 (After Disaster).

  2. SQL Server question

    What do you do when the server cannot start due to a corrupt master database?

  3. The EXECUTE permission was denied on the object 'sp_BackupDatabases', database '
    master', schema 'dbo'.

    facing this error while running backup script