By Clemens Bleile

After cloning e.g. a production database into a database for development or testing purposes, the DBA has to make sure that no activities in the cloned database have an impact on data in other production databases. Because after cloning production data jobs may still try to modify data through e.g. db-links. I.e. scheduled database jobs must not start in the cloned DB and applications connecting to the cloned database must not modify remote production data. Most people are aware of this issue and a first measure is to start the cloned database with the DB-parameter

job_queue_processes=0

That ensures that no database job will start in the cloned database. However, before enabling scheduler jobs again, you have to make sure that no remote production data is modified. Remote data is usually accessed through db-links. So the second step is to handle the db-links in the cloned DB.

In a recent project we decided to be strict and drop all database links in the cloned database.
REMARK: Testers and/or developers should create the needed db-links later again pointing to non-production data.
But how to do that, because private DB-Links can only be dropped by the owner of the db-link? I.e. even a connection with SYSDBA-rights cannot drop private database links:

sys@orcl@orcl> connect / as sysdba
Connected.
sys@orcl@orcl> select db_link from dba_db_links where owner='CBLEILE';
 
DB_LINK
--------------------------------
CBLEILE_DB1
PDB1
 
sys@orcl@orcl> drop database link cbleile.cbleile_db1;
drop database link cbleile.cbleile_db1
                   *
ERROR at line 1:
ORA-02024: database link not found
 
sys@orcl@orcl> alter session set current_schema=cbleile;
 
Session altered.
 
sys@orcl@orcl> drop database link cbleile_db1;
drop database link cbleile_db1
*
ERROR at line 1:
ORA-01031: insufficient privileges

We’ll see later on how to drop the db-links. Before doing that we make a backup of the db-links. That can be achieved with expdp:

Backup of db-links with expdp:

1.) create a directory to store the dump-file:

create directory prod_db_links as '<directory-path>';

2.) create the param-file expdp_db_links.param with the following content:

full=y
INCLUDE=DB_LINK:"IN(SELECT db_link FROM dba_db_links)"

3.) expdp all DB-Links

expdp dumpfile=prod_db_links.dmp logfile=prod_db_links.log directory=prod_db_links parfile=expdp_db_links.param
Username: <user with DATAPUMP_EXP_FULL_DATABASE right>

REMARK: Private db-links owned by SYS are not exported by the command above. But SYS must not own user-objects anyway.

In case the DB-Links have to be restored you can do the following:

impdp dumpfile=prod_db_links.dmp logfile=prod_db_links_imp.log directory=prod_db_links
Username: <user with DATAPUMP_IMP_FULL_DATABASE right>

You may also create a script prod_db_links.sql with all ddl (passwords are not visible in the created script):

impdp dumpfile=prod_db_links.dmp directory=prod_db_links sqlfile=prod_db_links.sql
Username: <user with DATAPUMP_IMP_FULL_DATABASE right>

Finally drop the directory again:

drop directory prod_db_links;

Now, that we have a backup we can drop all db-links. As mentioned earlier, private db-links cannot be dropped, but you can use the following method to drop them:

As procedures are running with definer rights by default, we can create a procedure under the owner of the db-link and in the procedure drop the dblink. SYS has the privileges to execute the procedure. The following example will drop the db-link cbleile.cbleile_db1:

select db_link from dba_db_links where owner='CBLEILE';
 
DB_LINK
--------------------------------
CBLEILE_DB1
PDB1

create or replace procedure CBLEILE.drop_DB_LINK as begin
execute immediate 'drop database link CBLEILE_DB1';
end;
/
 
exec CBLEILE.drop_DB_LINK;
 
select db_link from dba_db_links where owner='CBLEILE';
 
DB_LINK
--------------------------------
PDB1

I.e. the db-link CBLEILE_DB1 has been dropped.
REMARK: Using a proxy-user would also be a possibility to connect as the owner of the db-link, but that cannot be automated in a script that easily.

As we have a method to drop private db-links we can go ahead and automate creating the drop db-link commands with the following sql-script drop_all_db_links.sql:

set lines 200 pages 999 trimspool on heading off feed off verify off
set serveroutput on size unlimited
column dt new_val X
select to_char(sysdate,'yyyymmdd_hh24miss') dt from dual;
spool drop_db_links_&&X..sql
select 'set echo on feed on verify on heading on' from dual;
select 'spool drop_db_links_&&X..log' from dual;
select 'select count(*) from dba_objects where status='''||'INVALID'||''''||';' from dual;
REM Generate all commands to drop public db-links
select 'drop public database link '||db_link||';' from dba_db_links where owner='PUBLIC';
REM Generate all commands to drop db-links owned by SYS (except SYS_HUB, which is oracle maintained)
select 'drop database link '||db_link||';' from dba_db_links where owner='SYS' and db_link not like 'SYS_HUB%';
PROMPT
REM Generate create procedure commands to drop private db-link, generate the execute and the drop of it.
declare
   current_owner varchar2(32);
begin
   for o in (select distinct owner from dba_db_links where owner not in ('PUBLIC','SYS')) loop
      dbms_output.put_line('create or replace procedure '||o.owner||'.drop_DB_LINK as begin');
      for i in (select db_link from dba_db_links where owner=o.owner) loop
         dbms_output.put_line('execute immediate '''||'drop database link '||i.db_link||''''||';');
      end loop;
      dbms_output.put_line('end;');
      dbms_output.put_line('/');
      dbms_output.put_line('exec '||o.owner||'.drop_DB_LINK;');
      dbms_output.put_line('drop procedure '||o.owner||'.drop_DB_LINK;');
      dbms_output.put_line('-- Seperator -- ');
   end loop;
end;
/
select 'select count(*) from dba_objects where status='''||'INVALID'||''''||';' from dual;
select 'set echo off' from dual;
select 'spool off' from dual;
spool off
 
PROMPT
PROMPT A script drop_db_links_&&X..sql has been created. Check it and then run it to drop all DB-Links.
PROMPT

Running above script generates a sql-script drop_db_links_<yyyymmdd_hh24miss>.sql, which contains all drop db-link commands.

sys@orcl@orcl> @drop_all_db_links
...
A script drop_db_links_20200509_234906.sql has been created. Check it and then run it to drop all DB-Links.
 
sys@orcl@orcl> !cat drop_db_links_20200509_234906.sql
 
set echo on feed on verify on heading on
 
spool drop_db_links_20200509_234906.log
 
select count(*) from dba_objects where status='INVALID';
 
drop public database link DB1;
drop public database link PDB2;
 
create or replace procedure CBLEILE.drop_DB_LINK as begin
execute immediate 'drop database link CBLEILE_DB1';
execute immediate 'drop database link PDB1';
end;
/
exec CBLEILE.drop_DB_LINK;
drop procedure CBLEILE.drop_DB_LINK;
-- Seperator --
create or replace procedure CBLEILE1.drop_DB_LINK as begin
execute immediate 'drop database link PDB3';
end;
/
exec CBLEILE1.drop_DB_LINK;
drop procedure CBLEILE1.drop_DB_LINK;
-- Seperator --
 
select count(*) from dba_objects where status='INVALID';
 
set echo off
 
spool off
 
sys@orcl@orcl>

After checking the file drop_db_links_20200509_234906.sql I can run it:

sys@orcl@orcl> @drop_db_links_20200509_234906.sql
sys@orcl@orcl> 
sys@orcl@orcl> spool drop_db_links_20200509_234906.log
sys@orcl@orcl> 
sys@orcl@orcl> select count(*) from dba_objects where status='INVALID';
 
  COUNT(*)
----------
   1
 
1 row selected.
 
sys@orcl@orcl> 
sys@orcl@orcl> drop public database link DB1;
 
Database link dropped.
 
sys@orcl@orcl> drop public database link PDB2;
 
Database link dropped.
 
sys@orcl@orcl> 
sys@orcl@orcl> create or replace procedure CBLEILE.drop_DB_LINK as begin
  2  execute immediate 'drop database link CBLEILE_DB1';
  3  execute immediate 'drop database link PDB1';
  4  end;
  5  /
 
Procedure created.
 
sys@orcl@orcl> exec CBLEILE.drop_DB_LINK;
 
PL/SQL procedure successfully completed.
 
sys@orcl@orcl> drop procedure CBLEILE.drop_DB_LINK;
 
Procedure dropped.
 
sys@orcl@orcl> -- Seperator --
sys@orcl@orcl> create or replace procedure CBLEILE1.drop_DB_LINK as begin
  2  execute immediate 'drop database link PDB3';
  3  end;
  4  /
 
Procedure created.
 
sys@orcl@orcl> exec CBLEILE1.drop_DB_LINK;
 
PL/SQL procedure successfully completed.
 
sys@orcl@orcl> drop procedure CBLEILE1.drop_DB_LINK;
 
Procedure dropped.
 
sys@orcl@orcl> -- Seperator --
sys@orcl@orcl> 
sys@orcl@orcl> select count(*) from dba_objects where status='INVALID';
 
  COUNT(*)
----------
   1
 
1 row selected.
 
sys@orcl@orcl> 
sys@orcl@orcl> set echo off
sys@orcl@orcl> 
sys@orcl@orcl> select owner, db_link from dba_db_links;

OWNER				 DB_LINK
-------------------------------- --------------------------------
SYS				 SYS_HUB

1 row selected.

A log-file drop_db_links_20200509_234906.log has been produced as well.

After dropping all db-links you may do the following checks as well before releasing the cloned database for the testers or the developers:

  • disable all jobs owned by not Oracle maintained users. You may use the following SQL to generate the commands in sqlplus:

select 'exec dbms_scheduler.disable('||''''||owner||'.'||job_name||''''||');' from dba_scheduler_jobs where enabled='TRUE' and owner not in (select username from dba_users where oracle_maintained='Y');
  • check all directories in the DB and make sure the directory-paths do not point to shared production folders

column owner format a32
column directory_name format a32
column directory_path format a64
select owner, directory_name, directory_path from dba_directories order by 1;
  • mask sensitive data, which should not be visible to testers and/or developers.

At that point you are quite sure to not affect production data with your cloned database and you can set
job_queue_processes>0
again and provide access to the cloned database to the testers and/or developers.