Oracle DBA Interview Questions

Differences Between Data Warehouse and OLTP Systems

2008-12-17T08:57:00.000-08:00

Data warehouses and OLTP systems have very different requirements. Here are some examples of differences between typical data warehouses and OLTP systems:
Workload

Data warehouses are designed to accommodate ad hoc queries. You might not know the workload of your data warehouse in advance, so a data warehouse should be optimized to perform well for a wide variety of possible query operations.

OLTP systems support only predefined operations. Your applications might be specifically tuned or designed to support only these operations.
Data Modifications

A data warehouse is updated on a regular basis by the ETL process (run nightly or weekly) using bulk data modification techniques. The end users of a data warehouse do not directly update the data warehouse.

In OLTP systems, end users routinely issue individual data modification statements to the database. The OLTP database is always up to date, and reflects the current state of each business transaction.
Schema Design

Data warehouses often use denormalized or partially denormalized schemas (such as a star schema) to optimize query performance.

OLTP systems often use fully normalized schemas to optimize update/insert/delete performance, and to guarantee data consistency.
Typical Operations

A typical data warehouse query scans thousands or millions of rows.For example, "Find the total sales for all customers last month."

A typical OLTP operation accesses only a handful of records. For example, "Retrieve the current order for this customer."
Historical Data

Data warehouses usually store many months or years of data. This is to support historical analysis.

OLTP systems usually store data from only a few weeks or months. The OLTP system stores only historical data as needed to successfully meet the requirements of the current transaction.

Parameter File Search Order in Oracle Real Application Clusters

2008-11-12T09:28:00.001-08:00

Oracle searches for your parameter file in a particular order depending on your platform. On UNIX-based platforms, Oracle examines directories in the following order:

1.

$ORACLE_HOME/dbs/spfilesid.ora
2.

$ORACLE_HOME/dbs/spfile.ora
3.

$ORACLE_HOME/dbs/initsid.ora

The search order on Windows-based platforms is:

1.

%ORACLE_HOME%\database\spfilesid.ora
2.

%ORACLE_HOME%\database\spfile.ora
3.

%ORACLE_HOME%\database\initsid.ora

Using Function-based Indexes for Performance

2008-10-20T14:32:00.001-07:00

A function-based index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression, such as col1 + col2. With a function-based index, you can store computation-intensive expressions in the index.

Defining a function-based index on the transformed column or expression allows that data to be returned using the index when that function or expression is used in a WHERE clause or an ORDER BY clause. This allows Oracle to bypass computing the value of the expression when processing SELECT and DELETE statements. Therefore, a function-based index can be beneficial when frequently-executed SQL statements include transformed columns, or columns in expressions, in a WHERE or ORDER BY clause.

Oracle treats descending indexes as function-based indexes. The columns marked DESC are sorted in descending order.

For example, function-based indexes defined with the UPPER(column_name) or LOWER(column_name) keywords allow case-insensitive searches. The index created in the following statement:

CREATE INDEX uppercase_idx ON employees (UPPER(last_name));

facilitates processing queries such as:

SELECT * FROM employees
WHERE UPPER(last_name) = 'MARKSON';

Choosing Composite Indexes

2008-10-20T14:30:00.001-07:00

A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes:

*Improved selectivity

Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with higher selectivity.

*Reduced I/O

If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.

A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index.

Note:This is no longer the case with index skip scans. See "Index Skip Scans".

A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:

CREATE INDEX comp_ind
ON table1(x, y, z);

*x, xy, and xyz combinations of columns are leading portions of the index

*yz, y, and z combinations of columns are not leading portions of the index

Choosing Keys for Composite Indexes

Follow these guidelines for choosing keys for composite indexes:

*Consider creating a composite index on keys that are used together frequently in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either key individually.

*If several queries select the same set of keys based on one or more key values, then consider creating a composite index containing all of these keys.

Of course, consider the guidelines associated with the general performance advantages and trade-offs of indexes described in the previous sections.

Ordering Keys for Composite Indexes

Follow these guidelines for ordering keys in composite indexes:

*Create the index so the keys used in WHERE clauses make up a leading portion.

*If some keys are used in WHERE clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.

*If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.

Choosing Columns and Expressions to Index

2008-10-20T13:59:00.000-07:00

A key is a column or expression on which you can build an index. Follow these guidelines for choosing keys to index:

* Consider indexing keys that are used frequently in WHERE clauses.

* Consider indexing keys that are used frequently to join tables in SQL statements. For more information on optimizing joins, see the section "Using Hash Clusters for Performance".

* Choose index keys that have high selectivity. The selectivity of an index is the percentage of rows in a table having the same value for the indexed key. An index's selectivity is optimal if few rows have the same value.

Note:Oracle automatically creates indexes, or uses existing indexes, on the keys and expressions of unique and primary keys that you define with integrity constraints.

Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.

*Do not use standard B-tree indexes on keys or expressions with few distinct values. Such keys or expressions usually have poor selectivity and therefore do not optimize performance unless the frequently selected key values appear less frequently than the other key values. You can use bitmap indexes effectively in such cases, unless the index is modified frequently, as in a high concurrency OLTP application.

*Do not index columns that are modified frequently. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.

*Do not index keys that appear only in WHERE clauses with functions or operators. A WHERE clause that uses a function, other than MIN or MAX, or an operator with an indexed key does not make available the access path that uses the index except with function-based indexes.

*Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.

*When choosing to index a key, consider whether the performance gain for queries is worth the performance loss for INSERTs, UPDATEs, and DELETEs and the use of the space required to store the index. You might want to experiment by comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL trace facility.

Finding Locks and Lock Holders

2008-10-17T11:41:00.003-07:00

Query V$LOCK to find the sessions holding the lock. For every session waiting for the event enqueue, there is a row in V$LOCK with REQUEST <> 0. Use one of the following two queries to find the sessions holding the locks and waiting for the locks.

If there are enqueue waits, you can see these using the following statement:

SELECT * FROM V$LOCK WHERE request > 0;

To show only holders and waiters for locks being waited on, use the following:

SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;

TM enqueue

2008-10-17T11:41:00.001-07:00

TM enqueue

The most common reason for waits on TM locks tend to involve foreign key constraints where the constrained columns are not indexed. Index the foreign key columns to avoid this problem.

HW enqueue

2008-10-17T11:40:00.003-07:00

The HW enqueue is used to serialize the allocation of space beyond the high water mark of a segment.

*V$SESSION_WAIT.P2 / V$LOCK.ID1 is the tablespace number.
*V$SESSION_WAIT.P3 / V$LOCK.ID2 is the relative dba of segment header of the object for which space is being allocated.

If this is a point of contention for an object, then manual allocation of extents solves the problem.

ST enqueue

2008-10-17T11:40:00.001-07:00

ST enqueue

If the contended-for enqueue is the ST enqueue, then the problem is most likely to be dynamic space allocation. Oracle dynamically allocates an extent to a segment when there is no more free space available in the segment. This enqueue is only used for dictionary managed tablespaces.

To solve contention on this resource:

*Check to see whether the temporary (that is, sort) tablespace uses TEMPFILES. If not, then switch to using TEMPFILES.

*Switch to using locally managed tablespaces if the tablespace that contains segments that are growing dynamically is dictionary managed.

#If it is not possible to switch to locally managed tablespaces, then ST enqueue resource usage can be decreased by changing the next extent sizes of the growing objects to be large enough to avoid constant space allocation. To determine which segments are growing constantly, monitor the EXTENTS column of the DBA_SEGMENTS view for all SEGMENT_NAMEs. See Oracle Database Administrator's Guide for information about displaying information about space usage.

#Preallocate space in the segment, for example, by allocating extents using the ALTER TABLE ALLOCATE EXTENT SQL statement.

TX enqueue

2008-10-17T11:36:00.001-07:00

TX enqueue

These are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

* Waits for TX in mode 6: occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

* Waits for TX in mode 4 can occur if the session is waiting for an ITL (interested transaction list) slot in a block. This happens when the session wants to lock a row in the block but one or more other sessions have rows locked in the same block, and there is no free ITL slot in the block. Usually, Oracle dynamically adds another ITL slot. This may not be possible if there is insufficient free space in the block to add an ITL. If so, the session waits for a slot with a TX enqueue in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - allocate ITL entry.

The solution is to increase the number of ITLs available, either by changing the INITRANS or MAXTRANS for the table (either by using an ALTER statement, or by re-creating the table with the higher values).

*Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

*Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each 'entry' in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

*Waits for TX in Mode 4 can also occur waiting for a PREPARED transaction.

*Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait event enq: TX - index contention.

sar - Top 20 DBA unix commands

2008-10-16T13:05:00.001-07:00

SAR: Displays the activity for the CPU.

Syntax

sar [-a] [-A] [-b] [-c] [-d] [-g] [-k] [-m] [-p] [-q] [-r] [-u] [-v] [-w] [-y ] [ -o filename ] t [ n ]

sar [-a] [-A] [-b] [-c] [-d] [-g] [-k] [-m] [-p] [-q] [-r] [-u] [-v] [-w] [-y ] [ -e time ] [ -f filename ] [-i sec ] [ -s time ]

-a Report use of file access system routines: iget/s, namei/s, dirblk/s
-A Report all data. Equivalent to -abcdgkmpqruvwy.
-b Report buffer activity:

bread/s, bwrit/s
transfers per second of data between system buffers and disk or other block devices.

lread/s, lwrit/s
accesses of system buffers.

%rcache, %wcache
cache hit ratios, that is, (1-bread/lread) as a percentage.

pread/s, pwrit/s
transfers using raw (physical) device mechanism.
-c Report system calls:

scall/s system calls of all types.

sread/s, swrit/s, fork/s, exec/s
specific system calls.

rchar/s, wchar/s
characters transferred by read and write system calls. No incoming or outgoing exec and fork calls are reported.
-d Report activity for each block device (for example, disk or tape drive) with the exception of XDC disks and tape drives. When data is displayed, the device specification dsk- is generally used to represent a disk drive. The device specification used to represent a tape drive is machine dependent. The activity data reported is:

%busy, avque
portion of time device was busy servicing a transfer request, average number of requests outstanding during that time.

read/s, write/s, blks/s
number of read/write transfers from or to device, number of bytes transferred in 512-byte units.

avwait average wait time in milliseconds.

avserv average service time in milliseconds.
-g Report paging activities:

pgout/s
page-out requests per second.

ppgout/s
pages paged-out per second.

pgfree/s
pages per second placed on the free list by the page stealing daemon.

pgscan/s
pages per second scanned by the page stealing daemon.

%ufs_ipf
the percentage of UFS inodes taken off the freelist by iget which had reusable pages associated with them. These pages are flushed and cannot be reclaimed by processes. Thus, this is the percentage of igets with page flushes.
-k Report kernel memory allocation (KMA) activities:

sml_mem, alloc, fail
information about the memory pool reserving and allocating space for small requests: the amount of memory in bytes KMA has for the small pool, the number of bytes allocated to satisfy requests for small amounts of memory, and the number of requests for small amounts of memory that were not satisfied (failed).

lg_mem, alloc, fail
information for the large memory pool (analogous to the information for the small memory pool).

ovsz_alloc, fail
the amount of memory allocated for oversize requests and the number of oversize requests which could not be satisfied (because oversized memory is allocated dynamically, there is not a pool).
-m Report message and semaphore activities:

msg/s, sema/s
primitives per second.
-p Report paging activities:

atch/s
page faults per second that are satisfied by reclaiming a page currently in memory (attaches per second).

pgin/s
page-in requests per second.

ppgin/s
pages paged-in per second.

pflt/s
page faults from protection errors per second (illegal access to page) or "copy-on-writes".

vflt/s
address translation page faults per second (valid page not in memory).

slock/s
faults per second caused by software lock requests requiring physical I/O.
-q Report average queue length while occupied, and percent of time occupied:

runq-sz, %runocc
run queue of processes in memory and runnable.

swpq-sz, %swpocc
these are no longer reported by sar .
-r Report unused memory pages and disk blocks:

freemem average pages available to user processes.

freeswap disk blocks available for page swapping.
-u Report CPU utilization (the default):

%usr, %sys, %wio, %idle
portion of time running in user mode, running in system mode, idle with some process waiting for block I/O, and otherwise idle.
-v Report status of process, i-node, file tables:

proc-sz, inod-sz, file-sz, lock-sz
entries/size for each table, evaluated

ov
overflows that occur between sampling points for each table.
-w Report system swapping and switching activity:

swpin/s, swpot/s, bswin/s, bswot/s
number of transfers and number of 512-byte units transferred for swapins and swapouts (including initial loading of some programs).

pswch/s
process switches.
-y Report TTY device activity:

rawch/s, canch/s, outch/s
input character rate, input character rate processed by canon, output character rate.

rcvin/s, xmtin/s, mdmin/s
receive, transmit and modem interrupt rates.
-o filename Save samples in file, filename, in binary format.
-e time Select data up to time . Default is 18:00.
-f filename Use filename as the data source for sar . Default is the current daily data file /var/adm/sa/sadd.
-i sec Select data at intervals as close as possible to sec seconds.

Examples

sar - display today's CPU activity so far. You must have permission and the information must be present on the computer.

top: top - display top CPU processes
SYNOPSIS
top [-] [d delay] [p pid] [q] [c] [C] [S] [s] [i] [n iter] [b]
DESCRIPTION
top provides an ongoing look at processor activity in real time. It displays a listing of the most CPU-intensive tasks on the system, and can provide an interactive interface for manipulating processes. It can sort the tasks by CPU usage, memory usage and runtime. can be better configured than the standard top from the procps suite. Most features can either be selected by an interactive command or by specifying the feature in the personal or system-wide configuration file. See below for more information.

COMMAND-LINE OPTIONS
-d
Specifies the delay between screen updates. You can change this with the s interactive command.
-p
Monitor only processes with given process id. This flag can be given up to twenty times. This option is neither available interactively nor can it be put into the configuration file.
-q
This causes top to refresh without any delay. If the caller has superuser privileges, top runs with the highest possible priority.
-S
Specifies cumulative mode, where each process is listed with the CPU time that it as well as its dead children has spent. This is like the -S flag to ps(1). See the discussion below of the S interactive command.
-s
Tells top to run in secure mode. This disables the potentially dangerous of the interactive commands (see below). A secure top is a nifty thing to leave running on a spare terminal.
-i
Start top ignoring any idle or zombie processes. See the interactive command i below.
-C
display total CPU states instead of individual CPUs. This option only affects SMP systems.
-c
display command line instead of the command name only. The default behavior has been changed as this seems to be more useful.
-H
Show all threads.
-n
Number of iterations. Update the display this number of times and then exit.
-b
Batch mode. Useful for sending output from top to other programs or to a file. In this mode, top will not accept command line input. It runs until it produces the number of iterations requested with the n option or until killed. Output is plain text suitable for display on a dumb terminal.
FIELD DESCRIPTIONS
top displays a variety of information about the processor state. The display is updated every 5 seconds by default, but you can change that with the d command-line option or the s interactive command.
"uptime"
This line displays the time the system has been up, and the three load averages for the system. The load averages are the average number of process ready to run during the last 1, 5 and 15 minutes. This line is just like the output of uptime(1). The uptime display may be toggled by the interactive l command.
processes
The total number of processes running at the time of the last update. This is also broken down into the number of tasks which are running, sleeping, stopped, or undead. The processes and states display may be toggled by the t interactive command.
"CPU states"
Shows the percentage of CPU time in user mode, system mode, niced tasks, iowait and idle. (Niced tasks are only those whose nice value is positive.) Time spent in niced tasks will also be counted in system and user time, so the total will be more than 100%. The processes and states display may be toggled by the t interactive command.
Mem
Statistics on memory usage, including total available memory, free memory, used memory, shared memory, and memory used for buffers. The display of memory information may be toggled by the m interactive command.
Swap
Statistics on swap space, including total swap space, available swap space, and used swap space. This and Mem are just like the output of free(1).
PID
The process ID of each task.
PPID
The parent process ID each task.
UID
The user ID of the task's owner.
USER
The user name of the task's owner.
PRI
The priority of the task.
NI
The nice value of the task. Negative nice values are higher priority.
SIZE
The size of the task's code plus data plus stack space, in kilobytes, is shown here.
TSIZE
The code size of the task. This gives strange values for kernel processes and is broken for ELF processes.
DSIZE
Data + Stack size. This is broken for ELF processes.
TRS
Text resident size.
SWAP
Size of the swapped out part of the task.
D
Size of pages marked dirty.
LC
Last used processor. (That this changes from time to time is not a bug; Linux intentionally uses weak affinity. Also notice that the very act of running top may break weak affinity and cause more processes to change current CPU more often because of the extra demand for CPU time.)
RSS
The total amount of physical memory used by the task, in kilobytes, is shown here. For ELF processes used library pages are counted here, for a.out processes not.
SHARE
The amount of shared memory used by the task is shown in this column.
STAT
The state of the task is shown here. The state is either S for sleeping, D for uninterruptible sleep, R for running, Z for zombies, or T for stopped or traced. These states are modified by trailing < for a process with negative nice value, N for a process with positive nice value, W for a swapped out process (this does not work correctly for kernel processes).
WCHAN
depending on the availability of either /boot/psdatabase or the kernel link map /boot/System.map this shows the address or the name of the kernel function the task currently is sleeping in.
TIME
Total CPU time the task has used since it started. If cumulative mode is on, this also includes the CPU time used by the process's children which have died. You can set cumulative mode with the S command line option or toggle it with the interactive command S. The header line will then be changed to CTIME.
%CPU
The task's share of the CPU time since the last screen update, expressed as a percentage of total CPU time per processor.
%MEM
The task's share of the physical memory.
COMMAND
The task's command name, which will be truncated if it is too long to be displayed on one line. Tasks in memory will have a full command line, but swapped-out tasks will only have the name of the program in parentheses (for example, "(getty)").
"A , WP"
these fields from the kmem top are not supported.
INTERACTIVE COMMANDS
Several single-key commands are recognized while top is running. Some are disabled if the s option has been given on the command line.
space
Immediately updates the display.
^L
Erases and redraws the screen.
h or ?
Displays a help screen giving a brief summary of commands, and the status of secure and cumulative modes.
k
Kill a process. You will be prompted for the PID of the task, and the signal to send to it. For a normal kill, send signal 15. For a sure, but rather abrupt, kill, send signal 9. The default signal, as with kill(1), is 15, SIGTERM. This command is not available in secure mode.
i
Ignore idle and zombie processes. This is a toggle switch.
I
Toggle between Solaris (CPU percentage divided by total number of CPUs) and Irix (CPU percentage calculated solely by amount of time) views. This is a toggle switch that affects only SMP systems.
n or #
Change the number of processes to show. You will be prompted to enter the number. This overrides automatic determination of the number of processes to show, which is based on window size measurement. If 0 is specified, then top will show as many processes as will fit on the screen; this is the default.
q
Quit.
r
Re-nice a process. You will be prompted for the PID of the task, and the value to nice it to. Entering a positve value will cause a process to be niced to negative values, and lose priority. If root is running top, a negative value can be entered, causing a process to get a higher than normal priority. The default renice value is 10. This command is not available in secure mode.
S
This toggles cumulative mode, the equivalent of ps -S, i.e., that CPU times will include a process's defunct children. For some programs, such as compilers, which work by forking into many separate tasks, normal mode will make them appear less demanding than they actually are. For others, however, such as shells and init, this behavior is correct. In any case, try cumulative mode for an alternative view of CPU use.
s
Change the delay between updates. You will be prompted to enter the delay time, in seconds, between updates. Fractional values are recognized down to microseconds. Entering 0 causes continuous updates. The default value is 5 seconds. Note that low values cause nearly unreadably fast displays, and greatly raise the load. This command is not available in secure mode.
f or F
Add fields to display or remove fields from the display. See below for more information.
o or O
Change order of displayed fields. See below for more information.
l
toggle display of load average and uptime information.
m
toggle display of memory information.
t
toggle display of processes and CPU states information.
c
toggle display of command name or full command line.
N
sort tasks by pid (numerically).
A
sort tasks by age (newest first).
P
sort tasks by CPU usage (default).
M
sort tasks by resident memory usage.
T
sort tasks by time / cumulative time.
W
Write current setup to ~/.toprc. This is the recommended way to write a top configuration file.
The Field and Order Screens
After pressing f, F, o or O you will be shown a screen specifying the field order on the top line and short descriptions of the field contents. The field order string uses the following syntax: If the letter in the filed string corresponding to a field is upper case, the field will be displayed. This is furthermore indicated by an asterisk in front of the field description. The order of the fields corresponds to the order of the letters in the string.
From the field select screen you can toggle the display of a field by pressing the corresponding letter.
From the order screen you may move a field to the left by pressing the corresponding upper case letter resp. to the right by pressing the lower case one.
Configuration Files
The command top reads it's default configuration from two files, /etc/toprc and ~/.toprc. The global configuration file may be used to restrict the usage of top to the secure mode for non-non-privileged users. If this is desired, the file should contain a 's' to specify secure mode and a digit d (2<=d<=9) for the default delay (in seconds) on a single line. The personal configuration file contains two lines. The first line contains lower and upper letters to specify which fields in what order are to be displayed. The letters correspond to the letters in the Fields or Order screens from top. As this is not very instructive, it is recommended to select fields and order in a running top process and to save this using the W interactive command.

The second line is more interesting (and important). It contains information on the other options. Most important, if you have saved a configuration in secure mode, you will not get an insecure top without removing the lower 's' from the second line of your ~/.toprc.

A digit specifies the delay time between updates, a capital 'S' cumulative mode, a lower 'i' no-idle mode, a capital 'I' Irix view. As in interactive mode, a lower 'm', 'l', and 't' suppresses the display of memory, uptime resp. process and CPU state information. Currently changing the default sorting order (by CPU usage) is not supported.

iostat - Top 20 DBA unix commands

2008-10-16T13:03:00.000-07:00

iostat: Reports Central Processing Unit (CPU) statistics and input/output statistics for the entire system, adapters, tty devices, disks and CD-ROMs.

Syntax

iostat [ -s ] [ -a ] [ -d | -t ] [ PhysicalVolume ... ] [ Interval [ Count ] ]

Description

The iostat command is used for monitoring system input/output device loading by observing the time the physical disks are active in relation to their average transfer rates. The iostat command generates reports that can be used to change system configuration to better balance the input/output load between physical disks and adapters.

The first report generated by the iostat command provides statistics concerning the time since the system was booted. Each subsequent report covers the time since the previous report. All statistics are reported each time the iostat command is run. The report consists of a tty and CPU header row followed by a row of tty and CPU statistics. On multiprocessor systems, CPU statistics are calculated system-wide as averages among all processors.

If the -s flag is specified, a system-header row is displayed followed by a line of statistics for the entire system. The hostname of the system is printed in the system-header row.

If the -a flag is specified, an adapter-header row is displayed followed by a line of statistics for the adapter. This will be followed by a disk-header row and the statistics of all the disks/CD-ROMs connected to the adapter. Such reports are generated for all the disk adapters connected to the system.

A disks header row is displayed followed by a line of statistics for each disk that is configured. If the PhysicalVolume parameter is specified, only those names specified are displayed.

If the PhysicalVolume parameter is specified, one or more alphabetic or alphanumeric physical volumes can be specified. If the PhysicalVolume parameter is specified, the tty and CPU reports are displayed and the disk report contains statistics for the specified drives. If a specified logical drive name is not found, the report lists the specified name and displays the message Drive Not Found. If no Logical Drive Names are specified, the report contains statistics for all configured disks and CD-ROMs. If no drives are configured on the system, no disk report is generated. The first character in the PhysicalVolume parameter cannot be numeric.

The Interval parameter specifies the amount of time in seconds between each report. The first report contains statistics for the time since system startup (boot). Each subsequent report contains statistics collected during the interval since the previous report. The Count parameter can be specified in conjunction with the Interval parameter. If the Count parameter is specified, the value of count determines the number of reports generated at Interval seconds apart. If the Interval parameter is specified without the Count parameter, the iostat command generates reports continuously.

The iostat command is useful in determining whether a physical volume is becoming a performance bottleneck and if there is potential to improve the situation. The % utilization field for the physical volumes indicates how evenly the file activity is spread across the drives. A high % utilization on a physical volume is a good indication that there may be contention for this resource. Since the CPU utilization statistics are also available with the iostat report, the percentage of time the CPU is in I/O wait can be determined at the same time. Consider distributing data across drives if the I/O wait time is significant and the disk utilization is not evenly distributed across volumes.

Note: Some system resource is consumed in maintaining disk I/O history for the iostat command. Use the sysconfig subroutine, or the System Management Interface Tool (SMIT) to stop history accounting.

Reports

The iostat command generates four types of reports, the tty and CPU Utilization report, the Disk Utilization report, the System throughput report and the Adapter throughput report.

tty and CPU Utilization Report

The first report generated by the iostat command is the tty and CPU Utilization Report. For multiprocessor systems, the CPU values are global averages among all processors. Also, the I/O wait state is defined system-wide and not per processor. The report has the following format:
Column Description
tin Shows the total number of characters read by the system for all ttys.
tout Shows the total number of characters written by the system to all ttys.
% user Shows the percentage of CPU utilization that occurred while executing at the user level (application).
% sys Shows the percentage of CPU utilization that occurred while executing at the system level (kernel).
% idle Shows the percentage of time that the CPU or CPUs were idle and the system did not have an outstanding disk I/O request.
% iowait Shows the percentage of time that the CPU or CPUs were idle during which the system had an outstanding disk I/O request.

This information is updated at regular intervals by the kernel (typically sixty times per second). The tty report provides a collective account of characters per second received from all terminals on the system as well as the collective count of characters output per second to all terminals on the system.

Methods Used to Compute CPU Disk I/O Wait Time

Operating system version 4.3.3 and later contain enhancements to the method used to compute the percentage of CPU time spent waiting on disk I/O (wio time).The method used in AIX 4.3.2 and earlier versions of the operating system can, under certain circumstances, give an inflated view of wio time on SMPs. The wio time is reported by the commands sar (%wio), vmstat (wa) and iostat (% iowait).

The method used in AIX 4.3.2 and earlier versions is as follows: At each clock interrupt on each processor (100 times a second per processor), a determination is made as to which of the four categories (usr/sys/wio/idle) to place the last 10 ms of time. If the CPU was busy in usr mode at the time of the clock interrupt, then usr gets the clock tick added into its category. If the CPU was busy in kernel mode at the time of the clock interrupt, then the sys category gets the tick. If the CPU was not busy, a check is made to see if any I/O to disk is in progress. If any disk I/O is in progress, the wio category is incremented. If no disk I/O is in progress and the CPU is not busy, the idle category gets the tick. The inflated view of wio time results from all idle CPUs being categorized as wio regardless of the number of threads waiting on I/O. For example, systems with just one thread doing I/O could report over 90 percent wio time regardless of the number of CPUs it has.

The method used in AIX 4.3.3 and later is as follows: The change in operating system version 4.3.3 is to only mark an idle CPU as wio if an outstanding I/O was started on that CPU. This method can report much lower wio times when just a few threads are doing I/O and the system is otherwise idle. For example, a system with four CPUs and one thread doing I/O will report a maximum of 25 percent wio time. A system with 12 CPUs and one thread doing I/O will report a maximum of 8 percent wio time. NFS client reads/writes go through the VMM, and the time that biods spend in the VMM waiting for an I/O to complete is now reported as I/O wait time.

Disk Utilization Report

The second report generated by the iostat command is the Disk Utilization Report. The disk report provides statistics on a per physical disk basis. The report has a format similar to the following:
% tm_act Indicates the percentage of time the physical disk was active (bandwidth utilization for the drive).
Kbps Indicates the amount of data transferred (read or written) to the drive in KB per second.
tps Indicates the number of transfers per second that were issued to the physical disk. A transfer is an I/O request to the physical disk. Multiple logical requests can be combined into a single I/O request to the disk. A transfer is of indeterminate size.
Kb_read The total number of KB read.
Kb_wrtn The total number of KB written.

Statistics for CD-ROM devices are also reported.

For large system configurations where a large number of disks are configured, the system can be configured to avoid collecting physical disk input/output statistics when the iostat command is not executing. If the system is configured in the above manner, the first Disk report displays the message Disk History Since Boot Not Available instead of the disk statistics. Subsequent interval reports generated by the iostat command contain disk statistics collected during the report interval. Any tty and CPU statistics after boot are unaffected. If a system management command is used to re-enable disk statistics keeping, the first iostat command report displays activity from the interval starting at the point that disk input/output statistics were enabled.

System Throughput Report

This report is generated if the -s flag is specified. This report provides statistics for the entire system. This report has the following format:
Kbps Indicates the amount of data transferred (read or written) in the entire system in KB per second.
tps Indicates the number of transfers per second issued to the entire system.
Kb_read The total number of KB read from the entire system.
Kb_wrtn The total number of KB written to the entire system.

Adapter Throughput Report

This report is generated if the -a flag is specified. This report provides statistics on an adapter-by-adapter basis.This report has the following format:
Kbps Indicates the amount of data transferred (read or written) in the adapter in KB per second.
tps Indicates the number of transfers per second issued to the adapter.
Kb_read The total number of KB read from the adapter.
Kb_wrtn The total number of KB written to the adapter.

Disk Input/Output History

To improve performance on, the collection of disk input/output statistics has been disabled. To enable the collection of this data, enter:

chdev -l sys0 -a iostat=true

To display the current settings, enter:

lsattr -E -l sys0 -a iostat

If the collection of disk input/output history is disabled, the first disk report of iostat output displays the message Disk History Since Boot Not Available instead of disk statistics. As before, subsequent interval reports generated by the iostat command contain disk statistics collected during the report interval.

Flags

-a Displays the adapter throughput report.
-d The -d flag is exclusive of the -t flag and displays only the disk utilization report.
-s Displays the system throughput report.
-t The -t flag is exclusive of the -d flag and displays only the tty and cpu usage reports.

Notes:

* The -s and -a flags can both be specified to display both the system and adapter throughput reports.
* If the -a flag is specified with the -t flag, the tty and CPU report is displayed, followed by the adapter throughput report. Disk Utilization reports of the disks connected to the adapters, will not be displayed after the Adapter throughput report.
* If the -a flag is specified with the -d flag, tty and CPU report will not be displayed. If Physical Volume parameter is specified, the Disk Utilization Report of the specified Physical volume will be printed under the corresponding adapter to which it belongs.

Examples

1. To display a single history since boot report for all tty, CPU, and Disks, enter:

iostat

2. To display a continuous disk report at two second intervals for the disk with the logical name disk1, enter:

iostat -d disk1 2

3. To display six reports at two second intervals for the disk with the logical name disk1, enter:

iostat disk1 2 6

4. To display six reports at two second intervals for all disks, enter:

iostat -d 2 6

5. To display six reports at two second intervals for three disks named disk1, disk2, disk3, enter:

iostat disk1 disk2 disk3 2 6

6. To print the System throughput report, enter:

iostat -s

7. To print the Adapter throughput report, enter:

iostat -a

8. To print the System and Adapter throughput reports, with only the tty and CPU report (no disk reports), enter:

iostat -sat

9. To print the System and Adapter throughput reports with the Disk Utilization reports of hdisk0 and hdisk7, enter

iostat -sad hdisk0 hdisk7

File

/usr/bin/iostat Contains the iostat command.

vmstat - Top 20 DBA Unix commands

2008-10-16T13:02:00.001-07:00

VMSTAT: Reports virtual memory statistics.

Syntax

vmstat [ -f ] [ -i ] [ -s ] [ -I ] [ -t ] [ PhysicalVolume ... ] [ Interval [ Count ] ]

Description

The vmstat command reports statistics about kernel threads, virtual memory, disks, traps and CPU activity. Reports generated by the vmstat command can be used to balance system load activity. These system-wide statistics (among all processors) are calculated as averages for values expressed as percentages, and as sums otherwise.

If the vmstat command is invoked without flags, the report contains a summary of the virtual memory activity since system startup. If the -f flag is specified, the vmstat command reports the number of forks since system startup. The PhysicalVolume parameter specifies the name of the physical volume.

The Interval parameter specifies the amount of time in seconds between each report. The first report contains statistics for the time since system startup. Subsequent reports contain statistics collected during the interval since the previous report. If the Interval parameter is not specified, the vmstat command generates a single report and then exits. The Count parameter can only be specified with the Interval parameter. If the Count parameter is specified, its value determines the number of reports generated and the number of seconds apart. If the Interval parameter is specified without the Count parameter, reports are continuously generated. A Count parameter of 0 is not allowed.

AIX 4.3.3 and later contain enhancements to the method used to compute the percentage of CPU time spent waiting on disk I/O (wio time).The method used in AIX 4.3.2 and earlier versions of the operating system can, under certain circumstances, give an inflated view of wio time on SMPs.

The method used in AIX 4.3.2 and earlier versions is as follows: At each clock interrupt on each processor (100 times a second per processor), a determination is made as to which of the four categories (usr/sys/wio/idle) to place the last 10 ms of time. If the CPU was busy in usr mode at the time of the clock interrupt, then usr gets the clock tick added into its category. If the CPU was busy in kernel mode at the time of the clock interrupt, then the sys category gets the tick. If the CPU was not busy, a check is made to see if any I/O to disk is in progress. If any disk I/O is in progress, the wio category is incremented. If no disk I/O is in progress and the CPU is not busy, the idle category gets the tick. The inflated view of wio time results from all idle CPUs being categorized as wio regardless of the number of threads waiting on I/O. For example, systems with just one thread doing I/O could report over 90 percent wio time regardless of the number of CPUs it has. The wio time is reported by the commands sar (%wio), vmstat (wa) and iostat (% iowait).

The method used in operating system AIX 4.3.3 and later is as follows: The change in operating system AIX 4.3.3 is to only mark an idle CPU as wio if an outstanding I/O was started on that CPU. This method can report much lower wio times when just a few threads are doing I/O and the system is otherwise idle. For example, a system with four CPUs and one thread doing I/O will report a maximum of 25 percent wio time. A system with 12 CPUs and one thread doing I/O will report a maximum of 8 percent wio time. NFS client reads/writes go through the VMM, and the time that biods spend in the VMM waiting for an I/O to complete is now reported as I/O wait time.

The kernel maintains statistics for kernel threads, paging, and interrupt activity, which the vmstat command accesses through the use of the knlist subroutine and the /dev/kmem pseudo-device driver. The disk input/output statistics are maintained by device drivers. For disks, the average transfer rate is determined by using the active time and number of transfers information. The percent active time is computed from the amount of time the drive is busy during the report.

The following example of a report generated by the vmstat command contains the column headings and their description:

kthr: kernel thread state changes per second over the sampling interval.
r Number of kernel threads placed in run queue.
b Number of kernel threads placed in wait queue (awaiting resource, awaiting input/output).

Memory: information about the usage of virtual and real memory. Virtual pages are considered active if they have been accessed. A page is 4096 bytes.
avm Active virtual pages.
fre Size of the free list.

Note: A large portion of real memory is utilized as a cache for file system data. It is not unusual for the size of the free list to remain small.

Page: information about page faults and paging activity. These are averaged over the interval and given in units per second.
re Pager input/output list.
pi Pages paged in from paging space.
po Pages paged out to paging space.
fr Pages freed (page replacement).
sr Pages scanned by page-replacement algorithm.
cy Clock cycles by page-replacement algorithm.

Faults: trap and interrupt rate averages per second over the sampling interval.
in Device interrupts.
sy System calls.
cs Kernel thread context switches.

Cpu: breakdown of percentage usage of CPU time.
us User time.
sy System time.
id CPU idle time.
wa CPU idle time during which the system had outstanding disk/NFS I/O request(s). See detailed description above.

Disk: Provides the number of transfers per second to the specified physical volumes that occurred in the sample interval. The PhysicalVolume parameter can be used to specify one to four names. Transfer statistics are given for each specified drive in the order specified. This count represents requests to the physical device. It does not imply an amount of data that was read or written. Several logical requests can be combined into one physical request.

If the -I flag is specified, an I/O oriented view is presented with the following column changes.
kthr The column p will also be displayed besides columns r and b.

p
Number of threads waiting on actual physical I/O per second.

page New columns fi and fo will be displayed instead of re and cy columns.

fi
File page-ins per second.

fo
File page-outs per second.

Flags

Note: Both the -f and -s flags can be entered on the command line, but the system will only accept the first flag specified and override the second flag.

-f Reports the number of forks since system startup.
-i Displays the number of interrupts taken by each device since system startup.
-I Displays I/O oriented view with the new columns of output, p under heading kthr,and columns fi and fo under heading page instead of the columns re and cy in the page heading.
-s Writes to standard output the contents of the sum structure, which contains an absolute count of paging events since system initialization. The -s option is exclusive of the other vmstat command options. These events are described as follows:

address translation faults
Incremented for each occurrence of an address translation page fault. I/O may or may not be required to resolve the page fault. Storage protection page faults (lock misses) are not included in this count.

page ins
Incremented for each page read in by the virtual memory manager. The count is incremented for page ins from page space and file space. Along with the page out statistic, this represents the total amount of real I/O initiated by the virtual memory manager.

page outs
Incremented for each page written out by the virtual memory manager. The count is incremented for page outs to page space and for page outs to file space. Along with the page in statistic, this represents the total amount of real I/O initiated by the virtual memory manager.

paging space page ins
Incremented for VMM initiated page ins from paging space only.

paging space page outs
Incremented for VMM initiated page outs to paging space only.

total reclaims
Incremented when an address translation fault can be satisfied without initiating a new I/O request. This can occur if the page has been previously requested by VMM, but the I/O has not yet completed; or if the page was pre-fetched by VMM's read-ahead algorithm, but was hidden from the faulting segment; or if the page has been put on the free list and has not yet been reused.

zero-filled page faults
Incremented if the page fault is to working storage and can be satisfied by assigning a frame and zero-filling it.

executable-filled page faults
Incremented for each instruction page fault.

pages examined by the clock
VMM uses a clock-algorithm to implement a pseudo least recently used (lru) page replacement scheme. Pages are aged by being examined by the clock. This count is incremented for each page examined by the clock.

revolutions of the clock hand
Incremented for each VMM clock revolution (that is, after each complete scan of memory).

pages freed by the clock
Incremented for each page the clock algorithm selects to free from real memory.

backtracks
Incremented for each page fault that occurs while resolving a previous page fault. (The new page fault must be resolved first and then initial page faults can be backtracked.)

lock misses
VMM enforces locks for concurrency by removing addressability to a page. A page fault can occur due to a lock miss, and this count is incremented for each such occurrence.

free frame waits
Incremented each time a process is waited by VMM while free frames are gathered.

extend XPT waits
Incremented each time a process is waited by VMM due to a commit in progress for the segment being accessed.

pending I/O waits
Incremented each time a process is waited by VMM for a page-in I/O to complete.

start I/Os
Incremented for each read or write I/O request initiated by VMM. This count should equal the sum of page-ins and page-outs.

iodones
Incremented at the completion of each VMM I/O request.

CPU context switches
Incremented for each CPU context switch (dispatch of a new process).

device interrupts
Incremented on each hardware interrupt.

software interrupts
Incremented on each software interrupt. A software interrupt is a machine instruction similar to a hardware interrupt that saves some state and branches to a service routine. System calls are implemented with software interrupt instructions that branch to the system call handler routine.

traps
Not maintained by the operating system.

syscalls
Incremented for each system call.

-t Prints the time-stamp next to each line of output of vmstat. The time-stamp is displayed in the HH:MM:SS format.

Note: Time stamp will not be printed if -f, -s, or -i flags are specified.

Examples

1. To display a summary of the statistics since boot, enter:

vmstat

2. To display five summaries at 2-second intervals, enter:

vmstat 2 5

The first summary contains statistics for the time since boot.
3. To display a summary of the statistics since boot including statistics for logical disks scdisk13 and scdisk14, enter:

vmstat scdisk13 scdisk14

4. To display fork statistics, enter:
vmstat -f
5. To display the count of various events, enter:

vmstat -s

6. To display time-stamp next to each column of output of vmstat, enter:

vmstat -t

7. To display the new I/O oriented view with an alternative set of columns, enter:

vmstat -I

Files

/unix Symbolic link to the kernel boot image.

/usr/bin/vmstat Contains the vmstat command.

Find how much memory is in your unix machine/server?

2008-10-16T13:00:00.000-07:00

Solaris

1. dmesg | grep mem
2. prtdiag | grep Memory
3. prtconf -v | grep Memory

AIX

1. bootinfo -r
2. lsattr -E1 sys0 -a realmem
3. getconf REAL_MEMORY

HPUX

1. dmesg | grep Physical
2. /opt/ignite/bin/print_manifest | grep Memory
3. machinfo | grep Memory

Linux

1. dmesg | grep Memory
2. grep -i memtotal /proc/meminfo
3. free

OpenVMS

1. show mem /page

FreeBSD

1. dmesg | grep memory
2. grep memory /var/run/dmesg.boot
3. sysctl -a | grep mem

Difference between Session, Connection and Process

2008-10-16T10:41:00.000-07:00

A connection is a physical circuit between you and the database. A connection might be
one of many types -- most popular begin DEDICATED server and SHARED server. Zero, one or
more sessions may be established over a given connection to the database as show above
with sqlplus. A process will be used by a session to execute statements. Sometimes
there is a one to one relationship between CONNECTION->SESSION->PROCESS (eg: a normal
dedicated server connection). Sometimes there is a one to many from connection to
sessions (eg: like autotrace, one connection, two sessions, one process). A process does
not have to be dedicated to a specific connection or session however, for example when
using shared server (MTS), your SESSION will grab a process from a pool of processes in
order to execute a statement. When the call is over, that process is released back to
the pool of processes.

Moving data files

2008-10-15T12:33:00.001-07:00

Moving or renaming a datafile while the database is in the MOUNT stage requires the use of the ALTER DATABASE

command. When using the ALTER DATABASE method to move datafiles, the datafile is moved after the instance is shut

down. A summary of the steps involved follows:

1. Shutdown the instance
2. Use operating system commands to move or rename the files(s).
3. Mount the database and use the ALTER DATABASE to rename the file within the database.
4. Opening the Database

% sqlplus "/ as sysdba"

SQL> shutdown immediate

SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf

SQL> startup mount

SQL> alter database rename file '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';

Do not disconnect after this step. Stay logged in
and proceed to open the database!

SQL> alter database open;

SQL> exit

Moving or renaming a datafile while the database is in the 'OPEN' stage requires the use of the ALTER TABLESPACE

command. When using the ALTER TABLESPACE method to move datafiles, the datafile is moved while the instance is

running. A summary of the steps involved follows:

1. Take the tablespace OFFLINE.
2. Use operating system commands to move or rename the file(s).
3. Use the ALTER TABLESPACE command to rename the file within the database.
4. Bring the tablespace back ONLINE.

NOTE: This method can only be used for non-SYSTEM tablespaces. It also cannot be used for tablespaces that contain

active ROLLBACK segments or TEMPORARY segments.

% sqlplus "/ as sysdba"

SQL> alter tablespace INDX offline;

SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf

SQL> alter tablespace INDX
2 rename datafile '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';

Do not disconnect after this step. Stay logged in
and proceed to bring the tablespace back online!

SQL> alter tablespace INDX online;

SQL> exit

Setting oracle database in archive mode from non-archive mode

2008-09-24T07:33:00.000-07:00

Lets start by checking the current archive mode.

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

So we're in NOARCHIVELOG mode and we need to change. We can use a database alter statement, but that won't be permanent, so lets just update the pfile directly.C:\oracle\product\10.2.0\admin\orcl

###########################################
# Enable Archive Log
###########################################
log_archive_dest_1='location=C:\oracle\product\10.2.0\oradata\archive'
log_archive_start=TRUE

note: You must first create the archive directory and grant access priv to oracle user.

Note that we're not actually required to specify the location of the log destination, but if you don't it'll end up in strange places (in my test it went to $ORACLE_HOME/dbs making a mess). You can specify as many as 10 diffrent archive log destinations by using the paramters log_archive_dest_1 through log_archive_dest_10. Remember, if you run out of space in your archive log destination the database will shut down!

Now we can startup the database in mount mode and put it in archivelog mode.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             230689020 bytes
Database Buffers          373293056 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

NOARCHIVELOG

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             234883324 bytes
Database Buffers          369098752 bytes
Redo Buffers                7094272 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

ARCHIVELOG

SQL>

Oracle Version Information - How to CheckYour Current Release Number ?

2008-09-21T10:50:00.000-07:00

To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query follows. (You can also query the V$VERSION view to see component-level information.) Other product release levels may increment independent of the database server.

COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION     STATUS
---------------------------------------- ----------- -----------
NLSRTL                                   10.2.0.1.0  Production
Oracle Database 10g Enterprise Edition   10.2.0.1.0  Prod
PL/SQL                                   10.2.0.1.0  Production
...

Database and Instance Shutdown

2008-09-21T10:47:00.000-07:00

Close a Database

When you close a database, Oracle writes all database data and recovery data in the SGA to the datafiles and redo log files, respectively. Next, Oracle closes all online datafiles and redo log files. (Any offline datafiles of any offline tablespaces have been closed already. If you subsequently reopen the database, any tablespace that was offline and its datafiles remain offline and closed, respectively.) At this point, the database is closed and inaccessible for normal operations. The control files remain open after a database is closed but still mounted.

Close the Database by Terminating the Instance

In rare emergency situations, you can terminate the instance of an open database to close and completely shut down the database instantaneously. This process is fast, because the operation of writing all data in the buffers of the SGA to the datafiles and redo log files is skipped. The subsequent reopening of the database requires recovery, which Oracle performs automatically.

Note:

If a system or power failure occurs while the database is open, then the instance is, in effect, terminated, and recovery is performed when the database is reopened.

Unmount a Database

After the database is closed, Oracle unmounts the database to disassociate it from the instance. At this point, the instance remains in the memory of your computer.

After a database is unmounted, Oracle closes the control files of the database.

Shut Down an Instance

The final step in database shutdown is shutting down the instance. When you shut down an instance, the SGA is removed from memory and the background processes are terminated.

Abnormal Instance Shutdown

