Active Sql Monitor

SYS AS SYSDBA>l
1 select sesion.sid,
2 sesion.serial#,
3 sesion.username,
4 sesion.sql_id,
5 sesion.sql_child_number,
6 optimizer_mode,
7 hash_value,
8 address,
9 sql_text
10 from v$sqlarea sqlarea, v$session sesion
11 where sesion.sql_hash_value = sqlarea.hash_value
12 and sesion.sql_address = sqlarea.address
13 and sesion.username is not null
14* and sql_text not like 'select sesion.sid,%'

Successful archiving of previously failed ORA-16014 ORA-00312

ORA-16038: log 1 sequence# 106 cannot be archived





Mon Nov 8 09:36:43 2010
ORA-16014: log 1 sequence# 106 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/cccdb/logs/redo01.log'
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/cccdb/logs/redo02.log'
Mon Nov 8 09:36:43 2010
Errors in file /opt/oracle/admin/cccdb/bdump/cccdb_arc0_668.trc:
ORA-16014: log 1 sequence# 106 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/cccdb/logs/redo01.log'
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/cccdb/logs/redo02.log'
Mon Nov 8 09:38:07 2010
PMON failed to delete process, see PMON trace file
Mon Nov 8 09:41:47 2010
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Nov 8 09:41:47 2010
ORACLE Instance cccdb - Archival Error
Mon Nov 8 09:41:47 2010
ORA-16038: log 1 sequence# 106 cannot be archived
ORA-00001: unique constraint (.) violated
Mon Nov 8 09:41:47 2010
Errors in file /opt/oracle/admin/cccdb/bdump/cccdb_arc0_668.trc:
ORA-16038: log 1 sequence# 106 cannot be archived
ORA-00001: unique constraint (.) violated
Mon Nov 8 09:41:53 2010
kcrrdmx: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Mon Nov 8 09:41:54 2010
Thread 1 advanced to log sequence 109 (LGWR switch)
Current log# 1 seq# 109 mem# 0: /opt/oracle/oradata/cccdb/logs/redo01.log
Current log# 1 seq# 109 mem# 1: /opt/oracle/oradata/cccdb/logs/redo02.log
Mon Nov 8 09:41:57 2010
PMON deletion of process succeeded
Mon Nov 8 09:42:42 2010


check the mount point ... is it full ?

if yes then

change the destination .....

alter system set log_archive_dest_1='location=/opt/oracle/backup/arch' scope=both;

or
delete the archive logfile

or
But not a very good idea ....


SQL> ALTER SYSTEM ARCHIVE LOG STOP;

System altered.



SQL> ALTER SYSTEM ARCHIVE LOG start;

System altered.