May 2008 – Persistent Storage Solutions

Oracle Database Recovery Details

Published on May 29, 2008June 26, 2008 by Advait5 Comments

Recovery Fundamentals:

This post is to give you information about various recovery fundamental details and how recovery works.

We will start by looking at various SCNs and where they are stored.

There are 3 SCNs basically in control file

Checkpoint SCN
Stop SCN
Thread checkpoint SCN

Checkpoint SCN is the datafile checkpoint SCN when checkpoint happens for datafile. This checkpoint SCN is recorded in datafile header as well.

Stop SCN is the SCN which gets recoreded in control file when datafile is taken in begin backup mode or when datafile is taken offline. This is the checkpoint at a point when datafile header is freezed.

Thread Checkpoint SCN is the one related to online redo log files. This SCN gets generated when ever transaction get recoreded in online redo log file.

When we shut down database with normal or immediate option, all these SCN are synchronized and made equal.

Lets take a quick example:

1) System checkpoint SCN in controlfile

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
——————
3700901
2) Datafile checkpoint SCN in controlfile

SQL> select name, checkpoint_change# from v$datafile
2 where name like ‘%htmldb%’;

NAME
——————————————————————————–
CHECKPOINT_CHANGE#
——————
/dy/oracle/product/db10g/dbf/htmldb01.dbf
3700901

3) Stop SCN in control file

SQL> select name, last_change# from v$datafile
2 where name like ‘%htmldb%’;

NAME
——————————————————————————–
LAST_CHANGE#
————
/dy/oracle/product/db10g/dbf/htmldb01.dbf

4) Start SCN in datafile header

SQL> select name, checkpoint_change# from v$datafile_header
2 where name like ‘%htmldb%’;

NAME
——————————————————————————–
CHECKPOINT_CHANGE#
——————
/dy/oracle/product/db10g/dbf/htmldb01.dbf
3700901

Shut down the database now and start in mount mode

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 243276104 bytes
Database Buffers 822083584 bytes
Redo Buffers 6397952 bytes
Database mounted.
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
——————
3722204

SQL> select name, checkpoint_change# , last_change# from v$datafile
2 where name like ‘%htmldb%’;

NAME
——————————————————————————–
CHECKPOINT_CHANGE# LAST_CHANGE#
—————— ————
/dy/oracle/product/db10g/dbf/htmldb01.dbf
3722204 3722204

All these SCN values are coming from control file. Here you can see that last_change# from v$datafile was showing NULL. But when we shut down the database this value got updated to same as checkpoint_change#. This last_change# is the stop SCN and checkpoint_change# is the start SCN. So when we shutdown the database it run a checkpoint and makes start SCN = stop SCN.

Lets check the SCN in datafile header

SQL> select name, checkpoint_change# from v$datafile
2 where name like ‘%htmldb%’;

NAME
——————————————————————————–
CHECKPOINT_CHANGE#
——————
/dy/oracle/product/db10g/dbf/htmldb01.dbf
3722204
So here we see that datafile header is having same checkpoint # as system checkpoint number.

How oracle decides whethere recovery is required?

When database is started, Oracle checks the system SCN stored in control file and datafiles header. It compared system SCN which each datafile header and it those matches, then next it checks the start SCN and stop SCN in datafile headers, if those are also same then it will open the database else it as for recovery.
Also as soon as we open the database the last_change# in v$datafile_header will be set to NULL again.

Now shutdown the database with abort option.

SQL> shut abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

CHECKPOINT_CHANGE#
——————
3722206

So we can see thet system checkpoint # is 3722206

SQL> select name, checkpoint_change# , last_change# from v$datafile
2 where name like ‘%htmldb%’;

NAME
——————————————————————————–
CHECKPOINT_CHANGE# LAST_CHANGE#
—————— ————
/dy/oracle/product/db10g/dbf/htmldb01.dbf
3722206
Here you can see that datafile header checkpoint SCN is also 3722206, but stop SCN # in controlfile is NULL. If shutdown checkpoint would have happened, then it would have updated the stop SCN for controlfile. But since we used “shut abort”, no checkpoint happened during shutdown. This situation is called “crash recovery”. Here the start SCN of datafile header and stop SCN of datafile header are not matching. This kind of situation is automatically taken care by Oracle. When you open the database, oracle automatically applies the transaction from redo log files and undo tablespace and it will recover the database. Problem happens when system SCN # does not match with datafile header start SCN. This is called “instance recovery”.

During start of database Stop SCN = NULL => Needs crash recovery
During Start of database DATAFILE HEADER START SCN != SYSTEM SCN in control file => Media recovery

When doing media recover we can have 2 situations

1) Datafile header SCN is less then datafile SCN stored in control file.

So when you open the database, Oracle checks the SCN number of datafile present in datafile header and control file. If the SCN matches it will open the datafile, else it will ask for recovery. Now when it ask for recovery, it will check the start SCN of datafile in datafile header. From this SCN onwards it needs recovery. So all the logs having this SCN number and beyond is required for recovery.

2) Datafile header SCN is more then datafile SCN stored in control file.

This kind of situation happens when you use backup control file or when you are recovering using “Backup controlfile”. In such situation since datafile header SCN is higher then control file, Oracle really doesn’t know till what SCN to recover. So you tell Oracle that you are using a “backup controlfile” and that you will tell it when to stop applying redo by replying “cancel.” When Oracle starts recovery, it looks at the datafiles to know the last time a checkpoint was performed on the datafile. Oracle now knows to start applying recovery to the datafile for all SCNs after the SCN in the datafile header. But Oracle does not know when to stop, and eventually, Oracle applies recovery in all of your archived redo logs. You can then tell Oracle to use the redo in the online redo logs. Oracle will ask you where to find more redo. At this point, you tell it to quit applying redo by replying CANCEL.

Once we open in reset logs mode, SCN numbers are synchronized in datafiles and controlfiles and redo sequence numbers are reset to 1.

References:

http://jenniferlinca.wordpress.com/2008/02/08/what-is-the-scn/#comments

Blogged with the Flock Browser

Tags: oracle9i recovery, database recovery, database SCN, how recovery works

Excess redo log generation during Hot Backup – Oracle 9i

Published on May 27, 2008 by Advait24 Comments

Many of you must have heard or experienced that while taking hot backup of database LGWR process writes aggressively. Meaning that more redo data has been written to redo log file and consecutively more archive logs gets generated.

Here is the common misconception we have in our mind. If some one ask, why excessive redo logs and archive logs are getting generated when we start a hot backup of database ?? Quickly we answer .. Its simple, when we put tablespace in hot backup mode, Oracle will take a check point of tablespace and data files belonging to this tablespace will be freezed. Any user activity happening on objects belonging to this tablespace wont write data to these datafiles, instead it will write data to redo log files. So obviously there will be more redo log file generation.

Well, to some extent this is COMPLETELY WRONG !!!

I will straight way come to the point and explain you what happens when we put the tablespace in hot backup mode.

Your first assumption that datafiles belonging to the tablespace in hot backup mode is freezed is wrong. Datafiles are not freezed, only the datafile headers will be freezed !! So simply imagine that when you put the tablespace in backup mode, Oracle will take a checkpoint and update the datafile headers with checkpoint SCN and there after it is freezed until we take tablespace out of backup mode.

Other datafile (other then header part) remains as normal and data changes happens continuously to this datafile.

Now you may want to ask me “do I mean to say that datafiles gets updated continuously even when we are coping the same to backup location ?”. The answer is YES. Never think that the datafile you are coping is “Consistent”. No, datafiles gets changed continuously !!!

You might want to ask couple of more questions then.

1) If we say that backup file is not consistent and changes continuously, then how come Oracle is able to recover the database when we restore that datafile?

2) If the data changes are anyway happening continuously on data files, then why there is excess redo log generation ?

Thats it !! don’t ask me more then this. Let me explain answers to these questions.

Consider a typical case, where an Oracle database is installed on Linux platform. The standard Oracle block size if 8K and lets say that OS level data block size is 512K. Now when we put the tablespace in “Begin Backup” mode checkpoint has happened and datafile header is freezed. You found which are the files related to this tablespace and started copying using OS command. Now when you copy a datafile using OS command it is going to copy as per OS block size. Lets say when you start copying it gave 8 blocks to you to copy – that means you are copying 4K (512K X 4) to backup location. That means you are copying half of Oracle block to backup location. Now this process of copy can be preempted by Server CPU depending on load. Lets say when you started copying after copy of those 8 block (4K, half of Oracle block), your process get preempted by CPU and it has allocated CPU time to some other important process. Mean while DBWR process changes that block that you have copied halfway (since datafile is not freezed and only header is freezed, continuous updates can happen to datafile).

After a while CPU returns back and gives you next 8 blocks to copy (rest of the halk Oracle block). Now here is the problem !!! we copied half of the oracle block taken at time T0 and another half taken at time T1 and in-between the data block got changed. Does this sounds consistent ? Not to me !! Such type of block is called “Fractured Block”.

Well, since Oracle copies files like this it should do some thing, so that during recovery it wont face any problem.

Usually in case of a normal tablespace (which is not in begin backup mode), when a transaction happens oracle generates redo information and puts in redo log file. This is the bare minimum information that oracle generates in order to redo the information. It does not copy the complete block. Where as in case of begin backup mode, if a transaction happens and changes any block FOR THE FIST TIME, oracle copies the complete block to redo log file. This happens only during first time. If subsequent transaction updates the same block again, oracle will not copy the complete block to redo, instead it will generate minimum information to redo the changes. Now because oracle has to copy the complete block when it changes for the first time in begin backup mode, we say that excess redo gets generated when we put tablespace in begin backup mode.

Question arises, why Oracle has to copy the complete block to redo log files. As you have seen above that during copy of datafile, there can be many fractured blocks, and during restore and recovery its going to put those block back and try to recover. Now assume that block is fractured and oracle has minimum information that it generates in the redo. Under such condition it wont be possible for Oracle to recover such blocks. So instead Oracle just copies the entire block back from redo log files to datafiles during recovery process. This will make the datafile consistent. So recovery process is very important which takes care of all fractured blocks and makes it possible to recover a database.

I hope this explains above 2 questions.

Now you can easily explain why hot backup is not possible if database is in NOARCHIVELOG mode.

When you take a backup using RMAN, it does not generate excessive redo logs. The reason is simple. RMAN is intelligent. It does not use OS block for copying, instead it uses oracle blocks for copying datafiles so the files are consistent.

Hope this helps !!

Changing SMTP port in Oracle Collabsuite 10g

Published on May 23, 2008May 23, 2008 by AdvaitLeave a comment

Some times we encounter a situation where we need to change the SMTP E-mail port after OCS (Oracle Collabsuite) is installed. For example, you have installed collabsuite 10g with SMTP port as 30 and after installation requirement comes to change SMTP port to 25. In such situation you can follow below steps to make the changes.

Also if you are changing the port to 25, make sure to stop sendmail process as it uses port 25.

You can stop sendmail process using following steps

login to OCS host as root user
run following command

/sbin/services sendmail stop

Login to OCS host and go to apps side.
Source the instance by setting ORACLE_HOME, ORACLE_SID etc.
go to $TNS_ADMIN and change ESSMI port from 30 to 25
Start oiadmin tool and navigate to following location

Entry Management -> OracleContext -> Services -> email -> VirtualServices -> smtp -> labeledUri

Change the port here from 30 to 25 and save. Exit oidadmin
Bounce LISTENER_ES

lsnrctl stop LISTENER_ES

lsnrctl start LISTENER_ES

Bounce all services of apps tier using opmnctl

opmnctl stopall

opmnctl startall

Hope this helps !!

Uploading excel sheet using Oracle Application Express (APEX)

Published on May 21, 2008July 17, 2008 by Advait173 Comments

Introduction:

Some times we need the flexibility to upload the data to a table from excel sheet. The can be implemented very well in JSP and other application. I was having this requirement to implement the same in Oracle Application Express (APEX).

I was going though one of the blog by Ittichai, for the same and he has given a very clean steps for getting this work. I followed the steps and it worked for me. I have documented the similar steps below as per my understanding. Below are the details.

