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)