Sunday, November 20, 2011

ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows

its for dual table (more then one row)

Even Oracle 10gR1 and above always makes sure that DUAL returns one and only one row even if it contains more than one row.
still you can get below error for more rows in dual table.


 SQL> drop table drop_test ; 
drop table drop_test 
* 
ERROR at line 1: 
ORA-00604: error occurred at recursive SQL level 1 
ORA-01422: exact fetch returns more than requested number of rows 


(note:- don't play with dual table in production database ever)

examples ;-


 SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 20 10:19:55 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys@orcl105 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> select sysdate from dual ;
SYSDATE
---------
20-NOV-11
SQL>
SQL> select * from dual ;
D
-
X
SQL>
SQL>
SQL>
SQL> insert into dual values ('H') ;
1 row created.
SQL> commit ;
Commit complete.
SQL>
SQL> create table dual_two_row as select * from dual ;
Table created.
SQL>
SQL>
SQL> select * from dual_two_row ;
D
-
X
H
SQL> select * from dual ;
D
-
X
SQL>
SQL>
SQL> create table drop_test (id number) ;
Table created.
SQL>
SQL> insert into drop_test values(5);
1 row created.
SQL> commit ;
Commit complete.
SQL>
SQL> drop table drop_test ;
drop table drop_test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
SQL>
SQL>
SQL> select * from dual ;
D
-
X
SQL> drop table dual_two_row ;
drop table dual_two_row
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
SQL> select * from dual ;
D
-
X
SQL> create table dual_new as select * from dual ;
Table created.
SQL>
SQL> select * from dual_new ;
D
-
X
H
SQL> delete from dual where dummy='H' ;
1 row deleted.
SQL> commit ;
Commit complete.
SQL>
SQL> drop table dual_two_row ;
Table dropped.
SQL>
SQL> drop table drop_test ;
Table dropped.
SQL>
SQL> drop table dual_new ;
Table dropped.
SQL>
SQL>
SQL> select * from dual ;
D
-
X
SQL>
SQL>
SQL>
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE  10.2.0.4.0   Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL>
SQL>


see more about dual table ...

Monday, November 14, 2011

ORA-00257: archiver error. Connect internal only,until freed.

*Cause: The archiver process received an error while trying to archive
a redo log. If the problem is not resolved soon, the database
will stop executing transactions. The most likely cause of this
message is the destination device is out of space to store the
redo log file.

*Action: Check archiver trace file for a detailed description
of the problem. Also verify that the
device specified in the initialization parameter
ARCHIVE_LOG_DEST is set up properly for archiving.


Explanation:-

The archiver process received an error while trying to archive a log.
The database will stop executing transactions.
The cause of this error is the archive destination device is out of space.
This will stop the system from working allowing only internal to connect.



solution:-

1)

Sql> conn / as sysdba

sql> archive log list;

---see the Archive Destination and
---increase the size of that location

suppose if archive destination is USE_DB_RECOVERY_FILE_DEST

then increase the size with following command.

SQL> alter system set db_recovery_file_dest_size=3G ;


2) backup your database and archive logs to somewhere else then delete archive log file via Rman

RMAN> connect target sys/sys@orcl as sysdba

RMAN> delete archivelog all ;


3) also you can configure

set the following parameters so that when the dest1 is full, archiving is automatically performed to the alternate dest2 :

log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=/other_dest_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'
db_recovery_file_dest='/u01/app/oracle/product/10.1.0/db_1/flash_recovery_area'
db_recovery_file_dest_size=2G

ORA-32004:obsolete and/or deprecated parameter(s) specified

Clue:= when you trying to startup your database you face this error
this means you set up a obsolete and/or deprecated parameter in database parameter file.
for this this error is showing .

so now you should check parameter file which is using when database is starting up.
and remove the obsolete and/or deprecated parameter then startup the database.


Now question is:- how can you find out of this obsolete and/or deprecated parameter?

Answer are :-

1)Look at the alert log of the associated instance (at the time that an instance startup is initiated)
it show the parameter with this "obsolete and/or deprecated" message.
like below

Deprecated system parameters with specified values:
(your Deprecated parameters)
End of deprecated system parameter listing

2) or if database is accessable then query this view V$OBSOLETE_PARAMETER

3) or read the document of the current database version and identified from the list of deprecated parameters

Sunday, November 13, 2011

