Category: Errors and Bugs

ASM, Database, Errors and Bugs, RAC | Data Guard

Starting ASM: ORA-29701: unable to connect to Cluster Synchronization Service

 , , ,

Hey all,
So, I bet you have seen this error already, as this is quite common when messing up with Cluster configuration, which DBAs love to do…. no?

Well, here is what you may be facing:

SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
SQL>

The error is kind of clear: Cluster Synchronization Service (CSS) is not available. So, let’s start it from ASM Cluster (or HAS).

$GRID_HOME/bin/crsctl start resource -all

Or, for Standalone:

$GRID_HOME/bin/crsctl start has

To check on status:

$GRID_HOME/bin/crsctl status resource -t

Complete example (attention to CSSD):

[root@greporasrv1 ~]# crsctl start has
CRS-4640: Oracle High Availability Services is already active
CRS-4000: Command Start failed, or completed with errors.
[root@greporasrv1 ~]# crsctl start resource -all
CRS-5702: Resource ‘ora.evmd’ is already running on ‘greporasrv1’
CRS-2501: Resource ‘ora.ons’ is disabled
CRS-2672: Attempting to start ‘ora.cssd’ on ‘greporasrv1’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘greporasrv1’
CRS-2676: Start of ‘ora.diskmon’ on ‘greporasrv1’ succeeded
CRS-2676: Start of ‘ora.cssd’ on ‘greporasrv1’ succeeded
CRS-4000: Command Start failed, or completed with errors.
[root@greporasrv1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ons
OFFLINE OFFLINE greporasrv1 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE greporasrv1 STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE greporasrv1 STABLE
--------------------------------------------------------------------------------
[root@greporasrv1 ~]#

Hope that worked! 😀

Oh, it didn’t? Did you changed hostname name or something? In this case, you may want to deconfig HAS and reconfigure using root.sh (part regular installation):

cd $ORACLE_HOME
./crs/install/roothas.pl -deconfig -force
./crs/install/roothas.pl -delete -force
./root.sh

 

Hey! Be careful with that, it might be unrecoverable. 😉


									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Enterprise Manager, Errors and Bugs
			  

			
OEM: HttpConnectionException:Response code:404
			
			

				
		
			
			
			
		
				
				 , , 		
					


		


		
				

					
Hello all,

So, a client’s OEM Information Report was running and email being sent, but all queries (Report sections) resulting on error:


Unexpected error getting the HTTP response stream while generating report: oracle.sysman.eml.ip.publishJob.HttpConnectionException:Response code:404


 


Weird?

I had a silar issue, as per documented on this post: OEM Information Reports: ORA-00600 [kpndbcon-svchpnotNULL]


This is happening due TCP timeout while executing Report after 5 minutes. By default, Idle Timeout set for Profile parameter (TCP) for the virtual server in F5 is 300 seconds, for example.

I simulated manually execution of each SQL on report and noticed report takes around 8 minutes to run.


What I did? I took some of the slow queries and transformed to a Materialized View with daily refresh.


This was valid in my case because the report runs only once a day. Remember to always check on your own situation. Perhaps an hourly refresh or so is better for you.


Another possibility is to engage the LB admin to modify this parameter by setting a new protocol profile for the virtual server or modifying the existing one.


NOTE: After modifying SLB Values, you may need to restart OMS :


emctl stop oms -all
emctl start oms


Reference?

MOS oracle.sysman.eml.ip.publishJob.HttpConnectionException:Response code:404″ Errors for reports run from EM (Doc ID 2061361.1)

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, Errors and Bugs, RAC | Data Guard
			  

			
12cR1 – Stop CRS: OC4J Fails to Stop
			
			

				
		
			
			
			
		
				
				 , , , , 		
					


		


		
				

					
Hi all!

So, I started to to see this error to stop a 12.1.0.2 CRS :


CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'grepora-srv-01'
CRS-2673: Attempting to stop 'ora.crsd' on 'grepora-srv-01'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'grepora-srv-01'
CRS-2679: Attempting to clean 'ora.oc4j' on 'grepora-srv-01'
CRS-2680: Clean of 'ora.oc4j' on 'grepora-srv-01' failed
CRS-2799: Failed to shut down resource 'ora.oc4j' on 'grepora-srv-01'
CRS-2794: Shutdown of Cluster Ready Services-managed resources on 'grepora-srv-01' has failed
CRS-2675: Stop of 'ora.crsd' on 'grepora-srv-01' failed
CRS-2799: Failed to shut down resource 'ora.crsd' on 'grepora-srv-01'
CRS-2795: Shutdown of Oracle High Availability Services-managed resources on 'grepora-srv-01' has failed
CRS-4687: Shutdown command has completed with errors.


When running a crsctl stat res -t it will show something like this


ora.oc4j 1 ONLINE UNKNOWN grpora-srv-01 CLEANING


Solution? Follow The rootupgrade.sh fails because the oc4j resource fails to stop (Doc ID 1921629.1):


1) Stop the OC4J resource, as grid user:


 srvctl stop oc4j -f


Note: Even if the oc4j does not stop, continue with step 2

2) Disable the OC4J resource, as grid user:


 srvctl disable oc4j


3) Confirm the OC4J resource is NOT running, as grid user:


 srvctl status oc4j


If the OC4J resource is still running, go to the node where oc4j is running and recycle GI by issuing the following as root:


 crsctl stop crs -f
 crsctl start crs


Why?

oc4j is used only for Quality of Service Management (QoS feature), as per:

https://docs.oracle.com/database/121/APQOS/install_config.htm#APQOS151

http://docs.oracle.com/cd/E11882_01/server.112/e24611.pdf


Searched on MOS and only found notes saying we cannot remove default services (it is not supported). But as a DBA, in the middle of the night with environment down, this may be a solution for you, specially if you are not using QoS.


Hope it helps. Cheers!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, Errors and Bugs
			  

			
ORA-31600: invalid input value DATABASE LINK for parameter OBJECT_TYPE in function GET_DDL When Dropping DBLink
			
			

				
		
			
			
			
		
					


		


		
				

					
So while back there was an error while trying to drop a public database link


SQL> drop public database link "BOB.DATABASE_LINK_1";
drop public database link "BOB.DATABASE_LINK_1"
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-31600: invalid input value DATABASE LINK for parameter OBJECT_TYPE in
function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 14


And the even strange part is that executing DBMS_METADATE.get_ddl worked against the database link


SQL> SELECT dbms_metadata.get_ddl('DB_LINK','BOB.DATABASE_LINK_1','SYS') FROM dual;

CREATE DATABASE LINK "BOB.DATABASE_LINK_1"
CONNECT TO "NYU" IDENTIFIED BY VALUES '*******'
USING 'ORCL';


The solution relied on MOS note Cannot drop a database link after changing the global_name ORA-02024 (Doc ID 382994.1) all though the symptoms in the note were not a match to the problem above the solution did the trick.

Which is to backup the table sys.link$ as CTAS and delete the row corresponding to the database link which you need to drop.


Take a complete consistent backup of the database or use CTAS can be used to backup sys.link$:


$sqlplus /nolog
connect / as sysdba

SQL> create table backup_link$ as select * from sys.link$:




Delete the DBLINK as follows:


$sqlplus /nolog
connect / as sysdba

SQL> delete sys.link$ where name='db_link_name>';

SQL>commit;


Verify if the operation was correctly proceeded:


select db_link, username, host from user_db_links;


Hope it helps!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, Errors and Bugs, RAC | Data Guard
			  

			
DG Broker ORA-16766: Redo Apply is stopped after a Database Restart
			
			

				
		
			
			
			
		
				
				 , , , 		
					


		


		
				

					
So, the other day I was engaged to check a dataguard, while checking its status with the dg broker I found this error


Error: ORA-16766: Redo Apply is stopped


Checking for it on dg broker:


DGMGRL> show configuration verbose

Configuration - DGCONFIG

Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Error: ORA-16766: Redo Apply is stopped

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

GMGRL> show database verbose STANDBY

Database - STANDBY

Enterprise Manager Name: STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 3 seconds ago)
Apply Lag: 1 hour(s) 50 minutes 6 seconds (computed 0 seconds ago)
Apply Rate: 828.00 KByte/s
Real Time Query: OFF
Instance(s):
STANDBY

Database Error(s):
ORA-16766: Redo Apply is stopped

Properties:
DGConnectIdentifier = 'STANDBY'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '900'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'STANDBY'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SB_SERVER)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDBY_DGMGRL)(INSTANCE_NAME=STANDBY)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'STANDBY_%r_%t_%s.arc'
TopWaitEvents = '(monitor)'

Database Status:
ERROR


The standby state was APPLY-ON but when checked the standby if the MRP process was running I found none!


sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 4 17:05:12 2019

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


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

17:05:12 SYS@STANDBY AS SYSDBA> SELECT PROCESS, STATUS FROM v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS WRITING

8 rows selected.

Elapsed: 00:00:00.01


Checking further I found that the database was bounced while the MRP was running and that was the only explanation that I could see to see a difference between the dg broker also I could not find any error in the dg broker log $ORACLE_HOME/rdbms/log/drc*.log


