Author: Dean Capps

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