Tuesday, October 14, 2008

V$RMAN_BACKUP_SUBJOB_DETAILS

V$RMAN_BACKUP_SUBJOB_DETAILS merges similar operations within an RMAN session into a single row. For example, if there are four BACKUP DATAFILE commands, three RECOVERY COPY OF DATAFILE commands, and one BACKUP RECOVERY AREA command, this view will contain three rows - one each for BACKUP, ROLLFORWARD, and COPY_DISK_TO_TAPE operation.

Column

Datatype

Description

SESSION_KEY

NUMBER

Session identifier

SESSION_RECID

NUMBER

Together with SESSION_KEY and SESSION_STAMP, used to uniquely identify job output from V$RMAN_OUTPUT

SESSION_STAMP

NUMBER

Together with SESSION_KEY and SESSION_RECID, used to uniquely identify job output from V$RMAN_OUTPUT

OPERATION

VARCHAR2(33)

Can be BACKUP, ROLLFORWARD, VALIDATE, or COPY_DISK_TO_TAPE. A row for each suboperation type for the session will be in the output view.

COMMAND_ID

VARCHAR2(33)

Either a user-specified SET COMMAND ID or a unique command ID generated by RMAN

START_TIME

DATE

Start time of the first BACKUP command in the job

END_TIME

DATE

End time of the last BACKUP command in the job

INPUT_BYTES

NUMBER

Sum of all input file sizes backed up by this job

OUTPUT_BYTES

NUMBER

Output size of all pieces generated by this job

STATUS_WEIGHT

NUMBER

Used internally by Enterprise Manager

OBJECT_TYPE_WEIGHT

NUMBER

Used internally by Enterprise Manager

OPTIMIZED_WEIGHT

NUMBER

Used internally by Enterprise Manager

OUTPUT_DEVICE_TYPE

VARCHAR2(17)

Can be DISK, SBT, or *. An * indicates more than one device (in most cases, it will be DISK or SBT).

AUTOBACKUP_DONE

VARCHAR2(3)

YES or NO, depending upon whether or not a control file autobackup was done as part of this job

STATUS

VARCHAR2(23)

One of the following values:

· RUNNING WITH WARNINGS

· RUNNING WITH ERRORS

· COMPLETED

· COMPLETED WITH WARNINGS

· COMPLETED WITH ERRORS

· FAILED

INPUT_TYPE

VARCHAR2(13)

Contains one of the following values. If the user command does not satisfy one of them, then preference is given in order, from top to bottom of the list.

· DB FULL

· RECVR AREA

· DB INCR

· DATAFILE FULL

· DATAFILE INCR

· ARCHIVELOG

· CONTROLFILE

· SPFILE

OPTIMIZED

VARCHAR2(3)

YES or NO, depending on whether optimization was applied. Applicable to backup jobs only.

AUTOBACKUP_COUNT

NUMBER

Number of autobackups performed by this job

COMPRESSION_RATIO

NUMBER

Compression ratio

INPUT_BYTES_DISPLAY

VARCHAR2(4000)

Values in user-displayable form. They will be converted to a format of nM, nG, nT, nP, and so on.

OUTPUT_BYTES_DISPLAY

VARCHAR2(4000)

Values in user-displayable form. They will be converted to a format of nM, nG, nT, nP, and so on.

Note:

1.

select session_key, operation, status, compression_ratio, input_bytes_display, output_bytes_display

from V$RMAN_BACKUP_SUBJOB_DETAILS;

SESSION_KEY

OPERATION

STATUS

COMPRESSION_RATIO

INPUT_BYTES_DISPLAY

OUTPUT_BYTES_DISPLAY

18205

BACKUP

COMPLETED

3.41799049598936

1.17T

350.35G

17946

BACKUP

COMPLETED

3.46084052187209

1023.16G

295.64G

18317

BACKUP

COMPLETED

3.55417940479047

1.30T

373.20G

18060

BACKUP

FAILED

3.20107064644334

115.62G

36.12G

18351

BACKUP

COMPLETED

3.5238063780519

958.81G

272.10G

17981

BACKUP

COMPLETED

3.46973467834098

1.08T

319.63G

18054

BACKUP

FAILED

1

0.00K

0.00K

18376

BACKUP

COMPLETED

3.51405127279056

819.52G

233.21G

18095

BACKUP

COMPLETED

3.4687228625705

1.02T

301.19G

17521

BACKUP

COMPLETED

3.48035890958661

1.03T

303.29G

Oracle data dictionary views

Oracle dynamic performance views

Posted by Admin at 10/14/2008 12:12:00 PM 0 comments

Labels: ,

V$RMAN_BACKUP_JOB_DETAILS

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================




V$RMAN_BACKUP_JOB_DETAILS displays details about backup jobs.

Column
Datatype
Description
SESSION_KEY
NUMBER
Session identifier
SESSION_RECID
NUMBER
Together, with SESSION_KEY and SESSION_STAMP, used to uniquely identify job output from V$RMAN_OUTPUT
SESSION_STAMP
NUMBER
Together, with SESSION_KEY and SESSION_RECID, used to uniquely identify job output from V$RMAN_OUTPUT
COMMAND_ID
VARCHAR2(33)
Either a user-specified SET COMMAND ID or a unique command ID generated by RMAN
START_TIME
DATE
Start time of the first BACKUP command in the job
END_TIME
DATE
End time of the last BACKUP command in the job
INPUT_BYTES
NUMBER
Sum of all input file sizes backed up by this job
OUTPUT_BYTES
NUMBER
Output size of all pieces generated by this job
STATUS_WEIGHT
NUMBER
Used internally by Enterprise Manager
OPTIMIZED_WEIGHT
NUMBER
Used internally by Enterprise Manager
OBJECT_TYPE_WEIGHT
NUMBER
Used internally by Enterprise Manager
OUTPUT_DEVICE_TYPE
VARCHAR2(17)
Can be DISK, SBT, or *. An * indicates more than one device (in most cases, it will be DISK or SBT).
AUTOBACKUP_COUNT
NUMBER
Number of autobackups performed by this job
AUTOBACKUP_DONE
VARCHAR2(3)
YES or NO, depending upon whether or not a control file autobackup was done as part of this backup job
STATUS
VARCHAR2(23)
One of the following values:
· RUNNING WITH WARNINGS
· RUNNING WITH ERRORS
· COMPLETED
· COMPLETED WITH WARNINGS
· COMPLETED WITH ERRORS
· FAILED
INPUT_TYPE
VARCHAR2(13)
Contains one of the following values. If the user command does not satisfy one of them, then preference is given in order, from top to bottom of the list.
· DB FULL
· RECVR AREA
· DB INCR
· DATAFILE FULL
· DATAFILE INCR
· ARCHIVELOG
· CONTROLFILE
· SPFILE
OPTIMIZED
VARCHAR2(3)
YES or NO, depending on whether optimization was applied. Applicable to backup jobs only.
ELAPSED_SECONDS
NUMBER
Number of elapsed seconds
COMPRESSION_RATIO
NUMBER
Compression ratio
INPUT_BYTES_PER_SEC
NUMBER
Input read-rate-per-second
OUTPUT_BYTES_PER_SEC
NUMBER
Output write-rate-per-second
INPUT_BYTES_DISPLAY
VARCHAR2(4000)
Values in user-displayable form. They will be converted to a format of nM, nG, nT, nP, and so on.
OUTPUT_BYTES_DISPLAY
VARCHAR2(4000)
Values in user-displayable form. They will be converted to a format of nM, nG, nT, nP, and so on
INPUT_BYTES_PER_SEC_DISPLAY
VARCHAR2(4000)
Input read-rate-per-second. These values are in user-displayable form. They will be converted to a format of nM, nG, nT, nP, and so on.
OUTPUT_BYTES_PER_SEC_DISPLAY
VARCHAR2(4000)
Output write-rate-per-second. These values are in user-displayable form. They will be converted to a format of nM, nG, nT, nP, and so on.
TIME_TAKEN_DISPLAY
VARCHAR2(4000)
Time taken, shown in user-displayable format h:m:s
Note:
  1. Useful query:
SELECT end_time, status, session_key, session_recid, session_stamp,
command_id, start_time, time_taken_display, input_type,
output_device_type, input_bytes_display, output_bytes_display,
output_bytes_per_sec_display
FROM V$RMAN_BACKUP_JOB_DETAILS;
select session_key, start_time, end_time, output_device_type, status, time_taken_display
from V$RMAN_BACKUP_JOB_DETAILS;
SESSION_KEY
START_TIME
END_TIME
OUTPUT_
DEVICE_TYPE
STATUS
TIME_TAKEN_
DISPLAY
17384
2008-07-15 6:00:19 PM
2008-07-16 5:14:57 AM
DISK
COMPLETED
11:14:38
17409
2008-07-17 6:00:20 PM
2008-07-18 4:23:06 AM
DISK
COMPLETED
10:22:46
18006
2008-09-04 6:01:09 PM
2008-09-05 3:59:53 AM
DISK
COMPLETED
09:58:44
18054
2008-09-09 9:04:44 AM
FAILED
18060
2008-09-09 1:14:13 PM
2008-09-09 2:24:57 PM
DISK
FAILED
01:10:44
18436
2008-10-09 6:00:20 PM
2008-10-10 2:08:14 AM
DISK
COMPLETED
08:07:54
18461
2008-10-11 6:01:06 PM
2008-10-12 2:04:21 AM
DISK
COMPLETED
08:03:15
  1. Accessing V$RMAN_BACKUP_JOB_DETAILS will cause a performance issue in Oracle 10.2.0.2 – 10.2.0.4. There are several notes related the view in Metalink.
The workaround is:
SQL> Connect / as sysdba
exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');
exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');

Oracle data dictionary views

Oracle dynamic performance views
Last updated: August 11, 2009

Posted by Admin at 10/14/2008 12:11:00 PM

Labels: ,

V$RMAN_ENCRYPTION_ALGORITHMS

V$RMAN_ENCRYPTION_ALGORITHMS displays supported encryption algorithms. It is used by the RMAN client to validate user-requested algorithms. This view will list AES128, AES192, and AES256 encryption algorithms for the current release. The default algorithm is AES128.

Column

Datatype

Description

ALGORITHM_ID

NUMBER

Number to identify the algorithm

ALGORITHM_NAME

VARCHAR2(64)

Name of the algorithm (for example, AES128, AES192, and AES256)

ALGORITHM_DESCRIPTION

VARCHAR2(64)

Description of the algorithm

IS_DEFAULT

VARCHAR2(3)

YES if this is the default encryption algorithm; otherwise NO.

RESTORE_ONLY

VARCHAR2(3)

NO if this algorithm can be used for backup; otherwise YES.

Note:

1.

select * from V$RMAN_ENCRYPTION_ALGORITHMS;

ALGORITHM_ID

ALGORITHM_NAME

ALGORITHM_DESCRIPTION

IS_DEFAULT

RESTORE_ONLY

1

AES128

AES 128-bit key

YES

NO

2

AES192

AES 192-bit key

NO

NO

3

AES256

AES 256-bit key

NO

NO

Oracle data dictionary views

Oracle dynamic performance views

Posted by Admin at 10/14/2008 11:46:00 AM 0 comments

Labels: ,

Sunday, October 12, 2008

Service-related statistics

V$SERVICE_WAIT_CLASS: aggregated wait counts and wait times for each service on a wait class basis, shows all wait statistics for each service, broken down by wait class.
V$SERVICE_EVENT: aggregated wait counts and wait times for each service on a per wait class basis, shows the same information as V$SERVICE_WAIT_CLASS, except that it is further broken down by event ID.

Oracle data dictionary views
Oracle dynamic performance views

Posted by Admin at 10/12/2008 11:35:00 PM 0 comments

Labels: ,

Session-related statistics

V$SESSION: stores current session information for each user logged on.
V$SESSION or V$SESSION_WAIT: used to determine the resources or events for which active sessions are waiting.
V$SESSTAT: displays user session statistics.
V$SESSION_EVENT: list information on waits for an event by a session.
V$SESSTAT and V$SYSSTAT: cumulative values for statistics are generally available through these two views.
V$MYSTAT: displays the statistics for the current session.
V$SESSMETRIC: display the performance metric values for all active sessions. The view lists performance metrics such as CPU usage, number of physical reads, number of hard parses, and the logical read ratio.

Oracle data dictionary views
Oracle dynamic performance views

Posted by Admin at 10/12/2008 11:23:00 PM 0 comments

Labels: ,

Oracle system statistics

To effectively diagonse performance problems, statistics must be available. Oracle generates many types of cumulative statistics for the system, sessions, and individual SQL statements. The Oracle database also tracks cumulative statistics oln segments and services.

Waite events statistics
V$EVENT_NAME: all the possible wait events are cataloged here.
V$SYSTEM_EVENT: Cumulative statistics for all sessions are stored here and showthe total waits for a particular event since instance startup.

Systemwide statistics
V$STATNAME: all the systemwide statistics are cataloged here and Oracle 10g has about 330 statistics.
V$SYSSTAT: The server displays all calculated system statistics here. The view can be used to find cumulative totals since the instance started.
V$SYSTEM_WAIT_CLASS: all wait events for a particular wait class class.

SGA global statistics
V$SGASTAT: all caculated memory statistics. The view can be queried to find cumulative totals of detailed SGA usage since the instance started.

Oracle data dictionary views
Oracle dynamic performance views

Posted by Admin at 10/12/2008 11:10:00 PM 0 comments

Labels: ,

Subscribe to: Posts (Atom)