Wednesday, October 5, 2011

ORA-12560 TNS:protocol adapter error

Cause: A generic protocol adapter error occurred.

Action: Check addresses used for proper protocol specification. Before
reporting this error, look at the error stack and check for lower level
transport errors.For further details, turn on tracing and reexecute the
operation. Turn off tracing when the operation is complete.

Explanation:
This is a high level error just reporting an error occurred in
the actual transport layer. Look at the next error down the
stack and process that.


Some workaround:-

1) Database Creation (DBCA) Fails With ORA-12560

I) in windows Anit-virus install that prevent write to system32 folder. so check it or uninstall it.

II) There was no ORA_DBA group and no ORADBA user on the server


2) (in SQLPlus) sys connection fails with ORA-12560 (in windows)

I) Make sure that the service for the SID is started and running.(in windows)

II) The PATH environment variable was incorrectly set
or
Oracle client software home was set as the first entry in the PATH
not Oracle database software home.
Therefore, the client version of sqlplus was used.

III) The ORACLE_SID is not set in the environment.
check (in windows) My Computer >> right click >> Properties >> Advanced >> Env variables >> System

IV) Make sure that your SID name is less than 15 charcaters long and does not
include any special characters such as _,-,$,&,!, etc.

V) if you use Terminal Services Client then, you MUST connect using a SQL*Net alias
e.g. sqlplus username/password@


3) Domain Administrator account can not log in with this SYS as SYSDBA

I) so need to add the Domain Administrators group to the
Local Administrators group and that same individual domain administrator account must also be a member of the local ORA_DBA or DBA group.



Tuesday, October 4, 2011

How to convert non asm (DB) to asm oracle database 10g

Migration from a non ASM instance to ASM instance is very easy with RMAN
i am doing it here ... just follow the steps.

Assume that you have created a ASM instance in a DISK group.
if you don't have then see here .....
How to create ASM environment and ASM instance

Now we are trying to convert a Non ASM instance(db) "HALIMDB" to a ASM instance .

Here i am using a Disk group "DISKGP1" AS A storage of ASM instance. now we have to convert all existing database file to ASM disk group.

here are live example :- just follow below steps ....( in a one session)

(this is in window environment)

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>set oracle_sid=HALIMDB

C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>SQLPLUS /NOLOG

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 15:50:23 2011

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

SQL>
SQL>
SQL> CONN / AS SYSDBA
Connected.
SQL>
SQL> SHOW parameter inst

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string halimdb
instance_number integer 0
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
SQL>
SQL> alter system set control_files='+DISKGP1' scope=spfile;

System altered.

SQL> alter system set db_create_file_dest='+DISKGP1' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest='+DISKGP1' scope=spfile;

System altered.

SQL>
SQL>
SQL>
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 205523844 bytes
Database Buffers 398458880 bytes
Redo Buffers 7135232 bytes
SQL>
SQL>
SQL> host rman target=sys

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 4 15:52:44 2011

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

target database Password:
connected to target database: HALIMDB (not mounted)

RMAN>

RMAN>

RMAN> restore controlfile from 'G:\oracle\product\10.2.0\oradata\HALIMDB\CONTROL
01.CTL' ;

Starting restore at 04-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DISKGP1/halimdb/controlfile/backup.256.763660435
Finished restore at 04-OCT-11

RMAN>

RMAN> alter database mount ;

database mounted
released channel: ORA_DISK_1

RMAN>

RMAN>

RMAN> backup as copy database format '+DISKGP1' ;

