RMAN

rman command reference

Posted on by debasis maity

##Connecting RMAN##############

rman TARGET SYS/target_pwd@target_str # connects in NOCATALOG mode
rman TARGET / CATALOG rman/rman@rcat
rman TARGET / CATALOG rman/rman@rcat AUXILIARY sys/aux_pwd@aux_str

##Create user and catalog in RMAN database ########

CREATE USER rman_dba IDENTIFIED BY rman_dba TEMPORARY TABLESPACE temp DEFAULT TABLESPACE rman_dba QUOTA UNLIMITED ON rman_dba;
GRANT RECOVERY_CATALOG_OWNER TO rman_dba;
CREATE CATALOG;

## Register Database######

Rman target / catalog rman_dba/rman_dba@<catalog>

register database;
select * from rc_database;

### Catalog copy in RMAN Catalog###

CATALOG BACKUPPIECE ‘/disk2/09dtq55d_1_2’, ‘/disk2/0bdtqdou_1_1’;
CATALOG DATAFILECOPY ‘/tmp/users01.dbf’;
CATALOG RECOVERY AREA;
CHANGE CONTROLFILECOPY ‘/tmp/control01.ctl’ UNCATALOG;
CHANGE DATAFILECOPY ‘/tmp/system01.dbf’ UNCATALOG;
CHANGE DATAFILECOPY ‘/tmp/control01.ctl’ UNAVAILABLE;
CHANGE COPY OF ARCHIVELOG SEQUENCE BETWEEN 1000 AND 1012 UNAVAILABLE;
CHANGE BACKUPSET 12 UNAVAILABLE;
CHANGE BACKUP OF SPFILE TAG “TAG20020208T154556” UNAVAILABLE;
CHANGE DATAFILECOPY ‘/tmp/system01.dbf’ AVAILABLE;
CHANGE BACKUPSET 12 AVAILABLE;
CHANGE BACKUP OF SPFILE TAG “TAG20020208T154556” AVAILABLE;
CATALOG START WITH ‘/backup/MYSID/arch’;

### Configure RMAN ######

CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
CONFIGURE RETENTION POLICY CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

CONFIGURE DEFAULT DEVICE TYPE TO DISK/SBT;
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘d:\oracle\orclbackup\ora_df%t_s%s_s%p’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘d:\oracle\orclbackup\ora_cf%F’;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT /tmp/%U;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘+dgroup1/%F’;
CONFIGURE DEVICE TYPE <DISK | SBT> BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/disk1/%U’, ‘/disk2/%U’;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE sbt TO 2;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE sbt TO 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;

###RMAN backup check view######

SELECT SID,
SERIAL#,
CONTEXT,
SOFAR,
TOTALWORK,
ROUND(SOFAR / TOTALWORK * 100, 2) “% COMPLETE”
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’ AND OPNAME NOT LIKE ‘%aggregate%’ AND
TOTALWORK != 0 AND SOFAR <> TOTALWORK ;

COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = ‘YES’
OR (RECOVER IS NULL AND ERROR IS NOT NULL);

SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME,
BLOCKS, DATAFILE_BLOCKS
FROM V$BACKUP_DATAFILE
WHERE INCREMENTAL_LEVEL > 0
AND BLOCKS / DATAFILE_BLOCKS > .2
ORDER BY COMPLETION_TIME;

SELECT * FROM V$RECOVERY_FILE_DEST;
SELECT * FROM V$RECOVERY_AREA_USAGE;
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

### List and Report of backup ####

LIST BACKUP OF DATABASE;
LIST COPY OF DATAFILE 1, 2;
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 10;
LIST BACKUPSET OF DATAFILE 1;
LIST BACKUP;
LIST cOPY;
LIST ARCHIVELOG;
LIST RESTORE POINT;
LIST EXPIRED;
LIST BACKUP SUMMARY;
LIST FAILURE;
LIST BACKUPSET TAG ‘weekly_full_db_backup’;
LIST BACKUPSET 213;
LIST COPY OF DATAFILE 2 COMPLETED BETWEEN ’10-DEC-2002′ AND ’17-DEC-2002′
LIST BACKUP OF DATAFILE 1;

