Drop empty tablespace fails with ORA-14404 or ORA-144047 partitioned table contains partitions/subpartitions in a different tablespace

Recently I was performing database reorg operation on one of pre-prod environment with traditional way of table/lob movement followed by index rebuild. After moving all segments from current tablespace to new, I was ready to drop original one.


SQL> select count(0) from dba_segments where tablespace_name='MY_TABLESPACE';
  COUNT(0)
----------         
0

And it errored out!!!


SQL> DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES
*ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

As first step, I tried purging dba_recyclebin, but it didn’t help.

After searching on metalink, I found following note with similar issue: ORA-14404 or ORA-14407 When Trying to Drop a Tablespace (Doc ID 1674989.1)

which talks about two causes of this issue:

1. An attempt was made to drop a tablespace which contains tables whose partitions or subpartitions are not completely contained in this tablespace. 

OR

2. This can happen due to deferred segment creation introduced in 11.2.

First cause was matching my case:

I tried finding out tables whose partitions were spread across other partitions along with tablespace MY_TABLESPACE & moved to it to new tablespace as well:


SELECT    'alter table '|| table_owner|| '.'|| table_name|| ' move partition '|| partition_name|| '   tablespace MY_TABLESPACE_NEW;'  FROM (SELECT DISTINCT table_name, partition_name          FROM dba_tab_partitions         WHERE tablespace_name = 'MY_TABLESPACE') a,       dba_tab_partitions b WHERE b.tablespace_name <> 'MY_TABLESPACE'       AND a.table_name = b.table_name;

After moving all affected table partitions, I once again tried dropping tablespace: MY_TABLESPACE. It once again failed with same error: ORA-14404.
Then I found same issue with some of index partitions as well, for which I used following command:


select distinct 'alter index ' || index_owner || '.' || a.index_name || ' rebuild partition ' || a.partition_name || ' tablespace MY_TABLESPACE_NEW;'from ( select distinct index_name,partition_namefrom dba_ind_partitionswhere tablespace_name = 'MY_TABLESPACE' ) a, dba_ind_partitions bwhere b.tablespace_name <> 'MY_TABLESPACE' and a.index_name = b.index_name;

Once indexes were rebuild, I was able to drop tablespace MY_TABLESPACE without any issues 🙂

Note: You may also get ORA-14407 is some cases, wherein sub-partitions are the issue, for which you will use above commands just with sub-partitions views.

Just to elaborate seconds possible cause of this error: deferred segment creation:


The Oracle Database 11.2 includes a new space allocation method. When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION  set to TRUE.So in simple words, if you have set this parameter to value TRUE, particular table will be populated in dba_tables once created, but will not be populated in dba_segments unless we insert a row to it. So in the case of defered segments, you will not be able to drop tablespace.


Hope so u will find this post very useful:-)

Cheers

Regards,
Adityanath

Advertisements

ORA-01450: maximum key length (6398) exceeded while creating index

Interesting issue happened today, while creating index in database(running 11.2.0.4 DB on Linux), I got ORA-01450: maximum key length (6398) exceeded.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> CREATE INDEX "TEST_USER"."DEMO_INDEX_1" ON "TEST_USER"."DEMO_TABLE" ("SEQUENCEGROUP", "SEQUENCENUMBER")
 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" ; 2 3 4 5 6
CREATE INDEX "TEST_USER"."DEMO_INDEX_1" ON "TEST_USER"."DEMO_TABLE" ("SEQUENCEGROUP", "SEQUENCENUMBER")
 *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

So I started searching on metalink to get details about ORA-01450 & found: ORA-01450 When Creating an Index (Doc ID 293599.1)

According to MOS note, There is a restriction on index data length. It depends on the db_block_size. For 8K Oracle block size,it is 6398. Also the restriction depends on the actual Oracle data block size rather than the DB_BLOCK_SIZE database initialization parameter. That means that when using tablespaces with a different block size, the restriction depends on the actual block_size of the tablespace used for the index storage.

So I checked table definition to get details about datatype for columns used in table.


SQL> desc TEST_USER.DEMO_TABLE
Name Null? Type
----------------------------------------- -------- ----------------------------
CID VARCHAR2(4000)
RT VARCHAR2(4000)
SEQUENCEGROUP VARCHAR2(4000)
SEQUENCENUMBER VARCHAR2(4000)
CLIENTID VARCHAR2(4000)
COUNTRY VARCHAR2(4000)
KEYTYPE VARCHAR2(4000)

As per table definition creating index on two columns SEQUENCEGROUP & SEQUENCENUMBER would make index key length to 8000 which is more than 6398, which is causing failure in index creation.

So how to resolve this: So we have 2 ways

1. Limit column key length if possible:

So in my case, I altered both column datatypes to VARCHAR2(3000), so that index key length was restricted to 6000.Post table alteration, I was able to created index successfully.

2. Create index in tablespace with bigger block size:

So you can create tablespace with block size 16K/32K, and then create index in this new tablespace.

Now we need to understand why we have this restriction:

As per Oracle 8i documentation, index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. In fact, it is required that any index block must contain at least TWO index entries per block. Therefore, the maximum key length for an index will be less than half of the DB_BLOCK_SIZE. The Oracle 8i Administrator’s Guide states that the maximum size of a single index entry is approximately one-half the data block size. However, when considering that we must also leave space in the block according to PCTFREE, INITRANS, and space for block overhead (Block Header, ROW Directory, Table Directory, etc) the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Oracle 9.2 onward, restriction on index data length has been increased from 3218 to 6398. So from 9.2 – 11g we have scope to accommodate more, but there is still a restriction and we cannot create the index beyond it.

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed on Windows server

Few days back, I was applying OPatch on one of the database residing on windows server. I tried it multiple times without any luck as everytime when I tried applying patch, I was getting below error:

OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.

This was due to some of the DLL files related to oracle on server were used by some of the active processes even after I had stopped all oracle related services. Even i tried rebooting whole server, but it didn’t help.

I did check related logfile so as to get more details about the error:


[Oct 6, 2016 6:20:18 PM] Prereq checkPatchApplicableOnCurrentPlatform Passed for patch : 23530402
[Oct 6, 2016 6:20:18 PM] Following files are active :
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oracell11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraasmclnt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orapls11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orageneric11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraclient11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oracommon11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraplp11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraxml11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orannzsbb11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orazt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraztkg11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oran11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orantcp11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oranl11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orancrypt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orahasgen11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocr11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocrb11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocrutl11.dll
[Oct 6, 2016 6:20:18 PM] Prerequisite check "CheckActiveFilesAndExecutables" failed.
 The details are:
 Following files are active :
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oracell11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraasmclnt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orapls11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orageneric11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraclient11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oracommon11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraplp11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraxml11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orannzsbb11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orazt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraztkg11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oran11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orantcp11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oranl11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orancrypt11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\orahasgen11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocr11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocrb11.dll
 G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\bin\oraocrutl11.dll
[Oct 6, 2016 6:20:18 PM] OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Oct 6, 2016 6:20:18 PM] Finishing UtilSession at Thu Oct 06 18:20:18 CAT 2016
[Oct 6, 2016 6:20:18 PM] Log file location: G:\OPENPA~1\REPOSI~1\SERVER~2\software\database\cfgtoollogs\opatch\opatch2016-10-06_18-20-12PM_1.log
[Oct 6, 2016 6:20:18 PM] Stack Description: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:5765)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:905)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:367)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.opatchutil.NApply.process(NApply.java:362)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1084)
[Oct 6, 2016 6:20:18 PM] StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[Oct 6, 2016 6:20:18 PM] StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[Oct 6, 2016 6:20:18 PM] StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[Oct 6, 2016 6:20:18 PM] StackTrace: java.lang.reflect.Method.invoke(Method.java:597)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.UtilSession.process(UtilSession.java:358)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.OPatchSession.process(OPatchSession.java:2515)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.OPatch.process(OPatch.java:796)
[Oct 6, 2016 6:20:18 PM] StackTrace: oracle.opatch.OPatch.main(OPatch.java:846)

Such errors are very easy to fix on UNIX platform wherein we do have commands like ps, fuser or lsof to get details about the process accessing particular file. But what we can do for Windows platform.

I did check with widows admins to help me out here for identifying active processes. He told me about very intersting command “tasklist”.

Syntax: tasklist.exe /m <filename>

