It might be just because the lack of space in the recovery dest.
SQL> startup
ORACLE instance started.
Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 385877416 bytes
Database Buffers 385875968 bytes
Redo Buffers 5259264 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4152
Session ID: 5 Serial number: 3
Check the alert log for further investination.
==============================================
contents from alert log
=======================
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file c:\app\rpilla\diag\rdbms\mntest1\mntest1\trace\mntest1_arc2_4956.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 2866688 bytes disk space from 4039114752 limit
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 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.
************************************************************************
I used the 4th suggestion by deleting archive log files from db_recovery_file_dest.
login
rman target /
list backup;
list copy;
delete archivelog all;
ORA-19815: WARNING: db_recovery_file_dest_size is 100.00% used,
Sat May 30 08:28:20 2015
ARC2 started with pid=22, OS id=38112
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Sat May 30 08:28:20 2015
ARC3 started with pid=23, OS id=39704
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_arc1_39816.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 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.
************************************************************************
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44892672 bytes disk space from 4039114752 limit
ARCH: Error 19809 Creating archive log file to 'E:\APP\MURTHY\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_05_30\O1_MF_1_98_%U_.ARC'
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-16038: log 2 sequence# 98 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'E:\APP\MURTHY\ORADATA\ORCL\REDO02.LOG'
USER (ospid: 38608): terminating the instance due to error 16038
************************************************************************
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.
************************************************************************
Instance terminated by USER, pid = 38608
To check the free space :
select name,
floor(space_limit/1024/1024) "Size_MB",
ceil(space_used/1024/1024) "Used_MB"
from v$recovery_file_dest
order by name;
/
Solution :
open CMD>
1. RMAN
2. connect /
3. crosscheck archivelog all;
4. delete archivelog all completed before 'sysdate -7';
5. Prompt for Yes or No
6. Y and enter
7. exit
C:\Users\jnmurthy>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 30 08:44:13 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1381995653)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
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 MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\MURTHY\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default
before deleted the file location and size
SYS@orcl:SQL>show parameter recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:\app\Murthy\flash_recovery_area
db_recovery_file_dest_size big integer 30M
Above 3rd and 4th command check for the archieve log file and delete the old archieve log file which is older than the week from current date.
So Now we have free space in "flash_recovery_area" .
After deleted the backup-ed archive files:
SYS@orcl:SQL>show parameter recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:\app\Murthy\flash_recovery_area
db_recovery_file_dest_size big integer 300M
After successfully deleted will get the space and then try to startup the db.
SYS@orcl:SQL>startup
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1375792 bytes
Variable Size 713032144 bytes
Database Buffers 352321536 bytes
Redo Buffers 4603904 bytes
Database mounted.
Database opened.
more commands :
SQL> show parameter alert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert boolean FALSE
SQL> show parameter BACKGROUND_DUMP_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string e:\app\****\diag\rdbms\za1\za1\trace
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 3852M
SQL> startup
ORACLE instance started.
Total System Global Area 778387456 bytes
Fixed Size 1374808 bytes
Variable Size 385877416 bytes
Database Buffers 385875968 bytes
Redo Buffers 5259264 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4152
Session ID: 5 Serial number: 3
Check the alert log for further investination.
==============================================
contents from alert log
=======================
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file c:\app\rpilla\diag\rdbms\mntest1\mntest1\trace\mntest1_arc2_4956.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 2866688 bytes disk space from 4039114752 limit
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 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.
************************************************************************
I used the 4th suggestion by deleting archive log files from db_recovery_file_dest.
login
rman target /
list backup;
list copy;
delete archivelog all;
ORA-19815: WARNING: db_recovery_file_dest_size is 100.00% used,
Sat May 30 08:28:20 2015
ARC2 started with pid=22, OS id=38112
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Sat May 30 08:28:20 2015
ARC3 started with pid=23, OS id=39704
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_arc1_39816.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 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.
************************************************************************
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44892672 bytes disk space from 4039114752 limit
ARCH: Error 19809 Creating archive log file to 'E:\APP\MURTHY\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_05_30\O1_MF_1_98_%U_.ARC'
Errors in file e:\app\murthy\diag\rdbms\orcl\orcl\trace\orcl_ora_38608.trc:
ORA-16038: log 2 sequence# 98 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'E:\APP\MURTHY\ORADATA\ORCL\REDO02.LOG'
USER (ospid: 38608): terminating the instance due to error 16038
************************************************************************
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.
************************************************************************
Instance terminated by USER, pid = 38608
To check the free space :
select name,
floor(space_limit/1024/1024) "Size_MB",
ceil(space_used/1024/1024) "Used_MB"
from v$recovery_file_dest
order by name;
/
Solution :
open CMD>
1. RMAN
2. connect /
3. crosscheck archivelog all;
4. delete archivelog all completed before 'sysdate -7';
5. Prompt for Yes or No
6. Y and enter
7. exit
C:\Users\jnmurthy>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 30 08:44:13 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1381995653)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
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 MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\MURTHY\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default
before deleted the file location and size
SYS@orcl:SQL>show parameter recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:\app\Murthy\flash_recovery_area
db_recovery_file_dest_size big integer 30M
Above 3rd and 4th command check for the archieve log file and delete the old archieve log file which is older than the week from current date.
So Now we have free space in "flash_recovery_area" .
After deleted the backup-ed archive files:
SYS@orcl:SQL>show parameter recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:\app\Murthy\flash_recovery_area
db_recovery_file_dest_size big integer 300M
After successfully deleted will get the space and then try to startup the db.
SYS@orcl:SQL>startup
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1375792 bytes
Variable Size 713032144 bytes
Database Buffers 352321536 bytes
Redo Buffers 4603904 bytes
Database mounted.
Database opened.
more commands :
SQL> show parameter alert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert boolean FALSE
SQL> show parameter BACKGROUND_DUMP_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string e:\app\****\diag\rdbms\za1\za1\trace
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 3852M
No comments:
Post a Comment
Thanks for giving comments!!