How to check RMAN backup job status in Oracle using
v$rman_backup_job_details
RMAN backup job details for 'n' number of days:-
=========================================
Monitoring RMAN backup status
using v$rman_backup_job_details and v$rman_status.
Note : - Enter the number of days required for
status report, for 1 day backup status report provide input as '1'.
RMAN backup status using v$rman_backup_job_details :-
set pages 9999 lines 500
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') output_mb_per_sec,
time_taken_display elapsed,input_type,status
FROM v$rman_backup_job_details
where start_time >= sysdate - &NUMBER_OF_DAYS
ORDER BY start_time
/
RMAN backup status using v$rman_backup_job_details , v$rman_status:-
set pages 9999 lines 500
set numformat 99999.99
set trim on
set trims on
alter session set nls_date_format = 'DD-MM-YYYY
HH24:MI:SS';
col INSTANCE for a9
col status for a22
col COMMAND_ID for a20
col INPUT_TYPE for a10
col OUTPUT_DEVICE_TYPE for a10
col OUTPUT_BYTES_PER_SEC_DISPLAY for a9
col status heading "BACKUP|STATUS"
col COMMAND_ID heading "BACKUP
NAME"
col STARTED_TIME heading "START
TIME"
COL END_TIME heading "END TIME"
col ELAPSED_TIME heading "MINUTES |
TAKEN"
col INPUT_TYPE heading "INPUT|TYPE"
col OUTPUT_DEVICE_TYPE heading
"OUTPUT|DEVICES"
col INPUT_SIZE heading "INPUT
SIZE|GB"
col OUTPUT_SIZE heading "OUTPUT
SIZE|GB"
col OUTPUT_BYTES_PER_SEC_DISPLAY heading
"OUTPUT | RATE|(PER SEC)"
SELECT (SELECT instance_name FROM v$instance) || '
' || (SELECT instance_number FROM v$instance) instance,rs.sid,
rj.COMMAND_ID,
rj.STATUS,
max(rj.START_TIME) STARTED_TIME,
rj.END_TIME,
rj.ELAPSED_SECONDS/60 ELAPSED_TIME,
rj.INPUT_TYPE,
rj.OUTPUT_DEVICE_TYPE,
rj.INPUT_BYTES/1024/1024/1024 INPUT_SIZE,
rj.OUTPUT_BYTES/1024/1024/1024 OUTPUT_SIZE,
rj.OUTPUT_BYTES_PER_SEC_DISPLAY
from v$rman_backup_job_details rj, v$rman_status rs
where rj.COMMAND_ID=rs.COMMAND_ID
group by rs.sid,rj.COMMAND_ID,rj.STATUS,rj.START_TIME,rj.END_TIME,rj.ELAPSED_SECONDS,rj.INPUT_TYPE,rj.OUTPUT_DEVICE_TYPE,rj.INPUT_BYTES,rj.OUTPUT_BYTES,rj.OUTPUT_BYTES_PER_SEC_DISPLAY
having max(rj.START_TIME) >
sysdate-&NUMBER_OF_DAYS order by rj.START_TIME desc
/
BACKUP
MINUTES INPUT
OUTPUT INPUT SIZE OUTPUT SIZE OUTPUT RATE
INSTANCE SID BACKUP NAME
STATUS
START TIME END TIME
TAKEN TYPE DEVICES
GB
GB (PER SEC)
--------- --- --------------------
---------------------- ------------------- ------------------- ---------
---------- ---------- ---------- ----------- ------------
To get the job details for a specific backup job, use the following query:-
set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 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
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.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
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;
Where,
CF: Number of controlfile backups included in the backup set
DF: Number of datafile full backups included in the backup set
I0: Number of datafile incremental level-0 backups included in the backup set
I1: Number of datafile incremental level-1 backups included in the backup set
L: Number of archived log backups included in the backup set
Backup set details : -
To get the Backup set details for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, use the following query:
set lines 220
set pages 1000
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
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 session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by d.start_time;
Backup job output :-
To get the Backup job output for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, use the following query:
set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by recid;
Database links backup
---------------------
All DBlinks backup:
---------------------
set pages 0
set long 90000
set trimspool on
SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;
For a specific DBlink backup:
---------------------------------
set pages 0
set long 90000
set trimspool on
SELECT DBMS_METADATA.GET_DDL('DB_LINK','&DB_LINK_NAME','&USER_NAME') FROM dba_db_links;
---------------------
All DBlinks backup:
---------------------
set pages 0
set long 90000
set trimspool on
SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;
For a specific DBlink backup:
---------------------------------
set pages 0
set long 90000
set trimspool on
SELECT DBMS_METADATA.GET_DDL('DB_LINK','&DB_LINK_NAME','&USER_NAME') FROM dba_db_links;
PROCEDURE BACKUP IN ORACLE
PROCEDURE BACKUP
----------------
set heading off
set feedback off
set trimspool on
set echo off
set flush off
set pagesize 0
set linesize 32767
set long 99999999
spool PROCEDURE_NAME.LOG
select dbms_metadata.get_ddl('PROCEDURE','&PROCEDURE_NAME','&OWNER_NAME') from dual
/
spool off;
OR
set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool PROCEDURE_NAME.LOG
select text from dba_source where owner='&OWNER_NAME'and name='&PROCEDURE_NAME'
/
spool off;
----------------
set heading off
set feedback off
set trimspool on
set echo off
set flush off
set pagesize 0
set linesize 32767
set long 99999999
spool PROCEDURE_NAME.LOG
select dbms_metadata.get_ddl('PROCEDURE','&PROCEDURE_NAME','&OWNER_NAME') from dual
/
spool off;
OR
set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool PROCEDURE_NAME.LOG
select text from dba_source where owner='&OWNER_NAME'and name='&PROCEDURE_NAME'
/
spool off;
Oracle DBA Interview Questions and Answers - Backup
and Recovery
Oracle Backup and Recovery Interview Questions and
Answers
How would you decide your backup strategy and timing for backup?In fact backup strategy is purely depends upon your organization business need.
If no downtime then database must be run on archivelog mode and you have to take frequently or daily backup.
If sufficient downtime is there and loss of data would not affect your business then you can run your database in noarchivelog mode and backup can be taken in-frequently or weekly or monthly.
In most of the case in an organization when no downtime then frequent inconsistent backup needed (daily backup), multiplex online redo log files (multiple copies), different location for redo log files, database must run in archivelog mode and dataguard can be implemented for extra bit of protection.
What is difference between Restoring and Recovery of database?
Restoring means copying the database object from the backup media to the destination where actually it is required where as recovery means to apply the database object copied earlier (roll forward) in order to bring the database into consistent state.
What is the difference between complete and incomplete recovery?An incomplete database recovery is a recovery that it does not reach to the point of failure. The recovery can be either point of time or particular SCN or Particular archive log specially incase of missing archive log or redolog failure where as a complete recovery recovers to the point of failure possibly when having all archive log backup.
What is the benefit of running the DB in archivelog mode over no archivelog mode?
When a database is in no archivelog mode whenever log switch happens there will be a loss of some redoes log information in order to avoid this, redo logs must be archived. This can be achieved by configuring the database in archivelog mode.
If an oracle database is crashed? How would you recover that transaction which is not in backup?If the database is in archivelog we can recover that transaction otherwise we cannot recover that transaction which is not in backup.
What is the difference between HOTBACKUP and RMAN backup?
For hotbackup we have to put database in begin backup mode, then take backup where as RMAN would not put database in begin backup mode. RMAN is faster can perform incremental (changes only) backup, and does not place tablespace in hotbackup mode.
Can we use Same target database as Catalog database?
No, the recovery catalog should not reside in the target database (database to be backed up) because the database can not be recovered in the mounted state.
How would you decide your backup strategy and timing for backup?In fact backup strategy is purely depends upon your organization business need.
If no downtime then database must be run on archivelog mode and you have to take frequently or daily backup.
If sufficient downtime is there and loss of data would not affect your business then you can run your database in noarchivelog mode and backup can be taken in-frequently or weekly or monthly.
In most of the case in an organization when no downtime then frequent inconsistent backup needed (daily backup), multiplex online redo log files (multiple copies), different location for redo log files, database must run in archivelog mode and dataguard can be implemented for extra bit of protection.
What is difference between Restoring and Recovery of database?
Restoring means copying the database object from the backup media to the destination where actually it is required where as recovery means to apply the database object copied earlier (roll forward) in order to bring the database into consistent state.
What is the difference between complete and incomplete recovery?An incomplete database recovery is a recovery that it does not reach to the point of failure. The recovery can be either point of time or particular SCN or Particular archive log specially incase of missing archive log or redolog failure where as a complete recovery recovers to the point of failure possibly when having all archive log backup.
What is the benefit of running the DB in archivelog mode over no archivelog mode?
When a database is in no archivelog mode whenever log switch happens there will be a loss of some redoes log information in order to avoid this, redo logs must be archived. This can be achieved by configuring the database in archivelog mode.
If an oracle database is crashed? How would you recover that transaction which is not in backup?If the database is in archivelog we can recover that transaction otherwise we cannot recover that transaction which is not in backup.
What is the difference between HOTBACKUP and RMAN backup?
For hotbackup we have to put database in begin backup mode, then take backup where as RMAN would not put database in begin backup mode. RMAN is faster can perform incremental (changes only) backup, and does not place tablespace in hotbackup mode.
Can we use Same target database as Catalog database?
No, the recovery catalog should not reside in the target database (database to be backed up) because the database can not be recovered in the mounted state.
Incremental backup levels:
Level 0 – full backup that can be used for
subsequent incrementals
RMAN> backup incremental level 0 database;
Differential Level 1–only the blocks that have changed
since the last backup (whether it is level 0 or level 1)
RMAN> backup incremental level 1 differential
database;
Cumulative Level 1 – all changes since the last
level 0 incremental backup
RMAN> backup incremental level 1 cumulative
database;
A full backup cannot be used for a cumulative level
1 backup.
A cumulative level 1 backup must be done on top of
an incremental level 0 backup.
Why RMAN incremental backup fails even though full backup exists?If you have taken the RMAN full backup using the command ‘Backup database’, where as a level 0 backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository so it can be used as the parent for a level 1 backup. Simply the ‘full backup without level 0’ can not be considered as a parent backup from which you can take level 1 backup.
Can we perform RMAN level 1 backup without level 0?If no level 0 is available, then the behavior depends upon the compatibility mode setting (oracle version).
If the compatibility mode less than 10.0.0, RMAN generates a level 0 backup of files contents at the time of backup.
If the compatibility is greater than 10.0.0, RMAN copies all block changes since the file was created, and stores the results as level 1 backup.
How to put Manual/User managed backup in RMAN?In case of recovery catalog, you can put by using catalog command:
RMAN> CATALOG START WITH ‘/oracle/backup.ctl’;
How to check RMAN version in oracle?If you want to check RMAN catalog version then use the below query from SQL*plus
SQL> Select * from rcver;
What happens actually in case of instance Recovery?While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re-started. Instance recovery occurs in 2 steps:
Cache recovery: Changes being made to a database are recorded in the database buffer cache as well as redo log files simultaneously. When there are enough data in the database buffer cache, they are written to data files. If an Oracle instance fails before these data are written to data files, Oracle uses online redo log files to recover the lost data when the associated database is re-started. This process is called cache recovery.
Transaction recovery: When a transaction modifies data in a database (the before image of the modified data is stored in an undo segment which is used to restore the original values in case the transaction is rolled back). At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files. To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery.
My Database has Level 1 backup, tell me what are all backed up ? with Example?
Database is UP and has taken Level 0 backup, is the backup taken is Consistent or Inconsistent?
How do you say a backup is Consistent or Inconsistent, Oracle Terminology?
Can we take backup when the Database is down?
If i have a RMAN full backup Level 0 of Sun @9PM, on Mon 9PM taken incremental Level 1 backup.What type of backup do you get and what is actually backedup?
If i have a RMAN full backup of Sun @9PM, on Mon 9PM taken incremental Level 1 backup.On Tuesday Database Crashed.What type of backup do you get and what is actually backedup?
There is no Backup available, Can we take a Level 1 backup?
A table got dropped between 9AM - 11AM how to get the Table backup using RMAN,
DB size 500GB available mount point space for table recovery is 15GB?
Sys Admin has changed the time from 10:00 AM to 9:30 AM, table dropped, How do you recover the Table?
A DATAFILE is corrupted and there is no backup, How to recover the datafile?
All Controlfiles are corrupted, How to recover the controlfile?
RMAN Backup of Database
How to take RMAN backup with and without
incremental level.
RMAN INCREMENTAL BACKUP LEVEL 0
$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 0 database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc' not backed up 1 times;;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>
RMAN INCREMENTAL BACKUP LEVEL 1
$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 1 database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc' not backed up 1 times;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>
RMAN BACKUP OF DATABASE (WITHOUT USING LEVEL 0)
$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>
NOTE:-
If you have taken the RMAN full backup using the command ‘Backup database’, where as a level 0 backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository so it can be used as the parent for a level 1 backup. Simply the ‘full backup without level 0’ cannot be considered as a parent backup from which you can take level 1 backup.
Incremental backup levels:-
Level 0 – full backup that can be used for subsequent incrementals
RMAN> backup incremental level 0 database;
Differential Level 1–only the blocks that have changed since the last backup (whether it is level 0 or level 1)
RMAN> backup incremental level 1 differential database;
Cumulative Level 1 – all changes since the last level 0 incremental backup
RMAN> backup incremental level 1 cumulative database;
A full backup cannot be used for a cumulative level 1 backup.
A cumulative level 1 backup must be done on top of an incremental level 0 backup.
RMAN INCREMENTAL BACKUP LEVEL 0
$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 0 database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc' not backed up 1 times;;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>
RMAN INCREMENTAL BACKUP LEVEL 1
$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 1 database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc' not backed up 1 times;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>
RMAN BACKUP OF DATABASE (WITHOUT USING LEVEL 0)
$ rman target /
RMAN> show all;
RMAN>
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup database format '/path/%d_%T_%t_%s_%p.dbf';
backup archivelog all format '/path/%d_%T_%t_%s_%p.arc;
backup current controlfile format '/path/%d_%T_%t_%s_%p.ctl';
release channel ch1;
release channel ch2;
}
RMAN>
NOTE:-
If you have taken the RMAN full backup using the command ‘Backup database’, where as a level 0 backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository so it can be used as the parent for a level 1 backup. Simply the ‘full backup without level 0’ cannot be considered as a parent backup from which you can take level 1 backup.
Incremental backup levels:-
Level 0 – full backup that can be used for subsequent incrementals
RMAN> backup incremental level 0 database;
Differential Level 1–only the blocks that have changed since the last backup (whether it is level 0 or level 1)
RMAN> backup incremental level 1 differential database;
Cumulative Level 1 – all changes since the last level 0 incremental backup
RMAN> backup incremental level 1 cumulative database;
A full backup cannot be used for a cumulative level 1 backup.
A cumulative level 1 backup must be done on top of an incremental level 0 backup.
Backup and Restore Scenario from existing Level 0
backup
-------------------------------------------------------
steps to recover the database:-
1. Take Level 0 backup.
2. Lost SYSTEM datafile.
3. Start the instance without mounting the database.
4. Restore Controlfile.
5. Mount the database.
6. Restore database.
7. Recovery database.
8. Open the database and reset logs.
1. Take Level 0 backup:
run
{
allocate channel channel1 type disk;
allocate channel channel2 type disk;
allocate channel channel3 type disk;
backup incremental level=0 database;
backup archivelog all;
backup current controlfile;
}
2. Lost SYSTEM datafile.
Note:
----
Need controlfile, backup and parameter file for recoverying database.
3. Start the instance without mounting the database.
$sqlplus "/as sysdba"
SQL> startup nomount;
SQL> exit
4. Restore Controlfile.
$rman target /
RMAN> restore controlfile;
5. Mount the database.
RMAN> alter database mount;
6. Restore and recover & Point-In-Time Recovery of all data back to a particular date/time in the past.
run
{
set until time "to_date('2013-03-20:14:40:00','YYYY-MM-DD:hh24:mi:ss')";
restore database;
recovery database;
}
7.Open the database and reset logs.
SQL> alter database open resetlogs;
This will update all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.
NOTE:
----
As soon as you have done a resetlogs run a full backup, this is important as should you suffer a second failure you will not be able to perform a second recovery because after resetting the logs the SCN numbers will no longer match any older backup files.
http://ss64.com/ora/rman_pitr_example.html
-------------------------------------------------------
steps to recover the database:-
1. Take Level 0 backup.
2. Lost SYSTEM datafile.
3. Start the instance without mounting the database.
4. Restore Controlfile.
5. Mount the database.
6. Restore database.
7. Recovery database.
8. Open the database and reset logs.
1. Take Level 0 backup:
run
{
allocate channel channel1 type disk;
allocate channel channel2 type disk;
allocate channel channel3 type disk;
backup incremental level=0 database;
backup archivelog all;
backup current controlfile;
}
2. Lost SYSTEM datafile.
Note:
----
Need controlfile, backup and parameter file for recoverying database.
3. Start the instance without mounting the database.
$sqlplus "/as sysdba"
SQL> startup nomount;
SQL> exit
4. Restore Controlfile.
$rman target /
RMAN> restore controlfile;
5. Mount the database.
RMAN> alter database mount;
6. Restore and recover & Point-In-Time Recovery of all data back to a particular date/time in the past.
run
{
set until time "to_date('2013-03-20:14:40:00','YYYY-MM-DD:hh24:mi:ss')";
restore database;
recovery database;
}
7.Open the database and reset logs.
SQL> alter database open resetlogs;
This will update all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.
NOTE:
----
As soon as you have done a resetlogs run a full backup, this is important as should you suffer a second failure you will not be able to perform a second recovery because after resetting the logs the SCN numbers will no longer match any older backup files.
http://ss64.com/ora/rman_pitr_example.html
RMAN Troubleshooting
RMAN Troubleshooting Queries at Database Level
SET PAGESIZE 20000
SET LINESIZE 1000
SET TRIMSPOOL ON
SET PAUSE OFF
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET ECHO ON
SET NUMFORMAT 999999999999999
COL TABLESPACE_NAME FORMAT A50
COL FILE_NAME FORMAT A50
COL NAME FORMAT A50
COL MEMBER FORMAT A50
col DFILE_CHKP_CHANGE format a40
col DFILE_HED_CHKP_CHANGE format a40
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ARCHIVE LOG LIST;
SELECT * FROM gv$instance;
SELECT * FROM v$recover_file;
SELECT * FROM v$recovery_log;
select distinct status from v$backup;
select distinct(status) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile_header;
select * from v$backup;
SELECT dbid,
name,
TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode,
log_mode,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
TO_CHAR(controlfile_change#, '999999999999999') as controlfile_change#,
TO_CHAR(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time
FROM v$database;
SELECT f.name, b.status, b.change#, b.time
FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';
SELECT name,
file#,
status,
enabled,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(offline_change#, '999999999999999') as offline_change#,
TO_CHAR(online_change#, '999999999999999') as online_change#,
TO_CHAR(online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile
where status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);
SELECT name,
file#,
status,
error,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile_header
WHERE status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);
SELECT status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;
SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
to_char(dd.checkpoint_change#,'999999999999999') dfile_chkp_change,
to_char(dh.checkpoint_change#,'999999999999999') dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#;
SELECT name file_name,
recover,
fuzzy,
checkpoint_change#
FROM v$datafile_header
ORDER BY checkpoint_change#;
SELECT hxfil file_num,
hxfnm file_name,
fhtyp type,
hxerr validity,
fhscn scn,
fhtnm tablespace_name,
fhsta status ,
fhrba_seq sequence
FROM x$kcvfh;
SELECT group#,
thread#,
sequence#,
members,
archived,
status,
TO_CHAR(first_change#, '999999999999999') as first_change#
FROM v$log;
SELECT group#,member
FROM v$logfile;
SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.archived,
a.deleted,
TO_DATE(a.completion_time, 'DD-MON-YYYY HH24:MI:SS') as completed
FROM v$archived_log a, v$log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;
SET PAGESIZE 20000
SET LINESIZE 1000
SET TRIMSPOOL ON
SET PAUSE OFF
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET ECHO ON
SET NUMFORMAT 999999999999999
COL TABLESPACE_NAME FORMAT A50
COL FILE_NAME FORMAT A50
COL NAME FORMAT A50
COL MEMBER FORMAT A50
col DFILE_CHKP_CHANGE format a40
col DFILE_HED_CHKP_CHANGE format a40
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ARCHIVE LOG LIST;
SELECT * FROM gv$instance;
SELECT * FROM v$recover_file;
SELECT * FROM v$recovery_log;
select distinct status from v$backup;
select distinct(status) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile_header;
select * from v$backup;
SELECT dbid,
name,
TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode,
log_mode,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
TO_CHAR(controlfile_change#, '999999999999999') as controlfile_change#,
TO_CHAR(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time
FROM v$database;
SELECT f.name, b.status, b.change#, b.time
FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';
SELECT name,
file#,
status,
enabled,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(offline_change#, '999999999999999') as offline_change#,
TO_CHAR(online_change#, '999999999999999') as online_change#,
TO_CHAR(online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile
where status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);
SELECT name,
file#,
status,
error,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile_header
WHERE status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);
SELECT status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;
SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
to_char(dd.checkpoint_change#,'999999999999999') dfile_chkp_change,
to_char(dh.checkpoint_change#,'999999999999999') dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#;
SELECT name file_name,
recover,
fuzzy,
checkpoint_change#
FROM v$datafile_header
ORDER BY checkpoint_change#;
SELECT hxfil file_num,
hxfnm file_name,
fhtyp type,
hxerr validity,
fhscn scn,
fhtnm tablespace_name,
fhsta status ,
fhrba_seq sequence
FROM x$kcvfh;
SELECT group#,
thread#,
sequence#,
members,
archived,
status,
TO_CHAR(first_change#, '999999999999999') as first_change#
FROM v$log;
SELECT group#,member
FROM v$logfile;
SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.archived,
a.deleted,
TO_DATE(a.completion_time, 'DD-MON-YYYY HH24:MI:SS') as completed
FROM v$archived_log a, v$log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;
sqlplus "/as sysdba"
archive log list;
!df -h or !df -kgt
show parameter db_recovery;
NAME TYPE VALUE
---- ---- -----
db_recovery_file_dest string +FLASHDG
db_recovery_file_dest_size big integer 150G
if +FRA,
select * from V$FLASH_RECOVERY_AREA_USAGE;
(see what kind of files are available in the Flash Recovery Area)
set pages 9999 lines 300
col name format a40
select name,
to_char(space_limit, '999,999,999,999') as space_limit,
to_char(space_limit - space_used + space_reclaimable,
'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---- ----------- ---------- ----------------- ---------------
if SPACE_USED is full, then increase the db_recovery_file_dest_size
rman target /
crosscheck archivelog all;
delete expired archivelog all;
crosscheck backup;
SQL>alter system set db_recovery_file_dest_size=200G scope=both;
System altered.
rman target /
show all;
LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1'; ------List all archivelog backups for the past 24 hours
list backup of archivelog all completed before 'sysdate -1';
list backup of archivelog all;
list backup of archivelog all tag 'TAGXXX';
list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';
crosscheck archivelog all;
list expired archivelog ALL;
delete expired archivelog all;
delete obsolete device type disk;
crosscheck backup;
delete expired backup device type disk;
delete expired backupset device type disk;
ls -ltr archivebackup.cmd
nohup rman target / cmdfile=archivebackup.cmd log=archivebackup_dbname_DDMONYY.log &
nohup: appending output to `nohup.out'
tail -f archivebackup_dbname_DDMONYY.log
+FRA/+RECO01 filled with archivelogs
====================================
Backup archivelogs to disk
--------------------------
run
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
allocate channel ch3 type DISK;
allocate channel ch4 type DISK;
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;
}
Backup archivelogs to disk (specified location,if there is no space to take bakup at archive default location)
--------------------------------------------------------------------------------------------------------------
RUN
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
allocate channel ch3 type DISK;
allocate channel ch4 type DISK;
BACKUP ARCHIVELOG ALL format '/location/arch_%d_%p_%s.rman' FILESPERSET 10 DELETE INPUT;
}
Backup archivelogs to tape (PROD Environment)
--------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;
}
+FRA filled with Bacupsets (PROD Environment)
==========================
Move older backupset to tape (to keep more recent backups on disk and older backups only on tape)
-------------------------------------------------------------------------------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
BACKUP BACKUPSET COMPLETED BEFORE 'SYSDATE-7' DELETE INPUT;
}
if required,
BACKUP BACKUPSET ALL FILESPERSET 10 DELETE INPUT;
Delete BACKUPSET backed up to tape/disk (PROD Environment)
-----------------------------------------
DELETE NOPROMPT BACKUPSET COMPLETED BEFORE 'SYSDATE-14' DEVICE TYPE DISK;
+FRA filled with Bacupsets (NON-PROD Environment)
==========================
Move older backupset to tape (to keep more recent backups on disk and older backups only on tape)
-------------------------------------------------------------------------------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
DELETE BACKUPSET COMPLETED BEFORE 'SYSDATE-4';
}
Delete archivelogs backed up to tape/disk (PROD Environment)
-----------------------------------------
DELETE archivelog UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE DISK;
DELETE archivelog UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE;
Delete archivelogs backed up to tape/disk (NON-PROD Environment)
-----------------------------------------
DELETE archivelog all BACKED UP 1 TIMES TO DEVICE TYPE DISK;
list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';
delete archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';
backup archivelog [all] [until time 'sysdate']| [sequence between 100 to 110 thread 1] as filesperset 5 delete
input;
backup archivelog from sequence 100 until sequence 110 thread 1 delete input;
backup archivelog from logseq 100 until logseq 110 thread 1 delete input;
report obsolete;
DELETE OBSOLETE REDUNDANCY = 3;
DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS;
http://anuj-singh.blogspot.in/2011_07_16_archive.html
oracledbazone.wordpress.com/2012/04/18/useful-rman-commands/
Source : Internet
RMAN Backup Details
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/
ARCHIVE LOG BACKUP TO TAPE
--------------------------
ps -ef|grep pmon
ORACLE_SID=`ps -ef | grep asm_smon | grep -v 'grep' | grep -v 'sed' | awk '{printf $8}' | awk 'BEGIN{FS="_"} {printf $3}'`
export ORACLE_SID
sqlplus "/as sysdba"
set pages 9999 lines300
col OPEN_MODE for a10
col HOST_NAME for a10
select name,INSTANCE_NAME,HOST_NAME,OPEN_MODE,DATABASE_STATUS,DATABASE_ROLE, PROTECTION_LEVEL,CONTROLFILE_TYPE,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,Gv$instance;
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used &nb; allocation_unit_size
&n"sans-serif" verdana="verdana"> , type  1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
, (total_mb - free_mb) &)*100, 2) pct_used<;pct_used
FROM
v\$asm_diskgroup
WHERE
state = 'MOUNTED'
ORDER BY
name
/
nohup rman f;">Disk Group Sector Block Allocation
Name &nb; type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v\$asm_diskgroup
WHERE
state = 'MOUNTED'
ORDER BY
name
/
Disk Group Sector Block Allocation
Name Size Size Unit Size State Type Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------ -f archivebackup_dbname_DDMONYY.log
archivebackup.cmd
-----------------
run
{
delete archivelog until time 'sysdate-3' backed up 1 times to sbt_tape;
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
backup archivelog all filesperset 10 delete input;
}
nohup: appending output to `nohup.out'
tail -f archivebackup_dbname_DDMONYY.log
------------------------------------------------------------------------------------------------------------------ls -lrth
rm arch (remove oldest archive if not required)
--------------------------
ps -ef|grep pmon
ORACLE_SID=`ps -ef | grep asm_smon | grep -v 'grep' | grep -v 'sed' | awk '{printf $8}' | awk 'BEGIN{FS="_"} {printf $3}'`
export ORACLE_SID
sqlplus "/as sysdba"
set pages 9999 lines300
col OPEN_MODE for a10
col HOST_NAME for a10
select name,INSTANCE_NAME,HOST_NAME,OPEN_MODE,DATABASE_STATUS,DATABASE_ROLE, PROTECTION_LEVEL,CONTROLFILE_TYPE,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,Gv$instance;
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used &nb; allocation_unit_size
&n"sans-serif" verdana="verdana"> , type  1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
, (total_mb - free_mb) &)*100, 2) pct_used<;pct_used
FROM
v\$asm_diskgroup
WHERE
state = 'MOUNTED'
ORDER BY
name
/
nohup rman f;">Disk Group Sector Block Allocation
Name &nb; type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v\$asm_diskgroup
WHERE
state = 'MOUNTED'
ORDER BY
name
/
Disk Group Sector Block Allocation
Name Size Size Unit Size State Type Total Size (MB) Used Size (MB) Pct. Used
-------------------- ------- ------- ------ -f archivebackup_dbname_DDMONYY.log
archivebackup.cmd
-----------------
run
{
delete archivelog until time 'sysdate-3' backed up 1 times to sbt_tape;
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
backup archivelog all filesperset 10 delete input;
}
nohup: appending output to `nohup.out'
tail -f archivebackup_dbname_DDMONYY.log
------------------------------------------------------------------------------------------------------------------ls -lrth
rm arch (remove oldest archive if not required)
PACKAGE BACKUP
--------------
set heading off
set echo off
set flush off
set pagesize 50000
set linesize 32767
set long 99999999
spool PACKAGE_NAME.pks
select dbms_metadata.get_ddl('PACKAGE','<PACKAGE_NAME>','<OWNER_NAME>') from dual ;
spool off;
OR
set linesize 500
set pagesize 100000
set head off
set verify off
spool PACKAGE_NAME.pks
select text from dba_source where owner='<OWNER_NAME>'and name='<PACKAGE_NAME>';
spool off
OR
select text from dba_source where owner='<OWNER_NAME>' and name='PACKAGE_NAME' and type='PACKAGE' order by line;
PACKAGE BODY BACKUP
-------------------
select dbms_metadata.get_ddl('PACKAGE_BODY','<PACKAGE_BODY_NAME>','<OWNER_NAME>') from dual ;
OR
set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool PACKAGE_BODY_NAME.pkb
select text from dba_source where owner='<OWNER_NAME>'and name='<PACKAGE_BODY_NAME>';
spool off
OR
select text from dba_source where owner='<OWNER_NAME>' and name='PACKAGE_BODY_NAME' and type='PACKAGE BODY' order by line;
DROP PACKAGE
------------
set pagesize 0
set linesize 800
col object_name for a30
col object_type for a25
col owner for a25
spool package_drop.sql
select owner,object_name,object_type from dba_objects where object_name='<object_name>';
drop package <owner>.<package_name>;
spool Off;
Oracle Home and Inventory Backup
-----------------------------------------
tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz
Note:
tar -cvf <destination_location> <source_location>
ORACLE_HOME & ORACLE_CRS_HOME backup’s (for all nodes in RAC)
cd /u01/before_patch_backup
tar -cvf oracle_base_jun30.tar $ORACLE_BASE
tar -cvf oracle_home_jun30.tar $ORACLE_HOME
tar -cvf oracle_crs_home_jun30.tar $ORACLE_CRS_HOME
Controlfile Backup
---------------------
alter database backup controlfile to trace;
show parameter user_dump_dest
(go to udump dest and make the note of controlfile trace)
-----------------------------------------
tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz
Note:
tar -cvf <destination_location> <source_location>
ORACLE_HOME & ORACLE_CRS_HOME backup’s (for all nodes in RAC)
cd /u01/before_patch_backup
tar -cvf oracle_base_jun30.tar $ORACLE_BASE
tar -cvf oracle_home_jun30.tar $ORACLE_HOME
tar -cvf oracle_crs_home_jun30.tar $ORACLE_CRS_HOME
Controlfile Backup
---------------------
alter database backup controlfile to trace;
show parameter user_dump_dest
(go to udump dest and make the note of controlfile trace)
No comments:
Post a Comment