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

Sunday, 26 May 2013

DBA Interview Questions with Answer Part 16

What is Oracle database firewall?
The database firewall has the ability to analyze SQL statements sent from database clients and determine whether to pass, block, log, alert or substitute SQL statements, based on a defined policy. User can set whitelist and blacklist policy to control the firewall. It can detect the injected SQL’s and block them. The database firewall can do the following:
·        Monitor and block SQL traffic on the network with whitelist, blacklist and exception list policies.
·        Protect against application bypass, SQL injection and similar threats.
·        Report on database activity.
·        Supports other database as well MS-SQL Server, IBM DB2 and Sybase.
However there are some key issues that it does not address. For Example privilege user can login to the OS directly and make local connections to the database. This bypasses the database firewall. For these issues, would need use of other security options such as Audit Vault, VPD etc.
What is Oracle RAC One Node?
Oracle RAC one Node is a single instance running on one node of the cluster while the 2nd node is in cold standby mode. If the instance fails for some reason then RAC one node detect it and restart the instance on the same node or the instance is relocate to the 2nd node incase there is failure or fault in 1st node. The benefit of this feature is that it provides a cold failover solution and it automates the instance relocation without any downtime and does not need a manual intervention. Oracle introduced this feature with the release of 11gR2 (available with Enterprise Edition).
What are invalid objects in database?
Sometimes schema objects reference other objects such as a view contains a query that reference table or other view and a PL/SQL subprogram invokes other subprograms or may reference another tables or views. These references are established at compile time and if the compiler cannot resolve them, the dependent object being compiled is marked invalid.
An invalid dependent object must be recompiled against the new definition of a referenced object before the dependent object can be used. Recompilation occurs automatically when the invalid dependent object is referenced
How can we check DATAPUMP file is corrupted or not?
Sometimes we may be in situation, to check whether the dumpfile exported long time back is VALID or not or our application team is saying that the dumpfile provided by us is corrupted.
Use SQLFILE Parameter with import script to detect corruption. The use of this parameter will read the entire datapump export dumpfile and will report if corruption is detected.
impdp system/*** directory=dump_dir dumpfile=expdp.dmp logfile=corruption_check.log sqlfile=corruption_check.sql 
This will write all DDL statements (which will be executed if an import is performed) into the file which we mentioned in the command.
How can we find elapsed time for particular object during Datapump or Export?
We have an undocumented parameter ‘metrics’ in DATAPUMP to check how much it took to export different objects types.
Expdp system/oracle directory = dump_dir dumpfile = exp_full.dmp logfile = exp_full.log full = y metrics = y;
How to check oracle database service is running in server?
DBA using this command on daily basis to find running oracle service on server
On Linux: ps -ef

On Windows: Tasklist /svc | find "oracle" 
How can we find different OS block size?
In oracle we can say that database block size should be multiple of OS block size.
On Windows: fsutil fsinfo ntfsinfo c: | find /i "bytes" 
On Linux: tune2fs -l 
On Solaris: df -g /tmp
How to find location of OCR file when CRS is down?
If you need to find the location of OCR (Oracle Cluster Registry) but your CRS is down.
When the CRS is down:
Look into “ocr.loc” file, location of this file changes depending on the OS:
On Linux: /etc/oracle/ocr.loc 
On Solaris: /var/opt/oracle/ocr.loc 
When CRS is UP:
Set ASM environment or CRS environment then run the below command:
How can you Test your Standby database is working properly or not?
To test your standby database, make a change to particular table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you have done, shutdown your standby and startup again in standby mode. 
What is Dataguard & what is the purpose of Data Guard? 
Oracle Dataguard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios. 
a) Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.
b) Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions.
c) With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems. 
What is role of Redo Transport Services in Dataguard?
It controls the automated transfer of redo data from the production database to one or more archival destinations. The redo transport services perform the following tasks:
a) Transmit redo data from the primary system to the standby systems in the configuration.
b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.
c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the
primary database or another standby database. 
Is Opatch (utility) is also another type of patch?
OPatch is utility from oracle corp. (Java based utility) that helps you in applying interim patches to Oracle's software and rolling back interim patches from Oracle's software. Opatch also able to Report already installed interim patch and can detect conflict when already interim patch has been applied. This program requires Java to be available on your system and requires installation of OUI. Thus from the above discussion coming to your question it is not ideal to say OPATCH is another patch.
When we applying single Patch, can you use opatch utility?
Yes, you can use Opatch incase of single patch. The only type of patch that cannot be used with OPatch is a patchset
When you applying Patchsets, You can use OUI.
Yes, Patcheset uses OUI. A patch set contains a large number of merged patches, to change the version of the product or introduce new functionality. Patch sets are cumulative bug fixes that fix all bugs and consume all patches since the last base release. Patch sets and the Patch Set Assistant are usually applied through OUI-based product specific installers.
Can you Apply OPATCH without downtime?
As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible without or zero downtime in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME).
You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from it?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture.
For Example:
opatch util napply <patch_location> -id 9 -skip_subset -skip_duplicate
This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.
If both CPU and PSU are available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1
PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?
CPUs are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus any one who is concerned only with security fixes and not functionality fixes, CPU may be good approach. 
Will Patch Application affect System Performance?
Sometimes applying certain patch could affect Application performance of SQL statements. Thus it is recommended to collect a set of performance statistics that can serve as a baseline before we make any major changes like applying a patch to the system.
What is your day to day activity as an Apps DBA?
As an Apps DBA we monitor the system for different alerts (Entreprise Manager or third party tools used for configuring the Alerts) Tablespace Issues, CPU consumption, Database blocking sessions etc., Regular maintenance activities like cloning, patching, custom code migrations (provided by developers) and Working with user issues.
How often do you use patch in your organization?
Usually for non-production the patching request comes around weekly 4-6 and the same patches will be applied to Production in the outage or maintenance window.
Production has weekly maintenance window (eg. Sat 6PM to 9PM) where all the changes (patches) will applied on production.
How often do you use cloning in your organization?
Cloning happens weekly or monthly depending on the organization requirement. Generally when we need to perform major task such as oracle financial annual closing etc.


  1. Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my visitors will find that very useful.

  2. DEAR SIR,

    very important and beautiful question and answer for me.
    sir may i asked one question. sir i rohit kumar MCA fresher(2013)
    with OCP,OCA,OCE certified pass out .but after 6 month completion
    of my certificate i cant seen any walkin or DBA fresher job.

    sir i am requested to you provide me proper guidance for my future
    career those will be provide me my path.

    I am very thankful you.

    your sincerely

    rohit kumar