Oracle DBA Blog Apps DBA Blog

PostgreSQL function similar to Oracle validate_conversion function

2023-10-21T19:42:00.002+04:00

This function accepts a text value, and a data type; returns 1 if the text value can be converted to the data type, otherwise 0. This is tested on server 15.4

CREATE OR REPLACE FUNCTION validate_conversion (p_input TEXT, p_type VARCHAR(128))
RETURNS integer
AS
$body$
DECLARE
valid_convert integer = 1;
tmp_val TEXT;

BEGIN
p_type = TRIM(LOWER(p_type));

EXECUTE FORMAT('SELECT CAST (''%s'' AS %s)', p_input, p_type)
INTO tmp_val;

IF LENGTH(tmp_val) <> LENGTH(p_input)
THEN
valid_convert = 0;
END IF;
RETURN valid_convert;

EXCEPTION
WHEN OTHERS THEN
valid_convert = 0;
RETURN valid_convert;
END;
$body$
LANGUAGE plpgsql;

NFS not mounting on Linux

2023-09-01T15:01:00.012+04:00

NFS not mounting

================

Seen below messages in /var/log/messages

Sep 1 08:57:45 server-app1 systemd[1]: u01.mount: Unit is bound to inactive unit dev-oracleoci-oraclevdb1.device. Stopping, too.

Sep 1 08:57:45 server-app1 systemd[1]: Unmounting /u01...

Sep 1 08:57:45 server-app1 nfsrahead[8179]: setting /u01 readahead to 128

Sep 1 08:57:45 server-app1 systemd[1]: u01.mount: Succeeded.

Sep 1 08:57:45 server-app1 systemd[1]: Unmounted /u01.

Change:

Created a new compute instance from a custom image which already has the mountpoint reference in fstab which we are trying to use (u01)

Solution:

-----------

After altering fstab, systemd need to reparse /etc/fstab and pick up the changes or else the mount point will be unmounted automatically by systemd.

Correct the entry in the /etc/fstab . Make sure that required mount point entry is fstab and unwanted is reqmoved.

Also, make sure to unmount if same nfs export is mounted in other mount point on this server

Reload systemctl or reboot the server to avoid this issue.

# systemctl daemon-reload

We have executed systemctl daemon-reload command and ran mount -a

Issue solved.

How to Resolve Oracle TNS Error ORA -12514 and ORA-12521

2023-06-23T12:56:00.005+04:00

When you are trying to connect to your database using TNS alias you may get below errors like

ORA-12514 : TNS:listener does not currently know of service requested in connect descriptor

ORA-12521: TNS:listener does not currently know of instance requested in connect descriptor

For example you are trying to connect to a DB using sqlplus as below

sqlplus user1@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 23 05:28:08 2023

Version 19.18.0.0.0

Enter password:

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When you try tnsping your TNS alias

Used TNSNAMES adapter to resolve the alias

# tnsping ORCL

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=orclhost1)(PORT=1526)) (CONNECT_DATA= (SERVICE_NAME=ORCL) (INSTANCE_NAME=ORCLCDB1)))

OK (0 msec)

Solution for ORA-12514

It seems the SERVICE_NAME given in the TNS alias is not correct. Please check with your DBA and verify the listener has a service named ORCL. Find out the correct service_name and update your TNS alias in the local tnsnames.ora file.

Solution for ORA-12521

If you are getting ORA-12521: TNS:listener does not currently know of instance requested in

connect descriptor, similarly, try tnsping of the TNS alias. In this case INSTANCE_NAME in the TNS alias description is wrong. Find out the correct instance_name and update your TNS alias in the local tnsnames.ora file.

Oracle error-20001:APP-FND-01972: When Trying To End Date A Responsibility

2021-05-30T15:30:00.001+04:00

EBS Release 12.1.3

When trying to end_date a responsibility that is assigned to a user an error occurs.

ERROR
-----------------------
Oracle error-20001:APP-FND-01972:Error in FND_USER_RESP_GROUPS_API. Update_ Assignment. Cannot update this row because there is no direct assignment of the user XXX to the role FND_RESP\XXX\XXXXXXXXX\STANDARD in the workflow user/Role table. has been detected in FND_USER_RESP_GROUP_API.UPDATE_ASSIGNMENT.

This is caused by invalid data in the workflow user/Role table.

Solution

please execute the following steps:

1. Go into the responsibility: System administrator.

2. Navigate to Requests > Run

3. Select request ''Workflow Directory Services User/Role Validation' and submit

4. Retry to update the responsibility

Error when application cloning- adcfgclone: EBS R12.2 /etc/inittab does not seem to contain default runlevel information. Linux 7.x

2021-05-30T15:01:00.000+04:00