REPORT OBSOLETE;
REPORT SCHEMA;
REPORT NEED BACKUP
REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE DEVICE TYPE sbt;
REPORT NEED BACKUP DEVICE TYPE DISK;
REPORT NEED BACKUP TABLESPACE TBS_3 DEVICE TYPE sbt;
REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
REPORT OBSOLETE REDUNDANCY 1;

## Crosscheck backup #####

CROSSCHECK BACKUP DEVICE TYPE DISK;
CROSSCHECK BACKUP DEVICE TYPE sbt;
CROSSCHECK BACKUP; # checks backup sets, proxy copies, and image copies
CROSSCHECK COPY OF DATABASE;
CROSSCHECK BACKUPSET 1338, 1339, 1340;
CROSSCHECK BACKUPPIECE TAG ‘nightly_backup’;
CROSSCHECK BACKUP OF ARCHIVELOG ALL SPFILE;
CROSSCHECK BACKUP OF DATAFILE “?/oradata/trgt/system01.dbf” COMPLETED AFTER ‘SYSDATE-14’;
CROSSCHECK CONTROLFILECOPY ‘/tmp/control01.ctl’;
CROSSCHECK DATAFILECOPY 113, 114, 115;
CROSSCHECK PROXY 789;

### Delete backup ########

DELETE BACKUPPIECE 101;
DELETE CONTROLFILECOPY ‘/tmp/control01.ctl’;
DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE 300;
DELETE BACKUP TAG ‘before_upgrade’;
DELETE ARCHIVELOG ALL BACKED UP 3 TIMES TO sbt;
DELETE EXPIRED BACKUP;
DELETE OBSOLETE;

###Simple unix script #############

#!/bin/tcsh
# name: runbackup.sh
# usage: use the tag name and number of copies as arguments
set media_family = $argv[1]
set format = $argv[2]
set restore_point = $argv[3]
rman @’/disk1/scripts/whole_db.cmd’ USING $media_family $format $restore_point

% runbackup.sh archival_backup bck0906 FY06Q3

##Backup Database command #####

BACKUP DEVICE TYPE sbt BACKUPSET COMPLETED BEFORE ‘SYSDATE-7’ DELETE INPUT;
BACKUP DEVICE TYPE DISK COPIES 3 DATAFILE 7 FORMAT ‘/disk1/%U’,’?/oradata/%U’,’?/%U’;
BACKUP DEVICE TYPE sbt BACKUPSET COMPLETED BEFORE ‘SYSDATE-7’ DELETE INPUT;
BACKUP AS BACKUPSET DATABASE;
BACKUP AS BACKUPSET DEVICE TYPE DISK DATABASE;
BACKUP AS BACKUPSET DEVICE TYPE SBT DATABASE;
BACKUP AS COPY DEVICE TYPE DISK DATABASE;
BACKUP AS BACKUPSET COPIES 1 DATAFILE 7 TAG mondaybkp;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
BACKUP DEVICE TYPE sbt DATAFILE 1,2,3,4 DATAFILECOPY ‘/tmp/system01.dbf’;
BACKUP AS COPY DB_FILE_NAME_CONVERT (‘/maindisk/oradata/users’,’/backups/users_ts’) TABLESPACE users;
BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP AS BACKUPSET DATABASE FORMAT ‘/disk1/%U’,’/disk2/%U’;
BACKUP AS BACKUPSET DEVICE TYPE DISK COPIES 3 INCREMENTAL LEVEL 0 DATABASE;
BACKUP DURATION 4:00 TABLESPACE users;
BACKUP DURATION 4:00 PARTIAL TABLESPACE users FILESPERSET 1;
BACKUP DURATION 4:00 PARTIAL MINIMIZE TIME DATABASE FILESPERSET 1;
BACKUP DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE FILESPERSET 1;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
backup as compressed backupset incremental level 0 database plus archivelog;
BACKUP DEVICE TYPE sbt ARCHIVELOG ALL DELETE ALL INPUT;
BACKUP INCREMENTAL LEVEL 1 TABLESPACE SYSTEM, tools;
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE TABLESPACE users;
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
BACKUP ARCHIVELOG FROM TIME ‘SYSDATE-1’;
BACKUP ARCHIVELOG FROM TIME ‘SYSDATE-5’ UNTIL TIME ‘SYSDATE-1’

RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/disk1/%d_backups/%U’;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT ‘/disk2/%d_backups/%U’;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT ‘/disk3/%d_backups/%U’;
BACKUP AS COPY DATABASE;
}

RUN
{
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT ‘/disk1/%d_backups/%U’;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT ‘/disk2/%d_backups/%U’;
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT ‘/disk3/%d_backups/%U’;
BACKUP AS COPY DATABASE;
}

## Incremental updated backup #######

recover copy of database with tag ‘tcstest3’;
backup incremental level 1 tag ‘tcstest3’ for recover of copy with tag ‘tcstest3’ database ;

## Block change tracking ########

ALTER SYSTEM SET
DB_CREATE_FILE_DEST = ‘/disk1/bct/’
SCOPE=BOTH SID=’*’;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE ‘/mydir/rman_change_track.f’ REUSE;

COL STATUS FORMAT A8
COL FILENAME FORMAT A60
SELECT STATUS, FILENAME
FROM V$BLOCK_CHANGE_TRACKING;

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘new_location’;

### Restore Preview #########

RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;
RESTORE DATABASE PREVIEW;
RESTORE ARCHIVELOG FROM TIME ‘SYSDATE-7’ PREVIEW;
RESTORE DATABASE PREVIEW SUMMARY;
RESTORE ARCHIVELOG ALL PREVIEW RECALL;
REPAIR FAILURE PREVIEW;
VALIDATE DATABASE;
RUN{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
VALIDATE DATAFILE 1 SECTION SIZE 1200M;
}
VALIDATE DATAFILE 4 BLOCK 10 TO 13;
VALIDATE BACKUPSET 3;
RECOVER CORRUPTION LIST;

### Recover database flashback technology ####

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

SELECT CURRENT_SCN FROM V$DATABASE;
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE=’YES’;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

FLASHBACK DATABASE TO SCN 46963;
FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;
FLASHBACK DATABASE TO TIME “TO_DATE(’09/20/05′,’MM/DD/YY’)”;
ALTER DATABASE OPEN READ ONLY;

SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

SET UNTIL TIME ‘Nov 15 2004 09:00:00’;
SET UNTIL SEQUENCE 9923;
SET UNTIL RESTORE POINT before_update;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

### Recover database to copy location and again recover back to original location #######

RUN{
SWITCH DATABASE TO COPY;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}

