Monitor Oracle query status -- ELAPSED TIME SO FAR

Monitor the queries.
set linesize 1000
col units format a10
col target format a8
col opname format a20
Select round(sofar*100/totalwork,2)"finished(%)",
Sid,
Serial#,
Opname,
Target,
Sofar,
Totalwork,
Units,
(Time_Remaining/60) Time_Rem_Mins,
(Elapsed_Seconds/60) Elapsed_Time_Mins
From V$session_Longops
where TIME_REMAINING>0


If you want to monitor the SQL queries it is worth including the sql info like

SQL_ID,
SQL_PLAN_HASH_VALUE HASH_VALUE,
SQL_PLAN_LINE_ID LINE_ID,

ORA-00494: enqueue [CF] held for too long on NFS mount

DB Version: 11.2.0.1.

NFS was mounted with below options and when we try to backup the controlfile via sqlplus or RMAN. we get the below enqueue error.
no rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,forcedirectio,vers=3,suid


Backup on NFS mount:
SQL> set time on echo on feedback on
09:49:36 SQL> alter database backup controlfile to '/NFSMOUNT/cloning/control_nfs_09142011.ctl';
alter database backup controlfile to '/NFSMOUNT/cloning/control_nfs_09142011.ctl'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 23041
Session ID: 4796 Serial number: 395


10:16:40 SQL>

Alert log:
alter database backup controlfile to '/NFSMOUNT/cloning/control_nfs_09142011.ctl'
Wed Sep 14 10:04:19 2011
Incremental checkpoint up to RBA [0x128.85a78.0], current log tail at RBA [0x128.870c8.0]
Wed Sep 14 10:05:19 2011
Errors in file /icm01/u0001/app/diag/rdbms/icmqc/ICMQC1/trace/ICMQC1_diag_8307.trc (incident=956919):
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 23041'
Incident details in: /icm01/u0001/app/diag/rdbms/icmqc/ICMQC1/incident/incdir_956919/ICMQC1_diag_8307_i956919.trc
Wed Sep 14 10:05:25 2011
Sweep [inc][956919]: completed
Sweep [inc2][956919]: completed
Wed Sep 14 10:06:19 2011
Killing enqueue blocker (pid=23041) on resource CF-00000000-00000000 by (pid=8685)
by killing session 4796.395
Wed Sep 14 10:10:39 2011
Killing enqueue blocker (pid=23041) on resource CF-00000000-00000000 by (pid=8321)
by killing session 4796.395
Wed Sep 14 10:16:39 2011
Killing enqueue blocker (pid=23041) on resource CF-00000000-00000000 by (pid=8321)
by terminating the process
Wed Sep 14 10:22:05 2011
Errors in file /icm01/u0001/app/diag/rdbms/icmqc/ICMQC1/trace/ICMQC1_diag_8307.trc (incident=956920):
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 23041'
Incident details in: /icm01/u0001/app/diag/rdbms/icmqc/ICMQC1/incident/incdir_956920/ICMQC1_diag_8307_i956920.trc
Wed Sep 14 10:22:35 2011
Sweep [inc][956920]: completed
Sweep [inc2][956920]: completed
Wed Sep 14 10:24:23 2011
Incremental checkpoint up to RBA [0x128.87fc1.0], current log tail at RBA [0x128.88243.0]
Wed Sep 14 10:28:19 2011
Killing enqueue blocker (pid=23041) on resource CF-00000000-00000000 by (pid=8685)
by killing session 4796.395
Wed Sep 14 10:32:39 2011
Killing enqueue blocker (pid=23041) on resource CF-00000000-00000000 by (pid=8321)
by terminating the process

Backup on local disk:


SQL> alter database backup controlfile to '/ora_backup/u0001/control_disk_09142011.ctl';

Database altered.

SQL>

Solution:

ORA-02020: too many database links in use

Got the below error:

ORA-02020: too many database links in use at

In the OBIEE query testing.

State: HY000. Code: 0. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 2020, message: ORA-02020: too many database links in use at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed.

Solution:
By default you will have only 4 links open and if your query is using more than 4 db links then you might get the above error.


Increase the open_links and open_links_instance parameter in the DB to appropriate level.
and bounce the db & app servers.

if the above parameter is set to appropriate values then consider closing the open db links using

SQL>alter session close database link "link name";

Oracle post db creation steps

-- Disable 10g/11g Automatic stats.
execute dbms_scheduler.disable('GATHER_STATS_JOB');

-- Configure AWR Retention Settings (30days/15 min for PROD, 15day/30 min for QA)
-- For Production, the SQL is
execute dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => 43200);
-- QA
-- execute dbms_workload_repository.modify_snapshot_settings(interval => 30, retention => 21600);

-- Enable Block change Tracking as needed
alter database enable block change tracking ;

--
-- dbsnmp user
alter user dbsnmp identified by ;
alter user dbsnmp account unlock ;

-- other modifications << Modify the values before execution >>>
--
alter system set recyclebin=OFF scope=spfile sid='*' ;
alter system set db_file_multiblock_read_count=0 scope=spfile sid='*' ;
-- alter system set processes=1500 scope=spfile sid='*' ;
-- alter system set shared_pool_size=2000G scope=spfile sid='*' ;
-- alter system set db_cache_size=1500M scope=spfile sid='*' sid='*' ;
-- alter system set sga_target=0 scope=spfile sid='*' ;
-- alter system set large_pool_size=50M scope=spfile sid='*' ;
-- alter system set java_pool_size=50M scope=spfile sid='*' ;
alter system set parallel_execution_message_size=16384 scope=spfile sid='*' ;
-- alter system set instance_groups= scope=spfile sid='*' ;
-- alter system set parallel_instance_group= scope=spfile sid='*' sid='*' ;
alter system set local_listener=DBISPCDP scope=spfile sid='*' ;
alter system set remote_listener=DBISPCDP scope=spfile sid='*' ;
alter system set db_recovery_file_dest_size=100G scope=spfile sid='*' ;
-- alter system set db_recovery_file_dest=<< >> scope=spfile sid='*' ;
alter system set log_archive_dest_1='LOCATION=use_db_recovery_file_dest' scope=spfile sid='*' ;
alter system set undo_retention=3600 scope=spfile sid='*' ;

Oracle tablespace status report ....

ACCEPT tbsp PROMPT 'Enter the Tablespace name in Alert:'
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN ALLOCATED_MB FORMAT 999,999,990.00
COLUMN FREE_MB FORMAT 99,999,990.00
COLUMN MAX_MB FORMAT 999,999,990.00
COLUMN PCT_FREE FORMAT 90.00
COLUMN PCT_MAX_FREE FORMAT 999,999,990.00
COLUMN AVAILABLE_MB FORMAT 999,999,990.00
COLUMN NEXT_MB FORMAT 999,999,990.00
COLUMN tbsp FORMAT A30 heading "TABLESPACE"
COLUMN file_name FORMAT A70 HEADING "DATA FILE(S)"
COLUMN AUTOEXTENSIBLE FORMAT A7 HEADING "AUTOEXT"
COLUMN BLS NEW_VALUE BLOCK_SIZE
set verify off
SELECT BLOCKSIZE BLS FROM SYS.TS$ WHERE NAME='&tbsp' ;
set lines 136 pages 50

SELECT T.TABLESPACE_NAME tbsp,
ROUND(T.MB_TOTAL,2) "ALLOCATED_MB",
ROUND(F.MB_FREE,2) "FREE_MB",
ROUND(F.MB_FREE/ROUND(T.MB_TOTAL)*100,2) PCT_FREE,
ROUND(T.MB_MAX,2) "MAX_MB",
ROUND((T.MB_MAX-(T.MB_TOTAL-F.MB_FREE))/ROUND(T.MB_MAX)*100,2) PCT_MAX_FREE,
ROUND(F.MB_FREE + T.MB_MAX - T.MB_TOTAL) AVAILABLE_MB
FROM
(
SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) MB_FREE
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) MB_TOTAL,
SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024) MB_MAX
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE T.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
AND T.TABLESPACE_NAME='&tbsp'
ORDER BY pct_max_free, PCT_FREE, AVAILABLE_MB, ALLOCATED_MB ;
SELECT FILE_NAME, ROUND(BYTES/1024/1024,2) "ALLOCATED_MB", ROUND(MAXBYTES/1024/1024,2) "MAX_MB", AUTOEXTENSIBLE ,
INCREMENT_BY*&&BLOCK_SIZE/1024/1024 "NEXT_MB"
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '&tbsp' ;