Memory Notification: Library Cache Object loaded into SGA Heap size K exceeds notification threshold (2048K)

I found this message in my oracle database alert log file. my database version is 10.2.0.1 .

clue:-

This is for an undocumented parameters value. which parameter is

"_kgl_large_heap_warning_threshold"

This is new in 10g release 2 and in 10.2.0.1 default threshold value is 2Mb.
and in 10.2.0.2 and above default threshold value is 50Mb.

so, for avoiding this type of warning message from alert log file you have to configure in parameter
"_kgl_large_heap_warning_threshold" to a reasonable high value or zero . Value needs to be set in bytes.
or upgrate the Database version.

Saturday, October 22, 2011

Logical comparison of NULL = NULL And NULL != NULL in (SQL) oracle database is false ?

see the example


C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 22 13:15:50 2011

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

SQL>
SQL>
SQL>
SQL> conn sys@orcl105 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> begin
2 if null = null then dbms_output.put_line('TRUE');
3 elsif not (null = null) then dbms_output.put_line('FALSE');
4 else dbms_output.put_line('Not TRUE and not FALSE');
5 end if;
6 end;
7 /

Not TRUE and not FALSE

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf

1 begin
2 if null != null then dbms_output.put_line('TRUE');
3 elsif not (null != null) then dbms_output.put_line('FALSE');
4 else dbms_output.put_line('Not TRUE and not FALSE');
5 end if;
6* end;
SQL> /

Not TRUE and not FALSE

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>

see the following link
https://forums.oracle.com/forums/thread.jspa?messageID=4098609

Monday, October 17, 2011

ORA-09945: Unable to initialize the audit trail file

this error is showing when i am trying to start oracle database. solution is below.


SQL> startup

---it show this error

ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
ORA-09945: Unable to initialize the audit trail file
Linux-x86_64 Error: 28: No space left on device


Why :=
============

its because of full the Mount point where Oracle is installed .
your audit_trail_dest or audit trail destination is full for generation of audit file.


you can do :-
==============

1) check the space of mount point. like below.....

$ df -k

Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda1 9877432 9500268 0 100% /
none 3932160 1736 3930424 1% /dev/shm
/dev/sdf 30963708 9434056 19956788 33% /u02
/dev/sdb 433455904 203020 411234580 1% /u03


2) delete some unnecessary trace file or something from that fulled mount point.
then try to start the oracle database.

it is advisable, backup the files before they are delete since they might be useful in future.

you see here

Monday, October 10, 2011

How to see ORACLE_HOME OR ORACLE_BASE OR ORACLE_SID from command prompt?


On windows
=============

1)

C:\>echo %ORACLE_HOME%
%ORACLE_HOME%

C:\>
C:\>SET ORACLE_HOME=G:\oracle\product\10.2.0\db_1

C:\>
C:\>echo %ORACLE_HOME%

G:\oracle\product\10.2.0\db_1

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

C:\>echo %ORACLE_SID%
+ASMHALIM

C:\>
C:\>
C:\>echo %ORACLE_HOME%
G:\oracle\product\10.2.0\db_1

C:\>
C:\>
C:\>SQLPLUS /NOLOG

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 10 16:15:19 2011

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

SQL>


2)

RUN----->CMD----->regedit------>

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES



ON LINUX/UNIX
================


1) $env | grep ORACLE_HOME
$env | grep ORACLE_BASE
#env | grep ORACLE_SID


2) $ echo $ORACLE_HOME

for setting ORACLE_HOME

$ export ORACLE_HOME=

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>

Thursday, September 22, 2011

In 32-bit Linux operating system, Memory utilization limit for oracle database

In 32 bit Linux operating system, the default maximum size of the SGA is 1.7GB. For this SHMMAX value needed lager of 1.7GB .
On a 32-bit Linux operating system, without Physical Address Extension (PAE), the physical memory is divided into a 3GB user space and a 1GB kernel space. It is therefore possible to create a 2.7GB SGA, but you will need make several changes at the Linux operating system level by changing the mapped base. In the case of a 2.7GB SGA, you would want to set the SHMMAX parameter to 3GB.

Maximum value of the SHMMAX parameter is 4GB In 32-bit Linux operating system .

Wednesday, September 21, 2011

ORA-03115:unsupported network datatype or representation in toad 9.6

