restoretemplate.cfg

[template]
autoinitora: *.compatible='${db_compatible}'
  *.control_files='${autorestoredestination}/restore.cf'
  *.db_block_size=${db_block_size}
  *.db_create_file_dest='${mountdestination}'
  *.db_create_online_log_dest_1='${mountdestination}'
  *.db_files=${db_files}
  *.db_name='${db_name}'
  *.filesystemio_options='SETALL'
  *.pga_aggregate_target=${pga_size}
  *.processes=300
  *.remote_login_passwordfile='EXCLUSIVE'
  *.sga_max_size=${sga_size}
  *.sga_target=${sga_size}
  *.undo_management='AUTO'
  *.undo_tablespace='${db_undotbs}'
  *.job_queue_processes=0
  *.diagnostic_dest='${logdir}'
  *.instance_number=${instancenumber}
  *.thread=${thread}
  *.streams_pool_size=200M
  *.db_recovery_file_dest_size=500G
  *.db_recovery_file_dest='${mountdestination}'
  *._disk_sector_size_override=TRUE
cdbinitora:
  *.enable_pluggable_database=${cdb}
sqlplusheader: set pages 0
  set lines 200
  set timing on
  set echo on
  spool ${logfile} append
  conn / as sysdba
sqlplusfooter: spool off
  exit
startupnomount: whenever sqlerror exit failure
  startup nomount pfile='${initora}'
shutdown: shutdown immediate
openandverify: whenever sqlerror exit failure
  prompt alter database open read only;
  alter database open read only;
  -- Check times
  prompt Set time formatting
  alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
  alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';
  prompt Query database timestamps
  select 'CURRENT DATABASE SCN: '||to_char(current_scn) from v$$database UNION ALL
  select 'LAST ROW IN SCN TO TIME MAP: '||max(time_dp) from sys.smon_scn_time UNION ALL
  select 'MIN/MAX TIME FROM ARCHLOGS: '||min(first_time)||'-'||min(next_time) from v$$archived_log where (select current_scn from v$$database) between first_change# and next_change# UNION ALL
  select 'CUSTOM VERIFICATION TIME: '||(${customverifydate}) from dual;
  -- Check data files
  col file_name format a100
  col status format a15
  col online_status format a20
  prompt Datafiles where online_status not in ('SYSTEM','ONLINE') or status <> 'AVAILABLE'
  select file_name, status, online_status from dba_data_files where online_status not in ('SYSTEM','ONLINE') or status <> 'AVAILABLE';
  /*
  declare
    i number;
  begin
    select count(*) into i from dba_data_files where online_status not in ('SYSTEM','ONLINE') or status <> 'AVAILABLE';
    if i > 0 then
      raise_application_error(-20000, 'Some data files have invalid status.');
    end if;
  end;
  / */
validateblocks: backup validate check logical database;
showcorruptblocks: col corruption_type format a15
  set pages 100
  set lines 80
  select * from v$$database_block_corruption;

rmanheader: set echo on
  connect target /
rmanfooter: exit
rmanmount: run {
  allocate channel c1 device type disk;
  restore controlfile from '${mountdestination}/after_backup_controlfile.cf';
  }
  alter database mount;
  CONFIGURE SNAPSHOT CONTROLFILE NAME TO '${autorestoredestination}/snapcf.f';
clearlogs: begin
    for rec in (select group# from v$$log) loop
      -- Commenting it out since the created logfiles seem to confuse the restore
      -- execute immediate 'alter database clear logfile group '||rec.group#;
      null;
    end loop;
  end;
  /
rmancatalog: change archivelog all uncatalog;
  change backup device type disk unavailable;
  change copy of database uncatalog;
  ${catalogstatements}
  sql "alter database flashback off";
switchdatafiles: set serverout on
  -- Switch datafiles
  -- Need to do it through RMAN, because alter database rename file will DELETE source OMF file!!!!!!!!!!!
  declare
    cnt NUMBER;
    v_datafiles_needing_restore VARCHAR2(4000);
  begin
    -- Do we have some datafailes that do not have a copy and have to be created
    select count(*), listagg(to_char(file#), ',') within group (order by file#) INTO cnt, v_datafiles_needing_restore
    from (select file# from v$$datafile minus select file# from v$$datafile_copy where tag='IMAGE_COPY_BACKUP' and deleted='NO');
    -- Create nonexisting datafiles
    IF cnt > 0 THEN
      dbms_output.put_line('RENAMEDF-run {');
      dbms_output.put_line('RENAMEDF-set newname for database to new;');
      dbms_output.put_line('RENAMEDF-restore datafile '||v_datafiles_needing_restore||';');
      dbms_output.put_line('RENAMEDF-}');
    END IF;
    -- Switch all datafile
    dbms_output.put_line('RENAMEDF-switch database to copy;');
  end;
  /
recoverdatafiles: run {
  allocate channel c1 device type disk;
  set newname for database to new;
  set until time "to_date('${lasttime}', 'yyyy-mm-dd hh24:mi:ss')";
  recover database;
  }
disablebct: whenever sqlerror exit failure
  alter database rename file '${bctfile}' to '${autorestoredestination}/bct.bct';
  alter database disable block change tracking;
  select * from V$$BLOCK_CHANGE_TRACKING;
shutdownabort: shutdown abort
createcatalog: set pages 0
  set lines 200
  set timing on
  set echo on
  whenever sqlerror exit failure
  spool ${logfile} append
  conn ${autorestorecatalog}
  create sequence restoreaction_seq;
  create table restoreaction (
    id number not null primary key,
    db_unique_name varchar2(100) not null,
    start_time date not null,
    finish_time date not null,
    success number(1) check (success in (0,1)),
    logfile varchar2(500),
    logfilecontents clob,
    verificationtimediffseconds number,
    snapid varchar2(200),
    restore_target date,
    validated_corruption number(1) check (validated_corruption in (0,1)),
    unique (db_unique_name, start_time, success)
  ) pctfree 0;
  create table restorelob (logfile varchar2(200) primary key, loglob clob) organization index;
  exec dbms_scheduler.create_job('LOG_RETENTION','PLSQL_BLOCK', 'DELETE FROM restoreaction WHERE start_time < SYSDATE-100;',repeat_interval=>'FREQ=daily', enabled=>true);
  exit
sqlldrlog: LOAD DATA
  INFILE *
  APPEND
  INTO TABLE restorelob
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  (logfile CHAR(200), loglob LOBFILE(logfile) TERMINATED BY EOF)
  BEGINDATA
  "${logfile}"
insertlog: whenever sqlerror exit failure
  set echo on
  spool ${logfile} append
  conn ${autorestorecatalog}
  set serverout on
  declare
    v_dbname restoreaction.db_unique_name%%type:= '${log_dbname}';
    v_starttime restoreaction.start_time%%type:= to_date('${log_start}', 'yyyy-mm-dd hh24:mi:ss');
    v_stoptime restoreaction.finish_time%%type:= to_date('${log_stop}', 'yyyy-mm-dd hh24:mi:ss');
    v_success restoreaction.success%%type:= ${log_success};
    v_logfile restoreaction.logfile%%type:= '${logfile}';
    v_diff restoreaction.verificationtimediffseconds%%type:= ${log_diff};
    v_snap restoreaction.snapid%%type:= '${log_snapid}';
    v_target restoreaction.restore_target%%type:= to_date('${lasttime}', 'yyyy-mm-dd hh24:mi:ss');
    v_validated restoreaction.validated_corruption%%type:= ${log_validated};
    p_log_id NUMBER;
  begin
    insert into restoreaction (id, db_unique_name, start_time, finish_time, success, logfile, verificationtimediffseconds, snapid, restore_target, validated_corruption)
      values (restoreaction_seq.nextval, v_dbname, v_starttime, v_stoptime, v_success, v_logfile, v_diff, v_snap, v_target, v_validated);
    p_log_id:= restoreaction_seq.currval;
    for rec in (select loglob from restorelob where logfile = v_logfile) loop
      update restoreaction set logfilecontents = rec.loglob where id = p_log_id;
    end loop;
    delete from restorelob where logfile = v_logfile;
    commit;
    dbms_output.put_line('LOG MESSAGE ID: '||to_char(p_log_id));
  end;
  /
  exit