Implementing data upload using excel sheet

Following is the table detail which I want to populate from data in excel sheet.

SQL> desc data_upld
Name Null? Type
—————————————– ——– —————————-
SR_NO NUMBER
CODE_ID NOT NULL VARCHAR2(20)
CODE_CTGY NUMBER
CTGY_NAME VARCHAR2(20)
CTGY_DESC VARCHAR2(20)
ITEM_NAME VARCHAR2(100)
ITEM_DESC VARCHAR2(512)
ITEM_UNIT VARCHAR2(20)
ACTUL_COST NUMBER
DIS_PER NUMBER
SCHM_ID NUMBER

1) Created a “File Browse” item on a page and having internal name it P35_UPLOAD.

2) Created a button having internal name as “Upload“. This button is used to upload and process the data in the excel sheet.

3) Created a conditional branch when upload button is pressed, it should branch to the same page (35 in my case)

4) When we select an excel sheet using browse button and click on submit button, it will branch to same page (indirectly we are saying that its going to refresh the page). In such case even if there is no code written in the backend, APEX is going to load the excel sheet in a BLOB format into a table wwv_flow_files.

This is the internal table used by APEX. Our task is to read this table and get the required row which got inserted into this table. With each upload 1 row will get inserted into the table. One of the column of the table (BLOB_CONTENT) is BLOB where the actual excel sheet is uploaded. All other columns are metadata about excel.

When we upload an excel sheet, a random name will get generated for the file in the form FXXXXX/<file_name_you_uploaded>. You can use this to get the file details. Also when you refresh the page, it will upload the file to this table wwv_flow_files, but as soon as refresh completed and it displays the page back again, that row will get deleted again. So you have to get that row processed immediately using the code that you will write when submit button is pressed.

In my case, I want to upload the rows into data_upld table. My excel sheet content looks as shown below.

I want to upload the above 6 rows.

5) Following is the code for the same

Before using the below code, make sure you have a function hex_to_decimal in your database. The code for the same is as given below.


create or replace function hex_to_decimal
--this function is based on one by Connor McDonald
--http://www.jlcomp.demon.co.uk/faq/base_convert.html
( p_hex_str in varchar2 ) return number
is
v_dec   number;
v_hex   varchar2(16) := '0123456789ABCDEF';
begin
v_dec := 0;
for indx in 1 .. length(p_hex_str)
loop
v_dec := v_dec * 16 + instr(v_hex,upper(substr(p_hex_str,indx,1)))-1;
end loop;
return v_dec;
end hex_to_decimal;

Below is the code for uploading the excel sheet.


DECLARE
 v_blob_data       BLOB;
 v_blob_len        NUMBER;
 v_position        NUMBER;
 v_raw_chunk       RAW(10000);
 v_char      CHAR(1);
 c_chunk_len   number       := 1;
 v_line        VARCHAR2 (32767)        := NULL;
 v_data_array      wwv_flow_global.vc_arr2;
 v_rows number;
 v_sr_no number := 1;

BEGIN

delete from data_upld;

-- Read data from wwv_flow_files</span>
 select blob_content into v_blob_data
 from wwv_flow_files
 where last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER)
 and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);

v_blob_len := dbms_lob.getlength(v_blob_data);
 v_position := 1;

-- Read and convert binary to char</span>
 WHILE ( v_position <= v_blob_len ) LOOP
 v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);
 v_char :=  chr(hex_to_decimal(rawtohex(v_raw_chunk)));
 v_line := v_line || v_char;
 v_position := v_position + c_chunk_len;
 -- When a whole line is retrieved </span>
 IF v_char = CHR(10) THEN
 -- Convert comma to : to use wwv_flow_utilities </span>
 v_line := REPLACE (v_line, ',', ':');
-- Convert each column separated by : into array of data </span>
 v_data_array := wwv_flow_utilities.string_to_table (v_line);
 -- Insert data into target table </span>
 EXECUTE IMMEDIATE 'insert into DATA_UPLD (SR_NO, CODE_ID, CODE_CTGY, CTGY_NAME, CTGY_DESC, ITEM_NAME, ITEM_DESC,  ACTUL_COST, DIS_PER,SCHM_ID, ITEM_UNIT)
 values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)'
 USING
 v_sr_no,
 v_data_array(1),
 to_number(v_data_array(2)),
 v_data_array(3),
 v_data_array(4),
 v_data_array(5),
 v_data_array(6),
 to_number(v_data_array(7)),
 to_number(v_data_array(8)),
 to_number(v_data_array(9)),
 v_data_array(10);
 -- Clear out
 v_line := NULL;
 v_sr_no := v_sr_no + 1;
 END IF;

END LOOP;
 END;

Create a new PLSQL process under Processes section in APEX and put this code under the same. Make the process run when upload button is pressed.

Data in excel sheet will get loaded into table.

Hope this helps !!

Reference:

http://oraexplorer.blogspot.com/2007/11/apex-to-upload-text-file-and-write-into.html

Upgrading Developer 6i with Oracle Applications 11i

Published on May 20, 2008 by AdvaitLeave a comment

Introduction:

Some times we face problem due to older version or incorrect version of developer 6i in Oracle Applications 11i. For example I can explain my scenario, where I had to upgrade Forms Developer 6i version from Patch level 17 to Patch level 18.

I was trying to apply patch 6372396 TXK AUTOCONFIG AND TEMPLATES ROLLUP PATCH S (APRIL/MAY 2008) to one of the environment. In the pre-reqs section it ask to run txkprepatchcheck.pl script to generate the report, which will actually carry out the analysis of the techstack level in your environment and whether all the techstack level is sufficient enough to apply this patch.

In my case if gave me a report as shown in this link.

So I had to upgrade my Developer 6i version. You can get the current version of Forms Developer 6i by logging on application side and sourcing the environment and running following command

$ORACLE_HOME/bin/f60gen help=y

Upgrading Developer 6i Home:

Following are the steps performed for upgrading developer 6i from 6.0.8.26.0 (Patch 17) to 6.0.8.27.0 (Patch 18). (The steps are followed from metalink note ID 125767.1)

1) Check if you have a latest version of Oracle Jinitiator in your application.

Oracle JInitiator is now available on two streams of JDK for Oracle Applications 11i customers. Customers may continue to use JInitiator 1.1.8.x (JDK 1.1 based) or move to JInitiator 1.3.1.x (JDK 1.3 based). In some cases, migration to JInitiator 1.3.1.x requires additional technology upgrades. Please review the list of requirements in MetaLink Note 124606.1 (Upgrading Oracle JInitiator with Oracle Applications 11i) to determine feasibility of migration to JInitiator 1.3.1.x at this time. We strongly recommend you upgrade to the latest version of Oracle JInitiator certified with Applications along either stream.

If you don’t have latest version for Oracle Jinitiator, then you can upgrade the same using this link.

2) Apply Developer 6i Patch set (4948577). This is the techstack patch set and needs to be applied to 8.0.6 oracle home.

Following steps are to be done for applying the patch

Download patch 4948577 from metalink
Set ORACLE_HOME to your 8.0.6 forms oracle home
unzip the patch in you 8.0.6 oracle home
cd $ORACLE_HOME/developer6i_patch18
./patch_install.sh 2>&1 | tee patch_install_p18.log (ksh)
./patch_install.sh |& tee patch_install_p18.log (csh)
Check patch_install_p18.log for errors.
Relink Procedure Builder, Forms, Graphics and Reports:
cd $ORACLE_HOME/procbuilder60/lib; make -f ins_procbuilder.mk install
cd $ORACLE_HOME/forms60/lib; make -f ins_forms60w.mk install
cd $ORACLE_HOME/graphics60/lib; make -f ins_graphics60w.mk install
Reports has both link-time and run-time dependency with libjava.so so you need to append either one of:
$ORACLE_HOME/network/jre11/lib/linux/native_threads
$ORACLE_HOME/network/jre11/lib/i686/native_threads
in $LD_LIBRARY_PATH before linking Reports.
Please check your files under $ORACLE_HOME/network/jre11/lib to see which one of the above is appropriate on your system. The same $LD_LIBRARY_PATH should be used at run-time.

cd $ORACLE_HOME/reports60/lib; make -f ins_reports60w.mk install

3) Applying the additional patches as mentioned in metalink docs

5713544
4261542
5216496
5753922 – If your database is 9i, skip this patch as this is for database 10g
6195758
5938515

After applying the Oracle Developer 6i Patch and apps interop on UNIX platforms, you must relink several Oracle Applications executables to include Developer patch changes. The executables are f60webmx, ar60run , ar60runb, ar60rund, and are all owned by FND.

You can relink these executables by running adadmin
When the Main Menu appears select ‘Maintain Applications Files Menu‘ and then select ‘Relink Applications Program‘
Answer the questions below as follows, in order to select the individual executables for relinking.

Enter list of products to link (‘all’ for all products)[all] : fnd
Generate specific executables for each selected product [No] ? y
Relink with debug information [No] ? n

(You will then be offered a list of executables that are available for relinking)

Enter executables to relink, or enter ‘all’ [all] : f60webmx ar60run ar60runb ar60rund *

4) Download and apply the apps interop patch (4888294)

This completes the upgrade of developer 6i home from 6.0.8.26.0 (Patch 17) to 6.0.8.27.0 (Patch 18).

Run the txkprepatchcheck.pl report once again and check and you will see the report as shown by this link.

Hope this helps !!

Reference:

Metalink Note ID: 125767.1

Enabling SSL for Infra Tier – Oracle Application Server 10g

Published on May 19, 2008 by Advait1 Comment

This small post is to let you know, how to enable SSL for for infrastructure tier in oracle application server 10g. When we start the apache server for infra tier, it runs in non SSL mode by default. However we can change from non SSL to SSL by doing a small change in opmn.xml file on infra tier.

Follow below steps for enabling SSL on infra tier.

1) Go to opmn.xml on infra tier.

cd $ORACLE_HOME/opmn/conf

2) make following changes in opmn.xml file

change ssl-disabled to ssl-enabled for HTTP_Server as given below

<ias-component id=”HTTP_Server”>
<process-type id=”HTTP_Server” module-id=”OHS”>
<module-data>
<category id=”start-parameters”>
<data id=”start-mode” value=”ssl-enabled“/>
</category>
</module-data>
<process-set id=”HTTP_Server” numprocs=”1″/>
</process-type>
</ias-component>

3) Bounce HTTP Server of infra tier.

cd $ORACLE_HOME/opmn/bin

./opmnctl stopproc ias-component=HTTP_Server

./opmnctl startproc ias-component=HTTP_Server

Now you should be able to access the HTTP server on infra tier using 4443 port instead of 7777 port and using HTTPS protocol.

Example: https://ap101fam.us.oracle.com:4443/oiddas/

Hope this helps !!

Dealing with Oracle Certificate Authority – Oracle Application Server 10g

Published on May 19, 2008May 20, 2008 by Advait2 Comments

Introduction:

Digital certificates are essential to securing an enterprise infrastructure deployment. Oracle Application Server Certificate Authority generates and publishes X.509 v3 PKI certificates to support uses such as securing network connections, digital signatures, and strong user authentication methods. Key features of Oracle Application Server Certificate Authority include its easy, out-of-the-box deployment and a web-based, self service interface for certificate provisioning.

Requesting the web administrator certificate

For OCA, there is a web administrator which can manage and also approve the certificate requested by other users. Users can request either a server certificate of user certificate. Server certificate is required when a user want to enable SSL for his application deployed on server. User certificate is required for user to install the same in his browser. This user certificate can be used a an option for authentication when a user wants to login a SSL enabled application which is also a SSO enabled.

Before we get the administrator account for OCA (Oracle Certificate Authority), we need to first get the OCA certificate and install the same in out browser.

Follow below steps to get the certificate for you AS10g OCA installation.

1) Login to OCA self-service URL using https://(hostname):(port)/oca/admin

Example in my case https://ap101fam.us.oracle.com:6602/oca/admin

Remember here that port you will be using is “Oracle Certificate Authority SSL Server Authentication port” present in ORACLE_HOME/install/portlist.ini file of your infra tier. Click on “Click Here” link.

2) Enrollment form will appear next. Fill in the details as shown in example below.

Click on “Submit” button.

3) Once you submit the certificate will be issued immediately.

This is the certificate for web administrator of OCA. This will be issued by OCA to the web administrator which is going to administer the OCA in future. This is issued only once.

Status:Valid
Serial Number:4
Signature Algorithm:RSA
Issuing Authority:O=oracle,C=US
Usage:Client Authentication, Signing, Encryption
Key Size::2048
Subject DN:CN=oca_admin,O=oracle,C=IN
Not Valid Before:Saturday, May 3, 2008 5:23:41 AM PDT
Not Valid After:Sunday, May 3, 2009 5:23:41 AM PDT

Once you get this information click on “Install in Browser”.

Once you install in browser you can check if the certificate has been installed correctly or not using

Tools -> Options -> Advanced Tab -> Encryption -> View Certificate

if you are using Mozilla fire fox browser. For other browsers, use the browser specific path.

Requesting Server Certificate

Once we get the OCA administrator certificate and we became the administrator, then any user who is needs the server certificate or user certificate will have to go through our approval for the same. The request for the certificate will come to us for approval and once we approve, user can download and implement SSL using that certificate. Lets see the process of requesting the certificate and approving the same.

1) Create a certificate request in OWM

If you are opening the wallet for the first time, it will ask you to create a new wallet and create a default directory for the same.
Also you need to set password for your wallet
Once this is done it will ask you if you want to create a new certificate request. Click on Yes.
A new window will open asking for the details as shown below.

Enter all the relevant information in the above screen and click on OK.

Once a certificate is created, you can export the same or you can copy the content to notepad.

2) Submitting the request to OCA

go to https://(hostname):(port)/oca/user. Example : https://ap101fam.us.oracle.com:6602/oca/user
Click on “Request a Certificate”
Enter the certificate request you created in OWM and other details as shown below.

Click on submit. You will see the confirmation window as shown below.

3) Once you request for certificate, now you as an administrator can approve the certificate. Usually in production you have to request the certificate to some legeal certifying authority which will approve your certificate after a period of time and you will get your certificate. Here we being administrator for OCA, we can approve the certificate.

For this just go to https://(hostname):(port)/oca/admin Example : https://ap101fam.us.oracle.com:6602/oca/user
“Certificate Management” tab
You can see that the certificate request is pendng for your approval. If you dont see any request, you can search using the search textbox on the top using the request ID you got when you requested the certificate.

You can click on View Details and at the bottom, there will be button called “Approve”. Click on Approve.
You will get the confirmation window as given below.

4) Now you need to download this certificate and import into your wallet.

For this again go to https://(hostname):(port)/oca/user. Example : https://ap101fam.us.oracle.com:6603/oca/user
Server / SubCA Certificates tab and search for certificate with certificate number that is issued. Note that we have requested a certificate with a request number 4, but the issued certificate number is 5. So you have to search for certiifcate # 5.
Once you get it click on “View Details”
You should be able to see the certificate. Go down to the bottom of the page and click on “Save Certificate”.
There are 2 certificates in this. One is the CA certificate and one is the actual server certificate. The first one if the actual server certificate that you requested for and second one is the CA certificate.

5) You now have to import both server certificate and CA certificate.

For this go to OWM again and click on Operations -> Import User Certificate. It will give you 2 options 1) Paste the content 2) Upload as file.
In my case I am pasting the certificate as shown below.

When we click on OK, it might give following error
“User certificate import has failed because CA certificate does not exits. DO you want to import CA certificate?“
Click on Yes and then paste CA certificate in new Textarea.
In the bottom of OWM window you will see a success message “Your certificate has been successfully imported“.

This is how we can import the certificate that can be used for SSL configuration for your application (like EBS).

Hope this helps !!

Utility to check techstack components – Oracle Applications

Published on May 16, 2008 by AdvaitLeave a comment

There is a good utility for checking all the techstack components and there versions present in e-business application. This is applicable to both 11i and R12 environments.

There is a script $FND_TOP/patch/115/bin/txkInventory.pl on apps side which is going to fetch the versions of all components on apps side. This script can be run by giving input to $FND_TOP/patch/115/bin/TXKScript.pl script. TXKScript.pl script takes 2 mandatory arguments, one is the script to run and another is the directory for storing log file and out file. Other then these arguments you must give context file name and location and apps password as well. Also you need to give outfile where it will create report of techstack components.

(appmgr06) appmgr – -bash $ perl $FND_TOP/patch/115/bin/TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$OA_HTML/techstack_info.html
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** STDOUT = /slot06/appmgr/scmc2mq0comn/rgf/scmc2mq0/TXK/txkInventory_Fri_May_16_04_01_03_2008_stdout.log
Reportfile /slot06/appmgr/scmc2mq0comn/html/techstack_info.html generated successfully.

You can access the above report file using http://(hostname.domain):(port)/OA_HTML/techstack_info.html

The report will look like this.

The report will have following information.

Summary of environment details
Inventory of HTTP Server Node
Inventory of Forms Server Node
Inventory of Concurrent Processing Server Node
Inventory of Administration Server Node

Similarly you can check on DB side as well

(oracle06) scmc2mq0 – -bash $ perl $ORACLE_HOME/appsutil/bin/TXKScript.pl -script=$ORACLE_HOME/appsutil/bin/txkInventory.pl -txktop=$ORACLE_HOME/appsutil/temp -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$ORACLE_HOME/appsutil/temp/master_db.html

Hope this helps !!

One Good Year …

Published on May 14, 2008May 20, 2008 by Advait3 Comments

First of all, I would like to thank all the visitors of this blog for there support and co-operation extended and making this blog a success !!! THANKS A TON !!!

I started this blog exactly an year a go… with an intention to keep notes of the things that I do, so that some time down the line, this might help.

I never thought it will grow this big and will serve so many people around globe…

During this year, I have learned many new things, and posted many critical and general things about Oracle technology. I never limited the information to just Oracle Database, but explored other technologies of Oracle as well. I tried to provide uncommon things which one may not be able to see on any other sites. I also tried to consolidate the things which are present at different places (giving the references) so that a visitor should get everything he/she wanted at one place.

Most of the steps and procedure mentioned in my post are executed by me and posted only after a successful try. Therefore all visitors can definitely trust the reliability of any post on this site.

Last thing, I would like to present the statistics for this blog. In 1 year, I posted 72 Posts, 18 Pages distributed among 17 categories.

Below graph shows the month wise status for the year.

Total number of hits in 1 year is close to 45000 ( 44,820 Exact)

Thanks all again for the support and I look forward for more knowledge sharing and growth in the upcoming years. I will be posting more and more information about Oracle technology. Please let me know if you have any feedback for me.

THANKS !!

Start/Stop LISTENER_ES as Oracle User in Oracle Collabsuite 10g

Published on May 13, 2008 by AdvaitLeave a comment

Introduction

When we install Oracle Collabsuite 10g, we can administer all the services using oracle user (user who has installed Oracle Collabsuite 10g) except one service, which is LISTENER_ES.

LISTENER_ES is a apps side listener which is mainly responsible for bringing up SMTP and IMAP ports. All the ports for apps tier are defined in ORACLE_HOME/network/admin/listener.ora file present on apps side.

We always have to start LISTENER_ES as a root user and we cannot by default start this using oracle user. However, there is a way to enable start/stop using oracle user. This post explains the same.

Follow below steps to enable oracle to start/stop LISTENER_ES

Login to OCS host as root user
Go to apps tier and Source the environment using .env file
Stop the LISTENER_ES as root user using following command

$ORACLE_HOME/bin/tnslsnr LISTENER_ES stop &

As a root user run the below commands

cd $ORACLE_HOME/bin
chown root tnslsnr
chmod 6751 tnslsnr

Exit and login to OCS host as OH owner, source the instance on apps side using.env file and Start/stop the listener

lsnrctl start LISTENER_ES
lsnrctl stop LISTENER_ES

Hope this helps !!

References:

Metalink Note ID: 205298.1