Validate Oracle Backup from Tape

Purpose:

The main purpose of RMAN validation is to check for corrupt blocks and missing files. You can also use RMAN to determine whether backups can be restored. You can use the following RMAN commands to perform validation:

Oracle Spfile Backup Validate from Tape

$ rman target / catalog rman/rman123@catdb

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Sep 6 12:39:12 2010

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

connected to target database: SSADB (DBID=3387211866)

connected to recovery catalog database

RMAN> list backup of spfile;

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

152286 Full 23.00M SBT_TAPE 00:01:04 05-SEP-10

BP Key: 152288 Status: AVAILABLE Compressed: NO Tag: TAG20100905T213

149

Handle: c-3389811866-20100905-02 Media: 0011L4

SPFILE Included: Modification time: 05-SEP-10

RMAN> run {

2> allocate channel ch001 type 'SBT_TAPE';

3> SEND 'NB_ORA_CLIENT=ora-rs';

4> validate backupset 152286;

5> }

allocated channel: ch001

channel ch001: sid=1720 devtype=SBT_TAPE

channel ch001: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

sent command to channel: ch001

channel ch001: starting validation of datafile backupset

channel ch001: reading from backup piece c-3389811866-20100905-02

channel ch001: restored backup piece 1

piece handle=c-3389811866-20100905-02 tag=TAG20100905T213149

channel ch001: validation complete, elapsed time: 00:02:05

released channel: ch001

Validate Oracle Controlfile Backup from Tape

RMAN> list backup of controlfile;



BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
152272 Full 22.75M SBT_TAPE 00:01:01 05-SEP-10
BP Key: 152275 Status: AVAILABLE Compressed: NO Tag: TAG20100905T213
042
Handle: cntrl_5906_1_728947842 Media: 0011L4
Control File Included: Ckp SCN: 676569375 Ckp time: 05-SEP-10



BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
152286 Full 23.00M SBT_TAPE 00:01:04 05-SEP-10
BP Key: 152288 Status: AVAILABLE Compressed: NO Tag: TAG20100905T213
149
Handle: c-3389811866-20100905-02 Media: 0011L4
Control File Included: Ckp SCN: 676569420 Ckp time: 05-SEP-10

RMAN> run {

2> allocate channel ch001 type 'SBT_TAPE';

3> SEND 'NB_ORA_CLIENT=ora-rs';

4> validate backupset 152286;

5> }

Validate Oracle Datafile Backup from Tape

RMAN>list backup of database completed between '04-SEP-10' and '06-SEP-10';

RMAN>list backup of database completed after '01-SEP-10';

RMAN>list backup of database;

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

152119 Incr 0 22.43G SBT_TAPE 00:17:10 05-SEP-10

BP Key: 152129 Status: AVAILABLE Compressed: NO Tag: HOT_DB_BK_LEVEL

0

Handle: bk_5900_1_728946270 Media:

List of Datafiles in backup set 152119

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

2 0 Incr 676568348 05-SEP-10 /ora_dat/ssadb/undotbs01.dbf

4 0 Incr 676568348 05-SEP-10 /ora_dat/ssadb/users01.dbf

5 0 Incr 676568348 05-SEP-10 /ora_dat/ssadb/system02.dbf

30 0 Incr 676568348 05-SEP-10 /ora_idx/ssadb/idx_SSAERP13.dbf

31 0 Incr 676568348 05-SEP-10 /ora_idx/ssadb/idx_SSAERP14.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

152120 Incr 0 7.15G SBT_TAPE 00:10:59 05-SEP-10

BP Key: 152130 Status: AVAILABLE Compressed: NO Tag: HOT_DB_BK_LEVEL

0

Handle: bk_5901_1_728946836 Media:

List of Datafiles in backup set 152120

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 0 Incr 676568630 05-SEP-10 /ora_dat/ssadb/system01.dbf

3 0 Incr 676568630 05-SEP-10 /ora_dat/ssadb/sysaux01.dbf

32 0 Incr 676568630 05-SEP-10 /ora_idx/ssadb/idx_SSAERP15.dbf

RMAN> run {

2> allocate channel ch00 type 'SBT_TAPE';

3> send 'NB_ORA_CLIENT=ora-rs';

4> validate backupset 152120,151119;

5> release channel ch00;

6> }

