Category: Errors and Bugs

Database, Errors and Bugs

ORA-65532: cannot alter or drop automatically created indexes

 , , ,

Hello all,
Quickly walking through this, as it was new to me (as for everybody, I guess): So what if I’m not happy with Oracle algorithm and I want to drop and AUTO Index on 19c?

Should be easy, like “drop index XXXX;” right? Wrong. See the struggle:

SQL> drop index GREPORA."SYS_AI_9xu652x5fyu5i";
drop index GREPORA."SYS_AI_9xu652x5fyu5i"
                 *
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

OK, so let’s have a look on my auto created indexes. You can see them with flag AUTO as YES on query below:

SQL> select owner, index_name, auto, tablespace_name from dba_indexes natural where auto='YES';
OWNER              INDEX_NAME    AUTO    TABLESPACE_NAME
________ _______________________ _______ __________________
GREPORA  SYS_AI_9xu652x5fyu5i    YES     GREPORA
GREPORA  SYS_AI_few32swe423dw    YES     GREPORA
GREPORA  SYS_AI_94osd824n202f    YES     GREPORA

Ok, so I cannot drop, let me alter it and set it as unusable, then, as it would archive my intend anyway:

SQL> alter index ADMIN."SYS_AI_9xu652x5fyu5i" unusable;
alter index ADMIN."SYS_AI_9xu652x5fyu5i" unusable
 *
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

Hmmm, so it means I cannot alter it as well?
Well, kind of, there is something I can do:

SQL> alter index ADMIN."SYS_AI_9xu652x5fyu5i" rebuild tablespace GREPORA2 online;
Index altered.

Noice, thanks for nothing. What is changed?
Well, you can use a workaround to move it to a new tablespace and then drop the tablespace:

SQL> drop tablespace GREPORA2 including contents;
Tablespace dropped.

And index will be gone:

SQL> select owner,index_name,auto,tablespace_name from dba_indexes natural where auto='YES';
OWNER              INDEX_NAME    AUTO    TABLESPACE_NAME
________ _______________________ _______ __________________
GREPORA  SYS_AI_few32swe423dw    YES     GREPORA
GREPORA  SYS_AI_94osd824n202f    YES     GREPORA

OK, so that’s bad, right… What the worse part? If you simply drop it, Oracle will probably recreate it, based on algorithm, right?
Gee, thanks for nothing (again)!

This, though, is easier to change: Simply alter the Audo Indexing to report mode:

exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT');

This is interesting right?
Ok, we have more options, we can even exclude specific schemas from AUTO_INDEX, choose a default tablespace, define retentions and a lot of things.

As usual, the best reference I could find: https://oracle-base.com/articles/19c/automatic-indexing-19c

Just one thing you might want to consider and it’s not on Tim Hall’s post. Specially if the index is bad for one specific query, is to avoid using it during that specific query. We can do it with session/system parameter:

“_optimizer_use_auto_indexes”=OFF

Well, hope it helps you. Cheers!

Database, Errors and Bugs, MySQL

MySQL 8 requested authentication method unknown

 , , ,

Hey folks,
Be aware when you’re migrating from MySQL 5.7 to 8.0 on how you’re doing and what kind of applications have access to it. The version 8.0 introduced a new authentication plugin called caching_sha2_password and it’s the default auth plugin now.  Here is the list of compatible connectors to check if your app is ok with that or if you should call the dev team.

Example: If, for some reason, you get stuck with the following error (PHP sample), you have to do a few changes.

Connect Error: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

Edit your config file and set the option default-authentication-plugin with mysql_native_password value, restart the server and set the password again with the following command.

Config File:

[mysqld]
/... other configs .../
default-authentication-plugin=mysql_native_password

SQL:

ALTER USER 'adv'@'localhost' IDENTIFIED WITH mysql_native_password BY '1AB@8CD#E91F22!';

New users should be created the same way. Be aware that you have to do that just for users that the application will use. Regular users for DBAs and queries you don’t have to do that change.

So, look out before just doing a update on your server, or you could break your app 😉

Database, Errors and Bugs

Everything Stopped on ORA-02002!

 , ,

Hi all,
So, I got contacted by a client in emergency because no more sessions were being opened on database with error ORA-02002. Even worse, everything stopped working and started raising this error.

Unusual, right?

So , checking on this trace generated for error here is what I found:

ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8 in tablespace AUDIT_TBS

Seems client has moved the AUD$ to another tablespace to avoid filling SYSTEM, just like described in this post. The tablespace got full, however, due bad monitoring, it got full.

As the auditing facility was unable to write to the AUDIT_TRAIL table, SQL statements that were currently being audited also failed.

What to do?

  1. Turn off auditing for the database
  2. Restart database
  3. Add space to tablespace
  4. Re-enable auditing, if required.

To avoid application back and forth, I did it in restrict mode, until get all fixed.

To avoid this sort of issue:

  • Be sure you are monitoring properly the new tablespace.
  • Place the audit tablespace on a reliable disk location and perhaps mirrored for protection.

