My Blog

January 5, 2009

Automatic Workload Repository (AWR) & ADDM(Automatic Database Diagnostic Monitor)

Filed under: Oracle — by enotebook @ 4:30 pm
Tags: , ,

Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

bash-3.00$ pwd
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin
bash-3.00$ ls |grep awr
awrddinp.sql
awrddrpi.sql
awrddrpt.sql
awrextr.sql
awrinfo.sql
awrinpnm.sql
awrinput.sql
awrload.sql
awrrpt.sql
awrrpt_1_142_143.html
awrrpti.sql
awrsqrpi.sql
awrsqrpt.sql
catawrtb.sql
catawrvw.sql
dbmsawr.sql
prvtawr.plb
bash-3.00$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.2.0 – Production on Mon Jan 5 10:58:51 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id    DB Name      Inst Num Instance
———– ———— ——– ————
1202538587 ORCL                1 orcl1

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type:

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id     Inst Num DB Name      Instance     Host
———— ——– ———— ———— ————
1202538587        2 ORCL         orcl2        solaris02
* 1202538587        1 ORCL         orcl1        solaris01

Using 1202538587 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day’s Completed Snapshots

Snap
Instance     DB Name        Snap Id    Snap Started    Level
———— ———— ——— —————— —–
orcl1        ORCL               134 05 Jan 2009 00:00      1
135 05 Jan 2009 01:00      1
136 05 Jan 2009 02:00      1
137 05 Jan 2009 03:00      1
138 05 Jan 2009 04:00      1
139 05 Jan 2009 05:00      1
140 05 Jan 2009 06:00      1
141 05 Jan 2009 07:00      1
142 05 Jan 2009 08:00      1
143 05 Jan 2009 09:00      1
144 05 Jan 2009 10:00      1
145 05 Jan 2009 11:00      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 144
Begin Snapshot Id specified: 144

Enter value for end_snap: 145
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_144_145.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Report written to awrrpt_1_144_145.html
SQL>

Now you can check the file of awrrpt_1_144_145.html to see the detailed info what likes you have been using statpack to generate.
As a optional part of oracle database performance tuning process, we can use ADDM(Automatic Database Diagnostic Monitor) to check database system  by running the scripts of @?/rdbms/admin/addmrpt.sql.
The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes:

* CPU load
* Memory usage
* I/O usage
* Resource intensive SQL
* Resource intensive PL/SQL and Java
* RAC issues
* Application issues
* Database configuration issues
* Concurrency issues
* Object contention

For more details, refer to following links:
http://www.oracle.com/technology/pub/articles/10gdba/week6_10gdba.html
http://www.oracle-base.com/articles/10g/AutomaticWorkloadRepository10g.php
http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_talking.html
http://www.oracle-base.com/articles/10g/AutomaticDatabaseDiagnosticMonitor10g.php

Advertisements

Using RMAN to delete the backups of database and archivelog

Filed under: Oracle — by enotebook @ 4:30 pm
Tags: ,

This is a simple test by using RMAN to delete the backups of database and archivelog.

RMAN> backup database tag ‘dbfull0812’;

Starting backup at 04-JAN-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
.
.
.
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-JAN-09
channel ORA_DISK_1: finished piece 1 at 04-JAN-09
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_01_04/o1_mf_nnndf_DBFULL0812_4p0cb35n_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 04-JAN-09
channel ORA_DISK_1: finished piece 1 at 04-JAN-09
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_01_04/o1_mf_ncsnf_DBFULL0812_4p0cjwr6_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 04-JAN-09

RMAN> list backup of database;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
318     Full    1G         DISK        00:04:01     01-DEC-08
BP Key: 318   Status: AVAILABLE  Compressed: NO  Tag: DBFULL
Piece Name: /u01/app/orabackup/data/dbfull_a7k15m6q_327_1
List of Datafiles in backup set 318
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
1       Full 51602730   01-DEC-08 /u01/app/oracle/oradata/orcl/system01.dbf
2       Full 51602730   01-DEC-08 /u01/app/oracle/oradata/orcl/undotbs01.dbf
.
.
.

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
418     Full    1G         DISK        00:03:30     04-JAN-09
BP Key: 418   Status: AVAILABLE  Compressed: NO  Tag: DBFULL0812
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_01_04/o1_mf_nnndf_DBFULL0812_4p0cb35n_.bkp
List of Datafiles in backup set 418
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
1       Full 53779751   29-DEC-08 /u01/app/oracle/oradata/orcl/system01.dbf
2       Full 53779751   29-DEC-08 /u01/app/oracle/oradata/orcl/undotbs01.dbf
.
.
.

RMAN> delete backup of database tag ‘dbfull0812’;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
——- ——- — — ———– ———– ———-
418     418     1   1   AVAILABLE   DISK        /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_01_04/o1_mf_nnndf_DBFULL0812_4p0cb35n_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_01_04/o1_mf_nnndf_DBFULL0812_4p0cb35n_.bkp recid=418 stamp=675257411
Deleted 1 objects

RMAN> list backup of database;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
318     Full    1G         DISK        00:04:01     01-DEC-08
BP Key: 318   Status: AVAILABLE  Compressed: NO  Tag: DBFULL
Piece Name: /u01/app/orabackup/data/dbfull_a7k15m6q_327_1
List of Datafiles in backup set 318
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
1       Full 51602730   01-DEC-08 /u01/app/oracle/oradata/orcl/system01.dbf
2       Full 51602730   01-DEC-08 /u01/app/oracle/oradata/orcl/undotbs01.dbf
.
.
.

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
318     Full    1G         DISK        00:04:01     01-DEC-08
BP Key: 318   Status: AVAILABLE  Compressed: NO  Tag: DBFULL
Piece Name: /u01/app/orabackup/data/dbfull_a7k15m6q_327_1
List of Datafiles in backup set 318
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
1       Full 51602730   01-DEC-08 /u01/app/oracle/oradata/orcl/system01.dbf
2       Full 51602730   01-DEC-08 /u01/app/oracle/oradata/orcl/undotbs01.dbf
.
.
.

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
319     Full    4M         DISK        00:00:02     01-DEC-08
BP Key: 319   Status: AVAILABLE  Compressed: NO  Tag: DBFULL
Piece Name: /u01/app/orabackup/data/dbfull_a8k15meh_328_1
Controlfile Included: Ckp SCN: 51602878     Ckp time: 01-DEC-08
SPFILE Included: Modification time: 03-NOV-08

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
320     Full    4M         DISK        00:00:00     01-DEC-08
BP Key: 320   Status: AVAILABLE  Compressed: NO  Tag: CTL
Piece Name: /u01/app/orabackup/data/ctl_329_1_672324058
Controlfile Included: Ckp SCN: 51602889     Ckp time: 01-DEC-08

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
419     Full    4M         DISK        00:00:02     04-JAN-09
BP Key: 419   Status: AVAILABLE  Compressed: NO  Tag: DBFULL0812
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_01_04/o1_mf_ncsnf_DBFULL0812_4p0cjwr6_.bkp
Controlfile Included: Ckp SCN: 53780052     Ckp time: 29-DEC-08
SPFILE Included: Modification time: 04-JAN-09

RMAN> delete noprompt backup of archivelog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found “;”: expecting one of: “all, from, high, like, logseq, low, scn, sequence, time, until”
RMAN-01007: at line 1 column 37 file: standard input

RMAN>

RMAN> delete noprompt backup of archivelog from logseq 3000;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
——- ——- — — ———– ———– ———-
321     321     1   1   AVAILABLE   DISK        /u01/app/orabackup/data/arch_aak15mf4_330_1
323     323     1   1   AVAILABLE   DISK        /u01/app/orabackup/data/arch_ack15mfc_332_1
.
.
.
backup piece handle=/u01/app/orabackup/data/arch_aak15mf4_330_1 recid=321 stamp=672324069
deleted backup piece
backup piece handle=/u01/app/orabackup/data/arch_ack15mfc_332_1 recid=323 stamp=672324077
deleted backup piece
.
.
.
Deleted 59 objects

iSCSI storage with zvols – setup iscsi on Solaris 10

Filed under: Unix — by enotebook @ 4:25 pm
Tags: ,

Solairs 10 supports iscsi storage via ZFS, following shows the process to setup iscsi storage with zvols

1. create a pool on the server and share it over iscsi.
bash-3.00# zpool create iscsistore c0t1d0
bash-3.00# zfs create -s -V 30gb iscsistore/zvol
bash-3.00# zfs set shareiscsi=on iscsistore/zvol
bash-3.00# iscsitadm list target -v
Target: iscsistore/zol
iSCSI Name: iqn.1986-03.com.sun:02:04342d14-f21d-c534-e00c-a417327804e7
Alias: iscsistore/zol
Connections: 1
Initiator:
iSCSI Name: iqn.1986-03.com.sun:01:4afacef3ffff.494a1fa4
Alias: unknown
ACL list:
TPGT list:
LUN information:
LUN: 0
GUID: 01000013728528c000002a00494b45bd
VID: SUN
PID: SOLARIS
Type: disk
Size:   30G
Backing store: /dev/zvol/rdsk/iscsistore/zol
Status: online
bash-3.00#

2. on the client, we need to discover the iSCSI share (172.16.113.146 is the IP of the server):
bash-3.00# iscsiadm list discovery
Discovery:
Static: disabled
Send Targets: enabled
iSNS: disabled

bash-3.00# iscsiadm modify discovery –sendtargets enable
bash-3.00# iscsiadm add discovery-address 172.16.113.146
bash-3.00# svcadm enable network/iscsi_initiator
bash-3.00# iscsiadm list target
bash-3.00# iscsiadm list target
Target: iqn.1986-03.com.sun:02:04342d14-f21d-c534-e00c-a417327804e7
Alias: iscsistore/zol
TPGT: 1
ISID: 4000002a0000
Connections: 1
bash-3.00#

3. now we can format the shared iscsi storage as needed on the client
bash-3.00# format
Searching for disks…done

AVAILABLE DISK SELECTIONS:
0. c1d0 <DEFAULT cyl 10526 alt 2 hd 255 sec 63>
/pci@0,0/pci-ide@1f,2/ide@0/cmdk@0,0
1. c2t01000013728528C000002A00494B45BDd0 <DEFAULT cyl 3913 alt 2 hd 255 sec 63>
/scsi_vhci/disk@g01000013728528c000002a00494b45bd
Specify disk (enter its number): 1
selecting c2t01000013728528C000002A00494B45BDd0
[disk formatted]

FORMAT MENU:
disk       – select a disk
type       – select (define) a disk type
partition  – select (define) a partition table
current    – describe the current disk
format     – format and analyze the disk
fdisk      – run the fdisk program
repair     – repair a defective sector
label      – write label to the disk
analyze    – surface analysis
defect     – defect list management
backup     – search for backup labels
verify     – read and display labels
save       – save new disk/partition definitions
inquiry    – show vendor, product and revision
volname    – set 8-character volume name
!<cmd>     – execute <cmd>, then return
quit
format> q
bash-3.00#
Done.

January 4, 2009

Hello world!

Filed under: Uncategorized — by enotebook @ 7:16 am

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Blog at WordPress.com.