OraFAQ Forum: Backup & Recovery » Error with reclaim db_recovery_file

Home » RDBMS Server » Backup & Recovery » Error with reclaim db_recovery_file_dest (Oracle 10.2.0.1, Sun Solaris 10 - SPARC)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error with reclaim db_recovery_file_dest [message #352081]

Mon, 06 October 2008 00:38

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

Hi all!

My database did not use flashback option, however, I set 2 parameters before

SQL> col name format a30
SQL> set pagesize 6000
SQL> set linesize 500
SQL> col name format a30
SQL> col value format a35
SQL> select name, value
  2  from v$parameter
  3  where name like 'db_recovery_%';

NAME                           VALUE
------------------------------ -----------------------------------
db_recovery_file_dest          /vnporadataSYSTEM/vnp_flashback
db_recovery_file_dest_size     42949672960

SQL> select flashback_on
  2  from v$database;

FLASHBACK_ON
------------------
NO

SQL> col name format a30
SQL> select name,
  2  floor(space_limit/1024/1024) "Size_MB",
  3  ceil(space_used/1024/1024) "Used_MB"
  4  from v$recovery_file_dest
  5  order by name
  6  /
NAME                              Size_MB    Used_MB
------------------------------ ---------- ----------
/vnporadataSYSTEM/vnp_flashbac      40960          0
k


SQL>

Some days ago, the mail_alert show me some error when backup with crontab job. Using vi, the alert log file shows

Quote:

Mon Oct 6 10:41:16 2008
Errors in file /u02/app/oracle/admin/VNP/udump/vnp_ora_15051.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 32212254720 bytes is 97.01% us
ed, and has 964526080 remaining bytes available.
Mon Oct 6 10:41:16 2008
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Mon Oct 6 10:41:43 2008
Errors in file /u02/app/oracle/admin/VNP/udump/vnp_ora_15078.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 32212254720 bytes is 100.00% u
sed, and has 0 remaining bytes available.
Mon Oct 6 10:41:43 2008
************************************************************************
Deleted Oracle managed file /vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_1
0_06/o1_mf_nnnd0_TAG20081006T105900_4gm385sv_.bkp
Mon Oct 6 11:13:01 2008
db_recovery_file_dest_size of 30720 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Oct 6 11:27:54 2008
Errors in file /u02/app/oracle/admin/VNP/udump/vnp_ora_15809.trc:
Mon Oct 6 11:27:54 2008
Errors in file /u02/app/oracle/admin/VNP/udump/vnp_ora_15809.trc:
Mon Oct 6 11:27:54 2008
Errors in file /u02/app/oracle/admin/VNP/udump/vnp_ora_15809.trc:
Mon Oct 6 11:29:13 2008

The disk which mounted by /vnporadataSYSTEM is free up.

SQL> ! df -k /vnporadataSYSTEM
Filesystem            kbytes    used   avail capacity  Mounted on
/dev/dsk/c4t600A0B8000482FB20000045247E9D08Ad0s0
                     140447186 9499602 129543113     7%    /vnporadataSYSTEM

SQL>

Why did Oracle server introduce that? I really do not understand. The last backup has remain errors

Starting backup at 06-OCT-08
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00004 name=/vnporadataUSERS/oradataUSERS/users01.dbf
input datafile fno=00002 name=/vnporadataSYSTEM/VNP/undotbs01.dbf
input datafile fno=00001 name=/vnporadataSYSTEM/VNP/system01.dbf
input datafile fno=00003 name=/vnporadataSYSTEM/VNP/sysaux01.dbf
input datafile fno=00006 name=/vnporadataUSERS/oradataUSERS/users02.dbf
input datafile fno=00005 name=/vnporadataUSERS/oradataUSERS/tool.dbf
input datafile fno=00007 name=/vnporadataUSERS/oradataUSERS/logvnp.dbf
channel d1: starting piece 1 at 06-OCT-08
RMAN-03009: failure of backup command on d1 channel at 10/06/2008 12:07:41
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 42949672960 limit
continuing other job steps, job failed will not be re-run
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00004 name=/vnporadataUSERS/oradataUSERS/users01.dbf
channel d1: starting piece 1 at 06-OCT-08
channel d1: finished piece 1 at 06-OCT-08
piece handle=/vnprctl/backupFullVNP/Level0_VNP_1587_1_hjjsfb4d.bkp tag=LEVEL0 comment=NONE
channel d1: backup set complete, elapsed time: 00:08:06
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00006 name=/vnporadataUSERS/oradataUSERS/users02.dbf
channel d1: starting piece 1 at 06-OCT-08
channel d1: finished piece 1 at 06-OCT-08
piece handle=/vnprctl/backupFullVNP/Level0_VNP_1588_1_hkjsfbjj.bkp tag=LEVEL0 comment=NONE
channel d1: backup set complete, elapsed time: 00:06:45
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00002 name=/vnporadataSYSTEM/VNP/undotbs01.dbf
channel d1: starting piece 1 at 06-OCT-08
channel d1: finished piece 1 at 06-OCT-08
piece handle=/vnprctl/backupFullVNP/Level0_VNP_1589_1_hljsfc08.bkp tag=LEVEL0 comment=NONE
channel d1: backup set complete, elapsed time: 00:01:45
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=/vnporadataSYSTEM/VNP/system01.dbf
channel d1: starting piece 1 at 06-OCT-08
channel d1: finished piece 1 at 06-OCT-08
piece handle=/vnprctl/backupFullVNP/Level0_VNP_1590_1_hmjsfc3i.bkp tag=LEVEL0 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:35
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00005 name=/vnporadataUSERS/oradataUSERS/tool.dbf
channel d1: starting piece 1 at 06-OCT-08
channel d1: finished piece 1 at 06-OCT-08
piece handle=/vnprctl/backupFullVNP/Level0_VNP_1591_1_hnjsfc4l.bkp tag=LEVEL0 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:25
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00007 name=/vnporadataUSERS/oradataUSERS/logvnp.dbf
channel d1: starting piece 1 at 06-OCT-08
channel d1: finished piece 1 at 06-OCT-08
piece handle=/vnprctl/backupFullVNP/Level0_VNP_1592_1_hojsfc5e.bkp tag=LEVEL0 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:15
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00003 name=/vnporadataSYSTEM/VNP/sysaux01.dbf
channel d1: starting piece 1 at 06-OCT-08
channel d1: finished piece 1 at 06-OCT-08
piece handle=/vnprctl/backupFullVNP/Level0_VNP_1593_1_hpjsfc5t.bkp tag=LEVEL0 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:25
channel d1: starting incremental level 0 datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 06-OCT-08
channel d1: finished piece 1 at 06-OCT-08
piece handle=/vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_06/o1_mf_ncnn0_TAG20081006T115315_4gm8c70q_.bkp tag=TAG20081006T115315 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:02
released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on d1 channel at 10/06/2008 12:07:41
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 42949672960 limit

RMAN> **end-of-file**

RMAN>

Do you clarify it to me?

Thank you very much!

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352172 is a reply to message #352081]

Mon, 06 October 2008 08:30

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Your db_recovery_file_dest_size is showing 42949672960, yet the alert log is showing the size as 32212254720. I assume you resized this area?

It appears that one file alone keeps filling up this area. Do you have one file that is massive ?

How big are your normal level 0 backups?

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352184 is a reply to message #352172]

Mon, 06 October 2008 09:17

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

These are the Level 0 backup sets of my database. I do not use the option backup as compressed backupset in 10g. These are normal.

login: oracle
Password:
Last login: Mon Oct  6 20:37:57 from 10.149.33.23
Sun Microsystems Inc.   SunOS 5.10      Generic January 2005
You have mail.
$ df -k /vnprctl/backupfullVNP
df: (/vnprctl/backupfullVNP) not a block device, directory or mounted resource
$ ls -l /vnprctl/backupFullVNP
total 115530064
-rw-r-----   1 oracle   dba      29660545024 Oct  6 12:15 Level0_VNP_1587_1_hjjs
fb4d.bkp
-rw-r-----   1 oracle   dba      22745407488 Oct  6 12:22 Level0_VNP_1588_1_hkjs
fbjj.bkp
-rw-r-----   1 oracle   dba      3610296320 Oct  6 12:24 Level0_VNP_1589_1_hljsf
c08.bkp
-rw-r-----   1 oracle   dba      953409536 Oct  6 12:24 Level0_VNP_1590_1_hmjsfc
3i.bkp
-rw-r-----   1 oracle   dba      1328857088 Oct  6 12:25 Level0_VNP_1591_1_hnjsf
c4l.bkp
-rw-r-----   1 oracle   dba      7643136 Oct  6 12:25 Level0_VNP_1592_1_hojsfc5e
.bkp
-rw-r-----   1 oracle   dba      816291840 Oct  6 12:25 Level0_VNP_1593_1_hpjsfc
5t.bkp
$

I have 2 big size datafiles in one tablespace USERS, each datafile is 30Gb. The other tablespace has small size datafile

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

SQL> @/u02/app/oracle/tbs
+------------------------------------------------------------------+
+--Identified tablespaces accosiated with datafiles--+
+------------------------------------------------------------------+

TBS_NAME             DFT_NAME                                 MB
-------------------- ------------------------------ ------------
SYSTEM               /vnporadataSYSTEM/VNP/system01        2,000
                     .dbf

UNDOTBS1             /vnporadataSYSTEM/VNP/undotbs0        6,000
                     1.dbf

SYSAUX               /vnporadataSYSTEM/VNP/sysaux01          880
                     .dbf

USERS                /vnporadataUSERS/oradataUSERS/       30,720
                     users01.dbf

TBS_NAME             DFT_NAME                                 MB
-------------------- ------------------------------ ------------

USERS                /vnporadataUSERS/oradataUSERS/       30,720
                     users02.dbf

TOOL                 /vnporadataUSERS/oradataUSERS/        1,500
                     tool.dbf

LOGVNP               /vnporadataUSERS/oradataUSERS/        1,024
                     logvnp.dbf


7 rows selected.

SQL>

Quote:

Your db_recovery_file_dest_size is showing 42949672960, yet the alert log is showing the size as 32212254720. I assume you resized this area?

I really do not understand why the Oracle show me error when I did not switch on flashback option?

I think, I will delete 3 parameters:
- db_recovery_file_dest
- db_recovery_file_dest_size
- db_flashback_retetion

in my pfile, and create spfile after that.

If I do that, does rman not show error like above?

Thank you very much!

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352187 is a reply to message #352184]

Mon, 06 October 2008 09:39

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Flashback database and Flash Recovery Area are two different things. To see if flashback database is on, check the value of FLASHBACK_ON in V$DATABASE.

Can you provide the output of your backup script and the output of SHOW ALL from RMAN? If I added it up correctly, it appears that the sum of your backuppieces in /vnprctl/backupFullVNP are more than you've allocated for the FRA.

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352193 is a reply to message #352187]

Mon, 06 October 2008 10:11

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

Quote:

Oh, I see, you said and I remember, that's my mistake.
And the RMAN's policy


SQL> ! rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Oct 6 22:15:53 2008

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

connected to target database: VNP (DBID=54722743)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
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 CHANNEL DEVICE TYPE DISK FORMAT   '/vnprctl/backupFullVNP/FullVNP%d_%s
_%p_%u';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/vnprctl/snapshotVNP/snapcf_VNP.f';

RMAN> crosscheck backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=956 devtype=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1587_1_hjjsfb4d.bkp recid=
1557 stamp=667397261
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1588_1_hkjsfbjj.bkp recid=
1558 stamp=667397747
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1589_1_hljsfc08.bkp recid=
1559 stamp=667398157
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1590_1_hmjsfc3i.bkp recid=
1560 stamp=667398258
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1591_1_hnjsfc4l.bkp recid=
1561 stamp=667398293
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1592_1_hojsfc5e.bkp recid=
1562 stamp=667398318
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1593_1_hpjsfc5t.bkp recid=
1563 stamp=667398333
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_06/o1_
mf_ncnn0_TAG20081006T115315_4gm8c70q_.bkp recid=1564 stamp=667398359
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_159
5_667398996_1.bkp recid=1565 stamp=667398997
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_159
6_667399442_1.bkp recid=1566 stamp=667399443
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_159
7_667399889_1.bkp recid=1567 stamp=667399890
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_159
8_667400295_1.bkp recid=1568 stamp=667400296
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_159
9_667400762_1.bkp recid=1569 stamp=667400763
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_160
0_667401208_1.bkp recid=1570 stamp=667401209
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_160
1_667401635_1.bkp recid=1571 stamp=667401636
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_160
2_667401801_1.bkp recid=1572 stamp=667401802
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_160
3_667401827_1.bkp recid=1573 stamp=667401828
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_160
4_667401844_1.bkp recid=1574 stamp=667401845
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_160
5_667401870_1.bkp recid=1575 stamp=667401871
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_160
6_667401886_1.bkp recid=1576 stamp=667401887
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_160
7_667401902_1.bkp recid=1577 stamp=667401903
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_06/o1_
mf_nnnd1_TAG20081006T132518_4gmctj2p_.bkp recid=1578 stamp=667401920
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_160
9_667401935_1.bkp recid=1579 stamp=667401936
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/vnporadataSYSTEM/vnp_flashback/VNP/autobackup/2008_10_06/o1
_mf_s_667401940_4gmcv47p_.bkp recid=1580 stamp=667401940
Crosschecked 24 objects

RMAN>
RMAN> list backup of database;


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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1557    Incr 0  27.62G     DISK        00:07:56     06-OCT-08
        BP Key: 1557   Status: AVAILABLE  Compressed: NO  Tag: LEVEL0
        Piece Name: /vnprctl/backupFullVNP/Level0_VNP_1587_1_hjjsfb4d.bkp
  List of Datafiles in backup set 1557
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4    0  Incr 23307655660 06-OCT-08 /vnporadataUSERS/oradataUSERS/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1558    Incr 0  21.18G     DISK        00:06:44     06-OCT-08
        BP Key: 1558   Status: AVAILABLE  Compressed: NO  Tag: LEVEL0
        Piece Name: /vnprctl/backupFullVNP/Level0_VNP_1588_1_hkjsfbjj.bkp
  List of Datafiles in backup set 1558
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  6    0  Incr 23307723474 06-OCT-08 /vnporadataUSERS/oradataUSERS/users02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1559    Incr 0  3.36G      DISK        00:01:42     06-OCT-08
        BP Key: 1559   Status: AVAILABLE  Compressed: NO  Tag: LEVEL0
        Piece Name: /vnprctl/backupFullVNP/Level0_VNP_1589_1_hljsfc08.bkp
  List of Datafiles in backup set 1559
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2    0  Incr 23307789375 06-OCT-08 /vnporadataSYSTEM/VNP/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1560    Incr 0  909.23M    DISK        00:00:32     06-OCT-08
        BP Key: 1560   Status: AVAILABLE  Compressed: NO  Tag: LEVEL0
        Piece Name: /vnprctl/backupFullVNP/Level0_VNP_1590_1_hmjsfc3i.bkp
  List of Datafiles in backup set 1560
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 23307807391 06-OCT-08 /vnporadataSYSTEM/VNP/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1561    Incr 0  1.24G      DISK        00:00:22     06-OCT-08
        BP Key: 1561   Status: AVAILABLE  Compressed: NO  Tag: LEVEL0
        Piece Name: /vnprctl/backupFullVNP/Level0_VNP_1591_1_hnjsfc4l.bkp
  List of Datafiles in backup set 1561
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5    0  Incr 23307807451 06-OCT-08 /vnporadataUSERS/oradataUSERS/tool.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1562    Incr 0  7.28M      DISK        00:00:09     06-OCT-08
        BP Key: 1562   Status: AVAILABLE  Compressed: NO  Tag: LEVEL0
        Piece Name: /vnprctl/backupFullVNP/Level0_VNP_1592_1_hojsfc5e.bkp
  List of Datafiles in backup set 1562
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7    0  Incr 23307822157 06-OCT-08 /vnporadataUSERS/oradataUSERS/logvnp.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1563    Incr 0  778.47M    DISK        00:00:15     06-OCT-08
        BP Key: 1563   Status: AVAILABLE  Compressed: NO  Tag: LEVEL0
        Piece Name: /vnprctl/backupFullVNP/Level0_VNP_1593_1_hpjsfc5t.bkp
  List of Datafiles in backup set 1563
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3    0  Incr 23307822171 06-OCT-08 /vnporadataSYSTEM/VNP/sysaux01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1578    Incr 1  10.37M     DISK        00:00:08     06-OCT-08
        BP Key: 1578   Status: AVAILABLE  Compressed: YES  Tag: TAG20081006T132
18
        Piece Name: /vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_06/o1
mf_nnnd1_TAG20081006T132518_4gmctj2p_.bkp
  List of Datafiles in backup set 1578
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 23308387742 06-OCT-08 /vnporadataSYSTEM/VNP/system01.dbf
  2    1  Incr 23308387742 06-OCT-08 /vnporadataSYSTEM/VNP/undotbs01.dbf
  3    1  Incr 23308387742 06-OCT-08 /vnporadataSYSTEM/VNP/sysaux01.dbf
  4    1  Incr 23308387742 06-OCT-08 /vnporadataUSERS/oradataUSERS/users01.dbf
  5    1  Incr 23308387742 06-OCT-08 /vnporadataUSERS/oradataUSERS/tool.dbf
  6    1  Incr 23308387742 06-OCT-08 /vnporadataUSERS/oradataUSERS/users02.dbf
  7    1  Incr 23308387742 06-OCT-08 /vnporadataUSERS/oradataUSERS/logvnp.dbf

May you clarify me something more?

Thank you!

[Updated on: Mon, 06 October 2008 10:15]

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352207 is a reply to message #352193]

Mon, 06 October 2008 10:51

ebrian
Messages: 2794
Registered: April 2006

Senior Member

I'm sorry, I meant a copy of your actual backup script. It appears two backups are being taken.

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352208 is a reply to message #352207]

Mon, 06 October 2008 10:59

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

Yes! This is my crontab job, the job calls to the rman backup script weekly/daily. The following script is for Level0 backup that I had taken job.

login: oracle
Password:
Last login: Mon Oct  6 21:19:53 from 10.149.33.23
Sun Microsystems Inc.   SunOS 5.10      Generic January 2005
You have mail.
$ crontab -l
30 02 * * 0 /vnporadataSYSTEM/backupIncrementalVNP/rmanscheduleLevel0
00 05 * * * /vnporadataSYSTEM/backupIncrementalVNP/rmanschedule
30 20 * * 6 rm -f /u02/app/oracle/admin/VNP/adump/ora*.aud

$ cat /vnporadataSYSTEM/backupIncrementalVNP/rmanscheduleLevel0
ORACLE_HOME=/u02/app/oracle/oracle/product/10.2.0/NEO
export ORACLE_HOME
ORACLE_SID=VNP
export ORACLE_SID
PATH=/usr/sbin:/usr/bin:/u02/app/oracle/oracle/product/10.2.0/NEO/bin
export PATH
rman target sys/neovnp@vnp @/vnporadataSYSTEM/backupIncrementalVNP/Level0VNP.rc$

$ cat /vnporadataSYSTEM/backupIncrementalVNP/Level0VNP.rc
run{
allocate channel d1 type disk;
backup incremental level 0 database
tag=level0
format '/vnprctl/backupFullVNP/Level0_%d_%s_%p_%u.bkp'
filesperset 1
(database include current controlfile);
backup archivelog all format '/vnporadataUSERS/backuparchiveVNP/arc%d_%s.bkp' tag ARCBKP;
crosscheck archivelog all;
delete archivelog all;
delete obsolete;
release channel d1;
}$
$

[Updated on: Mon, 06 October 2008 11:01]

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352210 is a reply to message #352208]

Mon, 06 October 2008 11:14

ebrian
Messages: 2794
Registered: April 2006

Senior Member

From what I can tell, it appears that (database include current controlfile) can be written as (include current controlfile) or removed completely (a controlfile should be backed up already when you conduct the backup the way you have it configured).

This appears to be backing up the database again after the level 0 has finished.

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352212 is a reply to message #352210]

Mon, 06 October 2008 11:31

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

Oh, following your advice, I will remove the line (include current control file) in the RMAN script.

Exactly, some months ago, I configured one separate catalog database to execute backup it - vnp database, and this script was made when the RMAN's policy Configure autobackup controlfile off were default. 2 months ago, I removed the catalog database because some reasons. And that scripts was leave without re-editing.

I will re-write as

run{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup as compressed backupset incremental level 0 database plus archivelog 
tag=Level0
format '/vnprctl/backupFullVNP/Level_0_%d_%s_%t_%p.bkp
filesperset 2;
crosscheck backup;
delete obsolete;
delete expired backup;
release channel d1;
release channel d2;
}

I can use this option compressed because this job executes at 2.30am every Sunday, when lowest sessions, users and something as batch_jobs else.

And I allocate 2 channels, because the server has 4 CPUs.

Am I correct?

Thank you so much!

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352216 is a reply to message #352212]

Mon, 06 October 2008 11:43

ebrian
Messages: 2794
Registered: April 2006

Senior Member

trantuananh24hg wrote on Mon, 06 October 2008 12:31

Sure, that seems realistic.

This should work (I haven't tested it for syntax - no warranties expressed or implied).

run{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup as compressed backupset incremental level 0 database tag=Level0
format '/vnprctl/backupFullVNP/Level_0_%d_%s_%t_%p.bkp' filesperset 2
plus archivelog 
format '/vnporadataUSERS/backuparchiveVNP/arc%d_%s.bkp' tag=ARCBKP;
crosscheck backup;
delete obsolete;
delete expired backup;
release channel d1;
release channel d2;
}

I reformatted the script to accommodate some of the format & TAG settings you specified in your previous script, so feel free to remove/add what you need.

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352219 is a reply to message #352216]

Mon, 06 October 2008 12:12

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

No, I do not need remove any line in your reformatted script.

Thank you very much! Have a nice day!

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352248 is a reply to message #352219]

Mon, 06 October 2008 21:21

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

Thank you, ebrian, one more time.

I have fixed problem, by re-formatted rman's script, and deallocation location of db_recovery_file_dest

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /vnporadataSYSTEM/vnp_flashbac
                                                 k
db_recovery_file_dest_size           big integer 40G
SQL> alter system set db_recovery_file_dest='';     

System altered.

SQL> alter system switch logfile;

System altered.

SQL> ! ls -l /vnporadataSYSTEM/vnp_flashback
total 2
drwxr-x---   4 oracle   dba          512 Sep 19 11:02 VNP

SQL> ! rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 7 09:28:16 2008

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

connected to target database: VNP (DBID=54722743)

RMAN> @/vnporadataSYSTEM/backupIncrementalVNP/Level0VNP.rc
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup as compressed backupset incremental level 0 database plus archivelog 
tag=Level0
format '/vnprctl/backupFullVNP/Level_0_%d_%s_%t_%p.bkp'
filesperset 2;
delete obsolete;
delete expired backup;
release channel d1;
release channel d2;
}
1559    1559    1   1   AVAILABLE   DISK        /vnprctl/backupFullVNP/Level0_VNP_1589_1_hljsfc08.bkp
1560    1560    1   1   AVAILABLE   DISK        /vnprctl/backupFullVNP/Level0_VNP_1590_1_hmjsfc3i.bkp
1561    1561    1   1   AVAILABLE   DISK        /vnprctl/backupFullVNP/Level0_VNP_1591_1_hnjsfc4l.bkp
1562    1562    1   1   AVAILABLE   DISK        /vnprctl/backupFullVNP/Level0_VNP_1592_1_hojsfc5e.bkp
1563    1563    1   1   AVAILABLE   DISK        /vnprctl/backupFullVNP/Level0_VNP_1593_1_hpjsfc5t.bkp
1564    1564    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_06/o1_mf_ncnn0_TAG20081006T115315_4gm8c70q_.bkp
1565    1565    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1595_667398996_1.bkp
1566    1566    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1596_667399442_1.bkp
1567    1567    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1597_667399889_1.bkp
1568    1568    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1598_667400295_1.bkp
1569    1569    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1599_667400762_1.bkp
1570    1570    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1600_667401208_1.bkp
1571    1571    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1601_667401635_1.bkp
1572    1572    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1602_667401801_1.bkp
1573    1573    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1603_667401827_1.bkp
1574    1574    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1604_667401844_1.bkp
1575    1575    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1605_667401870_1.bkp
1576    1576    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1606_667401886_1.bkp
1577    1577    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1607_667401902_1.bkp
1578    1578    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_06/o1_mf_nnnd1_TAG20081006T132518_4gmctj2p_.bkp
1579    1579    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1609_667401935_1.bkp
1580    1580    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/vnp_flashback/VNP/autobackup/2008_10_06/o1_mf_s_667401940_4gmcv47p_.bkp
1581    1581    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1611_1_ibjsh6ej.bkp
1582    1582    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_07/o1_mf_nnnd1_TAG20081007T050003_4go2m4hn_.bkp
1583    1583    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_07/o1_mf_nnnd1_TAG20081007T050003_4go2p3kb_.bkp
1584    1584    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1613_1_idjsh6hj.bkp
1585    1585    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_07/o1_mf_nnnd1_TAG20081007T050003_4go2q6kz_.bkp
1586    1586    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_07/o1_mf_ncnn1_TAG20081007T050003_4go2qftj_.bkp
1587    1587    1   1   AVAILABLE   DISK        /vnporadataSYSTEM/vnp_flashback/VNP/autobackup/2008_10_07/o1_mf_s_667458142_4go2qh18_.bkp

Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1587_1_hjjsfb4d.bkp recid=1557 stamp=667397261
deleted backup piece
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1588_1_hkjsfbjj.bkp recid=1558 stamp=667397747
deleted backup piece
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1589_1_hljsfc08.bkp recid=1559 stamp=667398157
deleted backup piece
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1590_1_hmjsfc3i.bkp recid=1560 stamp=667398258
deleted backup piece
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1591_1_hnjsfc4l.bkp recid=1561 stamp=667398293
deleted backup piece
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1592_1_hojsfc5e.bkp recid=1562 stamp=667398318
deleted backup piece
backup piece handle=/vnprctl/backupFullVNP/Level0_VNP_1593_1_hpjsfc5t.bkp recid=1563 stamp=667398333
deleted backup piece
backup piece handle=/vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_06/o1_mf_ncnn0_TAG20081006T115315_4gm8c70q_.bkp recid=1564 stamp=667398359
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1595_667398996_1.bkp recid=1565 stamp=667398997
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1596_667399442_1.bkp recid=1566 stamp=667399443
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1597_667399889_1.bkp recid=1567 stamp=667399890
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1598_667400295_1.bkp recid=1568 stamp=667400296
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1599_667400762_1.bkp recid=1569 stamp=667400763
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1600_667401208_1.bkp recid=1570 stamp=667401209
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1601_667401635_1.bkp recid=1571 stamp=667401636
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1602_667401801_1.bkp recid=1572 stamp=667401802
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1603_667401827_1.bkp recid=1573 stamp=667401828
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1604_667401844_1.bkp recid=1574 stamp=667401845
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1605_667401870_1.bkp recid=1575 stamp=667401871
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1606_667401886_1.bkp recid=1576 stamp=667401887
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1607_667401902_1.bkp recid=1577 stamp=667401903
deleted backup piece
backup piece handle=/vnporadataSYSTEM/vnp_flashback/VNP/backupset/2008_10_06/o1_mf_nnnd1_TAG20081006T132518_4gmctj2p_.bkp recid=1578 stamp=667401920
deleted backup piece
backup piece handle=/vnporadataSYSTEM/backupIncrementalVNP/Level1/Level1_VNP_1609_667401935_1.bkp recid=1579 stamp=667401936
deleted backup piece
backup piece handle=/vnporadataSYSTEM/vnp_flashback/VNP/autobackup/2008_10_06/o1_mf_s_667401940_4gmcv47p_.bkp recid=1580 stamp=667401940
deleted backup piece
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
no obsolete backups found

released channel: d1

released channel: d2

RMAN> **end-of-file**

RMAN>

Some lines in this code are missed, and so that, I do not paste here. But every thing is normal now.

Have a nice day, ebrian!

Report message to a moderator

Re: Error with reclaim db_recovery_file_dest [message #352351 is a reply to message #352248]

Tue, 07 October 2008 04:48

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Thanks for the feedback! Glad it worked out for you.

Report message to a moderator

Previous Topic:	Recover database when windows Crash
Next Topic:	RMAN backup concept for Data Guard environment

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 12:35:40 CST 2024