This command will give you details such as process name, PID & filename used by process as shown below:

blog-1
So here I got the culprit. Now how to get rid of these processes. You can go to resource monitor under task manager to identify the process and then kill it as shown below:

blog-2
Now you can apply patch without any issues.

As per my past experience, killing windows process manually can cause some serious issues after some time. So after killing any process manually on windows server, will recommend you to cleanly restart your server.

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

srvctl start database fails with ORA-03113,ORA-27300,ORA-27301,ORA-27302,ORA-27303.

Yesterday I was trying to start DB services using SRVCTL on one of the new environment. I was able to start individual database instances using sqlplus, but it was giving issues for SRVCTL, I was able to start one of the two instances using SRVCTL, but not both, it was giving following error:


[oracle@uat-srv-fin2 dbs] Ora:TESTDB $ srvctl start database -d TESTDB
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-5017: The resource action "ora.testdb.db start" encountered the following error:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.4/grid/log/uat_srv-fin1/agent/crsd/oraagent_oracle//oraagent_oracle.log".
CRS-2674: Start of 'ora.testdb.db' on 'uat-srv-fin1' failed
CRS-2632: There are no more servers to try to place resource 'ora.testdb.db' on that would satisfy its placement policy.

After checking all RAC related init parameters, and making sure everything is right there, I had a look at DB alert log so as to investigate further and I got something useful :


Errors in file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_dia0_28019_base_1.trc:
ORA-27506: IPC error connecting to a port
ORA-27300: OS system dependent operation:proto mismatch failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxpcon
ORA-27303: additional information: Protocol of this IPC does not match remote (192.168.52.42). SKGXP IPC libraries must be the same version. [local: RDS,remote: UDP]
Errors in file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_lmon_28020.trc:
ORA-27550: Target ID protocol check failed. tid vers=1, type=1, remote instance number=2, local instance number=1
LMON (ospid: 28020): terminating the instance due to error 481
System state dump requested by (instance=1, osid=28020 (LMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_diag_27999_20160815112931.trc
Dumping diagnostic data in directory=[cdmp_20160815112932], requested by (instance=1, osid=28020 (LMON)), summary=[abnormal instance termination].
Instance terminated by LMON, pid = 28020

So messages were pointing to RDS/UDP binary linking.

As per metalink note : ORA-27303: SKGXP IPC libraries must be the same version. [local: RDS,remote: UDP] on Exadata (Doc ID 1574772.1)

RAC instances can’t see communicate with each other. IPC error is due to mismatch in cluster interconnect protocol being in use by these 2 nodes. Node2 used UDP and Node1 uses RDS now. So due to this mismatch the other node is down. Exadata does not support mixing of UDP and RDS ports.

As a solution I did following steps so as to resolve the issue:

1. Shutdown all the instances running on this ORACLE_HOME which was configured for the instance having UDP port
– Execute as oracle user,
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk ipc_rds ioracle
2. Startup the instance using srvctl

Now I was able to start database using SRVCTL without any issues:


[oracle@uat-srv-fin2 ~] Ora:TESTDB $ srvctl start database -d TESTDB
[oracle@uat-srv-fin2 ~] Ora:TESTDB $
[oracle@uat-srv-fin2 ~] Ora:TESTDB $
[oracle@uat-srv-fin2 ~] Ora:TESTDB $ srvctl status database -d TESTDB
Instance TESTDB1 is running on node uat_srv-fin1
Instance TESTDB2 is running on node uat_srv-fin2

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

 

ORA-08103 Object no longer exists during select operation

Yesterday, application team informed us about production job failure with error: ORA-08103 Object no longer exists. It was simple select query on object with type VIEW. I tried rerunning query which ran without any issue.

I tried to investigate, if the underlying objects have been dropped when job was running. But object creation time was in the year 2014 so definitely object was not dropped & recreated and it was present during job execution.

I tried to search in metalink support & found following note explaining issue:

OERR: ORA-8103 “object no longer exists” Master Note / Troubleshooting, Diagnostic and Solution (Doc ID 8103.1)

According to note: We get this error when, tables are being dropped/truncated while a SQL statement for those tables is still in execution. In the case of an index, it might be caused by an index rebuild. In other words the object has been deleted by another session since the operation began.

I had a look into aud$ & found one of the underlying table in view was truncated at same time when job was executing select on view. So I got the culprit 🙂

But why oracle gave error: ORA-08103 Object no longer exists even when object was present???

If you check DBA_OBJECTS dictionary view, it has two columns OBJECT_ID & DATA_OBJECT_ID. Each object is assigned a unique number to recognise it in the database (OBJECT_ID). In the same manner, each object is linked to a segment. The data object id is assigned to that segment and any kind of change done on the physical segment would lead to give a change in that number (DATA_OBJECT_ID). Both the numbers are same initially but when the modifications happen on the segment, the DATA_OBJECT_ID changes. Both the OBJECT_ID and DATA_OBJECT_ID are the numbers used by oracle to denote the metadata for the object that is used within the data dictionary. Operations such as Truncate, Move, Rebuild Index, Spilt Partition etc would cause change in DATA_OBJECT_ID of the objects.

So this gives enough justification for oracle error: ORA-08103 Object no longer exists!!

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

Resolving ORA-1031 while connecting as “/ as sysdba”

In this post, we are discussing about issue which I faced on fresh oracle software install.


[oracle@test1.test.com ] Ora:TEST2 $ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 30 12:30:31 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

Normally this issue points to incorrect permissions on oracle binaries OR incorrect value for SQLNET.AUTHENTICATION_SERVICES in sqlnet.ora. But in my case both were correctly set.

After checking on metalink, I found following note related to the same issue, which explains different scenarios, on of them is if the OS user is part of the OSDBA group.

Troubleshooting ORA-1031 or ORA-1017 Errors When Connecting As SYSDBA (Doc ID 730067.1)

I tried to compare file “$ORACLE_HOME/rdbms/lib/config.s” with other oracle home where sqlplus “/ as sysdba” was working fine.

Note: config file name vary from OS to OS on some OS it is config.c and on some OS it is config.s


[oracle@test1.test.com ] Ora: TEST1 $ diff config.s /u01/app/oracle/TEST2/11.2.0.4/rdbms/lib/config.s
23c23
< .ascii "oinstall\0"
---
> .ascii "\0"

I found the culprit!!! In my new installation, Library file was missing group details. After making all necessary changes, I had to relink all oracle binaries.

For binary relink, perform following steps:

– Make sure that no oracle processes running
– Login as oracle
– Make sure LD_LIBRARY_PATH and ORACLE_HOME are set properly
– $ORACLE_HOME/bin/relink all

After binary relink, sqlplus “/ as sysdba” started working as expected.

Done!!!!!!!!!!

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

From last two days, I have been getting “ORA-27054: NFS file system not mounted with correct options” error while running an RMAN backup to a Sun ZFS Storage Appliance.

This error have been observed particularly, while taking controlfile backups. RMAN datafile or archivelog backups without controlfile were running fine.


RMAN-08132: WARNING: cannot update recovery area reclaimable file list
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch01 channel at 02/15/2016 16:02:44
ORA-01580: error creating control backup file /mnt/bkp1/TEST1/TEST1_CTRL_snapcf.ctl
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
Additional information: 1

After searching on MOS, I found following note, which explains different mount point options for configuring the NFS mounts.

Sun ZFS Storage Appliance: Oracle Database 11g R2 NFS Mount Point Recommendations (Doc ID 1567137.1)

After discussing the same with SOLARIS team, we found that backup mount point were mounted with all recommended parameters.

So tried to search more documents on MOS and found the one:

Oracle ZFS Storage: FAQ: Exadata RMAN Backup with The Oracle ZFS Storage Appliance (Doc ID 1354980.1)

As per note “DNFS is strongly recommended when protecting an Exadata with the ZFS Storage Appliance. It is required to achieve the published backup and restore rates”.

You can confirm that DNFS is enabled by running following query on database:

select * from v$dnfs_servers;

In my case it was not enabled. DNFS to be enabled on a each database node with the following command:

$ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk dnfs_on

dcli may be used to enable Oracle Direct NFS on all of the database nodes simultaneously:

$ dcli -l oracle -g /home/oracle/dbs_group make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk dnfs_on

The database instance should be restarted after enabling Oracle Direct NFS.

After enabling DNFS activity, my backup started running without any issues.

Done!!!!!!!!!!

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath