Easy to Learn Oracle Database and Solve Your Problem. : May 2015

Saturday, May 30, 2015

How to find Voting Disk File Location in RAC

[grid@OEL5RACN2 ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 6980257b6acc4ff0bf752c6901e25711 (/dev/oracleasm/disks/CRS_FILES_01) [OCRS_FILES]
Located 1 voting disk(s).
[grid@OEL5RACN2 ~]$

PRCD-1120 : The resource for database RAC could not be found.

[oracle@OEL5RACN1 ~]$ srvctl status database -d RACDB -v
PRCD-1120 : The resource for database RACDB could not be found.
PRCR-1001 : Resource ora.rac.db does not exist

This is indicate that Database are not registered in OCR.

Solution : Register database into OCR (database and instances).

[grid@OEL5RACN1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA_01.dg
ONLINE ONLINE oel5racn1
ONLINE ONLINE oel5racn2
ora.FRA_01.dg
ONLINE ONLINE oel5racn1
ONLINE ONLINE oel5racn2
ora.LISTENER.lsnr
ONLINE ONLINE oel5racn1
ONLINE ONLINE oel5racn2
ora.OCRS_FILES.dg
ONLINE ONLINE oel5racn1
ONLINE ONLINE oel5racn2
ora.asm
ONLINE ONLINE oel5racn1 Started
ONLINE ONLINE oel5racn2 Started
ora.eons
ONLINE ONLINE oel5racn1
ONLINE ONLINE oel5racn2
ora.gsd
OFFLINE OFFLINE oel5racn1
OFFLINE OFFLINE oel5racn2
ora.net1.network
ONLINE ONLINE oel5racn1
ONLINE ONLINE oel5racn2
ora.ons
ONLINE ONLINE oel5racn1
ONLINE ONLINE oel5racn2
ora.registry.acfs
ONLINE ONLINE oel5racn1
ONLINE ONLINE oel5racn2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE oel5racn1
ora.oc4j
1 OFFLINE OFFLINE
ora.oel5racn1.vip
1 ONLINE ONLINE oel5racn1
ora.oel5racn2.vip
1 ONLINE ONLINE oel5racn2
ora.racdb.db
1 OFFLINE OFFLINE
2 OFFLINE OFFLINE
ora.scan1.vip
1 ONLINE ONLINE oel5racn1
[grid@OEL5RACN1 ~]$
[oracle@OEL5RACN1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@OEL5RACN1 ~]$ srvctl add database -d RACDB1 -o /u01/app/oracle/product/11.2.0/db_1
[oracle@OEL5RACN1 ~]$ srvctl start database -d RACDB1
Database RACDB1 cannot be started since it has no configured instances.
[oracle@OEL5RACN1 ~]$ srvctl config database -d RACDB1
Database unique name: RACDB1
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RACDB1
Database instances:
Disk Groups:
Services:
Database is administrator managed
[oracle@OEL5RACN1 ~]$ srvctl start database -d RACDB
[oracle@OEL5RACN1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is running on node oel5racn1
Instance RACDB2 is running on node oel5racn2
[oracle@OEL5RACN1 ~]$

[Note : If your instance are not added you also need to add instance into CRS the following way ]
[oracle@OEL5RACN1 ~]$ srvctl add instance -d RACDB -i RACDB1 -n OEL5RACN1

Find Parallel Index and Stop Parallel Operation.

This will help us to find parallel Index and stop Parallel Execution.

SQL> SET LINE 200
SQL> SET VERIFY OFF;
SQL> COLUMN DEGREE FORMAT 999 HEAD 'Number Of CPU'
SQL> COLUMN LAST_ANALYZED FORMAT a30 HEAD 'Last Stat'
SQL> COLUMN NUM_ROWS FORMAT 999,999,999 HEAD 'Number Of Rows'
SQL> COLUMN PARTITIONED FORMAT a10 HEAD 'Partition Status'
SQL> SELECT DEGREE, LAST_ANALYZED, NUM_ROWS,PARTITIONED
2 FROM ALL_INDEXES
3 WHERE UPPER(OWNER)=UPPER('&Owner')
4 AND UPPER(INDEX_NAME)=UPPER('&Index');
Enter value for owner: part
Enter value for index: IND_ORDER_LIST

Number Of CPU Last Stat Number Of Rows Partition
---------------------------------------- ------------------------------ -------------- ----------
16 30-MAY-15 0 NO

SQL>

====== Stop Parallel Execution =====

SQL> ALTER INDEX PART.IND_ORDER_LIST NOPARALLEL;

Index altered.

SQL>

SQL> SELECT DEGREE, LAST_ANALYZED, NUM_ROWS,PARTITIONED
2 FROM ALL_INDEXES
3 WHERE UPPER(OWNER)=UPPER('&Owner')
4 AND UPPER(INDEX_NAME)=UPPER('&Index');
Enter value for owner: part
Enter value for index: IND_ORDER_LIST

Number Of CPU Last Stat Number Of Rows Partition
---------------------------------------- ------------------------------ -------------- ----------
1 30-MAY-15 0 NO

SQL>

Make Index Creation Faster.

At the time of index creation full table scan are performed. Oracle fetch all rows from data file to memory and sort them prior to create index, to complete this task oracle need large memory for large table, when memory are not enough oracle will divide the data into smaller part, sort each part individually and then merge together the result. To complete all this task index creation will be slower.

Faster Index Creation :

SORT_AREA_SIZE: Value of this parameter (in byte) are used from memory to short data.

PARALLEL: At the time of index creation oracle collect collect ROWID from table with full table scan. With parallel option supplied in index clause Oracle will scan full table using the number of CPU's meson in parallel clause.

NOLOGGING: With NOLOGGING option provide Oracle will generate minimal redo, for that Index creation will be faster.

SQL> conn SALES/PASS@SALESDB
Connected.
SQL> show parameter SORT_AREA_SIZE;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536
SQL>
SQL> alter session set SORT_AREA_SIZE=700000;

Session altered.

SQL> CREATE INDEX IND_ORDER_LIST ON ORDERS(ORDER_ID) PARALLEL 16 NOLOGGING TABLESPACE USERS;

Index created.

SQL>

Thursday, May 28, 2015

Instantiating disk: failed

Cause :

1. Shell Linux Not Disabled.
2. ASM is not loaded.

Action :

1. Ensure your SELINUX=disabled from vi /etc/selinux/config.

2. Perform the following ..

[root@OEL5RACN2 ~]# oracleasm createdisk CRS_FILES_01 /dev/sdb1
Writing disk header: done
Instantiating disk: failed
Clearing disk header: done
[root@OEL5RACN2 ~]# oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no

Reboot your System.

[root@OEL5RACN1 ~]# oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@OEL5RACN1 ~]# oracleasm createdisk CRS_FILES_01 /dev/sdb1
Writing disk header: done
Instantiating disk: done

[root@OEL5RACN1 ~]#

RMAN Backup In ASM Disk Group.

It will help us to take RMAN Backup in ASM Disk Group.

[oracle@OEL564ASMN1 ~]$ rman target/

Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 28 17:16:59 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ASMDB (DBID=624768357)

RMAN> backup device type disk format '+RMAN_BACKUP/%U' database plus archivelog delete input;

Starting backup at 28-MAY-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=880572465
input archived log thread=1 sequence=4 RECID=2 STAMP=880573855
input archived log thread=1 sequence=5 RECID=3 STAMP=880658166
input archived log thread=1 sequence=6 RECID=4 STAMP=880897231
input archived log thread=1 sequence=7 RECID=5 STAMP=880898978
input archived log thread=1 sequence=8 RECID=6 STAMP=880909238
input archived log thread=1 sequence=9 RECID=7 STAMP=880910336
channel ORA_DISK_1: starting piece 1 at 28-MAY-15
channel ORA_DISK_1: finished piece 1 at 28-MAY-15
piece handle=+RMAN_BACKUP/01q83801_1_1 tag=TAG20150528T171856 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+FRA/asmdb/archivelog/2015_05_24/thread_1_seq_3.260.880572457 RECID=1 STAMP=880572465
archived log file name=+FRA/asmdb/archivelog/2015_05_24/thread_1_seq_4.261.880573849 RECID=2 STAMP=880573855
archived log file name=+FRA/asmdb/archivelog/2015_05_25/thread_1_seq_5.262.880658159 RECID=3 STAMP=880658166
archived log file name=+FRA/asmdb/archivelog/2015_05_28/thread_1_seq_6.263.880897227 RECID=4 STAMP=880897231
archived log file name=+FRA/asmdb/archivelog/2015_05_28/thread_1_seq_7.264.880898973 RECID=5 STAMP=880898978
archived log file name=+FRA/asmdb/archivelog/2015_05_28/thread_1_seq_8.265.880909235 RECID=6 STAMP=880909238
archived log file name=+FRA/asmdb/archivelog/2015_05_28/thread_1_seq_9.266.880910333 RECID=7 STAMP=880910336
Finished backup at 28-MAY-15

Starting backup at 28-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/asmdb/datafile/system.256.880569463
input datafile file number=00002 name=+DATA/asmdb/datafile/sysaux.257.880569467
input datafile file number=00005 name=+DATA/asmdb/datafile/example.265.880569629
input datafile file number=00003 name=+DATA/asmdb/datafile/undotbs1.258.880569467
input datafile file number=00004 name=+DATA/asmdb/datafile/users.259.880569471
channel ORA_DISK_1: starting piece 1 at 28-MAY-15
channel ORA_DISK_1: finished piece 1 at 28-MAY-15
piece handle=+RMAN_BACKUP/02q8380o_1_1 tag=TAG20150528T171920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-MAY-15
channel ORA_DISK_1: finished piece 1 at 28-MAY-15
piece handle=+RMAN_BACKUP/03q83833_1_1 tag=TAG20150528T171920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 28-MAY-15

Starting backup at 28-MAY-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=8 STAMP=880910447
channel ORA_DISK_1: starting piece 1 at 28-MAY-15
channel ORA_DISK_1: finished piece 1 at 28-MAY-15
piece handle=+RMAN_BACKUP/04q8383g_1_1 tag=TAG20150528T172047 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+FRA/asmdb/archivelog/2015_05_28/thread_1_seq_10.266.880910443 RECID=8 STAMP=880910447
Finished backup at 28-MAY-15

RMAN> exit

Recovery Manager complete.
[oracle@OEL564ASMN1 ~]$

=== Backup Piece ====

[grid@OEL564ASMN1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
RMAN_BACKUP/
ASMCMD> cd /RMAN_BACKUP/
ASMCMD> ls
01q83801_1_1
02q8380o_1_1
03q83833_1_1
04q8383g_1_1
ASMDB/
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'

Type Redund Striped Time Sys Name
Y ASMDB/
N 01q83801_1_1 => +RMAN_BACKUP/ASMDB/BACKUPSET/2015_05_28/annnf0_TAG20150528T171856_0.256.880910339
N 02q8380o_1_1 => +RMAN_BACKUP/ASMDB/BACKUPSET/2015_05_28/nnndf0_TAG20150528T171920_0.257.880910361
N 03q83833_1_1 => +RMAN_BACKUP/ASMDB/BACKUPSET/2015_05_28/ncsnf0_TAG20150528T171920_0.258.880910437
N 04q8383g_1_1 => +RMAN_BACKUP/ASMDB/BACKUPSET/2015_05_28/annnf0_TAG20150528T172047_0.259.880910449
ASMCMD>

Add New Disk Group In ASM

Disk Redundancy : There are three disk redundancy in Oracle ASM which are used at the time of Disk group creation.

NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID.

Disk Group REBALANCE: REBALANCE would not required normally, ASM automatically rebalances disk groups when their configuration changes. We can do an manual REBALANCE operation to control the speed.

POWER: This is specifies the degree of parallelism, and thus the speed of the rebalance operation.

[

CREATE DISKGROUP RMAN_BACKUP EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/RMAN_BKP';

CREATE DISKGROUP RMAN_BACKUP NORMAL REDUNDANCY
FAILGROUP fail_group_1 DISK
'/dev/oracleasm/disks/file01' NAME disk1,
'/dev/oracleasm/disks/file02' NAME disk2
FAILGROUP fail_group_2 DISK
'/dev/oracleasm/disks/file03' NAME disk1,
'/dev/oracleasm/disks/file04' NAME disk2;

CREATE DISKGROUP DATA_DISK01 HIGH REDUNDANCY
FAILGROUP FAIL_GROUP_1 DISK
'/dev/oracleasm/disks/file01'
FAILGROUP FAIL_GROUP_1 DISK
'/dev/oracleasm/disks/file02'
FAILGROUP FAIL_GROUP_1 DISK
'/dev/oracleasm/disks/file03'
FAILGROUP FAIL_GROUP_1 DISK
'/dev/oracleasm/disks/file04'
FAILGROUP FAIL_GROUP_1 DISK
'/dev/oracleasm/disks/file05'
FAILGROUP FAIL_GROUP_1 DISK
'/dev/oracleasm/disks/file06'
ATTRIBUTE 'compatible.asm' = '11.2.0.0.0';

]

=== From root User

List of LUN Available for ASM

[root@OEL564ASMN1 ~]# fdisk -l

Disk /dev/sda: 53.6 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 2624 20972857+ 83 Linux
/dev/sda3 2625 3146 4192965 82 Linux swap / Solaris
/dev/sda4 3147 6527 27157882+ 5 Extended
/dev/sda5 3147 6527 27157851 8e Linux LVM

Disk /dev/sdb: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 3916 31455238+ 83 Linux

Disk /dev/sdc: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdc1 1 1305 10482381 83 Linux

Disk /dev/sdd: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdd doesn't contain a valid partition table

Prepare Disk For ASM

[root@OEL564ASMN1 ~]# fdisk /dev/sdd
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-652, default 652):
Using default value 652

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@OEL564ASMN1 ~]#

List of Disk For ASM

[root@OEL564ASMN1 ~]# fdisk -l

Disk /dev/sda: 53.6 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 2624 20972857+ 83 Linux
/dev/sda3 2625 3146 4192965 82 Linux swap / Solaris
/dev/sda4 3147 6527 27157882+ 5 Extended
/dev/sda5 3147 6527 27157851 8e Linux LVM

Disk /dev/sdb: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 3916 31455238+ 83 Linux

Disk /dev/sdc: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdc1 1 1305 10482381 83 Linux

Disk /dev/sdd: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdd1 1 652 5237158+ 83 Linux
[root@OEL564ASMN1 ~]# oracleasm createdisk RMAN_BKP /dev/sdd1
Writing disk header: done
Instantiating disk: done
[root@OEL564ASMN1 ~]#
[root@OEL564ASMN1 ~]# oracleasm listdisks
DATAVOL1
FRAVOL1
RMAN_BKP
[root@OEL564ASMN1 ~]#
[root@OEL564ASMN1 ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:DATAVOL1 [62910477 blocks (32210164224 bytes), maxio 512]
Discovered disk: ORCL:FRAVOL1 [20964762 blocks (10733958144 bytes), maxio 512]
Discovered disk: ORCL:RMAN_BKP [10474317 blocks (5362850304 bytes), maxio 512]
[root@OEL564ASMN1 ~]#
[root@OEL564ASMN1 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
[root@OEL564ASMN1 ~]#

=== From grid User

[grid@OEL564ASMN1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu May 28 14:17:16 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn /as sysasm
Connected.
SQL> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;

GROUP_NUMBER Disk Name
------------ ------------
1 DATA
2 FRA

SQL> SET LINE 2000
SQL> COLUMN group_number FORMAT 999,999 HEAD 'Group Number'
SQL> COLUMN MOUNT_STATUS FORMAT a10 HEAD 'Mount Status'
SQL> COLUMN HEADER_STATUS FORMAT a12 HEAD 'Header Status'
SQL> COLUMN MODE_STATUS FORMAT a10 HEAD ' Mode Status'
SQL> COLUMN STATE FORMAT a10 HEAD 'State'
SQL> COLUMN TOTAL_MB FORMAT 999,999 HEAD 'Total Size In MB'
SQL> COLUMN FREE_MB FORMAT 999,999 HEAD 'Free Space In MB'
SQL> COLUMN NAME FORMAT a12 HEAD 'Disk Name'
SQL> COLUMN PATH FORMAT a45 HEAD 'Disk Location'
SQL> COLUMN LABEL FORMAT a12 HEAD 'Disk Level'
SQL> SELECT GROUP_NUMBER, MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB,FREE_MB,NAME,PATH,LABEL FROM V$ASM_DISK;

Group Number Mount Stat Header Statu Mode Stat State Total Size In MB Free Space In MB Disk Name Disk Location Disk Level
------------ ---------- ------------ ---------- ---------- ---------------- ---------------- ------------ --------------------------------------------- ------------
0 CLOSED PROVISIONED ONLINE NORMAL 0 0 /dev/oracleasm/disks/RMAN_BKP
2 CACHED MEMBER ONLINE NORMAL 10,236 9,942 FRA_0000 /dev/oracleasm/disks/FRAVOL1
1 CACHED MEMBER ONLINE NORMAL 30,718 28,836 DATA_0000 /dev/oracleasm/disks/DATAVOL1

SQL>
SQL> CREATE DISKGROUP RMAN_BACKUP EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/RMAN_BKP';

Diskgroup created.

SQL>
SQL> SELECT GROUP_NUMBER, MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB,FREE_MB,NAME,PATH,LABEL FROM V$ASM_DISK;

Group Number Mount Stat Header Statu Mode Stat State Total Size In MB Free Space In MB Disk Name Disk Location Disk Level
------------ ---------- ------------ ---------- ---------- ---------------- ---------------- ------------ --------------------------------------------- ------------
2 CACHED MEMBER ONLINE NORMAL 10,236 9,942 FRA_0000 /dev/oracleasm/disks/FRAVOL1
1 CACHED MEMBER ONLINE NORMAL 30,718 28,836 DATA_0000 /dev/oracleasm/disks/DATAVOL1
3 CACHED MEMBER ONLINE NORMAL 5,114 5,064 RMAN_BACKUP_ /dev/oracleasm/disks/RMAN_BKP
0000
SQL>

http://oracle-base.com/articles/10g/automatic-storage-management-10g.php

Wednesday, May 27, 2015

Move Whole Index From one Tablespace to Another Tablespace

CREATE OR REPLACE PROCEDURE SP_INDEX_MOVE

(P_USER_NAME VARCHAR2,

P_NEW_TABLESPACE VARCHAR2

)

V_INDEX_NAME VARCHAR2(100);

V_IDX_SCRIPTS VARCHAR2(3000);

BEGIN

FOR IND IN (

SELECT OWNER, INDEX_NAME, TABLESPACE_NAME

FROM DBA_INDEXES

WHERE OWNER=P_USER_NAME

AND TABLESPACE_NAME<>P_NEW_TABLESPACE

AND TABLE_NAME NOT LIKE 'SYS%'

AND INDEX_TYPE<>'LOB')

LOOP

V_IDX_SCRIPTS:='ALTER INDEX '||IND.OWNER||'.'||IND.INDEX_NAME||' REBUILD TABLESPACE '||P_NEW_TABLESPACE;

BEGIN

EXECUTE IMMEDIATE V_IDX_SCRIPTS;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILD INDEX' || IND.INDEX_NAME ||' : '|| SQLERRM);

END;

END LOOP;

END;

============= Execution ==========

EXEC SP_INDEX_MOVE(USER,'TABLESPACE_NAME');

Monday, May 25, 2015

Install Oracle ASM packages and create ASM disk groups.

We will perform the following task for configuring ASM disk group. All task will perform from root user.

1. Install ASM rpm.
2. Configuring and load the ASM kernel module.
3. Creating ASM disk volumes.
4. Creating ASM disks.
5. Test disks discovery.

1. Install ASM rpm.

To configure ASM we have to install the following rpm. You can find this rpm file from OTN

oracleasm-2.6.18-194.el5-2.0.5-1.el5.x86_64.rpm
oracleasmlib-2.0.4-1.el5.i386.rpm
oracleasm-support-2.1.8-1.el5.i386.rpm

[root@OEL564ASMN1 ASM]# ls -ltr
total 244
-rwxrwxr-x 1 root root 137486 Apr 11 12:14 oracleasm-2.6.18-194.el5-2.0.5-1.el5.x86_64.rpm
-rwxrwxr-x 1 root root 85303 Apr 11 12:16 oracleasm-support-2.1.8-1.el5.i386.rpm
-rwxrwxr-x 1 root root 13929 Apr 11 12:17 oracleasmlib-2.0.4-1.el5.i386.rpm
[root@OEL564ASMN1 ASM]# rpm -Uvh oracleasm-support-2.1.8-1.el5.i386.rpm
warning: oracleasm-support-2.1.8-1.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:oracleasm-support ########################################### [100%]
[root@OEL564ASMN1 ASM]# rpm -Uvh oracleasm-2.6.18-194.el5-2.0.5-1.el5.x86_64.rpm
warning: oracleasm-2.6.18-194.el5-2.0.5-1.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:oracleasm-2.6.18-194.el########################################### [100%]
[root@OEL564ASMN1 ASM]# rpm -Uvh oracleasmlib-2.0.4-1.el5.i386.rpm
warning: oracleasmlib-2.0.4-1.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:oracleasmlib ########################################### [100%]

2. Configuring and load the ASM kernel module.

[This script completes the following tasks.
Creates the /etc/sysconfig/oracleasm configuration file
Creates the /dev/oracleasm mount point
Mounts the ASMLib driver file system
]

[root@OEL564ASMN1 ASM]# oracleasm configure -i
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 []: grid
Default group to own the driver interface []: asmadmin
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

[This will load oracleasm kernel module ]

[root@OEL564ASMN1 ASM]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm

Mounting ASMlib driver filesystem: /dev/oracleasm

3. Creating ASM disk volumes.

Showing available disk for Oracle ASM Disk Volume.
[root@OEL564ASMN1 ASM]# fdisk -l

Disk /dev/sda: 53.6 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 2624 20972857+ 83 Linux
/dev/sda3 2625 3146 4192965 82 Linux swap / Solaris
/dev/sda4 3147 6527 27157882+ 5 Extended
/dev/sda5 3147 6527 27157851 8e Linux LVM

Disk /dev/sdb: 32.2 GB, 32212254720 bytes
255 heads, 63 sectors/track, 3916 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table

Disk /dev/sdc: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table

[ We will use /dev/sdb and /dev/sdc for ASM Disk group ]

[root@OEL564ASMN1 ASM]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 3916.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-3916, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-3916, default 3916):
Using default value 3916

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@OEL564ASMN1 ASM]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):
Using default value 1305

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

4. Creating ASM disks.

[root@OEL564ASMN1 ~]# oracleasm createdisk DATAVOL1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@OEL564ASMN1 ~]# oracleasm createdisk FRAVOL1 /dev/sdc1
Writing disk header: done

Instantiating disk: done

5. Test disks discovery. (discovery is being used by the oracle DBCA)

[root@OEL564ASMN1 ~]# oracleasm listdisks
DATAVOL1
FRAVOL1
[root@OEL564ASMN1 ~]#
[root@OEL564ASMN1 ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:DATAVOL1 [62910477 blocks (32210164224 bytes), maxio 512]
Discovered disk: ORCL:FRAVOL1 [20964762 blocks (10733958144 bytes), maxio 512]
[root@OEL564ASMN1 ~]#

How to check Archive mode of database and Archive log Location.

[oracle@OEL564ASMN1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 25 20:11:00 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 4122M

Candidate of ASM Disk Not Showing.

Problem: When I am going to Configure ASM in oracle grid infrastructure standalone server installation, I am not able to showing Candidate disk group of ASM.

Solution: Find the ASM disk group location and edit Change Disk Discovery Path.

1. Disk location are empty like this.

2. Open terminal and find you disk group by the following command.

3.Now click Change Disk Discovery Path and you will see the following screen.

4. Now edit Disk Discovery Path Value with your ASM Disk group location.

5. Now Click OK and you can see your ASM Disk group.

6. Now Select Your Disk group and go ahead.

Thursday, May 21, 2015

ORA-09945: Unable to initialize the audit trail file

Cause: There is no available space for Oracle. audit_trail_dest are full.

Action: Clear some file or allocate space for Oracle database.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup;
ORACLE instance started.

Total System Global Area 1119043584 bytes
Fixed Size 2227624 bytes
Variable Size 838861400 bytes
Database Buffers 268435456 bytes
Redo Buffers 9519104 bytes
Database mounted.
ORA-09945: Unable to initialize the audit trail file
Linux-x86_64 Error: 28: No space left on device
SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OEL64BN1 ~]$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda3 17545020 2940900 13712884 18% /
tmpfs 1154528 710572 443956 62% /dev/shm
/dev/sda1 25803068 24492340 8 100% /u01
[oracle@OEL64BN1 ~]$

Finding Lock In Oracle Database using SQL Statements.

1. Find All Object Which are Currently Locked By Session.

SELECT OBJECT_NAME,S.INST_ID,S.SID,S.SERIAL#,S.OSUSER,S.SERVER,S.MACHINE, S.STATUS,P.PNAME,SQL_TEXT, SQL_FULLTEXT
FROM GV$LOCKED_OBJECT L,GV$SESSION S,GV$PROCESS P,DBA_OBJECTS O, GV$SQLAREA T
WHERE L.OBJECT_ID=O.OBJECT_ID
AND S.SQL_ADDRESS =T.ADDRESS
AND S.SQL_HASH_VALUE =T.HASH_VALUE
AND L.SESSION_ID=S.SID
AND S.PADDR=P.ADDR;

2. Lists all DML locks and all outstanding requests for a DML lock.

SELECT * FROM DBA_DML_LOCKS;

3. Holding a lock on an object for which another session is waiting.

SELECT * FROM DBA_BLOCKERS;

4. Blocking Session Details.

SELECT BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS,SECONDS_IN_WAIT
FROM V$SESSION
WHERE BLOCKING_SESSION IS NOT NULL
ORDER BY BLOCKING_SESSION;

Finding SQL Statements History in RAC/Single Instance Database.

1. Session Wise Latest Running SQL Statement.

SELECT T.INST_ID,S.USERNAME, S.SID, S.SERIAL#,T.SQL_ID,T.SQL_TEXT "Last SQL"
FROM GV$SESSION S, GV$SQLAREA T
WHERE S.SQL_ADDRESS =T.ADDRESS
AND S.SQL_HASH_VALUE =T.HASH_VALUE;

2. Currently Executing SQL Statements.

SELECT I.INST_ID,S.SID,SERIAL#,A.SQL_ID,S.EVENT,A.SQL_TEXT,A.SQL_FULLTEXT,S.USERNAME,I.HOST_NAME,MACHINE,S.SECONDS_IN_WAIT SEC_WAIT,
TO_CHAR(LOGON_TIME,'DD-MON-RR HH24:MI') "Session Login Time"
FROM GV$SESSION S,GV$SQLAREA A,GV$INSTANCE I
WHERE S.USERNAME IS NOT NULL
AND S.STATUS='ACTIVE'
AND S.SQL_ADDRESS=A.ADDRESS
AND S.INST_ID=A.INST_ID
AND I.INST_ID = A.INST_ID
AND SQL_TEXT NOT LIKE 'SELECT SID,SERIAL#,A.SQL_ID,A.SQL_TEXT%';

3. Currently Waiting Session With SQL Statements.

SELECT W.SID, W.EVENT, W.WAIT_CLASS_ID, W.WAIT_CLASS, W.WAIT_TIME, W.SECONDS_IN_WAIT, W.WAIT_TIME_MICRO, Q.SQL_ID, Q.PLAN_HASH_VALUE,
SQL_TEXT, SQL_FULLTEXT
FROM V$SESSION_WAIT W, V$SESSION S, V$SQL Q
WHERE W.SID=S.SID
AND S.SQL_ID=Q.SQL_ID
AND W.WAIT_TIME =0
AND W.EVENT<>'SQL*Net message from client'
AND W.WAIT_CLASS<>'Idle'
ORDER BY W.SID;

Note :

W.WAIT_TIME > 0 - Value is the duration of the last wait in hundredths of a second
W.WAIT_TIME =-1 - Duration of the last wait was less than a hundredth of a second
W.WAIT_TIME =-2 - Parameter TIMED_STATISTICS was set to false

4. Latest Running SQL Statements.

SELECT H.INST_ID,H.SAMPLE_TIME,H.SESSION_ID,H.SESSION_SERIAL#,H.SQL_ID
FROM GV$ACTIVE_SESSION_HISTORY H
WHERE H.SQL_ID IS NOT NULL
ORDER BY 1 DESC;

5. Long Time Running SQL Statements.

SELECT SID,OPNAME,SQL_ID, SQL_PLAN_HASH_VALUE, SQL_EXEC_START,TARGET,ROUND(SOFAR/TOTALWORK*100,2) "Percent Done" ,
START_TIME,LAST_UPDATE_TIME,TIME_REMAINING
FROM V$SESSION_LONGOPS;

6. Active Session Running More Then 30 Min.

SELECT USERNAME,MACHINE,INST_ID,SID,SERIAL#,PROGRAM,TO_CHAR(LOGON_TIME,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) MINUTES_LOG_ON,ROUND(LAST_CALL_ET/60,1) MINUTES_CURRENT_SQL
FROM GV$SESSION
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL
AND ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 30
ORDER BY MINUTES_LOG_ON DESC;

7. Active Transaction Details with SQL ID.

SELECT SQL_ADDRESS, SQL_HASH_VALUE, SQL_ID,T.STATUS, START_TIME,USERNAME, T.USED_UBLK, T.USED_UREC
FROM V$TRANSACTION T, V$SESSION S
WHERE T.ADDR = S.TADDR;

Wednesday, May 20, 2015

Find SQL Execution Plan

SQL> EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=50;

Explained.

SQL> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18113 | 2352K| 126 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 18113 | 2352K| 126 (1)| 00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("DEPARTMENT_ID"=50)

Note
-----
- dynamic sampling used for this statement (level=2)

17 rows selected.

SQL>

Monday, May 18, 2015

NULLs Value in Index Column.

If Indexed columns (Single Column that contain null values) contain null values then the associated row is not indexed, for that indexed resulting is smaller index structure.

We can analyze schema object such as table, index and cluster to collect and manage statistics, validity of storage format, identify migrated and chained rows.

Oracle always recomand that collect statistics using DBMS_STATS package, so it is better to collect optimizer statistics using DBMS_STATS package and not by analyzing.

SQL> CREATE TABLE ACCOUNT_INFORMATION
2 (ACC_NUMBER NUMBER,
3 ACC_TITLE VARCHAR2(100),
4 ACC_NARATION VARCHAR2(100)
5 );

Table created.

Elapsed: 00:00:00.02
SQL> CREATE UNIQUE INDEX IND_ACC_INFO_AC_NUMBER ON ACCOUNT_INFORMATION(ACC_NUMBER);

Index created.

Elapsed: 00:00:00.03
SQL> CREATE INDEX IND_ACC_INFO_NARATION ON ACCOUNT_INFORMATION(ACC_NARATION);

Index created.

Elapsed: 00:00:00.02
SQL> INSERT INTO ACCOUNT_INFORMATION (ACC_NUMBER, ACC_TITLE, ACC_NARATION)
2 SELECT LEVEL, LEVEL||' ACC NAME TEST '||LEVEL, NULL FROM DUAL CONNECT BY LEVEL <= 100000;

100000 rows created.

Elapsed: 00:00:00.34
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL> ANALYZE INDEX IND_ACC_INFO_AC_NUMBER VALIDATE STRUCTURE;

Index analyzed.

Elapsed: 00:00:00.04
SQL> SELECT NAME, LF_ROWS, LF_BLKS, BR_ROWS, BR_BLKS, DEL_LF_ROWS, DISTINCT_KEYS FROM INDEX_STATS;

NAME LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS DISTINCT_KEYS
------------------------------ ---------- ---------- ---------- ---------- ----------- -------------
IND_ACC_INFO_AC_NUMBER 100000 187 186 1 0 100000

Elapsed: 00:00:00.02
SQL> ANALYZE INDEX IND_ACC_INFO_NARATION VALIDATE STRUCTURE;

Index analyzed.

Elapsed: 00:00:00.02
SQL> SELECT NAME, LF_ROWS, LF_BLKS, BR_ROWS, BR_BLKS, DEL_LF_ROWS, DISTINCT_KEYS FROM INDEX_STATS;

NAME LF_ROWS LF_BLKS BR_ROWS BR_BLKS DEL_LF_ROWS DISTINCT_KEYS
------------------------------ ---------- ---------- ---------- ---------- ----------- -------------
IND_ACC_INFO_NARATION 0 1 0 0 0 0

Elapsed: 00:00:00.00
SQL>

Faster Access Rows inside Table Using ROWID pseudocolumn.

ROWID : ROWID is pseudo column that returns the address of the row. It is faster to access data from table. This column are unique for an particular table. Rows in different tables that are stored together in the same cluster can have the same ROWID .

This is combination of :

The data object number of the object.
The data block in the datafile.
The position of the row in the data block (first row is 0).
The datafile number of tablespace.

SQL> SET TIMING ON;
SQL> CREATE TABLE BIG_TABLE
2 (ID NUMBER(10),
3 NAME VARCHAR2(200)
4 );

Table created.

Elapsed: 00:00:00.05
SQL> INSERT INTO BIG_TABLE (ID , NAME)
2 SELECT LEVEL, NULL FROM DUAL CONNECT BY LEVEL <= 100000;

100000 rows created.

Elapsed: 00:00:00.11
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL> DECLARE
2 TYPE REC_DATA IS RECORD
3 (
4 ID BIG_TABLE.ID%TYPE
5 );
6 TYPE TT_DATA IS TABLE OF REC_DATA INDEX BY PLS_INTEGER;
7 T_DATA TT_DATA;
8 BEGIN
9 SELECT ID BULK COLLECT INTO T_DATA FROM BIG_TABLE ;
10
11 FORALL IND IN T_DATA.FIRST .. T_DATA.LAST
12 UPDATE BIG_TABLE
13 SET NAME ='NAME '||T_DATA(IND).ID
14 WHERE ID=T_DATA(IND).ID;
15 END;
16 /

PL/SQL procedure successfully completed.

Elapsed: 00:04:59.99
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> DECLARE
2 TYPE REC_DATA IS RECORD
3 (
4 ID BIG_TABLE.ID%TYPE,
5 ROW_ID VARCHAR2(1000)
6 );
7 TYPE TT_DATA IS TABLE OF REC_DATA INDEX BY PLS_INTEGER;
8 T_DATA TT_DATA;
9 BEGIN
10 SELECT ID,ROWID BULK COLLECT INTO T_DATA FROM BIG_TABLE ;
11
12 FORALL IND IN T_DATA.FIRST .. T_DATA.LAST
13 UPDATE BIG_TABLE
14 SET NAME ='NAME '||T_DATA(IND).ID
15 WHERE ROWID=T_DATA(IND).ROW_ID;
16 END;
17 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.55
SQL>

Easy to Learn Oracle Database and Solve Your Problem.

Query

Search Blog