Cause: A user bind or define, or an Oracle function, is not supported by this heterogeneous Net8 connection.

Action: Upgrade the older version of Oracle and try again.


Problem:-
i am connecting remote oracle database 10g using oracle developer 6i home as a "installed clients" conncetion location in Toad.
its give me this above error, when i am trying to execute AWR report via toad.


Solution:
after then I change the conncetion location home to "oraDB10g_home" in replace of developer 6i home.
then above error is gone.

so this problem is happen for lower version of installed clients net8 connection home.

Thursday, September 15, 2011

ORA-28056:writing audit records to windows event log failed

This is because the Event Viewer log of windows is full
and could not write log anymore.

Solution:-

1) clear the event log

as follows

Control Panel->Administrative Tools->Event Viewer->
right click on specific event viewer then > Clear All Events

Note:- you can backup event viewer log. it is just log nothing else.

Wednesday, September 14, 2011

Oracle Streams and Oracle(CDC) Change Data Capture comparative description

Tom kytes says

think of streams like a brick.  think of CDC like a building made of brick.
streams can be used to build CDC.
CDC is built on top of streams (async CDC is anyway, sync CDC is trigger based).
they are complimentary, not really competing.


CDC

Streams

Definition

Change Data Capture is database functionality that enables capturing incremental changes with predefined API against an Oracle Database and to make the change data available for further use.

Streams provides a flow mechanism in which, the database changes flow in a streamed manner, hence the name Streams. It is a very broad and flexible way of capturing changes, moving them, and applying them.

Purpose

CDC is for monitoring changes in a database.
You can create subscriptions for applications so they can see only certain changed data.

For Information Sharing/Distribution

Streams are for replicating data between databases for more high availability. The stream routes published information to subscribed destinations. You can route the flow of data through multiple databases without having the changes applied at each intermediate server.

Mechanism

Source TABLE => Change Table => Subscriber view =>TARGET system

Change data capture does not depend on intermediate flat files to stage the data outside of the relational database. It captures the change data resulting from INSERT, UPDATE, and DELETE operations made to user tables. The change data is then stored in a database object called a change table, and the change data is made available to applications in a controlled way.

CATPURE => Staging => APPLY

Streams, keeps track of the entire database changes. It then stages those changes into queues and later moves them to the destination queues where they are applied to the destination database objects.You make changes to the data before it is applied at the destination. Database objects and data are kept synchronized at all of the databases in the replication environment.

DML and DDL changes

You can capture only DML changes CDC is *NOT* supported for doing DDL operation

You can also capture DDL changes.Note 238457.1 What DML and DDL is Captured by Streams

Usage

CDC most commonly used to capture transactional changes from an OLTP system and publish the changes to one or more subscription systems use Change Data Capture to simplify ET L (extraction and transportation of relational data) in data warehouse applications. The advantage of CDC is that it is intended for extracting data in a data-warehousing environment so you can process only the changed data not entire tables and makes the change data available for further use.

Used for the following:
Replicating data from one database to another
Message Queuing
Data Warehouse Loading.
Event management and notification.
Data provisioning in a grid environment
High availability during database upgrade, platform migration, and application upgrade.
Allows data to be transparently shared between both Oracle and non-Oracle data stores.

How does it work

Synchronous Change Data Capture uses triggers on the source database to capture change data.
Asynchronous Change Data Capture uses the redo log files. Change data is captured after a SQL statement that performs a DML operation is committed. In this mode, change data is not captured as part of the transaction that is modifying the source table, and therefore has no effect on that transaction. Asynchronous Change Data Capture is available with Oracle Enterprise Edition only not the standard edition.



Streams works by scanning through the redo logs and capturing changes that have been made to specified objects or schema's.





Oracle and OS/platform compatibility

The internal Change Data Capture (CDC) tasks of Streams Capture and Apply are
decoupled, thereby enabling a heterogeneous CDC setup with different operating
systems and Oracle versions. The propagation between source and target database
is a pure Streams implementation, enabling asynchronous CDC to leverage any existing Oracle9i Release 2 system as a source.
Asynchronous Change Data Capture (CDC) no longer requires the same operating
system for source and target. Furthermore, distributed asynchronous CDC
capabilities expands the reach of CDC source systems to include Oracle9i Release 2.

In case of Asynchronous AutoLog Mode the source database and the staging database must be running on the same hardware,operating system, and Oracle version.

