Oracle DBA Interview Questions

1.      Oracle DBA Checklist – Common procedures

Daily Procedures
·         Verify all instances are up
·         Look for any new alert log entries
·         Verify DBSNMP is running
·         Verify free space in tablespaces
·         Verify rollback segment (v$rollstat)
·         Identify bad growth projections
·         Identify space-bound objects
·         Processes to review contention for CPU, memory, network or disk resources Copy Archived      Logs to Standby Database and Roll Forward
·         Read DBA manuals for one hour

Weekly Procedures
·         Look for objects that break rules
·         All tables should have unique primary keys
·         All indexes should use INDEXES tablespace. Run mkrebuild_idx.sql
·         Look for security policy violations
·         Visit home pages of key vendors

Monthly Procedures
·         Look for Harmful Growth Rates
·         Review Tuning Opportunities
·         Look for I/O Contention
·         Review Fragmentation
·         Project Performance into the Future
·         Perform Tuning and Maintenance
Explain what are Oracle Catalog and Oracle Archive log?
Oracle catalog consists of the metadata about objects like tables synonyms views etc.
Oracle catalog is read by oracle when the database is getting up. Archive log as the backup or a copy of the redolog files.  Archive logs are used to recover the database or whenever the log mining is required to perform.
What is PCT Free and PCT Used? What is PCT increase parameter in segment? What is growth factor?
PCTFREE 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 kalyan after that we will update the name as kalyan kumar). If mention PCTFREE as 10 oracle will add the new rows to block up to 90 It allows 10 for future updates.
What is dump destination?
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
In 11g this concept has been replaced with diagnostic test!
What are four errors found in an alert (error) log that can ruin a DBA's appetite and how can you avoid them?
·         Deadlock Errors
·         Oracle Internal errors
·         Backup and recovery errors
·         Snapshot too old error
·         Database Startup/shutdown
Can you perform DML operations when the standby database is in read only in Standby Database?
Standby can be of two types:  physical standby and logical standby.
Physical standby database is exactly the same in structure as primary database. When it is in read-only mode SQL queries can be run on it. The other mode is redo-apply mode and database is not accessible for queries at that time.
Logical standby database may have different structure from primary database. When it is in read-only mode SQL statements generated from redo are applied and queries may be run concurrently. When in read-write mode one can modify data in tables created in addition to primary schema. But this setting may be over written by specifying additional security options.
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?
Check alert_log file for any error.
Check system log file (dmesg)
Use metalink.
What are the different scenarios for rebuilding indexes...
Actually you need to rebuild the indexes for 2 main scenarios.
·         Deleted entries represent 20 or more of the current entries.
·         The index depth is more than 4 levels
How to convert local management tablespace to dictionary managed tablespace?
Use the following package
·         execute dbms_space_admin.tablespace_convert_to_local('tablespace_name');
·         execute dbms_space_admin.tablespace_convert_from_local('tablespace_name');
As an Oracle DBA what are the entire UNIX file you should be familiar with?
Log file of server, in unix dmesg.
Oratab
Database files .dbf
When the developer trying to compile the package. But the database gets hanged? What will you check?
Check objects and objects dependencies; that are using them.
Select * from v$access
Then wait or kill session are using.
What are the prioritized tuning steps to implement a database with optimal performance?
Proactive tuning during the database design and development stage is the most effective way to achieve optimal database performance. The following list has been ordered according to their general return on effort.
·         Tune the business rules.
·         Tune the data design
·         Tune the application design.
·         Tune the logical structure of the database.
·         Tune the database operations.
·         Tune the access paths.
·         Tune memory allocation.
·         Tune the I/O and physical structure.
·         Tune the resource contention.
·         Tune the underlying platform(s).
What are the tools that can be used to monitor database performance?
The database has a complete set of statistics and mechanisms to alert when problems are occurring.
Gathering information to tune an Oracle database can be obtained with the following tools and database resources:
·         Alert log and trace files: The first step in detecting a performance problem is searching for errors or warnings issued by the database. These files keep track of this information.
·         V$ views: Database statistical information is stored in the V$ Views.
·         Utlbstat/utlestat and STATSPACK (available from 8.1.6): These tools bundled with the Oracle Server generate complete reports of the database activity. The new STATSPACK utility bundled with Oracle 8.1.6 and above provides more flexibility in managing statistical snapshots.
·         OEM Performance Pack. The Performance pack offers a complete set of graphical tools to monitor the performance of the database.
What are the major tuning areas in database performance tuning?
·         Memory - shared pool, large pool, buffer cache, redo log buffer, and sort area size.
·         I/O - distributing I/O, striping, multiple DBWn processes, and DBWn I/O slaves.
·         CPU - CPU utilization.
·         Space management - extent allocation and Oracle block efficiency.
·         Redo log and checkpoint - redo log file configuration, redo entries, and checkpoint.
·         Rollback segment - sizing rollback segments.
·         Network
What is the appropriate size of SGA?
The appropriate size of SGA is system dependent, and it is limited by the available system resources. For optimal performance, SGA should fit into real memory avoiding the need for swapping. When sizing the SGA consider to tune all memory structures (the shared pool, the buffer cache and the redolog buffer cache) together so you can assign appropriate resources to each according with the database requirements.
How will you rectify if one of the rollback segments gets corrupted?
It is a rollback segment with a status of "NEEDS RECOVERY".  Basically, an undo entry could not be applied to the data block for some reason.
For whatever reason, that data block is unavailable, the undo cannot be applied. The result is a 'corrupted' rollback segment with the status of needs recovery.
MAKE sure that all tablespaces are online and all datafiles are online
What is the difference between Cost Based Optimizer and Rule Based Optimizer?
In brief the rule-based method means that when executing a query the database must follow certain predefined rules and matter what data is stored in affected database tables. The cost-based method means the database must decide which query execution plan to choose using best guess approach that takes into account what data is stored in db.
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.
What are the uses of rollback segment?
·         Transaction Rollback
·         Transaction Recovery
·         Read Consistency
Can you use a commit statement within a database trigger?
Yes, if you are using autonomous transactions in the Database triggers.
Shall we create procedures to fetch more than one record?
Yes. We can create procedures to fetch more than a row. By using CURSOR commands we could able to do that.
Give the two types of tables involved in producing a star schema and the type of data they hold.
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
What is a Synonym?
A synonym is an alias for a table, view, sequence or program unit.
How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.
Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual;
Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
What command would you use to encrypt a PL/SQL application?
WRAP
Explain the use of table functions.
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
Where in the Oracle directory tree structure are audit traces placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer
What does a Control file Contain?
A Control file records the physical structure of the database. It contains the following information. DB Name, Names and locations of a database’s files and redolog files. Time stamp of database creation.
What is difference between UNIQUE constraint and PRIMARY KEY constraint?
A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls.
What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.
How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.
How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
What are the options available to refresh snapshots?
·   COMPLETE - Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced.
·   FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables.
·   FORCE - Default value. If possible it performs a FAST refresh; otherwise it will perform a complete refresh.
What is a SNAPSHOT LOG?
A snapshot log is a table in the master database that is associated with the master table. ORACLE uses a snapshot log to track the rows that have been updated in the master table. Snapshot logs are used in updating the snapshots based on the master table.
What are the different Levels of Auditing?
Statement Auditing, Privilege Auditing and Object Auditing.
What is COST-based approach to optimization?
Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.
What is a deadlock? Explain.
Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of these processes being killed externally.
How does one see the uptime for a database?
SELECT startup_time
FROM v$instance;
How do I find used/free space in a TEMPORARY tablespace?
Use the V$TEMP_SPACE_HEADER view:
SELECT tablespace_name, SUM (bytes_used), SUM (bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;
How can one see who is using a temporary segment?
For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.
For usage stats, see SYS.V_$SORT_SEGMENT
From Oracle 8.0, one can just query SYS.v$sort_usage
What is OCI (Oracle Call Interface)? What are its uses?
OCI is Oracle Call Interface. When applications developers demand the most powerful interface to the Oracle Database Server, they call upon the Oracle Call Interface (OCI). OCI provides the most comprehensive access to all of the Oracle Database functionality. The newest performance, scalability, and security features appear first in the OCI API. If you write applications for the Oracle Database, you likely already depend on OCI. Some types of applications that depend upon OCI are:
·         PL/SQL applications executing SQL
·         C++ applications using OCCI
·         Java applications using the OCI-based JDBC driver
·         C applications using the ODBC driver
·         VB applications using the OLEDB driver
·         Pro*C applications
·         Distributed SQL
What are ORACLE PRECOMPILERS?
A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way. Examples are the Pro*C Precompiler for C, Pro*Cobol for Cobol, SQLJ for Java etc.
Can cursor variables be stored in PL/SQL tables? If answer is yes, explain how? If not why?
Yes. Create a cursor type - REF CURSOR and declare a cursor variable of that type.
What should be the return type for a cursor variable? Can we use a scalar data type as return type?
The return type of a cursor variable can be %ROWTYPE or record_name%TYPE or a record type or a ref cursor type. A scalar data type like number or varchar can’t be used but a record type may evaluate to a scalar value.
What is use of a cursor variable? How it is defined?
Cursor variable is used to mark a work area where Oracle stores a multi-row query output for processing. It is like a pointer in C or Pascal. Because it is a TYPE, it is defined as TYPE REF CURSOR RETURN
What WHERE CURRENT OF clause does in a cursor?
The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.
What is difference between an implicit and an explicit cursor?
The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers
What is a cursor?
A cursor is a mechanism by which you can assign a name to a “select statement” and manipulate the information within that SQL statement.
What is an OUTER JOIN?
An OUTER JOIN returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
How does one add users to a password file?
One can select from the SYS.V$PWFILE_USERS view to see which users are listed in the password file. New users can be added to the password file by granting them SYSDBA or SYSOPER privileges, or by using the orapwd utility
Why are OPS$ accounts a security risk in a client/server environment?
If you allow people to log in with OPS$ accounts from Windows Workstations, you cannot be sure who they really are. With terminals, you can rely on operating system passwords, with Windows, you cannot.
If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle assumes that the remote OS has authenticated the user. If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password. IDENTIFIED EXTERNALLY will only be in effect from the local host. Also, if you are using "OPS$" as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.
Where the tuning effort should be directed?
Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the "data access path" in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.
Application Tuning:
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.
Memory Tuning:
Properly size your database buffers (shared pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.
Disk I/O Tuning:
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc
Eliminate Database Contention:
Study database locks, latches and wait events carefully and eliminate where possible.
Tune the Operating System:
Monitor and tune operating system CPU, I/O and memory utilization
When cost is based optimization triggered?
It's important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved in a statement does not have statistics, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much too just have the larger tables analyzed.
Generally, the CBO can change the execution plan when you:
·         Change statistics of objects by doing an ANALYZE;
·         Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).
How can one optimize %XYZ% queries?
It is possible to improve %XYZ% queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints. If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.
My query was fine last week and now it is slow. Why?
The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.
Some factors that can cause a plan to change are:
·         Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
·         Has OPTIMIZER_MODE been changed in INIT.ORA?
·         Has the DEGREE of parallelism been defined/changed on any table?
·         Have the tables been re-analyzed? Were the tables’ analyzed using estimate or compute?
·         If estimate, what percentage was used?
·         Have the statistics changed?
·         Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
·         Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
·         Have any other INIT.ORA parameters been changed?
·         What do you think the plan should be? Run the query with hints to see if this produces the required performance.
Why is Oracle not using the damn index?
This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index.
Fundamental things that can be checked are:
·      USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.
·      USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index increase thereby is making the index less desirable.
·      USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.
·      Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.
·      Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN or SKIP SCANNING).
·      There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.
When should one rebuild an index?
You can run the 'ANALYZE INDEX VALIDATE STRUCTURE' command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'. => 'DEL_LF_ROWS' / 'LF_ROWS'
Explain the two types of Cursors?
There are two types of cursors, Implicit Cursor and Explicit Cursor. PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.
What is Fine Grained Auditing?
Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table. A list of defined policies can be obtained from DBA_AUDIT_POLICIES. Audit records are stored in DBA_FGA_AUDIT_TRAIL.
What is a Virtual Private Database?
Oracle 8i introduced the notion of a Virtual Private Database (VPD). A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data. This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies' data within the same schema, without them knowing about it. VPD configuration is done via the DBMS_RLS (Row Level Security) package. Select from SYS.V$VPD_POLICY to see existing VPD configuration.
What is Oracle Label Security?
Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle 8i to implement row level security. Accesses to rows are restricted according to a user's security sensitivity tag or label. Oracle Label Security is configured, controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger, Why?
It is not possible. As triggers are defined for each table, if you use COMMIT or ROLLBACK in a trigger, it affects logical transaction processing.
What are advantages of Stored Procedures?
Extensibility, modularity, reusability, maintainability and one time compilation.
What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.
These attributes are preceded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.
What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents an entire row of a table or view or columns selected in the cursor.
The advantages are:
·      Need not know about variable's data type.
·      If the database definition of a column in a table changes, the data type of a variable changes accordingly.
What are the advantages of having a Package?
Increased functionality (for example, global package variables can be declared and used by any procedure in the package) and performance (for example all objects of the package are parsed compiled, and loaded into memory once)
What are the uses of Database Trigger?
Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.
What a SELECT FOR UPDATE cursor represent?
SELECT......FROM......FOR......UPDATE [OF column-reference][NOWAIT]
The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.
What WHERE CURRENT OF clause does in a cursor?
This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT OF clause causes an error.
What is Program Global Area (PGA)?
A Program Global Area (PGA) is a memory buffer that is allocated for each individual database session and it contains session specific information such as SQL statement data or buffers used for sorting. The value specifies the total memory allocated by all sessions, and changes will take effect as new sessions are started.
If the DB is in Archivelog mode, up to what point can you recover the DB?
Until last commit.
Which action would you take to ensure resource limit are imposed?
Set the RESOURCE_LIMIT parameter to TRUE.
How can reduce the recovery time of the DB?
·         Decreasing size of the redo log files
·         Configuring Mean Time To Recovery (MTTR) to lower value
How check the AWR statistics?
·         Using OEM
·         Using DBMS_WORKLOAD_REPOSITORY package
When redo log group are corrupted and archiving has stopped?
Alter database clear unarchived logfile group 2
What naming methods Oracle Net supports?
Local Naming: Resolves a net service name stored in a tnsnames.ora file stored on a client.
Directory Naming: Resolves a database service name, net service name, or net service alias stored in a centralized LDAP-compliant directory server.
Easy Connect Naming: Enables clients to connect to a database server without any configuration. Clients use a connect string for a simple TCP/IP address, consisting of a host name and optional port and service name.
CONNECT username/password@host[:port][/service_name]
External Naming: Resolves service information stored in a third-party naming service.
What is the difference between a PFILE and SPFILE?
A PFILE is a static, client-side text file.
An SPFILE (Server Parameter File) is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command.
Viewing Parameters Settings
One can view parameter values using one of the following methods (regardless if they were set via PFILE or SPFILE):
The "SHOW PARAMETERS" command from SQL*Plus (i.e.: SHOW PARAMETERS timed_statistics)
V$PARAMETER view - display the currently in effect parameter values
V$PARAMETER2 view - display the currently in effect parameter values, but "List Values" are shown in multiple rows
V$SPPARAMETER view - display the current contents of the server parameter file.
What is a Big File Tablespace (BFT)?
A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size.
select * from database_properties
where property_name='DEFAULT_TBS_TYPE';
ALTER DATABASE SET DEFAULT bigfile TABLESPACE;
The clause LOGGING of the CREATE TABLESPACE does?
·         It is no valid for temporary or undo tablespace.
·         The tablespace-level logging attribute can be overridden by logging specification at the table, index, materialized view and materialized view log and partition level.
What is the resumable space allocation?
In case a transaction is suspended for space allocation reasons, the resumable space allocation feature allows the transaction to be resumed.
How to be able to resume a transaction facing a space allocation issue?
·         The user running the transaction requires the RESUMABLE system privilege: grant resumable to scott;
·         Set the session so that the following transactions might be resumed    in case of interruption due to space allocation: alter session enable resumable;
·         Displaying the DBA_RESUMABLE view
·         Use procedure DBMS_RESUMABLE
How does the listener get the information of location of ORACLE_HOME and instance name?
·                     The instance automatically registers with the default listener.
·                     The listener gets this information through the listener.ora file
What is Tablespace Point-in-Time Recovery (TSPITR)?
Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.
What is ADDM?
The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL.
What did DB_FLASHBACK_RETENTION_TARGET parameter use for?
DB_FLASHBACK_RETENTION_TARGET specifies the upper limit (in minutes) on how far back in time the database may be flashed back. How far back one can flashback a database depends on how much flashback data Oracle has kept in the flash recovery area.
What did UNDO_RETENTION parameter use for?
UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries.
How recover corrupted block with RMAN?
The BLOCKRECOVER command can restore and recover individual datablocks within a datafile. This procedure is useful when a trace file or standard output reveals that a small number of blocks within a datafile are corrupt. Block media recovery is not useful in cases where the extent of data loss or corruption is not known; in this case, use datafile recovery instead.
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
What is the FLASHBACK_TRANSACTION_QUERY view?
The FLASHBACK_TRANSACTION_QUERY view is used to provide information about all flashback transaction queries in a database.
What is Locks, types and modes?
Locks are mechanisms that prevent transactions from accessing the same resource concurrently with intent of modification to the data.
Oracle Lock Modes
·         Exclusive Lock
·         Shared Lock
Oracle Lock types
·         DML locks
·         DDL locks (dictionary locks)
·         Oracle Internal Locks/Latches
·         Oracle Distributed Locks
Oracle Table Level Lock Modes
·         row share (RS),
·         row exclusive (RX),
·         share (S),
·         share row exclusive (SRX),
·         exclusive (X)
What is latch?
A mechanism to protect shared data structures in the System Global Area. A latch is a low-level internal lock used by Oracle to protect memory structures.
What is Oracle Partitioning and types?
Oracle partitioning an option of oracle database can enhance the manageability, performance and availability. Partitioning allow a table index & index organized table to be subdivided into smaller pieces. Each piece of database object is called a partition. Each partition has its own name and may optionally have its own storage.
·         Range Partition: by ranges of values
·         List Partition: by list of values
·         Hash Partition: by hash algorithm
·         Composite Partition: It can contain sub-partition of range, list or hash.
·         Interval partition: Is an extension of range partition, At least one partition must be created
·         Reference partition: Based on partition method of a table referenced in its referential constraint.
·         Virtual Column Based Partition:
·         System Partition: Enables application-controlled partitioning for arbitrary tables.
What index can by created on partitioned tables?
·         Local Index: same manner as the index on existing partitioned table.
·         Global Partitioned Indexes: It can be created on a partitioned or a non-partitioned tables.
·         Global Non-Partitioned Indexes: This is no different than the ordinary index.
Differences between automatic undo and manual undo management?
Automatic Undo Management Mode:
·         Oracle will automatically name, create and manage the Undo Segments.
·         Oracle can control the sizing and the number of undo segments
·         You require an Undo tablespace for automatic undo management.
Manual Undo Management Mode:
·         Same as using Rollback Segments in Oracle8i and below
·         DBA has to create and manage the roll back segments.
·         Manual undo management is not obsolete. It is even supported in the latest versions of Oracle.
Users of the database report slow response times to their queries. Yet when you look at the system performance, you find there is adequate CPU capacity and the disks are not overloaded. What might be wrong?
The combination of symptoms described cannot be CPU or Disk I/O bottleneck, per details question. It is not likely to be memory related, either as a memory problem would also raise Disk I/O. Therefore, it is probably a contention (locking) issue, or due the network bandwidth problems.
FLASHBACK Technology
The Oracle Database architecture leverages the unique technological advances in the area of database recovery due to human errors. Oracle Flashback Technology provides a set of new features to view and rewind data back and forth in time. The Flashback features offer the capability to query historical data, perform change analysis, and perform self-service repair to recover from logical corruptions while the database is online. With Oracle Flashback Technology, you can indeed undo the past!
Oracle9i introduced Flashback Query to provide a simple, powerful and completely non-disruptive mechanism for recovering from human errors. It allows users to view the state of data at a point in time in the past without requiring any structural changes to the database.
Oracle Database 10g extended the Flashback Technology to provide fast and easy recovery at the database, table, row, and transaction level. Flashback Technology revolutionizes recovery by operating just on the changed data. The time it takes to recover the error is now equal to the same amount of time it took to make the mistake. Oracle 10g Flashback Technologies includes Flashback Database, Flashback Table, Flashback Drop, Flashback Versions Query, and Flashback Transaction Query.
What is the difference between locks, latches, enqueues and semaphores?
A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET are used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.
An Enqueue protects shared resources from concurrent, incompatible accesses
Prevent two sessions from writing the same row of a table at the same time
Facilitates enforcement of parent/child locking for referential integrity
Prevent two sessions from updating the definition of a table (drop/add column, etc) at the same time
Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.
Enqueues differ from latches:
·         Latches are the Database’s version of a mutex
·         Whereas latches (mostly) provide for mutually exclusive access, enqueues allow for shared access, if mode is compatible
·         Enqueues allow for enqueueing, that is, sessions waiting for access to an enqueue will queue in a line
·         In the case of latches, sessions waiting for access must spin or sleep, and there’s no guarantee who will get the latch next
The most common enqueue are:
·         Though there are many common enqueues, usually only TM and TX create problems for applications and developers.
·         TM – DML, or table enqueue
·         TX – Transaction enqueue.
Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:
·         semmns = sum of the "processes" parameter for each instance (see init<instance>.ora for each instance)
·         semmni = number of instances running simultaneously;
·         semmsl = semmns
Another difference between latches and enqueues is that in latches there is no ordered queue of waiters like in enqueues.
What is a deadlock and how does one fix deadlock errors?
A deadlock occurs when two or more users are waiting for data locked by each other. When this happens, these users are stuck (deadly embraced) and cannot continue processing.
Oracle automatically detects deadlocks and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. The session that is rolled back will observe Oracle error: ORA-00060: deadlock detected while waiting for resource. Oracle will also write out a trace file with detailed information to the database's UDUMP directory.
Multi-table deadlocks can be avoided by locking tables in same order (in all applications), thus preventing a deadlock condition. For example, session1 lock table: emp then dept; session2: emp then dept. If this is not possible, your application should check for ORA-60 errors and restart the rolled back transactions.
Top 20 New Features in Oracle 10g
•      Flashback Versions Query
•      Rollback Monitoring
•      Tablespace Management
•      Oracle Data Pump
•      Flashback Table
•      Automatic Workload Repository
•      SQL*Plus Rel 10.1
•      Automatic Storage Management
•      RMAN: offline recovery of incremental backups, previewing restore, recovering through resetlogs, file compression
•      Auditing: captures user activities at a very detailed level, which may obviate manual, trigger-based auditing    
•      Wait Interface
•      Materialized Views
•      Enterprise Manager 10g
•      Virtual Private Database
•      Automatic Segment Management
•      Transportable Tablespaces
•      Automatic Shared Memory Management
•      ADDM and SQL Tuning Advisor
•      Scheduler
Top New Features in Oracle 11g
•      Database Replay Explore Database Replay, the new tool that captures SQL statements and lets you replay them at will.
•      Partitioning Learn about Referential, Internal, and Virtual Column partitioning; new sub-partitioning options; and more.
•      Transaction Management Get an introduction to Flashback Data Archive and explore Enterprise Manager's LogMiner interface.
•      Schema Management Add columns with a default value easily and explore invisible indexes, virtual columns, and read only tables.
•      SQL Plan Management Use bind variables that pick the right plan every time and ensure a new execution plan is perfect before it's used.
•      SQL Performance Analyzer Accurately assess the impact of rewriting of SQL statements and get suggested improvements.
•      SQL Access Advisor Get advice about optimal table design based on actual use of the table, not just data.
•      PL/SQL: Efficient Coding Triggers that fire several times at different events and ability to force triggers of the same type to follow a sequence are some new gems.
•      RMAN Explore Data Recovery Advisor, do parallel backup of the same file, and create and manage virtual catalogs.
•      Security Learn about Tablespace Encryption, case-sensitive passwords, data masking, and other features.
•      Automatic Storage Management Learn about new SYSASM role, variable extent sizes, and other ASM improvements.
•      Manageability Explore automatic memory management, multicolumn statistics, online patching, and more features.
•      Caching and Pooling Explore SQL Result Cache, PL/SQL Function Cache, and Database Resident Connection Pooling.
•      SQL Operations: Pivot and Unpivot Present information in a spreadsheet-type crosstab report from any relational table using simple SQL, and store any data from a crosstab table to a relational table.
•      SecureFiles Explore next-generation LOBs: LOB encryption, compression, deduplication, and asynchronicity.
•      Resiliency Explore Automatic Health Monitor, Automatic Diagnostic Repository, and other new resiliency features.
•      Data Guard Query the physical standby database in real time without shutting down recovery, just for starters.
•      PL/SQL Performance Explore in-lining of code, "real" native compilation, PLS timer, use of simple integer, and more.
•      Data Warehousing and OLAP Get a tour of new features in these areas, including Cube Organized MVs.
•      And Don't Forget... COPY command, Export/Imports, Data Pump improvements, and more.
One of my control file corrupted and I am unable start database, how can I perform recovery?
If one of your control file is missing or corrupted then we have 2 options to recover it. Check alert.log for exact name and location of corrupted control file. Delete it manually and copy from available rest of control file and rename it and start database. Another option is delete corrupted control file and remove name from parameter file/ spfile. After removing said control file from spfile, start your database.
What is incremental checkpoint?
In incremental checkpoint process, CKPT process records lowest Low RBA to the control file to keep advancing the Buffer checkpoint Queue (BCQ) to make easy and fastest Active Checkpoint Queue (ACQ).
By mistake one table was dropped by application user, How to recover that dropped table?
If your database is running on Oracle 10g version then there is new feature available called Recyclebin. You can recover dropped table from user_recyclebin or dba_recyclebin.
The above scenario, no Recycle bin enabled,  how to I recover my table?
Then you should need to restore backup on your UAT or test database server and enable time based recovery for applying all archives before drop command execution. For an instance, apply archives up to 3:55 PM here.
What is the meaning of LGWR SYNC and LGWR ASYN in log archive destination parameters for standby configuration?
When use LGWR with SYNC, it means once network I/O initiated, LGWR has to wait for completion of network I/O before write processing. LGWR with ASYNC, means LGWR doesn’t wait to finish network I/O and continuing write processing.
How can I know my require table is available in export dump file or not?
Create indexfile of export dump file using import with indexfile command. A text file will be generating with all table and index object name with number of rows. You can confirm your require table object from this text file.
How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?
We can perform this recovery in 2 ways. One is open database mode and another is database mount mode.  After taking offline lost datafile, we can bring database open and after that restore lost datafile from last backup. After restoration of datafile we can perform datafile level recovery for applying archive logs and make it online. In database mount mode, we can restore datafile from backup and perform datafile recovery using “Recover datafile” command.
Why we should need to open database using RESETLOGS after finishing incomplete recovery?
When we are performing incomplete recovery it means we bring database to past time or rewind period of time using change based, cancel based or time based recovery. These all recovery make database in prior state of database. The forward sequence number already available after performing recovery, due to mismatching of this sequence numbers and prior state of database, it needs to open database with new sequence number of Redo log and archive logs.
What is the basic difference between view and materialized view?
Simple view is logical and only definition stored in data dictionary view of system schema means it doesn’t occupy any space in database, but materialized view is real table and it occupy space in database.
What is the use of Data Dictionary?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc. Data Dictionary stores Oracle database metadata.
What is difference between foreign key and reference key?
Foreign key is the key i.e. attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.