APPLICATION DBA: 2014

Tuesday 8 April 2014

Oracle SOA

MDS-01370: MetadataStore configuration for metadata-store-usage "soa-infra-store" is invalid

In Windows 7 OS we might face an issue where SOA_INFRA is down even after starting SOA managed server,In this case we need to be aware of the below error.This error can be resolved as given below.

MDS-01370: MetadataStore configuration for metadata-store-usage "soa-infra-store" is invalid

Error Message:

Caused By: oracle.mds.exception.MDSExceptionList: MDS-01329: unable to load element "persistence-config"
MDS-01370: MetadataStore configuration for metadata-store-usage "soa-infra-store" is invalid.
ORA-04063: package body "DEV_MDS.MDS_INTERNAL_SHREDDED" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DEV_MDS.MDS_INTERNAL_SHREDDED"
ORA-06512: at line 1

Resolution:

Step 1: Uninstall the XE Database by launching Oracle XE graphical utility

Step 2:Install the XE Database

Step 3:Set the Oracle Environment variables 'RCU_JDBC_TRIM_BLOCKS=TRUE'
and Go to the repository creation utility Home(rcuHome) and BIN path and execute the rcu.bat batch file
C:\Users\RafiAlvi>set RCU_JDBC_TRIM_BLOCKS=TRUE

C:\Users\RafiAlvi>cd D:\Rafi_docs\Oracle_SOA_INSTALL\ofm_rcu_win_11.1.1.4.0_disk1_1of1\rcuHome\BIN

C:\Users\RafiAlvi>D:

D:\Rafi_docs\Oracle_SOA_INSTALL\ofm_rcu_win_11.1.1.4.0_disk1_1of1\rcuHome\BIN>rcu.bat

Step 4:Drop all the Schema owner and Tablespaces and
then run RCU again to Re-create the SOA Suite schemas and tablespaces.
The list of Component,Schema Owner and Tablespaces are given below:

Component Schema Owner Tablespaces
SOA Infrasture DEV_SOAINFRA DEV_SOAINFRA
BAM DEV_ORABAM DEV_ORABAM
UserMessaging service DEV_ORASDPM DEV_IAS_ORASDPM

Step 5:Find and compile the Invalid objects in the XE Database

SQL> COLUMN object_name FORMAT A30
SQL> SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

OWNER OBJECT_TYPE
------------------------------ -------------------
OBJECT_NAME STATUS
------------------------------ -------
DEV_ESS PACKAGE BODY
ESSPURGE INVALID

DEV_ESS PROCEDURE
CLOSE_FILE INVALID

DEV_ESS PROCEDURE
DEBUG_PURGE INVALID

OWNER OBJECT_TYPE
------------------------------ -------------------
OBJECT_NAME STATUS
------------------------------ -------
DEV_ESS PROCEDURE
LOG_ERROR INVALID

DEV_ESS PROCEDURE
LOG_MESSAGE INVALID

DEV_ESS PROCEDURE
WRITE_LINE INVALID

OWNER OBJECT_TYPE
------------------------------ -------------------
OBJECT_NAME STATUS
------------------------------ -------
DEV_SOAINFRA PROCEDURE
PURGE_B2B_INSTANCES_DOWN INVALID

DEV_SOAINFRA PROCEDURE
PURGE_RUNTIME_WITH_TIMEOUT INVALID

8 rows selected.

SQL> conn DEV_SOAINFRA
Enter password:
Connected.
SQL> ALTER PROCEDURE PURGE_B2B_INSTANCES_DOWN COMPILE;

Procedure altered.

SQL> ALTER PROCEDURE PURGE_RUNTIME_WITH_TIMEOUT COMPILE;

Procedure altered.

SQL> CONN DEV_MDS
Enter password:
Connected.
SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'DEV_MDS');

PL/SQL procedure successfully completed.

Step 6:Now Bounce the Weblogic admin server and SOA Managed Server

weblogic_server_stop.bat:
cd D:\oracle_middleware\user_projects\domains\Soa_suitedev_domain\bin
D:
stopWebLogic.cmd

weblogic_server_start.bat:

cd D:\oracle_middleware\user_projects\domains\Soa_suitedev_domain\bin
D:
startWebLogic.cmd

soa_server_stop.bat:

cd D:\oracle_middleware\user_projects\domains\Soa_suitedev_domain\bin
D:
stopManagedWeblogic.cmd soa_server1

soa_serve_start.bat:

soa_server_start.bat:

cd D:\oracle_middleware\user_projects\domains\Soa_suitedev_domain\bin
D:
startManagedWeblogic.cmd soa_server1

Happy SOA learning...

Tuesday, August 9, 2011

Step by step Oracle SOA suite 11g installation on RHEL 5.3

Hi,
Below are the lists of steps I have followed for installing Oracle SOA Suite 11g,I have not put screenshots but tried to put explain without screenshots.

Oracle SOA Suite 11g Installation plan:

Downloads:
1)DATABASE :11.2.0.1.0(x86 32-bit LINUX):
2)WEBLOGICSERVER
3)RCU(REPOSITORY CREATION UTILITY
4) SOA
5) JDEVELOPER
6)SOA EXTENSION

Preparatory step:
[oracle@fmwtestnode /]$ mkdir Web_logic_soft
[oracle@fmwtestnode /]$ su
Password:
[root@fmwtestnode /]# mkdir Oracle11gR2soft
[root@fmwtestnode /]# chown oracle:oinstall /Oracle11gR2soft/
[root@fmwtestnode /]# chmod -R 755 /Oracle11gR2soft/
[root@fmwtestnode /]# mkdir Web_logic_soft
[root@fmwtestnode /]# mkdir Jdev_soft
[root@fmwtestnode /]# mkdir SOA_SUITE_11g_soft
[root@fmwtestnode /]# chown -R oracle:oinstall /Web_logic_soft/
[root@fmwtestnode /]# mkdir Reposit_create_soft
[root@fmwtestnode /]# chown -R oracle:oinstall /Jdev_soft/
[root@fmwtestnode /]# chown -R oracle:oinstall /SOA_SUITE_11g_soft/
[root@fmwtestnode /]# chown -R oracle:oinstall Reposit_create_soft/
[root@fmwtestnode /]# chmod -R 755 /Oracle10gsoft/
[root@fmwtestnode /]# chmod -R 755 /Web_logic_soft/
[root@fmwtestnode /]# chmod -R 755 /Reposit_create_soft/
[root@fmwtestnode /]# chmod -R 755 /Jdev_soft/
[root@fmwtestnode /]# chmod -R 755 /SOA_SUITE_11g_soft/

Check your browser:
Enterprise Manager requires Firefox 3, Safari 4+ or IE 7+.
BAM requires IE 7 or 8

Check your JDK:
The WebLogic server installation exe is packaged with a JDK. The JDeveloper exe also comes with a JDK.
If you are going to install WebLogic server and JDeveloper on the same machine,we use the JDK from WebLogic for JDeveloper too. However, if you are going to install on separate machines, you need to have Oracle Sun Java 1.6 update 21+ JDK for JDeveloper. You can get it from the Oracle Sun downloads page. Be sure to get the JDK not the JRE: http://java.sun.com/products/archive/

Installations:

1)Install the database:
Check my previous post for Oracle Database 11g rel2 installation on linux.

2) Install WebLogic server:
Weblogic pre-installation steps:
Check the Certification matrix to check the supported Weblogic server for
Various Operating system.For our operating system RHEL 5.3 the supported
Weblogic server software is 10.3.

SQL> select name from v$database;

NAME
---------
FMWDB

SQL> select status from v$instance;

STATUS
------------
OPEN
SQL> show parameter processes
SQL> alter system set processes=1000 scope=spfile;
SQL> shutdown immediate
SQL> startup
SQL> show parameter processes

[root@fmwtestnode /]# mkdir -p Middleware/SOASuite11gR1
[root@fmwtestnode /]# chown oracle:oinstall /Middleware/SOASuite11gR1/
[root@fmwtestnode /]# chmod -R 755 /Middleware/SOASuite11gR1/

We have to create Middleware home directory for installation FUSION MIDDLEWARE COMPONENTS in this particular by granting the ownership to the ‘oracle’ user with all the required privileges

1)DATABASE
2)WEBLOGIC SERVER
3)RCU
4)SOA
5)JDeveloper
6)SOA Extension

Once your database is ready, we can install the next step, the WebLogic server.
When you are ready to install, complete the following:

I granted all the ownership to the oracle user and permission where the executable of weblogic software is present.I launched the installation on vm server as follows:

Here ,I have entered the Fusion Middlware Home path which has all the ownership and permission so that ‘oracle’ user can install weblogic server here.

Choose 'Graphical' method to install Weblogic server to have pictorial representation:

Press ‘Enter’ and proceed……..

Press ‘Enter’……..

Make uninformed to ‘yes’ as shown below in screenshot and proceed:

Select Typical, click Next

Press ‘Enter’ and proceed……..

Here,As we see above Along with Weblogic platform 10.3.5.0,JDKs and other JDBC ,weblogic server plugins and other products get installed…

Press ‘Enter’ proceed…….

That’s it.Weblogic installation is proceeding as see in the above screen shot…………..

First JDK files are installed,OCM,domains…….

As we see from the screenshot Weblogic installation Completed successfully….
Press ‘Enter’ and it is done……….

Once the Weblogic installation is done we need to Configure Domain,And we have to make sure weblogic server is up & running.

What is a weblogic server domain?>
A domain is the basic administration unit for Weblogic Server instances. A domain consists of an Administration Server and Managed application servers and resources. The main configuration file in the domain is config.xml, which is in the domain directory(where you install your weblogic server domain) under config directory.
We can use Administration Server to do the configuration tasks. A web based console comes with the domain installation, where an administrator can login and perform the administration tasks.
Weblogic domain can have multiple servers in a cluster or multiple clusters. A domain can have an Administration Server which can act as an Administration Server and managed server. This architecture is mostly used by the developers in developing and deploying applications in there local environments.
Production and Development Modes

Weblogic domain can be configured in two different modes:
1.Development Mode
2.Production Mode

1. Development Mode: In the development mode of configuration,Weblogic server domain has minimum security and is used for the development of application purposes. In this mode, you can also auto-deploy the application.
2. Productions Mode: This is the final mode of the domain configuration, where you deploy the application which will be directly used by the customers. This is a very important environment, with maximum security.
Now, as we got a general idea on what a weblogic server domain is, we will now configure the weblogic server domain on Linux operating system can be done as follows:

Step 1: Go to /Middleware/SOASuite11gR1/wlserver_10.3/common/bin and look for file name "config.sh".

[oracle@fmwtestnode bin]$ cd /Middleware/SOASuite11gR1/wlserver_10.3/common/bin
[oracle@fmwtestnode bin]$ ls -altr
total 128
-rwxr-x--- 1 oracle oinstall 13740 Jul 28 11:10 wlsifconfig.sh
drwxr-x--- 11 oracle oinstall 4096 Jul 28 11:10 ..
-rwxr-x--- 1 oracle oinstall 761 Jul 28 11:11 wlst.sh
-rwxr-x--- 1 oracle oinstall 31126 Jul 28 11:11 wlscontrol.sh
-rwxr-x--- 1 oracle oinstall 3298 Jul 28 11:11 upgrade.sh
-rwxr-x--- 1 oracle oinstall 2087 Jul 28 11:11 unpack.sh
-rwxr-x--- 1 oracle oinstall 1448 Jul 28 11:11 stopDerby.sh
-rwxr-x--- 1 oracle oinstall 4082 Jul 28 11:11 startDerby.sh
-rwxr-x--- 1 oracle oinstall 2579 Jul 28 11:11 pack.sh
-rwxr-x--- 1 oracle oinstall 2274 Jul 28 11:11 config.sh
-rwxr-x--- 1 oracle oinstall 2122 Jul 28 11:11 config_builder.sh
-rwxr-x--- 1 oracle oinstall 22511 Jul 28 11:11 commEnv.sh
-rwxr-x--- 1 oracle oinstall 2746 Jul 28 11:11 setPatchEnv.sh
-rwxr-x--- 1 oracle oinstall 4156 Jul 28 16:36 startManagedWebLogic.sh
drwxr-xr-x 3 oracle oinstall 4096 Jul 28 16:36 servers

Step 2: Run the 'config.sh' file for the domain configuration.
‘Config.sh’ will do the Domain Configure.There are 3 ways you can do this configuration
1)Graphical
2)Console
3)Silent
We are using ‘Graphical’ method as it display all the information to our screen…
For doing this we need to set the ‘DISPLAY’ as follows:
Check ‘xclock’ command is working and showing the current time.If it shows than we just need to export ‘DISPLAY’ environment Variable.

$xclock
$ export DISPLAY
$./config.sh

Proceed with the configuration,supply 'weblogic' user with credential.Choose
Development or Production depending on our requirement and obtain the
final URL.After completion of Domain configuration,we will obtain:

Domain Location:/Middleware/SOASuite11gR1/user_projects/domain/base_domain
Admin Server URL:http://fmwtestnode.apac.xyz.com:7001/console

3)Install database schema using RCU (REPOSITORY CREATING UTILITY)

Useful pre-requistics steps:

[oracle@fmwtestnode admin]$ hostname
fmwtestnode.apac.bosch.com
[oracle@fmwtestnode admin]$ tnsping fmwdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:23

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/u01/app//product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = fmwdb.apac.bosch.com)))
TNS-12541: TNS:no listener
[oracle@fmwtestnode admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:30

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /u01/app//product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app//product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/diag/tnslsnr/fmwtestnode/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fmwtestnode.apac.bosch.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 29-JUL-2011 18:26:32
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app//product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/diag/tnslsnr/fmwtestnode/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fmwtestnode.apac.bosch.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@fmwtestnode admin]$ tnsping fmwdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-JUL-2011 18:26:40

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/u01/app//product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = fmwdb.apac.bosch.com)))
OK (30 msec)

2) Please do below step in Database side,If you are installing all the Oracle Fusion Middleware products,For SOA installation alone setting open_cursors=300,but as we are installating all the Oracle Fusion middleware products we need to set open_cursors=1000.

Conn “/as sysdba” in Database.

SQL> alter system set open_cursors=1000;

System altered.

SQL> show parameter open_cursors

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 1000

Now it is time to set up the database schema.
Note: If we have installed SOA 11g before and you are not upgrading, you must drop your existing database schema before reinstalling or create a second schema for this installation. We cannot reuse an existing schema.

Configure Schema using RCU
Now create the new schema.

Give the ownership and permission to ‘oracle’ user to run the repository creation utility.

[root@fmwtestnode /]# chown oracle:oinstall /Reposit_create_soft/
[root@fmwtestnode /]# chmod -R 755 /Reposit_create_soft/

[root@fmwtestnode Reposit_create_soft]# su - oracle
[oracle@fmwtestnode ~]$ pwd
/home/oracle
[oracle@fmwtestnode ~]$ cd /Reposit_create_soft/
[oracle@fmwtestnode Reposit_create_soft]$ ls -altr
total 389240
-rwxr-xr-x 1 root root 398173821 Jul 26 18:53 ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip
drwxr-xr-x 2 oracle oinstall 4096 Jul 27 10:36 .
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
[oracle@fmwtestnode Reposit_create_soft]$ unzip ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip

[oracle@fmwtestnode /]$ cd Reposit_create_soft/
[oracle@fmwtestnode Reposit_create_soft]$ ls -altr
total 389244
drwxr-xr-x 32 oracle oinstall 4096 Apr 26 05:04 rcuHome
-rwxr-xr-x 1 root root 398173821 Jul 26 18:53 ofm_rcu_linux_11.1.1.5.0_disk1_1of1.zip
drwxr-xr-x 37 root root 4096 Jul 28 10:26 ..
drwxr-xr-x 3 oracle oinstall 4096 Jul 28 11:41 .
[oracle@fmwtestnode Reposit_create_soft]$ cd rcuHome/
[oracle@fmwtestnode rcuHome]$ ls
assistants common inventory jdk lib nls oraInst.loc plsql rcu relnotes sqlplus xdk
BC4J css javavm jlib modules oracleRoot.sh owm portal rdbms root.sh srvm
bin instantclient jdbc ldap network oracore perl precomp readme.htm sqlj util
[oracle@fmwtestnode rcuHome]$ cd bin

Go to path ‘/Reposit_create_soft_/rcuHome/bin’ as seen in the screenshot.

$ export Display

On Linux(UNIX):
./rcu

Give all the permission to the ‘rcu’ file to launch the installation:

Note: During installation, give the details of the Oracle database created in step 2, select all the SOA schemas to install and select default settings for all other options during the installtion.

Login as ‘root’ user:
#xhost +
To set display correctly and for avoiding java related errors:

$./rcu =>For installing repository Creation Utility.

On the welcome screen, click Next
Select Create

