Unable to login to application(Front end).Unable to connect to database from application host with apps user.SQL views getting hung,query to any view is displaying nothing
Alert log file having below error:
ARC3: Error 19809 Creating archive log file to '+ASM_TEST1'
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST1_arc0_5915.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 751619276800 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.
************************************************************************
ARC0: Error 19809 Creating archive log file to '+ASM_TEST1'
Verification:
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
SQL> show parameter db_recovery_file_dest_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 700G
SQL> show parameter db_recovery_file_dest ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +ASM_TEST1
db_recovery_file_dest_size big integer 700G
SQL>
Solution:
connect to RMAN
Rman> crosscheck archivelog all;
Output will display expired .arc logs
Rman> delete expired archivelog all;
This will delete the Expired archivelogs
or
If there are NO expired archive logs then backup the archive logs from the 'db_recovery_file_dest ' location to some other location (like TAPE) then delete archivelogs
RMAN> delete archivelog until time 'SYSDATE-7'; (deleting older than 7 days)
If db_recovery_file_dest_size is less, then increased it to high value to avoid the problem:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 500G scope=both;
Max value for db_recovery_file_dest_size is 17179869182G (in oracle version 10.2, 11.1, 11.2)
Query to check space used and max limit using:
select space_limit/1024/1024/1024 GB,space_used/1024/1024/1024 GB from v$recovery_file_dest;
Alert log file having below error:
ARC3: Error 19809 Creating archive log file to '+ASM_TEST1'
Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST1_arc0_5915.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 751619276800 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.
************************************************************************
ARC0: Error 19809 Creating archive log file to '+ASM_TEST1'
Verification:
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
SQL> show parameter db_recovery_file_dest_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 700G
SQL> show parameter db_recovery_file_dest ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +ASM_TEST1
db_recovery_file_dest_size big integer 700G
SQL>
Solution:
connect to RMAN
Rman> crosscheck archivelog all;
Output will display expired .arc logs
Rman> delete expired archivelog all;
This will delete the Expired archivelogs
or
If there are NO expired archive logs then backup the archive logs from the 'db_recovery_file_dest ' location to some other location (like TAPE) then delete archivelogs
RMAN> delete archivelog until time 'SYSDATE-7'; (deleting older than 7 days)
If db_recovery_file_dest_size is less, then increased it to high value to avoid the problem:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 500G scope=both;
Max value for db_recovery_file_dest_size is 17179869182G (in oracle version 10.2, 11.1, 11.2)
Query to check space used and max limit using:
select space_limit/1024/1024/1024 GB,space_used/1024/1024/1024 GB from v$recovery_file_dest;
No comments:
Post a Comment