Another strange parameter change issue....
My db_recovery_file_dest_size was set to 52G. I decided to lower that value to 5G because I don't expect of using it or whatever...
After the change, restarted database and got below warning in the alert.log:
..
..
Tue Dec 20 12:09:47 2011
Errors in file /oracle/admin/diag/rdbms/rd11852/RD11852/trace/RD11852_m000_31348.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5368709120 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from 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.
************************************************************************
..
..
Values for FRA related parameters:
SQL> show parameter recovery
NAME
------------------------------------
TYPE VALUE
-------------------------------------------- ------------------------------
db_recovery_file_dest
string /oracle/admin/fast_recovery_area
db_recovery_file_dest_size
big integer 5G
recovery_parallelism
integer 0
Checked for available space on OS:
$ cd /oracle/admin/fast_recovery_area/
$ df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/vgsan-oracle
30963708 16639264 14324444 54% /oracle
$ ls -ltr
total 4
drwxr-s--- 3 oracle dba 4096 Oct 24 10:33 RD11852
oracle@strudel:/oracle/admin/fast_recovery_area[RD11852]
$ du -sk RD11852
16 RD11852
oracle@strudel:/oracle/admin/fast_recovery_area[RD11852]
$ du -sk .
20 .
That means enough free space was available at OS level.
Checking database views related to FRA:
SQL> select * from v$recovery_file_dest;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oracle/admin/fast_recovery_area
5368709120 1.1520E+10 0 2
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE
--------------------------------------------------------------------------------
PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------ ------------------------- ---------------
CONTROL FILE
0 0 0
REDO LOG
0 0 0
ARCHIVED LOG
0 0 0
BACKUP PIECE
214.58 0 2
IMAGE COPY
0 0 0
FLASHBACK LOG
0 0 0
FOREIGN ARCHIVED LOG
0 0 0
7 rows selected.
Strange that it showed two backup pieces consuming around 214.58% of FRA.
Decided to check using RMAN:
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 10.72G DISK 00:12:04 24-OCT-11
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20111024T134517
Piece Name: /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/system01.dbf
2 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/undo01.dbf
3 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/sysaux01.dbf
4 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/axiom01.dbf
5 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/banaq01.dbf
6 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/baniam01.dbf
7 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/banlob.dbf
8 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl01.dbf
9 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl02.dbf
10 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl03.dbf
11 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl04.dbf
12 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl05.dbf
13 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl06.dbf
14 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl07.dbf
15 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl08.dbf
16 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl09.dbf
17 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl10.dbf
18 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/tools01.dbf
19 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/users01.dbf
20 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/xdb01.dbf
21 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl11.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.64M DISK 00:00:01 24-OCT-11
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20111024T134517
Piece Name: /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp
SPFILE Included: Modification time: 08-JUN-11
Control File Included: Ckp SCN: 7443640574332 Ckp time: 24-OCT-11
Checked on OS if those backup pieces existed:
$ ls -ltr /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp
/bin/ls: /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp: No such file or directory
$ ls -ltr /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp
/bin/ls: /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp: No such file or directory
Decided to delete those backups using rman:
RMAN> crosscheck backup;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp RECID=2 STAMP=765380718
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp RECID=3 STAMP=765381453
Crosschecked 2 objects
RMAN> delete backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
2 2 1 1 EXPIRED DISK /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp
3 3 1 1 EXPIRED DISK /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp RECID=2 STAMP=765380718
deleted backup piece
backup piece handle=/oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp RECID=3 STAMP=765381453
Deleted 2 objects
Verifying database views:
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE
--------------------------------------------------------------------------------
PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------ ------------------------- ---------------
CONTROL FILE
0 0 0
REDO LOG
0 0 0
ARCHIVED LOG
0 0 0
BACKUP PIECE
0 0 0
IMAGE COPY
0 0 0
FLASHBACK LOG
0 0 0
FOREIGN ARCHIVED LOG
0 0 0
Now that showed "0".
Restarted database:
Database alert.log showed below now. ..
..
Tue Dec 20 12:38:21 2011
db_recovery_file_dest_size of 5120 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.
..
..
Regards,
Vishal
My db_recovery_file_dest_size was set to 52G. I decided to lower that value to 5G because I don't expect of using it or whatever...
After the change, restarted database and got below warning in the alert.log:
..
..
Tue Dec 20 12:09:47 2011
Errors in file /oracle/admin/diag/rdbms/rd11852/RD11852/trace/RD11852_m000_31348.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5368709120 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from 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.
************************************************************************
..
..
Values for FRA related parameters:
SQL> show parameter recovery
NAME
------------------------------------
TYPE VALUE
-------------------------------------------- ------------------------------
db_recovery_file_dest
string /oracle/admin/fast_recovery_area
db_recovery_file_dest_size
big integer 5G
recovery_parallelism
integer 0
Checked for available space on OS:
$ cd /oracle/admin/fast_recovery_area/
$ df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/vgsan-oracle
30963708 16639264 14324444 54% /oracle
$ ls -ltr
total 4
drwxr-s--- 3 oracle dba 4096 Oct 24 10:33 RD11852
oracle@strudel:/oracle/admin/fast_recovery_area[RD11852]
$ du -sk RD11852
16 RD11852
oracle@strudel:/oracle/admin/fast_recovery_area[RD11852]
$ du -sk .
20 .
That means enough free space was available at OS level.
Checking database views related to FRA:
SQL> select * from v$recovery_file_dest;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oracle/admin/fast_recovery_area
5368709120 1.1520E+10 0 2
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE
--------------------------------------------------------------------------------
PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------ ------------------------- ---------------
CONTROL FILE
0 0 0
REDO LOG
0 0 0
ARCHIVED LOG
0 0 0
BACKUP PIECE
214.58 0 2
IMAGE COPY
0 0 0
FLASHBACK LOG
0 0 0
FOREIGN ARCHIVED LOG
0 0 0
7 rows selected.
Strange that it showed two backup pieces consuming around 214.58% of FRA.
Decided to check using RMAN:
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 10.72G DISK 00:12:04 24-OCT-11
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20111024T134517
Piece Name: /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/system01.dbf
2 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/undo01.dbf
3 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/sysaux01.dbf
4 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/axiom01.dbf
5 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/banaq01.dbf
6 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/baniam01.dbf
7 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/banlob.dbf
8 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl01.dbf
9 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl02.dbf
10 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl03.dbf
11 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl04.dbf
12 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl05.dbf
13 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl06.dbf
14 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl07.dbf
15 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl08.dbf
16 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl09.dbf
17 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl10.dbf
18 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/tools01.dbf
19 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/users01.dbf
20 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/xdb01.dbf
21 Full 7443640574332 24-OCT-11 /u02/oradata/RD11852/devl11.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.64M DISK 00:00:01 24-OCT-11
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20111024T134517
Piece Name: /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp
SPFILE Included: Modification time: 08-JUN-11
Control File Included: Ckp SCN: 7443640574332 Ckp time: 24-OCT-11
Checked on OS if those backup pieces existed:
$ ls -ltr /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp
/bin/ls: /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp: No such file or directory
$ ls -ltr /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp
/bin/ls: /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp: No such file or directory
Decided to delete those backups using rman:
RMAN> crosscheck backup;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp RECID=2 STAMP=765380718
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp RECID=3 STAMP=765381453
Crosschecked 2 objects
RMAN> delete backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
2 2 1 1 EXPIRED DISK /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp
3 3 1 1 EXPIRED DISK /oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_nnndf_TAG20111024T134517_7bc91gr2_.bkp RECID=2 STAMP=765380718
deleted backup piece
backup piece handle=/oracle/admin/fast_recovery_area/RD11852/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T134517_7bc9rf90_.bkp RECID=3 STAMP=765381453
Deleted 2 objects
Verifying database views:
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE
--------------------------------------------------------------------------------
PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------ ------------------------- ---------------
CONTROL FILE
0 0 0
REDO LOG
0 0 0
ARCHIVED LOG
0 0 0
BACKUP PIECE
0 0 0
IMAGE COPY
0 0 0
FLASHBACK LOG
0 0 0
FOREIGN ARCHIVED LOG
0 0 0
Now that showed "0".
Restarted database:
Database alert.log showed below now. ..
..
Tue Dec 20 12:38:21 2011
db_recovery_file_dest_size of 5120 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.
..
..
Regards,
Vishal
No comments:
Post a Comment