How to Fix Oracle 26ai Installer Error INS-13001 on RHEL 9

While deploying the new Oracle AI Database 26ai on Red Hat Enterprise Linux 9.x (RHEL 9.x), I have come across a confusing scenario where the installation goes perfectly smoothly on SERVER1 but throws a frustrating error on a seemingly identical SERVER2.

WARNING: [May 18, 2026 1:26:22 PM] Verification of target environment returned with errors. WARNING: [May 18, 2026 1:26:22 PM] [WARNING] [INS-13001] Oracle Database is not supported on this operating system. Installer will not perform prerequisite checks on the system.
CAUSE: This operating system may not have been in the certified list at the time of the release of this software.
ACTION: Refer to My Oracle Support portal for the latest certification information for this operating system. Proceed with the installation if the operating system has been certified after the release of this software..

Checking /etc/redhat-release and /etc/os-release on both machines confirms they are both pristine, matching copies of RHEL 9.6. So why is one failing while the other succeeds?

[oracle@SERVER1]$cat /etc/redhat-release
Red Hat Enterprise Linux release 9.6 (Plow)

[oracle@SERVER1]$cat /etc/os-release
NAME="Red Hat Enterprise Linux"
VERSION="9.6 (Plow)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="9.6"
PLATFORM_ID="platform:el9"
PRETTY_NAME="Red Hat Enterprise Linux 9.6 (Plow)"
ANSI_COLOR="0;31"
LOGO="fedora-logo-icon"
CPE_NAME="cpe:/o:redhat:enterprise_linux:9::baseos"
HOME_URL="https://www.redhat.com/"
DOCUMENTATION_URL="https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/9"
...etc

[oracle@SERVER2]$cat /etc/redhat-release
Red Hat Enterprise Linux release 9.6 (Plow)

[oracle@SERVER2]$cat /etc/os-release
NAME="Red Hat Enterprise Linux"
VERSION="9.6 (Plow)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="9.6"
PLATFORM_ID="platform:el9"
PRETTY_NAME="Red Hat Enterprise Linux 9.6 (Plow)"
ANSI_COLOR="0;31"

LOGO="fedora-logo-icon"
CPE_NAME="cpe:/o:redhat:enterprise_linux:9::baseos"
HOME_URL="https://www.redhat.com/"
DOCUMENTATION_URL="https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/9"
...etc

We also did the research and found out that REHL9.6 is fully certified.

The answer usually isn't your OS version at all, it's OS security hardening policy. Here is why this happens and how to fix it in under two minutes.

The Secret Culprit: noexec on /tmp

During the initialization phase, the Oracle Universal Installer (OUI) extracts temporary architecture and OS-validation binaries into the system's /tmp directory and attempts to execute them.

On many production-hardened Linux servers, corporate security policies dictate that the /tmp partition must be mounted with the noexec flag. When the installer's background detection scripts get blocked from running, the OUI hits a generic failure and defaults to its catch-all warning: “OS not supported.”

How to Verify the Issue

Run the following command on both servers to check the mount permissions of your temporary directory:

$ mount | grep /tmp

If the failing server outputs noexec inside the configuration brackets, you have officially found the culprit.

The Solution: Redirect Oracle's Temp Directory or mount the /tmp with "exec"

You don’t need to ask your security team to compromise server hardening rules by remounting /tmp. Instead, you can simply instruct the Oracle installer to use a directory where the oracle user naturally has execution permission, such as their own home directory.

Log into your failing server as the oracle installation user, and run these commands in the terminal before launching the installer:

# 1. Create a dedicated temp directory in the oracle home folder

$ mkdir -p /home/oracle/oratmp


# 2. Redirect the installer's environment variables

$export TMP=/home/oracle/oratmp
$export TMPDIR=/home/oracle/oratmp

# 3. Launch the installer from this same terminal session

$./runInstaller

By changing these variables, the installer bypasses /tmp entirely, successfully reads your RHEL 9 configuration from /home/oracle/oratmp, and allows the setup to proceed seamlessly.

Thanks & Regards

https://oracleracexpet.com

Resolving ORA-19502, ORA-16038 and ORA-27072 Errors in Oracle Database

We recently encountered errors below and there are several common causes.

 ORA-19502: write error on file "/oraarch/TESTDB/1_432678_12436018.dbf", block number 182272 (block size=512)
 ORA-16038: log 2 sequence# 432678 cannot be archived
 ORA-19502: write error on file "", block number (block size=)
 ORA-00312: online log 2 thread 1: '/redo2/TESTDB/TESTDB_1B.rdo'
 ORA-27072: File I/O error

The “ORA-27072: File I/O error” , can occur due to below are common reasons

• Disk issue – This error can also occur if the disk or storage is inaccessible. It might be due to hardware related issues
• File corruption- The file system where database resides might have corrupted.
• Permission issue – If the database user does not have enough permissions, you will get this error.
• Mount failures – when Filesystem not mounted properly

The “ORA-16038” error mainly occurs when archive log file cannot be archived. In this case if the database cannot be able to reuse redo log files, logs cannot switch, the database may hung.

The “ORA-19502” error mainly caused by insufficient disk space or file system full.

In our case, the issue was caused by a full archive log filesystem. 

When archive log file system got full, the redo log archiving failed triggering ORA-16038 and ORA-19502 errors. This eventually resulted ORA-27072 due to failed write attempts

Recommended steps

1. Check the archive log and db_recovery_file_dest destinations

SHOW PARAMETER log_archive_dest;
SHOW PARAMETER db_recovery_file_dest;

If using FRA:

SHOW PARAMETER db_recovery_file_dest_size;

 

2. User should use “df-h” to check the diskspace

User should Pay special attention to:

• Archive destination mount point
• FRA mount point

3. If the file system is full Increase size by extending lun or increasing FRA size.

4. Make sure user run the backup and delete old archive logs

rman target /
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-x';

Here X means number of days

5. In case FRA is full then user should increase the db_recovery_file_dest_size using below example

Check FRA usage using below query

SELECT name, space_limit/1024/1024 MB_LIMIT,
               space_used/1024/1024 MB_USED,
               space_reclaimable/1024/1024 MB_RECLAIMABLE

FROM   v$recovery_file_dest;  

ALTER SYSTEM SET db_recovery_file_dest_size = 200G;

6. Always check for alert.log to review errors and find the root cause.

Look for:

• ARCn errors
• Log switch failures
• Repeated I/O messages

To Avoid this issue in future user can take below measures:

• Monitor FRA usage regularly
• Set up alerting when disk usage exceeds 80%
• Configure proper RMAN retention policy
• Automate archive log deletion after backup
• Separate archive logs from other mount points
• Monitor log switch frequency

In our environment, the archive log filesystem became completely full.

This caused:
ORA-19502 (write failure)
ORA-16038 (cannot archive log)
ORA-27072 (I/O error)

Once disk space was cleared, archiving resumed automatically and the database returned to normal operation.

Thanks & Regards,

https://oracleracexpert.com

ORA-51801 Fix: How to Resolve Vector Dimension Mismatch in Oracle 26ai

As users adopt AI features in Oracle 26ai, I see one error appearing frequently

“ORA-51801: VECTOR dimension mismatch”

Users face this error when the dimension of the vector being inserted or queried does not match the VECTOR column definition. Pls note that Oracle 26ai requires consistency between VECTOR column definition and the embedding being inserted or used in queries.

Here are few common Embedding Dimensions

Model Type                      Typical Dimension
MiniLM                            384 or 768
BERT variants                  768
OpenAI embeddings        1536
Large transformer models  1024+

For example 1: The below table has 768 dimension and inserting 1536, it will result ORA-51801 error.

SQL> CREATE TABLE documents (
id NUMBER,
embedding VECTOR(768)
);

SQL> INSERT INTO documents VALUES (1, :embedding_1536);

ORA-51801: VECTOR dimension mismatch

If you plan to use a 1536-dimension model, you must recreate the table with VECTOR(1536), since VECTOR dimensions cannot be altered directly.

For example 2: User can get ORA-51801 error while querying as well

SELECT * FROM documents
ORDER BY VECTOR_DISTANCE(embedding, :query_vector)
FETCH FIRST 8 ROWS ONLY;

If :query_vector dimension ≠ column dimension user will receive the error.

How to avoid these errors

• Develops should define embedding model centrally and make sure they document its output dimensions.
• Validate dimensions before insert, pls find below example

              if len(embedding) != 768:
                    raise ValueError("Invalid embedding dimension")

You can prevent ORA-51801 by creating a metadata table and validating dimensions at runtime.

 
SQL> CREATE TABLE embedding_config (
model_name VARCHAR2(100),
dimension NUMBER);

If user encounters the issue, then you should check what the VECTOR column dimension is, embedding model output and queries using same model using below query

SQL> SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'DOCUMENTS';

or 

SQL> DESC DOCUMENTS;

The output shows

EMBEDDING VECTOR(768)

Always remember “Your VECTOR column dimension must exactly match your embedding model output”. Note that even single value difference will trigger ORA-51801

Thanks & Regards,

https://oracleracexpert.com

Avoiding ORA-04068 Using RESETTABLE Packages in Oracle 26ai

In Oracle prior Oracle 26ai, if a PL/SQL package is loaded the variable values are retained across multiple calls in the same session. That means the package will keep their state within a user session. However, if a package was recompiled or modified all the active sessions that has the package will receive ORA-04068 errors.

The RESETTABLE clause introduced in Oracle 26ai, it is beneficial to avoid -ORA04068 error when the existing state of the package has been discarded.

The RESETTABLE clause can be used in package or package body during the creation. But note that users cannot be able to use RESETTABLE clause in combination with the SERIALLY_REUSABLE pragma.

Syntax: - CREATE OR REPLACE PACKAGE [BODY] RESETTABLE

Using below sample package and package body for demonstration

SQL> CREATE OR REPLACE PACKAGE sales_pkg AS
g_total_sales NUMBER := 0;
PROCEDURE add_sale;
END sales_pkg;
/
Package created

SQL> CREATE OR REPLACE PACKAGE BODY sales_pkg AS
PROCEDURE add_sale IS
BEGIN
g_total_sales := g_total_sales + 100; -- adds a fixed sale amount
DBMS_OUTPUT.PUT_LINE('Total Sales = ' || g_total_sales);
END;
END sales_pkg;
/
Package body created

We will use two different sessions for demonstration

Session 1: execute the package
SQL> EXEC sales_pkg.add_sale;

Total Sales = 100

Session 2: recompile the package

SQL> ALTER PACKAGE sales_pkg COMPILE;
Package altered

Return to Session 1:

SQL> EXEC sales_pkg.add_sale;

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SALES_PKG.ADD_SALE" has been invalidated
ORA-04065: not executed, altered or dropped package "SALES_PKG.ADD_SALE"
ORA-06508: PL/SQL: could not find program unit being called: "SALES_PKG.ADD_SALE"

....

The main reason you are receiving this error because Oracle discards the package state when it is recompiled. When user run next time, it will reinitialize the package.

SQL> EXEC sales_pkg.add_sale;
Total Sales = 100

SQL> EXEC sales_pkg.add_sale;
Total Sales = 200

Now we will modify our sample code with RESETTABLE clause

SQL> CREATE OR REPLACE PACKAGE sales_pkg RESETTABLE AS
g_total_sales NUMBER := 0;
PROCEDURE add_sale;
END sales_pkg;
/
Package created

SQL> CREATE OR REPLACE PACKAGE BODY sales_pkg RESETTABLE AS
PROCEDURE add_sale IS
BEGIN
g_total_sales := g_total_sales + 100; -- adds a fixed sale amount
DBMS_OUTPUT.PUT_LINE('Total Sales = ' || g_total_sales);
END;
END sales_pkg;
/
Package body created

We will repeat the same exercise to observe how the RESETTABLE clause affects the package’s behavior.

Session 1: execute the package

SQL> EXEC sales_pkg.add_sale;
Total Sales = 100

Session 2: recompile the package

SQL> ALTER PACKAGE sales_pkg COMPILE;
Package altered

Return to Session 1: execute the package again

SQL> EXEC sales_pkg.add_sale;
Total Sales = 100

SQL> EXEC sales_pkg.add_sale;
Total Sales = 200

In the second scenario, when Oracle detects that a package’s state is no longer valid, it automatically reinitializes the package instead of raising an ORA-04068 error. By using the RESETTABLE clause, the package can safely discard its state and reinitialize without causing any errors to user session.

READ-ONLY PDB users in Oracle 23ai - Secure Multitenancy

Oracle 23ai has introduced a new feature, READ-ONLY PDB user to improve security, developer productivity and performance. This helps multi-tenant environment where data access is critical.

The READ-ONLY user cannot perform any DDL or DML activities.

Create a Read-Only PDB User: To create a Read-Only PDB use the new READ ONLY clause in the CREATE USER statement.

Connect to PDB user
SQL> ALTER SESSION SET CONTAINER = my_pdb;

Create readonly “hr_user”
SQL> CREATE USER hr_user IDENTIFIED BY passwordxxx READ ONLY;

Grant create session to hr_user
SQL> GRANT CREATE SESSION TO hr_user;

Note that the “hr_user” cannot be able to perform below tasks

• User cannot run INSERT, DELETE, UPDATE or MERGE.
• User Cannot create or modify tables, indexs, views or procedures
• User cannot change roles or privileges
• User cannot modify session-level settings

When you try any of the above user will receive “ORA-28194: Can perform read operations only " error.

Run below view to see the user is read-only or not
SQL> SELECT username, read_only from dba_users where username='HR_USER';
USERNAME      READ_ONLY
--------------------  -----------------
HR_USER           YES

SQL> Connect hr_user/paswordxxx;
Connected.

SQL> CREATE TABLE employee_test (emp_id number, emp_name varchar2(50));
*
ERROR at line 1:
ORA-28194: Can perform read operations only

SQL> DELETE FROM employee;
*
ERROR at line 1:
ORA-28194: Can perform read operations only

Note that READ-ONLY users can execute PL/SQL if it doesn’t have any DDL or DML.
The below procedure rev_salary has update statement and cannot perform the operation.

 
SQL> exec REV_SALARY;
ERROR at line 1:
ORA-28194: Can perform read operations only
ORA-06512: at "HR_USER.REV_SALARY", line 3
ORA-06512: at line 1

The READ-ONLY user can run a SELECT query without any issues.

SQL> SELECT emp_id, emp_name from employee;
EMP_ID EMP_NAME
-------------- ----------------------------
1 test_user1
2 test_user2
3 test_user3
SQL>

The Read-Only PDB Users provide a clean way to enforce non-modifiability of users at the database level. This helps with read intensive applications, as these users restricted to only SELECT and users cannot perform any DDL or DML activities.

Thanks & Regards,

https://oracleracexpert.com  

MAX_COLUMNS parameter in Oracle Database 23ai

Before Oracle 23ai, the maximum allowed columns in a table is 1000. From Oracle 23ai you can increase this value by modifying a parameter MAX_COLUMNS up to 4096 if you have any use case. This initialization parameter can be set at system level only and in case of PDB you can limit to specific PDB. 

To use this MAX_COLUMN parameter the compatibility should be set to 23.0.0.0 or higher. To increase max allowed colums you must set the MAX_COLUMNS value to “EXTENDED”. Note that you can change MAX_COLUMNS from STANDARD to EXTENDED any time but to change the value back to STANDARD only when any table or view in the database contains 1000 or fewer columns. 

By default, the MAX_COLUMNS initialization parameter is set to STANDARD

SQL>show parameters max_columns
NAME                                TYPE      VALUE
-------------------------------- ----------- -------------------------
max_columns                      string       STANDARD

When user trying to add columns more than 1000 in a table will receive below error

ORA-01792: maximum number of columns in a table or view is 1000

You can change MAX_COLUMNS value using below command that will allow up to 4096 columns

SQL> ALTER SYSTEM set MAX_COLUMNS=EXTENDED scope=spfile;
SQL> shutdown immediate;
SQL> startup

If the database has tables with more than 1000 columns and trying to update the MAX_COLUMNS parameter value back to STANDARD, then user should receive below error

SQL> ALTER SYSTEM SET set MAX_COLUMNS =STANDARD scope=spfile;

ORA-32017: failure in updating SPFILE
ORA-60471: max_columns cannot be set to STANDARD as there are one or more objects with more than 1000 columns

The only way user can change this value by dropping the objects with more than 1000 columns.

Note that older Oracle client versions (before Oracle 23ai) do not support columns more than 1000 in a table and only Oracle 23ai clients support the 4096 column limit.

Thanks & Regards,

https://oracleracexpert.com

ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^212","kglseshtTable")

We recently encountered the following error in 19c, which typically occurs when the database needs additional shared memory. In most of the cases setting MAX_SGA_SIZE to a higher value will resolve the issue.

Below are some possible cause

•  Insufficient memory allocated via initialization parameters
•  Fragmentation in app design
•  Auto tuning issues
•  A Bug causing the issue
•  Memory leaks

ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^334","kglseshtTable")
< ORA-00604: error occurred at recursive SQL level 1 < ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","unknown object","KGLH0^f185eace","kglHeapInitialize:temp")
< ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^394","kglseshtTable")


The error message will provide the amount of memory unavailable, memory pool facing the issue and failed allocation details

 

I would highly suggest running below query to get the optimal value for SGA_TARGET
Select * from V$SGA_TARGET_ADVICE

Note that in order to initialization parameters to take into effect we need to bounce the instance.

AGLT>oerr ora 04031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.
// parameter MAX_SGA_SIZE.

Refer below links for Oracle support notes

• This Oracle support note provides information about ORA-04031 related bugs and which release they were fixed

OERR: ORA-4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" (Doc ID 4031.1)

• This Oracle note provides detailed troubleshooting and diagnosing details

Troubleshooting and Diagnosing ORA-4031 Error [Video] (Doc ID 396940.1)

• This Oracle note provides detailed understanding and tuning of the of the shared pool

NOTE:62143.1 - Troubleshooting: Understanding and Tuning the Shared Pool

Thanks & Regards,

https://oracleracexpert.com

Opatch failed with "Make failed to invoke "/usr/bin/make -f ins_net_client.mk client_sharedlib"

I encountered a patch failure while upgrading to Oracle 19.23. I received "Opatch session completed with warnings".

The error clearly indicates that the root cause is a missing "shrept.lst" file.

Make failed to invoke "/usr/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst

make: *** [ins_net_client.mk:143: client_sharedlib] Error 1
'
Make failed to invoke "/usr/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst
make: *** [ins_rdbms.mk:56: client_sharedlib] Error 1
'
Make failed to invoke "/usr/bin/make -f ins_ldap.mk ldapsearch ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst

make: *** [/oracle/product/19.x.0.0/dbhome_1/ldap/lib/env_ldap.mk:2474: /oracle/product/19.x.0.0/dbhome_1/lib/libclntsh.so] Error 1
'
The following make actions have failed :

Re-link fails on target "client_sharedlib".
Re-link fails on target "client_sharedlib".
Re-link fails on target "ldapsearch".

Do you want to proceed? [y|n]
y
User Responded with: Y
Patch 36233263 successfully applied.
Sub-set patch [34765931] has become inactive due to the application of a super-set patch [36233263].
Please refer to Doc ID 2161861.1 for any possible further required actions.
OPatch Session completed with warnings.
Log file location: /oracle/product/19.x.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-08-24_22-25-17PM_1.log

I have reviewed the opatch log file to verify the error and I can see the the same error.

[Aug 24, 2024 11:32:40 PM] [INFO] Deleted the file "/oracle/product/19.x.0.0/dbhome_1/.patch_storage/unzip_action/1723357551241/inventory/Templates/perl/bin/zipdetails"

[Aug 24, 2024 11:32:40 PM] [INFO] --------------------------------------------------------------------------------
[Aug 24, 2024 11:32:40 PM] [INFO] The following warnings have occurred during OPatch execution:
[Aug 24, 2024 11:32:40 PM] [INFO] 1) OUI-67200:Make failed to invoke "/usr/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst
make: *** [ins_net_client.mk:143: client_sharedlib] Error 1
'
[Aug 24, 2024 11:32:40 PM] [INFO] 4) OUI-67124:Re-link fails on target "client_sharedlib".
Re-link fails on target "client_sharedlib".
Re-link fails on target "ldapsearch".
[Aug 24, 2024 11:32:40 PM] [INFO] --------------------------------------------------------------------------------
[Aug 24, 2024 11:32:40 PM] [SEVERE] OUI-67008:OPatch Session completed with warnings.
[Aug 24, 2024 11:32:40 PM] [INFO] Finishing UtilSession at Sat Aug 24 23:32:40 PDT 2024
[Aug 24, 2024 11:32:40 PM] [INFO] Log file location: /oracle/product/19.x.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-08-24_22-25-44PM_1.log

we found that shrept.lst file was missing and have rollback the patch using
$ opatch rollback -id <xxxxxx>

We copied the shrept.lst file from another environment, re-applied the patch using "opatch apply," and the process completed successfully without any issues.

Thanks & Regards

https://oracleracexpert.com

ORA-02304: invalid object identifier literal and ORA-39083 errors during impdp

Users might receive below error during the import.

ORA-39083: Object type TYPE:"ORCL"."OID_TAB1" failed to create with error:
ORA-02304: invalid object identifier literal

Failing sql is:
CREATE EDITIONABLE TYPE "ORCL"."OID_TAB1" OID 'FB3CF41KK327B011D053F114ABBC878C' AS OBJECT (
className1 VARCHAR2(200),
id1 NUMBER)

Run below query in the database and check for TPYE_ODI exists or not

 
SQL> select OWNER, TYPE_NAME from DBA_TYPES where TYPE_OID='FB3CF41KK327B011D053F114ABBC878C ';

OWNER TYPE_OID
------------------------------------------ --------------------------------
ORCL      FB3CF41KK327B011D053F114ABBC878C

In most cases you will see the ODI is already exists in the database. The ODI already exists, and you cannot create ODI with same name that’s why it failed. You need to follow one of the options.

1. Create the object with new ODI – You can remove the ODI clause from the statement and run again. The database will generate new ODI for the object.

2. Drop the existing TYPE_ODI and reimport - DROP the object and reimport so that object will create with same ODI

3. In “impdp” use the parameter transform=oid:n - Using the parameter during the impdp will import TYPES with new ODI.

I have used all 3 methods based upon different scenarios.

Thanks & Regards,

http://oracleracexpert.com

WARNING: inbound connection timed out (ORA-3136)

The ORA-3136 will be written into alert.log when users fails to provide credentials and cannot authenticate within the set timeout value.

The default value set for below parameters in sqlnet.ora is 60 seconds.

SQLNET.INBOUND_CONNECT_TIMEOUT
INBOUND_CONNECT_TIMEOUT_listener_name

You will see below error in the alert.log file

Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 19.0.0.0.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Version 19.5.0.0.0
Time: 14-OCT-2023 18:06:02
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.xxx.xx)(PORT=51290))
2023-10-12T16:06:02.222415-07:00
WARNING: inbound connection timed out (ORA-3136)
2023-10-12T16:15:18.866821-07:00

In the above log you can see the client IP and port, you can able to check corresponding entry in the listener log as well. Also, you can see the time stamp of user initiated the connection and time stamp when user got error, it will be more than 60 sec.

The main reason for the error is …

1. When user or application trying to connect using wrong credentials or no attend made under 1 min threshold default value of the instance.

You can able to reproduce the issue by entering wrong credentials

 
SQL> sqlplus HR/xxxx@orcl
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 13 19:31:30 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

$ tail -f orcl_alert.log
Fri Oct 13 19:32:31 2023
WARNING: inbound connection timed out (ORA-3136)

2. If the database has some performance issues or any network delay can cause long time and cannot authenticate user within default time out i.e. 60 seconds

If the database has some load causing the slowness, then increasing SQLNET.INBOUND_CONNECT_TIMEOUT to higher value will help to reduce the errors.

No DB restart required when you make the change in sqlnet.ora but note that it will be applicable to next server process.

3. The server receives the request but cannot be able to authenticate with in default time out i.e. 60 seconds

Find out what causing the delay in authentication if required increase the time out value.

If you are receiving error frequently you can enable tracing using below command
SQL> alter system set events '3136 trace name errorstack level 3';

 
You can trun off tracing using
SQL> alter system set events '3136 trace name context off';

Note that tracing will be generated under USER_DUMP_DEST or BACKGROUND_DUMP_DEST

If you still seeing these warnings I would suggest to raise a ticket with oracle support

Thanks,
https://oracleracexpert.com

SELECT without FROM Clause in Oracle 23c

Oracle 23c has many new features and “SELECT without FROM” is one of the features. By using this feature, you can run queries without using FROM clause and specifying table name for testing expressions to get the results which can be easy of use for developers.

You will no longer receive error “ORA-00923: FROM keyword not found where expected” when running expressions to get results in Oracle 23c. 

Here are few examples

Example 1: Run mathematical operations with or without using FROM clause and you will get the result

SQL> select 2+3 from dual;
SQL> select 2+3 ;

Example 2: Select current date with and without using FROM clause and you will get the result.

SQL> Select current_date from dual;
SQL> Select current_date;

Example 3: Select NEXTVAL with and without using FROM clause and you will get the result

SQL> Create sequence empno_seq;

 
SQL> select empno_seq.nextval from dual;
SQL> select empno_seq.nextval ;

Example 4: Pl/SQL block with and without using FROM clause and you will get the result
delcare
v1 number;
begin
select empno_seq.nextval into v1 from dual;
dbms_output.put_line ('v1= '||v1);
end;
/

delcare
v1 number;
begin
select empno_seq.nextval into v1;
dbms_output.put_line ('v1= '||v1);
end;
/

Many other databases such as MS SQL Server, MYSQL support without FROM clause, this will help improve SQL Code portability.

Thanks

https://oracleracexpert.com

Oracle 19c import issues ORA-31693, ORA-02354, ORA-39002, ORA-39405

When performing import come across the following issue

ORA-31693: Table data object "ORCL"."ITEMCG" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39840: A data load operation has detected data stream format error .
ORA-39844: Bad stream format detected: [klaprs_62] [139751105749101] [139751105749012] [4] [2] [2065583] [] []

User may encounter this issue when new column added to a table with cokumn optimization enabled and the same column was modified.

Below are the workarounds

• Use access_method=EXTERNAL_TABLE during export

• Prior export add and drop a dummy column to the problematic table using

SQL> ALTER TABLE <table_name> ADD dummy number;
SQL> ALTER TABLE <table_name> DROP dummy number;

• Take export of failed table and import using CONTENT=DATA_ONLY as tableau structure already imported.

The fix for this bug was initially available on 19.13 and above see if it helps.

Also, I come across below issues when importing data

ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 33 into a target database with TSTZ version 32.

User may encounter this issue when export from higher time zone version and importing into lower time zone version.

• Patch the target database to higher or equal to source time zone patch or DST TZ version
• Create a database with same time zone and perform export/import

For latest DST patches refer,

• Oracle RDBMS and OJVM DST-related notes
• Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)

Hope this helps,

Thanks & Regards

https://oracleracexpert.com

Errors ORA-12154, ORA-29003 when connecting to Autonomous Data Warehouse using Django

When connection to Autonomous Data Warehouse Using Django web framework first we come across “ORA-12154” error. That means Django cannot be able to connect to the Oracle Database service name with the settings in settings.py file.

File "C:\django\db\backends\oracle\base.py", line 254, in get_new_connection return Database.connect( cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve the connect identifier specified

First copy the tnsnames.ora into local system and test tnsping.

C:\> tnsping <service Name>

If the tnsping is working fine, then below are the passible reason for ORA-12154 error

• TNS_ADMIN might not be configured or set the right path
• There might be a typo in service name
• Connection details in settings.py might not be correct

We found that TNS_ADMIN is not set to the right path and updating the correct path resolved the issue. But we have received new error

ORA-29003: SSL transport detected mismatched server certificate

Django supports Oracle Database 19c or higher versions but cx_oracle python driver required. We verified that required driver is installed. We try to connect using SQL*PLUS but still receiving same error. That means there is no issue with the settings.

After research we found that 12c client is not supported for new mTLS authentication as per document “ALERT: Action Required for Autonomous Databases (Doc ID 2911553.1)”

We have installed Oracle 19c client and were able to connect without any issues.

Below are the options to resolve the issue

1. Install certified oracle client versions 11.2.0.4.220719 (or later), 18.19 (or later), 19.2 (or later), 21 (base release or later)

2. Update Autonomous database instance to allow both TLS and mTLS authentication.

• Goto Autonomous Database Details page --> Network --> click Edit in the Mutual TLS (mTLS) Authentication field.
• Change the value to allow TLS authentication, deselect Require mutual TLS (mTLS) authentication
• Click Update

3. Use ssl_server_dn_match=no in the connect string (when using 12c client updating the value to “no” didn’t help )

I hope this helps.

Thanks
Satishbabu G, Oracle ACE Pro

https://oracleracexpert.com

Create, Drop, Alter Blockchain tables in Oracle 21c

Blockchain tables are designed to allow insert operations only, updates and any modifications are not allowed, and delete operations are restricted. The rows are organized into chains by storing previous row’s hash values in the current row and chain of rows is verifiable by all participants. The blockchain tables concept introduced in Oracle 21c and can be backported to 19c using a patch 32431413, but COMPATIBLE parameter must be set to 19.10.0 or late

Create and Drop Blockchain table

When creating block chain you can specify retention period for Blockchain table using “NO DROP” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period and to specify retention period for rows use “NO DELETE” Clause .

Ex:- In below example creates Blockchain_T1 table the table can be dropped until 10 days and rows can be deleted until 15 days that they were inserted.

SQL> CREATE BLOCKCHAIN TABLE Blockchain_T1 (Col1 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE UNTIL 15 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";

Table level clauses

• NO DROP – The table cannot be dropped. 

• NO DROP UNTIL x DAYS IDLE – Table cannot be dropped when the table is IDLE and no new rows created for specified X number of days or retention period

Where X is the number of days.

Row level clauses

• NO DELETE or NO DELETE LOCKED – The Rows cannot be deleted.

• NO DELETE UNTIL x DAYS AFTER INSERT – the rows cannot be deleted until x number of days they were inserted, the retention setting can be changed using ALTER TABLE command.

• NO DELETE UNTIL x DAYS AFTER INSERT LOCKED – the rows cannot be deleted until x number of days they were inserted , also retention setting cannot be changed until x number of days

Create partition on Blockchain table

In below example creates Blockchain_T1 table with partitions. The table cannot be dropped until 10 days and rows cannot be deleted until 15 days that they were inserted.

SQL> CREATE BLOCKCHAIN TABLE Blockchain_T1 (Col1 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE UNTIL 15 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1"
PARTITION BY RANGE(Col3)
(PARTITION p1 VALUES LESS THAN (TO_DATE('01-31-2022','mm-dd-yyyy')),
PARTITION p2 VALUES LESS THAN (TO_DATE('02-28-2022','mm-dd-yyyy')),
PARTITION p3 VALUES LESS THAN (TO_DATE('03-31-2022','mm-dd-yyyy'))
);

You can query USER_TAB_COLS for Blockchain Table details

SQL> SELECT internal_column_id as colid
column_name
data_type,
data_length,
FROM user_tab_cols
WHERE table_name = 'BLOCKCHAIN_T1'
ORDER BY colid;

COLID COLUMN_NAME DATA_TYPE DATA_LENGTH
---------- ------------------------ ------------------------------ -----------

1 Col1 NUMBER 22
2 Col2_ VARCHAR2(48) 48
3 Col3T DATE 7
4 ORABCTAB_INST_ID$ NUMBER 22
5 ORABCTAB_CHAIN_ID$ NUMBER 22
6 ORABCTAB_SEQ_NUM$ NUMBER 22
7 ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE 13
8 ORABCTAB_USER_NUMBER$ NUMBER 22
9 ORABCTAB_HASH$ RAW 2000
10 ORABCTAB_SIGNATURE$ RAW 2000
11 ORABCTAB_SIGNATURE_ALG$ NUMBER 22
12 ORABCTAB_SIGNATURE_CERT$ RAW 16
13 ORABCTAB_SPARE$ RAW 2000

13 rows selected.

You can query {CDB|DBA|ALL|USER}_BLOCKCHAIN_TABLES views to get information about Blockchain Tables

DROP - The below example drops the table if the table has not modified for retention period defined in the Blockchain creation.

SQL> DROP TABLE Blockchain_T1 PURGE;

It is recommended to use PURGE option when dropping a Blockchain table.

 
Note that Blockchain tables cannot be create the root container and application root container.

 In below example Blockchain_T2 table creation failed as it cannot be created in root container

SQL> CREATE BLOCKCHAIN TABLE Blockchain_T2 (Col4 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE LOCKED
HASHING USING "SHA2_512" VERSION "v1";

Error report -
ORA-05729: blockchain table cannot be created in root container

ALTER Blockchain Tables : The Blockchain table retention can be modified using ALTER TABLE command

In below example we increased retention for Blockchain_T1 table that it cannot be dropped until table IDLE and no new rows created for 21 days.

SQL> ALTER TABLE Blockchain_T1 NO DROP UNTIL 21 DAYS IDLE;

In below example trying to lower retention for Blockchain_T1 table to 16 and the operation failed as retention value cannot be lowered.

SQL> ALTER TABLE Blockchain_T1 NO DROP UNTIL 16 DAYS IDLE;

Error report -
ORA-05732: retention value cannot be lowered

You can also increase column length but cannot add or drop column in Blockchain tables.
SQL> ALTER TABLE Blockchain_T1 MODIFY (COL2 VARCHAR2(58));

Table BLOCKCHAIN_T1 altered.

ADD column 

SQL> ALTER TABLE Blockchain_T1 ADD (Col4 varchar2(32));

Error report -
ORA-05715: operation not allowed on the blockchain table

DROP column 
SQL> ALTER TABLE Blockchain_T1 DROP column Col2;

Error report -
ORA-05715: operation not allowed on the blockchain table

DDL and DML on Block chain

In below example we are trying to DELETE, TRUNCATE, UPDATE and MOVE the operation not allowed

DELETE Table

SQL> DELETE FROM Blockchain_T1 where Col1 = 1;

Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

TRUNCATE Table

SQL> TRUNCATE TABLE Blockchain_T1;

Error report -
ORA-05715: operation not allowed on the blockchain table

UPDATE Table

SQL> UPDATE Blockchain_T1 SET Col2=“Test” WHERE id = 1;

Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table

DROP TABLE

SQL> DROP TABLE Blockchain_T1;

Error report -
ORA-05723: drop blockchain table BLOCKCHAIN_T1 not allowed

MOVE Table
SQL> ALTER TABLE Blockchain_T1 move tablespace Blockchain_TBS2 ;

Error report -
ORA-05715: operation not allowed on the blockchain table

Pls refer  Restrictions for Blockchain tables for more details.

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

Physical Standby Swithover_status as UNRESOLVABLE GAP

On Data Guard site, I can see that Archive logs are copying but not applying to Physical Standby Database. When I query I see that SWITCHOVER_STATUS showing as “UNRESOLVABLE GAP”

SQL> select OPEN_MODE,LOG_MODE,DATABASE_ROLE, switchover_status from v$database;

OPEN_MODE LOG_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ------------------------------ ---------------------------------------------
MOUNTED ARCHIVELOG PHYSICAL STANDBY UNRESOLVABLE GAP

I didn’t see any errors when we query v$archive_Dest_status;
SQL> select DEST_NAME, ERROR from v$archive_Dest_status;

But when we query V$ARCHIVE_GAP we can see archive log Gap

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 47402 47409

I don’t see missing archive logs in the archive log destination, but when I query v$managed_standby I can see that MRP0 processes waiting for archive log sequence 47402

SQL> select PROCESS,STATUS,THREAD#,SEQUENCE# from v$managed_standby where PROCESS='MRP0';
PROCESS STATUS THREAD# SEQUENCE#
------------------------------------ ------------------------------------------------ ---------- ----------
MRP0 WAIT_FOR_GAP 1 47402

If the archive logs on the Physical Standby site removed by mistake then you can restore using RMAN
RMAN> restore archivelog from logseq 47402 until logseq 47409;

If you want to restore archive logs into different destination other than default use below command.
RMAN> set archivelog destination to '/tmp/archive_restore';

In case, if you need to restore specific archive log then use below command
RMAN> restore archivelog from logseq=47402;

Once the archive logs are restored, it will apply to standby site. In case if you have restored to non-default destination then you need to copy the archive logs into default destination.

If the archive logs are not applying on Physical Standby site then shut down and open the Physical Standby in recovery mode again. In case if the archive logs are missing and cannot able to restore from backup then you might get below error message. 

SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 2.0737E+10 bytes
Fixed Size 9923356 bytes
Variable Size 8680473632 bytes
Database Buffers 2040487392 bytes
Redo Buffers 6859032 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Note that with missing archive logs you cannot able to recover the database.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

PGA Memory are not eligible to receive ORA-4036 interrupts

Users may receive ORA-04036 errors when PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA and you will see the errors written in the trace files as well.

ARC1 (PID:42467): Archived Log entry 10265 added for T-1.S-10440 ID 0xb264618a LAD:1
2022-05-15T02:07:49.670543-07:00
PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA
memory are not eligible to receive ORA-4036 interrupts. Further occurrences
of this condition will be written to the trace file of the DBRM process.

When you encounter this issue, the sessions consuming the PGA will be terminated until the bottleneck is cleared . Note that Oracle can exceed the amount of RAM without PGA_AGGREGATE_LIMIT which may lead to RAM buffer paging and RAC node eviction errors

The V$PGA_TARGET_ADVICE will help to predict how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view

SQL> select pga_target_for_estimate, pga_target_factor, estd_time  from v$pga_target_advice;

The below query can help to get PGA Target advice by querying v$pga_target_advice_histogram

SQL> SELECT LOW_OPTIMAL_SIZE/1024 "LOW VALUE IN KB", (HIGH_OPTIMAL_SIZE+1)/1024 "HIGH VALUE IN KB", ESTD_OPTIMAL_EXECUTIONS "OPTIMAL VALUE IN KB ", ESTD_ONEPASS_EXECUTIONS "ONE PASS EXECUTION", ESTD_MULTIPASSES_EXECUTIONS "MULTI-PASS EXECUTION "

FROM  V$PGA_TARGET_ADVICE_HISTOGRAM

WHERE PGA_TARGET_FACTOR = 2 AND ESTD_TOTAL_EXECUTIONS != 0

ORDER BY 1;

By default PGA_AGGREGATE_LIMIT is set to 2GB, when you receive the errors, I would suggest to double the value or set the appropriate value required for your environment.

You can run below command to get PGA_AGGREGATE_LIMIT and increase the value

SQL> show parameter PGA_AGGREGATE_LIMIT
SQL> alter system set PGA_AGGREGATE_LIMIT=<xGB> scope=spfile;

Sometimes users may receive below errors when PGA_AGGREGATE_LIMIT set to zero. Make sure you set the non-zero and appropriate value.

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:
REP-300: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Users may experience "Database Crash" in Oracle 19c versions when USING DBMS_STATS.GATHER_TABLE_STATS . This is due to a Oracle product defect Bug:30846782 which is fixed in 21.1.

As a workaround you may try to reduce the memory usage, set hidden parameter "_fix_control"='20424684:OFF'.

At session level:
alter session set "_fix_control"='20424684:OFF';
At Instance level:
alter system set "_fix_control"='20424684:OFF';

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE.

Oracle Data pump import stuck Processing object type DATABASE_EXPORT/SCHEMA

You might come across issues when importing data using DataPump. I have faced the issue several times while importing Domain Index and other objects. You can encounter DOMAIN INDEX issue  related to CTXSYS schema. Note that data import may complete quickly but import gets stuck when creating DOMAIN INDEXES or INDEX and it may still run even after few days. 

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/CODE_BASE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX

Pls note that domain or normal index rebuild may take time based upon the index size during the import and also other reasons may effect the operation. Here are few reasons that you need to look …

1. Did you gather Stats before the import – If yes, exclude STATS and manually gather stats after import completed.

2. Make sure all tablespaces have enough space – Sometimes imports get stuck due to not having enough space

3. Make sure you have enough STREAMS_POOL_SIZE - The value should be at least 512M or more

If you are still facing the issue then you need to run below commands to identify the SQL Statement A Data Pump Process Is Executing

a) Find out the the datapump import job running or not using below SQL

SQL> select owner_name, job_name, operation, job_mode, from dba_datapump_jobs where state='EXECUTING' ;

You can run below command to identify the session used by datapump job.

SQL> select owner_name, job_name, session_type from dba_datapump_sessions;

b)  If the job is still running on step 4 then Identify The Current SQL Statement A Data Pump Process Is Executing (refer Oracle support Doc ID 1528301.1) and identify the object

After you have tried all the options if the index creating is still taking time then the last option to EXCLUDE the object taking time and manually create after import operation is successful…

You can get the object DDL command using below query

SQL> select dbms_metadata.get_ddl('INDEX','<Index_Name>','<Schema_Name>') from dual;

or 

SQL> select dbms_metadata.get_ddl('TABLE','<Table_Name>','<Schema_Name') from dual;

You can exclude the index by adding below clause in the import command…

Exclude= INDEX:"LIKE ‘Index_name _that got stuck_%'"

After import is successful you can create the object manually in my case it is index…

To monitor Data Pump jobs query views DBA_DATAPUMP_JOBS AND DBA_DATAPUMP_SESSIONS. You can also query V$SESSION_LONGOPS to see the progress of data pump job.

The below script very useful to identify database role, version, registry status, patch level and Invalid objects. I would highly suggest to run this script for any maintenance activity you perform on a Database.

SET PAGESIZE 2000
SET LINESIZE 500
COL OBJECT_NAME FORMAT A30
COL OBJECT_TYPE FORMAT A30
COL COMP_ID FORMAT A10

COL COMP_NAME FORMAT A45  
COL OWNER FORMAT A15
COL STATUS FORMAT A10

COL VERSION FORMAT A10  
/* Database Role and Version */
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
select * from V$version where banner like 'Oracle Database%';
/* Database Component Registry status */
select comp_id, comp_name, status, version from dba_registry;
/* Database patch Level*/
select * from dba_registry_history;
/* INVALID objects in the DB count, by type & in detail */
select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';
select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;
select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;

Hope this helps

Regards,

http://oracleracexpert.com, Oracle ACE

Unregister a Database from RMAN Recovery Catalog

Use UNREGISTER command to remove the metadata from RMAN recovery catalog. You can unregister one or more databases and but note that physical backups are not deleted by this command. You need to use DELETE BACKUP/ DELETE EXPIRED BACKUP commands to remove physical backups

Before you unregister, make sure you connect to recovery catalog and verify the backups. If the backups no longer needed then you can proceed

The below command provides the summary of all backups taken for a Database using RMAN

RMAN> LIST BACKUP;
RMAN> LIST BACKUP SUMMARY;

You can specify EXPIRED keyword to identify backups not found during a crosscheck
SQL> LIST EXPIRED BACKUP;

You can use below command to delete expired backups
SQL> DELETE EXPIRED BACKUP;

You can use below command to delete all the existing backups going to DEVICE TYPE DISK
RMAN> DELETE BACKUP DEVICE TYPE DISK;

Method 1: In this method, we are connecting to target database and recovery catalog database to unregister. On a primary/standby databases scenario, it removes all metadata associated to primary as well standby.

$ rman

 
RMAN>connect target /
Connected to target database: TEST (DBID=87658657577)

RMAN> CONNECT CATALOG rcat/xxxxxx@RMANDB
connected to recovery catalog database

RMAN> UNREGISTER DATABASE NOPROMPT;
database name is "TEST" and DBID is 87658657577
database unregistered from the recovery catalog

or

RMAN> UNREGISTER DATABASE;
Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

When using NOPROMPT, it doesn't ask for confirmation before UNREGISTER.

Method 2: In this method, we are connecting only recovery catalog database to unregister.

RMAN> CONNECT CATALOG rcat/xxxxxx@RMANDB
connected to recovery catalog database

RMAN> UNREGISTER DATABASE TEST NOPROMPT;

In case if there is more than one DB in the recovery catalog database then you need to use DBID to unregister.

If you want to unregister backups associated with Standby only then you can use this method to unregister Standby database by setting DBID. In this case, the backups are still usable by other primary or standby databases

RMAN> SET DBID 87658657577;
executing command: SET DBID
database name is "TEST" and DBID is 87658657577

RMAN> UNREGISTER DATABASE TEST NOPROMPT;
database name is "TEST" and DBID is 87658657577
database unregistered from the recovery catalog

Method 3: In this method, we are unresigering the database dbms_rcvcat Package. When using this package you need to provide DB_KEY and DBID.

You can get the DBID, DB_KEY from RC_DATEABASE
SQL> SELECT db_key, dbid FROM rc_database WHERE name = ‘TEST’;

SQL> EXECUTE dbms_rcvcat.unregisterdatabase(3422 , 87658657577);
PL/SQL procedure successfully completed.

This UNREGISTER command cannot be used when target database configured with ZERO data loss recovery appliance. You can use DBMS_RA.DELETE_DB procedure to unregister a database from Recovery Appliance.

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE

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

http://oracleracexpert.com, Oracle ACE

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