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.***

Monday, 24 June 2013

DBA Interview Questions with Answer Part17

What are the common Tasks or Responsibilities for a Core DBA?
DBA responsibilities are varied from organization to organization. It depends on the organization nature of work. Following are the overall responsibility for a DBA:
  1. User Management: Create new user, remove existing user and provide the rights as per the requirement.
  2. Manage database storage (Timely space management of Tablespace or datafile)
  3. Administrator users and security.
  4. Manage Schema object.
  5. Monitor and Manage database performance.
  6. Perform backup and recovery.
  7. Schedule and automate jobs.
  8. Taking database snapshot or health report.
  9. Working with user issues for managing overall smooth running of database.
What are your day to day activities as an APPS DBA?
In compare to Core DBA Apps DBA include all the responsibilities of Core DBA Plus Upgrade, Cloning and Patching. As an Apps DBA we monitor the system for different alerts (EM or third party tools used for configuring the Alerts), Tablespace issues, CPU consumption, Database blocking session etc. Regular maintenance activities like cloning, patching and custom code migration (provided by developer), working with user issues.
What type of failure occurs when oracle fails due to OS or Hardware failure?
Instance Failure
An Oracle system change number (SCN):
  1. is a value that is incremented whenever a dirty read occurs.
  2. is incremented whenever a deadlock occurs.
  3. is a value that keeps track of explicit locks
  4. is a value that is incremented whenever database changes are made?
Answer: D
Which process read/write data from datafiles?
There is no background process which reads data from datafiles or database buffer. Oracle creates server process to handle request from connected user processes. A server process communicates with the user process and interacts with oracle to carry out request from the associated user process.
For example: If a user queries some data not already in database buffer of the SGA, then the associated server process reads the proper data block from the datafiles into the SGA.
DBWR background process is responsible to writes modified (dirty block from buffer cache to the datafiles) block permanently to disk.
Why RMAN incremental backup fails even though full backup exists?
If you have taken the RMAN full backup using the command ‘Backup database’, where as a level 0 backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository so it can be used as the parent for a level 1 backup. Simply the ‘full backup without level 0’ can not be considered as a parent backup from which you can take level 1 backup.
How can you change or rename the database name?
The above command will create a text control file in user_dump_dest directory and change name of the database in above file and also in init.ora file.
Now startup your database in nomount phase using the modified pfile and then run the modified controlfile script.
SQL> @D:\Backup\controlfile.txt
You can use DBNEWID utility NID for this purpose. For more information: DBNEWID, Changing DBNAME
Temp Tablespace is 100% FULL and there is no space available to add datafiles to increase temp tablespace. What can you do in that case to free up TEMP tablespace?
Try to close some of the idle sessions connected to the database will help you to free some TEMP space. Otherwise you can also use ‘Alter Tablespace PCTINCREASE 1’ followed by ‘Alter Tablespace PCTINCREASE 0’
What is the use of setting GLOBAL_NAMES equal to true?
Setting GLOBAL_NAMES indicates how you might connect to the database. This variable is either ‘true’ or ‘false’. If it is set to ‘true’ enforces database link to have same link as the remote database to which they are linking.
What is the purpose of fact and dimension table? What type of index is used with fact table?
Fact and dimension tables are involved in producing a star schema. A fact table contains measurements while dimension table will contain data that will help to describe the fact table. A Bitmap index is used with fact table.
If you got complain application is running very slow from your application user. Where do you start looking first?
Below are some of very important step to identify the root cause of slowness in Application database.
·        Run TOP command in Linux to check CPU usage.
·        Run VMSTAT, SAR, PRSTAT command to get more information on CPU, memory usage and possible blocking.
·        Run STATSPACK report to identify TOP 5 Events and Resource Intensive SQL statement.
        If found poor written statements then run ‘EXPLAIN PLAN’ on these statements and see whether new index or use of HINT brings the cost of SQL down.
How do you add second or subsequent BLOCK SIZE to an existing database?
In fact the block size in an oracle database cannot be changed after the database is created. The reason is because oracle track lot of information based on block number. If you change the block size all the block number is changed and basically the database would have to re-create. But in the case when you need to add second or subsequent BLOCK_SIZE for particular datafile then you have to re-create the CONTROLFILE to specify the new block size for specific datafiles.
or Take the database OFFLINE, and then bring back online with a new BLOCK SIZE specification.
You need to restore from backup and do not have any control files. What will be your step to recover the database?
Create a text based control files, saved on the disk same location where all the datafiles are located then issue the recover command by using backup control file clause.
Shutdown abort;    -- if db still open
Startup nomount;
create controlfile
database <name>
logfile '<online redo log groups>'
maxlogfiles 10
maxlogmembers <your value>
datafile '<names of all data files>'
maxdatafiles 254
SQL> alter database mount;
recover database [until cancel] [using backup controlfile];
alter database open [noresetlogs/resetlogs];
– Use alter database open if you created the control file with NORESETLOGS and have performed no recovery or a full recovery (without until cancel).
– Use alter database open noresetlogs if you created the control file with NORESETLOGS and performed a full recovery despite the use of the until cancel option.
– Use alter database open resetlogs if you created the control file with RESETLOGS or when you performed a partial recovery.
In below list which SQL phrase is NOT supported by oracle?
  3. CREATE SEQUENCE [SequenceName]
  4. DROP SEQUENCE [SequenceName]
Answer: B
What is the effect on working with Report when flex/confine mode are ON?
When flex mode is ON, reports automatically resize the parent when the child is resized.
When the confine mode is ON, the object cannot be moved outside its parent in layout.
How will you enforce security using stored procedure?
Don’t grant user access directly to tables within the application. Instead grant the ability to access the procedure that accesses the tables. When procedure execute it will execute the privilege of procedures owner. Users cannot access except via the procedure.
What is RAC? What is the benefit of RAC over single instance database?
In Real Application Clusters environments, all nodes concurrently execute transactions against the same database. Real Application Clusters coordinates each node's access to the shared data to provide consistency and integrity.
        Improve response time
        Improve throughput
        High availability
Can you configure primary server and standby server on different OS?
NO, Standby database must be on same version of database and same version of OS.
If you want users will change their passwords after every 60 days then how you will enforce this?
Oracle password security is implemented through oracle PROFILES which are assigned to users. PASSWORD_LIFE_TIME parameter limits the number of days the same password can be used for authentication.
You have to first create database PROFILE and then assign each user to this profile or if you have already having PROFILE then you need to just alter the above parameter.
create profile Sadhan_users
Then create user or already created user assigned to this profile.
SQL> Create user HRMS identified by oracle profile sadhan_users;
If you have already assigned profile then you can directly modify the profile parameter:
SQL> Alter profile sadhan_users set PASSWORD_LIFE_TIME = 90;
What happens actually in case of instance Recovery?
While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re-started. Instance recovery occurs in two steps:
Cache recovery: Changes being made to a database are recorded in the database buffer cache as well as redo log files simultaneously. When there are enough data in the database buffer cache, they are written to data files. If an Oracle instance fails before these data are written to data files, Oracle uses online redo log files to recover the lost data when the associated database is re-started. This process is called cache recovery.
Transaction recovery: When a transaction modifies data in a database (the before image of the modified data is stored in an undo segment which is used to restore the original values in case the transaction is rolled back). At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files. To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery.
What is the main purpose of ‘CHECKPOINT’ in oracle database?
A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery. For more information: Discussion on Checkpoint and SCN
Can you change the Characterset of database?
No, you can not change the character set of database, you will need to re-create the database with appropriate characterset.
What is Cascading standby database?
A CASCADING STANDBY is a standby database that receives its REDO information from another standby database (not from primary database).
What the use of ANALYZE command?
To collect statistics about object used by the optimizer and store them in the data dictionary, delete statistics about the object, validate the structure of the object and identify migrated and chained rows of the table or cluster.
How will you check active shared memory segment?
ipcs -a
How will you check paging swapping in Linux?
vmstat s
prstat s
swap l
sar p
How do you check number of CPU installed on Linux server?
psrinfot v
When you moved oracle binary files from one ORACLE_HOME server to another server then which oracle utility will be used to make this new ORACLE_HOME usable?
Relink all
In which months oracle release CPU patches?
Oracle version what does each number refers to?
Oracle version number refers:
 9 – Major database release number
 2 – Database Maintenance release number
 0 – Application server release number
 4 – Component Specific release number
 0 – Platform specific release number
What does database do during the mounting process?
While mounting the database oracle reads the data from controlfile which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only.
When having multiple oracle homes on a single server or client what is the parameter that points all Oracle installs at one TNSNAMES.ORA file.
How to implement the multiple controlfile for existing database?
1.      Edit init.ora file, set controlfiles parameter with multiple location
2.      Shutdown immediate
3.      Copy controlfile to multiple locations & confirm from init.ora contolfiles parameter
4.      Start the database.
5.      Use the below query for changes confirmation
select name from v$controlfile;


  1. nice work

    For more ORACLE Database Interview Questions and Answers visit:DBA interview questions

  2. Very good collection of question and answers thank you for sharing this article with us. Know more about Oracle DBA Training

  3. Did you know that you can earn dollars by locking special areas of your blog / site?
    To begin just join AdWorkMedia and embed their Content Locking tool.