Oracle errors and solutions

ORA-Errors

===============================================================================================================================

Login / Oracle Space Issue

$ sqlplus sys/oracle as sysdba

$ orapwd file =orapwDYAXPROD PASSWORD=oracle force=y entries=3

orapwd file=orapwXE PASSWORD=oracle force=y entries=3

In Oracle 11g Default path for Alter log files is:

SQL> show parameter background_dump_dest

Ex: /u01/app/oracle/diag/rdbms/db01/db001/trace/alert_db001.log

For Issues:

SQL> show parameter background_dump_dest

Ex: /u01/app/oracle/diag/rdbms/db01/db001/trace/alert_db001.log

In Oracle : (1) Delete alert log files
(2) Drop unwanted user by taking the backup using expdp and keep the backup on the same server
(3) For Archive log issue, delete files from archive logs / # df -kh
SQL> archive log list;
(4) SQL DEVELOPER Connectivity issue listener
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

                sqlplus system/oracle as sysdba

                sqlplus / as sysdba
                Solution:

                sqlplus /nolog
                conn system/password 
                connected. 
                alter system register;
                exit

lsnrctl stat

Now you can see the service
Even if don’t see try this one out
$ sqlplus /nolog
SQL> conn system/password
connected.
SQL> alter system set local_listener = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))’ scope = both;
… system altered …
sql> alter system register;
SQL> exit
$ lsnrctl stat (IT SHOULD SHOW SERVICE REGISTERED NOW )

    This should probably work ...

===============================================================================================================================

ORA-12545: Connect failed because target host or object does not exist

Error remote connection to SCAN after Oracle RAC 11gR2 installation

If after the installation of Oracle RAC 11gR2, when you try to connect remotely you have the following error:

connecting on both nodes of RAC you have the following error:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

and both LISTENER and LISTENER_SCAN1 are ok.

Then look at database parameter local_listener.

If itís pointing to one of vip nameís (NODE1-vip or NODE2-vip), change it to RAC name (RAC01)

SQL> show parameter local_listener

NAME TYPE VALUE


local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=NODE1-vip)(PORT=1521))))

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PERF01-ORA01)(PORT=1521))))’;

System altered.

SQL> alter system register;

System altered.

===============================================================================================================================

ORA-01102: cannot mount database in EXCLUSIVE mode

I received this error message when I was trying to mount the primary database in my dataguard configuration.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01102: cannot mount database in EXCLUSIVE mode

Here is how I resolved it:

  • Shutdown the primary database

SQL> shu immediate;
ORA-01507: database not mounted

ñ Find out all the processes belonging to the database still running

[[email protected] dbs]$ ps -ef |grep ora_|grep pc00sb

ñ Kill all the processes

[[email protected] dbs]$ kill -9 32231

ñ Check that there are no more processes running

[[email protected] dbs]$ ps -ef |grep ora_|grep pc00sb

ñ startup the database ñ succeeeds

SQL> startup
ORACLE instance started.

Total System Global Area 439406592 bytes
Fixed Size 1337072 bytes
Variable Size 348129552 bytes
Database Buffers 83886080 bytes
Redo Buffers 6053888 bytes
Database mounted.
Database opened.

===============================================================================================================================

Setting FRA SIZE or disabling it /profile setup:

To setup the flash recovery area in your existing database, you need to set below mentioned required parameters in the database parameter file (PFILE) and also database needs a bounce or restart.
You can also set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters to setup a flash recovery area to a running/open database.

Steps to setup Flash Recovery Area?

  1. Connect to SQL*Plus as sysdba
  2. SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G SCOPE=BOTH SID=’*’;

Where 20G is the size of the flash recovery area size, SCOPE to BOTH will make the change to both memory and server parameter file, SID=í*í has no effect in the single instance database where as in RAC database it causes the change to take effect across all instance.

  1. Decide the physical location of the flash recovery area in the file system and execute below mentioned DDL to set the location.
    SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘ /opt/oracle/FRA’ SCOPE=BOTH SID=’*’;
  2. If the flash recovery area is in ASM (automatic storage management) disk group (eg. disk1), then you have to run the following.
    SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘+disk1′ SCOPE=BOTH SID=’*’;

Note:
DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.

How to check flash recovery area USAGE for oracle(10g, 11g) database?

There are two dictionary views to monitor the flash recovery area usage.

  1. V$RECOVER_FILE_DEST can be used to find out the physical location, allocated space, space reclaimable, number of files present in the directory.
    SQL> l
    1* SELECT * FROM V$RECOVERY_FILE_DEST
    SQL> /

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES


/apps/oracle/oradata/FRA 2.1475E+11 9.3252E+10 0 20

  1. V$flash_recovery_area_usage can be used to find out percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.

SQL> set pages 100
SQL> set lines 120
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE

===============================================================================================================================

How to disable flash recovery area of existing open Oracle (10g, 11g) database?

Flash recovery area can be disabled by setting DB_RECOVERY_FILE_DEST initialization parameter to a null value.
Connect to SQL*Plus as sys as sysdba and issue following command to disable the flash recover area.

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=” SCOPE=BOTH SID=”*”;

Once you disable the flash recovery area, the database will not provide any space management features of the flash recovery area for the files stored in the DB_RECOVER_FILE_DEST location. The existing files still can be accessible for the RMAN for backup and recovery purpose.

In a RAC database, all database instances MUST have the same values for these parameters.

Disable the Flash Recovery Area:

To disable FRA set the DB_RECOVERY_FILE_DEST initialization parameter to a null string.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ” scope=both;

Monitor the Flash recovery area using Dynamic performance views
To find out the current flash recovery area location, disk quota and current usage in flash recovery area query V$RECOVERY_FILE_DEST.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

The $FLASH_RECOVERY_AREA_USAGE views shows the percentage of the total disk quota used by different types files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

select profile from DBA_USERS where username = ‘RBMUSER’;

alter profile DEFAULT limit password_life_time UNLIMITED;

=============================================================================================================================

Inncreasing Processes or Max connection’s in oracle

How much connections are possible in Oracle 11g Express ?

There’s no hardcoded limit, however there is an effective default limit of ~20 concurrent connections, however you can extend that with something like: ALTER SYSTEM SET processes=200 scope=spfile; (and restart the DB)

In practice you’ve probably either hit other XE limits well before 200 connections, or should have been using a simpler DB to begin with.

By default, Oracle XE has the parameter PROCESSES=40. This parameter specifies the maximum number of OS user
processes that connect to the Oracle instance. This number must take into account the background processes and the
login process that started the instance.

Take a look on this below, I just open one connection:

SQL> show parameter processes

NAME TYPE VALUE


processes integer 40

SQL> select count(*) from v$process;

COUNT(*)


    21

SQL>

This mean that left just 19 processes that Oracle will accept. In this case, you are able to open just 19 sessions, for example using SQL*PLUS.

Well, I get the following results: – Oracle 10g Express: I get 19 connections;
It is therefore that you it is able to only open 19 connections. If you try to open one more session after 19th, you will receive this error below:
ERROR:
ORA-00020: maximum number of processes (%s) exceeded

Then in your case, I advice you to increase the value of the PROCESSES parameter.
e.g: alter system set processes=100 scope=spfile.

Additional Oracle configuration

Oracle XE is initially configured to grant 40 connections to its database. Spacewalk needs more than this default configuration. We recommend increasing this setting to 400.

In order to avoid these two problems we need to increase the max processes in Oracle and to

SQL> alter system set processes = 400 scope=spfile;

SQL> quit

/sbin/service oracle-xe restart

You might want to install ‘rlwrap’ to make your sqlplus more usable. rlwrap will retain a ?curses like behavior with sqlplus such as maintaining sql call history, etc.

yum install rlwrap

You can then do

rlwrap sqlplus spacewalk/[email protected]//localhost/XE

===============================================================================================================================

ARCHIVE LOG MANAGEMENT

su – grid
ASMCMD
ASMCMD>ls
ASMCMD>cd DG_DATA/
ASMCMD>cd du — to check full database size
ASMCMD>cd DG_FRA/
ASMCMD>ls
ASMCMD>cd ARCHIVELOG/
ASMCMD>ls

RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

RMAN> DELETE noprompt archivelog until time “to_date(‘2015_09_28:23:00:00′,’YYYY-MM-DD:hh24:mi:ss’)”;

===============================================================================================================

How to Fix ìXlib: No protocol specifiedî

While installing Oracle database I was getting this error. I searched various blogs and forums and
found out many solutions but lastly I got one solution which worked for me.

But well I will post all the possible ways which you can try (as per my knowledge) to overcome this issue.

Error:

Xlib: connection to “:0.0” refused by server
Xlib: No protocol specified

Specify the display protocol by logging into the user account you are getting the error

Solution 1:

$ export DISPLAY=localhost:0.0
$ xhost +

Solution 2:

Syntax:
$ export DISPLAY=(machine_name):0.0

For example:
$ export DISPLAY=10.10.20.42:0.0
$ xhost +

Solution 3 (which worked for me)
Login to the user account where you are getting the above error

$ xauth $DISPLAY
xauth: creating new authority file /home/oracle/.Xauthority
xauth: (argv):1: unknown command “:0.0”

The best way to check whether your Xlib display protocol is working or not is by using xclock command

$ xclock

After running this command you should see a small clock on your screen.

Solution 4
In case the above solution does not works
Login as root

xauth $DISPLAY

xauth: creating new authority file /home/root/.Xauthority

xauth list $DISPLAY

test.example.com/unix:0 MIT-MAGIC-COOKIE-1 f601e6a29ea688786765434c5c6325071

Now copy the above output and su to the user you are facing issue

su – oracle

$ xauth add test.example.com/unix:0 MIT-MAGIC-COOKIE-1 f601e6a29ea688786765434c5c6325071
xauth: creating new authority file /home/oracle/.Xauthority

Now test if your DISPLAY is working

$xclock

===============================================================================================================================

Check Users Connected to Database

To find how many users are on the database issue the following:

SQL> SELECT username FROM v$session;

Show what users are running

SQL> SELECT sid, serial#, username, b.sql_text FROM v$session a, v$sqlarea b WHERE a.sql_address=b.address;

Select username, machine from v$session where username is not null;

===============================================================================================================================

xhost + unable to Open Display / ASM disks not showing during Grid Infrastructure Installation

DISPLAY=:0.0 ; export DISPLAY

/usr/sbin/oracleasm deletedisk GI_D1 /dev/sdb1
/usr/sbin/oracleasm deletedisk GI_D2 /dev/sdb2
/usr/sbin/oracleasm deletedisk GI_D3 /dev/sdb3