run{
SET NEWNAME FOR DATAFILE 1 TO ‘/oracle/oradata/tcstest/system01.dbf’;
SET NEWNAME FOR DATAFILE 2 TO ‘/oracle/oradata/tcstest/undotbs01.dbf’;
SET NEWNAME FOR DATAFILE 3 TO ‘/oracle/oradata/tcstest/sysaux01.dbf’;
SET NEWNAME FOR DATAFILE 4 TO ‘/oracle/oradata/tcstest/users01.dbf’;
SET NEWNAME FOR DATAFILE 5 TO ‘/oracle/oradata/tcstest/example01.dbf’;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

## Recover individual tablespace and datafile###

sql “alter tablespace working_data offline”;
sql “alter database datafile 13 offline”;
restore tablespace working_data;
restore datafile 13;
recover tablespace working_data;
recover datafile 13;
sql “alter tablespace working_data online”;
sql “alter database datafile 13 online”;

SWITCH DATAFILE 4 TO COPY;
RECOVER DATAFILE 4;

backup as copy datafile 4;

run{
SET NEWNAME FOR DATAFILE 4 TO ‘/oracle/oradata/tcstest/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
}

SET NEWNAME FOR DATAFILE ‘/disk1/oradata/prod/users01.dbf’
TO ‘/disk2/users01.dbf’;
RESTORE TABLESPACE users;
SWITCH DATAFILE ALL; # update control file with new filenames
RECOVER TABLESPACE users;

## Recover individual block #######

RECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199 FROM TAG mondayam;
RECOVER CORRUPTION LIST;

## Recover No-archivelog mode with catalog########

rman target sys/password catalog rcat_user/rcat_password@catalogdb
startup force nomount;
restore spfile from autobackup;
shutdown immediate;
startup nomount;
restore controlfile from autobackup;
alter database mount;
configure default device type to sbt;
configure channel 1 device type sbt parms = “env=(nb_ora_serv=mgtserv, nb_ora_client=cervantes)”;
restore database;
recover database noredo;
alter database open resetlogs;

## Recover No-archivelog mode with no catalog####

rman target sys/password
startup nomount
set dbid=2540040039;
restore controlfile from autobackup;
sql ‘alter database mount’;
restore database;
recover database noredo;
sql “alter database open resetlogs”;

### Recover complete database loss–No catalog ###

rman target /
set dbid=204062491;
startup force nomount;
run {
allocate channel tape_1 type sbt
parms=’env=(nb_ora_serv=rmsrv, nb_ora_client=cervantes)’;
restore spfile from autobackup;

}

shutdown immediate;

startup nomount;

alter system set control_files= ‘/u02/oradata/prod/control01.dbf’,
‘/u03/oradata/prod/control02.dbf’ scope=spfile;

alter system set db_file_name_convert= (‘/u04’ , ‘/u02’ ,
‘/u05’ , ‘/u02’ ,
‘u06’ , ‘ u03’ ,
‘u07’ , ‘u03’) scope=spfile;

alter system set log_file_name_convert= (‘/u04’ , ‘/u02’ ,
‘/u05’ , ‘/u02’ ,
‘u06’ , ‘ u03’ ,
‘u07’ , ‘u03’) scope=spfile;

alter system set log_archive_dest_1=
‘location=/u02/oradata/prod/arch’ scope=spfile;

alter system set db_cache_size=300m scope=spfile;

alter system set shared_pool_size=200m scope=spfile;

shutdown immediate;

startup nomount;

run {
allocate channel tape_1 type sbt
parms=’env=(nb_ora_serv=rmsrv, nb_ora_client=Cervantes)’;
restore controlfile from autobackup;

}

alter database mount;

configure default device type to sbt;

configure device type sbt parallelism 2;

configure auxiliary channel 1 device type sbt parms
= “env=(nb_ora_serv=mgtserv, nb_ora_client=cervantes)”;

configure auxiliary channel 2 device type sbt parms
= “env=(nb_ora_serv=mgtserv, nb_ora_cient=cervantes)”;

list backup of archivelog from time = ‘sysdate-7’;

restore database;

recover database until sequence=<number>;

alter database open resetlogs;

## Recovery of database if inactive redo log is deleted####

sql>startup mount;
sql>alter database clear logfile group 2;
sql>alter database open;

##Recovery after loss of controlfile #######

rman target /
startup nomount;
restore controlfile from autobackup;
alter database mount;
recover database;
alter database open (resetlogs);

## Tablespace point in time recovery ########

Database should be in opened state

recover tablespace “APP_DATA” until time
“to_date(‘2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS’)”
auxiliary destination ’/opt/oracle/temp’;

### Recovery after loss of current redolog file ###

RUN
{
# SET UNTIL TIME ‘Nov 15 2002 09:00:00’;
# SET UNTIL SCN 1000; # alternatively, specify SCN
SET UNTIL SEQUENCE 1; # alternatively, specify log sequence number
RESTORE DATABASE;
RECOVER DATABASE;
}

###Backup of CDB and PDB in 12c#####

—CDB backup–

export ORACLE_SID=ORCL1

[oracle@rac1 ~]$ rman target /

RMAN> backup database plus archivelog;

—CDB root backup—

RMAN> backup pluggable database “CDB$ROOT”;

–Backup pluggable database—

backup pluggable database oem;

backup pluggable database oem plus archivelog;

Database backup and restore after manual catalog RMAN backup

Posted on by debasis maity

Database backup and restore after manual catalog RMAN backup

1.Please take backup of primary database ORCL

rman target / nocatalog log=/tmp/rman_bkp.log << EOF1
run
{
backup as compressed backupset database format ‘/opt/app/oratest1/bkp/ORCL_%U’;
backup as compressed backupset archivelog all format ‘/opt/app/oratest1/bkp/ORCL_ARCH_%U’;
}
exit;
EOF1

Please copy it in backup location to /opt/app/oratest1/bkp

2.Please start nomount RCATT database.

3.Please create controlfile from backup.The DB_CREATE_FILE_DEST should be set to diskgroup. Control file location should be in Diskgroup location
as control.ctl

CREATE CONTROLFILE SET DATABASE “RCATT” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘+DATA1/rcatt/onlinelog/group_1.261.825435443’ SIZE 50M BLOCKSIZE 512,
GROUP 2 ‘+DATA1/rcatt/onlinelog/group_2.262.825435443’ SIZE 50M BLOCKSIZE 512,
GROUP 3 ‘+DATA1/rcatt/onlinelog/group_3.263.825435445’ SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘+DATA1/rcatt/datafile/system.256.825435379’,
‘+DATA1/rcatt/datafile/sysaux.257.825435379’,
‘+DATA1/rcatt/datafile/undotbs1.258.825435381’,
‘+DATA1/rcatt/datafile/users.259.825696133’,
‘+DATA1/rcatt/datafile/orcl.266.825438401’
CHARACTER SET WE8MSWIN1252
;

4.rman target /
RMAN> catalog start with ‘/opt/app/oratest1/bkp’;
RMAN>restore database;
RMAN>list backup; (Please identify highest SCN of archivelog)
RMAN>recover database until sequence 26;(Please specify highest SCN)

Please ignore the following error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/11/2013 17:02:13
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 16 and starting SCN of 1545450

5.Please open database with resetlogs option.

SQL>alter database open resetlogs

RMAN incremental Backup cumulative and differential

Posted on by debasis maity

RMAN Incremental backup

This backups only datafile blocks changed since specified previous backup.

1.This strategy could be followed to make incrementally updated backup .These incrementally updated backup will be used to make updated image copy will all roll forward image.

2.Reduce amount of time to take backup.

3.Save network bandwidth

  1. To be able to recover changes to objects created with the NOLOGGING option. For example, direct load inserts do not create redo log entries and their changes cannot be reproduced with media recovery. They do, however, change data blocks and so are captured by incremental backups.
  2. To reduce backup sizes for NOARCHIVELOG databases. Instead of making a whole database backup every time, you can make incremental backups.

As with full backups, if you are in ARCHIVELOG mode, you can make incremental backups if the database is open; if the database is in NOARCHIVELOG mode, then you can only make incremental backups after a consistent shutdown.

Level 0 and Level 1 Incremental Backups

Level 0 incremental backup will register all copies of block which has data.This is base for incremental backup .

Difference between full and level 0 backup is:

Level 0 backup will involve in incremental backup strategy.

Why cumulative backup are useful than differential

Cumulative backups are preferable to differential backups when recovery time is more important than disk space, because during recovery each differential backup must be applied in succession. Use cumulative incremental backups instead of differential, if enough disk space is available to store cumulative incremental backups.

 

Differential Incremental Backups

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

 

  • Sunday

An incremental level 0 backup backs up all blocks that have ever been in use in this database.

  • Monday – Saturday

On each day from Monday through Saturday, a differential incremental level 1 backup backs up all blocks that have changed since the most recent incremental backup at level 1 or 0. So, the Monday backup copies blocks changed since Sunday level 0 backup, the Tuesday backup copies blocks changed since the Monday level 1 backup, and so forth.

  • The cycle is repeated for the next week.

Cumulative Incremental Backups

BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; # blocks changed since level 0

In the example shown in the following occurs:

  • Sunday

An incremental level 0 backup backs up all blocks that have ever been in use in this database.

  • Monday – Saturday

A cumulative incremental level 1 backup copies all blocks changed since the most recent level 0 backup. Because the most recent level 0 backup was created on Sunday, the level 1 backup on each day Monday through Saturday backs up all blocks changed since the Sunday backup.

  • The cycle is repeated for the next week.

Basic incremetal Backup strategy

you can implement a three-level backup scheme so that a full or level 0 backup is taken monthly, a cumulative level 1 is taken weekly, and a differential level 1 is taken daily. In this scheme, you never have to apply more than a day’s worth of redo for complete recovery.

Making Incremental Backups: BACKUP INCREMENTAL

After starting RMAN, run the BACKUP INCREMENTAL command at the RMAN prompt. This example makes a level 0 incremental backup of the database:

BACKUP INCREMENTAL LEVEL 0 DATABASE;

This example makes a differential level 1 backup of the SYSTEM tablespace and datafile tools01.dbf. It will only back up those data blocks changed since the most recent level 1 or level 0 backup:

BACKUP INCREMENTAL LEVEL 1  TABLESPACE SYSTEM  DATAFILE ‘ora_home/oradata/trgt/tools01.dbf’;

This example makes a cumulative level 1 backup of the tablespace users, backing up all blocks changed since the most recent level 0 backup.

BACKUP INCREMENTAL LEVEL = 1 CUMULATIVE  TABLESPACE users;

 

Enabling and Disabling Change Tracking to improve performance of incremental backup

To store the change tracking file in the database area, set DB_CREATE_FILE_DEST in the target database. Then issue the following SQL statement to enable change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

You can also create the change tracking file in a location you choose yourself, using the following SQL statement:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING   USING FILE ‘/mydir/rman_change_track.f’ REUSE;

The REUSE option tells Oracle to overwrite any existing file with the specified name.

To disable change tracking, use this SQL statement:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

If the change tracking file was stored in the database area, then it is deleted when you disable change tracking.

Checking Whether Change Tracking is Enabled

From SQL*Plus, you can query V$BLOCK_CHANGE_TRACKING.STATUS to determine whether change tracking is enabled, and if it is, query V$BLOCK_CHANGE_TRACKING.FILENAME to display the filename.

 

Database corrupt block recovery using RMAN

Posted on by debasis maity

1 Introduction

The next document will describe the steps to recover corrupt block using RMAN

 

2. Recover block if datafile header is corrupted

 

2.1. Please check the physical corruption

 

[oracle@cdv1pradmdbv01 datafile]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 12:55:06 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (DBID=2013020542)

RMAN> backup validate check logical datafile 5;

Starting backup at 26-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
RMAN-06169: could not read file header for datafile 5 error reason 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/26/2017 12:55:10
RMAN-06056: could not access datafile 5

 

2.2. Restore and recover the particular tablespace (For example test1)

 

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> restore tablespace test1;
5> recover tablespace test1;
6> alter tablespace test1 online;
7> }

