tape restore for oracle database take long time

[Deyaa]

Hi all ;

currently i take oracle backup to tape and conmpress it via netbackup so the total size approximatly 35 GB , then to test the database restore from the netbackup i delete a datafile called example and try to restore it from rman via the below script

run

{

allocate channel c1 device type sbt_tape;

restore datafile 5;

recover datafile 5;

sql 'alter database datafile 5 online';

}

but it take long time approximatly 2 hour to restore datafile 1GB ,and after diagnose we find that it restore all datafile 31GB compressed and uncomress them and restore the only datafile 5.

from job details:

04/29/2015 20:55:27 - Info bptm (pid=9757) DT0013
04/29/2015 20:55:27 - Info bptm (pid=9757) INF - Waiting for positioning of media id DT0013 on server netbackup-bck for reading.
04/29/2015 20:55:27 - positioning DT0013 to file 21
04/29/2015 20:56:43 - positioned DT0013; position time: 0:01:16
04/29/2015 20:56:43 - begin reading
04/29/2015 21:12:26 - positioning DT0013 to file 22
04/29/2015 21:12:26 - positioned DT0013; position time: 0:00:00
04/29/2015 21:27:47 - positioning DT0013 to file 23
04/29/2015 21:27:47 - positioned DT0013; position time: 0:00:00
04/29/2015 21:43:34 - positioning DT0013 to file 24
04/29/2015 21:43:34 - positioned DT0013; position time: 0:00:00
04/29/2015 21:59:44 - positioning DT0013 to file 25
04/29/2015 21:59:44 - positioned DT0013; position time: 0:00:00
04/29/2015 22:15:28 - positioning DT0013 to file 26
04/29/2015 22:15:28 - positioned DT0013; position time: 0:00:00
04/29/2015 22:31:25 - positioning DT0013 to file 27
04/29/2015 22:31:25 - positioned DT0013; position time: 0:00:00
04/29/2015 22:34:58 - Info dbclient (pid=7737) done. status: 0: the requested operation was successfully completed
04/29/2015 22:34:59 - Info bpbrm (pid=9755) sending message to media manager: STOP RESTORE dbserver-bck_1430330834 EXIT=0
04/29/2015 22:34:59 - end reading; read time: 1:38:16
04/29/2015 22:34:59 - restored from image dbserver-bck_1430330834; restore time: 1:40:45
04/29/2015 22:35:00 - end Restore; elapsed time 1:40:49
the requested operation was successfully completed  (0)

From rman:

/////////////////

  List of Archived Logs in backup set 20

  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time

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

  1    14      788279267  29-APR-2015 18:26:18 788336285  29-APR-2015 20:24:42

  1    15      788336285  29-APR-2015 20:24:42 788336303  29-APR-2015 20:24:42

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

21      Full    18.00M     SBT_TAPE    00:04:15     29-APR-2015 20:29:34

        BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20150429T202519

        Handle: ctrl_dSTD1_u0mq5kflf_s22_p1_t878329519   Media: DT0013

  Control File Included: Ckp SCN: 788336362    Ckp time: 29-APR-2015 20:25:19

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

22      Incr 0  80.58G     SBT_TAPE    00:31:13     29-APR-2015 21:34:56

        BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20150429T210342

        Handle: bk_dSTD1_u0nq5khtf_s23_p1_t878331823   Media: DT0013

List of Datafiles in backup set 22

  File LV Type Ckp SCN    Ckp Time             Name

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

  1    0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/system.284.863301143

  2    0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/sysaux.296.863301143

  3    0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/undotbs1.279.863297791

  4    0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.289.863297811

  5    0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/example.295.863301145

  6    0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/undotbs2.288.863297807

  7    0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/newind.294.863301161

  8    0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/newind.293.863301185

  9    0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/newind.292.863301349

  10   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.278.863297813

  11   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.277.863297815

  12   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.281.863297817

  13   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.280.863297821

  14   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.283.863297873

  15   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.282.863298181

  16   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.287.863300295

  17   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/newind.286.863301139

 18   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.285.863301141

  19   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/system.291.863301885

  20   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.290.863297809

  21   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.276.863302149

  22   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.297.863325391

  23   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.298.863326911

  24   0  Incr 788338466  29-APR-2015 21:03:43 /u01/app/oracle/oradata/STD1/datafile/users.299.871921675

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

23      Incr 0  18.25M     SBT_TAPE    00:00:25     29-APR-2015 21:35:24

        BP Key: 23   Status: AVAILABLE  Compressed: NO  Tag: TAG20150429T210342

        Handle: bk_dSTD1_u0oq5kjo3_s24_p1_t878333699   Media: DT0013

  SPFILE Included: Modification time: 29-APR-2015 19:56:17

  SPFILE db_unique_name: STD1

  Control File Included: Ckp SCN: 788339944    Ckp time: 29-APR-2015 21:34:56

BS Key  Size       Device Type Elapsed Time Completion Time

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

24      3.50M      SBT_TAPE    00:00:25     29-APR-2015 21:35:52

        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20150429T213527

        Handle: arch_dSTD1_u0pq5kjov_s25_p1_t878333727   Media: DT0013

  List of Archived Logs in backup set 24

  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time

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

  1    16      788336303  29-APR-2015 20:24:42 788339982  29-APR-2015 21:35:26

  1    17      788339982  29-APR-2015 21:35:26 788340000  29-APR-2015 21:35:26

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

25      Full    18.00M     SBT_TAPE    00:04:41     29-APR-2015 21:40:44

        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20150429T213602

        Handle: ctrl_dSTD1_u0qq5kjq3_s26_p1_t878333763   Media: DT0013

  Control File Included: Ckp SCN: 788340060    Ckp time: 29-APR-2015 21:36:03

///////////////////////////////////////////////////////

we can see from the above that all datafile are in backup set 22 , DBA said that netbackup restore all Datafiles in backup set 22  to restore only the needed datafile . 

can i divide these datafiles into more than one backup set ,so it restore only the  backup set needed ?

BR

Deyaa 

[Nicolai · Accepted solution]

when you mention Netbackup compression, is it the compression option in the policy ?

Tape drives does compression much better than Netbackup. Compression in Netbackup is software, tape drive is in hardware. Please disable policy compression is turned on.

2 hours of read time for 31GB is horrible slow - please try a backup to disk and re-try restore. I suspect you may have a tape drive issue.