DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, November 9, 2017

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Error:

ORA-39120: Table "OWNER"."TABLENAME" can't be truncated, data will be skipped. Failing error is:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Solution:

Please see from v$lock if the object is being used by other sessions
SQL
select obect_id1,object_id2 from dba_objects where owner='schemanname' and object_name='table_name';
select sid from v$lock where id1='id1_obtained from previous query' and id2='obtained from previous query';
select serial# from v$session where sid='sid obtained from previous query';

Kill those sessions or wait until user complete those sessions
alter system kill session 'serial#,sid';

Monday, November 6, 2017

UDI-31623: operation generated ORACLE error 31623

                               UDI-31623: operation generated ORACLE error 31623
                               ORA-31623: a job is not attached to this session via the specified handle.

ISSUE:-


We encountered one issue while running the impdp job on one of our database with the below listed error:

oracle@bossWW:~$  impdp oracle/xxxx@Tnsname schemas=HPSEBL,MIS,CRM  directory=EXP_BKP  dumpfile=dbschema.dmp logfile=impdbschema.log

Import: Release 11.2.0.4.0 - Production on Mon Oct 30 15:07:28 2017

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

Username: / as sysdba

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

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1


Solution:-

As a workaround we are modify the parameters and just restarting the database and the impdp job was running fine.

Cause:

The problem is the stream pool size. It was configured to value

SQL> show parameter streams_pool

NAME                                 TYPE             VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer      0
Solution:

Increase the value of stream_pool_size initialization parameter

oracle@bossWW:~$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 16 14:44:23 2016

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

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

SQL> alter system set streams_pool_size=256M scope=both;
alter system set streams_pool_size=256M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

SQL> show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 596M
sga_target                           big integer 0


Here we increase the SGA size and modify the streams_pool_size size.

QL> alter system set sga_max_size=700m scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2256832 bytes
Variable Size             591396928 bytes
Database Buffers           67108864 bytes
Redo Buffers               69951488 bytes
Database mounted.
Database opened.
SQL> alter system set streams_pool_size=20m scope=both;

System altered.

After increasing the stream_pool_size parameter all expdp and impdp jobs started working without issues.
Orscle datapump uses the stream_pool_size parameter, So we must ensure that this parameter is configured appropriately before running the data pump jobs.

ERROR: Invalid username and/or password LOG: Local Authentication failed...Attempt PAM authentication...PAM failed with error: Authentication failure

Issue:

Got the following error while setting preferred credentials for the host target

ERROR: Invalid username and/or password LOG: Local Authentication failed...Attempt PAM authentication...PAM failed with error: Authentication failure

Cause:
For LADP kind of authentication PAM setup should be done.

Solution:

Make sure that PAM libraries are installed 

rpm -ql pam-1.1.1-4.el6 | grep libpam.so

you should see the output like below.

/lib64/libpam.so.0        
/lib64/libpam.so.0.82.2   

If not installed, install the pam-1.1.1-4.el6 rpm and verify again.

And then  as root user, do the below.

cd /usr/lib64
ln -s /lib64/libpam.so.0.82.2 libpam.so

cd /etc/pam.d
cp sshd emagent

go to $AGENT_HOME/bin

backup commonenv and add the following line

if [ "$LD_LIBRARY_PATH" = "" ] ; then
LD_LIBRARY_PATH=/lib64
else
LD_LIBRARY_PATH=:$LD_LIBRARY_PATH
fi

Restart Agent
./emctl stop agent
./emctl start agent

Install 12.1.5 oem management agent in silent mode

Overview:

Installing a management agent in silent mode is only an alternative option to installing the agent using “Add Hosts” from console.

“Add Hosts” target wizard will be useful if you want to install the same OMS version of management agent. For example, if your OMS version is 13.2, you can install only 13.2 version agent on target hosts.  If your target host OS version is not compatible with 13.2 version agent then you must go for 12.1.5 agent, this scenario will be opt for this blog.

This post about install 12.1.5 management agent in silent mode,

High level steps:

1.       Download 12.1.0.5 agent software from OEM server.
2.       Update agent.rsp.
3.       Install the agent through agentDeploy.sh script.

Download 12.1.0.5 agent software from OEM server:

./emcli setup -url=https://omsserver.domain.com:7803/em -username="sysman" -password="pwd"
./emcli login -username=sysman
./emcli sync
./emcli get_supported_platforms
./emcli get_agentimage -destination=/installers/OEM/OEM_13c/12C_Agent -platform="Linux x86-64" -version=12.1.0.5.0

Update agent.rsp as below:

OMS_HOST= omsserver.domain.com
EM_UPLOAD_PORT=4903
AGENT_REGISTRATION_PASSWORD=pwd
AGENT_PORT=3874
b_startAgent=true

Install the agent through agentDeploy.sh script:

sh agentDeploy.sh AGENT_BASE_DIR=/u01/app/Agent12c05 RESPONSE_FILE=/installers/OEM/OEM_13c/archives/agent.rsp