TAGS: SAP, ORACLE, BASIS, UNIX, INTERVIEW QUESTIONS,
Question 1. Explain the difference between a hot backup and a cold backup and the benefits
associated with each.
Answer:
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
Question 2. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
Answer:
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
Question 3. How do you switch from an init.ora file to a spfile?
Answer:
Issue the create spfile from pfile command.
Question 4. Explain the difference between a data block, an extent and a segment.
Answer:
A data block is the smallest unit of logical storage for a database object. As objects grow they take
chunks of additional storage that are composed of contiguous data blocks. These groupings of
contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
Question 5. Give two examples of how you might determine the structure of the table DEPT.
Answer:
Use the describe command or use the dbms_metadata.get_ddl package.
Question 6. Where would you look for errors from the database engine?
Answer:
In the alert log.
Question 7. Compare and contrast TRUNCATE and DELETE for a table.
Answer:
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table.
The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
Question 8. Give the reasoning behind using an index.
Answer:
Faster access to data blocks in a table.
Question 9. Give the two types of tables involved in producing a star schema and the type of data they hold.
Answer:
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
Question 10. What type of index should you use on a fact table?
Answer:
A Bitmap index.
Question 11. Give two examples of referential integrity constraints.
Answer:
A primary key and a foreign key.
Question 12. A table is classified as a parent table and you want to drop and re-create it. How
would you do this without affecting the children tables?
Answer:
Disable the foreign key constraint to the parent, drop the table, re-create the table, and enable the foreign key constraint.
Question 13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG
mode and the benefits and disadvantages to each.
Answer:
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
Question 14. What command would you use to create a backup control file?
Answer:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Question 15. Give the stages of instance startup to a usable state where normal users may access
it.
Answer:
STARTUP NOMOUNT - Instance startup
ALTER DATABASE MOUNT - The database is mounted
ALTER DATABASE OPEN - The database is opened
Question 16. What column differentiates the V$ views to the GV$ views and how?
Answer:
The INST_ID column which indicates the instance in a RAC environment the information came from.
Question 17. How would you go about generating an EXPLAIN plan?
Answer:
Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql
Question 18. How would you go about increasing the buffer cache hit ratio?
Answer:
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.
Question 19. Explain an ORA-01555
Answer:
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing
the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the
application getting the error message.
Question 20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
Answer:
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is
where the oracle products reside.
Question 21. How would you determine the time zone under which a database was operating?
Answer:
select DBTIMEZONE from dual;
Question 22. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Answer:
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.
Question 23. What command would you use to encrypt a PL/SQL application?
Answer:
WRAP
Question 24. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Answer:
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that
carries a single task. While a procedure does not have to return any values to the calling application, a
function will return a single value. A package on the other hand is a collection of functions and procedures
that are grouped together based on their commonality to a business function or application.
Question 25. Explain the use of table functions.
Answer:
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.
Question 26. Name three advisory statistics you can collect.
Answer:
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
Question 27. Where in the Oracle directory tree structure are audit traces placed?
Answer:
In unix $ORACLE_HOME/rdbms/audit,
in Windows the event viewer
Question 28. Explain materialized views and how they are used.
Answer:
Materialized views are objects that are reduced sets of information that have been summarized, grouped,
or aggregated from base tables. They are typically used in data warehouse or decision support systems.
Question 29. When a user process fails, what background process cleans up after it?
Answer:
PMON
Question 30. What background process refreshes materialized views?
Answer:
The Job Queue Processes.
Question 31. How would you determine what sessions are connected and what resources they
are waiting for?
Answer:
Use of V$SESSION and V$SESSION_WAIT
Question Question 32. Describe what redo logs are.
Answer:
Redo logs are logical and physical structures that are designed to hold all the changes made to a
database and are intended to aid in the recovery of a database.
Question 33. How would you force a log switch?
Answer:
ALTER SYSTEM SWITCH LOGFILE;
Question 34. Give two methods you could use to determine what DDL changes have been made.
Answer:
You could use Logminer or Streams
Question 35. What does coalescing a tablespace do?
Answer:
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining
neighboring free extents into large single extents.
Question 36. What is the difference between a TEMPORARY tablespace and a PERMANENT
tablespace?
Answer:
A temporary tablespace is used for temporary objects such as sort structures while permanent
tablespaces are used to store those objects meant to be used as the true objects of the database.
Question 37. Name a tablespace automatically created when you create a database.
Answer:
The SYSTEM tablespace.
Question 38. When creating a user, what permissions must you grant to allow them to
connect to the database?
Answer:
Grant the CONNECT to the user.
Question 39. How do you add a data file to a tablespace?
Answer:
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>
Question 40. How do you resize a data file?
Answer:
ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;
Question 41. What view would you use to look at the size of a data file?
Answer:
DBA_DATA_FILES
Question 42. What view would you use to determine free space in a tablespace?
Answer:
DBA_FREE_SPACE
Question 43. How would you determine who has added a row to a table?
Answer:
Turn on fine grain auditing for the table.
Question 44. How can you rebuild an index?
Answer:
ALTER INDEX <index_name> REBUILD ONLINE;
Question 45. Explain Partitioning and benefits of using Partioning.
Answer:
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more
manageable pieces.
Question 46. You have just compiled a PL/SQL package but got errors, how would you view the
errors?
Answer:
SHOW ERRORS
Question 47. How can you gather statistics on a table?
Answer:
The ANALYZE command or DBMS_STAT package.
Question 48. How can you enable a trace for a session?
Answer:
Use the DBMS_SESSION.SET_SQL_TRACE or Use
ALTER SESSION SET SQL_TRACE = TRUE;
Question 49. What is the difference between the SQL*Loader and IMPORT utilities?
Answer:
These two Oracle utilities are used for loading data into the database. The difference is that the import
utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility
allows data to be loaded that has been produced by other utilities from different data sources just so long
as it conforms to ASCII formatted or delimited files.
Question 50. Name two files used for network connection to a database.
Answer:
TNSNAMES.ORA and SQLNET.ORA
Question 51. Explain difference between Oracle Database and Instance
Answer:
Oracle database consist of following:
Datafiles, Control Files, Redo Log Files
Oracle instance consist of following:
SGA and Oracle processes (DBWR, LGWR, SMON, PMON, ARCH, CKPT, etc.)
Question 52. What is Mutating Table error?
Answer:
Mutating table error happens when you try to modify the same row you are using it. Use temp table to
avoid this error.
Question 53. What is correlated sub-query?
Answer:
Correlated sub-query is a sub-query, which has reference to the main query
Question 54. Which one of the below change will decrease PAGING/SWAPPING
Answer:
1. INCREASE SORT_AREA_RETAINED_SIZE
2. DECREASE SHARED_POOL_SIZE (correct answer)
3. DECREASE OPEN_CURSORS parameters
4. INCREASE DB_CACHE_SIZE
Question 55. Explain steps involved in Migration from Oracle 9.0.1 to 9.2.0.6
Answer:
1. Shutdown 9.0.1 database
2. Apply software patch to new/old ORACLE_HOME
3. Change ORACLE_HOME to point to new 9.2.0.6 binaries
4. STARTUP MIGRATE
5. @ORACLE_HOME/rdbms/admin/catpatch.sql
6. @ORACLE_HOME/rdbms/admin/utlrp.sql
7. Change init.ora parameter COMPATIBLE = 9.2.0.6
8. Shutdown and startup normal
Question 56. Can you create database under any Unix userid besides oracle?
Answer:
Yes. You can create database user other then oracle as long as that user is part of DBA group in unix
Question 57. Locally Managed TEMP Tablespace is 100% FULL and there is no space available to
add datafile to increase TEMP tablespace. What can you do that might free up TEMP space?
Answer:
You can try one of the following to free up TEMP space
1. Issue ALTTER TABLESPACE PCTINCREASE 1 followed by
ALTTER TABLESPACE PCTINCREASE 0 command;
2. Close some of the idle sessions connected to the database
Question 58. List five most important features of Oracle 9i
Answer:
1. AUTOMATIC UNDO MANAGEMEND
2. AUTOMATICE PGA MEMORY MANAGEMENT
3. MULTI-TABLE INSERT statements
4. EXTERNAL TABLES
5. DYNAMIC MEMORY MANAGEMENT
Question 59. Name five top 9I init.ora parameters affecting performance
Answer:
1. CURSOR_SHARING
2. DB_CACHE_SIZE
3. PGA_AGGREGATE_TARGET
4. WORKAREA_SIZE_POLICY
5. DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE
Question 60. How do you recover database when you lost all of your control files?
Answer:
In case of loss of loss of all control files, you can still recover database as long as you have all
archivelog files. You can issue following command to recover the database.
RECOVER DATABASE USING BACKUP CONTROLFILES UNTIL CANCEL;
Apply all archivelog files
ALTER DATABASE OPEN RESETLOGS;
SHUTDOWN IMMEDIATE;
Backup database (COLD)
STARTUP;
Question 61. What is main purpose of CHECKPOINT?
Answer:
A Checkpoint is a database event, which synchronizes the data blocks in memory with the datafiles on disk.
A checkpoint has two purposes:
1. to establish data consistency
2. Enable faster database recovery.
Question 62. You got a call from Application team saying Application is running very SLOW.
Where do you start looking first?
Answer:
Below are some of very important you should gather to identify the root cause of slowness in
application/database.
Run a TOP command in unix to see CPU usage (identify CPU killer processes)
Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and
memory usage and possible blocking
Run STATSPACK report to identify:
a. TOP 5 WAIT EVENTS
b. RESOURCE intensive SQL statements
See if STATISTICS on affected tables needs to be re-generated
IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and
see whether new index or use of HINT brings the cost of SQL down.
Question 63. How to SWITCH from PRIMARY to PHYSICAL STANDBY in 9i
Answer:
Perform below on Primary DB:
1. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH
SESSION SHUTDOWN;
2. SHUTDOWN IMMEDIATE;
3. STARTUP NOMOUNT;
4. ALTER DATABASE MOUNT STANDBY DATABASE;
5. RECOVER MANAGED STANDBY DATABASE DICONNECT FROM SESSION;
6. ALTER SYSEM SET LOG_ARCHIVE_DEST_2_STATUS= DEFER SCOPE=SPFILE;
Perform below on STANDBY DB:
7. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
8. SHUTDOWN IMMEDIATE;
9. STARTUP;
10. ALTER SYSEM SET LOG_ARCHIVE_DEST_2_STATUS= ENABLE SCOPE=SPFILE;
At this time Standby becomes Primary and Primary becomes new Standby.
Question 64. How to ACTIVATE PHYSICAL STANDBY database in 9i
Answer:
Perform below on Primary DB if available to transfer all pending archive logs to standby:
1. ALTER SYSTEM SWITCH LOGFILE;
2. ALTER SYSTEM SWITCH LOGFILE;
Perform below on STANDBY DB:
1. ALTER DATABASE ACTIVATE STANDBY DATABASE;
2. SHUTDOWN IMMEDIATE;
3. STARTUP
4. Add TEMP file if needed.
At this time Standby database becomes PRIMARY database and relationship between Primary and
Standby has been lost by activating standby database.
Question 65. Explain different protection mode in DATAGURD
Answer:
MAXIMUM PROTECTION
MAXIMUM AVAILIBILITY
MAXIMUM PERFORMANCE
Question 66. What is CASCADING STANDBY database?
Answer:
A CASCADING STANDBY is a standby database that receives its REDO information from another
STANDBY database (not from Primary database).
Question 67. What are some of the dba tables you query to find out about UNDO segments?
Answer:
You would query:
DBA_UNDO_SEGS
V$UNDOSTAT
Question 68. What does Block Media Recovery (BMR) do?
Answer:
BMR is responsible for restore and recover of specified BLOCK
Question 69. What is the difference between Fine Grained Audit (FGA) and Fine Grained Access
Control (FGAC)?
Answer:
FGA tracks when sensitive rows have been accesses, where FGAC prevents access to sensitive rows
Question 70. Which dynamic performance view DBA can query to see who deleted data from a
particular table sometime back?
Answer:
V$LOGMNR_CONTENTS
Question 71. What do you use DBMS_REDEFINITION package for?
Answer:
DBMS_REDEFINITION package is used to perform an ONLINE REBUILD of a table
Question 72. How do you add second or subsequent BLOCK SIZE to an existing database?
Answer:
Re-Create the CONTROLFILE to specify the new BLOCK SIZE for specific data files
Or Take the database OFFLINE, and the bring back online with a new BLOCK SIZE
specification
Question 73. What are new RMAN features in 9i?
Answer:
It allows you to set retention period of backups
It enables you to store the CHANNEL attributes so you do not have to specify in each
BACKUP or RESTORE command
Question 74, Explain different PARTITIONING options available in 9i
Answer:
Range Partitioning Used when there are logical ranges of data. Possible usage: dates, part numbers,
and serial numbers.
Hash Partitioning Used to spread data evenly over partitions. Possible usage: data has no logical
groupings.
List Partitioning Used to list together unrelated data into partitions. Possible usage: a number of states
list partitioned into a region.
Composite Range-Hash Partitioning Used to range partition first, then spreads data into hash partitions.
Possible usage: range partition by date of birth then hash partition by name; store the results into the
hash partitions.
Composite Range-List Partitioning Used to range partition first, then spreads data into list partitions.
Possible usage: range partition by date of birth then list partition by state, then store the results into the
list partitions.
Question 75. How to convert a Single Instance 9i database to a Real Application Clusters 9i (RAC)
database without using export/import tools.
Answer:
1. Make a full database backup before you change anything
2. Copy the existing $ORACLE_HOME/dbs/init<SID1>.ora to
$ORACLE_HOME/dbs/init<db_name>.ora. Add the following parameters to
$ORACLE_HOME/dbs/init<db_name>.ora:
a. *.cluster_database = TRUE
b. *.cluster_database_instances = 2
c. *.undo_management=AUTO (Add if you don't have it )
d. <SID1>.undo_tablespace=undotbs (undo tablespace which already exists)
e. <SID1>.instance_name=RAC1
f. <SID1>.instance_number=1
g. <SID1>.thread=1
h. <SID1>.local_listener=LISTENER_RAC1
3. Open your database and run $ORACLE_HOME/rdbms/admin/catclust.sql to create cluster
database specific views within the existing instance
4. Recreate control file if you defined maxinstances to be 1 when you created the single instance
database
5. Add instance specific parameters in the init<db_name>.ora for the second instance on the
second node and set appropriate values for it
a. <SID2>.instance_name=RAC2
b. <SID2>.instance_number=2
c. <SID2>.local_listener=LISTENER_RAC2
d. <SID2>.thread=2
e. <SID2>.undo_tablespace=UNDOTBS2
f. <SID2>.cluster_database = TRUE
g. <SID2>.cluster_database_instances = 2
6. From the first instance, mount the database and run the following command
alter database
add logfile thread 2
group 3 ('/dev/RAC/redo2_01_100.dbf') size 100M,
group 4 ('/dev/RAC/redo2_02_100.dbf') size 100M;
alter database enable public thread 2;
7. Create a second Undo Tablespace from the existing instance
8. Set ORACLE_SID and ORACLE_HOME environment variables on the second node and
START second instance.
Question 76. Explain RAC and advantages of using RAC
Answer:
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.
Advantages of RAC:
Improved throughput
Scalability over single instance systems
Improved response time
High availability
Transparency
Question 77. Explain Row Locking and Multi-version Read Consistency in RAC
Answer:
Oracle's row locking feature allows multiple transactions from separate nodes to lock and update
different rows of the same data block. This is done without any of the transactions waiting for the others
to commit. If a row has been modified but not yet committed, then the original row values are available to
all instances for read access. This is called multi-version read consistency.
Question 78. What is an External table introduced in 9i?
Answer:
Oracle9i introduces external tables, which provide a mechanism to view data stored in external sources
as if it were a table in the database. This ability to read external data provides a more straightforward
method of loading and transforming data from external sources. Administrators no longer need to reserve
space inside the database for staging tables or write external programs to transform the data outside of
the database environment. By making it no longer necessary to stage data in the Oracle database,
Oracle9i's external tables have essentially streamlined the ETL function by merging the transformation
and loading processes.
Question 79. What is SPFILE?
Answer:
The SPFILE (server parameter file) is Oracle's new method of maintaining database parameters. The old
method of editing a text based parameter file (INIT.ORA) has given way to the new method of maintaining
persistent parameters. By this Oracle means that you can change a system parameter and have its value
be maintained across shutdown and startup. This is a great savings from the past where you had to issue
the ALTER SYSTEM command and then remember to edit the INIT.ORA parameter file. Here is how to
create SPFILE:
SQL> CREATE SPFILE [='spfile_name'] FROM PFILE [='pfile_name'];
SQL> CREATE PFILE [='pfile_name'] FROM SPFILE [='spfile_name'];
SQL> create spfile from pfile;
Usage:
1. SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1000M SCOPE=SPFILE;
Question 80. How do you backup and restore using Transportable Tablespaces
Answer:
1.
2.
3.
4.
5.
6.
7.
Run DBMS_TTS against the tablespace to see if tablespace is self contained or not.
Make tablespace Read Only.
Export Metadata (EXP TRANSPORT_TABLESPACES=Y TABLESPACE=ts1)
Copy data file to target host
Copy export dump to target
Import the Metadata (IMP TRANSPORT_TABLESPACES=Y DATAFILES (file1,file2) )
Bring Tablespace ONLINE and enable SOURCE tablespace to READ WRITE
Question 81. Explain different type of Database Failures
Answer:
Statement failure failed SQL is automatically rolled back and an error is returned to user.
User Process failure abnormal disconnect PMON detects and rolls back and releases
locks.
User Error (drop table, data) DBA is required to recover data (import or incomplete
recovery)
Media Failure Loss or corruption of files DBA needs to apply appropriate recovery.
Instance Failure Abnormal shutdown Instance simply needs restarted, SMON auto
recovers by:
Rolling forward changes in the redo log not recorded in the data files before Open of
database.
Rollbacks can occur after the database is open, when block data is requested.
Question 82. What does RESETLOGS option do?
Answer:
1. Creates a new incarnation of the database, putting a new SCN in all data file headers.
2. Reset Log Sequence number to 1
3. Reformats ONLINE REDO LOGFILES if they exists
Question 83. Name 5 system views that can be used to retrieve information about backup and
recovery
Answer:
1.
2.
3.
4.
5.
V$BACKUP
V$DATAFILE_HEADER
V$RECOVER_FILE
V$RECOVERY_LOG
V$RECOVERY_STATUS
Question 84. What is the quickest way to clone a database give your backup is done via RMAN?
Answer:
Using RMAN command DUPLICATE DATABASE
Question 85. What is the use of the RESUMABLE parameter in EXPORT?
Answer:
The RESUMABLE parameter allows the export to
1. Suspend if a space allocation issue occurs
2. Wait until the space allocation issue is solved
3. Then resume and therefore not to abort, provided a timeout is set appropriately.
Question 86. Name init.ora parameters need to set for Advanced Replication
Answer:
1.
2.
3.
4.
5.
6.
DISTRIBUTED_TRANSACTIONS
GLOBAL_NAMES
JOB_QUEUE_PROCESSES
OPEN_LINKS
PARALLEL_MAX_SERVERS
PARALLEL_MIN_SERVERS
Question 87. Name 5 init.ora parameters that needs to set in Primary database for DataGuard
configuration
Answer:
1.
2.
3.
4.
5.
6.
LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_2_STATE
FAL_CLIENT
FAL_SERVER
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT
Question 88. What is the use of FORCE LOGGING option?
Answer:
By setting FORCE LOGGING to TRUE, all transactions will generate REDO. This is used for Data Guard,
so no data will be missed (i.e. transactions that were run with NOLOGGING option) in Standby database.
Question 89. (On UNIX) When should more than one DB writer process be used? How many
should be used?
Answer:
If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is
not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB
writers should be specified by use of the db_writers initialization parameter.
Question 90. If you have an example table, what is the best way to get sizing data for the
production table implementation?
Answer:
The best way is to analyze the table and then use the data provided in the DBA_TABLES view to get the
average row length and other pertinent data for the calculation. The quick and dirty way is to look at the
number of blocks the table is actually using and ratio the number of rows in the table to its number of
blocks against the number of expected rows.
Question 91. What special Oracle feature allows you to specify how the cost based system treats a
SQL statement?
Answer:
The COST based system allows the use of Hints to control the optimizer path selection. If they can give
some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
Question 92. You want to determine the location of identical rows in a table before attempting to
place a unique index on the table, how can this be done?
Answer:
select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);
Question 93. You are joining a local and a remote table, the network manager complains about the
traffic involved, how can you reduce the network traffic?
Answer:
Push the processing of the remote data to the remote instance by using a view to pre-select the
information for the join. This will result in only the data required for the join being sent across.
Question 94. How do you prevent output from coming to the screen?
Answer:
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output.
This option can be shortened to TERM.
Question 95. You see multiple fragments in the SYSTEM tablespace, what should you check first?
Answer:
Ensure that users don¡¯t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace
assignment by checking the DBA_USERS view.
Question 96. What are some indications that you need to increase the SHARED_POOL_SIZE
parameter?
Answer:
Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily
decreasing performance with all other tuning parameters the same.
Question 97. When should you increase copy latches? What parameters control copy latches?
Answer:
When you get excessive contention for the copy latches as shown by the ¡°redo copy latch hit ratio. You
can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the
number of CPUs on your system.
Question 98. Describe hit ratio as it pertains to the database buffers. What is the difference
between instantaneous and cumulative hit ratio and which should be used for tuning?
Answer:
The hit ratio is a measure of how many times the database was able to read a value from the buffers
verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is
good, less could indicate problems. If you simply take the ratio of existing parameters this will be a
cumulative value since the database started. If you do a comparison between pairs of readings based on
some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an
instantaneous reading gives more valuable data since it will tell you what your instance is doing for the
time it was generated over.
Question 99. What can cause a high value for recursive calls? How can this be fixed?
Answer:
A high value for recursive calls is cause by improper cursor usage, excessive dynamic space
management actions, and or excessive statement re-parses. You need to determine the cause and
correct it By either re-linking applications to hold cursors, use proper space management techniques
(proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.
Question 100. You look at the dba_rollback_segs view and see that there is a large number of
shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a
problem?
Answer:
A large number of small shrinks indicates a need to increase the size of the rollback segment extents.
Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of
the extents and adjust optimal accordingly.
Question 101. You look at the dba_rollback_segs view and see that you have a large number of
wraps is this a problem?
Answer:
A large number of wraps indicates that your extent size for your rollback segments are probably too small.
Increase the size of your extents to reduce the number of wraps. You can look at the average transaction
size in the same view to get the information on transaction size.
Question 102. How many redo logs should you have and how should they be configured for
maximum recoverability?
Answer:
You should have at least three groups of two redo logs with the two logs each on a separate disk spindle
(mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.
Question 103. If the database cannot be bounced, how would you kill a user?
Answer:
Use command:
ALTER SYSTEM KILL SESSION SID,SERIAL# ;
Question 104: What is the frequency of log Updated..?
Answer:
On commit or
On checkpoint or
Redolog buffer is 1/3rd full
Question 105: How do you rename a database?
Answer:
You can change Database name by following below procedure:
1. Alter Database backup control file to trace;
2. Above step will create a text control file in user_dump_dest directory.
3. Change name of the Database in above file and in init.ora file.
4. STARTUP NOMOUNT
5. Run the script that was modified in step 3
6. ALTER DATABASE OPEN RESETLOGS;
Question 106: Is it possible to configure primary server and stand by server on different OS?
Answer:
Answer for this questions is NO. Standby database must be on same version of database and same
version of Operating system.
Question 107: What does database do during mounting process?
Answer:
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.
Question 108: What is a deadlock and Explain
Answer:
A deadlock is a condition where two or more users are waiting for data locked by each other. Oracle
automatically detects a deadlock and resolves them by rolling back one of the statements involved in the
deadlock, thus releasing one set of data locked by that statement. Statement rolled back is usually the
one which detects the deadlock. Deadlocks are mostly caused by explicit locking because oracle does
not do lock escalation and does not use read locks. Multi-table deadlocks can be avoided by locking the
tables in same order in all the applications, thus precluding a deadlock.
Question 109: What are the options available to refresh snapshots?
Answer:
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.
Question 110: What is snapshot log?
Answer:
It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the
same database as master table and is only available for simple snapshots. It should be created before
creating snapshots.
Question 111: What is Two-Phase Commit?
Answer:
Two-phase commit is mechanism that guarantees a distributed transaction either commits on all
involved nodes or rolls back on all involved nodes to maintain data consistency across the global
distributed database. It has two phase, a Prepare Phase and a Commit Phase.
Question 112: Describe two phases of Two-phase commit?
Answer:
Prepare phase - The global coordinator (initiating node) ask a participants to prepare (to promise to
commit or rollback the transaction, even if there is a failure)
Commit - Phase - If all participants respond to the coordinator that they are prepared, the coordinator
asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all
nodes to roll back the transaction.
Question 113: What are the factors causing the reparsing of SQL statements in SGA
Answer:
There are main two causes for reparsing:
1. The objects which the query is referencing has been modified
2. The parsed version of sql text has been aged out of the library cache.
Question 114: How to implement the multiple control files for an existing database
Answer:
1. Edit init.ora file, set control_files parameter with multiple location
2. shutdown immediate
3. copy control file to multiple locations & confirm from init.ora contol_files parameter
4. start the database.
5. run this query for changes confirmation - select name from v$controlfile;
Question 115: What is mean by Program Global Area (PGA)
Answer:
PGA - Program Global Area
or the Process Global Area is a memory region that contains data and control information for a single
server process or a single background process.
The PGA is allocated when a process is created and de-allocated when the process is terminated. PGA is
an area that is used by only one process.
Question 116: What is meant by recursive hints?
Answer:
Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to
the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can
optimize the size of Data Dictionary Cache.
Question 117: Is it possible to use raw devices as data files and what is the advantages over
filesystem files ?
Answer:
Yes.
The advantages over file system files:
I/O will be improved because Oracle will bypass the OS. Disk Corruption will be very less.
Question 118: What are disadvantages of having raw devices?
Answer:
We have to depend on export/import utility for backup/recovery
The tar command cannot be used for physical file backup, instead we have to use dd command which is
less flexible and has limited recoveries.
Question 119. What are the system resources that can be controlled by profile?
Answer:
1. Number of concurrent sessions by user
2. CPU processing time
3. Amount of Logical I/O
4. Amount of Idle time
Question 120. Explain different level of Auditing
Answer:
Statement Auditing
Privilege Auditing
Object Auditing
Question 121. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
Answer:
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is
where the oracle products reside.
Question 122. Name three advisory statistics you can collect.
Answer:
Database Cache Advisory
Shared Pool Advisory
PGA Advisory
Question 123. When a user process fails, what background process cleans up after it?
Answer:
PMON
Question 124. How would you force a log switch?
Answer:
ALTER SYSTEM SWITCH LOGFILE;
Question 125. When creating a user, what permissions must you grant to allow them to connect to
the database?
Answer:
CREATE SESSION
Question 126. What view would you use to determine free space in a tablespace?
Answer:
DBA_FREE_SPACE
Question 127. How would you determine who has added a row to a table?
Answer:
If database auditing is turned ON, query SYS.AUD$ table
Question 128. You have just compiled a PL/SQL package but got errors, how would you view the
errors?
Answer:
SHOW ERRORS
Question 129. How can you enable a trace for a session?
Answer:
ALTER SESSION SET TRACING ON
Question 130. A DBA had to remove some Archivelogs to free up space in filesystem. Now when
the RMAN job starts to backup Archivelogs, it complains about missing Archivelogs that were deleted by
DBA. To resolve the issue and continue backing up remainder of Archivelogs, which RMAN command
can be used so it won't complain about missing Archivelogs.
Answer:
Crosscheck command
Question 131. Which RMAN command is used to create an exact replica of a database in new
host?
Answer:
DUPLICATE DATABASE
Question 132. How do you install STATSPACK?
Answer:
By running $ORACLE_HOME/rdbms/admin/spcreate.sql script
Question 133. Process you follow to start looking into Performance issue at database level (If the
application is running very slow, at what points do you need to go about the database in order to improve
the performance?)
Answer:
Run a TOP command in Unix to see CPU usage (identify CPU killer processes)
Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and memory usage
and possible blocking
Run STATSPACK report to identify:
1. TOP 5 WAIT EVENTS
2. RESOURCE intensive SQL statements
See if STATISTICS on affected tables needs to be re-generated
IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and see whether
new index or use of HINT brings the cost of SQL down.
Question 134. Which is most important v$ view to see performance related information?
Answer:
V$WAITSTAT
Question 135. Explain below wait events in STATSPACK report
DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE
Answer:
DB SCATTERED READ
DB SEQUENTIAL REAL
ENQUEUE
- FULL TABLE SCAN
- IO
- LOCKING
Question 135. List five most important parameter in 9i affecting performance
Answer:
CURSOR_SHARING
DB_CACHE_SIZE
PGA_AGGREGATE_TARGET
DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE
Question 136. What is PGA_AGGREGATE_TARGET?
Answer:
This parameter controls the maximum amount of memory PGA which can be used by the queries when
WORKAREA_SIZE_POLICY is set to Auto.
The value you can be set in Bytes, kilobytes (K), megabytes (M) or gigabytes (G). The default value is 0
This parameter also has an effect on the execution plans of the cost based optimizer. The optimizer uses
the value of the parameter PGA_AGGREGATE_TARGET to derive an estimate for the minimum and
maximum amount of memory which should be available at run-time for each sort, hash-join and bitmap
operator in the query. Based on this minimum and maximum value, the optimizer selects the best plan.
Question 137. How do you analyze table partition using Oracle provided package?
Answer:
DBMS_STATS.GATHER_TABLE_STATS with GRANULARITY => 'PARTITION' OPTION
Question 138. You see a wait on LMS process in statspack, what does that mean?
Answer:
A. Wait is due to Data Guard Broker.
Question 139. Name three advisory statistics you can collect.
Answer:
SHARED_POOL_ADVICE
PGA_TARGET_ADVICE
DB_CACHE_ADVICE
Question 140. Explain procedure to Change CHARACTERSET of a database.
Answer:
Can't change CHARACTERSET of a database, you will need to re-create the database with appropriate
CHARACTERSET.
Question 141. If you had a tablespace, TEST_TABLESPACE, which consists of three files:
TEST01.dbf, TEST02.dbf, and TEST03.dbf, and someone accidentally used the Unix command "rm" to
delete the file TEST02.dbf, what else would you need in order to recover all the data that was present in
TEST_TABLESPACE at the time that TEST02.dbf was deleted?
Answer:
All Archivelogs
Question 142. How do you put database is ARCHIVELOG mode, explain procedure
Answer:
1. Modify init.ora parameter START_ARCHIVE=TRUE
2. SQL> SHUTDOWN IMMEDIATE;
3. STARTUP MOUNT;
4. ALTER DATAVASE ARCHIVELOG;
5. ALTER DATABASE OPEN;
Question 143. How do you create PASSWORD FILE?
Answer:
using orapwd utility
Question 144. How can you tell if an index on particular table is USED or NOT USED in 9i?
Answer:
By turning MONITORING ON that index and querying into INDEX_USAGE table
Question 145. How do you switch from an init.ora file to a spfile?
Answer:
SQL> CREATE SPFILE FROM PFILE;
Question 146. Explain FORCE LOGGING feature in 9i.
Answer:
By setting FORCE LOGGING to TRUE, all transactions will generate REDO. This is used for Data Guard,
so no data will be missed (i.e. transactions that were run with NOLOGGING option) in Standby database
Question 147. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Answer:
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.
Question 148. How do you set up 9i Data Guard?
Answer:
Take a full hot backup of Primary database
Create standby control file
Transfer full backup, init.ora, standby control file to standby node.
Modify init.ora file on standby node.
Restore database
Recover Standby database
(Alternatively, RMAN command DUPLICATE DATABASE FOR STANDBY DO RECOVERY can
be also used)
Setup FAL_CLIENT and FAL_SERVER parameters on both sides
Put Standby database in Managed Recover mode
Question 149. How do you create Physical Standby database?
Answer:
Take a full hot backup of Primary database
Create standby control file
Transfer full backup, init.ora, standby control file to standby node.
Modify init.ora file on standby node.
Restore database
Recover Standby database
(Alternatively, RMAN command DUPLICATE DATABASE FOR STANDBY DO RECOVERY can
be also used)
Setup FAL_CLIENT and FAL_SERVER parameters on both sides
Put Standby database in Managed Recover mode
Question 150. Explain LOG_ARCHIVE_DEST_2 parameter and Dataguard related parameters.
Answer:
log_archive_dest_2='SERVICE=ORACLE_SID_STBY optional lgwr async=20480 noaffirm reopen=15
max_failure=10 net_timeout=30 delay=0'
Question 151. Explain Database SWITCH OVER PROCEDURE
Answer:
On Primary:
alter database commit to switchover to physical standby with session shutdown;
shutdown;
startup nomount;
alter database mount standby database;
recover managed standby database disconnect from session;
On Standby:
alter database commit to switchover to primary;
shutdown;
startup;
Question 152. Exaplain How to Activate STANDBY Database
Answer:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
SHUTDOWN IMMEDIATE;
STARTUP;
Question 153. What do you know about VCS?
Answer:
VCS stands for Veritas Cluster Software.
VCS is used as a High Availability solution in case of host failure. There will be typically two nodes in a
cluster and database will be running on one node. Incase of host failure, VCS will failover database
(service group) to second node.
Question 154. What are typical resources in VCS service group?
Answer:
In typical VCS configuration, there are three main resources required for Oracle database failover.
1. Oracle Database
2. Listener
3. Filesystems
Question 155. Which file contains VCS service group and resource information?
Answer:
Main.cf
Question 156. What database related information we need to set in VCS?
Answer:
ORACLE_SID
LISTENER name
LISTENER password (if used)
Oracle filesystems that will be failed over
Location to init.ora/spfile if not available at default location
Question 157. What is VCS in-depth monitoring?
Answer:
In VCS in-depth monitoring, a database user (VCSMON) will be created which will login to database at
certain interval (i.e. every 5 minute) to see if database connectivity is OK. For some reason, if this user
can t login VCS will fail over the database to second node.
Question 158. In ACTIVE VCS environment, you need to recycle the database but doesn’t want
database to failover to other node. What you must do before shutting down the database, so it doesn t
failover to other node?
Answer:
You need to FREEZE the service group.
Alternatively, you can make Oracle resource NON-CRITICAL (not recommended)
Question 159. What if you accidentally shutdown the LISTENER in VCS environment?
Answer:
If Listener Resource is marked CRITICAL, and Restart Attempt is set to 0, then Service Group will failover
to second node.
It is recommended for Listener resource to set RESTART ATTEMPTS value to 3.
Question 160. Explain Automatic Segment Space Management
Answer:
Automatic Segment Space Management features was introduced in 9i which simplifies management of
free space usage within object for below parameter:
FREELIST
FREELIST GROUPS
PCTUSED
Question 161. How do you REBUILD index online and Compute Statistics at the same time?
Answer:
ALTER INDEX INDEX_NAME REBUILD COMPUTE STATISTICS ONLINE;
Question 162. Explain Bitmap Join Indexes
Answer:
Bitmap Join Indexes pre-stores results of a join and can avoid an expensive join operation at runtime.
Question 162. Which parameters can be modified dynamically as part of 9i Dynamic Memory
Management feature?
Answer:
Below parameters can be modified dynamically as part of 9i Dynamic Memory Management feature
DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
Question 163. What additional statistics are gathered by DBMS_STATS that were not available in
earlier version or by using ANALYE command?
Answer:
In 9i DBMS_STATS package now collects below statistics as well:
CPU Usage
System I/O
Question 163. Explain Different values supported by CURSOR_SHARING parameter and its
explanation
Answer:
Below values are supported by CURSOR_SHARING init.ora parameter:
FORCE - Literals will be replaced by system generated bind variables where possible
SIMILAR - Oracle determines which literals are "safe" for substitution with bind variables. This will result
in some SQL not being shared in an attempt to provide a more efficient execution plan.
EXACT - Only allows statements with identical text to share the same cursor
Question 163. Name three very important Hit Ratios in database
Answer:
Buffer Cache Hit Ratio
Data Dictionary Hit Ratio
Library Cache Hit Ratio
(Use V$SYSSTAT)
(Use V$ROWCACHE)
(Use V$LIBRARYCACHE, V$SGASTAT)
Question 164. Name Different Latches in database and Dynamic Performance Views to get more
information about Latches
Answer:
Database Latches:
Redo Allocation Latch
Redo Copy Latch
Row Cache Latch
Use V$LATCH, V$LATCHHOLDER, V$LATCHNAME to get more information about the latches
Question 165. In which scenarios you need to set Large Pool?
Answer:
You need to set Large Pool if you are using below:
MTS (Multithreaded Server)
RMAN backups
Question 166. Explain Tuning Process that involves Application, Database, OS, Network
Answer:
In such a scenario, tuning should perform in following order:
1. Business Rules
2. Data Design
3. Application Design
4. Logical Structure of the Database
5. Database Operations
6. Access Path
7. Memory Allocation
8. I/O and Physical Structure of the Database
9. Resource Allocation
10. OS
Question 167. Explain Different Tuning Areas in Database
Answer:
Following areas within database can be tuned:
Memory -
I/O -
CPU
Space Management
Redo & Checkpoint
Rollback -
Shared Pool, Buffer Cache, Redo Buffer, Sort Area Size, PGA, Large Pool
Multiple Database Writer Processes, Distributing I/O, RAID
Extent Allocation, Oracle Block Efficiency
Redo log file configuration, checkpoints
Retention, number of Rollback Segments, Optimal
Question 168. How do you setup Auditing in Database?
Answer:
If audit packages are not installed, run $ORACLE_HOME/rdbms/admin/cataudit.sql script
Modify initialization parameter AUDIT_TRAIL=DB and setup AUDIT_DUMP_DEST
Select what type of operations needs to be audited
View Audit results from SYS.AUD$ table
Question 169. Can you Audit System Operations? If Yes, how?
Answer:
SYS connections can be audited by setting init.ora parameter AUDIT_SYS_OPERATIONS=TRUE
Question 170. How can you setup Encryption in Database?
Answer:
Data within Database can be encrypted and decrypted using package:
DBMS_OBFUSCATION_TOOLKIT
Question 171. Name five parameters can be used for Password Management?
Answer:
Following parameters can be used to manage user password:
1.
2.
3.
4.
5.
FAILED_LOGIN_ATTEMPTS
PASSWORD_GRACE_TIME
PASSWORD_LIFE_TIME
PASSWORD_REUSE_MAX
PASSWORD_REUSE_TIME
Question 172: What is difference between Logical Standby Database and Physical Standby
database?
Answer:
The primary functional difference between logical and physical standby database setups is that logical
standby permits you to add additional objects (tables, indexes, etc) to the database, while physical
standby is always an exact structural duplicate of the master database. The downside, though, is that
logical standby is based on newer technologies (logical standby is new in Oracle 9.2) and tends to be
generally regarded as more temperamental than physical standby.
Technical – UNIX
Every DBA should know something about the operating system that the database will be running on. The
questions here are related to UNIX but you should equally be able to answer questions related to
common Windows environments.
Question 1. How do you list the files in an UNIX directory while also showing hidden files?
Answer:
ls -ltra
Question 2. How do you execute a UNIX command in the background?
Answer:
Use the "&" at the end of command
Question 3. What UNIX command will control the default file permissions when files are created?
Answer:
Umask
Question 4. Explain the read, write, and execute permissions on a UNIX directory.
Answer:
Read allows you to see and list the directory contents.
Write allows you to create, edit and delete files and subdirectories in the directory.
Execute gives you the previous read/write permissions plus allows you to change into the directory and
execute programs or shells from the directory.
Question 5. The difference between a soft link and a hard link?
Answer:
A symbolic (soft) linked file and the targeted file can be located on the same or different file system while
for a hard link they must be located on the same file system.
Question 6. Give the command to display space usage on the UNIX file system.
Answer:
df -lk
Question 7. Explain iostat, vmstat and netstat.
Answer:
Iostat reports on terminal, disk and tape I/O activity.
Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat reports on the contents of network data structures.
Question 8. How would you change all occurrences of a value using VI?
Answer:
Use:%s/<old>/<new>/g
Question 9. Give two UNIX kernel parameters that effect an Oracle install
Answer:
SHMMAX & SHMMNI
Question 10. Briefly, how do you install Oracle software on UNIX.
Answer:
Basically, set up disks, kernel parameters, create oracle user and dba group, and run runinstaller.
Question 11. How do you create a decision tree in a shell script?
Answer:
Depending on shell, usually a case-esac or an if-endif or fi structure
Question 12. What is a pipe and give an example?
Answer:
A pipe is two or more commands separated by pipe char '|'. That tells the shell to arrange for the output of
the preceding command to be passed as input to the following command.
Example : ls -l | pr
The output for a command ls is the standard input of pr.
When a sequence of commands are combined using pipe, then it is called pipeline.
Question 13. What is the difference between > and >> redirection operators?
Answer:
> is the output redirection operator when used it overwrites while >> operator appends into the file.
Question 14. What is the difference between process and thread.
Answer:
Creation of new process requires new resources and Address space whereas the thread can be created
in the same address space of the process which not only saves space and resources but are also easy to
create and delete, and many threads can exists in a process.
Question 15. What is the difference between a shell variable that is exported and the one that is
not exported?
Answer:
export LANG=C
will make the variable LANG the global variable, put it into the global environment. all other processes
can use it.
LANG=C
will change the value only in the current script.
Question 16. How will you list only the empty lines in a file (using grep)
Answer:
grep "^$" filename.txt
Question 17. What is Semaphore?
Answer:
A data object that represents the right to use a limited resource, used for synchronization and
communication between asynchronous processes.
Question 18. How do you execute a UNIX command in the background?
Answer:
You can use & at the end of command or use nohup command
Question 19. How do you check active shared memory segments?
Answer:
ipcs -a
Question 20. How do you check Paging/Swapping in Unix?
Answer:
You can check Paging/Swapping using below commands
vmstat s
prstat s
swap l
sar p
Question 21. How do you check number of CPU installed on Unix server?
Answer:
psrinfot v
Question 22. How do you check Paging/Swapping in Unix?
Answer:
Vmstat s
Prstat s
Swap l
Sar p
Question 1. Explain the difference between a hot backup and a cold backup and the benefits
associated with each.
Answer:
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
Question 2. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
Answer:
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
Question 3. How do you switch from an init.ora file to a spfile?
Answer:
Issue the create spfile from pfile command.
Question 4. Explain the difference between a data block, an extent and a segment.
Answer:
A data block is the smallest unit of logical storage for a database object. As objects grow they take
chunks of additional storage that are composed of contiguous data blocks. These groupings of
contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
Question 5. Give two examples of how you might determine the structure of the table DEPT.
Answer:
Use the describe command or use the dbms_metadata.get_ddl package.
Question 6. Where would you look for errors from the database engine?
Answer:
In the alert log.
Question 7. Compare and contrast TRUNCATE and DELETE for a table.
Answer:
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table.
The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
Question 8. Give the reasoning behind using an index.
Answer:
Faster access to data blocks in a table.
Question 9. Give the two types of tables involved in producing a star schema and the type of data they hold.
Answer:
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
Question 10. What type of index should you use on a fact table?
Answer:
A Bitmap index.
Question 11. Give two examples of referential integrity constraints.
Answer:
A primary key and a foreign key.
Question 12. A table is classified as a parent table and you want to drop and re-create it. How
would you do this without affecting the children tables?
Answer:
Disable the foreign key constraint to the parent, drop the table, re-create the table, and enable the foreign key constraint.
Question 13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG
mode and the benefits and disadvantages to each.
Answer:
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
Question 14. What command would you use to create a backup control file?
Answer:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Question 15. Give the stages of instance startup to a usable state where normal users may access
it.
Answer:
STARTUP NOMOUNT - Instance startup
ALTER DATABASE MOUNT - The database is mounted
ALTER DATABASE OPEN - The database is opened
Question 16. What column differentiates the V$ views to the GV$ views and how?
Answer:
The INST_ID column which indicates the instance in a RAC environment the information came from.
Question 17. How would you go about generating an EXPLAIN plan?
Answer:
Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql
Question 18. How would you go about increasing the buffer cache hit ratio?
Answer:
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.
Question 19. Explain an ORA-01555
Answer:
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing
the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the
application getting the error message.
Question 20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
Answer:
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is
where the oracle products reside.
Question 21. How would you determine the time zone under which a database was operating?
Answer:
select DBTIMEZONE from dual;
Question 22. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Answer:
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.
Question 23. What command would you use to encrypt a PL/SQL application?
Answer:
WRAP
Question 24. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Answer:
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that
carries a single task. While a procedure does not have to return any values to the calling application, a
function will return a single value. A package on the other hand is a collection of functions and procedures
that are grouped together based on their commonality to a business function or application.
Question 25. Explain the use of table functions.
Answer:
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.
Question 26. Name three advisory statistics you can collect.
Answer:
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
Question 27. Where in the Oracle directory tree structure are audit traces placed?
Answer:
In unix $ORACLE_HOME/rdbms/audit,
in Windows the event viewer
Question 28. Explain materialized views and how they are used.
Answer:
Materialized views are objects that are reduced sets of information that have been summarized, grouped,
or aggregated from base tables. They are typically used in data warehouse or decision support systems.
Question 29. When a user process fails, what background process cleans up after it?
Answer:
PMON
Question 30. What background process refreshes materialized views?
Answer:
The Job Queue Processes.
Question 31. How would you determine what sessions are connected and what resources they
are waiting for?
Answer:
Use of V$SESSION and V$SESSION_WAIT
Question Question 32. Describe what redo logs are.
Answer:
Redo logs are logical and physical structures that are designed to hold all the changes made to a
database and are intended to aid in the recovery of a database.
Question 33. How would you force a log switch?
Answer:
ALTER SYSTEM SWITCH LOGFILE;
Question 34. Give two methods you could use to determine what DDL changes have been made.
Answer:
You could use Logminer or Streams
Question 35. What does coalescing a tablespace do?
Answer:
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining
neighboring free extents into large single extents.
Question 36. What is the difference between a TEMPORARY tablespace and a PERMANENT
tablespace?
Answer:
A temporary tablespace is used for temporary objects such as sort structures while permanent
tablespaces are used to store those objects meant to be used as the true objects of the database.
Question 37. Name a tablespace automatically created when you create a database.
Answer:
The SYSTEM tablespace.
Question 38. When creating a user, what permissions must you grant to allow them to
connect to the database?
Answer:
Grant the CONNECT to the user.
Question 39. How do you add a data file to a tablespace?
Answer:
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>
Question 40. How do you resize a data file?
Answer:
ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;
Question 41. What view would you use to look at the size of a data file?
Answer:
DBA_DATA_FILES
Question 42. What view would you use to determine free space in a tablespace?
Answer:
DBA_FREE_SPACE
Question 43. How would you determine who has added a row to a table?
Answer:
Turn on fine grain auditing for the table.
Question 44. How can you rebuild an index?
Answer:
ALTER INDEX <index_name> REBUILD ONLINE;
Question 45. Explain Partitioning and benefits of using Partioning.
Answer:
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more
manageable pieces.
Question 46. You have just compiled a PL/SQL package but got errors, how would you view the
errors?
Answer:
SHOW ERRORS
Question 47. How can you gather statistics on a table?
Answer:
The ANALYZE command or DBMS_STAT package.
Question 48. How can you enable a trace for a session?
Answer:
Use the DBMS_SESSION.SET_SQL_TRACE or Use
ALTER SESSION SET SQL_TRACE = TRUE;
Question 49. What is the difference between the SQL*Loader and IMPORT utilities?
Answer:
These two Oracle utilities are used for loading data into the database. The difference is that the import
utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility
allows data to be loaded that has been produced by other utilities from different data sources just so long
as it conforms to ASCII formatted or delimited files.
Question 50. Name two files used for network connection to a database.
Answer:
TNSNAMES.ORA and SQLNET.ORA
Question 51. Explain difference between Oracle Database and Instance
Answer:
Oracle database consist of following:
Datafiles, Control Files, Redo Log Files
Oracle instance consist of following:
SGA and Oracle processes (DBWR, LGWR, SMON, PMON, ARCH, CKPT, etc.)
Question 52. What is Mutating Table error?
Answer:
Mutating table error happens when you try to modify the same row you are using it. Use temp table to
avoid this error.
Question 53. What is correlated sub-query?
Answer:
Correlated sub-query is a sub-query, which has reference to the main query
Question 54. Which one of the below change will decrease PAGING/SWAPPING
Answer:
1. INCREASE SORT_AREA_RETAINED_SIZE
2. DECREASE SHARED_POOL_SIZE (correct answer)
3. DECREASE OPEN_CURSORS parameters
4. INCREASE DB_CACHE_SIZE
Question 55. Explain steps involved in Migration from Oracle 9.0.1 to 9.2.0.6
Answer:
1. Shutdown 9.0.1 database
2. Apply software patch to new/old ORACLE_HOME
3. Change ORACLE_HOME to point to new 9.2.0.6 binaries
4. STARTUP MIGRATE
5. @ORACLE_HOME/rdbms/admin/catpatch.sql
6. @ORACLE_HOME/rdbms/admin/utlrp.sql
7. Change init.ora parameter COMPATIBLE = 9.2.0.6
8. Shutdown and startup normal
Question 56. Can you create database under any Unix userid besides oracle?
Answer:
Yes. You can create database user other then oracle as long as that user is part of DBA group in unix
Question 57. Locally Managed TEMP Tablespace is 100% FULL and there is no space available to
add datafile to increase TEMP tablespace. What can you do that might free up TEMP space?
Answer:
You can try one of the following to free up TEMP space
1. Issue ALTTER TABLESPACE PCTINCREASE 1 followed by
ALTTER TABLESPACE PCTINCREASE 0 command;
2. Close some of the idle sessions connected to the database
Question 58. List five most important features of Oracle 9i
Answer:
1. AUTOMATIC UNDO MANAGEMEND
2. AUTOMATICE PGA MEMORY MANAGEMENT
3. MULTI-TABLE INSERT statements
4. EXTERNAL TABLES
5. DYNAMIC MEMORY MANAGEMENT
Question 59. Name five top 9I init.ora parameters affecting performance
Answer:
1. CURSOR_SHARING
2. DB_CACHE_SIZE
3. PGA_AGGREGATE_TARGET
4. WORKAREA_SIZE_POLICY
5. DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE
Question 60. How do you recover database when you lost all of your control files?
Answer:
In case of loss of loss of all control files, you can still recover database as long as you have all
archivelog files. You can issue following command to recover the database.
RECOVER DATABASE USING BACKUP CONTROLFILES UNTIL CANCEL;
Apply all archivelog files
ALTER DATABASE OPEN RESETLOGS;
SHUTDOWN IMMEDIATE;
Backup database (COLD)
STARTUP;
Question 61. What is main purpose of CHECKPOINT?
Answer:
A Checkpoint is a database event, which synchronizes the data blocks in memory with the datafiles on disk.
A checkpoint has two purposes:
1. to establish data consistency
2. Enable faster database recovery.
Question 62. You got a call from Application team saying Application is running very SLOW.
Where do you start looking first?
Answer:
Below are some of very important you should gather to identify the root cause of slowness in
application/database.
Run a TOP command in unix to see CPU usage (identify CPU killer processes)
Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and
memory usage and possible blocking
Run STATSPACK report to identify:
a. TOP 5 WAIT EVENTS
b. RESOURCE intensive SQL statements
See if STATISTICS on affected tables needs to be re-generated
IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and
see whether new index or use of HINT brings the cost of SQL down.
Question 63. How to SWITCH from PRIMARY to PHYSICAL STANDBY in 9i
Answer:
Perform below on Primary DB:
1. ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH
SESSION SHUTDOWN;
2. SHUTDOWN IMMEDIATE;
3. STARTUP NOMOUNT;
4. ALTER DATABASE MOUNT STANDBY DATABASE;
5. RECOVER MANAGED STANDBY DATABASE DICONNECT FROM SESSION;
6. ALTER SYSEM SET LOG_ARCHIVE_DEST_2_STATUS= DEFER SCOPE=SPFILE;
Perform below on STANDBY DB:
7. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
8. SHUTDOWN IMMEDIATE;
9. STARTUP;
10. ALTER SYSEM SET LOG_ARCHIVE_DEST_2_STATUS= ENABLE SCOPE=SPFILE;
At this time Standby becomes Primary and Primary becomes new Standby.
Question 64. How to ACTIVATE PHYSICAL STANDBY database in 9i
Answer:
Perform below on Primary DB if available to transfer all pending archive logs to standby:
1. ALTER SYSTEM SWITCH LOGFILE;
2. ALTER SYSTEM SWITCH LOGFILE;
Perform below on STANDBY DB:
1. ALTER DATABASE ACTIVATE STANDBY DATABASE;
2. SHUTDOWN IMMEDIATE;
3. STARTUP
4. Add TEMP file if needed.
At this time Standby database becomes PRIMARY database and relationship between Primary and
Standby has been lost by activating standby database.
Question 65. Explain different protection mode in DATAGURD
Answer:
MAXIMUM PROTECTION
MAXIMUM AVAILIBILITY
MAXIMUM PERFORMANCE
Question 66. What is CASCADING STANDBY database?
Answer:
A CASCADING STANDBY is a standby database that receives its REDO information from another
STANDBY database (not from Primary database).
Question 67. What are some of the dba tables you query to find out about UNDO segments?
Answer:
You would query:
DBA_UNDO_SEGS
V$UNDOSTAT
Question 68. What does Block Media Recovery (BMR) do?
Answer:
BMR is responsible for restore and recover of specified BLOCK
Question 69. What is the difference between Fine Grained Audit (FGA) and Fine Grained Access
Control (FGAC)?
Answer:
FGA tracks when sensitive rows have been accesses, where FGAC prevents access to sensitive rows
Question 70. Which dynamic performance view DBA can query to see who deleted data from a
particular table sometime back?
Answer:
V$LOGMNR_CONTENTS
Question 71. What do you use DBMS_REDEFINITION package for?
Answer:
DBMS_REDEFINITION package is used to perform an ONLINE REBUILD of a table
Question 72. How do you add second or subsequent BLOCK SIZE to an existing database?
Answer:
Re-Create the CONTROLFILE to specify the new BLOCK SIZE for specific data files
Or Take the database OFFLINE, and the bring back online with a new BLOCK SIZE
specification
Question 73. What are new RMAN features in 9i?
Answer:
It allows you to set retention period of backups
It enables you to store the CHANNEL attributes so you do not have to specify in each
BACKUP or RESTORE command
Question 74, Explain different PARTITIONING options available in 9i
Answer:
Range Partitioning Used when there are logical ranges of data. Possible usage: dates, part numbers,
and serial numbers.
Hash Partitioning Used to spread data evenly over partitions. Possible usage: data has no logical
groupings.
List Partitioning Used to list together unrelated data into partitions. Possible usage: a number of states
list partitioned into a region.
Composite Range-Hash Partitioning Used to range partition first, then spreads data into hash partitions.
Possible usage: range partition by date of birth then hash partition by name; store the results into the
hash partitions.
Composite Range-List Partitioning Used to range partition first, then spreads data into list partitions.
Possible usage: range partition by date of birth then list partition by state, then store the results into the
list partitions.
Question 75. How to convert a Single Instance 9i database to a Real Application Clusters 9i (RAC)
database without using export/import tools.
Answer:
1. Make a full database backup before you change anything
2. Copy the existing $ORACLE_HOME/dbs/init<SID1>.ora to
$ORACLE_HOME/dbs/init<db_name>.ora. Add the following parameters to
$ORACLE_HOME/dbs/init<db_name>.ora:
a. *.cluster_database = TRUE
b. *.cluster_database_instances = 2
c. *.undo_management=AUTO (Add if you don't have it )
d. <SID1>.undo_tablespace=undotbs (undo tablespace which already exists)
e. <SID1>.instance_name=RAC1
f. <SID1>.instance_number=1
g. <SID1>.thread=1
h. <SID1>.local_listener=LISTENER_RAC1
3. Open your database and run $ORACLE_HOME/rdbms/admin/catclust.sql to create cluster
database specific views within the existing instance
4. Recreate control file if you defined maxinstances to be 1 when you created the single instance
database
5. Add instance specific parameters in the init<db_name>.ora for the second instance on the
second node and set appropriate values for it
a. <SID2>.instance_name=RAC2
b. <SID2>.instance_number=2
c. <SID2>.local_listener=LISTENER_RAC2
d. <SID2>.thread=2
e. <SID2>.undo_tablespace=UNDOTBS2
f. <SID2>.cluster_database = TRUE
g. <SID2>.cluster_database_instances = 2
6. From the first instance, mount the database and run the following command
alter database
add logfile thread 2
group 3 ('/dev/RAC/redo2_01_100.dbf') size 100M,
group 4 ('/dev/RAC/redo2_02_100.dbf') size 100M;
alter database enable public thread 2;
7. Create a second Undo Tablespace from the existing instance
8. Set ORACLE_SID and ORACLE_HOME environment variables on the second node and
START second instance.
Question 76. Explain RAC and advantages of using RAC
Answer:
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.
Advantages of RAC:
Improved throughput
Scalability over single instance systems
Improved response time
High availability
Transparency
Question 77. Explain Row Locking and Multi-version Read Consistency in RAC
Answer:
Oracle's row locking feature allows multiple transactions from separate nodes to lock and update
different rows of the same data block. This is done without any of the transactions waiting for the others
to commit. If a row has been modified but not yet committed, then the original row values are available to
all instances for read access. This is called multi-version read consistency.
Question 78. What is an External table introduced in 9i?
Answer:
Oracle9i introduces external tables, which provide a mechanism to view data stored in external sources
as if it were a table in the database. This ability to read external data provides a more straightforward
method of loading and transforming data from external sources. Administrators no longer need to reserve
space inside the database for staging tables or write external programs to transform the data outside of
the database environment. By making it no longer necessary to stage data in the Oracle database,
Oracle9i's external tables have essentially streamlined the ETL function by merging the transformation
and loading processes.
Question 79. What is SPFILE?
Answer:
The SPFILE (server parameter file) is Oracle's new method of maintaining database parameters. The old
method of editing a text based parameter file (INIT.ORA) has given way to the new method of maintaining
persistent parameters. By this Oracle means that you can change a system parameter and have its value
be maintained across shutdown and startup. This is a great savings from the past where you had to issue
the ALTER SYSTEM command and then remember to edit the INIT.ORA parameter file. Here is how to
create SPFILE:
SQL> CREATE SPFILE [='spfile_name'] FROM PFILE [='pfile_name'];
SQL> CREATE PFILE [='pfile_name'] FROM SPFILE [='spfile_name'];
SQL> create spfile from pfile;
Usage:
1. SQL> ALTER SYSTEM SET DB_CACHE_SIZE=1000M SCOPE=SPFILE;
Question 80. How do you backup and restore using Transportable Tablespaces
Answer:
1.
2.
3.
4.
5.
6.
7.
Run DBMS_TTS against the tablespace to see if tablespace is self contained or not.
Make tablespace Read Only.
Export Metadata (EXP TRANSPORT_TABLESPACES=Y TABLESPACE=ts1)
Copy data file to target host
Copy export dump to target
Import the Metadata (IMP TRANSPORT_TABLESPACES=Y DATAFILES (file1,file2) )
Bring Tablespace ONLINE and enable SOURCE tablespace to READ WRITE
Question 81. Explain different type of Database Failures
Answer:
Statement failure failed SQL is automatically rolled back and an error is returned to user.
User Process failure abnormal disconnect PMON detects and rolls back and releases
locks.
User Error (drop table, data) DBA is required to recover data (import or incomplete
recovery)
Media Failure Loss or corruption of files DBA needs to apply appropriate recovery.
Instance Failure Abnormal shutdown Instance simply needs restarted, SMON auto
recovers by:
Rolling forward changes in the redo log not recorded in the data files before Open of
database.
Rollbacks can occur after the database is open, when block data is requested.
Question 82. What does RESETLOGS option do?
Answer:
1. Creates a new incarnation of the database, putting a new SCN in all data file headers.
2. Reset Log Sequence number to 1
3. Reformats ONLINE REDO LOGFILES if they exists
Question 83. Name 5 system views that can be used to retrieve information about backup and
recovery
Answer:
1.
2.
3.
4.
5.
V$BACKUP
V$DATAFILE_HEADER
V$RECOVER_FILE
V$RECOVERY_LOG
V$RECOVERY_STATUS
Question 84. What is the quickest way to clone a database give your backup is done via RMAN?
Answer:
Using RMAN command DUPLICATE DATABASE
Question 85. What is the use of the RESUMABLE parameter in EXPORT?
Answer:
The RESUMABLE parameter allows the export to
1. Suspend if a space allocation issue occurs
2. Wait until the space allocation issue is solved
3. Then resume and therefore not to abort, provided a timeout is set appropriately.
Question 86. Name init.ora parameters need to set for Advanced Replication
Answer:
1.
2.
3.
4.
5.
6.
DISTRIBUTED_TRANSACTIONS
GLOBAL_NAMES
JOB_QUEUE_PROCESSES
OPEN_LINKS
PARALLEL_MAX_SERVERS
PARALLEL_MIN_SERVERS
Question 87. Name 5 init.ora parameters that needs to set in Primary database for DataGuard
configuration
Answer:
1.
2.
3.
4.
5.
6.
LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_2_STATE
FAL_CLIENT
FAL_SERVER
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT
Question 88. What is the use of FORCE LOGGING option?
Answer:
By setting FORCE LOGGING to TRUE, all transactions will generate REDO. This is used for Data Guard,
so no data will be missed (i.e. transactions that were run with NOLOGGING option) in Standby database.
Question 89. (On UNIX) When should more than one DB writer process be used? How many
should be used?
Answer:
If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is
not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB
writers should be specified by use of the db_writers initialization parameter.
Question 90. If you have an example table, what is the best way to get sizing data for the
production table implementation?
Answer:
The best way is to analyze the table and then use the data provided in the DBA_TABLES view to get the
average row length and other pertinent data for the calculation. The quick and dirty way is to look at the
number of blocks the table is actually using and ratio the number of rows in the table to its number of
blocks against the number of expected rows.
Question 91. What special Oracle feature allows you to specify how the cost based system treats a
SQL statement?
Answer:
The COST based system allows the use of Hints to control the optimizer path selection. If they can give
some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
Question 92. You want to determine the location of identical rows in a table before attempting to
place a unique index on the table, how can this be done?
Answer:
select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);
Question 93. You are joining a local and a remote table, the network manager complains about the
traffic involved, how can you reduce the network traffic?
Answer:
Push the processing of the remote data to the remote instance by using a view to pre-select the
information for the join. This will result in only the data required for the join being sent across.
Question 94. How do you prevent output from coming to the screen?
Answer:
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output.
This option can be shortened to TERM.
Question 95. You see multiple fragments in the SYSTEM tablespace, what should you check first?
Answer:
Ensure that users don¡¯t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace
assignment by checking the DBA_USERS view.
Question 96. What are some indications that you need to increase the SHARED_POOL_SIZE
parameter?
Answer:
Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily
decreasing performance with all other tuning parameters the same.
Question 97. When should you increase copy latches? What parameters control copy latches?
Answer:
When you get excessive contention for the copy latches as shown by the ¡°redo copy latch hit ratio. You
can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the
number of CPUs on your system.
Question 98. Describe hit ratio as it pertains to the database buffers. What is the difference
between instantaneous and cumulative hit ratio and which should be used for tuning?
Answer:
The hit ratio is a measure of how many times the database was able to read a value from the buffers
verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is
good, less could indicate problems. If you simply take the ratio of existing parameters this will be a
cumulative value since the database started. If you do a comparison between pairs of readings based on
some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an
instantaneous reading gives more valuable data since it will tell you what your instance is doing for the
time it was generated over.
Question 99. What can cause a high value for recursive calls? How can this be fixed?
Answer:
A high value for recursive calls is cause by improper cursor usage, excessive dynamic space
management actions, and or excessive statement re-parses. You need to determine the cause and
correct it By either re-linking applications to hold cursors, use proper space management techniques
(proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.
Question 100. You look at the dba_rollback_segs view and see that there is a large number of
shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a
problem?
Answer:
A large number of small shrinks indicates a need to increase the size of the rollback segment extents.
Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of
the extents and adjust optimal accordingly.
Question 101. You look at the dba_rollback_segs view and see that you have a large number of
wraps is this a problem?
Answer:
A large number of wraps indicates that your extent size for your rollback segments are probably too small.
Increase the size of your extents to reduce the number of wraps. You can look at the average transaction
size in the same view to get the information on transaction size.
Question 102. How many redo logs should you have and how should they be configured for
maximum recoverability?
Answer:
You should have at least three groups of two redo logs with the two logs each on a separate disk spindle
(mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.
Question 103. If the database cannot be bounced, how would you kill a user?
Answer:
Use command:
ALTER SYSTEM KILL SESSION SID,SERIAL# ;
Question 104: What is the frequency of log Updated..?
Answer:
On commit or
On checkpoint or
Redolog buffer is 1/3rd full
Question 105: How do you rename a database?
Answer:
You can change Database name by following below procedure:
1. Alter Database backup control file to trace;
2. Above step will create a text control file in user_dump_dest directory.
3. Change name of the Database in above file and in init.ora file.
4. STARTUP NOMOUNT
5. Run the script that was modified in step 3
6. ALTER DATABASE OPEN RESETLOGS;
Question 106: Is it possible to configure primary server and stand by server on different OS?
Answer:
Answer for this questions is NO. Standby database must be on same version of database and same
version of Operating system.
Question 107: What does database do during mounting process?
Answer:
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.
Question 108: What is a deadlock and Explain
Answer:
A deadlock is a condition where two or more users are waiting for data locked by each other. Oracle
automatically detects a deadlock and resolves them by rolling back one of the statements involved in the
deadlock, thus releasing one set of data locked by that statement. Statement rolled back is usually the
one which detects the deadlock. Deadlocks are mostly caused by explicit locking because oracle does
not do lock escalation and does not use read locks. Multi-table deadlocks can be avoided by locking the
tables in same order in all the applications, thus precluding a deadlock.
Question 109: What are the options available to refresh snapshots?
Answer:
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.
Question 110: What is snapshot log?
Answer:
It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the
same database as master table and is only available for simple snapshots. It should be created before
creating snapshots.
Question 111: What is Two-Phase Commit?
Answer:
Two-phase commit is mechanism that guarantees a distributed transaction either commits on all
involved nodes or rolls back on all involved nodes to maintain data consistency across the global
distributed database. It has two phase, a Prepare Phase and a Commit Phase.
Question 112: Describe two phases of Two-phase commit?
Answer:
Prepare phase - The global coordinator (initiating node) ask a participants to prepare (to promise to
commit or rollback the transaction, even if there is a failure)
Commit - Phase - If all participants respond to the coordinator that they are prepared, the coordinator
asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all
nodes to roll back the transaction.
Question 113: What are the factors causing the reparsing of SQL statements in SGA
Answer:
There are main two causes for reparsing:
1. The objects which the query is referencing has been modified
2. The parsed version of sql text has been aged out of the library cache.
Question 114: How to implement the multiple control files for an existing database
Answer:
1. Edit init.ora file, set control_files parameter with multiple location
2. shutdown immediate
3. copy control file to multiple locations & confirm from init.ora contol_files parameter
4. start the database.
5. run this query for changes confirmation - select name from v$controlfile;
Question 115: What is mean by Program Global Area (PGA)
Answer:
PGA - Program Global Area
or the Process Global Area is a memory region that contains data and control information for a single
server process or a single background process.
The PGA is allocated when a process is created and de-allocated when the process is terminated. PGA is
an area that is used by only one process.
Question 116: What is meant by recursive hints?
Answer:
Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to
the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can
optimize the size of Data Dictionary Cache.
Question 117: Is it possible to use raw devices as data files and what is the advantages over
filesystem files ?
Answer:
Yes.
The advantages over file system files:
I/O will be improved because Oracle will bypass the OS. Disk Corruption will be very less.
Question 118: What are disadvantages of having raw devices?
Answer:
We have to depend on export/import utility for backup/recovery
The tar command cannot be used for physical file backup, instead we have to use dd command which is
less flexible and has limited recoveries.
Question 119. What are the system resources that can be controlled by profile?
Answer:
1. Number of concurrent sessions by user
2. CPU processing time
3. Amount of Logical I/O
4. Amount of Idle time
Question 120. Explain different level of Auditing
Answer:
Statement Auditing
Privilege Auditing
Object Auditing
Question 121. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
Answer:
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is
where the oracle products reside.
Question 122. Name three advisory statistics you can collect.
Answer:
Database Cache Advisory
Shared Pool Advisory
PGA Advisory
Question 123. When a user process fails, what background process cleans up after it?
Answer:
PMON
Question 124. How would you force a log switch?
Answer:
ALTER SYSTEM SWITCH LOGFILE;
Question 125. When creating a user, what permissions must you grant to allow them to connect to
the database?
Answer:
CREATE SESSION
Question 126. What view would you use to determine free space in a tablespace?
Answer:
DBA_FREE_SPACE
Question 127. How would you determine who has added a row to a table?
Answer:
If database auditing is turned ON, query SYS.AUD$ table
Question 128. You have just compiled a PL/SQL package but got errors, how would you view the
errors?
Answer:
SHOW ERRORS
Question 129. How can you enable a trace for a session?
Answer:
ALTER SESSION SET TRACING ON
Question 130. A DBA had to remove some Archivelogs to free up space in filesystem. Now when
the RMAN job starts to backup Archivelogs, it complains about missing Archivelogs that were deleted by
DBA. To resolve the issue and continue backing up remainder of Archivelogs, which RMAN command
can be used so it won't complain about missing Archivelogs.
Answer:
Crosscheck command
Question 131. Which RMAN command is used to create an exact replica of a database in new
host?
Answer:
DUPLICATE DATABASE
Question 132. How do you install STATSPACK?
Answer:
By running $ORACLE_HOME/rdbms/admin/spcreate.sql script
Question 133. Process you follow to start looking into Performance issue at database level (If the
application is running very slow, at what points do you need to go about the database in order to improve
the performance?)
Answer:
Run a TOP command in Unix to see CPU usage (identify CPU killer processes)
Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and memory usage
and possible blocking
Run STATSPACK report to identify:
1. TOP 5 WAIT EVENTS
2. RESOURCE intensive SQL statements
See if STATISTICS on affected tables needs to be re-generated
IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and see whether
new index or use of HINT brings the cost of SQL down.
Question 134. Which is most important v$ view to see performance related information?
Answer:
V$WAITSTAT
Question 135. Explain below wait events in STATSPACK report
DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE
Answer:
DB SCATTERED READ
DB SEQUENTIAL REAL
ENQUEUE
- FULL TABLE SCAN
- IO
- LOCKING
Question 135. List five most important parameter in 9i affecting performance
Answer:
CURSOR_SHARING
DB_CACHE_SIZE
PGA_AGGREGATE_TARGET
DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE
Question 136. What is PGA_AGGREGATE_TARGET?
Answer:
This parameter controls the maximum amount of memory PGA which can be used by the queries when
WORKAREA_SIZE_POLICY is set to Auto.
The value you can be set in Bytes, kilobytes (K), megabytes (M) or gigabytes (G). The default value is 0
This parameter also has an effect on the execution plans of the cost based optimizer. The optimizer uses
the value of the parameter PGA_AGGREGATE_TARGET to derive an estimate for the minimum and
maximum amount of memory which should be available at run-time for each sort, hash-join and bitmap
operator in the query. Based on this minimum and maximum value, the optimizer selects the best plan.
Question 137. How do you analyze table partition using Oracle provided package?
Answer:
DBMS_STATS.GATHER_TABLE_STATS with GRANULARITY => 'PARTITION' OPTION
Question 138. You see a wait on LMS process in statspack, what does that mean?
Answer:
A. Wait is due to Data Guard Broker.
Question 139. Name three advisory statistics you can collect.
Answer:
SHARED_POOL_ADVICE
PGA_TARGET_ADVICE
DB_CACHE_ADVICE
Question 140. Explain procedure to Change CHARACTERSET of a database.
Answer:
Can't change CHARACTERSET of a database, you will need to re-create the database with appropriate
CHARACTERSET.
Question 141. If you had a tablespace, TEST_TABLESPACE, which consists of three files:
TEST01.dbf, TEST02.dbf, and TEST03.dbf, and someone accidentally used the Unix command "rm" to
delete the file TEST02.dbf, what else would you need in order to recover all the data that was present in
TEST_TABLESPACE at the time that TEST02.dbf was deleted?
Answer:
All Archivelogs
Question 142. How do you put database is ARCHIVELOG mode, explain procedure
Answer:
1. Modify init.ora parameter START_ARCHIVE=TRUE
2. SQL> SHUTDOWN IMMEDIATE;
3. STARTUP MOUNT;
4. ALTER DATAVASE ARCHIVELOG;
5. ALTER DATABASE OPEN;
Question 143. How do you create PASSWORD FILE?
Answer:
using orapwd utility
Question 144. How can you tell if an index on particular table is USED or NOT USED in 9i?
Answer:
By turning MONITORING ON that index and querying into INDEX_USAGE table
Question 145. How do you switch from an init.ora file to a spfile?
Answer:
SQL> CREATE SPFILE FROM PFILE;
Question 146. Explain FORCE LOGGING feature in 9i.
Answer:
By setting FORCE LOGGING to TRUE, all transactions will generate REDO. This is used for Data Guard,
so no data will be missed (i.e. transactions that were run with NOLOGGING option) in Standby database
Question 147. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Answer:
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.
Question 148. How do you set up 9i Data Guard?
Answer:
Take a full hot backup of Primary database
Create standby control file
Transfer full backup, init.ora, standby control file to standby node.
Modify init.ora file on standby node.
Restore database
Recover Standby database
(Alternatively, RMAN command DUPLICATE DATABASE FOR STANDBY DO RECOVERY can
be also used)
Setup FAL_CLIENT and FAL_SERVER parameters on both sides
Put Standby database in Managed Recover mode
Question 149. How do you create Physical Standby database?
Answer:
Take a full hot backup of Primary database
Create standby control file
Transfer full backup, init.ora, standby control file to standby node.
Modify init.ora file on standby node.
Restore database
Recover Standby database
(Alternatively, RMAN command DUPLICATE DATABASE FOR STANDBY DO RECOVERY can
be also used)
Setup FAL_CLIENT and FAL_SERVER parameters on both sides
Put Standby database in Managed Recover mode
Question 150. Explain LOG_ARCHIVE_DEST_2 parameter and Dataguard related parameters.
Answer:
log_archive_dest_2='SERVICE=ORACLE_SID_STBY optional lgwr async=20480 noaffirm reopen=15
max_failure=10 net_timeout=30 delay=0'
Question 151. Explain Database SWITCH OVER PROCEDURE
Answer:
On Primary:
alter database commit to switchover to physical standby with session shutdown;
shutdown;
startup nomount;
alter database mount standby database;
recover managed standby database disconnect from session;
On Standby:
alter database commit to switchover to primary;
shutdown;
startup;
Question 152. Exaplain How to Activate STANDBY Database
Answer:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
SHUTDOWN IMMEDIATE;
STARTUP;
Question 153. What do you know about VCS?
Answer:
VCS stands for Veritas Cluster Software.
VCS is used as a High Availability solution in case of host failure. There will be typically two nodes in a
cluster and database will be running on one node. Incase of host failure, VCS will failover database
(service group) to second node.
Question 154. What are typical resources in VCS service group?
Answer:
In typical VCS configuration, there are three main resources required for Oracle database failover.
1. Oracle Database
2. Listener
3. Filesystems
Question 155. Which file contains VCS service group and resource information?
Answer:
Main.cf
Question 156. What database related information we need to set in VCS?
Answer:
ORACLE_SID
LISTENER name
LISTENER password (if used)
Oracle filesystems that will be failed over
Location to init.ora/spfile if not available at default location
Question 157. What is VCS in-depth monitoring?
Answer:
In VCS in-depth monitoring, a database user (VCSMON) will be created which will login to database at
certain interval (i.e. every 5 minute) to see if database connectivity is OK. For some reason, if this user
can t login VCS will fail over the database to second node.
Question 158. In ACTIVE VCS environment, you need to recycle the database but doesn’t want
database to failover to other node. What you must do before shutting down the database, so it doesn t
failover to other node?
Answer:
You need to FREEZE the service group.
Alternatively, you can make Oracle resource NON-CRITICAL (not recommended)
Question 159. What if you accidentally shutdown the LISTENER in VCS environment?
Answer:
If Listener Resource is marked CRITICAL, and Restart Attempt is set to 0, then Service Group will failover
to second node.
It is recommended for Listener resource to set RESTART ATTEMPTS value to 3.
Question 160. Explain Automatic Segment Space Management
Answer:
Automatic Segment Space Management features was introduced in 9i which simplifies management of
free space usage within object for below parameter:
FREELIST
FREELIST GROUPS
PCTUSED
Question 161. How do you REBUILD index online and Compute Statistics at the same time?
Answer:
ALTER INDEX INDEX_NAME REBUILD COMPUTE STATISTICS ONLINE;
Question 162. Explain Bitmap Join Indexes
Answer:
Bitmap Join Indexes pre-stores results of a join and can avoid an expensive join operation at runtime.
Question 162. Which parameters can be modified dynamically as part of 9i Dynamic Memory
Management feature?
Answer:
Below parameters can be modified dynamically as part of 9i Dynamic Memory Management feature
DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
Question 163. What additional statistics are gathered by DBMS_STATS that were not available in
earlier version or by using ANALYE command?
Answer:
In 9i DBMS_STATS package now collects below statistics as well:
CPU Usage
System I/O
Question 163. Explain Different values supported by CURSOR_SHARING parameter and its
explanation
Answer:
Below values are supported by CURSOR_SHARING init.ora parameter:
FORCE - Literals will be replaced by system generated bind variables where possible
SIMILAR - Oracle determines which literals are "safe" for substitution with bind variables. This will result
in some SQL not being shared in an attempt to provide a more efficient execution plan.
EXACT - Only allows statements with identical text to share the same cursor
Question 163. Name three very important Hit Ratios in database
Answer:
Buffer Cache Hit Ratio
Data Dictionary Hit Ratio
Library Cache Hit Ratio
(Use V$SYSSTAT)
(Use V$ROWCACHE)
(Use V$LIBRARYCACHE, V$SGASTAT)
Question 164. Name Different Latches in database and Dynamic Performance Views to get more
information about Latches
Answer:
Database Latches:
Redo Allocation Latch
Redo Copy Latch
Row Cache Latch
Use V$LATCH, V$LATCHHOLDER, V$LATCHNAME to get more information about the latches
Question 165. In which scenarios you need to set Large Pool?
Answer:
You need to set Large Pool if you are using below:
MTS (Multithreaded Server)
RMAN backups
Question 166. Explain Tuning Process that involves Application, Database, OS, Network
Answer:
In such a scenario, tuning should perform in following order:
1. Business Rules
2. Data Design
3. Application Design
4. Logical Structure of the Database
5. Database Operations
6. Access Path
7. Memory Allocation
8. I/O and Physical Structure of the Database
9. Resource Allocation
10. OS
Question 167. Explain Different Tuning Areas in Database
Answer:
Following areas within database can be tuned:
Memory -
I/O -
CPU
Space Management
Redo & Checkpoint
Rollback -
Shared Pool, Buffer Cache, Redo Buffer, Sort Area Size, PGA, Large Pool
Multiple Database Writer Processes, Distributing I/O, RAID
Extent Allocation, Oracle Block Efficiency
Redo log file configuration, checkpoints
Retention, number of Rollback Segments, Optimal
Question 168. How do you setup Auditing in Database?
Answer:
If audit packages are not installed, run $ORACLE_HOME/rdbms/admin/cataudit.sql script
Modify initialization parameter AUDIT_TRAIL=DB and setup AUDIT_DUMP_DEST
Select what type of operations needs to be audited
View Audit results from SYS.AUD$ table
Question 169. Can you Audit System Operations? If Yes, how?
Answer:
SYS connections can be audited by setting init.ora parameter AUDIT_SYS_OPERATIONS=TRUE
Question 170. How can you setup Encryption in Database?
Answer:
Data within Database can be encrypted and decrypted using package:
DBMS_OBFUSCATION_TOOLKIT
Question 171. Name five parameters can be used for Password Management?
Answer:
Following parameters can be used to manage user password:
1.
2.
3.
4.
5.
FAILED_LOGIN_ATTEMPTS
PASSWORD_GRACE_TIME
PASSWORD_LIFE_TIME
PASSWORD_REUSE_MAX
PASSWORD_REUSE_TIME
Question 172: What is difference between Logical Standby Database and Physical Standby
database?
Answer:
The primary functional difference between logical and physical standby database setups is that logical
standby permits you to add additional objects (tables, indexes, etc) to the database, while physical
standby is always an exact structural duplicate of the master database. The downside, though, is that
logical standby is based on newer technologies (logical standby is new in Oracle 9.2) and tends to be
generally regarded as more temperamental than physical standby.
Technical – UNIX
Every DBA should know something about the operating system that the database will be running on. The
questions here are related to UNIX but you should equally be able to answer questions related to
common Windows environments.
Question 1. How do you list the files in an UNIX directory while also showing hidden files?
Answer:
ls -ltra
Question 2. How do you execute a UNIX command in the background?
Answer:
Use the "&" at the end of command
Question 3. What UNIX command will control the default file permissions when files are created?
Answer:
Umask
Question 4. Explain the read, write, and execute permissions on a UNIX directory.
Answer:
Read allows you to see and list the directory contents.
Write allows you to create, edit and delete files and subdirectories in the directory.
Execute gives you the previous read/write permissions plus allows you to change into the directory and
execute programs or shells from the directory.
Question 5. The difference between a soft link and a hard link?
Answer:
A symbolic (soft) linked file and the targeted file can be located on the same or different file system while
for a hard link they must be located on the same file system.
Question 6. Give the command to display space usage on the UNIX file system.
Answer:
df -lk
Question 7. Explain iostat, vmstat and netstat.
Answer:
Iostat reports on terminal, disk and tape I/O activity.
Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat reports on the contents of network data structures.
Question 8. How would you change all occurrences of a value using VI?
Answer:
Use:%s/<old>/<new>/g
Question 9. Give two UNIX kernel parameters that effect an Oracle install
Answer:
SHMMAX & SHMMNI
Question 10. Briefly, how do you install Oracle software on UNIX.
Answer:
Basically, set up disks, kernel parameters, create oracle user and dba group, and run runinstaller.
Question 11. How do you create a decision tree in a shell script?
Answer:
Depending on shell, usually a case-esac or an if-endif or fi structure
Question 12. What is a pipe and give an example?
Answer:
A pipe is two or more commands separated by pipe char '|'. That tells the shell to arrange for the output of
the preceding command to be passed as input to the following command.
Example : ls -l | pr
The output for a command ls is the standard input of pr.
When a sequence of commands are combined using pipe, then it is called pipeline.
Question 13. What is the difference between > and >> redirection operators?
Answer:
> is the output redirection operator when used it overwrites while >> operator appends into the file.
Question 14. What is the difference between process and thread.
Answer:
Creation of new process requires new resources and Address space whereas the thread can be created
in the same address space of the process which not only saves space and resources but are also easy to
create and delete, and many threads can exists in a process.
Question 15. What is the difference between a shell variable that is exported and the one that is
not exported?
Answer:
export LANG=C
will make the variable LANG the global variable, put it into the global environment. all other processes
can use it.
LANG=C
will change the value only in the current script.
Question 16. How will you list only the empty lines in a file (using grep)
Answer:
grep "^$" filename.txt
Question 17. What is Semaphore?
Answer:
A data object that represents the right to use a limited resource, used for synchronization and
communication between asynchronous processes.
Question 18. How do you execute a UNIX command in the background?
Answer:
You can use & at the end of command or use nohup command
Question 19. How do you check active shared memory segments?
Answer:
ipcs -a
Question 20. How do you check Paging/Swapping in Unix?
Answer:
You can check Paging/Swapping using below commands
vmstat s
prstat s
swap l
sar p
Question 21. How do you check number of CPU installed on Unix server?
Answer:
psrinfot v
Question 22. How do you check Paging/Swapping in Unix?
Answer:
Vmstat s
Prstat s
Swap l
Sar p
Post a Comment