Create v$archived_log Local view on Oracle

[David_Fergen]

When Replicate needs to retrieve a redo log from the database, we run a select statement similar to the one below in order to retrieve the name of the redo logs: 

select NVL(name, ' '), status from <schema>.v$archived_log where first_time IS NOT NULL and name IS NOT NULL and resetlogs_change# = (select resetlogs_change# from v$database) and resetlogs_time = (select resetlogs_time from v$database) and thread# = :1 and sequence# = :2 and dest_id = :3 order by status  

If for any reason they have restored deleted redo logs in a way that no entries got created in the view, we can fake entries by creating a local view under the Oracle user Dedicated to Replicate. 

• Note that if Replicate is reading from a standby, the view will need to be created on the Primary. 
• Once the temporary issue was resolved, make sure to delete the local view in order to avoid future confusions. 

Some examples for local views: 

 Code with some examples:

create or replace view  SUDATR1.v$archived_log as select RECID,STAMP,  

  REGEXP_REPLACE(v.NAME, 

                 '\+RECO/o93xda1_gdc/archivelog/.+/(.+)', 

                 '/zfssa/attunity/donot_delete_attunity_ARTEST/\1') as NAME 

,   DEST_ID,THREAD#,SEQUENCE#,RESETLOGS_CHANGE#,RESETLOGS_TIME,RESETLOGS_ID,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME,BLOCKS,BLOCK_SIZE,CREATOR, 

   REGISTRAR,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,COMPLETION_TIME,DICTIONARY_BEGIN,DICTIONARY_END,END_OF_REDO,BACKUP_COUNT,ARCHIVAL_THREAD#, 

   ACTIVATION#,IS_RECOVERY_DEST_FILE,COMPRESSED,FAL,END_OF_REDO_TYPE 

from  sys.v_$archived_log v ; 

 

 

 

 

create or replace view attunity.v$archived_log as  

(select * from sys.v_$archived_log  

where dest_id=5)  

union ( select * from sys.v_$archived_log  

where dest_id=1 and sequence# not in (  

select sequence# from sys.v_$archived_log  

where dest_id=5 and resetlogs_change# =  

(select resetlogs_change# from v$database)));