PostgreSQL function similar to Oracle validate_conversion function

 

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;  

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

 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 download and setup Oracle Cloud Control agent software for provisioning

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

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

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;

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

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.

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;

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

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:

1. Login to E-Business Suite as System Administrator
2. Select the "User Management" responsibility
3. Click 'Users' in the right hand side menu
4. Enter 'User Name' and click 'Go'
5. Click 'Update' on the correct user
6. Click 'Assign Roles' button
7. 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
8. Check the box next to the appropriate role and click 'Select'
9. 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.

Shell Script to Monitor Data Guard Status

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

Using TOAD for Oracle on Windows 8 64bit

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

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

Changing OC4J Password in Oracle Application Server 10g R3

We have Oracle Application Server webCenter Suite(10.1.3.1) installed on Linux Platform. Today i was trying to change the password of oc4jadmin user as describedin the Oracle Documentation.



- I logged in to the EM console as o44jadmin user and clicked on the 'setup' link top of the console page.

- Clikcked the 'Password' link and entered old and new passwords in the text box provided.

- Clicked Apply button to save.


The password changed sucessfully message shown at the top of the page.


Now here goes the worst part! I am not able to open any of the OC4J instance on this server. When i try to open any of the OC4j instance i am getting the following error :

"Unable to make a connection to OC4J instance [OC4J instance name] on Application Server [AS instance name]. A common cause for this failure is an authentication error. The administrator password for each OC4J instance in the Cluster must be the same as the administrator password for the OC4J instance onwhich Application Server Control is running."


So you can not simply change the password of OC4jadmin. Following is the method how i did to change the password.


- Login to the EM console as oc4jadmin.

- Click on the OC4J instance

- Click on the Administration tab of the OC4J instance

- Click on the security providers on the table

- Click on the instance level security in security providers page

- click Realms on the instance level security page

- In the jazn.com row of the Results table, click the number (for example, 3) in the Users column

- Click oc4jadmin to modify the oc4jadmin user account.

- Enter the old and new passwords on the text boxes provided.

- Click apply to save changes.

Do this for all your OC4J instance. The password must be same across all OC4J instances.
Once you are done with OC4J instances, go to the cluster topology page and click on the setup link and change the password of oc4jadmin. This oc4jadmin password must be same as the password of the OC4J instances.