1] Is an RMAN Catalog really needed ?
I got a
phone call from a former colleague about the why should we have an RMAN
Catalog ?
Since
Controlfile and RMAN Catalog Information are Synchronized,
what are the
advantages of having a Catalog vs. Target Controlfile ?
-
Backup Information are kept longer
than in Controlfile - however, backup information retention in Controlfile may
be tuned ( control_file_record_keep_time ).
-
Easy reporting of backup status for all
the Databases with a single SQL
-
RMAN scripts may be stored in
Catalog ( I’ve never used that feature )
-
When backuping a Physical Standby DB
or Mirrored DB ( FlashCopy - Snapshot ) – only RCatalog will keep the Backup
information that Primary DB Controlfile may get when synchronizing with
it ; Standby or Mirrored DB Controlfile can’t record those backups
information
-
TSPITR Limitations , see below.
Reasons NOT
to have a Catalog are :
-
It is possible to restore from
« scratch » a database WITHOUT an RMAN Catalog ; since 9i, the
« Controlfile Autobackup » feature alllows Spfile & Controlfile
restore without any data about the backups – RMAN retrieves those backups with
a standard named format ( %F )
-
The Catalog may be considered as a
SPOF because if it’s not there, backup will fail
-
The Catalog DB should always be at
the highest Oracle version ( minor number included = the fourth numbers ) of
all Backuped DB and therefore should be upgraded first.
Conclusion :
Disavantages
of having an RCAT seem to me more important than PROs, thus
I would
recommend NOT TO HAVE an RMAN Catalog if there are no backups on Standby or
Mirrored DB.
Update : for very big IT, with a lot of DBs under RMAN, I’ll
provide the Reports scripts on Catalog.
** UPDATE 2 **
I found a
bug when not using a Catalog : my Incremental ( Cumulative ) Backups were
never removed using the command :
delete force noprompt obsolete recovery window of 7
days ;
Therefore
I had to install RMAN Catalog.
Read
also :
Limitations
of TSPITR Without a Recovery Catalog
====================================
====================================
The
undo segments at the time of the TSPITR must be part of the auxiliary set.
Because RMAN has no historical record of the undo in the control file, RMAN
assumes that the current rollback or undo segments were the same segments
present at the time to which recovery is performed. If the undo segments
have changed since that time, then TSPITR will fail.
TSPITR
to a time that is too old may not succeed if Oracle has reused the control file
records for needed backups. (In planning your database, set the
CONTROL_FILE_RECORD_KEEP_TIME initialization parameter to a value large enough
to ensure that control file records needed for TSPITR are kept.)
When
not using a recovery catalog, the current control file has no record of the
older incarnation of the recovered tablespace. Thus, recovery with a current
control file that involves this tablespace can no longer use a backup taken
prior to time t. You can, however, perform incomplete recovery of the whole
database to any time less than or equal to t, if you can restore a backup
control file from before time t.
2] Backup Reports from Controlfile
2.1 Report Status
Since I
choose not to have a Catalog, I’ll query directly the Controlfile to find out
what happened. There is a new 10g view v$rman_backup_job_details , very
useful but with many bugs :
even in
10.2.0.5, columns input_bytes_display & input_bytes_display are equal , controlfile_autobackup is always
‘NO’ , Backup type DB FULL doesn’t appear.
Hence, I’m
looking for the right information in another view v$backup_set_details which
provides real Backup Type between DB FULL , DB INCR , ARCHIVELOG or CONTROLFILE
( that one I don’t keep because my backups always uses « controlfile
autobackup » feature ).
$ cat rman_backup_report.sql
set echo off;
set serveroutput on size 1000000
set flush on;
set lines 150 pages 5000
alter session set nls_date_format='DD/MM/YYYY
HH24:MI:SS';
variable a number
begin
:a:=&1;
end;
/
with rman_rep as (
select Start_At,End_At,DB_ARCs_Sz_GB,Backup_Sz_GB,Media,max(Dec_Type) Type,status,Elapsed,output_MB_per_sec,compression
from (
select distinct to_char(a.start_time,'DD/MM/YYYY
HH24:MI:SS') Start_At, to_char(a.end_time,'DD/MM/YYYY
HH24:MI:SS') End_At,
ceil(a.input_bytes/1024/1024/1024) DB_ARCs_Sz_GB, ceil(a.output_bytes/1024/1024/1024)
Backup_Sz_GB,
ceil(a.compression_ratio) compression, ceil(a.output_bytes_per_sec/1024/1024) output_MB_per_sec, a.output_device_type
Media , -- a.input_type ,
decode(b.backup_type,'I','DB INCR',decode(b.backup_type,'L','Archlog',decode(b.backup_type,'D',decode(controlfile_included,'NO','DB FULL','CTL')))) Dec_Type,
a.status , a.time_taken_display
Elapsed -- ,
b.controlfile_included, b.compressed
from
v$rman_backup_job_details a , v$backup_set_details
b
where a.session_key = b.session_key and a.session_recid = b.session_recid
and a.start_time > sysdate-:a
) where Dec_Type != 'CTL'
group by Start_At,End_At,DB_ARCs_Sz_GB,Backup_Sz_GB,compression,output_MB_per_sec,Media,status,Elapsed
)
select
instance_name Instance,Start_At,End_At,DB_ARCs_Sz_GB,Backup_Sz_GB,Media,Type,r.status,Elapsed,output_MB_per_sec,compression from rman_rep
r , v$instance
union
select
instance_name Instance,'No Backup
Found',null,null,null,null,null,null,null,null,null from dual ,
v$instance where not exists ( select '1' from rman_rep )
order by Start_At desc ;
exit
Call it with
a loop on all the databases and HTML output with sqlplus, then we have this
great report :
EMAIL_HEAD="To:
${dest_list} \nFrom: \"DBA MONITORING \"\nSubject: ${msg_sub}
\nMIME-Version: 1.0\nContent-Type: Multipart/Mixed;
boundary=Message-Boundary-$boundary\n\n--Message-Boundary-$boundary\nContent-Type:
text/html; charset=us-ascii\nContent-Transfer-Encoding: 7bit \n\n";
echo
$EMAIL_HEAD>${bup_gen}
#
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
# 1.
RMAN backup report
#
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
for
BDc in ${db_1} ; do
sqlplus
-s -MARKUP "HTML ON" system/${system_psw}@${BDc}
@${REP_SCRIPTS}/rman_backup_report.sql ${wind} | sed -e
"/<br>/d" -e 's/td {font:10pt/td
{text-align:center;font:10pt/g' -e 's/font:10pt/font:9pt/g' | egrep -iv
"USER|Session|^old|^new|procedure|rows|selected" >> ${bup_gen}
if ((
$? != 0 )); then return 1 ; fi
done
#
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
# 2.
Mail
#
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
sendmail
-t ${dest_list} < ${bup_gen}
Execution
$ ./report_rman_bup_all.ksh 1
** [Oracle] RMAN Backup Report past 1 day(s) 19/10/11
**
** INST1 **
** INST3 **
** INFDE **
…
** PPRQU **
** DEVUN **
** DEVQU **
** DEVBI **
** end of script
**
Mailed
Output ( still received in Notes ! )
[Oracle] 07/05/12 RMAN Backup Report
past 1 day(s)
|
INSTANCE
|
START_AT
|
END_AT
|
DB_ARCS_SZ_GB
|
BACKUP_SZ_GB
|
MEDIA
|
TYPE
|
STATUS
|
ELAPSED
|
OUTPUT_MB_PER_SEC
|
COMPRESSION
|
|
INST1
|
06/05/2012 08:00:17
|
06/05/2012 09:09:48
|
391
|
48
|
DISK
|
DB FULL
|
COMPLETED
|
01:09:31
|
12
|
9
|
|
INST2
|
06/05/2012 09:10:01
|
06/05/2012 09:15:37
|
34
|
6
|
DISK
|
DB FULL
|
COMPLETED
|
00:05:36
|
16
|
7
|
[Oracle] 16/05/12 RMAN Backup Report
past 1 day(s)
|
INSTANCE
|
START_AT
|
END_AT
|
DB_ARCS_SZ_GB
|
BACKUP_SZ_GB
|
MEDIA
|
TYPE
|
STATUS
|
ELAPSED
|
OUTPUT_MB_PER_SEC
|
COMPRESSION
|
|
INST1
|
15/05/2012 22:00:28
|
15/05/2012 22:33:06
|
432
|
4
|
DISK
|
DB INCR
|
COMPLETED
|
00:32:38
|
3
|
111
|
|
INST2
|
15/05/2012 22:33:16
|
15/05/2012 22:35:23
|
34
|
1
|
DISK
|
DB INCR
|
COMPLETED
|
00:02:07
|
4
|
84
|
2.2 DB Backup Status for Monitoring Purpose
This Sql may
be plugged into Nagios tool to warn for Failed Backup. Run at DB level.
Did I have a
valid backup in the previous 24 Hours sysdate-1 ?
-- last 24 hours failed
backup status
select decode(count(Bstatus),0,'FAILED','OK')
NagiosStatus from (
with rman_rep
as (
select Start_At,End_At,DB_ARCs_Sz_GB,Backup_Sz_GB,Media,max(Dec_Type) Type,status,Elapsed,output_MB_per_sec,compression
from (
select distinct to_char(a.start_time,'DD/MM/YYYY
HH24:MI:SS') Start_At, to_char(a.end_time,'DD/MM/YYYY
HH24:MI:SS') End_At,
ceil(a.input_bytes/1024/1024/1024) DB_ARCs_Sz_GB, ceil(a.output_bytes/1024/1024/1024)
Backup_Sz_GB,
ceil(a.compression_ratio) compression, ceil(a.output_bytes_per_sec/1024/1024) output_MB_per_sec, a.output_device_type
Media , -- a.input_type ,
decode(b.backup_type,'I','DB INCR',decode(b.backup_type,'L','Archlog',decode(b.backup_type,'D',decode(controlfile_included,'NO','DB FULL','CTL')))) Dec_Type,
a.status , a.time_taken_display
Elapsed -- , b.controlfile_included,
b.compressed
from
v$rman_backup_job_details a ,
v$backup_set_details b
where a.session_key = b.session_key and a.session_recid = b.session_recid
and a.start_time > sysdate-1
) where Dec_Type like 'DB%'
group by Start_At,End_At,DB_ARCs_Sz_GB,Backup_Sz_GB,compression,output_MB_per_sec,Media,status,Elapsed
)
select r.status Bstatus from rman_rep
r , v$instance
union
select 'No Backup
Found' Bstatus from dual , v$instance where not exists ( select '1' from rman_rep )
) where Bstatus = 'COMPLETED'
;
Run it in a
loop / sql
$ for BDc in DB1 DB2 … ; do
echo "** ${BDc} **"
sqlplus -s ina_svc/<psw>@${BDc} << !
set head off
@rman_backup_nagios.sql
!
done
** DB1 **
OK
** DB2 **
OK
3] Backup Reports from Catalog
3.1 Query to get Backup status in the Past
Reminder :
DB FULL or INCRemental Backup Report includes also the Archivelogs taken during
the Backup.
Type
Description : DB FULL : RMAN FULL Backup previous strategy
Status is
COMPLETED , FAILED or RUNNING.
Connected to
the RMAN Catalog : looking back for Backups in the past Day a.start_time >
sysdate-1
with rman_rep as
(
select DB_NAME,DB_KEY,Start_At,End_At,DB_ARCs_Read_Sz_GB,Backup_Sz_GB,compression,Type,output_MB_per_sec,status,Elapsed
from (
select a.db_name,a.db_key,to_char(a.start_time,'DD/MM/YYYY HH24:MI:SS') Start_At, to_char(end_time,'DD/MM/YYYY HH24:MI:SS') End_At,
ceil(input_bytes/1024/1024/1024)
DB_ARCs_Read_Sz_GB, ceil(a.output_bytes/1024/1024/1024) Backup_Sz_GB,
ceil(a.compression_ratio) compression, ceil(output_bytes_per_sec/1024/1024) output_MB_per_sec,
decode(a.input_type,'DB INCR',decode(b.incremental_level,'0','DB
FULL_0','DB INCR_'||b.incremental_level),a.input_type) Type,
a.status , a.time_taken_display Elapsed
from
rc_rman_backup_job_details a , rc_backup_set_details b
where a.session_key = b.session_key and a.session_recid = b.session_recid and a.db_key = b.db_key
and a.start_time > sysdate-:a )
)
, rman_rep_run as
(
select DB_NAME,DB_KEY,Start_At,End_At,DB_ARCs_Read_Sz_GB,Backup_Sz_GB,compression,Type,output_MB_per_sec,status,Elapsed
from (
select a.db_name,a.db_key,to_char(a.start_time,'DD/MM/YYYY HH24:MI:SS') Start_At, to_char(end_time,'DD/MM/YYYY HH24:MI:SS') End_At,
ceil(input_bytes/1024/1024/1024)
DB_ARCs_Read_Sz_GB, ceil(a.output_bytes/1024/1024/1024) Backup_Sz_GB,
ceil(a.compression_ratio) compression, ceil(output_bytes_per_sec/1024/1024) output_MB_per_sec,
a.input_type Type, a.status , a.time_taken_display
Elapsed
from
rc_rman_backup_job_details a
where a.start_time > sysdate-:a and a.status = 'RUNNING' )
)
select name,Start_At,End_At,DB_ARCs_Read_Sz_GB,Backup_Sz_GB,'
'||Type Type,status,Elapsed,output_MB_per_sec,compression from rman_rep a , rc_database b
where a.db_key = b.db_key and Type not like '%\_' escape '\'
union -- when running, incremental_level is null
select name,Start_At,null,null,null,null,status,null,null,null from rman_rep_run a , rc_database b
where a.db_key = b.db_key
union
select name,'No Backup Found',null,null,null,null,null,null,null,null from dual , rc_database a
where not exists ( select '1' from rman_rep b where a.db_key = b.db_key )
order by name, Start_At desc ;
Run SQLPLUS
Connected to the RMAN Catalog :
argument:
Number of Days to look back for Backups
Query Catalog One Day in the past
$ sqlplus -s rman/rman@RCAT
@rman_backup_report_cat_n_days.sql 1
NAME START_AT END_AT
DB_ARCS_READ_SZ_GBBACKUP_SZ_GBTYPE STATUS ELAPSED
OUTPUT_MB_PER_SECCOMPRESSION
DB1 25/01/2013 09:21:13 RUNNING
DB2 22/01/2013 23:00:55 22/01/2013
23:43:13 29 25 DB INCR_1 COMPLETED 00:42:18 10 2
DB2 22/01/2013 18:02:53 22/01/2013
21:58:55 250 148 DB FULL COMPLETED 03:56:02 11 2
DB3 No Backup Found
DB4 23/01/2013 16:21:45 23/01/2013 16:23:26 1 1 ARCHIVELOG COMPLETED 00:01:41 1 1
DB4 23/01/2013 00:01:07 23/01/2013 01:48:22 141 3 DB FULL COMPLETED 01:47:15 1 48
DB4 22/01/2013 21:00:38 22/01/2013 21:15:35 1 1 DB INCR_1 COMPLETED 00:14:57 1 2
$
Query Catalog 5 Days in the past
$ sqlplus -s rman/rman@rmaopeo02
@rman_backup_report_cat_n_days.sql 5
NAME START_AT END_AT
DB_ARCS_READ_SZ_GBBACKUP_SZ_GBTYPE STATUS ELAPSED
OUTPUT_MB_PER_SECCOMPRESSION
-----------------------------------------------------------------------------------------------------------------------------------------------
DB_1 21/01/2013 23:01:05 21/01/2013
23:54:00 36 35 DB INCR_1 COMPLETED 00:52:55 12 2
DB_1 21/01/2013 18:03:55 21/01/2013
20:40:34 200 97 DB FULL COMPLETED 02:36:39 11 3
DB_1 20/01/2013 18:03:20 20/01/2013
20:21:34 199 97 DB FULL COMPLETED 02:18:14 12 3
DB_1 20/01/2013 11:01:06 20/01/2013
12:30:24 200 97 DB FULL_0 COMPLETED 01:29:18 19 3
DB_1 19/01/2013 18:03:37 19/01/2013 20:43:02 199 97 DB FULL COMPLETED 02:39:25 11 3
DB_1 18/01/2013 23:00:49 18/01/2013
23:29:47 14 14 DB INCR_1 COMPLETED 00:28:58 8 2
DB_1 18/01/2013 18:03:59 18/01/2013
20:53:38 231 129 DB FULL COMPLETED 02:49:39 13 2
DB_1 17/01/2013 23:00:50 17/01/2013
23:24:11 1 1 DB INCR_1 COMPLETED 00:23:21 1
3
DB_1 17/01/2013 18:08:06 17/01/2013
20:47:12 199 97 DB FULL COMPLETED 02:39:06 11 3
DB_1 17/01/2013 15:03:16 17/01/2013
15:19:17 1 1 DB INCR_1 COMPLETED 00:16:01 1 3
3.2 Same Query, ready to run
$ cat rman_backup_report_cat_n_days.sql
-- RMAN Backup report Status
-- From RMAN Catalog
-- 01/17/13 vers 1.0 AUC
-- argument: Number of Days to look back for Backups
set serveroutput on size 1000000
SET VERIFY OFF ECHO OFF NEWP 0 SPA 0 PAGES 5000 FEED
OFF TRIMS ON TAB OFF lines 300
alter session set nls_date_format='MM/DD/YYYY
HH24:MI:SS';
variable a number
begin
:a:=&1;
end;
/
col name for a10
col Type for a15
col status for a10
col Elapsed for a10
col Start_At for a20
col End_At for a20
with rman_rep as
(
select
DB_NAME,DB_KEY,Start_At,End_At,DB_ARCs_Read_Sz_GB,Backup_Sz_GB,compression,Type,output_MB_per_sec,status,Elapsed
from (
select a.db_name,a.db_key,to_char(a.start_time,'DD/MM/YYYY
HH24:MI:SS') Start_At, to_char(end_time,'DD/MM/YYYY HH24:MI:SS') End_At,
ceil(input_bytes/1024/1024/1024) DB_ARCs_Read_Sz_GB,
ceil(a.output_bytes/1024/1024/1024) Backup_Sz_GB,
ceil(a.compression_ratio) compression,
ceil(output_bytes_per_sec/1024/1024) output_MB_per_sec,
decode(a.input_type,'DB
INCR',decode(b.incremental_level,'0','DB FULL_0','DB
INCR_'||b.incremental_level),a.input_type) Type,
a.status , a.time_taken_display Elapsed
from rc_rman_backup_job_details a , rc_backup_set_details
b
where a.session_key = b.session_key and
a.session_recid = b.session_recid and a.db_key = b.db_key
and a.start_time > sysdate-:a )
)
, rman_rep_run as
(
select
DB_NAME,DB_KEY,Start_At,End_At,DB_ARCs_Read_Sz_GB,Backup_Sz_GB,compression,Type,output_MB_per_sec,status,Elapsed
from (
select
a.db_name,a.db_key,to_char(a.start_time,'DD/MM/YYYY HH24:MI:SS') Start_At,
to_char(end_time,'DD/MM/YYYY HH24:MI:SS') End_At,
ceil(input_bytes/1024/1024/1024) DB_ARCs_Read_Sz_GB,
ceil(a.output_bytes/1024/1024/1024) Backup_Sz_GB,
ceil(a.compression_ratio) compression,
ceil(output_bytes_per_sec/1024/1024) output_MB_per_sec,
a.input_type Type, a.status , a.time_taken_display
Elapsed
from rc_rman_backup_job_details a
where a.start_time > sysdate-:a and a.status =
'RUNNING' )
)
select
name,Start_At,End_At,DB_ARCs_Read_Sz_GB,Backup_Sz_GB,' '||Type
Type,status,Elapsed,output_MB_per_sec,compression from rman_rep a , rc_database
b
where a.db_key = b.db_key and Type not like '%\_'
escape '\'
union -- when running, incremental_level is null
select
name,Start_At,null,null,null,null,status,null,null,null from rman_rep_run a ,
rc_database b
where a.db_key = b.db_key
union
select name,'No Backup
Found',null,null,null,null,null,null,null,null from dual , rc_database a
where not exists ( select '1' from rman_rep b where
a.db_key = b.db_key )
order by name, Start_At desc ;
exit
4] Collect Data before going for Incremental
Backups
At a Client, I had to show the Return On Investment of going
from a FULL RMAN Backup to an Incremental strategy.
Therefore, I wrote this SQL to collect information
from 9i and 10g+ DBs.
4.1 Overall DBs : Last FULL Backup Report
9i SQL
with
dbf_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) dbf_GB from
dba_data_files ) ,
seg_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) seg_GB from
dba_segments ) ,
tmp_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) tmp_GB from
dba_temp_files )
select a.name DB , null OS , log_mode , database_role , host_name ,
b.version , value compatible , dbf_sz.dbf_GB , seg_sz.seg_GB , tmp_sz.tmp_GB
from v$database a , v$instance b , v$parameter c , dbf_sz , seg_sz , tmp_sz
where c.name like 'compatible'
10g+ SQL
-- 10g+
with
dbf_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) dbf_GB from
dba_data_files ) ,
seg_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) seg_GB from
dba_segments ) ,
tmp_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) tmp_GB from
dba_temp_files )
/* 10g+ */
, rman_rep as (
select Start_At,End_At,DB_ARCs_Sz_GB,Backup_Sz_GB,Media,max(Dec_Type)
Type,status,Elapsed,output_MB_per_sec,compression,compressed,BCT
from (
select distinct to_char(a.start_time,'DD/MM/YYYY HH24:MI:SS') Start_At,
to_char(a.end_time,'DD/MM/YYYY HH24:MI:SS') End_At,
ceil(a.input_bytes/1024/1024/1024) DB_ARCs_Sz_GB,
ceil(a.output_bytes/1024/1024/1024) Backup_Sz_GB,
ceil(a.compression_ratio) compression,
ceil(a.output_bytes_per_sec/1024/1024) output_MB_per_sec, a.output_device_type
Media , -- a.input_type ,
decode(b.backup_type,'I','DB INCR',decode(b.backup_type,'L','Archlog',decode(b.backup_type,'D',decode(controlfile_included,'NO','DB
FULL','CTL')))) Dec_Type,
a.status , a.time_taken_display Elapsed -- , b.controlfile_included
, b.compressed , c.status BCT
from v$rman_backup_job_details a , v$backup_set_details b ,
v$block_change_tracking c
where a.session_key = b.session_key and a.session_recid = b.session_recid
order by Start_At desc
) where Dec_Type != 'CTL' and Dec_Type = 'DB FULL' and rownum < 2
group by Start_At,End_At,DB_ARCs_Sz_GB,Backup_Sz_GB,compression,output_MB_per_sec,Media,status,Elapsed,compressed,BCT
) /* 10g+ end */
select a.name DB , platform_name OS , log_mode , database_role , host_name
, b.version , value compatible , dbf_sz.dbf_GB , seg_sz.seg_GB , tmp_sz.tmp_GB
/* 10g+ */ , Media RMAN_Media,r.Type,r.status,Elapsed,to_char(DB_ARCs_Sz_GB)
DB_ARCs_Sz_GB,Backup_Sz_GB,compressed,BCT /* 10g+ end */
from v$database a , v$instance b , v$parameter c , dbf_sz , seg_sz , tmp_sz
/* 10g+ */ , rman_rep r /* 10g+ end */
where c.name like 'compatible'
/* 10g+ */
union
select a.name DB , platform_name OS , log_mode , database_role , host_name
, version , value compatible , dbf_sz.dbf_GB , seg_sz.seg_GB , tmp_sz.tmp_GB
, 'No Backup Found',null,null,null,null,null,null,null
from v$database a , v$instance , v$parameter c , dbf_sz , seg_sz , tmp_sz ,
dual
where not exists ( select '1' from rman_rep ) and c.name like 'compatible'
/* 10g+ end */
Since I query new 10g+ RMAN Views, I needed a dynamic
SQL generated accordingly to the BD version.
On those views, I only need the last FULL backup data.
--
cat rman_audit_backup.sql
set serveroutput on
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF lines 300
declare
v_vers varchar2(16) ;
v_res varchar2(256) ;
sCall varchar2(3096);
ct integer;
ig number;
begin
dbms_output.disable; -- *** Line added ***
dbms_output.enable(100000); -- ***
Line added ***
select substr(version,1,1) into v_vers from v$instance ;
-- dbms_output.put_line ('Oracle Version: '|| v_vers);
case when v_vers = '9' or v_vers = '8' then -- 9i version
begin
-- 9i+
sCall :='with '
||'dbf_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) dbf_GB from
dba_data_files ) , '
||'seg_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) seg_GB from
dba_segments ) , '
||'tmp_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) tmp_GB from
dba_temp_files ) '
||'select
''''||a.name||'';''||null||'';''||log_mode||'';''||database_role||'';''||host_name||'';''||b.version||'';''||value||'';''||dbf_sz.dbf_GB||'';''||seg_sz.seg_GB||'';''||tmp_sz.tmp_GB||''
'' '
||'from v$database a , v$instance b , v$parameter c , dbf_sz , seg_sz ,
tmp_sz '
||'where c.name like ''compatible'' ' ;
-- sCall :='select sysdate from dual' ;
-- dbms_output.put_line (sCall);
EXECUTE IMMEDIATE sCall INTO v_res ;
dbms_output.put_line (v_res);
end ;
else -- 10g+
version
begin
sCall :='with '
||'dbf_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) dbf_GB from
dba_data_files ) , '
||'seg_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) seg_GB from
dba_segments ) , '
||'tmp_sz as ( select to_char(ceil(sum(bytes)/1024/1024/1024)) tmp_GB from
dba_temp_files ), '
||'rman_rep as ( '
||'select Start_At,End_At,DB_ARCs_Sz_GB,Backup_Sz_GB,Media,max(Dec_Type)
Type,status,Elapsed,output_MB_per_sec,compression,compressed,BCT '
||' from ( '
||'select distinct to_char(a.start_time,''DD/MM/YYYY HH24:MI:SS'') Start_At,
to_char(a.end_time,''DD/MM/YYYY HH24:MI:SS'') End_At, '
||'ceil(a.input_bytes/1024/1024/1024) DB_ARCs_Sz_GB, ceil(a.output_bytes/1024/1024/1024)
Backup_Sz_GB, '
||'ceil(a.compression_ratio) compression,
ceil(a.output_bytes_per_sec/1024/1024) output_MB_per_sec, a.output_device_type
Media , '
||'decode(b.backup_type,''I'',''DB
INCR'',decode(b.backup_type,''L'',''Archlog'',decode(b.backup_type,''D'',decode(controlfile_included,''NO'',''DB
FULL'',''CTL'')))) Dec_Type, '
||'a.status , a.time_taken_display Elapsed '
||', b.compressed , decode(c.status,''ENABLED'',''YES'',''DISABLED'',''NO'')
BCT '
||'from v$rman_backup_job_details a , v$backup_set_details b ,
v$block_change_tracking c '
||'where a.session_key = b.session_key and a.session_recid = b.session_recid
'
||'order by Start_At desc '
||') where Dec_Type != ''CTL'' and Dec_Type = ''DB FULL'' and rownum < 2 '
||'group by Start_At,End_At,DB_ARCs_Sz_GB,Backup_Sz_GB,compression,output_MB_per_sec,Media,status,Elapsed,compressed,BCT
'
||') '
||'select
''''||DB||'';''||OS||'';''||log_mode||'';''||database_role||'';''||host_name||'';''||version||'';''||compatible||'';''||dbf_GB||'';''||seg_GB||'';''||tmp_GB||
'
||''';''||RMAN_Media||'';''||Type||'';''||status||'';''||Elapsed||'';''||DB_ARCs_Sz_GB||'';''||Backup_Sz_GB||'';''||compressed||'';''||BCT||''
'' '
||'from ( '
||'select a.name DB , platform_name OS , log_mode , database_role , host_name
, b.version , value compatible , dbf_sz.dbf_GB dbf_GB, seg_sz.seg_GB seg_GB,
tmp_sz.tmp_GB tmp_GB '
||', Media RMAN_Media,r.Type,r.status,Elapsed,to_char(DB_ARCs_Sz_GB)
DB_ARCs_Sz_GB,Backup_Sz_GB,compressed,BCT '
||'from v$database a , v$instance b , v$parameter c , dbf_sz , seg_sz ,
tmp_sz , rman_rep r '
||'where c.name like ''compatible'' '
||'union '
||'select a.name DB , platform_name OS , log_mode , database_role , host_name
, b.version , value compatible , dbf_sz.dbf_GB dbf_GB, seg_sz.seg_GB seg_GB,
tmp_sz.tmp_GB tmp_GB '
||', ''No Backup Found'',null,null,null,null,null,null,null '
||'from v$database a , v$instance b , v$parameter c , dbf_sz , seg_sz ,
tmp_sz , dual '
||'where not exists ( select ''1'' from rman_rep ) and c.name like ''compatible''
) ' ;
-- dbms_output.put_line (sCall);
EXECUTE IMMEDIATE sCall INTO v_res ;
dbms_output.put_line (v_res);
end ;
end case ;
end ;
/
exit
Results from
9i-10g-11g DBs :
$ for BDc in DB1 DB2 ; do
sqlplus -s ina_svc/<psw>@${BDc} @rman_audit_backup.sql; done
DB1;AIX-Based Systems
(64-bit);ARCHIVELOG;PRIMARY;sr-app-2;10.2.0.5.0;10.2.0.5;445;398;32;DISK;DB
FULL;COMPLETED;01:16:10;416;52;YES;DISABLED
DB2;AIX-Based Systems
(64-bit);ARCHIVELOG;PRIMARY;sr-app-2;10.2.0.5.0;10.2.0.5;30;20;2;DISK;DB FULL;COMPLETED;00:05:39;30;5;YES;DISABLED
. . .
DBn;Linux x86
64-bit;ARCHIVELOG;PRIMARY;db_srv-1.billettique.corp.fr;11.2.0.3.0;11.2.0.0.0;5;3;2;DISK;DB
FULL;COMPLETED;00:01:08;3;1;YES;DISABLED
Or get a csv
to be plugged into Excel, to sum volumes, durations and get the gains assuming
that Incremental Backups are around 10 % volume of a FULL.
$ for BDc in
DB1 DB2 … ; do sqlplus -s ina_svc/<psw>@${BDc} @rman_audit_backup.sql;
done |tee rman_audit_backup.csv
|
BASE
9i
|
version
|
compatible
|
dbf_GB
|
seg_GB
|
tmp_GB
|
|
DB9i
|
9.2.0.6.0
|
9.2.0.0.0
|
3
|
3
|
4
|
|
TOT
|
FULL
|
3
|
3
|
4
|
|
|
INCR
|
0,3
|
||||
|
Weekly
GAIN
|
13,5
|
|
BASE
10g+
|
version
|
Type
|
Status
|
Elapsed
|
DB_ARCs
Sz_GB |
Backup_Sz
GB |
compressed
|
BCT
|
|
DB1
|
10.2.0.5.0
|
DB FULL
|
COMPLETED
|
01:16:39
|
416
|
52
|
YES
|
NO
|
|
DB2
|
10.2.0.5.0
|
DB FULL
|
COMPLETED
|
00:05:34
|
30
|
5
|
YES
|
NO
|
|
…
|
||||||||
|
DBn
|
11.2.0.3.0
|
DB FULL
|
COMPLETED
|
00:00:52
|
2
|
1
|
YES
|
NO
|
|
DBo
|
11.2.0.3.0
|
DB FULL
|
COMPLETED
|
00:01:08
|
3
|
1
|
YES
|
NO
|
|
TOT
|
FULL
|
5:54:44
|
1330
|
186
|
||||
|
INCR
|
0:35:28
|
133
|
18,6
|
|||||
|
Weekly
GAIN
|
26:36:18
|
5985
|
837
|
4.2 Focus on a DB
select
set_count,used_change_tracking
bct,incremental_level
INCR,sum(datafile_blocks) "Total_Blocks",
sum(blocks_read) "Blocks
Reads",trunc(sum(blocks_read)/sum(datafile_blocks) * 10000)/100 as "% read",
sum(blocks) "Blocks
Writes",trunc(sum(blocks)/sum(datafile_blocks) * 10000)/100 as "%
write"
from v$backup_datafile
group by set_count,used_change_tracking,incremental_level
order by 1 desc;
|
SET_COUNT
|
BCT
|
INCR
|
Total_Blocks
|
Blocks Reads
|
% read
|
Blocks Writes
|
% write
|
|
9464
|
NO
|
|
2424
|
2424
|
100
|
2424
|
100
|
|
9460
|
NO
|
2
|
12021438
|
12021438
|
100
|
74150
|
0,61
|
|
9459
|
NO
|
2
|
12021502
|
12021502
|
100
|
902794
|
7,5
|
|
9458
|
NO
|
2
|
12021502
|
12021502
|
100
|
39330
|
0,32
|
|
9457
|
NO
|
2
|
12021502
|
12021502
|
100
|
423534
|
3,52
|
|
9456
|
NO
|
2
|
12021670
|
12021670
|
100
|
132057
|
1,09
|
|
9449
|
NO
|
|
2424
|
2424
|
100
|
2424
|
100
|
|
9448
|
NO
|
|
2424
|
2424
|
100
|
2424
|
100
|
|
9444
|
NO
|
0
|
12019342
|
8722916
|
72,57
|
8489997
|
70,63
|
|
9443
|
NO
|
0
|
12019358
|
11870810
|
98,76
|
11553661
|
96,12
|
|
9442
|
NO
|
0
|
12019390
|
11907662
|
99,07
|
11602367
|
96,53
|
|
9441
|
NO
|
0
|
12019446
|
11879997
|
98,83
|
11604757
|
96,54
|
|
9440
|
NO
|
0
|
12019454
|
11888040
|
98,9
|
11730742
|
97,59
|
|
9432
|
NO
|
|
2424
|
2424
|
100
|
2424
|
100
|
As a result
of the Write %, BCT should be installed.
5] RMAN Validate Backups
Backups are OK, but is it possible to Restore them ?
RMAN Dump files may have been erased and therefore, Restore is not
possible : this should be checked before we need to an actual
Restore !
One way is using the PREVIEW Option.
The other way uses a combination of : Oracle 12C
RMAN> run {
2> restore
database validate ;
3> recover
database validate header ;
My preferred method is compatible with 10G+
Instance : the first one, PREVIEW.
It will list every dumpfiles needed to restore ;
we can then check if those dumps are available ( using ssh to remote host ).
5.1 RMAN Script with PREVIEW Option
$ cat
rman_restor_db_preview_def_dsk.scr
#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+
# RMAN preview
script
# Database 10g+
# Media : DISK
# Catalog
# Default Last
Backup
#+-+-+-++-+-+-++-+-+-++-+-+-+-+-+-+-+
connect catalog
RCAT_CONNECT_STRING
run {
set until time
"to_date('2018-10-12 11:50:00','YYYY-MM-DD HH24:MI:SS')";
RESTORE DATABASE
PREVIEW ;
}
5.2 Shell / RMAN
$ cat rman_restor_db_preview_def_dsk_v2.ksh
#!/bin/ksh
# 26/02/16 - AUC
# Check Backups
# retour 0 ok 1 ko
# test args
if [ $# -lt 3 ] ; then
echo "Manque arguments"
echo "$0 PRO|NOPRO N_DAYS DEBUG_ON|OFF"
echo "N_DAYS Back, Current = 0"
echo "exemple d'appel:"
echo "$0 PRO 7 DEBUG_OFF"
echo "$0 NOPRO 1 DEBUG_OFF"
return 1 ; fi
envir=$1
N_DAYS=$2
Cmd=$3
MOTE=12
. /etc/profile${MOTE}.ora
rma_dir=/UTILS/rman_rep_daily/PREVIEW
rma_ori=rman_restor_db_preview_def_dsk.ori
rma_scr=rman_restor_db_preview_def_dsk.scr
file_gen=${rma_dir}/log/rman_restor_db_PREVIEW_dsk_${envir}_$(date
'+%m%d%y_%H%M').log
find ${rma_dir}/log -name '*.log' -mtime +31 -exec rm {} \;
# Get PSW
function get_psw_adb {
XXX
print $PSW
}
# Get database list and version
function get_rcat_bds {
print $LIST
}
function get_bds_ver {
XXX
print $VER
}
function get_host {
bd=$1
co="usr/psw@${bd}"
ora_cmd="select UPPER(replace(HOST_NAME,'.cg13.fr','')) from
v\$instance ;"
#ora_cmd="select UPPER(HOST_NAME) from v\$instance ;"
RET_V=$( sqlplus -s ${co} <<!
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
${ora_cmd}
!
)
print ${RET_V}
}
codret=0
ret_char=OK
scr_dir=/UTILS/scripts
str_ina_svc=usr/psw
# Plug de la Date - ARC
# %%DATE%%
# TZ="GMT+24" ; date '+%Y-%m-%d %H:%M:%S'
#new_date=$( /usr/linux/bin/date '+%Y-%m-%d %H:%M:%S' -d "${N_DAYS}
days ago" )
N_D=$((${N_DAYS} * 24))
new_date=$( TZ="GMT+${N_D}" ; date '+%Y-%m-%d %H:%M:%S' )
sed "s/%%DATE%%/${new_date}/" ${rma_dir}/${rma_ori} >
${rma_dir}/${rma_scr}
print "Date Until: "|tee ${file_gen}
grep until ${rma_dir}/${rma_scr} | grep -v '#'|tee -a ${file_gen}
li_bdc=$( get_rcat_bds ${envir} )
for BDC in ${li_bdc} ; do
rma_log=${rma_dir}/log/rman_restor_db_preview_def_dsk_${envir}_${BDC}_$(date
'+%m%d%y_%H%M').log
echo "\n*** $BDC - appel scr RMAN @${rma_dir}/${rma_scr} ***"|tee
-a ${file_gen}
echo "\n*** $BDC - log ${rma_log} ***"|tee -a ${file_gen}
psw=$( get_psw_adb SYS ${BDC} )
ove=$( get_bds_ver ${BDC} )
if [[ ${ove} = '10' ]] ; then continue ; fi
. /etc/profile${ove}.ora
# using Dedicated
STR="SYS/${psw}@${BDC}D"
#echo "***** $STR *****"
export NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'
rman target ${STR} @${rma_dir}/${rma_scr} log=${rma_log}
print "\n1/ result
ERR|RMAN-|ORA- :"|tee -a ${file_gen}
#egrep "ERR|RMAN-|ORA-|not found" ${rma_log} > /dev/null
egrep -v
"RMAN-06908|RMAN-06909" ${rma_log} |egrep "ERR|RMAN-|ORA-|no
backup of log thread|be created automatically during restore" >
/dev/null
if (( $? == 0 )) then print "\n!! Error execution rman : check log
${rma_log} !!\n"|tee -a ${file_gen} ;
codret=1 ; ret_char=KO ; continue ; else echo OK| tee -a ${file_gen} ;
fi
print "\n2/ result Piece
Name :"|tee -a ${file_gen}
#cat ${rma_log} |egrep -i 'Piece Name' |awk '{ print $3}' | xargs ls -l
2>&1 | egrep "does not exist|Permission denied|Aucun
fichier|Permission non|No such file"
if [[ $Cmd = "DEBUG_ON" ]] ; then # Tst version
cat ${rma_log} |egrep -i 'Piece Name' |awk '{ print $3}' | xargs ssh ${BDC}
"ls -l "
else # Prod version
cat
${rma_log} |egrep -i 'Piece Name' |awk '{ print $3}' | xargs ssh ${BDC}
"ls -l " 2>&1 | egrep "does not exist|Permission
denied|Aucun fichier|Permission non|No such file"
if (( $? == 0 )) then print "\n!! Error execution rman : check log
${rma_log} !!\n"|tee -a ${file_gen} ;
codret=1 ; ret_char=KO ; continue ; else echo OK| tee -a ${file_gen} ;
fi
fi
print "\n3/ result
Archive Logs:"|tee -a ${file_gen}
# ssh oracle@PROQU "ls -l /arch/pelun/PELUN1_24210_842120473.arc"
if [[ $Cmd = "DEBUG_ON" ]] ; then # Tst version
cat ${rma_log} |awk '{ print ($4=="A") ? $7 : ""}' |
xargs ssh ${BDC} "ls -l "
else # Prod version
cat
${rma_log} |awk '{ print ($4=="A") ? $7 : ""}' | xargs ssh
${BDC} "ls -l " 2>&1 | egrep "does not exist|Permission
denied|Aucun fichier|Permission non|No such file"
if (( $? == 0 )) then print "\n!! Error execution rman : check log
${rma_log} !!\n"|tee -a ${file_gen} ;
codret=1 ; ret_char=KO ; continue ;
else echo OK| tee -a ${file_gen} ; fi
fi
done
if [[ $Cmd = "DEBUG_OFF" ]] ; then
cat ${file_gen} | mailx -s "[RMAN] Check Restoration Preview -
${envir} - ${N_DAYS} days ago - status: ${ret_char}" 'dsit.dba@cg13.fr'
fi
return ${codret}
5.3 Run Shell
# RMAN Check
20 13 * * 1-5 /UTILS/rman_rep_daily/PREVIEW/rman_restor_db_preview_def_dsk_v2.ksh
DEV 7 DEBUG_OFF
30 13 * * 1-5 /UTILS/rman_rep_daily/PREVIEW/rman_restor_db_preview_def_dsk_v2.ksh
QUA 7 DEBUG_OFF
40 13 * * 1-5 /UTILS/rman_rep_daily/PREVIEW/rman_restor_db_preview_def_dsk_v2.ksh
PPR 7 DEBUG_OFF
Mail
sent :
[RMAN] Check Restoration Preview -
PRO - 10 days ago - status: OK
Date Until:
set until time "to_date('2018-10-13 11:50:00','YYYY-MM-DD
HH24:MI:SS')";
*** DBNAME - appel scr RMAN @/UTILS/oracle/dba/bao/rman_rep_daily/PREVIEW/rman_restor_db_preview_def_dsk.scr
***
*** DBNAME - log
/UTILS/oracle/dba/bao/rman_rep_daily/PREVIEW/log/rman_restor_db_preview_def_dsk_PRO_DBNAME_102318_1350.log
***
1/ result ERR|RMAN-|ORA- :
OK
2/ result Piece Name :
OK
3/ result Archive Logs:
OK
