ACFS Snapshots

ACFS snapshot is a online read point-in-time copy of an Oracle ACFS file system. It can be used to recover a deteted file, revert to a prior version.  When the snapshot is created, a set of metadata including the directory structures, name of all files are created on the same volume.

+ASM1 > acfsutil registry -l
Device : /dev/asm/vol_share-356 : Mount Point : /share : Options : none : Nodes : all : Disk Group : DG_POC_ACFS : Volume : VOL_SHARE

Create ACFS snapshot image
+ASM1 > /sbin/acfsutil snap create snap_share001 /share
acfsutil snap create: Snapshot operation is complete.
/share/.ACFS/snaps/snap_share001 => is the snapshot file system

View  File System
+ASM1 > /sbin/acfsutil info fs /share
    ACFS Version: 11.2.0.2.0
    flags:        MountPoint,Available
    mount time:   Wed Oct 19 14:05:09 2011
    volumes:      1
    total size:   140660178944
    total free:   138043518976
    primary volume: /dev/asm/vol_share-356
        label:                 DBA Share FS
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 182273
        size:                  140660178944
        free:                  138043518976
        ADVM diskgroup         DG_POC_ACFS
        ADVM resize increment: 268435456
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  1
    snapshot space usage: 49152

Resize ACFS
+ASM1 > acfsutil size +3g /share
acfsutil size: new file system size: 140660178944 (134144MB)

View detail information on each file system
acfsutil info fs /share
acfsutil info fs /share -s (statistics)

How to configure ACFS - ASM Cluster File System and EXT3 file system

In Oracle 11gR2 Grid Infrastructure, Oracle introduces the ASM Cluster file system, or ACFS.  It provides shared, cluster-wide access to various type files like database binaries, user data, reports, configuration files, BFILEs, trace files and alerts.

Below are all the steps to setup ACFS file system or EXT3 file system via ACFS.
1)  Pre-checking for ACFS configuration:

+ASM1 > cluvfy stage -pre acfscfg -n racapp1,racapp2-verbose

2) Create DG_POC_ACFS diskgroup

cat /apps/oracle/work/an/dg_acfs.xml to view contain

<dg name="DG_POC_ACFS" redundancy="external">

    <dsk string="ORCL:ORADATA_DD501_DISK11" />

    <dsk string="ORCL:ORADATA_DD501_DISK12" />

    <dsk string="ORCL:ORADATA_DD501_DISK13" />

    <a name="compatible.asm" value="11.2"/>

    <a name="compatible.rdbms" value="11.2"/>

</dg>

+ASM1 > asmcmd mkdg /apps/oracle/work/an/dg_acfs.xml

3) Create ASM volume

ASMCMD> volcreate -G DG_POC_ACFS -s 128g vol_share

ASMCMD> volinfo -a

Diskgroup Name: DG_POC_ACFS

         Volume Name: VOL_SHARE

         Volume Device: /dev/asm/vol_share-356

         State: ENABLED

         Size (MB): 131072

         Resize Unit (MB): 256

         Redundancy: UNPROT

         Stripe Columns: 4

         Stripe Width (K): 128

         Usage:

         Mountpath:

4) Create the ACFS file system

as root

/sbin/mkfs -t acfs -n "DBA Share FS" /dev/asm/vol_share-356

mkfs.acfs: version                   = 11.2.0.2.0

mkfs.acfs: on-disk version           = 39.0

mkfs.acfs: volume                    = /dev/asm/vol_share-356

mkfs.acfs: volume size               = 137438953472

mkfs.acfs: Format complete.

5) Add acfs registry:

/sbin/acfsutil registry -f -a /dev/asm/vol_share-356 /share

acfsutil registry: mount point /share successfully added to Oracle Registry

6) Mount /share file system

/bin/mount -t acfs /dev/asm/vol_share-356 /share

Or you can also create it as an EXT3 file system

volcreate -G DG_POC_ACFS -s 128g  vol_dba

mkfs -t ext3 /dev/asm/vol_dba-356

mount /dev/asm/vol_dba-356 /dba

7) Validate Post ACFS configuration

cluvfy stage -post acfscfg -n racapp1,racapp2-verbose

Shared Storage for Grid Infrastructure and Software

OCR and Voting can be stored in:

 Automatic Storage Manager (ASM),
Oracle Cluster File System (OCFS2),
Redhat Global File System (GFS),
Certified Network File System (NFS).

Oracle Software can be stored in all of the above storage option and ASM Cluster File System (ACFS).

