Multiple Database Block Sizes and the Buffer Cache

In oracle 10g we can have multiple block sizes at the same time. When a tablespace is created we can assign a block size for the objects that will be created in that tablespace.

The DB_BLOCK_SIZE parameter in your initialization parameter file determines the size of your standard block size in the database and frequently is the only block size for the entire database.

The DB_CACHE_SIZE parameter in your initialization parameter file specifies the size (in bytes) of the cache of the standard block sized buffers. Notice that you don’t set the number of database buffers; rather, you specify the size of the buffer cache itself in the DB_CACHE_SIZE parameter.

You can have up to five different database block sizes in your databases. That is, you can create your tablespaces with any one of the five allowable database block sizes.

But before you use non standard block size, you have to define the cache size for these non standard block size. We have a paramter called DB_nK_CACHE_SIZE for setting the cache size for non standard block size.

The new init.ora parameters that allow you to use non-default block sizes are:

DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

Another classification for buffer cache is depending on the algorithm used to keep the contents into the cache. We have basically 3 types in this catagory.

1) DB_KEEP_CACHE_SIZE
2) DB_RECYCLE_CACHE_SIZE
3) DB_CACHE_SIZE

DB_KEEP_CACHE_SIZE is where the object are always present when they are loaded. The objects which qualifies for this cache are those which are very frquently accessed and which has to be retained in memory. For example, frquently used small lookup tables. This cache is a subset of default cache defined by parameter DB_CACHE_SIZE. For any database we need to have DB_CACHE_SIZE set.

DB_RECYCLE_CACHE_SIZE is where you dont want to store the object. You want to clear off the object from cache as soon as it is used. You have to be careful while using this, since this may incure performance hit in case you allocated a frequently used object to this cache.

DB_CACHE_SIZE is the size for default cache.

it is important to note that the init.ora parameters and functionality regarding the keep and recycle buffer pools has changed between Oracle8i and Oracle9i. Those changes are
summarized in the table below:

Version Of Oracle Init.ora parameters Functionality
Oracle8i                             BUFFER_POOL_KEEP = <buffers>                Subsets of the data
BUFFER_POOL_RECYCLE = <buffers>        buffer cache

Oracle9i and 10g              DB_KEEP_CACHE_SIZE = <size>                   Independent of the
DB_RECYCLE_CACHE_SIZE = <size>           data buffer cache

To specify the use of the keep, recycle or default buffer pools, you can use the storage clause of the alter table statement:

alter table <table_name> storage (buffer pool keep);
alter table <table_name> storage (buffer pool recycle);
alter table <table_name> storage (buffer pool default);

Note: The keep and recycle buffer pools are only available for the standard
block size. Non-standard block-size caches have a single default pool.

So again back to non standard cache size. Lets say the default block size is 8K and you want to create 1 more block size for you future tablespaces. In that case you have to
assign the buffer cache for those block size in the memory. Remember, when
you create a non standard block sizes, the memory (cache size) allocation for these block
size will be taken again from physical memory RAM and hence the RAM consumption with go up. This memory wont be allocated from existing db_cache_size.

Example

SQL> show parameters db_cache_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_cache_size                        big integer 200M

Now we have 200M set for DB_CACHE_SIZE and db_keep_cache_size and db_recycle_cache_size is not set.

SQL> show parameter db_keep_cache_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_keep_cache_size                   big integer 0

SQL> show parameter db_recycle_cache_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recycle_cache_size                big integer 0

Also we can see the size of buffer cache using show sga

SQL> show sga

Total System Global Area 1073741824 bytes
Fixed Size                  1984184 bytes
Variable Size             750786888 bytes
Database Buffers 209715200 bytes
Redo Buffers                6397952 bytes
Lets now try to create a tablespace with 4K block size.

SQL> create tablespace test_tbs4k datafile ‘/dy/oracle/product/db10g/dbf/test_tbs4k_1.dbf’ size 100M blocksize 4K;
create tablespace test_tbs4k datafile ‘/dy/oracle/product/db10g/dbf/test_tbs4k_1.dbf’ size 100M blocksize 4K
*
ERROR at line 1:
ORA-29339: tablespace block size 4096 does not match configured block sizes

Oracle in not intelligent enough to translate the block size of 4k into 8k buffer size. Because it cannot load the content of this datafile created for this tablespace having block size of 4k into buffer buffer of 8k.

So we need to create a buffer of 4K block size, after that only we can create a tablespace for 4k block size.

If we see the parameter db_4k_cache_size is not set.

SQL> show parameters db_4k_cache_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_4k_cache_size                     big integer 0

SQL> alter system set db_4k_cache_size = 100M;

System altered.

SQL> show parameter db_4k_cache_size;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_4k_cache_size                     big integer 100M
SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_cache_size                        big integer 200M
SQL> show sga

Total System Global Area 1073741824 bytes
Fixed Size                  1984184 bytes
Variable Size             750786888 bytes
Database Buffers 314572800 bytes
Redo Buffers                6397952 bytes
SQL>

If we see the above stats, it clearly shows tghat db_cache_size has not reduced, but
database buffer size has increased not to 300M. Now we can create a tablespace for 4K block size

SQL> create tablespace test_tbs4k datafile ‘/dy/oracle/product/db10g/dbf/test_tbs4k_1.dbf’ size 100M blocksize 4K;

Tablespace created.

SQL>

You can get the information about your database buffer cache from a view v$buffer_pool

SQL> select name, block_size, current_size from v$buffer_pool;

NAME                 BLOCK_SIZE CURRENT_SIZE
——————– ———- ————
DEFAULT                    8192          200
DEFAULT                    4096          100

You can get more stats and information on you buffer pool using the view v$buffer_pool_statistics.

 

Installing Oracle Database 10g R2 on Solaris 5.9

Introduction

To being with installation we need to follow some pre-reqs docs regarding operating system requirements, Checking disk space, RAM, correct OS patches and correct Oracle software dump. Here we will see the installation steps of Oracle Database 10.2.0.2 on Solaris 5.9

Determining Disk and Memory Requirements

For Solaris OS you can check the OS level setting as given below. Check metalink note 169706.1 for the same.

Checking swap space

bash-2.05$ swap -s
total: 9342392k bytes allocated + 1155400k reserved = 10497792k used, 6616928k available

Checking RAM

bash-2.05$ /usr/sbin/prtconf | grep -i memory
Memory size: 16384 Megabytes
memory (driver not attached)
virtual-memory (driver not attached)

Checking OS version

bash-2.05$ uname -r
5.9

Checking Disk space

bash-2.05$ df -h .
Filesystem             size   used  avail capacity  Mounted on
/dev/vx/dsk/dg01/vol01
615G   258G   335G    44%    /dy
Performing Pre-Installation Tasks

1)  Checking Additional OS packages for Solaris

Some additional packages are required for successful installation of Oracle software. To check whether required packages are installed on your operating system use following command:

bash-2.05$ pkginfo -i SUNWlibms SUNWtoo SUNWi1cs SUNWi15cs SUNWxwfnt SUNWxwplt SUNWmfrun SUNWxwplr SUNWxwdv SUNWgcc SUNWbtool SUNWi1of SUNWhea SUNWlibm SUNWsprot SUNWuiu8
system      SUNWbtool      CCS tools bundled with SunOS
system      SUNWhea        SunOS Header Files
system      SUNWi1of       ISO-8859-1 (Latin-1) Optional Fonts
system      SUNWlibm       Forte Developer Bundled libm
system      SUNWlibms      Forte Developer Bundled shared libm
system      SUNWmfrun      Motif RunTime Kit
system      SUNWsprot      Solaris Bundled tools
system      SUNWtoo        Programming Tools
system      SUNWuiu8       Iconv modules for UTF-8 Locale
system      SUNWxwdv       X Windows System Window Drivers
system      SUNWxwfnt      X Window System platform required fonts
system      SUNWxwplt      X Window System platform software
ERROR: information for “SUNWi1cs” was not found
ERROR: information for “SUNWi15cs” was not found
ERROR: information for “SUNWxwplr” was not found
ERROR: information for “SUNWgcc” was not found

Install the missing package using command

pkgadd -d . SUNWi1cs SUNWi15cs SUNWxwplr SUNWgcc

(Note that above installation of packages must be done as a ROOT user)

2) Setting kernel parameters

Set the following values (either greater then or equal to) in /etc/system file as root user.

noexec_user_stack 1
semsys:seminfo_semmni 100
semsys:seminfo_semmns 1024
semsys:seminfo_semmsl 256
semsys:seminfo_semvmx 32767
shmsys:shminfo_shmmax 4294967295
shmsys:shminfo_shmmin 1
shmsys:shminfo_shmmni 100
shmsys:shminfo_shmseg 10

3) Reboot the system

/usr/sbin/reboot

4) Creating Oracle user

groupadd oinstall
groupadd dba
useradd -d /export/home/oracle -g oinstall -G dba -m -s /bin/ksh oracle
passwd -r files oracle

5) Check DISPLAY settings

You should check the DISPLAY setting if you are not doing the silent installation. You can do this using following command.

Check the IP address from /etc/hosts file.

export DISPLAY=(IP Address):1.0

6) Go to the dump location and execute runInstaller.

bash-2.05$ pwd
/nfs/stage/solaris/oracle/10G-R2-102010/Disk1/database
bash-2.05$ ls
doc           install       response      runInstaller  stage         welcome.html

The first screen that you see is the welcome screen. You can check the Oracle products that are installed using this screen. Also you can uninstall a product.

Once we press next…

Next screen will ask about Inventory directoy for our database. Specify the correct inventory directory (either central inventory or local inventory). The OS group can be either DBA or OINSTALL. Specify the correct value and press next.

Select the type of installation you want to do. The features for each type of installation is already provided. Press next when selected. Here we are doing Enterprise installation.

Select the location for your Oracle Home and the name. Please note that Oracle base directory should be created before proceeding further and also it should have correct permissions. Here Oracle base is /u00/oracle/product. The other directories for Oracle Home will be created by Oracle. Press next when selected the correct directory.

Oracle Universal Installer will perform the pre-install checks about the OS version, patch levels, memory parametrers etc.

You can select at this screen, whether you want to create a database or you want to install just a software on you want to configure ASM as well. Lets select database creation option and press next.

Select the type of database you want to create. In this case we will create a General purpose database.

Here you have to select the Global database name and SID. You can also select the character case and a choice to create sample schemas. Press next.

Since you are installing a single database and not a grid installation, so the choice of “Use Grid Control for Database Management” is disabled. You have to sue Database management, and you can enable EMail notifications. So that if there are some critical alerts then you can get an email notification for the same.

On “Specify Database Storage Option” you can specify the option for your file system. It can be either OS file system, or ASM or raw disk. Here we will be using OS file system.

On “Specify Backup and Recovery Option” you can specify if you need any automated backups for the environment. If you are opting for automated backups, then you need to specify the location for flash recovery area. Also you need to specify the operating system login credentials.

On this screen you need to specify the admin passwords. You need to specify passwords for SYS, SYSTEM, SYSMAN and DBSNMP. Here you have an option for having different passwords as well as same passwords for all users.

Final summary screen will just display the summary of installations that are going to happen. It will show complete product list, which is going to be installed. Press Install button.

Once you press on Install, the installation will start and it will install the products automatically. Also you can check the current action of installer by checking the log file provided at the botton of window. After the main software installation, the next screen comes the configuration screen as shown below.

Configuration screen will carry out network configuration, database configurations and installation of SQL* plus client tool. Network configuration is just configuring listener for database to take up remote connections. Database configuration assistant will come into picture when we are giving the option to create a database in “Select Configuration option” screen.

Once network configuration is done, universal installer will automatically start database configuration assistant. This will do the actual database creation. Here database name will be the one, which we specified on “Specify database configuration options” screen.

Once the installation completes an “End of Installation” screen will come. Here OUI will provide you will all the URLs for logging into Database Enterprise Manager. Here the last URL is EM URL.

That completes the installation of Oracle Database 10g R2 with creating a database using DBUA(Database Configuration Assistant).

 

Installing Oracle Apps 11i

Introduction

This post is about installing Oracle Apps 11i using rapid install. This is a very brief discription of the install document provided by Oracle. For details, you can always refer to the official docs of Oracle. I am providing the installation sequence for one of my test instance. I hope this will serve as a fast and simple docs for you to quickly understand the installation and the steps.

Basic installation of Oracle Applications 11i is divided into 3 parts.

  1. Pre-installation
  2. Installation
  3. Post-installation.

Pre-Installation

i) Checking system requirement

In pre-installation we check about

1) Software Requirement
2) CPU Requirement
3) Memory Requirement
4) Disk Space Requirement

required for installing Oracle Applications.

ii) Creating staging area

We then create a staging area where we download and extract all the required files. The staging area after extracting the software will look as shown below.

[root@ocvmrh2122 11i10_CU2_115102]# ls -rlt
total 24
drwxr-xr-x    5 root     root         4096 Oct 13  2005 oraDB
drwxr-xr-x   26 root     root         4096 Oct 13  2005 oraAppDB
drwxr-xr-x    6 root     root         4096 Oct 13  2005 oraiAS
drwxr-xr-x   10 root     root         4096 Oct 13  2005 oraApps
drwxr-xr-x    9 root     root         4096 May  7  2007 startCD

You need around 24G for staging area after extraction.

iii) Creating User Accounts

Before we start installation we need to create 2 users. One user (APPLMGR) will be the owner of middle tier and other user (ORACLE) will be the owner of database. Assign the primary owner as “oinstall” and secondary owner as “dba” for both the users.

Check the display setting before starting the installation. You can set the DISPLAY to hostname:0.0

Installation

We begin installation by running rapidwiz present in  startCD/Disk1/rapidwiz/ directory. Below are the screen shots that you will see. I have given the screen shots with my input for your easy referrence so that you can go through the same fast.

1.jpg

Welcome screen lists the database version and the technology stack components that are installed with the E-Business Suite. Click on next.

2.jpg

You can either make a new installation of upgrade an existing installation. In our case we are going to do a new installation. In case of Express Configuration you supply a few basic parameters, such as database type and name, top-level install directory, and increments for port settings. The remaining directories and mount points are supplied by Rapid Install using default values.

3.jpg

If you have previous installation saved configuration file you can give that as input. If you answer No, Rapid Install saves the configuration parameters you enter on the wizard screens in a new configuration file (config.txt) that it will use to configure your system for the new installation.

4.jpg

You can either select a single node installation of a multi-node installation. In our case we are going for a single node installation.

5.jpg

Select database type. We can either have a Vision demo database or a production database. Production database won’t have any data. Vision demo database will have test data present for our testing. If you are using Vision demo database then your database will need around 130G-140G of space. Else in case of production database space required would be 45G.

6.jpg

Set up the Oracle user and base install directory. Once you set the base install directory, all other directories will be set automatically. You can also edit the individual directories like ORACLE_HOME or db file location as per your requirement.

72.jpg

Select the type of licensing you got from Oracle. Completing a licensing screen does not constitute a license agreement. It simply registers your products as active.

8.jpg

If you select E-Business Suite price bundle then you will see this screen with some of the checkbox grayed. The products that are checked and grayed are licensed automatically as a part of the suite. The ones that are not must be registered separately as additional products — they are not part of the E-Business Suite price bundle. Place a check mark next to any additional products you have licensed and want to register.

9.jpg

Some systems require the country-specific functionality of a localized Applications product. For example, if your company operates in Canada, products such as Human Resources require additional features to accommodate the Canadian labor laws and codes that differ from those in the United States. In such situation, select the proper country. In my case there is no country specific functionality.

10.jpg

Select additional language. By default US will be selected. If you want to install any more language, then you can always select from the available list.

11.jpg

The Select Internationalization Settings screen derives information from the languages you entered on the Select Additional Languages screen. You use it to further define NLS configuration parameters.

12.jpg

Select the user and base directory for the application side installation. Once you set the base install directory, all other directories will be set automatically. You can also edit the individual directories like APPL_TOP, COMMON_TOP etc. as per your requrement.

13.jpg

Provide the domain name and the port ranges. Give the ports that are not used before as per your knowledge. Anyway the installer will check for port conflicts before it installs the application. You can even change the individual port setting as well.

14.jpg

You have now completed all the information Rapid Install needs to set up and install a single-node system. The Save Instance-specific Configuration screen asks you to save the values you have entered in the wizard in a configuration file.

15.jpg

Review pre-install checks. This will check whether all the requirements are met or not.

171.jpg

Once all the requirements are met, please proceed further to install the application.

16.jpg

Before installation it will give the summary of the techstack its going to install. Click on next.

18.jpg

You can see the progress of installation.

19.jpg

Once all the installation is done, it will show the components installed and its status.

21.jpg

Post-Installation

You can check for the post install steps from the metalink note ID 316365.1 as applicable.

Reference:

Oracle Apps 11i – Install Docs

Metalink Note ID: 316365.1

 

Enabling SSL in Oracle E-Business Suite Release 12

