rmantemplate.cfg

[template]
# This is always executed with RMAN scripts. Must include CONNECT TARGET clause
header: SET ECHO ON
  CONNECT TARGET /@${configname}
# This is added when database is registered in catalog, so connect to catalog here
headercatalog: CONNECT CATALOG ${catalogconnect}
# This is always executed with RMAN, the last line must be EXIT
footer: EXIT
# Backup commands, all these commands are run inside a run block
backupimagecopy: backup ${sectionsize} incremental level 1 for recover of copy with tag 'image_copy_backup' database;
  recover copy of database with tag 'image_copy_backup';
  delete noprompt backupset tag 'image_copy_backup';
  delete noprompt force archivelog until time 'sysdate-${recoverywindow}';
  backup spfile tag 'image_copy_backup';
  backup current controlfile format '${backupdest}/after_backup_controlfile.cf' reuse tag 'image_copy_backup';
# Backupfooter is always executed with backup commands
backupfooter: delete noprompt obsolete recovery window of ${recoverywindow} days;
  show all;
# Configuration commands
registerdatabase: REGISTER DATABASE;
resynccatalog: RESYNC CATALOG;
config: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF ${recoverywindow} DAYS;
  CONFIGURE BACKUP OPTIMIZATION ON;
  CONFIGURE DEFAULT DEVICE TYPE TO DISK;
  CONFIGURE CONTROLFILE AUTOBACKUP ON;
  CONFIGURE DEVICE TYPE DISK PARALLELISM ${parallel} BACKUP TYPE TO BACKUPSET;
  CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '${backupdest}/%%U';
  CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${backupdest}/%%F';
  CONFIGURE SNAPSHOT CONTROLFILE NAME TO '${backupdest}/snapcf_${configname}.f';
configdelalnodg: CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
configdelaldg: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
# Report command
report: REPORT NEED BACKUP RECOVERY WINDOW OF ${recoverywindow} DAYS;
  REPORT UNRECOVERABLE;
  RESTORE DATABASE PREVIEW;
# This is executed to validate backup
validatebackup: RESTORE DATABASE VALIDATE HEADER;
  RESTORE CONTROLFILE VALIDATE;
  RESTORE ARCHIVELOG ALL VALIDATE;
  RESTORE DATABASE VALIDATE;
# Restore script
headerrestore: export ORACLE_SID=${configname}1 # The last number is RAC instance number
  export TNS_ADMIN=${tnspath}
  rman target /@${configname} catalog ${catalogconnect}
  # RMAN commands follow
fullrestore: set dbid ${dbid};
  startup nomount;
  # Spfile
  restore spfile;
  startup force nomount;
  # Controlfile
  restore controlfile;
  alter database mount;
  sql "alter database disable block change tracking";
  # Restore datafiles to diskgroup +DATA
  backup as copy format '+DATA' tag 'datafilerestore' copy of database from tag 'image_copy_backup';
  switch database to copy;
  recover database;
  # Recover will end with an error (cannot find the latest online log), if online logs are not available, then just ignore it, this data will be lost
  alter database open resetlogs;
  shutdown immediate;
restorefooter:
  == The following are OS command to put register database in GI
  == TODO
allocatearchlogchannel: allocate channel d1 device type disk format '${archdir}/%%U';

# Scheduler
dropschedule: begin
  begin
    DBMS_SCHEDULER.DROP_JOB(
      job_name=>'${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_JOB,${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_JOB',
      force=>true,
      commit_semantics=>'ABSORB_ERRORS');
  exception
    when others then null;
  end;
  begin
    DBMS_SCHEDULER.DROP_SCHEDULE(
      schedule_name=>'${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_SCHEDULE,${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_SCHEDULE',
      force=>true);
  exception
    when others then null;
  end;
  begin
    DBMS_SCHEDULER.DROP_PROGRAM(
      program_name=>'${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_PRG,${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_PRG',
      force=>true);
  exception
    when others then null;
  end;
  begin
    DBMS_SCHEDULER.DROP_CREDENTIAL(
      credential_name=>'${scheduleuserprefix}BACKUPEXEC.OSCRED',
      force => true);
  exception
    when others then null;
  end;
  begin
    DBMS_SCHEDULER.DROP_JOB_CLASS (
      job_class_name=>'BACKUPEXEC_JOB',
      force=>true);
  exception
    when others then null;
  end;
  end;
  /

