Lab Exercise: Test oracle backup of backupset (both backup/restore)
For the lab exercise if at all we needed to use sbt library, we will use oracle.disksbt library which is a dummy library provided by oracle.
[read about this here: http://practical-tech.blogspot.com/2012/12/simulating-oracle-tape-backups.html & oracle 9.2 rman guide]
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';
setup:
1) create a tablespace for the testing purpose...
create tablespace backupofbackup01 datafile size 10m autoextend on next 10m;
select file_name from dba_data_files where lower(tablespace_name)='backupofbackup01';
alter user scott quota unlimited on backupofbackup01;
2) create a test table...
create table scott.test1 tablespace backupofbackup01 as
select mod(level,2) mdlvl,level lvl
from dual
connect by level < 1000;
3) Try access the table
select mdlvl,count(1)
from scott.test1
group by mdlvl;
4) Put the tbspc in Ro mode and take a backup of the tablespace as backupset to local disk
alter tablespace BACKUPOFBACKUP01 read only;
select tablespace_name,status from dba_tablespaces where tablespace_name='BACKUPOFBACKUP01';
backup disk location: /oradata_2/oratest_20apr19
run
{
allocate channel d1 device type disk;
backup as backupset tablespace BACKUPOFBACKUP01 format '/oradata_2/oratest_20apr19/backup_tbspc_%s_%t_%T';
}
5) Try backing up the backupset of the tbspc to disk and sbt.
to backup backupset...
BACKUP BACKUPSET command
to backup copy backups...
backup copy of
BACKUP DATAFILECOPY FROM TAG
option disk:
get the backupset key value from the last list command for the tablespace backup and use it in the command.
list backupset summary;
run
{
allocate channel d1 device type disk;
backup backupset 90 format '/oradata_2/oratest_dkch/backupdk_tbspc_%d_%s_%t_%T';
}
option tape:
run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
backup backupset 90 format 'backuptp_tbspc_%d_%s_%t_%T';
}
6) Try to restore the tablespace from each of this backupset copies and try fetch the records from the test table.
option tape backupset (copy#3):
i) list backup of tablespace BACKUPOFBACKUP01 summary;
ii) manually delete the file from disk /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
Try data selection to ensure the accidental damage is done... [if this retrives the rows, try disc from current session, flush the buffer cache and retry]
select mdlvl,count(1)
from scott.test1
group by mdlvl;
select error from v$datafile_header where file#=1014;
>> verify your getting "CANNOT OPEN FILE" message.
iii) To facilitate a tablespace restore on a nonarchivelog db, please keep the tablespace in offline state.Otherwise you may hit ORA-19573 & skipping datafile 1014; already restored to file errors.
alter tablespace BACKUPOFBACKUP01 offline;
iv) restore from the tape backupset copy (#3)
run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
restore tablespace BACKUPOFBACKUP01;
}
---below isnt needed, keeping it for reference purpose only---
run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
restore tablespace BACKUPOFBACKUP01 force;
}
------------------------
v) Bring the tablespace to online
alter tablespace BACKUPOFBACKUP01 online;
vi) Try data selection
select mdlvl,count(1)
from scott.test1
group by mdlvl;
option disk backup (copy#1) or disk backupset (copy#2):
i) Archive the previous results.
ii) manually delete the file from disk /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf [the name of the file may change based on the previous restore since we are using OMF for this test]
rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
Try data selection to ensure the accidental damage is done... [if this retrives the rows, try disc from current session, flush the buffer cache and retry]
select mdlvl,count(1)
from scott.test1
group by mdlvl;
select error from v$datafile_header where file#=1014;
>> verify your getting "CANNOT OPEN FILE" message.
iii) To facilitate a tablespace restore on a nonarchivelog db, please keep the tablespace in offline state.Otherwise you may hit ORA-19573 & skipping datafile 1014; already restored to file errors.
alter tablespace BACKUPOFBACKUP01 offline;
iv) restore from the backupset copy on disk (#2) -- let us try a default restore using disk channel and observe what happens, then let us force it to a specific copy by renaming the other copy.
run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP01;
}
--- it properly picks the copy#1 first and incase the copy#1 isnt available it fails over to the next one which is copy#2 [you will notice messages like "failover to piece handle=" in rman restore when copy#1 is inaccessible].
v) Bring the tablespace to online
alter tablespace BACKUPOFBACKUP01 online;
vi) Try data selection
select mdlvl,count(1)
from scott.test1
group by mdlvl;
vii) Query and store the v$rman_status
select START_TIME,END_TIME,OPERATION,status,MBYTES_PROCESSED,INPUT_BYTES,OUTPUT_BYTES,OPTIMIZED,OUTPUT_DEVICE_TYPE
from v$rman_status
where END_TIME > sysdate -1/24 and operation!='LIST'
order by start_time;
########Execution of setup:
########Step 1)
SQL> create tablespace backupofbackup01 datafile size 10m autoextend on next 10m;
Tablespace created.
SQL> select file_name from dba_data_files where lower(tablespace_name)='backupofbackup01';
FILE_NAME
--------------------------------------------------------------------------------
/oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
SQL> alter user scott quota unlimited on backupofbackup01;
User altered.
########Step 2)
SQL> create table scott.test1 tablespace backupofbackup01 as
select mod(level,2) mdlvl,level lvl
from dual
connect by level < 1000; 2 3 4
Table created.
SQL> select table_name,owner,tablespace_name from dba_tables where table_name='TEST1' and owner='SCOTT';
TABLE_NAME OWNER TABLESPACE_NAME
---------- ---------- ------------------------------
TEST1 SCOTT BACKUPOFBACKUP01
########Step 3)
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl; 2 3
MDLVL COUNT(1)
---------- ----------
1 500
0 499
########Step 4)
SQL> alter tablespace BACKUPOFBACKUP01 read only;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='BACKUPOFBACKUP01';
TABLESPACE_NAME STATUS
------------------------------ ---------
BACKUPOFBACKUP01 READ ONLY
SQL> select tablespace_name,status from dba_tablespaces where status='READ ONLY';
TABLESPACE_NAME STATUS
------------------------------ ---------
TBSPC_IMGCPY_COMP_TEST READ ONLY
BACKUPOFBACKUP01 READ ONLY
RMAN> list backup of tablespace BACKUPOFBACKUP01;
specification does not match any backup in the repository
RMAN> run
{
allocate channel d1 device type disk;
backup as backupset tablespace BACKUPOFBACKUP01 format '/oradata_2/oratest_20apr19/backup_tbspc_%s_%t_%T';
}2> 3> 4> 5>
allocated channel: d1
channel d1: SID=275 device type=DISK
Starting backup at 20-APR-19
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=01014 name=/oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
channel d1: starting piece 1 at 20-APR-19
channel d1: finished piece 1 at 20-APR-19
piece handle=/oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 tag=TAG20190420T175642 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:07
Finished backup at 20-APR-19
Starting Control File and SPFILE Autobackup at 20-APR-19
piece handle=/oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006106210_gcov7hkt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-APR-19
released channel: d1
RMAN> list backup of tablespace BACKUPOFBACKUP01;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
90 Full 1.08M DISK 00:00:01 20-APR-19
BP Key: 90 Status: AVAILABLE Compressed: NO Tag: TAG20190420T175642
Piece Name: /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
List of Datafiles in backup set 90
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1014 Full 5667749 20-APR-19 /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
RMAN>
RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
47 B F A DISK 12-AUG-18 1 1 NO TAG20180812T180622
..
89 B F A DISK 18-NOV-18 1 1 NO TAG20181118T135357
90 B F A DISK 20-APR-19 1 1 NO TAG20190420T175642 << backupset of interest
91 B F A DISK 20-APR-19 1 1 NO TAG20190420T175650
########Step 5)
Option 1) backup backupset to disk:
RMAN> run
{
allocate channel d1 device type disk;
backup backupset 90 format '/oradata_2/oratest_dkch/backupdk_tbspc_%d_%s_%t_%T';
}2> 3> 4> 5>
allocated channel: d1
channel d1: SID=275 device type=DISK
Starting backup at 20-APR-19
channel d1: input backup set: count=98, stamp=1006106203, piece=1
channel d1: starting piece 1 at 20-APR-19
channel d1: backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
piece handle=/oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420 comment=NONE
channel d1: finished piece 1 at 20-APR-19
channel d1: backup piece complete, elapsed time: 00:00:01
Finished backup at 20-APR-19
Starting Control File and SPFILE Autobackup at 20-APR-19
piece handle=/oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006107048_gcow1bcm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-APR-19
released channel: d1
RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
47 B F A DISK 12-AUG-18 1 1 NO TAG20180812T180622
80 B F A DISK 08-SEP-18 1 1 NO TAG20180908T114452
82 B F A DISK 08-SEP-18 1 1 NO TAG20180908T121002
84 B F A DISK 08-SEP-18 1 1 NO TAG20180908T135002
86 B F A DISK 08-SEP-18 1 1 NO TAG20180908T140833
87 B F A DISK 18-NOV-18 1 1 NO TAG20181118T135118
89 B F A DISK 18-NOV-18 1 1 NO TAG20181118T135357
90 B F A DISK 20-APR-19 1 2 NO TAG20190420T175642
91 B F A DISK 20-APR-19 1 1 NO TAG20190420T175650
92 B F A DISK 20-APR-19 1 1 NO TAG20190420T181048
RMAN> list backup of tablespace BACKUPOFBACKUP01;
List of Backup Sets
===================
BS Key Type LV Size
------- ---- -- ----------
90 Full 1.08M
List of Datafiles in backup set 90
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1014 Full 5667749 20-APR-19 /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
Backup Set Copy #1 of backup set 90
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 20-APR-19 NO TAG20190420T175642
List of Backup Pieces for backup set 90 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
90 1 AVAILABLE /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
Backup Set Copy #2 of backup set 90
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 20-APR-19 NO TAG20190420T175642
List of Backup Pieces for backup set 90 Copy #2 <<<<<<<<<<< 2nd copy information, we retained here the tag as well.
BP Key Pc# Status Piece Name
------- --- ----------- ----------
92 1 AVAILABLE /oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420
RMAN> list backup of spfile;
List of Backup Sets
===================
...
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
89 Full 20.58M DISK 00:00:02 18-NOV-18
BP Key: 89 Status: AVAILABLE Compressed: NO Tag: TAG20181118T135357
Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2018_11_18/o1_mf_s_992526837_fz1zmprx_.bkp
SPFILE Included: Modification time: 18-NOV-18
SPFILE db_unique_name: RMN01TST
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
91 Full 20.58M DISK 00:00:14 20-APR-19
BP Key: 91 Status: AVAILABLE Compressed: NO Tag: TAG20190420T175650
Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006106210_gcov7hkt_.bkp
SPFILE Included: Modification time: 20-APR-19
SPFILE db_unique_name: RMN01TST
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
92 Full 20.58M DISK 00:00:03 20-APR-19
BP Key: 93 Status: AVAILABLE Compressed: NO Tag: TAG20190420T181048
Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006107048_gcow1bcm_.bkp
SPFILE Included: Modification time: 20-APR-19
SPFILE db_unique_name: RMN01TST
RMAN> list backup of tablespace BACKUPOFBACKUP01 summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
90 B F A DISK 20-APR-19 1 2 NO TAG20190420T175642
RMAN>
Observation: See we now 2 copies vs earlier 1 copies as reported by "list backupset summary" against BS key = 90. Important to note the BP key value is 92 still vs 90 for the first copy, which I think is unique key.
Option 2) backup backupset to tape:
RMAN> run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
backup backupset 90 format 'backuptp_tbspc_%d_%s_%t_%T';
}2> 3> 4> 5>
allocated channel: t1
channel t1: SID=275 device type=SBT_TAPE
channel t1: WARNING: Oracle Test Disk API
Starting backup at 20-APR-19
channel t1: input backup set: count=98, stamp=1006106203, piece=1
channel t1: starting piece 1 at 20-APR-19
channel t1: backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
piece handle=backuptp_tbspc_RMN01TST_98_1006106203_20190420 comment=API Version 2.0,MMS Version 8.1.3.0
channel t1: finished piece 1 at 20-APR-19
channel t1: backup piece complete, elapsed time: 00:00:01
Finished backup at 20-APR-19
Starting Control File and SPFILE Autobackup at 20-APR-19
piece handle=c-2907708373-20190420-02 comment=API Version 2.0,MMS Version 8.1.3.0
Finished Control File and SPFILE Autobackup at 20-APR-19
released channel: t1
RMAN>
RMAN> list backup of tablespace BACKUPOFBACKUP01 summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
90 B F A * 20-APR-19 1 3 NO TAG20190420T175642
RMAN> list backup of tablespace BACKUPOFBACKUP01;
List of Backup Sets
===================
BS Key Type LV Size
------- ---- -- ----------
90 Full 1.08M
List of Datafiles in backup set 90
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1014 Full 5667749 20-APR-19 /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
Backup Set Copy #2 of backup set 90
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 20-APR-19 NO TAG20190420T175642
List of Backup Pieces for backup set 90 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
92 1 AVAILABLE /oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420
Backup Set Copy #1 of backup set 90
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 20-APR-19 NO TAG20190420T175642
List of Backup Pieces for backup set 90 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
90 1 AVAILABLE /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
Backup Set Copy #3 of backup set 90
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
SBT_TAPE 00:00:01 20-APR-19 NO TAG20190420T175642
List of Backup Pieces for backup set 90 Copy #3 <<< 3rd copy of the backupset 90, this time it is written to tape.
BP Key Pc# Status Media Piece Name
------- --- ----------- ----------------------- ----------
94 1 AVAILABLE /oratest_sbtch,backuptp_tbspc_RMN0 backuptp_tbspc_RMN01TST_98_1006106203_20190420
RMAN> list backup of spfile completed after 'sysdate-1';
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
91 Full 20.58M DISK 00:00:14 20-APR-19
BP Key: 91 Status: AVAILABLE Compressed: NO Tag: TAG20190420T175650
Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006106210_gcov7hkt_.bkp
SPFILE Included: Modification time: 20-APR-19
SPFILE db_unique_name: RMN01TST
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
92 Full 20.58M DISK 00:00:03 20-APR-19
BP Key: 93 Status: AVAILABLE Compressed: NO Tag: TAG20190420T181048
Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006107048_gcow1bcm_.bkp
SPFILE Included: Modification time: 20-APR-19
SPFILE db_unique_name: RMN01TST
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
93 Full 24.00M SBT_TAPE 00:00:01 20-APR-19
BP Key: 95 Status: AVAILABLE Compressed: NO Tag: TAG20190420T182221
Handle: c-2907708373-20190420-02 Media: /oratest_sbtch,c-2907708373-201904
SPFILE Included: Modification time: 20-APR-19
SPFILE db_unique_name: RMN01TST
RMAN>
Observation: Note this time the media became '*' since we now backups present in both disk and tape. The copy# increased by 1, so the # of backup copies count is 3 for tablespace BACKUPOFBACKUP01.
Allset with first round of tests with backupsets. Now let us try deleting the file and perform restoration from each of this backups to see if it works fine.
########step 6) Restore test.
>>>>>>>>>>>>>>>option tape restore (copy#3)
RMAN> list backup of tablespace backupofbackup01 summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
90 B F A * 20-APR-19 1 3 NO TAG20190420T175642
RMAN>
SQL> !rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf
SQL> !ls -altr /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf
ls: cannot access /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf: No such file or directory
SQL> alter system flush buffer_cache;
System altered.
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl; 2 3
select mdlvl,count(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 1014
ORA-01110: data file 1014:
'/oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select error from v$datafile_header where file#=1014;
ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE
SQL> alter tablespace BACKUPOFBACKUP01 offline;
Tablespace altered.
SQL>
RMAN> run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
restore tablespace BACKUPOFBACKUP01;
}2> 3> 4> 5>
released channel: ORA_DISK_1
allocated channel: t1
channel t1: SID=29 device type=SBT_TAPE
channel t1: WARNING: Oracle Test Disk API
Starting restore at 21-APR-19
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 01014 to /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf
channel t1: reading from backup piece backuptp_tbspc_RMN01TST_98_1006106203_20190420
channel t1: piece handle=backuptp_tbspc_RMN01TST_98_1006106203_20190420 tag=TAG20190420T175642
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-19
released channel: t1
RMAN>
SQL> alter tablespace BACKUPOFBACKUP01 online;
Tablespace altered.
SQL> select tablespace_name,status from dba_Tablespaces where tablespace_name='BACKUPOFBACKUP01';
TABLESPACE_NAME STATUS
------------------------------ ---------
BACKUPOFBACKUP01 READ ONLY
SQL>
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl; 2 3
MDLVL COUNT(1)
---------- ----------
1 500
0 499
SQL>
=> so reading the backup piece sent to tape works fine, which is the copy#3 (of backupset)
>>>>>>>>>>>>>>>option disk restore (copy#1 - which is the default)
Now let us test backupset copy#1 or actual backupset as preferred by oracle...
SQL> !rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf
SQL> !ls -altr /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf
ls: cannot access /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf: No such file or directory
SQL> alter system flush buffer_cache;
System altered.
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl; 2 3
select mdlvl,count(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 1014
ORA-01110: data file 1014:
'/oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select error from v$datafile_header where file#=1014;
ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE
SQL>
SQL> alter tablespace BACKUPOFBACKUP01 offline;
Tablespace altered.
SQL>
RMAN> run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP01;
}2> 3> 4> 5>
allocated channel: d1
channel d1: SID=29 device type=DISK
Starting restore at 21-APR-19
channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 01014 to /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf
channel d1: reading from backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 <<<<<<<< this is the actual backupset
channel d1: piece handle=/oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 tag=TAG20190420T175642
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-19
released channel: d1
RMAN>
SQL> alter tablespace BACKUPOFBACKUP01 online;
Tablespace altered.
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl 2 3 ;
MDLVL COUNT(1)
---------- ----------
1 500
0 499
>>>>>>>>>>>>>>>option disk restore (copy#2 - which is the failover)
Let us now rename this actual backupset and see if this restore can fallback to copy#2 (the one in disk)
SQL> !mv /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420_KEEPSAFE
SQL> !ls -altr /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
ls: cannot access /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420: No such file or directory
SQL>
SQL> alter system flush buffer_cache;
System altered.
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl 2 3 ;
select mdlvl,count(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 1014
ORA-01110: data file 1014:
'/oradata_1/ORATEST/datafile/o1_mf_backupof_gcplkdns_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select error from v$datafile_header where file#=1014;
ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE
SQL> alter tablespace BACKUPOFBACKUP01 offline;
Tablespace altered.
SQL>
RMAN> run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP01;
}2> 3> 4> 5>
allocated channel: d1
channel d1: SID=29 device type=DISK
Starting restore at 21-APR-19
channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 01014 to /oradata_1/ORATEST/datafile/o1_mf_backupof_gcplkdns_.dbf
channel d1: reading from backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
channel d1: errors found reading piece handle=/oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
channel d1: failover to piece handle=/oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420 tag=TAG20190420T175642
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-19
released channel: d1
RMAN>
SQL> select error from v$datafile_header where file#=1014;
ERROR
-----------------------------------------------------------------
OFFLINE NORMAL
SQL> alter tablespace BACKUPOFBACKUP01 online;
Tablespace altered.
SQL> select error from v$datafile_header where file#=1014;
ERROR
-----------------------------------------------------------------
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl 2 3 ;
MDLVL COUNT(1)
---------- ----------
1 500
0 499
SQL> select file_name from dba_data_Files where tablespace_name='BACKUPOFBACKUP01';
FILE_NAME
--------------------------------------------------------------------------------
/oradata_1/ORATEST/datafile/o1_mf_backupof_gcpltsnk_.dbf
SQL>
SQL> select START_TIME,END_TIME,OPERATION,status,MBYTES_PROCESSED,INPUT_BYTES,OUTPUT_BYTES,OPTIMIZED,OUTPUT_DEVICE_TYPE
from v$rman_status
where END_TIME > sysdate -1/24 and operation!='LIST'
order by start_time; 2 3 4
START_TIME END_TIME OPERATION STATUS MBYTES_PROCESSED INPUT_BYTES OUTPUT_BYTES OPT OUTPUT_DEVICE_TYP
------------------- ------------------- --------------------------------- ----------------------- ---------------- ----------- ------------ --- -----------------
20/04/2019 23:47:55 21/04/2019 00:54:14 RMAN RUNNING WITH ERRORS 0 0 0 NO
21/04/2019 00:15:03 21/04/2019 00:15:06 RESTORE COMPLETED 10 0 10485760 NO
21/04/2019 00:26:25 21/04/2019 00:26:26 VALIDATE FAILED 0 0 0 NO
21/04/2019 00:29:40 21/04/2019 00:29:42 RESTORE COMPLETED 10 0 10485760 NO
21/04/2019 00:34:24 21/04/2019 00:34:27 RESTORE FAILED 0 2080768 0 NO
21/04/2019 00:34:51 21/04/2019 00:34:53 RESTORE COMPLETED 10 1130496 10485760 NO DISK
21/04/2019 00:39:52 21/04/2019 00:39:54 RESTORE COMPLETED 10 1130496 10485760 NO DISK
7 rows selected.
===> Summary: So oracle's backup of backupset works fine with both disk and tape copies of it. We just need to be careful when trying this restore operation on a nonarchivelog mode db to avoid ORA-19753 and skip already restored file RMAN errors.
Thanks for the visit, your valuable comments are welcome :)
For the lab exercise if at all we needed to use sbt library, we will use oracle.disksbt library which is a dummy library provided by oracle.
[read about this here: http://practical-tech.blogspot.com/2012/12/simulating-oracle-tape-backups.html & oracle 9.2 rman guide]
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';
setup:
1) create a tablespace for the testing purpose...
create tablespace backupofbackup01 datafile size 10m autoextend on next 10m;
select file_name from dba_data_files where lower(tablespace_name)='backupofbackup01';
alter user scott quota unlimited on backupofbackup01;
2) create a test table...
create table scott.test1 tablespace backupofbackup01 as
select mod(level,2) mdlvl,level lvl
from dual
connect by level < 1000;
3) Try access the table
select mdlvl,count(1)
from scott.test1
group by mdlvl;
4) Put the tbspc in Ro mode and take a backup of the tablespace as backupset to local disk
alter tablespace BACKUPOFBACKUP01 read only;
select tablespace_name,status from dba_tablespaces where tablespace_name='BACKUPOFBACKUP01';
backup disk location: /oradata_2/oratest_20apr19
run
{
allocate channel d1 device type disk;
backup as backupset tablespace BACKUPOFBACKUP01 format '/oradata_2/oratest_20apr19/backup_tbspc_%s_%t_%T';
}
5) Try backing up the backupset of the tbspc to disk and sbt.
to backup backupset...
BACKUP BACKUPSET command
to backup copy backups...
backup copy of
BACKUP DATAFILECOPY FROM TAG
option disk:
get the backupset key value from the last list command for the tablespace backup and use it in the command.
list backupset summary;
run
{
allocate channel d1 device type disk;
backup backupset 90 format '/oradata_2/oratest_dkch/backupdk_tbspc_%d_%s_%t_%T';
}
option tape:
run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
backup backupset 90 format 'backuptp_tbspc_%d_%s_%t_%T';
}
6) Try to restore the tablespace from each of this backupset copies and try fetch the records from the test table.
option tape backupset (copy#3):
i) list backup of tablespace BACKUPOFBACKUP01 summary;
ii) manually delete the file from disk /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
Try data selection to ensure the accidental damage is done... [if this retrives the rows, try disc from current session, flush the buffer cache and retry]
select mdlvl,count(1)
from scott.test1
group by mdlvl;
select error from v$datafile_header where file#=1014;
>> verify your getting "CANNOT OPEN FILE" message.
iii) To facilitate a tablespace restore on a nonarchivelog db, please keep the tablespace in offline state.Otherwise you may hit ORA-19573 & skipping datafile 1014; already restored to file errors.
alter tablespace BACKUPOFBACKUP01 offline;
iv) restore from the tape backupset copy (#3)
run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
restore tablespace BACKUPOFBACKUP01;
}
---below isnt needed, keeping it for reference purpose only---
run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
restore tablespace BACKUPOFBACKUP01 force;
}
------------------------
v) Bring the tablespace to online
alter tablespace BACKUPOFBACKUP01 online;
vi) Try data selection
select mdlvl,count(1)
from scott.test1
group by mdlvl;
option disk backup (copy#1) or disk backupset (copy#2):
i) Archive the previous results.
ii) manually delete the file from disk /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf [the name of the file may change based on the previous restore since we are using OMF for this test]
rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
Try data selection to ensure the accidental damage is done... [if this retrives the rows, try disc from current session, flush the buffer cache and retry]
select mdlvl,count(1)
from scott.test1
group by mdlvl;
select error from v$datafile_header where file#=1014;
>> verify your getting "CANNOT OPEN FILE" message.
iii) To facilitate a tablespace restore on a nonarchivelog db, please keep the tablespace in offline state.Otherwise you may hit ORA-19573 & skipping datafile 1014; already restored to file errors.
alter tablespace BACKUPOFBACKUP01 offline;
iv) restore from the backupset copy on disk (#2) -- let us try a default restore using disk channel and observe what happens, then let us force it to a specific copy by renaming the other copy.
run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP01;
}
--- it properly picks the copy#1 first and incase the copy#1 isnt available it fails over to the next one which is copy#2 [you will notice messages like "failover to piece handle=" in rman restore when copy#1 is inaccessible].
v) Bring the tablespace to online
alter tablespace BACKUPOFBACKUP01 online;
vi) Try data selection
select mdlvl,count(1)
from scott.test1
group by mdlvl;
vii) Query and store the v$rman_status
select START_TIME,END_TIME,OPERATION,status,MBYTES_PROCESSED,INPUT_BYTES,OUTPUT_BYTES,OPTIMIZED,OUTPUT_DEVICE_TYPE
from v$rman_status
where END_TIME > sysdate -1/24 and operation!='LIST'
order by start_time;
########Execution of setup:
########Step 1)
SQL> create tablespace backupofbackup01 datafile size 10m autoextend on next 10m;
Tablespace created.
SQL> select file_name from dba_data_files where lower(tablespace_name)='backupofbackup01';
FILE_NAME
--------------------------------------------------------------------------------
/oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
SQL> alter user scott quota unlimited on backupofbackup01;
User altered.
########Step 2)
SQL> create table scott.test1 tablespace backupofbackup01 as
select mod(level,2) mdlvl,level lvl
from dual
connect by level < 1000; 2 3 4
Table created.
SQL> select table_name,owner,tablespace_name from dba_tables where table_name='TEST1' and owner='SCOTT';
TABLE_NAME OWNER TABLESPACE_NAME
---------- ---------- ------------------------------
TEST1 SCOTT BACKUPOFBACKUP01
########Step 3)
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl; 2 3
MDLVL COUNT(1)
---------- ----------
1 500
0 499
########Step 4)
SQL> alter tablespace BACKUPOFBACKUP01 read only;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='BACKUPOFBACKUP01';
TABLESPACE_NAME STATUS
------------------------------ ---------
BACKUPOFBACKUP01 READ ONLY
SQL> select tablespace_name,status from dba_tablespaces where status='READ ONLY';
TABLESPACE_NAME STATUS
------------------------------ ---------
TBSPC_IMGCPY_COMP_TEST READ ONLY
BACKUPOFBACKUP01 READ ONLY
RMAN> list backup of tablespace BACKUPOFBACKUP01;
specification does not match any backup in the repository
RMAN> run
{
allocate channel d1 device type disk;
backup as backupset tablespace BACKUPOFBACKUP01 format '/oradata_2/oratest_20apr19/backup_tbspc_%s_%t_%T';
}2> 3> 4> 5>
allocated channel: d1
channel d1: SID=275 device type=DISK
Starting backup at 20-APR-19
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=01014 name=/oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
channel d1: starting piece 1 at 20-APR-19
channel d1: finished piece 1 at 20-APR-19
piece handle=/oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 tag=TAG20190420T175642 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:07
Finished backup at 20-APR-19
Starting Control File and SPFILE Autobackup at 20-APR-19
piece handle=/oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006106210_gcov7hkt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-APR-19
released channel: d1
RMAN> list backup of tablespace BACKUPOFBACKUP01;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
90 Full 1.08M DISK 00:00:01 20-APR-19
BP Key: 90 Status: AVAILABLE Compressed: NO Tag: TAG20190420T175642
Piece Name: /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
List of Datafiles in backup set 90
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1014 Full 5667749 20-APR-19 /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
RMAN>
RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
47 B F A DISK 12-AUG-18 1 1 NO TAG20180812T180622
..
89 B F A DISK 18-NOV-18 1 1 NO TAG20181118T135357
90 B F A DISK 20-APR-19 1 1 NO TAG20190420T175642 << backupset of interest
91 B F A DISK 20-APR-19 1 1 NO TAG20190420T175650
########Step 5)
Option 1) backup backupset to disk:
RMAN> run
{
allocate channel d1 device type disk;
backup backupset 90 format '/oradata_2/oratest_dkch/backupdk_tbspc_%d_%s_%t_%T';
}2> 3> 4> 5>
allocated channel: d1
channel d1: SID=275 device type=DISK
Starting backup at 20-APR-19
channel d1: input backup set: count=98, stamp=1006106203, piece=1
channel d1: starting piece 1 at 20-APR-19
channel d1: backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
piece handle=/oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420 comment=NONE
channel d1: finished piece 1 at 20-APR-19
channel d1: backup piece complete, elapsed time: 00:00:01
Finished backup at 20-APR-19
Starting Control File and SPFILE Autobackup at 20-APR-19
piece handle=/oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006107048_gcow1bcm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-APR-19
released channel: d1
RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
47 B F A DISK 12-AUG-18 1 1 NO TAG20180812T180622
80 B F A DISK 08-SEP-18 1 1 NO TAG20180908T114452
82 B F A DISK 08-SEP-18 1 1 NO TAG20180908T121002
84 B F A DISK 08-SEP-18 1 1 NO TAG20180908T135002
86 B F A DISK 08-SEP-18 1 1 NO TAG20180908T140833
87 B F A DISK 18-NOV-18 1 1 NO TAG20181118T135118
89 B F A DISK 18-NOV-18 1 1 NO TAG20181118T135357
90 B F A DISK 20-APR-19 1 2 NO TAG20190420T175642
91 B F A DISK 20-APR-19 1 1 NO TAG20190420T175650
92 B F A DISK 20-APR-19 1 1 NO TAG20190420T181048
RMAN> list backup of tablespace BACKUPOFBACKUP01;
List of Backup Sets
===================
BS Key Type LV Size
------- ---- -- ----------
90 Full 1.08M
List of Datafiles in backup set 90
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1014 Full 5667749 20-APR-19 /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
Backup Set Copy #1 of backup set 90
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 20-APR-19 NO TAG20190420T175642
List of Backup Pieces for backup set 90 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
90 1 AVAILABLE /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
Backup Set Copy #2 of backup set 90
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 20-APR-19 NO TAG20190420T175642
List of Backup Pieces for backup set 90 Copy #2 <<<<<<<<<<< 2nd copy information, we retained here the tag as well.
BP Key Pc# Status Piece Name
------- --- ----------- ----------
92 1 AVAILABLE /oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420
RMAN> list backup of spfile;
List of Backup Sets
===================
...
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
89 Full 20.58M DISK 00:00:02 18-NOV-18
BP Key: 89 Status: AVAILABLE Compressed: NO Tag: TAG20181118T135357
Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2018_11_18/o1_mf_s_992526837_fz1zmprx_.bkp
SPFILE Included: Modification time: 18-NOV-18
SPFILE db_unique_name: RMN01TST
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
91 Full 20.58M DISK 00:00:14 20-APR-19
BP Key: 91 Status: AVAILABLE Compressed: NO Tag: TAG20190420T175650
Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006106210_gcov7hkt_.bkp
SPFILE Included: Modification time: 20-APR-19
SPFILE db_unique_name: RMN01TST
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
92 Full 20.58M DISK 00:00:03 20-APR-19
BP Key: 93 Status: AVAILABLE Compressed: NO Tag: TAG20190420T181048
Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006107048_gcow1bcm_.bkp
SPFILE Included: Modification time: 20-APR-19
SPFILE db_unique_name: RMN01TST
RMAN> list backup of tablespace BACKUPOFBACKUP01 summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
90 B F A DISK 20-APR-19 1 2 NO TAG20190420T175642
RMAN>
Observation: See we now 2 copies vs earlier 1 copies as reported by "list backupset summary" against BS key = 90. Important to note the BP key value is 92 still vs 90 for the first copy, which I think is unique key.
Option 2) backup backupset to tape:
RMAN> run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
backup backupset 90 format 'backuptp_tbspc_%d_%s_%t_%T';
}2> 3> 4> 5>
allocated channel: t1
channel t1: SID=275 device type=SBT_TAPE
channel t1: WARNING: Oracle Test Disk API
Starting backup at 20-APR-19
channel t1: input backup set: count=98, stamp=1006106203, piece=1
channel t1: starting piece 1 at 20-APR-19
channel t1: backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
piece handle=backuptp_tbspc_RMN01TST_98_1006106203_20190420 comment=API Version 2.0,MMS Version 8.1.3.0
channel t1: finished piece 1 at 20-APR-19
channel t1: backup piece complete, elapsed time: 00:00:01
Finished backup at 20-APR-19
Starting Control File and SPFILE Autobackup at 20-APR-19
piece handle=c-2907708373-20190420-02 comment=API Version 2.0,MMS Version 8.1.3.0
Finished Control File and SPFILE Autobackup at 20-APR-19
released channel: t1
RMAN>
RMAN> list backup of tablespace BACKUPOFBACKUP01 summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
90 B F A * 20-APR-19 1 3 NO TAG20190420T175642
RMAN> list backup of tablespace BACKUPOFBACKUP01;
List of Backup Sets
===================
BS Key Type LV Size
------- ---- -- ----------
90 Full 1.08M
List of Datafiles in backup set 90
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1014 Full 5667749 20-APR-19 /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
Backup Set Copy #2 of backup set 90
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 20-APR-19 NO TAG20190420T175642
List of Backup Pieces for backup set 90 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
92 1 AVAILABLE /oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420
Backup Set Copy #1 of backup set 90
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 20-APR-19 NO TAG20190420T175642
List of Backup Pieces for backup set 90 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
90 1 AVAILABLE /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
Backup Set Copy #3 of backup set 90
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
SBT_TAPE 00:00:01 20-APR-19 NO TAG20190420T175642
List of Backup Pieces for backup set 90 Copy #3 <<< 3rd copy of the backupset 90, this time it is written to tape.
BP Key Pc# Status Media Piece Name
------- --- ----------- ----------------------- ----------
94 1 AVAILABLE /oratest_sbtch,backuptp_tbspc_RMN0 backuptp_tbspc_RMN01TST_98_1006106203_20190420
RMAN> list backup of spfile completed after 'sysdate-1';
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
91 Full 20.58M DISK 00:00:14 20-APR-19
BP Key: 91 Status: AVAILABLE Compressed: NO Tag: TAG20190420T175650
Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006106210_gcov7hkt_.bkp
SPFILE Included: Modification time: 20-APR-19
SPFILE db_unique_name: RMN01TST
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
92 Full 20.58M DISK 00:00:03 20-APR-19
BP Key: 93 Status: AVAILABLE Compressed: NO Tag: TAG20190420T181048
Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006107048_gcow1bcm_.bkp
SPFILE Included: Modification time: 20-APR-19
SPFILE db_unique_name: RMN01TST
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
93 Full 24.00M SBT_TAPE 00:00:01 20-APR-19
BP Key: 95 Status: AVAILABLE Compressed: NO Tag: TAG20190420T182221
Handle: c-2907708373-20190420-02 Media: /oratest_sbtch,c-2907708373-201904
SPFILE Included: Modification time: 20-APR-19
SPFILE db_unique_name: RMN01TST
RMAN>
Observation: Note this time the media became '*' since we now backups present in both disk and tape. The copy# increased by 1, so the # of backup copies count is 3 for tablespace BACKUPOFBACKUP01.
Allset with first round of tests with backupsets. Now let us try deleting the file and perform restoration from each of this backups to see if it works fine.
########step 6) Restore test.
>>>>>>>>>>>>>>>option tape restore (copy#3)
RMAN> list backup of tablespace backupofbackup01 summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
90 B F A * 20-APR-19 1 3 NO TAG20190420T175642
RMAN>
SQL> !rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf
SQL> !ls -altr /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf
ls: cannot access /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf: No such file or directory
SQL> alter system flush buffer_cache;
System altered.
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl; 2 3
select mdlvl,count(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 1014
ORA-01110: data file 1014:
'/oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select error from v$datafile_header where file#=1014;
ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE
SQL> alter tablespace BACKUPOFBACKUP01 offline;
Tablespace altered.
SQL>
RMAN> run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
restore tablespace BACKUPOFBACKUP01;
}2> 3> 4> 5>
released channel: ORA_DISK_1
allocated channel: t1
channel t1: SID=29 device type=SBT_TAPE
channel t1: WARNING: Oracle Test Disk API
Starting restore at 21-APR-19
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 01014 to /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf
channel t1: reading from backup piece backuptp_tbspc_RMN01TST_98_1006106203_20190420
channel t1: piece handle=backuptp_tbspc_RMN01TST_98_1006106203_20190420 tag=TAG20190420T175642
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-19
released channel: t1
RMAN>
SQL> alter tablespace BACKUPOFBACKUP01 online;
Tablespace altered.
SQL> select tablespace_name,status from dba_Tablespaces where tablespace_name='BACKUPOFBACKUP01';
TABLESPACE_NAME STATUS
------------------------------ ---------
BACKUPOFBACKUP01 READ ONLY
SQL>
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl; 2 3
MDLVL COUNT(1)
---------- ----------
1 500
0 499
SQL>
=> so reading the backup piece sent to tape works fine, which is the copy#3 (of backupset)
>>>>>>>>>>>>>>>option disk restore (copy#1 - which is the default)
Now let us test backupset copy#1 or actual backupset as preferred by oracle...
SQL> !rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf
SQL> !ls -altr /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf
ls: cannot access /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf: No such file or directory
SQL> alter system flush buffer_cache;
System altered.
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl; 2 3
select mdlvl,count(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 1014
ORA-01110: data file 1014:
'/oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select error from v$datafile_header where file#=1014;
ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE
SQL>
SQL> alter tablespace BACKUPOFBACKUP01 offline;
Tablespace altered.
SQL>
RMAN> run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP01;
}2> 3> 4> 5>
allocated channel: d1
channel d1: SID=29 device type=DISK
Starting restore at 21-APR-19
channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 01014 to /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf
channel d1: reading from backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 <<<<<<<< this is the actual backupset
channel d1: piece handle=/oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 tag=TAG20190420T175642
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-19
released channel: d1
RMAN>
SQL> alter tablespace BACKUPOFBACKUP01 online;
Tablespace altered.
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl 2 3 ;
MDLVL COUNT(1)
---------- ----------
1 500
0 499
>>>>>>>>>>>>>>>option disk restore (copy#2 - which is the failover)
Let us now rename this actual backupset and see if this restore can fallback to copy#2 (the one in disk)
SQL> !mv /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420_KEEPSAFE
SQL> !ls -altr /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
ls: cannot access /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420: No such file or directory
SQL>
SQL> alter system flush buffer_cache;
System altered.
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl 2 3 ;
select mdlvl,count(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 1014
ORA-01110: data file 1014:
'/oradata_1/ORATEST/datafile/o1_mf_backupof_gcplkdns_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select error from v$datafile_header where file#=1014;
ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE
SQL> alter tablespace BACKUPOFBACKUP01 offline;
Tablespace altered.
SQL>
RMAN> run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP01;
}2> 3> 4> 5>
allocated channel: d1
channel d1: SID=29 device type=DISK
Starting restore at 21-APR-19
channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 01014 to /oradata_1/ORATEST/datafile/o1_mf_backupof_gcplkdns_.dbf
channel d1: reading from backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
channel d1: errors found reading piece handle=/oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
channel d1: failover to piece handle=/oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420 tag=TAG20190420T175642
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-19
released channel: d1
RMAN>
SQL> select error from v$datafile_header where file#=1014;
ERROR
-----------------------------------------------------------------
OFFLINE NORMAL
SQL> alter tablespace BACKUPOFBACKUP01 online;
Tablespace altered.
SQL> select error from v$datafile_header where file#=1014;
ERROR
-----------------------------------------------------------------
SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl 2 3 ;
MDLVL COUNT(1)
---------- ----------
1 500
0 499
SQL> select file_name from dba_data_Files where tablespace_name='BACKUPOFBACKUP01';
FILE_NAME
--------------------------------------------------------------------------------
/oradata_1/ORATEST/datafile/o1_mf_backupof_gcpltsnk_.dbf
SQL>
SQL> select START_TIME,END_TIME,OPERATION,status,MBYTES_PROCESSED,INPUT_BYTES,OUTPUT_BYTES,OPTIMIZED,OUTPUT_DEVICE_TYPE
from v$rman_status
where END_TIME > sysdate -1/24 and operation!='LIST'
order by start_time; 2 3 4
START_TIME END_TIME OPERATION STATUS MBYTES_PROCESSED INPUT_BYTES OUTPUT_BYTES OPT OUTPUT_DEVICE_TYP
------------------- ------------------- --------------------------------- ----------------------- ---------------- ----------- ------------ --- -----------------
20/04/2019 23:47:55 21/04/2019 00:54:14 RMAN RUNNING WITH ERRORS 0 0 0 NO
21/04/2019 00:15:03 21/04/2019 00:15:06 RESTORE COMPLETED 10 0 10485760 NO
21/04/2019 00:26:25 21/04/2019 00:26:26 VALIDATE FAILED 0 0 0 NO
21/04/2019 00:29:40 21/04/2019 00:29:42 RESTORE COMPLETED 10 0 10485760 NO
21/04/2019 00:34:24 21/04/2019 00:34:27 RESTORE FAILED 0 2080768 0 NO
21/04/2019 00:34:51 21/04/2019 00:34:53 RESTORE COMPLETED 10 1130496 10485760 NO DISK
21/04/2019 00:39:52 21/04/2019 00:39:54 RESTORE COMPLETED 10 1130496 10485760 NO DISK
7 rows selected.
===> Summary: So oracle's backup of backupset works fine with both disk and tape copies of it. We just need to be careful when trying this restore operation on a nonarchivelog mode db to avoid ORA-19753 and skip already restored file RMAN errors.
Thanks for the visit, your valuable comments are welcome :)
No comments:
Post a Comment