Oracle DBA Interview Questions Answered_ Technical (1) JAI MAA DURGA

Oracle DBA Interview Questions Answered: Technical Monday, 16 May 2011 10:25

60 Oracle Database Administration (DBA) Interview Questions (Technical) 1. What is an Oracle Instance? An Oracle database server consists of an Oracle database and an Oracle instance. Every time a database is started, a system global area (SGA) is allocated and Oracle background processes are started. The combination of the background processes and memory buffers is called an Oracle instance. We can run multiple instances on the same Oracle Database Server, where each instance connects to its database. Oracle instance includes: SGA SGA - System or Shared Global Area Components of SGA: DBBC - Database Buffer Cache SP - Shared Pool; divided into Library Cache (LC) and Data Dictionary Cache (DDC) or Row Cache. RLB - Redo log Buffer Background Process (10/11g database): Mandatory Processes SMON - System Monitor PMON - Process Monitor DBWR - Database writer LGWR - Log Writer

Oracle DBA Interview Questions Answered: Technical

http://www.emarcel.com/myblog/44-oraclearticles/163-oracle-dba-interview-questions-answere...

Oracle Database must have at least one control file. It's a binary file contains some of the following information: The database name and unique ID The timestamp of database creation The names and locations of associated datafiles and redo log files Tablespace information Datafile offline ranges Archived log information and history Backup set and backup piece information Backup datafile and redo log information Datafile copy information Log records: sequence numbers, SCN range in each log RMAN Catalog Database block corruption information

The location of the control files is specified through the control_files init param: SYS@DB1_SID SQL>show parameter control_file; NAME TYPE VALUE ------------------------------------ ----------- -----------------------------control_file_record_keep_time integer 7 control_files string /u01/app/oracle/oradata/DB1_SID /control01.ctl, /u01/app/oracl e/flash_recovery_area/DB1_SID/c ontrol02.ctl

3. When you start an Oracle DB which file is accessed first? Oracle first opens and reads the initialization parameter file (init.ora) [oracle@hostname ~]$ ls -la $ORACLE_HOME/dbs/initDB1_SID.ora -rw-r--r-- 1 oracle oinstall 1023 May 10 19:27 /u01/app/oracle/product/11.2.0/dbs/initDB1_SID.ora

4. What is the job of SMON and PMON processes? SMON - System Monitor Process - Performs recovery after instance failure, monitors temporary segments and extents; cleans temp segments, coalesces free space (mandatory process for DB and starts by default) PMON - Process Monitor - Recovers failed process resources. In Shared Server architecture, monitors and retarts any failed dispatcher or server proceses (mandatory process for DB and starts by default) [oracle@hostname ~]$ ps -ef |grep -e pmon -e smon |grep -v grep oracle 6755 1 0 12:59 ? 00:00:05 ora_pmon_DB1_SID



Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments. You can get an ORA-01555 error with a too-small undo_retention, even with a large undo tables. However, you can set a super-high value for undo_retention and still get an ORA-01555 error. The ORA-01555 snapshot too old error can be addressed by several remedies: Re-schedule long-running queries when the system has less DML load Increasing the size of your rollback segment (undo) size The ORA-01555 snapshot too old also relates to your setting for automatic undo retention Don't fetch between commits

more info: http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm 26. What is a Locally Managed Tablespace? Locally Managed Tablespace is a tablespace that record extent allocation in the tablespace header. Each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Advantages of Locally Managed Tablespaces: Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables) Reduce contention on data dictionary tables (single ST enqueue) Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space) Changes to the extent bitmaps do not generate rollback information

27. Can you audit SELECT statements? YES. But beware, you will need a storage mechanism to hold your SQL SELECT audits, a high data volume that can exceed the size of your whole database, everyday. SQL SELECT auditing can be accomplished in several ways: Oracle audit table command: audit SELECT table by FRED by access; Oracle Fined-grained Auditing In a busy database, the volume of the SELECT audit trail could easily exceed the size of the database every data. Plus, all data in the audit trail must also be audited to see who has selected data from the audit trail. 28. What does DBMS_FGA package do? The DBMS_FGA package provides fine-grained security functions. DBMS_FGA is a PL/SQL package used to define Fine Grain Auditing on objects. DBMS_FGA Package Subprograms: ADD_POLICY Procedure - Creates an audit policy using the supplied predicate as the audit condition DISABLE_POLICY Procedure - Disables an audit policy DROP_POLICY Procedure - Drops an audit policy



YES. You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online. The following statements illustrate online index build operations: CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE; 40. Can you see Execution Plan of a statement? YES. In many ways, for example from GUI based tools like TOAD, Oracle SQL Developer. Configuring AUTOTRACE, a SQL*Plus facility AUTOTRACE is a facility within SQL*Plus to show us the explain plan of the queries we've executed, and the resources they used. Once the PLAN_TABLE has been installed in the database, You can control the report by setting the AUTOTRACE system variable. SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default. SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer execution path. SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution statistics. SET AUTOTRACE ON - The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics. SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. 41. A table has been created with below settings. What will be size of 4th extent? storage (initial 200k next 200k minextents 2 maxextents 100 pctincrease 40) What will be size of 4th extent? "NEXT" Specify in bytes the size of the next extent to be allocated to the object. Percent Increase allows your segment to grow at an increasing rate. The first two extents will be of a size determined by the Initial and Next parameter (200k) The third extent will be 1 + PCTINCREASE/100 times the second extent (1,4*200=280k). AND The fourth extent will be 1 + PCTINCREASE/100 times the third extent (1,4*280=392k!!!), and so on... 42. What is DB Buffer Cache Advisor? The Buffer Cache Advisor provides advice on how to size the Database Buffer Cache to obtain optimal cache hit ratios. Member of Performance Advisors --> Memory Advisor pack. 43. What is STATSPACK tool?



PASSWORD_LIFE_TIME 60 PASSWORD_GRACE_TIME 10 PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX 0 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNLIMITED; 2) Create user and assign user to the all_users profile SQL>create user chuck identified by norris profile all_users; 3) To "alter profile" parameter, say; change to three months: SQL>alter profile all_users set PASSWORD_LIFE_TIME = 90; 48. How do you delete duplicate rows in a table? There is a few ways to achieve that: Using subquery to delete duplicate rows: DELETE FROM table_name WHERE rowid NOT IN (SELECT max(rowid) FROM table_name GROUP BY id); More ways: Use RANK to find and remove duplicate table rows Use self-join to remove duplicate rows Use analytics to detect and remove duplicate rows Delete duplicate table rows that contain NULL values source: http://www.dba-oracle.com/t_delete_duplicate_table_rows.htm 49. What is Automatic Management of Segment Space setting? Oracle9i New Feature Series: Automatic Segment Space Management Automatic Segment Space Management (ASSM) introduced in Oracle9i is an easier way of managing space in a segment using bitmaps. It eliminates the DBA from setting the parameters pctused, freelists, and freelist groups. ASSM can be specified only with the locally managed tablespaces (LMT). Oracle uses bitmaps to manage the free space. Bitmaps allow Oracle to manage free space more automatically. Here is an example: CREATE TABLESPACE example DATAFILE '/oradata/ORA_SID/example01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M SEGMENT SPACE MANAGEMENT AUTO; The storage parameters PCTUSED, FREELISTS and FREELIST GROUPS specified while creating a table are ignored by Oracle on a LMT ASSM tablespace. Oracle does not produce an error. One huge benefit of having ASSM is to reduce the “Buffer Busy Waits” you see on segments.



A server process can be either of the following: - A dedicated server process, which services only one user process - A shared server process, which can service multiple user processes Your database is always enabled to allow dedicated server processes, but you must specifically configure and enable shared server by setting one or more initialization parameters. 58. Can you import objects from Oracle ver. 7.3 to 9i? Different versions of the import utility are upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database. 59. How do you move tables from one tablespace to another tablespace? There are several methods to do this; 1) export the table, drop the table, create the table definition in the new tablespace, and then import the data (imp ignore=y). 2) Create a new table in the new tablespace with the CREATE TABLE statement AS SELECT all from source table command: CREATE TABLE temp_name TABLESPACE new_tablespace AS SELECT * FROM source_table; Then drop the original table and rename the temporary table as the original: DROP TABLE real_table; RENAME temp_name TO real_table; Note: don't forget to rebuild any indexes. 60. How to display how much space is used and free in a tablespace? Example query to check free and used space per tablespace: SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", NVL( ROUND(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", ROUND((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, ( SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files



GROUP BY tablespace_name ) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,df.bytes UNION ALL SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), NVL(ROUND((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), ROUND((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes) FROM dba_temp_files fs, ( SELECT tablespace_name,bytes_free, bytes_used FROM v$temp_space_header GROUP BY tablespace_name,bytes_free, bytes_used ) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,fs.bytes, df.bytes_free,df.bytes_used; Sample output: Tablespace Size (MB) Free (MB) % Free % Used

------------------------------ ---------- ---------- ---------- ---------UNDOTBS1 65 17.8125 27 73 EXAMPLE 100 22.625 23 77 USERS 5 1.0625 21 79 TEMP 20 2 10 90 SYSAUX 625.125 54.5 9 91 SYSTEM 700 9.0625 1 99

Oracle DBA Interview Questions Answered_ Technical (1) JAI MAA DURGA

Recommend Documents