The Distributed HotLog mode of Change Data Capture supports the use of different hardware platforms or operating systems (or both) for the source and staging databases..

For bidirectional/unidirectional Streams replication does not have any restriction on Oracle version or OS .
Both source and target can be on different platforms as well different Oracle base releases..


Operational Requirements for Downstream Capture
The following are operational requirements for using downstream capture:
■ The source database must be running at least Oracle Database 10g and the
downstream capture database must be running the same release of Oracle as the
source database or later.
■ The downstream database must be running Oracle Database 10g Release 2 to
configure real-time downstream capture. In this case, the source database must
be running Oracle Database 10g Release 1 or later.
■ The operating system on the source and downstream capture sites must be the
same, but the operating system release does not need to be the same. In
addition, the downstream sites can use a different directory structure from the source site.
■ The hardware architecture on the source and downstream capture sites must be the same. For example, a downstream capture configuration with a source
database on a 32-bit Sun system must have a downstream database that is
configured on a 32-bit Sun system. Other hardware elements, such as the number of CPUs, memory size, and storage configuration, can be different between the source and downstream sites.

With respect to Non Oracle Databases


Does not support changed tables and subscriber view to be present on non-Oracle Databases


Allows data to be shared between oracle and non-oracle data stores.

With respect to logical Standby


CDC Async Distributed Hotlog Mode is *NOT* supported on LOGICAL STANDBY
Logical standby as the source of Async CDC (in any mode)/Streams is not supported in any releases 9.2, 10.1, 10.2 and 11.1


Streams cannot be used in 9i and 10g with Logical Standby as logical standby cannot run jobs BUT in 11g you can use Streams with Logical Standby only in Combined Capture and Apply mode





Benefits

CDC offers cost savings by simplifying the extraction of change data from database as its part of Oracle 9i database and later versions.

CDC Captures change data resultant of DML operations including the before and after update values of an update operation.

Data changes are captured automatically to change table.Very friendly simple to use APIs to publish and subscribe to the changes.Can be scripted with very little effort.

Asynchronous CDC captures data with very little performance impact. Best of both worlds.Automatic purge of consumed or obsolete change data captured in change table.

CDC ensures that every subscriber sees all changes.Efficient tracking of multiple subscribers and provides a shared access to the changed data.

Transactional consistency for changes across multiple source tables is guaranteed. Transparently coordinates sharing of change data across users and applications

Integrated Feature of Oracle9i Database
No additional software to install. No special commands to learn.Takes advantage of reliability and security provided with Oracle9i Database.

Manageable
Provides maximum flexibility for configuration and administration with Oracle-supplied PL/SQL packages.

Provides wizards and monitoring capabilities for ease of use with Streams tool in Oracle Enterprise Manager.

Keeps users informed about their environment through Data dictionary view.

Performance
Hot mining of the online redo log reduces the latency of data capture.
Parallel capture and apply processes ensure maximum throughput for concurrent events

LIMITATION

Asynchronous mode CDC purely worked based on logged operations, so any non-logged DML operations are not captured.

Synchronous mode CDC does not support direct load insert.

CDC cannot be implemented on table with TDE (Transparent Data Encryption) enabled.


Asynchronous mode capture will not work without supplemental logging.

Although direct select is possible on change table but the extraction of the changed data is valid/supported only via subscriber views.

CDC is not a development solution to perform any validations or transformation or provide any application specific checks.

TDE columns are not supported by Oracle Streams in 9i and 10g but supported in 11g
TDE is supported is 11g for Asyncronous CDC as specified in the Datawarehouse guide:

Note that you cannot use any table that uses transparent data encryption as a source table for synchronous Change Data Capture. Asynchronous Change Data Capture supports transparent data encryption if both the source and staging databases have COMPATIBLE set to 11 or higher.











Change Data Capture will not encrypt the data in the change table. A user who wants to encrypt a column in the change table can manually use an ALTER TABLE statement to encrypt the column in the change table.


Oracle Streams provides database support for a wide variety of data types, but does not provide native support for data movement of some advanced data types. However, by using Extended Data type Support (EDS), you can take advantage of the flexibility of Streams to accommodate several more advanced data types. See

http://www.oracle.com/technology/deploy/availability/pdf/maa_edtsoverview.pdf

Note 238455.1 Streams DML Types Supported and Supported Data types

