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

Article: Mastering Data History using Oracle's Flashback Data Archive Feature

The Historical data is key for business decisions and having reliable data is very important for organizations. There are frequent implications for both financial and legal data for organizations and having a secure and accurate history of change of data is crucial for business.

Oracle Flashback technology offers significant benefits for database management and recovery. 

Please see the below article how it will help to meet the organizational needs.

Mastering Data History : A Guide using Oracle's Flashback Data Archive Feature
https://www.linkedin.com/pulse/mastering-data-history-guide-using-oracles-flashback-archive?trk=public_profile_article_view

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

Flashback Data Archive enhancements in Oracle 12c

Flashback feature uses Automatic Undo Management for historical and metadata transactions. Flashback Data archive (FDA) feature introduced in Oracle 11g for undo-based flashback operations, and it is configured using retention time. Flashback data archive supported for multitenant (12.1.0.2 and above versions) as well using local or shared undo configuration.

Oracle 12c (12.1.0.1) has below enhancements in FDA

• Data Hardening
• User context tracking

Data Hardening – This feature helps to associate set of tables for a specific application, so that you can enable Flashback Data archive for all those tables in a single command. Use DMBS_FLASHBACK_ARCHIVE.REGISTER_APPLICATION to register an application

You can create new application using REGISTER_APPLICATION

SQL> Begin
DBMS_FLASHBACK_ARCHIVE.register_application(
application_name => 'ORACLERACEXPERT',
flashback_archive_name => 'FDA1');
end;
/

You can add tables to the application using ADD_TABLE_TO_APPLICATION procedure

SQL> Begin
DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (

application_name=> 'ORACLERACEXPERT', 

table_name=> 'EMP' , 

schema_name -> 'USER1');
end;
/

SQL> Begin
DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (

application_name=> 'ORACLERACEXPERT', 

table_name=> 'DEPT' , 

schema_name -> 'USER1');
end;
/

You can remove the tables using REMOVE_TABLE_FROM_APPLICATION procedure from

The application will not enable automatically, use ENABLE_APPLICATION procedure to enable Flashback Data Archive for all tables in the specified application.

SQL> Begin
DBMS_FLASHBACK_ARCHIVE.enable_application(

application_name => 'ORACLERACEXPERT');
end;
/

To disable the application use disable_application procedure

SQL> Begin
DBMS_FLASHBACK_ARCHIVE.disable_application(
application_name => 'ORACLERACEXPERT');
end;
/

User context tracking - By enabling this feature it is easy to track which user made what changes to the table.

Use DMBS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL procedure to Set the user content level and procedure DMBS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT procedure To Access the context

There are 3 options depending upon how much user context needs to save
ALL – The entire SYS_CONTEXT is stored
TYPICAL – The user context is stored
NONE- Nothing

For ex: - To set context level to ALL
SQL> DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL ( level=>‘ALL’);

You can get the XID from the archive table

SQL> select XID from SYS_FBA_HIST_93222;
XID
----------------
05000A0B7040000

Now with XID you can get the context information using dbms_flashback_archive.get_sys_context procedure


SQL> begin
dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'SESSION_USER'));
dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'HOST'));
dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'MODULE'));
end;
/
USER1
SRVHOST
SQL*Plus

You can get all Transactions ID using below query

SQL> select empno, empname, VERSIONS_XID
from EMP order by empno;

EMPNO EMPNAME VERSIONS_XID
----------- --------------- ---------------------
1              ORARAC    05000A0B7040000

You can use SYS_FBA_CONTEXT_AUD to get context information for each transaction.

There are many Flashback data archive view available and to get the list of all views run below query

SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 600
SQL> COLUMN owner FORMAT A10
SQL> COLUMN table_name FORMAT A25

SQL> SELECT owner, table_name FROM dba_tables WHERE table_name LIKE '%FBA%';

 
OWNER       TABLE_NAME
--------------  -------------------------
SYS              SYS_FBA_FA
SYS              SYS_FBA_TSFA
SYS              SYS_FBA_TRACKEDTABLES
SYS              SYS_FBA_PARTITIONS
SYS              SYS_FBA_USERS
SYS              SYS_FBA_BARRIERSCN
SYS              SYS_FBA_DL
SYS              SYS_FBA_CONTEXT
SYS              SYS_FBA_CONTEXT_AUD
SYS              SYS_FBA_CONTEXT_LIST
SYS              SYS_FBA_APP
SYS              SYS_FBA_APP_TABLES
SYS              SYS_FBA_COLS
SYS              SYS_FBA_PERIOD
SYS              SYS_MFBA_STAGE_RID
SYS              SYS_MFBA_TRACKED_TXN
SYS              SYS_MFBA_NROW
SYS              SYS_MFBA_NCHANGE
SYS              SYS_MFBA_NTCRV

You can refer below Oracle Doc for best practices

FDA - Flashback Data Archive Usage and Best Practices (Doc ID 2370465.1)

Flashback Data Archive provides many benefits for maintaining historic data against tracked tables. The FDA helps to perform undo-based flashback operations for an extended period and take advantage of this feature.