ORACLE - Install ASMLib

Install ASMLib for Unbrakable Linux 6:

sudo rpm -qa | grep -i oracleoracle-logos-60.0.11-9.el6.noarch
oraclelinux-release-6Server-3.0.2.x86_64
oraclelinux-release-notes-6Server-7.x86_64

sudo rpm -ivh oracleasmlib-2.0.4-1.el5.x86_64.rpm
sudo rpm -ivh oracleasm-support-2.1.7-1.el5.x86_64.rpm
======================================================================
$ sudo rpm -ivh oracleasmlib-2.0.4-1.el5.x86_64.rpm
warning: oracleasmlib-2.0.4-1.el5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 1e5e0159: NOKEY
Preparing...                ########################################### [100%]
   1:oracleasmlib           ########################################### [100%]
hpmidb02 ~ $ sudo rpm -ivh oracleasm-support-2.1.7-1.el5.x86_64.rpm
warning: oracleasm-support-2.1.7-1.el5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 1e5e0159: NOKEY
Preparing...                ########################################### [100%]
   1:oracleasm-support      ########################################### [100%]
======================================================================

$ sudo /etc/init.d/oracleasm statusChecking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no

sudo /etc/init.d/oracleasm configure
$ sudo /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

$ sudo /etc/init.d/oracleasm statusChecking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

ORACLE - ASM devices

List ASM devices:

#!/bin/ksh
for i in `sudo /etc/init.d/oracleasm listdisks`
do
v_asmdisk=`sudo /etc/init.d/oracleasm querydisk -d $i | awk  '{print $2}'`
v_minor=`sudo /etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
v_major=`sudo /etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
v_device=`ls -la /dev | grep -i $v_minor | grep -i $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device  [$v_minor $v_major] " | grep ASM
done

Output:
ASM disk "D_JDDB_01" based on /dev/dm-9  [252 9]
ASM disk "D_JDDB_04" based on /dev/dm-12  [252 12]
ASM disk "D_JDDB_05" based on /dev/dm-13  [252 13]
ASM disk "D_JDDB_06" based on /dev/dm-14  [252 14]

ORACLE - STATS collection script

Collect the stats at schema level :
#!/bin/bash
#------------------------------------------------------------------------------
# Author:    Jaydeep
# Purpose:   Collect the stats at schema level
#------------------------------------------------------------------------------
#
# Set env.
#
export ORACLE_SID=JDQA1
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export PATH=${ORACLE_HOME}/bin:$PATH
export LOG=${LOGDIR}/stats_JDQA1log.${DATE}
# Capture the number of files opened by the EM agent.
echo "------------------------------------------------------------------------------"
echo "START"
echo `date`
echo "------------------------------------------------------------------------------"
echo "ORACLE_SID=${ORACLE_SID}"
#echo "ORACLE_HOME=${ORACLE_HOME}"
#echo "PATH=${PATH}"
echo "LOG=${LOG}"
#
# login to the database
#
sqlplus /nolog << EOF
connect / as sysdba;
set echo on
exec dbms_stats.gather_schema_stats(ownname=>'JAYDEEP', GRANULARITY=>'ALL', method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',e
stimate_percent=>30,CASCADE=>TRUE);
EOF
#
# Finish
#
echo "------------------------------------------------------------------------------"
echo "FINISH"
echo `date`
echo "------------------------------------------------------------------------------"

ORACLE - Linux

ls -al | grep "May 10" | awk '{ print $9 }' | xargs rm  ==> For remove

find /path/files* -mtime +2 -exec rm {} \; ==> delete last 2 days

sudo du -s * |sort -nbr

version:
cat /etc/*-release
cat /etc/redhat-release

-- tar the dir dba
tar -cvf dba_new.tar dba/

ln -s {/path/to/file-name} {link-name}

cat /proc/cpuinfo | grep processor | wc -l ( Check Number of Processsors)

#Check 4903 port is open or not :
netstat -an | grep 4903
tcp        0      0 0.0.0.0:4903                0.0.0.0:*                   LISTEN

#Check 4903 port is listening from another server:
hpremdb01:
nc -v 172.19.160.38 4903

grep MemTotal /proc/meminfo

rpm -q package_name

=========================================================multipath
[root@npqntdb01 ~]# multipath -ll
mpathb (360a98000486e58624e6f6e752f624c6f) dm-0 NETAPP,LUN
size=10.0G features='3 queue_if_no_path pg_init_retries 50' hwhandler='0' wp=rw
|-+- policy='round-robin 0' prio=4 status=active                                 ===> Active
| |- 4:0:1:0 sdd 8:48 active ready running
| `- 3:0:1:0 sde 8:64 active ready running
`-+- policy='round-robin 0' prio=1 status=enabled                                ===> Standby for failover
  |- 4:0:0:0 sdb 8:16 active ready running
  `- 3:0:0:0 sdc 8:32 active ready running

dmesg | grep multipath
2)hqmidb02 dev $  dmesg | grep HBA
qla2xxx [0000:00:00.0]-0005: : QLogic Fibre Channel HBA Driver: 8.04.00.03.39.0-k.
qla2xxx [0000:0c:00.0]-00fb:0: QLogic QLE2460 - PCI-Express Single Channel 4Gb Fibre Channel HBA.
3)dmesg
bnx2 0000:05:00.0: eth0: NIC Copper Link is Down
bnx2 0000:05:00.0: eth0: NIC Copper Link is Up, 100 Mbps full duplex, receive & transmit flow control ON
bnx2 0000:05:00.0: eth0: NIC Copper Link is Down
bnx2 0000:05:00.0: eth0: NIC Copper Link is Up, 100 Mbps full duplex, receive & transmit flow control ON
=================================================Check for b(block must be 0)
$ vmstat 5
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  1 847812  73724    984  17300  114   38   166    47    5    4  0  0 92  8  0
 0  0 844412  64464   1648  18144 1487    0  1769    14  372  391  1  1 52 47  0
 0  2 849668  68236   2268  17004 1329 1644  1862  1665  395  424  0  0 35 64  0
 0  2 855868  74936   2144  14980  802 1649   810  1653  332  283  0  0 32 68  0
==================================================Top High IO (%wa)
Cpu(s): 24.9%us,  0.1%sy,  0.0%ni, 74.8%id,  0.100%wa,  0.0%hi,  0.1%si,  0.0%st

            CPU      %user    %nice    %system  %iowait  %idle
08:01:19     1        0.00     0.00     0.00     100.00   0.00  >>> High IO and No CPU available for resource  
08:01:19     2        0.00     0.00     0.00     100.00   0.00
==================================================Duplex and speed
/sbin/ethtool eth0
Settings for eth0:
        Supported ports: [ TP ]
        Supported link modes:   10baseT/Half 10baseT/Full
                                100baseT/Half 100baseT/Full
                                1000baseT/Full
        Supports auto-negotiation: Yes
        Advertised link modes:  Not reported
        Advertised pause frame use: No
        Advertised auto-negotiation: No
        Speed: 100Mb/s
        Duplex: Full
        Port: Twisted Pair
        PHYAD: 1
        Transceiver: internal
        Auto-negotiation: off
        MDI-X: Unknown
Cannot get wake-on-lan settings: Operation not permitted
Cannot get link status: Operation not permitted
==========================================================iptables
# disable Port 3306 for remote access
sudo /sbin/iptables -A INPUT -p tcp --dport 3306 -j DROP
# Enable Port 3306
sudo /sbin/iptables -F
#List port
sudo /sbin/iptables -L
========================================================CPU Utilization
sar -P ALL 5 2  ==>Find CPU utilization by CPU
iostat -dx 5 | grep dm-
vmstat 3

ORACLE - OCR & Voting Disk

Check OCR location and status:

ocrcheck Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     516620
         Used space (kbytes)      :      156652
         Available space (kbytes) :     506468
         ID                       :   88678969
         Device/File Name         : +JD01
                                    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 succeeded

Another way to check ocr disk:
# cat /etc/oracle/ocr.loc
ocrconfig_loc=+JD01
local_only=false

# ocrconfig -showbackup
# ocrconfig -manualbackup
# ocrconfig -add +NEWOCRVOTE1 (new Diskgroup)
# ocrconfig -delete +OLDOCRVOTE2 (Old Diskgroup)

Check Vote disk status and location:

crsctl query css votedisk ##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   6f97g53306844f79bt2f3cf2c5576d5d (/dev/mapper/VOTE01p1) 
2. ONLINE   a69f08d9b6c54f95bt7eab415ba06510 (/dev/mapper/VOTE02p1) 
3. ONLINE   9c48gd75bcc64fe6bt0e3fe44c6d0852 (/dev/mapper/VOTE03p1) 
Located 3 voting disk(s).

# crsctl replace votedisk +NEWOCRVOTE1  (move voting disk)

ORACLE - ASM command

lsct
DB_Name   Status     Software_Version  Compatible_version  Instance_Name  Disk_Group
JDB   CONNECTED        11.2.0.3.0          11.2.0.0.0  JDB                DG_JDB_01
JDB2  CONNECTED        11.2.0.3.0          11.2.0.0.0  HMWPRD01           DG_JDB2_01

lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   1046400   930493                0          930493              0             N  DG_JDB2_01/

 lsdskPath
ORCL:D_JDB_01
ORCL:D_JDB_02
ORCL:D_JDB_03
ORCL:D_JDB_01
ORCL:D_JDB_02
ORCL:D_JDB2_01
ORCL:D_JDB2_02
ORCL:D_JDB2_03

lsdsk -t -G DG_JDB_01Create_Date  Mount_Date  Repair_Timer  Path
12-FEB-13    27-MAR-13   0             ORCL:D_JDB_01
12-FEB-13    27-MAR-13   0             ORCL:D_JDB_02
12-FEB-13    27-MAR-13   0             ORCL:D_JDB_03

-help
        md_backup, md_restore
        lsattr, setattr
        cd, cp, du, find, help, ls, lsct, lsdg, lsof, mkalias
        mkdir, pwd, rm, rmalias
        chdg, chkdg, dropdg, iostat, lsdsk, lsod, mkdg, mount
        offline, online, rebal, remap, umount
        dsget, dsset, lsop, shutdown, spbackup, spcopy, spget
        spmove, spset, startup
        chtmpl, lstmpl, mktmpl, rmtmpl
        chgrp, chmod, chown, groups, grpmod, lsgrp, lspwusr, lsusr
        mkgrp, mkusr, orapwusr, passwd, rmgrp, rmusr
        volcreate, voldelete, voldisable, volenable, volinfo
        volresize, volset, volstat

ORACLE 11gR2 - DOP and CLIBRATE_IO

In 11.2.0.2 Automatic Degree of Parallelism can only be used if  I/O statistics are gathered. When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines whether a statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics.

Check the explain plan:
If I/O calibration has not been run to gather the required statistics, the explain plan includes the following text in its notes section:  "- automatic DOP : skipped because of IO calibrate statistics are missing"

Excute dbms_resource_manager.calibrate_io:
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
--DBMS_RESOURCE_MANAGER.CALIBRATE_IO(<NUM_DISKS>, <MAX_LATENCY>,iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
/
Note: NUM DISKS - JDATA disk group is made of 1 LUN (1 physical disk)
          LATENCY - Response time (depened upon SLA) 10 sec

STATUS        CALIBRATION_TIME
-----------------------------------------
READY         23-JAN-11 09.30.17.343   [Note: Default status will be NOT AVAILABLE]

Check the explain plan:
The execution plan now shows that the feature automatic degree of parallelism can be used:
"- automatic DOP: Computed Degree of Parallelism is 2"

   0

Ref. DOC ID - 1269321.1

ORA error

ORACLE error messages:
1)
SQL> startup force
ORA-00845: MEMORY_TARGET not supported on this system

Solution: It uses /dev/shm on Linux, if max_target set over /dev/shm size.
as root
mount -o remount size=5G /dev/shm
Add into /etc/fstab:
Metalink:ORA-00845 When Starting Up An 11g Instance With AMM Configured

2) impdp - corrupt dump file
ORA-39246: cannot locate master table within provided dump files
ORA-39059: dump file set is incomplete

solution: Re-run expdp and copy to take valid dump files.

ORACLE - Database/Server Migration

Diffrent method/tools to migrate Oracle Database:

1) DataGuard Physical Standby :

2) Data Pump : 

3) Trasportable Tablespace :

4) Golden Gate :

5) Stream :

6) Trasportable Database :

7) Cross Plateform incremental backup (w/TTS)  :

8) SRDF :

9) Upgrade and Migrate database from 10gR2 to 11gR2 and from NetAPP to EMC SAN:
1) Old server (oldtest):
Storage : EMC SAN
Existing installed Oracle 10gR2
Existing ASM Diskgroup : +DGTEST1
Existsing Database: +ASM and TEST1

2) New server (newtest):
Storage : NetAPP (slow storage)
Installed the Oracle 11gR2 software
Create diskgroup : +DGTEST1 (same name)
Create the database : +ASM and TEST1 (same name)

Shutdown the database (both)
SA Team: UNMOUNT the EMC SAN and remove all existing data
SA Team: Mount the EMC SAN at NEW server (newtest)

DBA:
1) Create a new disk DISK2 (Note: At old server DISK1 exists):
sudo /etc/init.d/oracleasm createdisk DISK2 /dev/dm-01 (EMC SAN)
Marking disk "DISK2" as an ASM disk:                      [  OK  ]

2) Add EMC disk grup to newserver:
alter diskgroup +DGTEST1 add disk 'ORCL:DISK2' rebalance power 11;

3) Drop the NetAPP (slow) device from the diskgroup:
alter diskgroup +DGTEST1 drop disk DISK1:

After rebalance, newtest server is ready with EMC SAN storage.

ORACLE - UNDO files

UNDO tablespce is used for :
UNDO (ROLLBACK), READ CONSISTENCY and FLASHBACK


Calculate UNDO size:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
      v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

ORACLE - RAC - cluvfy

$ cluvfy comp nodecon -n testdrac01,testdrac02  -verbose

Verifying node connectivity

Checking node connectivity...

Checking hosts config file...
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  testdrac02   passed
  testdrac01   passed

Verification of the hosts config file successful


Interface information for node "testdrac02"
 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   10.109.19.20    10.109.19.0     0.0.0.0         10.109.19.254   00:0E:7F:F1:AA:FB 1500
 bond0  192.168.2.2     192.168.2.0     0.0.0.0         10.109.19.254   00:0E:7F:F1:AA:FA 1500


Interface information for node "testdrac01"
 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   10.109.19.17    10.109.19.0     0.0.0.0         10.109.19.254   00:11:0A:9B:17:3F 1500
 bond0  192.168.2.1     192.168.2.0     0.0.0.0         10.109.19.254   00:11:0A:9B:17:3E 1500


Check: Node connectivity of subnet "10.109.19.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  testdrac02:eth0                testdrac01:eth0                yes
Result: Node connectivity passed for subnet "10.109.19.0" with node(s) testdrac02,testdrac01


Check: TCP connectivity of subnet "10.109.19.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  testdrac01:10.109.19.17        testdrac02:10.109.19.20        passed
Result: TCP connectivity check passed for subnet "10.109.19.0"


Check: Node connectivity of subnet "192.168.2.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  testdrac02:bond0               testdrac01:bond0               yes
Result: Node connectivity passed for subnet "192.168.2.0" with node(s) testdrac02,testdrac01


Check: TCP connectivity of subnet "192.168.2.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  testdrac01:192.168.2.1         testdrac02:192.168.2.2         passed
Result: TCP connectivity check passed for subnet "192.168.2.0"


Interfaces found on subnet "10.109.19.0" that are likely candidates for VIP are:
testdrac02 eth0:10.109.19.20
testdrac01 eth0:10.109.19.17

Interfaces found on subnet "192.168.2.0" that are likely candidates for a private interconnect are:
testdrac02 bond0:192.168.2.2
testdrac01 bond0:192.168.2.1

Result: Node connectivity check passed


Verification of node connectivity was successful.

ORACLE - RAC - CACHE - NOORDER

How to avoid  Contention for the 'SQ (sequence) Enqueue'  in RAC ?

CACHE  NOORDER :  This setting has the least performance impact on RAC, and it is the default when creating a new sequence without options. Each instances caches a distinct set of numbers in the shared pool. Sequences will not be globally ordered, and bigger gaps can occur when the shared pool is refreshed (e.g. instance shut down) like on single instance databases.

ORACLE - ASM CHECK ALL DISK GROUP

How to verify all disk for a group ?

alter diskgroup MWDATA1 check all norepair;

SQL> alter diskgroup MWDATA1 check all norepair
NOTE: starting check of diskgroup MWDATA1
Thu Mar 07 12:11:42 2013
GMON checking disk 1 for group 1 at 25 for pid 20, osid 10202
Thu Mar 07 12:11:54 2013
GMON checking disk 2 for group 1 at 26 for pid 20, osid 10202
Thu Mar 07 12:11:58 2013
SUCCESS: check of diskgroup MWDATA1 found no errors
SUCCESS: alter diskgroup MWDATA1 check all norepair

ASM - Rebalance Issue 10g

Note 7436152.8 - Bug 7436152 - ASM rebalance leaves DISKGROUP imbalanced for large files
=> Fixed in 11.2
Bug 7699985 - UNBALANCED DISTRIBUTION OF FILES ACROSS DISKS
=> Fixed in 11.2
Bug 7039108 - IMBALANCE ACCUMULATOR MOVES TOO MANY EXTENTS
=> Fixed in 11.2
Note 813013.1 - FREE_MB in Same Size Disks Differ Even After Successful Rebalance

1. Please use the workaround in the above notes by setting "_asm_imbalance_tolerance"=0 followed by a manual rebalance. Please apply the bug fixes if possible.

2. Once all disks are the same percentage full, please retry the file creation.

3. If the issue persists after above workaround, please upload ASM debug query results in the following note
Note 470211.1 - How To Gather/Backup ASM Metadata In A Formatted Manner?
Note 351117.1 - Information to gather when diagnosing ASM space issues

ORACLE - Expdp estimate_only with compression

How to find expdp dump size ?

expdp oper/oper schemas=JX_2011_1 estimate_only=y

Export: Release 11.2.0.3.0 - Production on Wed Mar 20 12:28:05 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "OPER"."SYS_EXPORT_SCHEMA_01":  oper/******** schemas=JX_2011_1 estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "JX_2011_1"."M_TICKET_HIST"              32.49 GB
.  estimated "JX_2011_1"."M_EXECUTION"                11.31 GB
.  estimated "JX_2011_1"."M_BLOCK_EXECUTION"          5.703 GB
.  estimated "JX_2011_1"."M_BLOCK"                      848 MB
.  estimated "JX_2011_1"."HV_TRADE_REPORT"              816 MB
.  estimated "JX_2011_1"."M_TICKET_CHARGES_HIST"         30 MB
.  estimated "JX_2011_1"."TICKETS_MXE_JAN_2011"           2 MB
.  estimated "JX_2011_1"."M_SESSION"                     64 KB
.  estimated "JX_2011_1"."OPTION_PARTIES"                 0 KB
.  estimated "JX_2011_1"."OPTION_TRADES"                  0 KB
Total estimation using BLOCKS method: 51.17 GB
Job "OPER"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:28:17

expdp oper/oper schemas=JX_2011_1  compression=all estimate_only=y

Export: Release 11.2.0.3.0 - Production on Wed Mar 20 12:28:30 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "OPER"."SYS_EXPORT_SCHEMA_01":  oper/******** schemas=JX_2011_1 compression=all estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "JX_2011_1"."M_TICKET_HIST"              32.49 GB
.  estimated "JX_2011_1"."M_EXECUTION"                11.31 GB
.  estimated "JX_2011_1"."M_BLOCK_EXECUTION"          5.703 GB
.  estimated "JX_2011_1"."M_BLOCK"                      848 MB
.  estimated "JX_2011_1"."HV_TRADE_REPORT"              816 MB
.  estimated "JX_2011_1"."M_TICKET_CHARGES_HIST"         30 MB
.  estimated "JX_2011_1"."TICKETS_MXE_JAN_2011"           2 MB
.  estimated "JX_2011_1"."M_SESSION"                     64 KB
.  estimated "JX_2011_1"."OPTION_PARTIES"                 0 KB
.  estimated "JX_2011_1"."OPTION_TRADES"                  0 KB
Total estimation using BLOCKS method: 51.17 GB
Job "OPER"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:28:34

ORACLE - RMAN Create Manual standby database

How to create MANUAL Standby database ?

Add following parameter in init.ora:
*.standby_file_management='AUTO'
*.log_archive_dest_1='LOCATION=/ArchLog/oracle/JAYDB/arch'
*.log_archive_format='arch_%t_%s_%r.arc'
*.db_file_name_convert='+DG_JAYDB','+DG_JAYDB'
*.log_file_name_convert='+DG_JAYDB','+DG_JAYDB'

RMAN> startup nomount;
RMAN> set dbid=1392320630;
RMAN> restore standby controlfile from'/bkup/JAYDB/rman/rman_JAYDB_stbyctl_20130717_1voev8c9.bk';
RMAN> sql 'alter database mount standby database';
RMAN> sql 'create spfile from pfile';
RMAN>run
{
allocate channel c1 type disk format '/bkup/REMEDY/rman/rman_%d_inc0_%T_%u.bk';
restore database;
}

Errors and solutions:
Errors in file /u01/app/oracle/diag/rdbms/JAYDB/JAYDB/trace/JAYDB_lgwr_19545.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DG_JAYDB01/JAYDB/onlinelog/group_1.290.820901579'
ORA-17503: ksfdopn:2 Failed to open file +DG_JAYDB01/JAYDB/onlinelog/group_1.290.820901579
ORA-15012: ASM file '+DG_JAYDB01/JAYDB/onlinelog/group_1.290.820901579' does not exist
ORA-00312: online log 1 thread 1: '+DG_JAYDB01/JAYDB/onlinelog/group_1.291.820901579'
ORA-17503: ksfdopn:2 Failed to open file +DG_JAYDB01/JAYDB/onlinelog/group_1.291.820901579
ORA-15012: ASM file '+DG_JAYDB01/JAYDB/onlinelog/group_1.291.820901579' does not exist
Errors in file /u01/app/oracle/diag/rdbms/JAYDB/JAYDB/trace/JAYDB_lgwr_19545.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DG_JAYDB01/JAYDB/onlinelog/group_1.290.820901579'
ORA-17503: ksfdopn:2 Failed to open file +DG_JAYDB01/JAYDB/onlinelog/group_1.290.820901579
ORA-15012: ASM file '+DG_JAYDB01/JAYDB/onlinelog/group_1.290.820901579' does not exist
ORA-00312: online log 1 thread 1: '+DG_JAYDB01/JAYDB/onlinelog/group_1.291.820901579'
ORA-17503: ksfdopn:2 Failed to open file +DG_JAYDB01/JAYDB/onlinelog/group_1.291.820901579
ORA-15012: ASM file '+DG_JAYDB01/JAYDB/onlinelog/group_1.291.820901579' does not exist
Errors in file /u01/app/oracle/diag/rdbms/JAYDB/JAYDB/trace/JAYDB_lgwr_19545.trc:

Solution: Ref Doc ID: 352879.1
Use :log_file_name_convert

ORACLE - RMAN Re-create control file

How to re-create a control file ?

1) select name from v$controlfile;

2) Create control file:
show parameter user_dump_dest
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE  AS '/tmp/create_ctl.sql';

3) Issue create control file statment:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "REMEDY" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2920
LOGFILE
  GROUP 1 (
    '+DG1/jaydb/onlinelog/group_1.290.821033159',
    '+DG1/jaydb/onlinelog/group_1.289.821033159'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '+DG1/jaydb/onlinelog/group_2.291.821033183',
    '+DG1/jaydb/onlinelog/group_2.268.821033183'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '+DG1/jaydb/onlinelog/group_3.288.821033283',
    '+DG1/jaydb/onlinelog/group_3.286.821033285'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 4 (
    '+DG1/jaydb/onlinelog/group_4.276.821033337',
    '+DG1/jaydb/onlinelog/group_4.287.821033339'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 5 (
    '+DG1/jaydb/onlinelog/group_5.275.821033345',
    '+DG1/jaydb/onlinelog/group_5.256.821033347'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DG1/jaydb/datafile/system.262.821036305',
  '+DG1/jaydb/datafile/sysaux.263.821036305',
  '+DG1/jaydb/datafile/undotbs1.258.821036305',
  '+DG1/jaydb/datafile/users.261.821036543',
  '+DG1/jaydb/datafile/aratt_tbl.264.821036305',
  '+DG1/jaydb/datafile/aratt_idx.265.821036381',
  '+DG1/jaydb/datafile/armd_tbl.271.821036303',
  '+DG1/jaydb/datafile/armd_idx.270.821036305',
  '+DG1/jaydb/datafile/arsh_tbl.259.821036305',
  '+DG1/jaydb/datafile/arsh_idx.267.821036385',
  '+DG1/jaydb/datafile/jaydb_idx.266.821036393',
  '+DG1/jaydb/datafile/arsystem.257.821036301'
CHARACTER SET WE8MSWIN1252
;

4) Performa media recovery:
RECOVER DATABASE

5) Open the database:
ALTER DATABASE OPEN

ORACLE - orapw (password file)

How to create password file for Oracle database ?

SHUTDOWN IMMEDIATE

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw<SID> password=<password> force=y ignorecase=n entries=5

Add in the pfile or spfile follwoing parameter:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

STARTUP

SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

Note:
You must re-start the database to acces re-created password file.
The password MUST be the password for sys.

ORACLE - ORA-01111 for Standby db

Isssue:On standby database:
SQL>alter database commit to switchover to primary;
or
SQL>recover standby database

Errors in
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
file /u01/app/oracle/diag/rdbms/np_hmwprd01/HMWPRD01/trace/HMWPRD01_pr00_32286.trc:ORA-01111: name for data file 6 is unknown - rename to correct fileORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006'
Slave exiting with ORA-1111 exception

Solution:

1)Check : standby_file_management = manual or AUTO (make it AUTO)
2)select name from v$datafile;
NAME
----------------------------------------------------
+DG_jd_01/jd/datafile/system.259.808327713
+DG_jd_01/jd/datafile/sysaux.258.808327713
+DG_jd_01/jd/datafile/undotbs1.260.808327713
+DG_jd_01/jd/datafile/users.261.808327713
+DG_jd_01/jd/datafile/mwdata.257.808327711/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006

alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006'
as '+DG_jd_01/jd/datafile/mwdata.dbf';

Check following parameter to avoid these issue again:
alter system set db_create_file_dest='+DG_jd_01';
alter system set standby_file_management=auto;
Check standby UNIQUE name

ORACLE - Data Guard - SWITCHOVER_STATUS

What is switch over status for data guard ?

1) Status on Primary database :
select switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
TO PRIMARY

2) Status on Standby database :
select switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED 

3) Status on Standby database :  (After making Primary as a standby)
i.e.alter database commit to switchover to physical standby with session shutdown;
SQL> select database_role from v$database ;
DATABASE_ROLE
----------------
PHYSICAL STANDBY

4) Recovery status on standby:
Use : RECOVER MANAGED STANDBY DATABASE FINISH;
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED

Info for switchover_status column of v$database:
Not Allowed :  Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.

Session Active : Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.

Switchover Pending : This is a standby database and the primary database switchover request has been received but not processed.

Switchover Latent :  The switchover was in pending mode, but did not complete and went back to the primary database.

To Primary :  This is a standby database, with no active sessions, that is allowed to switch over to a primary database.
To Standby :  This is a primary database, with no active sessions, that is allowed to switch over to a standby database.

Recovery Needed : This is a standby database that has not received the switchover request.

ORACLE - Data Guard End-of-Redo

How to verify switchover in primary and standby alert log files ?
alter database commit to switchover to physical standby with session shutdown;

On OLD primary database:Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/hmwprd02/HMWPRD02/trace/HMWPRD02_ora_15117.trc
Clearing standby activation ID 3663711578 (0xda5fcd5a)
The primary database controlfile was created using the
'MAXLOGFILES 40' clause.
There is space for up to 31 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl8.f' SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl9.f' SIZE 314572800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl10.f' SIZE 314572800;
Archivelog for thread 1 sequence 4670 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown requiredCompleted: alter database commit to switchover to physical standby with session shutdown

Old Standby Databse:Tue Jul 23 11:56:28 2013
Media Recovery Log /ArchLog/oracle/HMWPRD02/arch/arch_1_4678_810055494.arcIdentified End-Of-Redo (switchover) for thread 1 sequence 4678 at SCN 0x0.f988595
Resetting standby activation ID 3675145693 (0xdb0e45dd)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Media Recovery Waiting for thread 1 sequence 4679

ORACLE - Data Guard - Switchover

How to perform switchover for Oracle Data Guard ?

=============================
Verification before switchover:
=============================
select switchover_status from v$database;
show parameter log_archive_dest_state
select sequence#, status, applied from v$archived_log;
alter system switch logfile;

=============================
1)old primary site:
=============================
SQL>alter database commit to switchover to physical standby with session shutdown;
SQL>shutdown immediate
SQL>startup nomount
SQL>alter database mount standby database;
SQL>alter system set log_archive_dest_state_2=defer scope=both;

=============================
2)old standby site:
=============================
SQL>select switchover_status from v$database;
SQL>alter database commit to switchover to primary;
SQL>shutdown immediate
SQL>startup

=============================
3)New standby site - START MRP -
=============================
SQL>alter database recover managed standby database disconnect from session;

ORACLE - RMAN-05529

Warning message:
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

Cause:
It was not possible to convert ASM Oracle Managed Files names using DB_FILE_NAME_CONVERT parameter. RMAN changed these invalid names to the converted disk group name instead.

Action: 
No action is required. This is an informational message only.

ORACLE - Alert log:Failed to Establish Dependency

Issue - Oracle 11gR2 alert log has follwoing messages:

ALTER DATABASE   MOUNT
NOTE: Loaded library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
NOTE: Loaded library: System
SUCCESS: diskgroup DG_HMWPRD01_01 was mounted
ERROR: failed to establish dependency between database NP_HMWPRD01 and diskgroup resource ora.DG_HMWPRD01_01.dg
ARCH: STARTING ARCH PROCESSES
Fri Jul 19 09:58:38 2013
ARC0 started with pid=26, OS id=11260
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES

Solution:
This error is harmless.
Documented in Bug 8304720
Ignore it or srvctl modify database -d <db_unique_name> -a "<diskgroup_list>"
Ref Doc ID - 1474954.1

ORACLE - Drop database

How to drop database with ASM file system ?

SQL> select name from v$database;
NAME
------
ATEST
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict mount
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size                  2234960 bytes
Variable Size             805307824 bytes
Database Buffers          230686720 bytes
Redo Buffers                5656576 bytes
Database mounted.

SQL> drop database;
Database dropped.

ORACLE - Binary MisMatch

Issue - Oracle 11gR2 alert log has following error:

WARNING: Oracle executable binary mismatch detected.
 Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages
Thu Jul 18 15:47:12 2013
WARNING: Oracle executable binary mismatch detected.
 Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages
Thu Jul 18 15:47:17 2013

Solution:
alter system set "_disable_image_check" = true scope=both; and Restart the database (optional)
(Ref Doc ID: 369260.1)

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

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