Click Next
Enter the database information
Before that:
[oracle@fmwtestnode admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 29 18:19:44 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user sys identified by fmwsysdba;

User altered.

Enter the information as above.

Click Next

When complete, click OK. The utility moves to the next page – with a slight delay, just wait for it.

On the Select Components screen, enter DEV in the field for creating a new prefix.
Select the component SOA,DATA INTEGRATOR and BPM Infrastructure and all the components for our Oracle Fusion Middleware setup..
Dependent schemas are selected automatically.

Click Next
The pre-requisites for this step are checked.

Please do below step in Database side and click ok:

Conn “/as sysdba” in Database.

SQL> alter system set open_cursors=1000;

System altered.

SQL> show parameter open_cursors

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 1000

When completed, click OK

Select the radio button to Use the same password for all schemas.
Enter a schema password. The password welcome123 is assumed in this document, but we should choose your own secure password or a different one for each schema and be sure to record your passwords as you will need them later

Click Next

Accepting the defaults, click Next

When the pre-requisites for this step are completed, click OK

Click Create to create the tables. This takes about 60 minutes,as we are creating the repository
For all the fusion middleware products. ‘Portal’ repository creation take more than 30 minutes.

I took screenshots of this step:

COMPLETION SUMMARY :

When completed, click Close
Our Repository Creation for All fusion Middleware product is Completed…

4)Install SOA Suite 11g:

1. Download the software from here:
http://download.oracle.com/otn/nt/middleware/11g/ofm_soa_generic_11.1.1.5.0_disk1_1of1.zip
Also present in our shard drive:
\\bmh2fs01\bmhapp01_projects\Ebs1_Vss\DB_services\FMW\FMW_SOFTWARES\ORACLE_SOA_SUITE_INSTALL_SOFT

Once the schema has been created, you are ready to install the SOA server.
Login as ‘root’ user:

#xserver +
#xclock

Perform the following:
unzip ofm_soa_generic_11.1.1.5.0_disk1_1of2.zip

[root@fmwtestnode /]# cd SOA_SUITE_11g_soft/
[root@fmwtestnode SOA_SUITE_11g_soft]# unzip ofm_soa_generic_11.1.1.5.0_disk1_2of2.zip
[root@fmwtestnode SOA_SUITE_11g_soft]# ls
Disk1 Disk2 Disk3 Disk4 Disk5

Give 'Oracle' user ownership and permission to install Oracle SOA Suite software.
root@fmwtestnode /]#chown Oracle:ointall /SOA_SUITE_11g_soft/Disk*
Login as ‘oracle’ user and do the Oracle SOA software installation:

[oracle@fmwtestnode Disk1]# cd /SOA_SUITE_11g_soft/Disk1/
[oracle@fmwtestnode Disk1]# ls -altr
total 108
-rwxrwxr-x 1 root root 73728 Aug 21 2009 setup.exe
-rwxrwxr-x 1 root root 11460 Oct 28 2009 runInstaller
drwxrwxr-x 10 root root 4096 Nov 4 2009 install
drwxr-xr-x 5 root root 4096 Nov 4 2009 .
drwxrwxr-x 9 root root 4096 Nov 4 2009 doc
drwxr-xr-x 13 root root 4096 Nov 4 2009 stage
drwxr-xr-x 5 oracle oinstall 4096 Aug 1 13:59 ..

Login as ‘root’ user:
#xhost +
To set display correctly and for avoiding java related errors:

$./runInstaller -jreLoc /Middleware/SOASuite11gR1/jdk160_24

When the install wizard welcome screen comes up, click Next

Now ,All Prerequistics checks are passed…With no errors……..

Click Next

On the Specify Installation Location screen, select the Middleware home:
/Middleware/SOASuite11gR1/
Accept the default Oracle home: Oracle_SOA1

Click Next

I created one response file to see the installation flow,Before going for install.

Click Install

Thats it SOA Installation started Successfully,

click Next…
Very much happy to see below screen.Our Oracle SOA suite 11g installation finished successfully.

Click Finish………

Create the SOA domain:

1. Perform the following:
$cd /Middleware/SOASuite11gR1/Oracle_SOA1/common/bin
$./config.sh

Choose Extend to existing Weblogic domain
Click 'Next'

Select Generate a domain configured automatically to support the following products:
Check all to select all the products…
Domains

Click 'Next'

Name:weblogic
Pass:weblogic123

Click ‘Next’

Select the JRocket SDK as it will yield beter performance for Oracle SOA and leave Development Mode checked

Check 'Oracle SOA Suite - 11.1.5.0 [Oracle_SOA1]'
Check 'Oracle Enterprise Manager - 11.1.1.0 [oracle_common]'
Check 'Oracle Business Activity Monitoring - 11.1.5.0 [Oracle_SOA1]'
Click 'Next'
Click 'Next'
User password: welcome123
Confirm user password: welcome123
Click 'Next'
Click 'Next'
Check 'BAM Schema', 'SOA Infrastructure', 'User Messaging Service', 'OWSM MDS Schema', 'SOA MDS Schema'
DBMS/Service: orcl.ipnweb.com
Host Name: oradev.ipnweb.com
Schema Password: welcome1
Click 'Next'
Click 'Next'
Click 'Next'

Click 'Create'

If all are successful, click Next, otherwise click Previous and correct any errors.

Now you must choose whether to have your server run as a managed server or in a single server configuration.
As we have chosen the developer’s configuration, there will be one admin server and one managed server for BAM.
Don’t check any of the boxes here and click Next, unless you want to make changes to the default configuration

Click Next

This is the summary page for the Configuration. Click Extend to extend to the domain.
Wait for the create to finish – takes just a minute

Check the box to start the admin server and Click Done.

Add the machine entry:
We have to add the host entry in windows 'hosts' file.

Internet explorer settings:
Go to Tools=>Internet Options=>Connections=>LAN Settings and Uncheck Use automatic configuration script =>click ok =>click ok

Now access the Admin Server URL:

Admin Server URL:
http://fmwtestnode.apac.abc.com:7001

Note:We have to use compatible version of Oracle Repository creation utility and Oracle SOA i.e If rcu is 11.1.1.5.0 than use SOA also with the same version
11.1.1.5.0

Weblogic Server

Apex Deployment on Weblogic Serve

I started my Oracle fusion middleware journey with some good experiments.
Below are the steps which I performed for deploying Apex on Weblogic Server.
(Installation and configuration of Weblogic Server I will cover in separate post and Apex installation and configuration you can get from Apex_tasks Label link)

Apex can be integrated with weblogic by follwing below experiment:

I)
Case study:

WebLogic Server and APEX:
When a WebLogic Server (WLS) is the primary Web server, accessing APEX pages though the WLS requires a proxy. The configuration of APEX generally follows one of two configurations – Apache with mod_plsql or the Embedded PL/SQL gateway. When WebLogic (without Apache) is the main HTTP server, getting APEX to surface on the same port as WebLogic requires some form of proxy.

Overall Approach:
This solution creates a very simple Web Application that invokes a Java Proxy Servlet when a user tries to access APEX pages. Wrapping this Web Application around the Java Proxy Servlet lets the WLS serve APEX without any port conflicts. The WLS Proxy Servlet is a fully documented out of the box tool.
To create and deploy the Web Application simply build the files outlined in this document, deploy the application and then access APEX.

Exploded deployment:

For convenience, this solution takes advantage of the exploded deployment feature of the WLS. In addition to the ability to deploy jar, war and ear files, the WLS can deploy Web Applications as an exploded directory that contains the same contents of the archive file. An exploded archive directory contains the same files and directories as a jar archive. However, the files and directories reside directly in the file system instead of as a single archive file (JAR). This example uses the exploded deployment style to create the Web Application for this example.
Default WebLogic application
The default Web Application is what the server sends to browser clients who do not specify a recognized URI (or specify only “/” as the URI). Each server has only one default Web Application and for this solution to work, this application must be set as the default. If there is already a default, this servlet could be added to the existing application by using an exploded deployment of the default with modification to the web.xml to register the APEX proxy.

II)

Pre-requisites:

1)An Oracle database successfully serving APEX pages. The APEX instance may be on the same or different machine and served from either Apache or the Embedded PL/SQL gateway. In this example, APEX uses the xdb feature of Database which is included in Oracle 11g Database and database is running on the same machine as the WebLogic server and natively appears at the http://10.47.33.10:8080/apex URL.
Database side task:
Open the Apex http port from the Database side
SQL> begin
dbms_xdb.sethttpport('8080');
end;
/

PL/SQL procedure successfully completed.

SQL> select dbms_xdb.gethttpport as "HTTP-Port"
from dual;

HTTP-Port
----------
8080

2) An Oracle WebLogic Server 10.3(11g) running a Node Manger, the Administration Console and a Managed server. This example uses a domain created specifically for this exercise named APEXDemo. The WLS Administration console uses port 7001 and the SOA Managed Server uses port 8001.We are using SOA Managed Server for Deploying Apex

3) There is no other “default” WebLogic application for the Managed Server.

III)Experiment:

Create the APEX Proxy Servlet
1)Create the following directory structure somewhere on disk. This experment assumes the / root disk is used. Note: the apexproxy.war directory name mimics the normal J2EE naming convention for Web Application archive (WAR).

Create the directory structure as given below in linux OS:

#mkdir APEX_PROXY
#cd /APEX_PROXY
#mkdir apexproxy.war
#cd /APEX_PROXY/apexproxy.war

Rest of the steps with screen shots very well documented in the below link:

http://christopherbeck.wordpress.com/2008/09/15/weblogic-server-and-apex/

Thanks Christopherbeck.

Enjoy Oracle Fusion Middleware learning

Step by step Oracle SOA suite 11g installation on RHEL 5.3

Data Mining in Oracle 11g

Manually Installing Oracle Data Mining in Oracle 11g

If you compare Oracle 11g and previous versions there are lot of changes in terms of installing Data Mining component,
For installing check the MY ORACLE SUPPORT note :
How To Manually Install Data Mining In Oracle 11g? [ID 818314.1].

Information about data mining:
The brief information about Oracle Data mining can be obtained from the below link:

http://docs.oracle.com/html/B14339_01/1intro.htm

In short,If you do the Oracle installation with Enterprise Edition installation type ,it selects Oracle Data Mining option by default.In Oracle Database 11g,the Data Mining metadata is created with 'SYS' metadata when you select the Create Database option.

For verifying the installation of Oracle Data Mining:
Check if the parameter DATA MINING is set to TRUE,if it is than Oracle Data Mining component is already installed in the Database.The V$OPTION is very useful for checking the various functionality exist in your Database or not as seen from the below results:

SQL>SET LINESIZE 1000
SQL>SELECT *FROM V$OPTION;

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
Change Data Capture TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Materialized view warehouse refresh TRUE
Database resource manager TRUE
Spatial TRUE
Automatic Storage Management FALSE
Export transportable tablespaces TRUE
Transparent Application Failover TRUE
Fast-Start Fault Recovery TRUE
Sample Scan TRUE
Duplexed backups TRUE
Java TRUE
OLAP Window Functions TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Block Media Recovery TRUE
Fine-grained Auditing TRUE
Application Role TRUE
Enterprise User Security TRUE
Oracle Data Guard TRUE
Oracle Label Security FALSE
OLAP TRUE
Basic Compression TRUE
Join index TRUE
Trial Recovery TRUE
Data Mining TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Online Redefinition TRUE
Streams Capture TRUE
File Mapping TRUE
Block Change Tracking TRUE
Flashback Table TRUE
Flashback Database TRUE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE
Oracle Database Vault FALSE
Result Cache TRUE

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
SQL Plan Management TRUE
SecureFiles Encryption TRUE
Real Application Testing TRUE
Flashback Data Archive TRUE
DICOM TRUE
Active Data Guard TRUE
Server Flash Cache TRUE
Advanced Compression TRUE
XStream TRUE
Deferred Segment Creation TRUE

PRIVILEGE REQUIRED FOR DATA MINING SCHEMA:

Let us say,If you want to use Data Mining feature to a specific user,than we have to grantCREATE MINING MODEL privilege to that user.

SQL> conn dmuser
Enter password:
Connected.
SQL> select *from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
CREATE MINING MODEL

11 rows selected.

Happy Oracle DBA tasks...

Thursday, July 28, 2011

RMAN backup types and Usage

Hi,
Here I'm descrbing RMAN backups Types,Commands and Usage in Brief in Oracle 11g Database.Hope it will help one and all.

RMAN BACKUPS:
----------------

1)RMAN HOT BACKUP =>database up & running
2)RMAN COLD BACKUP =>database is not up(shutdown =>mount(now take backup).

1)RMAN HOT BACKUP: OPEN STATE (ARCHIVE_LOG MODE)
2)RMAN COLD BACKUP:MOUNT STATE (NO ARCHIVE LOG MODE)

INCREMENTAL BACKUP:
1)DIFFERENTIAL BACKUP =>BY DEFAULT INCREMENTAL BACKUP IS DIFFERENTIAL.
=>BACKUP FROM SAME OR LOWER LEVEL.
=>LEVEL 0,LEVEL 1,LEVEL 2.....
2)CUMMULATIVE BACKUP =>BACKUP FROM LEVEL 0(LOWEST LEVEL)
=>LEVEL 0,LEVEL 1,LEVEL 2.....

WHERE:
LEVEL 0=FULL BACKUP
LEVEL 1=CHANGE DATA FROM LEVEL 0
LEVEL 2=CHANGE DATA FROM LEVEL 1...

BACKUP STRATERGY:
--------------------

SUNDAY =LEVEL 0 =>INCREMENTAL (DIFFERENTIAL)=>BACKUP LEVEL 0(LOWEST LEVEL)
MONDAY =LEVEL 1 =>INCREMENTAL
TUESDAY=LEVEL 2 =>INCREMENTAL
WED =LEVEL 0 =>INCREMENTAL (CUMMULATIVE) =>BACKUP LEVEL 0(LOWEST LEVEL)
THUR =LEVEL 1 =>INCREMENTAL
FRI =LEVEL 2 =>INCREMENTAL
SAT= =LEVEL 1 =>CUMMULATIVE =>BACKUP LEVEL 1(CHANGE DATA FROM LEVEL 0)

TIMESTAMP SYMBOLS:
-------------------

%U=UNIQUE BACKUPSET NAME
%T= TIMESTAMP OF TIME OF BACKUPSET

RMAN COMMANDS:
----------------

INCREMENTAL BACKUP:
---------------------

1)DIFFERENTIAL BACKUP
2)CUMULATIVE BACKUP

1)DIFFERENTIAL BACKUP:
-- INCREMENTAL LEVEL 0
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=0 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

-- INCREMENTAL LEVEL 1
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=1 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

-- INCREMENTAL LEVEL 2
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=2 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

2)CUMULATIVE BACKUP:
----------------------

-- CUMMULATIVE BACKUP
RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> Backup incremental level=0 CUMULATIVE database tag='complete_backup';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }

released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK

Starting backup at 24-JUL-11
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1HMI7EPO_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1IMI7EPP_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:15
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1JMI7ES5_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11

released channel: ch1

-- CUMULATIVE BACKUP LEVEL LOWEST(0)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=0 CUMULATIVE DATABASE FILESPERSET 4 tag='cumulative';
tag='complete_cummulative_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

-- CUMULATIVE BACKUP LEVEL LOWEST(1)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}

RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }

released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK

Starting backup at 24-JUL-11
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1NMI7F9M_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:25
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1OMI7FCC_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:35
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1PMI7FDF_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11

released channel: ch1

allocated channel: c1
channel c1: SID=73 device type=DISK

Starting backup at 24-JUL-11
channel c1: starting datafile copy
copying current control file
output file name=D:\RMAN_BACKUP\ORCL_CTRL_BACKUP2_CF_D-ORCL_ID-1280115002_1QMI7FDK_20110724 tag=TAG20110724T055044 R
D=31 STAMP=757317045
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11

released channel: c1

ARCHIVE LOG DELETION POLICY FOR A DATABASE:
----------------------------------------------

run {
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -5';
}

I do backups from the primary database to a local drive and have been puzzled
how to delete the standby archive logs after they ship.
The ‘obvious’ solution is the documented feature in RMAN:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

COMPRESSION LEVEL ORACLE 11g:
----------------------------------

basic =DEFAULT
none =Not recomended
medium = license
high =license

Drawbacks:
------------

medium =>consume hight system resource
high =>consume high system resource

II)RMAN COLD BACKUP:
----------------------

=>NOARCHIVE LOG MODE
=>MOUNT STATE
=> DEVELOPMENT => MUCH DISK SPACE IS NOT THERE
=>DOWNTIME TOLERABLE..

run_orcl.txt:
-----------------------------------------------------

*SNAPSHOT CONTROLFILE:RMAN USES FOR BACKUP OF CONTROL FILE.

Configure setting in RMAN :
----------------------------
We can use configure command to change any setting in RMAN.I would definetly like to outside my script like below.

$rman target / catalog rman/rman@catdb
RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';

rman_cold.txt:
--------------

run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
backup database TAG='ORCL_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\TESTDB_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}

rman_orcl.bat:
---------------

-- orcl DB RMAN COLD BACKUP
set ORACLE_SID=orcl
set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\app\RafiAlvi\
rman target sys/orcldba @D:\RMAN_BACKUP_SCRIPTS\run_ORCL.txt log=D:\RMAN_BACKUP_SCRIPTS\LOGS\rman_cold_orcl_24july_cum.log

Note: In Unix environment use export for setting the enviromnmet and Write shell script 'rman_orcl.sh',rest all script is almost same and can fit as per our path in Unix environment.

