Thursday, April 30, 2015

ORA-01034 ORA-27101 Linux-x86_64 Error: 2: No such file or directory

Error Found

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

To resolve this make sure the Oracle Home locations is correct in the listener.ora file.

You can check the OH from where listener is running by running

ps -ef | grep tns

And correct the entries in the listener.ora file with the correct OH location.

Monday, April 27, 2015

Oracle RAC SRVCTL commands

What is srvctl? 

It is the Server Control Utility, we use SRVCTL to start and stop the database and instances, manage configuration information, and to add, move or remove instances and services.

These are some of the srvctl commands I frequently use, this is not a complete reference guide.

I organized the commands as follow:

To start a rac database
To stop a rac database
To check status and configurations
To start and stop instances
To start, stop and manage services
Start a rac database (order: nodeapps – asm – database)

srvctl start nodeapps -n nodename
srvctl start asm -n nodename
srvctl start database -d dbname

[options are: srvctl start database -d dbname -o open | -o mount | -o nomount]

Stop a rac database (order: database – asm – nodeapps)

srvctl stop database -d dbname -o immediate
options are: srvctl stop database -d dbname -o normal | -o transactional | -o immediate | -o abort

srvctl stop asm -n nodename
options are: srvctl stop asm -n nodename -o immediate

srvctl stop nodeapps -n nodename

To check status and configurations

srvctl status nodeapps -n nodename

srvctl config nodeapps -n nodename


srvctl status asm -n nodename
srvctl config asm -n nodename


srvctl status database -d dbname
srvctl config database -d dbname (shows instances name, node and oracle home)


srvctl status instance -d dbname -i instancename

srvctl status service -d dbname

To start and stop instances

srvctl start instance -d dbname -i instancename
srvctl stop instance -d dbname -i instancename

To create services

srvctl add service -s servicename -r preferrednode -a availablenode -d dbname -P BASIC -e SELECT -z 180 -w 5 -B NONE

To start, stop and manage services

srvctl status service -d dbname
srvctl config service -d dbname
srvctl start service -d dbname -s servicename
srvctl stop service -d dbname -s servicename

srvctl relocate service -d dbname -s servicename -i currentinstancename -t newinstancename [-f]

Monday, April 6, 2015

ORA-00600: internal error code, arguments: [qesmaGetPamR-NullCtx]

ORA-00600: internal error code, arguments: [qesmaGetPamR-NullCtx], [], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Apr 06 11:00:30 2015
Sweep [inc][1123067]: completed
Mon Apr 06 11:00:30 2015

This can be resolved by following the Oracle Document
(Doc ID 12591399.8)
Partition pruning for joins between local partitioned tables and
remote tables may fail with ORA-600 [qesmagetpamr-nullctx] or other unexplained ORA- errors like ORA-14091.


Set _subquery_pruning_enabled=FALSE "

BUT, there is another workaround

Flush Shared pool and the alert log gets cleared.

alter system flush shared_pool; 

Thursday, April 2, 2015

Oracle : Find Database Size

compute sum of size_in_mb on dummy
compute sum of size_in_gb on dummy
break on dummy

col dummy noprint
col size_in_mb format 99,999,999        heading "Size MB"
col size_in_gb format 999,999.9         heading "Size GB"
col nbr_of_files format 99,999,999      heading "Nbr of Files"

select null dummy,
       count(*)                         nbr_of_files,
       sum(bytes/1024/1024)             size_in_mb,
       sum(bytes/1024/1024/1024)        size_in_gb
from dba_data_files
group by tablespace_name