get file

function get_File ( p_directory varchar2, p_file_name varchar2) return BLOB
is

 l_bf BFILE;
 l_amount  INTEGER := 32767;
 l_position  INTEGER := 1;
 buffer       RAW(32767);
 l_bb          BLOB;
 l_file_path  varchar2(256);
 begin

   l_bf := bfilename(  p_directory , p_file_name );  
 
   dbms_lob.open(l_bf, dbms_lob.lob_readonly);
   DBMS_LOB.CREATETEMPORARY(l_bb, TRUE, DBMS_LOB.SESSION);
    LOOP
     begin
     dbms_lob.read(l_bf,l_amount, l_position, buffer);
     EXCEPTION
         WHEN NO_DATA_FOUND THEN
            EXIT;
      end;
     dbms_lob.writeappend(l_bb,l_amount,buffer);
     l_position := l_position + l_amount;
    END LOOP;
   
   dbms_lob.close(l_bf);

   return l_bb;
 

end get_File;

store password in korn

log(){
    typeset rundate=$(date +"%F %X")
printf "${rundate} -- $*\n"
printf "${rundate} -- $*\n" >> $screen_log
}

do_sql(){
sql=$*
out=$(sqlplus -s '/ as sysdba'<<-eof p=""> whenever sqlerror exit 1
whenever oserror exit 1
set heading off feedback off serveroutput on trimout on pagesize 0
$sql
EOF
)
ret=$?
printf '%s' "$out"
return $ret
}

store_pwd(){

load_env $destdb

log "checking database open mode..."
sql="select status from v\$instance;"
log "running sql: $sql"
out=$(do_sql "$sql")
ret=$?
[[ $ret -ne 0 ]] && { log "*** failed to query database -- \n$out"; exit 1; }
log "got: ${out}"
typeset local_inst_status=$out
[[ $local_inst_status != "OPEN" ]] && { log "*** $destdb is not OPEN, not able to store passwords; skipping"; return 102; }
sql=$(cat<<-eof p=""> set echo off
set lines 1000
col lines for a1000
select u.name ||','|| u.spare4 ||','|| u.password ||','|| du.default_tablespace ||','|| du.profile||','||u.defrole||','||du.authentication_type||','||'"'||u.ext_username||'"' lines
from user$ u join dba_users du on (u.name=du.username) where u.name in ('SYS','SYSTEM','ORA_NCYCLE') ;
EOF
)

log "querying password hashes..."
out=$(do_sql "$sql")
ret=$?
[[ $ret -ne 0 ]] && { log "*** failed to query database -- \n$out"; return 1; }

touch $pwd_store
chmod 600 $pwd_store
echo "$out" > $pwd_store
return $?

}
restore_pwd(){
# return 102: file hashes are not there->try with cloakware
# return 1: error

load_env $destdb
log "checking dump file at $pwd_store"

[[ ! -e $pwd_store ]] && { log "dump file with hashes does not exist"; return 102; }

cnt=$(grep -E "SYS|SYSTEM|ORA_NCYCLE" $pwd_store | wc -l)
[[ $cnt -ne 3 ]] && { log "dump file does not contain rows for SYS|SYSTEM|ORA_NCYCLE"; return 102;}

while read line 
do
usr=$(echo $line| awk -F, '{print $1}')
hash10g=$(echo $line| awk -F, '{print $3}')
hash11g=$(echo $line| awk -F, '{print $2}')

if [[ ! -z $hash11g ]];then
sql=$(cat<<-eof font="">
set feed on
alter user $usr identified by values '$hash11g;$hash10g';
EOF
)
elif [[ ! -z $hash10g ]];then 
sql=$(cat<<-eof font="">
set feed on
alter user $usr identified by values '$hash10g';
EOF
)
else 
log "password hash not found for user $usr"
return 102
fi

log "setting password for user $usr"
out=$(do_sql "$sql")
ret=$?
if [[ $ret -ne 0 && $out != *ORA-28007* ]]; then
log "*** failed to run sql -- \n$out"
return 1
elif [[ $ret -ne 0 && $out = *ORA-28007* ]]; then
log "password is already set"
else 
log "password set"
fi


done < $pwd_store

rm $pwd_store

return 0

}

run sqlplus in perl

sub mask_pwd {
my $str = shift;
$str =~ s/conn(ect)?\s+(.*?)\/(.*?)([@\s])/connect $2\/(hidden)$4/ig;
$str =~ s/identified\s+by(\s+values)?\s+(.+?)(\s)/identified by$1 (hidden) $3/ig;
return $str;

}

sub run_sql {

my $sql = shift;
my $error_handling = shift;
my $error_mode;
my $exec_sqlfile;

if (defined $error_handling) {

switch ($error_handling) {

case 'IGNORE_SQL_ERROR' {
$error_mode = "WHENEVER OSERROR exit 1; ";
}
case 'IGNORE_OS_ERROR' {
$error_mode = "WHENEVER SQLERROR exit SQL.SQLCODE; ";
}
case 'IGNORE_ALL_ERROR' {
$error_mode = "";
}

}

}
else { # default
$error_mode = "WHENEVER OSERROR exit 1; \nWHENEVER SQLERROR exit SQL.SQLCODE; ";
}


if ( $sql =~ /^\//) {  # starting with '/' -> filename  
$exec_sqlfile = $sql;
}
else {
&xOpen_File_Write( $sqlplusfile );
&xWrite_File( $sql );
&xClose_File;

$exec_sqlfile = $sqlplusfile;
}
 
   
  my $str = qq{ . /cs/oracle/local/dba/bin/basenv.ksh; . /cs/oracle/local/dba/bin/oraenv.ksh $instance;
sqlplus -s \\"/ as sysdba\\" << EOF > $sqlpluslogfile 2>&1
${error_mode}
\@$exec_sqlfile
EOF
  };

$cmd = &xKSH_Call_Out_String( $str );

&xWrite_Log("running:\n$error_mode\n".&mask_pwd($sql) );

my $log            = qx( $cmd );
my $err            = $?>>8;

chomp(my $sqllog         = &xGobble( $sqlpluslogfile ));

if ($err) {
&xWrite_Log( &mask_pwd($sqllog) );
return (-1, $sqllog);
}
else {

if ( $sql !~ /feed off/ ) {
&xWrite_Log("got:\n".&mask_pwd($sqllog));
}

return (0, $sqllog) ;

}

}

dumping system, role and proxy grants for users

set long 10000000
      set longchunksize 1000000
      set pages 0 head off feed off echo off
      set lines 500
      col lines for a500 word_wrapped
      exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
      select '-- SYSTEM GRANTS' from dual;
      select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',du.username) as lines from dba_users du where du.username in (${preserve_grant_quoted}) and exists (select grantee from dba_sys_privs where grantee=du.username);
      select '-- ROLE GRANTS' from dual;
      select dbms_metadata.get_granted_ddl('ROLE_GRANT',du.username) as lines from dba_users du where du.username in (${preserve_grant_quoted}) and exists (select grantee from dba_role_privs where grantee=du.username);
      select '-- DEFAULT ROLES' from dual;
      select dbms_metadata.get_granted_ddl('DEFAULT_ROLE',du.username) as lines from dba_users du where du.username in (${preserve_grant_quoted}) ;
      select '-- PROXY GRANTS' from dual;
      select dbms_metadata.get_granted_ddl('PROXY', pu.client) as lines  from proxy_users pu where pu.client in (${preserve_grant_quoted}) ;

How to test a DB-Link as SYS

VARIABLE owner VARCHAR2(30)
VARIABLE link VARCHAR2(128)
set serveroutput on
set define off
set verify off
set feed off
DECLARE 
cur_remote INTEGER;
lv_usr INTEGER;
lv_ignore INTEGER;
lv_db_name VARCHAR2(256);
lv_link_name VARCHAR2(128);

BEGIN

:owner:='$db_link->[0]';
:link:='$db_link->[1]';

FOR x IN ( select dbl.owner, dbl.db_link, dbl.username, dbl.host, du.user_id from 
dba_db_links dbl left join dba_users du on (dbl.owner = du.username)
where dbl.owner=:owner and dbl.db_link=:link
) LOOP

cur_remote := sys.dbms_sys_sql.open_cursor();

IF ( x.owner != 'PUBLIC' ) THEN
dbms_sys_sql.parse_as_user(
cur_remote,
'select property_value db_name
from database_properties\@'||x.db_link||
' where property_name=''GLOBAL_DB_NAME''',
DBMS_SQL.NATIVE,
x.user_id); 
ELSE
dbms_sys_sql.parse(
cur_remote,
'select property_value db_name
from database_properties\@'||x.db_link||
' where property_name=''GLOBAL_DB_NAME''',
DBMS_SQL.NATIVE); 
END IF;
 
dbms_sys_sql.define_column(cur_remote, 1, lv_db_name,256); 

lv_ignore := dbms_sys_sql.execute(cur_remote); 
 
IF dbms_sys_sql.fetch_rows(cur_remote)>0 THEN 
dbms_sys_sql.column_value(cur_remote, 1, lv_db_name); 
dbms_sys_sql.close_cursor(cur_remote); 
END IF;

-- dbms_output.put_line('x.db_link||','||sys_context('userenv','db_name')||','||x.owner||','||regexp_substr(lv_db_name,'[^.]+')||','||x.username);

sys.pk_compliance_sys.log_dblink_compliance (x.db_link, x.owner, regexp_substr(lv_db_name,'[^.]+'),
                             x.username, '', '' );
 
dbms_output.put_line('registered link '||x.db_link||' with taget db: '||regexp_substr(lv_db_name,'[^.]+'));

END LOOP;

EXCEPTION 
WHEN OTHERS THEN 
IF dbms_sys_sql.is_open(cur_remote) THEN 
dbms_sys_sql.CLOSE_CURSOR(cur_remote); 
END IF; 
RAISE;  
END;
/