V$ASM_ATTRIBUTE and COMPATIBLE attributes

If the diskgroup attribute COMPATIBLE.ASM is set to 11.1 or higher, you can see all attributes by querying v$ASM_ATTRIBUTE and v$ASM_DISKGROUP.  Also you can run lsattr via ASMCMD to some the same information for the diskgroup like below..

+ASM3 - oracle: asmcmd lsattr -lm -G OV

Group_Name  Name                     Value       RO  Sys 
OV          access_control.enabled   FALSE       N   Y   
OV          access_control.umask     066         N   Y   
OV          au_size                  1048576     Y   Y   
OV          cell.smart_scan_capable  FALSE       N   N   
OV          compatible.asm           11.2.0.0.0  N   Y   
OV          compatible.rdbms         10.1.0.0.0  N   Y   
OV          disk_repair_time         3.6h        N   Y   
OV          sector_size              512         Y   Y 

Compatibility Attributes:  COMPATIBLE.RDBMS must be less than or equal to COMPATIBLE.ASM.  To revert to previous value, you must create a new diskgroup with the old campatiblity attributes and then restore the database files that were on the diskgroup.  If setting COMPATIBLE.RDBMS and COMPATIBLE.ASM to the same value like 10.1, it will allow the greatest flexibility regarding the ASM and databas software versions that can be use a diskgroup.  However, ihis setting limits access to the 11g ASM features like fast mirror resync, allocation unit, preferrred read failure groups, etc..

ASM Metadata Backup md_backup and Restore md_restore

Most of the time, backup ASM disk group is not necessary since you can recreate it and restore its content.  However, if you want to keep the user templates, aliases, and directories within ASM, you need to backup the metadata for the ASM objects.  The new utility in 11g is AMBR (ASM metadata backup and restore).  


Backup example:  

asmcmd md_backup /apps/oracle/bkdgroup -G DATA

asmcmd md_backup /apps/oracle/bkdgroupall -G 'DATA,DG_FRA_PF501'

Disk group metadata to be backed up: DATA

Disk group metadata to be backed up: DG_FRA_PF501

Current alias directory path: rac-scan

Current alias directory path: rac-scan/ASMPARAMETERFILE

Current alias directory path: rac-scan/OCRFILE

Current alias directory path: ORATOOLS/ARCHIVELOG/2011_10_04

Current alias directory path: ORATOOLS/CONTROLFILE

Current alias directory path: ORATOOLS/ARCHIVELOG

Current alias directory path: ORATOOLS

Current alias directory path: ORATOOLS/ONLINELOG

Current alias directory path: ORATOOLS/ARCHIVELOG/2011_10_06

with backup mode md_backup, information about ASM disks and disk groups, configurations, attributes, etc.. are stored in a text file bkdgroup or bkdgroupall.  It will be used later to restore the ASM diskgroup metadata definition.  The information gathers during ASM backup contains the ASM diskgroup name, Redundancy Type, Allocation Unit Size, diskpath, alias directories, stripe, full path of alias entries, etc..

In the restore mode md_restore, it re-create the disk group based on the backup file with all user-defined templates with the exact configuration as the backuped disk group.  There are several options when restore the disk group

full - re-create the disk group with the exact configuration

nodg - Restores metadata in an existing disk group provided as an input parameter

newdg - Change the configuration like failure group, disk group name, etc..

Restore example:

asmcmd md_restore bkdgroup --nodg -g DATA
asmcmd md_restore bkdgroupall --newdg -o 'DATA:DATA_NEW'

Troubleshooting Database Network Issue

Below are couple tips to investigate network traffic and database network connections.

netstat -ptc :  Display the process ID and tcp connections.  If a process with abnormally high in Send-Q, it means that it consumes too much network traffic.  

ping and telnet - if you can't connect the remote servers, contact your network admin.

tnsping - check the remote listener and database to make sure they're up and running.

TNS_ADMIN  - verify the tnsnames.ora file

Virtualization with Xen

Advantages:  1)  Effective resource usage 2)  Manageability 3)  Security

Concepts:

Hypervisor is the manager of the Xen enviornment.  It controls and provides acess to resource like CPU, memory, storage.  It's started by the bootloader and acts as traffic controller for all virtualized OS. It control migration, starting, stopping, and puasing the virtualized OS.  There are 2 types of virtualizations.

Paravirtualization:  It's the native mode of Xen.  Performance is the main advantage incurs no more than 5 % performnace impact v.s bare hardware