“/dev/oracleasm/disks/*”

===============================================================================================================================

Space Full / lOG Clean Up Oracle RAC / XE

STEP:1 Delete Archivelog Using RMAN in Oracle:

If your storage is about full, you must either delete old backup and archivelog or move to tape.
If you want to delete archivelog from FRA(ASM Storage-Flash Revovery Area) or filesystem for win space,
you can use below commands. You can delete archivelog safely, because archivelog deleting does not harm to database.

Archivelog List Commands

RMAN>list archivelog all;
RMAN>list copy of archivelog until time ëSYSDATE-10';
RMAN>list copy of archivelog from time ëSYSDATE-10'

Archivelog Delete Commands

RMAN>delete archivelog all;
RMAN>delete archivelog until time ëSYSDATE-10';
RMAN>delete archivelog from time ëSYSDATE-10'

Note : Also, you can use noprompt statement for do not yes-no question.
RMAN>delete noprompt archivelog until time ëSYSDATE-10′;

STEP:2 Delete files from alert log files at OS Level (Do not delete alert Log File):

[email protected] : $ /u01/app/oracle/diag/rdbms/db_name/trace/alert_.log

Ex: $ cd /u01/app/oracle/diag/rdbms/proddb/proddb1

    $ rm -rf proddb1_*

STEP:3 Delete trace files from trace location at OS Level (Do not delete trace Folder):

Ex: $ cd /u01/app/oracle/diag/rdbms/db_name/trace
    $ rm -rf log_*

===============================================================================================================================

Service Creation in RAC and Listener Registration

alter system set LOCAL_LISTENER = ‘(ADDRESS = (PROTOCOL = TCP) (HOST = 10.10.9.204 ) (PORT = 1521))’ scope=both;

alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.9.204)(PORT=1521))))’;

alter system register;

alter system set db_recovery_file_dest_size = 20g scope= both; –FOR STANDALONE OR XE

alter system register;

alter system set service_names = ‘sqa2db1’;

Adding / Creating Service:

sqa2db ñ name of database
sqa2db1- instance on node1
sqa2db2- instance on node2
sqa2db1 name for the new taf service

#####SRVCTL COMMAND TO ADD SERVICE FOR TAF

srvctl add service -s NEWSRV -r VALORA1 -a VALORA2 -d VALORA ————–working one

srvctl add service -d dyaxuat1 -s dyaxuatp -r dyaxuat11 -a dyaxuat12 -P BASIC -m BASIC -z 180 -w 5 -j LONG -q TRUE -e SELECT

srvctl add service -d dyaxuat1 -s dyaxuatp -r dyaxuat11 -a dyaxuat12 -P PRECONNECT -m BASIC -z 180 -w 5 -j LONG -q TRUE -e SELECT

(OR)

srvctl add service -d VALORA -s valservice1 -r VALORA1 -a VALORA2 -P basic -y AUTOMATIC

srvctl stop service -d VALORA -s valservice2

srvctl relocate service -d VALORA -s valservice1 -i VALORA2 -t VALORA1

http://oracledbabay.blogspot.in/2012/11/srvctl-commands-10g-rac-11g-r2-rac.html

srvctl remove service -d VALORA -s valservice2
Where,

-d database unique name

-s name of the service to be created

-r preferred instances where the service would run

-a available instance

-P Basic or Preconnect method of connection, PRECONNECT establishes a backup connection on another node, whereas BASIC does a session failover

-z failover retries

-w failover delay

-j session type. either a long session or short session, LONG or SHORT

-q Send Oracle Advanced Queuing (AQ) HA notifications. For standalone servers, applicable in Oracle Data Guard environments only

-e Session Select or None. Use sess

Command to check serrvice status

srvctl start service -d sqa2db -s sqa2db1

srvctl status service -d sqa2db -s sqa2db1 -v

srvctl config service -d sqa2db

lsnrctl status
lsnrctl status LISTENER_SCAN1
lsnrctl status LISTENER_SCAN2
lsnrctl status LISTENER_SCAN3

SQL> select name,service_id from dba_services where name = sqa2db1;

SQL> select name,service_id from dba_services;

SQL> select name, failover_method, failover_type, failover_retries,goal,
clb_goal,aq_ha_notifications from dba_services where service_id = 3;

[email protected] $ srvctl enable service -d sqqa2db -s sqa2db1

    srvctl stop service -d sqa2db -s sqa2db1

    srvctl remove service -d sqa2db -s sqa2db1

In your client tnsnames.ora file add below entry after creating TAF service above :

SQA2DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.9.204)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SQA2DB1)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))))

sqlplus CDRProject/[email protected]

You can verify the client connections to 11gR2 database for TAF using ñ

SQL> SELECT MACHINE, FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,COUNT(*) FROM V$SESSION;

EXEC dbms_service.delete_service(”);
exec dbms_service.delete_service(‘demo’)

SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)FROM V$SESSION GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

=======================================================================

TESTING THE TAF USING SCAN

In your client tnsnames.ora file add below entry after creating TAF service above :

testSCAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testscan.domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testscan)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))))

sqlplus system/[email protected]

You can verify the client connections to 11gR2 database for TAF using ñ

SQL> SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
FROM V$SESSION
GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

MACHINE AILOVER_TYPE FAILOVER_M FAI COUNT(*)
óóó óóóó- óóó óñ ó
mymachine SELECT BASIC NO 1

SQL> select count(*) from table;

You can continously run this query and mean while go to the cluter node and down the service on node2 and the instance also on node 2

LOGON TO CLUSTER NODE1 as ìgridî user:

srvctl stop service -d test -n testracnode2

srvctl stop instance -d test -n testracnode2

crsctl stat res -t

And when you go back to your client connection. You can see that the query is still executing without the connection being lost.

SQL> SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
FROM V$SESSION
GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

Don;t forget to start the service again :))

srvctl start instance -d test -n testracnode2

srvctl start service -d test -n testracnode2

===============================================================================================================================

Service Creation in RAC and Listener Registration

SQL command to find size of Oracle database

The size of the database is the total size of the datafiles that make up the tablespaces of the database. These details are found in the dba_extents view. type the following lines at the SQL*PLUS prompt:

## datafiles size

SQL> select sum(bytes/1024/1024/1024) from dba_data_files;

## actual database size

SQL> select sum(bytes/1024/1024/1024) from dba_segments;

The size returned is in GB

How do I find the overall database size?

The biggest portion of a database’s size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:

select sum(bytes)/1024/1024 “Meg” from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 “Meg” from dba_temp_files;

To get the size of the on-line redo-logs:

select sum(bytes)/1024/1024 “Meg” from sys.v_$log;

Putting it all together into a single query:

select a.data_size+b.temp_size+c.redo_size “total_size”
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;

Another query (“Free space” reports data files free space):

col “Database Size” format a20
col “Free space” format a20
select round(sum(used.bytes) / 1024 / 1024 ) || ‘ MB’ “Database Size”
, round(free.p / 1024 / 1024) || ‘ MB’ “Free space”
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p
/

How do I find the used space within the database size?

Select from the DBA_SEGMENTS or DBA_EXTENTS views to find the used space of a database. Example:

SELECT SUM(bytes)/1024/1024 “Meg” FROM dba_segments;

Ref : http://www.orafaq.com/wiki/Oracle_database_FAQ

========= Script to find Oracle database size ==========

To find out how many MBs are allocated to ALL datafiles:
select sum(bytes)/1024/1024 “MB” from dba_data_files;

Find size of the online redo logs:
select sum(bytes)/1024/1024 “Meg” from sys.v_$log;

Find the size of all TEMP files:
select nvl(sum(bytes),0)/1024/1024 “MB” from dba_temp_files;

Find size of the control files usage,
SQL> select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) “MB” from v$controlfile;

===Find all in one go===

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size “total_size in MB”
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;

===============================================================================================================================

tnsnames.ora sample client side using a service

cdrproject =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.9.204)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdrproject)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))))

SQA2DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SQA2-ORA1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SQA2DB1)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))))

===============================================================================================================================

How to generate ER diagram using Oracle SQL developer

Tool Used : SQL Developer version 3.1.07

Schema Used : HR
Tables Used : Countries, Departments, Employees, Job_History, Jobs, Locations, Regions

Navigate the below path and step by step screen shots:

File > Data Modeler > Import > Data Dictionary

1# Select DB Connection and click on next

2# Select Schema/DB you wish to import and select Import to New Relational Model and click on next

3# select the objects you wish to import and click on next and finish

4# E-R relationship diagram is created between list of tables

5# You can save and print ER relationship by following below path

File > Data Modeler > Print Diagram or Print

===============================================================================================================================

OEM Configuration

$ env | grep ORA

E:>set oracle_sid=db01

E:>emctl getemhome
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
EMHOME=C:\oracle\product\10.1.0\Db_1/taj.domainname.local_db01

If you get above output mean DATABASE configure FOR DB CONTROL otherwise DATABASE is not configure for DB CONTROL and you will see message like “EM Configuration issue”.

We can configure DATABASE/INSTANCE for DB CONTROL through TWO WAY
1.DBCA
2.EMCA

DBCA
E:>set oracle_sid=sh

E:>emctl status dbconsole
OC4J Configuration issue. C:\oracle\product\10.1.0\Db_1/oc4j/j2ee/OC4J_DBConsole
taj.domainname.localsh not found.

E:>dbca
Note : dbca —> Configure Database option —> Select DB and give PWD —> Check Mark on Configure database with enterprise manager. —> Finish
E:>set oracle_sid=sh

Ref:https://community.oracle.com/thread/513549

===============================================================================================================================

Drop All Tables in ORACLE SCHEMA at Once

SELECT ‘DROP TABLE “‘ || table_name || ‘” CASCADE CONSTRAINTS;’ FROM user_tables;
select ‘drop table ‘||table_name||’ cascade constraints;’ from user_tables;

user_tables is a system table which contains all the tables of the user
the SELECT clause will generate a DROP statement for every table

This will generate something like:

DROP TABLE “FOO” CASCADE CONSTRAINTS;
DROP TABLE “BAR” CASCADE CONSTRAINTS;
DROP TABLE “DUMMY” CASCADE CONSTRAINTS;

Now you can easily run that script 🙂

and

purge recyclebin;
select * from tab;
select count(*) from tab;

===============================================================================================================================

xhost + unable to Open Display……

I set DISPLAY like this:
$ DISPLAY=:0.0 ; export DISPLAY
and then problem solved. I don’t know how it works, but it really do.

===============================================================================================================================
TNS-12560: Message 12560 not found; No message file for product=network, facility=TNS

problem

TNS-12560: Message 12560 not found; No message file for product=network, facility=TNS
problem:
Message 1053 not found; No message file for product=network, facility=TNSTNS-12541:
Message 12541 not found; No message file for product=network, facility=TNS
TNS-12560: Message 12560 not found; No message file for product=network, facility=TNS
TNS-00511: Message 511 not found; No message file for product=network, facility=TNS
Linux Error: 111: Connection refused

solution:
set below variable on OS level :
#export ORACLE_SID=
#export ORACLE_HOME=
#export PATH=/usr/sbin:$PATH
#export PATH=$ORACLE_HOME/bin:$PATH

      then run the command which giving the error after execution.

===============================================================================================================================

ORA-00845: MEMORY_TARGET not supported on this system

SQL> show parameter memory

NAME TYPE VALUE


hi_shared_memory_address integer 0
memory_max_target big integer 968M
memory_target big integer 968M
shared_memory_address integer 0
SQL>

SQL> alter system set memory_max_target=1200m scope=spfile;

System altered.

SQL> alter system set memory_target=1200m scope=spfile;

System altered.

SQL> startup force;
ORA-00845: MEMORY_TARGET not supported on this system

SQL> !df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
21G 17G 2.9G 86% /
tmpfs 1000M 420K 1000M 1% /dev/shm <<<<—————— increase this value
/dev/sda1 485M 41M 419M 9% /boot

from root
[[email protected] ~]# mount -t tmpfs shmfs -o size=2048m /dev/shm

/etc/fstab

/dev/mapper/VolGroup-lv_root / ext4 defaults 1 1
UUID=9a9b0c22-cacf-491c-ad27-b636c69da3cf /boot ext4 defaults 1 2
/dev/mapper/VolGroup-lv_swap swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
shmfs /dev/shm tmpfs size=2048m 0 0 <<<<<<<<<—- add following line

then init 6 <<<<<— reboot

SQL> startup ;
ORACLE instance started.

Total System Global Area 1255473152 bytes
Fixed Size 1344652 bytes
Variable Size 754977652 bytes
Database Buffers 486539264 bytes
Redo Buffers 12611584 bytes
Database mounted.

============================

SQL> show parameter memory

NAME TYPE VALUE


hi_shared_memory_address integer 0
memory_max_target big integer 1200M
memory_target big integer 1200M
shared_memory_address integer 0

======================================================================================================
[[email protected] ~]$ rman target sys/[email protected]

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Sep 2 11:14:15 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12545: Connect failed because target host or object does not exist

Solution:
[[email protected] ~]$ rman

Recovery Manager: Release 10.2.0.1.0 ñ Production on Cum Nis 27 10:56:17 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN> connect target sys

target database Password:
connected to target database: XE (DBID=2496353564)

RMAN> exit

Recovery Manager complete.

======================================================

TO KILL A USER SESSION

ALTER SYSTEM KILL SESSION ‘sid,serial#’;
ALTER SYSTEM KILL SESSION ‘201,3’ IMMEDIATE;

select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = ‘SNP041220150307495’ and p.addr = s.paddr;

=================================================================================

SYSTEM TABLESPACE FULL ISSUE RESOLVING

Andrew’s Oracle Blog

Saturday, May 17, 2014
TRUNCATE TABLE SYS.AUD$ Gives ORA-00942
I saw that the SYSTEM tablespace in an Oracle 11.2 database was getting quite big so I checked the size of SYS.AUD$:

C:\Users\AJ0294094>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 16 13:20:54 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from sys.aud$;

COUNT(*)

35483888

SQL>

Ö but when I tried to TRUNCATE it, Oracle returned an ORA-00942:

SQL> truncate table sys.aud$
2 /
truncate table sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>

I took a closer look and saw that the AUD$ table had been moved to the SYSTEM schema and SYS.AUD$ was just a synonym pointing to it. This was a while later, by which time, several more rows had been added to it:

SQL> select owner, object_type
2 from dba_objects where object_name = ‘AUD$’;

OWNER OBJECT_TYPE


SYS SYNONYM
SYSTEM TABLE

SQL> select table_owner, table_name
2 from dba_synonyms
3 where owner = ‘SYS’
4 and synonym_name = ‘AUD$’
5 /

TABLE_OWNER TABLE_NAME


SYSTEM AUD$

SQL> select count(*) from system.aud$
2 /

COUNT(*)

35484319

SQL>

Ö so I did the TRUNCATE on SYSTEM.AUD$ instead:

SQL> truncate table system.aud$
2 /

Table truncated.

SQL>

Ö then SYS.AUD$ appeared empty afterwards:

SQL> select count(*) from sys.aud$
2 /

COUNT(*)

     0

SQL>

Ö and the SYSTEM tablespace had plenty of free space:

SQL> select sum(bytes) from dba_free_space
2 where tablespace_name = ‘SYSTEM’
3 /

SUM(BYTES)

8996651008

SQL>

This happened because you cannot TRUNCATE a table through a synonym. This applies, incidentally, even if you own both the table and the synonym, as you can see in the example below:

SQL> create table tab1 (col1 number);

Table created.

SQL> truncate table tab1;

Table truncated.

SQL> create synonym tab2 for tab1;

Synonym created.

SQL> desc tab2
Name Null? Type


COL1 NUMBER

SQL> truncate table tab2;
truncate table tab2
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>

Ref:http://international-dba.blogspot.in/2014/05/truncate-table-sysaud-gives-ora-00942.html

===========================================================

To find tablespace size in oracle

Select tablespace_name, ‘Mb’||’ ‘||round(sum(bytes/1024/1024)) “Free_Size” from dba_free_space group by tablespace_name;

===============================================================

Disable audit_trail

truncate table SYS.AUD$;
select count(*) from SYS.AUD$;
purge recyclebin;

alter system set audit_trail=’NONE’ scope=spfile sid=’*’;
shut immediate;
startup;

========================================================================

Adding datafile to a tablespace

alter tablespace SYSTEM add datafile ‘+DATA/sqa2db/datafile/system02.dbf’ size 300m;

========================================================================

FAILOVER TNS ENTRY IN RAC ENVIRONMENT

orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(FAILOVER = ON)(FAILOVER_MODE = (TYPE = SELECT)
(METHOD = BASIC)(RETRIES = 120)(DELAY = 3)
)
)
)

========================================================================

Startup Restrict Mode

Sometimes it is necessary to do work on a database without any other users being logged in. It is possible to restrict the database session in such a case. When the database starts in restricted mode only users with restricted session privileges can get access to the database even though it is technically in open mode.

Enable / Disable Restricted Session

SQL> startup restrict
ORACLE instance started.
Total System Global Area 504366872 bytes
Fixed Size 743192 bytes
Variable Size 285212672 bytes
Database Buffers 218103808 bytes
Redo Buffers 307200 bytes
Database mounted.
Database opened.

Startup the database in restricted mode

The alter system command can be used to put the database in and out of restricted session once it is open:

SQL> alter system enable restricted session;
system altered

SQL> alter system disable restricted session;
system altered

NOTE: Find and disconnect users connected during restricted session. Any users connected to the database when restricted session is enabled will remain connected and need to be manually disconnected

To check which users are connected to the database run the following:

SQL> SELECT username, logon_time, process from v$session;
USERNAME LOGON_TIM PROCESS


SYS 17-NOV-10 1310796
17-NOV-10 1343899

By querying the process id you can then issue a kill -9 at the operating system level to disconnect the connected user. The blank usernames in v$session refer to background database processes.

Check if database in restricted mode, If you are unsure whether the database is in restricted session or not you can run the following query to check:

SQL> SELECT logins from v$instance;

LOGINS

RESTRICTED

========================================================================

Undo Tablespace Management / Resize

create undo tablespace undotbs_2 datafile ‘+DATA/sqa2db/datafile/undotbs_1’ size 100m autoextend on;

grant connect,resource to CDR_PROJECT_NEW identified by CDR_PROJECT_NEW;

/u01/app/oracle/dyaxprod/undotbs01.dbf

drop tablespace UNDOTBS2 including contents and datafiles;

Create new undo tablespace and remove the old one

SQL> show parameter undo

NAME TYPE VALUE


undo_management string AUTO
undo_retention integer 2700
undo_tablespace string UNDOTBS

SQL> create undo tablespace undotbs_1
2 datafile ‘file_path’ size 10m;

Tablespace created.

SQL> alter system set undo_tablespace=UNDOTBS_2 scope=both;

System altered.

SQL> show parameter undo

NAME TYPE VALUE


undo_management string AUTO
undo_retention integer 2700
undo_tablespace string UNDOTBS_1
SQL> drop tablespace undotbs including contents and datafiles;

Tablespace dropped.

SQL>


How to Shrink the datafile of Undo Tablespace
Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.2.0.2
Oracle Server – Standard Edition – Version: 9.2.0.7 to 11.2.0.2
Information in this document applies to any platform.
Goal:
Your production database has bi-annual or annual purging programs which generate huge redo. Due to this requirement, your undo tablespace grows rapidly and occupies most of the space on file system.
The purging process is run only few times a year. So would not like to keep the huge undo datafile in your database throughout the year. You don’t want to buy additional disks unnecessarily.

You have created an undo tablespace with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid Error: ORA 1651 : unable to extend save undo segment by in tablespace .

You have tried “alter database datafile .. resize” which always fails with Error: ORA 3297 : file contains blocks of data beyond requested RESIZE value.

You want to shrink the datafile to utilize the disk space for other tablespaces or other purposes.
Solution:
— Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile ‘undorbs1.dbf’ size 100m;

— Set new tablespace as undo_tablespace
SQL> alter system set undo_tablespace=undo_rbs1;

— Drop the old tablespace.
SQL> drop tablespace undo_rbs0 including contents.

NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace ‘%s’ is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.

Or Your can use “Undo Tablespace Moved To Pending Switch-Out State” to resolve this error.

Also be aware that on some platforms, disk space is not freed to the OS until the database is restarted.  The disk space will remain "allocated" from the OS perspective until the database restart.

Reference Note: How to Shrink the datafile of Undo Tablespace [ID 268870.1]

=======================================================================================================

Oracle 11g User Account Status LOCKED(TIMED) with FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME Parameters

At least few times you might have noticed the account status is with LOCKED(TIMED). In this topic we will discuss about how it is happens.

See one example here.

SQL> SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES
2 WHERE PROFILE=’DEFAULT’ AND RESOURCE_NAME IN (‘FAILED_LOGIN_ATTEMPTS’,’PASSWORD_LOCK_TIME’);

RESOURCE_NAME LIMIT


FAILED_LOGIN_ATTEMPTS 1
PASSWORD_LOCK_TIME 1

SQL> alter user test profile DEFAULT;

User altered.

SQL> select username, account_status from dba_users where username =’TEST’;

USERNAME ACCOUNT_STATUS


TEST OPEN

SQL> CONNECT TEST/A
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> CONNECT TEST/A
ERROR:
ORA-28000: the account is locked

After 2nd failure attempt the account got locked.

SQL> CONNECT / AS SYSDBA
Connected.

SQL> select username, account_status from dba_users where username =’TEST’;

USERNAME ACCOUNT_STATUS


TEST LOCKED(TIMED)

Explanation

Oracle 10g onwards for DEFAULT profile all the resource parameter values are UNLIMITED except FAILED_LOGIN_ATTEMPTS. One more parameter needs to be checked for this timed account lock which is PASSWORD_LOCK_TIME. PASSWORD_LOCK_TIME parameter determines how many days/time the account should be locked mode after n number of failure attempts. These two parameters makes the account status to LOCKED(TIMED) when you try with wrong passwords.

To avoid this particular kind of unexpected locking you have to create a new profile with FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME values as UNLIMITED and assign this new profile to the user. It is not recommended to change the resource values to the DEFAULT profile.

Create A new profile

SQL> CREATE PROFILE NOEXPIRY LIMIT
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED;

Profile created.

Assign the profile to the user

SQL> ALTER USER TEST PROFILE NOEXPIRY;

User altered.

Unlock the account.

SQL> ALTER USER TEST ACCOUNT UNLOCK;

User altered.

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME=’SCOTT’;

USERNAME ACCOUNT_STATUS


TEST OPEN

=======================================================================================================

How to re-open expired oracle database account without change password

Today iíll show you how to reopen Oracle database user account without changing password, which have status expired. letís create demonstration:

create user

CREATE USER test IDENTIFIED BY test;
grant create session to test;

check status

SELECT username, account_status, expiry_date
FROM dba_users

WHERE username = ‘TEST’;

USERNAME ACCOUNT_STATUS EXPIRY_DATE
TEST OPEN 20-Sep-14 11:29:00

expire it

alter user test password expire;

re-check status

SELECT username, account_status, expiry_date
FROM dba_users

WHERE username = ‘TEST’;

USERNAME ACCOUNT_STATUS EXPIRY_DATE
TEST EXPIRED 24-Mar-14 11:31:50

get the existing password, here is two method:
1) in 11g

select password from user$ where name=’TEST’;

PASSWORD

7A0F2B316C212D67

in 10g

select password from dba_users where username=’TEST’;

PASSWORD

7A0F2B316C212D67

open account with

alter user test identified by values ‘7A0F2B316C212D67’;

2)

SELECT DBMS_METADATA.get_ddl (‘USER’, ‘TEST’)

FROM DUAL;

CREATE USER “TEST” IDENTIFIED BY VALUES ‘S:79B1417837DCF0FBFACEFB10D7DBDC7B7EA63CC986036567BDCBA144B940;7A0F2B316C212D67’
DEFAULT TABLESPACE “USERS”
TEMPORARY TABLESPACE “TEMP”
PASSWORD EXPIRE

edit above script and execute it

ALTER USER “TEST” IDENTIFIED BY VALUES ‘S:79B1417837DCF0FBFACEFB10D7DBDC7B7EA63CC986036567BDCBA144B940;7A0F2B316C212D67’

chek status again

SELECT username, account_status, expiry_date
FROM dba_users

WHERE username = ‘TEST’;

USERNAME ACCOUNT_STATUS EXPIRY_DATE
TEST OPEN 20-Sep-14 11:36:47

after that user test still have its old pass test.

thatís all, good luck.

To Make lifetime

select profile,USERNAME,EXPIRY_DATE from dba_users;

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Ref:

2nd Method:

To unexpire and account simply assign a new password to the user.

eg: alter user USER_ACCOUNT identified by NEW_PASSWORD

ALTER USER EDPULSECHECK_SANDBOXPROJECT1 identified by EDPULSECHECK_SANDBOXPROJECT1;

3rd Method:

For development you can disable password policy if no other profile was set (i.e. disable password expiration in default one):

ALTER PROFILE “DEFAULT” LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Then, reset password and unlock user account. It should never expire again:

alter user user_name identified by new_password account unlock;

==================================

UnInstalling the Oracle 11g XE:

Step:1

/etc/init.d/oracle-xe status

/etc/init.d/oracle-xe stop

Step2:
[[email protected] ~]$ rpm -qa | grep oracle
oracle-xe-11.2.0-1.0.x86_64

Step3:

[[email protected] ~]$ rpm -e oracle-xe-11.2.0-1.0.x86_64

=====================================================

Convert Archivelog Mode to NoArchivelog Mode in RAC

Step 1: Set the Environment Variable
$ hostname
node1
$ export ORACLE_SID=gcprod1
$ sqlplus system @ gcprod1
password:

Step 2: Disable Cluster
SQL> alter system set CLUSTER_DATABASE=FALSE scope=spfile;
system altered

Step 3: Set the parameters (Optional)
SQL> alter system set LOG_ARCHIVE_START= FALSE scope=spfile;
system altered

Step 4: Shutdown the database
SQL> exit
$ srvctl stop database -d gcprod

Step 5: Mount the database

$ echo $ORACLE_SID
gcprod1

$ sqlplus “sys as sysdba”
SQL> startup mount
ORACLE instance started
Total System Global Area: 4.4678E+10 bytes
Fixed Size 2166864 bytes
Variable Size 4345302960 bytes
Database mounted.

Step 6: Disable Archive log
SQL> alter database noarchivelog;

Step 7: Enable the cluster
SQL> alter system set CLUSTER_DATABASE=TRUE scope=spfile;
system altered

Step 8: Shutdown the instance
SQL> shutdown immediate;

Step 9: Start all the instances
$ srvctl start database -d gcprod

Step 10: verify the status.
$ crs_stat -t
all instances must be online

$ sqlplus “sys as sysdba”
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 308
Next log sequence to archive 309
Current log sequence 309

Your database is in No Archive Log Mode.

========================================

Putting a Table in NOLOGGING MODE:

LOGGING

A table marked as LOGGING will generate redo in the Oracle Database – Redo Log (Log Files) while a table
marked as Oracle Database – NOLOGGING will bypass it.

SQL> ALTER TABLE big_table logging;

SQL> select TABLE_NAME,LOGGING from user_tables;

==============================================================================

How to check the maximum number of allowed connections to an Oracle database?

SELECT name, value FROM v$parameter WHERE name = ‘sessions’;
SELECT COUNT(*) FROM v$session;

ORA-00018 comes under “Oracle Database Server Messages”. These messages are generated
by the Oracle database server when running any Oracle program.

How to increase SESSION initialization parameter:

  1. Login as sysdba
    sqlplus / as sysdba
  2. Check Current Setting of Parameters
    sql> show parameter sessions
    sql> show parameter processes
    sql> show parameter transactions
  3. If you are planning to increase “sessions” parameter you should also plan to increase
    “processes and “transactions” parameters.
    A basic formula for determining these parameter values is as follows:
    processes=x
    sessions=x1.1+5 transactions=sessions1.1
  4. These paramters can’t be modified in memory. You have to modify the spfile only
    (scope=spfile) and bounce the instance.
    sql> alter system set processes=500 scope=spfile sid=’‘; sql> alter system set sessions=555 scope=spfile sid=’‘;
    sql> alter system set transactions=610 scope=spfile sid=’*’;
    sql> shutdown abort
    sql> startup

========================================================

Kill Data pump job by datapump export prompt

//After initiating export backup, Kindly make sure datapump job by issuing the following query as sysdba:

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME
óóóóóóóóóó óóóóóóóóóó
OPERATION JOB_MODE
óóóóóóóóóó óóóóóóóóóó
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
óóóóóóóóóó óóó- óóóóóñ óóóóóñ
SYSTEM SYS_EXPORT_FULL_01
EXPORT FULL
EXECUTING 1 1 3

//Now connect to datapump export prompt with JOB_NAME(attach) as below & issue the datapump command: KILL_JOB.

[[email protected] ~]$ expdp system/manager attach=SYS_EXPORT_FULL_01

Job: SYS_EXPORT_FULL_01

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes
[[email protected] ~]$

==================================================

Add Data File to system tablespace RAC

alter tablespace system add datafile ‘+DB_DATA/demodb/system02.dbf’ size 100m autoextend on maxsize unlimited;

==================================================

How to create undo tablespace in oracle RAC

1) Create Undo tablespace UNDOTBS01

SQL> create undo tablespace UNDOTBS01 DATAFILE ‘+DATAGROUP’ SIZE 4g;
Tablespace created.
SQL> @asm
NAME TOTAL_GB FREE_GB


DATAGROUP 249.995117 11.46875
IDXGROUP 149.99707 7.55761719

2) Create Undo tablespace UNDOTBS02

SQL> create undo tablespace UNDOTBS02 DATAFILE ‘+DATAGROUP’ SIZE 2G;
Tablespace created.

3) Assign the Newly created UNDOTBS01 tablespace to node1

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS01 SCOPE=BOTH SID=’test1′;
System altered.

3) Assign the Newly created UNDOTBS02 tablespace to node2

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02 SCOPE=BOTH SID=’test2′;
System altered.

SQL>

SQL> show parameter undo

NAME TYPE VALUE
óóóóóóóóóóóó ñ
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDOTBS02
SQL>

To drop tablespace UNDOTBS1(old Undo) see its segment status

SQL> SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE a.usn = b.usn
AND a.usn = c.xidusn
AND c.ses_addr = d.saddr
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = ‘UNDOTBS1’
);
NAME STATUS USERNA SID SERIAL#


_SYSSMU82$ PENDING OFFLINE SCOTT 10 16007
_SYSSMU241$ PENDING OFFLINE SCOTT 17 30587
_SYSSMU34$ PENDING OFFLINE SCOTT 31 22303
_SYSSMU9$ PENDING OFFLINE SCOTT 33 10379
_SYSSMU22$ PENDING OFFLINE SCOTT 42 1131
_SYSSMU105$ PENDING OFFLINE SCOTT 45 58502
_SYSSMU234$ PENDING OFFLINE SCOTT 46 44824

wait for some time and execute again

QL> SELECT a.name,b.status , d.username , d.sid , d.serial#
2 FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
3 WHERE a.usn = b.usn
4 AND a.usn = c.xidusn
5 AND c.ses_addr = d.saddr
6 AND a.name IN (
7 SELECT segment_name
8 FROM dba_segments
9 WHERE tablespace_name = ‘UNDOTBS1’
10 );
SQL> 0 rows

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace ëUNDOTBS1í is currently in use

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’UNDOTBS1′ and status=’ONLINE’;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS


PUBLIC _SYSSMU103$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU198$ UNDOTBS1 ONLINE

After sometime executed the same query

SQL> /

no rows selected

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’UNDOTBS1′ and status=’ONLINE’
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
And same for UNDOTBS2.

==================================================

Resize undo tablespace 10gR2/11gR2 RAC

On RAC configuration each instance has assigned its own UNDO tablespace. So in order to resize the UNDO you must create a new one for each instance and assign it to the instance then you cane drop the old ones.

For each instance :

— Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile ‘undorbs1.dbf’ size 100m;

— Set new tablespace as undo_tablespace
SQL> alter system set undo_tablespace=undo_rbs1;

— Drop the old tablespace.
SQL> drop tablespace undo_rbs0 including contents.

NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace ‘%s’ is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.

===================================================================================

ORA-00845: MEMORY_TARGET not supported on this system

This is a very common error which you might face while starting up your oracle database.

This happens when your system is out of temporary storage. While starting up the database oracle uses /dev/shm to store temporary files but when it runs out of memory you get this error.

Error:sql> startup
ORA-00845: MEMORY_TARGET not supported on this system
Reason:
/dev/shm is also know as tmpfs i.e. temporary file system which keeps all the file system in virtual memory to speed up several processes.

Solution:
Increase the size of /dev/shm

To check the size of /dev/shm

df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda3 7.6G 4.4G 2.9G 61% /
tmpfs 504M 76K 504M 1% /dev/shm
/dev/sda1 194M 25M 160M 14% /boot

To increase the size

mount -o remount,size=3G /dev/shm

Verify the size

df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda3 7.6G 4.4G 2.9G 61% /
tmpfs 3G 1007M 2.1G 33% /dev/shm
/dev/sda1 194M 25M 160M 14% /boot
To make permanent changes to your file system update your fstab

vi /etc/fstab

tmpfs /dev/shm tmpfs defaults,size=80G 0 0
Update the new fstab file

mount -a

—-Reboot the servers in RAC ALSO AFTER BRINGING DOWN ALL SERVICES—

==============================================================================================

How to change SGA in Oracle Rac

This Article for newbe Oracle DBA Since If you trying to change sga on rac with wrong way , spfile maybe be corrupted .

Node One : ORCL1
Node Two : ORCL2

Note : You Need To Check Memory Parameter On Database , if its Have Available Size .

Connect To Node One :


alter system set sga_max_size=16g scope=spfile sid = 'ORCL1';
alter system set sga_target=12g scope=spfile sid = 'ORCL1';
alter system set sga_max_size=16g scope=spfile sid = 'ORCL2';
alter system set sga_target=12g scope=spfile sid = 'ORCL2';


Or In Another Way :
sql>alter system set sga_target=12G scope=spfile sid='*';
sql>alter system set sga_max_size=16G scope=spfile sid='*';
sql>alter system set sga_max_size=16G scope=spfile ;
sql>alter system set sga_target=12G scope=spfile;

 PGA :

sql>alter system set pga_aggregate_target=4G scope =spfile sid='*';
sql>alter system set pga_aggregate_target=4G scope=both;



shutdown database PROD (two instances should be shut down)

 $>srvctl stop database -d PROD

$>srvctl START database -d PROD

Check The New Size For Both Instance By :

Show parameter sga ; 

Ref:http://orababy.blogspot.in/2013/08/ora-00845-memorytarget-not-supported-on.html

http://www.databaseskill.com/3551442/

http://osamamustafa.blogspot.in/2012/05/how-to-change-sga-in-oracle-rac.html

===============================================================================================

Oracle 11gR2 RAC ORA-00845 MEMORY_TARGET not supported on this system solution

Just on the line set of Oracle 11gR2 RAC. The the MEMORY_TARGET memory set to 12G, start at the error:

   ORA-00845: MEMORY_TARGET not supported on this system

00845, 00000, “MEMORY_TARGET not supported on this system”

/ / * Cause: The MEMORY_TARGET parameter was not supported on this operating system or / dev / shm was not sized correctly on Linux.

/ / * Action: Refer to documentation for a list of supported operating systems. Or, size / dev / shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.

In Oracle 11g, MEMORY_TARGET parameter to automatically manage SGA and PGA. But in Oracle 11gR2 if the the MEMORY_TARGET parameter value set is too large, it may occur this error.

Workaround: Increase the / dev / shm / dev / shm is usually used as shared memory.

/ Dev / shm is a temporary file system (tmpfs). From the / etc / fstab mount supports standard parameter “size” size increase or reduce the the tmpfs size of / dev / shm. If you do not set the size, its size is larger than the size the MEMORY_TARGET.

If you want to take effect immediately, as follows:

Umount tmpfs

Mount-t tmpfs shmfs-o size = 14G / dev / shm

So that after each restart the OS automatically mount, modify the file / etc / fstab tmpfs modified to the following values:

tmpfs / dev / shm tmpfs defaults, size = 14G 0 0

If you directly modify the / etc / fstab, by rebooting the OS allows parameters to take effect.

Note: The size of this size needs to be set to an integer.

df-h / dev / shm look modified the size of the

File System Capacity Used Available Use% mount point

/ Dev/mapper/vg00-lv_root

                   20G    11G   7.7G   59% /

/ Dev/mapper/vg00-lv_home

                   97G   6.8G    86G    8% / home

/ Dev/sda1 97M 13M 80M 14% / boot

tmpfs 14G 0 14G 0% / dev / shm

Ref:http://www.databaseskill.com/3551442/

========================================================================

How to find out the Size of the Oracle Database (Any Versions 10g, 11g)

Col DBFSizeGB for 99999.99

Col TMPFSizeGB for 99999.99

Col LOGFSizeMB for 99999.99

Col CTRLSizeGB for 99999.99

Col TotalSIzeGB for 99999.99

select DBFSizeGB, TMPFSizeGB, LOGFSizeMb, CTRLSizeMB, DBFSizeGB+TMPFSizeGB+LOGFSizeMb/1024+CTRLSizeMB/1024 TotalSIzeGB from (select sum(bytes)/1024/1024/1024 DBFSizeGB from dba_data_files ) ,( select nvl(sum(bytes),0)/1024/1024/1024 TMPFSizeGB from dba_temp_files ),( select sum(bytes)/1024/1024 LOGFSizeMB from sys.v_$log ) ,( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 CTRLSizeMB from v$controlfile);

——To find out Total Objects used Space

col ObjUsedSpace for 99999.99

select sum(bytes)/1024/1024/1024 ObjUsedSpace from dba_segments;

=================================================================================================

How to Move a Datafile to Different Location

If the database is in archivelog mode, you can move the datafile while database is up. Take the datafile offline, copy (or move) the datafile to the new location:

ALTER DATABASE DATAFILE ‘/old_path/name_of_datafile’ OFFLINE !mv /old_path/name_of_file /new_path/name_of_file

ALTER DATABASE DATAFILE ‘/old_path/name_of_datafile’ OFFLINE
!mv /old_path/name_of_file /new_path/name_of_file

Then issue the following commands to rename the datafile and make it online:
Oracle PL/SQL
ALTER DATABASE RENAME FILE ‘/old_path/name_of_datafile’ TO ‘/new_path/name_of_file’; RECOVER DATAFILE ‘/new_path/name_of_file’; ALTER DATABASE DATAFILE ‘/new_path/name_of_file’ ONLINE;

ALTER DATABASE RENAME FILE ‘/old_path/name_of_datafile’ TO ‘/new_path/name_of_file’;
RECOVER DATAFILE ‘/new_path/name_of_file’;
ALTER DATABASE DATAFILE ‘/new_path/name_of_file’ ONLINE;

If the database is in no archivelog mode, you need to shutdown the database, open the database in mount mode and copy/move the datafile:
Oracle PL/SQL
SHUTDOWN IMMEDIATE; STARTUP MOUNT; !mv /old_path/name_of_file /new_path/name_of_file

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
!mv /old_path/name_of_file /new_path/name_of_file

Then rename the file and open the database:

ALTER DATABASE RENAME FILE ‘/old_path/name_of_datafile’ TO ‘/new_path/name_of_file’; ALTER DATABASE OPEN;

ALTER DATABASE RENAME FILE ‘/old_path/name_of_datafile’
TO ‘/new_path/name_of_file’;
ALTER DATABASE OPEN;

Ref: http://www.gokhanatil.com/2008/12/how-to-move-a-datafile-to-different-location.html

=================================================================================================

ORA-01207: File is more recent than control file – old control file

Few days ago I faced this issue in my database, due to some problem in my ASM disks, my database closed abruptly and I was getting the following error when I was trying to open my database using STARTUP command.

Errors:-

ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: ‘+AMIT_R1/orcl/datafile/amit.271.795743985’
ORA-01207: file is more recent than control file – old control file.

Cause :-

The information in this file is inconsistent with information from the control file.
The datafile header CHECKPOINT count is beyond the controlfile CHECKPOINT count.

Solution :-

  1. Mount the database.
    SQL> startup mount;
    ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size 1341000 bytes
Variable Size 322963896 bytes
Database Buffers 696254464 bytes
Redo Buffers 4739072 bytes
Database mounted.

  1. Recreate the control file of the database.
    Please refer to
    http://amit7oracledba.blogspot.in/2012/10/how-to-recreate-control-file-in-oracle.html
    only to recreate your control file.
  2. After creation of control file, database is in mount stage

SQL> select open_mode from v$database;

OPEN_MODE

MOUNTED

  1. Recover the database.
    SQL> recover database;

If it does recovery from the redo logs then from above command only your database will be recovered , but if the error is “control file is more recent than the file” then it asks you for archive logs to recover then you have to use below command

SQL> recover database using backup controlfile until cancel;

it will ask you for archive logs, give the path of all archive logs which it needs until it gives you message, MEDIA RECOVERY COMPLETED.

  1. Open the database.
    SQL> alter database open;

Database altered.
SQL> select open_mode from v$database;

OPEN_MODE

READ WRITE

Database recovery complete.

Ref: http://amit7oracledba.blogspot.in/2012/10/how-to-recreate-control-file-in-oracle.html

=================================================================================================

LEARN HOW TO OPEN RESETLOGS WITHOUT RECOVERY

Amazing Oracle Trick

What is Open resetlog mode :

Online redo logs are re-created . The log sequence is reset to 1.
If the databaseis running in archive log mode, the archived redo logs should then be deleted. Otherwise, chances are, that Oracle will eventually try to create an archived redo log whose filename already exists.

First i will show its Normal Database this mean not in recovery mode :

sqlplus / as sysdba

Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit  

Now Lets Open it :

sqlplus / as sysdba

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2087672 bytes
Variable Size 213910792 bytes
Database Buffers 616562688 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>

Ref:http://osamamustafa.blogspot.in/2012/05/open-resetlogs-without-really-doing.html

===========================================================================

To Drop A TEMP datafile

alter database tempfile ‘/mnt/TESTDB/oracledata/TESTDB/temp02.dbf’ offline ;

alter database tempfile ‘/mnt/TESTDB/oracledata/TESTDB/temp02.dbf’ drop including datafiles;

==============================================================================

# How to check the maximum number of allowed connections to an Oracle database?

There are a few different limits that might come in to play in determining the number of connections an Oracle database supports. The simplest approach would be to use the SESSIONS parameter and V$SESSION, i.e.

The number of sessions the database was configured to allow

SELECT name, value FROM v$parameter WHERE name = 'sessions';

The number of sessions currently active

SELECT COUNT(*)FROM v$session;

As I said, though, there are other potential limits both at the database level and at the operating system level and depending on whether shared server has been configured. If shared server is ignored, you may well hit the limit of the PROCESSES parameter before you hit the limit of the SESSIONS parameter. And you may hit operating system limits because each session requires a certain amount of RAM.

————————Mximum Connection in Mysql———————————

show status like ‘Conn%’;

show status like ‘%onn%’;

show processlist;

Check The Number Of MySQL Open Database Connections on Linux Or Unix-like Server
by Vivek Gite on January 8, 2015
in Database, FreeBSD, Linux, MySQL, UNIX

===============================================================================

How can I check the number of active MySQL connections on Linux based system?

MySQL show status command to see open database connections example

First, connect to the your mysql server:

mysql -u root -p

Type the following sql query to see the number of connection attempts to the MySQL server includes both failed and successful connection attempts:

mysql> show status like ‘Conn%’;

Sample outputs:
Fig.01: “show status like ‘Conn%’;” in action

Fig.01: “show status like ‘Conn%’;” in action

You can use the following sql command to see the number of currently open connections at mysql> prompt:

mysql> show status like ‘%onn%’;
+————————–+———+
| Variable_name | Value |
+————————–+———+
| Aborted_connects | 7 |
| Connections | 6304067 |
| Max_used_connections | 85 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 7 | <—- No of currently open connections
+————————–+———+
7 rows in set (0.00 sec)

Use show processlist sql command to see the number of open connections

Type the following sql command at mysql> prompt to see the number of currently open connections:

mysql> show processlist;
+———+————+——————-+————+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+———+————+——————-+————+———+——+——-+——————+
| 6297128 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 6308321 | faqwpblogu | 10.10.29.66:42945 | lesaibkfaq | Sleep | 1 | | NULL |
| 6308323 | faqwpblogu | 10.10.29.74:46993 | lesaibkfaq | Sleep | 0 | | NULL |
| 6308325 | faqwpblogu | 10.10.29.74:46995 | lesaibkfaq | Sleep | 1 | | NULL |
| 6308326 | faqwpblogu | 10.10.29.74:46996 | lesaibkfaq | Sleep | 0 | | NULL |
+———+————+——————-+————+———+——+——-+——————+
5 rows in set (0.00 sec)

The above output indicates four currently open connection for user called ‘faqwpblogu’ from app server located at 10.10.29.66 and 10.10.29.74.

==================================================================================================

How to delete duplicte record in a table using rowid:

delete from
where rowid not in
(select max(rowid) from personal_inf group by );

==================================================================================================

ORA-01031 error in 11g r2 when trying to connect to RMAN:

RMAN> connect target rmanuser/[email protected]

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges

How do I fix this RMAN ORA-01031 error?

Answer: Yu need a user with the SYSDBA privilege to avoid the ORA-01031 error.

Your user does not have the proper RMAN privileges (SYSDBA privilege):

To find a list of user who have SYSDBA privilege, issue this SQL:
select *
from
v$pwfile_users;
Starting in Oracle 11g, all RMAN sessions must be configured for remote login or you will get the ORA-01031: insufficient privileges error. To connect to the target database using RMAN as SYSDBA, you must do one of the following procedures:

Connections with sysdba or sysoper privileges must always be authenticated. This is possible through OS authentication by assigning the appropriate OS group to the OS user. The RMAN user account should be part of the operating system DBA group on the target server.

Another method is the use of a password file. If an 11g client is not configured you will get the ORA-01031: Insufficient privileges error when connect to Oracle in SQL*Plus as the SYS or SYSTEM user. You do this by creating a password file using orapwd utility and enable the remote_login_passwordfile initialization parameter. Oracle remote login is commonly configured with a password file using the orapwd utility. If you already have a password file and you are getting the ORA-01021 error, try deleting and re-creating your password file using orapwd.

Here are the steps to prevent the ORA-01031 error when connecting as sysdba in 11g:

1 – Create the password file. This is done by executing the following command:

$ orapwd file=filename password=password entries=max_users

The max_users is the number of database users that can be granted SYSDBA or SYSOPER. This parameter should be set to a higher value than the number of anticipated users to prevent having to delete and recreate the password file.

2 – Edit the init.ora parameter remote_login_passwordfile.

This parameter must be set to either SHARED or EXCLUSIVE. When set to SHARED, the password file can be used by multiple databases, yet only the SYS user is recognized. When set to EXCLUSIVE, the file can be used by only one database, yet multiple users can exist in the file.

SQL> show parameter password

NAME TYPE VALUE
—————————– ———– ———-remote_login_passwordfile string EXCLUSIVE
3 – Grant SYSDBA or SYSOPER to users. When SYSDBA or SYSOPER privileges are granted to a user, that user’s name and privilege information are added to the password file.

SQL> grant sysdba to scott;

Grant succeeded.

================================================================================================

How to increase sessions, processes, transactions in Oracle 11g database

What are Sessions in Oracle database : Sessions specify the number of connections that can served by oracle database at a time. This is equivalent to the number of concurrent users of the application. If your application has a lot of concurrent users then you need to increase the sessions in the oracle database.
What are processes in Oracle database : Processes run in the background of Oracle database to maximize performance and accommodate many users.
What are transactions in Oracle database : A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database
What is the relation between Sessions, transactions & processes : By default, the following formula is followed in oracle database.
Processes (x) , Sessions(s), Transactions(t)
s = x * 1.1 + 5
t = s * 1.1

For example, if processes are 100, then sessions will be 115 and transactions will be 127.

To alter the sessions,transactions, processes follow this procedure ñ

  1. At command line, type sqlplus
  2. For username give / as sysdba
  3. Give the following commands ñ
    alter system set sessions=400 scope=spfile;
    alter system set processes=350 scope=spfile;
    alter system set transactions=440 scope=spfile;
    Give the values according to the requirement
  4. After running these commands, you need to restart the Oracel instance, follow these commands
    shutdown abort;
    startup;
  5. After startup is complete run the following commands to see the updated values
    show parameters processes;
    show parameters sessions;
    show parameters transactions;

Ref:http://www.fatlemon.co.uk/2009/06/increasing-processes-sessions-and-transactions-in-oracle-xe/

======================================================================================================

Fix ORA-00257: archiver error. Connect internal only, until freed ñ in Oracle 11G

Published on Apr 2 2012 # 91,683 oracle
When your database hangs with a ORA-00257 error while you notice there is plenty of space in the /recoveryarea mountpoint youíre likely to have reached the maximum size defined by the new ** initialization parameter ëdb_recovery_file_dest_sizeë. Fast solution is to simply increase the value for db_recovery_file_dest_size (and after that of course start archivingÖ)

** 2012-04-02T12:10:; I see now the init parameter db_recovery_file_dest_size is not so new in fact has been around since Oracle 10.1 Ö OKÖ.

Mon Apr 02 06:01:12 2012
Errors in file /ora/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/$ORACLE_SID_arc0_25342.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.


You have following choices to free up space from recovery area:

  1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
    then consider changing RMAN ARCHIVELOG DELETION POLICY.
  2. Back up files to tertiary device such as tape using RMAN
    BACKUP RECOVERY AREA command.
  3. Add disk space and increase db_recovery_file_dest_size parameter to
    reflect the new space.
    SQL> sho parameter db_recovery_file

NAME TYPE VALUE


db_recovery_file_dest string /recoveryarea
db_recovery_file_dest_size big integer 10G
SQL>

Still plenty of space in the /recoveryarea mountpoint ñ namely 42G however in the databaseís archive log dest is getting pretty filled up ñ reaching itís defined maxium (calucalated by the os command ëduí).

Overall space usage in /recoveryarea mountpoint

$ df -h /recoveryarea/$ORACLE_SID/archivelog/
Filesystem size used avail capacity Mounted on
/recoveryarea 98G 55G 42G 57% /recoveryarea
Space usage in database /recoveryarea directory

$ du -sh /recoveryarea/$ORACLE_SID/archivelog/
10G /recoveryarea/$ORACLE_SID/archivelog
So in this scenario the quick fix was to issue command below;

SQL> alter system set db_recovery_file_dest_size = 12g;

System altered.

SQL>

====================================================================================================

DEFAULT TABLESPACE

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE’;

====================================================================================================

Connection To RMAN Auxiliary Fails With ORA-01031: INSUFFICIENT PRIVILEGES

Connecting to an auxiliary instance via RMAN fails with ORA-01031 errors. The same is seen when properly testing a connection via SQL*Plus to the auxiliary instance.

RMAN-04006: ERROR FROM AUXILIARY DATABASE: ORA-01031: INSUFFICIENT PRIVILEGES
Changes
You may have tried to add the connecting user to the password file but that has failed:
SQL> select * from v$pwfile_users;
No rows
SQL> grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01109: database not open

Because the auxiliary is nomounted, the addition of a user to the password file for the auxiliary is not allowed, nor does it resolve this problem.

You have verified the same problem happens in SQL*Plus as does RMAN. For example, if in RMAN you are issuing:

RMAN> connect auxiliary @

then for testing SQL*Plus you’d have to issue this from the operating system prompt:

% sqlplus “@ as sysdba”

also shows the ORA-1031 error.

(connecting in two steps to the auxiliary is not a valid test).
Cause
There may be a mismatch in the “case” of characters between these items on the host where the auxiliary database resides:

1) The entry in the listener.ora file for service_name for the auxiliary instance.
2) The entry for db_name in the init.ora file for the auxiliary instance.
3) The naming of the password file.
4) The $ORACLE_SID for the auxiliary instance.

If all four items have matching case characters, you may find the errors connecting remotely via the password file are resolved.
Solution
Set the reference to service_name in the listener.ora, db_name in the init.ora, the password file name itself, and the $ORACLE_SID to all uppercase.

Listener example:

(DESCRIPTION=
(ADDRESS=…)
(ADDRESS=…)
(CONNECT_DATA=
(SERVICE_NAME=SALES.US.ACME.COM)))

The init.ora file:

db_name=SALES

The password file:

orapwSALES.ora or orapwSALES

The $ORACLE_SID:

$ ORACLE_SID=SALES; export ORACLE_SID

==============================================================================================================

Open Database in Restricted Mode

Restricted mode will only allow users with RESTRICTED SESSION privileges to access the database (typically DBAís).

We use the startup restrict command to open the database in restricted mode as seen in this example.

SQL> startup restrict;
After an instance is started, we can use alter system enable/disable restricted session to enable or disable restricted mode.

— Put the database in restricted mode.

SQL> alter system enable restricted session;
— Take the database out of restricted mode.

SQL> alter system disable restricted session;
We can check if the database is in restricted mode by the logins column from v$instance.

SQL> select logins from v$instance;
Any users connected to the Oracle instance when going into restricted mode will remain connected; they must be manually disconnected from the database by exiting gracefully or by the DBA with the ìalter system kill sessionî command.

SQL> alter system kill session ‘session-id, session-serial’;
The session-id and session-serial parameters are found in the v$session view (columns sid and serial#).

==============================================================================================================

RMAN-00554 initialization of internal recovery manager package failed …

you need to connect to the database through RMAN with the help of a SYS user and not ordinary use. Try as

rman target sys/@

==============================================================================================================

PURGE RECYCLEBIN

When the recyclebin is enabled (RECYCLEBIN=ON), any tables that you drop do not get fully deleted, instead,
they are moved to the RecycleBin. The PURGE command allows you to empty the recyclebin. This feature was first introduced in Oracle 10g.

Syntax:

PURGE RECYCLEBIN

PURGE DBA_RECYCLEBIN

PURGE USER_RECYCLEBIN

PURGE TABLE TableName

Purging from all users recyclebins (using DBA_RECYCLEBIN) requires DBA privileges.

Examples

Drop a table, bypassing the recyclebin:

drop table SS64 purge;

Disable the recyclebin for this session:

ALTER SESSION SET RECYCLEBIN=OFF;

===================================================================================================================

SQL PLUS command for displaying datafile size

the space occupied by the tables in the database.

If yes

select sum(bytes)/1024/1024, segment_name from dba_segments group by segment_name

or

select sum(bytes)/1024/1024 from dba_segments where segment_name = ‘Table_name’

Or the space occupied by the datafiles

select bytes/1024/1024, file_name from dba_data_files;

Space allocated:

Select sum(bytes)/1024/1024 from dba_segments;

Free space:

Select sum(bytes)/1024/1024 from dba_free_space;

===================================================================================================================

How to check datafile usage in Oracle Database?

Below is the script script which will list datafile wise Allocated size, Used Size and Free Size
Size details are displayed in MB (Mega Bytes)

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 “Allocated Size(MB)”,
((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) “Used Size (MB)”,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) “Free Size(MB)”
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

===============================================================================================================

Increase datafile size RAC / Resize System Tablespace

alter database datafile ‘+FRA_DG/sqa/datafile/users.259.856599299’ resize 13000M;

alter database datafile ‘/u01/app/oracle/AGPILOT/users01.dbf’ resize 9000M;

alter database datafile ‘/u01/app/oracle/AGPILOT/sysaux01.dbf’ resize 2000M;

Two options:

a) resize the current datafile(s) belonging to SYSTEM tablespace

ALTER DATABASE DATAFILE ‘fileXYZ’ RESIZE nnnn;

b) Add new datafile to SYSTEM tablespace

ALTER TABLESPACE system ADD DATAFILE ‘fileXYZ’ SIZE ….

===============================================================================================================

Query to find space used by a database user

Query to find space used by a database user. Following query can be used to know the space used by the logged in user in MBs:

SELECT sum(bytes)/1024/1024 user_size FROM user_segments;

Query to find the space occupied by all the users in a database. This requires access to dba_segments table:

SELECT owner, sum(bytes)/1024/1024 total_size FROM dba_segments GROUP BY owner ORDER BY total_size DESC;

Total space occupied by all users:

SELECT sum(bytes)/1024/1024 total_size FROM dba_segments;

Total tablespace used by a user

select tablespace_name, username, bytes, max_bytes from dba_ts_quotas;

where,
BYTES – Number of bytes charged to the user
MAX_BYTES – User’s quota in bytes, or -1 if no limit

================================================================================

Space Calculate Privileges to User

grant select on dba_segments to CDR_Nirakar;

grant select on dba_free_space to CDR_Nirakar;

=============================================================================================================================

How to rename a table in Oracle?

There are two ways of renaming a table in Oracle:

Method 1: Simple

rename {old_table_name} to {new_table_name}

Example:

rename CUSTOMER to CUSTOMER_BACKUP

Method 2: Not so Complex

alter table {old_table_name} rename to {new_table_name};

Example:

alter table CUSTOMER rename to CUSTOMER_BACKUP;

The minimum version that supports table renaming is Oracle 8i. All the dependencies of the table will automatically updated.
No need of updating them after wards.

=========================================================================================================

To rename an oracle schema.

Try,

1-Create new schema

2-Export the old schema,

$exp owner=test2
3-Import old schema to new schema,

$imp fromuser=test2 touser=newuser_name

=========================================================================================================

Listener reset in oracle RAC

alter system reset local_listener scope=spfile;

alter system register;

shutdown both instances and ;

startup;

alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=VAL-ORA1)(PORT=1521))’;

=========================================================================================================

Finding a locking session

How to identify lockers?

This article will explain about locks on rows and on objects in ORACLE.
Locks on rows can cause performance problems or even impede a transaction from finishing, when there are processes running for long time we need to validate that they are not waiting on a row(s).

When there is a lock on a row there is also a lock on the dependent objects, if we want to perform a DDL on a locked object we will get an ORA-00054 error.

1 – find blocking sessions with v$session

SELECT
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
FROM
v$session s
WHERE
blocking_session IS NOT NULL

2 – find blocking sessions using v$lock

SELECT
l1.sid || ‘ is blocking ‘ || l2.sid blocking_sessions
FROM
v$lock l1, v$lock l2
WHERE
l1.block = 1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l1.id2 = l2.id2

3 – Views to find blocking session

    DBA_WAITERS
    DBA_BLOCKERS

Scripts for detecting locking

Note 1020012.6 TFTS SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO Note 1020008.6TFTS FULLY DECODED LOCKING SCRIPT
Note 1020007.6 SCRIPT: DISPLAY LOCKS AND GIVE SID AND SERIAL # TO KILL ()
Note 1020010.6 SCRIPT: DISPLAY SQL TEXT FROM LOCKS
Note 1020047.6 SCRIPT: SCRIPT TO DISPLAY USER LOCK INFORMATION Note 1020088.6 SCRIPT: REPORT SESSIONS WAITING FOR LOCKS ()

How to resolve locking situations?

Most locking issues are application specifics. To resolve locking contention, one needs to free the resource by:

Asking the HOLDER to commit or rollback

Killing the session which holds the lock,
For example:
ALTER SESSION KILL SESSION ‘sid, serial#’;

Killing the unix/vms shadow process directly.
This is not recommended as it may prevent proper cleanup of a session
When killing the shadow process, please be careful of shared servers in a multi-threaded environment.
ROLLBACK FORCE or COMMIT FORCE if 2pc pending transaction.

=============================================================================================

Alert log location in Oracle 11gR2

Oracle 11gR2 is a major release upgrade from 10gR2 and 11gR1 both. Many previous parameters like user_dump_dest etc have been deprecated. There is a single parameter called diag_dest. Many DBAís are so used to the location of alert log and trace files , that it becomes quite inconvenient when you start using Oracle 11g . But Oracle 11g has a powerful new utility called ADR CLI to navigate around the alert log and other trace files. I will demonstrate a few ways to check the alert log and trace files using ìadrciî command.

$ adrci

ADRCI: Release 11.2.0.3.0 – Production on Wed Feb 13 10:25:59 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ADR base = “/mnt/PRDapps/oracle/trcprddb/11.2.0/dbhome_1/log”

adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
ìHELP EXTENDEDî to see the list
adrci> show alert
1: diag\clients\user_system\host_3726408541_76
2: diag\rdbms\testdb\testdb
3: diag\tnslsnr\test\listener
Q: to quit
Please select option:

Many of the adrci commands are very similar to the standard Unix vi commands. For example
SHOW ALERT -TAIL (to view the last 10 entries)
SHOW ALERT -TAIL 50 (to view the last 50 entries)
SHOW ALERT -TAIL -F (keeps the alert log open to view as entries arrive until Control C is pressed)
show alert -p ìmessage_text like ë%ORA-07445%íî (to show all occurences of the string ëORA-07445′ in the alert log.)
Purging Alert Log Content
The adrci command ëpurgeí can be used to purge entries from the alert log. Note that this purge will only apply to the XML based alert log and not the text file based alert log which still has to be maintained using OS commands.
The purge command takes the input in minutes and specifies the number of minutes for which records should be retained.
So to purge all alert log entries older than 7 days the following command will be used:
adrci > purge -age 10080 -type ALERT
It is a powerful utility with much more functionality

===========================================================================================================================

FAL_SERVER & FAL_CLIENT IN DATAGUARD

FAL_CLIENT and FAL_SERVER are initialization parameters used to configure log gap detection and resolution at the standby database side of a physical database configuration. This functionality is provided by log apply services and is used by the physical standby database to manage the detection and resolution of archived redo logs.

FAL_CLIENT and FAL_SERVER only need to be defined in the initialization parameter file for the standby database(s). It is possible; however, to define these two parameters in the initialization parameter for the primary database server to ease the amount of work that would need to be performed if the primary database were required to transition its role.

FAL_CLIENT specifies the TNS network services name for the standby database (which is sent to the FAL server process by log apply services) that should be used by the FAL server process to connect to the standby database. The syntax would be:

FAL_CLIENT=

∑ FAL_SERVER specifies the TNS network service name that the standby database should use to connect to the FAL server process. The syntax would be:

FAL_SERVER=

========================================================================================================================

What SQL is currently using the most resources?

SELECT active_session_history.user_id,
dba_users.username, sqlarea.sql_text,
SUM(active_session_history.wait_time + active_session_history.time_waited) total_wait_time
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea, dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE – 60/2880 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4;

=========================================================================================================================
OEM Error : EmKey not configured properly !
EmKey not configured properly. Run “emctl status emkey” : RECONFIGURE ENTERPRISE MANAGER
===========================================================-=============================================================

you need to follow the steps as below :

emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create

=========================================================================================================================

ORA-01138: database must either be open in this instance or not at all

The error was due to the database being open on another instance.
ORA-01138: database must either be open in this instance or not at all

When I shut down the other instance on another node 2 and mounted the instance
on node 1 the backup worked.

=========================================================================================================================

What to do when listener.log is growing.

The best way (IMHO) to do it is to leave the listener up and

  1. stop logging (lsnrctl set log_status off)
  2. del/mv file
  3. start logging (lsnrctl set log_status on)

over time, the Listener.log file can grow to huge size and take a lot of disk space.
If you will try to delete it in windows you may get the following error:

óóóóóóóóó
Error Deleting File or Folder
óóóóóóóóó
Cannot delete listener: It is being used by another person or program.

Close any programs that might be using the file and try again.
óóóóóóóóó
OK
óóóóóóóóó

Linux will allow you to delete the file but it will not create a new one.

The solution to this issue is simple. following are some options:

The easiest solution would be to stop the listener process, delete the file and restart the listener service. However, stopping the listener will prevent new connections to the database and disrupt service.

A better option would be to stop logging to the listener.log file:

In the command prompt type >lsnrctl
This will open the Listener control utility. Type LSNRCTL> set log_status off
now you can navigate to the file location and delete the file using the operating system.
This will prevent the file from growing in the futre.
If you like, you can restart logging LSNRCTL> set log_status on
This will allow you to delete listener.log but nothing will be logged when the log_status is set to off.

If you want to the logging to continue. you can switch to a different logging file:
type LSNRCTL> set log_file listener2.log

The listener will now log to the new file. you can delete or move the old file.

=================================================================================

How to recreate TEMP Tablespace in Oracle

Problem:
You need to recreate a temporary tablespace in Oracle 9i/10g/11g/12c.

Solution:
–Create Temporary Tablespace Temp2
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
‘/oradata/testdb/temp02.dbf’ SIZE 500M;

–Move Default Database temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

–Drop temp tablespace
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

–Recreate Tablespace Temp
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
‘/oradata/testdb/temp01.dbf’ SIZE 500M;

–Move Tablespace Temp, back to new temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

–Drop temporary for tablespace temp
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

===========================================================================================================

sqlplus / as sysdba – ORA-01031 insufficient priv

Check the below:

  1. check groups for oracle user.
    2.check password file is proper or not.
  2. Try to see this line, SQLNET.AUTHENTICATION_SERVICES = (NTS), in sqlnet.ora file if exists. If yes, comment that and try to connect using / as sysdba.

=============================================================================================================

ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) – dependencies exist

ISSUE:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) – dependencies exist

Restore TABLE backup

Schema Status

select username from dba_users where username=upper(‘&username’);

Table Status

select count(*) from SCHEMANAME.TABLENAME;

TRUNCATE TABLE

spool TARGET_TABLENAME_TRUNCATE.log
set echo on term on feed on timing on

truncate table SCHEMANAME.TABLENAME;
truncate table SCHEMANAME.TABLENAME
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where TABLE_NAME=’&TABLE_NAME’;

CONSTRAINT_NAME C TABLE_NAME STATUS


SYS_CONSTRAINT1 C TABLENAME ENABLED
PK_TABLENAME P TABLENAME ENABLED

alter table SCHEMANAME.TABLENAME DISABLE constraint SYS_CONSTRAINT1;

alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME;
ERROR at line 1:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) –
dependencies exist

SOLUTION:

alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME cascade;

Table altered.

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where TABLE_NAME=’TABLE_NAME’;

====================================================================================================================

Check total size of Ram/Memory For Each Database :

select decode( grouping(nm), 1, ‘total’, nm ) nm, round(sum(val/1024/1024)) mb
from
(
select ‘sga’ nm, sum(value) val
from v$sga
union all
select ‘pga’, sum(value)
from v$sysstat
where name = ‘session pga memory’
)
group by rollup(nm);

==================================================================================================================

ORA-00020: maximum number of processes (%s) exceeded

Ever received this error message in your alert log? I have on a couple of occasions. Perhaps an application server started spawning more database connections than it normally does in turn creating more processes on the database. The Oracle description of this error is:
Error: ORA 20 Text: maximum number of processes exceeded
Cause: An operation requested a resource that was unavailable. The maximum number of processes is specified by the initialization parameter PROCESSES. When this maximum is reached, no more requests are processed. Action: Try the operation again in a few minutes. If this message occurs often, shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.

But what if you cannot connect to your database to shut it down and increase the parameter?

oradba01t[labdb01]-/home/oracle/>sqlplus /as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Mon Jun 6 11:25:05 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (%s) exceeded

I always like to look at the alert log to check for any additional details.

Using adrci I tailed the alert log and sure enough we had our ORA-00020 error all over.

2011-06-06 11:17:36.042000 -05:00
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Process m000 submission failed with error = 20
2011-06-06 11:24:35.878000 -05:00
ORA-00020: maximum number of processes 0 exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
adrci> exit

How do I connect to the database to increase my processes parameter? Well I remember Tanel Poder blogged about “How to log on even when SYSDBA can’t do so?”

oradba01t[labdb01]-/home/oracle/>sqlplus -prelim “/as sysdba”

SQL*Plus: Release 11.1.0.7.0 – Production on Mon Jun 6 11:29:54 2011

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

SQL>

I was able to log on to the instance without getting the error message. Now to see if I can shut it down and start it up to increase the parameter.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

It appears that the shutdown abort was successful but the mount command did not complete. Disconnect from the previously connected session and logon normally to start the instance.

SQL>exit
oradba01t[labdb01]-/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 – Production on Mon Jun 6 11:33:07 2011

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

Connected to an idle instance.

SQL>startup mount
ORACLE instance started.

Total System Global Area 4175568896 bytes
Fixed Size 2160352 bytes
Variable Size 3489663264 bytes
Database Buffers 671088640 bytes
Redo Buffers 12656640 bytes
Database mounted.
SQL>

Now we can go ahead and increase our processes parameter. Actually I opted not to increase the parameter since I knew the root cause of the processes being exceeded. This was a Enterprise Manager Grid Control repository and due to some OMS processes had caused the database processes to be exceeded.

=====OR====

ORA-00020 maximum number of processes exceeded
ORA-00020 maximum number of processes exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.

ORA-00020 comes under “Oracle Database Server Messages”. These messages are generated by the Oracle database server when running any Oracle program.

Reference: Oracle Documentation

How to increase PROCESSES initialization parameter:

  1. Login as sysdba
    sqlplus / as sysdba
  2. Check Current Setting of Parameters
    sql> show parameter sessions
    sql> show parameter processes
    sql> show parameter transactions
  3. If you are planning to increase “PROCESSES” parameter you should also plan to increase “sessions and “transactions” parameters
    A basic formula for determining these parameter values is as follows: processes=x sessions=x*1.1+5 transactions=sessions*1.1
  4. These paramters can’t be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
    sql> alter system set processes=500 scope=spfile;
    sql> alter system set sessions=555 scope=spfile;
    sql> alter system set transactions=610 scope=spfile;
    sql> shutdown abort
    sql> startup

==================================================================================================================

Avoid the confusion surrounding Automatic Memory Management

In Oracle Database 11g, the Automatic Memory Management feature requires more shared memory (/dev/shm) and file descriptors.
Metalink Note 169706.1 states that shared memory should be sized to be at least the greater of the database initialisation parameters; MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the server.

However, I have discovered in most cases, that the shared memory needs to be at least 2G greater than MEMORY_TARGET else the dynamic memory allocation becomes inappropriate and can crash the server.

When setting the MEMORY_TARGET parameter, the Oracle RDBMS will dynamically size the SGA and PGA and write ìunderscore-underscoreî parameters to the spfile. This is done to protect the database initialisation memory settings following an instance crash.

On Linux, if the shared memory is not sized (and mounted) to a value greater than MEMORY_TARGET you will receive the following error when attempting to start the database instance.

ORA-00845: MEMORY_TARGET not supported on this system

Below are some examples of the dynamic settings made by the Oracle RDBMS when setting the MEMORY_TARGET parameter.

(Note the small SGA and huge PGA size when MEMORY_TARGET 1GB less than shared memory size)

With 20G MEMORY_TARGET and 25G shared memory (stable)

Oracle init.ora parameter file generated by instance ORCL1 on 08/26/2009 11:39:02

*.__db_cache_size=8G
*.__java_pool_size=256M
*.__large_pool_size=256M
*.__pga_aggregate_target=8G
*.__sga_target=12G
*.__shared_io_pool_size=0
*.__shared_pool_size=2816M
*.__streams_pool_size=512M

With 24G MEMORY_TARGET and 25G shared memory (unstable)

Oracle init.ora parameter file generated by instance ORCL1 on 08/17/2009 14:30:08

ORCL1.__db_cache_size=512M
ORCL2.__db_cache_size=1G
*.__java_pool_size=256M
*.__large_pool_size=256M
*.__pga_aggregate_target=20736M
*.__sga_target=3840M
*.__shared_io_pool_size=0
ORCL1.__shared_pool_size=2G
ORCL2.__shared_pool_size=2304M
*.__streams_pool_size=512M

Configuring Shared Memory
The shared memory can be configured using a swap partition or swap file. Best practice is to create an appropriately sized swap partition. However, if the existing swap partition exists and is too small, you can add a swap file by following the example below (as root user):

To determine the amount of shared memory available, enter the
following command:

df -h /dev/shm/
Filesystem Size Used Avail Use% Mounted on
shmfs 8G 0 8G 0% /dev/shm

Create a 16G swap file:

dd if=/dev/zero of=/swapfile bs=1024 count=16777216

Setup the swap file with the command:

mkswap /swapfile

To enable the swap file immediately but not automatically at boot time:

swapon /swapfile

To enable it at boot time, edit /etc/fstab to include:

/swapfile swap swap defaults 0 0

To view swap space:

cat /proc/swaps
Filename Type Size Used Priority
/dev/cciss/c0d0p5 partition 8193108 0 -1
/swapfile file 16777208 0 -2

Edit /etc/fstab to set shmfs size at bootup:

shmfs /dev/shm tmpfs size=24g 0

To re-read /etc/fstab and set shmfs size on the fly:

mount -t tmpfs shmfs -o size=24g /dev/shm

View /etc/fstab

cat /etc/fstab
LABEL=/ / ext3 defaults 1 1
LABEL=/u02 /u02 ext3 defaults 1 2
LABEL=/u01 /u01 ext3 defaults 1 2
LABEL=/boot /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs defaults 0 0
shmfs /dev/shm tmpfs size=24g 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SW-cciss/c0d0p5 swap swap defaults 0 0
/swapfile swap swap defaults 0 0

N.B.
MEMORY_MAX_TARGET and MEMORY_TARGET cannot be used when LOCK_SGA is enabled or with huge pages on Linux

Conclusion
From experience I suggest sizing the shared memory partition (/dev/shm) to at least 2 GB greater than the MEMORY_TARGET or MEMORY_MAX_TARGET for your database instance. This will enable Oracle to grow instance memory based on workload.

This is particularly important when using Automatic Storage Management. Oracle will set the MEMORY_TARGET parameter by default for an ASM instance, which will of course also use the available shared memory on the server.

More Gotchas
Setting tmpfs size
Another Oracle 11g gotcha to be aware of is; receiving the ORA-00845 error even though you have created a swap partition/file greater than your database MEMORY_TARGET. This is because the default size of tmpfs on Linux (2.4 kernel and above) is approximately half the physical system memory.

This limitation can be dynamically changed by editing the /etc/fstab file, removing the ìdefaultsî attribute and specifying a maximum size for tmpfs as shown in example below:

tmpfs /dev/shm tmpfs size=24g 0 0

Hitting Bug 7272646

If your RDBMS Oracle Home has newly been upgraded from 11.1.0.6 to 11.1.0.7 with no additional interim patches applied, you are likely to hit the following bug if the MEMORY_TARGET database initialisation parameter exceeds 3G in size.

BUG 7272646 – ORA-27103 WHEN MEMORY_TARGET > 3G

The bug causes the Oracle instance to crash when opening the database. The following errors are seen in the instanceís alert log:

ORA-27103: internal error
Linux-x86_64 Error: 2: No such file or directory
Additional information: -1
Additional information: 1
MMAN (ospid: 12211): terminating the instance due to error 27103
Tue May 25 12:46:46 2010
ORA-1092 : opitsk aborting process
Tue May 25 12:46:46 2010
System state dump is made for local instance
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_diag_12185.trc
Trace dumping is performing id=[cdmp_20100525124646]
Instance terminated by MMAN, pid = 12211

To resolve this issue:
Download patch 7272646 from Oracle Metalink
Shutdown the database instance
Apply the patch as described in the README.txt file
Startup the Oracle instance

==================================================================================================================

Where is the password column from DBA_USERS in 11g?

Oracle 11g brought several security enhancements, as it is well known by the 11g users. On previous Oracle versions it was possible to query the DBA_USERS PASSWORD column to get the hashed password string. It was useful when someone tried to temporarily reset the user’s password and restore it to its original value without actually knowing it.

The command:

ALTER USER IDENTIFID BY VALUES ’14C785FC66029BF9′;

it could take the hashed value from the DBA_USERS data dictionary view. However starting with Oracle 11g this column is null … so where are we supposed to take this hashed value from?.

SQL> SELECT USERNAME, PASSWORD
2 FROM DBA_USERS
3 WHERE USERNAME=’SYSTEM’;

USERNAME PASSWORD


SYSTEM <>

By taking a look at the underlying data dictionary table where the DBA_USERS view is built on, we can easily find the data dictionary table is SYS.USER$, and it has a column named … guess … PASSWORD.

Oracle 11g only makes it a little bit more difficult to get the hashed password, but if you have enough privileges you can still apply the traditional procedure to temporarily reset the password, and still have access to the hashed password.

SQL> SELECT NAME, PASSWORD
FROM SYS.USER$
WHERE NAME = ‘SYSTEM’ ;

NAME PASSWORD


SYSTEM 2D594E86F93B17A1

SQL> ALTER USER SYSTEM IDENTIFIED BY tempPasswd;
User altered.

SQL> connect system/[email protected];
Connected.

SQL> ALTER USER SYSTEM IDENTIFIED BY VALUES ‘2D594E86F93B17A1’
User altered.

SQL> connect system/[email protected]
Connected.

===========================================================================================================

How do I synchronize two Oracle Databases?/How to sync oracle production database to development database

First of all I’d suggest you reconsider your design. The simplest way to repicate your data is through views as suggested in this SO. You could create a DATABASE LINK between your two DBs and create views at the remote site that would query the local database. This would be the simplest way to have Real-Time synchronization (less code, less maintenance).

If you really want to replicate your data synchronously, you should read the Replication Guide. You could go with materialized views. You will need to define materialized view logs on your tables at your master site. At the remote site you will create ON COMMIT REFRESH materialized views.
OR
Maybe you can use MATERIALIZED VIEWS (with FAST REFRESH), esp. when it is not necessary to copy/update each single table.

Another option it the “Oracle Data Guard” product, however there is a considerable price-tag on it.

==================================================================================================================

ORA-21561 OID generation failed Linux

When trying to connect to database , error ORA-21561 observed –

[[email protected] scripts]# sqlplus shareo/[email protected]

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 11 20:30:29 2014

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

ERROR:
ORA-21561: OID generation failed

[[email protected] ~]# tnsping ORAINST

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 11-DEC-2014 20:27:53
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/opt/oracle/product/11.2.0/client_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.20.5.9)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORAINST) (SID = ORAINST)))
OK (0 msec)

How we solved :

The entry for host 192.20.5.9 was missing in /etc/hosts.
Added the IP 192.20.5.9 to /etc/hosts

Terminal # cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.20.5.9 ShareoliteLab

After this connection was successful.

============================================================================

adding tnsping to the instant client 11.2

apparently oracle removed tnsping from the instant client 11.2.0.2.
this very handy command to do some troubleshooting is crucial in our environment so we tried to add it from a full blown oracle client installation.

in order to add tnsping to the instant_client you must copy the tnsping command to the bin directory of the instant client.

however you also need to copy
$ORACLE_HOME/network/message directory to the instant client directory in the network subdir

also if you want to make use of ldap you need to copy the

$ORACLE_HOME/ldap directory to you instant client directory.
and $ORACLE_HOME/lib directory to your instant client directory.

=======================================================================

SQL DEVELOPER CONNECTION USING JDBC

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.8.156)(PORT=1521)))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=demodb)))

=======================================================================

CRS-5005: IP Address: is already in use in the network

After a change in ASM process parameter i restared the crs stack.
Everything went fine… almost the following resource are offline

ora.srvtst01.vip
ora.LISTENER.lsnr
ora.srvtst01.LISTENER_SRVTST01.LSNR

when i tried start the ora.srvtst01.vip i got the following error
$ crsctl start res ora.srvtst01.vip
CRS-2672: Attempting to start ‘ora.srvtst01.vip’ on ‘srvtst01’
CRS-5017: The resource action “ora.srvtst01.vip start” encountered the following error:
CRS-5005: IP Address: 10.110.140.173 is already in use in the network
CRS-2674: Start of ‘ora.srvtst01.vip’ on ‘srvtst01’ failed
CRS-2679: Attempting to clean ‘ora.srvtst01.vip’ on ‘srvtst01’
CRS-2681: Clean of ‘ora.srvtst01.vip’ on ‘srvtst01’ succeeded
CRS-2632: There are no more servers to try to place resource ‘ora.srvtst01.vip’ on that would satisfy its placement policy
CRS-4000: Command Start failed, or completed with errors.
That is strange the vip ip address should not be in use off course
$ nslookup srvtst01
Server: 145.72.247.10
Address: 145.72.247.10#53

Name: srvtst01.italie.nl
Address: 10.110.140.73

[email protected]:CRS:/root
$ nslookup srvtst01-vip.italie.nl.
Server: 145.72.247.10
Address: 145.72.247.10#53

Name: srvtst01-vip.italie.nl
Address: 10.110.140.173

As you see the ip address for the host and vip are different. When a do a SSH whith the vip ip 10.110.140.173 i connect to the host srvtst01 what is good.When i look with ifconfig command i don’t see the IP address of the vip.
$ ifconfig
eth0 Link encap:Ethernet HWaddr 00:16:3E:70:40:01
inet addr:10.110.140.73 Bcast:10.233.240.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:43716169 errors:0 dropped:0 overruns:0 frame:0
TX packets:10090483 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:8036689632 (7.4 GiB) TX bytes:98860268867 (92.0 GiB)

eth1 Link encap:Ethernet HWaddr 00:16:3E:70:40:02
inet addr:172.29.96.64 Bcast:172.29.96.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:19477710 errors:0 dropped:0 overruns:0 frame:0
TX packets:9399 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:2589277692 (2.4 GiB) TX bytes:9266153 (8.8 MiB)

eth1:1 Link encap:Ethernet HWaddr 00:16:3E:70:40:02
inet addr:169.254.168.11 Bcast:169.254.255.255 Mask:255.255.0.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:25295206 errors:0 dropped:0 overruns:0 frame:0
TX packets:25295206 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:37308951844 (34.7 GiB) TX bytes:37308951844 (34.7 GiB)
As you see i’m missing the vip ip address. I only see the host ip address.
To correct this i’ve to make a network alias and adjust it to ETH0 as it shoud be.

[email protected]:CRS:/root
$ ifconfig eth0:1 10.110.140.173 up
[email protected]:CRS:/root
$ ifconfig
eth0 Link encap:Ethernet HWaddr 00:16:3E:70:40:01
inet addr:10.233.240.73 Bcast:10.233.240.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:43718610 errors:0 dropped:0 overruns:0 frame:0
TX packets:10090883 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:8036957710 (7.4 GiB) TX bytes:98860318155 (92.0 GiB)

eth0:1 Link encap:Ethernet HWaddr 00:16:3E:70:40:01
inet addr:10.110.140.173 Bcast:10.255.255.255 Mask:255.0.0.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

As you see there is now a eth0:1 with the ip address of the vip.
Now i start the vip
[email protected]:CRS:/root
$ crsctl start res ora.srvtst01.vip
CRS-2672: Attempting to start ‘ora.srvtst.vip’ on ‘srvtst01’
CRS-2676: Start of ‘ora.srvtst01.vip’ on ‘srvtst7040’ succeeded
Why O why the eth0:1 was missing is still a ridle.

Thats it

==============================================================

Use below query to see your total session

SELECT username, count(username) FROM v$session WHERE username IS NOT NULL group by username;

==============================================================

VIP missing in RAC /Adding VIP in RAC

Try this
srvctl remove vip -i “nodo2-vip” -f -y
srvctl remove network -k 2
srvctl add vip -n nodo2 -k 1 -A 192.168.4.143/255.255.255.0/eth0

==============================================================

Detecting Lock Conflicts

Detecting locks in Oracle,
V$SESSION,
V$TRANSACTION,
V$LOCK, and V$LOCKED_OBJECT
to see who is locking what resource.

==============================================================

Linux: “Argument list too long” error when trying to use ‘/bin/rm’ command

This error is not very common but this is something that you will see for sure in your daily routine if you are a Linux user. Lets say you are working with Apache webserver and there comes a situation where you have to delete log files for Apache.

[root @ guest logs]# pwd
/u01/app/oracle/product/j2ee/Apache/Apache/logs
[root @ guest logs]# ls -l | grep log | wc -l
5195
Try issuing the “rm” command to delete those long list of files
[root @ guest logs] # rm log
/bin/rm: Argument list too long.
If that returns an error message regarding “Argument list too long” then there are
alternatives to remove the files. It seems that the rm command can’t deal with such
number of arguments. Fortunately there are some workarounds for this problem.

You can combine rm with find:

find . | xargs rm -f

Or if you want to delete all the files in the directory with a single stroke issue the following code:

ls | xargs rm -f

==================================================================

Dynamic Listener Registration ith the Instance in RAC/Satandalone

alter system set local_listener = ‘(address_list=(address=(protocol=tcp)(host=)(port=1521)))’ scope=spfile sid=’sqa2db1′;

==================================================================

ORA-00054: resource busy and acquire with NOWAIT specified

Solution : 1

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT ,S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;

ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

Solution : 2

DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

alter session set ddl_lock_timeout = 600;

How to avoid the ORA-00054:
– Execute DDL at off-peak hours, when database is idle.
– Execute DDL in maintenance window.
– Find and Kill the session that is preventing the exclusive lock.

=========================

VIP missing in RAC

srvctl remove vip -i “nodo2-vip” -f -y
srvctl remove network -k 2
srvctl add vip -n nodo2 -k 1 -A 192.168.4.143/255.255.255.0/eth0

====================================================================================================================

When I’d like to view incoming connections from our application servers to the database I use the following command:

SELECT username FROM v$session WHERE username IS NOT NULL ORDER BY username ASC;

select status, count(1) as cxns from V$SESSION group by status;

Select count(1) From V$session where status=’ACTIVE’;

Select count(1) From V$session where status=’INACTIVE’;

select job from dba_jobs where log_user=’SNP180420161601833′;

SELECT SID, SERIAL#, MACHINE, USERNAME FROM V$SESSION WHERE USERNAME=’SNP180420161601833′;

====================================================================================================================

For disabling sysdba remote login:

show parameter remote_login_passwordfile

alter system set remote_login_passwordfile=none scope=spfile;

restart db

other options SHARED/EXCLUSIVE

====================================================================================================================

—-Generate DDL—

SET LONG 20000
SET PAGESIZE 0

SELECT DBMS_METADATA.get_ddl (object_type, object_name, USER)
FROM user_objects;

====================================================================================================================

Features are available in standard edition

SQL> SELECT Parameter,Value FROM V$OPTION Where Value = ‘TRUE’;

====================================================================================================================

catalog.sql & catproc.sql,utlrp.sql

catalog.sql and catproc.sql are used to create/recreate database internal views/procedures/packages, etc…

Catalog – creates data dictionary views.

Catproc – create in built PL/SQL Procedures, Packages etc

No no harm in running. But as alapps said its time consuming as well as resource consuming.
You can try to run utlrp.sql script to recompile all invalid PL/SQL packages and then recheck the INVALID objects. If you still have that many INVALID objects after running the script, then startup in restricted mode and try the catalog.sql & catproc.sql script.

BTW…have you patched your database recently? If so, did you run catpatch.sql?

$sqlplus “/as sysdba”

SQL> shutdown immediate

SQL> startup upgrade

SQL > @?/rdbms/admin/catalog.sql

SQL > @?/rdbms/admin/catproc.sql

SQL > @?/rdbms/admin/utlrp.sql

SQL> shutdown immediate

SQL> startup


SQL> select * from DBA_REGISTRY;

also run the utilrip utility (as sysdba user) to recompile all objects within your databases.

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

then re-run the query again:

SQL> select * from DBA_REGISTRY;

=======================================================================

RESTORE POINT DATABASE TO ANY POINT IN TIME

create restore point test_guarantee guarantee flashback database;

select name, scn, storage_size, time, guarantee_flashback_database from v$restore_point;

flashback database to restore point large_update;
flashback table test to restore point table_update;

=======================================================================

How to quickly restore to a clean database using Oracleís restore point

Applies to:
Oracle database ñ 11gR2

Problem:

Often while conducting benchmarking tests, it is required to load a clean database before the start of a new run. One way to ensure a clean database is to recreate the entire database before each test run, but depending on the size of it, this approach may be very time consuming or inefficient.

Solution:

This article describes how to use Oracleís flashback feature to quickly restore a database to a state that existed just before running the workload. More specifically, this article describes steps on how to use the ëguaranteed restore pointsí.

Restore point:
Restore point is nothing but a name associated with a timestamp or an SCN of the database. One can create either a normal restore point or a guaranteed restore point. The difference between the two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available (assuming you have enough space in the flash recovery area).

NOTE: In this article Flashback logging was not turned ON.

Guaranteed Restore point:

Prerequisites: Creating a guaranteed restore point requires the following prerequisites:

The user must have the SYSDBA system privileges
Must have created a flash recovery area
The database must be in ARCHIVELOG mode
Create a guaranteed restore point:
After you have created or migrated a fresh database, first thing to do is to create a guaranteed restore point so you can flashback to it each time before you start a new workload. The steps are as under:

$> su ñ oracle
$> sqlplus / as sysdba;
Find out if ARCHIVELOG is enabled
SQL> select log_mode from v$database;
If step 3 shows that ARCHIVELOG is not enabled then continue else skip to step 8 below.
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> create restore point CLEAN_DB guarantee flashback database;
where CLEAN_DB is the name given to the guaranteed restore point.
Viewing the guaranteed restore point
SQL> select * from v$restore_point;

Verify the information about the newly created restore point. Also, note down the SCN# for reference and we will refer to it as ìreference SCN#î

Flashback to the guaranteed restore point
Now, in order to restore your database to the guaranteed restore point, follow the steps below:

$> su ñ oracle
$> sqlplus / as sysdba;
SQL> select current_scn from v$database;
SQL> shutdown immediate;
SQL> startup mount;
SQL> select * from v$restore_point;
SQL> flashback database to restore point CLEAN_DB;
SQL> alter database open resetlogs;
SQL> select current_scn from v$database;
Compare the SCN# from step 9 above to the reference SCN#.

NOTE: The SCN# from step 9 above may not necessarily be the exact SCN# as the reference SCN# but it will be close enough.


ORA-03113: end-of-file on communication channel

archive log issue

1) Add disk space and increase db_recovery_file_dest_size parameter
2) Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.