DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, April 17, 2014

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

ERROR:
======

ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []

CAUSE:
======

This also can be cause by the following defect

Bug 8240762 Abstract: Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] after SHRINK

Details:

Undo corruption may be caused after a shrink and the same undo block may be used
for two different transactions causing several internal errors like:

SOLUTION:
=========

startup mount

SQL>  select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         3         ONLINE
/u03/app/oracle/oradata/star/redo03.log
NO

         2         ONLINE
/u03/app/oracle/oradata/star/redo02.log
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         1         ONLINE
/u03/app/oracle/oradata/star/redo01.log
NO

/u03/app/oracle/oradata/star/undotbs01.dbf


SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

If We are using pfile then edit pfile and change the parameter :
=========================================

undo_management=MANUAL

If you are using spfile then
====================

alter system set undo_management = MANUAL scope=spfile;

alter database open;

Now drop the undo file
================

alter database datafile  '/u03/app/oracle/oradata/star/undotbs01.dbf' offline drop;

Drop the tablespace :
=============
drop tablespace UNDOTBS1;

Got the following error while droping the tablespace:
===================================

ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping tablespace

Inorder to ignore this error,
==================

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU1_3780397527$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU2_2232571081$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU3_2097677531$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU4_1152005954$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU5_1527469038$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU6_2443381498$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU7_3286610060$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU8_2012382730$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU9_1424341975$           NEEDS RECOVERY   UNDOTBS1
_SYSSMU10_3550978943$          NEEDS RECOVERY   UNDOTBS1

10 rows selected.

Now shut down the database.

Add the lines in pfile inorder to ignore the segments that are used :
=================================================

_corrupted_rollback_segments =

('_SYSSMU1_3780397527$','_SYSSMU2_2232571081$','_SYSSMU3_2097677531$','_SYSSMU4_1152005954$','_SYSSMU5_1527469038$','_SYSSMU6_2443381498$','_SYSSMU7_32866100

60$','_SYSSMU8_2012382730$','_SYSSMU9_1424341975$','_SYSSMU10_3550978943$')


SQL> STARTUP MOUNT pfile='/u03/app/oracle/product/11.2.0/db_1/dbs/initstar.ora'
ORACLE instance started.

Total System Global Area  242208768 bytes
Fixed Size                  2212168 bytes
Variable Size             184553144 bytes
Database Buffers           50331648 bytes
Redo Buffers                5111808 bytes
Database mounted.

SQL> alter database open;

Database altered.

drop tablespace UNDOTBS1;

Now create the new undo tablespace with datafile.
====================================

create UNDO tablespace undotbs1 datafile '/u03/app/oracle/oradata/star/undotbs01_1.dbf' size 25m autoextend on next 1m maxsize 1024m;

Now start with spfile and make undo_management AUTO.

alter system set undo_management = AUTO scope=spfile;

REFERENCE :
========

Step by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1)

Friday, April 11, 2014

Expdp Hangs :-

Experience on EXP JOB Hangs :-

Oracle DB Version - 10.1/Sun Solaris 5.8

Expdp/Exp hangs for more than 4 hrs, No errors were reported in the alert log and no blocking Session.

After some time received timeout session error.

By checking the above, we found as a root cause that Exp job started a week back didn't complete properly. It's holding up any new Exp job.

I Killed a Exp session from the database, but Still in the database as a "KILLED" state. So I decided lets kill the OS process of the Exp Session. But It looks like "ora_dw001_SID". I'm not sure that I can kill this process, So raised a TAR with oracle to get confirmation to do it.

After killing this OS process, Session went away from the database, I can start a New Exp backup without any Issue.

So,

1. After killing the Session from the database, make sure It's go away from the DB and Server.

2. If you see any process like "ora_dw001_SID" that holds your Exp job, you can safely kill this, It's not a BACKGROUND PROCESS, It's  a worker for Exp job.



 

Monday, April 7, 2014

Step by Step Asm Instance And Database Creation

                                  Disk creation in vmware


Click Add Button

Click Next Button

Click Next Button

Click ide and next Button

Size 3gb
Click Next Button
Click Finish Button
Add another one disk 3g
Step:1 Partition the Disks
[root@DR ~]#fdisk -l
[root@DR ~]# fdisk /dev/hdd
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
The number of cylinders for this disk is set to 6241.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-6241, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-6241, default 6241):
Using default value 6241
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
To follow the same steps to 2nd  disk to format
Step:2 RAW Device Setup
fdisk -l
vi /etc/udev/rules.d/63-oracle-raw.rules
ACTION=="add",KERNEL=="hdb1",RUN+="/bin/raw /dev/raw/raw1%N"
ACTION=="add",KERNEL=="hdd1",RUN+="/bin/raw /dev/raw/raw2%N"
step:3 Mapping the raw device
[root@DR ~]# /bin/raw /dev/raw/raw1 /dev/hdb1
/dev/raw/raw1:  bound to major 3, minor 65
[root@DR ~]# /bin/raw /dev/raw/raw2 /dev/hdd1
/dev/raw/raw2:  bound to major 22, minor 65
Step:4 Permissions
[root@DR ~]# chown -R oracle:oinstall /dev/raw/raw1
[root@DR ~]# chown -R oracle:oinstall /dev/raw/raw2
[root@DR ~]# chown 777 /dev/raw/raw2
[root@DR ~]# chown 777 /dev/raw/raw1
Step:5 To start the CSS
 cd /u01/app/oracle/product/10.2.0/db_1/bin
 ./localconfig add/delete
Step:6 ASM instance creation
su - oracle
[oracle@DR ~]$ cd $ORACLE_HOME/dbs
[oracle@DR dbs]$ ls –lrt
Creating asm instance pfile
vi init+asm25.ora
instance_type=asm
instance_name=+asm25
asm_power_limit=1
step:7 startup the asm instance
[oracle@upgrade ~]$ export ORACLE_SID=+asm25
[oracle@upgrade ~]$ sqlplus '/as sysdba'
startup
SQL> select HEADER_STATUS,PATH,NAME from v$asm_disk;
creating diskgroup
create diskgroup sachin normal redundancy
failgroup  f1   disk  '/dev/raw/raw1'
failgroup  f2   disk  '/dev/raw/raw2';
SQL> select HEADER_STATUS,PATH,NAME from v$asm_disk;
**********************************************************
step :1    Database creation
Creating database instance pfile
cd $ORACLE_HOME/dbs
vi initproddb.ora
db_block_size=8192
db_domain=""
db_name=proddb
background_dump_dest=/u01/app/oracle/product/10.2.0/admin/proddb/bdump
core_dump_dest=/u01/app/oracle/product/10.2.0/admin/proddb/cdump
user_dump_dest=/u01/app/oracle/product/10.2.0/admin/proddb/udump
control_files=("+sachin/control01.ctl")
compatible=10.2.0.1.0
sga_target=800m
audit_file_dest=/u01/app/oracle/product/10.2.0/admin/proddb/adump
remote_login_passwordfile=EXCLUSIVE
step:2 create directories
cd $ORACLE_BASE/admin
mkdir proddb
cd proddb
mkdir adump bdump cdump dpdump udump pfile
step:3 startup the database instance
export ORACLE_SID=proddb
sqlplus '/as sysdba'
startup nomount
step :4 create database
create database proddb
logfile group 1 ('+sachin') size 4M,
group 2 ('+sachin') size 4M
datafile '+sachin' size 300M
sysaux datafile '+DATA ';
step:4 run the scripts
@?/rdbms/admin/catlog.sql
@?/rdbms/admin/catproc.sql