createschedule: CREATE OR REPLACE PROCEDURE ${scheduleuserprefix}backupexec.check_archlog_copy IS
    i  NUMBER;
  BEGIN
  -- Procedure automatically generated from backup.py
  -- Check if there are any archivelogs missing from the optional destination
  SELECT COUNT (*) INTO i
	 FROM (SELECT thread#, sequence#
				FROM v$$archived_log l
					  JOIN v$$archive_dest d ON l.dest_id = d.dest_id
					  JOIN v$$archive_dest_status s ON d.dest_id = s.dest_id
			  WHERE l.archived = 'YES' AND l.deleted = 'NO' AND l.status = 'A' AND UPPER (d.destination) = 'USE_DB_RECOVERY_FILE_DEST' AND d.status = 'VALID' AND s.TYPE = 'LOCAL'
			 MINUS
			 SELECT thread#, sequence#
				FROM v$$archived_log l
			  WHERE l.archived = 'YES' AND l.deleted = 'NO' AND l.status = 'A' AND l.name LIKE '${archdir}%%');
    IF i > 0 THEN
	 DBMS_SCHEDULER.create_job (job_name => '${scheduleuserprefix}BACKUPEXEC.'||DBMS_SCHEDULER.generate_job_name('MISSINGARCH'),
										 program_name => '${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_PRG',
										 job_class => 'BACKUPEXEC_JOB',
										 enabled => TRUE,
										 credential_name => '${scheduleuserprefix}BACKUPEXEC.OSCRED'
										);
    END IF;
  END;
  /
  begin
  DBMS_SCHEDULER.CREATE_JOB_CLASS(
    job_class_name=>'BACKUPEXEC_JOB',
    logging_level=>DBMS_SCHEDULER.LOGGING_FULL,
    log_history=>60,
    comments=>'This class is used to execute RMAN backup jobs by BACKUPEXEC user.');
  execute immediate 'grant execute on BACKUPEXEC_JOB to ${scheduleuserprefix}BACKUPEXEC';
  --
  DBMS_SCHEDULER.CREATE_CREDENTIAL(
    credential_name=>'${scheduleuserprefix}BACKUPEXEC.OSCRED',
    username=>'${osuser}',
    password=>'${ospassword}');
  --
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name=>'${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_PRG',
    program_type=>'EXECUTABLE',
    program_action=>'${scriptpath}/backup.py',
    number_of_arguments=>2,
    enabled=>false,
    comments=>'This program executes the image copy refresh.');
  DBMS_SCHEDULER.define_program_argument(
    program_name=>'${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_PRG',
    argument_position=>1,
    argument_name=>'configname',
    argument_type=>'VARCHAR2',
    default_value=>'${configname}');
  DBMS_SCHEDULER.define_program_argument(
    program_name=>'${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_PRG',
    argument_position=>2,
    argument_name=>'action',
    argument_type=>'VARCHAR2',
    default_value=>'imagecopywithsnap');
  DBMS_SCHEDULER.ENABLE('${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_PRG');
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name=>'${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_PRG',
    program_type=>'EXECUTABLE',
    program_action=>'${scriptpath}/backup.py',
    number_of_arguments=>2,
    enabled=>false,
    comments=>'This program executes archivelog backup.');
  DBMS_SCHEDULER.define_program_argument(
    program_name=>'${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_PRG',
    argument_position=>1,
    argument_name=>'configname',
    argument_type=>'VARCHAR2',
    default_value=>'${configname}');
  DBMS_SCHEDULER.define_program_argument(
    program_name=>'${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_PRG',
    argument_position=>2,
    argument_name=>'action',
    argument_type=>'VARCHAR2',
    default_value=>'missingarchlog');
  DBMS_SCHEDULER.ENABLE('${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_PRG');
  --
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name=>'${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_SCHEDULE',
    repeat_interval=>'${schedulebackup}',
    comments=>'Schedule for refreshing image copy.');
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name=>'${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_SCHEDULE',
    repeat_interval=>'${schedulearchlog}',
    comments=>'Schedule for archivelog backups.');
  --
  DBMS_SCHEDULER.CREATE_JOB(
    job_name=>'${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_JOB',
    program_name=>'${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_PRG',
    schedule_name=>'${scheduleuserprefix}BACKUPEXEC.IMAGECOPY_SCHEDULE',
    job_class=>'BACKUPEXEC_JOB',
    enabled=>${backupjobenabled},
    comments=>'This job executes image copy refresh.',
    credential_name=>'${scheduleuserprefix}BACKUPEXEC.OSCRED');
  DBMS_SCHEDULER.CREATE_JOB(
    job_name=>'${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_JOB',
    job_type=>'STORED_PROCEDURE',
    job_action=>'${scheduleuserprefix}backupexec.check_archlog_copy',
    schedule_name=>'${scheduleuserprefix}BACKUPEXEC.ARCHLOGBACKUP_SCHEDULE',
    job_class=>'BACKUPEXEC_JOB',
    enabled=>true,
    comments=>'This job checks if there are any archivelogs that are missing in the backup set.');
  end;
  /

cdbdetect: declare
  p_cnt NUMBER;
  p_value varchar2(20);
 begin
  select 1 into p_cnt from dual where sys_context('USERENV','CDB_NAME') is not null and SYS_CONTEXT('USERENV','CON_NAME') = 'CDB$$ROOT';
  select nvl(max(value), 'C##') into p_value from v$$parameter where name = 'common_user_prefix';
  dbms_output.put_line('CDB-DETECT: '||p_value);
 exception
  when others then
    dbms_output.put_line('CDB-DETECT: NO');
 end;
 /

