https://community.oracle.com/thread/1081454?start=15&tstart=0
ORA-16038: log 3 sequence# 13 cannot be archived ORA-19504: failed to create file “”
4 Votes
When you try to open the database you may get the error ORA-16038,ORA-19809, ORA-00312.
[root@localhost ~]# su – oracle
-bash-3.2$ export ORACLE_SID=+asm
-bash-3.2$ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jun 7 02:51:44 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ASM instance started
Total System Global Area 130023424 bytes
Fixed Size 2019032 bytes
Variable Size 102838568 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
-bash-3.2$ export ORACLE_SID=dbtest
-bash-3.2$ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jun 7 02:52:26 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 96469032 bytes
Database Buffers 62914560 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-16038: log 3 sequence# 13 cannot be archived
ORA-19504: failed to create file “”
ORA-00312: online log 3 thread 1:
‘+DATA/dbtest/onlinelog/group_3.266.720140799’
ORA-00312: online log 3 thread 1:
‘+DATA/dbtest/onlinelog/group_3.267.720140805’
Cause of The Problem:
—————————————-
There was an attempt to archived the online log 3 but it could not archive the online log in the available archived log destination. The most common of happening the error is the archive log destination if full. You have flash recovery area configured and rman retention policy is failed to delete any archived or incremental backups and so can’t archived new online log.
Solution – Increase dynamically (without shutdown/startup database) the parameter db_recovery_file_dest_size and delete unwanted archivel log files to free up the space.
SQL> select * from v$asm_client;
GROUP_NUMBER INSTANCE_NAME
———— —————————————————————-
DB_NAME STATUS
——– ————
SOFTWARE_VERSION
————————————————————
COMPATIBLE_VERSION
————————————————————
1 +asm
dbtest CONNECTED
10.2.0.1.0
10.2.0.0.0
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
-bash-3.2$ asmcmd
asmcmd: command disallowed by current instance type
-bash-3.2$ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jun 7 02:57:53 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from v$logfile;
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
3 ONLINE
+DATA/dbtest/onlinelog/group_3.266.720140799
NO
3 ONLINE
+DATA/dbtest/onlinelog/group_3.267.720140805
YES
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
2 STALE ONLINE
+DATA/dbtest/onlinelog/group_2.264.720140789
NO
2 STALE ONLINE
+DATA/dbtest/onlinelog/group_2.265.720140793
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
YES
1 ONLINE
+DATA/dbtest/onlinelog/group_1.262.720140777
NO
1 ONLINE
GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
+DATA/dbtest/onlinelog/group_1.263.720140783
YES
6 rows selected.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME
——————————————————————————–
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
———– ———- —————– —————
+DATA
5368709120 684480000 0 24
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 13
Current log sequence 15
SQL> show parameter db_rec
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 5G
db_recycle_cache_size big integer 0
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME
——————————————————————————–
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
———– ———- —————– —————
+DATA
5368709120 684480000 0 24
SQL> alter system set db_recovery_file_dest =”” scope=both;
alter system set db_recovery_file_dest =”” scope=both
*
ERROR at line 1:
ORA-01741: illegal zero-length identifier
SQL> alter system set db_recovery_file_dest =” ” scope=both;
alter system set db_recovery_file_dest =” ” scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-38775: cannot disable flash recovery area – flashback database is enabled
SQL> select name,total_mb,free_mb from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB
—————————— ———- ———-
DATA 6589 2667
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 13 not archived, no available destinations
ORA-00312: online log 3 thread 1:
‘+DATA/dbtest/onlinelog/group_3.266.720140799’
ORA-00312: online log 3 thread 1:
‘+DATA/dbtest/onlinelog/group_3.267.720140805’
SQL> show parameter db_rec
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 5G
db_recycle_cache_size big integer 0
SQL> alter system set db_recovery_file_dest_size=10G scope=both;
System altered.
SQL> alter system set db_recovery_file_dest_size=15G scope=both;
System altered.
-bash-3.2$ export ORACLE_SID=dbtest
-bash-3.2$ sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jun 7 03:06:26 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
-bash-3.2$ rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Mon Jun 7 03:06:38 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBTEST (DBID=1113003619, not open)
RMAN> delete archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
——- —- ——- – ——— —-
1 1 1 A 27-MAY-10 +DATA/dbtest/archivelog/2010_05_31/thread_1_seq_1.272.720416503
2 1 2 A 31-MAY-10 +DATA/dbtest/archivelog/2010_06_01/thread_1_seq_2.278.720499021
3 1 3 A 01-JUN-10 +DATA/dbtest/archivelog/2010_06_02/thread_1_seq_3.281.720579491
4 1 4 A 02-JUN-10 +DATA/dbtest/archivelog/2010_06_03/thread_1_seq_4.282.720747463
5 1 5 A 03-JUN-10 +DATA/dbtest/archivelog/2010_06_04/thread_1_seq_5.283.720755633
6 1 6 A 04-JUN-10 +DATA/dbtest/archivelog/2010_06_04/thread_1_seq_6.284.720755869
7 1 7 A 04-JUN-10 +DATA/dbtest/archivelog/2010_06_04/thread_1_seq_7.285.720756011
8 1 8 A 04-JUN-10 +DATA/dbtest/archivelog/2010_06_04/thread_1_seq_8.286.720756141
9 1 9 A 04-JUN-10 +DATA/dbtest/archivelog/2010_06_04/thread_1_seq_9.287.720756203
10 1 10 A 04-JUN-10 +DATA/dbtest/archivelog/2010_06_04/thread_1_seq_10.288.720756277
11 1 11 A 04-JUN-10 +DATA/dbtest/archivelog/2010_06_04/thread_1_seq_11.289.720756341
12 1 12 A 04-JUN-10 +DATA/dbtest/archivelog/2010_06_04/thread_1_seq_12.290.720756447
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_05_31/thread_1_seq_1.272.720416503 recid=1 stamp=720416516
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_01/thread_1_seq_2.278.720499021 recid=2 stamp=720499030
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_02/thread_1_seq_3.281.720579491 recid=3 stamp=720579496
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_03/thread_1_seq_4.282.720747463 recid=4 stamp=720747471
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_04/thread_1_seq_5.283.720755633 recid=5 stamp=720755682
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_04/thread_1_seq_6.284.720755869 recid=6 stamp=720755886
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_04/thread_1_seq_7.285.720756011 recid=7 stamp=720756036
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_04/thread_1_seq_8.286.720756141 recid=8 stamp=720756155
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_04/thread_1_seq_9.287.720756203 recid=9 stamp=720756218
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_04/thread_1_seq_10.288.720756277 recid=10 stamp=720756287
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_04/thread_1_seq_11.289.720756341 recid=11 stamp=720756356
deleted archive log
archive log filename=+DATA/dbtest/archivelog/2010_06_04/thread_1_seq_12.290.720756447 recid=12 stamp=720756502
Deleted 12 objects
RMAN> exit
SQL> show parameter db_rec
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 15G
db_recycle_cache_size big integer 0
SQL> alter database open;
Database altered.
SQL>
NOw you can try to run asmcmd also:
-bash-3.2$ asmcmd
ASMCMD> ls
DATA/
