Wednesday, September 8, 2021

ORA-14552: Cannot Perform a DDL Commit or Rollback inside a query or DML

You will receive this error when you use COMMIT or ROLLBACK and make sure you don’t use DML in a function to avoid this error.

In case if you need to use COMMIT or ROLLBACK then change your function as autonomous transaction by using “PRAGMA AUTONOMOUS_TRANSACTION" in your function

For Ex:-
CREATE OR REPLACE FUNCTION Function_Autonomous
return number
as
v_number number;
pragma autonomous_transaction;
BEGIN
…………
END;
/

14552, 00000, "cannot perform a DDL, commit or rollback inside a query or DML "
*Cause: DDL operations like creation tables, views etc. and transaction
control statements such as commit/rollback cannot be performed
inside a query or a DML statement.

*Action: Ensure that the offending operation is not performed or
use autonomous transactions to perform the operation within
the query/DML operation.

Thanks & Regards

Monday, July 19, 2021

Generate Table, Index or tablespace DDLs in oracle

When migrating database from one server to another you need Tablespace creation DDL. Also it is very useful when copying specific table or index or exp/imp from one database to databases.

Here are few ways to generate the tablespace script from source database.

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL>SPOOL TBS_DDL.SQL
SQL>SELECT DBMS_METADATA.GET_DDL('TABLESPACE',DBA_TABLESPACES.TABLESPACE_NAME) FROM DBA_TABLESPACES;
SQL>SPOOL OFF

You can see below command if you want the TABLESPACE creation script specific to one Table space.

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

You can use below command to generate specific TABLE, INDEX Script from a user

Syntax:-
select dbms_metadata.get_ddl('TABLE','<TABEL NAME>','<SCHEMA>') from dual;
select dbms_metadata.get_ddl('INDEX','<INDEX NAME>','<SCHEMA>') from dual;


Ex:-
select dbms_metadata.get_ddl('TABLE','EMP_SAL','EMP') from dual;
select dbms_metadata.get_ddl('INDEX','EMPNO_IDX','EMP') from dual;


You can use below command to all TABLE, INDEX Script from a user. First, connect to user

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME)
FROM USER_TABLES U;
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)
FROM USER_INDEXES U;  

Thanks & Regards
http://oracleracexpert.com






Wednesday, July 14, 2021

DBCA fails with ORA-29516: Aurora assertion failure

When creating 11g database on a new system we encounter below error…

BEGIN
*
ERROR at line 1:
ORA-29516: Aurora assertion failure: Assertion failure at joez.c:3422
Bulk load of method java/lang/Object.<init> failed; insufficient shm-object space
ORA-06512: at line 3

You should look for dbca error logs for detailed error message that you can find under /<ORACLE_BASE>/cfgtoollogs/dbca/<SID>/

We found below error from postDBCreation.log

IF CatbundleCreateDir(:catbundleLogDir) = 0 THEN
*
ERROR at line 71:
ORA-06550: line 71, column 14:
PLS-00201: identifier 'CATBUNDLECREATEDIR' must be declared
ORA-06550: line 71, column 11:
PL/SQL: Statement ignored


This error could be one of the below reasons

1. The file systems or mount point options are in correct
2. Incorrect ulimit sessions for oracle user
3. You system has JAVA_JIT_ENABLED=TRUE

In my case I have below mount options in /etc/fstab
/dev/shm tmpfs defaults,nodev,nosuid,noexec 0 0

After removing “nodev,nosuid,noexec” from /etc/fstab resolved the issue.
/dev/shm tmpfs defaults 0 0

Thanks & Regards
http://oracleracexpert.com  





Wednesday, May 26, 2021

ORA-01555: snapshot too old: rollback segment number x with name "_xxxxx" too small

When we come across “snapshot too old” error we need to look into all possibilities

1. Determine if UNDO_MANAGEMENT is MANUAL or AUTO – Make sure you are using Auto, this will take care of auto management and will help to tune

2. In the ora-01555, if you see segment number with name that means it is caused by UNDO segment, if not LOG segment due to read consistency

3. Find out which client sessions or programs causing the issue and QUERY DURATION.

The possible solution will be set UNDO_MANAGEMENT to AUTO then make sure we have set correct value for UNDO_RETENTION.

Run below SQL query to identify weather undo tablespace was too small to maintain UNDO_RETENTION

select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT "# Unexpired|Stolen", EXPSTEALCNT "# Expired|Reused",
SSOLDERRCNT "ORA-1555|Error", NOSPACEERRCNT "Out-Of-space|Error",
MAXQUERYLEN "Max Query|Length" from gv$undostat
where begin_time between
to_date(‘Start time of the query','MM/DD/YYYY HH24:MI:SS')
and
to_date('End time of the query','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time;


Find out the current retention period by querying the tuned_undoretention column of v$undostat. The database tunes the undo retention period to be longer than the long running query. The v$undostat view contains one row for each 10-minute stats collection interval over the last 4 days. The Data beyond 4 days can query the dba_hist_undostat view.

The below query will display the tuned_undoretention value in seconds:

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time,
tuned_undoretention from v$undostat
order by end_time;


Refer Oracle notes
Note 563470.1 Lob retention not changing when undo_retention is changed
Note 800386.1 ORA-1555 - UNDO_RETENTION is silently ignored if the LOB
Note 422826.1 How To Identify LOB Segment Use PCTVERSION Or RETENTION
Bug:3200789 Abstract: VISIBILITY OF LOB SEGMENT USAGE FOR UNDO


Thanks & Regards,
http://oracleracexpert.com, Oracle ACE

Monday, May 24, 2021

Oracle software cloning VERIFICATION_FAILED with ACFSUtil:PRCT-1011 : Failed to run "acfsutil". Detailed error

I recently come across below error message during the Oracle Binaries cloning for ORacle 19c  using “runInstaller”

I fond below messages during the troubleshooting 

INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isCRSConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isHAConfigured() returns value false
FINEST: [May 8, 2021 12:02:43 AM] oracle.install.library.asm.ACFSUtil:PRCT-1011 : Failed to run "acfsutil". Detailed error:
/bin/sh: /sbin//acfsutil: No such file or directory
INFO: [May 8, 2021 12:02:43 AM] Retrieving ASM Cluster File System information...
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isCRSConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isHAConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] Getting the last existing parent of: /oracle/product/19.0.0.0/dbhome_1
INFO: [May 8, 2021 12:02:43 AM] Path: /oracle/product/19.0.0.0/dbhome_1
INFO: [May 8, 2021 12:02:43 AM] Last existing parent: /oracle/product/19.0.0.0/dbhome_1
INFO: [May 8, 2021 12:02:43 AM] Executing [df, -P, /oracle/product/19.0.0.0/dbhome_1]
INFO: [May 8, 2021 12:02:43 AM] Starting Output Reader Threads for process df
INFO: [May 8, 2021 12:02:43 AM] Filesystem 1024-blocks Used Available Capacity Mounted on
INFO: [May 8, 2021 12:02:43 AM] The process df exited with code 0
INFO: [May 8, 2021 12:02:43 AM] Waiting for output processor threads to exit.
INFO: [May 8, 2021 12:02:43 AM] Output processor threads exited.
INFO: [May 8, 2021 12:02:43 AM] PATH has :==>/oracle/product/19.0.0.0/dbhome_1/lib:/oracle/product/19.0.0.0/dbhome_1/oui/lib/linux64:/oracle/product/19.0.0.0/dbhome_1/bin:/oracle/product/19.0.0.0/dbhome_1/ctx/lib:/usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib
INFO: [May 8, 2021 12:02:43 AM] PATH has :==>/oracle/product/19.0.0.0/dbhome_1/lib:/oracle/product/19.0.0.0/dbhome_1/oui/lib/linux64:/oracle/product/19.0.0.0/dbhome_1/bin:/oracle/product/19.0.0.0/dbhome_1/ctx/lib:/usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isCRSConfigured() returns value false
INFO: [May 8, 2021 12:02:43 AM] oracle.install.library.crs.CRSInfo.isHAConfigured() returns value false
FINEST: [May 8, 2021 12:02:43 AM] oracle.install.library.asm.ACFSUtil:PRCT-1011 : Failed to run "acfsutil". Detailed error:
/bin/sh: /sbin//acfsutil: No such file or directory


Verify any pre-requisites verifications failed.
$ cat installActions2021-05-08_00-02-39AM.log |grep VERIFICATION_FAILED

TaskKernelParam:OS Kernel Parameter: shmmax[CHECK_KERNEL_PARAMETER_shmmax]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [139 Milliseconds]
TaskKernelParam:OS Kernel Parameter: shmall[CHECK_KERNEL_PARAMETER_shmall]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [155 Milliseconds]
TaskKernelParam:OS Kernel Parameter: file-max[CHECK_KERNEL_PARAMETER_file-max]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [107 Milliseconds]
TaskKernelParam:OS Kernel Parameter: rmem_default[CHECK_KERNEL_PARAMETER_rmem_default]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [105 Milliseconds]
TaskKernelParam:OS Kernel Parameter: rmem_max[CHECK_KERNEL_PARAMETER_rmem_max]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [103 Milliseconds]
TaskKernelParam:OS Kernel Parameter: wmem_default[CHECK_KERNEL_PARAMETER_wmem_default]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [103 Milliseconds]
TaskKernelParam:OS Kernel Parameter: wmem_max[CHECK_KERNEL_PARAMETER_wmem_max]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [106 Milliseconds]
TaskKernelParam:OS Kernel Parameter: aio-max-nr[CHECK_KERNEL_PARAMETER_aio-max-nr]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [104 Milliseconds]
TaskPackage:Package: gcc-c++-4.8.2[CHECK_PACKAGE_EXISTENCE_gcc-c++]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [80 Milliseconds]

1. Make sure you verify that ORACLE_HOME has owned by Oracle and Group and it has proper permissions…
chown -R oracle:dba /oracle/product/19.0.0.0/dbhome_1
chmod 775 /oracle/product/19.0.0.0/dbhome_1

2. Also verify the logs under oraInventory for any VERIFICATION_FAILED error messages. Make sure you fix all Oracle pre-requisites before you run runInstaller again. This should resolve many of the issues during the cloning.

Thanks & Regards
http://oracleracexpert.com, Oracle ACE