Wednesday 18 December 2013

Convert Non-ASM Database to ASM Database in oracle Using RMAN

Assume that you have already created an ASM instance in a disk group.
1. Install ASM binaries.
2. Configure ASM & create 2 Disk groups DATA & FRA & assign disks for the respective disk groups.
3. Keep the ASM Instance UP.



Step by Step Configuring ASM in Oracle 10g.
$ export ORACLE_SID=PROD

$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 15 11:30:10 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>

Make the necessary changes:--
SQL> alter system set control_files='+DATA' scope=spfile;
SQL> alter system set db_create_file_dest='+DATA' scope=spfile;
SQL> alter system set db_recovery_file_dest='+DATA' scope=spfile;

SQL> shutdown immediate;

Connect the RMAN & restore the controlfile into the new location from the original location

$ export ORACLE_SID=Prod
$ rman target
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (not started)


RMAN> startup nomount
Oracle instance started


RMAN>Restore controlfile from '\u01\product\10.2.0\oradata\prd\ctl_prd01.ctl' ;
RMAN> ALTER DATABASE MOUNT ;
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA' ;

Switch the database to the copy created using the following RMAN command

RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE;

Using SQL*Plus to migrate flashback logs.

SQL> ALTER DATABASE FLASHBACK OFF;
SQL> select flashback_on from v$database;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS


Convert Redo log files from Non-ASM to ASM
SQL> create temporary tablespace temp1 tempfile '+DATA';
Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
Database altered.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Create new Redo logs in ASM Diskgroup and delete the old ones.

SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 UNUSED
4 UNUSED

SQL> alter system switch logfile;
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 prod (thread 1)
ORA-00312: online log 1 thread 1:
'\U01\PRODUCT\10.2.0\ORADATA\PROD\REDO01.LOG'

Note: Need to make sure that the redo logs groups that are dropped are not CURRENT or ACTIVE

SQL> alter system checkpoint global;
System altered.
SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 10m ;
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;
SQL>alter database add logfile group 2 size 10m ;
SQL>alter system checkpoint global;
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> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 UNUSED
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 size 10m ;
SQL> select group#,status from v$log;
GROUP# STATUS
------- ------------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
SQL> alter database drop logfile group 4;
SQL> alter database add logfile group 4 size 10m ;
SQL> select group#,status from v$log;
GROUP# STATUS
------- ----------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED


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

Create spfile for ASM

SQL> create pfile from spfile ;
SQL> create spfile='+DATA/SPFILEASM.ORA' FROM pfile;

Monday 16 December 2013

ORA-600 Metalink-Oracle Doc ID's

ORA-600 Errors 1 to 3000
==============================
Note 138300.1 "ORA-600 [105]"
Note 263295.1 "ORA-600 [106]"
Note 28104.1 "ORA-600 [504]"
Note 138871.1 "ORA-600 [510]"
Note 138888.1 "ORA-600 [525]"
Note 138939.1 "ORA-600 [711]"
Note 39308.1 "ORA-600 [723]"
Note 31056.1 "ORA-600 [729]"
Note 131490.1 "ORA-600 [733]"
Note 217860.1 "ORA-600 [1013]"
Note 138123.1 "ORA-600 [1100]"
Note 41767.1 "ORA-600 [1113]"
Note 40514.1 "ORA-600 [1114]"
Note 137262.1 "ORA-600 [1115]"
Note 66387.1 "ORA-600 [1158]"
Note 138354.1 "ORA-600 [1236]"
Note 28045.1 "ORA-600 [2103]"
Note 28929.1 "ORA-600 [2662]"
Note 31057.1 "ORA-600 [2845]"
Note 138733.1 "ORA-600 [2865]"


ORA-600 Errors 3001 to 6000
=============================

Note 30866.1 "ORA-600 [3020]"
Note 93665.1 "ORA-600 [3668]"
Note 47456.1 "ORA-600 [4000]"
Note 96642.1 "ORA-600 [4036]"
Note 43914.1 "ORA-600 [4137]"
Note 28226.1 "ORA-600 [4146]"
Note 39282.1 "ORA-600 [4193]"
Note 39283.1 "ORA-600 [4194]"
Note 138822.1 "ORA-600 [4400]"
Note 29702.1 "ORA-600 [4414]"
Note 138836.1 "ORA-600 [4454]"
Note 39553.1 "ORA-600 [4511]"
Note 73455.1 "ORA-600 [4512]"
Note 27955.1 "ORA-600 [4519]"
Note 204536.1 "ORA-600 [4820]"
Note 41840.1 "ORA-600 [4882]"


ORA-600 Errors 6001 to 9000
=============================

Note 47449.1 "ORA-600 [6002]"
Note 116552.1 "ORA-600 [6034]"
Note 40640.1 "ORA-600 [6101]"
Note 99300.1 "ORA-600 [6122]"
Note 138913.1 "ORA-600 [6193]"
Note 39399.1 "ORA-600 [6731]"
Note 41719.1 "ORA-600 [6856]"


ORA-600 Errors 9001 to 15000
=============================

Note 138325.1 "ORA-600 [12209]"
Note 33174.1 "ORA-600 [12235]"
Note 138332.1 "ORA-600 [12261]"
Note 138340.1 "ORA-600 [12304]"
Note 35928.1 "ORA-600 [12333]"
Note 28229.1 "ORA-600 [12700]"
Note 67496.1 "ORA-600 [13009]"
Note:28185.1 "ORA-600 [13013]"


ORA-600 Errors 15001 to 17000
==============================

Note 138428.1 "ORA-600 [15160]"
Note 138430.1 "ORA-600 [15201]"
Note 138431.1 "ORA-600 [15203]"
Note 131186.1 "ORA-600 [15212]"
Note 137266.1 "ORA-600 [15419]"
Note 216277.1 "ORA-600 [15456]"
Note 138457.1 "ORA-600 [15709]"
Note 67490.1 "ORA-600 [15851]"
Note 76528.1 "ORA-600 [15868]"
Note 138499.1 "ORA-600 [16201]"
Note 106607.1 "ORA-600 [16365]"
Note 138523.1 "ORA-600 [16515]"
Note 138526.1 "ORA-600 [16607]"


ORA-600 Errors 17001 to 30000
==============================

Note 138537.1 "ORA-600 [17003]"
Note 138541.1 "ORA-600 [17012]"
Note 41472.1 "ORA-600 [17034]"
Note 138554.1 "ORA-600 [17059]"
Note 39616.1 "ORA-600 [17069]"
Note 29616.1 "ORA-600 [17090]"
Note 138565.1 "ORA-600 [17099]"
Note 47411.1 "ORA-600 [17112]"
Note 39453.1 "ORA-600 [17113]"
Note 34782.1 "ORA-600 [17114]"
Note 138576.1 "ORA-600 [17128]"
Note 138580.1 "ORA-600 [17147]"
Note 34781.1 "ORA-600 [17148]"
Note 138586.1 "ORA-600 [17172]"
Note 263110.1 "ORA-600 [17175]"
Note 34779.1 "ORA-600 [17182]"
Note 45725.1 "ORA-600 [17271]"
Note 138597.1 "ORA-600 [17274]"
Note 134139.1 "ORA-600 [17280]"
Note 39361.1 "ORA-600 [17281]"
Note 138602.1 "ORA-600 [17285]"
Note 138621.1 "ORA-600 [17585]"
Note 138640.1 "ORA-600 [18209]"
Note 216278.1 "ORA-600 [18261]"
Note 138678.1 "ORA-600 [20084]"
Note 100073.1 "ORA-600 [25012]"


ORA-600 Errors kc
==================================

Note 138981.1 "ORA-600 [kcbgcur_2]"
Note 70097.1 "ORA-600 [kcbgcur_3]"
Note 114058.1 "ORA-600 [kcbgcur_9]"
Note 138990.1 "ORA-600 [kcbgtcr_4]"
Note 138991.1 "ORA-600 [kcbgtcr_5]"
Note 261264.1 "ORA-600 [kcbgtcr]"
Note 248874.1 "ORA-600 [kcbgtcr_6]"
Note 233612.1 "ORA-600 [kcbgtcr_12]"
Note 204512.1 "ORA-600 [kcbnew_3]"
Note 216104.1 "ORA-600 [kcbrbo1]"
Note 139011.1 "ORA-600 [kcbzpb_1]"
Note 139012.1 "ORA-600 [kcbzpb_2]"
Note 229467.1 "ORA-600 [kcbzwb_4]"
Note 139013.1 "ORA-600 [kccsbck_first]"
Note 216108.1 "ORA-600 [kcllcu_0]"
Note 263225.1 "ORA-600 [kclwcrs_15]"
Note 76434.1 "ORA-600 [kcoapl_blkchk]"
Note 248718.1 "ORA-600 [kcratr1_lostwrt]"


ORA-600 Errors kd to kw
=====================================

Note 139037.1 "ORA-600 [kdddgb2]"
Note 139042.1 "ORA-600 [kdisle:nrows]"
Note 139051.1 "ORA-600 [kghalo2]"
Note 139052.1 "ORA-600 [kghasp1]"
Note 139066.1 "ORA-600 [kghxhdr1]"
Note 229809.1 "ORA-600 [kgliep_1]"
Note 66501.1 "ORA-600 [kkrqtab2]"
Note 139095.1 "ORA-600 [kkslgop1]"
Note 139116.1 "ORA-600 [kohdtf048]"
Note 264061.1 "ORA-600 [kqludp2]"
Note 139162.1 "ORA-600 [kssrmp1]"
Note 247822.1 "ORA-600 [ksmals]"
Note 139153.1 "ORA-600 [ksmguard2]"
Note 233864.1 "ORA-600 [kteuproptime-2]"
Note 139180.1 "ORA-600 [ktsgsp5]"
Note 139193.1 "ORA-600 [kttmupd_segment-]"
Note 228480.1 "ORA-600 [kwqitnmptme:read]"
Note 228480.1 "ORA-600 [kwqitnmptme:ready]"
Note 228364.1 "ORA-600 [kwqitnmptme:wait]"


ORA-600 Errors q to z
=====================================

Note 248095.1 "ORA-600 [qctcte1]"
Note 216273.1 "ORA-600 [qctstc2o1]"
Note 209363.1 "ORA-600 [qerrmofbu1]"
Note 237598.1 "ORA-600 [qertqtableallocate2]"
Note 226887.1 "ORA-600 [qkagby4]"
Note 222876.1 "ORA-600 [qmxiunppacked2]"
Note 244365.1 "ORA-600 [rworupo.1]"
Note 139263.1 "ORA-600 [srsget_1]"
Note 260951.1 "ORA-600 [ttcgcshnd]"
Note 216452.1 "ORA-600 [ttcgcshnd-1]"
Note 216453.1 "ORA-600 [ttcgcshnd-2]"

Wednesday 11 December 2013

Steps for performing database cloning using hot backup


 Below steps helps you in performing database cloning using hot backup
...

Assumptions:

1. directory structure is different in both source and target servers
2. Oracle version : 10.2.0.5
3. OS version : Linux 5
4. target database name is same as source database name
5. DATABASE SHOULD BE IN ARCHIVEMODE

step 1 : Take the hot backup of source database

If the souce database in not in Archivemode, alter database to archivemode.

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

sql> alter database begin backup;
 
Check the status whether the datafiles are in backup mode.
select * from v$backup;
FILE# STATUS CHANGE# TIME
===========================
1 ACTIVE 000007 22-DEC-11
2 ACTIVE 000007 22-DEC-11

 
 
 $ copy datafiles to backup location

select name from v$datafile;
NAME
================================================
/oradata/test123/system01.dbf
/oradata/test123/undotbs01.dbf
SQL>exit
$ cd /u04/app/oracle/product/10.2.0/oradata/test/
$ ls -lrt
-rw-r-- 1 oracle staff 209723392 DEC 22 10:00 undotbs01.dbf
-rw-r-- 1 oracle staff 429924352 DEC 22 10:00 system01.dbf
$ cp *.dbf /oradata/clonedb/data

 sql> alter database end backup;

step 2 : Take controlfile trace and pfile or spfile (that was using by the source database)

step 3 : Install Oracle software on another machine (choose “Install only” option in OUI). Don’t create any database

step 4 : Copy all the files (including trace file and pfile or spfile) from source server to target server either using FTP or rcp
Note: Not necessary in copying control files and redologfiles

step 5 : Place pfile or spfile in “dbs” directory on target

step 6 : Copy the remaining files to their respective locations (If any directories are missing, do create them)

step 7 : Connect as sysdba and Startup the database in nomount stage

step 8 : Edit the trace file (that was copied) and generate a create controlfile script from it.
Modify the script and specify the new locations of the files.

step 9 : Execute the controlfile script which will create controlfiles in the location specified in CONTROL_FILES parameter.
Once control files are created, database will be forwarded to MOUNT state.

sql> @create_controlfile.sql

step 10 : Finally, Open the database with resetlogs option

sql> startup nomount

sql> alter database mount;

sql> recover database using backup controlfile until cancel;

sql> alter database open resetlogs;



Tuesday 3 December 2013

ERROR 1133 (42000): Can't find any matching row in the user table

Error :--
 
mysql>grant all privileges on *.* to root@'%' with grant option;
ERROR 1133 (42000): Can't find any matching row in the user table
 
 
Solution:--
 
Make root user as password protected..
 
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root123');
 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root123' WITH GRANT OPTION;
Flush privileges;

Tuesday 26 November 2013

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

Error:--


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 11/20/2013 05:27:58
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file +DG_ARCH/test/archivelog/2013_11_20/thread_2_seq_199159.7269.832482119
ORA-17503: ksfdopn:2 Failed to open file +DG_ARCH/test/archivelog/2013_11_20/thread_2_seq_199159.7269.832482119
ORA-15012: ASM file '+DG_ARCH/test/archivelog/2013_11_20/thread_2_seq_199159.7269.832482119' does not exist

Cause:
The archived log was not found. The repository thinks it does exist. If the archived log has in fact been lost and there is no backup, then the database is no longer recoverable across the point in time covered by the archived log.
This may occur because the archived log was removed by an outside utility without updating the repository.

Fixed
1. Perform a crosscheck, run the following command from within RMAN:
RMAN> change archivelog all crosscheck;
2. Perform a full backup of the database at this point.


Tuesday 8 October 2013

ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated

Issue:--

Errors in file /opt/oracle/admin/ABC/bdump/abc_j000_20902.trc:
ORA-12012: error on auto execute of job 2
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 3588
ORA-06512: at "PERFSTAT.STATSPACK", line 5300
ORA-06512: at "PERFSTAT.STATSPACK", line 104
ORA-06512: at line 1


Solution :--

SQL> alter table perfstat.STATS$SQL_SUMMARY disable constraint STATS$SQL_SUMMARY_PK;

Table altered.

SQL> exec statspack.snap;

PL/SQL procedure successfully completed.
SQL>

Thursday 19 September 2013

Setting password for mysql user in .my.cnf

Sometimes you want automated access for root on your MySQL database. One way of accomplishing that is by doing this:

cd /etc
ls -lrt my.cnf
chmod 700 .my.cnf

And put in it:
[client]
user=root
password=root123


And jut type MySQL u will able to login directly in MySQL prompt ...

Wednesday 31 July 2013

Communication Errors and Aborted Connections in MySQL

If connection problems occur such as communication errors or aborted connections, use these sources of information to diagnose problems:
The error log. See "The Error Log”
If you start the server with the --log-warnings option, you might find messages like this in your error log:

130728  4:58:05 [Warning] Aborted connection 1023 to db: 'xyz' user: 'opiuyijuy' host: 'abcd' (Got an error reading communication packets)
130728  4:58:07 [Warning] Aborted connection 2342 to db: 'mnb user: 'cvbnmxz' host: 'abcd' (Got an error reading communication packets)


If a client successfully connects but later disconnects improperly or is terminated, the server increments the Aborted_clients status variable, and logs an Aborted connection message to the error log.

The cause can be any of the following:
==>The client program did not call mysql_close() before exiting.
==>The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.
==>The client program ended abruptly in the middle of a data transfer.

If a client is unable even to connect, the server increments the Aborted_connects status variable.Unsuccessful connection attempts can occur for the following reasons:
==>A client does not have privileges to connect to a database.
==>A client uses an incorrect password.
==>A connection packet does not contain the right information.
==>It takes more than connect_timeout seconds to get a connect packet. See Section 5.1.4, “Server System Variables”.

If these kinds of things happen, it might indicate that someone is trying to break into your server! Messages for these types of problems are logged to the general query log if it is enabled.Other reasons for problems with aborted clients or aborted connections:
==>The max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld.“Packet Too Large”.
==>Use of Ethernet protocol with Linux, both half and full duplex. Many Linux Ethernet drivers have this bug.You should test for this bug by transferring a huge file using FTP between the client and server machines. If a transfer goes in burst-pause-burst-pause mode, you are experiencing a Linux duplex syndrome. Switch the duplex mode for both your network card and hub/switch to either full duplex or to half duplex and test the results to determine the best setting.
==>A problem with the thread library that causes interrupts on reads.
==>Badly configured TCP/IP.

Faulty Ethernets, hubs, switches, cables, and so forth. This can be diagnosed properly only by replacing hardware.

Tuesday 23 July 2013

ORA-00600: internal error code, arguments: [17059]

Getting ORA-600 [17059] on the database alert log file

Problem Explanation:

ORA-600 [17059] is a memory corruption error that occurs while building a table to hold the list of child cursor dependencies relating to a given parent cursor and we exceed the maximum possible size of the table.
identify why so many child cursors have been created, can you please check V$SQLAREA for a statement using a excessive number of child cursors, e.g.:

connect / as sysdba
spool cursor_info.txt
select version_count, sql_id, sql_text from v$sqlarea
where version_count > 5000 order by version_count;

and then for the statement with the largest version count, identify why they are not getting shared from V$SQL_SHARED_CURSOR, e.g.:

select * from v$sql_shared_cursor where address in (select address from v$sqlarea where sql_id = '<SQL_ID from above>');


Alert Log File shows;

Mon Jan 18 16:32:47 2010
Errors in file /orasys/ipay/udump/ipay_ora_24069.trc:
ORA-00600: internal error code, arguments: [17059], [0x4633429D8], [], [], [], [], [], []
Mon Jan 18 16:32:52 2010
Errors in file /orasys/ipay/udump/ipay_ora_24071.trc:
ORA-00600: internal error code, arguments: [17059], [0x4633429D8], [], [], [], [], [], []
Mon Jan 18 16:32:55 2010
Errors in file /orasys/ipay/udump/ipay_ora_24117.trc:
ORA-00600: internal error code, arguments: [17059], [0x4633429D8], [], [], [], [], [], []
Mon Jan 18 16:32:58 2010
Errors in file /orasys/ipay/udump/ipay_ora_24073.trc:


Trace Files Shows:

parent=463342af0 maxchild=32770 plk=46e81ca48 ppn=n
cursor instantiation=ffffffff7a730318 used=1263817964
child#32769(0) pcs=40d6be768
 clk=0 ci=0 pn=0 ctx=0
kgsccflg=0 llk[ffffffff7a730320,ffffffff7a730320] idx=0
xscflg=28 fl2=0 fl3=82080 fl4=0
sharing failure(s)=400
No bind info: cannot access child information block
and the oacdefs are not stored in the instantiation
Frames pfr 0 siz=0 efr 0 siz=0
Cursor frame dump
Session cached cursors


Workaround:
you can stop the ORA-600 [17059] errors occurring by flushing the shared pool using:
connect / as sysdba
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;
exit


Solution:
Patch 5705795 would rectify the issue however it conflicts with patch 4367986.
A merged version of the patches is available for download from My Oracle Support as patch 7007477

Monday 22 July 2013

ora.diskmon OFFLINE status in oracle 11R2

After installation of the 11.2.0.3 GRID Infrastructure release started to look for new stuff. The first think I noticed was the offline status of diskmon. When no Exedata is used diskmon will be offline by default.

Diskmon is used for Exadata fencing.

crsctl stat res -t -w "STATE = OFFLINE"
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.diskmon
               OFFLINE OFFLINE      test_abc1
               OFFLINE OFFLINE      test_abc2

Tuesday 16 July 2013

ORA-27300: OS system dependent operation:fork failed with status: 2


When operating system is encountering with some unknown error like insufficient space in temp Area or swap Area or insufficient system resources then Oracle throws following errors.

Error:
ORA-27300: OS system dependent operation:fork failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: skgpspawn5

Solution:
On System: maxproc 128 to 16384             

ora11g@abc_test#  lsattr -E -l sys0 | grep -i maxuproc
maxuproc        16384              Maximum number of PROCESSES allowed per user      True


On DB: process 150 to 300

SQL> show parameter proce
processes   integer     300

Thursday 27 June 2013

ORA-00600: internal error code, arguments: [ktrget2:kcbz_objdchk]

Error:==
ORA-00600:  [ktrget2:kcbz_objdchk]

select TIMESLOT_emp,TIMESLOT2US_imp from emp ,emp a, b,timeslot
where usedby2emp=a.empid
and uses2emp=b.empid
and a.emp2emptype=0000010
and b.emp2emptype=0000009
and TIMESLOT_emp=a.empid
and TIMESLOT_imp=b.empid
group by TIMESLOT_emp,TIMESLOT_imp
having count(*)>1;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktrget2:kcbz_objdchk], [12], [0],[31], [], [], [], [], [], [], [], []






Solution :===

flush the shared pool 3 times :-
alter system flush shared_pool

Please run following query to check which objects the block belongs to:
SQL> SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE relative_fno = 26
AND 58378 between block_id AND block_id + blocks - 1;


TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
------------------------------ ------------------ ------------------------------ ---------------------------------------------------------------------------------
USR INDEX CMD EMP_CC_FK_I


SELECT object_id, data_object_id, status FROM dba_objects WHERE owner = 'CMD AND object_name = 'EMP_CC_FK_I'
AND object_type = 'INDEX';


OBJECT_ID DATA_OBJECT_ID STATUS
---------- -------------- -------
244663 1156881 VALID


Please rebuild the index:
SQL> ALTER INDEX cmd.emp_cc_fk_i REBUILD ONLINE;

Than analyze the index again to check it ORA-8103 are solved:

SQL> ANALYZE INDEX cmd.emp_cc_fk_i VALIDATE STRUCTURE;

after both the (Rebuild & Analyze) complete please try again same SQL QUERY.

Friday 21 June 2013

Create/Drop database link from another schema

Create DB Link from another schema:-

SQL> CREATE or replace PROCEDURE DATALOAD.create_db_link AS
 BEGIN
 EXECUTE IMMEDIATE 'create database link VSNLDEV1 connect to DATALOAD identified by xxxxx using ''CRAMER''';
 END create_db_link;
 /

Procedure created.

SQL> show user
 USER is "SYS"



SQL> exec DATALOAD.create_db_link
PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='DATALOAD';
OWNER                          DB_LINK              USERNAME             HOST                 CREATED
 ------------------------------ -------------------- -------------------- -------------------- --------
 DATALOAD                       VSNLDEV1             CRAMER               VSNLDEV1             13-06-08



SQL> drop database link DATALOAD.VSNLDEV1;
 drop database link DATALOAD.VSNLDEV1
 *
 ERROR at line 1:
 ORA-02024: database link not found



Drop DB Link from another schema:-

SQL> CREATE PROCEDURE DATALOAD.drop_db_link AS
 BEGIN
 EXECUTE IMMEDIATE 'drop database link VSNLDEV1';
 END drop_db_link;
 /

Procedure created.

SQL> exec DATALOAD.drop_db_link
PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='DATALOAD';
no rows selected

SQL>

For dropping multiple database links:-

 

ERROR 11.2.0\client_1\bin/Emctl Common.pm line 598.

Get below error for start/status emctl in oracle

Error:-

C:\Users\hyporacle>emctl status dbconsole
Can't locate CompEMdbconsole.pm in @INC (@INC contains: %s_javapOracleHome%/sysman/admin/scripts %s_javapOracleHome%/bin D:\win64_11gR2_client\product
\11.2.0\client_1\\perl\bin D:\win64_11gR2_client\product\11.2.0\client_1\perl\lib D:\win64_11gR2_client\product\11.2.0\client_1\perl\lib
0.0 D:\win64_11gR2_client\product\11.2.0\client_1\perl\lib\site_perl D:\win64_11gR2_client\product\11.2.0\client_1\perl\site\lib D:\win64_11gR2_client
\product\11.2.0\client_1\sysman\admin\scripts D:\win64_11gR2_client\product\11.2.0\client_1\sysman\admin\scripts\Net-DNS-0.48\lib D:\win64_11gR2_clien
t\product\11.2.0\client_1\bin D:/win64_11gR2_client/product/11.2.0/client_1/perl/lib D:/win64_11gR2_client/product/11.2.0/client_1/perl/site/lib .) at
 D:\win64_11gR2_client\product\11.2.0\client_1\bin/EmctlCommon.pm line 598.

\site_perl/5.1

Solution:-

Please set ORACLE_SID and ORACLE_HOME then re-run emctl status dbconsole from <ORACLE_HOME\BIN> location


Tuesday 11 June 2013

Oracle Environment variable -->> ORACLE_HOME


What is ORACLE_HOME used for?

* The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.
* The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.
* This directory can be used by any user who wants to use the particular database.
* If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.
* Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.


What is ORACLE_BASE used for?

* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.
* Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.


Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is

user_home_directory/app/username.

How to check if ORACLE_HOME is set already?

On Unix/Linux Systems:
Basically, before or after the Oracle Database is installed, the oracle user profile, the environment variable file, is prepared where all the required environment variables for Oracle are set. i.e. ORACLE_BASE, ORACLE_HOME, ORACLE_SID,PATH, LD_LIBRARY_PATH, NLS_LANG, etc.
The user profile file can be
.bash_profile - Bash Shell
.profile - Bourne Shell or Korn shell
.login ­- C Shell


Note: This user profile file will be under user’s home directory i.e. $HOME/.bash_profile
To check specific environment variable set:

$ echo $ORACLE_HOME
To check all the environment variables set:
$ env
On Windows Systems:
To check specific environment variable set:
C:\> set ORACLE_HOME
OR
C:\echo %ORACLE_HOME%
To check all the environment variables set:
C:\> set
Or
C:\> env

Other way, to check the ORACLE_HOME, is as follows.

Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLE
i.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

How to check using sqlplus command:

To find the ORACLE_HOME path in Oracle Database
How to set the ORACLE_HOME environment variable?

On Unix/Linux Systems:

Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profile
ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME

Source the user profile as follows:
Bash shell:
$ . ./.bash_profile
Bourne shell or Korn shell:
$ . ./.profile
C shell:
% source ./.login
If no profile file is set with environment variables, then physically also be set as follows:
Bourne, Bash, or Korn shell:
$ ORACLE_BASE=/oracle/app
$ export ORACLE_BASE
$ ORACLE_HOME=$ORACLE_BASE/product/10.2.0
$ export ORACLE_HOME

C Shell:
% setenv ORACLE_BASE /oracle/app
% setenv ORACLE_HOME /oracle/app/product/10.2.0


On Windows Systems:

My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)

After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.
Another way to physically set the variables as follow at the DOS prompt:
C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0
C:\> echo %ORACLE_HOME%


Note: I would suggest to refer the Oracle Documentation on Installation where these environment variables are discussed and explained with the enough information.

Monday 22 April 2013

EXPDP with oracle 10g & oracle 11g with Advanced Compression

Datapump in 11g has a good feature to reduce size of exports and resources used on machines and tapes by compressing the dumps as and when the export happens..
In version 10g, datapump did not have the compress option for data only the default option to compress METADATA only.
Example in 10g -
TEST schema using datapump -

nohup expdp \'/ as sysdba\' schemas=ACE directory=CRAMERDATA_EXP
DUMPFILE=ACESCHEMABK_22-apr13.dmp LOGFILE=ACESCHEMABK_22-apr13.log &

Export took   50 minutes.
Export dump size was 20 gb.
The same data was exported in 11g -
Only difference was adding the following bit to the expdp command in 11g  > compression=all

nohup expdp \'/ as sysdba\' schemas=ACE directory=CRAMERDATA_EXP
DUMPFILE=ACESCHEMABK_22-apr13.dmp LOGFILE=ACESCHEMABK_22-apr13.log compression=all &