Starting backup at 04-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\SYSTEM01.
DBF
output filename=+DISKGP1/halimdb/datafile/system.257.763661277 tag=TAG20111004T1
60749 recid=2 stamp=763661331
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\SYSAUX01.
DBF
output filename=+DISKGP1/halimdb/datafile/sysaux.258.763661343 tag=TAG20111004T1
60749 recid=3 stamp=763661360
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\EXAMPLE01
.DBF
output filename=+DISKGP1/halimdb/datafile/example.259.763661367 tag=TAG20111004T
160749 recid=4 stamp=763661375
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\UNDOTBS01
.DBF
output filename=+DISKGP1/halimdb/datafile/undotbs1.260.763661383 tag=TAG20111004
T160749 recid=5 stamp=763661386
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\USERS01.D
BF
output filename=+DISKGP1/halimdb/datafile/users.261.763661391 tag=TAG20111004T16
0749 recid=6 stamp=763661392
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DISKGP1/halimdb/controlfile/backup.262.763661393 tag=TAG2011100
4T160749 recid=7 stamp=763661395
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 04-OCT-11
channel ORA_DISK_1: finished piece 1 at 04-OCT-11
piece handle=+DISKGP1/halimdb/backupset/2011_10_04/nnsnf0_tag20111004t160749_0.2
63.763661397 tag=TAG20111004T160749 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 04-OCT-11

RMAN>

RMAN>

RMAN>

RMAN> switch database to copy ;

datafile 1 switched to datafile copy "+DISKGP1/halimdb/datafile/system.257.76366
1277"
datafile 2 switched to datafile copy "+DISKGP1/halimdb/datafile/undotbs1.260.763
661383"
datafile 3 switched to datafile copy "+DISKGP1/halimdb/datafile/sysaux.258.76366
1343"
datafile 4 switched to datafile copy "+DISKGP1/halimdb/datafile/users.261.763661
391"
datafile 5 switched to datafile copy "+DISKGP1/halimdb/datafile/example.259.7636
61367"

RMAN>

RMAN>

RMAN> exit


Recovery Manager complete.

SQL> conn / as sysdba
Connected.
SQL>
SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 664362 generated at 10/03/2011 17:49:50 needed for thread 1
ORA-00289: suggestion : +DISKGP1
ORA-00280: change 664362 for thread 1 is in sequence #3


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>
SQL>
SQL> alter database open resetlogs ;

Database altered.

SQL>
SQL>
SQL> alter database tempfile 'G:\oracle\product\10.2.0\oradata\HALIMDB\TEMP01.DB
F' drop including datafiles ;

Database altered.

SQL>
SQL>
SQL> alter tablespace temp add tempfile size 512m autoextend off ;

Tablespace altered.

SQL>column member format a30
SQL>
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
SQL>
GROUP# MEMBER BYTES
---------- ------------------------------ ----------
3 G:\ORACLE\PRODUCT\10.2.0\ORADA 52428800
TA\HALIMDB\REDO03.LOG

2 G:\ORACLE\PRODUCT\10.2.0\ORADA 52428800
TA\HALIMDB\REDO02.LOG

1 G:\ORACLE\PRODUCT\10.2.0\ORADA 52428800
TA\HALIMDB\REDO01.LOG

4 G:\ORACLE\PRODUCT\10.2.0\ORADA 52428800
TA\HALIMDB\REDO04.LOG

GROUP# MEMBER BYTES
---------- ------------------------------ ----------


SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 UNUSED
4 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT
4 UNUSED

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance halimdb (thread 1)
ORA-00312: online log 1 thread 1:
'G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\REDO01.LOG'


SQL> alter system checkpoint global;

System altered.

SQL> alter system checkpoint global;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL>
SQL>
SQL> alter database add logfile group 1 size 50m ;

Database altered.

SQL> alter system checkpoint global;

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 UNUSED
2 INACTIVE
3 CURRENT
4 UNUSED

SQL> alter database drop logfile group 2;

Database altered.

SQL>
SQL> alter database add logfile group 2 size 50m ;

Database altered.

SQL>
SQL>
SQL> alter system checkpoint global;

System altered.

SQL>
SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 UNUSED
2 UNUSED
3 CURRENT
4 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL>
SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 UNUSED

SQL> alter database drop logfile group 3;

Database altered.

SQL>
SQL> alter database add logfile group 3 size 50m ;

Database altered.

SQL>
SQL>
SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED

SQL> alter database drop logfile group 4;

Database altered.

SQL>
SQL> alter database add logfile group 4 size 50m ;

Database altered.

SQL>
SQL>
SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED

SQL>
SQL> select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group#
= b.group#;

GROUP# MEMBER BYTES
---------- ------------------------------ ----------
3 +DISKGP1/halimdb/onlinelog/gro 52428800
up_3.269.763664937

2 +DISKGP1/halimdb/onlinelog/gro 52428800
up_2.267.763664865

1 +DISKGP1/halimdb/onlinelog/gro 52428800
up_1.265.763664821

4 +DISKGP1/halimdb/onlinelog/gro 52428800
up_4.271.763664975

GROUP# MEMBER BYTES
---------- ------------------------------ ----------

1 +DISKGP1/halimdb/onlinelog/gro 52428800
up_1.266.763664825

2 +DISKGP1/halimdb/onlinelog/gro 52428800
up_2.268.763664869

3 +DISKGP1/halimdb/onlinelog/gro 52428800
up_3.270.763664941

4 +DISKGP1/halimdb/onlinelog/gro 52428800

GROUP# MEMBER BYTES
---------- ------------------------------ ----------
up_4.272.763664977


8 rows selected.

SQL>
SQL>
SQL>
SQL> create pfile ='G:\oracle\asmhalim\initnow.ora' from spfile ;

File created.

SQL>
SQL> create spfile='+DISKGP1/SPFILEASM.ORA' FROM pfile='G:\oracle\asmhalim\init
now.ora' ;

File created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> host rman target=sys

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 4 17:13:05 2011

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

target database Password:
connected to target database: HALIMDB (DBID=3159673037)

RMAN>

RMAN>

RMAN> delete noprompt force copy ;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
specification does not match any archive log in the recovery catalog

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
8 1 A 04-OCT-11 664362 03-OCT-11 G:\ORACLE\PRODUCT\10.2
.0\ORADATA\HALIMDB\SYSTEM01.DBF
9 2 A 04-OCT-11 664362 03-OCT-11 G:\ORACLE\PRODUCT\10.2
.0\ORADATA\HALIMDB\UNDOTBS01.DBF
10 3 A 04-OCT-11 664362 03-OCT-11 G:\ORACLE\PRODUCT\10.2
.0\ORADATA\HALIMDB\SYSAUX01.DBF
11 4 A 04-OCT-11 664362 03-OCT-11 G:\ORACLE\PRODUCT\10.2
.0\ORADATA\HALIMDB\USERS01.DBF
12 5 A 04-OCT-11 664362 03-OCT-11 G:\ORACLE\PRODUCT\10.2
.0\ORADATA\HALIMDB\EXAMPLE01.DBF

List of Control File Copies
Key S Completion Time Ckp SCN Ckp Time Name
------- - --------------- ---------- --------------- ----
7 A 04-OCT-11 664362 03-OCT-11 +DISKGP1/halimdb/controlfil
e/backup.262.763661393
deleted datafile copy
datafile copy filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\SYSTEM01.DBF rec
id=8 stamp=763661844
deleted datafile copy
datafile copy filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\UNDOTBS01.DBF re
cid=9 stamp=763661844
deleted datafile copy
datafile copy filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\SYSAUX01.DBF rec
id=10 stamp=763661844
deleted datafile copy
datafile copy filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\USERS01.DBF reci
d=11 stamp=763661844
deleted datafile copy
datafile copy filename=G:\ORACLE\PRODUCT\10.2.0\ORADATA\HALIMDB\EXAMPLE01.DBF re
cid=12 stamp=763661844
deleted control file copy
control file copy filename=+DISKGP1/halimdb/controlfile/backup.262.763661393 rec
id=7 stamp=763661395
Deleted 6 objects


