DBMS_LOCK Must Be Declared In Swingbench

Today I was simulating workload with Swingbench (Version 2.5). I fan of this free tool it is a workload simulator for Oracle databases, of course, HammerDB is a worderful tool too. When executed the first workload I received lots of errors from event log into the Console. It says me that SOE.ORDERENTRY package body was invalid.

When I tryed compile it get the next error;

 
XXXXX>  alter package soe.ORDERENTRY compile body;

Advertencia: Cuerpo del paquete modificado con errores de compilaciĆ³n.

Transcurrido: 00:00:00.34
XXXXX>  show err
Errores para PACKAGE BODY SOE.ORDERENTRY:

LINE/COL ERROR
________ _________________________________________________________________
79/13    PL/SQL: Statement ignored
79/13    PLS-00201: identifier 'DBMS_LOCK' must be declared
82/13    PL/SQL: Statement ignored
82/13    PLS-00201: identifier 'DBMS_LOCK' must be declared

Of course, I usually never use the sys user to install anything, in my case I installed Swingbench with system user. The solution is so easy like

 

 
os11> sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 17 16:06:38 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>
SQL> grant execute on dbms_lock to soe;

Grant succeeded.

 

And recompile the package body again;

 
XXXXX>  alter package soe.ORDERENTRY compile body;

Cuerpo del paquete modificado.

Transcurrido: 00:00:00.42
XXXXX>  show err
No hay errores.

HTH – Antonio NAVARRO

 

Advertisements

Error ORA-00933 Creating DB Link With Special Characters

Today I get the next error executing a create database link commandd;

ORA-00933: SQL command not properly ended

The syntax is show below

create database link unodos connect to Stevy identified by Pa**word using ‘remotedb.worl.es’;

In this case, the pass have special characters (two asterisks) and Oracle thinks it is sql syntax, you need specify it like string, you can this by user quotes like in the next sample;

create database link unodos connect to Stevy identified by “Pa**word” using ‘remotedb.worl.es’;

 

HTH – Antonio NAVARRO

ORA-19511, ORA-19870, ORA-19501 And ORA-27190 Errors

Today when I arrived to the work I saw a email from Backup department about a restore failing the last Saturday. The error was the next;

 
channel aux12: ORA-27192: skgfcls: sbtclose2 returned error - failed to close file
ORA-19511: non RMAN, but media manager or vendor specific failure, error text:
   We could not read the checksum. (0:3:2)
ORA-19870: error while restoring backup piece WEBP_k3sq8u31_1_1
ORA-19501: read error on file "CLOUD_k3sf8t32_1_1", block number 1 (block size=512)
ORA-27190: skgfrd: sbtread2 returned error
ORA-19511: non RMAN, but media manager or vendor specific failure, error text:
   asdf_

After a bit of research I discovered the problem was in the network. This backup connect to serveral servers to work (Legato Server, a recover catalog and other database becouse of be a duplicate). Maybe a cut down of miliseconds order was enough to crash the restore. To verify the network stability and repeat the Rman Script solve the problem.

HTH – Antonio NAVARRO

 

ORA-31693, ORA-29913, ORA-29400, KUP-11011, KUP-11014 When Executing Impdp

Today I get the next error when perform a full import.

 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-31693: Table data object "CLOUD"."HCO_TELMEM_BRO" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11011: the following file is not valid for this load operation
KUP-11014: internal metadata in file /temporary/uploads/fullcloud33.dmp is not valid

It is a bug (20690515), by use multiples dump files, generated in 10g, maybe trying reduce their number by create them more bigger work. If you hava support you can download from ML/MOS. In other case, you can use like workaround, if your tables support it, set up ACCESS_METHOD=DIRECT_PATH.

HTH – Antonio NAVARRO

 

ORA-31603 Error When Get DDL For A Profile

Today I was getting the DDL for differents objects in a database, the problem is when I get the Profiles definition, by using the next query;

 
SELECT DBMS_METADATA.GET_DDL('PROFILE',PROFILE)
 FROM (SELECT UNIQUE PROFILE FROM DBA_PROFILES);  

ERROR:

ORA-31603: object "DEFAULT" of type PROFILE not found in schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

The problem is the default profile. It is make not sense export (import) and default profile fron one database to other. The solution is exclude the default profile like show below;

 
SELECT DBMS_METADATA.GET_DDL('PROFILE',PROFILE)
FROM (SELECT UNIQUE PROFILE FROM DBA_PROFILES)
WHERE profile != 'DEFAULT';

HTH – Antonio NAVARRO

 

 

ORA-32004 And ORA-27102 After Increase Memory

Today I was increasing the Memory_target for a database when I get the next error;

SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-27102: out of memory
SVR4 Error: 12: Not enough space
Additional information: 1671
Additional information: 16106127360
Additional information: 12851347456

The problem looks clear, Not enough space, of course, in my case (using Solaris 11.3 with ZFS) this is a well know problem, if ZFS is on default it is very easy use all physical memory;

You can see the memory consume from root user executin the next command

echo ::memstat | mdb -k

The output is below, in this case the 56% of memroy is used by ZFS

Page Summary                 Pages             Bytes  %Tot
----------------- ----------------  ----------------  ----
Kernel                     1551129             11.8G   18%
Defdump prealloc            182104              1.3G    2%
ZFS Metadata                258844              1.9G    3%
ZFS File Data              4657165             35.5G   56%
Anon                        637853              4.8G    8%
Exec and libs                97157            759.0M    1%
Page cache                   57244            447.2M    1%
OSM                         469504              3.5G    6%
Free (cachelist)             14532            113.5M    0%
Free (freelist)             530445              4.0G    6%
Total                      8388608               64G

From ZFS Doc;

zfs_arc_max

Description
Determines the maximum size of the ZFS Adaptive Replacement Cache (ARC). However, see user_reserve_hint_pct.

Default
75% of memory on systems with less than 4 GB of memory

physmem minus 1 GB on systems with greater than 4 GB of memory

Free the memory in used by ZFS and limit it solve the problem.

 

HTH – Antonio NAVARRO

 

ORA-39126, ORA-06502 And LPX-00216 Errors In impdp Full

Last day I get the next error when I was importing a database (from 10.2.0.3 to 12.1.0.2);

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [PROCACT_SYSTEM]
ORA-06502: PL/SQL: numeric or value error
LPX-00216: invalid character 0 (0x0)

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 11259

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
3e032fef0     27116  package body SYS.KUPW$WORKER
3e032fef0     11286  package body SYS.KUPW$WORKER
3e032fef0     21860  package body SYS.KUPW$WORKER
3e032fef0      4516  package body SYS.KUPW$WORKER
3e032fef0     12063  package body SYS.KUPW$WORKER
3e032fef0      2081  package body SYS.KUPW$WORKER
3dba97a40         2  anonymous block

Search in Google I find the problem is the resource manager objects and the solution exclude them in expdp time. I hadn’t time to perform other expdp, in my case works fine exclude them in impdp time.

HTH – Antonio NAVARRO