Archivelog Size
Carefully plan Archivelog space, as, when archivelog space runs out, the database will freeze until space is available.
Log into the oracle user.
Set Oracle database environment, using
. oraenv
Connect to the database and get archive log destination and size information issuing the following command in the database directory:
sqlplus
connect / as sysdba
sql> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 304
Next log sequence to archive 305
Current log sequence 305
sql> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ -------------------------------- -------------
db_recovery_file_dest string +ORAFLASH001
db_recovery_file_dest_size big integer 10G
Increase archive log destination size. but 1st find out, How much archived
1) select count(*),sum(blocks*block_size) from v$archived_log where backup_count=0 and deleted='NO';
2) increase db_recovery_file_dest_size
ALTER SYSTEM SET db_recovery_file_dest_size = 20G SCOPE=BOTH SID='*';
3) SHOW PARAMETER RECOVERY
Check space utilization
sql> col name format a20
sql> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ----------- ---------- ----------------- ---------------
+ORAFLASH001 1.0737E+11 20971520 0 1
Sql> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE .02 0 1
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
7 rows selected.
List archived files not backed-up
select NAME, ARCHIVED , deleted, status
from v$archived_log
where status='A';
Hope this help. Regards Rupam