RMAN>

RMAN>

RMAN>

RMAN>

RMAN> end end end end end end




Monday, October 3, 2011

ORA-29701: unable to connect to Cluster Manager when startup oracle asm instance

I found this error when trying to startup oracle asm instance... solution is very easy . just see below.

i use

C:\>set ORACLE_SID=+ASMHALIM
C:\> localconfig delete
C:\>localconfig add

live example:-

SQL> startup pfile='G:\oracle\asmhalim\initasm.ora'
ORA-29701: unable to connect to Cluster Manager
SQL>
SQL>
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>set ORACLE_SID=+ASMHALIM

C:\>
C:\> localconfig delete

Step 1: stopping local CSS stack
Step 2: deleting OCR repository
failed to open the OCR subkey, err(1060)
failed to cleanup local OCR repository
successfully deleted local CSS setup

C:\>
C:\>localconfig add

Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'administrator', privgrp ''..
Operation successful.
Step 2: creating new CSS service
successfully created local CSS service
successfully added CSS to home

C:\>
C:\>
C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 3 15:44:07 2011

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

Connected to an idle instance.

SQL>
SQL>
SQL> startup pfile='G:\oracle\asmhalim\initasm.ora'
ASM instance started

Total System Global Area 176160768 bytes
Fixed Size 1247948 bytes
Variable Size 149746996 bytes
ASM Cache 25165824 bytes

How to create Oracle ASM instance environment in windows with oracle database 10g ?

How to configure ASM instance environment (within 10 minutes) in windows for oracle database 10g

if you want to convert non ASM to ASM instance then click here

STEP-1) we can configure ASM instance DISKS environment in two ways

one :- not using RAW device or logical DISK drive
--------------------------------------------------------

*._ASM_ALLOW_ONLY_RAW_DISKS=false (in pfile)

This parameter is for not using RAW or logical drive(windows) .
we can view the ASM DISKS, for training purpose we can use it.
for this we have to start for step - 2

Two :- using RAW/ logical DISK
-------------------------------------------------
in this way we cannot see the disks .

A ) First we have to create some logical drive (RAW in unix) not formated by NTFS or somethink file system , not assign any drive letter .
we can do this from below

My computer >right click> Manage> Disk Management
or you can do it via DISKPART.EXE command line utility .

B) Once the partitions are completed, use the ASMTOOLG.EXE utility to "stamp" each partition with an ASM label so that Oracle can recognize these partitions as candidate disks for the ASM instance. I executed the ASMTOOLG.EXE program from the /bin directory of the Oracle home path for my Windows NT database
CMD> ASMTOOLG.EXE (granphical) in 10g
CMD> ASMTOOL.EXE (command line in 10g) like below

ASMTOOL -create \\.\G:\oracle\asmhalim\asm_disks\ASMDISK1.ASM 100m

(we will create below)

after stamped the disks for ASM you can find them, when you try create ASM instance.



STEP-2)

you can Use DBCA for creating ASM instance .
at the storage page of the DBCA , you have to select Automatic Storage Management (ASM) the next disk group , diskes etc.
or
you can create manually (command line) . here i am executed manual steps ..



Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>

C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>cd ..

C:\Documents and Settings>cd ..

C:\>
C:\>
C:\>oradim -new -asmsid +ASMHALIM -startmode manual
Instance created.

C:\>oradim -edit -asmsid +ASMHALIM -startmode a

C:\>
C:\>oradim -edit -asmsid +ASMHALIM -syspwd sys

C:\>
C:\>SET ORACLE_SID=+ASMHALIM

C:\>
C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 3 15:33:33 2011

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

SQL>
SQL> conn / as sysdba
Connected to an idle instance.
SQL>

here i created a txt file name "initasm.ora" in location 'G:\oracle\asmhalim\'
this is the init ora (pfile of the ASM instance) .