createuser: declare
  i number;
  begin
    select count(*) into i from dba_users where username = '${scheduleuserprefix}BACKUPEXEC';
    if i = 0 then
      execute immediate 'create user ${scheduleuserprefix}backupexec identified by backupexec account lock';
      execute immediate 'grant create job, create external job, create procedure, select any dictionary to ${scheduleuserprefix}backupexec';
    end if;
  end;
  /

sqlplusheader: whenever sqlerror exit failure
  conn ${sqlplusconnection}
  set feedback on
  set timing on
  set echo on
  set lines 300
  set serverout on
  col host_name format a24
  col version format a10
  spool ${logfile} append
  select instance_name, host_name, version, status, database_status from v$$instance;
  set pages 0
  select user from dual;

sqlplusfooter: spool off
  exit;

# Archivelog commands
archivecurrentlogs: select 'CURRENT DATABASE TIME: '||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual union all
  select 'CURRENT DATABASE SCN: '||to_char(current_scn) from v$$database union all
  select 'BCT FILE: '||filename from v$$block_change_tracking;
  exec dbms_lock.sleep(1);
  alter system archive log current;
  -- Sleep below is to allow Data Guard standby time to archive the log also
  exec dbms_lock.sleep(10);
archivelogmissing: select q'[BACKUP force as copy archivelog sequence ]'||sequence#||' thread '||thread#||';' from (
    select thread#,sequence# from v$$archived_log l join v$$archive_dest d on l.dest_id=d.dest_id join v$$archive_dest_status s on d.dest_id=s.dest_id where l.archived='YES' and l.deleted='NO' and l.status = 'A' AND upper(d.destination)='USE_DB_RECOVERY_FILE_DEST' and d.status='VALID' and s.type='LOCAL'
    minus
    select thread#,sequence# from v$$archived_log l where l.archived='YES' and l.deleted='NO' and l.status = 'A' AND l.name like '${archdir}%%'
    order by 1,2
  );

# Delete datafilecopy
deletedatafilecopy: select 'DELETECOPY: delete noprompt datafilecopy '''||name||''';' cmd
    from v$$datafile_copy where deleted='NO' and tag='IMAGE_COPY_BACKUP' and (file#,CREATION_CHANGE#) not in (select file#,CREATION_CHANGE# from v$$datafile);

# SQL*Plus part of the configuration commands
configfromsqlplus: -- Block change tracking
  declare
    i number;
    j number;
  begin
    select count(*) into i from V$$BLOCK_CHANGE_TRACKING where status='DISABLED';
    select count(*) into j from v$$version where banner like '%%Enterprise Edition%%';
    if (i = 1) and (j > 0) then
      execute immediate 'alter database enable block change tracking';
    else
      dbms_output.put_line('BCT already enabled or database is not EE');
    end if;
  end;
  /
  -- Archivelog destination
  declare
    cnt number;
    s varchar2(60);
    l varchar2(100):= '${archdir}/';
    function min_param return varchar2 is
      v varchar2(50);
    begin
      select min(name) into v from v$$parameter where name like 'log\_archive\_dest\__'  escape '\' and value is null;
      return v;
    end;
  begin
    select count(*) into cnt from v$$parameter where name like 'log\_archive\_dest\__'  escape '\' and upper(value) like 'LOCATION%%=%%USE_DB_RECOVERY_FILE_DEST%%';
    if cnt=0 then
      s:= min_param;
      dbms_output.put_line('Setting '||s||' to USE_DB_RECOVERY_FILE_DEST');
      execute immediate 'alter system set '||s||q'[='LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY' scope=both]';
      execute immediate 'alter system set '||replace(s, 'log_archive_dest_', 'log_archive_dest_state_')||'=enable scope=both';
    end if;
    select count(*) into cnt from v$$parameter where name like 'log\_archive\_dest\__'  escape '\' and value like '%%'||l||'%%';
    if cnt=0 then
      s:= min_param;
      dbms_output.put_line('Setting '||s||' to '||l);
      l:= q'['LOCATION=]'||l||q'[ VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)']';
      execute immediate 'alter system set '||s||'='||l||' scope=both';
      execute immediate 'alter system set '||replace(s, 'log_archive_dest_', 'log_archive_dest_state_')||'=enable scope=both';
    end if;
  end;
  /

isdbregisteredincatalog: conn ${catalogconnect}
  set lines 100
  set pages 0
  spool ${logfile} append
  set echo on
  select user, sys_context('USERENV','DB_UNIQUE_NAME'), sys_context('USERENV','SERVER_HOST'), sys_context('USERENV','INSTANCE_NAME') from dual;
  select 'DATABASE IS REGISTERED IN RC' from rc_database where dbid = ${dbid};
  spool off
  exit

autorestoreparameters: whenever sqlerror exit failure
  set pages 0
  set lines 200
  set feedback off
  select 'dbconfig-'||name||': '||value from v$$parameter where name in ('db_name','undo_tablespace','compatible','db_block_size','db_files','enable_pluggable_database')
  union all
  select 'dbconfig-backup-finished: '||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
  exit