RMAN BACKUP DETAILS Views:
---------------------------------

We can use Dictionary Views for checking the status of rman backups.

1)USE AFTER SCHEDULING RMAN BACKUP:
SELECT START_TIME||' '||END_TIME||' '||STATUS FROM V$RMAN_BACKUP_JOB_DETAILS;

2)USE TO CHECK BYYES PROCESSED IN RMAN BACKUP:
SELECT SID||' '||STATUS||' '||MBYTES_PROCESSED||' '||START_TIME||' '||END_TIME
FROM V$RMAN_STATUS;

3)USE TO CHECK SESSION AND RMAN BACKUP OUTPUT:
SELECT SID||' '||RECID||' '||OUTPUT||' '||SESSION_STAMP
FROM V$RMAN_OUTPUT;

Note:On Unix terminal,It will be very much useful to check the process in order to check the status by using 'ps' command.
$ps -eaf|grep rman

Hope it helps...

Monday, June 6, 2011

ORA-00845: MEMORY_TARGET not supported on this system

Hi,
As we know MEMORY_TARGET is a new dynamic parameter added in Oracle 11g for automatic SGA and PGA management.Working with LINUX OS you might face error
ORA-00845.

Error:
ORA-00845: MEMORY_TARGET not supported on this system

The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

For me the first case had happen i.e MEMORY_TARGET=1G,So I decided to increase to 2G,This is because of the below factor:
•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process so we need extra memory or size of this parameter must be high,If you have 2 Databases running in your machine you can keep MEMORY_TARGET=2G.For 3 or 4 Database MEMORY_TARGET=2.5g should
be fine.

Resolving ORA-00845:
As a root user
mount -t tmpfs shmfs -o size=2g /dev/shm

In order to make the settings persistence so that it will affect after
restarting machine add an entry in /etc/fstab similar to the following:
vi /etc/fstab

shmfs /dev/shm tmpfs size=12g 0

[root@node1 /]# mount -t tmpfs shmfs -o size=2g /dev/shm

:wq
Save and quit

Note 1:In the above example I've created the shmfs(shared memory file system) with a size of 2G as that is the size of the buffer cache I am planning to use.
The other elements of the SGA are placed in regular memory,not this shared memory file system, so they should not be included when deciding on the size of the shmfs.
It is recommended to size this slightly bigger than the actual size needed,
Here I've used a 2G shmfs for a 2G buffer cache.

Note 2: The size of memory_target parameter we can grow upto memory_max_target and not beyond memory_max_target.

SQL> show parameter %MEMORY%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1G
memory_target big integer 1G

[root@node1 /]# reboot

Broadcast message from root (pts/3) (Thu May 19 19:28:02 2011):

The system is going down for reboot NOW

Check after reboot:
-------------------

[root@node1 ~]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda6 5952252 4117872 1527140 73% /
/dev/sda7 5080796 4292008 526532 90% /u01
/dev/sda3 6605824 1807356 4457492 29% /u03
/dev/sda2 35550448 26956160 6759264 80% /u02
/dev/sda1 497829 16695 455432 4% /boot
tmpfs 2097152 0 2097152 0% /dev/shm
/dev/sdb1 39381744 35555696 1825556 96% /u04
/dev/sdc1 51605436 1233976 47750056 3% /u05
shmfs 2097152 0 2097152 0% /dev/shm

Check after starting database:
-------------------------------
[oracle@node1 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda6 5.7G 4.0G 1.5G 73% /
/dev/sda7 4.9G 4.1G 515M 90% /u01
/dev/sda3 6.3G 1.8G 4.3G 29% /u03
/dev/sda2 34G 26G 6.5G 80% /u02
/dev/sda1 487M 17M 445M 4% /boot
tmpfs 2.0G 1.4G 714M 66% /dev/shm
/dev/sdb1 38G 34G 1.8G 96% /u04
/dev/sdc1 50G 1.2G 46G 3% /u05
shmfs 2.0G 1.4G 714M 66% /dev/shm

Hope it helps.

Monday, May 30, 2011

Resolving ORA-24247 and ORA-06512

Resolving ORA-24247 and ORA-06512:
-----------------------------------
When you start working with Oracle 11g rel2 environment.You might face some issues related to ACL
access control list(extra security layer).One of my developer complained he is not able to send mails from stored procedure
and getting the error as given below:

Connecting to the database TEST_USER_DEV 11g.
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "TEST_USER.SP_GM_INIT_MAIL", line 43
ORA-06512: at line 15

I resolve this error as follows:

Step 1: connect to the Database and create a procedure as given below:
--------

SQL> select name from v$database;

NAME
---------
TEST_DB

SQL> set serveroutput on
SQL> create or replace procedure mailserver_acl(
aacl varchar2,
acomment varchar2,
aprincipal varchar2,
aisgrant boolean,
aprivilege varchar2,
aserver varchar2,
aport number)
is
begin
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
dbms_output.put_line('ACL dropped.....');
exception
when others then
dbms_output.put_line('Error dropping ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
dbms_output.put_line('ACL created.....');
exception
when others then
dbms_output.put_line('Error creating ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
dbms_output.put_line('ACL assigned.....');
exception
when others then
dbms_output.put_line('Error assigning ACL: '||aacl);
dbms_output.put_line(sqlerrm);
end;
commit;
dbms_output.put_line('ACL commited.....');
end;
/

Procedure created.

SQL> show errors
No errors.

Step 2: create an ACL as given below:
--------

SQL> begin
mailserver_acl(
'mailserver_acl.xml',
'ACL for used Email Server to connect',
'TEST_USER',
TRUE,
'connect',
'rb-smtp-int.MAIL_SERVER.com',
25);
end;
/
Error dropping ACL: mailserver_acl.xml
ORA-31001: Invalid resource handle or path name "/sys/acls/mailserver_acl.xml"
ACL created.....
ACL assigned.....
ACL commited.....

PL/SQL procedure successfully completed.

Now tell the developer to try and send mail it should work.It is just a short and sweet process
to solve ORA-24247 along with ORA-06512.

Best regards,

Rafi.

Tuesday, March 22, 2011

Installation of Oracle 11g release2 software,Database creation on Windows 32-bit and Useful new features

Hi,
This post might be simpler but I will try to put some useful details which can help a beginner's or DBA's to understand oracle11g release2 installation,Database creation and useful feature of this excellent Database.

Step1:Download the Oracle11g release2 software & Unzip it.
We can download the Oracle11g release2 software from OTN(If you are not having OTN(Oracle technology network) account please create one).Download the software from the below link:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Note:You need around 4.5 Gb of space to download these software in your machine,so please make sure you have enough space.The software will be in zip format.Unzip in your machine where around 4.5 GB of space is available.

Step2:Create Directories for your Installation with useful naming conventions
We can create two directories atleast 1)Oracle11g release2 Home:Oracle11g_home=>Where all the binaries,admin files are present and 2)Oracle11g release2 Database files directory:Oracle11g_DB=>Here you can put your Database files and Tablespaces.In this article:

D:\Oracle11g_home=>Oracle11g release2 Home.
D:\Oracle11gDB=>Oracle11g Database files.

Step3: Start your Oracle11g release2 installation
Click the 11gSetup and start your installation.The installation will proceed as mentioned in the diagrams.
Specify the Oracle11g release2 home path,Make sure you have around 4 GB of free space in the Drive where you are keeping the home
Specify the Oracle11g releases2 Database files here all your Datafiles,logfile,parameter files and Tablespace will be present.

Step4::Choose password management page to unlock some users like scott,hr and specify the suitable password

Step5:Verify your installation as follows.

SQL> select name from v$database;

NAME
---------
ORACLE11

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select *from v$version;

BANNER
--------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.
2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction

NLSRTL Version 11.2.0.1.0 - Production

Component installed:
---------------------
SQL> select comp_id||' '||comp_name||' '||version from dba_registry;

COMP_ID||''||COMP_NAME||''||VERSION
--------------------------------------------------
OWB OWB 11.2.0.1.0
APEX Oracle Application Express 3.2.1.00.10
EM Oracle Enterprise Manager 11.2.0.1.0
AMD OLAP Catalog 11.2.0.1.0
SDO Spatial 11.2.0.1.0
ORDIM Oracle Multimedia 11.2.0.1.0
XDB Oracle XML Database 11.2.0.1.0
CONTEXT Oracle Text 11.2.0.1.0
EXF Oracle Expression Filter 11.2.0.1.0
RUL Oracle Rules Manager 11.2.0.1.0
OWM Oracle Workspace Manager 11.2.0.1.0

COMP_ID||''||COMP_NAME||''||VERSION
--------------------------------------------------
CATALOG Oracle Database Catalog Views 11.2.0.1.0
CATPROC Oracle Database Packages and Types 11.2.0.
1.0

JAVAVM JServer JAVA Virtual Machine 11.2.0.1.0
XML Oracle XDK 11.2.0.1.0
CATJAVA Oracle Database Java Packages 11.2.0.1.0
APS OLAP Analytic Workspace 11.2.0.1.0
XOQ Oracle OLAP API 11.2.0.1.0

18 rows selected.

Datafiles:
------------
SQL> select name from v$datafile;

NAME
--------------------------------------------------
D:\ORACLE11GDB\ORACLE11GDB\SYSTEM01.DBF
D:\ORACLE11GDB\ORACLE11GDB\SYSAUX01.DBF
D:\ORACLE11GDB\ORACLE11GDB\UNDOTBS01.DBF
D:\ORACLE11GDB\ORACLE11GDB\USERS01.DBF
D:\ORACLE11GDB\ORACLE11GDB\EXAMPLE01.DBF

Multiplexed controlfiles:
------------------------
SQL> select name from v$controlfile;

NAME
--------------------------------------------------
D:\ORACLE11GDB\ORACLE11GDB\CONTROL01.CTL
C:\APP\ARL1KOR\FLASH_RECOVERY_AREA\ORACLE11GDB\CON
TROL02.CTL

Redolog files:
--------------
SQL> select group#,member from v$logfile;

GROUP#
----------
MEMBER
--------------------------------------------------
3
D:\ORACLE11GDB\ORACLE11GDB\REDO03.LOG

2
D:\ORACLE11GDB\ORACLE11GDB\REDO02.LOG

1
D:\ORACLE11GDB\ORACLE11GDB\REDO01.LOG

Diagnostic dump dest:
---------------------

SQL> sho parameter %DIAG%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string C:\APP\ARL1KOR
SQL> sho parameter %USER%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_users integer 0
parallel_adaptive_multi_user boolean TRUE
redo_transport_user string
user_dump_dest string c:\app\arl1kor\diag\rdbms\orac
le11gdb\oracle11gdb\trace
SQL> sho parameter %AUDIT%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string C:\APP\ARL1KOR\ADMIN\ORACLE11G
DB\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
SQL> sho parameter %CORE%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
core_dump_dest string c:\app\arl1kor\diag\rdbms\orac
le11gdb\oracle11gdb\cdump
shadow_core_dump string none
SQL> sho parameter %BACK%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string c:\app\arl1kor\diag\rdbms\orac
le11gdb\oracle11gdb\trace
backup_tape_io_slaves boolean FALSE
db_flashback_retention_target integer 1440
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5

In the upcoming posts,I will posts the issues that can occur while using this releases or when you migrate from Oracle9i,10g to Oracle11g release2.Hope it helps.

Oracle 11g crucial new features for DBA

Hi ,
Below are some of the crucial new features that should be known by DBA working on oracle 11g Databases:

Oracle Database 11.1 Top New Feature for DBAs : SUMMARY
=====================================

1) Automatic Diagnostic Repository [ADR]

