How to Create Oracle ASM disks in windows

In windows environment ASM disks have to be created using asmtoolg.exe file, before that we have to create the partitions of given RAW disk using DISKPART utility in windows.

Once raw partition added to the target server, check the RAW partition available or not.

1. Connect to the command prompt and use below command to check the disk.

C:\diskpart 

DISKPART> list disk

2. Select the disk which you want to made partitions for ASM disk.

DISKPART> select disk 1

Read More

Top 5 I/O wait events in oracle AWR report

Top 5 I/O wait events in oracle:

“Db file sequential read” wait event occur on I/O operations on datafiles.

This wait event related to single block read, for index data blocks are table data blocks accessed through an index but can also be seen for datafile header blocks.

Troubleshooting:

Find the top sql statements in physical reads from

1. Statspack

2. AWR report under Sql order by reads or

3. From v$sql

Reasons for db file sequential read:

1.       If index range scans are involved, more blocks than necessary could be being visited if the index is unselective. By forcing and enabling the more selective index, we can access the same table data by visiting the fewer index blocks.

2.       If the indexes are fragmented, then again, we have to visit more blocks because there is a less index data per block.

3.       If the index being used has a large clustering factor, then more table data blocks have to be visited in order to get the rows in each index block.

Db file scattered read:

This wait even indicates that we are waiting for the read operation which reads number of blocks from disk. Most of the cases it indicates there is full table scan or index fast full scan.

It occurs when oracle performs multiblock reads from disk into non-contiguous buffers in the buffer cache. Such reads are issues for up to DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time. These typically happen for full table scans and fast full index scans.

Fast full index scans: When the data in the index is not in particular order.

Read More

What are the AWR, ADDM and ASH reports in Oracle? How to Analyse?

AWR, ASH and ADDM reports are tools to analyse the Oracle database performance bottlenecks of intervals.

ADDM Report:

How to generate the ADDM report?

To generate the ADDM report by non-sys users, they need below privileges. As a sys user no privileges are required.

How to grant the ADDM report generation privileges to non-sys users:

1.       Login as a sys user.

2.       Sql> grant advisor to ;

3.       Sql> grant select_catalog_role to ;

4.       Sql> grant execute on dbms_workload_repository to ;

Note: From 12c ADDM report also comes with AWR report.

ADDM generate script is in $ORACLE_HOME/RDBMS/admin/addmrpt.sql

Sql>@addmrpt.sql

ADDM report suggests potential resolution options for many issues. ADDM reports gives recommendations on various areas like CPU load, Memory Usage, I/O usage, Object contention, Application Issues, DB configuration issues, high load sql statements..etc.

Read More

What are the Oracle database and Grid/RAC health check/Monitoring tool’s?

Hang Manger

Quality Of service Management

TAF (Trace file analyser) (1513912.1)

Exacheck

Memory Guard

Cluster Healthcheck advisor

Cluster Health monitor

Cluster Verification utility

ORAchk

DBSAT (Database Security Assessment tool)

OSwatcher (301137.1)

Read More

What are the RAC/Grid specific daemons in Oracle 12c

CRSD (Cluster Ready Service Daemon):

CRSD is primarily responsible for maintaining the availability of the application resources such as DB instances. Main responsibility of the CRSD daemon is starting and stopping of the resources, relocating the resources when required to another node in the event of failure and updating the resource profiles in the OCR file and also responsible for backing up the OCR file.

Read More