released channel: ORA_DISK_1
allocated channel: dev_0
channel dev_0: SID=28 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 26-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 26-JUN-17

Starting recover at 26-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-JUN-17

Statement processed
released channel: dev_0

 

 

2.3. Now please validate after restore and recover (For example test1)

 

RMAN> backup validate check logical datafile 5;

Starting backup at 26-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_do2hg0cw_.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5 OK 0 12673 12800 1510201
File Name: /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_do2hg0cw_.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 127

Finished backup at 26-JUN-17

 

 

3.  Recover corrupt block (Logical corruption)

 

3.1  Detect the logical corruption

 

 RMAN> backup database;
…
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/23/2017 11:27:36
ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/EAMDB01/DATAFILE/system.266.926328081

$ dbv file=+DATA/EAMDB01/DATAFILE/system.266.926328081

DBVERIFY: Release 12.1.0.2.0 – Production on Fri Jun 23 11:33:22 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

DBVERIFY – Verification starting : FILE = +DATA/EAMDB01/DATAFILE/system.266.926328081

DBVERIFY – Verification complete

Total Pages Examined : 262144
Total Pages Processed (Data) : 70470
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 15803
Total Pages Failing (Index): 0
Total Pages Processed (Other): 6573
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 169298
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)

 

3.2 Validate using RMAN

 

 

  • Validate the datafile using RMAN:RMAN> backup validate check logical datafile 1;2) After completion of RMAN command, provide output from Sqlplus on connection AS SYSDBA:select * from v$database_block_corruption ;set pagesize 2000
    set linesize 280
    SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
    , greatest(e.block_id, c.block#) corr_start_block#
    , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
    , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
    – greatest(e.block_id, c.block#) + 1 blocks_corrupted
    , corruption_type description
    FROM dba_extents e, v$database_block_corruption c
    WHERE e.file_id = c.file#
    AND e.block_id <= c.block# + c.blocks – 1
    AND e.block_id + e.blocks – 1 >= c.block#
    UNION
    SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
    , header_block corr_start_block#
    , header_block corr_end_block#
    , 1 blocks_corrupted
    , corruption_type||’ Segment Header’ description
    FROM dba_segments s, v$database_block_corruption c
    WHERE s.header_file = c.file#
    AND s.header_block between c.block# and c.block# + c.blocks – 1
    UNION
    SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
    , greatest(f.block_id, c.block#) corr_start_block#
    , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
    , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
    – greatest(f.block_id, c.block#) + 1 blocks_corrupted
    , ‘Free Block’ description
    FROM dba_free_space f, v$database_block_corruption c
    WHERE f.file_id = c.file#
    AND f.block_id <= c.block# + c.blocks – 1
    AND f.block_id + f.blocks – 1 >= c.block#
    order by file#, corr_start_block#;

 

 

SQL> select * from v$database_block_corruption ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
———- ———- ———- —————— ——— ———-
5 341508 1 0 CORRUPT 0
3 117889 1 0 FRACTURED 0
4 456056 1 0 FRACTURED 0
…

OWNER SEGMENT_TYPE SEGMENT_NAME
——————————————————————————————————————————– —————— ——————————————————————————————————————————–
PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
——————————————————————————————————————————– ———- —————– ————— —————- ————————
SYS INDEX PARTITION WRH$_SYSSTAT_PK
WRH$_SYSSTA_2631080415_9515 3 117889 117889 1 FRACTURED
4 456056 456056 1 Free Block
5 341508 341508 1 Free Block

 

 

3.3 Repair corrupt block using RMAN

 

ACTION PLAN
============

1) Repair the affected blocks using RMAN:

RMAN> blockrecover corruption list;

2) Validate again to repopulate view v$database_block_corruption

RMAN> backup validate check logical datafile1;

3) Verify the output again from sqlplus to make sure all is clear:

select * from v$database_block_corruption ;

set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
– greatest(e.block_id, c.block#) + 1 blocks_corrupted
, corruption_type description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks – 1
AND e.block_id + e.blocks – 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, corruption_type||’ Segment Header’ description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks – 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
– greatest(f.block_id, c.block#) + 1 blocks_corrupted
, ‘Free Block’ description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks – 1
AND f.block_id + f.blocks – 1 >= c.block#
order by file#, corr_start_block#;

Clone point in time Recovery using Dataprotector and RMAN for single instance to single instance

Posted on by debasis maity

1.Login to source database

2. Create temporary pfile from spfile of source database

 

2.1. create temporary pfile from spfile

sqlplus / as sysdba

SQL>create pfile=’/tmp/inittest.ora’ from spfile;

 

 

2.2. Change parameters in init parameter to fit target database

 

Please change all source database name to target database name and create audit_file_dest and control_files loction

 

BKPTSTC.__data_transfer_cache_size=0

BKPTSTC.__db_cache_size=939524096

BKPTSTC.__inmemory_ext_roarea=0

BKPTSTC.__inmemory_ext_rwarea=0

BKPTSTC.__java_pool_size=16777216

BKPTSTC.__large_pool_size=33554432

BKPTSTC.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

BKPTSTC.__pga_aggregate_target=469762048

BKPTSTC.__sga_target=1375731712

BKPTSTC.__shared_io_pool_size=67108864

BKPTSTC.__shared_pool_size=301989888

BKPTSTC.__streams_pool_size=0

*.audit_file_dest=’/u01/app/oracle/admin/BKPTSTC/adump’

*.audit_trail=’db’

*.compatible=’12.2.0′

*.control_files=’/u01/app/oracle/oradata/BKPTSTC/control01.ctl’,’/u01/app/oracle/oradata/BKPTSTC/control02.ctl’

*.db_block_size=8192

*.db_create_file_dest=’/u01/app/oracle/oradata’

*.db_name=’BKPTSTC’

*.diagnostic_dest=’/u01/app/oracle’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=BKPTSTCXDB)’

*.local_listener=’LISTENER_BKPTSTC’

*.nls_language=’AMERICAN’

*.nls_territory=’AMERICA’

*.open_cursors=300

*.pga_aggregate_target=436m

*.processes=300

*.remote_login_passwordfile=’EXCLUSIVE’

*.sga_target=1305m

*.undo_tablespace=’UNDOTBS1′

3.  Startup no mount the database using temporary parameter file.

 

[oracle@xxx tmp]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 26 20:07:11 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile=’/tmp/inittest.ora’;
ORACLE instance started.

Total System Global Area 1375731712 bytes
Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes
SQL> exit

 

4.Clone the database after connecting to source, catalog and auxiliary

 

[oracle@xxx tmp]$ rman target sys/oracle@bkptst catalog rco/rco@botdbadb auxiliary /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 20:09:06 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (DBID=2013020542)
connected to recovery catalog database
connected to auxiliary database: BKPTSTC (not mounted)

RMAN> run {
set until time = “TO_DATE(’06/26/2017 11:59:00′,’MM/DD/YYYY HH24:MI:SS’)” ;
2> 3> configure auxiliary channel 1 device type sbt parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> DUPLICATE TARGET DATABASE TO BKPTSTC
5> logfile
6> group 1 (‘/u01/app/oracle/oradata/BKPTSTC/redolog1a.log’) size 512M,
7> group 2 (‘/u01/app/oracle/oradata/BKPTSTC/redolog2a.log’) size 512M,
8> group 3 (‘/u01/app/oracle/oradata/BKPTSTC/redolog3a.log’) size 512M;
9> }

executing command: SET until clause

new RMAN configuration parameters:
CONFIGURE AUXILIARY CHANNEL 1 DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Starting Duplicate Db at 26-JUN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=36 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protector A.09.00/110

contents of Memory Script:
{
sql clone “create spfile from memory”;
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

contents of Memory Script:
{
set until scn 1732640;
sql clone “alter system set db_name =
”BKPTST” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”BKPTSTC” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set db_name = ”BKPTST” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”BKPTSTC” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

Starting restore at 26-JUN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=35 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protector A.09.00/110

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-2013020542-20170626-00
channel ORA_AUX_SBT_TAPE_1: piece handle=c-2013020542-20170626-00 tag=TAG20170626T113217
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
output file name=/u01/app/oracle/oradata/BKPTSTC/control01.ctl
output file name=/u01/app/oracle/oradata/BKPTSTC/control02.ctl
Finished restore at 26-JUN-17

database mounted
datafile 7 not processed because file is offline

contents of Memory Script:
{
set until scn 1732640;
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
restore
clone database
skip forever tablespace “USERS” ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 26-JUN-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1

channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy RECID=21 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k)
channel ORA_AUX_DISK_1: restoring datafile 00003
input datafile copy RECID=20 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n)
channel ORA_AUX_DISK_1: restoring datafile 00004
input datafile copy RECID=19 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r)
channel ORA_AUX_DISK_1: restoring datafile 00005
input datafile copy RECID=17 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q
destination for restore of datafile 00005: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q)
failover to previous backup

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
Finished restore at 26-JUN-17

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=37 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=38 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=39 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=40 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf

contents of Memory Script:
{
set until time “to_date(‘JUN 26 2017 11:59:00’, ‘MON DD YYYY HH24:MI:SS’)”;
recover
clone database
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 26-JUN-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_42:947503915:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_42:947503915:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_43:947503930:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_43:947503930:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_45:947503941:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_45:947503941:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35

Executing: alter database datafile 7 offline drop
starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_3_947619127.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf thread=1 sequence=1
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf thread=1 sequence=2
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_3_947619127.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:28
Finished recover at 26-JUN-17
Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”BKPTSTC” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
}
executing Memory Script

sql statement: alter system set db_name = ”BKPTSTC” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “BKPTSTC” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog1a.log’ ) SIZE 512 M ,
GROUP 2 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog2a.log’ ) SIZE 512 M ,
GROUP 3 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog3a.log’ ) SIZE 512 M
DATAFILE
‘/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf’
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy “/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf”,
“/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf”,
“/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf RECID=1 STAMP=947708374
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf RECID=2 STAMP=947708374
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf RECID=3 STAMP=947708374

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened