DBA Interview Questions With Answers
What are four common errors found in an alert .log?
If we are getting any issue regarding database while performing any activity we should check alert log file in dump destination.. The four common error we find in alert.log are:
Deadlock Errors (ORA-00060), Oracle Internal errors, Backup and recovery errors, Snapshot too old error (O1555)
What is PCT Free/PCT Used/PCT increase parameter in segment? What is growth factor?
PCT-FREE is a block storage it uses to mention how much space should be left in database block for future updates (updating the records eg. previously name Smith after that we will update the name as Smith Taylor). If mention PCTFREE as 10, oracle will adding the new rows to block up to 90% it allows 10% for future updates.
If the PCT used was set to 60 this means if the data inside the block is 60 it is FULL and if the data inside the block is 59 it is Empty.
This is the parameter which specify in percent that a block can only used for insert or come in the free list(list of blocks in segment ready for insert operation) when used space in a block is less than PCTUSED.
Suppose value of pctused is 40 and pctfree is 20 then data can be inserted till 80 of the block directly. And suppose the used space is 60 and some one has perform a delete operation in a row in the same block which brings the used space to 50 .Now one cannot insert any record in the same block unless the used space comes down below 40 i.e. pctused.
What is dump destination? What are bdump, cdump and udump?
The dump destination is the location where the trace files are located for all the Oracle process.
bdump-->Background processes + alert_SID.log file location
cdump--> Core Processes dump, udump--> User Processes dump, adump--> for ASM processes
These destinations contains useful information related to process failures.
UDUMP is specifying the user dump directory where all user error logs (trace files) will be placed.
BDUMP is specifying the background dump directory where all database error logs (trace files) will be placed.
CDUMP is specifying the core dump directory where all OS error logs (core dump files) will be placed.
Default location is (ORACLE_BASE/admin/<SID>)
SQL>show parameters dump_dest;
It'll show you all the dump directories wherever it is currently located. You can change your parameters in init.ora by creating spfile from pfile.
What will you do if in any condition you do not know how to troubleshoot the error at all and there are no seniors or your co-workers around?
We need to find where in the compilation the error is occurring. We have to divide the code and check for correctness of the code part-by-part. This is called debugging. Keep checking the code until you find the code which is wrong.
Search forums for similar error codes or symptoms and make a plan then submit it to your supervising DBA if you are not authorized to carry it out yourself.
I am getting error "No Communication channel" after changing the domain name? What is the solution?
Here Question is not clear about Where the Oracle database is residing. If the Oracle Database is resides on your local machine then the domain name must be updated in the tnsnames.ora file. Change this file in ../Admin folder contained one. If you are accessing remote Database then there are no changes required to your tnsnames.ora file only check with tnsping with the database service name. Change the domain name in the sqlnet.ora file in NAMES.DEFAULT_DOMAIN parameter
You have taken import of a table in a database. You have got the Integrity constraint violation error. How you are going to resolve it.
If u wants to import the table just says constraints=n the movement table got imported then u create constraint on that tables.
What is the most important action a DBA must perform after changing the database from NOARCHIVELOG TO ARCHIVELOG?
First of all take an offline backup of whole database (including the (datafile controlfile and redolog files). It is obvious that archive log process should be started by:
SQL>alter system Archivelog start;
Otherwise the database halts if unable to rotate redo logs
Show one instance when you encountered an error in alert log and you overcome that error. What actions you took to overcome that error.
Oracle writes error in alert log file. Depending upon the error corrective action needs to be taken.
1) Deadlock Error: Take the trace file in user dump destination and analysis it for the error.
2) ORA-01555 Snapshot error: Check the query try to fine tune and check the undo size.
3) Unable to extent segment: Check the tablespace size and if require add space in the tablespace by 'alter database datafile .... resize' or alter tablespace add datafile command.
What is Ora-1555 Snapshot too Old error? Explain in detail?
Oracle Rollback Segments (Undo more recently) hold a copy of data before it was modified and they work in a round-robin fashion. Writing and then eventually overwriting the entries as soon as the changes are committed.
They are needed to provide read consistency (a consistent set of data at a point in time) or to allow a process to abandon or rollback the changes or for database recovery.
Here’s a typical scenario:-
User A opens a query to fetch every row from a billion row table. If User B updates and commits the last row of the billion row table a Rollback entry will be created so User A can see the data as it was before the update.
Other users are busily updating rows in the database and this in turn generates rollback – which may eventually cause the entry needed for User A to be overwritten (after all User B did commit the change – so it’s OK to overwrite the rollback segment). Maybe 15 minutes later the query is still running and User A finally fetches the last row of the billion row table – but the rollback entry is gone. He gets ORA-01555: Snapshot too old rollback segment too small
I have applied the following commands: Now what will happen, will the database will give an error / it will work?
Shutdown abort;
Startup;
Definitely database will be start without error but all uncommitted data will be lost such as killed all sessions, killed all transactions, and didn't write from the buffers because shutdown abort directly shutdown instance without committing.
There is four modes to shutdown the database:
1) Shutdown immediate, 2) Shutdown normal, 3) Shutdown transactional, 4) Shutdown aborts
When the database is shutdown by first 3 methods checkpoint takes place but when is shutdown by abort option it doesn't enforces checkpoints, it simply shutdowns without waiting any users to disconnect.
What is mutated trigger? In single user mode we got mutated error, as a DBA how you will resolve it?
Mutated error will occur when same table access more than once in one state. If you are using before in trigger block then replace it with after.
Explain Dual table. Is any data internally stored in dual Table. Lot of users is accessing select sysdate from dual and they getting some millisecond differences. If we execute SELECT SYSDATE FROM EMP; what error will we get. Why?
Dual is a system owned table created during database creation. Dual table consist of a single column and a single row with value x. We will not get any error if we execute select sysdate from scott.emp instead sysdate will be treated as a pseudo column and displays the value for all the rows retrieved. For Example if there is 12 rows in emp table it will give result of date in 12 rows.
As an Oracle DBA what are the entire UNIX file you should be familiar with?
To check the process use: ps -ef |grep pmon or ps -ef
To check the alert log file: Tail -f alert.log
To check the cpu usage; Top vmstat 2 5
What is a Database instance?
A database instance also known as server is a set of memory structures and background processes that access a set of database files. It is possible for a single database to be accessed by multiple instances (this is oracle parallel server option).
What are the Requirements of simple Database?
A simple database consists of:
One or more data files, One or more control files, Two or more redo log files, Multiple users/schemas, One or more rollback segments, One or more Tablespaces, Data dictionary tables, User objects (table, indexes, views etc.)
The server (Instance) that access the database consists of:
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool), SMON (System Monitor),PMON (Process Monitor), LGWR (Log Write), DBWR (Data Base Write), ARCH (ARCHiver), CKPT (Check Point), RECO, Dispatcher, User Process with associated PGS
Which process writes data from data files to database buffer cache?
The Background process DBWR rights data from datafile to DB cache.
How to DROP an Oracle Database?
You can do it at the OS level by deleting all the files of the database. The files to be deleted can be found using:
1) select * from dba_data_files; 2) select * from v$logfile; 3) select * from v$controlfile; 4) archive log list
5) initSID.ora 6) clean the UDUMP, BDUMP, scripts etc, 7) Cleanup the listener.ora and the tnsnames.ora. Make sure that the oratab entry is also removed.
Otherwise, go to DBCA and click on delete database.
In Oracle 10g there is a new command to drop an entire database.
Startup restrict mount;
drop database <instance_name>;
In fact DBA should never drop a database via OS level commands rather use GUI utility DBCA to drop the database
How can be determining the size of the log files.
Select sum(bytes)/1024/1024 "size_in_MB" from v$log;
What is difference between Logical Standby Database and Physical Standby database?
A physical or logical standby database is a database replica created from a backup of a primary database. A physical standby database is physically identical to the primary database on a block-for-block basis. It's maintained in managed recovery mode to remain current and can be set to read only; archive logs are copied and applied.
A logical standby database is logically identical to the primary database. It is updated using SQL statements
How do you find whether the instance was started with pfile or spfile
1) SELECT name, value FROM v$parameter WHERE name = 'spfile';
This query will return NULL if you are using PFILE
2) SHOW PARAMETER spfile
This query will returns NULL in the value column if you are using pfile and not spfile
3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;
If the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
What is full backup?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute oracle database and the parameter.If you are using the Rman for backup then in Rman full backup means Incremental backup on 0 level.
While taking hot backup (begin end backup) what will happens back end?
When we r taking hot backup (begin backup - end backup) the datafile header associated with the datafiles in the corresponding Tablespace is frozen. So Oracle will stop updating the datafile header but will continue to write data into datafiles. In hot backup oracle will generate more redos this is because oracle will write out complete changed blocks to the redo log files.
Which is the best option used to move database from one server to another serve on same network and Why?
Import – Export, Backup-Restore, Detach-Attach
Import-Export is the best option used to move database from one server to another serve on same network. It reduces the network traffic.Import/Export works well if you’re dealing with very small databases. If we have few million rows its takes minutes to copy when compared to seconds using backup and restore.
What is Different Type of RMAN Backup?
Full backup: During a Full backup (Level 0) all of the block ever used in datafile are backed up. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.
Comulative Backup: During a cumulative (Level 0) the entire block used since last full backup are backed up.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; # blocks changed since level 0
Differential Backup: During incremental backup only those blocks that have changed since last cumulative (Level 1) or full backup (Level 0) are backed up. Incremental backup are differential by default.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE
Give one method for transferring a table from one schema to another:
There are several possible methods: Export-Import, CREATE TABLE... AS SELECT or COPY.
What is the purpose of the IMPORT option IGNORE? What is its default setting?
The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the table’s data will be inserted. The default value is N.
What happens when the DEFAULT and TEMP tablespace clauses are left out from CREATE USER statements?
The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement (only data dictionary objects and the system rollback segment should be in SYSTEM).
What happens if the constraint name is left out of a constraint clause?
The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder.
What happens if a Tablespace clause is left off of a primary key constraint clause?
This result in the index that is automatically generated being placed in then USERS default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.
What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause?
The index is created in the user’s default tablespace and all sizing information is lost. Oracle doesn’t store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.
Using hot backup without being in archive log mode, can you recover in the event of a failure? Why or why not?
You can't recover the data because in archive log mode it take the backup of redo log files if it in Active mode, If it in inactive mode then it is not possible to take the backup of redolog files once the size is full, so in that case it is impossible to take hot backup
What causes the "snapshot too old" error? How can this be prevented or mitigated?
This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space. This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback segments and their extents.
How can you tell if a database object is invalid?
If we are getting any issue regarding database while performing any activity we should check alert log file in dump destination.. The four common error we find in alert.log are:
Deadlock Errors (ORA-00060), Oracle Internal errors, Backup and recovery errors, Snapshot too old error (O1555)
What is PCT Free/PCT Used/PCT increase parameter in segment? What is growth factor?
PCT-FREE is a block storage it uses to mention how much space should be left in database block for future updates (updating the records eg. previously name Smith after that we will update the name as Smith Taylor). If mention PCTFREE as 10, oracle will adding the new rows to block up to 90% it allows 10% for future updates.
If the PCT used was set to 60 this means if the data inside the block is 60 it is FULL and if the data inside the block is 59 it is Empty.
This is the parameter which specify in percent that a block can only used for insert or come in the free list(list of blocks in segment ready for insert operation) when used space in a block is less than PCTUSED.
Suppose value of pctused is 40 and pctfree is 20 then data can be inserted till 80 of the block directly. And suppose the used space is 60 and some one has perform a delete operation in a row in the same block which brings the used space to 50 .Now one cannot insert any record in the same block unless the used space comes down below 40 i.e. pctused.
What is dump destination? What are bdump, cdump and udump?
The dump destination is the location where the trace files are located for all the Oracle process.
bdump-->Background processes + alert_SID.log file location
cdump--> Core Processes dump, udump--> User Processes dump, adump--> for ASM processes
These destinations contains useful information related to process failures.
UDUMP is specifying the user dump directory where all user error logs (trace files) will be placed.
BDUMP is specifying the background dump directory where all database error logs (trace files) will be placed.
CDUMP is specifying the core dump directory where all OS error logs (core dump files) will be placed.
Default location is (ORACLE_BASE/admin/<SID>)
SQL>show parameters dump_dest;
It'll show you all the dump directories wherever it is currently located. You can change your parameters in init.ora by creating spfile from pfile.
What will you do if in any condition you do not know how to troubleshoot the error at all and there are no seniors or your co-workers around?
We need to find where in the compilation the error is occurring. We have to divide the code and check for correctness of the code part-by-part. This is called debugging. Keep checking the code until you find the code which is wrong.
Search forums for similar error codes or symptoms and make a plan then submit it to your supervising DBA if you are not authorized to carry it out yourself.
I am getting error "No Communication channel" after changing the domain name? What is the solution?
Here Question is not clear about Where the Oracle database is residing. If the Oracle Database is resides on your local machine then the domain name must be updated in the tnsnames.ora file. Change this file in ../Admin folder contained one. If you are accessing remote Database then there are no changes required to your tnsnames.ora file only check with tnsping with the database service name. Change the domain name in the sqlnet.ora file in NAMES.DEFAULT_DOMAIN parameter
You have taken import of a table in a database. You have got the Integrity constraint violation error. How you are going to resolve it.
If u wants to import the table just says constraints=n the movement table got imported then u create constraint on that tables.
What is the most important action a DBA must perform after changing the database from NOARCHIVELOG TO ARCHIVELOG?
First of all take an offline backup of whole database (including the (datafile controlfile and redolog files). It is obvious that archive log process should be started by:
SQL>alter system Archivelog start;
Otherwise the database halts if unable to rotate redo logs
Show one instance when you encountered an error in alert log and you overcome that error. What actions you took to overcome that error.
Oracle writes error in alert log file. Depending upon the error corrective action needs to be taken.
1) Deadlock Error: Take the trace file in user dump destination and analysis it for the error.
2) ORA-01555 Snapshot error: Check the query try to fine tune and check the undo size.
3) Unable to extent segment: Check the tablespace size and if require add space in the tablespace by 'alter database datafile .... resize' or alter tablespace add datafile command.
What is Ora-1555 Snapshot too Old error? Explain in detail?
Oracle Rollback Segments (Undo more recently) hold a copy of data before it was modified and they work in a round-robin fashion. Writing and then eventually overwriting the entries as soon as the changes are committed.
They are needed to provide read consistency (a consistent set of data at a point in time) or to allow a process to abandon or rollback the changes or for database recovery.
Here’s a typical scenario:-
User A opens a query to fetch every row from a billion row table. If User B updates and commits the last row of the billion row table a Rollback entry will be created so User A can see the data as it was before the update.
Other users are busily updating rows in the database and this in turn generates rollback – which may eventually cause the entry needed for User A to be overwritten (after all User B did commit the change – so it’s OK to overwrite the rollback segment). Maybe 15 minutes later the query is still running and User A finally fetches the last row of the billion row table – but the rollback entry is gone. He gets ORA-01555: Snapshot too old rollback segment too small
I have applied the following commands: Now what will happen, will the database will give an error / it will work?
Shutdown abort;
Startup;
Definitely database will be start without error but all uncommitted data will be lost such as killed all sessions, killed all transactions, and didn't write from the buffers because shutdown abort directly shutdown instance without committing.
There is four modes to shutdown the database:
1) Shutdown immediate, 2) Shutdown normal, 3) Shutdown transactional, 4) Shutdown aborts
When the database is shutdown by first 3 methods checkpoint takes place but when is shutdown by abort option it doesn't enforces checkpoints, it simply shutdowns without waiting any users to disconnect.
What is mutated trigger? In single user mode we got mutated error, as a DBA how you will resolve it?
Mutated error will occur when same table access more than once in one state. If you are using before in trigger block then replace it with after.
Explain Dual table. Is any data internally stored in dual Table. Lot of users is accessing select sysdate from dual and they getting some millisecond differences. If we execute SELECT SYSDATE FROM EMP; what error will we get. Why?
Dual is a system owned table created during database creation. Dual table consist of a single column and a single row with value x. We will not get any error if we execute select sysdate from scott.emp instead sysdate will be treated as a pseudo column and displays the value for all the rows retrieved. For Example if there is 12 rows in emp table it will give result of date in 12 rows.
As an Oracle DBA what are the entire UNIX file you should be familiar with?
To check the process use: ps -ef |grep pmon or ps -ef
To check the alert log file: Tail -f alert.log
To check the cpu usage; Top vmstat 2 5
What is a Database instance?
A database instance also known as server is a set of memory structures and background processes that access a set of database files. It is possible for a single database to be accessed by multiple instances (this is oracle parallel server option).
What are the Requirements of simple Database?
A simple database consists of:
One or more data files, One or more control files, Two or more redo log files, Multiple users/schemas, One or more rollback segments, One or more Tablespaces, Data dictionary tables, User objects (table, indexes, views etc.)
The server (Instance) that access the database consists of:
SGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool), SMON (System Monitor),PMON (Process Monitor), LGWR (Log Write), DBWR (Data Base Write), ARCH (ARCHiver), CKPT (Check Point), RECO, Dispatcher, User Process with associated PGS
Which process writes data from data files to database buffer cache?
The Background process DBWR rights data from datafile to DB cache.
How to DROP an Oracle Database?
You can do it at the OS level by deleting all the files of the database. The files to be deleted can be found using:
1) select * from dba_data_files; 2) select * from v$logfile; 3) select * from v$controlfile; 4) archive log list
5) initSID.ora 6) clean the UDUMP, BDUMP, scripts etc, 7) Cleanup the listener.ora and the tnsnames.ora. Make sure that the oratab entry is also removed.
Otherwise, go to DBCA and click on delete database.
In Oracle 10g there is a new command to drop an entire database.
Startup restrict mount;
drop database <instance_name>;
In fact DBA should never drop a database via OS level commands rather use GUI utility DBCA to drop the database
How can be determining the size of the log files.
Select sum(bytes)/1024/1024 "size_in_MB" from v$log;
What is difference between Logical Standby Database and Physical Standby database?
A physical or logical standby database is a database replica created from a backup of a primary database. A physical standby database is physically identical to the primary database on a block-for-block basis. It's maintained in managed recovery mode to remain current and can be set to read only; archive logs are copied and applied.
A logical standby database is logically identical to the primary database. It is updated using SQL statements
How do you find whether the instance was started with pfile or spfile
1) SELECT name, value FROM v$parameter WHERE name = 'spfile';
This query will return NULL if you are using PFILE
2) SHOW PARAMETER spfile
This query will returns NULL in the value column if you are using pfile and not spfile
3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;
If the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
What is full backup?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute oracle database and the parameter.If you are using the Rman for backup then in Rman full backup means Incremental backup on 0 level.
While taking hot backup (begin end backup) what will happens back end?
When we r taking hot backup (begin backup - end backup) the datafile header associated with the datafiles in the corresponding Tablespace is frozen. So Oracle will stop updating the datafile header but will continue to write data into datafiles. In hot backup oracle will generate more redos this is because oracle will write out complete changed blocks to the redo log files.
Which is the best option used to move database from one server to another serve on same network and Why?
Import – Export, Backup-Restore, Detach-Attach
Import-Export is the best option used to move database from one server to another serve on same network. It reduces the network traffic.Import/Export works well if you’re dealing with very small databases. If we have few million rows its takes minutes to copy when compared to seconds using backup and restore.
What is Different Type of RMAN Backup?
Full backup: During a Full backup (Level 0) all of the block ever used in datafile are backed up. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.
Comulative Backup: During a cumulative (Level 0) the entire block used since last full backup are backed up.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; # blocks changed since level 0
Differential Backup: During incremental backup only those blocks that have changed since last cumulative (Level 1) or full backup (Level 0) are backed up. Incremental backup are differential by default.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE
Give one method for transferring a table from one schema to another:
There are several possible methods: Export-Import, CREATE TABLE... AS SELECT or COPY.
What is the purpose of the IMPORT option IGNORE? What is its default setting?
The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the table’s data will be inserted. The default value is N.
What happens when the DEFAULT and TEMP tablespace clauses are left out from CREATE USER statements?
The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement (only data dictionary objects and the system rollback segment should be in SYSTEM).
What happens if the constraint name is left out of a constraint clause?
The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder.
What happens if a Tablespace clause is left off of a primary key constraint clause?
This result in the index that is automatically generated being placed in then USERS default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.
What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause?
The index is created in the user’s default tablespace and all sizing information is lost. Oracle doesn’t store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.
Using hot backup without being in archive log mode, can you recover in the event of a failure? Why or why not?
You can't recover the data because in archive log mode it take the backup of redo log files if it in Active mode, If it in inactive mode then it is not possible to take the backup of redolog files once the size is full, so in that case it is impossible to take hot backup
What causes the "snapshot too old" error? How can this be prevented or mitigated?
This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space. This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback segments and their extents.
How can you tell if a database object is invalid?