Export took  15 minutes.
Export dump size 6 gb


In 11g it took just over half the time and saved 7 times the space which will tremendously reduce the amount of data moved off to tape on a daily basis especially in some databases where exports are taken daily and moved to tape.
Also there is no need to use unix pipes or uncompress dumps before commencing the import as Oracle takes care of that.

The compression clause can be specified at the tablespace, table or partition level with the following options:
NOCOMPRESS - The table or partition is not compressed. This is the default action when no compression clause is specified.
COMPRESS - This option is considered suitable for data warehouse systems. Compression is enabled on the table or partition during direct-path inserts only.
COMPRESS FOR DIRECT_LOAD OPERATIONS - This option has the same affect as the simple COMPRESS keyword.
COMPRESS FOR ALL OPERATIONS - This option is considered suitable for OLTP systems. As the name implies, this option enables compression for all operations, including regular DML statements. This option requires the COMPATIBLE initialization parameter to be set to 11.1.0 or higher. In 11gR2 this option has been renamed to COMPRESS FOR OLTP and the original name has been deprecated.

 

Friday 19 April 2013

ORA-16401: archivelog rejected by RFS

How do I fix the error "ORA-16401: archivelog rejected by RFS"?


Errors in file /oracle/app/oracle/admin/nms/udump/drnms1_rfs_17756.trc:
ORA-16401: archivelog rejected by RFS


Answer:  The "ORA-16401: archivelog rejected by RFS" error is a noise error.  Per the docs:. the ORA-16401 says that no action is necessary; this is an informational statement provided to record the event for diagnostic purposes."
ORA-16401: archivelog rejected by RFS

Cause: An attempt was made to re-archive an existing archivelog. This usually happens because either a multiple primary database or standby database(s) or both are trying to archive to this standby database.

Action: See alert log and trace file for more details. No action is necessary; this is an informational statement provided to record the event for diagnostic purposes.

 

Thursday 18 April 2013

Slave Behind Master in MySQL


In order for you to know that MySQL is fully processing the SQL from the relay logs. Try the following:

Check the network status with your Network Ops group, and allocate dedicated bandwidth to the MySQL replication process, if possible.

OR

STOP SLAVE IO_THREAD;
This will stop replication from downloading new entries from the master into its relay logs.
The other thread, known as the SQL thread, will continue processing the SQL statements it downloaded from the master.

When you run SHOW SLAVE STATUS\G, keep your eye on Exec_Master_Log_Pos. Run SHOW SLAVE STATUS\G again. If Exec_Master_Log_Pos does not move after a minute, you can go ahead run START SLAVE IO_THREAD;. This may reduce the number of Seconds_Behind_Master.
Other than that, there is really nothing you can do except to:

Trust Replication
Monitor Seconds_Behind_Master
Monitor Exec_Master_Log_Pos
Run SHOW PROCESSLIST;, take note of the SQL thread to see if it is processing long running queries.


 

Tuesday 26 March 2013

SecureFiles Oracle 11g

Oracle Secure Files also known as Fast Files is Oracle’s improvement to storage of large object types (LOBs). Secure Files gives comparable performance to a file system for LOBs and LOBs can store many types from data from images, large amounts of text, word documents, excel spreadsheets, XML, HTML, etc. as well as DICOM formatted medical images.
This is a step forward in helping manage unstructured data with the Oracle database by boosting performance, but also by improving security. Secure Files extends Transparent Data Encryption to LOBs, this not only makes storing and managing unstructured content easier, but improves the security of the unstructured content.
If that was not enough Secure Files also gives advanced file system features such as compression and data de-duplication. Data De-duplication is where duplicate objects in LOBs tied to many records within the database is only stored once rather then a copy for each record. This not only improves storage space, but can also offer performance improvements. Compression like it indicates compresses LOB data transparently offering storage savings and a performance boost, but Oracle takes it a step further and automatically determines if the data is able to be compressed and if so are the space savings from the compression of benefit.
By default normal LOB storage is used, called BASIC file. To use SecureFile for LOB storage the SECUREFILE lob Storage keyword must be used.

The default behavior for securefile usage can be changed via the db_securefile initialization parameter.
db_securefile={ALWAYS FORCE PERMITTED NEVER IGNORE}

ALWAYS – Always attempts to create all LOBs as SECUREFILE LOBs
FORCE - all LOBs created in the system will be created as SECUREFILE LOBs.
PERMITTED – Allows LOBs that use SecureFiles.
NEVER – Never Allows LOBs that use SecureFiles, they will always be BASICFILE LOBs
IGNORE – Ignore errors that otherwise would be raised by forcing LOBs as SECUREFILE LOBs


