WLST (Weblogic Scripting Tool)

Introduction

With Fusion Middleware 11g, Oracle has moved from the traditional Apache and OC4J to Oracle weblogic. There are many new things that has come up with weblogic. There is a change in architecture. Changes related to managing the services, deployment procedure, changing of port and many other things.

Well, should we say that it has got better or it got worst ? I am not sure about good or bad, but I know its new. Per my opinion and experience, weblogic provides more flexibility in terms of changing the parameters or making any changes in the existing deployment. Or may be I havent worked much on OC4J or weblogic.

Anyways, this is a short post which explains about a tool called WLST (Weblogic Scripting Tool). WLST framework comes along with weblogic installation and provides a platform to carry out all the task at command line. This is a very useful tool for the people working on weblogic.

WLST Commands

For this post, I have following home

Middleware Home – /slot/ems3398/oracle/mwhome

Weblogic Home – /slot/ems3398/oracle/mwhome/wlserver_10.3

In weblogic we have a concept called domain. Domain is nothing but a project created inside weblogic which will have applications deployed within it. Every domain will have one AdminServer and multiple managed servers. Applications are deployed either on a AdminServer (less recommended) or on managed servers (more recommended).

Domains are created from a template. To begin with weblogic provides some default templates which can be used to create a domain. These templates are nothing but the jar files which has complete file system and services in side it. When a domain is created the jar file is extracted to create the required domain file system and AdminServer for that domain.

The purpose of AdminServer is for administering the services. In case you have managed server for the domain, then you can start and stop manage server only using AdminServer. If your AdminServer is down, then you cannot start or stop Managed servers. However manage servers can remain up and accessible even if AdminServer is down. Each of these servers have there own separate ports.

Default installation of weblogic does not create any domain. We need to create domain manually using the default templates provided by weblogic.

Default template for weblogic will be available at /slot/ems4179/oracle/mwhome/wlserver_10.3/common/templates/domains location

-bash-3.00$ pwd
/slot/ems4179/oracle/mwhome/wlserver_10.3/common/templates/domains
-bash-3.00$ ls -rlt
total 164
-rw-r—–  1 ora4179 ems4179  32510 Jul 28 11:27 wls.jar
-rw-r—–  1 ora4179 ems4179 130456 Jul 28 11:27 wls_starter.jar

You can create domain using this default template (.jar file) using config.sh script present in /slot/ems4179/oracle/mwhome/wlserver_10.3/common/bin location. This script will launch a GUI where all the details can be provided and it will create the required domain.

You can also create the same domain using WLST (Weblogic Scripting Tool). WLST has many commands that helps to get the required configuration. Also the complete domain is visible in WLST as directory and file listing. To connect to WLST framework just run wlst.sh script present in /slot/ems4179/oracle/mwhome/wlserver_10.3/common/bin location

Once you run wlst.sh, you will get the WLS prompt.

wls:/offline>

Creating Default Domain

For creating any domain, you need to first read the required template at WLS prompt and then create domain out of it. The command is readDomain and it takes 1 argument as complete name and location of template.

wls:/offline> readTemplate('/slot/ems4179/oracle/mwhome/wlserver_10.3/common/templates/domains/wls.jar');

wls:/offline/base_domain>ls()
drw-   Security
drw-   Server

-rw-   Active                                        false
-rw-   AdminServerName                               AdminServer
-rw-   AdministrationMBeanAuditingEnabled            false
-rw-   AdministrationPort                            9002
-rw-   AdministrationPortEnabled                     false
-rw-   AdministrationProtocol                        null
-rw-   AutoDeployForSubmodulesEnabled                true
-rw-   ClusterConstraintsEnabled                     false
-rw-   ConfigBackupEnabled                           false
-rw-   ConfigurationAuditType                        null
-rw-   ConfigurationVersion                          10.3.1.0
-rw-   ConsoleContextPath                            console
-rw-   ConsoleEnabled                                true
-rw-   ConsoleExtensionDirectory                     console-ext
-rw-   DomainVersion                                 10.3.1.0
-rw-   GuardianEnabled                               false
-rw-   InternalAppsDeployOnDemandEnabled             true
-rw-   LastModificationTime                          0
-rw-   Name                                          base_domain
-rw-   Notes                                         null
-rw-   OcmEnabled                                    true
-rw-   ProductionModeEnabled                         false
-rw-   RootDirectory                                 null

If you want to list the attributes and services of the template you can use ls() command. The command is same as that of OS command but we need to supply () along with the command. Similarly you can change directory in WLS prompt and go inside the template. Example, I want to set the password for weblogic inside the template, So I can use following command

wls:/offline/base_domain>cd('/Security/base_domain/User/weblogic');
wls:/offline/base_domain/Security/base_domain/User/weblogic>cmo.setPassword('welcome1');

Complete list of all the commands available in WLS is giving in reference section of this post.

Next, after setting the password, you can close the template using closeTemplate(); before creating the domain

wls:/offline/base_domain/Security/base_domain/User/weblogic>closeTemplate();

Next is creating the domain. The command for that is createDomain. It takes 4 argument as shown below

createDomain('/slot/ems4179/oracle/mwhome/wlserver_10.3/common/templates/domains/wls.jar','/slot/ems4179/oracle/mwhome/user_projects/domain/soa_domain1','weblogic','welcome1');

1st Argument is the template name

2nd arguement is the location where the domain is to be created

3rd argument is the weblogic username (which will be weblogic)

4th argument will be the weblogic password

This command is going to extract /slot/ems4179/oracle/mwhome/wlserver_10.3/common/templates/domains/wls.jar file completly and create a directory strucrure under /slot/ems4179/oracle/mwhome/user_projects/domain/soa_domain1

Once the domain is created you can exit the WLST command promt and start the AdminServer using the script shown below

wls:/offline>exit()

Exiting WebLogic Scripting Tool.

-bash-3.00$ cd /slot/ems4179/oracle/mwhome/user_projects/domain/soa_domain1/bin/
-bash-3.00$ ./startWebLogic.sh  &

