Here we have listed out some of common interview questions asked in a oracle dba interview. We are trying our best to include QAs from different topics of oracle database.
1. What is the difference between database and instance
Oracle database is consists of physical files like datafile, redo logs, tempfile, undo file and controlfiles.
Whereas Oracle instance is consists of SGA(shared global area) and different background processes.
2. What are the different phases of database startup?
Startup nomount:In this phase, the database reads the initialization parameters (pfile/spfile).If any invalid parameter defined in the pfile , then it will throw error.
Startup mount:In this phase the database checks the consistency of control file which records all the physical structures of the datase files.
Startup open:During this phase the database tries to start in open mode i.e (read,write) for end users. Here it will check the consistency of the the datafiles and redologs . In case of any inconsistency it will try to recover the database from redologs.
2. What are the different states of a redolog member and its significance.
We can find the status of redologs from v$log.
select group#,status from v$log;
Different status of redolog are:
UNUSED– Online redo log has never been used. This is the status of a redo log that was newly added, or just after a RESETLOGS, and not being used yet.
CURRENT– Means this redo log is currently getting written. This implies that the redo log is active.
ACTIVE– Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
CLEARING– Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING CURRENT- Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE– Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
3. What do you mean by multiplexing controlfile and how to achieve this.
4. What are the different partitioning techniques in oracle db .
5. What is global and local partitioned index.
6. What are the methods of converting non-partitioned table to partitioned table and explain on this.
7. What is an ACL.
From 11g onwards, to access network packages like utl_mail,utl_http,utl_smtp, we need to have addtional privilege through ACL.
You can check the below link for usage of ACL, for sending mail using utl_mail.
8. How to make password of an user non-expiry.
Password parameters are defined in profiles. So to make an password non-expiry, First we need to create a profile with password_life_time set to UNLIMITED and assign that profile to the user.(This user will inherit all the password limits of the profile)
9. How to drop a private database link, when you are not the owner.
10. What happens when we open the database in resetlog mode.
We should reset the online logs, when we do incomplete recovery or recovery through backup control file. When we do open resetlog, a new incarnation number is generated for the database. Incarnation details can be viewed in v$database_incarnation.
11. What is SCN in oracle?
SCN Means, system change number. SCN is the logical point in time at which changes are made to a database. Oracle assigns every committed transaction a unique SCN. The database uses these SCNs to query and track the changes. For example, if a transaction inserts a row, then the database records the SCN at which this delete occurred.
12. What is buffer cache?
Oracle keeps copies of database blocks in an area of the SGA known as the buffer cache. The cache may hold more than one copy of a block from different points in time, and may contain ‘dirty’ blocks – ie: blocks which have been updated but not yet flushed back to disk.
13. What is buffer cache hit ratio?
The buffer cache hit ratio measures how many times a required block was found in memory rather than having to execute a expensive read operation on disk to get the block.
A good buffer cache hit ratio is generally considered when it is > 80.
14. What is the TEMPORARY tablespace and its use?
As the name suggests, Temporary tablespaces is used for tasks such as sort operations for users and sorting during index creation. User cannot create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session and the temp space is released, once the session is closed.
15. Difference between pfile vs spfile.
The pfile is read at instance startup time to get specific instance characteristics. Any changes made the pfile would only take effect when the database is restarted.
The spfile is a server-side initialization parameter file and it permits dynamic changes without requiring you to bring down the instance.
16. How to check whether database is using pfile or spfile.
1) SQL> SELECT name,value FROM v$parameter WHERE name = ‘spfile’;
NAME VALUE
———- ————————————————–
spfile /fsys1/oracle/product/9.2.0/spfileTEST.ora
2) SQL> show parameter spfile;
17. What is the default block size in database? Can we use different block sizes and how?
Starting from oracle 10g, the default block size(DB_BLOCK_SIZE) is 8Kb. In previous versions it was 2Kb.
To use different block size, use the parameter DB_nK_CACHE_SIZE .
For using 2K block size, set db_2k_cache_size=2G in the init pfile and bounce the database.
Similarly for using block size of diffent type, below can be used.
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
18. What happens when we open the database with resetlog?
We need to open the database using resetlog option after an incomplete database recovery.
SYNTAX- ALTER DATABASE OPEN RESETLOGS;
This command will do below things in the database.
- It recreates the online redologs and reset the log sequence to 1.
- Updates all the datafile and online redolog files with new resetlog timestamp and scn.
- New incarnation number is generated.
Note – It is always recommended to take a fresh full DB backup after opening the database with resetlog.
19. What is checkpoint and when it happens?
When we do any changes to the database and commit , then modified blocks are not written to datafile directly,Instead they are written to redolog.And checkpoint is the action, when these modified(committed) blockes will be flushed to the datafile. These blocks are also known as dirty blocks.
A checkpoint number is the SCN number, at which all the dirty blocks are written to datafile(disk).
Below events make a checkpoint to occur:
1. When a redo log switch happens.
2. When DBWR writes all dirty buffers from SGA to datafiles.
3. When we execute alter system switch logfile (or) alter system checkpoint;
20. What is SCN in oracle?
SCN is system change number. Oracle assigns unique number is each committed transaction in the database. SCN value gets incremented with each transaction.SCN information is updated in controlfile.
Below queries to find SCN:
select dbms_flashback.get_system_change_number scn from dual;
select current_scn from v$database;
21. How to rebuild an INDEX?
There are two ways to rebuild an index.
OFFLINE:(During this the exclusive lock will be applied on the table, which will impact DDL and DMLS.)
ALTER INDEX SCHEMA_NAME.INDEX_NAME REBUILD;
Alternatively we can drop and recreate the index .
ONLINE:(DML and DDLS operations will work as usual, without any impact).
ALTER INDEX SCHEMA_NAME.INDEX_NAME REBUILD ONLINE;
22. What is a relation between GLOBAL_NAME parameter and DB_LINK?
GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects. GLOBAL_NAMES is either set to FALSE OR TRUE.
If the value of GLOBAL_NAMES is FALSE, then any name can be given to DB_LINK. If the value is TRUE, then database link name should be same as that of the database it is pointing.
23. What is SYSTEM tablespace and what it contains?
SYSTEM tablespace is the first tablespace which is created while creating a fresh database.
It stores the data dictionary i.e the metadata ( Means data about different objects in the database).
This is a mandatory tablespace cannot be dropped.
24. What is SYSAUX tablespace and what it contains?
As the name SYSAUX says, it is an auxiliary tablespace to SYSTEM tablespace. It contains non-sys default data about different components of the database like OLAP, WORKSPACE MANAGER, TEXT SEARCH, DATA MINING, RECOVERY CATALOG, OEM, SPATIAL ETC.
This tablespace was introduced in Oracle 10g to reduce to workload from SYSTEM tablespace. Like SYSTEM tablespace also gets created by creating the database and it cannot be dropped.
25. What is SYSAUX tablespace and what it contains?
26. What do you know by SGA and its purpose?
27. What do you mean by PGA and its purpose?
28. What is the job of DBWR process and how many DBWR processes are there?
DBWR Means, database writer is mainly responsible for writing modified blocks(ie. dirty blocks) to the physical data files(disks).
For below events, DBWR writes to datafiles.
- Checkpoint is issued
- Too much dirty buffers in Buffer cache
- No free space in the buffer cache
- During database shutdown(Except abort method)
- Tablespace being dropped (or)taken offline(or) placed in read only mode, (or) in hot backup mode.
The parameter DB_WRITER_PROCESS controls the number of DBW processes you want to use. It can be from 1 to 20. Using more number of DBWR process will increase the write performance. However, it will increase the CPU usage on the DB server.
29. What is the job of LGWR process?
LGWR, i.e log writer writes the contents of the redolog buffer to an online redo log file.
LGWR writes when any of the below event occurs.
- When a commit is issued
- Every 3 seconds
- When 1/3rd of redo buffer is full
- Before DBWr writes to disk.
30. What is the function of SMON process?
SMON (System Monitor) background performs below tasks like
- It performs instance recovery(including crash recovery during instance startup)
- It cleans up unused temporary segments for proper space utilization
31. What is the function of PMON process?
PMON (Process Monitor) performs below tasks like
- cleanup dead processes and sessions
- terminate sessions which exceed idle timeout limit
- managing job queue processes
32. What is the function of MMON process?
MMON i.e Manageability Monitor ,background process which performs tasks like taking AWR snapshots and performing ADDM analysis.
33. How instance recovery works in oracle database?
When an instance terminated as abnormally or crashed the database goes down in an inconsistent state that
means all ongoing transactions committed or uncommitted were not completed.So before it can be opened, the database must be in a consistent mode.
Hence SMON performs critical role in recovering the database. Oracle using last SCN in the control file and will apply committed or uncommitted transaction from Redo logs, which is known as roll forward. In this state database is in MOUNT state . Database then checks the accessibility of UNDO segments and opens the database. Now uncommitted transactions are rolled back with the help of UNDO ,which is called ROLL BACK.
helpful information thanks for uploading
I follow your each and every document.
Really best you are providing.
Thank you