Lokeshaggarwal's DBA

Live the life you love. Love the life you live

ORA-16038: log 3 sequence# 13 cannot be archived ORA-19504: failed to create file “”


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/

Advertisements

June 7, 2010 - Posted by | ASM

2 Comments »

  1. this was the exact solution to remove the excess archive logs. Thanks!

    Comment by tom | August 21, 2012 | Reply

  2. Saved my day! Thanks

    Comment by Gab | January 9, 2013 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: