Differences

This shows you the differences between two versions of the page.

--- rman [2019/02/04 09:56] – [RMAN SQL] z0hpvk
+++ rman [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ==== RMAN Parameters ====
-<code>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
+<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';
@@ Line 10: / Line 12: @@
 <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';</code>
+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 ====
@@ Line 26: / Line 99: @@
   </code>
-==== Fast Recovery Area ====
+<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 linesize 120
+set lines 300
-col name format a20
+set pages 1000
-select name, space_limit/1024/1024 SPACE_LIMIT_MB,
+col cf for 99
-             space_used/1024/1024 SPACE_USED_MB,
+col df for 99
-             space_reclaimable/1024/1024 SPACE_RECLAIMABLE_MB,
+col elapsed_seconds heading "ELAPSED|SECONDS"
-             NUMBER_OF_FILES from V$RECOVERY_FILE_DEST;
+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"
-select * from V$RECOVERY_AREA_USAGE;
+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>