Create v$archived_log Local view on Oracle

David_Fergen — Fri, 05 Feb 2021 21:57:45 GMT

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))); 

article Create v$archived_log Local view on Oracle in Official Support Articles

Create v$archived_log Local view on Oracle