Can be set dynamically via an ALTER SYSTEM:
SQL> ALTER SYSTEM SET db_securefile = 'ALWAYS' ;
Securefiles offer a lot of benefits over the old LOB storage method, such as deduplication capability, compression and encryption.
Compression has 2 forms medium which is the default and high. Keep in mind the high level of compression the larger resource impact on the database you should expect.

CREATE TABLE table1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
(COMPRESS
CACHE
NOLOGGING );

CREATE TABLE table1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
CACHE
NOLOGGING );


Encryption for SecureFiles is implemented via the Transparent Data Encryption (TDE) and SecureFiles extends that TDE for LOB data types. Encryption is performed at the block level and uses the following valid encryption levels 2DES168, AES128, AES192 (default) and AES256. Keep in mind for SecureFiles the NO SALT option is not supported.

CREATE TABLE table1 ( a CLOB ENCRYPT USING 'AES128')
LOB(a) STORE AS SECUREFILE
( CACHE );


DeDuplication can be a powerful feature that can reduce the amount of storage space for LOBs as all duplicated LOBs are only stored once. With the desire to reduce database disk space the compress and DeDuplication can provide significant cost savings for storage.

CREATE TABLE table1 ( REGION VARCHAR2(20), ID NUMBER, a BLOB)
LOB(a) STORE AS SECUREFILE
( DEDUPLICATE
CACHE)

CREATE TABLE table1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
DEDUPLICATE
CACHE ) ;


Oracle Secure Files utilizes Shared IO Pool. The shared IO pool is used from the SGA and allocations are always for a specific session and therefore the data is specific to the session. We can look at the Shared IO Pool via the v$sga_dynamic_componets and v$sgainfo V$ views. If we examine the memory structures of the SGA we can see what the Shared IO Pool max size is in relationship to the other memory structures of the database.

SQL> select name, bytes from v$sgainfo ;
NAME BYTES
-------------------------------- ----------
Fixed SGA Size 1334380
Redo Buffers 5844992
Buffer Cache Size 268435456
Shared Pool Size 239075328
Large Pool Size 4194304
Java Pool Size 12582912
Streams Pool Size 4194304
Shared IO Pool Size 0
Granule Size 4194304
Maximum SGA Size 535662592
Startup overhead in Shared Pool 46137344
Free SGA Memory Available 0


SQL> select * from v$sga_dynamic_components where component='Shared IO Pool' ;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
Shared IO Pool 0
0 0 0 0 STATIC
4194304


When a session is unable to find free memory in the Shared IO Pool, PGA memory would be used. To see PGA memory allocations you can use the V$SECUREFILE_TIMER view which gets an entry each time memory is allocated out of the PGA.

SQL> select * from v$securefile_timer where name like '%PGA%';
NAME LAYER_ID OWNTIME MAXTIME MINTIME INVOCATIONS LAYER_NAME
------------------------- ---------- ------- ------- ------- ----------- --------------------------------------------------
kdlw kcbi PGA alloc timer 2 0 0 0 0
Write gather cache
kdlw kcbi PGA free timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA borrow timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA free timer 2 0 0 0 0
Write gather cache

Monday 25 March 2013

ORA-03106: fatal two-task communication protocol error

Error:

ORA-24330: internal OCI error
ORA-03106: fatal two-task communication protocol error
ORA-03114: not connected to ORACLE


Where: Error logged on oracle 11.2.0.2 client application server. Application based on c/c++ coding. Solaris 10 SPARC

Database 11.2.0.2 compatible, Solaris 10 SPARC

Background:
ORA-24330: internal OCI error & ORA-03114: not connected to ORACLE errors caused due to session disconnection byORA-03106: fatal two-task communication protocol error

Solution:
1. Set db_securefile to PERMITTED & re-create all LOB data type tables (Workaround)
2. Replace blank row column values of the table by some value or NULL flag, and enable NOT NULL constraint for those columns

Lesson Learned:

As the parameter db_securefile default value is PERMITTED , which got changed to ALWAYS as a part if databasehardening as per CSI benchmark 11g


Need to have proper testing & review from application after DB hardening
 
NOTE:-- If need more details please check below blog:-
http://www.moreajays.com/2013/03/ora-03106-fatal-two-task-communication.html
 
 

Thursday 21 March 2013

Nulls in ORACLE


Nulls

If a column in a row has no value, then the column is said to be null, or to contain null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.
Do not use null to represent a value of zero, because they are not equivalent.

-------------------------------------------------------------------------
Note:
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.
-------------------------------------------------------------------------


Nulls in SQL Functions

All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument. You can use the NVL function to return a value when a null occurs. For example, the expression NVL(commission_pct,0) returns 0 if commission_pct is null or the value of commission_pct if it is not null.
Most aggregate functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.

Nulls with Comparison Conditions

To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function. Please refer to DECODE for syntax and additional information.
Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.

Nulls in Conditions

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.
Table 2-20 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.