In response for a reader of this blog who asked me for some interview questions for a 3+Year experienced DBA, I have written this series of questions that you can use in your interviews. Either if you are the interviewer, or the interviewer. Enjoy!
1. Basic (Every DBA should answer correctly ALL these questions. This knowledge is just basic for a 3+ year experienced DBA)
1.1 Q- Which are the default passwords of SYSTEM/SYS?
A- MANAGER / CHANGE_ON_INSTALL
1.2 Q- How can you execute a script file in SQLPLUS?
A- To execute a script file in SQLPlus, type @ and then the file name.
1.3 Q- Where can you find official Oracle documentation?
A- tahiti.oracle.com
1.4 Q- What is the address of the Official Oracle Support?
A- metalink.oracle.com or support.oracle.com
1.5 Q- What file will you use to establish Oracle connections from a remote client?
A- tnsnames.ora
1.6 Q- How can you check if the database is accepting connections?
A- lsnrctl status or lsnrctl services
1.7 Q- Which log would you check if a database has a problem?
A- Alert log
1.8 Q- Name three clients to connect with Oracle, for example, SQL Developer:
A- SQL Developer, SQL-Plus, TOAD, dbvisualizer, PL/SQL Developer… There are several, but an experienced dba should know at least three clients.
1.9 Q- How can you check the structure of a table from sqlplus?
A- DESCRIBE or DESC
1.10 Q- What command will you start to run the installation of Oracle software on Linux?
A- runInstaller
2. Moderate (Standard knoledge for a daily-work of every DBA. He could fail one or two questions, but not more)
2.1 Q- What should you do if you encounter an ORA-600?
A- Contact Oracle Support
2.2 Q- Explain the differences between PFILE and SPFILE
A- PFILE is a Static, text file that initialices the database parameter in the moment that it’s started. If you want to modify parameters in PFILE, you have to restart the database.
SPFILE is a dynamic, binary file that allows you to overwrite parameters while the database is already started (with some exceptions)
2.3 Q- In which Oracle version was Data Pump introduced?
A- Oracle 10g
2.4 Q- Say two examples of DML, two of DCL and two of DDL
A- DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DDL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DCL: GRANT, REVOKE
2.5 Q- You want to save the output of an Oracle script from sqlplus. How would you do it?
A- spool script_name.txt
select * from your_oracle_operations;
spool off;
2.6 Q- What is the most important requirement in order to use RMAN to make consistent hot backups?
A- Your database has to be in ARCHIVELOG mode.
2.7 Q- Can you connect to a local database without a listener?
A- Yes, you can.
2.8 Q- In which view can you find information about every view and table of oracle dictionary?
A- DICT or DICTIONARY
2.9 Q- How can you view all the users account in the database?
A- SELECT USERNAME FROM DBA_USERS;
2.10 Q- In linux, how can we change which databases are started during a reboot?
A- Edit /etc/oratab
3. Advanced (A 3+ year experienced DBA should have enough knowledge to answer these questions. However, depending on the work he has done, he could still fail up to 4 questions)
3.1 Q- When a user process fails, what Oracle background process will clean after it?
A- PMON
3.2 Q- How can you reduce the space of TEMP datafile?
A- Prior to Oracle 11g, you had to recreate the datafile. In Oracle 11g a new feature was introduced, and you can shrink the TEMP tablespace.
3.3 Q- How can you view all the current users connected in your database in this moment?
A- SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME;
3.4 Q- Explain the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT
A- SHUTOWN NORMAL = SHUTDOWN : It waits for all sessions to end, without allowing new connections.
SHUTDOWN IMMEDIATE : Rollback current transactions and terminates every session.
SHUTDOWN ABORT : Aborts all the sessions, leaving the database in an inconsistent state. It’s the fastest method, but can lead to database corruption.
3.5 Q- Is it possible to backup your database without the use of an RMAN database to store the catalog?
A- Yes, but the catalog would be stored in the controlfile.
3.6 Q- Which are the main components of Oracle Grid Control?
A- OMR (Oracle Management Repository), OMS (Oracle Management Server) and OMA (Oracle Management Agent).
3.7 Q- What command will you use to navigate through ASM files?
A- asmcmd
3.8 Q- What is the difference between a view and a materialized view?
A- A view is a select that is executed each time an user accesses to it. A materialized view stores the result of this query in memory for faster access purposes.
3.9 Q- Which one is faster: DELETE or TRUNCATE?
A- TRUNCATE
3.10 Q- Are passwords in oracle case sensitive?
A- Only since Oracle 11g.
4. RAC (Only intended for RAC-specific DBAs, with varied difficultied questions)
4.1 Q- What is the recommended method to make backups of a RAC environment?
A- RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file.
4.2 Q- What command would you use to check the availability of the RAC system?
A- crs_stat -t -v (-t -v are optional)
4.3 Q- What is the minimum number of instances you need to have in order to create a RAC?
A- 1. You can create a RAC with just one server.
4.4 Q- Name two specific RAC background processes
A- RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG.
4.5 Q- Can you have many database versions in the same RAC?
A- Yes, but Clusterware version must be greater than the greater database version.
4.6 Q- What was RAC previous name before it was called RAC?
A- OPS: Oracle Parallel Server
4.7 Q- What RAC component is used for communication between instances?
A- Private Interconnect.
4.8 Q- What is the difference between normal views and RAC views?
A- RAC views has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION
4.9 Q- Which command will we use to manage (stop, start…) RAC services in command-line mode?
A- srvctl
4.10 Q- How many alert logs exist in a RAC environment?
A- One for each instance.
5. Master (A 3+ year experienced DBA would probably fail these questions, they are very specifid and specially difficult. Be glad if he’s able to answer some of them)
5.1 Q- How can you difference a usual parameter and an undocumented parameter?
A- Undocumented parameters have the prefix ‘_’. For example, _allow_resetlogs_corruption
5.2 Q- What is BBED?
A- An undocumented Oracle tool used for foresnic purposes. Stans for Block Browser and EDitor.
5.3 Q- The result of the logical comparison (NULL = NULL) will be… And in the case of (NULL != NULL)
A- False in both cases.
5.4 Q- Explain Oracle memory structure
The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer s RAM is called memory structure. Oracle has two memory structures in the computer s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA).
The PGA contains data and control information for a single user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database.
5.5 Q- Will RMAN take backups of read-only tablespaces?
A- No
5.6 Q- Will a user be able to modify a table with SELECT only privilege?
A- He won’t be able to UPDATE/INSERT into that table, but for some reason, he will still be able to lock a certain table.
5.7 Q- What Oracle tool will you use to transform datafiles into text files?
A- Trick question: you can’t do that, at least with any Oracle tool. A very experienced DBA should perfectly know this.
5.8 Q- SQL> SELECT * FROM MY_SCHEMA.MY_TABLE;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
Why I’m getting this error?
A- The table has a BLOB column.
5.9 Q- What parameter will you use to force the starting of your database with a corrupted resetlog?
A- _ALLOW_RESETLOGS_CORRUPTION
5.10 Q- Name the seven types of Oracle tables
A- Heap Organized Tables, Index Organized Tables, Index Clustered Tables, Hash Clustered Tables, Nested Tables, Global Temporary Tables, Object Tables.
====================================================================================
1. How many memory layers are in the shared pool?
The shared pool portion of the SGA contains three major areas: library cache, dictionary cache, buffers for parallel execution messages, and control structures
2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
1) backup set
RMAN> list archivelog all;
RMAN> list archivelog from ... until ...;
e.g
RMAN> list archivelog from time 'sysdate-1';
2)backup
RMAN> list backup of archivelog all;
RMAN> list backup of archivelog FROM SEQUENCE ... UNTIL SEQUENCE ...;
e.g
RMAN> list backup of archivelog FROM SEQUENCE 1437 UNTIL SEQUENCE 1437;
3. How can you tell how much space is left on a given file system and how much space each of the file system's subdirectories take-up?
df -k in Solaris
4. Define the SGA and:
• How you would configure SGA for a mid-sized OLTP environment?
use proper log buffer normally 512k/1m.
use proper shared pool size normally 150m-200m.
if not use java, set java_pool_size to 0.
if not use rman/mts/parallel set large_pool_size to minimize, normally 8m.
the other memory set to buffer cache, the method is below.
• What is involved in tuning the SGA?
put different data into different buffer pools,small frequently used tables put into keep pool. large seldom used tables put into recycle pool. others mainly put into default pool. if some table physical reads is enomous, then consider to put some segments(index/data) into keep pool. if possible use 40%-60% of memory for buffer cache.
5. What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
cache hit ratio = 1 - physical reads/(consistents gets + db block gets)
consistents gets refers to select query or none dml part of a dml statement.
db blocks gets refers to blocks that have be modified/created.
phisical reads refers to blocks that reads directly from disks.
logical reads are blocks that directly read from memory.
if this ratio is too small, may the system have too much disk reads, we should consider to tune the memory usage, and try to tune the sql statements with so much disk reads.
but now we prefer to use the wait events to tune the system. I prefer to use top 5/10 events togather with top 5/10 logical/disk reads to tune the system.
bad sql is the cause 90% system problems .
6. Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
1).tablespace usage: dba_free_space
2).invalidate objects/indexes: dba_objects/dba_indexes
3).top large data segments/top large index segment: dba_segments
4).active transaction list: v$transaction
5).active session list: v$session.active
6).active lock list: v$lock
7).session distribution between machines/applications: v$session group by machine,owner,logon_time
8).system resource usage: v$resource_usage
7. How do you tell what your machine name is and what is its IP address?
hostname
ifconfig -a
8. How would you go about verifying the network name that the local_listener is currently using?
tnsping network_name;
ping ip_address.
sqlplus user/pass@network_name
9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
ipcs
ps –ef
10. What view(s) do you use to associate a user's SQLPLUS session with his o/s process?
v$process.addr = v$session.paddr
11. What is the recommended interval at which to run statspack snapshots, and why?
15-30 minutes at busy time. the statistics can show the shot of the system. and also will not be wipe out by useless information of the system.
Don Burlson says "The trick is to find the balance between too often and not often enough. For most reactive tuning situations, snapshots taken at intervals from five to fifteen minutes apart will provide a reasonable amount of data to make a good start on tuning."
12. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
Two parameters will make CBO to "re-calculate" the cost of indexing, so that Index looks like a "cheaper" path.
1)optimizer_index_cost_adj
The default value of 100 means that an index access to a table is just as expensive as a full table scan. On most systems, the default value of 100 should be lowered.
e.g A value of 10 means that the cost of an index access path is one-tenth the normal cost of an index access path.
2)optimizer_index_caching
This value is a percentage that indicates how many blocks are found in the buffer cache when an index is read. This value is only for index blocks, not for blocks in the table that the index points to. On most systems, the default value of 0 is too low and should be altered.
13. Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.
$ conn scott/tiger
SQL> var job_no number
SQL> dbms_job.submit(:job_no,trunc(sysdate+3+9/24),'trunc(sysdate+1) + 2/24');
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
$ crontab -e
0 14 */2 * * /test/test.sh >/tmp/test.log 2>&1
15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
DBMS_STANDARD package provides language facilities that help your application interact with Oracle.
Function sql_txt (sql_text out ora_name_list_t) return binary_integer;
16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
dba_mviews/user_mviews
dba_jobs/user_jobs.(refer to the specified mview).
17. How would you best determine why your MVIEW couldn't FAST REFRESH?
1) is there any mview log.
2) is therer any primary key exists.
18. How does propagation differ between Advanced Replication and Snapshot Replication (readonly)?
Advanced Replication can have multiple master sites i.e. you can update at any master site, while snapshot replication only has 1 update site.
19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
dba_repcatlog ; dba_repsites_new ; dba_repextensions
20. How would you begin to troubleshoot an ORA-3113 error?
1) is the network has some problem.
2) is the db has some problem.
3) has somebody killed the session.
21. Which dictionary tables and/or views would you look at to diagnose a locking issue?
sys.v_$lock, sys.v_$session sys.obj$ , sys.user$ ,
sys.v_$process
22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed", how do you approach troubleshooting this issue?
1) check the error no with alert$ORACLE_SID.log. or jobq trace file.
2) check job parameters.
23. How would you extract DDL of a table without using a GUI tool?
exp rows=n
imp indexfile=aaa.sql
24. You're getting high "busy buffer waits" - how can you find what's causing it?
1) find the hot block. with the v$session_wait/trace file/v$bh
2) find the main sql statement .
3) tune it.
25. What query tells you how much space a tablespace named "test" is taking up, and how much space is remaining?
dba_free_spaces / dba_extents
26. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
1) .first view active sessions. and v$session_wait .
2). see the uptime and top processes of the system.
3). analyze the main reason. and tune it.
27. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle's. What database recovery options are available? Database is in archive log mode.
instance recovery
28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).
Linux:
#cat /proc/cpuinfo
# top
Solaris:
#mpstat -a
#psrinfo -v
#prstat –a
29. How do you increase the OS limitation for open files (LINUX and/or Solaris)?
On solaris, use "ulimit"
e.g
# ulimit -n 8192
And verify this limit by using the following command:
# ulimit –a
On Linux, use "sysctl"
e.g
% cat /proc/sys/fs/file-max
8192
The current limit shown is 8192. To increase it to 65535 (as root):
# echo "65535" > /proc/sys/fs/file-max
If you want this new value to survive across reboots you can add it to /etc/sysctl.conf o specify the maximum number of open files permited:
fs.file-max = 65535
To load new values from the sysctl.conf file:
% sysctl -p /etc/sysctl.conf
30. Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.
sqlplus /nolog <<_END_
connect / as sysdba
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;
set time on timing on
drop table table_name;
select sysdate from dual;
exit;
_END_
31. Explain how you would restore a database using RMAN to Point in Time?
restore database until time '2006-08-18 00:00:00';
32. How does Oracle guarantee data integrity of data changes?
use constraints / isolation level
33. Which environment variables are absolutely critical in order to run the OUI?
DISPLAY
34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
select count(*) from v$session where username = '<USERNAME>';
35. Why does Oracle not permit the use of PCTUSED with indexes?
Hence, an Oracle index block may contain two types of pointers:
1 – Pointers to other index nodes (data blocks)
2 – ROWID pointers to specific table rows
Oracle manages the allocation of pointers within index blocks, and this is the reason why we are unable to specify a PCTUSED value (the freelist re-link threshold) for indexes."
36. What would you use to improve performance on an insert statement that places millions of rows into that table?
1) at idle time.
2) direct insert /*+append*/
3) if possible nologging
4) if possible parallel
5) if possible disable constraints(foreign key/primary key)
6) if possible disable index.
37. What would you do with an "in-doubt" distributed transaction?
rollback force LOCAL_TRAN_ID;
38. What are the commands you'd issue to show the explain plan for "select * from dual"?
SQL> explain plan for select * from dual;
SQL> @?/rdbms/admin/utlxplp.sql
SQL> select * from (dbms_xplan.display)
39. In what script is "snap$" created? In what script is the "scott/tiger" schema created?
SQL> @?/rdbms/admin/sql.bsq
SQL> @?/sqlplus/demo/demobld.sql
40. If you're unsure in which script a sys or system-owned object is created, but you know it's in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
$ORACLE_HOME/rdbms/admin/
$ORACLE_HOME/sqlplus/admin
use 'grep' command to search for this script. correct?
e.g #grep 'abc' *.sql
41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
When entering username/password@SID, Oracle stops by the sqlnet.ora file to pick up the NAMES.DEFAULT_DOMAIN value,
in 'tnsnames.ora'
(SERVICE_NAME = DSS)
in 'sqlnet.ora'
NAMES.DEFAULT_DOMAIN= icallinc.com
42. You create a private database link and upon connection,
fails with: ORA-2085: connects to .
What is the problem? How would you go about resolving this error?
This happens after the DB_NAME of the database was changed (as in note# 15390.1 ie re-create controfile with set database option etc ...).After This, the NAME column value is the new DB_NAME in V$DATABASE. The problem comes from the PROPS$ where GLOBAL_DB_NAME is still the old database name. So the WA is to execute the ALTER DATABASE RENAME GLOBAL_NAME (global_name=false is not a WA if global_naming is required).
43. I have my backup RMAN script called "backup_rman.sh". I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
rman target / catalog rman/rman <<_END_
rman script here.
_END_
$ nohup shell_script &
44. Explain the concept of the DUAL table.
a table with only one row and one column. to help show expressions internally in oracle.
45. What are the ways tablespaces can be managed and how do they differ?
dmt: dictionary managed tablespace (permenent tablespace/temprary tablespace).
uniform sized extents,freelist managed space allocation,permenent tablespace,temporary tablespace datafile
lmt: local managed tablespaces.
system managed extents,bitmap managed space allocation,temporary tablespace tempfile,undo tablespace
46. From the database level, how can you tell under which time zone a database is operating?
select dbtimezone from dual;
47. What's the benefit of "dbms_stats" over "analyze"?
you can import/export/set statistics directly with dbms_stats
it is easier to automate with dbms_stats (it is procedural, analyze is just a command)
dbms_stats is the stated, preferred method of collecting statisttics.
dbms_stats can analyze external tables, analyze cannot.
DBMS_STATS gathers statistics only for cost-based optimization; it does not gather other statistics. For example, the table statistics gathered by DBMS_STATS include the number of rows, number of blocks currently containing data, and average row length but not the number of chained rows, average free space, or number of unused data blocks.
dbms_stats (in 9i) can gather system stats (new)
ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics,such as the number of distinct values. DBMS_Stats won't do that.
Most importantly, in the future, ANALYZE will not collect statistics needed by the cost-based optimizer.
48. Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
$ORACLE_HOME/bin
49. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
using IMP to import
50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.
alter database create datafile 'file_path' as 'file_path';
recover datafile;
参考：http://marioalcaide.wordpress.com/2011/02/09/3years-interview-questions/