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

Purge materialized view log table

We found huge MV log (~14GB) on table which has 1G. The reason was that few months ago, after some maintenance we had to recreate materialized view (REFRESH FAST).

After recreating MV, log had two registered snapshots and leave data for first MV which was dropped. MV has been created to replicate data beatween two databases and inMV definition we using database links. After dropping MV,  information about registred MV had not been refreshed.

To purge log table from database which waiting for not existed MV I used dbms_mview.purge_mview_from_log procedure, but in first step I have to check which snapshot not exist (thanks Remek: http://remigium.blogspot.com/2013/08/the-orphaned-mview-registration-on.html)

SELECT s.mowner,
  s.master,
  s.snaptime,
  'exec dbms_mview.purge_mview_from_log('
  ||s.snapid
  ||');' fix1
FROM sys.slog$ s
WHERE NOT EXISTS
  (SELECT 1 FROM DBA_REGISTERED_SNAPSHOTS R WHERE S.SNAPID=R.SNAPSHOT_ID
  )
ORDER BY MOWNER,
  master;

and run purge_mview_from_log for not exited snapid.

After that all data for not existed MV will be remove, but only for this snap id, so we don't need run complete refresh for exited MV.

MV log table is a normall segment and it's no problem with shrinking segment by alter table MLOG$_EX_TABLE shrink space;

On our db after purging and shrinking, log table segment has 80MB.

SQL profile and invisible index

A few days ago we had a problem with the performance of several queries and we found that the problem is with queries which take different index than should be for some not bind variables.

So we decide set invisible parameter for this index. Before that I checked what queries use this index using v$sql_plan and I ran:

alter index index_owner.index_name INVISIBLE;

The list of queries that used this index there was one important query and after setting invisible for this index execution plan for this query start use different index and elapsed time increased almost 100 times. 

There was no possibility to add hint USE_INVISIBLE_INDEXES to this query directly, so I hat to use sql profile:

DECLARE
  clsql_text CLOB;
BEGIN
  SELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = '&SQL_ID';
  DBMS_SQLTUNE.IMPORT_SQL_PROFILE( sql_text => clsql_text, profile => sqlprof_attr('USE_INVISIBLE_INDEXES'), name => 'PROFILE_&SQL_ID', force_match => TRUE );
END;
/