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.

cron run hourly ...

cron run every hr

every Hr ....
59 min =hr


59 * * * * echo " ">/var/log/warn > /dev/null 2>&1
59 * * * * echo " ">/var/log/messages > /dev/null 2>&1

expdp with Query

expdp "'/ as sysdba'" directory=datapump_dir DUMPFILE=user_sites.dmp TABLES=cccprod1.user_sites QUERY=ANUJ.user_sites:\"order by USER_OBJECT_ID,SITE_NO\" EXCLUDE=STATISTICS

with parallel option

-bash-3.00$ expdp "'/ as sysdba'" directory=datapump_dir DUMPFILE=user_sites%U.dmp TABLES=cccprod1.user_sites QUERY=cccprod1.user_sites:\"order by USER_OBJECT_ID,SITE_NO\" EXCLUDE=STATISTICS PARALLEL=4 LOGFILE=user_sites_exp.log job_name=anuj1

Oracle OCR and voting disk

OCR : File that manages the cluster and RAC configuration

Voting Disk : Contains and manages information of all the node memberships and avoid
split-brain

Oracle Licensing Option usage

Oracle Licensing Option usage

set pagesize 200
select name,last_usage_date from dba_feature_usage_statistics where detected_usages>0 order by 1;

SET PAGES 200
COL "Component" FORMAT A55 TRUNC
SELECT name "Component", currently_used "Used",first_usage_date "Start", last_usage_date "Last Time"
FROM dba_feature_usage_statistics;

RAC database user lock script

SQL> connect sys@rac1/sys as sysdba
Connected.



SQL> SELECT o.name object_name, u.name owner, lid.*
FROM (SELECT
s.inst_id, s.SID, s.serial#, p.spid,NVL (s.sql_id, 0), s.sql_hash_value,
DECODE (l.TYPE,
'TM', l.id1,
'TX', DECODE (l.request,
0, NVL (lo.object_id, -1),
s.row_wait_obj#
),
-1
) AS object_id,
l.TYPE lock_type,
DECODE (l.lmode,
0, 'NONE',
1, 'NULL',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE',
'?'
) mode_held,
DECODE (l.request,
0, 'NONE',
1, 'NULL',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE',
'?'
) mode_requested,
l.id1, l.id2, l.ctime time_in_mode,s.row_wait_obj#, s.row_wait_block#,
s.row_wait_row#, s.row_wait_file#
FROM gv$lock l,
gv$session s,
gv$process p,
(SELECT object_id, session_id, xidsqn
FROM gv$locked_object
WHERE xidsqn > 0) lo
WHERE l.inst_id = s.inst_id
AND s.inst_id = p.inst_id
AND s.SID = l.SID
AND p.addr = s.paddr
AND l.SID = lo.session_id(+)
AND l.id2 = lo.xidsqn(+)) lid,
SYS.obj$ o,
SYS.user$ u
WHERE o.obj#(+) = lid.object_id
AND o.owner# = u.user#(+)
AND object_id <> -1 ;