Oracle Point, Oracle Life.

Most Popular Posts

August 18, 2010

Configuring Wallet Manager to enable HTTPS connect

Filed under: OraclePoint, My Reference — R.Wang @ 6:39 pm

Recently , one of request has been raised in our organization. That is HTTPS site access through oracle PL/SQL package by using UTL_HTTP. Our developers got success in accessing un-secured HTTP site, but failed in accessing Secured Socket Layer protocol (SSL) HTTPS site.

As per Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) , “An Oracle wallet must be set before accessing Web servers over HTTPS by using SET_WALLET procedure”. The general syntax of SET_WALLET is listed below.

UTL_HTTP.SET_WALLET (
   path      IN VARCHAR2,
   password  IN VARCHAR2 DEFAULT NULL);

Here, parameter “path” is the directory path that contains the Oracle Wallet. And, parameter “password” is the password needed to open wallet.

Before using SET_WALLET procedure, a new wallet need to be created, if there is not, and the HTTPS site certificate need to imported. To complete that, we follow the steps below.

Step 1: Obtaining the correct certificate from the HTTPS site that we are connecting to

a) Access target https URL in browser

b) Select “Properties” from menu

c) Click button “Certificates” in “Properties” window, and highlight the top most certificate in tab of “Certification Path”, and then click button “View Certificate”, and eventually click button “Copy to File…” in tab of “Details”

d) Copy certificate file over network to oracle server

Note: Be sure to highlight “the top most” certificate. Otherwise, you’ll experience    failure.

https_certificate

Step 2: Creating wallet on oracle server side because we don’t have

a) Start the wallet manager on oracle server with command “owm”

b) Create new wallet if there is not

c) Save this new wallet to the location you want

Step 3: Importing certificate we got in step 1

Within oracle wallet manager, go to the “operations” tab and click on ‘Import Trusted Certificate’, choose the option to select newly-created certificate file that was created in step 1. After that, save wallet again.

Step 4: Testing the access from SQL*PLUS

a) Start a SQL*PLUS session as any user that has access to the UTL_HTTP package

b) Issue the following command to test.

SQL> select utl_http.request(’https://secure.aizan.biz/Web/Common/XmlAutomation/Upload.aspx’, null, ‘file:/etc/ORACLE/WALLETS/oracle’,'doug1las’) from dual;

OUTPUT:

UTL_HTTP.REQUEST(’HTTPS://SECURE.AIZAN.BIZ/WEB/COMMON/XMLAUTOMATION/UPLOAD.ASPX’
——————————————————————————–
<?xml version="1.0" encoding="utf-8" ?>
<Aizan_XMLAutomation_Response>
<Error>
<Body>
Empty or wrong request XML
</Body>
</Error>
</Aizan_XMLAutomation_Response>

Done!

bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark

Popularity: 2% [?]

Tags:none

July 28, 2010

Upgrade Oracle Database from 10g to 11g with Data Pump

Filed under: OraclePoint, Oracle Case Study, My Reference — R.Wang @ 1:45 pm

As described in “Oracle Database Upgrade Guide 11g Release 1 (11.1)”, there are three upgrade methods offered while you want to upgrade database from 10g to 11g.

  1. Database Upgrade Assistant (DBUA)
  2. Manual Upgrade (Oracle provided scripts)
  3. Export/Import (exp/imp, or expdp/impdp)

Besides benefits mentioned in above upgrade document, I’m thinking we are beneficial from using export/import approach. The reasons we decided using this method are:

  • we want to set up identical testing databases on development server first
  • we want to keep original production databases unchanged anyway for safety reason

With this approach, we first installed Oracle 11g software on development server and created empty database by using provided standard template New_Database.dbt, which could be found at $ORACLE_HOME/assistants/dbca/templates. After that, we followed the following steps to move data from 10g database to newly created 11g database.

Step 1: Pre-create tablespaces on target 11g database

Step 2: Export full database of source 10g database

expdp system@database directory=DATA_PUMP_DIR full=y dumpfile=export.dmp logfile=export.log

Step 3: Copy dumpfile over network to development server

Step 4: Grant IMPORT_FULL_DATABASE system privilege to user SYSTEM of target 11g database

Step 5: Import full database to target 11g database

Note: If you have co-existing oracle home/version on development server, please make sure switching to Oracle 11g environment, which will assure to use data pump at 11g version to import data.

Impdp system@database directory=DATA_PUMP_DIR full=y dumpfile=export.dmp logfile=import.log

That’s it. Done!

bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark

Popularity: 8% [?]

Tags:, , ,

Page: 1 | 2 | 3 | 4 | ... | 57
 

Windows Live Translator

Google