Thursday, March 21, 2013

ORA-00955: name is already used by an existing object



While doing the Production deployment with the scripts given by application team, encountered the error.

This error which initially looked like strange but then came to know that Oracle works Like this..

##########################
#   Errors 
##########################

ORA-00955: name is already used by an existing object

##########################
#   Command Used  
##########################


SQL> ALTER TABLE tracking ADD CONSTRAINT tracking_pk PRIMARY KEY (menu_id,asset_version);
ALTER TABLE tracking ADD CONSTRAINT tracking_pk PRIMARY KEY (menu_id,asset_version)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


##########################
#   Informations  

##########################


Initially i thought that a constraint exists with the same name TRACKING_PK 
But there is no constraint created with the same name in this schema. 

So started to Dig more on this...

##########################
 Solution 
##########################

  Drop the indexes which were previously created as a part of Primary Key Constraint. 

The constraint which we are creating is a Primary Constriant. 

When we create a Primary Constraint, Oracle Creates 2 objects.

1) Constraint
2) Index

These 2 objects controls the uniqueness in the table.

So i checked DBA_INDEXES and found that a index with the same name TRACKING_PK still exists in the database which is not allowing to create a constraint with the same name TRACKING_PK

Select owner,index_name,index_type,table_owner,table_name from dba_indexes where table_name='tracking' and owner='AU_USER';


OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME 
------------------------------ ------------------------------------------------------------ 
AU_USER       tracking_PK                      AU_USER       tracking   

So Dropped the index and added the constraint and it worked.

Drop index "AU_USER"."tracking_PK";



SQL> ALTER TABLE tracking ADD CONSTRAINT tracking_pk PRIMARY KEY (menu_id,asset_version);

Table altered.


Thursday, March 14, 2013

Oradim -edit -sid gives Unable to start service, OS Error 1056



Every Time i restart the system, i need to bring up the Oracle instance service either from services.msc or using netstart.

So i have decided to make it auto start whenever i restart the system.

##########################
#   Errors 
##########################

Unable to start service, OS Error 1056

##########################
#   Command Used  
##########################

oradim -edit -sid orawin -startmode auto -srvcstart system


##########################
#   Informations  
##########################

I have used this command before in 10g but it didnt give any errors, But when i tried it in 11.2.0.1.0 version of database, getting this error. This seems to be Bug 9584383 and its expected if a service is edited when its running.

But in my case, i have tried by stopping the service also still  the error comes. So whether a service is running or not running, when we try to modify the startmode of a service we will get an error.

##########################
 Solution 
##########################

  We don't have to worry about the error because eventhough we get an error while modifying the service startmode, the command does it purpose, Instance Service startmode gets changed when we issue the command. 

This bug is fixed in.
  • 12.1 (Future Release)
  • 11.2.0.2 (Server Patch Set)
  • 11.2.0.1 Patch 2 on Windows Platforms
Change the Database Service Startmode from Manual to Automatic.

oradim -edit -sid orawin -startmode auto -srvcstart system




Change the Database Service Startmode from Automatic to Manual.

oradim -edit -sid orawin -startmode manual -srvcstart demand



Below Link will be useful if you want start the service using commands like oradim and net start.




Friday, March 8, 2013

ORA-00997: illegal use of LONG datatype

Our Application team has created objects in the USERS tablespace instead of creating the objects in their dedicated tablespace.

So we need to move the objects from USERS tablespace to their default tablespace.

##########################
#   Errors 
##########################


SQL> alter table AU.TRACTION move tablespace AU_DATA;
alter table AU.TRACTION move tablespace AU_DATA
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


##########################
#   Command Used  
##########################

alter table AU.TRACTION move tablespace AU_DATA;

##########################
#   Informations  
##########################

Table which i tried to move contains LONG datatype. Tables with Data Types with LONG or LONG RAW cannot be moved.

##########################
 Solution 
##########################

  Export & Import The Table. 

SQL> desc AU.TRACTION

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(256)
 DATA                                      NOT NULL LONG


Alter table move will not work. We need to Export & Import the tables. 

Friday, March 1, 2013

ORA-1613 signalled during: alter database enable public thread 2...

Enabling the thread 2 of a RAC 2 node database fails with the below error.

##########################
#   Errors 
##########################

Wed Feb 27 07:34:57 2013

ORA-1613 signalled during: alter database enable public thread 2...


ORA-1613 signalled during: alter database enable public thread 2...

##########################
#   Command Used  
##########################

Alter Database Enable Public Thread 2;

##########################
#   Informations  
##########################

There are no redo logs which belongs to Thread 2, all the redo logs are created in Thread 1.

##########################
 Solution 
##########################

Added New Redo log groups to thread 2. 


alter database add logfile thread 2 group 7
('/u01/oradata/mydb/redo_02_07.log') size 1024M;

And then enabled the thread.

Alter Database Enable Public Thread 2;


Check Whether Thread is Enabled Or Not :


SQL> select thread#, enabled from v$thread;

   THREAD# ENABLED
---------- --------
         1 PUBLIC
         2 PUBLIC


ORA-01618: redo thread 2 is not enabled - cannot mount & ORA-1613 Error.

RAC Duplication was performed in our database and then we changed the cluster_database=TRUE, and tried to startup the RAC database using SRVCTL command.

First instance Comes Up without any issues but when 2nd instance was trying to come up it fails in the mount stage with the errors. 

##########################
#   Errors 
##########################

Alert Log Info :

ORA-1618 signalled during: ALTER DATABASE   MOUNT...

ORA-01618: redo thread 2 is not enabled - cannot mount

##########################
#   Command Used  
##########################

srvctl start database -d mydb

##########################
#   Informations  
##########################

Initially what i thought is that Redo Thread 2 is not Enable, so i tried to enable it with the below command, which gave me ORA-1613 Error. 

http://stepintooracledba.blogspot.com/2013/03/ora-1613-signalled-during-alter.html

alter database enable public thread 2;

With further investigation found whats the issue.


##########################
 Solution 
##########################

  Error says that Redo Thread 2 Cannot be Enabled. So to Enable Redo Thread we need REDO LOGS, When i checked the Redologs in our database,  i Can see we have 12 Redo Groups all belongs to Thread 1, 

But Actually we have allocated 6 Redo logs to Thread 1 and 6 Redo Logs to Thread 2.  

So Added Redo Log Groups to thread 2 and enabled the thread and it got completed without any issues.

Command Used To Add Redo Logs to thread 2 :


alter database add logfile thread 2 group 7
('/u01/oradata/mydb/redo_02_07.log') size 1024M;

Now Opening the database with srvctl doesnt give any error and both the instances were UP & Running.

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

When Performing a RAC 2 node Production Database Restore with disk based backup, by duplicating to a single instance database, Restoration went successful.

Once Restore has completed, i have tried to open the database with RESETLOGS option. But end with the below error, So thought of sharing this little information.

##########################
#   Errors 
##########################

RESETLOGS after incomplete recovery UNTIL CHANGE 13115861554203
ORA-38856 signalled during: alter database open resetlogs...

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

##########################
#   Command Used  
##########################

Alter Database open ResetLogs;

##########################
#   Informations  
##########################


This seems to be Bug 4355382 and its expected while doing RAC Restore/Recovery.

##########################
 Solution 
##########################

  ADD _no_recovery_through_resetlogs Parameter and set it to TRUE. 


I have added _no_recovery_through_resetlogs=TRUE parameter to our PFILE and brought up the database to Mount Stage.


Now opened the database with RESETLOGS options and it worked.

This parameter tells oracle not to do any recovery while doing this resetlogs operation.

After Opening the database, the parameter can be removed from the pfile.



expdp estimate_only with & Without compression

Application team wants us to refresh a table which has LOB data's. So we wanna check the size of the export dump file before running it to allocate storage.

Using Datapump its a very easy task but want to check whether we can estimate the size of the Compressed Export Dump file. 

From 11g onwards, advanced compresssion was introduced which will compress DATA's also when compared to the option of compressing only METADATA which was available in 10g.

##########################
#   Task 
##########################

Compare the Datapump Export Dumpfile size using Compressed and Normal one.

Lets see the Difference between using Normal & Compressed

##########################
#   Normal Export Estimation  
##########################

Command to Estimate export dumpfile.


expdp / tables=u1.T1393 estimate_only=y

mydb_2 @ myserver:/dcunix
> expdp \'/ as sysdba\' tables=u1.T1393 estimate_only=y

Export: Release 11.2.0.3.0 - Production on Fri Mar 1 02:32:30 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=u1.T1393 estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "u1"."T1393"                           190.4 GB
Total estimation using BLOCKS method: 190.4 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 02:33:06

So Estimated Size using Normal One is 190.4 GB. 



##########################
#   Compressed Export Estimation  
##########################

Command to Estimate Compressed export dumpfile.


expdp / tables=u1.T1393  tables=u1.T1393 compression=all estimate_only=y


mydb_2 @ myserver:/dcunix
> expdp \'/ as sysdba\' tables=u1.T1393 compression=all estimate_only=y

Export: Release 11.2.0.3.0 - Production on Fri Mar 1 02:34:29 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=u1.T1393 compression=all estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "u1"."T1393"                           190.4 GB
Total estimation using BLOCKS method: 190.4 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 02:34:40


So Estimated Size using Compressed One is also 190.4 GB. 


##########################
 End of Story 
##########################

 We cant estimate the Size of the compressed Export Dump File. Hope Oracle May introduce this in Upcoming Versions. 


In 10g, we can compress only METADATA's but from 11g onwards we can compress DATA's also.  
 Available Options in 11g, Compression Parameters are,
NONE
METADATA_ONLY
DATA_ONLY
ALL
This Advanced Compression in 11g requires license so make sure before using it.