Добрый день.
Такой вопрос как изменить параметр DB_RECOVERY_FILE_DEST_SIZE???

При бекапе выдалась ошибка ORA-19809: limit exceeded for recovery files

pravednik спасибо.

На самом деле ситуация такая:
При бекапе вылезла ошибка ORA-19809: limit exceeded for recovery files, теперь база не отвечает.

хотим польностью остановить базу, сделать бекап, затем изменить параметр DB_RECOVERY_FILE_DEST_SIZE.

Или можно както по другому????

человек ушел в отпуск, другой в командировке, а я не занимался этим, так процедуры, запросы писал.
Куда хоть двигаться, в каком направлении????

А как посмотреть размер который был установлен???

Сделали бекапы, подконектился, поменял значение DB_RECOVERY_FILE_DEST_SIZE (было 2, поставил 5)

Что дальше делать????

alert.log:

Fri May 16 15:19:52 2008
ALTER SYSTEM SET db_recovery_file_dest_size='5G' SCOPE=MEMORY;
Fri May 16 15:19:57 2008
ARC0: Evaluating archive thread 1 sequence 5006
db_recovery_file_dest_size of 5120 MB is 41.66% 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.
Committing creation of archivelog 'G:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\BUDJETGK\ARCHIVELOG\2008_05_16\O1_MF_1_5006_42TVGXFL_.ARC'
Created Oracle managed file G:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\BUDJETGK\ARCHIVELOG\2008_05_16\O1_MF_1_5006_42TVGXFL_.ARC
Archiver process freed from errors. No longer stopped
ARC0: Evaluating archive thread 1 sequence 5007
Fri May 16 15:19:57 2008
ARC1: Evaluating archive thread 1 sequence 5007
ARC1: Unable to archive thread 1 sequence 5007
Log actively being archived by another process
Fri May 16 15:19:57 2008
Committing creation of archivelog 'G:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\BUDJETGK\ARCHIVELOG\2008_05_16\O1_MF_1_5007_42TVGXMY_.ARC'
Created Oracle managed file G:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\BUDJETGK\ARCHIVELOG\2008_05_16\O1_MF_1_5007_42TVGXMY_.ARC
Fri May 16 15:54:31 2008
Shutting down instance: further logons disabled
Fri May 16 15:54:31 2008
Stopping background process CJQ0
Fri May 16 15:54:31 2008
Shutting down instance (immediate)
License high water mark = 2
Fri May 16 15:54:31 2008
Stopping Job queue slave processes
Fri May 16 15:54:31 2008
Job queue slave processes stopped
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Fri May 16 15:54:34 2008
alter database close normal
ORA-1109 signalled during: alter database close normal...
Starting up ORACLE RDBMS Version: 10.1.0.5.0.
System parameters with non-default values:
processes = 150
shared_pool_size = 83886080
large_pool_size = 8388608
java_pool_size = 50331648
control_files = G:\ORADB\BUDJETGKDBFILES\BUDJETGK\CONTROL01.CTL, G:\ORADB\BUDJETGKDBFILES\BUDJETGK\CONTROL02.CTL, G:\ORADB\BUDJETGKDBFILES\BUDJETGK\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 25165824
compatible = 10.1.0.2.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = g:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=budjetgkXDB)
job_queue_processes = 10
background_dump_dest = G:\ORACLE\PRODUCT\10.1.0\ADMIN\BUDJETGK\BDUMP
user_dump_dest = G:\ORACLE\PRODUCT\10.1.0\ADMIN\BUDJETGK\UDUMP
core_dump_dest = G:\ORACLE\PRODUCT\10.1.0\ADMIN\BUDJETGK\CDUMP
sort_area_size = 65536
db_name = budjetgk
open_cursors = 300
pga_aggregate_target = 25165824
PMON started with pid=2, OS id=4460
MMAN started with pid=3, OS id=5024
DBW0 started with pid=4, OS id=2492
LGWR started with pid=5, OS id=4356
CKPT started with pid=6, OS id=3276
SMON started with pid=7, OS id=3532
RECO started with pid=8, OS id=3476
CJQ0 started with pid=9, OS id=2156
Fri May 16 15:54:34 2008
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Oracle Data Guard is not available in this edition of Oracle.
Fri May 16 15:54:34 2008
alter database mount exclusive
Fri May 16 15:54:34 2008
Controlfile identified with block size 16384
Fri May 16 15:54:39 2008
Setting recovery target incarnation to 2
Fri May 16 15:54:39 2008
Successful mount of redo thread 1, with mount id 4092097018
Fri May 16 15:54:39 2008
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Fri May 16 15:54:39 2008
alter database open
Fri May 16 15:54:39 2008
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=13, OS id=2440
ARC0: Archival started
ARC1 started with pid=14, OS id=2776
Fri May 16 15:54:39 2008
LGWR: STARTING ARCH PROCESSES COMPLETE
Fri May 16 15:54:39 2008
LGWR: Primary database is in CLUSTER CONSISTENT mode
Fri May 16 15:54:40 2008
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Fri May 16 15:54:40 2008
Thread 1 advanced to log sequence 5009
Maximum redo generation record size = 120832 bytes
Maximum redo generation change vector size = 116476 bytes
Private_strands 7 at log switch
Thread 1 opened at log sequence 5009
Current log# 1 seq# 5009 mem# 0: G:\ORADB\BUDJETGKDBFILES\BUDJETGK\REDO01.LOG
Successful open of redo thread 1
Fri May 16 15:54:40 2008
ARC0: Evaluating archive thread 1 sequence 5008
Fri May 16 15:54:40 2008
Errors in file g:\oracle\product\10.1.0\admin\budjetgk\bdump\budjetgk_arc0_2440.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.

Fri May 16 15:54:40 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.
************************************************************************
Fri May 16 15:54:40 2008
Errors in file g:\oracle\product\10.1.0\admin\budjetgk\bdump\budjetgk_arc0_2440.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 10017792 bytes disk space from 2147483648 limit

ARC0: Error 19809 Creating archive log file to 'G:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\BUDJETGK\ARCHIVELOG\2008_05_16\O1_MF_1_5008_U_.ARC'
ARC0: All standby destinations failed; successful archival assumed
ARC0: Failed to archive thread 1 sequence 5008 (19809)
ARCH: Archival stopped, error occurred. Will continue retrying
ARCH:
Fri May 16 15:54:40 2008
ORA-16038: log 3 sequence# 5008 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'G:\ORADB\BUDJETGKDBFILES\BUDJETGK\REDO03.LOG'

потом перезапустили службу на серваке, щас пишет:

Fri May 16 16:55:45 2008
ARC1: Evaluating archive thread 1 sequence 5008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 5008 (4)
Fri May 16 16:55:45 2008
Errors in file g:\oracle\product\10.1.0\admin\budjetgk\bdump\budjetgk_arc1_2800.trc:
ORA-16014: log 3 sequence# 5008 not archived, no available destinations
ORA-00312: online log 3 thread 1: 'G:\ORADB\BUDJETGKDBFILES\BUDJETGK\REDO03.LOG'

Что делать без понятия, как понял изменить параметр DB_RECOVERY_FILE_DEST_SIZE мало, что потом????

именно так и писал:

SQL> alter system set db_recovery_file_dest_size = 5g;

Ñèñòåìà èçìåíåíà.

сделал так:

SQL> alter system set db_recovery_file_dest_size = 5g;

Ñèñòåìà èçìåíåíà.

SQL>alter system archive log CURRENT;


в логах написал :

Fri May 16 17:20:17 2008
ALTER SYSTEM SET db_recovery_file_dest_size='5G' SCOPE=MEMORY;
Fri May 16 17:20:33 2008
ARCH: Evaluating archive thread 1 sequence 5008
db_recovery_file_dest_size of 5120 MB is 41.85% 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.
Fri May 16 17:20:34 2008
Private_strands 7 at log switch
Thread 1 advanced to log sequence 5010
Fri May 16 17:20:34 2008
Current log# 2 seq# 5010 mem# 0: G:\ORADB\BUDJETGKDBFILES\BUDJETGK\REDO02.LOG
Fri May 16 17:20:34 2008
Committing creation of archivelog 'G:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\BUDJETGK\ARCHIVELOG\2008_05_16\O1_MF_1_5008_42V2K1XK_.ARC'
Created Oracle managed file G:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\BUDJETGK\ARCHIVELOG\2008_05_16\O1_MF_1_5008_42V2K1XK_.ARC
Fri May 16 17:20:35 2008
ARC1: Evaluating archive thread 1 sequence 5008
Fri May 16 17:20:35 2008
ARC1: Unable to archive thread 1 sequence 5008
Log actively being archived by another process
ARC1: Evaluating archive thread 1 sequence 5009
Fri May 16 17:20:35 2008
ARCH: Evaluating archive thread 1 sequence 5009
ARCH: Unable to archive thread 1 sequence 5009
Log actively being archived by another process
Fri May 16 17:20:35 2008
Committing creation of archivelog 'G:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\BUDJETGK\ARCHIVELOG\2008_05_16\O1_MF_1_5009_42V2K39K_.ARC'
Created Oracle managed file G:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\BUDJETGK\ARCHIVELOG\2008_05_16\O1_MF_1_5009_42V2K39K_.ARC
Archiver process freed from errors. No longer stopped

перезапустили процесс, теперь подключиться можно, все вроде работает в логах вот что написал после запуска:

Fri May 16 17:32:53 2008
Starting background process MMNL
MMNL started with pid=17, OS id=5044
Fri May 16 17:32:54 2008
Completed: alter database open
Fri May 16 17:32:57 2008
Errors in file g:\oracle\product\10.1.0\admin\budjetgk\bdump\budjetgk_mmon_5584.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.

Fri May 16 17:32:57 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.
************************************************************************

после этого вроде без ошибок не ругается, и в логах ниче не пишет

не хочет:


а в логах до этого написал:

авторне хочет, потому что стартовали с PFILE-а......
ищите свой пфайл. Тушите базу, редактируйте в пфайле db_recovery_file_dest_size и стартуйте с него же.

как это сделать???

на сервере через управление компютером