Full Virtualization:  Xen provides a complete machine simulation to run OS which does not include Xen support.  The extra operations make the OS  beliving that it runs on bare hardware add overhead.  Therefore, the performance impact is greated than paravirtualization.

Xen Domains:  Xen virtual hosts.  The first domain loaded is called Domain-0.  Domian-0 is booted immediately after the hypervisor starts and provides the user interface at the console.  It's used to install and manage other domain.  Any virtual machine other than Domain-0 is called Domain-U.  Dom-U has no visibility of the hypervisor or the physical hardware

ethtool, mii-tool

ethtool displays or changes detailed information about ethernet adapter. 

ethtool eth3
Settings for eth3:
        Supported ports: [ TP ]
        Supported link modes:   10baseT/Half 10baseT/Full
                                100baseT/Half 100baseT/Full
                                1000baseT/Full
        Supports auto-negotiation: Yes
        Advertised link modes:  10baseT/Half 10baseT/Full
                                100baseT/Half 100baseT/Full
                                1000baseT/Full
        Advertised auto-negotiation: Yes
        Speed: 1000Mb/s
        Duplex: Full
        Port: Twisted Pair
        PHYAD: 1
        Transceiver: internal
        Auto-negotiation: on
        Supports Wake-on: g
        Wake-on: g
        Link detected: yes
       
mii-tool is another useful tool for quickly determining the state of an ethernet adapter
root > mii-tool -v eth3
eth3: negotiated, link ok
  product info: vendor 00:50:ef, model 60 rev 8
  basic mode:   autonegotiation enabled
  basic status: autonegotiation complete, link ok
  capabilities: 100baseTx-FD 100baseTx-HD 10baseT-FD 10baseT-HD
  advertising:  100baseTx-FD 100baseTx-HD 10baseT-FD 10baseT-HD flow-control

oracleasm - Prepare the Partitions for ASM via ASMLIB

rpm -ivh oracleasm* to install 3 ASMLIB RPM packages

In the first node of the cluster:
/etc/init.d/oracleasm configure to configure the driver
/etc/init.d/oracleasm createdisk ORADATA_DISK01 /dev/emcpowera1 to mark the disk as ASM disk
/etc/init.d/oracleasm querydisk  /dev/emcpowera1 to show the mapping between ASM disks and the device


From the remaining nodes:
/etc/init.d/oracleasm scandisks to detect the configured disks
/etc/sysconfig/oracleasm is modified to match and exclude the disk naming partterns

# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER="emcpower"
# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE="sd"

Some Note about Configuring SAN Disks

1) To check HBA driver version and supported

cat /etc/modprobe.conf
alias scsi_hostadapter1 lpfc
alias scsi_hostadapter2 lpfc
modinfo lpfc | more
filename:       /lib/modules/2.6.18-194.el5/kernel/drivers/scsi/lpfc/lpfc.ko
version:        0:8.2.0.63.3p
author:         Emulex Corporation - tech.support@emulex.com
description:    Emulex LightPulse Fibre Channel SCSI driver 8.2.0.63.3p
license:        GPL
srcversion:     CABE989E4BB61146A4D4D5D

2) Check LUNs -cat /proc/scsi/scsi | more

3) Partitioning Disks: Should be run on one node only when actually partiioning the disks.  Then on the other nodes, you can run partprobe to update the partition table or you can reboot the servers to update the partition table changes.

If you prepare the partitions for ASM with udev, you can define rules and in the configuration file /etc/udev/udev.conf../etc/udev/rules.d
-rw-r--r-- 1 root root   255 Jun 15  2010 89-raw_permissions.rules
-rw-r--r-- 1 root root   600 Jun 15  2010 60-raw.rules
[root@lltcind01 rules.d]# more 89-raw_permissions.rules
#OCR
KERNEL=="raw1",OWNER="root", GROUP="oinstall", MODE="640"
#Votingdisk
KERNEL=="raw2",OWNER="oracle", GROUP="oinstall", MODE="640"
KERNEL=="raw3",OWNER="oracle", GROUP="oinstall", MODE="640"
KERNEL=="raw4",OWNER="oracle", GROUP="oinstall", MODE="640"
[root@lltcind01 rules.d]# more 60-raw.rules
# Enter raw device bindings here.
#
# An example would be:
#   ACTION=="add", KERNEL=="sda", RUN+="/bin/raw /dev/raw/raw1 %N"
# to bind /dev/raw/raw1 to /dev/sda, or
#   ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="1", RUN+="/bin/raw /dev/raw/raw2 %M %m"
# to bind /dev/raw/raw2 to the device with major 8, minor 1.
ACTION=="add", KERNEL=="emcpoweras1", RUN+="/bin/raw /dev/raw/raw1 %N"
ACTION=="add", KERNEL=="emcpowerat1", RUN+="/bin/raw /dev/raw/raw2 %N"
Then you can reoload the udev rules and restart udev via "udevcontrol reload_rules, start_dev

