Category Archives: Oracle DBA Practice

Index on Timestamp column in Oracle Database

Index on timestamp is not used when compared with systimestamp value. SQL> desc james_test Name Null? Type ———— ——– ————————————- … .. EXPIRY_TIME TIMESTAMP(6) … .. Create an index on expiry_time column, and see the index is not used. SQL>CREATE INDEX JAMES_TEST_IDX1 ON JAMES_TEST (EXPIRY_TIME); … Continue reading

Posted in Performance Tunning | Tagged , , , , , , , | Leave a comment

Too many AQ$_PLSQL_NTFN_XXXXX jobs In Oracle Database

There are more than one million  AQ$_PLSQL_NTFN_xxxxxxxx scheduler jobs in oracle database.  some times sql hangs for querying dba_scheduler_jobs. Please note there are a couple of bugs on this issue, the below steps are not necessarily working for every database … Continue reading

Posted in dbms_scheduler | Tagged , , , , | Leave a comment

PMON (ospid: nnnn): terminating the instance due to error 481

ASM failed to start up with below messages in ASM alert log: Tue Oct 02 10:54:12 2018 ERROR: Network OS Ping failed to inst 1 on IP (169.254.255.151), Tue Oct 02 10:54:55 2018 Instance Critical Process (pid: 11, ospid: 8470, … Continue reading

Posted in GI and RAC | Tagged , , , , , , , , | Leave a comment

DataGuard ORA-07286 ORA-16055 ORA-19809 ORA-19804 ORA-19815

On primary database  side, below ORA errors in alert log: Sat Sep 22 03:56:48 2018 Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_arc4_20835.trc: ORA-07286: sksagdi: cannot obtain device information. Sat Sep 22 03:56:48 2018 ARC4: FAL archive failed with error 7286. See trace for … Continue reading

Posted in DataGuard, ORA- ERRORS | Tagged , , , , , , | Leave a comment

Long Running Operations Monitoring ( V$SESSION_LONGOPS ) in Oracle Database

List operations remaining time > 10 minutes : SQL>select username,sid, opname, target, sofar, totalwork, units, to_char(start_time,’YYYYMMDD-HH24:MI:SS’) StartTime, time_remaining, message  from V$SESSION_LONGOPS where time_remaining>600; USERNAME     SID    OPNAME     TARGET     SOFAR  TOTALWORK  UNITS    STARTTIME         TIME_REMAINING MESSAGE ———— —— ———- ———- ——- ———- ——- —————– ————– … Continue reading

Posted in SQL Scripts For DBA | Tagged , | Leave a comment

max_dump_file_size in 12c

MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert log). Modifiable ALTER SESSION, ALTER SYSTEM A numeric value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks. A numeric value followed by a K or M or … Continue reading

Posted in Configuration | Tagged | Leave a comment

Truncate a Table of Other User

In order to truncate a table of other user, the DROP ANY TABLE system privilege is required. Without granting this powerful privilege, instead, a procedure is created and granted to the user who can truncate other user’s tables. In this example, … Continue reading

Posted in SQL Scripts For DBA, User Privileges and Roles | Tagged , | Leave a comment

Gather Fixed Object Stats Hangs on X$DURABLE_SHARDED_SUBS

On 12.1.0.2 database, try to gather fixed table statistics with below command, it hangs at object “X$DURABLE_SHARDED_SUBS”.  Also hangs at select couint(*) from X$DURABLE_SHARDED_SUBS. SQL>EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS () SQL>select count(*) from X$DURABLE_SHARDED_SUBS; CAUSES It is a bug for this issue, it is supposed to … Continue reading

Posted in Performance Tunning | Tagged , | Leave a comment

How do I change my Oracle password ?

c:\> C:\>sqlplus james/Password@//ractest-scan.ractest.local:1521/service SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 31 11:44:13 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production With the Partitioning, Real Application Clusters and … Continue reading

Posted in Configuration, Security, User Privileges and Roles | Tagged , | Leave a comment

ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database

Oracle database instance hung due to recovery area full causing “Archival Stopped Error”. Check alert.log with below ORA errors: — Alert.log ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database. ORA-17502: ksfdcre:4 Failed to create file … Continue reading

Posted in ORA- ERRORS | Tagged , , , , | Leave a comment