Hope it helps,
Cheers!

Database, Errors and Bugs

12.2 Scheduler Job Disappeared After Creation

 , ,

Hi all,
Just sharing an experience here. Recently in a migrated 12.2 environment I created a Scheduler Job and it simply disappeared. How come?
Well, seems since 12.2 the AUTO_DROP attribute is set to true by default. I absolutely don’t know why. So I had to recreate the job and set auto_drop to false, simple like this.

I couldn’t find any related bug on MOS, but I’d relate this as a bug, once the default behavior have changed with no prior information.
So, now on, I strongly suggest you to check on your job AUTO_DROP attribute after job creation.

Here is a test case that reproduces the problem:

In a 12.2 database:

begin 
  DBMS_SCHEDULER.create_job (
    job_name        => 'TEST_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN null; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'SYSTIMESTAMP + INTERVAL ''10'' SECOND',
    end_date        => NULL,
    enabled         => false,
    comments        => 'Test job');
end;
/
select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB';
-- Job should appear here

begin 
  dbms_scheduler.enable('TEST_JOB'); 
  dbms_scheduler.run_job('TEST_JOB',false); 
end;
/
 
select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB';

-- There is no job now in 12.2.

You can take the AUTO_DROP easily from the query above, or using getting the job_ddl and you’ll see the AUTO_DROP set to true, as in the bottom of this page by Tim Hall.

Hope it helps!
Cheers!

Database, Errors and Bugs

ORA-00600 [ipc_recreate_que_2]

 , ,

Hi all,
So this week I started receiving this error in a client environment. This was happening due Bug 26803191 – Getting ORA-00600 [ipc_recreate_que_2] instead of ORA-27515 – superseded (Doc ID 26803191.8)
In summary, failures due to low memlock limit are leading ORA-00600 [ipc_recreate_que_2] instead of ORA-27515, patching ensure that ORA-27515 is raised on failures due to low memlock limit instead of this ORA-600.

As workaround, the memlock limited can be changed, as also described in MOS ORA-00600: Internal Error Code, Arguments: [ipc_recreate_que_2] When Running Exachk (Doc ID 2480088.1).

Before:

[boesing@exa1dbadm01 trace]$ grep memlock /etc/security/limits.conf |grep oracle
# - memlock - max locked-in-memory address space (KB)
oracle soft memlock 237809520
oracle hard memlock 237809520

After:

[boesing@exa1dbadm01 ~]$ grep memlock /etc/security/limits.conf
# - memlock - max locked-in-memory address space (KB)
oracle soft memlock unlimited
oracle hard memlock unlimited
* soft memlock 32768
* hard memlock 32768

Hope it helps,
Cheers!

Database, Errors and Bugs

ORA-00600: internal error code, arguments: [13011], [7907] on SYS.DBMS_AQ_INV

 , , , ,

Hi all,

So a couple weeks ago I was facing the following on a client environment. It was appearing every 10 mins on alert.log:

ORA-00600: internal error code, arguments: [13011], [7907], [12679954], [1], [13461738], [0], [], [], [], [], [], []
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_INV", line 1248

I could find several references to ORA-600 [13011], always related to some internal corruption. As this table is related to Advanced Queueing, decided to check on Scheduler Job table structures:

SQL> analyze table SYS.SCHEDULER$_EVENT_LOG validate structure cascade; 

Table analyzed. 

SQL> analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure cascade; 
analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure cascade 
* 
ERROR at line 1: 
ORA-01499: table/index cross reference failure - see trace file

SQL> analyze table SYS.SCHEDULER$_JOB_RUN_DETAILS validate structure;

Table analyzed.

Ahá!
Also on the generated trace file:

2019-03-01 22:26:37.736 
SESSION ID:(39.32751) 2019-03-01 22:26:37.736 
CLIENT ID) 2019-03-01 22:26:37.736 
SERVICE NAME:(SYS$USERS) 2019-03-01 22:26:37.736 
MODULE NAME:(sqlplus.exe) 2019-03-01 22:26:37.736 
CLIENT DRIVER:(SQL*PLUS) 2019-03-01 22:26:37.736 
ACTION NAME) 2019-03-01 22:26:37.736 
CONTAINER ID:(1) 2019-03-01 22:26:37.736 
Table/Index row count mismatch 
table 273184 : index 275017, 1832 
Index root = tsn: 1 rdba: 0x00c0128a

So ANALYZE on table SCHEDULER$_JOB_RUN_DETAILS fails with CASCADE but succeeds without CASCADE. This means that there is a problem with one of the index of this table. It has gone out of sync with the table.

SQL> select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where table_name='SCHEDULER$_JOB_RUN_DETAILS';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index SYS.I_SCHEDULER_JOB_RUN_DETAILS rebuild;

SQL> alter index SYS.I_SCHEDULER_JOB_RUN_DETAILS rebuild online;

Index altered.

After this, error solved and no more recurrence of that ORA-600.

Also note I couldn’t find any document about this on MOS, so this is kind of exclusive by now. 🙂

Cheers!

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!