After setting the state to apply-off and them to apply-on the issue was resolved.


DGMGRL> edit database STANDBY set state='apply-off';
Succeeded.
DGMGRL> edit database STANDBY set state='apply-on';
Succeeded.
DGMGRL> show database verbose STANDBY;

Database - STANDBY

Enterprise Manager Name: STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
STANDBY

Properties:
DGConnectIdentifier = 'STANDBY'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '900'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'STANDBY'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SB_SERVER)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDBY_DGMGRL)(INSTANCE_NAME=STANDBY)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'STANDBY_%r_%t_%s.arc'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show configuration

Configuration - DGCONFIG

Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> exit

sqlplus / as sysdba


17:17:54 SYS@STANDBY AS SYSDBA> SELECT PROCESS, STATUS FROM v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 APPLYING_LOG

9 rows selected.

Elapsed: 00:00:00.00




Hope it helps, unti the next one.


Elisson Almeida


 

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, Errors and Bugs, MySQL
			  

			
MySQL: ERROR 1356 (HY000): View ‘sys.innodb_lock_waits’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
			
			

				
		
			
			
			
		
				
				 , , , , 		
					


		


		
				

					
Hello all!

So, I was messing around with a Dev environment to simulate some strategies, doing some tests, and after a mysqldump exporting, dropping, and reimporting my whole database got this error.


If you arrived here from Google, Is this your case?


Well, this happens for a simple reason. The routines are not exported by mysqldump by default. Why? I don’t know either, this is an abomination to me. This would be very cheap to be the default right?


Happens that even for a new database, when importing a dump generated with –all-databases the sys/information_schema routines are deleted by the restore process. Well, at least this is recognized as a Bug (Bug 83259).


I noticed that when trying to query a sys view to get locks info, as per:


root@localhost-(none)-13:46:26>SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id,
-> blocking_pid, blocking_query FROM sys.innodb_lock_waits;
ERROR 1356 (HY000): View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them


Hm.. Weird message, let’s check for information_schema objects:


root@localhost-(none)-13:46:39>select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)


Ok, so, if you are already in this mess, how to quick recover?

Well, just run the mysql_upgrade (assuming you are in the top version/repository you have available on server):


root@localhost-(none)-13:47:34>exit
Bye
[root@greporasrv ~]# mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.1).
Found 0 sys functions, but expected 22. Re-installing the sys schema.
Upgrading the sys schema.
Checking databases.
mysqlslap.t1 OK
[... my other databases...]
sys.sys_config OK
world.city OK
world.country OK
world.countrylanguage OK
Upgrade process completed successfully.
Checking if update is needed.


Fine, lets test it:


[root@greporasrv ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost-(none)-13:49:44>select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys';
+----------+
| count(*) |
+----------+
| 48 |
+----------+
1 row in set (0.00 sec)

root@localhost-(none)-13:49:53>


Hope it helps.

Cheers!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, Errors and Bugs
			  

			
ORA 600 [kdt_bseg_srch_cbk PITL1] / ORA-00700 [PITL6] / ORA-00700 [PITL5]
			
			

				
		
			
			
			
		
				
				 , , , 		
					


		


		
				

					
Hi all,

So, right after a patching, a client environment started receiving entries on alert log like:


ORA-00700: soft internal error, arguments: [PITL6], [], [], [], [], [], [], [], [], [], [], []
ORA-00700: soft internal error, arguments: [kdt_bseg_srch_cbk PITL5], [11], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kdt_bseg_srch_cbk PITL1], [2], [], [], [], [], [], [], [], [], [], []


First a quick word regarding ORA-700: An ORA-700 is a so-called ‘soft’ assert. Soft asserts are triggered when the caller wants to make a note of the fact that something unexpected has happened, but would like to continue on because the failure is not fatal to the process or the instance. This was introduced in 12c and got some of ORA-600 messages (the informative ones), to leave ORA-600 for more critical issues.


Now to the errors: This is a clear match to Bug 28483184 (Bug 28483184 – ORA-600[PITL1] ON UPDATE TO COMPRESSED BLOCK WITH FIX FOR BUG 28364411 INSTALLED), which consists in a known defect in the Oracle code allows continued insertion of non-header block rows past the number of block ITLs in data blocks of OLTP-compressed tables.


More info: MOS Errors Noted in 12.2 and Above During DML on Compressed Tables: ORA-00600 [PITL1] / ORA-00600 [kdt_bseg_srch_cbk PITL1] / ORA-00700 [PITL6] / ORA-00700 [kdt_bseg_srch_cbk PITL5] (Doc ID 2420831.1)


To fix it: Patch on RDBMS for bug 28483184 and reorganize on involved tables. Seems to me that the patch only fixes the incorrect creation of non-header blocks, but doesn’t fix those that already have the problem in place.


My twists: I’d recommend you to increase a little bit the PCTFREE during the table reorganization, also based on Oracle’s recommendation. Recreating the tables with a larger PERCENT FREE will enable more space for ITLs. This is advantageous since the fix changes out a block that has the maximum amount of ITLs for one that has less ITLs.


If arranging a patching window is a big problem, note this patch is online installable. I’d recommend to do it offline using RAC Rolling however, as this seems a more consistent process in general.


So in summary, recommended actions are:

– Apply Patch 28483184 (RAC Rolling, Standby-First and online installable).

– Reorganize table, using methods mentioned by Suresh (ex: Create table as select (CTAS), Alter table move, Data Pump export / import, Online redefinition)


Hope that helps you!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, Errors and Bugs, SQL | PLSQL
			  

			
Changing initrans on a Partitioned Index
			
			

				
		
			
			
			
		
				
				 , , , , , 		
					


		


		
				

					
Hi all,


We were having high ITL waits and high number of deadlocks (due to the hanging transactions waiting on the ITL on the data block) on a table and index and part of the fix was to increase the INITRANS on the table and on the index.


INITRANS is a physical attribute that determines the initial number of concurrent transaction entries allocated within each data block for a given table/index/cluster. Every transaction that updates a block has to acquire an Interested Transaction List (ITL) slot, in order to store the transaction id, rollback information and ultimately lock the necessary row/index entry within the block.


For an existing object it needs to be rebuild as INITRANS is a physical attribute on the Oracle datablock. So if you change on a table and you want it to take effect for the current data you need to move the table or one partition for example.


If you are doing this type of change on a partitioned index you need to change the index default attribute as well rebuild the index partition or the entire index.


But one thing caught my eye while working on this as after setting the new INITRANS default attribute and rebuilt the index online it did not changed the INITRANS values.


See below the execution on a test system


More“Changing initrans on a Partitioned Index”

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, Errors and Bugs
			  

			
ORA-01450 on Index Rebuild
			
			

				
		
			
			
			
		
				
				 , , , , 		
					


		


		
				

					
Hello all,

So, I got this:


SQL> ALTER INDEX MATHEUS.INDEX_1 REBUILD online TABLESPACE NEW_TBS;
ALTER INDEX MATHEUS.INDEX_1 REBUILD online TABLESPACE NEW_TBS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded


So, is this a bug? Yes it is.


From MOS ALTER INDEX REBUILD ONLINE Fails with ORA-01450 (Doc ID 236329.1):

“This is caused by issue Bug:2525767. The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. Their total length is greater than number reported in ORA-01450 error message. This is a feature of online rebuild.

Maximum key length is calculated with respect to the database block size. It means that current value of the initialization parameter db_block_size is not large enough so that the internal journal IOT can be created without errors.”


Wanna know more, there is a great article on how this works exactly, by Alex Gorbachev: ORA-01450 During Online Index Rebuild


The solution?

It simply cannot be done online as explained, so doing it offline is the solution:


SQL> ALTER INDEX MATHEUS.INDEX_1 REBUILD TABLESPACE NEW_TBS;

Index altered.


Hope it helps, cheers!

									


			
		
	
         
           
         
         
    
	








		

		

			

				
					Database, Errors and Bugs
			  

			
ORA-02429 on Drop Tablespace
			
			

				
		
			
			
			
		
				
				 , , 		
					


		


		
				

					
Ok, so here is the error:


SQL> DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key


Hmm… Sounds pretty clear, right? So how to workaround it?


This is documented on MOS Drop Tablespace Failed with ORA-02429: cannot drop index used for enforcement of unique/primary key (Doc ID 1918060.1)


So, FIRST make sure this is not a problem for your application.

In case it is not, here is my step by step with some better scripting:


1) Disable the Constrains:


SQL> select 'ALTER TABLE '||owner||'.'||table_name||' DISABLE CONSTRAINT '|| constraint_name||';'
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes
where tablespace_name='MY_TBS');


2) In case of PKs, you may need to disable it with CASCADE clause:


SQL> select 'ALTER TABLE '||owner||'.'||table_name||' DISABLE CONSTRAINT '|| constraint_name||' cascade;'
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes
where tablespace_name='MY_TBS');


3) Drop the tablespace:


SQL> DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES;


Hope it helps,

Cheers!