Changing database link definition in pl/sql

To change database link definition in Oracle database we have to connect as a owner of this database link. In most cases it's possible by using output from dba_db_links and connect from sqlplus as a owner.

But if we use undocumented function dbms_sys_sql.parse_as_user, then it's possible to change definition from pl/sql:

-- if we use specific password schema
define pass_prefix = 'pre'
define pass_postfix = 'post'

DECLARE
  sqltext  VARCHAR2(1000);
  l_result NUMBER;
  l_cursor INTEGER;
  l_cursors dbms_sql.Number_Table;
BEGIN
  -- modify db_links
  FOR cur IN
  (SELECT u.user_id,
    d.OWNER,
    d.DB_LINK,
    d.USERNAME,
    d.HOST
  FROM dba_db_links d,
    dba_users u
  WHERE d.owner=u.username
  )
  LOOP
    --parse the cursor only if we haven't already
    sqltext:= 'alter database link "'||cur.DB_LINK||'" connect to '||cur.USERNAME||' identified by &&pass_prefix'||cur.USERNAME||'&&pass_postfix';
    IF ( NOT l_cursors.exists(cur.user_id) ) THEN
      l_cursors(cur.user_id):=dbms_sys_sql.open_cursor;
      --parsing anonymous PL/SQL block as a job owner
      dbms_sys_sql.parse_as_user( c => l_cursors(cur.user_id), STATEMENT => sqltext, language_flag => dbms_sql.native, userid => cur.user_id );
    END IF;
    --bind the job number
    --remove the job by executing
    l_result:=sys.dbms_sys_sql.execute(l_cursors(cur.user_id));
  END LOOP;
END;
/

RMAN - restore process failed

On test server I had to restore database (3TB).
Due to network issue RESTORE (not recovery) process  failed. In normal situation I have to  start restore process from beginning once again but I already have almost 2.5 TB restored. How to use this data?

Database is in mount mode and in view V$DATAFILE_COPY we have information about file_id and datafile name (this information is needfull if OMF is used).

select 'set newname for datafile '||FILE#||' to '''||name||''';' from V$DATAFILE_COPY where name is not null order by file#;


This query generate set newname for all datafiles already restored.

run {

set until time "to_date('2015-01-05 00:11:00','yyyy-mm-dd hh24:mi:ss')";
<generated set newname> 

restore database;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
recover database DELETE ARCHIVELOG MAXSIZE 90G;
}

and if we run this script, rman continue restore process.

If in v$datafile_copy you don't have information for some datafiles, then RMAN not restored then already and for these datafiles you have to created fake name.

"ORA-01555: snapshot too old" after failover physical standby

Recently I received error on  test database:

ORA-01555: snapshot too old: rollback segment number 53 with name "_SYSSMU53_526582059$" too small

simple problem with a simple solution :)

but...

This  is a test database and almost nobody use this database. I checked a UNDO utilization and was on very low level.

I changed undo guarantee, undo retention and I added additional datafiles to UNDO tablespace.

Nothing help...

and now the background on how we prepare the test database.

This test database is prepared as I storage snapshot from standby. Before preparing snapshot recovery managed standby had been cancel

recover managed standby database cancel;

and stopped.
So everything should be OK. 

I thought that then problem can be with UNDO and I decided to recreate UNDO tablespace. I create new one and switched database to use new UNDO tablespace. After that I dropped old UNDO tablespace and I executed query once again.

and...

the same error but without indicating the UNDO segment.

ORA-01555: snapshot too old: rollback segment number  with name "" too small

In original query application use two sub-queries, I wanted to find exactly where is the problem.

In execution plan I found that optimizer for one of the sub-query use index. After eliminating this sub-query everything works :)

BINGO

I tried rebuild this index and once again ORA-01555, so I decided to drop index and create index once again. Successfully...

After that original query works perfectly

OMF and Physical Standby

In normal Data Guard configuration on primary and standby database I want use the same parameters, especially storage parameters eg "db_create_file_dest". This parameter determines default location
Oracle-managed datafiles.

Sometimes I need create tablespace with manually setting datafile name (diffrent directory) and if on standby database OMF is enabled then datafile is created in different directory than on primary - standby use  "db_create_file_dest" location and after that we have to disable recovery mode on standby and move datafile on OS level and rename datafile on standby.
But if before creating tablespace on primary I set  

alter system set db_create_file_dest="" scope=both;

then on standby, database create datafile in the same directory as on primary database.

ORA-08106: cannot create journal table

After killing the session, where rebuilding index was performed, sometimes get an error:
 
SQL> ALTER INDEX ind_example_1 rebuild partition DATA_PART1 online
*
ERROR at line 1:
ORA-08106: cannot create journal table TEST.SYS_JOURNAL_2918162


solution for this error is running procedure from DBMS_REPAIR package:


declare
isclean boolean;
begin
isclean := false;
while isclean = false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN
(dbms_repair.all_index_id, dbms_repair.lock_wait);
dbms_lock.sleep (10);
end loop;
end;
/

Segment advisor - query

SELECT o.type AS object_type,
  o.attr1     AS schema,
  O.ATTR2     AS OBJECT_NAME,
  F.MESSAGE,
  ROUND(TO_NUMBER(SUBSTR(F.MORE_INFO,INSTR(F.MORE_INFO,':',1,1)+1,INSTR(F.MORE_INFO,':',1,2)-INSTR(F.MORE_INFO,':',1,1)-1))/(1024*1024)) ALLOCATED_SPACE,
  ROUND(TO_NUMBER(SUBSTR(F.MORE_INFO,INSTR(F.MORE_INFO,':',1,3)+1,INSTR(F.MORE_INFO,':',1,4)-INSTR(F.MORE_INFO,':',1,3)-1))/(1024*1024)) USED_SPACE,
  round(to_number(SUBSTR(f.more_info,INSTR(f.more_info,':',1,5)+1,INSTR(f.more_info,':',1,6)-INSTR(f.more_info,':',1,5)-1))/(1024*1024)) reclaim_space
FROM dba_advisor_findings f
JOIN DBA_ADVISOR_OBJECTS O
ON F.OBJECT_ID  = O.OBJECT_ID
AND F.TASK_NAME = O.TASK_NAME
where F.MESSAGE like '%shrink%'
ORDER BY 7 desc;

Monitoring RMAN backup

Query for EM 11 to monitoring all backups:

SELECT b.database_name                         AS "DB Name",
  t.type_qualifier1                            AS "Version",
  b.host                                       AS "Server Name" ,
  b.target_type                                AS "Type",
  TO_CHAR(b.start_time, 'YYYYMMDD-HH24:MI:SS') AS "Start Time",
  TO_CHAR(b.end_time, 'YYYYMMDD-HH24:MI:SS')   AS "End Time",
  b.input_type "Backup Info",
  b.time_taken_display   AS "Time taken",
  b.output_bytes_display AS "Final Size Bytes",
  status                 AS "Status"
FROM mgmt$ha_backup b,
  mgmt$target t
WHERE b.target_guid = t.target_guid
AND b.start_time    > sysdate -2
ORDER BY type_qualifier1,
  END_TIME ;

and progress restoring or backup from RMAN:

SELECT OPNAME,
  SOFAR                /TOTALWORK*100 PCT,
  TRUNC(TIME_REMAINING /60) MIN_RESTANTES,
  TRUNC(ELAPSED_SECONDS/60) MIN_ATEAGORA
FROM V$SESSION_LONGOPS
WHERE TOTALWORK>0
AND OPNAME LIKE '%RMAN%';