Category Archives: Oracle DBA Practice

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

ORA-15137: The ASM cluster is in rolling patch state

While adding a new disk into an existing diskgroup, below errors occurred: ORA-15032: not all alterations performed ORA-15137: The ASM cluster is in rolling patch state. 1) On both nodes, it shows the cluster in “In Rolling Patch”, and the … Continue reading

Posted in GI and RAC, ORA- ERRORS | Tagged , , , , , , , , , , , , , , , , , | Leave a comment

ORA-02085: database link DBLINK_TEST connects to TESTDB

Try yo query a remote table through a database link: SQL> select count(*) from jamesh.test@dblink_test; select count(*) from jamesh.test@dblink_test; * ERROR at line 1: ORA-02085: database link UDBLINK_TEST connects to TESTDB REASON Check database parameter global_names =TRUE. If parameter global_names =TRUE, the database … Continue reading

Posted in Client, Configuration | Tagged , , , | Leave a comment

ORA-27140 ORA-27300 ORA-27301 ORA-27302 ORA-273003 When Start Up or Shut Down RAC Instance

ORACLE_HOME is shared by both RAC instance A and RAC instance B. Both instance A and B are shut down, then ORACLE_HOME is patched, the ORACLE_HOME/bin/oracle binary will be as below just after the patching: $ id oracle uid=122(oracle) gid=202(oinstall) … Continue reading

Posted in ASM, GI and RAC, ORA- ERRORS | Tagged , , , , , , , , , , , , , , , | Leave a comment

CRS-2412: The Cluster Time Synchronization Service detects that the local time is significantly different from the mean cluster time

In crs alert.log, below message is shown every 30 minutes in 12.2.0.1 GI. [OCTSSD(9932)]CRS-2412: The Cluster Time Synchronization Service detects that the local time is significantly different from the mean cluster time ctss is running in observer mode, because NTP … Continue reading

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