http://download.oracle.com/docs/cd/B19306_01/server.102/b14229/strms_over.htm

Reference

Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) Part Number B14223-02
Chapter 16 Change Data Capture


http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_cdc_cookbook_0206.pdf
http://www.oracle.com/technology/oramag/oracle/03-nov/o63tech_bi.html

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223.pdf


Wednesday, September 7, 2011

How to Check Oracle Version?

we can find oracle database installed version from two way . see follow

1) From inside oracle

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

a) CMD> sqlplus /nolog

b) SQL> SELECT * FROM v$version ;

C) SQL> SELECT VERSION FROM v$instance;

d) SQL> select * from product_component_version;

2) from OS

a)

G:\app\Administrator\product\11.1.0\db_1\OPatch> set Oracle_home=G:\app\Administrator\product\11.1.0\db_1

G:\app\Administrator\product\11.1.0\db_1\OPatch>opatch lsinventory


B) $ORACLE_HOME/bin/svrmgrl command=exit | grep "Release"

c) $ORACLE_HOME/bin/oracle | grep NLSRTL

d) from “sqlnet.log’ file .

Monday, September 5, 2011

How to skip a wrongly deleted datafile when you want to open a oracle database ?


Answer:-

You cannot drop a datafile without open state of database .

so you have to offline the datafile .

in Archive log mode
--------------------

SQL>alter database datafile '.../test.dbf' offline ;


in noarchive log mode
---------------------

SQL>alter database datafile '.../test.dbf' offline drop ;



It does nothing more than taking offline the file.
DROP option is mandatory in NOARCHIVELOG mode and is ignored in ARCHIVELOG mode.



we take datafile with "offline drop" then we can't back datafile online.
but You can do it until the offline SCN goes out of the online redo logs.
So how long depends on your logs size and workload.

examples:-

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> alter database datafile 11 offline drop;

Database altered.

SQL> recover datafile 11;
Media recovery complete.

SQL> alter database datafile 11 online;


Database altered.



Restrictions for Dropping Datafiles
---------------------------------------

The following are restrictions for dropping datafiles and tempfiles:

The database must be open.

If a datafile is not empty, it cannot be dropped.

If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.

You cannot drop the first or only datafile in a tablespace.

This means that DROP DATAFILE cannot be used with a bigfile tablespace.

You cannot drop datafiles in a read-only tablespace.

You cannot drop datafiles in the SYSTEM tablespace.

If a datafile in a locally managed tablespace is offline, it cannot be dropped.


more...

Monday, August 8, 2011

Update records in one table based on values of another table in oracle database

drop table emp_halim;

CREATE TABLE emp_halim AS SELECT * FROM emp;

SET DEFINE OFF;
Insert into EMP_HALIM
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO)
Values
(7999, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1300, NULL, 70);
Insert into EMP_HALIM
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO)
Values
(7989, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1300, NULL, 60);
Insert into EMP_HALIM
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO)
Values
(7979, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1300, NULL, 50);
COMMIT;

SELECT *
FROM emp_halim;

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

drop table dept_halim;

CREATE TABLE dept_halim AS SELECT * FROM dept;

SELECT *
FROM dept_halim;

UPDATE emp_halim a
SET ename = (SELECT dname
FROM dept_halim b
WHERE a.deptno = b.deptno)

rollback ;

UPDATE emp_halim a
SET ename = (SELECT dname
FROM dept_halim b
WHERE a.deptno = b.deptno)
WHERE EXISTS (SELECT dname
FROM dept_halim b
WHERE a.deptno = b.deptno);

rollback;





Live examples:--


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 12 18:27:34 2010

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

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> set linesize 1000
SQL> set pagesize 1000
SQL>
SQL> UPDATE emp_halim a
2 SET ename = (SELECT dname
3 FROM dept_halim b
4 /
FROM dept_halim b
*
ERROR at line 3:
ORA-00907: missing right parenthesis

SQL>
SQL> drop table emp_halim;

Table dropped.

SQL>
SQL>
SQL> CREATE TABLE emp_halim AS SELECT * FROM emp;

Table created.

SQL>
SQL>
SQL> SET DEFINE OFF;
SQL> Insert into EMP_HALIM
2 (EMPNO, ENAME, JOB, MGR, HIREDATE,
3 SAL, COMM, DEPTNO)
4 Values
5 (7999, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 1300, NULL, 70);

1 row created.

SQL> Insert into EMP_HALIM
2 (EMPNO, ENAME, JOB, MGR, HIREDATE,
3 SAL, COMM, DEPTNO)
4 Values
5 (7989, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 1300, NULL, 60);

1 row created.

SQL> Insert into EMP_HALIM
2 (EMPNO, ENAME, JOB, MGR, HIREDATE,
3 SAL, COMM, DEPTNO)
4 Values
5 (7979, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 1300, NULL, 50);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL> SELECT *
2 FROM emp_halim;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7999 halim CLERK 7782 23-JAN-82 1300 70
7989 halim CLERK 7782 23-JAN-82 1300 60
7979 halim CLERK 7782 23-JAN-82 1300 50

16 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table dept_halim;

Table dropped.

SQL>
SQL>
SQL> CREATE TABLE dept_halim AS SELECT * FROM dept;

Table created.

SQL>
SQL> SELECT *
2 FROM dept_halim;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL>
SQL>
SQL>
SQL> UPDATE emp_halim a
2 SET ename = (SELECT dname
3 FROM dept_halim b
4 WHERE a.deptno = b.deptno);

16 rows updated.

SQL>
SQL> rollback ;

Rollback complete.

SQL>
SQL>
SQL>
SQL> UPDATE emp_halim a
2 SET ename = (SELECT dname
3 FROM dept_halim b
4 WHERE a.deptno = b.deptno)
5 WHERE EXISTS (SELECT dname
6 FROM dept_halim b
7 WHERE a.deptno = b.deptno);

13 rows updated.

SQL>
SQL>
SQL>
SQL>
SQL>

Saturday, August 6, 2011

ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S.....SCOPE=MEMORY SID ='' in alert log file


In my alert log, it shows like below


ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_254872100321.halimdb.halimdba.COM','halimdbservice' SCOPE=MEMORY SID='halimdb1';

why it shows this command, i am sure nobody can execute this command in this Database.
so how it appear in alert log.


Answer:-
You must have run expdp or impdp recently. These tools change the service name.
Datapump doesn't tamper with existing services, it just creates
new services for its own queue operation.
You can also find similar statement in the alert log file after running the expdp command.
just test it .

ORA-1461 encountered when generating server alert SMG-3500 in oracle 10.2.0.3 RAC.

this is a bug of 10.2.0.3 version

After applying the 10.2.0.3 patchset the following errors may be reported:

ORA-1461 ENCOUNTERED WHEN GENERATING SERVER ALERT SMG-3500

ORA-01461: can bind a LONG value only for insert into a LONG column

Solutions :-

simply you can avoid it.
or
see metalink note 461911.1

Friday, August 5, 2011

oracle database alert log shows one of ASM Diskgroup Gets Mounted/Dismounted, is it normal behavior ?


such as alert log contents follow............

-------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
SUCCESS: diskgroup ASMDISKG4 was mounted
SUCCESS: diskgroup ASMDISKG4 was dismounted
SUCCESS: diskgroup ASMDISKG4 was mounted
SUCCESS: diskgroup ASMDISKG4 was dismounted
SUCCESS: diskgroup ASMDISKG4 was mounted
Fri Jun 10 05:15:40 2011
SUCCESS: diskgroup ASMDISKG4 was dismounted
Fri Jun 10 05:23:45 2011
Thread 1 advanced to log sequence 985477
Current log# 1 seq# 985477 mem# 0: +ASMDISKG1/halimdb/redo01.log
Current log# 1 seq# 985477 mem# 1: +ASMDISKG2/halimdb/redo01db.log
Fri Jun 10 05:23:46 2011
SUCCESS: diskgroup ASMDISKG4 was mounted
SUCCESS: diskgroup ASMDISKG4 was dismounted
SUCCESS: diskgroup ASMDISKG4 was mounted
SUCCESS: diskgroup ASMDISKG4 was dismounted
SUCCESS: diskgroup ASMDISKG4 was mounted
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------

Answer:-

This is expected behavior. The messages you see are not errors (that is why they are prefixed with "SUCCESS"). The database instance is creating and then closing archive logs one at a time in the "ASMDISKG4" disk group. Whenever a database closes its last file in a disk group, it dismounts the disk group. Whenever a database accesses a disk group when it does not have any other files open in the disk group, it mounts the disk group.


If you do not want to see the log messages frequently, you can put a mirrored control-file, or mirrored online redo, or dummy online tablespace data file on the diskgroup. This message will not be output frequently in this case. Because CKPT keeps opening a control-file, LGWR keeps opening an online redo, DBW keeps opening an online tablespace data file. So, the diskgroup is not dismounted until the database shutdown.

ORA-00020: maximum number of processes () exceeded ORA-15055: unable to connect to ASM instance ORA-17503: ksfdopn:

If you face following type of error in alert log file and after that database is going to be hang...
and this problem is happen after some days or time

Errors in file /DB01/application1/oracle/admin/halimdb/bdump/halimdb_arc0_69854.trc:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '+ASMDISK1/halimdb/redo04.log'
ORA-17503: ksfdopn:2 Failed to open file +ASMDISK1/halimdb/redo04.log
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded
ORA-00312: online log 4 thread 1: '+ASMDISK1/halimdb/redo04.log'
ORA-17503: ksfdopn:2 Failed to open file +ASMDISK1/halimdb/redo04.log
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded

cause is :-
========
The Oracle parameter PROCESSES has been exceeded as a result of multiple database instances connecting to the ASM instance. The default value (often 40) is insufficient to support more than one database connecting to the ASM instance


Then solution is .....
==============
[note:-sometime we do mistake only just checking database processes parameter value,
in this case we have to check ALL ASM instance processes parameter value ]


1) connect to the ASM instance one (if more than one instance and do it for all ASM instance)

2) check the processes parameter value of ASM instance

SQL> show parameter processes

3) check the resource limit of processes

sql> select * from v$resource_limit
where RESOURCE_NAME = 'processes';

if v$resource_limit.MAX_UTILIZATION is reach the processes parameter value then

4) increase the processes parameter value, like

SQL> alter system set processes=300 scope=spfile sid=’+ASM1';

[NOTE : as oracle says......
Processes = 25 + 15n, where n is the number of instances on the box using ASM for their storage.
This formula is for a basic instance and does not accomodate for
* Multiple ARCH processes
* Multiple LGWR processes
Should the ORA-20 occur even after implementing this formula ... add additional for any multiples of these background processes ]


5) restart (bounce) the database and ASM instance as well .

Tuesday, August 2, 2011

REGEXP_LIKE examples

A very simple example explaining a few functionalities using  REGEXP_LIKE operator. Much more variations/functionality can be achieved using different-2 search conditions. 

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Aug 2 15:49:46 2011

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

SQL> conn scott@test123
Enter password:
ERROR:
ORA-28000: the account is locked


SQL>
SQL>
SQL> conn scott@orcl105
Enter password:
Connected.
SQL>
SQL>
SQL> drop table ta_regexp_like;
drop table ta_regexp_like
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table ta_regexp_like
2 (
3 contact_info varchar2(100)
4 )
5 /

Table created.

SQL>
SQL>
SQL> insert into ta_regexp_like values('Contact number for smith is 238-564-7645');

1 row created.

SQL> insert into ta_regexp_like values('Contact number for Adam is 22-269-45');

1 row created.

SQL> insert into ta_regexp_like values('Contact number for Sumit is 64-75');

1 row created.

SQL> insert into ta_regexp_like values('Contact number for Rajeev is 4564-564-7');

1 row created.

SQL> insert into ta_regexp_like values('Contact number for Rajeev is sdas-767-9');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL> select contact_info
2 from ta_regexp_like
3 where regexp_like(contact_info,'..-...')
4 /

CONTACT_INFO
--------------------------------------------------------------------------------
Contact number for smith is 238-564-7645
Contact number for Adam is 22-269-45
Contact number for Rajeev is 4564-564-7
Contact number for Rajeev is sdas-767-9

SQL>
SQL>
SQL>

Query not to select sdas-564-7 and select only those rows having
just digits on both sides of '-'

SQL>
SQL>
SQL> select contact_info
2 from ta_regexp_like
3 where regexp_like(contact_info,'[0-9]{2}-[0-9]{3}')
4 /

CONTACT_INFO
--------------------------------------------------------------------------------
Contact number for smith is 238-564-7645
Contact number for Adam is 22-269-45
Contact number for Rajeev is 4564-564-7

SQL>
SQL>
SQL>