Environment Details:

OS: RHEL 7.8
EBS Release: 12.2.4
AD/TXK delta 12
ATG: 7

Issue:

While cloning (adcfgclone) the EBS R12.2 on Linux 7.8 getting below error.

Check Name:CertifiedVersions

Check Description:This is a prerequisite condition to test whether the Oracle software is certified on the current O/S or not.

/etc/inittab does not seem to contain default runlevel information.

Expected result: One of oracle-7,redhat-7,redhat-6,oracle-6,oracle-5,enterprise-5.4,enterprise-4,enterprise-5,redhat-5.4,redhat-4,redhat-5,SuSE-10,SuSE-11

Actual Result: redhat-Red

Check complete. The overall result of this check is: Failed <<<<

Solution:

Verify the RPM redhat-lsb-4.1-27.0.1.el7.x86_64 is Installed on the server. If missing, install it. This is a pre-requisite for EBS R12.2 on Linux 7.x

Oracle Apps - CDB / PDB how to source the environment and connect to CDB and PDB

2021-05-09T15:26:00.000+04:00

In Oracle E-Business suite with Multi tenant database, load the proper environment variables and connect to the database by performing the following steps:

For the non-CDB database, source the $ORACLE_HOME/<non-CDB SID>_<HOST>.env/cmd file. Then, run sqlplus<user>/<password>@<non-CDB SID>

For the CDB database, source the $ORACLE_HOME/<CDB SID>_<HOST>.env / cmd file. Then, run sqlplus<user>/<password> or connect as SYSDBA.

For the PDB database on UNIX/Linux platforms, to connect as SYSDBA, source the $ORACLE_HOME/<CDBSID>_<HOST>.env file. Set the ORACLE_PDB_SID environment variable to <PDB SID>. Then, connect as SYSDBA.

For the PDB database on Windows platforms, to connect as SYSDBA, source the $ORACLE_HOME/<PDBSID>_<HOST>.cmd file. Then, connect as SYSDBA.

For the PDB database, to connect to other users, source the $ORACLE_HOME/<PDB_SID>_<HOST>.env / cmd file. Then,run sqlplus <user>/<password>@<PDB SID>

How to check if connected to Oracle Pluggable Database (PDB)

2021-05-03T17:06:00.000+04:00

To know if you are connected to a Pluggable Database ( PDB) which is part of Container Database(CDB)

Execute below SQL script.

SELECT DECODE (SYS_CONTEXT ('Userenv', 'CDB_NAME')

,null,'NO','YES'),

Sys_Context('Userenv', 'CON_ID'),

Sys_Context('Userenv', 'CON_NAME')

From dual;

If connected to PDB you will get first column value as YES , a non zero number for second column and PDB Name in third column

If connected to a normal database (non CDB/PDB) you will get first column value as NO, zero for second column and Database Name in third column.

How to Delete and Clear some URLs from Edge Browser Address Bar History

2018-06-29T22:34:00.001+04:00

Microsoft Edge is the modern browser available with Windows 10. When you browse a website by typing in the address(URL) directly in the address bar of Edge browser, the URL will be saved, and can be viewed in the address bar. Due to privacy concern, you may want to clear the history of link(s)/URL from Edge Browser address bar. As of now there is no direct interface to delete url from address bar in Edge.

Follow below steps to delete particular URL(s) without deleting your entire browsing history

Note: You must have privilege to do edit the registry. Take backup of registry as safeguard by exporting.

Warning: Be informed and careful while editing registry. This could cause system instability.

Close all Edge Browser

1. Press Windows+R button to Open Run Window.

2. Run Registry Edit by typing RegEdit in the text box and click OK

3. In Regedit, navigate the entry similar to:

Computer\HKEY_CLASSES_ROOT\Local Settings\Software\Microsoft\Windows\CurrentVersion\AppContainer\Storage\microsoft.microsoftedge_8wekyb3d8bbwe\MicrosoftEdge\TypedURLs

4. Delete the registry value name for the url on right side pane for the one you wish to delete.

5. Close the registry editor.

How to move files from one directory to another using find and xargs command

2017-11-27T14:08:00.000+04:00

find - search for files in a directory hierarchy
xargs - build and execute command lines from standard input

Using find and xargs we can search any file in the directory hiearchy and xargs execute the command accepting the output of find command as input.

Use xargs with -I option to replace occurrences of replace-str in the initial-arguments with names read from standard input(In our case from find command). The placeholder following the -I is a string that can appear multiple times in the command.

Example: Below command will find all files with pattern *.txt in current directory and move to /NewDir/.

find . -name "*.txt" | xargs -I '{}' mv '{}' /NewDir/'{}'

Here '{}' is placeholder

How to download and setup Oracle Cloud Control agent software for provisioning

2017-07-25T11:37:00.001+04:00

You want to add/update your target server to the Oracle Enterprise Manager Cloud Control and you notice that the Agent Software for your platform is shown as unavailable. Here I will describe the method how to download the agent software manually and setup in the Oracle Enterprise Manager Cloud Control

This step is tested on Oracle Enterprise Manager Cloud Control 12C 12.1.0.5.0

1) Ensure Cloud Control is set to offline mode. From the Setup menu, select Provisioning and Patching, then select Offline Patching.

2) Change the setting for Connection to Offline and click Apply.

3) From the Setup menu, select Extensibility, then select Self Update.

4) Click on Agent Software Type and open Agent Software updates page.

5) Select the Agent Software you want to download for example: Oracle Solaris on x86-64 (64-bit) and click Download button on the top of table

A message is displayed that contains the URL to be accessed to download

6) From an Internet-enabled computer, download the agent zip file
Once the update is downloaded, it can be imported to Enterprise Manager in one of the following ways:

7) Transfer the downloaded file to the OMS host, and run following command to import it to Enterprise Manager

8) Navigate to the 12.1 MOS home/bin directory and log in to emcli, running the command as the install user, for example:

./emcli login -username=sysman
./emcli import_update -omslocal -file=<absolute path to zipfile>

At this stage, the update will show up in a downloaded state in the Self Update home page.
Once the download is complete, select the Management Agent, then click Apply. This step will stage the Management Agent software in the Software Library and make it available to the Add Host Targets wizard, which you will use to install the Management Agent on host machines.
Click Agent Software to launch the Add Host Targets wizard.

java.sql.SQLException: ORA-28040: No matching authentication protocol

2017-07-20T10:36:00.000+04:00

When connecting to 10g ,11g or 12c databases using a JDBC thin driver , fails with following errors:

The Network Adapter could not establish the connection

ORA-28040: No matching authentication protocol

This happens because you are using a non compatible version of JDBC driver.

To resolve this issue, make sure that you are using the latest version of Oracle JDBC driver, if not use JDBC 12c or higher versions.

Oracle Client/Server Compatibility Matrix:

Oracle Client
Version Database Version

12.2.0 12.1.0 11.2.0 11.1.0 10.2.0 10.1.0 9.2.0

12.2.0 Yes Yes Yes No No No No

12.1.0 Yes Yes Yes Was MDS No No

11.2.0 Yes Yes Yes Was MDS No Was

11.1.0 No Was Was Was Was Was Was

10.2.0 No MDS MDS Was MDS Was Was

10.1.0 No No Was Was Was Was Was

9.2.0 No No Was Was Was Was Was

Key:

Yes Supported

ES Supported but fixes only possible for customers with Extended Support .

LES or MDS Supported but fixes only possible for customer with a valid Limited Extended Support or Market-Driven Support contract respectively.

Was Was a supported combination but one of the releases is no longer covered by any of Premier Support, Primary Error Correct support, Extended Support, nor Extended Maintenance Support. Fixes are no longer possible.

No Has never been Supported

Courtesy: My Oracle Support

Oracle Messaging Gateway - MGW - Install and Setup

2016-10-16T22:51:00.001+04:00

Install and setup Oracle Messaging Gateway - MGW

Messaging Gateway(MGW) is a feature in Oracle Database Enterprise Edition. Messaging Gateway enables communication between applications based on non-Oracle messaging systems such as Java Message Service (Oracle JMS) ,WebSphere MQ Java Message Service (WebSphere MQ JMS),TIB/Rendezvous messages and Oracle Streams Advanced Queuing.

Here I will provide the steps I used to load and setup MGW in a existing Oracle Database.

Enviroment

OS: Oracle Enterprise Linux 7.2

DB: Oracle 12c Enterprise Edn.

Pre-Requisites

Check value of the JOB_QUEUE_PROCESSES database initialization parameter. It must be 1 or high.

Loading Database Objects into the Database

Using SQL*Plus, run ORACLE_HOME/mgw/admin/catmgw.sql as user SYS as SYSDBA

This script loads the database objects required for Messaging Gateway. It also creates two roles, MGW_ADMINISTRATOR_ROLE and MGW_AGENT_ROLE, with certain privileges granted.

Modifying listener.ora for the External Procedure

LISTENER=
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome_1)
      (SID_NAME = DB1)
    )
    (SID_DESC =
     (SID_NAME= mgwextproc)
    (ENVS="LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/jre/lib/amd64:/u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/jre/lib/amd64/server:/u01/app/oracle/product/12.1.0.2/dbhome_1/lib")
     (ORACLE_HOM= /u01/app/oracle/product/12.1.0.2/dbhome_1)
     (PROGRAM= extproc)
    )
)

Note: Set up the LD_LIBRARY_PATH environment needed for the external procedure to run. The LD_LIBRARY_PATH must contain the following paths:
JRE_HOME/lib/PLATFORM_TYPE
JRE_HOME/lib/PLATFORM_TYPE/server
ORACLE_HOME/lib

Modifying tnsnames.ora for the External Procedure

The net service name must be MGW_AGENT (this value is fixed). The KEY value must match the KEY value specified for the IPC protocol in listener.ora. The SID value must match the value specified for SID_NAME of the SID_DESC entry in listener.ora.

MGW_AGENT=
(DESCRIPTION=
   (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))
   )
   (CONNECT_DATA=(SID=mgwextproc))
)

In sqlnet.ora set SQLNET.INBOUND_CONNECT_TIMEOUT =0 and restart the listener

Setting Up an mgw.ora Initialization File

The Messaging Gateway external procedure uses it to get initialization parameters to start the Messaging Gateway agent. Copy ORACLE_HOME/mgw/admin/sample_mgw.ora to mgw.ora and modify it according to your environment.

Creating an Oracle Messaging Gateway Administrator User

CREATE USER mgwadmin IDENTIFIED BY admin123;
GRANT CREATE SESSION to mgwadmin ;
GRANT MGW_ADMINISTRATOR_ROLE to mgwadmin ;

Creating an Oracle Messaging Gateway Agent User

CREATE USER mgwagent IDENTIFIED BY agent123;
GRANT CREATE SESSION to mgwagent ;
GRANT MGW_AGENT_ROLE to mgwagent ;

Configuring Oracle Messaging Gateway Connection Information

sqlplus /nolog

SQL> set echo off
SQL> set verify off
SQL> connect mgwadmin
Enter password:
Connected.

SQL> EXEC DBMS_MGWADM.ALTER_AGENT(agent_name => 'default_agent',username => 'mgwagent',password => 'agent123',database => 'DB1')

PL/SQL procedure successfully completed.

SQL>

Verifying the Oracle Messaging Gateway Setup

The following procedure verifies the setup and includes a simple startup and
shutdown of the Messaging Gateway agent:
1. Start the database listeners.
Start the listener for the external procedure and other listeners for the regular database connection.
2. Test the database connect string for the Messaging Gateway agent user.

Run sqlplus mgwagent/agent123@DB1.

If it is successful, then the Messaging Gateway agent can connect to the database.
3. Linux Operating System Only: Test the net service entry used to call the external
procedure.

Run sqlplus mgwagent/agent123@MGW_AGENT.

This should fail with "ORA-28547: connection to server failed, probable Oracle Net
admin error". Any other error indicates that the tnsnames.ora, listener.ora,
or both are not correct.

4. Connect as admin_user and call DBMS_MGWADM.STARTUP to start the Messaging
Gateway agent.

sqlplus mgwadmin/admin123@DB1

SQL> exec DBMS_MGWADM.STARTUP

PL/SQL procedure successfully completed.

wait for AGENT_STATUS to change to RUNNING and AGENT_PING to change to REACHABLE
SQL> select agent_status , agent_ping from MGW_GATEWAY;

Command Equivalent to "grep" in Windows

2016-09-11T11:37:00.000+04:00

grep in Linux/Unix, find in Windows

Many times you might have wondering what is the command available in Windows which is equivalent for "grep" in Linux/Unix. You can use findstr command in Windows

grep
The grep command in Linux/Unix is one of the most used command. This is used for searching patterns in text. grep process text line by line and prints any line that matching the specified pattern. Regular Expressions can be used searching.

grep can be used in many scenarios.

Examples: Search for a file Starting with "XX" in a directory

$ ls | grep XX

List all files except file Starting with "XX" in a directory

$ ls | grep -v XX

Search for pattern in a file and list file names and lines

$ grep 187 *

Windows - findstr

findstr command in Windows can be used for similar purpose. Now let us see how above scenarios can be achieved using findstr command.

Examples: Search for a file Starting with "XX" in a directory

c:\ dir | findstr "XX"

List all files except file Starting with "XX" in a directory

c:\ dir | findstr /v "XX"

Search for pattern in a file and list file names and lines

c:\ findstr "Internal" *

There are more parameters that can be used in combination to get the output.

Import Favorites from Internet Explorer to Edge Browser from Old PC to New PC

2015-08-11T12:01:00.003+04:00

If you have newly installed Windows 10 on New PC or Dual Boot with earlier version of Windows, you may wonder how to transfer the favorites to Edge browser. It is easy to import favorites from Internet Explorer to Edge on Windows 10 if you are upgraded. But if you did a clean install, importing favorites from Earlier version is bit tricky.

Here is how I did it:

Environment: I have a PC with Dual Boot enabled for Windows 8.1 and Windows 10

Note: If you are doing a clean install Windows 10 on a PC, before installing windows 10 you must export the favorites to file and copy it to a secure location for later use.

Step 1: Export the Favorites from Old version

On Windows 8.1 Open Internet Explorer , Press Alt + F, Move down and select Import Export. Select Export to a file option in the Dialog window and click next. Select Favorites and Click Next. Select the folders you want to export and click next. Select the file location you want to save.

Step 2: Import the favorites in Internet Explorer in Windows 10

Note: Since the Edge browser does not support importing from html file directly, you have to import it to Internet Explorer and then import to Edge.

Copy the exported favorites to Windows 10. Open the Internet Explorer and Import the favorites from file.

If you can not see the menu/icon for internet explorer in Windows 10, you open it by typing it in Cortana Search Box.

On Windows 10 Internet Explorer , Press Alt + F, Move down and select Import Export. Select Import from a file option in the Dialog window and click next. Select Favorites and Click Next. Select the html file path you want to import and click next. Now favorites imported to the Internet Explorer in Windows

Step 3: Import favorites to Edge from Internet Explorer on Windows 10

Open the Edge Browser, and click on the Hub link in the top right corner (3 Lines)

Click on the import favorites, and on the pane which opened will show the browsers you have installed. Select the Internet Explorer and Click Import. When import completed, you will see All Done message.

Windows 10 : How to setup proxy

2015-08-06T12:53:00.001+04:00

Windows 10 : How to setup proxy

On Windows 10 Proxy setting is done in different method on comparison to previous versions.

To Setup proxy on Windows 10:

Click on the Start Menu Button
Click on Settings Menu
On the Settings Window, Click on Network & Internet Icon
Choose Proxy from Left Hand Side, On right Hand side you can setup proxy server, Scroll down to setup proxy manually.
Click on Save Button.

OBIEE 11g Graphs Do not display after applying patch on 11.1.1.6.x

2014-10-11T11:13:00.001+04:00

I have applied patch bundle 11.1.1.6.5 on my OBIEE 11.1.1.6.0. After applying the patch, I have noticed that none of my graphs/charts are displayed on the browser. I have deleted the browser cache and cookies, but still not displaying.

After referring the oracle support, I have did the following to resolve the issue.

We have customization for custom skins and styles in custom directory, so we did the below section first.

Section 1 Copy BIChart.swf file to custom directory

1. Copied the BIChart.swf file from ORACLE_BI_HOME'bifoundation\web\app\res\sk_blafp\dvt directory to our custom directory

Custom directory located at <MW_HOME>/Oracle_BI/bifoundation/OracleBIPresentationServiceComponent/coreapplication_obips1/analyticsRes/<custom_dir>/dvt

2. Stop and Restart the BI Presentation Service components

cd <INSTANCE_HOME>/bin
opmnctl stopproc ias-component=coreapplication_obips1

opmnctl startproc ias-component=coreapplication_obips1

Section 2 Clear the managed server temporary files

1. Stop the Services
2. backup $DOMAIN_HOME/servers/bi_server1/tmp
3. cd $DOMAIN_HOME/servers/bi_server1/tmp
4. rm -rf * (including the hidden .internal directory)
5. Start the services

Now, open the browser and clear the cache and cookies.
Login to the analytics dashboard. If graphs/charts not visible, refresh the page by pressing ctrl+F5 (On Internet Explorer)

This should resolve the issue.

Oracle Apps: How to copy responsibilities assgined to a user to another user on same instance.

2014-10-06T12:18:00.000+04:00

In the PROD instance, there is a user X who has 10 responsibilities assigned. Now you want to assign these 10 responsibilities to another user Y. User Y already has 2 responsibility and want to copy remaining 8 responsibilities to user Y. You do not want to assign these responsibilities manually on the User Define form in System Administrator responsibility.

You can use a PL/SQL block similar to below to get it done.

DECLARE

resp_count NUMBER := 0;

CURSOR src_user_resps
IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg,
      fnd_responsibility_tl frt
      WHERE 1=1
      AND fu.user_name                        = '&From_Username'
      AND fu.user_id                          = furga.user_id
      AND fa.application_id                   = fr.application_id
      AND furga.responsibility_id             = fr.responsibility_id
      and furga.responsibility_id = frt.responsibility_id
      AND furga.responsibility_application_id = fa.application_id
      AND fsg.security_group_id               = furga.security_group_id
      AND furga.end_date IS NULL
minus
SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg,
      fnd_responsibility_tl frt
      WHERE 1=1
      AND fu.user_name                        = '&To_Username'
      AND fu.user_id                          = furga.user_id
      AND fa.application_id                   = fr.application_id
      AND furga.responsibility_id             = fr.responsibility_id
      and furga.responsibility_id = frt.responsibility_id
      AND furga.responsibility_application_id = fa.application_id
      AND fsg.security_group_id               = furga.security_group_id
      AND furga.end_date IS NULL;

BEGIN
FOR user_resp_rec IN src_user_resps
LOOP
    BEGIN
      --
      fnd_user_pkg.addresp
                 (username            => '&To_Username',
                  resp_app            => user_resp_rec.application_short_name,
                  resp_key            => user_resp_rec.responsibility_key,
                  security_group      => user_resp_rec.security_group_key,
                  description         => 'Demo',
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );

      resp_count := resp_count + 1;

EXCEPTION
    WHEN OTHERS THEN

      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_rec.responsibility_key );

    END;
END LOOP;

DBMS_OUTPUT.put_line (resp_count || ' Responsibilities copied Successfully!!' );

COMMIT;

END;

How to convert a PDF to Word (2013)

2014-02-06T13:17:00.001+04:00

Even though this is not related to DBA topic, this might be useful to you. In this article I will describe how to open a pdf document in Microsoft Word 2013 without using any third party component.

1. Open the word and create a new blank document
2. Select Insert Menu, in the Text group > click the arrow next to Object > click Text from File.
Keyboard shortcut: Press Alt Key then NJF

3. Select the pdf file you want to open in the Insert File window and Click Insert Button.
4. Click OK.

Wait for the conversion to complete. It will take some time to complete depends on the size of the pdf file and graphics content.

R12 Error: Application Diagnostics is not a valid responsibility for the current user. Please contact your System Administrator.

2013-09-01T10:56:00.000+04:00

User Must have one of the below roles to be assigned to run Oracle Diagnostics.

Diagnostics Super user
Application Super user
Application End User

To Assign role:

Select the "User Management" responsibility

Click 'Users' in the right hand side menu

Enter 'User Name' and click 'Go'

Click 'Update' on the correct user

Click 'Assign Roles' button

Enter role name (or partial with %) you want to assign. You can enter any of the seeded roles (Diagnostics Super User Role, Application Super User Role, Application End User Role), or your own one

Check the box next to the appropriate role and click 'Select'

Enter 'Justification' and click 'Save'

Now ask the user to logoff and login again to E-Business Suite and try again to access Oracle Diagnostics.

ORA-07445: exception encountered: core dump [__intel_new_memcpy()+382] [SIGILL] [ADDR:0x46F1D9E] [PC:0x46F1D9E] [Illegal operand]

2013-05-11T22:30:00.000+04:00

Recently one of my database shows ORA-07445: exception encountered: core dump [__intel_new_memcpy()+382] [SIGILL] [ADDR:0x46F1D9E] [PC:0x46F1D9E] [Illegal operand] while running one report.

This is how i resolved the issue.

I raised the SR and Oracle Support told me that the failing function is associated with ORA-07445 is "evadcd" , which shows that the error comes from decode.

Identify the failing statement: Trace file generated at the time of ORA-07445 will contain the SQL caused the error. Identify all the tables used in the SQL statement and analyze the tables to ensure that there is no corruption on the tables using analyze table <owner>.<table_name> validate structure cascade;

None of the tables reported any corruption, all tables analyzed successfully, So the error must be related to invalid data in some of the colums.

So, i have to identify the record(s) which causing the error. For that, to narrow down, i have added additional condition to the query the records for a prticular time period in my case.

For Example:

In my query on of the condition used was

where (txn_date) BETWEEN TO_DATE ('17-APR-2013 07:00:00',
                                                   'DD-MON-YYYY HH24:MI:SS')
                                      AND TO_DATE ('18-APR-2013 08:00:00',
                                                    'DD-MON-YYYY HH24:MI:SS')

In order to narrow down to the erroring record, i have tested the query by narrowing down to smaller date period to locate the exact row which throws the error. Once i have identified the row which gives the error, look for the colums used in the query and look for invalid value and correct it.

Shell Script to Monitor Data Guard Status

2012-10-29T11:46:00.001+04:00

Here is a shell script that can be scheduled to monitor the status of the data guard. The best method for monitoring data guard and databases is to user Oracle Enterprise Manager Grid Control and setup and Manage the Data Guard.

Environment Used to test the script.

OS: Enterprise Linux Server release 5.3
Database: 11.2.0.2
No of Standby DB: 1
Data guard Type: Physical Standby

Below script will connect to primary database and query V$ARCHIVE_DEST_STATUS and V$ARCHIVED_LOG to get the data guard status and un-applied log sequence count. If the data guard status is in error or count of un-applied logs are greater than 2, a subroutine can be called to alert the DBA. Assume that DEST_ID 2 is Standby Database.

#!/usr/bin/ksh
#Script to monitor Data guard Status and alert if there is any issue.
#=======================================================================
DG_STATUS=`sqlplus -s "/ as sysdba" <<EOF
set heading off verify off feedback off serveroutput on
declare
l_stat varchar2(30);
l_gap_stat varchar2(100);
l_stat1 varchar2(150);
l_stat2 varchar2(20);
l_gap_cnt number;
l_appld_seq number;
begin
l_stat2 :='DG_NORMAL';
SELECT STATUS, GAP_STATUS into l_stat, l_gap_stat FROM "V\\$ARCHIVE_DEST_STATUS" WHERE DEST_ID = 2;
l_stat1 := l_stat||l_gap_stat;
dbms_output.put_line(l_stat1);
if l_stat1 <> 'VALIDNO GAP' then
--      dbms_output.put_line('Datagaurd Status is in Error');
        l_stat2 := 'DG_ERRORR';
end if;
select count(1) into l_gap_cnt from "V\\$ARCHIVE_GAP";
dbms_output.put_line('Gap count: '||l_gap_cnt);
if l_gap_cnt>0 then
        dbms_output.put_line('Datagaurd Status is in Error');
        l_stat2 := 'DG_ERRORR';
end if;
SELECT COUNT(1) into l_appld_seq FROM "V\\$ARCHIVED_LOG" WHERE APPLIED='NO' and DEST_ID=2;
dbms_output.put_line('Unapplied log count: '||l_appld_seq);
if l_appld_seq>2 then -- Archived log may not apply immediatly so chk for more than 2
--      dbms_output.put_line('Datagaurd: Log apply is stopped');
        l_stat2 := 'DG_ERRORR';
end if;
dbms_output.put_line(l_stat2);
end;
/
quit
EOF`
rm -rf ./DG_PREV_STAT
cp ./DG_CURR_STAT ./DG_PREV_STAT
DG_PREV_STAT=`cat ./DG_PREV_STAT`

echo $DG_STATUS
l_stat=${DG_STATUS: -9}
echo $l_stat
if [ "$l_stat" = "DG_NORMAL" ]; then
        echo "NORMAL" > ./DG_CURR_STAT
        if [ "$DG_PREV_STAT" = "NORMAL" ]; then
                echo "No Change in Dataguard Status"
         else
                echo "Status of Dataguard changed from ERROR to NORMAL"
        fi
else
        echo "ERROR" > ./DG_CURR_STAT
if [ "$DG_PREV_STAT" = "ERROR" ]; then
    echo "No Change in Dataguard Status"
else
    echo "Status of Dataguard changed from NORMAL to ERROR"
    #--- Call Subroutine to Alert DBA -------
    # For Example use wget function to call a SMS gateway URL
    # or mail command to send email.
    #-----------------------------------------

EOFT
fi
fi

EBusiness - "Authentication Failed" while Save As Concurrent Output in the Internet Explorer

2012-10-07T10:45:00.000+04:00

How to solve Oracle Apps "Authentication Failed" error while saving a concurrent output in IE

Got the error "Authentication Failed" when i try to save a concurrent output on my computer.
The saved concurrent request output does not contain anything other than "Authentication Failed"

Solution

1. From Internet Explorer, select Tools > Interent Options
2. On the General tab, in the "Temporary Internet files" select the Settings
For IE 8 and Above: Click on the Settings button under Browsing history
3. Change "Check for newer versions of stored pages:" to "Automatically"
4. Save changes

Using TOAD for Oracle on Windows 8 64bit

2012-09-19T19:46:00.000+04:00

Update: Please note that TOAD 64 Bit is Now available for Windows 8 64 Bit Version. You can install it along with Oracle Client 64 Bit. It will work smoothly.

Today I have installed Oracle 10gR2 32 bit Client and TOAD for Oracle 11.5.0.56 on my Windows 8 laptop and when I try to connect to database it gave the error :

can't initialize oci.error -1

So, Followed the below workaround to resolve this issue and I am able to connect to databases.

1. Go to the folder where Toad is installed.
2. Right Click on the Toad.exe and select Compatibility Tab
3. Select the check box "Run this program in compatibility mode for"
4. Select Windows XP (Service Pack 3) in the list box below
5. Save the settings by clicking OK

Now open the Toad and I am able to connect to the databases.successfully

Note the following:

Oracle Client Installed Must be 32 bit.
Toad is running in 32 bit environment.
Windows 8 is 64 bit

How to resolve MRP stuck issues on a physical standby database

2012-04-23T22:59:00.001+04:00

How to resolve MRP stuck issues on a physical standby database

On my standby database, one day suddenly redo apply stopped and log files are showing the following error messages. On investigating, found that MRP process not running and doesn't apply more logs

ORA-16766: Redo Apply unexpectedly offline

ORA-00332: archived log is too small - may be incompletely archived

ORA-00334: archived log: <Archive log file name>

MRP stuck issues on a physical standby database

MRP0: Background Media Recovery terminated with error 332

Environment:

10g R2 Single Instance Database with Dataguard Broker on Linux

I have performed the following activities and was able to restart the redo log apply

Solution:

1. Verify the size & checksum of the reported archive log between primary and standby database. If they are different, then copy the archive log from primary to standby. Can use md5sum to verify the checksum.

If the archive log not available you can restore from rman backup as follows

connect target / catalog=username/pwd@catdb

run {
restore archivelog from logseq=340 until logseq=340 thread=1;
}

2. Once the file is copied, restart the redo apply service.

Stop the managed recovery and start the manual recovery.

DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='LOG-APPLY-OFF';
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='ONLINE';

3. Verify the redolog apply started

ps –ef | grep mrp

Adding Standby Redo Log and Resolving Data guard Error

2011-12-22T10:38:00.000+04:00

ORA-16801: redo transport-related property is inconsistent with database Settings

Data Guard Setup: Primary with Two Standbys

DB Version: 10.2.0.4.0 64Bit

OS: Enterprise Linux 5.3 64Bit

Data Guard Broker: Yes

Redo Transport Mode: ASYNC

Second standby database created using rman duplicate command. Started the managed recovery process and logs are started applying. Data Guard broker enabled for this database. While enabling & verifying the configuration for this database, I got the following error.

ORA-16801: redo transport-related property is inconsistent with database setting
ORA-16715: redo transport-related property LogXptMode of standby database "stby2" is inconsistent

Note: I tried to add Standby Redo Log (SRL) immediately after creating standby but before starting the managed recovery, but I got the error ORA-301 signalled during: ALTER DATABASE ADD STANDBY LOGFILE GROUP and not able to add the standby redo logs.

I performed the following steps to resolve this issue.

1. Stop the redo apply on the standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

2. Create the Standby Redo Log on standby

a) The size of the standby redo log files must match the size of primary database online redo log files. Find the size of the primary database online redo log files. Number of Standby Redo Logs should be at least one greater than number online redo logsSQL> select bytes from v$log;
BYTES

----------

52428800

52428800
Here the size of my online redo log files is 50M.

b) Use the following query to find the details of the log files in the primary database.

SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER

---------- ------- --------------------------------------------------

1 ONLINE /u03/oradata/prod_redo01a.log

1 ONLINE /u03/oradata/prod_redo01b.log

2 ONLINE /u03/oradata/prod_redo02a.log

2 ONLINE /u03/oradata/prod_redo02b.log

3 ONLINE /u03/oradata/prod_redo03a.log

3 ONLINE /u03/oradata/prod_redo03b.log

4 STANDBY /u03/oradata/prod_redo04a.log

4 STANDBY /u03/oradata/prod_redo04b.log

5 STANDBY /u03/oradata/prod_redo05a.log

5 STANDBY /u03/oradata/prod_redo05b.log

6 STANDBY /u03/oradata/prod_redo06a.log

GROUP# TYPE MEMBER

---------- ------- --------------------------------------------------

6 STANDBY /u03/oradata/prod_redo06b.log

7 STANDBY /u03/oradata/prod_redo07a.log

7 STANDBY /u03/oradata/prod_redo07b.log

From above output you can see that I already have standby redo logs on primary.

c) Now I have created the standby redo log files in the standby database.alter database add standby logfile group 4 ('/u03/oradata/prod_redo04a.log’, '/u03/oradata/prod_redo04b.log’) size 50M ;

alter database add standby logfile group 5 ('/u03/oradata/prod_redo05a.log’, ‘/u03/oradata/oas2/prod_redo05b.log’) size 50M;

alter database add standby logfile group 6 ('/u03/oradata/oas2/prod_redo06a.log’, ‘/u03/oradata/oas2/prod_redo06b.log’) size 50M;

alter database add standby logfile group 7 ('/u03/oradata/oas2/prod_redo07a.log’, ‘/u03/oradata/oas2/prod_redo07b.log’) size 50M;

3. Start the log apply

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

4. Enable second standby (SSTD)

DGMGRL> enable database stdby2;

5. Verify the configuration

DGMGRL> show configuration;