Overview

SSL stands for Secure Socket Layer which is protocol developed by Netscape. Data Transferred between Server & Client is secured (Encrypted).
Usually data transmitted between client machine & server (Web Server on http protocol & Forms Server on Sockets ) is clear text packets. Any one can put Packet Sniffer between Client machine & Server & can open & read all data transaction between your machine & Server (If he/she has network access) Hacker can get your Username/Password or any sensitive data. This become critical when you have Internet access to Oracle Applications 11i (Usually Self Service Implementation)

Communication between Client & Oracle Applications happens via three components.

  • Oracle Web Server (Initial Connection & all self service access is via Web Server/Apache). If your Form Server is in servlet Mode then Core Applications are also accessed via Web Server (oacore Component)
  • Oracle Form Server: For Core Oracle Application Access (Forms)
  • Database : You access web server which in turn talks to database Server via UTL_HTTP package via dad (/pls/$SID)

So You enable SSL on particular component depending on your requirement & component which is accessible over Internet & should be secured. You can Implement across all three component or only one or any two.

Concept:

SSL is a technology that defines the essential functions of mutual authentication, data encryption, and data integrity for secure transactions. Exchange of data between the client and server in such secure transactions is said to use the Secure Sockets Layer (SSL).

SSL uses 2 types of Certificates:

1.  User certificates:

These are Certificates issued to servers or users to prove their identity in a public key/private key exchange.

2.  Trusted certificates:

These are Certificates representing entities that you trust – such as certificate authorities that sign the user certificates they issue.

How SSL works with Middle Tier Oracle HTTP Server:

  1. The client sends a request to the server using HTTPS connection mode.
  2. The server presents its certificate to the client. This certificate contains the server’s identifying information.
  3. The client checks its list of Trust points and compares the information in the certificate with the server’s public key. If it matches, the server is authenticated as a trusted server.
  4. The client sends the server a list of the encryption levels, or ciphers, that it can use.
  5. The server receives the list and selects the strongest level of encryption that they have in common.
  6. The client creates a session key which is used to encrypt the data and sends this session key to the server which can decrypt the data with its private key

How SSL works with Oracle Database Server:

  1. The UTL_HTTP package is used for making HTTP callouts from SQL and PL/SQL to a Web Node (Oracle HTTP server).
  2. When the package fetches data from a Web site using HTTPS, it specifies the location to the Oracle Wallet that resides on the database server. This wallet contains the certificate for the Certifying Authority (CA) who signed the Web node’s server certificate.

Process:

For converting the application into SSL we need to have a Digital certificate. A Certificate Authority is a trusted third party responsible for issuing, revoking, and renewing digital certificates. All digital certificates are signed with the Certificate Authority’s private key to ensure authenticity. The Certificate Authority’s Public Key is widely distributed.

A Certificate Signing Request (CSR) is a digital file, which contains your public key and your name. You send the CSR to a Certifying Authority (CA) to be converted into a real Certificate.

A digital certificate is an electronic document that binds an identity to a pair of electronic keys that can be used to encrypt and sign digital information. A trusted third party, called a Certification Authority (CA), issues certificates. The document is usually in a standard X509 format and contains three elements:

  1. Entity attributes (information about your organization)
  2. Public key (which is bound to your organization)
  3. Digital signature of the trusted CA private key

Verisign (http://verisign.com/) will allow your organization to apply for a free trial certificate, which will be valid for 2 weeks for testing purposes.

Private (Server) Key:

The private key file is a digital file that you generate and for use to decrypt messages sent to you. The certificate request (CSR) that you send to your Certificate Authority (CA) is derived from this private key. Therefore, the resulting digital certificate (containing your public key), which is issued by your CA, is bound to this private key.

We will now look into the actual procedure for enabling the application with SSL. Not that below explanation is based on metalink note ID376700.1.

Middle tier setup

To start with SSL setup we have to create a certification request through Oracle wallet manager.

Step 1) Create wallet on the apps side

  • Login to application tier with applmgr user ID and navigate to $INST_TOP/ora/10.1.3 directory and source .env file present at that location. This will make 10.1.3 (IAS_ORACLE_HOME) as your ORACLE_HOME.
  • Navigate to $INST_TOP/certs/Apache directory.
  • Backup Apache directory
  • Start Oracle wallet manager

bash-2.05$ owm &
[1] 16555

  • Navigate Wallet -> New
    Answer No to: “Your default wallet directory doesn’t exist. Do you wish to create it now?”
    Set the wallet manager password (example. Welcome)
  • Click Yes when prompted “A new empty wallet has been created. Do you wish to create a certificate request at this time”.

Following screen appears

untitled.jpg

Common Name           – Your hostname
Organization Unit       – Your department name
Organization                – Your company name
Location/City              – Your city name
State/Province            – Your state name
Country                     – Your country name

Press OK once you fill all information. Save wallet in $INST_TOP/certs/Apache folder with name (server_name).cer

Once saved, it will ask, “A wallet already exist, you want to override”. You can say yes.
Click on certificate Request in the list provided on left side of Oracle wallet manager window and you can see your request contents on right side as seen in below screen.

untitled1.jpg

  • Wallet -> Autologin (Enable)
  • Wallet -> Save

Once you get the request content, you have to get the certificates from certifying Authority like verisign. Currently the certificate request that you created is your .cer file. Using this file you can get the actual certificate file, which will be .crt file. Along with that file you will also get your private key file (.key). So after getting certificate you will have 3 files with you

  1. Certificate request file (.cer)
  2. Actual certificate file (.crt)
  3. Private Key file (.key)

Once you get the certificate, you have to generate CA certification file. Since for any SSL certificate we need to have CA(Certifying Authority) file. Some of the certifying authority companies also provide CA files. But here we will see how to generate CA file from certificate file (.crt).

Step 2) Creating your certifying authority’s certificate

  • Copy/ftp (binary mode) .crt to the your PC desktop
  • Double-click the file and go to Certification Path tab
  • Double click on VeriSign/RSA Secure Server CA
  • Go to Details tab and press Copy to File…
  • Press Next and select “Base-64 encoded X.509(.CER)” and press next
  • Give the name as ca
  • Press Finish

A new ca.cer file will be present in your local PC. FTP back ca.cer file to your UNIX host.

Step 3) Converting the existing cert files

  • Source the .env file again present at location $INST_TOP/ora/10.1.3
  • Change dir to $INST_TOP/certs/Apache
  • Copy .crt, ca.crt, servername.key to the current dir.
  • Execute the following command:
    $ORACLE_HOME/Apache/Apache/bin/ssl2ossl -cert ./.crt -key ./.key -cafile ./ca.cer -wallet . -ssowallet yes

Step 4) Appstier setup
Edit the context xml file in $APPL_TOP/admin, change the SSL related variables to SSL values

SSL Related Variables in the Context File

Variable Non-SSL Value SSL Value
s_url_protocol http https
s_local_url_protocol http https
s_webentryurlprotocol http https
s_active_webport same as s_webport same as s_webssl_port
s_webssl_port not applicable default is 4443
s_https_listen_parameter not applicable same as s_webssl_port
s_help_web_agent url constructed with http protocol and s_webport for both non-SSL and SSL
s_login_page url constructed with http protocol and s_webport url constructed with https protocol and s_webssl_port
  • Execute autoconfig
  • Bounce the mid-tier services

Step 5) DB Tier Setup

Once you run autoconfig on Apps Tier, you need to do wallet setup on DB tier as well. Since R12 is autoconfig enabled, there will be a directory called appsutil in ORACLE_HOME on database side. Follow below steps to carry out the setup on database side.

  • Login to DB tier as oracle user
  • Change dir to $ORACLE_HOME/appsutil
  • Create a new dir with name as wallet
  • Start Oracle Wallet Manager (owm &)
  • Wallet -> New
  • Answer No to: “Your default wallet directory doesn’t exist. Do you wish to create it now?”
  • Click No when prompted “A new empty wallet has been created. Do you wish to create a certificate request at this time”
  • Save Wallet
  • Copy ca.crt from the appl_top to $ORACLE_HOME/appsutil/wallet
  • Navigate Operations-> Import Trusted Certificate
  • Navigate to ca.crt
  • Once import completed. Save wallet
  • Navigate wallet-> autologin (enable)
  • Save wallet

Step 6) Sanity check

  • New Appslogin url: https://(hostname):(port)/OA_HTML/AppsLogin
  • Notice the lock sign in the statusbar (bottom) of the browser. Click on the sign to verify the certificate
  • Sanity check all the components.
  • Login to db as sqlplus apps/(apps password)
  • select utl_http.request(‘https://(hostname):(port)’,null,’file:(path to ORACLE_HOME/appsutil/wallet)’, null) from dual;
  • It should display the first 2000 Chars of the html page.

 

Enabling OID/SSO for E-Business Suite R12

Following procedure shows registering Oracle E-Business suite R12 with OID and enabling SSO. This is based on metalink note ID 376811.1

There are three components that can be registered or de-registered in Release 12 with the SSO/OID registration utility. The utility automatically detects the registered components and performs registration for the un-registered components. So there is no need to pass individual registration arguments.

SSO-OID Registration can be done using a single command (Section 1.1). Even though it can be done in a single command it is divided into three parts.

  • Oracle Home Registration.
  • SSO Registration.
  • OID Registration.

Here we will see registering each component individually.

Also we need to have Oracle Application server 10g installed before proceeding for OID/SSO registration. Here are the list of component and supported version.

Components

Oracle E-Business Suite R12
The following components must be used on the E-Business Suite instance:

Component Name
Release
Oracle E-Business Suite Release 12
12.0.0
Oracle 10g Application Server
10.1.2
Oracle 10g Application Server
10.1.3
Oracle Developer 10g (includes Oracle Forms)
10.1.2
Oracle Application Server 10g Enterprise Edition

The following Oracle Application Server 10g Enterprise Edition components must be used on the standalone instance:

Component Name Release
Oracle Application Server 10g Enterprise Edition 10.1.2.0.2
Oracle Single Sign-On 10g 10.1.2.0.2
Oracle Internet Directory 10g 10.1.2.0.2
Oracle Portal 10g (optional) 10.1.4
Oracle Discoverer 10g (optional) 10.1.2.0.2

Before registering make sure to change the following profiles with the values give as below.

  • Applications SSO Type: SSWAw/SSO
  • Applications SSO Auto Link User: Enable
  • Applications SSO Login Types: Both
  • Application SSO LDAP Synchronization: Enable
  • Applications SSO Enable OID Identity Add Event: Enable
  • Link Applications user with OID user with same username: Enable

Once the profiles are set, go ahead with OID/SSO registration.

Registering with OID/SSO

Oracle Home Registration

bash-2.05$ $FND_TOP/bin/txkrun.pl \
> -script=SetSSOReg \
> -registerinstance=yes

You are registering ORACLE HOME only.

Enter the host name where Oracle iAS Infrastructure database is installed ? ocvmrh2119.us.oracle.com
Enter the LDAP Port on Oracle Internet Directory server ? 389
Enter SSL LDAP Port on Oracle Internet Directory server ? 636
Enter the Oracle Internet Directory Administrator (orcladmin) Bind password ? welcome1
Enter Oracle E-Business apps database user password ? apps

 

*** Log File = /dy/oracle/product/test12/inst/apps/test12_ap101fam/logs/appl/rgf/TXK/

txkSetSSOReg_Wed_Jun_20_05_14_34_2007.xml

Beginning input parameter validation for Oracle Home Instance registration.
Input parameter validation for Oracle Home Instance registration completed.

BEGIN ORACLE HOME INSTANCE REGISTRATION:
Oracle Home Instance preferences stored successfully.
Oracle Home Instance registered successfully.

 

 

End of /dy/oracle/product/test12/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/

txkSetSSOReg.pl : No Errors encountered

SSO Registration

bash-2.05$ $FND_TOP/bin/txkrun.pl \
> -script=SetSSOReg \
> -registersso=yes

You are registering this instance with SSO Server.

Enter Oracle E-Business apps database user password ? apps

 

*** Log File = /dy/oracle/product/test12/inst/apps/test12_ap101fam/logs/appl/rgf/TXK/

txkSetSSOReg_Wed_Jun_20_05_16_22_2007.xml

Beginning input parameter validation for SSO registration.
Input parameter validation for SSO registration completed.

BEGIN SSO REGISTRATION:
Beginning to register partner application.
Partner application has been registered successfully.
Configuration file uploaded successfully.
Single Sign-On partner application registered successfully.

 

 

End of /dy/oracle/product/test12/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/

txkSetSSOReg.pl : No Errors encountered

OID Registration

bash-2.05$ $FND_TOP/bin/txkrun.pl \
> -script=SetSSOReg \
> -registeroid=yes

You are registering this instance with OID Server.

Enter LDAP Host name ? ocvmrh2119.us.oracle.com
Enter the LDAP Port on Oracle Internet Directory server ? 389
Enter the Oracle Internet Directory Administrator (orcladmin) Bind password ? welcome1
Enter the instance password that you would like to register this application instance with ? welcome1
Enter Oracle E-Business apps database user password ? apps

 

*** Log File = /dy/oracle/product/test12/inst/apps/test12_ap101fam/logs/appl/rgf/TXK/

txkSetSSOReg_Wed_Jun_20_05_17_54_2007.xml

Beginning input parameter validation for OID registration.
Input parameters validation for OID registration completed.

 

BEGIN OID REGISTRATION:
Beginning to register Application and Service containers if necessary.
Application and Service containers were created successfully if necessary.
Beginning to register application in Oracle Internet Directory.
Registration of application in Oracle Internet Directory completed successfully.
-> LOADING:  /dy/oracle/product/test12/apps/apps_st/appl/fnd/12.0.0/admin/template

/AppsOIDRegistration.tmp

Beginning to register provisioning profile in Oracle Internet Directory.
Registration of provisioning profile in Oracle Internet Directory completed successfully.
Application is now registered successfully with provisioning in Oracle Internet Directory.

End of /dy/oracle/product/test12/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/

txkSetSSOReg.pl : No Errors encountered

Users are automatically synched up from local repository to OID repository. So when you create a user in local using AppsLocalLogin.jsp it will be automatically reflected in SSO.

Try to login to AppsLocalLogin.jsp and create a new user. Please note that if you dont give any numeric value in password then you may encounter following error.

Unable to call fnd_ldap_wrapper.create_user due to the following reason:
.
ORA-20001: Unable to call fnd_ldap_wrapper.create_user due to the following
reason:
An unexpected error occurred. Please contact your System Administrator

Example if you give your password as welcome, then you may get above error. For this, you need to give a numberic value in your password like welcome1. This behaviour is because while creating users in Apps (even via Forms) by logging via AppsLocalLogin.jsp, it will send the user to OID. In attempting to do so, it will check the OID’s password policies. According to the policies, a password should be alpha numeric.

De-Registrating OID/SSO

Deregister OID

bash-2.05$ $FND_TOP/bin/txkrun.pl \
> -script=SetSSOReg \
> -deregisteroid=yes

You are deregistering this instance from OID Server.

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

WARNING: You have selected to deregister individual components.
This might leave some components still at registered state
which might create inconsistencies at run time.
It is recommended that you use “-deregister=Yes” to completely deregister
the Oracle EBusiness instance from OID server
OR
Individually deregister components using “-deregisteroid=Yes”,
“-deregistersso=Yes” and “-deregisterinstance=Yes”.

===============================================================
Enter Oracle E-Business apps database user password ? apps
Enter the Oracle Internet Directory Administrator (orcladmin) Bind password ? welcome1

 

 

*** Log File = /dy/oracle/product/test12/inst/apps/test12_ap101fam/logs/appl/rgf/TXK/

txkSetSSOReg_Thu_Jun_21_00_12_25_2007.xml

Beginning input parameter validation for OID de-registration.
Input parameters validation for OID de-registration completed.

 

BEGIN OID DE-REGISTRATION:
Beginning to delete provisioning profile for this application.
Provisioning profile for this application has been deleted successfully.
Beginning to de-register this application from Oracle Internet Directory.
This application has been de-registered successfully from Oracle Internet Directory.
-> LOADING:  /dy/oracle/product/test12/apps/apps_st/appl/fnd/12.0.0/admin/template

/AppsOIDDeRegistration.tmp
Removing OID References Started.
Removing OID References Completed Successfully.
OID DE-REGISTRATION COMPLETED.

End of /dy/oracle/product/test12/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/

txkSetSSOReg.pl : No Errors encountered

Deregister SSO

bash-2.05$ $FND_TOP/bin/txkrun.pl \
> -script=SetSSOReg \
> -deregistersso=yes

You are deregistering this instance from SSO Server.

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

WARNING: You have selected to deregister individual components.
This might leave some components still at registered state
which might create inconsistencies at run time.
It is recommended that you use “-deregister=Yes” to completely deregister
the Oracle EBusiness instance from OID server
OR
Individually deregister components using “-deregisteroid=Yes”,
“-deregistersso=Yes” and “-deregisterinstance=Yes”.

===============================================================
Enter Oracle E-Business apps database user password ? apps

 

 

*** Log File = /dy/oracle/product/test12/inst/apps/test12_ap101fam/logs/appl/rgf/TXK/

txkSetSSOReg_Thu_Jun_21_00_14_52_2007.xml

Beginning input parameter validation for SSO de-registration.
Input parameters validation for SSO de-registration completed.

BEGIN SSO DE-REGISTRATION:
Partner application registered with SSO Server and will be de-registered.
Partner application has been de-registered successfully.
Configuration file deleted successfully.
SSO DE-REGISTRATION COMPLETED.

 

 

End of /dy/oracle/product/test12/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/

txkSetSSOReg.pl : No Errors encountered

Deregister Instance

bash-2.05$ $FND_TOP/bin/txkrun.pl \
> -script=SetSSOReg \
> -deregisterinstance=yes

You are deregistering ORACLE HOME only.

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

WARNING: You have selected to deregister individual components.
This might leave some components still at registered state
which might create inconsistencies at run time.
It is recommended that you use “-deregister=Yes” to completely deregister
the Oracle EBusiness instance from OID server
OR
Individually deregister components using “-deregisteroid=Yes”,
“-deregistersso=Yes” and “-deregisterinstance=Yes”.

===============================================================
Enter the Oracle Internet Directory Administrator (orcladmin) Bind password ? welcome1
Enter Oracle E-Business apps database user password ? apps

 

 

*** Log File = /dy/oracle/product/test12/inst/apps/test12_ap101fam/logs/appl/rgf/TXK/

txkSetSSOReg_Thu_Jun_21_00_16_25_2007.xml

Beginning input parameter validation for Oracle Home Instance de-registration.
Input parameter validation for Oracle Home Instance de-registration completed.

BEGIN ORACLE HOME INSTANCE DE-REGISTRATION:
Oracle Home Instance de-registered successfully from Infrastructure Host.
Oracle Home Instance preferences removed successfully
ORACLE HOME INSTANCE DE-REGISTRATION COMPLETED.

 

 

End of /dy/oracle/product/test12/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/

txkSetSSOReg.pl : No Errors encountered

Once these 3 steps are done, bounce the application system middle tier, login to application and change the profile options back to original value. Following values should be made for the profile options.

  • Applications SSO Type: SSWAw/SSO
  • Applications SSO Auto Link User: Disable
  • Applications SSO Login Types: Local
  • Application SSO LDAP Synchronization: Disable
  • Applications SSO Enable OID Identity Add Event: Disable
  • Link Applications user with OID user with same username: Disable

 

Converting / Migerating database character set

This article presents a simple example of migrating the default character set of a database using the CSSCAN and CSALTER character set scanner utilities provided by Oracle. The basic steps involved in character set conversion are listed below:

SHUTDOWN IMMEDIATE
BACKUP
STARTUP
CSSCAN
SHUTDOWN IMMEDIATE
STARTUP RESTRICT
CSALTER
SHUTDOWN IMMEDIATE
STARTUP

Before migerating the chanracter set its really very important that we backup the database completly. In case if some issue occurs while migeration or some file gets corrupted, then we can restore the database back.

Once backup is done, start the instance and begin scanning.

Before we migerate to a new character set we have to scan the database for determining
the language and character set for unknown file text. With each text, the character set detection engine sets up a series of probabilities, each probability corresponding to a language and character set pair. The most statistically probable pair identifies the dominant language and character set.

We can scan the database using CSSCAN utility. From the command prompt

===============================================================
bash-2.05$ csscan system/manager full=y tochar=al32utf8 ARRAY=10240 PROCESS=3

ld.so.1: csscan: fatal: libclntsh.so.10.1: open failed: No such file or directory
Killed
===============================================================

The above error is because LD_LIBRARY_PATH is not set. Set the LD_LIBRARY_PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

===============================================================
bash-2.05$ csscan system/manager full=y tochar=al32utf8 ARRAY=10240 PROCESS=3
Character Set Scanner v2.1 : Release 10.2.0.0.0 – Production on Sun May 27 10:52:53 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.
===============================================================
The above error is due to

CSS-00107 Character set migration utility schem not installed
Cause: CSM$VERSION table not found in the database.
Action: Run CSMINST.SQL on the database.

===============================================================
bash-2.05$ csscan system/manager full=y
Character Set Scanner v2.1 : Release 10.2.0.0.0 – Production on Sun May 27 11:13:46 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

Current database character set is US7ASCII.

Enter new database character set name: > al32utf8

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize(1..32): 1 > 3

Enumerating tables to scan…

. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
. process 2 scanning SYS.TYPE$[AAAAC1AABAAAAURAAA]
. process 2 scanning SYS.METHOD$[AAAAC1AABAAAAURAAA]
. process 3 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
. process 2 scanning SYS.PARAMETER$[AAAAC1AABAAAAURAAA]



. process 2 scanning SYS.LOGMNR_BUILDLOG[AAABXcAABAAADN5AAA]
. process 3 scanning SYSTEM.LOGSTDBY$APPLY_MILESTONE[AAABw/AADAAAAkJAAA]
. process 1 scanning SYSTEM.REPCAT$_CONFLICT[AAAB3IAABAAAEVhAAA]
. process 2 scanning SYS.DBMS_UPG_LOG$[AAACGVAABAAAFHRAAA]
. process 3 scanning SYS.WRH$_DATAFILE[AAACLIAADAAAAs5AAA]
. process 2 scanning SYS.WRH$_MTTR_TARGET_ADVICE[AAACORAADAAABBJAAA]
. process 1 scanning SYS.WRH$_STREAMS_CAPTURE[AAACOkAADAAABDBAAA]
. process 3 scanning DBSNMP.MGMT_CAPTURE_SQL[AAACWHAADAAABSZAAA]
. process 2 scanning SYS.WRH$_ACTIVE_SESSION_HISTORY[AAACVHAADAAABB5AAA]
. process 1 scanning SYSTEM.LOGMNRC_GTCS[AAABajAADAAAAZxAAA]
. process 3 scanning SYS.WRH$_DB_CACHE_ADVICE[AAACubAADAAABjhAAA]

Creating Database Scan Summary Report…

Creating Individual Exception Report…

Scanner terminated successfully.

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

Once the scaning is completed shutdown the database and start in restrict mode.

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

Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 750786888 bytes
Database Buffers 314572800 bytes
Redo Buffers 6397952 bytes
Database mounted.
Database opened.
SQL>

SQL> @@?/rdbms/admin/csalter.plb

0 rows created.
Function created.
Function created.
Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER(‘&conf’) <> ‘Y’) then
new 6: if (UPPER(‘Y’) <> ‘Y’) then
Checking data validility…
begin converting system objects
80 rows in table SYS.METASTYLESHEET are converted
4 rows in table SYS.RULE$ are converted
3369 rows in table SYS.WRH$_SQL_PLAN are converted
408 rows in table SYS.WRH$_SQLTEXT are converted
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
21 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted

PL/SQL procedure successfully completed.

Alter the database character set…
CSALTER operation completed, please restart database

PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

SQL>
===============================================================

If there are possible conversion problems, the process will report the problem and clean itself up without performing the conversion. Here are a couple of the messages I got when trying this process.

# When I tried to convert WE8MSWIN1252 -> AL32UTF8.
Checking data validility…
Unrecognized convertible date found in scanner result

# When I tried to run the CSALTER script without a SHUTDOWN-STARTUP RESTRICT.
Checking data validility…
Sorry only one session is allowed to run this script

Once the conversion is complete, you must restart the instance

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

Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 750786888 bytes
Database Buffers 314572800 bytes
Redo Buffers 6397952 bytes
Database mounted.
Database opened.
SQL>
===============================================================

For more information on this utility, please check the oracle documentation link.

 

Restoring the statistics – Oracle Database 10g

Here is the small article on how to restore the statistics on a table. Sometimes we gather stats on a table which causes it to flip the plan of a query accessing that table. It can lead to a great performance for some queries but there are situations where the query performance can degrade.

Certain tables in every databases are “Hot” tables and a DBA should not play around the statistics of those tables as there could be quiet a huge impact of the same.

In case if  a stats are gathered and you need to restore the previous stats in order to bring everything back to normal, here is what one should be doing.

Using RESTORE_TABLE_STATS

Step 1) Check the history of stats gathered on the table using DBA_TAB_STATS_HISTORY

SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name = 'PICKED_SHIPMENT_BATCHES';
TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ----------------------------------------
PICKED_SHIPMENT_BATCHES        30-OCT-10 07.50.59.539450 PM +02:00
PICKED_SHIPMENT_BATCHES        31-OCT-10 12.02.58.979300 AM +02:00
PICKED_SHIPMENT_BATCHES        31-OCT-10 12.17.19.315201 AM +02:00

STATS_UPDATE_TIME tells us when the stats were last updated.
Step 2) Check when was the table last analyzed

DCFRA1>select last_analyzed from dba_tables where table_name = 'PICKED_SHIPMENT_BATCHES';
LAST_ANALYZED
----------------
2010-10-31:20:12

 

Step 3) Use DBMS_STATS.RESTORE_TABLE_STATS procedure to restore the stats on the table

SQL> execute dbms_stats.restore_table_stats(<OWNER>,<TABLE_NAME>,<TIMESTAMP WITH TIMEZONE>);

Timestamp could be any timestamp in STATS_UPDATE_TIME column of dba_tab_stats_history table.

After restore, Check the last_analyzed date from dba_table and you should see the old date.
Following is a short FAQ on statistics.

Where does oracle store the statistics?

Oracle DBA_OPTSTAT_OPERATIONS table contains a log history of statistics collected.

SQL> select OPERATION, START_TIME, END_TIME from dba_optstat_operations;
OPERATION                      START_TIME                               END_TIME
------------------------------ ---------------------------------------- ----------------------------------------
gather_database_stats(auto)    30-OCT-10 06.00.04.042555 AM +02:00      31-OCT-10 11.00.02.114381 PM +01:00

 

How does Oracle maintain the Statistics History?

We can check the oldest statistics that can be restore using GET_STATS_HISTORY_AVAILABILITY procedure.

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY  from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
30-SEP-10 11.22.25.817428000 PM +01:00

Usually Oracle retains stats for 1 month (31 days).

DCFRA1>select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
                        31

You can change the retention using ALTER_STATS_HISTORY_RETENTION procedure.

Using EXPORT/IMPORT

 

Using export/import of statistics is a 6 steps process

Step 1) Create stats table in the database

EXEC DBMS_STATS.create_stat_table(‘SYSTEM’,’STATS_TABLE’);

Step 2) Export table/schema statistics

DBMS_STATS.export_schema_stats(‘DEMO_SCHEMA’,’STATS_TABLE’,NULL,’SYSTEM’);

Step 3) Export the table STATS_TABLE using exp utility.

This completes the export part. You can later import the stats using following steps

Step 4) Import table STATS_TABLE using imp utility

Step 5) Import table/schema statistics

DBMS_STATS.import_schema_stats(‘DEMO_SCHEMA’,’STATS_TABLE’,NULL,’SYSTEM’);

Step 6) Drop the stats table

EXEC DBMS_STATS.drop_stat_table(‘SYSTEM’,’STATS_TABLE’);

 

Hope this helps !!