2) Database Replay

3) Automatic Memory Tuning

4) Case sensitive password

5) Virtual columns and indexes

6) Interval Partition and System Partition

7) The Result Cache

8) ADDM RAC Enhancements

9) SQL Plan Management and SQL Plan Baselines

10) SQL Access Advisor & Partition Advisor

11) SQL Query Repair Advisor

12) SQL Performance Analyzer (SPA) New

13) DBMS_STATS Enhancements

14) The Result Cache

15) Total Recall (Flashback Data Archive)

Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently

Oracle 11.1 Database DBA New Features with brief explanation
==========================================

# Database Capture/replay database workloads :
This allows the total database workload to be captured, transferred to a test database create from a backup or standby database, then replayed to test the affects of an upgrade or system change. Currently, these are working to a capture performance overhead of 5%, so this will capture real production workloads

# Automatic Memory Tuning:
Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was already
introduced in Oracle 10g. But In 11g, all memory can be tuned automatically by setting one parameter. We can literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set.

# Interval partitioning for tables :

Interval partitions are extensions to range partitioning. These provide automation for equi-sized range partitions. Partitions are created as metadata and only the start partition is made persistent. The additional segments are allocated as the data arrives. The additional partitions and local indexes are automatically created.

# Feature Based Patching:
All one-off patches will be classified as to which feature they affect. This allows you to easily identify which patches are necessary for the features you are using. EM will allow you to subscribe to a feature based patching service, so EM automatically scans for available patches for the features you are using

# RMAN UNDO bypass :

Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now rman can bypass those types of tablespace. Great for exporting a tablespace from backup.

# Virtual columns/indexes :

User can create Virtual index on table. This Virtual index is not visible to optimizer, so it will
not affect performance, Developer can user HINT and see is Index is useful or not.Invisible
Indexesprevent premature use of newly created indexes

# New default audit settings :
Oracle database where general database auditing was "off" by default, logging is intended to be enabled by default with the Oracle Database 11g beta secure configuration. Notable performance improvements are planned to be introduced to reduce the performance degradation typically associated with auditing.

# Case sensitive password :

Passwords are expected to also become case sensitive This and other changes should result in better protection against password guessing scenarios. For
example, in addition to limiting the number of failed login attempts to 10 (default configuration in 10gR2), Oracle 11g beta’s planned default settings should expire passwords every 180 days, and limit to seven the number of times a user can login with an expired password before disabling access.

# Faster DML triggers : Create a disabled trigger; specify trigger firing order

# Fine grained access control for Utl_TCP:

in 10g all port are available, now it is controlled.

# Data Guard supports "Flashback Standby"

# New Trigger features

# Partitioning by logical object and automated partition creation.

# LOB's - New high-performance LOB features.

# New Oracle11g Advisors

# Enhanced Read only tables

# Table trigger firing order

# Enhanced Index rebuild online : - Online index build with NO pause to DML.

# No recompilation of dependent objects:- When

A) Columns added to tables
B) Procedures added to packages

# Improved optimizer statistics collection speed

# Online index build with NO pause to DML

# Read only table :-

alter table t read only

alter table t read write

Oracle 11g Database SQL/PL-SQL New Features
----------------------------------------------

> Fine Grained Dependency Tracking:

In 11g we track dependencies at the level of element within unit. so that these changes have no consequence

• Transparent performance improvement
•Unnecessary recompilation certainly consumes CPU

create table t(a number)

create view v as select a from t

alter table t add(Unheard_Of number)

select status from User_Objects where Object_Name = 'V'
- -----
VALID

No recompilation of dependent objects when Columns added to tables OR Procedures
added to packages

> Named and Mixed Notation from SQL:

select fun(P4=>10) from DUAL

In 10g not possible to call function in select statment by passing 4th parameter,
but in 11g it is possible

> PL/SQL "continue" keyword - It is same as we read in c/c++ loop

> Support for “super”: It is same "super" in Java.

> Powerfull Regular Expression:

Now we can access data between TAGS like data between tags .........

The new built-in REGEXP_COUNT returns the number of times the pattern is matched in the
input string.

> New table Data Type "simple_integer"

> SQL Performance Analyzer(SPA) :

It is same as Database replay except it not capture all transaction.The SQL Performance Analyzer (SPA) leverages existing Oracle Database 10g SQL tuning components. The SPA provides the ability to capture a specific SQL workload in a SQL Tuning Set, take a performance baseline before a major database or system change, make the desired change to the system, and then replay the SQL workload against the modified database or configuration. The before and after performance of the SQL workload can then be compared with just a few clicks of the mouse. The DBA only needs to isolate any SQL statements that are now performing poorly and tune them via the SQL Tuning Advisor

> Caching The Results with /*+ result_cache */ :

select /*+ result_cache */ * from my_table, New for Oracle 11g, the result_cache hint caches the result set of a select statement. This is similar to alter table table_name cache,but as you can adding predicates makes /*+ result_cache */ considerably more powerful by caching a subset of larger tables and common queries.

select /*+ result_cache */ col1, col2, col3 from my_table where colA = :B1

> The compound trigger :

A compound trigger lets you implement actions for each of the table DML timing points in a single trigger

> PL/SQL unit source can exceeds 32k characters

> Easier to execute table DDL operations online:

Option to wait for active DML operations instead of aborting

> Fast add column with default value:

Does not need to update all rows to default value.

Oracle 11g Database Backup & Recovery New Features
------------------------------------------------

* Enhanced configuration of archive deletion policies Archive can be deleted , if it is not need DG , Streams Flashback etc When you CONFIGURE an archived log deletion policy applies to all archiving destinations, including the flash recovery area. BACKUP ... DELETE
INPUT and DELETE... ARCHIVELOG use this configuration, as does the flash recovery area.
When we back up the recovery area, RMAN can fail over to other archived redo log
destinations if the flash recovery area is inaccessible.