Start of weblogic server will take some time. Wait until you see the message

<Sep 30, 2009 10:20:09 AM PDT> <Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>

Once AdminServer is started you can access the weblogic console using http://<hostname>:<port>/console

The default port number will be 7001 unless you change the port number in WLST as shown below

Changing the Default port number

Connect to WLST again and read the domain that you have created just now

wls:/offline> readDomain('/slot/ems4179/oracle/mwhome/user_projects/domain/soa_domain1');
wls:/offline/soa_domain1>cd ('/Server/AdminServer');
wls:/offline/soa_domain1/Server/AdminServer>set('ListenPort',8050);
wls:/offline/soa_domain1/Server/AdminServer>updateDomain()

Once you update the domain with new port 8050, exit the WLST and bounce AdminServer. You can now access the console using new port.

Adding SOA templates to default domain

In the previous post for installing SOA, I showed you the installation procedure for SOA. In those steps we used the GUI for installing the SOA. Here I will show you the steps for installing the SOA using WLST. Basically we can convert our existing default domain into SOA domain by adding SOA template to this domain and changing few attributes of the domains.

The database required for the SOA needs to be installed separately. I have a script for installing SOA using WLST and I will walk you through that script now.

The script looks as shown below

readTemplate('/slot/ems4179/oracle/mwhome/wlserver_10.3/common/templates/domains/wls.jar');
cd('/Security/base_domain/User/weblogic');
cmo.setPassword('welcome1');
closeTemplate();
createDomain('/slot/ems4179/oracle/mwhome/wlserver_10.3/common/templates/domains/wls.jar','/slot/ems4179/oracle/mwhome/user_projects/domain/fmw_domain','weblogic','welcome1');
readDomain('/slot/ems4179/oracle/mwhome/user_projects/domain/fmw_domain');
addTemplate('/slot/ems4179/oracle/mwhome/Oracle_SOA1/common/templates/applications/oracle.soa_template_11.1.1.jar');

addTemplate('/slot/ems4179/oracle/mwhome/Oracle_SOA1/common/templates/applications/oracle.bam_template_11.1.1.jar');

cd('/Servers/AdminServer');
set('ListenPort', 15079);
cd('/Servers/soa_server1');
set('ListenPort', 16079);
cd('/Servers/bam_server1');
set('ListenPort', 17079);
cd('/JDBCSystemResources/BAMDataSource/JdbcResource/BAMDataSource/JDBCDriverParams/NO_NAME_0');
set('URL','jdbc:oracle:thin:@stasa38.us.oracle.com:1600:soaemqa');
cmo.setPasswordEncrypted('welcome1');
cd('/JDBCSystemResources/EDNDataSource/JdbcResource/EDNDataSource/JDBCDriverParams/NO_NAME_0');
set('URL','jdbc:oracle:thin:@stasa38.us.oracle.com:1600:soaemqa');
cmo.setPasswordEncrypted('welcome1');
cd('/JDBCSystemResources/EDNLocalTxDataSource/JdbcResource/EDNLocalTxDataSource/JDBCDriverParams/NO_NAME_0');
set('URL','jdbc:oracle:thin:@stasa38.us.oracle.com:1600:soaemqa');
cmo.setPasswordEncrypted('welcome1');
cd('/JDBCSystemResources/OraSDPMDataSource/JdbcResource/OraSDPMDataSource/JDBCDriverParams/NO_NAME_0');
set('URL','jdbc:oracle:thin:@stasa38.us.oracle.com:1600:soaemqa');
cmo.setPasswordEncrypted('welcome1');
cd('/JDBCSystemResources/SOADataSource/JdbcResource/SOADataSource/JDBCDriverParams/NO_NAME_0');
set('URL','jdbc:oracle:thin:@stasa38.us.oracle.com:1600:soaemqa');
cmo.setPasswordEncrypted('welcome1');
cd('/JDBCSystemResources/SOALocalTxDataSource/JdbcResource/SOALocalTxDataSource/JDBCDriverParams/NO_NAME_0');
set('URL','jdbc:oracle:thin:@stasa38.us.oracle.com:1600:soaemqa');
cmo.setPasswordEncrypted('welcome1');
cd('/JDBCSystemResources/mds-owsm/JdbcResource/mds-owsm/JDBCDriverParams/NO_NAME_0');
set('URL','jdbc:oracle:thin:@stasa38.us.oracle.com:1600:soaemqa');
cmo.setPasswordEncrypted('welcome1');
cd('/JDBCSystemResources/mds-soa/JdbcResource/mds-soa/JDBCDriverParams/NO_NAME_0');
set('URL','jdbc:oracle:thin:@stasa38.us.oracle.com:1600:soaemqa');
cmo.setPasswordEncrypted('welcome1');
updateDomain();
exit();

The first 5 steps are same as we saw above. Next step is to read the domain and add SOA & BAM template to it. SOA template will come along with SOA installation.

After adding these templates, it will automatically create ManagedServers in the domain.

Next we are just navigating to the folders and changing the ports for AdminServer and each of the ManagedSever

SOA and BAM uses datasources for connecting to database, so we have to navigate to the corresponding datasource folders and update the JDBC connection, userID and passwords.

Once all this is done, update the domain and your SOA installation is ready. You can bounce the AdminServer and start ManagedServers.

References

WLST Command Reference : http://download.oracle.com/docs/cd/E12840_01/wls/docs103/config_scripting/reference.html

Hope this helps !!

Installing Oracle Identity Management 11g R1 (11.1.1.1)

Introduction:

Oracle Identity Management enables enterprises to manage the end-to-end lifecycle of user identities across all enterprise resources—both within and beyond the firewall. With Oracle Identity Management, you can deploy applications faster, apply the most granular protection to enterprise resources, automatically eliminate latent access privileges, and much more.

Oracle Identity Management 11g Release 1 (11.1.1) includes the following components:

• Oracle Internet Directory
• Oracle Directory Integration Platform
• Oracle Virtual Directory
• Oracle Directory Services Manager
• Oracle Identity Federation

