ORACLE - RMAN-06019 :Tablespace Restore issue

Task : Recover dropped Tablesapce or Restore New Tablespace at another database

> rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 19 10:56:40 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DBNAME (DBID=1948148797)

RMAN> run
{
allocate channel c1 type disk format '/bkup/dbname/rman/rman_%d_%T_%u.bk';
allocate channel c2 type disk format '/bkup/dbname/rman/rman_%d_%T_%u.bk';
restore tablespace TABLESPACE_NAME;
recover tablespace TABLESPACE_NAME;
release channel c1;
release channel c2;
}

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=676 device type=DISK
allocated channel: c2
channel c2: SID=1060 device type=DISK
Starting restore at 19-MAR-13
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/19/2013 10:57:05
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "TABLESPACE_NAME"
RMAN>

Solution:
 Control file does not has record for dropped Tablspace or New Tablespace henace RMAN-06019.
[RMAN-06019: could not translate tablespace name "TABLESPACE_NAME"]
1)Create a clone database using backup and expdp the tables or tablespace to the existing database.
2)Do a Point In Time Recovery (TSPITR) of the whole database until the time the tablespace was dropped.

Oracle - Create Password File

orapwd file=orapwDBNAME entries=100 ignorecase=y password=PASSWORD
alter user sys identified by PASSWORD;
alter user system identified by PASSWORD;

ORACLE - Partitions

Oracle Partitions Overview:

INTERVAL Partition:

CREATE TABLE Schema.TableName
(
  TRANSACTIONTIME       TIMESTAMP(6),
  Col2                  CHAR(1 CHAR),
  col3                  VARCHAR2(50)
)
PARTITION BY RANGE (TRANSACTIONTIME)
INTERVAL (NUMTODSINTERVAL(15,'DAY'))
(partition part_data_0 values less than (TO_DATE('01-JAN-2011','DD-MON-YYYY'))
) TABLESPACE DATA;

Note:
1.Only one partitioning key column, and it must be of NUMBER or DATE type.
2.The optional STORE IN clause lets you specify one or more table-spaces into which the database stores interval partition data using a round-robin algorithm

List Partition:

CREATE TABLE Hotel_Region
      (ID number,
       quarterly_sales number(10, 2),
       state varchar2(2))
PARTITION BY LIST (state)
      (PARTITION p1 VALUES ('CA', 'WA'),
       PARTITION p2 VALUES ('AZ', 'NJ', 'NY'),
       PARTITION p3 VALUES  ('FL', 'SD', 'WI'),
       PARTITION p4 VALUES ('TX', 'GA'));

Local Index:
CREATE UNIQUE INDEX INDEX_NAME ON TableName (Col_Name)
TABLESPACE TBS01 LOCAL PARALLEL NOLOGGING;

Global Index Non-Partion Index:
CREATE INDEX GINDX ON Hotel_Region (ID);

Global Index Partion Index:CREATE INDEX GINDX ON Hotel_Region (ID)
PARTITION BY LIST (ID)
(PARTITION p1 VALUES ('CA', 'WA'),
PARTITION p2 VALUES ('AZ', 'NJ', 'NY'));

View Partiton and Indexes;
Select index_name,partitioned, index_type from user_indexes where table_name=<table_name>;
Select PARTITION_NAME, num_rows from dba_tab_partitions where TABLE_OWNER = 'Schema' and TABLE_NAME IN  (  'Table_Name');

ORACLE - ASM Fast Mirror Resync

ASM FAST MIRROR RESYNC :

                             ASM fast resync keeps track of pending changes to extents on an OFFLINE disk during an outage. The extents are resynced when the disk is brought back online.By default, ASM drops a disk in 3.6 hours after it is taken offline. You can set the DISK_REPAIR_TIME attribute to delay the drop operation by specifying a time interval to repair the disk and bring it back online.

Example:
ALTER DISKGROUP DG1 OFFLINE DISK DG1_01
ALTER DISKGROUP DG01 SET ATTRIBUTE 'DISK_REPAIR_YIME' = '4.5h';
SELECT REPAIR_TIMER FROM V$ASM_DISK;
SELECT GROUP_NUMBER,NAME,VALUES FROM V$ASM_ATTRIBUTE;
SELECT GROUP_NUMBER, OPERATION, STATE FROM V$ASM_OPERATION;
ALTER DISKGROUP DG1 ONLINE DISK DG1_01;

ORACLE - EXADATA - Storage Index

Exadata Storagne Index overview:
                                  A storage index is a memory-based structure that reduces the amount of physical I/O required by the cell.

                                 Storage Indexes consist of a minimum and a maximum value for up to 8 columns. This structure is maintained for 1MB chunks of storage (storage region). Storage Indexes are stored in memory only and are never written to disk. Storage index works transparently with no user input. There is no need to create/drop or tune the storage index. Storage index is kept in memory, it disappears when the cell is re-booted. The first queries that run after a cell rebooted automatically cause the storage index to rebuild.

The storage index works best when the following conditions are true:
1. Data is roughly ordered (so that same column values are cluster together)
2.Query has a predicate on a storage index column checking for =, <, > or combination of these.

Monitoring Storage Indexes:

1) Use v$sesstat, v$sysstat  and v$sql:
select name, values from v$mystat a, v$statname b
where b.statistic# = s.statistic# and name like '%storage%';

NAME                                                                                                VALUE
---------------------------------------------------------------------- --------------
cell physical IO bytes saved by storage index                                       0
Note : (after Query run)                                                                       5095009234

Cell physical IO bytes saved by storage index : Shows how many bytes of I/O were eliminated by the application of storage indexes at the storage cell level.

Cell physical IO interconnect bytes returned by smart scan : Shows how many bytes of I/O were returned by a smart scan to the database server.

2. Tracing:
Use _CELL_STROAGE_INDEX_DIAG_MODE=TRUE in cellinit.ora file (restart the cellsrv)

Use full comparison operator:
Storage Index can evaluate predicates like =, <, >, <=, >=, BETWEEN, IS NULL, IS NOT NULL

Limitation:
Storage Index DO NOT work with: !=, % (wildcards) and CLOBs

Ref. Oracle Docs./Metalink