In unusual circumstances, shutdown of an instance might not occur cleanly; all memory structures might not be removed from memory or one of the background processes might not be terminated. When remnants of a previous instance exist, a subsequent instance startup most likely will fail. In such situations, the database administrator can force the new instance to start up by first removing the remnants of the previous instance and then starting a new instance, or by issuing a SHUTDOWN ABORT statement in SQL*Plus or using Enterprise Manager.

Database and Instance Shutdown

2008-09-21T10:46:00.000-07:00

The three steps to shutting down a database and its associated instance are:

Close the database.
Unmount the database.
Shut down the instance.

A database administrator can perform these steps using Enterprise Manager. Oracle automatically performs all three steps whenever an instance is shut down.

What Happens When You Open a Database

2008-09-21T10:45:00.001-07:00

Opening a mounted database makes it available for normal database operations. Any valid user can connect to an open database and access its information. Usually, a database administrator opens the database to make it available for general use.

When you open the database, Oracle opens the online datafiles and redo log files. If a tablespace was offline when the database was previously shut down, the tablespace and its corresponding datafiles will still be offline when you reopen the database.

If any of the datafiles or redo log files are not present when you attempt to open the database, then Oracle returns an error. You must perform recovery on a backup of any damaged or missing files before you can open the database.

How to Identify Your Oracle Database Software Release

2008-09-21T10:42:00.000-07:00

Because Oracle Database continues to evolve and can require maintenance, Oracle periodically produces new releases. Not all customers initially subscribe to a new release or require specific maintenance for their existing release. As a result, multiple releases of the product exist simultaneously.

As many as five numbers may be required to fully identify a release. The significance of these numbers is discussed in the sections that follow.

Release Number Format

To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database server labeled "Release 10.1.0.1.0".

Figure 1-1 Example of an Oracle Database Release Number

What is block change tracking in RMAN Oracle 10g

2008-09-21T06:34:00.000-07:00

Oracle Database 10g RMAN implements incremental backups in a manner that disposes of the objection " RMAN scans all the data blocks to identify candidates for backup. This process puts so much stress on the system that doing incrementals becomes impractical" . It uses a file, analogous to journals in filesystems, to track the blocks that have changed since the last backup. RMAN reads this file to determine which blocks are to be backed up.

You can enable this tracking mechanism by issuing the following command:

SQL> alter database enable block change tracking using file '/rman_bkups/change.log';

This command creates a binary file called /rman_bkups/change.log for tracking purposes. Conversely, you can disable tracking with

SQL> alter database disable block change tracking;

To see whether change tracking is currently enabled, you can query:

SQL> select filename, status from v$block_change_tracking;

What is Incremental Merge in RMAN 10g?

2008-09-21T06:26:00.000-07:00

Let's say you have the following backup schedule:

Sunday - Level 0 (full), with tag level_0
Monday - Level 1 (incremental) with tag level_1_mon
Tuesday - Level 1 (incremental) with tag level_1_tue

and so on. If the database fails on Saturday, prior to 10g you would have had to restore the tag level_0 and then apply all six incrementals. It would have taken a long time, which is another reason many DBAs shun incremental backups.

Oracle Database 10g RMAN radically changes that equation. Now, your incremental backup command looks like this:

RMAN> backup incremental level_1 for recover of copy with tag level_0 database;

Here we have instructed RMAN to make an incremental level_1 backup and merge that with the full backup copy with the tag level_0. After this command, level_0 becomes a full backup of that day.

So, on Tuesday, the backup with tag level_0, when merged with incremental level_1 backup, becomes identical to the full Tuesday backup. Similarly, the incremental taken on Saturday, when applied to the backup on disk, will be equivalent to a full level_0 Saturday backup. If the database fails on Saturday, you just need to restore the level_0 backup plus a few archive logs to bring the database into a consistent state; there is no need to apply additional incrementals. This approach cuts down recovery time dramatically, speeds backup, and eliminates the need to make a full database backup again.

Moving from DBMS_JOB to DBMS_SCHEDULER

2008-09-20T19:53:00.002-07:00

Removing a Job from the Job Queue

The following example removes a job using DBMS_JOB, where 14144 is the number of the job being run:

BEGIN
DBMS_JOB.REMOVE(14144);
COMMIT;
END;
/

Using DBMS_SCHEDULER, you would issue the following statement instead:

BEGIN
DBMS_SCHEDULER.DROP_JOB('myjob1');
END;
/

Moving from DBMS_JOB to DBMS_SCHEDULER

2008-09-20T19:53:00.001-07:00

Altering a Job

An example of altering a job using DBMS_JOB is the following:

BEGIN
DBMS_JOB.WHAT(31, 'INSERT INTO employees VALUES (7935, ''TOM'', ''DOGAN'',
''tom.dogan@xyzcorp.com'', NULL, SYSDATE,''AD_PRES'', NULL,
NULL, NULL, NULL);');
COMMIT;
END;
/

This changes the action for job 31 to insert a different value. An equivalent statement using DBMS_SCHEDULER is the following:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'JOB1',
attribute => 'job_action',
value => 'INSERT INTO employees VALUES (7935, ''TOM'', ''DOGAN'',
''tom.dogan@xyzcorp.com'', NULL, SYSDATE, ''AD_PRES'', NULL,
NULL, NULL, NULL);');
END;
/

Moving from DBMS_JOB to DBMS_SCHEDULER

2008-09-20T19:51:00.000-07:00

Creating a Job

An example of creating a job using DBMS_JOB is the following:

VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'INSERT INTO employees VALUES (7935, ''SALLY'',
''DOGAN'', ''sally.dogan@xyzcorp.com'', NULL, SYSDATE, ''AD_PRES'', NULL,
NULL, NULL, NULL);', SYSDATE, 'SYSDATE+1');
COMMIT;
END;
/

An equivalent statement using DBMS_SCHEDULER is the following:

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'job1',
job_type => 'PLSQL_BLOCK',
job_action => 'INSERT INTO employees VALUES (7935, ''SALLY'',
''DOGAN'', ''sally.dogan@xyzcorp.com'', NULL, SYSDATE,''AD_PRES'', NULL,
NULL, NULL, NULL);');
start_date => SYSDATE,
repeat_interval => 'FREQ = DAILY; INTERVAL = 1');
END;
/

Moving from DBMS_JOB to DBMS_SCHEDULER

2008-09-20T19:48:00.000-07:00

Moving from DBMS_JOB to DBMS_SCHEDULER

Oracle Database provides advanced scheduling capabilities through the database Scheduler, a collection of functions and procedures in the DBMS_SCHEDULER package. The Scheduler offers far more functionality than the DBMS_JOB package, which was the previous Oracle Database job scheduler. In our next few posting we will discusses how you can take statements created with DBMS_JOB and rewrite them using DBMS_SCHEDULER.

Oracle Database 10g Scheduler - Associating Jobs with Programs

2008-09-20T19:47:00.000-07:00

Associating Jobs with Programs

In the above case, you created a job independently of any program. Now let's create one that refers to an operating system utility or program, a schedule to specify how many times something should run, and then join the two to create a job.

First you need to make the database aware that your script is a program to be used in a job. To create this program, you must have the CREATE JOB privilege.

begin
dbms_scheduler.create_program
(
program_name => 'MOVE_ARCS',
program_type => 'EXECUTABLE',
program_action => '/home/arup/dbtools/move_arcs.sh',
enabled => TRUE,
comments => 'Moving Archived Logs to Staging Directory'
);
end;
/

Here you have created a named program unit, specified it as an executable, and noted what the program unit is called.

Next, you will create a named schedule to be run every 30 minutes called EVERY_30_MINS. You would do that with:

begin
dbms_scheduler.create_schedule
(
schedule_name => 'EVERY_30_MINS',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
comments => 'Every 30-mins'
);
end;
/

Now that the program and schedule are created, you will associate the program to the schedule to create a job.

begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE',
program_name => 'MOVE_ARCS',
schedule_name => 'EVERY_30_MINS',
comments => 'Move Archived Logs to a Different Directory',
enabled => TRUE
);
end;
/

This will create a job to be run every 30 minutes that executes the shell script move_arcs.sh. It will be handled by the Scheduler feature inside the database—no need for cron or the AT utility.

Oracle Database 10g Scheduler - Creating Jobs Without Programs

2008-09-20T19:46:00.000-07:00

Creating Jobs Without Programs

Perhaps the concept can be best introduced through examples. Suppose you have created a shell script to move archived log files to a different filesystem as follows:

/home/arup/dbtools/move_arcs.sh

We can specify the OS executable directly without creating it as a program first.

begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE_2',
schedule_name => 'EVERY_30_MINS',
job_type => 'EXECUTABLE',
job_action => '/home/arup/dbtools/move_arcs.sh',
enabled => true,
comments => 'Move Archived Logs to a Different Directory'
);
end;
/

Similarly, you can create a job without a named schedule.

begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE_3',
job_type => 'EXECUTABLE',
job_action => '/home/arup/dbtools/move_arcs.sh',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
enabled => true,
comments => 'Move Archived Logs to a Different Directory'
);
end;
/

One advantage of Scheduler over dbms_job is pretty clear from our initial example: the ability to call OS utilities and programs, not just PL/SQL program units. This ability makes it the most comprehensive job management tool for managing Oracle Database and related jobs. However, you may have noted another, equally important advantage: the ability to define intervals in natural language. Note that in the above example we wanted our schedule to run every 30 minutes; hence the parameter REPEAT_INTERVAL is defined with a simple, English-like expression (not a PL/SQL one) :

'FREQ=MINUTELY; INTERVAL=30'

A more complex example may help convey this advantage even better. Suppose your production applications become most active at 7:00AM and 3:00PM. To collect system statistics, you want to run Statspack from Monday to Friday at 7:00AM and 3:00PM only. If you use DBMS_JOB.SUBMIT to create a job, the NEXT_DATE parameter will look something like this:

DECODE
(
SIGN
(
15 - TO_CHAR(SYSDATE,'HH24')
),
1,
TRUNC(SYSDATE)+15/24,
TRUNC
(
SYSDATE +
DECODE
(
TO_CHAR(SYSDATE,'D'), 6, 3, 1
)
)
+7/24
)

Is that code easy to understand? Not really.

Now let's see the equivalent job in DBMS_SCHEDULER. The parameter REPEAT_INTERVAL will be as simple as:

'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15'

Furthermore, this parameter value can accept a variety of intervals, some of them very powerful. Here are some more examples:

* Last Sunday of every month:

FREQ=MONTHLY; BYDAY=-1SUN

* Every third Friday of the month:

FREQ=MONTHLY; BYDAY=3FRI

* Every second Friday from the end of the month, not from the beginning:

FREQ=MONTHLY; BYDAY=-2FRI

The minus signs before the numbers indicate counting from the end, instead of the beginning.

What if you wanted to verify if the interval settings are correct? Wouldn't it be nice to see the various dates constructed from the calendar string? Well, you can get a preview of the calculation of next dates using the EVALUATE_CALENDAR_STRING procedure. Using the first example—running Statspack every day from Monday through Friday at 7:00AM and 3:00PM—you can check the accuracy of your interval string as follows:

set serveroutput on size 999999

declare
L_start_date TIMESTAMP;
l_next_date TIMESTAMP;
l_return_date TIMESTAMP;
begin
l_start_date := trunc(SYSTIMESTAMP);
l_return_date := l_start_date;
for ctr in 1..10 loop
dbms_scheduler.evaluate_calendar_string(
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15',
l_start_date, l_return_date, l_next_date
);
dbms_output.put_line('Next Run on: ' ||
to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
);
l_return_date := l_next_date;
end loop;
end;
/

The output is:

Next Run on: 03/22/2004 07:00:00
Next Run on: 03/22/2004 15:00:00
Next Run on: 03/23/2004 07:00:00
Next Run on: 03/23/2004 15:00:00
Next Run on: 03/24/2004 07:00:00
Next Run on: 03/24/2004 15:00:00
Next Run on: 03/25/2004 07:00:00
Next Run on: 03/25/2004 15:00:00
Next Run on: 03/26/2004 07:00:00
Next Run on: 03/26/2004 15:00:00

This confirms that your settings are correct.

Oracle Database 10g Scheduler

2008-09-20T19:45:00.001-07:00

The Oracle Database 10g Scheduler feature offers the best of all worlds: a job scheduler utility right inside the database that is sufficiently powerful to handle all types of jobs, not just PL/SQL code segments, and that can help you create jobs either with or without associated programs and/or schedules. Best of all, it comes with the database at no additional cost. In this installment, we'll take a look at how it works.

DBMS_JOB Package

2008-09-20T19:42:00.000-07:00

Some of you may use the dbms_job package extensively to submit database jobs to be run in the background, control the time or interval of a run, report failures, and much more.

The problem with the package is that it can handle only PL/SQL code segments—just anonymous blocks and stored program units. It cannot handle anything outside the database that is in an operating system command file or executable. To do so, you would have to resort to using an operating system scheduling utility such as cron in Unix or the AT command in Windows. Or, you could use a third-party tool, one that may even extend this functionality by providing a graphical user interface.

Even so, dbms_job has a distinct advantage over these alternatives: it is active only when the database is up and running. If the database is down, the jobs don't run. A tool outside the database must manually check if the database is up—and that can be difficult. Another advantage is that dbms_job is internal to the database; hence you can access it via a database access utility such as SQL*Plus.

Data Guard Database Synchronization Options

2008-09-20T19:37:00.000-07:00

Data Guard Database Synchronization Options

Data Guard can be configured to run with varying synchronization modes indicating the potential for data loss:

* No-Data-Loss mode : This simply means that the log transport services will not acknowledge modifications to the primary database until they are available to the standby database. This doesn't mean that the modifications have been applied to the standby database, merely that the log information is available to the log apply services should failover occur. This mode is implemented using standby redo logs on the standby server.

* No-Data-Divergence mode : This is an extension of the no-data-loss mode whereby modifications to the primary database are prevented if conectivity between the primary and at least one standby database is unavailable.

* Minimal-Data-Loss mode : When the performance requirements of the primary database are the top priority this mode provides the optimum balance of data protection and performance.

Data Guard Role Management

2008-09-20T19:32:00.000-07:00

Role Management

Using Data Guard, the role of a database can be switched from a primary role to a standby role and vice versa, ensuring no data loss in the process, and minimizing downtime.

There are two kinds of role transitions - a switchover and a failover.

A switchover is a role reversal between the primary database and one of its standby databases. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role and the standby database transitions to the primary role. The transition occurs without having to re-create either database.

A failover is a transition of a standby database to the primary role following a sudden outage of the primary database. The failed primary can be reinstated as a standby database for the new primary using Oracle Flashback Database. This can eliminate the need to recreate the failed primary from a backup, dramatically reducing the time and effort required to return the configuration to a protected state.
Administrators have the option of executing failovers manually, or Data Guard can be configured to automatically detect primary database failures and execute a failover to the standby

Data Guard Broker

2008-09-20T19:30:00.000-07:00

Data Guard Broker

The Oracle Data Guard Broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations. All management operations can be performed either through Oracle Enterprise Manager, which uses the Broker, or through the Broker's specialized command-line interface (DGMGRL). Data Guard Broker 11g also enables automatic database failover for Data Guard configurations using either Maximum Availability or Maximum Performance modes.

Data Guard Broker

* Enables automatic database failover for configurations using either Maximum Availability or Maximum Performance mode.
* Enables configurable events to trigger immediate automatic failover to a target standby database.
* Improved support for redo transport options, enabling an administrator to specify a connect description for Redo Transport Services.
* Elimination of database downtime when changing the protection mode to and from Maximum Availability and Maximum Performance.
* Support for single instance databases configured for HA using Oracle Clusterware and cold failover clusters.

Data Guard Protection Modes

2008-09-20T19:28:00.000-07:00

Data Guard Protection Modes

In some situations, a business cannot afford to lose data at any cost. In other situations, some applications require maximum database performance and can tolerate a potential loss of data. Data Guard provides three distinct modes of data protection to satisfy these varied requirements:

*Maximum Protection—This mode offers the highest level of data protection. Data is synchronously transmitted to the standby database from the primary database and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode. If the last standby database configured in this mode becomes unavailable, processing stops on the primary database. This mode ensures no-data-loss, even in the event of multiple failures.

*Maximum Availability—This mode is similar to the maximum protection mode, including zero data loss. However, if a standby database becomes unavailable (for example, because of network connectivity problems), processing continues on the primary database. When the fault is corrected, the standby database is automatically resynchronized with the primary database. This mode achieves no-data-loss in the event of a single failure (e.g. network failure, primary site failure . . .)

*Maximum Performance—This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode. In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database. The commit operation of the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database. If any standby destination becomes unavailable, processing continues on the primary database and there is little effect on primary database performance.

Redo Apply and SQL Apply

2008-09-20T19:27:00.001-07:00

Redo Apply and SQL Apply

A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database as a synchronized copy of the primary database by transmitting primary database redo data to the standby system and then applying the redo data to the standby database.

Data Guard provides two methods to apply this redo data to the standby database and keep it synchronized with the primary, and these methods correspond to the two types of standby databases supported by Data Guard.

* Redo Apply, used for physical standby databases
* SQL Apply, used for logical standby databases

A physical standby database provides a physically identical copy of the primary database, with on-disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. The Redo Apply technology applies redo data on the physical standby database using standard Oracle media recovery techniques. In addition to traditional Data Guard functionality, the Active Data Guard Option for Oracle Database 11g enables a physical standby database to be open read-only while it applies updates received from the primary database. This makes physical standby databases useful for offloading the primary database from the overhead of processing read-only queries and reports. This also makes it simple to validate that the standby database is synchronized with the primary database at all times.

A logical standby database contains the same logical information as the production database, although the physical organization and structure of the data can be different. The SQL apply technology keeps the logical standby database synchronized with the primary database by transforming redo data received from the primary database into SQL statements and then executing the SQL statements on the standby database. This makes it possible for the logical standby database to be open read-write and accessed for queries and reporting purposes at the same time the SQL is being applied to it.

Data Guard Benefits

2008-09-20T19:26:00.001-07:00

Data Guard Benefits

1. Disaster recovery and high availability

Data Guard provides an efficient and comprehensive disaster recovery and high availability solution. Automatic failover and easy-to-manage switchover capabilities allow quick role reversals between primary and standby databases, minimizing the downtime of the primary database for planned and unplanned outages.

2. Complete data protection

A standby database also provides an effective safeguard against data corruptions and user errors. Storage level physical corruptions on the primary database do not propagate to the standby database. Similarly, logical corruptions or user errors that cause the primary database to be permanently damaged can be resolved. Finally, the redo data is validated at the time it is received at the standby database and further when applied to the standby database.

3. Efficient utilization of system resources

A physical standby database can be used for backups and read-only reporting, thereby reducing the primary database workload and saving valuable CPU and I/O cycles. A physical standby database can also be easily converted back and forth between being a physical standby database and an open read/write database, without compromising data protection. A logical standby database enables read-write access to a synchronized standby database, and/or adding local tables to the standby database that can also be updated, and/or creating additional indexes to optimize read performance.

4. Flexibility in data protection to balance availability against performance requirements

Oracle Data Guard offers the maximum protection, maximum availability, and maximum performance modes to help enterprises balance data availability against system performance requirements.

5. Protection from communication failures

If network connectivity is lost between the primary and one or more standby databases, redo data cannot be sent from the primary to those standby databases. Once connectivity is re-established, the missing redo data is automatically detected by Data Guard and the necessary archive logs are automatically transmitted to the standby databases. The standby databases are resynchronized with the primary database, with no manual intervention by the administrator.

6. Centralized and simple management

Data Guard Broker automates the management and monitoring tasks across the multiple databases in a Data Guard configuration. Administrators may use either Oracle Enterprise Manager or the Broker’s own specialized command-line interface (DGMGRL) to take advantage of this integrated management framework.

7. Integrated with Oracle database

Data Guard is available as an integrated feature of the Oracle Database (Enterprise Edition) at no extra cost.

Creating a Nested Materialized View: Example

2008-09-20T19:24:00.000-07:00

Creating a Nested Materialized View: Example

The following example uses the materialized view from the preceding example as a master table to create a materialized view tailored for a particular sales representative in the sample oe schema:

CREATE MATERIALIZED VIEW my_warranty_orders
AS SELECT w.order_id, w.line_item_id, o.order_date
FROM warranty_orders w, orders o
WHERE o.order_id = o.order_id
AND o.sales_rep_id = 165;

Creating a Fast Refreshable Materialized View: Example

2008-09-20T19:23:00.000-07:00

Creating a Fast Refreshable Materialized View: Example

The following statement creates a fast-refreshable materialized view that selects columns from the order_items table in the sample oe schema, using the UNION set operator to restrict the rows returned from the product_information and inventories tables using WHERE conditions. The materialized view logs for order_items and product_information were created in the "Examples " section of CREATE MATERIALIZED VIEW LOG. This example also requires a materialized view log on oe.inventories.

CREATE MATERIALIZED VIEW LOG ON inventories
WITH (quantity_on_hand);

CREATE MATERIALIZED VIEW warranty_orders REFRESH FAST AS
SELECT order_id, line_item_id, product_id FROM order_items o
WHERE EXISTS
(SELECT * FROM inventories i WHERE o.product_id = i.product_id
AND i.quantity_on_hand IS NOT NULL)
UNION
SELECT order_id, line_item_id, product_id FROM order_items
WHERE quantity > 5;

This materialized view requires that materialized view logs be defined on order_items (with product_id as a join column) and on inventories (with quantity_on_hand as a filter column).

Automatic Refresh Times for Materialized Views: Example

2008-09-20T19:22:00.000-07:00

Automatic Refresh Times for Materialized Views: Example

The following statement creates the complex materialized view all_customers that queries the employee tables on the remote and local databases:

CREATE MATERIALIZED VIEW all_customers
PCTFREE 5 PCTUSED 60
TABLESPACE example
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K)
REFRESH START WITH ROUND(SYSDATE + 1) + 11/24
NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24
AS SELECT * FROM sh.customers@remote
UNION
SELECT * FROM sh.customers@local;

Oracle Database automatically refreshes this materialized view tomorrow at 11:00 a.m. and subsequently every Monday at 3:00 p.m. The default refresh method is FORCE. The defining query contains a UNION operator, which is not supported for fast refresh, so the database will automatically perform a complete refresh.

The preceding statement also establishes storage characteristics for both the materialized view and the index that the database uses to maintain it:

*The first STORAGE clause establishes the sizes of the first and second extents of the materialized view as 50 kilobytes each.
*The second STORAGE clause, appearing with the USING INDEX clause, establishes the sizes of the first and second extents of the index as 25 kilobytes each.

Periodic Refresh of Materialized Views: Example

2008-09-20T19:21:00.001-07:00

Periodic Refresh of Materialized Views: Example

The following statement creates the primary key materialized view emp_data and populates it with data from the sample table hr.employees:

CREATE MATERIALIZED VIEW LOG ON employees
WITH PRIMARY KEY
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW emp_data
PCTFREE 5 PCTUSED 60
TABLESPACE example
STORAGE (INITIAL 50K NEXT 50K)
REFRESH FAST NEXT sysdate + 7
AS SELECT * FROM employees;

The statement does not include a START WITH parameter, so Oracle Database determines the first automatic refresh time by evaluating the NEXT value using the current SYSDATE. A materialized view log was created for the employee table, so Oracle Database performs a fast refresh of the materialized view every 7 days, beginning 7 days after the materialized view is created.

Because the materialized view conforms to the conditions for fast refresh, the database will perform a fast refresh. The preceding statement also establishes storage characteristics that the database uses to maintain the materialized view.

Creating Rowid Materialized Views: Example

2008-09-20T19:20:00.001-07:00

Creating Rowid Materialized Views: Example

The following statement creates a rowid materialized view on the sample table oe.orders:

CREATE MATERIALIZED VIEW order_data REFRESH WITH ROWID
AS SELECT * FROM orders;

Creating Primary Key Materialized Views: Example

2008-09-20T19:19:00.002-07:00

Creating Primary Key Materialized Views: Example

The following statement creates the primary key materialized view catalog on the sample table oe.product_information:

CREATE MATERIALIZED VIEW catalog
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/4096
WITH PRIMARY KEY
AS SELECT * FROM product_information;

Creating Subquery Materialized Views: Example

2008-09-20T19:19:00.001-07:00

Creating Subquery Materialized Views: Example

The following statement creates a subquery materialized view based on the customers and countries tables in the sh schema at the remote database:

CREATE MATERIALIZED VIEW foreign_customers FOR UPDATE
AS SELECT * FROM sh.customers@remote cu
WHERE EXISTS
(SELECT * FROM sh.countries@remote co
WHERE co.country_id = cu.country_id);

Creating Prebuilt Materialized Views: Example

2008-09-20T19:18:00.003-07:00

Creating Prebuilt Materialized Views: Example

The following statement creates a materialized aggregate view for the preexisting summary table, sales_sum_table:

CREATE TABLE sales_sum_table
(month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2));

CREATE MATERIALIZED VIEW sales_sum_table
ON PREBUILT TABLE WITH REDUCED PRECISION
ENABLE QUERY REWRITE
AS SELECT t.calendar_month_desc AS month,
c.cust_state_province AS state,
SUM(s.amount_sold) AS sales
FROM times t, customers c, sales s
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
GROUP BY t.calendar_month_desc, c.cust_state_province;

In this example, the materialized view has the same name and also has the same number of columns with the same datatypes as the prebuilt table. The WITH REDUCED PRECISION clause allows for differences between the precision of the materialized view columns and the precision of the values returned by the subquery.

Creating Materialized Join Views: Example

2008-09-20T19:18:00.001-07:00

Creating Materialized Join Views: Example

The following statement creates and populates the materialized aggregate view sales_by_month_by_state using tables in the sample sh schema. The materialized view will be populated with data as soon as the statement executes successfully. By default, subsequent refreshes will be accomplished by reexecuting the defining query of the materialized view:

CREATE MATERIALIZED VIEW sales_by_month_by_state
TABLESPACE example
PARALLEL 4
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT t.calendar_month_desc, c.cust_state_province,
SUM(s.amount_sold) AS sum_sales
FROM times t, sales s, customers c
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
GROUP BY t.calendar_month_desc, c.cust_state_province;

Creating Materialized Aggregate Views: Example

2008-09-20T19:17:00.001-07:00

The following statement creates and populates a materialized aggregate view on the sample sh.sales table and specifies the default refresh method, mode, and time. It uses the materialized view log created in "Creating a Materialized View Log: Examples", as well as the two additional logs shown here:

CREATE MATERIALIZED VIEW LOG ON times
WITH ROWID, SEQUENCE (time_id, calendar_year)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON products
WITH ROWID, SEQUENCE (prod_id)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;

Privilages to create a refresh-on-commit materialized view

2008-09-20T19:11:00.000-07:00

To create a refresh-on-commit materialized view (ON COMMIT REFRESH clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege.

To create the materialized view with query rewrite enabled, in addition to the preceding privileges:

*If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside the schema.
*If you are defining the materialized view on a prebuilt container (ON PREBUILT TABLE clause), then you must have the SELECT privilege WITH GRANT OPTION on the container table.

The user whose schema contains the materialized view must have sufficient quota in the target tablespace to store the master table and index of the materialized view or must have the UNLIMITED TABLESPACE system privilege.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

Privilages to create a materialized view in another user's schema

2008-09-20T19:10:00.000-07:00

To create a materialized view in another user's schema:

*You must have the CREATE ANY MATERIALIZED VIEW system privilege.
*The owner of the materialized view must have the CREATE TABLE system privilege. The owner must also have access to any master tables of the materialized view that the schema owner does not own (for example, if the master tables are on a remote database) and to any materialized view logs defined on those master tables, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

What Privilages required to create a materialized view?

2008-09-20T19:07:00.000-07:00

The privileges required to create a materialized view should be granted directly rather than through a role.

To create a materialized view in your own schema:

*You must have been granted the CREATE MATERIALIZED VIEW system privilege and either the CREATE TABLE or CREATE ANY TABLE system privilege.
*You must also have access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

What is materialized view?

2008-09-20T19:05:00.000-07:00

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term).

The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.

What is Optimizer Plan Stability?

2008-09-20T18:57:00.000-07:00

Plan stability prevents certain database environment changes from affecting the performance characteristics of applications. Such changes include changes in optimizer statistics, changes to the optimizer mode settings, and changes to parameters affecting the sizes of memory structures, such as SORT_AREA_SIZE and BITMAP_MERGE_AREA_SIZE. Plan stability is most useful when you cannot risk any performance changes in an application.

Plan stability preserves execution plans in stored outlines. An outline is implemented as a set of optimizer hints that are associated with the SQL statement. If the use of the outline is enabled for the statement, Oracle automatically considers the stored hints and tries to generate an execution plan in accordance with those hints.

Oracle can create a public or private stored outline for one or all SQL statements. The optimizer then generates equivalent execution plans from the outlines when you enable the use of stored outlines. You can group outlines into categories and control which category of outlines Oracle uses to simplify outline administration and deployment.

The plans Oracle maintains in stored outlines remain consistent despite changes to a system's configuration or statistics. Using stored outlines also stabilizes the generated execution plan if the optimizer changes in subsequent Oracle releases.

This utility has several features:

SQL parsing and execution time is reduced because Oracle will quickly grab and execute the stored outline for the SQL.

Tuning of SQL statements can easily be made permanent without locating the source code.

SQL from third-party products (e.g., SAP, Peoplesoft) can be tuned without touching the SQL source code.

Optimizer plan stability enables you to maintain the same execution plans for the same SQL statements, regardless of changes to the database. Without optimizer plan stability, any of the following database changes may alter the execution plan for every SQL statement on your system:

1 – Re-analyzing tables
2 - Adding or deleting data from tables
3 - Modifying a table's columns, constraints, or indexes
4 - Changing the system configuration
5 - Upgrading to a new version of the optimizer.

DBMS_ADVISOR Package

2008-09-20T18:45:00.000-07:00

The DBMS_ADVISOR package is new to Oracle 10g. It is used to provide the PL/SQL application programming interface (API), which manages all the advisors. The DBMS_ADVISOR package is also used to create and execute any advisor tasks, including SQL Access Advisor tasks. There are many procedures available for the package, as given below:

Procedure name Description
CREATE_TASK Creates a new advisor task in the repository.
SET_TASK_PARAMETER Modifies a user parameter within a task (a task can only be modified if it is in the initial state).
EXECUTE_TASK Executes the specified task (analysis task in the asked case).
GET_TASK_REPORT Creates and returns an XML report for the specified task.
DELETE_TASK Deletes the specified advisor task.
CANCEL_TASK Cancels a currently executing task.
INTERRUPT_TASK Stops the currently executing task, with normal exit.
RESUME_TASK Resumes an interrupted task.
RESET_TASK Resets a task to its original state.
UPDATE_TASK_ATTRIBUTES Changes various attributes of a task.
MARK_RECOMMENDATION Accepts, rejects, or ignores a recommendation.
GET_TASK_SCRIPT Creates a SQL*Plus-compatible SQL script of all the recommendations that are accepted from a specified task.
CREATE_FILE Creates an external file from a PL/SQL CLOB variable, used for creating scripts and reports.
QUICK_TIME Analyzes and generates recommendations for a single SQL statement.

Oracle10g introduced several intelligent utilities that produce performance tuning advices and suggestions based on historical data stored in the AWR. These utilities are called advisors. The dbms_advisor package provides PL/SQL API to access the following advisors:

The Automatic Database Diagnostic Monitor (ADDM) provides Oracle DBAs with performance data gathered by the AWR. The ADDM also identifies root causes of performance bottlenecks and generates a report containing recommendations or findings on how to improve database performance.

The SQLAccess Advisor provides analysis and recommendations about indexes and materialized views which may improve system performance.

Summary of DBMS_ADVISOR Subprograms

Subprogram
Description
Used in

ADD_SQLWKLD_REF Procedure
Adds a workload reference to an Advisor task
SQL Access Advisor only

ADD_SQLWKLD_STATEMENT Procedure
Adds a single statement to a workload
SQL Access Advisor only

ADD_STS_REF Procedure
Establishes a link between the current SQL Access Advisor task and a SQL Tuning Set
SQL Access Advisor only

CANCEL_TASK Procedure
Cancels a currently executing task operation
All Advisors

COPY_SQLWKLD_TO_STS Procedure
Copies the contents of a SQL workload object to a SQL Tuning Set
SQL Access Advisor

CREATE_FILE Procedure
Creates an external file from a PL/SQL CLOB variable, which is useful for creating scripts and reports
All Advisors

CREATE_OBJECT Procedure
Creates a new task object
All Advisors

CREATE_SQLWKLD Procedure
Creates a new workload object
SQL Access Advisor only

CREATE_TASK Procedures
Creates a new Advisor task in the repository
All Advisors

DELETE_SQLWKLD Procedure
Deletes an entire workload object
SQL Access Advisor only

DELETE_SQLWKLD_REF Procedure
Deletes an entire workload object
SQL Access Advisor only

DELETE_SQLWKLD_STATEMENT Procedures
Deletes one or more statements from a workload
SQL Access Advisor only

DELETE_STS_REF Procedure
Removes a link between the current SQL Access Advisor task and a SQL Tuning Set object
SQL Access Advisor only

DELETE_TASK Procedure
Deletes the specified task from the repository
All Advisors

EXECUTE_TASK Procedure
Executes the specified task
All Advisors

GET_REC_ATTRIBUTES Procedure
Retrieves specific recommendation attributes from a task
All Advisors

GET_TASK_REPORT Function
Creates and returns a report for the specified task
All Advisors

GET_TASK_SCRIPT Function
Creates and returns an executable SQL script of the Advisor task's recommendations in a buffer
All Advisors

IMPLEMENT_TASK Procedure
Implements the recommendations for a task
All Advisors

IMPORT_SQLWKLD_SCHEMA Procedure
Imports data into a workload from the current SQL cache
SQL Access Advisor only

IMPORT_SQLWKLD_SQLCACHE Procedure
Imports data into a workload from the current SQL cache
SQL Access Advisor only

IMPORT_SQLWKLD_STS Procedure
Imports data into a workload from a SQL Tuning Set into a SQL workload data object
SQL Access Advisor only

IMPORT_SQLWKLD_SUMADV Procedure
Imports data into a workload from the current SQL cache
SQL Access Advisor only

IMPORT_SQLWKLD_USER Procedure
Imports data into a workload from the current SQL cache
SQL Access Advisor only

INTERRUPT_TASK Procedure
Stops a currently executing task, ending its operations as it would at a normal exit, so that the recommendations are visible
All Advisors

MARK_RECOMMENDATION Procedure
Sets the annotation_status for a particular recommendation
All Advisors

QUICK_TUNE Procedure
Performs an analysis on a single SQL statement
All Advisors

RESET_TASK Procedure
Resets a task to its initial state
All Advisors

SET_DEFAULT_SQLWKLD_PARAMETER Procedures
Imports data into a workload from schema evidence
SQL Access Advisor only

SET_DEFAULT_TASK_PARAMETER Procedures
Modifies a default task parameter
All Advisors

SET_SQLWKLD_PARAMETER Procedures
Sets the value of a workload parameter
SQL Access Advisor only

SET_TASK_PARAMETER Procedure
Sets the specified task parameter value
All Advisors

TUNE_MVIEW Procedure
Shows how to decompose a materialized view into two or more materialized views or to restate the materialized view in a way that is more advantageous for fast refresh and query rewrite
SQL Access Advisor only

UPDATE_OBJECT Procedure
Updates a task object
All Advisors

UPDATE_REC_ATTRIBUTES Procedure
Updates an existing recommendation for the specified task
All Advisors

UPDATE_SQLWKLD_ATTRIBUTES Procedure
Updates a workload object
SQL Access Advisor only

UPDATE_SQLWKLD_STATEMENT Procedure
Updates one or more SQL statements in a workload
SQL Access Advisor only

UPDATE_TASK_ATTRIBUTES Procedure
Updates a task's attributes
All Advisors

What is Bigfile tablespace? why do we create it? what is the advantage of using it?

2008-09-20T18:36:00.000-07:00

Bigfile Tablespaces
Bigfile tablespaces are tablespaces with a single large datafile. In contrast normal (smallfile) tablespaces can have several datafiles, but each is limited in size. The benefits of bigfile tablespaces are:

Creating Bigfile tables space

-- Valid statement.
CREATE BIGFILE TABLESPACE mybigtbs
DATAFILE '/u01/oracle/MYSID/mybigtbs01.dbf' SIZE 100G;

-- Valid statement.
CREATE BIGFILE TABLESPACE mybigtbs
DATAFILE '/u01/oracle/MYSID/mybigtbs01.dbf' SIZE 100G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

-- invalid statement.
CREATE BIGFILE TABLESPACE mybigtbs
DATAFILE '/u01/oracle/MYSID/mybigtbs01.dbf' SIZE 100G
EXTENT MANAGEMENT DICTIONARY
SEGMENT SPACE MANAGEMENT MANUAL;

* An Oracle database can have a maximum of 64,000 datafiles which limits its total capacity. By allowing tablespaces to have a single large datafile the total capacity of the database is increased. A bigfile tablespace with 8K and 32K blocks can contain 32 terabyte and 128 terabyte datafiles respectively.
* Using fewer larger datafiles allows the DB_FILES and MAXDATAFILES parameters to be reduced, saving SGA and controlfile space.
* The ALTER TABLESPACE syntax has been updated to allow operations at the tablespace level, rather than datafile level.

Typically bigfile tablespaces must be locally managed with automatic segment-space management. Exceptions to this rule include temporary tablespaces, the SYSTEM tablespace and locally managed undo tablespaces which are all allowed to have manual segment-space management.

Bigfile tablespaces are create using the CREATE BIGFILE TABLESPACE command. The first two of the following statements have the same affect as the EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO clauses are the default actions, while the last statement would error.

Reverting a Table to its Previous Statistics

2008-09-20T18:33:00.001-07:00

Reverting a Table to its Previous Statistics

Usually, re-analyzing a schema and specifying a high percentage of rows for the sample size will improve performance. Unfortunately, the occasional hiccup will occur when you re-analyze tables. Sometimes the new statistics produce much worse execution plans than before. You can avoid the risk of a major screw up by using the DBMS_STATS package to save a copy of your current statistics just in case you need to restore them later. This requires the following steps:

Step 1.
Export your schema statistics to your statistics table. If you don’t already have a statistics table, you can create it using the DBMS_STATS.CREATE_STATS_TABLE procedure. The export is performed as follows:

EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=>'STRINIX', STATTAB=> 'PAR_STAT_TABLE', STATID=>'STAT_12DEC_2007');

Step 2. Gather your new statistics:

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'STRINIX', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE);

Step 3. If there are problems with unsuitable execution paths being selected as a result of the new statistics, revert back to the previous statistics by loading the previous statistics from the statistics table:

EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=>'HROA', STATTAB=>'HROA_STAT_TABLE', STATID=>'START_12DEC_2007');

Manipulating Statistics Using DBMS_STATS

2008-09-20T18:32:00.001-07:00

Manipulating Statistics Using DBMS_STATS

You can modify the individual statistic elements on a table. Perhaps this be mightbe useful where in if you want to replicate the table stats in development similar to that of production or replicate the table stats in production similar to that of development.

You can achieve this by using DBMS_STATS.SET_TABLE_STATS to modify the statistics for a table in your development or for a test database to match those in your production database. The optimizer uses the number of rows, number of blocks, and number of distinct values for a column to determine whether an index or a full table scan should be used.

The following example assumes that your symb_history table is going to have 20,00,567 rows in 9,000 blocks:

EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>'STRINIX_TEST', TABNAME=>'SYMB_HISTORY', NUMROWS=> 2000567, NUMBLKS=> 9000);

Regardless of how many rows you really have in your test database for the table SYMB_HISTORY, the cost-based optimizer will now behave as if there were 20,00,567.

The optimizer also uses the number of distinct values for each column to decide on index usage. If the number of distinct values is less than 10% of the number of rows in the table, the optimizer will usually decide to perform a full table scan in preference to using an index on the table column. Change the percentage of distinct values for a column as follows:

EXECUTE SYS.DBMS_STATS.SET_COLUMN_STATS (OWNNAME=>'STRINIX_TEST', TABNAME=>'COMP_SCRIP_MAP',COLNAME=>'SYMBOL', DISTCNT=>400);javascript:void(0)

Copying Statistics Using DBMS_STATS

2008-09-20T18:31:00.001-07:00

Copying Statistics Using DBMS_STATS

DBMS_STATS package provides you the ability to copy statistics from one schema to another, or from one oracle database to another Oracle database, using the following procedure:

Step1:
First save the statistics to a table. Create a table to store the statistics, DBMS_STATS.CREATE_STATS_TABLE can be used to create a new table in your own schema to store the statistics of tables and indexes.

EXECUTE DBMS_STATS.CREATE_STATS_TABLE (OWNNAME=>'STRINIX', STATTAB=>'PAR_STAT_TAB');

Step 2. Populate the table with the statistics from the schema that you are copying from:

EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=>'STRINIX', STATTAB=>'PAR_STAT_TAB', STATID=> 'STAT_19DEC_2007');

Step 3. If you are copying statistics to a different database, such as from production to development, export and import that statistics table as required:

exp strinix/baba@prod file=stats tables=PAR_stat_tab
imp strinix/babat@dev file=stats tables=PAR_stat_tab

Step 4. Populate the statistics in the target schema’s dictionary. Below example show you that statistics are being loaded for the schema STRINIX_TEST from the table named PAT_STAT_TAB:

EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=>'STRINIX_TEST', STATTAB=>'PAR_STAT_TAB', STATID=>'STAT_19DEC_2007', STATOWN=> 'STRINIX');

Gathering statistics with DBMS_STAT Package

2008-09-20T18:28:00.000-07:00

DBMS_STATS to Manage Statistics

DBMS_STATS package provides critical functionality for the cost-based optimizer, including the analyzing process extended to PL/SQL programs, allowing statistics to be modified, reverting back to previous statistics, and copying statistics from one schema (or database) to another.

By USING DBMS_STATS package you can

* You can collects statistics on tables and Indexes
* Modify Statistics on tables.
* Save statitstics at a certain point of time
* Revert back statistics to the Previously saved statistics.
* Copy Statistics from One Schema to another Schema.
* Import statistics from one database to another

Handling Errors During Backups: Example

2008-09-20T18:17:00.001-07:00

Handling Errors During Backups: Example

By default a checksum is calculated for every block read from a datafile and stored in the backup or image copy. If you use the NOCHECKSUM option, then checksums are not calculated. If the block already contains a checksum, however, then the checksum is validated and stored in the backup. If the validation fails, then the block is marked corrupt in the backup.

The SET MAXCORRUPT FOR DATAFILE command sets how many corrupt blocks in a datafile that BACKUP will tolerate. If a datafile has more corrupt blocks than specified by the MAXCORRUPT parameter, the command terminates. If you specify the CHECK LOGICAL option, RMAN checks for logical and physical corruption.

By default, the BACKUP command terminates when it cannot access a datafile. You can specify parameters to prevent termination, as listed in the following table.
If you specify the option ... Then RMAN skips...

SKIP INACCESSIBLE

Inaccessible datafiles. A datafile is only considered inaccessible if it cannot be read. Some offline datafiles can still be read because they exist on disk. Others have been deleted or moved and so cannot be read, making them inaccessible.

SKIP OFFLINE

Offline datafiles.

SKIP READONLY

Datafiles in read-only tablespaces.

The following example uses an automatic channel to back up the database, and sets the corruption level for the datafile in the SYSTEM tablespace so that up to 10 errors will be accepted:

RMAN> RUN
{
SET MAXCORRUPT FOR DATAFILE 1 TO 10;
BACKUP DATABASE
SKIP INACCESSIBLE
SKIP READONLY
SKIP OFFLINE;
}

Backup Validation with RMAN

2008-09-20T18:11:00.000-07:00

Backup Validation with RMAN

You can run the BACKUP ... VALIDATE command to check datafiles for physical and logical corruption, or to confirm that all database files exist in the correct locations. No backup is taken, but all specified files are scanned to verify that they can be backed up. Here is an example:

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

You cannot use the MAXCORRUPT or PROXY parameters with the VALIDATE option.

You can use the VALIDATE keyword of the BACKUP command to do the following:

* Check datafiles for physical and logical corruption
* Confirm that all database files exist and are in the correct locations

RMAN does not actually produce backup sets, but rather reads the specified files in their entirety, to determine whether they can be backed up and are not corrupted. In this sense, the BACKUP VALIDATE command is similar to the RESTORE VALIDATE command, except for backups rather than restore jobs.

If the backup validation discovers corrupt blocks, then RMAN updates the V$DATABASE_BLOCK_CORRUPTION view with rows describing the corruptions. After a corrupt block is repaired, the row identifying this block is deleted from the view.

For example, you can validate that all database files and archived redo logs can be backed up by running a command as follows:

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

This form of the command would check for physical corruption. To check for logical corruption,

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

RMAN displays the same output that it would if it were really backing up the files. If RMAN cannot validate the backup of one or more of the files, then it displays an error message. For example, RMAN may show output similar to the following:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/29/2001 14:33:47
ORA-19625: error identifying file /oracle/oradata/trgt/arch/archive1_6.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

You cannot use the MAXCORRUPT or PROXY parameters with the VALIDATE option.

Detection of Logical Block Corruption

2008-09-20T18:10:00.001-07:00

Besides testing for media corruption, the database can also test data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log. If CHECK LOGICAL was used, the block is also logged in the server session trace file. By default, error checking for logical corruption is disabled.

For BACKUP commands the MAXCORRUPT parameter sets the total number of physical and logical corruptions permitted in a file. If the sum of physical and logical corruptions for a file is less than its MAXCORRUPT setting, the RMAN command completes successfully. If MAXCORRUPT is exceeded, the command terminates and RMAN does not read the rest of the file. V$DATABASE_BLOCK_CORRUPTION is populated with corrupt block ranges if the command succeeds. Otherwise, you must set MAXCORRUPT higher and re-run the backup to find out the corrupt block ranges.

Detecting Physical and Logical Block Corruption

2008-09-20T18:08:00.000-07:00

Because an database server session is performing the backups and has a great understanding of files being backed up or copied, the server session is able to detect many types of physically corrupt blocks during the backup process. Each new corrupt block not previously encountered in a backup is recorded in the control file and in the alert.log. By default, error checking for physical corruption is enabled.

At the end of a backup, RMAN stores the corruption information in the recovery catalog and control file. Access this data using the V$DATABASE_BLOCK_CORRUPTION view.

If the server session encounters a datafile block during a backup that has already been identified as corrupt by the database, then the server session copies the corrupt block into the backup and the corrupt block is recorded the control file as either a logical or media corruption. RMAN copies the block in case the user wants to try to salvage the contents of the block.

If RMAN encounters a datafile block that has media corruption but that has not already been identified as corrupt by the database, then it writes the block to the backup with a reformatted header indicating that the block has media corruption.

Tests and Integrity Checks for Backups?

2008-09-20T18:04:00.000-07:00

The database prevents operations that result in unusable backup files or corrupt restored datafiles. The database server automatically does the following:

* Blocks access to datafiles while they are being restored or recovered
* Allows only one restore operation for each datafile at a time
* Ensures that incremental backups are applied in the correct order
* Stores information in backup files to allow detection of corruption

You can use the BACKUP VALIDATE and RESTORE VALIDATE commands to test backup and restore operations without creating output files. In this way, you can check your datafiles for possible problems. If you run RMAN with the following configuration, then it detects all types of corruption that are possible to detect:

* In the initialization parameter file, set DB_BLOCK_CHECKSUM=TRUE
* In the RMAN BACKUP and RESTORE commands, do not specify the MAXCORRUPT option, do not specify the NOCHECKSUM option, but do specify the CHECK LOGICAL option

When RMAN Performs Control File Autobackups

2008-09-20T13:22:00.001-07:00

When RMAN Performs Control File Autobackups

By default, control file autobackups are turned off, and no control file autobackups are performed. If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up.
Control File Autobackups After Backup Acivities

This means that the control file is backed up in the following situations:

* After every BACKUP command issued at the RMAN prompt.
* At the end of a RUN block, if the last command in the block was BACKUP.
* Whenever a BACKUP command within a RUN block is followed by a command that is not BACKUP.

The first channel allocated during the backup job creates the autobackup and places it into its own backup set. The control file autobackup may be written to disk or tape.
Control File Autobackups After Database Structural Changes

The control file is also automatically backed up after database structural changes such as adding a new tablespace, altering the state of a tablespace or datafile (for example, bringing it online), adding a new online redo log, renaming a file, adding a new redo thread, and so on. Losing this information would compromise your ability to recover the database.

This backup is performed by the server process itself, rather than one of the RMAN channels. This type of autobackup, unlike autobackups that occur after a successful backup, is always created on disk. You can use CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE DISK to set the location for this disk based control file autobackup. Note that a failure of the automatic control file autobackup after a structural change never causes the associated structural change to fail. For example, if you add a datafile, and if the resulting control file autobackup fails, then the datafile addition is still successful.

How RMAN Performs Control File Autobackups?

2008-09-20T13:20:00.001-07:00

How RMAN Performs Control File Autobackups

The first channel allocated during the backup job creates the autobackup and places it into its own backup set; for autobackups after database structural changes, the default disk channel makes the backup. If a server parameter file is used, it is backed up in the same backup set as the control file during a control file autobackup.

After the control file autobackup completes, the database writes a message containing the complete path of the backup piece and the device type to the alert log.

The RMAN behavior when the BACKUP command includes datafile 1 depends on the CONFIGURE CONTROLFILE AUTOBACKUP setting. If control file autobackups are ON and the backup includes datafile 1, RMAN writes the control file and SPFILE to a separate autobackup backup set. If control file autobackups are OFF and the backup includes datafile 1, then RMAN includes the current control file and SPFILE in the same backup set as the datafiles.

The control file autobackup filename has a default format of %F for all device types, so that RMAN can guess the file location and restore it without a repository. The substitution variable %F is defined in the description of the CONFIGURE command in Oracle Database Backup and Recovery Basics. You can specify a different format with the CONFIGURE CONTROLFILE AUTOBACKUP FORMAT command. All autobackup formats must include the %F variable.

The SET CONTROLFILE AUTOBACKUP FORMAT command, which you can specify either within a RUN block or at the RMAN prompt, overrides the configured autobackup format in the session only. The order of precedence is:

1. SET within a RUN block
2. SET at RMAN prompt
3. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT

You can configure the autobackup format even when CONFIGURE CONTROLFILE AUTOBACKUP is set to OFF, but RMAN does not generate autobackups in this case. For RMAN to make autobackups, you must set CONFIGURE CONTROLFILE AUTOBACKUP to ON.

what command do you execute to ensure that the control file is automatically backed up using Recovery Manager (RMAN)?

2008-09-20T13:18:00.000-07:00

what command do you execute to ensure that the control file is automatically backed up using Recovery Manager?

The automatic backup of the control file occurs independently of any backup of the current control file explicitly requested as part of your backup command. You can turn the autobackup feature on or off by running the following commands:

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;

Which background processes coordinates the rebalancing of disks in ASM

2008-09-20T13:07:00.002-07:00

In addition to the normal database background processes like CKPT, DBWR, LGWR, SMON, and PMON, an ASM instance uses at least two additional background processes to manage data storage operations. The Rebalancer process, RBAL, coordinates the rebalance activity for ASM disk groups, and the Actual ReBalance processes, ARBn, handle the actual rebalance of data extent movements. There are usually several ARB background processes (ARB0, ARB1, and so forth). I will talk more about disk rebalancing operations in the final article in this series.

Which background processes coordinates the rebalancing of disks in ASM

2008-09-20T13:07:00.001-07:00

What is oracle ASM in Oracle 10g? How do we create ASM instance?

2008-09-20T12:53:00.000-07:00

Automatic Storage Management (ASM) is a new feature that was introduced in Oracle 10g to simplify the storage of Oracle datafiles, controlfiles and logfiles.

Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.

ASM provides the following functionality:

* Manages groups of disks, called disk groups.
* Manages disk redundancy within a disk group.
* Provides near-optimal I/O balancing without any manual tuning.
* Enables management of database objects without specifying mount points and filenames.
* Supports large files.

Creating ASM instance

Step1. To create an ASM instance first create a file called initASM.ora.
INSTANCE_TYPE=ASM

The initialization parameters that are of specific interest for an ASM instance are:

INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.

Step 2: using SQL*Plus connect to the idle instance.

export ORACLE_SID=+ASM
sqlplus / as sysdba

Step3: Create an spfile using the contents of the initASM.ora file.

SQL> CREATE SPFILE FROM PFILE='initASM.ora';

File created.
Step 4: Start the instance with the NOMOUNT option.

SQL> startup nomount
ASM instance started

The ASM instance is now ready to use for creating and mounting disk groups. To shutdown the ASM instance issue the following command.

SQL> shutdown
ASM instance shutdown
SQL>

Startup and Shutdown of ASM Instances
ASM instance are started and stopped in a similar way to normal database instances. The options for the STARTUP command are:

* FORCE - Performs a SHUTDOWN ABORT before restarting the ASM instance.
* MOUNT - Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.
* NOMOUNT - Starts the ASM instance without mounting any disk groups.
* OPEN - This is not a valid option for an ASM instance.

The options for the SHUTDOWN command are:

* NORMAL - The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
* IMMEDIATE - The ASM instance waits for any SQL transactions to complete then shuts down. It doesn't wait for sessions to exit.
* TRANSACTIONAL - Same as IMMEDIATE.
* ABORT - The ASM instance shuts down instantly.

Load Balancing In Oracle 10g Real Application Clusters Environments

2008-09-20T07:45:00.000-07:00

An Oracle 10gR2 Real Application Clusters (RAC) clustered database needs more robust load balancing capabilities because of the nature of that environment.

A RAC clustered database comprises at least two (and usually many more) nodes, each running a separate instance of the clustered database. In addition, a RAC database usually needs to supply a minimum amount of connections and resources to several applications, each with dramatically different resource needs depending on the current business processing cycle(s), so the application load that’s placed on each instance in the clustered database therefore can be dramatically different at different times of the day, week, month, and year. Finally, it’s likely that a RAC clustered database will need to guarantee a minimum cardinality (i.e. a specific number of nodes on which the application needs to run at all times) to one or more mission-critical applications.

RAC Services. Starting in Oracle 8i, an Oracle database could dynamically register a database directly with its corresponding listener(s) based on the settings for the SERVICE_NAMES initialization parameter through the database’s Process Monitor (PMON) background process. To completely support this feature, Oracle strongly suggested that the SERVICE_NAME parameter should be used instead of the original SID parameter in the TNSNAMES.ORA configuration file so that an incoming user session could immediately identify the database instance to which a session intended to connect.

Oracle 10g RAC leverages this service naming feature to distribute application connections efficiently across a RAC clustered database. For example, a clustered database may need to support three different applications, OLTP, DSS, and ADHOC. The OLTP application is the main order entry application for this enterprise computing environment, and therefore it needs a minimum cardinality of two cluster database instances at all times. The DSS application, on the other hand, supports extraction, transformation and load (ETL) operations for the enterprise’s data warehouse, and thus it requires a minimum cardinality of just one instance. Likewise, the ADHOC application supports OLAP and general user query execution against the data warehouse, but it too only requires a minimum cardinality of a single instance.
Oracle 10gR2 RAC: Server-Side Connect-Time Load Balancing

It’s also important to realize that in a RAC environment, the server-side load balancing methodology differs slightly from the methodology used in a single-instance environment because Oracle 10gR2 discriminates whether the incoming connection has been requested as either a dedicated or a shared server connection:

* If a dedicated session is requested, then the listener will select the instance first on the basis of the node that is least loaded; if all nodes are equally loaded, it will then select the instance that has the least load.
* For a shared server connection, however, the listener goes one step further. It will also check to see if all of the available instances are equally loaded; if this is true, the listener will place the connection on the least-loaded dispatcher on the selected instance.

Advanced Load Balancing: The Load Balancing Advisor (LBA)

In a RAC environment, it’s not unlikely that one node may become overwhelmed by application requests for resources. For example, let’s assume that a two-node clustered database currently has 100 user sessions connected across both nodes in the cluster, and that the database is using standard server-side load balancing. If there is a sudden “storm” of 200 additional connections, the listeners on the two nodes will simply distribute them evenly across both nodes, 100 to each node, resulting in 150 connections per node. However, it’s possible that node RACDB2 is actually much “busier” than the other node. As a result, node RACDB2 will most likely be completely overwhelmed by those new connections’ resource demands, while node RACDB1 remains relatively underutilized with plenty of additional resources.

The good news is that Oracle 10gR2 now provides an advanced method to overcome this imbalance: the Load Balancing Advisor (LBA). The LBA will calculate how much work each instance in the clustered database has been asked to do. The LBA will then make sure that any new incoming work is routed to the instance(s) that are least busy. This determination takes place on a service-by-service basis across all instances that are providing resources to each service.

The LBA considers the following factors when determining how to perform this “balancing act”:

* Are there any differences in processing power between nodes?
* Are there any sessions that are currently blocked from execution because of waits?
* Have any failures occurred on a node that might block processing from continuing?
* Are there any services that are competing for resources, and have those services been granted a different priority to complete their tasks at hand?

Not only does this help to balance out the total workload across all instances in the cluster, it also insures that one node won’t be overwhelmed by requests for service by its primary application(s).

As part of its Fast Application Notification (FAN) event transmission features, Oracle 10g RAC uses Oracle Notification Services (ONS) to communicate the status of cluster resources to all participating node applications in the cluster. In Oracle 10gR2, FAN added a new event type, SERVICE_METRIC, whose event payload contains information about the relative workload of each node in the RAC cluster, and it’s this information that the Load Balancing Advisory uses to determine how to route new connections throughout the clustered database’s instances.

Oracle 10gR2 supplies an upgraded DBMS_SERVICE.MODIFY_SERVICE procedure that modifies an existing RAC service so that it can utilize the Load Balance Advisory for service-by-service load balancing. This procedure provides two crucial parameters, GOAL and CLB_GOAL, that interact to determine what methods (if any) Oracle 10gR2 will utilize to perform load balancing. For example, if I set the GOAL parameter to either the GOAL_SERVICE_TIME or the GOAL_THROUGHPUT enumerated constants for a specific RAC service, Oracle 10gR2 will activate the Load Balancing Advisory for load balancing of that service’s incoming connections. Table 1 explains the difference between these two load balancing targets.

Oracle 10gR2 load balancing - Server side load balancing

2008-09-20T07:42:00.000-07:00

The two previous methods will adequately handle the distribution of user sessions across available resources while helping to guarantee that no session will wait an excessive time to find a currently active address on which to connect. Clearly, a better solution was needed, and Oracle 9i offered one: server-side load balancing. This method divides the connection load evenly between all available listeners by determining the total number of connections on each listener, and then distributing new user session connection requests to the least loaded listener(s) based on the total number of sessions already connected. While a bit more complex to implement because it requires configuration of multiple listeners, it most definitely helps to even out connections across all available listeners in a database system.

To implement server-side load balancing, at least two listeners must be configured. Also, the REMOTE_LISTENERS initialization parameter must be added to the database’s PFILE or SPFILE so that the database knows to search out the value provided in that parameter in the database server’s TNSNAMES.ORA configuration file. When server-side load balancing is activated, each listener that contributes a listening endpoint communicates with the other listener(s) via each database instance’s PMON process. Oracle then determines how many user connections each listener is servicing, and it will distribute any new connection requests so that the load is balanced evenly across all servers. The entries in TNSNAMES.ORA direct the listeners to share information about the relative load connectivity.

Oracle 10gR2 load balancing- Client-Side Connection Failover

2008-09-20T07:41:00.000-07:00

Client-Side Connection Failover. Obviously, balancing the overall number of connections is a desirable goal, but what happens if the chosen connection point is unavailable because the database server’s listener is no longer active? To forestall this, Oracle 8i provided the capability to determine if the connection that has been chosen at random is still “alive” and, if not, continue to try the other listed connection points until a live connection is found. This simple method tends to limit the chance of a lost connection, but unfortunately it must rely on TCP/IP timeouts to determine if a connection is alive or dead, and this means that an application may wait several seconds (or even longer!) before it receives a notification that the connection has been terminated.

Oracle 10gR2 load balancing - Part2 - Client-Side Load balancing

2008-09-20T07:35:00.000-07:00

Client-Side Load Balancing Methods

1. Client-Side Connection Load Balancing.
This load balancing method has been available since Oracle 8i. When a user session attempts to connect to the database, the database’s listener will assign the session randomly to one of the listed multiple listening endpoints.

While this load balancing method is certainly simplest to implement, it also has an obvious limitation: the listener has no idea if the session has been assigned to an endpoint whose corresponding database server is already overloaded. Moreover, since the listener is essentially picking the connection completely at random, there is no guarantee that the connection chosen will even be available at that time. This may force the session to wait for a considerable length of time – perhaps even minutes, a relative eternity in computing timeframes! – until the operating system indicates to the listener that the connection is unavailable, which causes the user session to fail eventually with an ORA-01034 ORACLE not available error.

Oracle 10gR2 load balancing - Part1

2008-09-20T07:33:00.000-07:00

Oracle 10gR2 takes advantage of simple client-side connection load balancing, server-side load balancing, and the new Load Balancing Advisor (LBA) features to significantly improve the distribution of workloads across the appropriate nodes in an Oracle 10gR2 Real Application Clusters (RAC) clustered database environment.

Oracle database tuning without any tools - using performance views - part 4 - Examine the Oracle Statistics

2008-09-19T07:48:00.003-07:00

Examine the Oracle Statistics

Oracle provides the init.ora parameter STATISTICS_LEVEL (BASIC, TYPICAL-default, ALL), which controls all major statistics collections or advisories in the database.

you can see the statistics level from v$statistics_level view.

1. wait events

main views to look for wait events are
1. v$session
2. v$session_wait
3. v$session_wait_class
4. v$system_wait_class
5. v$session_wait_history
6. v$waitclassmetric
7. v$active_session_history
8. v$sess_time_model
9. v$session_event
10. v$system_event
11. v$event_histogram
12. v$file_histogram
13. v$temp_histogram

wait events are grouped into classes. main classes are
1. administrative
2. application
3. cluster
4. concurrency
5. commit
6. configuration
7. idle
8. network
9. other,
10. scheduler
11. system i/o
12. user i/o

Oracle database tuning without any tools - using performance views - part 3 - Examine the Host system

2008-09-19T07:48:00.002-07:00

Examine the Host system

1. CPU usage
2. Non oracle processes running on the system
3. Oracle processes (SQL_TRACe and TKPROF to identify bad PL/SQl code or SQL query)
4. Oracle CPU statistics (look at the following v$ views v$sysstat, v$sesstat, v$rsrc_consumer_group)
5. interpreting CPU statistics
6. Detecting I/O problems
7. Detecting Network problem

Oracle database tuning without any tools - using performance views - part 2 - Define a problem

2008-09-19T07:48:00.001-07:00

Define a problem

It is very important to understand the purpose of tuning and nature of the problem before we start implementing the solution. If we do not understand, it will be almost impossible to implement effective changes. Collect as much data as you can in this stage. following data is vital for the next step

1. Identify the performance objective: what is the measure of acceptable performance i.e. response time that will meet the required performance level.

2. Identify the scope of the problem

what is affected by the slowdown?
Is it particular application?
Is it particular program?
Is it a particular operation?
Is it a single user?

3. Identify the time frame when the problem occurs.
Is it happening in a particular time(peak time)?
Is the performance deteriorate over the course of the day?
Is the slowdown gradual or sudden?

4. Quantify the slowdown

This helps to identify the extent of the problem and also acts as a measure for comparison when deciding whether changes made to fix the problem have actually made the difference or not.

5. Identify the changes: Identify what has changed since performance was acceptable. this may narrow the potential problem very quickly. For example any change in hardware configuration, software versions, or oracle release has been upgraded, large amount of data has been loaded, data input or user volume grown etc

Oracle database tuning without any tools - using performance views - part 1

2008-09-19T07:48:00.000-07:00

Oracle instance tuning steps:

1. Define a problem : This step involves feedback from users about the scope of the problem.

2. Examine the Host System and Examine the Oracle Statistics

a) Obtain a full set of os, database, and application statistics, examine the data for any evidence of performance problem.
b) Consider the list of common performance errors and see if data gathered contribute to that problem
c) Build a conceptual model (mental model) what is happening in the system using the performance data gathered.

3. Implement and measure the change: In this step implement proposed changes and what will be expected result after these changes. do the change and see if you are getting expected results. Measure the application performance gain.

4.determine if the performance objective defined in step 1 are met not. if not then repead step 2 and 3 until the performance goals are met.

Quick DBA Test - Oracle 10g

2008-09-18T15:03:00.000-07:00

As a database administrator, you are required to implement a backup plan that has been documented as follows:

a) Full backups every Saturday night
b) Cumulative backups every Wednesday to back up everything since the last full backup
C) Daily incremental backups for the rest of the days of the week

The database has to be available 24 hours a day, 7 days a week, and can therefore not be shutdown at any time.

Referring to the scenario above, how do you implement this backup plan using RMAN?

a.) 1. Saturdays - Level 0 Backups2. Wednesdays - Level 1 Backups3. Sundays, Mondays, Tuesdays, Thursdays, Fridays - Level 2 Backups
b.) 1. Saturdays - Level 1 Backups2. Wednesdays - Level 2 Backups3. Sundays, Mondays, Tuesdays, Thursdays, Fridays - Level 3 Backups
c.) 1. Saturdays - Level 2 Backups2. Wednesdays - Level 1 Backups3. Sundays, Mondays, Tuesdays, Thursdays, Fridays - Level 0 Backups
d.) 1. Saturdays - Cold Backups2. Wednesdays - Level 1 Backups3. Sundays, Mondays, Tuesdays, Thursdays, Fridays - Level 2 Backups
e.) 1. Saturdays - Level 0 Backups2. Wednesdays - Level 1 Backups3. Sundays, Mondays, Tuesdays, Thursdays, Fridays - Level 1 Backups

Rob has been asked to look into the execution plans from an application which is working very slowly.

Based on the scenario above, at which one of the following does Rob first look when performing SQL statement tuning?

a.) Outer joins
b.) Inner joins
c.) Index range scans
d.) Full table scans
e.) Hash joins

Which one of the following do you execute to ensure that the control file is automatically backed up using Recovery Manager?

a.) CONFIGURE CONTROLFILE AUTOBACKUP ON;
b.) CONFIGURE CONTROLFILE BACKUP AUTO;
c.) CONFIGURE CONTROL FILE AUTOBACKUP;
d.) CONFIGURE CONTROL BACKUP AUTO;
e.) CONFIGURE CONTROL AUTO BACKUP;

Which one of the following background processes coordinates the rebalancing of disks in Automatic Storage Management?

a.) J000
b.) ORB1
c.) RECO
d.) RBAL
e.) CJQ0

You want to have the same statistics in your TEST database as the ones in your PRODUCTION database.

Referring to the above, how do you accomplish the requirements stated above?

a.) Run the GATHER_SCHEMA_STATS on TEST and PRODUCTION at the same time.
b.) Run the GATHER_DATABASE_STATS on TEST and PRODUCTION at the same time.
c.) EXPORT statistics from PRODUCTION and IMPORT to TEST using the DBMS_STATS package procedures.
d.) RESTORE statistics from PRODUCTION to TEST using the DBMS_STATS package procedures.
e.) RESTORE the PRODUCTION tables and data on TEST.

1. In Oracle 10g, how is the scheduler implemented?

a.) As a separate daemon which runs under root privileges
b.) As a separate thread under the SMON daemon which wakes up at a pre-defined interval to check for job schedules
c.) As a collection of functions and procedures in the DBMS_SCHEDULER package
d.) As a collection of functions, procedures, and packages in the DBMS_JOB_SCHEDULER package
e.) As a command-line tool named "schedjob" which interfaces with the OS scheduler

2. Which one of the following is a valid DDL statement to create a Bigfile tablespace?

a.) CREATE BIGFILE TABLESPACE tbs_bf_fct_001DATAFILE 'tbs_bf_fct_001.dbf'SIZE 6G EXTENT MANAGEMENT DICTIONARY;
b.) CREATE BIGFILE TABLESPACE tbs_bf_fct_001DATAFILE 'tbs_bf_fct_001.dbf'SIZE 6G;
c.) CREATE BIGFILE TABLESPACE tbs_bf_fct_001DATAFILE 'tbs_bf_fct_001.dbf'SIZE 6G SEGMENT SPACE MANAGEMENT MANUAL;
d.) CREATE BIGFILE TABLESPACE tbs_bf_fct_001DATAFILE 'tbs_bf_fct_001.dbf'SIZE 6G SEGMENT SPACE MANAGEMENT MANUAL EXTENT MANAGEMENT DICTIONARY;
e.) CREATE BIGFILE TABLESPACE tbs_bf_fct_001DATAFILE 'tbs_bf_fct_001.dbf' SIZE 3GB, 'tbs_bf_fct_002.dbf' SIZE 3G;

3. Which one of the following do you query in order to access fine-grained audit information?

a.) DBA_FGA_AUDIT_INFO
b.) DBA_FGA_AUDIT_TRAIL
c.) DBA_FGA_AUDIT
d.) V$FGA_AUDIT_TRAIL
e.) V$FGA_AUDIT_INFO

4. Which one of the following describes the "Reset database to incarnation" command used by Recovery Manager?
a.) It performs point-in-time recovery when using Recovery Manager.
b.) It is used to undo the effect of a resetlogs operation by restoring backups of a prior incarnation of the database.
c.) It restores the database to a save point as defined by the version control number or incarnation number of the database.
d.) It restores the database to the initial state in which it was found when first backing it up via Recovery Manager.
e.) It performs a resynchronization of online redo logs to a given archive log system change number (SCN).

5.You have a multi-master replication setup. You need to add a DATE type column to the table GL_ENTRY.

Referring to the scenario above, which one of the following Oracle packages do you execute to ensure that this change is replicated to all the other sites?

a.) DBMS_REPCAT.ALTER_MASTER_OBJECT
b.) DBMS_REPCAT.EXECUTE_MASTER_DDL
c.) DBMS_REPCAT.ALTER_MASTER_DDL
d.) DBMS_REPCAT.REPLICATE_OBJECT_DDL
e.) DBMS_REPCAT.ALTER_MASTER_REPOBJECT

6. How many levels of CPU percentage can be specified when creating a resource plan directive for Database Resource Manager?
a.) 2
b.) 4
c.) 8
d.) 16
e.) 32

7. Which one of the following procedures do you use to analyze a single SQL statement?

a.) DBMS_ADVISOR.ADD_SQLWKLD_REF
b.) DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT *
c.) DBMS_ADVISOR.QUICK_TUNE
d.) DBMS_ADVISOR.CREATE_TASK
e.) DBMS_ADVISOR.CREATE_SQLWKLD

8. Which one of the following components of Data Guard pulls archived redo logs from the primary site?

a.) MRP*
b.) FAL client
c.) FAL server
d.) ARCn
e.) LGWR

9.Which one of the following is a valid Data Availability Mode within Data Guard?

a.) Guaranteed protection mode
b.) Instant protection mode
c.) Rapid protection mode
d.) Maximum protection mode
e.) Delayed protection mode

10. Which one of the following do you access to find out what benefit you can expect to the system after a recommendation is carried out?

a.) DBA_ADVISOR
b.) DBA_ADVISOR_TASKS *
c.) DBA_ADVISOR_FINDINGS
d.) DBA_ADVISOR_RECOMMENDATIONS
e.) DBA_ADVISOR_LOG

11. Which one of the following is the default type of materialized view?

a.) Primary Key Materialized View*
b.) Object Materialized View
c.) ROWID Materialized View
d.) Complex Materialized View
e.) Hash Materialized View

12. One of your database developers is concerned about optimizer execution plan changes when other changes are made to the database.

Referring to the scenario above, how do you prevent this from occurring?

a.) Make no structural changes to the database.
b.) Use hints.
c.) Use optimizer plan stability.
d.) Set the optimizer mode to RULE.
e.) Set the optimizer mode to ALL_ROWS

what are the major new featurs in Oracle 10g data pump?

2008-09-17T08:25:00.000-07:00

Major new features in oracle 10g data pump

*The ability to specify the maximum number of threads of active execution operating on behalf of the Data Pump job. This enables you to adjust resource consumption versus elapsed time. See PARALLEL for information about using this parameter in export. See PARALLEL for information about using this parameter in import. (This feature is available only in the Enterprise Edition of Oracle Database 10g.)
*The ability to restart Data Pump jobs. See START_JOB for information about restarting export jobs. See START_JOB for information about restarting import jobs.
*The ability to detach from and reattach to long-running jobs without affecting the job itself. This allows DBAs and other operations personnel to monitor jobs from multiple locations. The Data Pump Export and Import utilities can be attached to only one job at a time; however, you can have multiple clients or jobs running at one time. (If you are using the Data Pump API, the restriction on attaching to only one job at a time does not apply.) You can also have multiple clients attached to the same job. See ATTACH for information about using this parameter in export. See ATTACH for information about using this parameter in import.
*Support for export and import operations over the network, in which the source of each operation is a remote instance. See NETWORK_LINK for information about using this parameter in export. See NETWORK_LINK for information about using this parameter in import.
*The ability, in an import job, to change the name of the source datafile to a different name in all DDL statements where the source datafile is referenced. See REMAP_DATAFILE.
*Enhanced support for remapping tablespaces during an import operation. See REMAP_TABLESPACE.
*Support for filtering the metadata that is exported and imported, based upon objects and object types. For information about filtering metadata during an export operation, see INCLUDE and EXCLUDE. For information about filtering metadata during an import operation, see INCLUDE and EXCLUDE.
*Support for an interactive-command mode that allows monitoring of and interaction with ongoing jobs. See Commands Available in Export's Interactive-Command Mode and Commands Available in Import's Interactive-Command Mode.
*The ability to estimate how much space an export job would consume, without actually performing the export. See ESTIMATE_ONLY.
*The ability to specify the version of database objects to be moved. In export jobs, VERSION applies to the version of the database objects to be exported. See VERSION for more information about using this parameter in export.

In import jobs, VERSION applies only to operations over the network. This means that VERSION applies to the version of database objects to be extracted from the source database. See VERSION for more information about using this parameter in import.
* Most Data Pump export and import operations occur on the Oracle database server. (This contrasts with original export and import, which were primarily client-based.) See Default Locations for Dump, Log, and SQL Files for information about some of the implications of server-based operations.

Flashback version query in oracle 10g

2008-09-07T09:57:00.000-07:00

flashback version query in oracle 10g

Flashback query was introduced in Oracle 9i. It provides a mechanism for viewing data as it existed at a particular point in time (a timestamp or SCN). With 10g Release 1, Oracle has extended flashback query to enable us to view different versions of our data in a given range between two timestamps or SCNs.

This article introduces the new flashback version query. It assumes that readers are familiar with flashback query concepts. For an overview, including the necessary privileges required, read this oracle-developer.net article.

sample data

For the examples in this article, we will use a scratch table and some dummy data. The table is created as follows.

SQL> CREATE TABLE fbt
2 ( x INTEGER
3 , y TIMESTAMP
4 , z VARCHAR2(30) );

Table created.
We can now populate the table and make several changes to the data. We will add a row, update it a couple of times and finally delete it. We will include a short pause between each DML operation, but capture the timestamp before and after the updates for use in later examples. All DML operations will be committed, because new versions of data are only recorded following a commit. We'll begin with a single-row insert.

SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> INSERT INTO fbt VALUES (1, LOCALTIMESTAMP, 'Initial population');

1 row created.

SQL> COMMIT;

Commit complete.
Note that we included a sleep before we added any data to the table. This is recommended by Oracle to avoid ORA-01466: unable to read data - table definition has changed (this will only be an issue if the table is subject to flashback queries as soon as it is created).

We can now update our sample data. Before we do, however, we'll capture the timestamp to use later in this article. We will also sleep for ten seconds. Again, our DML must be committed.

SQL> ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS.FF3';

Session altered.

SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> SELECT LOCALTIMESTAMP AS lower_bound FROM dual;

LOWER_BOUND
------------------------
10-AUG-2005 18:01:07.109

1 row selected.

SQL> UPDATE fbt SET y = LOCALTIMESTAMP, z = 'First update';

1 row updated.

SQL> COMMIT;

Commit complete.
Next, we update the data a second time and capture the timestamp after the commit.

SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> UPDATE fbt SET y = LOCALTIMESTAMP, z = 'Second update';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT LOCALTIMESTAMP AS upper_bound FROM dual;

UPPER_BOUND
------------------------
10-AUG-2005 18:01:17.125

1 row selected.
Finally, we will delete the data and commit the change.

SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> DELETE FROM fbt WHERE x = 1;

1 row deleted.

SQL> COMMIT;

Commit complete.
Following the sample data population, we should have no data in the FBT table, which we can verify as follows.

SQL> SELECT * FROM fbt;

no rows selected
flashback version query

Despite the fact that there is no data in FBT, we can now run some flashback version queries against it. This will enable us to view the data as it evolved between commits. Flashback version query is invoked using the new VERSIONS BETWEEN extension to the FROM clause. It takes two forms as follows:

VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound]; or
VERSIONS BETWEEN SCN [lower bound] AND [lower bound].
The lower and upper boundaries can either be specific timestamps/SCNs or the keywords MINVALUE and MAXVALUE. These keywords instruct Oracle to retrieve all available data versions. The age of the data available is determined by the undo_retention parameter. For our first flashback version query, we will attempt to retrieve all available data.

SQL> ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS.FF3';

Session altered.

SQL> SELECT x, y, z
2 FROM fbt VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
3 ORDER BY
4 y;

X Y Z
---------- ------------------------- -----------------------
1 10-AUG-2005 18:00:57.078 Initial population
1 10-AUG-2005 18:01:07.109 First update
1 10-AUG-2005 18:01:17.125 Second update
1 10-AUG-2005 18:01:17.125 Second update

4 rows selected.
We have seemingly generated four rows of data from one source record. What we are seeing, however, is the evolution of this single record in terms of all the values it has held over time (depending on its presence in the undo segments). However, we can see that the second update entry appears twice, yet we have no way of identifying why from the above output. Flashback version query therefore includes several pseudo-columns to describe each version of our data, which we can now use to determine the actual operations and change times.

versions pseudo-columns

As stated above, Oracle provides a variety of metadata with each version of our data. The metadata is exposed via a number of pseudo-columns that we can use with our flashback version queries. These pseudo-columns are as follows:

VERSIONS_STARTTIME (start timestamp of version);
VERSIONS_STARTSCN (start SCN of version);
VERSIONS_ENDTIME (end timestamp of version);
VERSIONS_ENDSCN (end SCN of version);
VERSIONS_XID (transaction ID of version); and
VERSIONS_OPERATION (DML operation of version).
We can now include some of these pseudo-columns in our flashback version query as follows. Note the SCN metadata is excluded as we are using timestamps for the examples.

SQL> SELECT z
2 , VERSIONS_STARTTIME
3 , VERSIONS_ENDTIME
4 , VERSIONS_XID
5 , VERSIONS_OPERATION
6 FROM fbt VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
7 ORDER BY
8 VERSIONS_ENDTIME;
Z VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION
-------------------- ------------------------- ------------------------- ---------------- ------------------
Initial population 10-AUG-2005 18:00:53.000 10-AUG-2005 18:01:05.000 040026008A010000 I
First update 10-AUG-2005 18:01:05.000 10-AUG-2005 18:01:14.000 040029008A010000 U
Second update 10-AUG-2005 18:01:14.000 10-AUG-2005 18:01:26.000 040027008A010000 U
Second update 10-AUG-2005 18:01:26.000 040028008A010000 D

4 rows selected.
This explains why we were seeing the second update row twice. If we look at the VERSIONS_OPERATION column, we can see that the second appearance of the final update record is actually the delete operation against it (specified by 'D'). Without the versions metadata, the Y timestamp column was actually confusing us into thinking we had two versions of the same record at the same time.

The metadata also gives us an indication that the delete operation was the final version of this data. The end timestamp of the version is NULL which tells us that there is no superceding record.

Interestingly, if we had not included a sleep between creating the FBT table and adding the single record, it would be likely (based on observations) that all VERSIONS_* pseudo-columns (except the ENDTIME and ENDSCN) would be NULL for the insert record.

flashback transaction query

Oracle has provided a new view, FLASHBACK_TRANSACTION_QUERY, to provide more information about the data versions. This includes the SQL required to reverse each change. Queries against this view are documented as "flashback transaction queries" and require the SELECT ANY TRANSACTION system privilege. The view definition is as follows.

SQL> desc flashback_transaction_query
Name Null? Type
----------------------------- -------- --------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
The VERSIONS_XID pseudo-column gives us the key to this view (the XID column) for specific versions of data. Rather than filter on XID, we will look at all records currently available for our FBT table, concentrating on the more interesting UNDO_SQL column.

SQL> SELECT xid
2 , operation
3 , undo_sql
4 FROM flashback_transaction_query
5 WHERE table_owner = USER
6 AND table_name = 'FBT'
7 ORDER BY
8 start_timestamp;

XID OPERATION UNDO_SQL
---------------- --------- ------------------------------------------------------------
040026008A010000 INSERT delete from "SCOTT"."FBT" where ROWID = 'AAANCeAAEAAAAuXAAA'
;

040029008A010000 UPDATE update "SCOTT"."FBT" set "Y" = TO_TIMESTAMP('10-AUG-2005 18:
00:57.000'), "Z" = 'Initial population' where ROWID = 'AAANC
eAAEAAAAuXAAA';

040027008A010000 UPDATE update "SCOTT"."FBT" set "Y" = TO_TIMESTAMP('10-AUG-2005 18:
01:07.000'), "Z" = 'First update' where ROWID = 'AAANCeAAEAA
AAuXAAA';

040028008A010000 DELETE insert into "SCOTT"."FBT"("X","Y","Z") values ('1',TO_TIMEST
AMP('10-AUG-2005 18:01:17.000'),'Second update');

4 rows selected.
The UNDO_SQL column shows us the reversal of every change we made to our sample record. Remember that we followed a sequence of INSERT-UPDATE-UPDATE-DELETE. The values in the OPERATION column show us this, reading from the top-down. The reversal of this sequence can be viewed by reading the UNDO_SQL column from the bottom-up. For recovery purposes this can be quite useful. Note that the user responsible for the change is also available.

versions between explicit ranges

So far, we have issued flashback version queries using the MINVALUE and MAXVALUE range boundaries. As noted earlier in the article, we can also supply specific timestamp or SCN ranges. Remember that we captured the timestamps before and after the updates of our data? We will use these to limit the data versions requested below. One restriction with using specific timestamps or SCNs is that they must be within the boundaries of the undo_retention parameter. Attempting to flashback to a version older than approximately query time-undo_retention will result in ORA-30052: invalid lower limit snapshot expression.

Note that in the following examples, the ALTER SESSION statement is included for convenience, to save having to supply a long format mask in the TO_TIMESTAMP calls.

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';

Session altered.

SQL> SELECT x
2 , y
3 , z
4 , VERSIONS_OPERATION
5 FROM fbt VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('10-AUG-2005 18:01:07.109')
6 AND TO_TIMESTAMP('10-AUG-2005 18:01:17.125')
7 ORDER BY
8 VERSIONS_ENDTIME;

X Y Z VERSIONS_OPERATION
--- ------------------------- -------------------- ------------------
1 10-AUG-2005 18:00:57.078 Initial population
1 10-AUG-2005 18:01:07.109 First update U
1 10-AUG-2005 18:01:17.125 Second update U

3 rows selected.
Remember that we took the timestamp for the lower boundary before our first update. We can see this from the above version query. At this time, there was already a version of our data in existence (the initial insert). The VERSIONS_OPERATION pseudo-column is NULL for this record because the change had already occurred prior to the lower timestamp boundary. Our two updates occurred within the timestamp range, however, and the VERSIONS_OPERATION column shows us this. This is a useful tracking mechanism that enables us to distinguish between changes and existing data.

a note on timestamps and scns

As a final note, it is very simple to switch between SCNs and timestamps, should the need arise. Oracle 10g provides two conversion functions, TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP for this purpose. Timestamps are mapped to SCNs with a precision of approximately 3 seconds (in 9i this was 5 minutes). Most readers will be aware of the SYSTIMESTAMP and LOCALTIMESTAMP functions to capture timestamps, but we can also capture SCNs using the DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER function. We will complete this article with an example of each.

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';

Session altered.

SQL> SELECT SYSTIMESTAMP
2 , LOCALTIMESTAMP
3 , DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER AS scn
4 FROM dual;

SYSTIMESTAMP LOCALTIMESTAMP SCN
----------------------------------- ------------------------- ----------
10-AUG-05 18.08.04.078000 +00:00 10-AUG-2005 18:08:04.078 1408640

1 row selected.

SQL> SELECT SCN_TO_TIMESTAMP(1408640) AS ts
2 FROM dual;

TS
-------------------------
10-AUG-2005 18:08:03.000

1 row selected.

SQL> SELECT TIMESTAMP_TO_SCN('10-AUG-2005 18:08:03.000') AS scn
2 FROM dual;

SCN
----------
1408640

1 row selected.

Optimizing Performance of Direct Path Loads

2008-09-07T08:29:00.001-07:00

Optimizing Performance of Direct Path Loads

You can control the time and temporary storage used during direct path loads.

To minimize time:

Preallocate storage space
Presort the data
Perform infrequent data saves
Minimize use of the redo log
Specify the number of column array rows and the size of the stream buffer
Specify a date cache value
To minimize space:

When sorting data before the load, sort data on the index that requires the most temporary storage space
Avoid index maintenance during the load
Preallocating Storage for Faster Loading

SQL*Loader automatically adds extents to the table if necessary, but this process takes time. For faster loads into a new table, allocate the required extents when the table is created.

To calculate the space required by a table, see the information about managing database files in the Oracle9i Database Administrator's Guide. Then use the INITIAL or MINEXTENTS clause in the SQL CREATE TABLE statement to allocate the required space.

Another approach is to size extents large enough so that extent allocation is infrequent.

Presorting Data for Faster Indexing

You can improve the performance of direct path loads by presorting your data on indexed columns. Presorting minimizes temporary storage requirements during the load. Presorting also allows you to take advantage of high-performance sorting routines that are optimized for your operating system or application.

If the data is presorted and the existing index is not empty, then presorting minimizes the amount of temporary segment space needed for the new keys. The sort routine appends each new key to the key list.

Instead of requiring extra space for sorting, only space for the keys is needed. To calculate the amount of storage needed, use a sort factor of 1.0 instead of 1.3. For more information on estimating storage requirements, see Temporary Segment Storage Requirements.

If presorting is specified and the existing index is empty, then maximum efficiency is achieved. The new keys are simply inserted into the index. Instead of having a temporary segment and new index existing simultaneously with the empty, old index, only the new index exists. So, temporary storage is not required, and time is saved.

SORTED INDEXES Clause

The SORTED INDEXES clause identifies the indexes on which the data is presorted. This clause is allowed only for direct path loads. See Case Study 6: Loading Data Using the Direct Path Load Method for an example.

Generally, you specify only one index in the SORTED INDEXES clause, because data that is sorted for one index is not usually in the right order for another index. When the data is in the same order for multiple indexes, however, all indexes can be specified at once.

All indexes listed in the SORTED INDEXES clause must be created before you start the direct path load.

Unsorted Data

If you specify an index in the SORTED INDEXES clause, and the data is not sorted for that index, then the index is left in an Index Unusable state at the end of the load. The data is present, but any attempt to use the index results in an error. Any index that is left in an Index Unusable state must be rebuilt after the load.

Multiple-Column Indexes

If you specify a multiple-column index in the SORTED INDEXES clause, the data should be sorted so that it is ordered first on the first column in the index, next on the second column in the index, and so on.

For example, if the first column of the index is city, and the second column is last name; then the data should be ordered by name within each city, as in the following list:

Albuquerque Adams
Albuquerque Hartstein
Albuquerque Klein
... ...
Boston Andrews
Boston Bobrowski
Boston Heigham
... ...
Choosing the Best Sort Order

For the best overall performance of direct path loads, you should presort the data based on the index that requires the most temporary segment space. For example, if the primary key is one numeric column, and the secondary key consists of three text columns, then you can minimize both sort time and storage requirements by presorting on the secondary key.

To determine the index that requires the most storage space, use the following procedure:

For each index, add up the widths of all columns in that index.
For a single-table load, pick the index with the largest overall width.
For each table in a multiple-table load, identify the index with the largest overall width for each table. If the same number of rows are to be loaded into each table, then again pick the index with the largest overall width. Usually, the same number of rows are loaded into each table.
If a different number of rows are to be loaded into the indexed tables in a multiple-table load, then multiply the width of each index identified in step 3 by the number of rows that are to be loaded into that index, and pick the index with the largest result.
Infrequent Data Saves

Frequent data saves resulting from a small ROWS value adversely affect the performance of a direct path load. Because direct path loads can be many times faster than conventional loads, the value of ROWS should be considerably higher for a direct load than it would be for a conventional load.

During a data save, loading stops until all of SQL*Loader's buffers are successfully written. You should select the largest value for ROWS that is consistent with safety. It is a good idea to determine the average time to load a row by loading a few thousand rows. Then you can use that value to select a good value for ROWS.

For example, if you can load 20,000 rows per minute, and you do not want to repeat more than 10 minutes of work after an interruption, then set ROWS to be 200,000 (20,000 rows/minute * 10 minutes).

Minimizing Use of the Redo Log

One way to speed a direct load dramatically is to minimize use of the redo log. There are three ways to do this. You can disable archiving, you can specify that the load is UNRECOVERABLE, or you can set the NOLOG attribute of the objects being loaded. This section discusses all methods.

Disabling Archiving

If archiving is disabled, direct path loads do not generate full image redo. Use the ARCHIVELOG and NOARCHIVELOG parameters to set the archiving mode. See the Oracle9i Database Administrator's Guide for more information about archiving.

Specifying the UNRECOVERABLE Parameter

To save time and space in the redo log file, use the UNRECOVERABLE parameter when you load data. An UNRECOVERABLE load does not record loaded data in the redo log file; instead, it generates invalidation redo.

The UNRECOVERABLE parameter applies to all objects loaded during the load session (both data and index segments). Therefore, media recovery is disabled for the loaded table, although database changes by other users may continue to be logged.

Note:
Because the data load is not logged, you may want to make a backup of the data after loading.

If media recovery becomes necessary on data that was loaded with the UNRECOVERABLE parameter, the data blocks that were loaded are marked as logically corrupted.

To recover the data, drop and re-create the data. It is a good idea to do backups immediately after the load to preserve the otherwise unrecoverable data.

By default, a direct path load is RECOVERABLE.

Setting the NOLOG Attribute

If a data or index segment has the NOLOG attribute set, then full image redo logging is disabled for that segment (invalidation redo is generated.) Use of the NOLOG attribute allows a finer degree of control over the objects that are not logged.

Specifying the Number of Column Array Rows and Size of Stream Buffers

The number of column array rows determines the number of rows loaded before the stream buffer is built. The STREAMSIZE parameter specifies the size (in bytes) of the data stream sent from the client to the server.

Use the COLUMNARRAYROWS parameter to specify a value for the number of column array rows.

Use the STREAMSIZE parameter to specify the size for direct path stream buffers.

The optimal values for these parameters vary, depending on the system, input datatypes, and Oracle column datatypes used. When you are using optimal values for your particular configuration, the elapsed time in the SQL*Loader log file should go down.

To see a list of default values for these and other parameters, invoke SQL*Loader without any parameters, as described in Invoking SQL*Loader.

Note:
You should monitor process paging activity, because if paging becomes excessive, performance can be significantly degraded. You may need to lower the values for READSIZE, STREAMSIZE, and COLUMNARRAYROWS to avoid excessive paging.

It can be particularly useful to specify the number of column array rows and size of the steam buffer when you perform direct path loads on multiple-CPU systems. See Optimizing Direct Path Loads on Multiple-CPU Systems for more information.

Specifying a Value for the Date Cache

If you are performing a direct path load in which the same date or timestamp values are loaded many times, a large percentage of total load time can end up being used for converting date and timestamp data. This is especially true if multiple date columns are being loaded. In such a case, it may be possible to improve performance by using the SQL*Loader date cache.

The date cache reduces the number of date conversions done when many duplicate values are present in the input data. It allows you to specify the number of unique dates anticipated during the load.

The date cache is enabled by default. To completely disable the date cache, set it to 0.

The default date cache size is 1000 elements. If the default is used and the number of unique input values loaded exceeds 1000, then the date cache is automatically disabled for that table. This prevents excessive and unnecessary lookup times that could affect performance. However, if instead of using the default, you specify a nonzero value for the date cache and it is exceeded, the date cache is not disabled. Instead, any input data that exceeded the maximum is explicitly converted using the appropriate conversion routines.

The date cache can be associated with only one table. No date cache sharing can take place across tables. A date cache is created for a table only if all of the following conditions are true:

The DATE_CACHE parameter is not set to 0
One or more date values, timestamp values, or both are being loaded that require datatype conversion in order to be stored in the table
The load is a direct path load
Date cache statistics are written to the log file. You can use those statistics to improve direct path load performance as follows:

If the number of cache entries is less than the cache size and there are no cache misses, then the cache size could safely be set to a smaller value.
If the number of cache hits (entries for which there are duplicate values) is small and the number of cache misses is large, then the cache size should be increased. Be aware that if the cache size is increased too much, it may cause other problems such as excessive paging or too much memory usage.
If most of the input date values are unique, the date cache will not enhance performance and therefore should not be used.

Note:
Date cache statistics are not written to the SQL*Loader log file if the cache was active by default and disabled because the maximum was exceeded.

If increasing the cache size does not improve performance, revert to the default behavior or set the cache size to 0. The overall performance improvement also depends on the datatypes of the other columns being loaded. Improvement will be greater for cases in which the total number of date columns loaded is large compared to other types of data loaded.

See Also:
DATE_CACHE
Table Load Information for an example of how date cache statistics are presented in the SQL*Loader log file

Data Conversion During Direct Path Loads

2008-09-07T08:22:00.000-07:00

Data Conversion During Direct Path Loads

During a direct path load, data conversion occurs on the client side rather than on the server side. This means that NLS parameters in the initialization parameter file (server-side language handle) will not be used. To override this behavior, you can specify a format mask in the SQL*Loader control file which is equivalent to the setting of the NLS parameter in the initialization parameter file, or set the appropriate environment variable. For example, to specify a date format for a field, you can either set the date format in the SQL*Loader control file as shown in Example 9-1 or set an NLS_DATE_FORMAT environment variable as shown in Example 9-2.

Example 9-1 Setting the Date Format in the SQL*Loader Control File

LOAD DATA
INFILE 'data.dat'
INSERT INTO TABLE emp
FIELDS TERMINATED BY "|"
(
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE 'YYYYMMDD',
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
Example 9-2 Setting an NLS_DATE_FORMAT Environment Variable

On UNIX bourne or korn shell:

% NLS_DATE_FORMAT='YYYYMMDD'
% export NLS_DATE_FORMAT

On UNIX csh:

%setenv NLS_DATE_FORMAT='YYYYMMDD'
Direct Path Load of a Partitioned or Subpartitioned Table

When loading a partitioned or subpartitioned table, SQL*Loader partitions the rows and maintains indexes (which can also be partitioned). Note that a direct path load of a partitioned or subpartitioned table can be quite resource-intensive for tables with many partitions or subpartitions.

Note:
If you are performing a direct path load into multiple partitions and a space error occurs, the load is rolled back to the last commit point. If there was no commit point, then the entire load is rolled back. This ensures that no data encountered after the space error is written out to a different partition.

You can use the ROWS parameter to specify the frequency of the commit points. If the ROWS parameter is not specified, the entire load is rolled back.

Direct Path Load of a Single Partition or Subpartition

When loading a single partition of a partitioned or subpartitioned table, SQL*Loader partitions the rows and rejects any rows that do not map to the partition or subpartition specified in the SQL*Loader control file. Local index partitions that correspond to the data partition or subpartition being loaded are maintained by SQL*Loader. Global indexes are not maintained on single partition or subpartition direct path loads. During a direct path load of a single partition, SQL*Loader uses the partition-extended syntax of the LOAD statement, which has either of the following forms:

LOAD INTO TABLE T PARTITION (P) VALUES ...

LOAD INTO TABLE T SUBPARTITION (P) VALUES ...

While you are loading a partition of a partitioned or subpartitioned table, you are also allowed to perform DML operations on, and direct path loads of, other partitions in the table.

Although a direct path load minimizes database processing, several calls to the Oracle database server are required at the beginning and end of the load to initialize and finish the load, respectively. Also, certain DML locks are required during load initialization and are released when the load completes. The following operations occur during the load: index keys are built and put into a sort, and space management routines are used to get new extents when needed and to adjust the upper boundary (high-water mark) for a data savepoint. See Using Data Saves to Protect Against Data Loss for information on adjusting the upper boundary.

Advantages of a Direct Path Load

A direct path load is faster than the conventional path for the following reasons:

Partial blocks are not used, so no reads are needed to find them, and fewer writes are performed.
SQL*Loader need not execute any SQL INSERT statements; therefore, the processing load on the Oracle database is reduced.
A direct path load calls on Oracle to lock tables and indexes at the start of the load and releases them when the load is finished. A conventional path load calls Oracle once for each array of rows to process a SQL INSERT statement.
A direct path load uses multiblock asynchronous I/O for writes to the database files.
During a direct path load, processes perform their own write I/O, instead of using Oracle's buffer cache. This minimizes contention with other Oracle users.
The sorted indexes option available during direct path loads allows you to presort data using high-performance sort routines that are native to your system or installation.
When a table to be loaded is empty, the presorting option eliminates the sort and merge phases of index-building. The index is filled in as data arrives.
Protection against instance failure does not require redo log file entries during direct path loads. Therefore, no time is required to log the load when:
Oracle is operating in NOARCHIVELOG mode
The UNRECOVERABLE parameter is set to Y
The object being loaded has the NOLOG attribute set
See Instance Recovery and Direct Path Loads.

Restrictions on Using Direct Path Loads

The following conditions must be satisfied for you to use the direct path load method:

Tables are not clustered.
Tables to be loaded do not have any active transactions pending.

To check for this condition, use the Oracle Enterprise Manager command MONITOR TABLE to find the object ID for the tables you want to load. Then use the command MONITOR LOCK to see if there are any locks on the tables.

For versions of the Oracle database server prior to 9i, you can only perform a SQL*Loader direct path load when the client and server are the same version. This also means that you cannot perform a direct path load of Oracle9i data into a database of an earlier version. For example, you cannot use direct path load to load data from a release 9.0.1 database into a release 8.1.7 database.

However, beginning with Oracle9i, you can perform a SQL*Loader direct path load between different versions as long as both the client and server are version 9i or later. For example, you can perform a direct path load from a release 9.0.1 database into a release 9.2 database.

The following features are not available with direct path load.

Loading VARRAYs
Loading a parent table together with a child table
Loading BFILE columns
Restrictions on a Direct Path Load of a Single Partition

In addition to the previously listed restrictions, loading a single partition has the following restrictions:

The table that the partition is a member of cannot have any global indexes defined on it.
Enabled referential and check constraints on the table that the partition is a member of are not allowed.
Enabled triggers are not allowed.
When to Use a Direct Path Load

If none of the previous restrictions apply, you should use a direct path load when:

You have a large amount of data to load quickly. A direct path load can quickly load and index large amounts of data. It can also load data into either an empty or nonempty table.
You want to load data in parallel for maximum performance. See Parallel Data Loading Models.

Data Loading Methods - SQL Loader

2008-09-07T08:19:00.001-07:00

Data Loading Methods

SQL*Loader provides two methods for loading data:

Conventional Path Load
Direct Path Load
A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load does not compete with other users for database resources, so it can usually load data at near disk speed. Considerations inherent to direct path loads, such as restrictions, security, and backup implications, are discussed in this chapter.

The tables to be loaded must already exist in the database. SQL*Loader never creates tables. It loads existing tables that either already contain data or are empty.

The following privileges are required for a load:

You must have INSERT privileges on the table to be loaded.
You must have DELETE privileges on the table to be loaded, when using the REPLACE or TRUNCATE option to empty old data from the table before loading the new data in its place.

Conventional Path Load

Conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications.

When SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources. This can slow the load significantly. Extra overhead is added as SQL commands are generated, passed to Oracle, and executed.

The Oracle database server looks for partially filled blocks and attempts to fill them on each insert. Although appropriate during normal use, this can slow bulk loads dramatically.

Conventional Path Load of a Single Partition

By definition, a conventional path load uses SQL INSERT statements. During a conventional path load of a single partition, SQL*Loader uses the partition-extended syntax of the INSERT statement, which has the following form:

INSERT INTO TABLE T PARTITION (P) VALUES ...
The SQL layer of the Oracle kernel determines if the row being inserted maps to the specified partition. If the row does not map to the partition, the row is rejected, and the SQL*Loader log file records an appropriate error message.

When to Use a Conventional Path Load

If load speed is most important to you, you should use direct path load because it is faster than conventional path load. However, certain restrictions on direct path loads may require you to use a conventional path load. You should use a conventional path load in the following situations:

When accessing an indexed table concurrently with the load, or when applying inserts or updates to a nonindexed table concurrently with the load

To use a direct path load (with the exception of parallel loads), SQL*Loader must have exclusive write access to the table and exclusive read/write access to any indexes.

When loading data into a clustered table

A direct path load does not support loading of clustered tables.

When loading a relatively small number of rows into a large indexed table

During a direct path load, the existing index is copied when it is merged with the new index keys. If the existing index is very large and the number of new keys is very small, then the index copy time can offset the time saved by a direct path load.

When loading a relatively small number of rows into a large table with referential and column-check integrity constraints

Because these constraints cannot be applied to rows loaded on the direct path, they are disabled for the duration of the load. Then they are applied to the whole table when the load completes. The costs could outweigh the savings for a very large table and a small number of new rows.

When loading records and you want to ensure that a record is rejected under any of the following circumstances:
If the record, upon insertion, causes an Oracle error
If the record is formatted incorrectly, so that SQL*Loader cannot find field boundaries
If the record violates a constraint or tries to make a unique index non-unique
Direct Path Load

Instead of filling a bind array buffer and passing it to the Oracle database server with a SQL INSERT statement, a direct path load uses the direct path API to pass the data to be loaded to the load engine in the server. The load engine builds a column array structure from the data passed to it.

The direct path load engine uses the column array structure to format Oracle data blocks and build index keys. The newly formatted database blocks are written directly to the database (multiple blocks per I/O request using asynchronous writes if the host platform supports asynchronous I/O).

Internally, multiple buffers are used for the formatted blocks. While one buffer is being filled, one or more buffers are being written if asynchronous I/O is available on the host platform. Overlapping computation with I/O increases load performance.

How Does Data Pump Access Data?

2008-09-07T08:15:00.001-07:00

How Does Data Pump Access Data?

Data Pump supports two access methods to load and unload table row data: direct path and external tables. Because both methods support the same external data representation, data that is unloaded with one method can be loaded using the other method. Data Pump automatically chooses the fastest method appropriate for each table.

Data Pump also uses functionality provided in the DBMS_METADATA PL/SQL package to handle all operations involving metadata, including complete extraction, transformation, and re-creation of all database object definitions.

Direct Path Loads and Unloads

The Oracle database has provided direct path unload capability for export operations since Oracle release 7.3 and a direct path API for OCI since Oracle8i. Data Pump technology enhances direct path technology in the following ways:

Support of a direct path, proprietary format unload.

Improved performance through elimination of unnecessary conversions. This is possible because the direct path internal stream format is used as the format stored in the Data Pump dump files.

Support of additional datatypes and transformations.

Data Pump uses direct path load and unload when the structure of a table allows it.

In the following circumstances, Data Pump cannot use direct path loading:

A global index on multipartition tables exists during a single-partition load. This includes object tables that are partitioned.

A domain index exists for a LOB column.

A table is in a cluster.

A table has an active trigger.

A table has fine-grained access control enabled in insert mode.

A table contains BFILE columns or columns of opaque types.

A referential integrity constraint is present.

A table contains VARRAY columns with an embedded opaque type.

If any of these conditions exist for a table, Data Pump uses external tables rather than direct path to move the data for that table.

Oracle data pump 10g impdp help

2008-09-07T08:11:00.001-07:00

impdp help=y

Import: Release 10.1.0.2.0 - Production on Saturday, 11 September, 2004 17:22

Copyright (c) 2003, Oracle. All rights reserved.

The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply (Y/N) to specific objects.
Valid transform keywords: SEGMENT_ATTRIBUTES and STORAGE.
ex. TRANSFORM=SEGMENT_ATTRIBUTES:N:TABLE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description (Default)
------------------------------------------------------------------------------
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
START_JOB=SKIP_CURRENT will start the job after skipping
any action which was in progress when job was stopped.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS=[interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.

Oracle data pump 10g expdp help

2008-09-07T08:10:00.000-07:00

Help

The HELP=Y option displays the available parameters:
expdp help=y

Export: Release 10.1.0.2.0 - Production on Tuesday, 23 March, 2004 8:33

Copyright (c) 2003, Oracle. All rights reserved.

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
ADD_FILE=dumpfile-name
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS=[interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.

Exporting and Importing External tables using Data Pump

2008-09-07T08:08:00.001-07:00

External Tables

Oracle have incorporated support for data pump technology into external tables. The ORACLE_DATAPUMP access driver can be used to unload data to data pump export files and subsequently reload it. The unload of data occurs when the external table is created using the "AS" clause:
CREATE TABLE emp_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY test_dir
LOCATION ('emp_xt.dmp')
)
AS SELECT * FROM emp;
The data can then be queried using:
SELECT * FROM emp_xt;
The syntax to create the external table pointing to an existing file is similar, but without the "AS" clause:
DROP TABLE emp_xt;

CREATE TABLE emp_xt (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY test_dir
LOCATION ('emp_xt.dmp')
);

SELECT * FROM emp_xt;

Oracle 10g Data Pump APIs

2008-09-07T08:06:00.001-07:00

Data Pump API

Along with the data pump utilities Oracle provide an PL/SQL API. The following is an example of how this API can be used to perform a schema export:
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_dp_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
BEGIN
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
job_name => 'EMP_EXPORT',
version => 'LATEST');

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'SCOTT.dmp',
directory => 'TEST_DIR');

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'SCOTT.log',
directory => 'TEST_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => '= ''SCOTT''');

DBMS_DATAPUMP.start_job(l_dp_handle);

DBMS_DATAPUMP.detach(l_dp_handle);
END;
/
Once the job has started the status can be checked using:
system@db10g> select * from dba_datapump_jobs;

Difference between old exp/imp and new data pump expdp/impdp

2008-09-07T08:03:00.000-07:00

Unlike the original exp and imp utilities all data pump ".dmp" and ".log" files are created on the Oracle server, not the client machine.

All data pump actions are performed by multiple jobs (server processes not DBMS_JOB jobs). These jobs are controlled by a master control process which uses Advanced Queuing. At runtime an advanced queue table, named after the job name, is created and used by the master control process. The table is dropped on completion of the data pump job. The job and the advanced queue can be named using the JOB_NAME parameter. Cancelling the client process does not stop the associated data pump job. Issuing "ctrl+c" on the client during a job stops the client output and presents a command prompt. Typing "status" at this prompt allows you to monitor the current job:
Export> status

Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: D:TEMPDB10G.DMP
bytes written: 4,096

Worker 1 Status:
State: EXECUTING
Object Schema: SYSMAN
Object Name: MGMT_CONTAINER_CRED_ARRAY
Object Type: DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Completed Objects: 261
Total Objects: 261
Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read:
expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log
The DBA_DATAPUMP_JOBS view can be used to monitor the current jobs:
system@db10g> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION
------------------------------ ------------------------------ ------------------------------
JOB_MODE STATE DEGREE ATTACHED_SESSIONS
------------------------------ ------------------------------ ---------- -----------------
SYSTEM SYS_EXPORT_FULL_01 EXPORT
FULL EXECUTING 1 1
The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export:
expdp scott/tiger@db10g schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

Oracle Data pump 10g getting started

2008-09-07T07:58:00.000-07:00

Setup account

CONN sys/password@db10g AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;
GRANT CREATE ANY DIRECTORY TO scott;

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:
expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:
expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:
expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

Oracle Data Pump in Oracle Database 10g

2008-09-07T07:57:00.001-07:00

Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

What is FLASHBACK TABLE ? Explain with examples?

2008-09-06T15:02:00.000-07:00

Purpose

Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.

Oracle strongly recommends that you run your database in Automatic Undo Management mode by setting the UNDO_MANAGEMENT initialization parameter to AUTO. In addition, set the UNDO_RETENTION initialization parameter to an interval large enough to include the oldest data you anticipate needing. For more information please refer to the documentation on the UNDO_MANAGEMENT and UNDO_RETENTION initialization parameters.

You cannot roll back a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause.

Prerequisites

To flash back a table to an earlier SCN or timestamp, you must have either the FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege. In addition, you must have the SELECT, INSERT, DELETE, and ALTER object privileges on the table. Row movement must be enabled for all tables in the Flashback list. Please refer to row_movement_clause for information on enabling row movement.

To flash back a table to before a DROP TABLE operation, you need only the privileges necessary to drop the table.

Semantics

During an Oracle Flashback Table operation, Oracle Database acquires exclusive DML locks on all the tables specified in the Flashback list. These locks prevent any operations on the tables while they are reverting to their earlier state.

The Flashback Table operation is executed in a single transaction, regardless of the number of tables specified in the Flashback list. Either all of the tables revert to the earlier state or none of them do. If the Flashback Table operation fails on any table, then the entire statement fails.

At the completion of the Flashback Table operation, the data in table is consistent with table at the earlier time. However, FLASHBACK TABLE TO SCN or TIMESTAMP does not preserve rowids, and FLASHBACK TABLE TO BEFORE DROP does not recover referential constraints.

Oracle Database does not revert statistics associated with table to their earlier form. Indexes on table that exist currently are reverted and reflect the state of the table at the Flashback point. If the index exists now but did not yet exist at the Flashback point, then the database updates the index to reflect the state of the table at the Flashback point. However, indexes that were dropped during the interval between the Flashback point and the current time are not restored.

schema

Specify the schema containing the table. If you omit schema, then the database assumes the table is in your own schema.

table

Specify the name of one or more tables containing data you want to revert to an earlier version.

Restrictions on Flashing Back Tables

*

Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.
*

The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).

TO SCN Clause

Specify the system change number (SCN) corresponding to the point in time to which you want to return the table. The expr must evaluate to a number representing a valid SCN.

TO TIMESTAMP Clause

Specify a timestamp value corresponding to the point in time to which you want to return the table. The expr must evaluate to a valid timestamp in the past.

ENABLE | DISABLE TRIGGERS

By default, Oracle Database disables all enabled triggers defined on table during the Flashback Table operation and then reenables them after the Flashback Table operation is complete. Specify ENABLE TRIGGERS if you want to override this default behavior and keep the triggers enabled during the Flashback process.

This clause affects only those database triggers defined on table that are already enabled. To enable currently disabled triggers selectively, use the ALTER TABLE ... enable_disable_clause before you issue the FLASHBACK TABLE statement with the ENABLE TRIGGERS clause.

TO BEFORE DROP Clause

Use this clause to retrieve from the recycle bin a table that has been dropped, along with all possible dependent objects.

You can specify either the original user-specified name of the table or the system-generated name Oracle Database assigned to the object when it was dropped.

*

System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, then the database retrieves that specified object.

To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary review. You can use the RECYCLEBIN synonym instead. The following two statements return the same rows:

SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;

*

If you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database retrieves the object that was moved to the recycle bin most recently. If you want to retrieve an older version of the table, do one of these things:
o

Specify the system-generated recycle bin name of the table you want to retrieve.
o

Issue additional FLASHBACK TABLE ... TO BEFORE DROP statements until you retrieve the table you want.

Oracle Database attempts to preserve the original table name. If a new table of the same name has been created in the same schema since the original table was dropped, then the database returns an error unless you also specify the RENAME TO clause.

RENAME TO Clause

Use this clause to specify a new name for the table being retrieved from the recycle bin.

Notes on Flashing Back Dropped Tables

*

Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes. (Bitmap join indexes are not put in the recycle bin during a DROP TABLE operation, so cannot be retrieved.)
*

The database also retrieves all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.

The retrieved indexes, triggers, and constraints have recycle bin names. Therefore it is advisable to query the USER_RECYCLEBIN view before issuing a FLASHBACK TABLE ... TO BEFORE DROP statement so that you can rename the retrieved triggers and constraints to more usable names.
*

When you drop a table, all materialized view logs defined on the table are also dropped but are not placed in the recycle bin. Therefore, the materialized view logs cannot be flashed back along with the table.
*

When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table.
*

You cannot flash back a table if it has been purged, either by a user or by Oracle Database as a result of some space reclamation operation.

Examples

Restoring a Table to an Earlier State: Examples

The examples below create a new table, employees_demo, with row movement enabled, update values within the new table, and issue the FLASHBACK TABLE statement.

Create table employees_demo, with row movement enabled, from table employees of the sample hr schema:

CREATE TABLE employees_demo
ENABLE ROW MOVEMENT
AS SELECT * FROM employees;

As a benchmark, list those salaries less than 2500:

SELECT salary
FROM employees_demo
WHERE salary < 2500;

SALARY
----------
2400
2200
2100
2400
2200

Note:
To allow time for the SCN to propagate to the mapping table used by the FLASHBACK TABLE statement, wait a minimum of 5 minutes prior to issuing the following statement. This wait would not be necessary if a previously existing table were being used in this example.

Issue a 10% salary increase to those employees earning less than 2500:

UPDATE employees_demo
SET salary = salary * 1.1
WHERE salary < 2500;

5 rows updated.
COMMIT;

As a second benchmark, list those salaries that remain less than 2500 following the 10% increase:

SELECT salary
FROM employees_demo
WHERE salary < 2500;

SALARY
----------
2420
2310
2420

Restore the table employees_demo to its state 1 minute prior to the current system time:

FLASHBACK TABLE employees_demo
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

List those salaries less than 2500. After the FLASHBACK TABLE statement issued above, this list should match the list in the first benchmark.

SELECT salary
FROM employees_demo
WHERE salary < 2500;

SALARY
----------
2400
2200
2100
2400
2200

Retrieving a Dropped Table: Example

If you accidentally drop the hr.employees table and wish to retrieve it, issue the following statement:

FLASHBACK TABLE employees TO BEFORE DROP;

If another employees table has been created in the hr schema, use the RENAME TO clause to rename the retrieved table:

FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_old;

If you know that the employees table has been dropped multiple times, and you wish to retrieve the oldest version, query the USER_RECYLEBIN table to determine the system-generated name, and then use that name in the FLASHBACK TABLE statement. (System-generated names in your database will differ from those shown here.)

SELECT object_name, droptime FROM user_recyclebin
WHERE original_name = 'employees';

OBJECT_NAME DROPTIME
------------------------------ -------------------
RB$$45703$TABLE$0 2003-06-03:15:26:39
RB$$45704$TABLE$0 2003-06-12:12:27:27
RB$$45705$TABLE$0 2003-07-08:09:28:01

Flashback Dropped Table
Flashback Table From The Recycle Bin Using The Object Name FLASHBACK TABLE TO BEFORE DROP
{RENAME TO };
CREATE TABLE test (
testcol VARCHAR2(20));

ALTER TABLE test
ADD CONSTRAINT pk_test
PRIMARY KEY (testcol)
USING INDEX
PCTFREE 0;

INSERT INTO test VALUES ('ABC');
INSERT INTO test VALUES ('DEF');
COMMIT;

SELECT * FROM test;

DROP TABLE test;

SELECT object_name, original_name
FROM recyclebin;

FLASHBACK TABLE "BIN$4/V75AlOSDqCJ4fbHLAtpQ==$0"
TO BEFORE DROP;

SELECT * FROM test;

SELECT constraint_name, constraint_type
FROM user_constraints
ORDER BY table_name;
Flashback Table From The Recycle Bin Using The Original Name FLASHBACK TABLE TO BEFORE DROP
{RENAME TO };
CREATE TABLE test (
testcol VARCHAR2(20));

INSERT INTO test VALUES ('ABC');

COMMIT;

SELECT * FROM test;

DROP TABLE test;

CREATE TABLE test (
testcol VARCHAR2(20));

INSERT INTO test VALUES ('DEF');

COMMIT;

SELECT * FROM test;

DROP TABLE test;

CREATE TABLE test (
testcol VARCHAR2(20));

INSERT INTO test VALUES ('GHI');

COMMIT;

SELECT * FROM test;

DROP TABLE test;

SELECT object_name, original_name, droptime
FROM recyclebin;

FLASHBACK TABLE test TO BEFORE DROP;

FLASHBACK TABLE test TO BEFORE DROP
RENAME TO test2;

FLASHBACK TABLE test TO BEFORE DROP
RENAME TO test1;

SELECT * FROM test;

SELECT * FROM test2;

SELECT * FROM test1;

Flashback To SCN

Flashback to identified SCN demo FLASHBACK TABLE TO SCN ;
CREATE TABLE test (
testcol VARCHAR2(20));

SELECT current_scn
FROM gv$database;

INSERT INTO test VALUES ('ABC');
COMMIT;

SELECT current_scn
FROM gv$database;

INSERT INTO test VALUES ('DEF');
COMMIT;

SELECT current_scn
FROM gv$database;

INSERT INTO test VALUES ('GHI');
COMMIT;

SELECT current_scn
FROM gv$database;

SELECT * FROM test;

SELECT current_scn
FROM gv$database;

ALTER TABLE test ENABLE ROW MOVEMENT;

SELECT * FROM test;

FLASHBACK TABLE test TO SCN 1833265;

Flashback To Timestamp

Flashback to identified timestamp demo FLASHBACK TABLE TO TIMESTAMP ;
CREATE TABLE test (
testcol VARCHAR2(20));

SELECT systimestamp
FROM dual;

INSERT INTO test VALUES ('ABC');
COMMIT;

SELECT systimestamp
FROM dual;

INSERT INTO test VALUES ('DEF');
COMMIT;

SELECT systimestamp
FROM dual;

INSERT INTO test VALUES ('GHI');
COMMIT;

SELECT systimestamp
FROM dual;

SELECT * FROM test;

SELECT systimestamp
FROM dual;

ALTER TABLE test ENABLE ROW MOVEMENT;

SELECT * FROM test;

FLASHBACK TABLE test TO TIMESTAMP
TO_TIMESTAMP('14-JUN-05 10.14.34.269 PM');

SELECT * FROM test;

What is Oracle Secure Backup (OSB)?

2008-09-06T14:50:00.000-07:00

What is Oracle Secure Backup (OSB)?

Oracle Secure Backup is a centralized tape backup management software providing secure data protection for heterogeneous file systems and the Oracle Database.

Why switch to Oracle Secure Backup?

Oracle Secure Backup delivers reliable, high performance tape backup for your entire IT environment including UNIX/Linux/Windows/NAS file systems and the Oracle database at the lowest-cost. The single component, per tape drive, pricing model is lower-cost and significantly reduces license management as compared to other media management products, which generally charge a premium for advanced functionality.

Oracle Secure Backup provides key benefits:
• Fastest Oracle database backup to tape
• Single technical resource for entire backup environment
• Dynamic tape drive sharing between NAS / UNIX / Linux / Windows servers
• Policy-based backup management:
o Backup encryption
o Vaulting (rotation of tapes between multiple locations)
o Tape duplication
o Migration from virtual tapes to physical tapes

Is Oracle Secure Backup a separate product from the Oracle database?

Yes. Oracle Secure Backup is a separate product with independent release schedule and versioning from that of the database.

What Oracle database versions does Oracle Secure Backup 10.2 support?

Oracle Secure Backup 10.2 is tightly integrated with Recovery Manager (RMAN) supporting Oracle9i to Oracle Database 11g.

What is Oracle Secure Backup Express (OSB-XE)?

Bundled with the Oracle database, Oracle Secure Backup Express is free and provides single-server tape backup management for one database server directly attached to one tape drive. Oracle Secure Backup documentation is applicable for OSB-XE with the exception of advanced functionality only available with the OSB edition. You can leverage advanced media management features by upgrading OSB-XE licensing to OSB edition. Please refer to the OSB 10.2 Licensing Documentation for feature restrictions in OSB-XE.

How difficult is upgrading from OSB 10.1 to OSB 10.2?

Upgrading from OSB 10.1 to 10.2 is relatively easy. The backup catalog is retained (unless explicitly deleted by user) during the upgrade maintaining all backup metadata and backwards compatibility of tapes (tapes written using OSB 10.1 are fully compatible with OSB 10.2 environments).

Where can I find compatibility matrixes for Oracle Secure Backup?
• Platform, Web browser and NAS support is listed on Certify at metaling.oracle.com
• Tape device matrix is available at otn.oracle.com/technology/products/secure-backup/tape_devices.pdf

If a tape device is not listed on the compatibility matrix, does OSB support it?
No. A tape device must be listed on the compatibility matrix to be supported. To request support for a tape device, log a Service Request (SR) through Oracle support requesting an enhancement request be filed on your behalf.

Which network protocols does Oracle Secure Backup support?
Oracle Secure Backup leverages NDMP for data transport over TCP/IP supporting backup, restore and other file access through NFSA and CIFS; host name resolution through NIS and DNS.

How does backup encryption differ between Oracle Secure Backup 10.1 and 10.2?
Oracle Secure Backup 10.2 provides backup encryption for file systems and Oracle9i forward. In OSB 10.2, policy based encryption is available at the global, server or backup job level. Encryption keys may be generated transparently (randomly) or with a user-defined passphrase. In OSB 10.1, backup encryption to tape leveraged RMAN backup encryption only.

What is the difference between RMAN backup encryption and OSB 10.2 database backup encryption?
While both RMAN and Oracle Secure Backup 10.2 backup encryption leverage the same underlying Oracle encryption library, RMAN backup encryption is performed within the database and Oracle Secure Backup encryption occurs on the database server (outside of database). OSB 10.2 backup encryption supports Oracle9i forward while RMAN backup encryption is supports Oracle Database 10gR2 forward. Encryption key management differs as following:

• Oracle Secure Backup encryption keys are managed by OSB and stored on the OSB Administrative Server
• RMAN backup encryption keys are managed by the Oracle database
Does Oracle Secure Backup support disk-based backups?
No. However, Oracle Secure Backup supports virtual tape libraries (VTL). VTLs are disk appliances, which emulated tape drives and libraries. Oracle databases have always supported disk-based backups via RMAN. With Oracle Database 10g, the Flash Recovery Area was introduced to manage Oracle recovery related files. The Flash Recovery may be backed up to tape using Oracle Secure Backup. More information on the Flash Recovery Area is here.

Does OSB vaulting support Iron Mountain FTP format?
Yes. Oracle Secure Backup vaulting reports may be generated in Iron Mountain FTP format.

Is Oracle Secure Backup integrated with 3rd-party backup tools such as Veritas NetBackup or Tivoli Storage Manager?
No, Oracle Secure Backup is not integrated with any 3rd-party backup tools. Oracle Secure Backup is an alternative to these products offering centralized backup management for both database data, in conjunction with Recovery Manager (RMAN), and non-database data stored in file systems.

Does Oracle Secure Backup support online backups of 3rd-party databases?
No, Oracle Secure Backup does not provide native plug-ins of non-Oracle databases. Non-Oracle databases may be backed up offline (closed/consistent) as part of a file system backup. Consult with your vendor on best practices of backing up and recovering applications.

Can Oracle Secure Backup share a server and/or tape resources with other backup applications?
It is not recommended to install two backup applications on the same server or share tape hardware due to potential contention of resources. Overlapping background processes may be problematic causing unusual behavior for one or both backup applications. However, a partitioned library may be successfully shared between two applications.

What is auxiliary instance? when it is created?

2008-09-06T14:46:00.000-07:00

When you perform a tablespace point-in-time recovery (TSPITR) to recover from certain database errors, Oracle Database 10g will now automatically create the auxiliary instance and remove it after the recovery is over.

This automatically generated instance will be in the same database server. Remember, as with previous versions, instance creation introduces performance overhead during the recovery operation.

Automatic creation of Temporary Datafiles

Starting from release 2, Temporary datafiles that belong to locally managed temporary tablespaces are automatically re-created during RMAN recovery operation. This eliminates the need to manually create temporary tablespaces after recovery.

New RMAN Dynamic Performance Views

In Oracle 10.2 and above, in order to provide more details about its operation, RMAN is supported by a number of new dynamic performance views including:

V$BACKUP_ARCHIVELOG_DETAILS
V$BACKUP_ARCHIVELOG_SUMMARY
V$BACKUP_CONTROLFILE_DETAILS
V$BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_COPY_DETAILS
V$BACKUP_COPY_SUMMARY
V$BACKUP_DATAFILE_DETAILS
V$BACKUP_DATAFILES_SUMMARY
V$BACKUP_JOB_DETAILS
V$BACKUP_PIECE_DETAILS
V$BACKUP_SET_DETAILS
V$BACKUP_SET_SUMMARY
V$BACKUP_SPFILE_DETAILS
V$BACKUP_SPFILE_SUMMARY

One other useful view is V$RMAN_BACKUP_JOB_DETAILS. It informs you about history of all backups done by the RMAN. You will find details like how long the backup took, how many RMAN jobs have been issued, the status of each job, what time they started and completed, rate of the backup produced and how fast data was read and written by the process.

COL STATUS FORMAT A9
COL HRS FORMAT 999.99
SELECT
SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'DD/MM/YY HH24:MI')
START_TIME,
TO_CHAR(END_TIME,'DD/MM/YY HH24:MI')
END_TIME,
ELAPSED_SECONDS/3600 HRS
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY
COL INS FORMAT A10
COL OUTS FORMAT A10
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY INS,
OUTPUT_BYTES_PER_SEC_DISPLAY OUTS,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY

Another new view is V$RMAN_BACKUP_TYPE. It informs the type of backups performed by RMAN : BACKUPSET, SPFILE, CONTROLFILE, ARCHIVELOG, DATAFILE INCR, DATAFILE FULL, DB INCR, RECVR AREA and DB FULL.

The view V$RMAN_OUTPUT records the output (log) from the RMAN jobs so that you can view it later. This view is useful for checking log of scripted RMAN jobs as well as ad-hoc jobs.

Note: All those view are in-memory and cleared upon the instance shut down.

Oracle Secure Backup

• In Oracle Database 10g Release 2, a new tool called Oracle Secure Backup (OSB) is available. OSB enables you to use RMAN to backup directly to tape library without using the costly third-party MML layer.

• OSB can be controlled and administered via Oracle Enterprise Manager or obtool command-line.

• Beside backing up the database, OSB can be used to backup filesystems as well.

Automatic Instance Creation for RMAN TSPITR

2008-09-06T14:43:00.001-07:00

Automatic Instance Creation for RMAN TSPITR
If a tablespace point-in-time recovery (TSPITR) is initiated with no reference to an auxillary instance RMAN now automatically creates an one. The auxillary instance configuration is based on that of the target database. As a result, any channels required for the restore operations must be present in the target database so they are configured correctly in the auxillary instance. The location of the datafiles for the auxillary instance are specified using the AUXILIARY DESTINATION clause shown below.

RECOVER TABLESPACE users
UNTIL LOGSEQ 2400 THREAD 1
AUXILIARY DESTINATION '/u01/oradata/auxdest';

The tablespace is taken offline, restored from a backup, recovered to the specified point-in-time in the auxillary instance and re-imported into the target database. The tablespace in the target database should then be backed up and the tablespace brought back online.

BACKUP TABLESPACE users;
SQL "ALTER TABLESPACE users ONLINE";

On successful completion the auxillary instance will be cleaned up automatically. In the event of errors the auxillary instance is left intact to aid troubleshooting.

Oracle 10 RMAN Views

2008-09-06T14:41:00.001-07:00

Oracle 10g includes additional V$ views making the reporting of backup operations more transparent.

* V$RMAN_OUTPUT - This is an in-memory view of the messages reported by RMAN holding a maximum of 32767 rows. Since this information is not recorded in the controlfile it is lost on instance restart.
* V$RMAN_STATUS - This view displays progress and status information for in-progress and complete RMAN jobs. The information for the in-progress jobs is memory only, while the complete job information comes from the controlfile.
* V$BACKUP_FILES - This view display information about RMAN image copies, backupsets and archived logs, similar to the information listed by the RMAN commands LIST BACKUP and LIST COPY. This view relies on the DBMS_RCVMAN.SETDATABASE procedure being run to set the database.

The V$RMAN_CONFIGURATION view from Oracle 9i is still available in Oracle 10g.

How to setup RMAN in Oracle 9i

2008-09-06T14:34:00.000-07:00

Commands and setup parameters used to configure a typical RMAN environment.

1. Configuring the Backup Database User

The RMAN executable requires a SYSDBA connection to the database. Although it is possible to use the SYS account, I generally create a user dedicated to backing up the database. Before creating the new user, ensure that your database is configured to use a password file.

% echo $ORACLE_SID
ORA920

% sqlplus "/ as sysdba"

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE
------------------------------- ----------- ------------
remote_login_passwordfile string EXCLUSIVE

SQL> CREATE USER backup_admin IDENTIFIED BY backup_admin
2 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

User created.

SQL> GRANT sysdba TO backup_admin;

Grant succeeded.

We should now test running RMAN while logging in as the newly created database user:

% rman target backup_admin/backup_admin

Recovery Manager: Release 9.2.0.5.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORA920 (DBID=6732194167)

RMAN> exit

Recovery Manager complete.

Configuring RMAN Default Parameters

RMAN (Oracle9i and higher) now allows the DBA to perform automated database backup and recovery. This feature is supported by RMAN with its ability to define default values for a number of settings, (i.e. channel configuration). Setting RMAN settings is performed with the configure command.

Before configuring our RMAN environment, lets take a look at the default settings by using the show all command:

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_ORA920.f'; # default

New RMAN Configuration Parameters

configure retention policy to redundancy 2;
configure default device type to disk;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/orabackup/rman/ORA920/%F';
configure channel device type disk format '/orabackup/rman/ORA920/backup_db_%d_S_%s_P_%p_T_%t' maxpiecesize 1024m;

10g RMAN Configuration Parameters

configure controlfile autobackup on;
configure channel device type disk maxpiecesize 2 g;

After running the above configuration commands in RMAN, here is what the show all command displays:

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup/rman/ORA920/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/orabackup/rman/ORA920/backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_ORA920.f'; # default

Backing up the Target Database

Now that RMAN is configured, backing up the database (including backing up all archive redo logs), is as easy as:

RMAN Statements to Perform Full (online) Database Backup

% rman target backup_admin/backup_admin nocatalog msglog rman_full_backup_db_online_ORA920.log

RMAN> backup database plus archivelog delete input;
RMAN> delete noprompt force obsolete;
RMAN> exit;

The following table includes all resulting backup sets contained in the control file after performing the above backup routine.

BS
Key Backup
Type Device
Type Controlfile
Included? SPFILE
Included? Piece
Number Handle (Piece) Name Start
Time End
Time Elapsed
Seconds Tag Block
Size
2 Datafile Full DISK 1 /orabackup/rman/ORA920/backup_db_ORA920_S_5_P_1_T_543443342 11/28/04 20:29:02 11/28/04 20:32:44 222 TAG20041128T202902 8,192
2 /orabackup/rman/ORA920/backup_db_ORA920_S_5_P_2_T_543443342 11/28/04 20:32:47 11/28/04 20:34:45 118 TAG20041128T202902 8,192
3 Archived Logs DISK 1 /orabackup/rman/ORA920/backup_db_ORA920_S_6_P_1_T_543443693 11/28/04 20:34:53 11/28/04 20:34:53 0 TAG20041128T203452 512
4 Datafile Full DISK YES YES 1 /orabackup/rman/ORA920/c-2542143170-20041128-01 11/28/04 20:34:54 11/28/04 20:34:55 1 8,192

Now, let's talk about the actual steps that were performed by RMAN after running the full (online) database backup statements. I attempted to annotate the RMAN log file (below) with the following steps. Note that the first 6 steps were all part of the backup database plus archivelog delete input; statement. The 7th step includes the delete noprompt force obsolete; statement

1. The first action performed by RMAN is a log switch (using the alter system archive log current command).

2. Next, all existing archived redo logs (including the one just archived) are backed up to a Archive Log backup set. Note that at the end of the RMAN statements in our example, this archive log backup set (backup_db_ORA920_S_4_P_1_T_543443326) will be removed as it will no longer be needed once the database files are backed up.

3. RMAN then starts the backup of the actual database files. For this backup set, (BS Key - 2), RMAN creates two backup pieces: backup_db_ORA920_S_5_P_1_T_543443342 and backup_db_ORA920_S_5_P_2_T_543443342.

4. After the full database backup, RMAN performs another archive redo log switch (using the alter system archive log current command).

5. After the archive redo log switch completes, RMAN creates an Archived Log backup set (BS Key - 3) of the remaining archived redo logs (using the backup archivelog all command). This is backup piece backup_db_ORA920_S_6_P_1_T_543443693. Keep in mind that this archived redo log backup set includes the redo logs that occured during the full database backup.

6. Finally, the autobackup of the control file and SPFILE occurs and creates another datafile backup set (BS Key - 4). This is contained in backup piece c-2542143170-20041128-01. Since a full database backup will always include datafile 1, which belongs to the SYSTEM tablespace, there will always be a backup of the control file and the SPFILE. At this point, all tasks for the backup database plus archivelog delete input; statement have been completed.

7. The delete noprompt force obsolete; statement is then run to apply the retention policy we have configured. In our example, the first Archive Log backup set (that was performed at the begining on this example) will be removed. This was piece backup_db_ORA920_S_4_P_1_T_543443326.

RMAN Full (online) Database Backup Log File

Recovery Manager: Release 9.2.0.5.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORA920 (DBID=2542143170)
using target database controlfile instead of recovery catalog

RMAN>

Starting backup at 28-NOV-04
current log archived (Step 1)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting archive log backupset (Step 2)
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=89 recid=1 stamp=543441301
input archive log thread=1 sequence=90 recid=2 stamp=543443042
input archive log thread=1 sequence=91 recid=3 stamp=543443132
input archive log thread=1 sequence=92 recid=4 stamp=543443258
input archive log thread=1 sequence=93 recid=5 stamp=543443325
channel ORA_DISK_1: starting piece 1 at 28-NOV-04
channel ORA_DISK_1: finished piece 1 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_4_P_1_T_543443326 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s89.dbf recid=1 stamp=543441301
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s90.dbf recid=2 stamp=543443042
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s91.dbf recid=3 stamp=543443132
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s92.dbf recid=4 stamp=543443258
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s93.dbf recid=5 stamp=543443325
Finished backup at 28-NOV-04

Starting backup at 28-NOV-04
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset (Step 3)
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u06/app/oradata/ORA920/system01.dbf
input datafile fno=00010 name=/u06/app/oradata/ORA920/users01.dbf
input datafile fno=00002 name=/u06/app/oradata/ORA920/undotbs01.dbf
input datafile fno=00011 name=/u06/app/oradata/ORA920/perfstat01.dbf
input datafile fno=00007 name=/u06/app/oradata/ORA920/example01.dbf
input datafile fno=00003 name=/u06/app/oradata/ORA920/cwmlite01.dbf
input datafile fno=00004 name=/u06/app/oradata/ORA920/drsys01.dbf
input datafile fno=00005 name=/u06/app/oradata/ORA920/odm01.dbf
input datafile fno=00006 name=/u06/app/oradata/ORA920/xdb01.dbf
input datafile fno=00008 name=/u06/app/oradata/ORA920/indx01.dbf
input datafile fno=00009 name=/u06/app/oradata/ORA920/tools01.dbf
input datafile fno=00012 name=/u06/app/oradata/ORA920/users02.dbf
channel ORA_DISK_1: starting piece 1 at 28-NOV-04
channel ORA_DISK_1: finished piece 1 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_5_P_1_T_543443342 comment=NONE
channel ORA_DISK_1: starting piece 2 at 28-NOV-04
channel ORA_DISK_1: finished piece 2 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_5_P_2_T_543443342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:50
Finished backup at 28-NOV-04

Starting backup at 28-NOV-04
current log archived (Step 4)
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset (Step 5)
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=94 recid=6 stamp=543443692
channel ORA_DISK_1: starting piece 1 at 28-NOV-04
channel ORA_DISK_1: finished piece 1 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_6_P_1_T_543443693 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s94.dbf recid=6 stamp=543443692
Finished backup at 28-NOV-04

Starting Control File and SPFILE Autobackup at 28-NOV-04 (Step 6)
piece handle=/orabackup/rman/ORA920/c-2542143170-20041128-01 comment=NONE
Finished Control File and SPFILE Autobackup at 28-NOV-04

RMAN>
RMAN retention policy will be applied to the command (Step 7)
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 28-NOV-04
Backup Piece 1 28-NOV-04 /orabackup/rman/ORA920/backup_db_ORA920_S_4_P_1_T_543443326
deleted backup piece
backup piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_4_P_1_T_543443326 recid=1 stamp=543443326
Deleted 1 objects

RMAN>

Recovery Manager complete.

UNIX RMAN Backup Script

The following is a short UNIX script that can be used to perform the RMAN backup example we used earlier in this article.

Simple RMAN Full Database (online) Backup Script

#!/bin/ksh

rman target backup_admin/backup_admin nocatalog msglog rman_full_backup_db_online_ORA920.log EOF
backup database plus archivelog delete input;
delete noprompt force obsolete;
exit;
EOF

exit

What is block pinging and why is it so bad?

2008-09-06T11:14:00.000-07:00

What is block pinging and why is it so bad?

Starting with Oracle 9i, RAC can transfer blocks from one instance to another across the interconnect (cache fusion). This method is much faster than the old "pinging" method, where one instance had to write the block to disk before another instance could read it.

Oracle 8i and below:

Pinging is the process whereby one instance requests another to write a set of blocks from its SGA to disk so it can obtain it in exclusive mode. This method of moving data blocks from one instance's SGA to another is extremely slow. The challenge of tuning RAC/OPS is to minimize pinging activity.