In this post we will see the installation of Oracle Identity Management 11g R1 (11.1.1.1). As mentioned before the approach for installing Oracle 11g FMW components is different then those of 10g components, I will mention the approach for installing Oracle Identity Management 11g.

Brief Installation Steps:

Following are the brief steps for installing Oracle Identity Management 11g

1) Install database 11g (11.1.0.6 / 11.1.0.7) including configuring TNS and listener

2) Create repository using Oracle RCU (Repository Creation Utility)

3) Install WLS (weblogic Server) and create a middleware home

4) Install Oracle Identity Management 11g inside middleware home

The installation will install and configure the complete Identity Management and provide the access URLs.

Download Locations:

You can download all the required software from following location

Oracle Database 11g (11.1.0.6/11.1.0.7) – http://www.oracle.com/technology/software/products/database/index.html

Oracle IDM 11g – http://www.oracle.com/technology/software/products/middleware/htdocs/111110_fmw.html (Download the product Identity Management)

Oracle RCU – http://www.oracle.com/technology/software/products/middleware/htdocs/111110_fmw.html

Oracle WLS 10.3.1 – http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html

Step 1) Install database 11g including configuring TNS and listener

For this you can refer previous post for database installation and create a 11g database. Also create a listener on any available port and configure TNS for the database.

Step 2) Create repository using Oracle RCU (Repository Creation Utility)

Using RCU, you can create repository for IDM. You dont have to install RCU for using it. RCU comes as a zip file along with the identity management software download. Once you unzip you run <RCU_UNZIP>/bin/rcu binary

This will invoke a GUI. On the first page you can select “Create Repository” and click on next.

On the next page RCU will ask for database details in which you want to create the repository. The page will look as shown below

Once you connect to database, on the next page you need to select the repository that you want to create. Here you can select “Identity Management” as shown below. Also you can use any prefix for these schema. All these schema created will have the prefix string prefixed to it. In this case it will prepend DEV before each schema name. Note that it wont prefix anything before ODS schema. This schema is used for OID and SSO configuration.

Once you click on next, it will show the summary and will create tablespace if they dont exists. After tablespace creation, click on create and it will create the required repository schemas.

Step 3) Install WLS (weblogic Server) and create a middleware home

Next step is to install WLS server. Carry out the basic installation of WLS. You need to provide a new location for middleware home when asked for as shown below.

Next it will ask for the location of weblogic. Here you can accept the default value as it will be created inside middleware home.

Select all other values as default and install WLS.

Step 4) Installing IDM 11g.

IDM installation involves many steps and screens. Please follow the below screen shots for installing IDM

When you start the IDM installer, you will see a welcome screen. Click on next.

Next you will see “Install option” screen. In this screen select “Install and Configure”. Click on next.

The installer will perform the pre-requisite checks. Click on next.

In the next screen “Select Domain”, click on “Create new domain” as shown below

On the next screen, specify the install location inside middleware home.Click Next.

Select default values for next screen – Security Updates. Click Next.

On configure components screen, keep the default values. Click Next

On configure port  screen, you can choose Automatic port assignment. Click Next.

On “Specify Oracle Virtual Directory Information” page, provide the inputs as shown in the screen below

Here you need to specify the password for orcladmin. Remember the password you are setting here as you will need that at many places later. Click on Next.

On “Specify Schema Database” page, you can specify the ODS schema details that we created using repository creation utility (Step 2)). Also you need to provide ODS schema password you set while running RCU in step 2) as shown below

On “Create Oracle Internet Directory” screen, specify the Realm and orcladmin password. Note here that this orcladmin user is different then we say couple of screens back. This orcladmin user is for OID, where as the previous orcladmin user was for OVD.

Next, On “Specify OIF Details” page, specify the PKCS12 password and the domain name for OIF as shown below

On rest of the screen, you can specify the default value and submit the installation. The installation will complete in approximately 45 mins. Once the installation and all configuration assistants completes successfully, you can login to weblogic console.

The default port for weblogic console is 7001. So the console URL becomes

http://<hostname>:7001/console

user name : Weblogic

password : As specified while installing WLS in step 3)

Once you login, you will see the managed servers created for OID and OIF.

Hope this helps !!

Installing Oracle SOA 11g

Introduction:

In this post we will see detailed steps to install SOA 11g. The approach used in all 11g FMW components (Including SOA) is very different then 10g. The basic limitation of any 10g component involving infrastructure was that the product itself used to create database and configure the entire infrastructure. This used to put limitation on database port which always used to be 1521.

Here in 11g version, a user has a flexibility to use any port. 11g expects installed database as a pre-reqs and uses that database to create corresponding repository. This way we can have database and listener at any port and provide that information while creating repository.

Also another important feature with all 11g components is that web server used is weblogic and it is kept as another separate component in the form of pre-reqs. So any 11g components requiring web server expects weblogic as pre-reqs. With this brief introduction about 11g FMW components characteristics we will see the installation procedure for SOA 11g.

Installing SOA 11g:

Following are the brief steps for installing SOA 11g

1) Install database 11g (11.1.0.6 / 11.1.0.7) including configuring TNS and listener

2) Create repository using Oracle RCU (Repository Creation Utility)

3) Install WLS (weblogic Server) and create a middleware home

4) Install SOA 11g inside middleware home

5) Use config.sh or WLST to create and configure SOA domain and managed servers

In case of SOA 11g, BAM also gets install and configured if selected. Here we will install both SOA and BAM

Download Locations:

You can download all the required software from following location

Oracle Database 11g (11.1.0.6/11.1.0.7) – http://www.oracle.com/technology/software/products/database/index.html

Oracle SOA suite 11g – http://www.oracle.com/technology/software/products/middleware/htdocs/111110_fmw.html

Oracle RCU – http://www.oracle.com/technology/software/products/middleware/htdocs/111110_fmw.html

Oracle WLS 10.3.1 – http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html

Step 1) Install database 11g including configuring TNS and listener

For this you can refer previous post for database installation and create a 11g database. Also create a listener on any available port and configure TNS for the database.

Step 2) Create repository using Oracle RCU (Repository Creation Utility)

Using RCU, you can create repository for SOA. You dont have to install RCU for using it. RCU comes as a zip file which you need to unzip. Once you unzip you run <RCU_UNZIP>/bin/rcu binary

This will invoke a GUI. On the first page you can select “Create Repository” and click on next.

On the next page RCU will ask for database details in which you want to create the repository. The page will look as shown below

Once you connect to database, on the next page you need to select the repository that you want to create. Here you can select SOA as shown below. Metadata services will get selected automatically. Also you can use any prefix for these schema. All these schema created will have the prefix string prefixed to it. In this case it will prepend DEV before each schema name. Example DEV_MDS, DEV_SOAINFRA etc.

Once you click on next, it will show the summary and will create tablespace if they dont exists. After tablespace creation, click on create and it will create the required repository schemas.

Step 3) Install WLS (weblogic Server) and create a middleware home

Next step is to install WLS server. Carry out the basic installation of WLS. You need to provide a new location for middleware home when asked for as shown below.

Next it will ask for the location of weblogic. Here you can accept the default value as it will be created inside middleware home.

Select all other values as default and install WLS.

Step 4) Install SOA 11g inside middleware home

Installation of SOA home is again very straight. Installation of SOA will just lay down the file system, it wont configure anything. Important input while installing SOA is the middleware home location and location for SOA home

Here Oracle_SOA1 directory will get created inside mwhome directory. Rest of the inputs for SOA installation can be default.

Step 5) Use config.sh or WLST to create and configure SOA domain and managed servers

Once SOA installation is done, the last and the most important step is configuring SOA domain.

You need to navigate to <middleware_home>/Oracle_SOA1/common/bin location and run config.sh script. This script will open a new GUI window. Follow the below screen shots for configuring SOA.

Select all the required template from this screen. If you don’t need BAM installation, you can uncheck the Business Activity Monitoring template. Click on Next.

On the next screen give any domain name (soa_domain) in this case. You can keep the Domain location and Application location as default. These directories will get automatically created when this domain gets created at the end of installation. Click on Next.

On this screen, provide the weblogic password and click on Next.

On this screen you need to provide the database connection details like database name, hostname and database port. You also need to provide all the schema names and password you created using RCU. RCU will create exact 5 schemas required for SOA configuration. You need to provide details of those schemas on this screen. Once you click in next, the configurator will test the connection for all 5 schemas.

Once the connection test goes successful, you can keep rest of the values as default and create the required domain.

After domain creation start the weblogic server using <middleware_home>/user_projects/domain/<domain_name>/bin/startWeblogic.sh script

Also start the managed servers using <middleware_home>/user_projects/domain/<domain_name>/bin/startManagedWeblogic.sh script

You can then login to console using http://<SOA hostname>:<AdminServer port>/console

In the next post, I will provide the WLST details instead of config.sh for performing tasks in step 5) above.

Hope this helps !!

http://advait.wordpress.com/installing-oracle-database-10g-r2-on-solaris-59/

Spawn, Expect, Send and Interact

Imagine a kind of automation you want to do where you want to telnet to a server within your shell script and carry out the activity on the remote server. There no RSH setup done on the remote server and you have to enter the password non-interactively. Under these kind of circumstances we have a tool called EXPECT.

expect is a unix command present under /usr/local/bin. In one of the scenario, I wanted to automate the installation Oracle Identity manager 9.1.0.1 (The install process will come in another post shortly). But Oracle Identity manager 9.1.0.1 is nither clonable nor it can be silently installed. Trust me, only way to install Oracle identity Manager is to do interactive installation using console mode of GUI mode.

But using expect commad we can simulate the complete installation non-interactively. Here we will see a small example of expect command.

As I said before expect is a binary present in /usr/local/bin directory and expect understand the command like spawn, expect, send and interact. These are the once that I used. Lets take a example where we want to telnet to a server and get the hostname and date (Just to verify that we connected to right server). I will provide the code and will explain the significance later.

#! /usr/bin/expect
spawn telnet mfgops;
expect "login:*";
send "cmsops\r";
expect "Password:*";
send "welcome\r";
expect "(cmsops) cmsops- ";
send "date;hostname;\r";
expect "(cmsops) cmsops- ";
send "exit\r";
expect eof;

Here is how you run this script and the expected output of the same.

-bash-3.00$ expect test.sh
spawn telnet mfgops
Trying 130.35.5.43...
Connected to mfgops.us.oracle.com (130.35.5.43).
Escape character is '^]'.

SunOS 5.6

login: cmsops
Password:
Last login: Fri Aug 14 11:05:42 from adc60020sems.us.
Sun Microsystems Inc.   SunOS 5.6       Generic August 1997
Built on Mon Sep 11 18:02:19 PDT 2000
You have new mail.
*********************************************************************
WARNING WARNING WARNING WARNING WARNING WARNING WARNING WARNING WARNING
*********************************************************************
The cmsops user should no longer be used for osn development. Please
use the pomops user instead.
*********************************************************************
WARNING WARNING WARNING WARNING WARNING WARNING WARNING WARNING WARNING
*********************************************************************
(cmsops) cmsops- date;hostname;
Fri Aug 14 11:06:56 PDT 2009
ap075sun
(cmsops) cmsops- exit
logout
Connection closed by foreign host.
-bash-3.00$

So we run the script using expect (expect test.sh) instead of using sh. You can even remove the expect and use ./ to run the script, but do not use sh, the script wont work.

spawn is used to spawn a command. A command which will expect some prompts.

expect us used to tell which prompts are expected. Here * is used as wild character, in case you are not sure about complete prompt.

send is used to send the inputs to those prompts. Example for login and password we send the appropriate inputs

interact is not used in this program, but in case you want the control back to the user, you can use interact.

We also have a expect module in perl which does almost similar kind of activities, but its more advanced than expect unix command. Expect.pm (perl module) is not installed by default, but you have to install it yourself.

I found this utility wonderful in case of automating something like telnet or any interactive console based installation. A very handy tool.

Hope this helps !!

Reference:

expect man pages.

Cleaning up the system – 11i and R12

Here is the small post which describes the potential placed where log files and trace files are present and can be cleaned up. This is requied when you want to free up space on the system.

Cleaning up R12 instance:

Following are the locations in applmgr side which you can check and clean up. Be careful while cleaning up the log files, because some might be needed for analysis or might contan errors for some reproducible test case. So if you delete some log file which is needed, you might have to redo that work. Usually its a good idea to leave the logs which are 1-2 days old and delete the other older logs

Main location for logs and other not useful files on applmgr side:

Forms dump files : $INST_TOP/logs/ora/10.1.2/forms

Reports Cache : $INST_TOP/logs/ora/10.1.2/reports/cache

Apache logs : $INST_TOP/logs/ora/10.1.3/Apache

OPMN Logs : $INST_TOP/logs/ora/10.1.3/opmn

Its advisable not to remove any logs under $INST_TOP/logs/ora/10.1.3/j2ee directory since these are very important logs and are required frequently for debugging.

Logs for service management : $INST_TOP/logs/appl/admin/log

Concurrent Manager logs : $INST_TOP/logs/appl/conc/log

Concurrent Manager out files : $INST_TOP/logs/appl/conc/out

Other then these directories if you have some patch downloaded at some location, it is advisable to remove those patches once applied.

Main location for logs and other not useful files on oracle side:

Cleanup background_dump_dest, user_dump_dest and core_dump_dest directories on database side. These locations usually have huge trace files.

Check the location of ORACLE_HOME using du -sh command. Usually the size of ORACLE_HOME should be aounr 3-4G. If size of ORACLE_HOME (other then datafiles) is abnormally large like 7-8G or more then you need to investigate which directory is consuming more space and accordingly clean up that directory if appropriate.

In some situation if it becomes impossible to clean up the space (because there are no trace files to cleanup) and you desperately want the space to be available on the file system, then you can connect to database as sysdba and try to reduce the size of temp files using “alter database tempfile .. resize” comamnd. This will release some space and prevent database from crashing in case file system is reaching 100% full.

Again if some patches are downloaded for application, make sure to remove the patches zip files once they are applied.

Some times we upgrade the database to a major release, like from 9i to 10g or from 10g to 11g. In that case its advicable to remove the old ORACLE_HOME. This will not only save space but also will avoid confusion.

Cleaning up 11i instance:

Main location for logs and other not useful files on applmgr side:

Logs for Concurrent manager, forms and reports: $COMMON_TOP/admin/log/$CONTEXT_NAME

Out files for Concurrent managers: $COMMON_TOP/admin/out/$CONTEXT_NAME

Apache Log files: $IAS_ORACLE_HOME/Apache/Apache/logs

Jserv Log files: $IAS_ORACLE_HOME/Apache/Jserv/logs

There are some log files in $APPL_TOP/admin/$TWO_TASK/log and $AD_TOP/log, but its advisable not to delete these logs as they are very important for debugging.

Main location for logs and other not useful files on oracle side:

Cleanup background_dump_dest, user_dump_dest and core_dump_dest directories on database side. These locations usually have huge trace files.

Hope this helps !!

AutoConfig Search Utility

Sometime if we want to find out a variable to be changed in context file, but we dont know the exact name and the meaning of the variable then we can use autoconfig search utility. Search utility will ask for the keyword to be searched for and will generate the report based on the available variables contaning that search keyword. It will also give the description about the autoconfig variables, which is very useful.

Following is the way to use search utility

-bash-3.00$ perl $FND_TOP/bin/txkrun.pl -script=GenCtxInfRep -keyword=mwa -outfile=$OA_HTML/txkContext_Apps.html
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /slot/ems2815/appmgr/inst/apps/az1mq206_rws60043rems/logs/appl/rgf/TXK/txkGenCtxInfRep_Wed_Jun_17_00_04_30_2009.log
Program : /slot/ems2815/appmgr/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/txkGenCtxInfRep.pl started @ Wed Jun 17 00:04:30 2009

*** Log File = /slot/ems2815/appmgr/inst/apps/az1mq206_rws60043rems/logs/appl/rgf/TXK/txkGenCtxInfRep_Wed_Jun_17_00_04_30_2009.log 

The HTML report is generated in /slot/ems2815/appmgr/apps/apps_st/comn/webapps/oacore/html/txkContext_Apps.html

Program : /slot/ems2815/appmgr/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/txkGenCtxInfRep.pl completed @ Wed Jun 17 00:04:59 2009

End of /slot/ems2815/appmgr/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/txkGenCtxInfRep.pl : No Errors encountered
-bash-3.00$

Here I used the keyword as mwa and the report will give all variables having mwa as sub-string. The report will look as shown in this link.

Hope this helps !!

The report will look as shown in this link

Automating FTP from Unix Shell Script

Recently I faced an issue about FTP. I was supposed to ask the name and location of the file as one of the input to the user and get the file at current host where I am running the script. The situation was bit complex. I mean, the inputs from the user were from a GUI tool and that was getting passed to one  of the shell script. One of the challenge that I faced was FTPing the file from some other server to the current server. Because normal FTP command ask for user ID and password and is interactive.

But with little investigation I was able to get the correct command options for FTP that can be used inside a shell script and its a totally non-interactive version. Here is the FTP code I used.

EMSUI_SOURCE_LOCATION=$1
echo "echo \"open statj11" >> $HOME/ftp.sh
echo "quote USER ora1818" >> $HOME/ftp.sh
echo "quote PASS ORA1818" >> $HOME/ftp.sh
echo "bin" >> $HOME/ftp.sh
echo "get $EMSUI_SOURCE_LOCATION $HOME/dump/GC_AGENT/`basename $EMSUI_SOURCE_LOCATION`" >> $HOME/ftp.sh
echo "close" >> $HOME/ftp.sh
echo "quite\" | ftp -n" >> $HOME/ftp.sh

sh $HOME/ftp.sh

Basically I am connecting to statj11 server as ora1818 user. The password for this user is ORA1818. EMSUI_SOURCE_LOCATION is the variable which has name and location of the file to be FTPed. I am taking the name and location (complete path) of the file to be FTPed from the user. (EMSUI_SOURCE_LOCATION=$1)

`basename $EMSUI_SOURCE_LOCATION` will give you the name of the file to be FTPed, which I am using as the name of my FTPed file.

All the above commands are echoed to a file called $HOME/ftp.sh and then I am executing this script which will FTP the required file.

Hope this helps !!

Oracle Password Encryption and Hashing

Introduction

The way Oracle stores the password in database is using Hash algorithm. We know a technique called enryption for storing the password, but it really not a safe idea to implement encryption for storing the password. Because all that matters in encryption is the “key”, which is used to encrypt the password.
In this short post we will see the difference between encryption and Hashing and how it can be used in Oracle database.
Oracle supports encryption but it doesnt use encryption for storing password. Instead it uses hashing for storing the password.

Oracle Encryption:

In oracle database, we can encrypt any string using DBMS_OBFUSCATION_TOOLKIT package. There is a procedure called DSEEncrypt which can be used to encrypt the string

The basic inputs that this procedure ask is input string which you want to encrypt, the encryption key to be used and gives output as encrypted password.
You can use DESDecrypt procedure of the same package to decrypt the password.
You need to decrypt the password using the same key.

SYS@TESTDB.US.ORACLE.COM> declare
 2  var1 varchar2(2000) := 'advait';
 3  l_temp varchar2(100);
 4  opvar varchar2(2000);
 5  begin
 6  l_temp := rpad (var1, (trunc(length(var1)/8)+1)*8, chr(0));
 7  dbms_obfuscation_toolkit.DESEncrypt(input_string => l_temp, key_string => 'oracleencryptionkey', encrypted_string => opvar);
 8  dbms_output.put_line('Enc Output :'||opvar);
 9  dbms_obfuscation_toolkit.DESdEcrypt(input_string => opvar, key_string => 'oracleencryptionkey', decrypted_string => var1 );
 10  dbms_output.put_line('Dec Output :'||var1 );
 11  end;
 12  /
Enc Output :I?8??+)i
Dec Output :advait

PL/SQL procedure successfully completed.

Problem with is method is the encryption key. Everytime we want to veryfy the password we need to know the encryption key. Without encryption key we cannot encrypt or decript the password and hence cannot be verfied. Now if someone comes to know about encryption key, he knows everything.

Oracle Hashing:

Next we will see how the Oracle uses hashing for storing the password in database. Hash is basically a key generated for specific input string. Example, we can define a hashing table which will have several finite entries. With any input string we can have some logic applied on that string and get a hash value for that sting. It could be a simple logic of checking a length of the string multiply that with some number and get a hash value for that from hash table to a very complex algorithm. Also we can hash a string to a hash value but we cannot get back our original string from a hash value. So how does password varification happens.
What Oracle database does it when it receives the password it gets a hash value of that password and stors it in USER$ table. When a user tries to login using the password it will again get the hash value of that password and compare that with hash value stored in USER$ table. If they match then user is in if now he is denied access.

Does this mean that 2 same password (example : welcome) will have same hash value? The answer is yes.
In that case a user sys and user scott has both password as tiger it will hash it to same value and user scott can login as sys using his password. Hence in order to avoid this we should usually hash a biger string instead of just the password. Like for example a ‘username/password’. This combination will be unique and a user with his password can login. If this case even if scott and sys has same password tiger the hash key for scott/tiger and sys/tiger “might” differ.
I said might because even 2 differnt strings can have a same hash value. The best hasing alogithm is the one which tries to give different values for each different input string. Basically we are trying to convert the infinite number of strings into finite number of hash values so there could be same hash values for different input strings.
Below is the simple hashing function we can use in Oracle. Oracle database itself uses some complete function to store the password.

SYS@TESTDB.US.ORACLE.COM> declare
 2  var1 number;
 3  begin
 4  var1 := dbms_utility.GET_HASH_VALUE('welcome',1000000000, power(2,30));
 5  dbms_output.put_line(var1);
 6  end;
 7  /
1291289758

PL/SQL procedure successfully completed.

So the hash value that we get here is a number. You can convert the same in hexadecimal as well.
GET_HASH_VALUE function takes 3 inputs and returns number. The 3 inputs that it takes are 1) the input string to be hashed 2) base value to be added to the hash number that we get 3) size of the hash table to be used
You can vary your input and change the hash value that you can get. Example instead of adding a constant value of 1000000000 to hash value I can as well give power(10,length(‘welcome’)). So depending on the input string my base value to be added will change as shown below.

SYS@TESTDB.US.ORACLE.COM> declare
 2  var1 number;
 3  begin
 4  var1 := dbms_utility.GET_HASH_VALUE('welcome',power(10,length('welcome')),power(2,30));
 5  dbms_output.put_line(var1);
 6  end;
 7  /
301289758

PL/SQL procedure successfully completed.

Another logic that you can apply for securing your password is encrypt the password and then get a hash key for the same and then store the hash key like below

SYS@TESTDB.US.ORACLE.COM> declare
 2  var1 varchar2(2000) := 'welcome';
 3  l_temp varchar2(100);
 4  opvar varchar2(2000);
 5  var2 number;
 6  begin
 7  l_temp := rpad (var1, (trunc(length(var1)/8)+1)*8, chr(0));
 8  dbms_obfuscation_toolkit.DESEncrypt(input_string => l_temp, key_string => 'oracleencryptionkey', encrypted_string => opvar);
 9  var2 := dbms_utility.GET_HASH_VALUE(var1,power(10,length(var1)),power(2,30));
 10  dbms_output.put_line('Enc Output :'||opvar);
 11  dbms_output.put_line('Hash value :'||var2);
 12  end;
 13  /
Enc Output  B;FAE
Hash value :301289758

PL/SQL procedure successfully completed.

So in this case even if the user is able to find the encryption key, he wont know the hashing algorithm and he wont be able to crack.

Hope this helps !!

Understanding SQL Trace file – Oracle Database 10g

Introduction

Many times when the application runs slow one of the usual approach for a DBA is to enable tracing and get the SQL queries the application is running. We check some of the SQL that application runs is becoming bottle neck here. But getting a trace file for different application can be very tricky. The reason is that when a user is connecting from the front end application its very difficult for us (the DBA) to track the session information. When he login to the application and does some database activity a new session will get created, its difficult to know if that session belongs to the user we intend to monitor. Also if the application is written efficiently then inbetween the application usage a client might get disconnected and then reconnect again when some database operation needs to be performed. Again the SID changes. So its very difficult for a DBA to keep tracks of sessions an application is changing.

So in order to get around this problem we have something called End-to-End application tracing.

End to End Tracing:

In end to end application tracing, instead of tracing the session we trace using different parameters like

Client identifier – specifies an end user based on the logon Id, such as HR.HR
Service – specifies a group of applications with common attributes, service level thresholds, and priorities; or a single application, such as ACCTG for an accounting application
Module – specifies a functional block, such as Accounts Receivable or General Ledger, of an application
Action – specifies an action, such as an INSERT or UPDATE operation, in a module
Session – specifies a session based on a given database session identifier (SID), on the local instance
Instance – specifies a given instance based on the instance name

To gather the appropriate statistics using PL/SQL, you need to enable statistics gathering for client identifier, service, module, or action using procedures in the DBMS_MONITOR package.

Example if you want to enable statistics gathering for user OE. You can enable statistics for this CLIENT_ID using following procedure

EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE(client_id => ‘OE.OE’);

So when ever any clients logs into the database using OE as user ID, all the activities for that user will get traced in the trace file. You can see the client identifier using CLIENT_IDENTIFIER column in v$session table.

EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_DISABLE(client_id => ‘OE.OE’); is used to disable the tracing for that client ID.

Similarly you can also enable the tracing for any service, any module, for an required action etc. For more information on end to end tracing, kindlly check the link in reference section of this post.

TKPROF:

coming to the contents of trace file. Initially when you get a trace file generated you wont be able to understand as its not in a readable format. You need to use a utility called TKPROF inorder to make the trace file readable.
The basic inputs to tkprof are 2 file names. First filename will be the name of the trace file which you want to format and second file name will be filename for output file.
Example: tkprof jdbc_traces.trc jdbc_traces.txt

Contents of Trace file:

After you use the tkprof utility you will get the output file in readable format. The complete trace file contains well formated blocks. Every block contains the SQL, statistics about that SQL, Miss statistics, query Explain plan and wait statistics.
The sample block from one of the trace file is as shown below

********************************************************************************

SQL ID : 74637j0wuhxfx
Select BEG_VERSION, EXT_VERSION, FIRST_DATE, FIRST_USER, IND_CHANGE,
 INT_VERSION, I_PROJECT, LAST_DATE, LAST_USER, PROJECT_CODE, PROJECT_NAME,
 PRV_VERSION, V_LAST_DATE
from
 SNP_PROJECT where I_PROJECT = :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        9      0.00       0.00          0          0          0           0
Execute    823      0.02       0.02          0          0          0           0
Fetch      823      0.05       0.05          0       1646          0         823
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1655      0.08       0.08          0       1646          0         823

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100

Rows     Row Source Operation
-------  ---------------------------------------------------
 1  TABLE ACCESS BY INDEX ROWID SNP_PROJECT (cr=2 pr=0 pw=0 time=0 us cost=1 size=108 card=1)
 1   INDEX UNIQUE SCAN PK_PROJECT (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 81820)

Elapsed times include waiting on following events:
 Event waited on                             Times   Max. Wait  Total Waited
 ----------------------------------------   Waited  ----------  ------------
 SQL*Net message to client                     823        0.00          0.00
 SQL*Net message from client                   822      103.76        173.63
********************************************************************************

Now Lets understand the meaning of these statistics. If we consider the call statistics table we have columns like count, cpu, elapsed, disk, query, current and rows. The meaning of these columns will be given at the starting of your trace file.
COUNT   – Number of times a statement was parsed, executed, or fetched.

CPU     – Total CPU time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.

ELAPSED – Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.

DISK    – Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls.

QUERY   – Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries.

CURRENT – Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.

ROWS    – Total number of rows processed by the SQL statement. This total does not include rows processed by subqueries of the SQL statement.
Also the rows gives statistics about 3 calls :- Parse, Execute and Fetch
PARSE   – Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.

EXECUTE – Actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.

FETCH   – Retrieves rows returned by a query. Fetches are only performed for SELECT statements.
For example in the above table we have following statistics
- SQL query has been parsed 9 times

- SQL query got executed 823 times

- fetch operation has been done 823 times

- CPU time taken for parsing is less then 1/100th of a second

- CPU time for execute was 0.02 sec

- CPU time for fetch was 0.06 sec

- there was no elapsed time involved for parsing

- elapsed time for execute was 0.02 sec

- elapsed time for fetch was 0.05

- No block was fetch from disk, so no physical reads

- blocks received in consistent mode (1646) during fetch operation. Since this is a Select statement the blocks are shown during Fetch operation. If its a DML statement then blocks will be shown during Execute operation.

- none of the blocks are received in current mode. You will see this value gets updated during DML statement or if some DML activites are done

- Total number of rows processed are 823
Other then this you can also see the Misses in library cache during each call. If there is no miss then it wont be mentioned. The 1 miss for the SQL is very much acceptable since when a SQL runs for the first time it need to be parsed and executed and execution plan will be stored. So parse and execute call will have 1 misses.

If you see the statement parse call happened 9 times but the miss count is only 1, meaning that the statement was parsed only once and was stored in library cache. For next 8 parses the same parsed statement from library cache was used. So we have miss 1 and hit 8.

Similarly execution plan was made only once and 822 times Oracle used same execution plan from library cache.
Next it gives the execution plan for the query as shown below

Rows     Row Source Operation
-------  ---------------------------------------------------
 1  TABLE ACCESS BY INDEX ROWID SNP_PROJECT (cr=2 pr=0 pw=0 time=0 us cost=1 size=108 card=1)
 1   INDEX UNIQUE SCAN PK_PROJECT (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 81820)

Here we have several parameters shown in bracket (cr=2 pr=0 pw=0 time=0 us cost=1 size=108 card=1)

cr = Consistent Reads

pr = Physical Reads

pw = Physical Writes

time = time taken by this step in microseconds

cost = cost incured by the step

size = represent the size of data in that step

card = cardinality

Finally at the end SQL trace give the wait time event for that query, the max wait time and total time it waited.

Concluding from the Traces:

With the values above we need make conclusions and decide whether to tune the SQL or not. Unless we have a locking issue or bad performing SQLs we should not worry about CPU time or the elapsed time. because timings come into consideration only when we have bad performing SQLs. The key is the number of block visits, both query (that is, subject to read consistency) and current (that is, not subject to read consistency). Segment headers and blocks that are going to be updated are acquired in current mode, but all query and subquery processing requests the data in query mode.

References:

Oracle® Database Performance Tuning Guide

Hope this helps !!

Oracle Database Recovery – Basic Concept

Its been a really long time since I wrote a post. These days seems to be very busy with all Fusion middle ware floating around me. Anyways, here I start again. This post is a kind of kickoff post. I was going through Expert one-on-one book by Thomas Kyte and read a wonderful basic recovery related article from the book. Ohh by the way…everyone should read Expert one-on-one atleast once in his/her life. A must read book, not only for DBA but specifically for developers.

So here is the recovery concept go. This is a very basic concept about how Oracle does crash recovery. I will be putting the same story here, but in my words.

We know the basic DML transactions are insert, update and delete. We will consider a simple transaction and understand : what data gets generated during the transaction, where it is stored and how oracle database recovers in case of crash.

Lets say an insert statement on EMP table

insert into EMP (EMPNO, NAME, JOB, SAL) values (10, ‘Deo’, ‘DBA’, 100);

Following things will happen

• This statement will modify the data blocks in buffer cache
• This statement will also fetch the index blocks in the buffer cache and modify the same
• This statement will generate undo data in buffer cache
• This statement will generate redo data in redo log buffer. This entries in redo log buffer is to protect the blocks in buffer cache.
  

So the picture in SGA will looks as shown below

So till now the user has executed an insert statement, its not yet committed. The blocks in buffer cache has been protected by entries in redo log buffer.

What if the system crashes at this stage?

If the system crashes at this point, then everything in the memory will get wiped out. It will be like nothing happened. There is no record of this transaction in any of the files (datafiles or redo log file). This is OK. We dont need anything that was in SGA.

What if the buffer cache fills up at this stage?

We are have done the insert, but we have not yet committed. Changed blocks are in buffer cache. Also redo blocks are not yet flushed to online redo log files and are still present in redo log buffer. At this stage suppose db buffer cache fills up. Now DBWR process needs to make a room for new blocks to be fetched for some other user session. In that case DBWR will ask LGWR process to flush the redo log buffer into online redo log files. So the redo log records generated by insert transaction will get flushed to online redo log file. Note that we have NOT yet commited the insert record.

This behavior of DBWR to ask LGWR process to flush redo data is perfect. imagine what will happen if the data in buffer cache gets written to database files before redo data gets flushed into redo log files. If system craches at a point after buffer cache data gets updated in data files and before redo data from redo log buffer gets flushed into online redo log files, then when the database reboots, there is no way to tell whether our insert transaction was commetted or not. Since our transaction was not commited, how will Oracle know that it needs to rollback the insert transaction. That’s why data in redo log buffer gets flushed to online redo log files first. The scenario will look like as shown in fig 2 below.

Now the blocks in database buffer cache are protected by the entries in redo log files. Some of this blocks might get written to datafiles in order for DBWR process to make room, or none of them gets written to datafiles if DBWR process is able to make room without touching these blocks. It depends.

What if the system crashes at this stage?

At this stage if the system crashes then all the data in memory will get wiped out. The data blocks in buffer cache will be gone. But we still have the entries in online redo log files. So when the oracle performs crash recovery it reads the online redo log file and roll-forward the transactions. Redo log file has data to regenerate the data blocks that were present in the buffer cache before system crash. So oracle will read the redo log files and generate the data block, the index block and undo data block as well. Note here that undo data block is also treated here as a normal data block and changes to undo data block will make redo entries. So while doing crash recovery it will generate the 3 data blocks and scenario will be similar to fig 2. On further reading of redo log file, oracle will come to know that this insert transaction was never committed. So Oracle will use the undo data generated for this transaction to rollback the data. This will delete the insert entry.

Now before system crash if the data in buffer cache has been applied to datafiles then oracle will reapply the latest data generated and overwrite the previously inserted data. Now everything is back the way it was.

This scenario covers the rudimentary details of crash recovery. Here the recovery is a 2 step process, first oracle roll forwards all the transactions and then it starts rolling back the transactions which are not committed (from the undo data that it generates during roll forward process).

Similar concept applies to the update and delete statements as well.

What happens to the undo data?

The undo data which gets generated during transactions (example in the above scenerio) will be stored for some time. If there is no space left in memory this undo data is flushed to undo segments in undo tablespace and it will reside in undo tablespace for a minimum time period defined by UNDO_RETENTION init.ora paramter. This undo data is used during rolling back a transaction. Please note that for rolling back a transaction redo log files are never used. Redo logs and archive logs are referred (or read) only during recovery of database. During all other time the data is written to redo logs files but never read from it. So for rolling back the transaction undo data saved in undo tablespace is required.

Hope this helps !!

References:

Expert One-on-One – By Thomas Kyte

‘    


    
’


,
*








    

’    

    
=