* Configuring backup compression:

In 11g can use CONFIGURE command to choose between the BZIP2 and ZLIB compression
algorithms for RMAN backups.

* Active Database Duplication:

Now DUPLICATE command is network aware i.e.we can create a duplicate or standby
database over the network without taking backup or using old backup.

* Parallel backup and restore for very large files:

RMAN Backups of large data files now use multiple parallel server processes to efficiently
distribute theworkload for each file. This features improves the performance of backups.

* Improved block media recovery performance:

RECOVER command can recover individual data blocks.
RMAN take older, uncorrupted blocks from flashback and the RMAN can use these blocks,
thereby speeding up block media recovery.

* Fast incremental backups on physical standby database:

11g has included new feature of enable block change tracking on a physical standby
database (ALTER DATABASE ENABLE/DISABLE BLOCK CHANGE TRACKING SQL statement).
This new 11g feature enables faster incremental backups on a physical standby database
than in previous releases.because RMAN identifywe the changed blocks sincethe last
incremental backup.

11g ASM New Features
-----------------------

The new features in Automatic Storage Management (ASM) extend the storage
management automation, improve scalability, and further simplify management for
Oracle Database files.

■ ASM Fast Mirror Resync

A new SQL statement, ALTER DISKGROUP ... DISK ONLINE, can be executed
after a failed disk has been repaired. The command first brings the disk online for writes so that no new writes are missed. Subsequently, it initiates a copy of all extents marked as stale on a disk from their redundant copies.
This feature significantly reduces the time it takes to repair a failed diskgroup,potentially from hours to minutes. The repair time is proportional to the number of extents that have been written to or modified since the failure.

■ ASM Manageability Enhancements

The new storage administration features for ASM manageability include the following:

■ New attributes for disk group compatibility
To enable some of the new ASM features, you can use two new disk group
compatibility attributes, compatible.rdbms and compatible.asm. These
attributes specify the minimum software version that is required to use disk
groups for the database and for ASM, respectively. This feature enables
heterogeneous environments with disk groups from both Oracle Database 10g and
Oracle Database 11g. By default, both attributes are set to 10.1. You must advance
these attributes to take advantage of the new features.

■ New ASM command-line utility (ASMCMD) commands and options

ASMCMD allows ASM disk identification, disk bad block repair, and backup and
restore operations in your ASM environment for faster recovery.

■ ASM fast rebalance

Rebalance operations that occur while a disk group is in RESTRICTED mode
eliminate the lock and unlock extent map messaging between ASM instances in
Oracle RAC environments, thus improving overall rebalance throughput.
This collection of ASM management features simplifies and automates storage
management for Oracle databases.

■ ASM Preferred Mirror Read

When ASM failure groups are defined, ASM can now read from the extent that is
closest to it, rather than always reading the primary copy. A new initialization parameter, ASM_PREFERRED_READ_FAILURE_GROUPS, lets the ASM administrator specify a list of failure group names that contain the preferred read disks for each node in a cluster.

In an extended cluster configuration, reading from a local copy provides a great
performance advantage. Every node can read from its local diskgroup (failure group),
resulting in higher efficiency and performance and reduced network traffic.

■ ASM Rolling Upgrade

Rolling upgrade is the ability of clustered software to function when one or more of the nodes in the cluster are at different software versions. The various versions of the software can still communicate with each other and provide a single system image.
The rolling upgrade capability will be available when upgrading from Oracle
Database 11g Release 1 (11.1).

This feature allows independent nodes of an ASM cluster to be migrated or patched without affecting the availability of the database. Rolling upgrade provides higher uptime and graceful migration to new releases.

■ ASM Scalability and Performance Enhancements

This feature increases the maximum data file size that Oracle can support to 128 TB.
ASM supports file sizes greater than 128 TB in any redundancy mode. This provides
near unlimited capacity for future growth. The ASM file size limits are:

■ External redundancy - 140 PB
■ Normal redundancy - 42 PB
■ High redundancy - 15 PB

Customers can also increase the allocation unit size for a disk group in powers of 2 up to 64 MB.

■ Convert Single-Instance ASM to Clustered ASM

This feature provides support within Enterprise Manager to convert a non-clustered
ASM database to a clustered ASM database by implicitly configuring ASM on all
nodes. It also extends the single-instance to Oracle RAC conversion utility to support standby databases.

Simplifying the conversion makes it easier for customers to migrate their databases and achieve the benefits of scalability and high availability provided by Oracle RAC.

■ New SYSASM Privilege for ASM Administration

This feature introduces the new SYSASM privilege to allow for separation of database management and storage management responsibilities.
The SYSASM privilege allows an administrator to manage the disk groups that can be shared by multiple databases. The SYSASM privilege provides a clear separation of
duties from the SYSDBA privilege.

Friday, March 19, 2010

Alert Log Scraping with Oracle's ADRCI Utility

Alert Log Scraping with Oracle's ADRCI Utility
By James Koopmann

Oracle’s new ADR with command interface shows promise for database administrators who like to script their own solution for quickly scraping the alert log and automatically looking for errors.

Oracle’s alert log is a chronological log of important messages and errors that are generated using an Oracle database. Often times it is the first place DBAs look to see if there are errors being generated or checking to see if the Oracle database is healthy, or at least not spitting out errors and complaining about something. If you have yet to pry open the hood of Oracle and have never seen an alert log, below is a snippet of some of the errors (ORA-) and messages that Oracle keeps in the alert log. Notice that I’ve included two different snippets. One is what most DBAs are familiar with, a pure text file, and one that is new to many, an XML alert log. Both are generated but the prior is, as suggested by Oracle, not being maintained and not receiving the same messaging as the XML alert log.

From the text alert log:

Wed Dec 09 14:55:16 2009
Errors in file /opt/app/oracle/diag/rdbms/db11/db11/trace/db11_ora_14798.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oradata/db11/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
From the XML alert log:

client_id='' type='UNKNOWN' level='16'
module='sqlplus@ludwig (TNS V1-V3)' pid='14798'>
Errors in file /opt/app/oracle/diag/rdbms/db11/db11/trace/db11_ora_14798.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/oradata/db11/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

Clearly, they are very similar except for the XML tags. These XML tags cause some interesting problems for those of us that like to scrape the alert log with system utilities. It isn’t as easy as pointing the scripts at a new file out on disk. Thankfully Oracle has provided a utility (ADRCI) that can be used to extract diagnostic information generated from an Oracle database, including the alert log.

When used interactively by issuing the “adrci” command from a system prompt, which is often the way it is used, a user can easily extract alert log detail with very simple commands such as the following:

SHOW ALERT –TAIL; To display the last 10 lines of the alert log.
SHOW ALERT -TAIL 50; To display the last 50 lines of the alert log.
SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'"; search for alert lines that have ORA- in them.
However, like many other DBA tasks we clearly hate logging into a database server and issuing commands. What is more expected is to have some set of commands that can be executed through a schedule to check and alert us, such as by email. Additionally if we schedule something to check on a regular interval, say 10 minutes, it becomes a better and more reliable monitoring methodology then having a DBA waste time logging into 10s or 100s of database servers every 10 minutes. Therefore, and to not belabor the point, here are the scripts that I’ve started to develop. I hope that you can use them:

This solution makes use of a very simple directory structure under the oracle user’s home directory. Here are the directories/files used:

/home/oracle/alert
This is where the main script and supporting files exist

/home/oracle/alert/CHKALERT
This file contains ORA- errors, or any other errors we are interested in checking for in the alert log

/home/oracle/alert/CHKALERT.sh
This is the script that will need to be executed

/home/oracle/alert/CHKALERT.curr
File that contains information on the last time the alert log was scanned; containing information about the errors found and put into the CHKALERT.yyyy-mm-dd files

/home/oracle/alert/lst
The directory where output is generated

/home/oracle/alert/lst/CHKALERT.adrci
Is dynamically generated during runtime and will be used as a script to the ADRCI utility

/home/oracle/alert/lst/CHKALERT.lst
Is dynamically generated during runtime and will be used as output to a SQL*Plus call to get some variables to be used when calling ADRCI

/home/oracle/alert/lst/CHKALERT.sql
Is the SQL*Plus SQL used to generate the CHKALERT.lst output

/home/oracle/alert/lst/CHKALERT.tmp
Contains header output from the alert log to get a timezone

/home/oracle/alert/lst/CHKALERT.yyyy-mm-dd
will be files generated for each day that contains ORA- errors for that day

Below is the script itself. To use, just execute by typing in at the command prompt:

[oracle@ludwig alert]$ ./CHKALERT.shThe file you will be interested in looking at, if you’ve setup the /home/oracle/alert/CHKALERT file properly to look for specific ORA errors, will be the /home/oracle/alert/lst/CHKALERT.log file. This file contains each of the ORA errors found for the last run and looks like the following:

CHKALERT.log
::::::::::::::
001:2010:03:04:02:32:29:ORA-00312: online log 1 thread 1: '/oradata/db11/redo01.log'
001:2010:03:04:02:32:29:ORA-00312: online log 2 thread 1: '/oradata/db11/redo02.log'
001:2010:03:04:02:32:29:ORA-00312: online log 3 thread 1: '/oradata/db11/redo03.log'
To fully automate the process, not covered in this article, would be to interrogate this file and if it contains something, it should be sent to the appropriate DBAs to figure out what to do with the errors found. Have fun with the script. Change as you see fit and begin to take advantage of Oracle new ADRCI utility for scraping the alert logs.

#!/bin/sh
#--File: CHKALERT.sh

#--setup
PGM="CHKALERT"
ALRT=$HOME/alert
LST=${ALRT}/lst
LOG=${LST}/${PGM}.log
CURR=${ALRT}/${PGM}.curr

#--Unix environment variables
ECHO=echo; export ECHO
CAT=/bin/cat; export CAT
RM=/bin/rm; export RM
TOUCH=/bin/touch; export TOUCH
GREP=/bin/grep; export GREP
AWK=/bin/awk; export AWK
WC=/usr/bin/wc; export WC
TAIL=/usr/bin/tail; export TAIL
HEAD=/usr/bin/head; export HEAD
SUM=/usr/bin/sum; export SUM

#--Oracle environment variables
ORACLE_SID=db11; export ORACLE_SID
ORACLE_HOME=`${GREP} ${ORACLE_SID}: /etc/oratab | ${AWK} -F: '{print $2}'`; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH

#--code
start=`date "+%Y:%m:%d:%H:%M:%S"`
${RM} ${LOG}
${TOUCH} ${LOG}

#--execute SQL to get some diagnostic variables
echo "set echo off" > ${LST}/${PGM}.sql
echo "set feedback off" >> ${LST}/${PGM}.sql
echo "set heading off" >> ${LST}/${PGM}.sql
echo "set linesize 40" >> ${LST}/${PGM}.sql
echo "set pagesize 55" >> ${LST}/${PGM}.sql
echo "set verify off" >> ${LST}/${PGM}.sql
echo "set linesize 300" >> ${LST}/${PGM}.sql
echo "SELECT 'homepath:'||replace(homepath.value,adrbase.value||'/','')" >> ${LST}/${PGM}.sql
echo " FROM v\$diag_info homepath, v\$diag_info adrbase" >> ${LST}/${PGM}.sql
echo " WHERE homepath.name = 'ADR Home'" >> ${LST}/${PGM}.sql
echo " AND adrbase.name = 'ADR Base';" >> ${LST}/${PGM}.sql
echo "SELECT 'day:'||to_char(sysdate ,'yyyy-mm-dd') FROM dual;" >> ${LST}/${PGM}.sql
echo "SELECT 'nextday:'||to_char(sysdate+1,'yyyy-mm-dd') FROM dual;" >> ${LST}/${PGM}.sql
echo "SELECT 'prevday:'||to_char(sysdate-1,'yyyy-mm-dd') FROM dual;" >> ${LST}/${PGM}.sql
echo "exit" >> ${LST}/${PGM}.sql
sqlplus -s '/as sysdba' @${LST}/${PGM}.sql > ${LST}/${PGM}.lst

#-- get diag information variables just queried from the database
homepath=`${GREP} homepath ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
day=`${GREP} "^day" ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
nextday=`${GREP} nextday ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`
prevday=`${GREP} prevday ${LST}/${PGM}.lst | ${AWK} -F":" '{print $2}'`

#-- get the timezone from the alert log (safest place to get)
#-- the proper timezone is needed to properly filter the alert log for date ranges you
#-- want to look at
echo "set echo off" > ${LST}/${PGM}.adrci
echo "set termout off" >> ${LST}/${PGM}.adrci
echo "set homepath ${homepath}" >> ${LST}/${PGM}.adrci
echo "spool ${LST}/${PGM}.tmp" >> ${LST}/${PGM}.adrci
echo "show alert -tail 1" >> ${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
adrci script=${LST}/${PGM}.adrci 1>/dev/null 2>/dev/null
timezone=`${HEAD} -1 ${LST}/${PGM}.tmp | ${AWK} -F" " '{print $3}'`

#-- extract alert log errors for the current day (today) and previous day (yesterday)
#-- previous day alerts will be used if the current file has yesterday's day as last day;
#-- meaning that we have had a switch to a new day and might have errors still to
#-- process from the previous day
echo "set echo off" > ${LST}/${PGM}.adrci
echo "set termout off" >> ${LST}/${PGM}.adrci
echo "set homepath ${homepath}" >> ${LST}/${PGM}.adrci
echo "spool ${LST}/${PGM}.${day}" >> ${LST}/${PGM}.adrci
echo "show alert -P \"ORIGINATING_TIMESTAMP BETWEEN '${day} 00:00:00.000000 ${timezone}' AND
'${nextday} 00:00:00.000000 ${timezone}' AND MESSAGE_TEXT LIKE '%ORA-%'\" -term" >>
${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
echo "spool ${LST}/${PGM}.${prevday}" >> ${LST}/${PGM}.adrci
echo "show alert -P \"ORIGINATING_TIMESTAMP BETWEEN '${prevday} 00:00:00.000000 ${timezone}' AND
'${day} 00:00:00.000000 ${timezone}' AND MESSAGE_TEXT LIKE '%ORA-%'\" -term" >>
${LST}/${PGM}.adrci
echo "spool off" >> ${LST}/${PGM}.adrci
adrci script=${LST}/${PGM}.adrci 1>/dev/null 2>/dev/null

#-- get current contents of the current file
#-- default to current day if no current file
if [ -r "${CURR}" ]
then
#-- if the current exists then get the information it contains
daychecksum=`${GREP} day ${CURR} | ${AWK} -F":" '{print $2}'`
daylastline=`${GREP} day ${CURR} | ${AWK} -F":" '{print $3}'`
daylastday=`${GREP} day ${CURR} | ${AWK} -F":" '{print $4}'`
else
#-- if the current does not exist then default to today
daychecksum=0
daylastline=3
daylastday=${day}
fi

#-- set the days to search through for alerts
#-- if last day in current file was yesterday then include previous day
#-- if last day in current file is not yesterday then just scan today's alerts
if [ "${daylastday}" = "${prevday}" ]
then
alertdays="${prevday} ${day}"
else
alertdays="${day}"
fi

#-- for each of the days to scan for alerts
for theday in ${alertdays}
do
#-- check alert errors for the last day.
if [ -r "${LST}/${PGM}.${theday}" ]
then
#-- If the checksum generated is DIFFERENT we should start reporting from the top.
#--
#-- If the checksum generated is the SAME we should start reporting from end of
#-- the previously generated output.
new_daychecksum=`${HEAD} -4 ${LST}/${PGM}.${theday} | ${SUM} | ${AWK} '{print $1}'`
if [ ${new_daychecksum} -ne ${daychecksum} ]
then
daychecksum=${new_daychecksum}
daylastline=3
fi

#-- get the number of lines in the generated errors so we can report to the
#-- end of the file and we know where to start next time.
new_daylastline=`${WC} -l ${LST}/${PGM}.${theday} | ${AWK} -F" " '{print $1}'`

#-- if the number of lines in the output is 3 then there are no errors found.
if [ ${new_daylastline} -ne 3 ]
then
#-- if number of lines in extracted alerts is the same as last time then no new alerts
if [ ${new_daylastline} -ne ${daylastline} ]
then
#-- find the line to begin reporting new alerts from
fromline=`expr ${new_daylastline} - ${daylastline}`
#-- produce alert lines for alerts defined in file CHKALERT
${TAIL} -${fromline} ${LST}/${PGM}.${theday} |
while read LINE
do
for ORAS in `${CAT} ${ALRT}/CHKALERT`
do
ora=`${ECHO} ${LINE} | ${GREP} ${ORAS}`
if [ $? -eq 0 ]
then
#-- you might want to do something here
#-- that is specific to certain ORA- errors
err="001"
echo "${err}:${start}:${LINE}" >> ${LOG}
fi
done
done
fi
fi
daylastline=${new_daylastline}
#-- update the current file only if the day being processed is current day
if [ "${theday}" = "${day}" ]
then
${ECHO} "day:"${daychecksum}":"${daylastline}":"${day} > ${CURR}
fi
fi
done