ORACLE - EXADATA - Cellcli and dcli

Cellcli and dcli Overview: 
                               EXADATA storage software uses the CELLCLI utility as its command line interface. It is to a cell what SQL * PLUS is to a database. CELLCLI use for a storage cell and DCLI for all cells i.e. (multiple cells in parallel threads)

How to login to a cell ?
1. Using a ssh clinet (putty) open up a session and go to a assign IP address:
ssh  <cell.ip.address> -l cellmonitor
or
cellcli -e list celldisk

2.Cellcli> list celldisk
3.CellCLI>help
4.CellCLI>list cell detail
5.CellCLI>list lun
6.CellCLI>list phycicaldisk
7.CellCLI>list griddisk detail

How to monitor the cell mertics ?

1. describe metriccurrent
2. describe metrichistory

CellCLI> list metriccurrent attribute name, metricValue where name =  
CellCLI> list metrichistory attribute  where name =

How to display ALERTDEFINATION ?

CellCLI> list alertdefination
CellCLI> list alertdefinationHardwareAlert detail
CellCLI> list alertdefinationattributes name, metricName, description

DCLI : User celladmin or root (not permitted as cellmonitor user):
dcli -h
dcli -g mycells -l celladmin date
dcli -g mycells -l celladmin - cellcli -e list celldisk
dcli -g mycells -l celladmin --vmstat="-a 5 5" 

Startup/Shutdown the cell services:
CellCLI>alter cell startup service all
CellCLI>alter cell shutdown service all


Example:
list physicaldisk detail
name:                                  20:0
deviceId:                             31
diskType:                            HardDisk
enclosureDeviceId:              20
errMediaCount:                   0
errOtherCount:                    0
foreignState:                         false
luns:                                    0_0
makeModel:                       "SEAGATE ST32000SSSUN2.0T"
physicalFirmware:               061A
physicalInsertTime:             2011-06-16T08:07:59-04:00
physicalInterface:                sas
physicalSerial:                     L5LK38
physicalSize:                       1862.6559999994934G
slotNumber:                        0
status:                                 normal

name:                                 FLASH_1_0
diskType:                           FlashDisk
errCmdTimeoutCount:       0
errHardReadCount:           0
errHardWriteCount:          0
errMediaCount:                 0
errOtherCount:                  0
errSeekCount:                   0
luns:                                  1_0
makeModel:                      "Sun Flash Accelerator F20 PCIe Card"
physicalFirmware:             D21Y
physicalInsertTime:           2011-06-16T08:08:57-04:00
physicalSerial:                  1101M062HU
physicalSize:                    22.8880615234375G
sectorRemapCount:         0
slotNumber:                     "PCI Slot: 1; FDOM: 0"
status:                              normal

list celldisk detail
name:                             CD_00_<cell_node>
comment:
creationTime:                 2011-06-30T15:49:02-04:00
deviceName:                 /dev/sda
devicePartition:             /dev/sda3
diskType:                     HardDisk
errorCount:                  0
freeSpace:                   0
id:                                3aa2ddcc-0576-47e8-b1e5-c61ca64b3811
interleaving:                  none
lun:                              0_0
physicalDisk:               L5LK38
raidLevel:                    0
size:                            1832.59375G
status:                         normal

name:                          FD_00_<cell_node>
comment:
creationTime:              2011-06-30T15:49:13-04:00
deviceName:              /dev/sdr
devicePartition:           /dev/sdr
diskType:                   FlashDisk
errorCount:                0
freeSpace:                 0
id:                              fc092196-47d4-4836-86e4-7133d77dcc83
interleaving:                none
lun:                            1_0
physicalDisk:             1101M062HU
size:                           22.875G
status:                        normal

list physicaldisk attributes name, disktype, makemodel, physicalrpm, physicalport, status
20:0                   HardDisk        "SEAGATE ST32000SSSUN2.0T"              normal
20:1                   HardDisk        "SEAGATE ST32000SSSUN2.0T"              normal
20:2                   HardDisk        "SEAGATE ST32000SSSUN2.0T"              normal
FLASH_1_0     FlashDisk       "Sun Flash Accelerator F20 PCIe Card"       normal
FLASH_1_1     FlashDisk       "Sun Flash Accelerator F20 PCIe Card"       normal

describe physicaldisk
name
ctrlFirmware
ctrlHwVersion
deviceId
diskType
enclosureDeviceId
errCmdTimeoutCount
errHardReadCount
errHardWriteCount
errMediaCount
errOtherCount
errSeekCount
errorCount
foreignState
hotPlugCount
lastFailureReason
luns
makeModel
notPresentSince
physicalFirmware
physicalInsertTime
physicalInterface
physicalPort
physicalRPM
physicalSerial
physicalSize
physicalUseType
sectorRemapCount
slotNumber
status

checking error:
list physicaldisk attributes name,disktype,errCmdTimeoutCount,errHardReadCount,errHardWriteCount
20:0                   HardDisk
20:1                   HardDisk
FLASH_1_0     FlashDisk       0       0       0
FLASH_1_1     FlashDisk       0       0       0

Filtering :
list physicaldisk attributes name, physicalInterface, physicalInsertTime where disktype = 'HardDisk'
20:0          sas     2011-06-16T08:07:59-04:00
20:1          sas     2011-06-16T08:08:05-04:00
20:2          sas     2011-06-16T08:08:11-04:00

ORACLE - Exadata INTERLEAVED DISKS

 What is Exadata INTERLEAVED DISKS ?

                            By default, space for grid disks is allocated from the outer tracks to the inner tracks of a physical disk.However, space for grid disks can be allocated in an interleaved manner.Grid disks that use this type of space allocation are referred to as interleaved gid disks.

                            Interleaved grid disks are best used in situations where you want to create separate ASM disk groups that share cell disks without performance bias.

Note: ORACLE ACS setup griddisks without interleaving.

Example:
create celldisk all harddisk interleaving='normal_redundancy'
create griddisk all prefix=DATA, size=200M
create griddisk all prefix=RECO, size=200M
list celldisk CD_00_<cell_node> detail
name:                     CD_00_<cell_node>
comment:
creationTime:         2011-06-30T15:49:02-04:00
deviceName:           /dev/sda
devicePartition:       /dev/sda3
diskType:               HardDisk
errorCount:            0
freeSpace:              0
id:                          3aa2ddcc-0576-47e8-b1e5-c61ca64b3811
interleaving:         normal_redundancylun:                        0_0
physicalDisk:         L5LK38
raidLevel:              0
size:                      1832.59375G
status:                   normal

ORACLE - EXADATA - HCC

Exadata Hybrid Columnar COmpression Architecture Overview:                    
                    Tables are organized into sets of rows called COMPRESSION UNIT. It is  is a logical structure spanning multiple database blocks.Within a compress unit, data is organized by column and then compressed.The coulmnn organized of data brings similar values close together, engancing the compression ratios.Each roe is self-contained with a compression unit.

                    Compression not only reduces the storage requirements for historical data but also reduce the size of data used by the Oracle database,with corresponding reductions in MEMEORY and an increase in I/O bandwith.

HCC copression type:
1) CREATE TABLE .....COMPRESS FOR QUERY LOW;    (Expected compression ration: 4X)
2) CREATE TABLE .....COMPRESS FOR QUERY HIGH;   (Expected compression ration: 6X)
3) CREATE TABLE .....COMPRESS FOR ARCHIVE LOW;  (Expected compression ration: 7X)
4) CREATE TABLE .....COMPRESS FOR ARCHIVE HIGH; (Expected compression ration: 12X)

Best practice to loda the data into HCC tables:
1) Direct path SQL*Loader
2) CREATE TABLE and AS SELECT statements
3) Parallel INSERT (or serial INSERT with an APPEND hint) statements

Example of compress and nocompress:
create table JTABLE (ID numeric(10))
partition by range (ID)
(partition p1 values less than (100000) nocompress,
partition p2 values less than (200000) compress for archive low,
partition p3 values less than (300000)  compress for query high,
partition p4 values less than (maxvalue) compress for query low)
enable row movement;
Alter table ORDERS modify partition P2 nocompress;
Select PARTITION_NAME,COMPRESSION, COMPRESS_FOR from DBA_tab_partitions where TABLE_NAME like 'JTABLE';

Ref. Oracle Docs/Metalink

ORACLE - DBNEWID

=======================================================================
What is the DBNEWID utility ?
DBNEWID is a database utility that can change the internal database identifier (DBID) or the database name (DBNAME) or both for a database.
=======================================================================
Example:
After restore ATESTD2 database, Rename  the database into ATESTS1:

SHUTDOWN IMMEDIATE
echo $ORACLE_SID
STARTUP MOUNTORACLE instance started.
Total System Global Area 1.8858E+10 bytes
Fixed Size                  2221336 bytes
Variable Size            1694505704 bytes
Database Buffers         1.7096E+10 bytes
Redo Buffers               64880640 bytes
Database mounted.
SQL> exit

nid TARGET=sys/pass DBNAME=ATESTS1
DBNEWID: Release 10.2.0.4.0 - Production on Wed Oct 21 12:25:29 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to database ATESTD2 (DBID=1422735007)
Connected to server version 10.2.0
Control Files in database:
    /ora02/oradata/ATESTS1/control01.ctl
    /ora03/oradata/ATESTS1/control02.ctl
    /ora04/oradata/ATESTS1/control03.ctl
Change database ID and database name ATESTD2 to ATESTS1? (Y/[N]) =>
Control File /ora02/oradata/ATESTS1/control01.ctl - dbid changed, wrote new name
Control File /ora03/oradata/ATESTS1/control02.ctl - dbid changed, wrote new name
Control File /ora04/oradata/ATESTS1/control03.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to ATESTS1.
Modify parameter file and generate a new password file before restarting.
Database ID for database ATESTS1 changed to 404185722.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

=======================================================================
After DBNEWID change:
=======================================================================
SHUTDOWN IMMEDIATE
1)create a new password file orapw
2)modify init.ora with new db name(*.DB_NAME=ATESTS1)
3)set new ORACLE HOME and SID
4)Modify Listernaer.ora
5)create new spfile

6)Startup of the database with open resetlogs
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;
========================================================================

ORACLE - IOPS and MBPS

There are two ways to measure I/O performance:  IOPS and MBPS.

1.IOPS : Small I/O operations and putting stress on the overhead associated with a single read or write. Throughput is not the main concern when you measure IOPS. This is most relevant on transactional systems.

2. MBPS : Large I/O operations and putting stress on the overall throughput. Latency is not the main concern when you measure MBPS. This is most relevant on warehouse and analytical systems.

spool IOPS.txt

set linesize 100
set head off

SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') FROM DUAL;

SELECT 'Number of Small Reads :'|| sum(decode(name,'physical read total IO requests',value,0)-decode(name,'physical read total multi block requests',value,0)),
'Number of Small Writes:'|| sum(decode(name,'physical write total IO requests',value,0)-decode(name,'physical write total multi block requests',value,0)),
'Number of Large Reads :'|| sum(decode(name,'physical read total multi block requests',value,0)),
'Number of Large Writes:'|| sum(decode(name,'physical write total multi block requests',value,0)),
'Total Bytes Read :'|| sum(decode(name,'physical read total bytes',value,0)),
'Total Bytes Written :'|| sum(decode(name,'physical write total bytes',value,0))
FROM gv$sysstat
;

spool off

ORACLE - AWR

How to  generate AWR report ?
/u01/app/oracle/product/11.2.0/db_1/rdbms/admin> ls -rlt awrr*
-rw-r--r-- 1 oracle dba 1999 Oct 24  2003 awrrpt.sql
-rw-r--r-- 1 oracle dba 7704 Jul 25  2011 awrrpti.sql

Find out Snap ID for a AWR report:
select d.dbid ,i.instance_number ,i.instance_name "Instance" ,d.name "DB name"
,snap_id "Snap Id" ,TO_CHAR(dbhs.end_interval_time,'DD/MM/YYYY HH24:MI') "Snap Started"
,snap_level "Snap Level" from v$database d , dba_hist_snapshot dbhs, v$instance i
where d.dbid = dbhs.dbid and dbhs.startup_time BETWEEN SYSDATE -3 AND SYSDATE ORDER BY 5;

AWR report for a snap ID:
select output FROM TABLE(dbms_workload_repository.awr_report_text(4211587007,1,677,690));
select output FROM TABLE(dbms_workload_repository.awr_report_html(4211587007,1,677,690));

AWR report for a date range:
select output FROM TABLE(dbms_workload_repository.ash_report_html(4211587007,1,SYSDATE-30/1440, SYSDATE-1/1440));
select output FROM TABLE(dbms_workload_repository.ash_report_text(4211587007,1,SYSDATE-30/1440, SYSDATE-1/1440));

AWR report for a SQL ID:
SELECT * FROM TABLE(dbms_workload_repository.awr_sql_report_html(4211587007, 1, 677, 690, 'fr0xt5q6dr3zc'));

ORACLE - RAC wait events

Oracle  RAC wait events:

select name, values from v$SYSSTAT name like 'g%';
or
View AWR for RAC wait event

Wait event grouped:
Block-oriented
        gc current block 2-way
        gc current block 3-way
        gc cr block 2-way
        gc cr block 3-way

 Message-oriented
        gc current grant 2-way
        gc cr grant 2-way

 Contention-oriented
        gc current block busy
        gc cr block busy
        gc buffer busy

 Load-oriented
        gc current block congested
        gc cr block congested

Resource Contention
       gc cr block busy
       gc current block busy
       gc buffer busy
       gc freelist
      

ORACLE - Install Grid Control 11g

================================================================
Master Note for Grid Control 11.1.0.1.0 Installation and Upgrade 

[ID 1067438.1]
How to Install Web Logic Server 10.3.2 for Installing 11g Grid Control [ID 1063762.1]
Step by step Installation of 11.1 Grid control [ID 1059516.1]
You can also refer to My Oracle Support note 1063587.1 for an example on installing JDK 1.6_18 on Linux x86_64 platform.
================================================================

Step 1:check java and Install Web logic

================================================================
Go to the OTN ( http://www.oracle.com/technetwork/index.html ) and from here go to Support tab ->Documentation->Middleware->BEA

A) Check java:

Check or Install java (optional)
labnycoradb01:/opt/oracle>/usr/bin/java -version
java version "1.6.0"
OpenJDK  Runtime Environment (build 1.6.0-b09)
OpenJDK 64-Bit Server VM (build 1.6.0-b09, mixed mode)
labnycoradb01:/opt/oracle>which java
/usr/bin/java

If not then install the java for 64 bit and version 1.6.
if require:
Download:jdk for 64bit platforms as determined by the WebLogic Server
jdk-6u18-linux-x64.bin

B)Install Web Logic:

WebLogic Server : 10.3.2 IS the ONLY supported version
jdk for 64bit platforms as determined by the WebLogic Server:

down load Genericfor Linux:wls1032_generic.zip
mv wls1032_generic.zip wls1032_generic.jar

/usr/bin/java -d64 -jar wls1032_generic.jar
GUI:
CREATE A NEW MIDDLEWARE HOME
Middleware Home Dir:/opt/oracle/Middleware
Choose Install Type: Typical
JDK selection:DEFAULT
PRODUCT INSTALLATION Dir:DEFAULT
INSTALLATION Summary:NEXT
Done (No Need to create Domin -OMS Installation going to take care of everything)

labnycoradb01:/opt/oracle/orasw/oms>which java
/usr/bin/java

================================================================

Step 2:Install OMS Server

================================================================
Use:Step by step Installation of 11.1 Grid control [ID 1059516.1]
OR
Go to Otn.orace =>documents =>database =>Enterprise Manager =>Installation

SELECT COUNT(*) FROM ALL_USERS WHERE USERNAME='SYSMAN';
  COUNT(*)
----------
1

If the result of this query is 1, then the database has this schema, so deconfigure it:
/opt/oracle/product/11.2/bin/emca -deconfig dbcontrol db -repos drop -SYS_PWD **** -SYSMAN_PWD ****

Check the count, if 1 drop the schema:
SQL>SELECT COUNT(*) FROM ALL_USERS WHERE USERNAME IN ('SYSMAN','SYSMAN_MDS');
SQL>SELECT FILE_NAME FROM DBA_DATA_FILES WHERE UPPER (TABLESPACE_NAME) LIKE 'MGMT%';

SQL> select value from v$option where parameter = 'Fine-grained access control';

VALUE

--------------------------------------------------------------------------------

TRUE

SELECT SUM(DECODE(autoextensible,'YES',200*1024*1024+1,bytes)) total

FROM dba_data_files f, dba_tablespaces s

WHERE s.contents = 'UNDO'

AND s.tablespace_name = f.tablespace_name;

>>>If the minimum space is less than 200 MB.

select count(*) from  dba_temp_files where tablespace_name='TEMP' and AUTOEXTENSIBLE  <> 'YES';

select count(*) from dba_data_files where tablespace_name='UNDOTBS' and AUTOEXTENSIBLE <> 'YES';

show parameter statistics_level

show parameter timed_statistics

show parameter undo_management

show parameter workarea_size_policy

show parameter job_queue_processes    >> 10 or greater

show parameter log_buffer             >> 10485760 or greater

show parameter open_cursors           >> 300 or greater

show parameter processes              >>  500 or greater

show parameter session_cached_cursors >> 200 or greater

Follow the sceen shot from OMS Installation document.

Notes:

1)Error: If agent not able to configure due to hostname use:

11GC Installation fails during Agent Configuration step [ID 1118270.1]

Error msg:Securing of the agent failed.

2)After Installation check for Grid Link:

<OMS HOME>/install/setupinfo.txt

3)If you want to change the SYSMAN password, then shut down the OMS, run the following command, and then restart the OMS.

emctl config oms -change_repos_pwd

=================================================================

Uninstall OMS

=================================================================

1)./runInstaller -deinstall

2)Go to PRODUCT INSTALLED

check oms home, agent11g, webtier and common11gr2 and uninstall.

3) Remove following directories:

devnycoradb02:/opt/oracle/Middleware>rm -rf oracle_common

devnycoradb02:/opt/oracle/Middleware>rm -rf agent11g

devnycoradb02:/opt/oracle/Middleware>rm -rf oms11g

devnycoradb02:/opt/oracle/Middleware>rm -rf Oracle_WT

devnycoradb02:/opt/oracle/Middleware>ls -rlt

=================================================================

ORACLE - Hang Analyze dump for a blocking session

1.Find the oracle pid for the session:
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID

2. Collect the dump using oradebug for blocking session :
$ sqlplus "/as sysdba"
    oradebug setorapid <ora pid> 
    oradebug unlimit
    oradebug dump errorstack 3
    <wait 1 minute>
    oradebug dump errorstack 3
    <wait 1 minute>
    oradebug dump errorstack 3
   <wait 1 minute>
    oradebug dump errorstack 3
    exit

If a blocking process is found, please also dump 3-4 errorstacks for the blocking process.

3. Generate RAC aware systemstate and hanganalyze :
Execute the following steps to take a hang analyze dump and a system state dump:
       $ sqlplus '/ as sysdba'
        oradebug setmypid
        oradebug unlimit
        oradebug setinst all
        oradebug -g all hanganalyze 4
        oradebug -g all dump systemstate 10
        oradebug tracefile_name
        quit
(ii) Wait 1-2 minutes and then repeat step 1 to take a second set of dumps.

4. Generate the truss output:
truss -o /tmp/mytruss_23150 -p 23150

5. systemstate and hanganalyze:
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3';
ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

ORACLE - Unable To Create ASM Spfile

Unable To Create ASM Spfile :

SQL> select * from V$ASM_ATTRIBUTE;
no rows selected

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> alter diskgroup MWDATA1 set ATTRIBUTE 'compatible.asm' = '11.2.0.0.0';
Diskgroup altered.

SQL> select * from V$ASM_ATTRIBUTE;
name                                     value                                      
------------------------------------------------
disk_repair_time                        3.6h     
au_size                                      1048576
sector_size                                 512    
compatible.asm                          11.2.0.0.0
compatible.rdbms                       10.1.0.0.0
cell.smart_scan_capable              FALSE  
access_control.enabled               FALSE
access_control.umask                 066
SQL> startup
ASM instance started
Total System Global Area  283930624 bytes
Fixed Size                  2227664 bytes
Variable Size             256537136 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

create spfile='+MWDATA1' from pfile='/u01/app/oracle/product/11.2.0/grid/dbs/initHMWHIST.ora';

SQL> show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +MWDATA1/asm/asmparameterfile/registry.253.806108083

Ref: Unable To Create ASM Spfile Standalone [ID 1082332.1]

ORACLE - Alter Index

How to make large index creation faster ?

1) PARALLE option:

Use number of cpu - 1 for parallel option. If server has 8 CPU then give parallel 7 and check the database perforamce : create index index_name on table_name(column_name) parallel 7;

2) NOLOGGING option: It will avoid to generate a large redo log

create index index_name on table_name(column_name) parallel 7 nologging;

3) Larger block size: Create a index in a tablespace that uses Larger block size.

create tablespace index_tbs  datafile '+jdata' size 1000M BLOCKSIZE 32K;

create index index_name on table_name(column_name) parallel 7 nologging tablespace index_tbs  ;

How to alter an index ?

Note: To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege.

Example: CREATE INDEX IndexName on TableName(ColumnName);

1) Rebuild/ Rebuild Online/Moving to new tablespace:

alter index index_name REBUILD [ONLINE];

alter index index_name REBUILD  TABLESPACE new_tablespace_name;

2) Coalesce :

alter index index_name COALESCE;

3) Alter storage parameters or physical attributes

alter index SchemaName.IndexName  STORAGE (PCTINCREASE 40);

alter index SchemaName.IndexName ENABLE PRIMARY KEY USING INDEX;

5) Make the index invisible:

alter index SchemaName.IndexName INVISIBLE;

alter index SchemaName.IndexName VISIBLE;

6) Rename the index:

alter index index_name RENAME TO new_index_name;

7) Monitoring of index usage:

alter index IndexName MONITORING USAGE;

select * from v$OBJECT_USAGE where index_name='IndexName';

alter index IndexName NOMONITORING USAGE;

Note: Parallel and nologging option already explained.

Useful View:

DBA_INDEXES

DB_IND_COLUMNS

INDEX_STATS

v$OBJECT_USAGE

ORACLE - ASM Health Check

Run following script to check the ASM health:

spool asm_health_check.html
SET MARKUP HTML ON
set echo on
set pagesize 1000
set linesize 250
set feedback off
col bytes format 999,999,999,999
col space format 999,999,999,999
col gn format 999
col name format a20
col au format 99999999
col state format a12
col type format a12
col total_mb format 999,999,999
col free_mb format 999,999,999
col od format 999
col compatibility format a12
col dn format 999
col mount_status format a12
col header_status format a12
col mode_status format a12
col mode format a12
col failgroup format a20
col label format a12
col path format a45
col path1 format a40
col path2 format a40
col path3 format a40
col bytes_read format 999,999,999,999,999
col bytes_written format 999,999,999,999,999
col cold_bytes_read format 999,999,999,999,999
col cold_bytes_written format 999,999,999,999,999
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS' ) current_time from dual;
select group_number gn, name, allocation_unit_size au, state, type, total_mb, free_mb, offline_disks od, compatibility
from v$asm_diskgroup;
select group_number gn,disk_number dn, mount_status, header_status,mode_status,state, total_mb, free_mb,name, failgroup, label, path,create_date, mount_date
from v$asm_disk order by group_number, disk_number;
break on g_n skip 1
break on failgroup skip 1
compute sum of t_mb f_mb on failgroup
compute count of failgroup on failgroup
select g.group_number g_n,g.disk_number d_n,g.name , g.path , g.total_mb t_mb,g.free_mb f_mb,g.failgroup from v$asm_disk g order by g_n, failgroup, d_n;

select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;

select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
select * from v$asm_diskgroup;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT * FROM V$ASM_CLIENT;
select * from V$ASM_ATTRIBUTE;
select * from v$asm_operation;
select * from v$version;
show parameter
show sga

spool off
exit

Oracle - Resource Manager

Create a Resource Manager to kill a User after 15 min Ideal:

Example:
dbms_resource_manager.create_consumer_group
(consumer_group => 'IDEL_TIME_15',
new_comment => 'Disconnect the session after idle time is >15 minutes ',
new_cpu_mth => 'ROUND-ROBIN');
dbms_resource_manager.create_plan_directive(
    plan => 'TEST_2NODE_PLAN',group_or_subplan => 'IDEL_TIME_15',
    comment => '',
    cpu_p1 => NULL, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
    cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => 100, cpu_p8 => NULL,
    parallel_degree_limit_p1 => 4,
    active_sess_pool_p1 => NULL,
    queueing_p1 => NULL,
    switch_group => 'CANCEL_SQL',
    switch_time => NULL,
    switch_estimate => false,
    max_est_exec_time => NULL,
    undo_pool => NULL,
    max_idle_time => 1800,
    max_idle_blocker_time => UNLIMITED,
    switch_time_in_call => NULL
);
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,'sandeep','idel_time_15');
dbms_resource_manager.set_consumer_group_mapping(dbms_resource_manager.oracle_user,'jaydeep','idel_time_15');
dbms_resource_manager_privs.grant_switch_consumer_group('anujka','idel_time_15',false);
dbms_resource_manager_privs.grant_switch_consumer_group('jaydeep','idel_time_15',false);
dbms_resource_manager.submit_pending_area();
END;
/

ORACLE - Exadata Smart Scan and Storage Index

SQL> set long 50000000
SQL> set pagesize 10000
SQL> select name, value
from v$sesstat a, v$statname b
where (a.statistic# = b.statistic#)
  and (a.sid) = userenv('sid')
  and (name in (
   'cell physical IO interconnect bytes returned by smart scan',
   'physical read IO requests',
   'physical read requests optimized',
   'cell physical IO bytes saved by storage index',
   'cell physical IO bytes eligible for predicate offload',
   'cell num smart IO sessions using passthru mode due to cellsrv',
   'cell num smart IO sessions using passthru mode due to user',
   'cell flash cache read hits',
   'db block gets',
   'db block gets from cache',
   'db block gets from cache (fastpath)',
   'db block gets direct')
)
order by name;


Reduced 'cell physical IO bytes saved by storage index' after storage cell upgrade to versions before 11.2.2.3.0 [ID 1321398.1]

ORACLE -Exadata Smart Flash Cache

Exadata Smart Flash Cache: Is a write-through cache, disk cache on exadata storage server.
It caches data for all instances that access the storage cell.

Caching: 
Control file reads/writes are cached
File header reads/writes are cached
Data Blocks and Index blocks are cached

Skip Caching:
I/Os mirror copies, backups, data pump, tablespace formatting, resistant to tables scan are skipped.

Operation OFF-LOADBLE through smart scans include following:
1) Predicate filtering :
2) Column Projection:
3) Joins:
4) Data Mining Model scoring - For users of Oracle Data Mining, scroing functions like PREDICT() cab be evaluated on storage servers.

ORACLE - Restart

What is ORACLE RESTART in oracle 11gR2 ?

Oracle Restart is the new feature oracle (11gR2) which ensures that various Oracle components gets automatically restarted after hardware or software failures.This feature depends on OHAS daemon.
1. Database instance
2. Database listener
3. Database services
4. Oracle ASM instance
5. Oracle ASM disk group
6. Oracle ONS

These commands are only for use in an  Oracle Restart environment:
crsctl start has       – To manually start the Oracle Restart stack when running disabled/stopping
crsctl stop has [-f] – To manually stop the Oracle Restart stack
crsctl enable has    – To enable the stack for automatic startup at server reboot
crsctl disable has   – To disable the stack for automatic startup at server reboot
crsctl config has     –  To display the configuration of Oracle Restart
crsctl check has     – To check the current status of Restart

crsctl stat res -t      – To check the status of the resources in the Oracle Restart stack managed by OHASD


Example: How to check oracle restart ?

1) Kill the pmon of a ATEST database:
/home/oracle> ps -ef | grep pmon
oracle        4844     1  0 Feb16 ?        00:01:50 asm_pmon_+ASM
oracle       15766  9816  0 23:22 pts/3    00:00:00 grep pmon
oracle       22524     1  0 Feb26 ?        00:00:36 ora_pmon_ATEST
/home/oracle> kill -9 22524

2) After Kill, check the ATEST database again. It is re-stared automatically:
/home/oracle> ps -ef | grep pmon
oracle        4844     1  0 Feb16 ?        00:01:50 asm_pmon_+ASM
oracle       17524     1  0 23:25 ?        00:00:00 ora_pmon_ATEST
oracle       18594  9816  0 23:26 pts/3    00:00:00 grep pmon

3) Check the alert log also:Shutting down instance (abort)
License high water mark = 24
USER (ospid: 17330): terminating the instance
Instance terminated by USER, pid = 17330
Sat Mar 02 23:25:43 2013
Instance shutdown complete
Sat Mar 02 23:25:43 2013
Adjusting the default value of parameter parallel_max_servers
from 640 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.

ORACLE - Reconfigure Oracle Restart

Issue - Error messages:
NOTE: client jdatabase01:jdatabase01 registered, osid 21900, mbr 0x1
Wed Jan 30 20:13:56 2013
2013-01-30 20:13:56.686
[USER(19635)]CRS-2317:Fatal error: cannot get local GPnP security keys (wallet).
2013-01-30 20:13:56.686
[USER(19635)]CRS-2316:Fatal error: cannot initialize GPnP, CLSGPNP_ERR (Generic GPnP error).
NOTE: failed to discover disks from gpnp profile asm diskstring
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_rbal_19635.trc:
ORA-29780: unable to connect to GPnP daemon [CLSGPNP_ERR]

Soultion:
1. Remove Oracle Restart configuration (as root)
$ sudo /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl -deconfig -forceUsing configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
CRS-4047: No Oracle Clusterware components configured.
CRS-4000: Command Stop failed, or completed with errors.
CRS-4047: No Oracle Clusterware components configured.
CRS-4000: Command Delete failed, or completed with errors.
CRS-4047: No Oracle Clusterware components configured.
CRS-4000: Command Stop failed, or completed with errors.
You must kill ohasd processes or reboot the system to properly
cleanup the processes started by Oracle clusterware
Either /etc/oracle/olr.loc does not exist or is not readable
Make sure the file exists and it has read and execute access
Successfully deconfigured Oracle Restart stack

2. Reconfigure Oracle Restart (as root):
$ sudo /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.plUsing configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'dba'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node hqmidb02 successfully pinned.
Adding Clusterware entries to upstart
hqmidb02     2013/01/30 22:19:35     /u01/app/oracle/product/11.2.0/grid/cdata/hqmidb02/backup_20130130_221935.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

3.Add ASM back to Oracle Restart configuration and  Start up ASM instance  (grid owner):
>srvctl add asm
>srvctl start asm
>crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.MWDATA1.dg ora....up.type ONLINE    ONLINE    hqmidb02
ora.asm        ora.asm.type   ONLINE    ONLINE    hqmidb02
ora.cssd       ora.cssd.type  ONLINE    ONLINE    hqmidb02
ora.diskmon    ora....on.type OFFLINE   OFFLINE
ora.evmd       ora.evm.type   ONLINE    ONLINE    hqmidb02
ora.ons        ora.ons.type   OFFLINE   OFFLINE

4.Restart HAS :
crsctl stop has
crsctl start has

5. Add components back to Oracle Restart Configuration like database, listener and other components: srvctl add database -d <db_unique_name>
srvctl add listener

Note: init+ASM.ora
+ASM.__oracle_base='/u01/app/oracle'
*.asm_power_limit=1
*.diagnostic_dest='/u01/app/oracle'
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'
*.ASM_DISKGROUPS ='DBDATA1'

Ref : How to Reconfigure Oracle Restart (Doc ID 986740.1)

ORACLE - Delete ASM datafiles

How to delete ASM files for a database ?

spool file.txt
COL gsql FORMAT a300
SET ECHO OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 600
SET TRIMSPOOL ON
spool del_asm.sql
SELECT 'ALTER DISKGROUP '||gname||' DROP FILE '''||full_path||''';' gsql FROM
(SELECT CONCAT('+'||gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path, gname FROM
(SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY adir
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
WHERE adir='N'
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex)
WHERE full_path LIKE UPPER('%&database1%');
spool off

ORACLE - RAC Interconnect

How to check RAC Interconnect ?

Use Grid control
or

1) select * from gv$configured_interconnects;

   INST_ID NAME            IP_ADDRESS       IS_ SOURCE
---------- --------------- ---------------- --- -------------------------------
         1 ce1             172.6.12.155     NO  Oracle Cluster Repository
         1 ce0             10.21.12.155     YES Oracle Cluster Repository
         2 ce1             172.6.12.160     NO  Oracle Cluster Repository
         2 ce0             10.21.12.159     YES Oracle Cluster Repository

2) select * from gV$CLUSTER_INTERCONNECTS;
    INST_ID NAME            IP_ADDRESS       IS_ SOURCE
---------- --------------- ---------------- --- -------------------------------
         2 lan902          172.17.2.163     NO  Oracle Cluster Repository
         1 lan902          172.17.2.161     NO  Oracle Cluster Repository
         3 lan902          172.17.2.165     NO  Oracle Cluster Repository

3) SELECT INST_ID, NAME_KSXPIA, IP_KSXPIA, decode (PUB_KSXPIA, 'Y', 'YES', 'N', 'NO'),
decode  (PICKED_KSXPIA, 'OSD', 'OS dependent software', 'OCR', 'Oracle Cluster Repository', 'CI', 'cluster_interconnects parameter') FROM X$KSXPIA ;
INST_ID NAME_KSXPIA     IP_KSXPIA        DECODE(PUB_KSXPIA,'Y','YES','N   DECODE(PICKED_KSXPIA,'OSD','OS
---------- --------------- ---------------- -------------------------------- --------------------------------
         2 lan902          172.17.2.163     NO                               Oracle Cluster Repository
         2 lan901          10.29.8.44         YES                             Oracle Cluster Repository

ORACLE - TAF - RAC Connect String

Here is the example for connect string :

1. Using SCAN - Oracle 11gR2 :
Service1=(DESCRIPTION =
    (LOAD_BALANCE = YES)
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster01-scan)(PORT = 1521))
     (CONNECT_DATA =  (SERVICE_NAME = ADHOC) ) )

2.Using VIPs - (Oracle 10g & 11g) :
(DESCRIPTION =
    (LOAD_BALANCE = YES)
    (FAILOVER = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdb01-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdb02-vip)(PORT = 1521))
     (CONNECT_DATA =
      (SERVICE_NAME = ADHOC)
      (FAILOVER_MODE =  (type = select)   (method = basic) ) ) )

3.Thin JDBC :
url="jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE = YES)
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster01-scan)(PORT = 1521))
     (CONNECT_DATA =  (SERVICE_NAME = ADHOC) ) )

4.Thick JDBC
url="jdbc:oracle:oci:@ADHOC"  (Add tnsnames.ora)


TAF Verifications :

select machine, failover_method, failover_type, failed_over, service_name, count(*)
from v$session  GROUP BY machine, failover_method, failover_type, failed_over, service_name

ORACLE - Linux

Linux Utility:

TOP
top : example -

top - 16:10:43 up 18 days,  4:03,  6 users,  load average: 0.00, 0.04, 0.05
Tasks: 253 total,   2 running, 251 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.2%us,  1.2%sy,  0.0%ni, 95.5%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8186944k total,  7507860k used,   679084k free,   121516k buffers
Swap:  8388600k total,   878620k used,  7509980k free,  5440128k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
27368 oracle    20   0 2635m 211m 201m R  7.6  2.7   0:08.36 oracle
30508 root      20   0 17276 1348  920 S  0.7  0.0   0:00.05 top

iostat :  reports CPU, disk I/O, and NFS statistics
>iostat
Linux 2.6.39-200.24.1.el6uek.x86_64 (hqmidb02)  01/29/2013      _x86_64_        (4 CPU)
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.93    0.05    0.60   12.20    0.00   85.22
Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda              31.64      2797.05      2908.67 4393977081 4569325965
sdb              63.58      3913.73      2626.79 6148215412 4126514780
dm-0             63.58      3913.73      2626.79 6148211845 4126514780

vmstat : reports virtual memory statistics
> vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0 878624 703044 122140 5426164    0    1   840   692    4    2  2  1 85 12  0


mpstat – Display cpu statistics of individual cpu/core

> mpstat -P ALL
Linux 2.6.39-200.24.1.el6uek.x86_64 (testdb02)  01/29/2013      _x86_64_        (4 CPU)
04:15:22 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest   %idle
04:15:22 PM  all    1.93    0.05    0.58   12.21    0.00    0.02    0.00    0.00   85.21
04:15:22 PM    0    2.23    0.04    0.54   15.83    0.00    0.02    0.00    0.00   81.33
04:15:22 PM    1    2.25    0.04    0.56   15.73    0.00    0.02    0.00    0.00   81.41
04:15:22 PM    2    1.59    0.07    0.60    8.59    0.00    0.02    0.00    0.00   89.14
04:15:22 PM    3    1.65    0.06    0.62    8.69    0.00    0.01    0.00    0.00   88.97

mpstat -P 0 : To display statistics information of a particular cpu

Check multipathing:
hqmidb02 temp-db-bkup $ sudo /sbin/multipath -ll
mpath1 (360a98000486e5865465a6e344e427058) dm-0 NETAPP,LUN
size=1022G features='3 queue_if_no_path pg_init_retries 50' hwhandler='0' wp=rw
`-+- policy='round-robin 0' prio=4 status=active
  `- 0:0:3:0 sdd 8:48 active ready running
mpathd (360a98000486e5865465a70446e4e6164) dm-3 NETAPP,LUN
size=200G features='3 queue_if_no_path pg_init_retries 50' hwhandler='0' wp=rw
`-+- policy='round-robin 0' prio=4 status=active
  `- 0:0:3:1 sdg 8:96 active ready running
mpathc (3600601603d410f0028ae7c5e2879dd11) dm-2 DGC,RAID 5
size=174G features='1 queue_if_no_path' hwhandler='1 emc' wp=rw
|-+- policy='round-robin 0' prio=1 status=active
| `- 0:0:4:0 sde 8:64 active ready running
`-+- policy='round-robin 0' prio=0 status=enabled
  `- 0:0:2:0 sdc 8:32 active ready running
mpathb (36006016078a1120016ce23afb7b1dd11) dm-1 DGC,RAID 5
size=695G features='1 queue_if_no_path' hwhandler='1 emc' wp=rw
|-+- policy='round-robin 0' prio=1 status=active
| `- 0:0:0:0 sda 8:0  active ready running
`-+- policy='round-robin 0' prio=0 status=enabled
  `- 0:0:1:0 sdb 8:16 active ready running

Checking netstat :
# netstat -in
IPv4:
Name      Mtu  Network         Address         Ipkts   Ierrs Opkts   Oerrs Coll
lan3      1500 172.16.1.208    172.16.1.211    11253193 0     5803471 0     0  
lan1:1    1500 10.20.188.0     10.20.188.29    458846207 0     4936    0     0  
lan1      1500 10.20.188.0     10.20.188.28    1878306228 7     2033507389 0     0  
lo0       4136 127.0.0.0       127.0.0.1       136644525 0     136644147 0     0  
lan7      1500 172.16.1.8      172.16.1.10     11217800 0     5771345 0     0  
lan5*     1500 none            none            31236   0     97824   0     0  
lan901    1500 172.16.1.0      172.16.1.2      3352355011 0     781342961 0     0  
lan4      1500 10.0.1.0        10.0.1.10       563999056 0     563158801 0     0  
lan900    1500 172.22.14.0     172.22.14.25    988494814 0     4141010767 0     0   

ORACLE - Opatch : Overview and Rollback

Types of Patches :
1. CRS Patchset
2. CRS Bundle Patch
3. One OFF patch
4. PSU (Bug fixes)

To obtain Opatch help:
opatch - help
opatch apply - help
opatch lsinventory -help
opatch rollback -help
opatch prereq -help

To check whether a patch support a rolling application:
Opatch query - is_rolling_patch  <patch_location>
Opatch query -all | grep -i Rolling

Example:
opatch query -all /oracle/dbawork/jaydeep/90000 | grep rolling
Patch is a rolling patch: true 

Apply rolling patch using opatch:
Example:
Use Oracle ReadMe for apply opatch:
1. Check Opatch version and inventory detail:
opatch version
opatch lsinventory -detail -oh <ORACLE_HOME_HOME>
opatch lsinventory -detail -oh <GRID_HOME>

2. Check rolling patch:
Opatch query - is_rolling_patch  <patch_location>
Opatch query -all | grep -i Rolling
3. Shutdown the instance on node1:

srvctl stop instance -d TESTQA -i TESTQA1    
srvctl stop asm -n stctestdb01
srvctl stop nodeapps -n stctestdb01
crsctl stop crs

or

crsctl stop crs

4. As as root to unlock protected files like
% custom/scripts/prerootpatch.sh -crshome <GRID_HOME> -crsuser oracle

5. This script will save important configuration settings like
% custom/scripts/prepatch.sh -crshome <GRID_HOME>

6. Use opatch to apply the patch on that node
<ORACLE_HOME>OPatch/opatch apply -skip_subset -skip_duplicate -local
Is the local system ready for patching?
Do you want to proceed? [y|n]
y

OPatch succeeded.

7. Start up the instance on that node
crsctl start crs

8. For all the remaining nodes, repeat steps 1-7 on each node, one node at a time

9. At this point, all of your instances should be up and the software on all nodes patched.Now pick one node on which to run the post-patch "catcpu.sql" script.You do not need to shut down any instances before doing this step.On the node you have picked, run catcpu.sql:

cd $ORACLE_HOME/cpu/CPUMONYYYY
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catcpu.sql
SQL> QUIT

10. Check the log file for errors.

11. Run this statement to check for "invalid objects"

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS= 'INVALID' and OWNER like 'SYS%';

If any are found, then run the following:

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @utlrp.sql
 
Patch De-installation Instructions:
% opatch rollback -id 900000 -local -oh <GRID_HOME> or <ORACLE_HOME>