ASM With Connected Clients

You cannot shutdown ASM instance when the OCR is stored on a diskgroup with error ORA-15097.

+ASM1 > srvctl stop diskgroup -g GI_VOTE

Although the srvctl reports the ASM OCR/vote diskgroup is stopped, but it still accessible and in quiesced state. 

SQL> select name, state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
GI_VOTE                        QUIESCING

Or completely shutdown ASM, you need to stop Grid Infrastructure, you can use "crsctl stop crs" / each node or "crsctl stop crs all" to shutdown ASM from all nodes.

In Oracle 11.2, the dependency between local ASM and database instance has been redefined

crsctl status resource ora.DATAoc.db -p

START_DEPENDENCIES=hard(ora.DATA_PD101.dg,ora.DATA_PF101.dg) pullup(ora.DATA_PD101.dg,ora.DATA_PF101.dg)
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA_PD101.dg,shutdown:ora.DATA_PF101.dg)

You should use srvctl start diskgroup -g diskgroup since ASM is now resouce within the Grid infrastructure

srvctl start diskgroup -g GI_VOTE -n lltcind01

DBMS_SQLDIAG.export_sql_testcase, DBMS_SQLDIAG.import_sql_testcase

In some situation you need to reproduce a SQL failure on a different machine for diagnosis or provide to Oracle Support.  Oracle offers the SQL Test Case Builder to reproduce a SQL error via EXPORT_SQL_TESTCASE and IMPORT_SQL_TESTCASE in the DBMS_SQLDIAG package.  Oracle creates the SQL test case as a script that contains SQL statements that will re-create the database objects with runtime information.  In addition, information will be captured are the SQL text, table data (optional), the execution plan, optimizer statistics, bind variables, user privileges, SQL profile, metadata, runtime information, etc..

EXPORT:

     

 SQL> create or replace directory mysql as '/data/oracle/bkup/test/';

  Directory created.

 SQL> grant read,write on directory mysql to anguyen_dba;

  Grant succeeded.

 SQL> grant dba to anguyen_dba;

Grant succeeded.

  1  declare mycase clob;

  2  begin

  3  dbms_sqldiag.export_sql_testcase

  4  (directory => 'MYSQL',

  5  sql_text =>'select * from rodba.edba_vmstats where rownum <=100 order by 1',

  6  user_name => 'ANGUYEN_DBA',

  7  exportData => TRUE,

  8  testcase => mycase

  9  );

10* end;

/

PL/SQL procedure successfully completed.

The export process create several files in directory MYSQL.  By default, Oracle doesn't export the data.  You can set this parameter to TRUE to get the data.  The file name oratcb1_00E2093D0001main.xml contains the metadata for the test case.

IMPORT:

SQL> create or replace directory mysql as '/data/oracle/bkup/test/';

Directory created.

SQL> grant read,write on directory mysql to anguyen_dba;

Grant succeeded.

SQL> grant dba to anguyen_dba;

Grant succeeded.

SQL> connect anguyen_dba

Enter password:

Connected.

SQL> begin

  2  dbms_sqldiag.import_sql_testcase

  3  (directory => 'MYSQL',

  4  filename =>'oratcb1_00E2093D0001main.xml',

  5  importDATA => TRUE);

  6  end;

  7  /

PL/SQL procedure successfully completed.

DBMS_SPACE.CREATE_INDEX_COST

You can use the procedure dbms_space.create_index_cost to estimate how much space will take place.  In the example below, the used_bytes is how much space is for the index data and the alloc_bytes is how much space is allocated within the tablespace.


SQL> set serverout on
SQL> exec dbms_stats.gather_table_stats('RODBA','EDBA_VMSTATS');

PL/SQL procedure successfully completed.

SQL> variable used_bytes number
SQL> variable alloc_bytes number

SQL> exec dbms_space.create_index_cost('create index RODBA.EDBA_VMSTAT_IX2 on RODBA.EDBA_VMSTATS(runqueue, blocked,cpu_user)',:used_bytes, :alloc_bytes);

PL/SQL procedure successfully completed.

SQL> print :used_bytes

USED_BYTES
----------
    276723

SQL> print :alloc_bytes

ALLOC_BYTES
-----------
     786432

What's New for Grid Infrastructure in 11.2.0.2

1.  Software Update before install or upgrade - new installer screen will allow you to download software updates, bug fixes, and Oracle patches from My Oracle Support.

2.  Automatic NIC Bonding for Private Networks:  The clusterware has the ability to automatically bond private interconnect interfaces

3.  Cluster Health Monitor:  It's now delivered with Grid Infrastructure

4.  Grid Installation Owner Group Requirements:  The grid installation owner no longer has to be a member of the group designated for ASMOPER if one has been created.

5.  Out of Place Install for Patchset Releases:  New installation and out of place so it can be patched easily prior to being applied to reduce downtime.  The Grid home at least must have 5.5 GB.

6.  Clusterware configuration Wizard

7.  Automatic CVU checks:  The CVU is managed by CRSD to report any problems if sees in the cluster setup.  You can check the frequency via "srvctl config cvu"

Creating a Disk Group via ASMCMD

Confirm that your system can discover the disks that are under ASMLib control:

+ASM1 - oracle: oracleasm-discover
Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:AN_GRID01_DISK1 [10475208 blocks (5363306496 bytes), maxio 256]
Discovered disk: ORCL:AN_GRID01_DISK2 [10475208 blocks (5363306496 bytes), maxio 256]
Discovered disk: ORCL:AN_GRID01_DISK3 [10475208 blocks (5363306496 bytes), maxio 256]
Discovered disk: ORCL:AN_DATA101_DISK1 [1073728213 blocks (549748845056 bytes), maxio 256]

ASMCMD> lsdsk --candidate
Path
ORCL:ORADATA_DD501_DISK01
ORCL:ORADATA_DD501_DISK02
ORCL:ORADATA_DD501_DISK03
ORCL:ORADATA_FD501_DISK01
ORCL:ORADATA_FD501_DISK02

Build an XML file to present to ASMCMD for creating the diskgroup.  The mkdg (make diskgroup) command in ASMCMD requires that an XML file to be used to pass the required parameters. 

Here is an example:

cat /apps/work/an/DG_DBA101.xml

<dg name="DG_DBA101" redundancy="external">
    <dsk string="ORCL:ORADATA_DD501_DISK01" />
    <dsk string="ORCL:ORADATA_DD501_DISK02" />
    <dsk string="ORCL:ORADATA_DD501_DISK03" />
    <a name="compatible.asm" value="11.2"/>
    <a name="compatible.rdbms" value="11.2"/>
</dg>

To create a diskgroup:
ASMCMD > mkdg /apps/work/an/DG_DBA101.xml

As of Oracle 11.1, the au_size = 4MB is recomended.  You need to setup partition aligment for your LUNs.

1MB partition alignment for ocr and vote disk diskgroup. 
4MB partition alignment for DATA and FRA diskgroups

For example:  To verify the partition aligment, execute the following:

sfdisk -uS -l /dev/emcpowera1Each sector is 512 bytes so you should have 8192 for 4MB and 2048 for 1MB.

To create a diskgroup with 4MB partition

<dg name="DG_DBA102" redundancy="external">
    <dsk string="ORCL:ORADATA_DD501_DISK05" />
    <dsk string="ORCL:ORADATA_DD501_DISK06" />
    <a name="compatible.asm" value="11.2"/>
    <a name="compatible.rdbms" value="11.2"/>
    <a name="au_size" value="4M" />
</dg>

OCR v.s OLR

The OCR holds the metadata and wallets for all the resounces under the CRSD statck and its agents.  OCR still hol information about local resources, but it's not required to be accesible to join the cluster.  It includes information about the state of resouces, how to stop/start resources.  At least one OCR must exist, but you can have it up to five copies. In 11gR2, OCR can be stored in ASM.  The OCR location can be found in /etc/oracle/ocr.loc

+ASM1 - oracle: ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3528
         Available space (kbytes) :     258592
         ID                       : 1710014838
         Device/File Name         :        +OV
                                    Device/File integrity check succeeded
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
         Cluster registry integrity check succeeded
         Logical corruption check bypassed due to non-privileged user

From the output above, there are 5 slots, but one is used. 

The OLR holds metadata for the local nodeand GPnP profile.  It's managed by the OHASD and also manages the low-level processes required to allow a node to join a cluster.  None of the OLR data is shared.

 - root: ocrcheck -local
Status of Oracle Local Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2572
         Available space (kbytes) :     259548
         ID                       :  272464046
         Device/File Name         : /apps/grid/11.2.0/grid/cdata/dbplno155.olr
                                    Device/File integrity check succeeded
         Local registry integrity check succeeded
         Logical corruption check succeeded

 or you can view it in /etc/oracle/olr.loc

Detect Database Corruption

**Check logical clause means that RMAN will check for logical corruption only.  If RMAN finds one or more data blocks are corrupted, it generates a trace and also listed in the v$database_block_corrupted. 

RMAN> run {

allocate channel d1 type disk;

allocate channel d4 type disk;

backup validate check logical database;

}

**The corrupted blocks are listed in the view v$database_block_corruption:

SQL> select * from v$database_block_corruption;

**The segment names are listed in the query below:

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#

, greatest(e.block_id, c.block#) corr_start_block#

, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#

, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)

- greatest(e.block_id, c.block#) + 1 blocks_corrupted

, null description

FROM dba_extents e, v$database_block_corruption c

WHERE e.file_id = c.file#

AND e.block_id <= c.block# + c.blocks - 1

AND e.block_id + e.blocks - 1 >= c.block#

UNION

SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#

, header_block corr_start_block#

, header_block corr_end_block#

, 1 blocks_corrupted

, 'Segment Header' description

FROM dba_segments s, v$database_block_corruption c

WHERE s.header_file = c.file#

AND s.header_block between c.block# and c.block# + c.blocks - 1

UNION

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#

, greatest(f.block_id, c.block#) corr_start_block#

, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#

, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)

- greatest(f.block_id, c.block#) + 1 blocks_corrupted

, 'Free Block' description

FROM dba_free_space f, v$database_block_corruption c

WHERE f.file_id = c.file#

AND f.block_id <= c.block# + c.blocks - 1

AND f.block_id + f.blocks - 1 >= c.block#

order by file#, corr_start_block#;

* Check the corruption with the table and its index(s) to perform the cross reference

ANALYZE TABLE <OWNER.TABLE_NAME> VALIDATE STRUCTURE CASCADE;

* Validate all the datafiles in the database.

RMAN> backup validate database;

*  Run expdp/ export to detect any table level issues with all of the databases in a specific schema. 

*  DBVFY to check physical corruption

Bug 12876128: ORA-600 KCLCHKBLK_4/2662 ERRORS IN THE LOG AFTER DOING A FLASHBACK.

Currently Oracle Development is working on the patch fix.  This issue occurs on 11.2.0.2 Linux when multiple restore points created while the database is opened.

DIAGNOSTIC ANALYSIS
  ===================
  Errors ORA-600 [kclchkblk_4] and ORA-600 [2662] After Recovery of
  Database (Doc ID 275902.1)
 
  BUG: 3517013  10.1.0.2 RDBMS 10.1.0.2 BUFFER CACHE PRODID-5 PORTID-23
  ORA-600
  Abstract: GSIST: OPEN DB RESETLOG AFTER FLASHBACK DB FAILS ORA-600
  [KCLCHKBLK_4], [1904]

Initially, Oracle Support suggested us to drop and re-create the temp since the symptom seems to be similar to the below description, but it doesn't work for us.  Oracle Developer is working on the fix patch..


Internal Problem Description:
  Flashback will move back the recent scn, but it will not touch temporary
  files, so it is possible to read a temporary file, and find a block
  scn which is greater than the recent scn.

ipcs, pmap, lsof

There are several useful Linux performance monitoring commands like uptime, last, ps, top, pgrep -flu oracle, free, etc. the ipcs, pmap, lsof I use them often to get the detail of memory utilization.

The ipcs command with the -m argument can be used to display the configured shared memory segments on the system.
ORATOOLS1 - oracle: ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status     
0x00000000 3571714    root      644        80         2                      
0x00000000 3604484    root      644        16384      2                      
0x00000000 3637253    root      644        280        2                      
0xf14e8d68 4096007    oracle    660        4096       0                      
0x148fbb84 4227085    oracle    660        4096       0     
The command ipcmr with the -M argument can be used to manually delete shared memory segments by a user.  However, you should use pmap and lsof beforehand to identify the processes using the shared memory segment
The pmap Command - details the memory mapped by that particial process including the total memory utilization. 
pmap -x
ORATOOLS1 - oracle: pmap -x 44234423:   ora_pmon_ORATOOLS1
Address           Kbytes     RSS   Dirty Mode   Mapping
0000000000400000  180232   12692       0 r-x--  oracle
000000000b602000    1820     280      72 rwx--  oracle
000000000b7c9000     300     204     196 rwx--    [ anon ]
000000000f707000     576     512     512 rwx--    [ anon ]
0000000060000000       4       0       0 r-xs-  ora_ORATOOLS1_4227085_0
0000000060001000    4092     356     164 rwxs-  ora_ORATOOLS1_4227085_0
0000000060400000    4096       0       0 rwxs-  ora_ORATOOLS1_4227085_1
0000000078800000    4096      28      20 rwxs-  ora_ORATOOLS1_4227085_98
0000000079800000    4096      16       8 rwxs-  ora_ORATOOLS1_4227085_102
0000000079c00000    4096      16      16 rwxs-  ora_ORATOOLS1_4227085_103
000000007a000000    4096     384     136 rwxs-  ora_ORATOOLS1_4227085_104
000000007f000000    4096      80      36 rwxs-  ora_ORATOOLS1_4227085_124
000000007f400000    4096    1836      48 rwxs-  ora_ORATOOLS1_4227085_125
000000007f800000    4096    1084      92 rwxs-  ora_ORATOOLS1_4227085_126
000000007fc00000    4096      76      28 rwxs-  ora_ORATOOLS1_4227085_127
0000000080000000    4096       4       4 rwxs-  ora_ORATOOLS1_4227085_128
00000035b1600000     112     104       0 r-x--  ld-2.5.so
00000035b5600000      84      24       0 r-x--  libnsl-2.5.so
00000035b5615000    2044       0       0 -----  libnsl-2.5.so
00000035b5814000       4       4       4 r-x--  libnsl-2.5.so
00000035b5815000       4       4       4 rwx--  libnsl-2.5.so
00000035b5816000       8       0       0 rwx--    [ anon ]
00002b01b37e1000       8       8       8 rwx--    [ anon ]
00002b01b37e3000       4       4       0 r-x--  libodmd11.so
00002b01b37e4000    1024       0       0 -----  libodmd11.so
00002b01b58f0000      64      64      24 rwx--  zero
00002b01b5900000     144     144       0 rwx--  zero
00002b01b5924000       8       8       8 rwx--    [ anon ]
00002b01b5926000       4       4       0 rwxs-  hc_ORATOOLS1.dat
00002b01b5927000      40      16       0 r-x--  libnque11.so
00002b01b5931000    1020       0       0 -----  libnque11.so
00002b01b5a30000       4       4       4 rwx--  libnque11.so
00002b01b5a31000    1048      24      24 rwx--    [ anon ]
00007fff35e2b000      84      52      52 rwx--    [ stack ]
ffffffffff600000    8192       0       0 -----    [ anon ]
----------------  ------  ------  ------
total kB          770108   22116    3248

The lsof command:  is an extensive command that lists the open files on the system.  It can be used for diagnosing connectivity to a number of resource.  It provides on the usage of standard files, shared memory segments,
and network ports.
 lsof -u oracle | grep 4006
ORATOOLS1 - oracle: lsof -u oracle | grep 4006oracle     4006 oracle  cwd       DIR               8,17      4096               950323 /apps/grid/11.2.0/grid/dbs
oracle     4006 oracle  rtd       DIR              253,0      4096                    2 /
oracle     4006 oracle  txt       REG               8,17 200678430               952608 /apps/grid/11.2.0/grid/bin/oracle
oracle     4006 oracle  mem       REG               0,20   4194304                98378 /dev/shm/ora_+ASM1_4096007_21
oracle     4006 oracle  mem       REG               0,20   4194304                98379 /dev/shm/ora_+ASM1_4096007_22
oracle     4006 oracle  mem       REG               0,20   4194304                98416 /dev/shm/ora_+ASM1_4096007_59
...
oracle     4006 oracle  mem       REG              253,0    139416               786005 /lib64/ld-2.5.so
oracle     4006 oracle  mem       REG              253,0   1717800               786006 /lib64/libc-2.5.so
oracle     4006 oracle  mem       REG              253,0    615136               786018 /lib64/libm-2.5.so
oracle     4006 oracle  mem       REG              253,0     23360               786008 /lib64/libdl-2.5.so
oracle     4006 oracle  mem       REG              253,0    145824               786009 /lib64/libpthread-2.5.so
oracle     4006 oracle  mem       REG              253,0     53448               786010 /lib64/librt-2.5.so
oracle     4006 oracle  mem       REG              253,0    114352               786023 /lib64/libnsl-2.5.so
oracle     4006 oracle  mem       REG               8,17     12315               958409 /apps/grid/11.2.0/grid/lib/libodmd11.so
oracle     4006 oracle  mem       REG               8,17    532417               958508 /apps/grid/11.2.0/grid/lib/libcell11.so
oracle     4006 oracle  mem       REG               8,17    993144               958469 /apps/grid/11.2.0/grid/lib/libskgxp11.so
oracle     4006 oracle  mem       REG               8,17   7898628               958504 /apps/grid/11.2.0/grid/lib/libnnz11.so
oracle     4006 oracle   10r      CHR                1,3                           1449 /dev/null
oracle     4006 oracle   11r      CHR                1,3                           1449 /dev/null
oracle     4006 oracle   12w      REG               8,17    284821              1261703 /apps/grid/11.2.0/grid/log/si01/alertsi01.log
oracle     4006 oracle   13u     IPv4             103693                            UDP 169.254.101.90:59753
oracle     4006 oracle   14u      REG               8,17      1544               958679 /apps/grid/11.2.0/grid/dbs/hc_+ASM1.dat
oracle     4006 oracle   15r      DIR                0,3         0            262537225 /proc/4006/fd
oracle     4006 oracle   16u     unix 0xffff81004225da00                          84931 socket
oracle     4006 oracle   17u     unix 0xffff81004225dcc0                          84932 socket
oracle     4006 oracle   18u     unix 0xffff81004223a100                          84933 socket
oracle     4006 oracle   19u     unix 0xffff81004225cf00                          84934 socket
oracle     4006 oracle   20r      CHR                1,5                           1451 /dev/zero
oracle     4006 oracle   21u     IPv4             103694                            UDP localhost.localdomain:40395
oracle     4006 oracle   22r      CHR                1,5                           1451 /dev/zero
oracle     4006 oracle   23r      REG               8,17   1092096              1179709 /apps/grid/11.2.0/grid/rdbms/mesg/oraus.msb
oracle     4006 oracle   24u      REG               8,17      1544               958679 /apps/grid/11.2.0/grid/dbs/hc_+ASM1.dat
oracle     4006 oracle   25u     IPv4             103695                            UDP 169.254.101.90:51252
oracle     4006 oracle   26u     IPv6             103696                            UDP localhost6.localdomain6:9648
oracle     4006 oracle   28u     IPv4             328057                            TCP si01-vip.an.com:33874->si01-vip.an.com:ncube-lm (ESTABLISHED)

Changing the Broker Configuration Properties

You can modify the configuration, database, and instance using the EDIT command.  See several examples below

Changing the Broker Configuration Properties
DGMGRL> show database verbose 'DBATOOLS'
Database - DBATOOLS
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    DBATOOLS
  Properties:
    DGConnectIdentifier             = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pi01.an.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBATOOLS)(SERVER=DEDICATED)))'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'DBATOOLS'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pi01.an.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBATOOLS_DGMGRL)(INSTANCE_NAME=DBATOOLS)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS
DGMGRL> show database 'DBATOOLS' statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
      
DGMGRL> show database 'DBATOOLS' logshipping;
  LogShipping = 'OFF' 
DGMGRL> edit database 'DBATOOLS' set Property Logshipping='ON';
Property "logshipping" updated
DGMGRL> show database 'DBATOOLS' logshipping;
  LogShipping = 'ON'
DGMGRL> show database 'DBATOOLS' lOgXptMode;
  LogXptMode = 'ASYNC'
DGMGRL> edit database 'DBATOOLS' set Property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> show database 'DBATOOLS' LogXptMode
  LogXptMode = 'SYNC'

To turn transport on / off using TRANSPORT-ON/TRANSPORT-OFF
DGMGRL> edit database 'DBATOOLS' SET STATE=TRANSPORT-OFF;
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;

DGMGRL> edit database 'DBATOOLS' SET STATE=TRANSPORT-ON';
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
To turn the apply services on or off on the standby database
DGMGRL> show configuration
Configuration - DBATOOLS
  Protection Mode: MaxPerformance
  Databases:
    DBATOOLS - Primary database
    STBYTOOL - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Changing The State of A Database
DGMGRL> edit database 'DBATOOLS' set STATE=APPLY-OFF;
Error: ORA-16516: current state is invalid for the attempted operation
Failed.
DGMGRL> edit database 'STBYTOOL' set STATE=APPLY-OFF;
In the alert log, you should see the below line
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

SQL> alter database open read only;
Database altered.

DGMGRL> edit database 'STBYTOOL' set STATE=APPLY-ON
In the alert log, you should see this line
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE