Monday, August 31, 2009

10g RMAN Incremental backup

Why incremental backup ?

The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files.Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are neither desirable nor practical. Therefore , doing incremental backups in RMAN is an ideal alternative.

WhyDBAs dis not like incremental backup in 9i ?

Oracle does full scan for finding changes in 9i which generates some performance problem.This is why most DBAs did not like incremental backup in 9i.

What is new with 10g incremental backup ?

Block changes can be tracked in 10g which means that Oracle does not need to do full scan anymore to find out changes.

To enable track changing run the following command.

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

To disable block track change.

alter database disable block change tracking;

Wednesday, August 12, 2009

How to Enable Listener Logging and Tracing

Here we will see two different ways to enable logging and tracing of listener.ora file. These logging and tracing helps you to identify the network problems and troubleshooting connection problems in oracle.It also help to identify when a client is connected to oracle database.

Enabling Logging and Tracing in listener.ora:
1)Way 1: Putting entry in listener.ora
2)Way 2: Using Enterprise Manager.

1)Way 1: Putting entry in listener.ora
a)With an editor software edit the listener.ora file, Like on unix, vi $ORACLE_HOME/network/admin/listener.ora
b)Put an entry of LOGGING_LISTENER for logging and TRACE_LEVEL_LISTENER for tracing.Like,

TRACE_FILE_LISTENER = LISTENERTEST.trc (The destination file for the trace file)TRACE_DIRECTORY_LISTENER = /export/home (The destination directory for the trace file)LOGGING_LISTENER = on
TRACE_LEVEL_LISTENER =SUPPORT

TRACE_LEVEL:It specifies the level of detail the trace facility records for the listener.The trace level value can either be a value within the range of 0 (zero) to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of off, admin, user, or support.

i))off (equivalent to 0) provides no tracing.
ii))user (equivalent to 4) traces to identify user-induced error conditions.
iii)admin (equivalent to 6) traces to identify installation-specific problems.
iv)support (equivalent to 16) provides trace information for troubleshooting information for Oracle Support Services.

c)Reload The Listener:
Like, lsnrctl reload

2)Way 2: Using Enterprise Manager.

i)Access the Oracle Net Administration page in Oracle Enterprise Manager.
ii)Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
iii)Click Go.

The Listeners page appears.
iv)Select a listener, and then click Edit.The Edit Listeners page appears.
v)Click the Logging & Tracing tab.
vi)Specify the settings.
vii)Click OK.

Some Troubleshooting:
Format of the Listener Log Audit Trail:
---------------------------------------------
The audit trail formats text into the following fields:

Timestamp * Connect Data [* Protocol Info] * Event [* SID Service] * Return Code

Properties of the audit trail are as follows:

-Each field is delimited by an asterisk (*).
-Protocol address information and service name or SID information appear only when a connection is attempted.
-A successful connection or command returns a code of zero.
-A failure produces a code that maps to an error message.

With the return code you can be able to see which type of error and when it occurs.

Audit Trail information can be used to view trends and user activity by first storing it in a table and then collating it into a report format.

Use the Trace Assistant to Examine Trace Files

With trcasst examine the trace file, Like

trcasst trace_file_name_here.

For example to see statistics we can use,

trcasst -s /export/home/mytracefile.trc