allocated channel: ch00

channel ch00: sid=690 devtype=SBT_TAPE

channel ch00: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

sent command to channel: ch00

channel ch00: starting validation of datafile backupset

channel ch00: reading from backup piece bk_5886_1_728858000

channel ch00: restored backup piece 1

piece handle=bk_5886_1_728858000 tag=HOT_DB_BK_LEVEL0

channel ch00: validation complete, elapsed time: 00:08:48

channel ch00: starting validation of datafile backupset

channel ch00: reading from backup piece bk_5887_1_728858565

channel ch00: restored backup piece 1

piece handle=bk_5887_1_728858565 tag=HOT_DB_BK_LEVEL0

channel ch00: validation complete, elapsed time: 00:02:15

released channel: ch00

Validate Oracle Archive Log Files

RMAN> list backup of archivelog all completed after '05-SEP-10';

List of Backup Sets

===================

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

152199 504.50M SBT_TAPE 00:01:09 05-SEP-10

BP Key: 152203 Status: AVAILABLE Compressed: NO Tag: TAG20100905T212

702

Handle: al_5903_1_728947623 Media:

List of Archived Logs in backup set 152199

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 20647 675468442 04-SEP-10 675618305 04-SEP-10

1 20648 675618305 04-SEP-10 675621933 04-SEP-10

1 20649 675621933 04-SEP-10 675787032 05-SEP-10

1 20650 675787032 05-SEP-10 675801972 05-SEP-10

1 20651 675801972 05-SEP-10 675950772 05-SEP-10

1 20652 675950772 05-SEP-10 675970688 05-SEP-10

1 20653 675970688 05-SEP-10 675990826 05-SEP-10

1 20654 675990826 05-SEP-10 676142884 05-SEP-10

1 20655 676142884 05-SEP-10 676161224 05-SEP-10

1 20656 676161224 05-SEP-10 676178891 05-SEP-10

1 20657 676178891 05-SEP-10 676195036 05-SEP-10

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

152200 476.00M SBT_TAPE 00:02:10 05-SEP-10

BP Key: 152204 Status: AVAILABLE Compressed: NO Tag: TAG20100905T212

702

Handle: al_5904_1_728947623 Media:

List of Archived Logs in backup set 152200

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 20658 676195036 05-SEP-10 676217270 05-SEP-10

1 20659 676217270 05-SEP-10 676234683 05-SEP-10

1 20660 676234683 05-SEP-10 676254444 05-SEP-10

1 20661 676254444 05-SEP-10 676274108 05-SEP-10

1 20662 676274108 05-SEP-10 676291037 05-SEP-10

1 20663 676291037 05-SEP-10 676305509 05-SEP-10

1 20664 676305509 05-SEP-10 676322927 05-SEP-10

1 20665 676322927 05-SEP-10 676351397 05-SEP-10

1 20666 676351397 05-SEP-10 676526354 05-SEP-10

1 20667 676526354 05-SEP-10 676543636 05-SEP-10

1 20668 676543636 05-SEP-10 676569175 05-SEP-10

1 20669 676569175 05-SEP-10 676569211 05-SEP-10

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

152499 687.25M SBT_TAPE 00:01:13 06-SEP-10

BP Key: 152503 Status: AVAILABLE Compressed: NO Tag: TAG20100906T212

801

Handle: al_5917_1_729034081 Media:

List of Archived Logs in backup set 152499

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 20670 676569211 05-SEP-10 676716175 05-SEP-10

1 20671 676716175 05-SEP-10 676724745 06-SEP-10

1 20672 676724745 06-SEP-10 676885284 06-SEP-10

1 20673 676885284 06-SEP-10 676905415 06-SEP-10

1 20674 676905415 06-SEP-10 676923094 06-SEP-10

1 20675 676923094 06-SEP-10 676942976 06-SEP-10

1 20676 676942976 06-SEP-10 677097830 06-SEP-10

1 20677 677097830 06-SEP-10 677247249 06-SEP-10

1 20678 677247249 06-SEP-10 677261164 06-SEP-10

1 20679 677261164 06-SEP-10 677277774 06-SEP-10

1 20680 677277774 06-SEP-10 677293510 06-SEP-10

1 20681 677293510 06-SEP-10 677310453 06-SEP-10

1 20682 677310453 06-SEP-10 677328878 06-SEP-10

1 20683 677328878 06-SEP-10 677347699 06-SEP-10

1 20684 677347699 06-SEP-10 677365452 06-SEP-10

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

152500 671.25M SBT_TAPE 00:02:13 06-SEP-10

BP Key: 152504 Status: AVAILABLE Compressed: NO Tag: TAG20100906T212

801

Handle: al_5918_1_729034081 Media:

List of Archived Logs in backup set 152500

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 20685 677365452 06-SEP-10 677394448 06-SEP-10

1 20686 677394448 06-SEP-10 677409834 06-SEP-10

1 20687 677409834 06-SEP-10 677426170 06-SEP-10

1 20688 677426170 06-SEP-10 677440722 06-SEP-10

1 20689 677440722 06-SEP-10 677457081 06-SEP-10

1 20690 677457081 06-SEP-10 677470170 06-SEP-10

1 20691 677470170 06-SEP-10 677483727 06-SEP-10

1 20692 677483727 06-SEP-10 677496501 06-SEP-10

1 20693 677496501 06-SEP-10 677510607 06-SEP-10

1 20694 677510607 06-SEP-10 677522135 06-SEP-10

1 20695 677522135 06-SEP-10 677534305 06-SEP-10

1 20696 677534305 06-SEP-10 677549321 06-SEP-10

1 20697 677549321 06-SEP-10 677703740 06-SEP-10

1 20698 677703740 06-SEP-10 677717755 06-SEP-10

1 20699 677717755 06-SEP-10 677739134 06-SEP-10

1 20700 677739134 06-SEP-10 677739175 06-SEP-10

RMAN> run {

2> allocate channel ch00 type 'SBT_TAPE';

3> send 'NB_ORA_CLIENT=ora-rs';

4> validate backupset 152500;

5> release channel ch00;

6> }

allocated channel: ch00

channel ch00: sid=690 devtype=SBT_TAPE

channel ch00: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

sent command to channel: ch00

channel ch00: starting validation of archive log backupset

channel ch00: reading from backup piece al_5918_1_729034081

channel ch00: restored backup piece 1

piece handle=al_5918_1_729034081 tag=TAG20100906T212801

channel ch00: validation complete, elapsed time: 00:06:56

released channel: ch00

Configure Flashback Database

Configuring Flashback Database

The steps for configuring Flashback database option in  are:

Configure database to run in ARCHIVELOG mode.

Ensure the datbase is operating in ARCHIVELOG mode.


SQL> ARCHIVE LOG LIST

Database log mode            Archive Mode
Automatic archival              Enabled
Archive destination             /home/oracle/archiev1
Oldest online log sequence          5
Next log sequence to archive      7
Current log sequence                 7


If not, shutdown immediate, then startup mount, and alter the database

SQL> alter database archivelog;


Configure Flashbask when the instance in mount operating mode

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT
SQL> ALTER DATABASE FLASHBACK ON;

Database altered.


Configuring flashback database related parameters

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST =
          '/home/oracle/flash_recover_area' scope=both;


SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=both;


SQL> ALTER SYSTEM SET DB_FLASHBACK_RENTENTION_TARGET = 4320 SCOPE=both;


SQL> show parameter DB_FLASH

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     4320


SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/oracle/flash_recovery_area

db_recovery_file_dest_size           big integer 3G


Obtain SCN before initiating a database change.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     945046


Perform database change

SQL> drop table scott.salgrade;

Table dropped.


Performing  flashback database

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;

ORACLE instance started.
Total System Global Area  608174080 bytes
Fixed Size                  1281624 bytes
Variable Size             163578280 bytes
Database Buffers          436207616 bytes
Redo Buffers                7106560 bytes
Database mounted.



SQL> FLASHBACK DATABASE TO SCN 945046;

Flashback complete.

Open the Database with RESETLOGS.


SQL> alter database open resetlogs;

Database altered.

Confirm success of flashback.



SQL> select * from scott.salgrade;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1             700          1200
         2             1201        1400
         3             1401        2000
         4             2001        3000
         5             3001        9999