ORA-00054: resource busy and acquire with NOWAIT specified Posted by MOHAMMED ABDUL HAMEED on 6:11 AM

SQL> alter table Details add (mobile varchar2(15));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Workaround :

1 . In Oracle 11g you can set ddl_lock_timeout i.e. allow DDL to wait 
for the object to become available, simply specify how long you would like it to wait:

SQL> alter session set ddl_lock_timeout = 500;
Session altered.

SQL> alter table Details add (mobile varchar2(15));
Table altered.

2.  We can mark your table as read-only to prevent DML:
SQL> alter table emp read only;
Session altered.

SQL> alter table Details add (mobile varchar2(15));
Table altered. 

3. This workaround is not preferred on production database , can you use according to the criticality of the problem. 

Identify the session which is locking the object

SQL> select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c 
where b.object_id = c.object_id 
and a.sid = b.session_id
and OBJECT_NAME='Details';

kill that session using

SQL> alter system kill session 'sid,serial#'; 

ORA-09817: Write to audit file failed. Posted by MOHAMMED ABDUL HAMEED on 11:01 PM

[oracle@exdprd]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 26 07:59:43 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on

Workaround :

1. Check the free space of mount point /u01 

[oracle@exdprd]$ df -h

2. Remove old and unwanted trace and audit files.

3 . Check the space now on mount pount /u01

4. Now we will be able to login successfully. 

Storage Terminology Posted by MOHAMMED ABDUL HAMEED on 11:16 PM

1  B (Byte)  = 8 Bits.

1 KB (Kilo Byte)  = 1024 B or 103 (Bytes).

1 MB (Mega Byte) = 1024 KB or 106 (Kilo Bytes).

1 GB (Giga Byte) = 1024 MB or 109 (Mega Bytes).

1 TB (Tera Byte) = 1024 GB or 1012 (Giga Bytes).

1 PB (Peta Byte) = 1024 TB or 1015 (Tera Bytes).

1 EB (Exa Byte) = 1024 PB or 1018 (Peta Bytes).

1 ZB (Zetta Byte) = 1024 EB or 1021 (Exa Bytes).

1 YB (Yotta Byte) = 1024 ZB or 1024 (Zetta Bytes).

1  (Bronto Byte) = 1024 YB or 1027 (Yotta Bytes).

1  (Geop Byte) = 1024 ZB or 1030 (Bronto Bytes).

Network Time Protocol (NTP) Posted by MOHAMMED ABDUL HAMEED on 12:58 PM

Network Time Protocol (NTP) is a networking protocol for clock synchronization between computer systems over packet-switched, variable-latency data networks.

The protocol is usually described in terms of a client-server model, but can as easily be used in peer-to-peer relationships where both peers consider the other to be a potential time source.

Clock synchronization :

A typical NTP client will regularly poll three or more servers on diverse networks. To synchronize its clock, the client must compute their time offset and round-trip delay. 

Round Trip Delay :

d=(T4-T1)-(T2-T3)

Time Offset :

t=(T2-T1)+(T3-T4)/2

Oracle Database 18c Posted by MOHAMMED ABDUL HAMEED on 9:49 AM

Larry Ellison introduced the first Autonomous Database Cloud with Oracle Database 18c at Oracle OpenWorld 2017, which runs on EXADATA infrastructure. The Oracle Autonomous Database Cloud uses ground-breaking machine learning to eliminate the human labor associated with tuning, patching, updating and maintaining the database and includes the following capabilities

1. Self-Driving: Provides continuous adaptive performance tuning based on machine learning. Automatically upgrades and patches itself while running. Automatically applies security updates while running to protect against cyber-attacks.

2. Self-Scaling: Instantly resizes compute and storage without downtime. Cost savings are multiplied because Oracle Autonomous Database Cloud consumes less compute and storage than Amazon, with lower manual administration costs.

3. Self-Repairing: Provides automated protection from downtime. SLA guarantees 99.995 percent reliability and availability, which reduces costly planned and unplanned downtime to less than 30-minutes per year.

DBConsole Not starting: An instance of Oracle Enterprise Manager Database Control is already running Posted by MOHAMMED ABDUL HAMEED on 4:46 AM

Cause : 

Process did not shutdown cleanly form a previous run.

Workaround :

1) Ensure all dbconsole related processes are not running:

ps ef | grep dbconsole
ps ef| grep emagent
kill -9


2) Start the dbconsole

$ emctl start dbconsole

Keeping SSH Connections Alive Posted by MOHAMMED ABDUL HAMEED on 5:51 AM

We often experience problems when using the SSH connections from the PuTTY such as dropped , inactive or unresponsive connections.

Workaround :

1.Start PuTTY.

2.Load your connection session.

3.In the Category pane, click Connection.

4.Under Sending of null packets to keep session active, in the Seconds between keepalives, type 240.

5.With this configuration, PuTTY sends a packet to the server every 240 seconds (4 minutes) to keep    the connection alive.

6.Click Save.

Fix : Unable to spawn jobq slave process, slot 0, error 1089 Posted by MOHAMMED ABDUL HAMEED on 4:38 AM

While shutting down our test database we observe the below mention message in the alert log continuously : 

kkjcre1p: unable to spawn jobq slave process, slot 0, error 1089

Probably its because of below mention reasons :

1) If we have fired "shut immediate" and presses "^C" to cancel the command.

OR

2) Some memory issue and you unable to down / start the database again.

Workaround :

Analysis the condition if its not due to memory then startup force.

$ sqlplus / as sysdba

SQL> startup force;

This will cause your database will be force fully down and start it again.

Note: Shut abort will not work in this situation.

Killing the Processes from Command Prompt Posted by MOHAMMED ABDUL HAMEED on 1:06 PM

Advanced Scripts for Indexes - Part 5 Posted by MOHAMMED ABDUL HAMEED on 3:37 PM

Rebuild Index Syntax

Syntax: alter index index_name rebuild;

SQL> alter index emp_ename rebuild;

Index altered.

Rebuild Index of multiple users :

Syntax : Select  'alter index ' || OWNER ||'.'||INDEX_NAME || ' rebuild;' from all_indexes Where Owner = 'USER NAME'; 

SQL> Select  'alter index ' || OWNER ||'.'||INDEX_NAME || ' rebuild;' from all_indexes Where Owner = 'SCOTT'; 

alter index SCOTT.EMP_ENAME rebuild;
alter index SCOTT.DEPT_UNIQUE_INDEX rebuild;

Collect Statistics on an Index :

Syntax : ALTER INDEX index_name REBUILD COMPUTE STATISTICS;

SQL> alter index emp_ename REBUILD COMPUTE STATISTICS; 

Index altered.

Move Table and Index to Other Table Space

Syntax: 
ALTER TABLE schema.table_name MOVE TABLESPACE TABLESPACE_NAME;

SQL Code:
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || OWNER || ’ts’ ||';'
from ALL_TABLES
WHERE OWNER IN ('USER NAME', 'USER NAME', 'USER NAME');

Syntax: 
ALTER INDEX schema.index_name REBUILD TABLESPACE TABLESPACE_NAME;

SQL Code
select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || 'idx’;'
from ALL_INDEXES
WHERE OWNER IN ('USER NAME', 'USER NAME', 'USER NAME');

How to identify the index unusable

SQL Code:
select index_name, status, owner
from all_indexes
where STATUS = 'UNUSABLE';

How to rebuild index unusable

SQL Code:
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes

where STATUS = 'UNUSABLE';

Check Index Creation or Index Rebuild Date in Oracle

SQL> select table_name,index_name,last_analyzed from dba_indexes where table_name='xxxxxxx';

Note : Last_Analyzed column date changes when the index is rebuild.

Find index status of a Particular Table.

SQL> SELECT OWNER,TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='xxxxx';

How to find the index status of Particular Schema.

SQL> SELECT OWNER,TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE OWNER='xxxxxx';

Check particular index size

SQL> select segment_name,sum(bytes)/1024/1024 as "SIZE in MB" from user_segments where segment_name='XXXXX' group by segment_name;

Check all the indexes of a particular table.

SQL> SELECT DISTINCT TABLE_NAME,INDEX_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='xxxxxxx';

Find Global & Local Indexes in Oracle Partitions


SQL> select table_name,index_name,locality from user_part_indexes where table_name='XXXXX';