Category Archives: Oracle database

Drop temp tablespace taking long time

Leave a reply

Created new temporary tablespace:

CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE ‘/u01/oracle/oradata/temp02.dbf’ SIZE 2g;

Make new temporary tablespace as default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;

Dropping old temporary tablespace, Which is taking long time to complete:

DROP TABLESPACE TEMP INCLDING CONTENTS AND DATAFILES;

The issue because of temp is being used by oracle sessions

SOLUTION 1:

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
SQL>SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;
SQL>DROP TABLEPSACE TEMP INCLUDIING CONTENTS AND DATAFILES;

SOLUTION 2:

Run below query and see any sessions using temp, Find out the all the sessions that are not active and kill those sessions.

select  tu.username,s.sid,s.serial# from v$tempseg_usage tu, v$session s
where tu.session_addr=s.saddr;

alter system kill session ‘sid,serial’;

Thank You !!

DROP DATABASE THROUGH RMAN

Leave a reply

In Oracle Database 10gR1 Oracle introduced the RMAN command DROP DATABSE. This one simple statement has the ability to completely remove a database including all RMAN backups with the optional INCLUDING BACKUPS clause.

sqlplus “/ as sysdba”

SQL> shutdown immediate;

SQL> startup mount exclusive

SQL> alter system enable restricted session

SQL> exit

rman target /

RMAN> drop database including backups;

 

Thank You !!

CHECK TABLESPACE SIZE, FREE AND USED SPACE

Leave a reply

Below query is used for finding total tablespace size and free, used space.

select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused
from (select a.tablespace_name, a.bytes_free,b.bytes_total,
ROUND((100-(100*a.bytes_free/b.bytes_total)),2) perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;

 

Thank You !!

ORA-12537: TNS:connection closed

1 Reply

Giving below error when trying to connect oracle user

ERROR:

Sqlplus scott/tiger@PROD

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Oct 16 15:06:46 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12537: TNS:connection closed

SOLUTION:

Add the relevant LD_LIBRARY_PATH into the SID_DESC section in listener.ora file

(SID_DESC =
(ORACLE_HOME = /home/oracle/product/1020)
(ENVS=”LD_LIBRARY_PATH=/home/oracle/product/1020/lib:/usr/dt/lib:/usr/openwin/lib”)
(SID_NAME = PROD)
)

 

Thank You !!

Oracle ORA-21561 : OID generation failed

Leave a reply

Giving below error when trying to connect from Oracle client

“ORA-21561 : OID generation failed”

ERROR:

sqlplus <test_user>/<test_user_password>@<database_name>
xxxxx xxxx
xxxx xxxx
ERROR:
ORA-21561 : OID generation failed

Enter user-name: <username>/<password> @ <tns connect string>

SOLUTION:

Then the problem is most likely in the client machine hosts file.
Check that there is client machine fully qualified name and short name in the client machine hosts file. If these are missing you’ll get ORA-21561 errors when trying to connect server.
Example:

127.0.0.1            localhost.localdomain      localhost
10.160.116.10   dbamasters.oracle.com     dbamasters

Thank You !!

ORA-01031: insufficient privileges when connect as sysdba

1 Reply

Not able to connect Oracle database, When issue command — > sqlplus “/ as sysdba”

[oraprod@oracle-dba ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 10.2.0.1.0 Production on Wed Jan 22 02:09:17 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges

SOLUTION:

SET CORRECT ORACLE SID
———————-
export ORACLE_SID=PROD         — LINUX
D:\oracle>set ORACLE_SID=PROD  — WINDOWS

SET ORACLE_HOME
—————
export ORACLE_HOME=/u01/oracle/product/10.2.0   — LINUX
set ORACLE_HOME=D:\oracle                                           — WINDOWS

SET SQLNET.ORA FILE
——————-
Check sqlnet.ora file for SQLNET.AUTHENTICATION_SERVICES parameter. It should be set to NTS for windows and for linux set it to ALL.

SQLNET.AUTHENTICATION_SERVICES=NTS  —WINDOWS
SQLNET.AUTHENTICATION_SERVICES=ALL  —LINUX

Please reproduce the issue and see if it resolves !!

ORA-27125: unable to create shared memory segment when run DBCA

5 Replies

I have installed 10gR2 on linux environment, When running DBCA it got filed with below error

ORA-27125: unable to create shared memory segment

SOLUTION:

cd $ORACLE_HOME/bin
 
mv oracle oracle.bin
 
 
—- Paste it as one —
 
cat >oracle <<“EOF”
#!/bin/bash
export DISABLE_HUGETLBFS=1
exec $ORACLE_HOME/bin/oracle.bin $@
EOF
 
—- End of paste —

chmod +x oracle

Thank You !!

NID Utility in Oracle – RENAME DATABASE

Leave a reply

DBNEWID (NID) utility was introduced in Oracle 10g, Which can change the database identifier (DBID) and the database name (DBNAME).

NID utility allows us to change

  • Only DBID
  • Only DBNAME
  • Both DBNAME and DBID of a database

Before performing below changes, Database should be in MOUNT status.

SHUTDOWN IMMEDIATE
STARTUP MOUNT

DBID Only:

nid TARGET=sys/password@PROD

DBNAME Only:

nid TARGET=sys/password@PROD DBNAME=TEST SETNAME=YES

BOTH DBNAME and DATABASE:

nid TARGET=sys/password@PROD DBNAME=TEST

DATABASE HANGS

Leave a reply

ERROR MESSAGE IN ALERTLOG FILE:

ORACLE Instance PROD – Archival Error
ORA-16038: log 4 sequence# 9961 cannot be archived
ORA-19502: write error on file “”, block number  (block size=)
ORA-00312: online log 4 thread 1: ‘/u01/oradata/data01/PROD/redo04.log’
ORA-16014: log 4 sequence# 9961 not archived, no available destinations

CAUSE:  Default archive log destination is 100% used, There is no space to create additional archive log

POSSIBLE ISSUES BECAUSE of archiving is stuck:

1. Database Hangs
2. Users not able to connect to database
3. Not able to open the database
4. Archive  space related error in alertlog file(ORA-16038,ORA-16014,ORA-19809 )

POSSIBLE SOLUTIONS:

1. Increase the ARCHIVE DESTINATION size
2. Take backup of the archivelogs to different location.
3. Change archivelogs destination to some other mountpoint.
4. Delete archivelogs to make more space. ( should be the last option) and in case of standby database make sure those logs are already applied to standby.

DATABASE SIZE (datafiles + tempfiles + redologfiles)

1 Reply

To get accurate size of the database, We have to combined the sizes of all datafiles, tempfiles and redologfiles.

select round ((a.data_size+b.temp_size+c.redo_size)/1024/1024/1024,2) “DB_size_in_GB”
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;