Thinking Out Loud

March 28, 2017

RMAN-06820: WARNING: failed to archive current log at primary database

Filed under: 11g,RMAN — mdinh @ 1:18 pm

The best solution is a simple one.

Standard Edition Oracle with Manual Standby generating error – RMAN-06820.

From RMAN-06820 ORA-17629 During Backup at Standby Site (Doc ID 1616074.1):
As of 11.2.0.4, we now include the current standby redo log as part of an RMAN archivelog backup at the standby site.
This is achieved by forcing a log switch at the primary site.

Some options to remedy the issue which seems too complicated.
RMAN-06820 ORA-17629 ORA-12154 During Backup of a Standby Database (Doc ID 2025142.1)
OERR: RMAN-6613 “Connect identifier for DB_UNIQUE_NAME %s not configured” Reference Note (Doc ID 2050646.1)
RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured (Doc ID 1598653.1)

DEMO:

oracle@arrow2:HAWKB:/home/oracle
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 1 14:09:07 2016

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

connected to target database: HAWK (DBID=3187737370, not open)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_HAWKB.f'; # default

RMAN> CONFIGURE DB_UNIQUE_NAME 'HAWKB' CONNECT IDENTIFIER 'HAWKB';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 12/01/2016 14:09:26
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

RMAN> backup archivelog all not backed up 2 times tag='ALL';

Starting backup at 2016-DEC-01 14:09:53
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=342 RECID=274 STAMP=929450567
input archived log thread=1 sequence=343 RECID=275 STAMP=929451468
input archived log thread=1 sequence=344 RECID=276 STAMP=929452365
input archived log thread=1 sequence=345 RECID=277 STAMP=929453265
input archived log thread=1 sequence=346 RECID=278 STAMP=929454166
input archived log thread=1 sequence=347 RECID=279 STAMP=929455067
channel ORA_DISK_1: starting piece 1 at 2016-DEC-01 14:09:54
channel ORA_DISK_1: finished piece 1 at 2016-DEC-01 14:09:55
piece handle=/fra/HAWKB/backupset/2016_12_01/o1_mf_annnn_ALL_d417xlg1_.bkp tag=ALL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-DEC-01 14:09:55

RMAN> backup archivelog until time 'sysdate' not backed up 2 times tag='UNTIL';

Starting backup at 2016-DEC-01 14:10:22
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=342 RECID=274 STAMP=929450567
input archived log thread=1 sequence=343 RECID=275 STAMP=929451468
input archived log thread=1 sequence=344 RECID=276 STAMP=929452365
input archived log thread=1 sequence=345 RECID=277 STAMP=929453265
input archived log thread=1 sequence=346 RECID=278 STAMP=929454166
input archived log thread=1 sequence=347 RECID=279 STAMP=929455067
channel ORA_DISK_1: starting piece 1 at 2016-DEC-01 14:10:22
channel ORA_DISK_1: finished piece 1 at 2016-DEC-01 14:10:23
piece handle=/fra/HAWKB/backupset/2016_12_01/o1_mf_annnn_UNTIL_d417ygxb_.bkp tag=UNTIL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-DEC-01 14:10:23

RMAN> backup archivelog all not backed up 2 times tag='ALL';

Starting backup at 2016-DEC-01 14:10:26
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 342 to 347; already backed up
Finished backup at 2016-DEC-01 14:10:26

RMAN> backup archivelog until time 'sysdate' not backed up 2 times tag='UNTIL';

Starting backup at 2016-DEC-01 14:10:32
using channel ORA_DISK_1
skipping archived logs of thread 1 from sequence 342 to 347; already backed up
Finished backup at 2016-DEC-01 14:10:32

RMAN> exit


Recovery Manager complete.
oracle@arrow2:HAWKB:/home/oracle
$

Database Backup and Recovery User’s Guide (11.2)
Backing Up Archived Redo Logs with RMAN
https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmbckba.htm#BRADV81399

Online Redo Log Switch will occur with backup contains the following clause:
PLUS ARCHIVELOG
ARCHIVELOG ALL
ARCHIVELOG FROM ..

The simple solutions for not switching redo at primary.

Use backup archivelog until time ‘sysdate’ instead of backup archivelog all.
Use backup until sequence instead of from sequence.
Use backup database followed with backup archivelog until time ‘sysdate’.

UPDATE based on comments:

Using RMAN with sys password did not work. This test was not done by me but a teammate and I did not wanted to waste more of client’s time on the matter.

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 24 07:43:11 2016

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

RMAN> connect target sys

target database Password: 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12001: could not open channel default
RMAN-10008: could not create channel context
RMAN-10002: ORACLE error: ORA-15021: parameter "remote_dependencies_mode" is not valid in asm instance
RMAN-10006: error running SQL statement: alter session set remote_dependencies_mode = signature

RMAN> exit
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 24 07:43:36 2016

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

RMAN> connect target sys

target database Password: 
connected to target database: PROD (DBID=4122225506, not open)

RMAN> BACKUP AS COMPRESSED BACKUPSET filesperset 20 maxsetsize 16G tag rman_PROD_AL_TEST ARCHIVELOG FROM SEQUENCE 47341;

Starting backup at 24-NOV-16
using target database control file instead of recovery catalog
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database: 
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

allocated channel: ORA_DISK_1

December 16, 2016

RMAN MAXSETSIZE, MAXPIECESIZE, FILESPERSET Unveil

Filed under: 11g,RMAN — mdinh @ 2:14 am

First, any backup implementation should have recovery point and time objective.

Next, there’s no right or wrong, just what fits the requirements.

FILESPERSET controls maximum number of files for each backupset.
MAXPIECESIZE controls maximum size of backuppiece.
MAXSETSIZE controls maximum size of backupset.

Typically, if MAXSETSIZE is set, it should be equal to or greater than maximum size of all data files.
In general, it is not recommended to set MAXSETSIZE.

Backupset can contains 1 or more backuppiece; hence, why set a limitation on maxsetsize when it may be better to set limitation for maxpiecsize?

Justification for filesperset=1 is faster recovery and filesperset>1 is to reduce management of backup, e.g. crosscheck.

Consideration, backup versus restore frequencies.

Here’s an analogy, you are at bank withdrawing $2000 and the teller ask, how do you want the bill?
Would you like 20 $100 bills or multiple small bills 1,5,10,20 or combination of there of?

Same concept applies to backupset and filesperset.

Note: FILESPERSET and MAXOPENFILES affect multiplexing, number of buffers, size of each buffer and is not the scope of this blog post.

Actual results for TB size DB.
Note: the objective is not to improve backup time performance but to reduce number of backuppiece.

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
MAXPIECESIZE 30 G MAXOPENFILES 1;
Increase filesperset 8 from filesperset 1.

+++ DEC 14 Level 1 backup (before)
Completed at: 14-DEC-2016 20:12:49
187 backuppiece
923352M total

+++ DEC 15 Level 1 backup (after)
Competed at: 15-DEC-2016 20:11:36
83 backuppiece
1012399M total

If there are more than 1 backuppiece per backupset, this would be the expected results.
63M     /oradata/backup/HAWK_3183859104_20161115_1irkveig_1_1.bkp
63M     /oradata/backup/HAWK_3183859104_20161115_1irkveig_2_1.bkp
15M     /oradata/backup/HAWK_3183859104_20161115_1irkveig_3_1.bkp

+++ Interesting to find there is only 1 backuppiece per backupset. 
du -sc -B 1073741824 *2092127717_20161215*|sort -n
1	2092127717_20161215_rhrni9ns_1_1
1	2092127717_20161215_rlrni9oc_1_1
1	2092127717_20161215_rrrni9qo_1_1
1	2092127717_20161215_rvrni9ro_1_1
1	2092127717_20161215_s0rni9rv_1_1
1	2092127717_20161215_s2rni9s8_1_1
1	2092127717_20161215_s4rni9sm_1_1
1	2092127717_20161215_s5rni9t5_1_1
1	2092127717_20161215_s8rni9tl_1_1
1	2092127717_20161215_s9rni9tl_1_1
1	2092127717_20161215_serni9v2_1_1
1	2092127717_20161215_sgrni9vb_1_1
1	2092127717_20161215_shrni9vq_1_1
1	2092127717_20161215_skrnia0b_1_1
1	2092127717_20161215_slrnia1k_1_1
1	2092127717_20161215_smrnia1k_1_1
1	2092127717_20161215_srrnia3f_1_1
1	2092127717_20161215_ssrnia3h_1_1
2	2092127717_20161215_rfrni9ns_1_1
2	2092127717_20161215_rgrni9ns_1_1
2	2092127717_20161215_rirni9oc_1_1
2	2092127717_20161215_rkrni9oc_1_1
2	2092127717_20161215_rmrni9or_1_1
2	2092127717_20161215_rtrni9r8_1_1
2	2092127717_20161215_rurni9r8_1_1
2	2092127717_20161215_s1rni9s6_1_1
2	2092127717_20161215_s6rni9t5_1_1
2	2092127717_20161215_s7rni9t5_1_1
2	2092127717_20161215_scrni9tv_1_1
2	2092127717_20161215_sirni9vr_1_1
2	2092127717_20161215_sjrnia0a_1_1
2	2092127717_20161215_sornia24_1_1
2	2092127717_20161215_sqrnia2b_1_1
3	2092127717_20161215_rerni9ns_1_1
3	2092127717_20161215_rnrni9os_1_1
3	2092127717_20161215_s3rni9sf_1_1
3	2092127717_20161215_sarni9tl_1_1
3	2092127717_20161215_sdrni9v2_1_1
3	2092127717_20161215_snrnia1k_1_1
4	2092127717_20161215_sprnia24_1_1
5	2092127717_20161215_rprni9pl_1_1
5	2092127717_20161215_rqrni9pl_1_1
9	2092127717_20161215_t6rnigpe_1_1
10	2092127717_20161215_rjrni9oc_1_1
11	2092127717_20161215_t1rnia4m_1_1
11	2092127717_20161215_t3rnigpe_1_1
11	2092127717_20161215_t4rnigpe_1_1
11	2092127717_20161215_t5rnigpe_1_1
15	2092127717_20161215_t0rnia4l_1_1
17	2092127717_20161215_surnia4l_1_1
17	2092127717_20161215_svrnia4l_1_1
19	2092127717_20161215_qorngs1e_1_1
20	2092127717_20161215_qerng6ug_1_1
21	2092127717_20161215_rorni9os_1_1
21	2092127717_20161215_rsrni9qp_1_1
21	2092127717_20161215_sfrni9v4_1_1
22	2092127717_20161215_r7rnhrm3_1_1
23	2092127717_20161215_qdrng6ug_1_1
23	2092127717_20161215_r2rnhh4i_1_1
24	2092127717_20161215_qbrng6ug_1_1
24	2092127717_20161215_qcrng6ug_1_1
24	2092127717_20161215_sbrni9tt_1_1
25	2092127717_20161215_qjrnghfr_1_1
25	2092127717_20161215_qnrngs1e_1_1
25	2092127717_20161215_rbrni67g_1_1
25	2092127717_20161215_rcrni67g_1_1
27	2092127717_20161215_qlrngs1d_1_1
27	2092127717_20161215_qmrngs1d_1_1
28	2092127717_20161215_qhrnghfr_1_1
28	2092127717_20161215_qirnghfr_1_1
28	2092127717_20161215_r6rnhrm3_1_1
28	2092127717_20161215_r9rni67g_1_1
28	2092127717_20161215_rarni67g_1_1
30	2092127717_20161215_qgrnghfr_1_1
30	2092127717_20161215_qtrnh6j5_1_1
30	2092127717_20161215_r5rnhrm3_1_1
31	2092127717_20161215_qqrnh6j5_1_1
31	2092127717_20161215_qrrnh6j5_1_1
31	2092127717_20161215_qsrnh6j5_1_1
31	2092127717_20161215_qvrnhh4i_1_1
31	2092127717_20161215_r0rnhh4i_1_1
31	2092127717_20161215_r1rnhh4i_1_1
31	2092127717_20161215_r4rnhrm3_1_1
989	total

From the DEMO, there is only 1 backuppiece for each backupset and depending on maxsetsize, the number of backupsets created is different.

MAX DATAFILE SIZE IS 513M

ARROW1:(SYS@HAWKA):PRIMARY> select max(bytes)/1024/1024 from v$datafile;

MAX(BYTES)/1024/1024
--------------------
                 513

ARROW1:(SYS@HAWKA):PRIMARY> select name, bytes/1024/1024 from v$datafile;

NAME                                                    BYTES/1024/1024
------------------------------------------------------- ---------------
/oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf                  513
/oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf                  257
/oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf                 256
/oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf                   129
/oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf                    16

ARROW1:(SYS@HAWKA):PRIMARY>

Notice the distribution and size of datafiles in backupsets.

CONFIGURE MAXSETSIZE TO UNLIMITED

PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
4 BACKUPSETS CREATED FOR LEVEL0 (2 datafiles, 1 controlfile, 1 spfile)
4 BACKUPSETS CREATED FOR LEVEL1
8 BACKUPSETS TOTAL SIZE 413680k
---------------------------------------------------------------------------
Datafiles backupset 1 (181.24M)
  2    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf (257M)
  3    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf(256M)
  4    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf  (129M)

NOTICE: sum of datafiles > 513M since maxsetsize is unlimited and backuppiece < 513M
Datafiles backupset 2 (190.24M)
  1    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf (513M)
  5    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf  ( 16M)

CONFIGURE MAXSETSIZE TO 513 M

PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
5 BACKUPSETS CREATED FOR LEVEL0 (3 datafiles, 1 controlfile, 1 spfile)
5 BACKUPSETS CREATED FOR LEVEL1
10 BACKUPSETS TOTAL SIZE 413808k
---------------------------------------------------------------------------
NOTICE: backupset is only 3.59M and no other datafiles can be considered since it would exceed maxsetsize of 513M
Datafiles backupset 1 (3.59M) 
  3    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf (256M)
  4    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf   (129M)

Before backup, RMAN does not know datafile usage and it would be too time consumsing to check. Only datafile size is checked.
RMAN performed NULL compression since backupset is 189M and datafile is 513M.

NOTICE: sum of datafiles = 513M, maxsetsize is 513M, backuppiece < 513M
Datafiles backupset 2 (189.23M)
  1    0  Incr 1706518    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf  (513M)

Datafiles backupset 3 (178.68M)
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf  (257M)
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf   ( 16M)

All the details.

CONFIGURE MAXSETSIZE TO UNLIMITED

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oradata/backup/%d_%I_%T_%U.bkp' MAXPIECESIZE 513 M MAXOPENFILES 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_HAWKA.f'; # default

RMAN> backup incremental level 0 database filesperset 4 tag 'L0_MAX';
RMAN> list backup summary tag L0_MAX;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
213     B  0  A DISK        2016-DEC-14 19:38:43 1       1       NO         L0_MAX
214     B  0  A DISK        2016-DEC-14 19:38:43 1       1       NO         L0_MAX
215     B  0  A DISK        2016-DEC-14 19:38:45 1       1       NO         L0_MAX
216     B  0  A DISK        2016-DEC-14 19:38:45 1       1       NO         L0_MAX

RMAN> list backup tag L0_MAX;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
213     Incr 0  181.24M    DISK        00:00:02     2016-DEC-14 19:38:43
        BP Key: 243   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6prnfjq1_1_1.bkp
  List of Datafiles in backup set 213
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf 
  3    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    0  Incr 1706145    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf  
  
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
214     Incr 0  190.24M    DISK        00:00:02     2016-DEC-14 19:38:43
        BP Key: 244   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6qrnfjq1_1_1.bkp
  List of Datafiles in backup set 214
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf
  5    0  Incr 1706146    2016-DEC-14 19:38:41 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
215     Incr 0  80.00K     DISK        00:00:00     2016-DEC-14 19:38:45
        BP Key: 245   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6srnfjq5_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
216     Incr 0  16.08M     DISK        00:00:00     2016-DEC-14 19:38:45
        BP Key: 246   Status: AVAILABLE  Compressed: NO  Tag: L0_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6rrnfjq5_1_1.bkp
  Control File Included: Ckp SCN: 1706149      Ckp time: 2016-DEC-14 19:38:45

RMAN> backup incremental level 1 database filesperset 8 tag 'L1_MAX';
RMAN> list backup summary tag L1_MAX;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
217     B  1  A DISK        2016-DEC-14 19:39:14 1       1       NO         L1_MAX
218     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX
219     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX
220     B  1  A DISK        2016-DEC-14 19:39:15 1       1       NO         L1_MAX

RMAN> list backup tag L1_MAX;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
217     Incr 1  40.00K     DISK        00:00:02     2016-DEC-14 19:39:14
        BP Key: 247   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6urnfjr0_1_1.bkp
  List of Datafiles in backup set 217
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    1  Incr 1706213    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf
  5    1  Incr 1706213    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
218     Incr 1  48.00K     DISK        00:00:03     2016-DEC-14 19:39:15
        BP Key: 248   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6trnfjr0_1_1.bkp
  List of Datafiles in backup set 218
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  3    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    1  Incr 1706212    2016-DEC-14 19:39:12 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
219     Incr 1  80.00K     DISK        00:00:00     2016-DEC-14 19:39:15
        BP Key: 249   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_70rnfjr3_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
220     Incr 1  16.08M     DISK        00:00:00     2016-DEC-14 19:39:15
        BP Key: 250   Status: AVAILABLE  Compressed: NO  Tag: L1_MAX
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_6vrnfjr3_1_1.bkp
  Control File Included: Ckp SCN: 1706215      Ckp time: 2016-DEC-14 19:39:15

RMAN> exit

oracle@arrow1:HAWKA:/home/oracle
$ du -skc /oradata/backup/HAWK_3187737370_20161214*|sort -n
48      /oradata/backup/HAWK_3187737370_20161214_6urnfjr0_1_1.bkp
56      /oradata/backup/HAWK_3187737370_20161214_6trnfjr0_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_6srnfjq5_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_70rnfjr3_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_6rrnfjq5_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_6vrnfjr3_1_1.bkp
185604  /oradata/backup/HAWK_3187737370_20161214_6prnfjq1_1_1.bkp 
194820  /oradata/backup/HAWK_3187737370_20161214_6qrnfjq1_1_1.bkp
413680  total
oracle@arrow1:HAWKA:/home/oracle
$

MAX DATAFILE SIZE IS 513M AND CONFIGURE MAXSETSIZE TO 513 M

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWKA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oradata/backup/%d_%I_%T_%U.bkp' MAXPIECESIZE 513 M MAXOPENFILES 1;
CONFIGURE MAXSETSIZE TO 513 M;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_HAWKA.f'; # default

RMAN> backup incremental level 0 database filesperset 4 tag 'L0_513';
RMAN> list backup summary tag L0_513;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
221     B  0  A DISK        2016-DEC-14 19:42:36 1       1       NO         L0_513
222     B  0  A DISK        2016-DEC-14 19:42:37 1       1       NO         L0_513
223     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513
224     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513
225     B  0  A DISK        2016-DEC-14 19:42:38 1       1       NO         L0_513

RMAN> list backup tag L0_513;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
221     Incr 0  3.59M      DISK        00:00:00     2016-DEC-14 19:42:36
        BP Key: 251   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_72rnfk1c_1_1.bkp
  List of Datafiles in backup set 221
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    0  Incr 1706519    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
222     Incr 0  189.23M    DISK        00:00:01     2016-DEC-14 19:42:37
        BP Key: 252   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_71rnfk1c_1_1.bkp
  List of Datafiles in backup set 222
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    0  Incr 1706518    2016-DEC-14 19:42:36 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
223     Incr 0  178.68M    DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 253   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
  List of Datafiles in backup set 223
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
224     Incr 0  80.00K     DISK        00:00:00     2016-DEC-14 19:42:38
        BP Key: 254   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_75rnfk1e_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
225     Incr 0  16.08M     DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 255   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_74rnfk1d_1_1.bkp
  Control File Included: Ckp SCN: 1706522      Ckp time: 2016-DEC-14 19:42:37

RMAN> backup incremental level 1 database filesperset 8 tag 'L1_513';
RMAN> list backup summary tag L1_513;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
226     B  1  A DISK        2016-DEC-14 19:44:20 1       1       NO         L1_513
227     B  1  A DISK        2016-DEC-14 19:44:20 1       1       NO         L1_513
228     B  1  A DISK        2016-DEC-14 19:44:22 1       1       NO         L1_513
229     B  1  A DISK        2016-DEC-14 19:44:23 1       1       NO         L1_513
230     B  1  A DISK        2016-DEC-14 19:44:23 1       1       NO         L1_513

RMAN> list backup tag L1_513;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
226     Incr 1  56.00K     DISK        00:00:01     2016-DEC-14 19:44:20
        BP Key: 256   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_77rnfk4j_1_1.bkp
  List of Datafiles in backup set 226
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  3    1  Incr 1706668    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_undotbs_d3q59m1h_.dbf
  4    1  Incr 1706668    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
227     Incr 1  32.00K     DISK        00:00:01     2016-DEC-14 19:44:20
        BP Key: 257   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_76rnfk4j_1_1.bkp
  List of Datafiles in backup set 227
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    1  Incr 1706667    2016-DEC-14 19:44:19 /oradata/HAWKA/datafile/o1_mf_system_d3q57w8h_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
228     Incr 1  96.00K     DISK        00:00:00     2016-DEC-14 19:44:22
        BP Key: 258   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_78rnfk4m_1_1.bkp
  List of Datafiles in backup set 228
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    1  Incr 1706671    2016-DEC-14 19:44:22 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    1  Incr 1706671    2016-DEC-14 19:44:22 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
229     Incr 1  80.00K     DISK        00:00:00     2016-DEC-14 19:44:23
        BP Key: 259   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_7arnfk4n_1_1.bkp
  SPFILE Included: Modification time: 2016-DEC-14 19:02:12
  SPFILE db_unique_name: HAWKA

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
230     Incr 1  16.08M     DISK        00:00:01     2016-DEC-14 19:44:23
        BP Key: 260   Status: AVAILABLE  Compressed: NO  Tag: L1_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_79rnfk4m_1_1.bkp
  Control File Included: Ckp SCN: 1706671      Ckp time: 2016-DEC-14 19:44:22

RMAN> list backuppiece '/oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp';

using target database control file instead of recovery catalog

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
253     223     1   1   AVAILABLE   DISK        /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp

RMAN> list backupset 223;

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
223     Incr 0  178.68M    DISK        00:00:01     2016-DEC-14 19:42:38
        BP Key: 253   Status: AVAILABLE  Compressed: NO  Tag: L0_513
        Piece Name: /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
  List of Datafiles in backup set 223
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_sysaux_d3q594c1_.dbf
  5    0  Incr 1706521    2016-DEC-14 19:42:37 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf

RMAN>

$ du -skc /oradata/backup/HAWK_3187737370_20161214*|sort -n
40      /oradata/backup/HAWK_3187737370_20161214_76rnfk4j_1_1.bkp
64      /oradata/backup/HAWK_3187737370_20161214_77rnfk4j_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_75rnfk1e_1_1.bkp
96      /oradata/backup/HAWK_3187737370_20161214_7arnfk4n_1_1.bkp
104     /oradata/backup/HAWK_3187737370_20161214_78rnfk4m_1_1.bkp
3688    /oradata/backup/HAWK_3187737370_20161214_72rnfk1c_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_74rnfk1d_1_1.bkp
16480   /oradata/backup/HAWK_3187737370_20161214_79rnfk4m_1_1.bkp
182976  /oradata/backup/HAWK_3187737370_20161214_73rnfk1d_1_1.bkp
193784  /oradata/backup/HAWK_3187737370_20161214_71rnfk1c_1_1.bkp
413808  total

November 26, 2016

Online Redo Log Switching from RMAN Backup

Filed under: 11g,RMAN — mdinh @ 5:28 pm

I was troubleshooting backup from standby databases and encountered an oddity which I wanted to verify.

Backing Up Archived Redo Logs with RMAN

Before beginning the backup, RMAN switches out of the current redo log group, and archives all online redo logs that have not yet been archived, up to and including the redo log group that was current when BACKUP command with any of the following clauses:
PLUS ARCHIVELOG
ARCHIVELOG ALL
ARCHIVELOG FROM …

 

 

July 9, 2016

RMAN 12c NF – SQL interface in RMAN


Filed under: 12c,RMAN,shell scripting — mdinh @ 11:51 pm

Admittedly, I did not realize the benefit at first until there came a need.

Example:


# Set ORACLE_SID
ORACLE_SID=$1
export ORACLE_SID
### This retrieve the numeric value from PROD1/PROD2, i.e. 1,2 respectively
n=`echo "${ORACLE_SID:(-1)}"`

run {
backup tag 'ARCHIVED_LOG' archivelog all delete input skip inaccessible;
alter database backup controlfile to trace as '/rman_bkup$n/$ORACLE_SID/cf_@.sql' REUSE RESETLOGS;
create pfile='/rman_bkup$n/$ORACLE_SID/init@.ora' from spfile;
create pfile from spfile;
}

 

March 4, 2016

A Better Diff

Filed under: 11g,linux,oracle,RMAN — mdinh @ 4:25 am

I have been working on simplifying, perfecting, and comparing RMAN backup scripts.

The typical diff file1 file2 was not useful as I wanted a complete picture.

Look at what I found!

$ diff -iwyB --suppress-common-lines -W 150 rman_bkupinc.sh rman_bkuparc.sh;echo
# RMAN database incremental backup                                        |     # RMAN archivelog backup
# rman_bkupinc.sh                                                         |     # rman_bkuparc.sh
# Shell script calls bkupinc.rman at at SCRIPT_DIR location               |     # Shell script calls bkuparc.rman at at SCRIPT_DIR location
SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"}                        |     SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID"}
LVL=${2:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"}                        <
DAY=`date '+%bW%U'`                                                       |     DAY=`date '+%aH%H'`
RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.L$2.$DAY.log       |     RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.$DAY.log
rman @${RMAN_SCRIPT} ${LVL} msglog $RMAN_LOG                              |     rman @${RMAN_SCRIPT} msglog $RMAN_LOG

$ diff -iwy -W 150 rman_bkupinc.sh rman_bkuparc.sh;echo
#!/bin/sh -ex                                                                   #!/bin/sh -ex
# Michael Dinh: Mar 03, 2016                                                    # Michael Dinh: Mar 03, 2016
# RMAN database incremental backup                                        |     # RMAN archivelog backup
# rman_bkupinc.sh                                                         |     # rman_bkuparc.sh
# Shell script calls bkupinc.rman at at SCRIPT_DIR location               |     # Shell script calls bkuparc.rman at at SCRIPT_DIR location

DN=`dirname $0`                                                                 DN=`dirname $0`
BN=`basename $0`                                                                BN=`basename $0`
SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"}                        |     SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID"}
LVL=${2:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"}                        <

set -a                                                                          set -a
### Edit for proper location                                                    ### Edit for proper location
SCRIPT_DIR=/media/sf_working/rman                                               SCRIPT_DIR=/media/sf_working/rman
LOG_DIR=/tmp                                                                    LOG_DIR=/tmp
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin               PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
LD_LIBRARY_PATH=/lib:/usr/lib                                                   LD_LIBRARY_PATH=/lib:/usr/lib

### Edit resync_catalog.rman                                                    ### Edit resync_catalog.rman
### Uncomment if catalog is being used                                          ### Uncomment if catalog is being used
# RESYNC_CATALOG=$SCRIPT_DIR/resync_catalog.rman                                # RESYNC_CATALOG=$SCRIPT_DIR/resync_catalog.rman

ORACLE_SID=$1                                                                   ORACLE_SID=$1
ORAENV_ASK=NO                                                                   ORAENV_ASK=NO
. oraenv                                                                        . oraenv
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"                                        NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
DAY=`date '+%bW%U'`                                                       |     DAY=`date '+%aH%H'`
RMAN_SCRIPT=$SCRIPT_DIR/`echo $BN|cut -d'.' -f1|cut -c6-`.rman                  RMAN_SCRIPT=$SCRIPT_DIR/`echo $BN|cut -d'.' -f1|cut -c6-`.rman
RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.L$2.$DAY.log       |     RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.$DAY.log
TMPLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.log                            TMPLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.log
ERRLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.err                            ERRLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.err
set +a                                                                          set +a

# Lock file                                                                     # Lock file
exec 200>/tmp/$BN.lck                                                           exec 200>/tmp/$BN.lck
flock -n 200 || exit 1                                                          flock -n 200 || exit 1

# RMAN archivelog backup                                                        # RMAN archivelog backup
rman @${RMAN_SCRIPT} ${LVL} msglog $RMAN_LOG                              |     rman @${RMAN_SCRIPT} msglog $RMAN_LOG

### Uncomment if catalog is being used                                          ### Uncomment if catalog is being used
# rman @${RESYNC_CATALOG} msglog $RMAN_LOG append                               # rman @${RESYNC_CATALOG} msglog $RMAN_LOG append

cp -v $RMAN_LOG $TMPLOG                                                         cp -v $RMAN_LOG $TMPLOG
egrep -i '^rman-|^ora-|error|fail' $RMAN_LOG > $ERRLOG                          egrep -i '^rman-|^ora-|error|fail' $RMAN_LOG > $ERRLOG
exit                                                                            exit


$ ll /tmp/rman*.*
-rw-r--r--. 1 oracle oinstall     0 Mar  3 17:19 /tmp/rman_bkuparc_hawklas.err
-rw-r--r--. 1 oracle oinstall  7910 Mar  3 17:19 /tmp/rman_bkuparc_hawklas.log
-rw-r--r--. 1 oracle oinstall  7910 Mar  3 17:19 /tmp/rman_bkuparc.hawklas.ThuH17.log
-rw-r--r--. 1 oracle oinstall     0 Mar  3 17:19 /tmp/rman_bkuparc.sh.lck
-rw-r--r--. 1 oracle oinstall     0 Mar  3 19:20 /tmp/rman_bkupinc_hawklas.err
-rw-r--r--. 1 oracle oinstall 15315 Mar  3 19:20 /tmp/rman_bkupinc.hawklas.L1.MarW09.log
-rw-r--r--. 1 oracle oinstall 15315 Mar  3 19:20 /tmp/rman_bkupinc_hawklas.log
-rw-r--r--. 1 oracle oinstall     0 Mar  3 19:20 /tmp/rman_bkupinc.sh.lck

February 27, 2016

Revised RMAN Backup Size

Filed under: 11g,oracle,RMAN — mdinh @ 7:25 pm

Long, long ago, I posted https://mdinh.wordpress.com/2013/10/31/whats-rman-backup-size/ and recently discovered  the SQL was incorrect.

Current SQL does not include backup_copy as it got a little complicated to create an all inclusive test case – being sick and lazy.

oracle@arrow:hawklas:/media/sf_working/rman
$ sysdba @bkupinfo.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 27 10:51:17 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


COMPLETION_TIME     BS_KEY TYPE        BP_KEY  BP_MB PIECES SET_COUNT PIECE# COM DEVICE_TYPE
------------------- ------ ----------- ------ ------ ------ --------- ------ --- -----------------
HANDLE
----------------------------------------------------------------------------------------------------
2016-02-24 18:43:02   1723 FULL          2058   25.6      1      1800      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_o8quog1h_1_1_s1800_p1

2016-02-24 18:43:12   1724 FULL          2059  40.88      1      1802      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_oaquog1p_1_1_s1802_p1

2016-02-24 18:43:22   1725 FULL          2060 109.32      1      1801      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_o9quog1i_1_1_s1801_p1

2016-02-24 18:43:25   1726 FULL          2061   8.48      1      1804      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_ocquog2b_1_1_s1804_p1

2016-02-24 18:43:33   1727 FULL          2062  59.28      1      1803      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_obquog2a_1_1_s1803_p1

2016-02-24 18:43:39   1728 ARCHIVELOG    2063    .02      1      1805      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_odquog2r_1_1_s1805_p1

2016-02-24 18:43:44   1729 CONTROLFILE   2064  11.23      1      1806      1 NO  DISK
/oradata/backup/HAWK_c-3130551611-20160224-0e

2016-02-27 09:57:49   1730 ARCHIVELOG    2065   3.39      1      1807      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ofquvecs_1_1_s1807_p1

2016-02-27 09:57:55   1731 ARCHIVELOG    2066  29.52      1      1808      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ogquvecs_1_1_s1808_p1

2016-02-27 09:57:55   1732 ARCHIVELOG    2067  13.55      1      1809      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ohquvecv_1_1_s1809_p1

2016-02-27 09:59:03   1733 LEVEL0        2068  25.78      1      1810      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_oiquved7_1_1_s1810_p1

2016-02-27 09:59:35   1734 LEVEL0        2069  30.08      4      1811      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ojquved7_1_1_s1811_p1

2016-02-27 09:59:35   1734 LEVEL0        2070  30.04      4      1811      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ojquved7_2_1_s1811_p2

2016-02-27 09:59:35   1734 LEVEL0        2071  30.09      4      1811      3 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ojquved7_3_1_s1811_p3

2016-02-27 09:59:35   1734 LEVEL0        2072  21.52      4      1811      4 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ojquved7_4_1_s1811_p4

2016-02-27 09:59:46   1735 LEVEL0        2073   30.3      2      1812      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_okquvefb_1_1_s1812_p1

2016-02-27 09:59:46   1735 LEVEL0        2074  11.87      2      1812      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_okquvefb_2_1_s1812_p2

2016-02-27 09:59:57   1736 LEVEL0        2075   8.51      1      1814      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_omquvego_1_1_s1814_p1

2016-02-27 10:00:02   1737 LEVEL0        2076  30.44      2      1813      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_olquveg9_1_1_s1813_p1

2016-02-27 10:00:02   1737 LEVEL0        2077  29.42      2      1813      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_olquveg9_2_1_s1813_p2

2016-02-27 10:00:09   1738 ARCHIVELOG    2078    .07      1      1815      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_onquveh9_1_1_s1815_p1

2016-02-27 10:00:14   1739 CONTROLFILE   2079  11.23      1      1816      1 NO  DISK
/oradata/backup/HAWK_c-3130551611-20160227-00

2016-02-27 10:02:49   1740 ARCHIVELOG    2080  11.42      1      1817      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_opquvem5_1_1_s1817_p1.bus

2016-02-27 10:02:53   1741 ARCHIVELOG    2081   1.77      1      1819      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_orquvemc_1_1_s1819_p1.bus

2016-02-27 10:02:57   1742 ARCHIVELOG    2082  14.07      3      1818      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_oqquvem5_1_1_s1818_p1.bus

2016-02-27 10:02:57   1742 ARCHIVELOG    2083  14.07      3      1818      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_oqquvem5_2_1_s1818_p2.bus

2016-02-27 10:02:57   1742 ARCHIVELOG    2084   5.38      3      1818      3 YES DISK
/oradata/backup/HAWK_3130551611_20160227_oqquvem5_3_1_s1818_p3.bus

2016-02-27 10:04:15   1743 LEVEL0        2085  14.15      2      1820      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_osquvemj_1_1_s1820_p1.bus

2016-02-27 10:04:15   1743 LEVEL0        2086  12.41      2      1820      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_osquvemj_2_1_s1820_p2.bus

2016-02-27 10:05:04   1744 LEVEL0        2087  14.17      4      1822      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ouquvep1_1_1_s1822_p1.bus

2016-02-27 10:05:04   1744 LEVEL0        2088  14.15      4      1822      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ouquvep1_2_1_s1822_p2.bus

2016-02-27 10:05:04   1744 LEVEL0        2089  14.02      4      1822      3 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ouquvep1_3_1_s1822_p3.bus

2016-02-27 10:05:04   1744 LEVEL0        2090    1.7      4      1822      4 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ouquvep1_4_1_s1822_p4.bus

2016-02-27 10:05:07   1745 LEVEL0        2091  14.01      9      1821      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_1_1_s1821_p1.bus

2016-02-27 10:05:07   1745 LEVEL0        2092  14.05      9      1821      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_2_1_s1821_p2.bus

2016-02-27 10:05:07   1745 LEVEL0        2093  14.01      9      1821      3 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_3_1_s1821_p3.bus

2016-02-27 10:05:07   1745 LEVEL0        2094  14.13      9      1821      4 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_4_1_s1821_p4.bus

2016-02-27 10:05:07   1745 LEVEL0        2095  14.11      9      1821      5 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_5_1_s1821_p5.bus

2016-02-27 10:05:07   1745 LEVEL0        2096  14.11      9      1821      6 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_6_1_s1821_p6.bus

2016-02-27 10:05:07   1745 LEVEL0        2097  14.05      9      1821      7 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_7_1_s1821_p7.bus

2016-02-27 10:05:07   1745 LEVEL0        2098  14.02      9      1821      8 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus

2016-02-27 10:05:07   1745 LEVEL0        2099   3.46      9      1821      9 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_9_1_s1821_p9.bus

2016-02-27 10:05:20   1746 LEVEL0        2100   8.51      1      1824      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_p0quveqo_1_1_s1824_p1.bus

2016-02-27 10:05:57   1747 LEVEL0        2101  14.17      5      1823      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ovquveqn_1_1_s1823_p1.bus

2016-02-27 10:05:57   1747 LEVEL0        2102   14.2      5      1823      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ovquveqn_2_1_s1823_p2.bus

2016-02-27 10:05:57   1747 LEVEL0        2103  14.09      5      1823      3 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ovquveqn_3_1_s1823_p3.bus

2016-02-27 10:05:57   1747 LEVEL0        2104  14.23      5      1823      4 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ovquveqn_4_1_s1823_p4.bus

2016-02-27 10:05:57   1747 LEVEL0        2105    5.6      5      1823      5 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ovquveqn_5_1_s1823_p5.bus

2016-02-27 10:06:07   1748 ARCHIVELOG    2106   5.01      1      1825      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_p1quvese_1_1_s1825_p1.bus

2016-02-27 10:06:14   1749 CONTROLFILE   2107  11.23      1      1826      1 NO  DISK
/oradata/backup/HAWK_c-3130551611-20160227-01

                                              ------


sum                                           880.92



50 rows selected.

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Let’t take a look at backupset 1745 and backuppiece 2098 from RMAN.

Note BS Key is not the same as SET_COUNT (which is used to label backuppiece)

BS Key = 1745 while SET_COUNT = s1821

oracle@arrow:hawklas:/media/sf_working/rman
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 27 10:51:22 2016

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

connected to target database: HAWK (DBID=3130551611)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1723    B  F  A DISK        2016-FEB-24 18:43:02 1       1       YES        FULL
1724    B  F  A DISK        2016-FEB-24 18:43:12 1       1       YES        FULL
1725    B  F  A DISK        2016-FEB-24 18:43:22 1       1       YES        FULL
1726    B  F  A DISK        2016-FEB-24 18:43:25 1       1       YES        FULL
1727    B  F  A DISK        2016-FEB-24 18:43:33 1       1       YES        FULL
1728    B  A  A DISK        2016-FEB-24 18:43:39 1       1       YES        ARCHIVELOG
1729    B  F  A DISK        2016-FEB-24 18:43:44 1       1       NO         TAG20160224T184341
1730    B  A  A DISK        2016-FEB-27 09:57:49 1       1       YES        ARCHIVELOG
1731    B  A  A DISK        2016-FEB-27 09:57:55 1       1       YES        ARCHIVELOG
1732    B  A  A DISK        2016-FEB-27 09:57:55 1       1       YES        ARCHIVELOG
1733    B  0  A DISK        2016-FEB-27 09:59:03 1       1       YES        LEVEL0
1734    B  0  A DISK        2016-FEB-27 09:59:35 4       1       YES        LEVEL0
1735    B  0  A DISK        2016-FEB-27 09:59:46 2       1       YES        LEVEL0
1736    B  0  A DISK        2016-FEB-27 09:59:57 1       1       YES        LEVEL0
1737    B  0  A DISK        2016-FEB-27 10:00:02 2       1       YES        LEVEL0
1738    B  A  A DISK        2016-FEB-27 10:00:09 1       1       YES        ARCHIVELOG
1739    B  F  A DISK        2016-FEB-27 10:00:14 1       1       NO         TAG20160227T100010
1740    B  A  A DISK        2016-FEB-27 10:02:49 1       1       YES        ARCHIVELOG
1741    B  A  A DISK        2016-FEB-27 10:02:53 1       1       YES        ARCHIVELOG
1742    B  A  A DISK        2016-FEB-27 10:02:57 3       1       YES        ARCHIVELOG
1743    B  0  A DISK        2016-FEB-27 10:04:15 2       1       YES        LEVEL0
1744    B  0  A DISK        2016-FEB-27 10:05:04 4       1       YES        LEVEL0
1745    B  0  A DISK        2016-FEB-27 10:05:07 9       1       YES        LEVEL0
1746    B  0  A DISK        2016-FEB-27 10:05:20 1       1       YES        LEVEL0
1747    B  0  A DISK        2016-FEB-27 10:05:57 5       1       YES        LEVEL0
1748    B  A  A DISK        2016-FEB-27 10:06:07 1       1       YES        ARCHIVELOG
1749    B  F  A DISK        2016-FEB-27 10:06:14 1       1       NO         TAG20160227T100609

RMAN> list backupset 1745;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
1745    Incr 0  115.94M    DISK        00:02:08     2016-FEB-27 10:05:07
  List of Datafiles in backup set 1745
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 7405326    2016-FEB-27 10:02:59 /oradata/HAWKLAS/datafile/o1_mf_sysaux_c711xx75_.dbf

  Backup Set Copy #1 of backup set 1745
  Device Type Elapsed Time Completion Time      Compressed Tag
  ----------- ------------ -------------------- ---------- ---
  DISK        00:02:08     2016-FEB-27 10:05:06 YES        LEVEL0

    List of Backup Pieces for backup set 1745 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    2091    1   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_1_1_s1821_p1.bus
    2092    2   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_2_1_s1821_p2.bus
    2093    3   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_3_1_s1821_p3.bus
    2094    4   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_4_1_s1821_p4.bus
    2095    5   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_5_1_s1821_p5.bus
    2096    6   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_6_1_s1821_p6.bus
    2097    7   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_7_1_s1821_p7.bus
    2098    8   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus
    2099    9   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_9_1_s1821_p9.bus

RMAN> list backuppiece 2098;


List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
2098    1745    8   1   AVAILABLE   DISK        /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus

RMAN> exit


Recovery Manager complete.

Note the size for backup piece is 15MB versus 14.02MB from SQL – good enough.

$ du -m /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus

15      /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus

$ sysdba @bkupsize.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 27 10:52:45 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


COMPLETION_TIME     TYPE                MB        MIN
------------------- ----------- ---------- ----------
2016-02-24 00:00:00 ARCHIVELOG         .02          0
2016-02-24 00:00:00 CONTROLFILE      11.23        .05
2016-02-24 00:00:00 FULL            243.56        .82
2016-02-27 00:00:00 ARCHIVELOG       98.25         .9
2016-02-27 00:00:00 CONTROLFILE      22.46        .13
2016-02-27 00:00:00 LEVEL0           505.4      38.52
                                ----------
sum                                 880.92

6 rows selected.


       GRP TYPE          TOTAL_MB
---------- ----------- ----------
         0 ARCHIVELOG       98.27
         0 CONTROLFILE      33.69
         0 FULL            243.56
         0 LEVEL0           505.4
         1                 880.92

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Let’s compare against file system.

oracle@arrow:hawklas:/media/sf_working/rman
$ du -m /oradata/backup/
882     /oradata/backup/

oracle@arrow:hawklas:/media/sf_working/rman
$ ll /oradata/backup/
total 902412
-rw-r-----. 1 oracle oinstall  26853376 Feb 24 18:43 HAWK_3130551611_20160224_o8quog1h_1_1_s1800_p1
-rw-r-----. 1 oracle oinstall 114638848 Feb 24 18:43 HAWK_3130551611_20160224_o9quog1i_1_1_s1801_p1
-rw-r-----. 1 oracle oinstall  42868736 Feb 24 18:43 HAWK_3130551611_20160224_oaquog1p_1_1_s1802_p1
-rw-r-----. 1 oracle oinstall  62169088 Feb 24 18:43 HAWK_3130551611_20160224_obquog2a_1_1_s1803_p1
-rw-r-----. 1 oracle oinstall   8904704 Feb 24 18:43 HAWK_3130551611_20160224_ocquog2b_1_1_s1804_p1
-rw-r-----. 1 oracle oinstall     19968 Feb 24 18:43 HAWK_3130551611_20160224_odquog2r_1_1_s1805_p1
-rw-r-----. 1 oracle oinstall   3551744 Feb 27 09:57 HAWK_3130551611_20160227_ofquvecs_1_1_s1807_p1
-rw-r-----. 1 oracle oinstall  30955520 Feb 27 09:57 HAWK_3130551611_20160227_ogquvecs_1_1_s1808_p1
-rw-r-----. 1 oracle oinstall  14206976 Feb 27 09:57 HAWK_3130551611_20160227_ohquvecv_1_1_s1809_p1
-rw-r-----. 1 oracle oinstall  27041792 Feb 27 09:59 HAWK_3130551611_20160227_oiquved7_1_1_s1810_p1
-rw-r-----. 1 oracle oinstall  31547392 Feb 27 09:58 HAWK_3130551611_20160227_ojquved7_1_1_s1811_p1
-rw-r-----. 1 oracle oinstall  31506432 Feb 27 09:58 HAWK_3130551611_20160227_ojquved7_2_1_s1811_p2
-rw-r-----. 1 oracle oinstall  31563776 Feb 27 09:59 HAWK_3130551611_20160227_ojquved7_3_1_s1811_p3
-rw-r-----. 1 oracle oinstall  22568960 Feb 27 09:59 HAWK_3130551611_20160227_ojquved7_4_1_s1811_p4
-rw-r-----. 1 oracle oinstall  31776768 Feb 27 09:59 HAWK_3130551611_20160227_okquvefb_1_1_s1812_p1
-rw-r-----. 1 oracle oinstall  12451840 Feb 27 09:59 HAWK_3130551611_20160227_okquvefb_2_1_s1812_p2
-rw-r-----. 1 oracle oinstall  31924224 Feb 27 09:59 HAWK_3130551611_20160227_olquveg9_1_1_s1813_p1
-rw-r-----. 1 oracle oinstall  30859264 Feb 27 10:00 HAWK_3130551611_20160227_olquveg9_2_1_s1813_p2
-rw-r-----. 1 oracle oinstall   8929280 Feb 27 09:59 HAWK_3130551611_20160227_omquvego_1_1_s1814_p1
-rw-r-----. 1 oracle oinstall     76288 Feb 27 10:00 HAWK_3130551611_20160227_onquveh9_1_1_s1815_p1
-rw-r-----. 1 oracle oinstall  11970560 Feb 27 10:02 HAWK_3130551611_20160227_opquvem5_1_1_s1817_p1.bus
-rw-r-----. 1 oracle oinstall  14755840 Feb 27 10:02 HAWK_3130551611_20160227_oqquvem5_1_1_s1818_p1.bus
-rw-r-----. 1 oracle oinstall  14749184 Feb 27 10:02 HAWK_3130551611_20160227_oqquvem5_2_1_s1818_p2.bus
-rw-r-----. 1 oracle oinstall   5646848 Feb 27 10:02 HAWK_3130551611_20160227_oqquvem5_3_1_s1818_p3.bus
-rw-r-----. 1 oracle oinstall   1853952 Feb 27 10:02 HAWK_3130551611_20160227_orquvemc_1_1_s1819_p1.bus
-rw-r-----. 1 oracle oinstall  14843904 Feb 27 10:03 HAWK_3130551611_20160227_osquvemj_1_1_s1820_p1.bus
-rw-r-----. 1 oracle oinstall  13025280 Feb 27 10:04 HAWK_3130551611_20160227_osquvemj_2_1_s1820_p2.bus
-rw-r-----. 1 oracle oinstall  14696448 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_1_1_s1821_p1.bus
-rw-r-----. 1 oracle oinstall  14737408 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_2_1_s1821_p2.bus
-rw-r-----. 1 oracle oinstall  14696448 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_3_1_s1821_p3.bus
-rw-r-----. 1 oracle oinstall  14827520 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_4_1_s1821_p4.bus
-rw-r-----. 1 oracle oinstall  14802944 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_5_1_s1821_p5.bus
-rw-r-----. 1 oracle oinstall  14802944 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_6_1_s1821_p6.bus
-rw-r-----. 1 oracle oinstall  14737408 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_7_1_s1821_p7.bus
-rw-r-----. 1 oracle oinstall  14704640 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus
-rw-r-----. 1 oracle oinstall   3637248 Feb 27 10:05 HAWK_3130551611_20160227_otquvemj_9_1_s1821_p9.bus
-rw-r-----. 1 oracle oinstall  14868480 Feb 27 10:04 HAWK_3130551611_20160227_ouquvep1_1_1_s1822_p1.bus
-rw-r-----. 1 oracle oinstall  14843904 Feb 27 10:04 HAWK_3130551611_20160227_ouquvep1_2_1_s1822_p2.bus
-rw-r-----. 1 oracle oinstall  14712832 Feb 27 10:04 HAWK_3130551611_20160227_ouquvep1_3_1_s1822_p3.bus
-rw-r-----. 1 oracle oinstall   1785856 Feb 27 10:05 HAWK_3130551611_20160227_ouquvep1_4_1_s1822_p4.bus
-rw-r-----. 1 oracle oinstall  14868480 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_1_1_s1823_p1.bus
-rw-r-----. 1 oracle oinstall  14893056 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_2_1_s1823_p2.bus
-rw-r-----. 1 oracle oinstall  14786560 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_3_1_s1823_p3.bus
-rw-r-----. 1 oracle oinstall  14925824 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_4_1_s1823_p4.bus
-rw-r-----. 1 oracle oinstall   5881856 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_5_1_s1823_p5.bus
-rw-r-----. 1 oracle oinstall   8929280 Feb 27 10:05 HAWK_3130551611_20160227_p0quveqo_1_1_s1824_p1.bus
-rw-r-----. 1 oracle oinstall   5254144 Feb 27 10:06 HAWK_3130551611_20160227_p1quvese_1_1_s1825_p1.bus
-rw-r-----. 1 oracle oinstall  11796480 Feb 24 18:43 HAWK_c-3130551611-20160224-0e
-rw-r-----. 1 oracle oinstall  11796480 Feb 27 10:00 HAWK_c-3130551611-20160227-00
-rw-r-----. 1 oracle oinstall  11796480 Feb 27 10:06 HAWK_c-3130551611-20160227-01
oracle@arrow:hawklas:/media/sf_working/rman
$

bkupinfo.sql


col BP_MB for 9,999,999.99
break on report
COMPUTE sum of BP_MB on report
set numw 6 lines 120
col handle for a100
SELECT
s.completion_time,
s.recid BS_KEY,
CASE
WHEN s.backup_type='L' THEN 'ARCHIVELOG'
WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
WHEN s.backup_type='D' AND s.incremental_level IS NULL THEN 'FULL'
END type,
p.recid BP_KEY,
round(p.bytes/1048576,2) BP_MB,
s.pieces,
s.set_count,
p.piece#,
p.compressed,
p.device_type,
p.handle
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND s.set_stamp (+) = p.set_stamp
AND s.set_count (+) = p.set_count
order by s.recid, p.piece#
;

bkupsize.sql


col MB for 9,999,999.99
col min for 9,999,999.99
col TOTAL_MB for 9,999,999.99
break on report
COMPUTE sum of MB on report
SELECT TRUNC(completion_time) completion_time, type, round(sum(MB),2) MB, round(sum(elapsed_seconds)/60,2) min
FROM (
SELECT
CASE
WHEN s.backup_type='L' THEN 'ARCHIVELOG'
WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
WHEN s.backup_type='D' AND s.incremental_level IS NULL THEN 'FULL'
END type,
TRUNC(s.completion_time) completion_time,
round(p.bytes/1048576,2) MB,
s.elapsed_seconds
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND s.set_stamp = p.set_stamp
AND s.set_count = p.set_count
-- UNION ALL
-- SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
)
GROUP BY TRUNC(completion_time), type
ORDER BY 1 asc,2,3
;
SELECT grouping(type) grp, type, round(sum(MB),2) TOTAL_MB
FROM (
SELECT
CASE
WHEN s.backup_type='L' THEN 'ARCHIVELOG'
WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
WHEN s.backup_type='D' AND s.incremental_level IS NULL THEN 'FULL'
END type,
TRUNC(s.completion_time) completion_time,
round(p.bytes/1048576,2) MB,
s.elapsed_seconds
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND s.set_stamp = p.set_stamp
AND s.set_count = p.set_count
-- UNION ALL
-- SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
)
GROUP BY ROLLUP(type)
ORDER BY 1 asc,2,3
;

January 28, 2016

Bad to crosscheck archivelog all

Filed under: 11g,RMAN — mdinh @ 10:44 pm

Typically, it’s not a good idea to have “crosscheck archivelog all;” in backup scripts and
even worse to have “delete expired archivelog all;” since any evidence will be eradicated.

When you don’t crosscheck archivelog and the archivelog is missing, backup archivelog will fail.
When you crosscheck archivelog and archivelog is missing backup archivelog does not fail since RMAN marks archivelog as expired and ignore.

oracle@arrow:hawklas:/tmp
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 28 14:17:35 2016

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

connected to target database: HAWK (DBID=3130551611)

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5191    1    868     X 2016-JAN-28 11:46:46
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc

5201    1    873     A 2016-JAN-28 14:15:44
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc

RMAN> list backup of archivelog sequence 873;

specification does not match any backup in the repository

RMAN> exit

$ mv -v /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc /tmp/

oracle@arrow:hawklas:/tmp
`/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc' -> `/tmp/o1_mf_1_873_cbo4v850_.arc'
oracle@arrow:hawklas:/tmp
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 28 14:18:30 2016

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

connected to target database: HAWK (DBID=3130551611)

RMAN> backup archivelog all;

Starting backup at 2016-JAN-28 14:18:40
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/28/2016 14:18:46
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_874_cbo4y4or_.arc RECID=5203 STAMP=902326725
Crosschecked 1 objects

validation failed for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc RECID=5191 STAMP=902317692
validation failed for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc RECID=5201 STAMP=902326632
Crosschecked 2 objects

RMAN> backup archivelog all;

sequence=873 is missing and no errors raised.

Starting backup at 2016-JAN-28 14:19:09
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=874 RECID=5203 STAMP=902326725
channel ORA_DISK_1: starting piece 1 at 2016-JAN-28 14:19:11
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=875 RECID=5204 STAMP=902326750
channel ORA_DISK_2: starting piece 1 at 2016-JAN-28 14:19:11
channel ORA_DISK_1: finished piece 1 at 2016-JAN-28 14:19:12
piece handle=/oradata/backup/HAWK_3130551611_20160128_jdqsgqev_1_1_1645_1 tag=TAG20160128T141910 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: finished piece 1 at 2016-JAN-28 14:19:14
piece handle=/oradata/backup/HAWK_3130551611_20160128_jeqsgqev_1_1_1646_1 tag=TAG20160128T141910 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03
Finished backup at 2016-JAN-28 14:19:14

Starting Control File and SPFILE Autobackup at 2016-JAN-28 14:19:14
piece handle=/oradata/backup/HAWK_c-3130551611-20160128-09 comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-28 14:19:21

RMAN> list backup of archivelog sequence 873;

specification does not match any backup in the repository

RMAN> exit


Recovery Manager complete.
oracle@arrow:hawklas:/tmp
$

RMAN> delete expired archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5191    1    868     X 2016-JAN-28 11:46:46
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc

5201    1    873     X 2016-JAN-28 14:15:44
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc RECID=5191 STAMP=902317692
deleted archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc RECID=5201 STAMP=902326632
Deleted 2 EXPIRED objects

RMAN> backup archivelog all;

Starting backup at 2016-JAN-28 14:36:20
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=874 RECID=5203 STAMP=902326725
input archived log thread=1 sequence=875 RECID=5204 STAMP=902326750
channel ORA_DISK_1: starting piece 1 at 2016-JAN-28 14:36:21
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=876 RECID=5207 STAMP=902327780
channel ORA_DISK_2: starting piece 1 at 2016-JAN-28 14:36:22
channel ORA_DISK_1: finished piece 1 at 2016-JAN-28 14:36:23
piece handle=/oradata/backup/HAWK_3130551611_20160128_jgqsgrf5_1_1_1648_1 tag=TAG20160128T143621 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_2: finished piece 1 at 2016-JAN-28 14:36:23
piece handle=/oradata/backup/HAWK_3130551611_20160128_jhqsgrf5_1_1_1649_1 tag=TAG20160128T143621 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-28 14:36:23

Starting Control File and SPFILE Autobackup at 2016-JAN-28 14:36:23
piece handle=/oradata/backup/HAWK_c-3130551611-20160128-0a comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-28 14:36:30

RMAN>

Updated:

Does this mean we should never perform crosscheck archivelog all? No.

The purpose is to let archivelog backup fail and to investigate.

If investigation shows archivelog is indeed missing, then might be better to perform Level 1 or Level 0 backup.

RMAN> crosscheck archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_874_cbo4y4or_.arc RECID=5203 STAMP=902326725
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_875_cbo4yy69_.arc RECID=5204 STAMP=902326750
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_876_cbo5z4lz_.arc RECID=5207 STAMP=902327780
Crosschecked 3 objects

validation failed for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_877_cbog0qbg_.arc RECID=5209 STAMP=902336024
Crosschecked 1 objects

RMAN> list expired archivelog all;

List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5209    1    877     X 2016-JAN-28 14:36:20
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_877_cbog0qbg_.arc

RMAN> list backup of archivelog sequence 877;

specification does not match any backup in the repository

RMAN>

January 27, 2016

Why use KEEP backup?

Filed under: 11g,RMAN — mdinh @ 1:30 am

Question which may have been asked.

For 1 time backup to migrate/clone database, KEEP backup does not affect retention policy and not backed up 1 times clause.

Imagine the consequences for deleting 1 off backup which has archivelog backup and the main scripts use not backed up 1-2 times clause which could mean no archivelog for subsequent backups.

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5067    1    806     A 2016-JAN-21 07:45:48
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_21/o1_mf_1_806_cb1zcp8y_.arc

5068    1    807     A 2016-JAN-21 07:47:01
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_21/o1_mf_1_807_cb1zd4ns_.arc

5070    1    808     A 2016-JAN-21 07:47:16
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_22/o1_mf_1_808_cb4tyo1y_.arc

5071    1    809     A 2016-JAN-22 09:50:11
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_22/o1_mf_1_809_cb4tytsp_.arc

5073    1    810     A 2016-JAN-22 09:50:18
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_26/o1_mf_1_810_cbh5st68_.arc

5072    1    811     A 2016-JAN-26 07:56:40
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_26/o1_mf_1_811_cbh5sz08_.arc

5074    1    812     A 2016-JAN-26 07:56:45
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_26/o1_mf_1_812_cbh7mc9h_.arc


RMAN> backup archivelog sequence 806 KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' tag ARC_KEEP
2> ;

Starting backup at 2016-JAN-26 17:19:57
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
backup will be obsolete on date 2016-FEB-26 17:19:57
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=806 RECID=5067 STAMP=901698422
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:19:58
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/26/2016 17:19:59
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

RMAN> backup archivelog sequence 806 format '/tmp/U%' KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' tag ARC_KEEP;

Starting backup at 2016-JAN-26 17:20:26
using channel ORA_DISK_1
backup will be obsolete on date 2016-FEB-26 17:20:26
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=806 RECID=5067 STAMP=901698422
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:20:26
channel ORA_DISK_1: finished piece 1 at 2016-JAN-26 17:20:27
piece handle=/tmp/U% tag=ARC_KEEP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-26 17:20:27

RMAN> backup archivelog sequence 806 not backed up 1 times tag ARC_BKUP;

Starting backup at 2016-JAN-26 17:21:02
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=806 RECID=5067 STAMP=901698422
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:21:02
channel ORA_DISK_1: finished piece 1 at 2016-JAN-26 17:21:03
piece handle=/oradata/fra/HAWKLAS/backupset/2016_01_26/o1_mf_annnn_ARC_BKUP_cbj6vyl7_.bkp tag=ARC_BKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-26 17:21:03

Starting Control File and SPFILE Autobackup at 2016-JAN-26 17:21:04
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/HAWK_c-3130551611-20160126-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-26 17:21:07

RMAN> backup archivelog sequence 807 tag ARC_BACKUP;

Starting backup at 2016-JAN-26 17:21:30
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=807 RECID=5068 STAMP=901698436
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:21:30
channel ORA_DISK_1: finished piece 1 at 2016-JAN-26 17:21:31
piece handle=/oradata/fra/HAWKLAS/backupset/2016_01_26/o1_mf_annnn_ARC_BACKUP_cbj6wt9y_.bkp tag=ARC_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-26 17:21:31

Starting Control File and SPFILE Autobackup at 2016-JAN-26 17:21:31
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/HAWK_c-3130551611-20160126-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-26 17:21:34

RMAN> backup archivelog sequence 807 not backed up 1 times;

Starting backup at 2016-JAN-26 17:21:43
using channel ORA_DISK_1
skipping archived log of thread 1 with sequence 807; already backed up
Finished backup at 2016-JAN-26 17:21:43

RMAN> list backup of archivelog all summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1360    B  A  A DISK        2016-JAN-26 17:20:27 1       1       NO         ARC_KEEP
1361    B  A  A DISK        2016-JAN-26 17:21:02 1       1       NO         ARC_BKUP
1363    B  A  A DISK        2016-JAN-26 17:21:30 1       1       NO         ARC_BACKUP

RMAN> list backup of archivelog sequence 806;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
1360    27.00K     DISK        00:00:01     2016-JAN-26 17:20:27
        BP Key: 1505   Status: AVAILABLE  Compressed: NO  Tag: ARC_KEEP
        Piece Name: /tmp/U%

  List of Archived Logs in backup set 1360
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    806     6645495    2016-JAN-21 07:45:48 6645595    2016-JAN-21 07:47:01

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
1361    27.00K     DISK        00:00:00     2016-JAN-26 17:21:02
        BP Key: 1506   Status: AVAILABLE  Compressed: NO  Tag: ARC_BKUP
        Piece Name: /oradata/fra/HAWKLAS/backupset/2016_01_26/o1_mf_annnn_ARC_BKUP_cbj6vyl7_.bkp

  List of Archived Logs in backup set 1361
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    806     6645495    2016-JAN-21 07:45:48 6645595    2016-JAN-21 07:47:01

RMAN> list backup of archivelog sequence 806 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1360    B  A  A DISK        2016-JAN-26 17:20:27 1       1       NO         ARC_KEEP
1361    B  A  A DISK        2016-JAN-26 17:21:02 1       1       NO         ARC_BKUP

RMAN>

OOPS! Did you see my errors?

January 26, 2016

Unsolved Case for Missing archived_log Backup

Filed under: 11g,oracle,RAC,RMAN — mdinh @ 11:00 pm

The project was to migrate database from one DC to another.

The decision we made was to perform RMAN KEEP backup so it does not interfere with existing retention policy.

Backup also resides in its own separate directory for easier checksum and transfer.

This is for 4 nodes RAC environment and backup was taken from node1 at 2016-JAN-21 14:12:10

RMAN backup scripts.

run {
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C4 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
ALLOCATE CHANNEL C5 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/%d_%I_%T_%U_MIGRATION_%s' MAXPIECESIZE 4G MAXOPENFILES 1;
SQL 'ALTER SYSTEM CHECKPOINT';

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE FILESPERSET 1 
KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' TAG='MIGRATION_KEEP';

BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG from time 'trunc(sysdate)' FILESPERSET 2 
KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' TAG='MIGRATION_KEEP';
}
run {
ALLOCATE CHANNEL C6 DEVICE TYPE DISK FORMAT '/oracle/FRA/migration_backup/CTL_%d_%I_%T_%U_MIGRATION_%s';
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' TAG='MIGRATION_KEEP';
}
LIST BACKUP OF DATABASE SUMMARY TAG='MIGRATION_KEEP';
LIST BACKUP OF ARCHIVELOG ALL SUMMARY TAG='MIGRATION_KEEP';
LIST BACKUP OF CONTROLFILE TAG='MIGRATION_KEEP';
REPORT SCHEMA;

When recovering database, we encountered the error below.

ERROR from database recovery

RMAN-06025: no backup of archived log for thread 1 with sequence 287407 and starting SCN of 198452997924 found to restore

According to gv$archived_log, the sequence has not been deleted.

SQL> select inst_id, thread#, sequence#, completion_time, status, deleted
from gv$archived_log
where thread#=1 and sequence# between 287406 and 287408
order by 1,2,3
;

  2    3    4    5  
   INST_ID    THREAD#  SEQUENCE# COMPLETION_TIME      S DEL
---------- ---------- ---------- -------------------- - ---
	 1	    1	  287406 2016-JAN-21 18:51:29 A NO
	 1	    1	  287407 2016-JAN-21 18:59:45 A NO
	 1	    1	  287408 2016-JAN-21 19:00:08 A NO
	 2	    1	  287406 2016-JAN-21 18:51:29 A NO
	 2	    1	  287407 2016-JAN-21 18:59:45 A NO
	 2	    1	  287408 2016-JAN-21 19:00:08 A NO
	 3	    1	  287406 2016-JAN-21 18:51:29 A NO
	 3	    1	  287407 2016-JAN-21 18:59:45 A NO
	 3	    1	  287408 2016-JAN-21 19:00:08 A NO
	 4	    1	  287406 2016-JAN-21 18:51:29 A NO
	 4	    1	  287407 2016-JAN-21 18:59:45 A NO
	 4	    1	  287408 2016-JAN-21 19:00:08 A NO

12 rows selected.

SQL> SQL> 

Backup was started at 2016-JAN-21 14:12:10.

Noticed sequence 287407 thread 1 was missing from the MIGRATION_KEEP backup.

RMAN> list backup of archivelog sequence 287406 thread 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233366  B  A  A DISK        2016-JAN-21 18:59:30 1       1       YES        MIGRATION_KEEP
233374  B  A  A DISK        2016-JAN-21 19:23:41 1       1       YES        ARC021THU1923

RMAN> list backup of archivelog sequence 287407 thread 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233375  B  A  A DISK        2016-JAN-21 19:23:46 1       1       YES        ARC021THU1923

RMAN> list backup of archivelog sequence 287408 thread 1 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233372  B  A  A DISK        2016-JAN-21 19:00:16 1       1       YES        MIGRATION_KEEP
233377  B  A  A DISK        2016-JAN-21 19:23:47 1       1       YES        ARC021THU1923


RMAN> list backup summary tag MIGRATION_KEEP;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233092  B  0  A DISK        2016-JAN-21 14:12:10 2       1       YES        MIGRATION_KEEP
233093  B  0  A DISK        2016-JAN-21 14:12:19 2       1       YES        MIGRATION_KEEP

233306  B  0  A DISK        2016-JAN-21 18:48:31 1       1       YES        MIGRATION_KEEP
233307  B  0  A DISK        2016-JAN-21 18:48:32 1       1       YES        MIGRATION_KEEP

233308  B  F  A DISK        2016-JAN-21 18:48:37 1       1       YES        MIGRATION_KEEP
233309  B  A  A DISK        2016-JAN-21 18:50:20 1       1       YES        MIGRATION_KEEP
233310  B  A  A DISK        2016-JAN-21 18:50:47 1       1       YES        MIGRATION_KEEP
233311  B  A  A DISK        2016-JAN-21 18:50:48 1       1       YES        MIGRATION_KEEP
233312  B  A  A DISK        2016-JAN-21 18:50:54 1       1       YES        MIGRATION_KEEP
233313  B  A  A DISK        2016-JAN-21 18:50:58 1       1       YES        MIGRATION_KEEP
233314  B  F  A DISK        2016-JAN-21 18:51:12 1       1       YES        MIGRATION_KEEP
233315  B  A  A DISK        2016-JAN-21 18:52:00 1       1       YES        MIGRATION_KEEP

233366  B  A  A DISK        2016-JAN-21 18:59:30 1       1       YES        MIGRATION_KEEP
233367  B  A  A DISK        2016-JAN-21 18:59:32 1       1       YES        MIGRATION_KEEP
233368  B  A  A DISK        2016-JAN-21 18:59:32 1       1       YES        MIGRATION_KEEP
233369  B  A  A DISK        2016-JAN-21 18:59:35 1       1       YES        MIGRATION_KEEP
233370  B  F  A DISK        2016-JAN-21 18:59:54 1       1       YES        MIGRATION_KEEP
233371  B  F  A DISK        2016-JAN-21 19:00:04 1       1       YES        MIGRATION_KEEP
233372  B  A  A DISK        2016-JAN-21 19:00:16 1       1       YES        MIGRATION_KEEP
233373  B  F  A DISK        2016-JAN-21 19:00:22 1       1       YES        MIGRATION_KEEP

RMAN> list backup of controlfile summary tag MIGRATION_KEEP;

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233314  B  F  A DISK        2016-JAN-21 18:51:12 1       1       YES        MIGRATION_KEEP
233370  B  F  A DISK        2016-JAN-21 18:59:54 1       1       YES        MIGRATION_KEEP
233373  B  F  A DISK        2016-JAN-21 19:00:22 1       1       YES        MIGRATION_KEEP --- This CF was restored.

RMAN> 
RMAN> restore controlfile from '/rman_bkp/FRA/migration_backup/CTL_3036635614_20160121_m6qrusa4_1_1_MIGRATION_235206';
RMAN> list backup of archivelog all summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
233309  B  A  A DISK        21-JAN-2016 18:50:20 1       1       YES        MIGRATION_KEEP

233365  B  A  A DISK        21-JAN-2016 18:59:29 1       1       YES        MIGRATION_KEEP
233366  B  A  A DISK        21-JAN-2016 18:59:30 1       1       YES        MIGRATION_KEEP
233367  B  A  A DISK        21-JAN-2016 18:59:32 1       1       YES        MIGRATION_KEEP
233368  B  A  A DISK        21-JAN-2016 18:59:32 1       1       YES        MIGRATION_KEEP
233369  B  A  A DISK        21-JAN-2016 18:59:35 1       1       YES        MIGRATION_KEEP
233372  B  A  A DISK        21-JAN-2016 19:00:16 1       1       YES        MIGRATION_KEEP

RMAN> list backupset 233372;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time     
------- ---------- ----------- ------------ --------------------
233372  35.84M     DISK        00:00:04     21-JAN-2016 19:00:16
        BP Key: 359665   Status: AVAILABLE  Compressed: YES  Tag: MIGRATION_KEEP
        Piece Name: /rman_bkp/FRA/migration_backup/CTL_3036635614_20160121_m5qrus9s_1_1_MIGRATION_235205
        Keep: BACKUP_LOGS        Until: 21-FEB-2016 19:00:12

  List of Archived Logs in backup set 233372
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    287408  198453187859 21-JAN-2016 18:59:44 198453194240 21-JAN-2016 19:00:08
  2    207046  198452998035 21-JAN-2016 18:51:29 198453187879 21-JAN-2016 18:59:44
  2    207047  198453187879 21-JAN-2016 18:59:44 198453193569 21-JAN-2016 19:00:05
  3    182524  198452999167 21-JAN-2016 18:51:31 198453188295 21-JAN-2016 18:59:47
  3    182525  198453188295 21-JAN-2016 18:59:47 198453194175 21-JAN-2016 19:00:08
  4    75721   198452999243 21-JAN-2016 18:51:32 198453188286 21-JAN-2016 18:59:47
  4    75722   198453188286 21-JAN-2016 18:59:47 198453194112 21-JAN-2016 19:00:08

RMAN> 

Even from the log file sequence 287407 is missing.

channel C4: backup set complete, elapsed time: 00:00:30
channel C4: starting compressed archived log backup set
channel C4: specifying archived log(s) in backup set
input archived log thread=4 sequence=75720 RECID=709008 STAMP=901738292
input archived log thread=1 sequence=287406 RECID=709005 STAMP=901738289
channel C4: starting piece 1 at 2016-JAN-21 18:59:28
channel C5: finished piece 1 at 2016-JAN-21 18:59:28
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_lvqrus7u_1_1_MIGRATION_235199 tag=MIGRATION_KEEP comment=NONE
channel C5: backup set complete, elapsed time: 00:00:13
channel C5: starting compressed archived log backup set
channel C5: specifying archived log(s) in backup set
input archived log thread=2 sequence=207045 RECID=709006 STAMP=901738289
channel C5: starting piece 1 at 2016-JAN-21 18:59:29
channel C3: finished piece 1 at 2016-JAN-21 18:59:30
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_luqrus7p_1_1_MIGRATION_235198 tag=MIGRATION_KEEP comment=NONE
channel C3: backup set complete, elapsed time: 00:00:20
channel C4: finished piece 1 at 2016-JAN-21 18:59:32
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_m1qrus8g_1_1_MIGRATION_235201 tag=MIGRATION_KEEP comment=NONE
channel C4: backup set complete, elapsed time: 00:00:04
channel C5: finished piece 1 at 2016-JAN-21 18:59:32
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_m2qrus8g_1_1_MIGRATION_235202 tag=MIGRATION_KEEP comment=NONE
channel C5: backup set complete, elapsed time: 00:00:03
channel C1: finished piece 1 at 2016-JAN-21 18:59:36
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_ltqrus7p_1_1_MIGRATION_235197 tag=MIGRATION_KEEP comment=NONE
channel C1: backup set complete, elapsed time: 00:00:31
channel C2: finished piece 1 at 2016-JAN-21 18:59:36
piece handle=/oracle/FRA/migration_backup/3036635614_20160121_m0qrus83_1_1_MIGRATION_235200 tag=MIGRATION_KEEP comment=NONE
channel C2: backup set complete, elapsed time: 00:00:15
Finished backup at 2016-JAN-21 18:59:36
released channel: C1
released channel: C2
released channel: C3
released channel: C4
released channel: C5
                               
allocated channel: C6
channel C6: SID=373 instance=1 device type=DISK

Starting backup at 2016-JAN-21 18:59:44
current log archived

backup will be obsolete on date 2016-FEB-21 18:59:52
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current control file in backup set
channel C6: starting piece 1 at 2016-JAN-21 18:59:53
channel C6: finished piece 1 at 2016-JAN-21 19:00:04
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m3qrus98_1_1_MIGRATION_235203 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:11

backup will be obsolete on date 2016-FEB-21 19:00:04
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current SPFILE in backup set
channel C6: starting piece 1 at 2016-JAN-21 19:00:04
channel C6: finished piece 1 at 2016-JAN-21 19:00:05
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m4qrus9k_1_1_MIGRATION_235204 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:01

backup will be obsolete on date 2016-FEB-21 19:00:04
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current SPFILE in backup set
channel C6: starting piece 1 at 2016-JAN-21 19:00:04
channel C6: finished piece 1 at 2016-JAN-21 19:00:05
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m4qrus9k_1_1_MIGRATION_235204 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:01

current log archived
backup will be obsolete on date 2016-FEB-21 19:00:12
archived logs required to recover from this backup will be backed up
channel C6: starting compressed archived log backup set
channel C6: specifying archived log(s) in backup set
input archived log thread=2 sequence=207046 RECID=709010 STAMP=901738785
input archived log thread=3 sequence=182524 RECID=709011 STAMP=901738788
input archived log thread=4 sequence=75721 RECID=709012 STAMP=901738788
input archived log thread=1 sequence=287408 RECID=709016 STAMP=901738808
input archived log thread=2 sequence=207047 RECID=709013 STAMP=901738806
input archived log thread=4 sequence=75722 RECID=709014 STAMP=901738808
input archived log thread=3 sequence=182525 RECID=709015 STAMP=901738808
channel C6: starting piece 1 at 2016-JAN-21 19:00:13
channel C6: finished piece 1 at 2016-JAN-21 19:00:20
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m5qrus9s_1_1_MIGRATION_235205 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:07

backup will be obsolete on date 2016-FEB-21 19:00:20
archived logs required to recover from this backup will be backed up
channel C6: starting compressed full datafile backup set
channel C6: specifying datafile(s) in backup set
including current control file in backup set
channel C6: starting piece 1 at 2016-JAN-21 19:00:21
channel C6: finished piece 1 at 2016-JAN-21 19:00:31
piece handle=/oracle/FRA/migration_backup/CTL_3036635614_20160121_m6qrusa4_1_1_MIGRATION_235206 tag=MIGRATION_KEEP comment=NONE
channel C6: backup set complete, elapsed time: 00:00:10
Finished backup at 2016-JAN-21 19:00:31
released channel: C6

Any ideas as to why the archived log was missing from backup?

BTW, I have already deleted the backups to save space.

August 23, 2015

Monitoring RMAN Operations

Filed under: RMAN — mdinh @ 5:41 am

Just a reference to source and my version of the script.

This is for restore since there are OUTPUTS.

Script to monitor RMAN Backup and Restore Operations (Doc ID 1487262.1)

$ sqlplus / as sysdba @mon_rman_restore.sql

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 23 01:14:31 2015

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Session altered.


  SID SERIAL# USERNAME	 LOGON_TIME	 OSUSER     PROCESS	   SPID 	MACHINE        ST PROGRAM
----- ------- ---------- --------------- ---------- -------------- ------------ -------------- -- --------------------------------
 3290	   12 SYS	 22-08-15 20:36  oracle     31267	   31298	prod2      I  rman@prod2 (TNS V1-V3)
 3292	    9 SYS	 22-08-15 20:36  oracle     31267	   31297	prod2      I  rman@prod2 (TNS V1-V3)
 3289	   11 SYS	 22-08-15 20:36  oracle     31267	   31299	prod2      A  rman@prod2 (TNS V1-V3)
 3279	    1 SYS	 22-08-15 20:36  oracle     31267	   31301	prod2      A  rman@prod2 (TNS V1-V3)
 3285	   14 SYS	 22-08-15 20:36  oracle     31267	   31300	prod2      A  rman@prod2 (TNS V1-V3)
 3278	    1 SYS	 22-08-15 20:36  oracle     31267	   31302	prod2      A  rman@prod2 (TNS V1-V3)
 3277	    1 SYS	 22-08-15 20:36  oracle     31267	   31303	prod2      A  rman@prod2 (TNS V1-V3)
 3275	    1 SYS	 22-08-15 20:36  oracle     31267	   31305	prod2      A  rman@prod2 (TNS V1-V3)
 3276	    1 SYS	 22-08-15 20:36  oracle     31267	   31304	prod2      A  rman@prod2 (TNS V1-V3)
 3274	    1 SYS	 22-08-15 20:36  oracle     31267	   31306	prod2      A  rman@prod2 (TNS V1-V3)
 3273	    1 SYS	 22-08-15 20:36  oracle     31267	   31307	prod2      A  rman@prod2 (TNS V1-V3)
 3272	    1 SYS	 22-08-15 20:37  oracle     31267	   31308	prod2      A  rman@prod2 (TNS V1-V3)
 3270	    1 SYS	 22-08-15 20:37  oracle     31267	   31310	prod2      A  rman@prod2 (TNS V1-V3)
 3271	    1 SYS	 22-08-15 20:37  oracle     31267	   31309	prod2      A  rman@prod2 (TNS V1-V3)

14 rows selected.


  SID SERIAL# CHANNEL			 SEQ# EVENT			     STATE		SECS	  SOFAR  TOTALWORK % COMPLETE
----- ------- -------------------- ---------- ------------------------------ ------------ ---------- ---------- ---------- ----------
 3274	    1 rman channel=d08		54992 RMAN backup & recovery I/O     WAITING		   0	 342523    6815742	 5.03
 3275	    1 rman channel=d07		18384 RMAN backup & recovery I/O     WAITING		   0	 501503    7340030	 6.83
 3278	    1 rman channel=d04		48839 RMAN backup & recovery I/O     WAITING		   3	 502704    7340030	 6.85
 3272	    1 rman channel=d10		13502 RMAN backup & recovery I/O     WAITING		   3	 495473    6815742	 7.27
 3270	    1 rman channel=d12		39023 RMAN backup & recovery I/O     WAITING		   0	 535039    7340030	 7.29
 3271	    1 rman channel=d11		51018 RMAN backup & recovery I/O     WAITING		   0	 536703    7340030	 7.31
 3276	    1 rman channel=d06		  121 RMAN backup & recovery I/O     WAITING		   0	 503423    6815742	 7.39
 3277	    1 rman channel=d05		  276 RMAN backup & recovery I/O     WAITING		   3	 553855    7389182	  7.5
 3285	   14 rman channel=d02		56444 RMAN backup & recovery I/O     WAITING		   3	 611128    7340030	 8.33
 3289	   11 rman channel=d01		 2482 RMAN backup & recovery I/O     WAITING		   3	 846732    7340030	11.54
 3279	    1 rman channel=d03		 5065 RMAN backup & recovery I/O     WAITING		   3	 882685    7340030	12.03
 3273	    1 rman channel=d09		49115 RMAN backup & recovery I/O     WAITING		   3	1004287    7340030	13.68

12 rows selected.


  SID CHANNEL		   STATUS		OPEN_TIME	       SOFAR_MB   TOTAL_MB % COMPLETE TYPE
----- -------------------- -------------------- -------------------- ---------- ---------- ---------- ---------
FILENAME
----------------------------------------------------------------------------------------------------
 3270 rman channel=d12	   IN PROGRESS		23-AUG-2015 01:06:36	4180.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH1_9qqf6d01_49466_1.bus

 3275 rman channel=d07	   IN PROGRESS		23-AUG-2015 01:06:59	3918.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH3_a0qf6hcg_49472_1.bus

 3289 rman channel=d01	   IN PROGRESS		23-AUG-2015 01:02:00	6615.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH8_9pqf6crq_49465_1.bus

 3285 rman channel=d02	   IN PROGRESS		23-AUG-2015 01:05:46	4647.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH7_9rqf6d1e_49467_1.bus

 3279 rman channel=d03	   IN PROGRESS		23-AUG-2015 01:01:26	6895.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH6_9uqf6d3c_49470_1.bus

 3278 rman channel=d04	   IN PROGRESS		23-AUG-2015 01:07:02	3922.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH2_9sqf6d1t_49468_1.bus

 3277 rman channel=d05	   IN PROGRESS		23-AUG-2015 01:06:20	4327.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH5_9oqf6coh_49464_1.bus

 3276 rman channel=d06	   IN PROGRESS		23-AUG-2015 01:07:00	3933.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH8_a2qf6i9i_49474_1.bus

 3274 rman channel=d08	   IN PROGRESS		23-AUG-2015 01:09:24	2674.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH7_a3qf6ic7_49475_1.bus

 3273 rman channel=d09	   IN PROGRESS		23-AUG-2015 00:59:40	7846.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH3_9vqf6d3d_49471_1.bus

 3272 rman channel=d10	   IN PROGRESS		23-AUG-2015 01:07:07	3869.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH5_a4qf6idl_49476_1.bus

 3271 rman channel=d11	   IN PROGRESS		23-AUG-2015 01:06:35	4193.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH4_9tqf6d1v_49469_1.bus

 3273 rman channel=d09	   IN PROGRESS		23-AUG-2015 00:59:42	   3923      24576	15.96 OUTPUT
+DATA01/prod2/datafile/xxxdata01.305.888454781

 3279 rman channel=d03	   IN PROGRESS		23-AUG-2015 01:01:28	3447.88      24576	14.03 OUTPUT
+DATA01/prod2/datafile/xxxdata01.307.888454887

 3289 rman channel=d01	   IN PROGRESS		23-AUG-2015 01:02:02	   3308      24576	13.46 OUTPUT
+DATA01/prod2/datafile/xxxdata01.309.888454921

 3273 rman channel=d09	   IN PROGRESS		23-AUG-2015 00:59:41	3923.88   32767.98	11.97 OUTPUT
+DATA01/prod2/datafile/xxxidx01.304.888454781

 3279 rman channel=d03	   IN PROGRESS		23-AUG-2015 01:01:27	3448.88   32767.98	10.53 OUTPUT
+DATA01/prod2/datafile/xxxidx01.306.888454887

 3289 rman channel=d01	   IN PROGRESS		23-AUG-2015 01:02:01	   3308   32767.98	 10.1 OUTPUT
+DATA01/prod2/datafile/xxxidx01.308.888454921

 3285 rman channel=d02	   IN PROGRESS		23-AUG-2015 01:05:47	2387.38      24576	 9.71 OUTPUT
+DATA01/prod2/datafile/xxxdata01.311.888455147

 3276 rman channel=d06	   IN PROGRESS		23-AUG-2015 01:07:03	1966.88      20480	  9.6 OUTPUT
+DATA01/prod2/datafile/xxxdata01.449.867145931.tts

 3272 rman channel=d10	   IN PROGRESS		23-AUG-2015 01:07:08	1935.88      20480	 9.45 OUTPUT
+DATA01/prod2/datafile/xxxidx01.325.888455227

 3277 rman channel=d05	   IN PROGRESS		23-AUG-2015 01:06:22	2163.88      24960	 8.67 OUTPUT
+DATA01/prod2/datafile/xxxdata01.313.888455181

 3271 rman channel=d11	   IN PROGRESS		23-AUG-2015 01:06:36	2096.88      24576	 8.53 OUTPUT
+DATA01/prod2/datafile/xxxdata01.315.888455195

 3270 rman channel=d12	   IN PROGRESS		23-AUG-2015 01:06:38	   2090      24576	  8.5 OUTPUT
+DATA01/prod2/datafile/xxxidx01.317.888455197

 3278 rman channel=d04	   IN PROGRESS		23-AUG-2015 01:07:03	   1964      24576	 7.99 OUTPUT
+DATA01/prod2/datafile/xxxdata01.323.888455223

 3275 rman channel=d07	   IN PROGRESS		23-AUG-2015 01:07:01	1958.88      24576	 7.97 OUTPUT
+DATA01/prod2/datafile/xxxidx01.319.888455221

 3285 rman channel=d02	   IN PROGRESS		23-AUG-2015 01:05:47	   2388   32767.98	 7.29 OUTPUT
+DATA01/prod2/datafile/xxxdata01.310.888455147

 3277 rman channel=d05	   IN PROGRESS		23-AUG-2015 01:06:21	   2164   32767.98	  6.6 OUTPUT
+DATA01/prod2/datafile/xxxidx01.312.888455181

 3274 rman channel=d08	   IN PROGRESS		23-AUG-2015 01:09:25	1337.88      20480	 6.53 OUTPUT
+DATA01/prod2/datafile/xxxidx01.327.888455365

 3271 rman channel=d11	   IN PROGRESS		23-AUG-2015 01:06:35	   2097   32767.98	  6.4 OUTPUT
+DATA01/prod2/datafile/xxxdata01.314.888455195

 3270 rman channel=d12	   IN PROGRESS		23-AUG-2015 01:06:37	2090.88   32767.98	 6.38 OUTPUT
+DATA01/prod2/datafile/xxxidx01.316.888455197

 3276 rman channel=d06	   IN PROGRESS		23-AUG-2015 01:07:02	   1967   32767.98	    6 OUTPUT
+DATA01/prod2/datafile/xxxdata01.320.888455221

 3278 rman channel=d04	   IN PROGRESS		23-AUG-2015 01:07:03	1964.38   32767.98	 5.99 OUTPUT
+DATA01/prod2/datafile/xxxidx01.321.888455223

 3275 rman channel=d07	   IN PROGRESS		23-AUG-2015 01:07:00	   1960   32767.98	 5.98 OUTPUT
+DATA01/prod2/datafile/xxxidx01.318.888455219

 3272 rman channel=d10	   IN PROGRESS		23-AUG-2015 01:07:07	   1936   32767.98	 5.91 OUTPUT
+DATA01/prod2/datafile/xxxidx01.324.888455227

 3274 rman channel=d08	   IN PROGRESS		23-AUG-2015 01:09:25	1338.88   32767.98	 4.09 OUTPUT
+DATA01/prod2/datafile/xxxdata01.326.888455365


36 rows selected.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ 


SET linesize 160 trimspool ON pages 1000
ALTER session SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
col sid FOR 9999
col serial# FOR 99999
col spid FOR a10
col username FOR a10
col osuser FOR a10
col status FOR a10
col program FOR a36 truncate
col logon_time FOR a15
col module FOR a30
col action FOR a35
col process FOR a10
col machine FOR a18 truncate
SELECT s.sid,
s.serial#,
s.username,
TO_CHAR(s.logon_time,'DD-MM-RR hh24:mi') logon_time,
s.osuser,
s.process,
p.spid,
s.machine,
s.status,
s.program
FROM v$session s, v$process p
WHERE s.program LIKE '%rman%'
AND s.paddr = p.addr (+)
ORDER BY s.logon_time, s.sid
;
col event FOR a30
col channel FOR a20
col state FOR a18
SELECT o.sid,
o.serial#,
client_info channel,
seq#,
event,
state,
seconds_in_wait secs,
sofar,
totalwork,
ROUND(sofar/totalwork*100,2) "%COMPLETE"
FROM v$session_longops o, v$session s
WHERE program LIKE '%rman%'
AND opname NOT LIKE '%aggregate%'
AND o.sid =s.sid
AND totalwork != 0
AND sofar != totalwork
AND wait_time > 0
AND NOT action IS NULL
ORDER BY 10 desc
;
col filename FOR a110
col status FOR a20
SELECT a.sid,
client_info channel,
a.status,
open_time,
ROUND(BYTES /1024/1024,2) SOFAR_MB,
ROUND(total_bytes/1024/1024,2) TOTAL_MB,
ROUND(BYTES/TOTAL_BYTES*100,2) "%COMPLETE",
a.type,
filename
FROM v$backup_async_io a, v$session s
WHERE NOT a.STATUS IN ('UNKNOWN')
AND a.sid =s.sid
AND a.status !='FINISHED'
AND total_bytes>0
ORDER BY 8, 7 DESC
;

Next Page »

Create a free website or blog at WordPress.com.