VMCD.ORG

/* ************************************************************* */
/* Index Fragmentation Status (idsfrag.sql):                     */
/*                                                               */
/* This script will report the index fragmentation status        */
/*   for a schema.                                               */
/*                                                               */
/* Note: - Do not run this scrip during peak processing hours!!! */
/*       - This script will fail for locked tables.              */
/*                                                               */
/* ************************************************************* */

The percentage of deleted rows exceeds 30% of the total, i.e. if
1.del_lf_rows / lf_rows > 0.3.
2.If the ‘HEIGHT’ is greater than 4.
3.If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large 4.number of deletes, indicating that the index should be rebuilt.

[ohasd(25286)]CRS-2112:The OLR service started on node db-41.
2012-04-25 14:46:13.120
[ohasd(25286)]CRS-1301:Oracle High Availability Service started on node db-41.
2012-04-25 14:46:13.120
[ohasd(25286)]CRS-8017:location: /etc/oracle/lastgasp has 2 reboot advisory log files, 0 were announced and 0 errors occurred
2012-04-25 14:46:14.181
[/data/11.2.0/grid/bin/orarootagent.bin(25332)]CRS-5016:Process "/data/11.2.0/grid/bin/acfsload" spawned by agent "/data/11.2.0/grid/bin/orarootagent.bin" for action "check" failed: details at "(:CLSN00010:)" in "/data/11.2.0/grid/log/db-41/agent/ohasd/orarootagent_root/orarootagent_root.log"
2012-04-25 14:46:17.031
[gpnpd(25510)]CRS-2328:GPNPD started on node db-41.
2012-04-25 14:46:20.794
[cssd(25593)]CRS-1713:CSSD daemon is started in clustered mode
2012-04-25 14:46:22.520
[ohasd(25286)]CRS-2767:Resource state recovery not attempted for 'ora.diskmon' as its target state is OFFLINE
2012-04-25 14:46:40.715
[cssd(25593)]CRS-1707:Lease acquisition for node db-41 number 1 completed
2012-04-25 14:46:42.003
[cssd(25593)]CRS-1605:CSSD voting file is online: /dev/mapper/Vote03; details in /data/11.2.0/grid/log/db-41/cssd/ocssd.log.
2012-04-25 14:46:42.008
[cssd(25593)]CRS-1605:CSSD voting file is online: /dev/mapper/Vote02; details in /data/11.2.0/grid/log/db-41/cssd/ocssd.log.
2012-04-25 14:46:42.015
[cssd(25593)]CRS-1605:CSSD voting file is online: /dev/mapper/Vote01; details in /data/11.2.0/grid/log/db-41/cssd/ocssd.log.
2012-04-25 14:46:52.173
[cssd(25593)]CRS-1601:CSSD Reconfiguration complete. Active nodes are db-41 .
2012-04-25 14:46:54.177
[ctssd(25738)]CRS-2407:The new Cluster Time Synchronization Service reference node is host db-41.
2012-04-25 14:46:54.177
[ctssd(25738)]CRS-2401:The Cluster Time Synchronization Service started on host db-41.
[client(25843)]CRS-10001:25-Apr-12 14:46 ACFS-9391: Checking for existing ADVM/ACFS installation.
[client(25848)]CRS-10001:25-Apr-12 14:46 ACFS-9392: Validating ADVM/ACFS installation files for operating system.
[client(25850)]CRS-10001:25-Apr-12 14:46 ACFS-9393: Verifying ASM Administrator setup.
[client(25853)]CRS-10001:25-Apr-12 14:46 ACFS-9308: Loading installed ADVM/ACFS drivers.
[client(25856)]CRS-10001:25-Apr-12 14:46 ACFS-9154: Loading 'oracleoks.ko' driver.
[client(25913)]CRS-10001:25-Apr-12 14:46 ACFS-9154: Loading 'oracleadvm.ko' driver.
[client(25974)]CRS-10001:25-Apr-12 14:46 ACFS-9154: Loading 'oracleacfs.ko' driver.
[client(26099)]CRS-10001:25-Apr-12 14:46 ACFS-9327: Verifying ADVM/ACFS devices.
[client(26103)]CRS-10001:25-Apr-12 14:46 ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
[client(26107)]CRS-10001:25-Apr-12 14:46 ACFS-9156: Detecting control device '/dev/ofsctl'.
[client(26113)]CRS-10001:25-Apr-12 14:46 ACFS-9322: completed
2012-04-25 14:47:06.975
[ohasd(25286)]CRS-2807:Resource 'ora.asm' failed to start automatically.

[ctssd(25738)]CRS-2405:The Cluster Time Synchronization Service on host db-41 is shutdown by user
2012-04-25 14:51:25.852
[/data/11.2.0/grid/bin/orarootagent.bin(25523)]CRS-5016:Process "/data/11.2.0/grid/bin/acfsload" spawned by agent "/data/11.2.0/grid/bin/orarootagent.bin" for action "check" failed: details at "(:CLSN00010:)" in "/data/11.2.0/grid/log/db-41/agent/ohasd/orarootagent_root/orarootagent_root.log"
2012-04-25 14:51:35.716
[cssd(25593)]CRS-1603:CSSD on node db-41 shutdown by user.
2012-04-25 14:51:35.725
[ohasd(25286)]CRS-2767:Resource state recovery not attempted for 'ora.cssdmonitor' as its target state is OFFLINE
2012-04-25 14:51:36.224
[cssd(25593)]CRS-1660:The CSS daemon shutdown has completed
2012-04-25 14:51:38.144
[gpnpd(25510)]CRS-2329:GPNPD on node db-41 shutdown.

Introduction

The concept of the Preferred Read is not a new idea, but is now implemented in Oracle’s ASM volume 
management in Oracle 11g.The concept is to read from the storage that can present the needed data at a lower
latency.  Initially, this was designed for WAN or site-specific storage in order to avoid higher-latency site
connections. By restricting data reads to the local storage, the application would be able to service requests at
nominal read speeds while writes were the only communication needed to traverse the long haul site link. This is
a feature that is available to most Operating Systems with their included volume manager and as a feature to
Symantec/Veritas through the title Preferred Plex. This paper will discuss the merits of using PRG technology with Oracle ASM

eg:


SELECT name, failgroup FROM v$asm_disk;

NAME                           FAILGROUP
------------------------------ ------------------------------
DATA_0000                      DATA_0000
DATA_0001                      DATA_0001
DATA_0002                      DATA_0002

3 rows selected.

SQL> ALTER SYSTEM SET ASM_PREFERRED_READ_FAILURE_GROUPS = 'data.data_0000', 'data.data_0001', 'data.data_0002';

Allocating ASM Extent Sets
With ASM redundancy, the first file extent allocated is chosen as primary extent, and
the mirrored extent is called the secondary extent. In the case of high redundancy,
there will two secondary extents. This logical grouping of primary and secondary
extents is called an extent set. When a block is read from disk, it is always read from
the primary extent, unless the primary extent cannot be read. In Oracle Database
11g, the preferred read feature allows the database to read the secondary extent first
instead of reading the primary extent. This is especially important for RAC Extended
Cluster implementations.

As stated earlier, ASM in Oracle Database 10g always reads the primary copy of a mirrored extent set. Thus, a read for a specific block may require a read of the primary extent at the remote site across the interconnect. Accessing a remote disk through a metropolitan area or wide area storage network is substantially slower than accessing a local disk. This can tax the interconnect as well as result in high I/ O and network latency. To assuage this, Oracle Database 11g provides a feature called preferred reads. This feature enables ASM administrators to specify a failure group for local reads–– that is, provide preferred reads. In a normal or high-redundancy diskgroup, when a secondary extent is on a preferred disk and the primary extent is remote, the secondary extent is read rather than the primary one on that node. This feature is especially beneficial for extended cluster configurations.

Execution Environment:
    

Access Privileges:
     Requires to be run connected as SYS schema

Usage:
$ sqlplus
SQL*Plus: Release 9.2.0.2.0 - Production on Mon Nov 11 12:00:06 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production 
SQL> set serveroutput on size unlimited
SQL> spool outputfile
SQL> execute hcheck.full

Instructions:

1. Connect as SYS schema in sqlplus
2. Create package hOut as described in Note:101468.1
3. Create package hcheck in SYS schema (Refer the attachments under SCRIPT to Create package hcheck, this is hcheck2.sql and/or hcheck3.sql 
4. spool outputfile
5. execute hcheck.full

6. Output will go to the spool file and the session trace file.
   The script will report various dictionary related issues
   which may or may not be a problem - Any problems reported
   should be reviewed by an experienced support analyst as some
   reported "problems" may be normal and expected.

PROOFREAD THE SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.

Description
To provide a single package which looks for common data dictionary
problems.
The script can be used with Oracle versions 9i onwards (release 2.00 includes 8.1)
It checks consistency of certain dictionary relationships and looks for certain
known issues - certain reported "problems" will be normal and expected.

This script is for use mainly under the guidance of Oracle Support.