ORA-12801,ORA-12853,ORA-04031

Error details:
————-
ORA-12801: error signaled in parallel query server P026, instance my_server:mydb (2)
ORA-12853: insufficient memory for PX buffers: current 323904K, max needed 2673360K
ORA-04031: unable to allocate 65560 bytes of shared memory (“large pool”,”unknown object”,”large pool”,”PX msg pool”)

The problem was that default parallel_max_servers was 470!
This number would be acceptable if database had enough free memory (specially pga)
to accommodate all of them, but database MEMORY_TARGET was the minimal.

SQL> show parameter parallel

NAME TYPE VALUE
———————————— ———– ——————————
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 470
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 2
parallel_servers_target integer 512
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0

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

ALTER SYSTEM SET parallel_max_servers=40 SCOPE=BOTH;

Advertisements
Posted in Oracle | Tagged , , , | Leave a comment

Oracle datapump Import (IMPDP) fails due to Error ORA-31693,ORA-31640,ORA-19505,ORA-27037 Oracle 11gR2


Error Details:
==============

ORA-31693: Table data object “COMMON”.”LOC_ITEM_DSPTCH_SPEC” failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file “/dev/shm/Q1051842/OMS_O_CUST_COMMON.dmp” for read
ORA-19505: failed to identify file “/dev/shm/Q1051842/OMS_O_CUST_COMMON.dmp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Solution Description:
=====================

Issue is due to the mount/file system was not being accessible from the second node in the RAC.
Then I made Cluster=N to force Data Pump to use only the instance where the job is started and ran again data pump job using below parfile

userid=”/ as sysdba”
CLUSTER=N
directory=MYDIR
JOB_NAME=JOB24_10thFeb2015
dumpfile=OMS_O_CUST_COMMON.dmp
logfile=imp_OMS_O_CUST_COMMON_10thFeb.log
TABLE_EXISTS_ACTION=REPLACE
EXCLUDE=db_link
schemas=OMS_O,CUST,COMMON

Posted in Oracle, Oracle RAC | Tagged , , , , | Leave a comment

Oracle datapump Import fails due to Error ORA-04030 Oracle 11gR2

===========================================================
ORA-39014,ORA-39029,ORA-31671,ORA-04030,ORA-06512,ORA-06512
===========================================================

Error:
—–
Starting “SYS”.”IMP_SCHEMA4″: /******** AS SYSDBA parfile=test.par
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”XYZ” already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name “DW00” prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-04030: out of process memory when trying to allocate 16048 bytes (session heap,kuxLpxAlloc)
ORA-06512: at “SYS.KUPW$WORKER”, line 1887
ORA-06512: at line 2

Cause:
======
This seems to be an Oracle bug. But I haven’t seen any metalink note confirming this for the PASSWORD_HISTORY module.

Workaround/Solution:
====================
If you don’t have a requirement to import such a password history to the new schema, perform impdp with:

EXCLUDE=PASSWORD_HISTORY

Posted in Oracle | Tagged , , , , , | Leave a comment

Step by Step Guide for Oracle 12c 2 Nodes RAC environment on laptop version 1.0

Short Introduction:
===================
It is my humble effort to help “me” like people to setup their own RAC environment on Laptop for practice. In second version I am planning to include 12c Dataguard and Pluggable Database.

Please don’t hesitate to give me your valuable feedback…for further improvement of this document.

2nodesoracle12c

Posted in Oracle RAC | Tagged , , , , , , | 2 Comments

Recovery of a Missing Datafile that has no backups (database is open).

Datafile Lost due to OS level delete:
=====================================
Recovery of a Missing Datafile that has no backups (database is open).
If a non system datafile that was not backed up since the last backup is missing,
recovery can be performed if all archived logs since the creation
of the missing datafile exist.
Pre requisites: All relevant archived logs.
1. alter datafile offline immediate;
2. alter database create datafile ‘fully_qualified_file_name’;
3. alter database datafile ‘fully_qualified_file_name’ online;

If the create datafile command needs to be executed to place the datafile on a
location different than the original use:
alter database create datafile ‘fully_qualified_file_name’ as ‘fully_qualified_file_name’;

Posted in Oracle | Tagged , , , | 3 Comments

CRS-2800 | Cannot start resource

set lines 190
set pages 150
select host_name,name,instance_name,database_role,status,open_mode,logins from v$instance,v$database;

HOST_NAME NAME INSTANCE_NAME DATABASE_ROLE STATUS OPEN_MODE LOGINS
—————————————————————- ——— —————- —————- ———— ——————– ———-
shanojrac1 ORCL ORCL1 PRIMARY OPEN READ WRITE ALLOWED

[grid@shanojrac1 root]$ srvctl status database -d orcl
Instance ORCL1 is not running on node shanojrac1
Instance ORCL2 is running on node shanojrac2
Instance ORCL3 is running on node shanojrac3

[oracle@shanojrac1 ~]$ srvctl start instance -i ORCL1 -d ORCL
PRCR-1013 : Failed to start resource ora.orcl.db
PRCR-1064 : Failed to start resource ora.orcl.db on node shanojrac1
CRS-2800: Cannot start resource ‘ora.OCR_DATA.dg’ as it is already in the INTERMEDIATE state on server ‘shanojrac1’
[oracle@shanojrac1 ~]$ srvctl stop instance -i ORCL1 -d ORCL
PRCC-1017 : ORCL was already stopped on shanojrac1

[oracle@shanojrac1 ~]$ crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.LISTENER.lsnr
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.OCR_DATA.dg
ONLINE INTERMEDIATE shanojrac1 CHECK TIMED OUT
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.asm
ONLINE ONLINE shanojrac1 Started
ONLINE ONLINE shanojrac2 Started
ONLINE ONLINE shanojrac3 Started
ora.gsd
OFFLINE OFFLINE shanojrac1
OFFLINE OFFLINE shanojrac2
OFFLINE OFFLINE shanojrac3
ora.net1.network
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.ons
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.registry.acfs
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE shanojrac1
ora.cvu
1 ONLINE ONLINE shanojrac1
ora.oc4j
1 ONLINE ONLINE shanojrac1
ora.orcl.db
1 ONLINE OFFLINE Instance Shutdown
2 ONLINE ONLINE shanojrac2 Open
3 ONLINE ONLINE shanojrac3 Open
ora.scan1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac2.vip
1 ONLINE ONLINE shanojrac2
ora.shanojrac3.vip
1 ONLINE ONLINE shanojrac3

=======================================
FIX:
=======================================

1) When you use the Oracle VM RAC templates with 11.2.0.2 and make use of role separation, you will not be able to build and RDBMS unless you
perform the following steps. During the creation of the database you will receive messages, Oracle not available. This is caused by the incorrect
setasmgid group used during the initiel configuration of you RAC 11.2.0.2 cluster.

Note:
—–
Please keep in mind that below work around is only applicable
when using role separation for the GRID infrastructure and uses the ORACLE RAC 11gR2 VM templates.

# cd /u01/app/11.2.0/grid/rdbms/lib
# cp -a ins_rdbms.mk ins_rdbms.mk.orig
# vi +1099 ins_rdbms.mk

Change line 1099 in ins_rdbms.mk, from this:

$(SETASMGID): $(ALWAYS) $(SETASMGID_DEPS) $(CONFIG)

to this:

$(SETASMGID): $(ALWAYS) $(SETASMGID_DEPS) config.$(OBJ_EXT)

2) Then tried to stop and start the ora.crsd to come out of INTERMEDIATE state.

[grid@shanojrac1 lib]$ crsctl stop res ora.crsd -init
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘shanojrac1’
CRS-2677: Stop of ‘ora.crsd’ on ‘shanojrac1’ succeeded
[grid@shanojrac1 lib]$ crsctl start res ora.crsd -init
CRS-2672: Attempting to start ‘ora.crsd’ on ‘shanojrac1’
CRS-2676: Start of ‘ora.crsd’ on ‘shanojrac1’ succeeded

[grid@shanojrac1 bin]$ crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.LISTENER.lsnr
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.OCR_DATA.dg
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.asm
ONLINE ONLINE shanojrac1 Started
ONLINE ONLINE shanojrac2 Started
ONLINE ONLINE shanojrac3 Started
ora.gsd
OFFLINE OFFLINE shanojrac1
OFFLINE OFFLINE shanojrac2
OFFLINE OFFLINE shanojrac3
ora.net1.network
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.ons
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.registry.acfs
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE shanojrac1
ora.cvu
1 ONLINE ONLINE shanojrac1
ora.oc4j
1 ONLINE ONLINE shanojrac1
ora.orcl.db
1 ONLINE ONLINE shanojrac1 Open
2 ONLINE ONLINE shanojrac2 Open
3 ONLINE ONLINE shanojrac3 Open
ora.scan1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac2.vip
1 ONLINE ONLINE shanojrac2
ora.shanojrac3.vip
1 ONLINE ONLINE shanojrac3

[grid@shanojrac1 bin]$ srvctl status database -d orcl
Instance ORCL1 is running on node shanojrac1
Instance ORCL2 is running on node shanojrac2
Instance ORCL3 is running on node shanojrac3

Posted in Oracle, Oracle RAC | Tagged , | Leave a comment

How to Audit User Activity in Oracle Database

Step 1.

SQL> alter system set audit_trail=db scope=spfile;
System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;

Step 2. stop Database

Step 3. start database.

Step 4. check parameter
SQL> show parameter audit_
NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /u01/db/product/10.2.0/rdbms/audit
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB

Step 5. Log in as sysdba.

SQL> audit all by apps by access;

OR
Give the audit permission for delete and drop objects.

SQL> audit DELETE ANY TABLE,DROP ANY INDEX,DROP ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,ALTER ANY PROCEDURE,ALTER ANY INDEX by apps
by access whenever successful;

Step 7. Check the operation (log in as sysdba);

SQL> SELECT username, extended_timestamp, owner,obj_name ,action_name FROM dba_audit_trail WHERE owner = ‘APPS’ and extended_timestamp=sysdate -1 ORDER BY timestamp;

SQL> SELECT count(1) FROM dba_audit_trail WHERE owner = ‘APPS’ and extended_timestamp=sysdate -1 ORDER BY timestamp;

SQL> SELECT count(1) FROM dba_audit_trail WHERE owner = ‘APPS’ and extended_timestamp=sysdate – 1

SELECT count(1) FROM dba_audit_trail WHERE owner = ‘APPS’ ORDER BY timestamp;

Step 8. Check how many no of record in audit tables;
select count(1) from sys.aud$;

Posted in Oracle | Tagged , , , | Leave a comment