Ошибка ora 29273 что это

Hey, today I’m going to talk about the oracle ora-29273 error. This error occurs when we run the utl_http.request package, mainly to access a microservice or webservice. This makes a request to a server

Let’s see an example, If we execute a select against a web, of which we don’t have loaded its certificate in the wallet of the database, we can get an error like this;

Oracle error ORA-29273 is a generic exception. You should examine the error stack messages displayed or use get_detailed_sqlerrm. Once you have determined the original error you should fix it.

Checklist to fix ORA-29273 error

It is important to check the following points. This will eliminate most of the errors that cause ORA 29273. We need to do this because the documentation itself says it is not the root cause;

1 Check the connectivity

We must be sure that we get to where we want to be (We have network access). The most basic test to do is to Ping;

ping sql1.wordpress.com

If at this point we do not reach this point we should check the network part.

2 Check ACLs

Just as firewalls block or allow access to other IPs and ports, Oracle implements the same through ACLs.

The Access Control List (ACL) identifies which user, inside the database, can access IPs outside.

You can check the list of open accesses with the following query

column acl format a50
column principal format a15
column privilege format a30
column acl_owner format a10
column host format a45
select A.HOST,  b.PRINCIPAL, b.privilege
  from DBA_NETWORK_ACLS a, DBA_NETWORK_ACL_PRIVILEGES b
  where a.aclid = b.aclid
  and a.host =   'sql1.wordpress.com'
order by 1 asc nulls first
/

If you need to give permissions to a user to a certain IP and port or port range you can do it with this procedure.

--
-- Open access to sql1.wordpress.com for range port from 1 up to 9999
--
BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'sql1.wordpress.com',
    lower_port => 1,
    upper_port => 9999,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'user',
                              principal_type => xs_acl.ptype_db));
END;
/

3 Check the Wallet

Sometimes we can receive an ora 29273 when we execute an sql that uses a digital certificate. An example of this case would be the following;

A certificate validation failure. We have to make sure that we have the corresponding certificate loaded in the wallet, usually called ewallet.p12, that we are using.

You can see the content of the wallet with the following command (you must use wallet password);

orapki wallet display -wallet /etc/oracle -pwd pass

If you don’t have it, you must download it from the site and install it.

orapki wallet add -wallet /etc/oracle -trusted_cert -cert “sql1.wordpress.com.cer” -pwd pass

HTH – Antonio NAVARRO

< GLOGIN, Configuration File For SQLPLUS

> Oracle 21c On Premise Released

I’m using the utl_http package to make HTTP GET requests to an IIS site on the same server (local) as Oracle. Sometimes it works and I get the response, but more often than not it hangs for about 15 seconds and then I get this error:

ORA-29273: HTTP request failed ORA-06512: at «SYS.UTL_HTTP», line 1722 ORA-29263: HTTP protocol error

As a test, I’ve got a small static text file in the IIS site, so this is how I’m testing it:

select utl_http.request('http://domain.com/test.txt') from dual

I get the same problem if I run it in Oracle Apex instead of direct on the db.

The other thing I’ve tried is to create a package of my own that does the HTTP request using the long utl_htp.begin_request() method, instead of the utl_http.request() shortcut. This gives the exact same problem (works sometimes but errors mostly — same error).

The pattern I’m seeing is if I wait a while and then try, it works for the first 2-10 times, and then begins erroring. When it does work, I get the response instantly and when it errors, there is always the delay before the error.

If I request the text file URL (or any other resource in the site) using a remote web browser then I get the correct response every time.

I have tried setting a timeout like below but it doesn’t have any effect. For example instead of timing out after 3 seconds it continues for 10 or 15 seconds before the error is shown.

UTL_HTTP.set_transfer_timeout(3);

I think I can rule out ACL because it works sometimes.

Does anyone know what might cause this behaviour?

 Доброго дня всем,

на днях столкнулся с проблемой, с Oracle Database 11g (11.2.0.4), необходимо было делать запросы на внешний URL с https. 

Как обычно настраивается такое взаимодействие:

1) Необходимо прописать acl

2) Настроить Oracle wallet

1) Настраиваем acl

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'https.xml', 
    description  => 'ACL to grant access to https',
    principal    => 'APEX_050100',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,



  


                       
  



    end_date     => NULL);
  COMMIT;
END;
/
BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'https.xml',
    host        => 'site_with_https.com', 
    lower_port  => 443,
    upper_port  => NULL);



  


                       
  



  COMMIT;
END;
/

2) Настраиваем  wallet 

Тут лучше всего подойдет статья https://oracle-base.com/articles/misc/utl_http-and-ssl

Теперь к сути проблемы, настроил продуктивный сервер, там все нормально, соединение устанавливается успешно. А на препроде ошибка ORA-29273 и ORA-29259:

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.