RMAN Backup Details
-------------------
set linesize 150
col INSTANCE for a9
col ELAPSED for a30
SELECT ( SELECT instance_name FROM v$instance)
|| ' '
|| ( SELECT instance_number FROM v$instance)
instance,
-- TO_CHAR (start_time, 'YYYY-MM-DD HH24:MI') start_time,
to_date (start_time, 'DD-MM-YYYY HH24:MI:SS') start_time,
TO_CHAR (output_bytes / 1048576, '999,999,999.9') output_mb,
TO_CHAR (output_bytes_per_sec / 1048576, '999,999.9') mb_S,
time_taken_display elapsed,input_type,status
FROM v$rman_backup_job_details
where start_time >= sysdate - &Days
ORDER BY start_time;
RMAN Backup Details
-------------------
select ctime "Date",
decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,
bsize "Size MB"
from (select trunc(bp.completion_time) ctime
, backup_type
, round(sum(bp.bytes/1024/1024),2) bsize
from v$backup_set bs, v$backup_piece bp
where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count
and bp.status = 'A'
group by trunc(bp.completion_time), backup_type)
order by 1, 2;
TAG Details
-----------
select bp.TAG, bp.handle, bp.STAMP, decode(bs.backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental')
backup_type,bp.SET_STAMP, to_char(bp.completion_time + 30/1440,'yyyy/mm/dd HH24:MI:SS') end_time from v$backup_set bs, v$backup_piece bp where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count and tag='&TAG';
RMAN Backup Details
-------------------
set pages 9999 lines 300
col status for a10
col object_type for a10
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select SID,STATUS,START_TIME,END_TIME,OBJECT_TYPE,OUTPUT_DEVICE_TYPE from v$rman_status order by START_TIME desc;
select max(START_TIME) "START_TIME" ,END_TIME,ELAPSED_SECONDS/60 "ELAPSED_MINS", OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES_GB",STATUS from v$rman_backup_job_details group by start_time,END_TIME,ELAPSED_SECONDS,OUTPUT_BYTES, STATUS having max(START_TIME) > sysdate-1;
level 0 backup in the last 7 days
---------------------------------
select /*+ rule */ NVL(min(r.status),'NO BACKUP') as status from V$RMAN_BACKUP_JOB_DETAILS r inner join
(select distinct session_stamp, incremental_level from v$backup_set_details) b on r.session_stamp = b.session_stamp where incremental_level is not null and r.start_time > sysdate - 7 and b.incremental_level = 0;
Longops - RMAN
--------------
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", TIME_REMAINING/60
Remaining_mins FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
More
----
List the most recent Level 0 backups
Here,
incremental level 0 backups which runs
on Sunday using "backup as compressed backupset incremental level 0 ...."
We have incremental level 1 backups which runs
everyday other than Sunday using "backup as compressed backupset incremental level 1 database...."
select BACKUP_TYPE,INCREMENTAL_LEVEL,START_TIME,COMPLETION_TIME from v$backup_set
where START_TIME > sysdate -4 and BACKUP_TYPE='L';
B INCREMENTAL_LEVEL START_TIME COMPLETION_TIME
- ----------------- ----------------- -----------------
D 0 28-AUG-2012 04:30 28-AUG-2012 04:33
D 0 28-AUG-2012 04:30 28-AUG-2012 04:33
I 1 29-AUG-2012 04:30 29-AUG-2012 04:33
I 1 29-AUG-2012 04:30 29-AUG-2012 04:35
I 1 30-AUG-2012 04:30 30-AUG-2012 04:33
I 1 30-AUG-2012 04:30 30-AUG-2012 04:35
I 1 31-AUG-2012 04:30 31-AUG-2012 04:33
I 1 31-AUG-2012 04:30 31-AUG-2012 04:35
D 31-AUG-2012 04:35 31-AUG-2012 04:35
D 31-AUG-2012 06:30 31-AUG-2012 06:30
D 31-AUG-2012 08:30 31-AUG-2012 08:30
As per Oracle documentation V$BACKUP_SET,BACKUP_TYPE holds
"BACKUP_TYPE VARCHAR2(1) Type of files that are in this backup. If the
backup contains archived redo logs, the value is L. If this is a datafile
full
backup, the value is D. If this is an incremental backup, the value is I."
"INCREMENTAL_LEVEL NUMBER Location where this backup set fits into
the database's backup strategy. Set to zero for full datafile backups,
nonzero for incremental datafile backups, and NULL for archivelog
backups."
So the above query shows an entry with "D" which means a full datafile
backup did run today, but you can see INCREMENTAL_LEVEL is null which
means it is not a full datafile backup.
SELECT DISTINCT TO_CHAR((b.CHECKPOINT_TIME), 'YYYY-MM-DD HH:MI.SS') t
FROM v$backup_datafile b, v$tablespace ts, v$datafile f
WHERE b.incremental_level = 0
AND INCLUDED_IN_DATABASE_BACKUP='YES'
AND f.file#=b.file#
AND f.ts#=ts.ts#
GROUP BY b.checkpoint_time
ORDER BY 1;
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
If you look at LV column, A=archivelog, 0=incremental level #, F=file backup.
RMAN> list backup by file tag <tag_name>;
Managing and Monitoring RMAN Backups
LIST
RMAN> list backup; // backup sets
RMAN> list copy; // image copies
RMAN> list backup of database; // whole database backup sets, full or incremental
RMAN> list backup of datafile 1;
RMAN> list backup of tablespace users;
RMAN> list backup of archivelog all;
RMAN> list copy of archivelog from time=’sysdate – 2';
RMAN> list backup of archivelog from sequence 1000 until sequence 1050;
REPORT
RMAN> report schema; // “schema” is confusing. this just shows datafiles and has nothing to do with user objects.
RMAN> report need backup;
RMAN> report need backup days 3;
RMAN> report need backup redundancy 3;
RMAN> report obsolete;
RMAN> report obsolete redundancy 2; // shows backups that are older than two others
DELETE
RMAN> delete obsolete;
RMAN> delete obsolete redundancy 2; // delete backups that are older than two others
Views & Tables
These views and data are contained in the control file only (not recovery catalog).
V$BLOCK_CHANGE_TRACKING – to monitor block change tracking
V$BACKUP_FILES
V$BACKUP_SET
V$BACKUP_PIECE
V$BACKUP_REDOLOG – each archived log that has been backed up
V$BACKUP_SPFILE – each spfile that has been backed up
V$BACKUP_DEVICE – names of sbt devices
V$RMAN_CONFIGURATION
Parameters
DB_CREATE_FILE_DEST – default location for the change tracking file.
level 0 incremental backup the base for subsequent incremental backups, copies all blocks containing data.
You can create a level 0 database backup as backup sets or image copies.
level 1 cumulative incremental backup backs up all blocks changed after the
most recent incremental backup at level 0
level 1 differential incremental backup backs up all blocks changed after the most recent incremental backup at
level 1 or 0 - Incremental backups are differential by default.
Block Change Tracking The block change tracking feature for incremental backups improves backup
performance by recording changed blocks for each datafile. - a small bitmap file.
An example is shown below where we are checking the validity of a database backup for a database which has a
weekly level 0 backup. Note that the RESTORE DATABASE VALIDATE command will cause RMAN to check for the last level
0 backup which has been performed in this case on the 15th of November.
RMAN> restore database validate;
Starting restore at 20-NOV-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
channel ORA_SBT_TAPE_1: starting validation of datafile backup set
channel ORA_SBT_TAPE_1: reading from backup piece vnkud0k5_1_1
channel ORA_SBT_TAPE_1: piece handle=vnkud0k5_1_1 tag=TAG20091115T060908
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: validation complete, elapsed time: 00:14:15
Finished restore at 20-NOV-09
RMAN> list backup of database tag TAG20091115T060908;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
3054 Incr 0 15.59G SBT_TAPE 00:39:00 15-NOV-09
BP Key: 3054 Status: AVAILABLE Compressed: NO Tag: TAG20091115T060908
Handle: vnkud0k5_1_1 Media:
List of Datafiles in backup set 3054
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/system01.dbf
2 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/undotbs01.dbf
3 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/sysaux01.dbf
4 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/users01.dbf
5 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/mgmt.dbf
6 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/mgmt_ecm_depot1.dbf
7 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/patrol01.dbf
http://www.freelists.org/post/oracle-l/RMAN-Query-on-VBACKUP-SET-inconsistency,1
http://gavinsoorma.com/2009/11/rman-restore-validate-examples/
http://myoracleworld.hobby-electronics.net/RMAN-basic-excersizes.html
http://www.pafumi.net/rman.htm
http://orainstance.wordpress.com/2013/04/18/interview-questions-answer-on-rman/
-------------------
set linesize 150
col INSTANCE for a9
col ELAPSED for a30
SELECT ( SELECT instance_name FROM v$instance)
|| ' '
|| ( SELECT instance_number FROM v$instance)
instance,
-- TO_CHAR (start_time, 'YYYY-MM-DD HH24:MI') start_time,
to_date (start_time, 'DD-MM-YYYY HH24:MI:SS') start_time,
TO_CHAR (output_bytes / 1048576, '999,999,999.9') output_mb,
TO_CHAR (output_bytes_per_sec / 1048576, '999,999.9') mb_S,
time_taken_display elapsed,input_type,status
FROM v$rman_backup_job_details
where start_time >= sysdate - &Days
ORDER BY start_time;
RMAN Backup Details
-------------------
select ctime "Date",
decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,
bsize "Size MB"
from (select trunc(bp.completion_time) ctime
, backup_type
, round(sum(bp.bytes/1024/1024),2) bsize
from v$backup_set bs, v$backup_piece bp
where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count
and bp.status = 'A'
group by trunc(bp.completion_time), backup_type)
order by 1, 2;
TAG Details
-----------
select bp.TAG, bp.handle, bp.STAMP, decode(bs.backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental')
backup_type,bp.SET_STAMP, to_char(bp.completion_time + 30/1440,'yyyy/mm/dd HH24:MI:SS') end_time from v$backup_set bs, v$backup_piece bp where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count and tag='&TAG';
RMAN Backup Details
-------------------
set pages 9999 lines 300
col status for a10
col object_type for a10
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select SID,STATUS,START_TIME,END_TIME,OBJECT_TYPE,OUTPUT_DEVICE_TYPE from v$rman_status order by START_TIME desc;
select max(START_TIME) "START_TIME" ,END_TIME,ELAPSED_SECONDS/60 "ELAPSED_MINS", OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES_GB",STATUS from v$rman_backup_job_details group by start_time,END_TIME,ELAPSED_SECONDS,OUTPUT_BYTES, STATUS having max(START_TIME) > sysdate-1;
level 0 backup in the last 7 days
---------------------------------
select /*+ rule */ NVL(min(r.status),'NO BACKUP') as status from V$RMAN_BACKUP_JOB_DETAILS r inner join
(select distinct session_stamp, incremental_level from v$backup_set_details) b on r.session_stamp = b.session_stamp where incremental_level is not null and r.start_time > sysdate - 7 and b.incremental_level = 0;
Longops - RMAN
--------------
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", TIME_REMAINING/60
Remaining_mins FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
More
----
List the most recent Level 0 backups
Here,
incremental level 0 backups which runs
on Sunday using "backup as compressed backupset incremental level 0 ...."
We have incremental level 1 backups which runs
everyday other than Sunday using "backup as compressed backupset incremental level 1 database...."
select BACKUP_TYPE,INCREMENTAL_LEVEL,START_TIME,COMPLETION_TIME from v$backup_set
where START_TIME > sysdate -4 and BACKUP_TYPE='L';
B INCREMENTAL_LEVEL START_TIME COMPLETION_TIME
- ----------------- ----------------- -----------------
D 0 28-AUG-2012 04:30 28-AUG-2012 04:33
D 0 28-AUG-2012 04:30 28-AUG-2012 04:33
I 1 29-AUG-2012 04:30 29-AUG-2012 04:33
I 1 29-AUG-2012 04:30 29-AUG-2012 04:35
I 1 30-AUG-2012 04:30 30-AUG-2012 04:33
I 1 30-AUG-2012 04:30 30-AUG-2012 04:35
I 1 31-AUG-2012 04:30 31-AUG-2012 04:33
I 1 31-AUG-2012 04:30 31-AUG-2012 04:35
D 31-AUG-2012 04:35 31-AUG-2012 04:35
D 31-AUG-2012 06:30 31-AUG-2012 06:30
D 31-AUG-2012 08:30 31-AUG-2012 08:30
As per Oracle documentation V$BACKUP_SET,BACKUP_TYPE holds
"BACKUP_TYPE VARCHAR2(1) Type of files that are in this backup. If the
backup contains archived redo logs, the value is L. If this is a datafile
full
backup, the value is D. If this is an incremental backup, the value is I."
"INCREMENTAL_LEVEL NUMBER Location where this backup set fits into
the database's backup strategy. Set to zero for full datafile backups,
nonzero for incremental datafile backups, and NULL for archivelog
backups."
So the above query shows an entry with "D" which means a full datafile
backup did run today, but you can see INCREMENTAL_LEVEL is null which
means it is not a full datafile backup.
SELECT DISTINCT TO_CHAR((b.CHECKPOINT_TIME), 'YYYY-MM-DD HH:MI.SS') t
FROM v$backup_datafile b, v$tablespace ts, v$datafile f
WHERE b.incremental_level = 0
AND INCLUDED_IN_DATABASE_BACKUP='YES'
AND f.file#=b.file#
AND f.ts#=ts.ts#
GROUP BY b.checkpoint_time
ORDER BY 1;
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
If you look at LV column, A=archivelog, 0=incremental level #, F=file backup.
RMAN> list backup by file tag <tag_name>;
Managing and Monitoring RMAN Backups
LIST
RMAN> list backup; // backup sets
RMAN> list copy; // image copies
RMAN> list backup of database; // whole database backup sets, full or incremental
RMAN> list backup of datafile 1;
RMAN> list backup of tablespace users;
RMAN> list backup of archivelog all;
RMAN> list copy of archivelog from time=’sysdate – 2';
RMAN> list backup of archivelog from sequence 1000 until sequence 1050;
REPORT
RMAN> report schema; // “schema” is confusing. this just shows datafiles and has nothing to do with user objects.
RMAN> report need backup;
RMAN> report need backup days 3;
RMAN> report need backup redundancy 3;
RMAN> report obsolete;
RMAN> report obsolete redundancy 2; // shows backups that are older than two others
DELETE
RMAN> delete obsolete;
RMAN> delete obsolete redundancy 2; // delete backups that are older than two others
Views & Tables
These views and data are contained in the control file only (not recovery catalog).
V$BLOCK_CHANGE_TRACKING – to monitor block change tracking
V$BACKUP_FILES
V$BACKUP_SET
V$BACKUP_PIECE
V$BACKUP_REDOLOG – each archived log that has been backed up
V$BACKUP_SPFILE – each spfile that has been backed up
V$BACKUP_DEVICE – names of sbt devices
V$RMAN_CONFIGURATION
Parameters
DB_CREATE_FILE_DEST – default location for the change tracking file.
level 0 incremental backup the base for subsequent incremental backups, copies all blocks containing data.
You can create a level 0 database backup as backup sets or image copies.
level 1 cumulative incremental backup backs up all blocks changed after the
most recent incremental backup at level 0
level 1 differential incremental backup backs up all blocks changed after the most recent incremental backup at
level 1 or 0 - Incremental backups are differential by default.
Block Change Tracking The block change tracking feature for incremental backups improves backup
performance by recording changed blocks for each datafile. - a small bitmap file.
An example is shown below where we are checking the validity of a database backup for a database which has a
weekly level 0 backup. Note that the RESTORE DATABASE VALIDATE command will cause RMAN to check for the last level
0 backup which has been performed in this case on the 15th of November.
RMAN> restore database validate;
Starting restore at 20-NOV-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1
channel ORA_SBT_TAPE_1: starting validation of datafile backup set
channel ORA_SBT_TAPE_1: reading from backup piece vnkud0k5_1_1
channel ORA_SBT_TAPE_1: piece handle=vnkud0k5_1_1 tag=TAG20091115T060908
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: validation complete, elapsed time: 00:14:15
Finished restore at 20-NOV-09
RMAN> list backup of database tag TAG20091115T060908;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
3054 Incr 0 15.59G SBT_TAPE 00:39:00 15-NOV-09
BP Key: 3054 Status: AVAILABLE Compressed: NO Tag: TAG20091115T060908
Handle: vnkud0k5_1_1 Media:
List of Datafiles in backup set 3054
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/system01.dbf
2 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/undotbs01.dbf
3 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/sysaux01.dbf
4 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/users01.dbf
5 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/mgmt.dbf
6 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/mgmt_ecm_depot1.dbf
7 0 Incr 23674579905 15-NOV-09 /u04/oradata/emrep/patrol01.dbf
http://www.freelists.org/post/oracle-l/RMAN-Query-on-VBACKUP-SET-inconsistency,1
http://gavinsoorma.com/2009/11/rman-restore-validate-examples/
http://myoracleworld.hobby-electronics.net/RMAN-basic-excersizes.html
http://www.pafumi.net/rman.htm
http://orainstance.wordpress.com/2013/04/18/interview-questions-answer-on-rman/
No comments:
Post a Comment