Category: General

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$;

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.

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

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”

The below MS Word Document contains the steps required to set up Flashback on a primary and standby RAC.

ORA Flashback RAC with Standby

This PL/SQL code identifies a list of tables that exist in a schema. For each table, it issues a count(*) dynamically.

set serveroutput on;
spool get_all_tables
 
declare
    dyn_sql varchar(300);
    count_x number (10);
    temp_tab_name char(40);
begin
for t in(select owner, substr(table_name,01,40) as table_name from dba_tables where owner = '' order by table_name)
  loop
     temp_tab_name := t.table_name;
     dyn_sql := 'select count(*) from ' || t.owner || '.' || t.table_name;
     execute immediate dyn_sql into count_x;
     dbms_output.put_line(t.owner || ' ' || temp_tab_name || ' = ' || count_x || ' rows');
  end loop;
end;
/
spool off

If a data file is added on a primary with a physical standby and there is no appropriate file name convert, the file gets created in the Oracle home. On a RAC this may also report as a certain node unable to lock a file as the file got created in the Oracle home (non ASM) of a different node.

Log on to the standby

Stop mrp

Set standby_file_management = ‘MANUAL’

Find the name of the “bad file”. In my case it was /oracle/product/11.2.0.3/db_1/dbs/UNNAMED00034

You can find the bad file with:

select     name 
from       v$datafile 
where not name like '+%'
;

Find the correct name of the file from the primary.

The file will not actually exist in /opt/oracle. Then

alter database create datafile '/oracle/product/11.2.0.3/db_1/dbs/UNNAMED00034' as '+ASM mount point/usually DB name/datafile/correct name of file from primary';

The goal is to create the file in the correct location as it would have been created if the correct file name convert was in place.

Check v$datafile again

Set standby_file_management = ‘AUTO’

Start MRP

One of our applications began to experience ORA-02049: timeout: distributed transaction waiting for lock errors during their processing. The application was connecting via a service name to a four node RAC cluster. Continue reading “Mysterious contention!”