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

Error Found

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

Solution:

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.

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

Nodeapps:
srvctl status nodeapps -n nodename

srvctl config nodeapps -n nodename

ASM:

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

Database:

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

Instance:

srvctl status instance -d dbname -i instancename

Services:
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]

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)

"Description

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.

Workaround

Set _subquery_pruning_enabled=FALSE "

BUT, there is another workaround

Flush Shared pool and the alert log gets cleared.

alter system flush shared_pool; 

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,
       tablespace_name,
       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
/

Oracle Database Directories

System Privileges Needed

GRANT create any directory TO <user_name>;
GRANT drop any directory TO <user_name>;

Create A Directory

CREATE OR REPLACE DIRECTORY <directory_name> AS '<operating_system_path>';

conn / as sysdba

desc dba_directories

set linesize 121
col owner format a15
col directory_name format a20
col directory_path format a70

SELECT * FROM dba_directories;

CREATE OR REPLACE DIRECTORY temp AS '/opt/app/user/';

SELECT * FROM dba_directories;

Grant Read On A Directory

GRANT READ ON DIRECTORY <directory_name> TO <schema_name>

col grantor format a20
col grantee format a20
col table_schema format a20
col table_name format a20
col privilege format a10

SELECT grantor, grantee, table_schema, table_name, privilege FROM all_tab_privs WHERE table_name = 'EMP';

GRANT READ ON DIRECTORY temp TO testuser;

SELECT grantor, grantee, table_schema, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'EMP';

Grant Write On A Directory

GRANT WRITE ON DIRECTORY <directory_name> TO <schema_name>

SELECT grantor, grantee, table_schema, table_name, privilege
FROM all_tab_privs
WHERE table_name = 'EMP';

Revoke Read On A Directory

REVOKE READ ON DIRECTORY <directory_name> FROM <schema_name>

Revoke Write On A Directory REVOKE WRITE ON DIRECTORY <directory_name> FROM <schema_name>

Drop A Directory

DROP DIRECTORY <directory_name>;

SELECT * FROM dba_directories;

DROP DIRECTORY temp;

SELECT * FROM dba_directories;

ORA-12853: insufficient memory for PX buffers: current 3091232K, max needed 6786000K

Got below error while running utlrp

ERROR at line 1:
ORA-12801: error signaled in parallel query server P619, instance (2)
ORA-12853: insufficient memory for PX buffers: current 3091232K, max needed
6786000K
ORA-04031: unable to allocate 65560 bytes of shared memory ("large
pool","unknown object","large pool","PX msg pool")
ORA-06512: at "SYS.UTL_RECOMP", line 865
ORA-06512: at line 4


As it was a “PX” (parallelism) error, and my system has a lot of processors, the problem was that my default parallel_max_servers was 970!
This number would be acceptable if I had enough free memory (specially pga) to accommodate all of them, but I didn’t. My MEMORY_TARGET was the minimal.

So to resolve it, I had to change this parameter to a lower value:

SQL> show parameter parallel_max_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     970


Brought this down to 50

SQL> alter system set parallel_max_servers=50 scope=both sid='*';

System altered.

This stopped the error!!.

Hope this works for you

Oracle : Start Database in No archive log mode.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/app/dbname/oraflsh01/archivelog
Oldest online log sequence     9
Next log sequence to archive   34
Current log sequence           34

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5.9861E+10 bytes
Fixed Size                  2267792 bytes
Variable Size            1.0737E+10 bytes
Database Buffers         4.8989E+10 bytes
Redo Buffers              131936256 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/app/dbname/oraflsh01/archivelog
Oldest online log sequence     9
Current log sequence           34