Advertisements

Cause: java.sql.SQLException: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body has been invalidated – ICS Integration Failed

 

 

ORA-04068: existing state of packages has been discarded

Cause: java.sql.SQLException: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package “XXX” has been invalidated

ORA-04065: not executed, altered or dropped package “XXXXX”

ORA-06508: PL/SQL: could not find program unit being called: “XXX” ORA-06512: at line 1

 

Recompilation is resulting in invalidation of packages by database for existing database sessions related to ICS.

Avoid recompilation of packages during the working hours and plan such all migrations at once during the weekend.

Hope next execution of ICS integration run should be fine, if not, restarting Agent is the solution at the moment or contact Oracle Support.

 

Thank you !!

 

 

 

Advertisements

oracle.cloud.cpi.agent.transport.aq.CpiAQException: Message not received within 240 seconds of wait interval

 

 

ICS runtime execution error Failed with following error message:

ICS runtime execution error</summary></part><part name=”detail”><detail><detail><errorCode>OSB-380001</errorCode><ICSfaultVar/><reason>oracle.cloud.cpi.agent.transport.aq.CpiAQException: Message not received within 240 seconds of wait interval.:Application Error

  1. Integrations created with DB Connection(with Agent) are getting stuck in Running state.
  2. DBAgent call got stuck.

Issue is due to the DB call got stuck on Agent due to some issue on Query or DB.

Solution:

  1. Check the query used and see what is the response time. Connection from ICS to agent has timeout of 240 seconds and calls should return before this time. Make changes to your query / stored procedure to respond quicker which should help address the issue.
  2. Try restart the Agent server to clear any stuck threads.

If the issue continue to occur, please contact Oracle Support for further review.

 

Thank you !!

 

 

Unlock an Integration – ICS OIC

 

When an ICS integration is in edit mode and the browser get suddenly crashes, the integration becomes locked, which prevents it from being edited. There are several ways to unlock the ICS integration.

To unlock the ICS integration:

  1. Log in as a user with the Administrators role.
  2. In the navigation pane, click Integrations.
  3. For the integration that is locked, click the hamburger menu (hamburger) menu at the far right, then select Unlock.

Thank You !!

 

DATAPUMP EXPDP/IMPDP – TABLES EXPORT AND IMPORT

Trying to export tables from one database to another database:

Source Database:

expdp system/manager directory=TEMP tables=SCHEMA1.TABLENAME1,SCHEMA2.TABLENAME2 dumpfile=SCHEMA_TABLES.dmp logfile=expdp_SCHEMATABLES.log

Dumpfile will be created under TEMP (To finout the Path/location: select DIRECTORY_PATH from dba directories where DIRECTORY_NAME=’TEMP’)

Target Database:

Copy the file from source database to target DATA_PUMP_DIR, use above query to findout location of directory DATA_PUMP_DIR.

Make sure tablespace names in source and target database should be same, other wise use remap_tablespace to avoid the issues.

impdp system/manager directory=DATA_PUMP_DIR dumpfile=SCHEMA_TABLES.dmp logfile=impdp_SCHEMATABLES.log

In impdp command, no need to expiciltly mention tables parameter as dumpfile contains two tables. If you wish to import only one table, use TABLES parameter set to required table so that other table will not import.

 

Thank You !!

Connect to Oracle Cloud Database Server using SSH

1. Login to http://cloud.oracle.com

2. Click on Dashboard -> Services -> Database

3. Click on Instance

4. Find out the PUBLIC IP ADDRESS from Instance overview.

5. Configure SSH PUTTY connectivity as below:

Provide Public IP Address:

Provide default username “opc” -> Under Connection -> data

Click on SSH -> AUH, Please provide saved private key location. which was asked you to save private key in previous post generate public and private keys.

6. Click on open, now you will connect to the Server:

As you connected to default user “opc”, please sudo to oracle user and access the database.

Thank you !!

Deploy Application using WEBLOGIC ADMIN Console

1. Login to Admin console http://hostname:7001/console

2. Go to Deployments under Domain structure. Click on deployments.
Click on “Lock&Edit” under change center.

Weblogic_deploy1

3. Click on install:

Weblogic_deploy2

4. Browse the location where the war/ear file placed on the server and check the radio button.

Weblogic_deploy3

5. Check the radio button, Install this deployment as an Application.

There are cases we may need to install libraries to support application, in such cases we will choose other library option.

Weblogic_deploy4

6. Select deployment target

Weblogic_deploy5

7. Check the radio button where our deployment file existed and click on Finish.

Weblogic_deploy6

8. Finish the process, save the changes and Activate it.

Now Start the Deployment which is in prepared state, Go to deployments, click on control, click ‘Start’ button. Select ‘Servicing all requests’. Click Yes to continue.

Weblogic_deploy7

Now the Deployment state comes to ACTIVE status.

Thank you !!

ORA-39142: incompatible version number 5.1 in dump file

PROBLEM:

Trying to export schema/tables from higher Database version 12.2.0.1.0 and import to version 12.1.0.1.0, causing following error message.

ERROR:

Import: Release 12.1.0.2.0 – Production on Wed Jun 20 05:22:35 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file “xxx.dmp”

SOLUTION:

Please set parameter Version=12.1 in export command and import the data without issue.

Source Database(12.2.0.1):

expdp scott/tiger@PRIM directory=DATAPUMP tables=scott.testtb Version=12.1 dumpfile=Exp_Scott_test.dmp logfile=Exp_Scott_test.log

Target Database(12.1.0.1):

impdp scott/tiger@TEST directory=DATAPUMP tables=scott.testtb dumpfile=Exp_Scott_test.dmp logfile=Imp_Scott.log

Thank you !!

Configure listener and tnsnames files (Oracle Network Configuration)

listener.ora:

The “listerner.ora” file contains server side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the server.

Make sure hostname, oracle_home location, port number and db names are pointed correct in below example:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <<SERVER NAME/IP>>)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u01/oracle/product/12c)
(SID_NAME = ORCL)
)
)

Listener Operations:

lsnrctl status –> To know the status
lsnrctl start –> To start the service
lsnrctl stop –> To stop the service
lsnrctl reload –> To restart the service

tnsnames.ora:

The “tnsnames.ora” file contains client side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the client.

ORCL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<<SERVERNAME/IP>>)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ORCL)
(INSTANCE_NAME=ORCL)
)
)

To avoid confusion on names, normally we keep listener name and tns name set to DBNAME for better understanding.

 

Thank you !!

Clone/Refresh Database using RMAN

For our understanding

Source Database: PRIM (It would be TARGET in case of RMAN Terminology)

Auxiliary/Clone Database: TEST

1) Take FULL backup on source database using RMAN:

Verify Source database is configured in archivelog mode.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

RMAN> backup database plus archivelog;

2) Perform following preparation tasks in Auxiliary Database:

Create a password file for the duplicate/Target instance, create password file in source and copy to auxiliary would also work here.

orapwd file=/u01/oracle/product/12c/dbs/orapwTEST password=password entries=10

Configure listener and tnsnames files for Auxiliary/TEST database server. Configure listener and tnsnames files

Create pfile for auxiliary environment and start the database to nomount status:

Add following entries to pfile, in case directory path for Auxiliary server is different for DB and log files when compare to source database.

Db_file_name_convert=’target db(PRIM) oradata path’,’clone db(TEST) oradata path’
Log_file_name_convert=’target db(PRIM) oradata path’,’clone db(TEST) oradata path

SQL> conn /as sysdba
SQL> startup nomount pfile=/u01/oracle/product/12c/dbs/initTEST.ora

 Add tnsentries of source database in Auxiliary, please do vice versa

3) duplicate command to create TEST database:

Veryfy the RMAN connection connecting to TARGET Database(PRIM):

rman TARGET sys/password@PRIM

Veryfy the RMAN connection connecting to TARGET Database(PRIM) and AUXILIARY(TEST):

rman TARGET sys/password@PRIM auxiliary /

Now We can use duplicate database command to create TEST database.

rman > duplicate target database to ‘TEST’;

This process takes a while depending on the size of database and server configuration.

Thank you !!

 

 

Generate Public and Private Keys using PuTTYgen

Start the PuTTYgen utility, by double-clicking on its .exe file

For Type of key to generate, select RSA. In the Number of bits in a generated key field, specify either 2048 or 4096 (increasing the bits makes it harder to crack the key by brute-force methods). Click the Generate button.

Putty Gen1

Move your mouse pointer around in the blank area of the Key section, below the progress bar (to generate some randomness) until the progress bar is full.

Putty Gen2

A private/ public key pair has now been generated, Click the Save public and private keys. If you lose your keys and have disabled username/password logins, you will no longer be able to log in server.

Putty Gen3

Thank you !!