ORA-12514 error on RAC environment

Hello

i faced ORA-12514 error on RAC environment.

i am able to tnsping but while connecting to db i got below error:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Solution:

1) Check tnsnames service and node tnsnames.ora service,port should be same.

2) listener and both node should be up and running.

3) user below command from sys or system users.

alter system set remote_listener='<scanname>:<port>';

Example:

alter system set remote_listener='rac-scan.sigmasys.net:1521';

  
 

Error: ora 00604 ora 12663 ora 36961 ora 06512

In alert log i found below error.

Error:

ORA-00604: error occurred at recursive SQL level 1
ORA-12663: Services required by client not available on the server
ORA-36961: Oracle OLAP is not available.
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 15

Problem: This error occurs due to some trigger.We can see trigger name here in error.Here trigger name is OLAPIHISTORYRETENTION.

Resolution : First we need schema name of that trigger.For that try below query through sys/system user.

SQL> select OWNER,TRIGGER_NAME from DBA_TRIGGERS where TRIGGERING_EVENT like '%STARTUP%';

Or 

SQL> select OWNER,TRIGGER_NAME from DBA_TRIGGERS where TRIGGER_NAME like 'OLAPIHISTORYRETENTION';


In my case Result is like that:
SYS,'AURORA$SERVER$STARTUP'’

SYS,‘OLAPISTARTUPTRIGGER’

Now we need to disable it.

SQL> alter trigger OLAPISTARTUPTRIGGER disable;

ORA-00600: internal error code

Cause: It may happen  the error was in undo corruption.

Resolve:

Step 1:
SQL> SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

NAME VALUE
-------------------- --------------------
undo_management MANUAL
undo_tablespace UNDO_TBS

Step2:
SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME like 'UNDO%';

FILE_NAME                         TABLESPACE_NAME
----------------------------------------------------------------
/ebiz/oracle/db/apps_st/data/undotbs_02.dbf     UNDO_TBS
/ebiz/oracle/db/apps_st/data/undotbs_01.dbf     UNDO_TBS

Step 3: Create a new undo tablespace
SQL> create UNDO tablespace UNDOTBS datafile '/ebiz/oracle/db/apps_st/data/undotbs01.dbf' size 1024m REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 1024M;
Tablespace created.

Step 4:
SQL> ALTER SYSTEM SET undo_tablespace = 'UNDOTBS' scope=spfile;
System altered.

Step 5: set old undo tablespace offine mode and drop
SQL> ALTER TABLESPACE UNDO_TBS offline;
Tablespace altered.

SQL> drop tablespace UNDO_TBS including contents and datafiles;
Tablespace dropped.

Step 6:
Rebounced the db services

Step 7: Changed the undo management parameter to AUTO
SQL> alter system set undo_management='AUTO' scope=spfile;
System altered.

SQL> SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

NAME VALUE
-------------------- --------------------
undo_management AUTO
undo_tablespace UNDOTBS

EXPDP AND IMPDP COMMANDS

----------------IMPDP FULL SCHEMA------------------------------
impdp system/<syspassword>  transform=segment_attributes:n DUMPFILE=<dumpfile>.dmp logfile=<logfilename>.log remap_schema=<expdp schema>:<impdp schema> PARALLEL=5

--------------IMPDP for table--------------------------------
impdp system/<syspassword> tables=<schema name>.<tablename> DUMPFILE=<dumpfile>.dmp logfile=<dumpfile>.log transform=segment_attributes:n 

--------------expdp for full database with parallel--------------
expdp system/<syspassword> FULL=y DUMPFILE=<filename>.dmp PARALLEL=5 LOGFILE=<logfilename>.log JOB_NAME=<jobname>

----------------expdp for a table----------------------------------
expdp system/<syspassword> tables=<schemaname>.<tablename> DUMPFILE=<filename>.dmp PARALLEL=5 LOGFILE=<logfilename>.log JOB_NAME=<jobname>

--------------how to stop and start job-----------------------------
IMPORT>STOP_JOB=IMMEDIATE;
IMPORT>START_JOB;
IMPORT>STATUS;
IMPORT>HELP;

---------------EXPDP for a schema--------------------
expdp system/<systempasword> SCHEMAS=<schemaname> DUMPFILE=<dumpfilename>.dmp LOGFILE=<logfilename>.log PARALLEL=5

--------------IMPDP for a schema---------------------------------
impdp system/<systempasword> SCHEMAS=<schemadname> DUMPFILE=<dumpfilename>.dmp LOGFILE=<logfilename>.log PARALLEL=5

------------IMPDP with exclude option----------------------------
impdp system/<password> SCHEMAS=<schemaname> REMAP_SCHEMA=<expdp schema>:<impdpschema>
DUMPFILE=<dumpfilename>.dmp 
EXCLUDE=constraint, ref_constraint, index,materialized_view  
logfile=<logfilename>.log 

-----------IMPDP with SQLFILE option------------------------
impdp <schemaname>/<password> DUMPFILE=<dumpfile>.dmp SQLFILE=<filename>.sql

----------How to use Exdp and Impdp over Network Link : Oracle DB--------------------

#Login in sqlplus 
sqlplus / as sysdba

#Create a connection link for the database which you want to export 
create database link remotelink connect to targetdbuser identified by targetdbpassword using 'hostname:port/sid'
#check connection 
select * from dual@remotelink

# create a local directory where the dump file will be stored and map it to your database dir by creating 
create directory dumpdir as '<path>';
# give permission to local user by whom we will be running expdp
GRANT read, write ON DIRECTORY dumpdir TO localdb;
# check if the dir is created 
 select directory_name, directory_path from dba_directories ;

# You might get following error , if you do not grant read write access of directory to the user .
#ORA-39002: invalid operation
#ORA-39070: Unable to open the log file.
#ORA-39087: directory name dumpdir is invalid

GRANT EXP_FULL_DATABASE to targetdbuser;

expdp userid=localdb/localdb@//localhost:1521/ORCL dumpfile=testdump.dmp logfile=testdump.log SCHEMAS=myschema directory=dumpdir 

grant imp_full_database to myschema ;

impdp myschema/mytest@//localhost:1521/orcl schemas=myschema directory=dumpdir dumpfile=testdump.dmp logfile=impdpnewtest1.log 


------------------------------Schema level with content dataonly and table exist action truncate--------------
impdp system/<password> schemas=<schemaname> content=data_only directory=<directoryname> dumpfile=<dumpfilename>.dmp logfile='<logfilename>.log' TABLE_EXISTS_ACTION=TRUNCATE

-----------------------------Schema level with content dataonly and table exist action replace---------------------
impdp system/<password> schemas=<schema> directory=<dictionary> dumpfile=<dumpfilename>.dmp logfile='<logfilename>.log' TABLE_EXISTS_ACTION=REPLACE

Note: TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE} 

SP2-0308: cannot close spool file

SP2-0308: cannot close spool file

This Error popup due to disk partition is going to full so to remove this error delete something from your hard disk partition..

ORA-02391:exceeded simultaneous SESSIONS_PER_USER limit

ORA-02391 is user session error. It means you need to kill some user session if its not required or you need to increase sessions per use limit. Killing sessions can be very destructive if you kill the wrong session so be careful when killing session. for that first we need to identify that sessions for that query is given below. . SQL>SELECT s.inst_id, s.sid,s.serial#,p.spid,s.username,s.program FROM   gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE  s.type != 'BACKGROUND'; The basic syntax for killing sessions is given below. SQL>ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' immediate; if you want to increase user session process are given below: 1) SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; 2) ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; 3) SELECT name, value FROM gv$parameter WHERE name = 'resource_limit'; 4) ALTER PROFILE <profile name>  LIMIT sessions_per_user 80;

Lookup error message & information with the help of error number

set serveroutput on
exec dbms_output.put_line('==> '||sqlerrm( &errno ) );