*.background_dump_dest='G:\oracle\asmhalim\bdump'
*.core_dump_dest='G:\oracle\asmhalim\cdump'
instance_type=asm
*.large_pool_size=100M
*.remote_login_passwordfile='SHARED'
*.user_dump_dest='G:\oracle\asmhalim\udump'
*._ASM_ALLOW_ONLY_RAW_DISKS=false
*.asm_diskstring='G:\oracle\asmhalim\asm_disks\*'

The setting of asm_diskstring is very important, this should be set to the directory where you are going to create the "ASM files" that will be disks.
*._ASM_ALLOW_ONLY_RAW_DISKS=false this is for not using RAW or logical(windows) drive .



SQL>
SQL> startup pfile='G:\oracle\asmhalim\initasm.ora'
ORA-29701: unable to connect to Cluster Manager
SQL>
SQL>
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>set oracle_sid=+ASMHALIM

C:\>
C:\> localconfig delete
Step 1: stopping local CSS stack
Step 2: deleting OCR repository
failed to open the OCR subkey, err(1060)
failed to cleanup local OCR repository
successfully deleted local CSS setup

C:\>
C:\>localconfig add
Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'administrator', privgrp ''..
Operation successful.
Step 2: creating new CSS service
successfully created local CSS service
successfully added CSS to home

C:\>
C:\>
C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 3 15:44:07 2011

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

Connected to an idle instance.

SQL>
SQL>
SQL> startup pfile='G:\oracle\asmhalim\initasm.ora'
ASM instance started

Total System Global Area 176160768 bytes
Fixed Size 1247948 bytes
Variable Size 149746996 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted


SQL>
SQL>
SQL> create spfile from pfile='G:\oracle\asmhalim\initasm.ora' ;

File created.

Asm instance are created . but not diskgroup . now need to create diskgroup .
before creating diskgroup , we have to create some disks ( this are creating using ASMTOOL.exe as says above)

(ignored if we created RAW disks as STEP-1 using DISK MANAMENT/DISKPART.EXE AND ASMTOOLG or ASMTOOL )

SQL>
SQL>
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>
C:\>ASMTOOL -create \\.\G:\oracle\asmhalim\asm_disks\ASMDISK1.ASM 100m

C:\>
C:\>
C:\>ASMTOOL -create \\.\G:\oracle\asmhalim\asm_disks\ASMDISK2.ASM 100m

C:\>
C:\>ASMTOOL -create \\.\G:\oracle\asmhalim\asm_disks\ASMDISK3.ASM 100m

C:\>ASMTOOL -create \\.\G:\oracle\asmhalim\asm_disks\ASMDISK4.ASM 100m

C:\>

C:\>
C:\>SQLPLUS /NOLOG

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 3 16:15:40 2011

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

SQL>
SQL>
SQL> CONN / AS SYSDBA
Connected.

SQL>
SQL>
SQL> SELECT group_number, disk_number, mount_status, header_status, state, path
2 FROM v$asm_disk;

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH
------------ ----------- ------- ------------ -------- -------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------------------
0 0 CLOSED CANDIDATE NORMAL G:\ORACLE\ASMHALIM\ASM_DI
SKS\ASMDISK1.ASM
0 3 CLOSED CANDIDATE NORMAL G:\ORACLE\ASMHALIM\ASM_DI
SKS\ASMDISK4.ASM
0 2 CLOSED CANDIDATE NORMAL G:\ORACLE\ASMHALIM\ASM_DI
SKS\ASMDISK3.ASM
0 1 CLOSED CANDIDATE NORMAL G:\ORACLE\ASMHALIM\ASM_DI
SKS\ASMDISK2.ASM

SQL>
SQL>
SQL>
SQL> show parameter inst

NAME TYPE VALUE
------------------------------------ ----------- -------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string +asmhalim
instance_number integer 0
instance_type string asm
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
SQL>


-----creating a diskgroup

SQL> ed
Wrote file afiedt.buf

