SQL>select FREE_MB from v$asm_diskgroup where NAME='PRNADGF';
FREE_MB
----------
2,761,992
SQL>show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL>alter system set db_recovery_file_dest_size=2700000M;
System altered.
SQL>alter system set db_recovery_file_dest='+PRNADGF';
System altered.
My problems started when I changed to Archive Log Mode
SQL>archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Current log sequence 12
SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup mount
ORACLE instance started.
Total System Global Area 3156803584 bytes
Fixed Size 2110872 bytes
Variable Size 402655848 bytes
Database Buffers 2734686208 bytes
Redo Buffers 17350656 bytes
Database mounted.
SQL>alter database archivelog;
Database altered.
SQL>alter database open;
Database altered.
SQL>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
So the archive log files should be written to my FRA on ASM, but when I tested it I got a unexpected error:
SQL>alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 3 sequence# 12 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 3 thread 1: '+PRNADG1/rnatst/onlinelog/group_3.278.672669129'
This error message is misleading because there is nothing wrong with the FRA on ASM. I found the real error message in the alert.log file:
ARCH: Encountered disk I/O error 19502
ARCH: Closing local archive destination LOG_ARCHIVE_DEST_1: '/opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch1_12_672668870.dbf' (error 19502)
(rnatst)
Errors in file /opt/apps/oracle/diag/rdbms/rnatst/rnatst/trace/rnatst_ora_21990.trc:
So the archiver also wants to archive to my $ORACLE_HOME/dbs directory, it cannot do that because there is not enough space on that filesystem, but the real question is why does it want to write to that destination?
When I do a show parameter I don't get the answer:
SQL> show parameter log_archive_dest
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_min_succeed_dest integer 1
Again I found the answer in the alert.log
Starting ORACLE instance (normal)
Using LOG_ARCHIVE_DEST_1 parameter default value as /opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
It seems to be a DEFAULT destination that does not show up when I use ARCHIVE LOG LIST or SHOW PARAMETER LOG_ARCHIVE_DEST.
It does show up in V$ARCHIVE_DEST though:
SQL> select dest_id, destination, status from V$ARCHIVE_DEST;
DEST_ID DESTINATION STATUS
---------- ------------------------------------------------------- ---------
1 /opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch VALID
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
7 INACTIVE
8 INACTIVE
9 INACTIVE
10 USE_DB_RECOVERY_FILE_DEST VALID
To solve the problem:
SQL> alter system set log_archive_dest_state_1=DEFER;
And to test
SQL>alter system archive log current;
System altered.
SQL>select dest_id, name from v$archived_log;
DEST_ID NAME
---------- ----------------------------------------------------------------------
10 +PRNADGF/rnatst/archivelog/2008_12_22/thread_1_seq_12.256.674133347
SQL> select dest_id, destination, status from V$ARCHIVE_DEST;
DEST_ID DESTINATION STATUS
---------- ------------------------------------------------------- ---------
1 /opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch DISABLED
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
7 INACTIVE
8 INACTIVE
9 INACTIVE
10 USE_DB_RECOVERY_FILE_DEST VALID
Lesson learned.
Keep on looking until you can explain the error.
