"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%';