DB Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
==== RMAN Parameters ====

<code>export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI"

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/oracle/backup/%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u03/oracle/backup/%U';</code>

==== RMAN Commands ====

<code>BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/home/oracle/%U' PLUS ARCHIVELOG;
BACKUP ARCHIVELOG FROM SEQUENCE 7500;
BACKUP ARCHIVELOG FROM SEQUENCE 7500 UNTIL SEQUENCE 8000 FORMAT '/home/oracle/%U';
BACKUP ARCHIVELOG FROM TIME 'SYSDATE -1';

DELETE ARCHIVELOG ALL;
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'sysdate -7';

LIST BACKUP OF ARCHIVELOG SEQUENCE xxxxx;
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE xxxxx;
LIST BACKUP OF ARCHIVELOG SEQUENCE BETWEEN xxxxx AND xxxxx;

CATALOG START WITH '<FILE_LOCATION>';

-- Data files showing in wrong location in control file
CATALOG START WITH '<DATAFILE_LOCATION>';
SWITCH DATABASE TO COPY;
RECOVER DATABASE;
</code>
==== Fast Recovery Area ====

<code SQL>
set linesize 120
col name format a20
select name, space_limit/1024/1024 SPACE_LIMIT_MB, 
             space_used/1024/1024 SPACE_USED_MB,
             space_reclaimable/1024/1024 SPACE_RECLAIMABLE_MB,
             NUMBER_OF_FILES from V$RECOVERY_FILE_DEST;

select * from V$RECOVERY_AREA_USAGE;
</code>

==== Incremental Recovery ====
This procedure is useful if a standby database has missing archive logs. \\
<code>
COL CURRENT_SCN FORMAT 999999999999999
SELECT CURRENT_SCN FROM V$DATABASE;
SELECT MIN(FHSCN) FROM X$KCVFH;
</code>
Example lowest number is 123456
<code>
RMAN> BACKUP INCREMENTAL FROM SCN 123456 DATABASE FORMAT 'F:\ad-hoc_backup\CPHG\ForStandby_%U' tag 'INCR_STANDBY';
</code>
Copy backup file to the standby database server. \\
<code>
RMAN> CATALOG START WITH 'F:\adhoc_backup\CPHG';
RMAN> RECOVER DATABASE NOREDO;
</code>
Backup the control file on the primary server. \\
<code>
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT 'F:\ad-hoc_backup\CPHG\standby.ctl';
</code>
Copy control file backup to the standby server. \\
If database locations are different on the standby server record the names ...

<code>
RMAN> REPORT SCHEMA;

RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT; 
RMAN> RESTORE STANDBY CONTROLFILE FROM 'F:\adhoc_backup\CPHG\standby.ctl';

SQL> SHUTDOWN; 
SQL> STARTUP MOUNT;

RMAN> CATALOG START WITH '<DATAFILE_LOCATION>';
RMAN> SWITCH DATABASE TO COPY;
</code>


If this is a Data Guard configuration then clear the standby redo log groups. \\
<code>
SQL> ALTER DATABASE CLEAR LOGFILE GROUP <STBY_GROUP_NO>;
</code>

==== RMAN SQL ====
<code SQL>
-- Active RMAN Job
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM  V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK
  </code>
  
<code SQL>
-- Last RMAN Operation
col object_type format a15
alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';

select object_type,mbytes_processed, start_time, end_time,status
from v$rman_status
where session_recid = (select max(session_recid) 
                         from v$rman_status)
and operation !='RMAN'
order by recid;
</code>
  
<code SQL>
set lines 300
set pages 1000
col cf for 99
col df for 99
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 99
col i1 for 99
col l for 99
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 9999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"

col start_time for a25
col end_time   for a25
col START_DOW  for a10


col L for 999

           col INPUT_BYTES_DISPLAY for a9 heading "INPUT|SIZE"
           col OUTPUT_BYTES_DISPLAY for a9 heading "OUTPUT|SIZE"
           col INPUT_BYTES_PER_SEC_DISPLAY for a9 heading "INPUT|PERSEC"
           col OUTPUT_BYTES_PER_SEC_DISPLAY for a9 heading "OUTPUT|PERSEC"

select
   j.session_recid, j.session_stamp,
   to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
   to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
   j.status, j.input_type,
   to_char(j.start_time, 'DAY') START_DOW,
 j.time_taken_display,
                                     INPUT_BYTES_DISPLAY,
                                     OUTPUT_BYTES_DISPLAY,
                                     INPUT_BYTES_PER_SEC_DISPLAY,
                                     OUTPUT_BYTES_PER_SEC_DISPLAY,
   x.cf, x.df, x.i0, x.i1, x.l
 from V$RMAN_BACKUP_JOB_DETAILS j
   left outer join (select
                      d.session_recid, d.session_stamp,
                      sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                      sum(case when d.controlfile_included = 'NO'
                                and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                      sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                      sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                    from
                      V$BACKUP_SET_DETAILS d
                      join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                    where s.input_file_scan_only = 'NO'
                    group by d.session_recid, d.session_stamp) x
     on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
   left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                    from GV$RMAN_OUTPUT o
                    group by o.session_recid, o.session_stamp)
     ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
 where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
 order by j.start_time;
</code>