1 CREATE DISKGROUP diskgp1 NORMAL REDUNDANCY
2 FAILGROUP controller1 DISK
3 'G:\oracle\asmhalim\asm_disks\ASMDISK1.ASM' NAME disk1,
4 'G:\oracle\asmhalim\asm_disks\ASMDISK2.ASM' NAME disk2
5 FAILGROUP controller2 DISK
6* 'G:\oracle\asmhalim\asm_disks\ASMDISK3.ASM' NAME disk3
SQL>
SQL> /

Diskgroup created.

SQL>
SQL> ed
Wrote file afiedt.buf

1 select GROUP_NUMBER,name ,type,total_mb, free_mb
2* from v$asm_diskgroup
SQL>
SQL> /

GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ----------
1 DISKGP1 NORMAL 300 196

SQL>
SQL>

-------------adding a disk into a diskgroup

SQL>
SQL> alter diskgroup diskgp1 add disk 'G:\oracle\asmhalim\asm_disks\ASMDISK4.ASM' ;

Diskgroup altered.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select GROUP_NUMBER,name ,type,total_mb, free_mb
2 from v$asm_diskgroup
3 /

GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ----------
1 DISKGP1 NORMAL 400 245

SQL>

Putting data in separate tablespaces, is it affect any performance issue in oracle database ?

Putting indexes and tables in different tablespaces will not affect performance.

Performance is based on the number of physical disks you can use, the RAID configuration, and your ability to distribute I/O across the physical disks.

putting an index for a table on disk 1 and the table data for a table on disk 2 is not good rather take disk 1 and disk 2 and stripe them together and have index and table data evenly spread over both devices so as to get even IO over both.

Putting data in separate tablespaces is only useful for management or administration purposes (such as Storage maintenance, backup and recovery).

And tom kytes says indexes would be in a different tablespace from the data but only because they are a different (extent) SIZE then the data not for performance .

See below links for details from oracle gurus

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1463404632043

https://forums.oracle.com/forums/thread.jspa?messageID=9909917#9909917

Sunday, October 2, 2011

How to Prepare a SQL select statement for SQL Tuning in oracle?

How to Prepare a SQL select statement for SQL Tuning in oracle?



1) connect sqlplus, then set following command

set linesize 1000
set pagesize 1000
set trimout on trimspool on

2) then issue following command

alter session set statistics_level=all;

3) [then Run your query ]

4) then issue following command

select * from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

5)
and then copy&paste the sqlplus output for sql tuning purpose.



example:-


SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 2 13:11:14 2011

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

SQL>
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL> set linesize 1000
SQL>
SQL> set pagesize 1000
SQL>
SQL> set trimout on trimspool on
SQL>
SQL> alter session set statistics_level=all;

Session altered.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select ename,sum(sal) from emp where ename like '%L%' group by ename order by ename ;

ENAME SUM(SAL)
---------- ----------
ALLEN 1600
BLAKE 2850
CLARK 2450
MILLER 1300

SQL>
SQL> select * from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));




PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------
SQL_ID 0rxmvbjh43c88, child number 0
-------------------------------------
select ename,sum(sal) from emp where ename like '%L%' group by ename order by ename

Plan hash value: 15469362

-------------------------------------------------------------------------------------------------------------------
-----------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers
| OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
-----------------------------
| 1 | SORT GROUP BY | | 1 | 4 | 80 | 4 (25)| 00:00:01 | 4 |00:00:00.01 | 7
| 9216 | 9216 | 8192 (0)|
|* 2 | TABLE ACCESS FULL| EMP | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7
| | | |
-------------------------------------------------------------------------------------------------------------------
-----------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / EMP@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/

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

2 - filter("ENAME" LIKE '%L%')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) "ENAME"[VARCHAR2,10], SUM("SAL")[22]
2 - "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]

Note
-----
- dynamic sampling used for this statement


47 rows selected.

SQL>