Category: Databases

Simulating an Oracle error into the alert log

In an Oracle AWS RDS instance we do not have direct access to the server and hence we could not use our regular CRON scripts. I was testing an Oracle Stored procedure to replace this functionality and needed to simulate an error in the alert log. I found the below web site

Simulating ORA-errors

(nice site with a good explanation)

and used these commands:

alter session set events '942 incident(SIMULATED_ERROR)';
drop table tablethatdoesnotexist;
alter session set events '942 trace name context off';

The alert log contained:

ORA-00700: soft internal error, arguments: [EVENT_CREATED_INCIDENT], [942], [SIMULATED_ERROR], [], [], [], [], [], [], [], [], []
ORA-00942: table or view does not exist

This was exactly what I needed for my testing.

Current Date U.S. Timezone Daylight Savings and Standard time SQL

The following SQL will return a formatted DB2 version and a literal U.S. Timezone value for the current date.  The CURRENT TIMEZONE value changes between Daylight Saving time and Standard time so the SQL takes that into consideration based on the current DST start on Second Sunday of March and end on First Sunday in November.

SELECT STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),4,2),L,'0')  
       ||'.'||                                                 
       STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),6,2),L,'0')  
       AS DB2VER                                               
      ,CASE CURRENT TIMEZONE                                   
            WHEN -40000 THEN 'US/Eastern'                      
            WHEN -50000 THEN 'US/Central'                      
            WHEN -60000 THEN 'US/Mountain'                     
            WHEN -70000 THEN 'US/Pacific'                      
        END DB2TZ                                              
  FROM SYSIBM.SYSDUMMY1                                        
 WHERE CURRENT DATE BETWEEN                                    
          DATE(YEAR(CURRENT DATE)||'-03-08') +                 
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-03-08'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS                   
       AND                                                     
          DATE(DATE(YEAR(CURRENT DATE)||'-11-01') +            
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-11-01'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS - 1 DAY)          
UNION ALL                                                      
SELECT STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),4,2),L,'0')  
       ||'.'||                                                 
       STRIP(SUBSTR(GETVARIABLE('SYSIBM.VERSION'),6,2),L,'0')  
       AS DB2VER                                               
      ,CASE CURRENT TIMEZONE                                   
            WHEN -50000 THEN 'US/Eastern'                      
            WHEN -60000 THEN 'US/Central'                      
            WHEN -70000 THEN 'US/Mountain'                     
            WHEN -80000 THEN 'US/Pacific'                      
        END DB2TZ                                              
  FROM SYSIBM.SYSDUMMY1                                        
 WHERE CURRENT DATE NOT BETWEEN                                
          DATE(YEAR(CURRENT DATE)||'-03-08') +                 
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-03-08'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS                   
       AND                                                     
          DATE(DATE(YEAR(CURRENT DATE)||'-11-01') +            
          (SELECT                                              
             CASE DAYOFWEEK(DATE(YEAR(CURRENT DATE)||'-11-01'))
                WHEN 1 THEN 0                                  
                WHEN 2 THEN 6                                  
                WHEN 3 THEN 5                                  
                WHEN 4 THEN 4                                  
                WHEN 5 THEN 3                                  
                WHEN 6 THEN 2                                  
                WHEN 7 THEN 1                                  
             END FROM SYSIBM.SYSDUMMY1) DAYS - 1 DAY)          

 

 

Hail mary on db links

We had a situation wherein a colleague was attempting to get DB Links working. This was on a 11.2.0.4 database where password obfuscation is not allowed and the users were unsure of the passwords. After multiple changes to the passwords on the target system and drops and recreates of the links, the users continued to encounter errors while using the db link. We looked up a number of posts by different people on the web and then tried

alter system flush shared pool;

This resolved the issue. This was a non production environment. Please consider all the implications before you issue a flush in a production environment.

ORA-20005: object statistics are locked in AWS/RDS environment

While testing the AWS RDS environment, I ran across the issue that the statistics were locked:

begin 
     DBMS_STATS.GATHER_TABLE_STATS 
       (
          ownname => '"DEAN"',
          tabname => '"table name"',
          estimate_percent => 5
       );
end;

The error was:

Error starting at line : 6 in command -
begin 
     DBMS_STATS.GATHER_TABLE_STATS 
       (
          ownname => '"DEAN"',
          tabname => '"table name"',
          estimate_percent => 5
       );
end;
Error report -
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 34707
ORA-06512: at line 2

I checked with

select table_name, stattype_locked from dba_tab_statistics where table_name in ( select table_name from dba_tables where owner = 'DEAN');

The STATTYPE_LOCKED column contained the value of “ALL”. I was able to unlock this with

exec dbms_stats.unlock_table_stats('DEAN', 'table name');

This changed the value in the STATTYPE_LOCKED to “null” after which I was able to gather statistics.

Cleaning up the sys.aud$ table

I inherited a non-production database that had been auditing to the DB (audit_trail=DB) without any cleanup process in place. The sys.aud$ table had over 8 million rows of data. Before I set up the cleanup jobs, I decided to remove all the data in the table. I issued a truncate:

Error starting at line : 16 in command -
truncate table sys.aud$
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

A select on dba_objects indicated that sys.aud$ was a synonym

select owner, object_name, object_type from dba_objects where object_name = 'AUD$';
 
OWNER    OBJECT_NAME   OBJECT_TYPE
-------- -----------   -----------
SYS      AUD$          SYNONYM
SYSTEM   AUD$          TABLE

The table is SYSTEM.AUD$, but I thought I would check anyway:

select table_owner, table_name from dba_synonyms where owner = 'SYS' and synonym_name = 'AUD$';
 
TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYSTEM                         AUD$

Truncated the base table:

truncate table system.aud$;

Oracle 11.2.0.4 and obfuscated (identified by values) passwords

In versions of Oracle older than 11.2.0.4, I have always migrated DB links with dbms_metadata.get_ddl to get the ddl for a db link as I do not have the passwords for the user at the remote site. This provides the ddl as shown below with an obfuscated password:

CREATE DATABASE LINK "db_link_name" 
CONNECT TO "remote_user" 
IDENTIFIED BY VALUES '06AD92A1C4E671B083FE5A long sting here' 
USING '(DESCRIPTION=
(ADDRESS=                                                            
(PROTOCOL=TCP)(HOST=remote.server.com)(PORT=remote port))
    (CONNECT_DATA=                      
      (SERVER=dedicated)
      (SERVICE_NAME=remote service name)
    )                                       
)'                                                                              
;

Oracle 11.2.0.4 has a security enhancements to prevent the use of obfuscated passwords (See Doc ID 1905221.1). So you have 2 options:
a. Know the password and provide it explicitly
b. Use expdp/impdp to copy it over

Very often, option a. is not available as we do not know that password of the user at the remote site.

So I tried option b which works and creates the ddl as:

CREATE DATABASE LINK "db_link_name" 
CONNECT TO "remote_user" 
IDENTIFIED BY VALUES ':1'
USING '(DESCRIPTION=
(ADDRESS=                                                            
(PROTOCOL=TCP)(HOST=remote.server.com)(PORT=remote port))
    (CONNECT_DATA=                      
      (SERVER=dedicated)
      (SERVICE_NAME=remote service name)
    )                                       
)'                                                                              
;

This can be directly imported via impdp.

One approach to identifying the cause of ORA-01017

If you have a user account that is repeatedly getting locked out as a result of exceeding the number of failed password attempts (ORA-01017) and you are unable to identify who or what is causing it, this approach may be helpful:

Create a table as follows to hold the failed login information:

create table dean_logon_errors
(
login_date date,
os_user varchar(15),
ip_address varchar(50),
AUTHENTICATED_IDENTITY varchar(50),
AUTHENTICATION_METHOD varchar(50),
CLIENT_IDENTIFIER varchar(50), 
CLIENT_INFO varchar(50),
terminal varchar(50)
)
TABLESPACE name_of_table_space
;

Create a trigger that will be fired when an ORA-01017 error occurs:

CREATE OR REPLACE TRIGGER dean_logon_failures
AFTER SERVERERROR
ON DATABASE
BEGIN
  IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO dean_logon_errors values
    ( sysdate
     ,sys_context('USERENV','OS_USER')
     ,SYS_CONTEXT('USERENV','IP_ADDRESS')
     ,SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
     ,SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
     ,SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
     ,SYS_CONTEXT('USERENV','CLIENT_INFO')
     ,SYS_CONTEXT('USERENV','TERMINAL')
);
  END IF;
END logon_failures;
/

Wait for some time to allow the trigger to collect information and then execute the below SQLs:

alter session set nls_date_format = 'dd-MON-yy hh24.mi.ss';
select * from dean_logon_errors order by login_date desc;

Output will look like:

LOGIN_DATE         OS_USER         IP_ADDRESS      AUTHENTICATE AUTHENTICATION_ CLIENT_IDENTIFI CLIENT_INFO     TERMINAL
------------------ --------------- --------------- ------------ --------------- --------------- --------------- ---------------
13-JAN-17 20.50.00 john_doe_os     110.170.248.345 user_acct    PASSWORD
13-JAN-17 20.50.00 john_doe_os     110.170.248.345 user_acct    PASSWORD

Checking the use of undo space in the past

We had a circumstance in which we had to delete 4 billion rows from a 5 billion row table. This was done programmatically as we could not take an outage on the system. After the deletes were complete we were attempting to reclaim space on the index on a table, we were attempting to reclaim the space via an online rebuild of the index when we ran out of undo space: Continue reading “Checking the use of undo space in the past”