Behavioral Oracle DBA Interview Preparation Guide

Download PDF

System table space and it's contents are created a part of database creation and contains the meta data of the database. I do not see any reason why one would want to export and import contents only the SYSTEM table space. It can be part of the full database export/import.

Yes we have to enable archive log because archive log are going to apply in standby database.

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

By turning MONITORING ON that index and querying into INDEX_USAGE table

1. Modify init.ora parameter START_ARCHIVE=TRUE
2. SQL> SHUTDOWN IMMEDIATE;
3. STARTUP MOUNT;
4. ALTER DATAVASE ARCHIVELOG;
5. ALTER DATABASE OPEN;

All Archivelogs

Can't change CHARACTERSET of a database, you will need to re-create the database with
appropriate CHARACTERSET.

Wait is due to Data Guard Broker.

DBMS_STATS.GATHER_TABLE_STATS with GRANULARITY => 'PARTITION' OPTION

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.

CURSOR_SHARING
DB_CACHE_SIZE
PGA_AGGREGATE_TARGET
DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE

DB SCATTERED READ - FULL TABLE SCAN
DB SEQUENTIAL REAL - IO
ENQUEUE - LOCKING

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

By running $ORACLE_HOME/rdbms/admin/spcreate.sql script

DUPLICATE DATABASE

Crosscheck command

group by is used when we use aggregate functions on the columns in a query the other columns
should be in group by query
example:
select empno,ename,sum(sal) from emp
group by empno,ename
Order by is used to sort values either in ascending or descending order

USING COMMENT LINES WITH (+) SIGN YOU CAN PASS THE HINTS TO SQL ENGINE
For example
/* +PARALLEL( ) */

DDL IS A AUTO COMMIT, YOU DON􀂶T NEED TO COMMIT AGAIN

Rolling forward to recover data that has not been recorded in data files, yet has been recorded
in the on-line redo log, including the contents of rollback segments.
Rolling back transactions that have been explicitly rolled back or have not been committed as
indicated by the rollback segments regenerated in step a.
Releasing any resources (locks) held by transactions in process at the time of the failure.
Resolving any pending distributed transactions undergoing a two-phase commit at the time of
the instance failure.

The point at which ORACLE ends writing to one online redo log file and begins writing to another
is called a log switch.

Complete database recovery from disk failure is possible only in ARCHIVELOG mode.
Online database backup is possible only in ARCHIVELOG mode.

Archived Redo Log consists of Redo Log files that have archived before being reused.

A Partial Backup is any operating system backup short of a full backup, taken while the
database is open or shut down.

A full backup is an operating system backup of all data files, on-line redo log files and control
file that constitute ORACLE database and the parameter.