SOA SUITE 12c Data Base Schema Dehydration Store States

Hi All,

As me, you might be stumbling to find out what, similar to List of all states from COMPOSITE_INSTANCE, CUBE_INSTANCE, DLV_MESSAGE  in 11g, the important tables would be for SOA Suite 12c and relevant states.

Basically this is described very well in the Oracle Support Document 2065869.1: 12c SOA composite <schema> States

For the benefit of every one, I have also copy pasted the content from the obove Oracle Note to here (copy right of the below details are with Oracle Support):

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

Details

Based on 11g:

Tables COMPOSITE_INSTANCE and MEDIATOR_INSTANCE does not apply to 12c.

Tables CUBE_INSTANCE, DLV_MESSAGE and DLV_TYPE did not have any change.

The FLOW_INSTANCE is available only in 12c.

CUBE_INSTANCE States

State	Description
0	STATE_INITIATED
1	STATE_OPEN_RUNNING
2	STATE_OPEN_SUSPENDED
3	STATE_OPEN_FAULTED
4	STATE_CLOSED_PENDING_CANCEL
5	STATE_CLOSED_COMPLETED
6	STATE_CLOSED_FAULTED
7	STATE_CLOSED_CANCELLED
8	STATE_CLOSED_ABORTED
9	STATE_CLOSED_STALE
10	STATE_CLOSED_ROLLED_BACK

DLV_MESSAGE States

State	Description
0	STATE_UNRESOLVED
1	STATE_RESOLVED
2	STATE_HANDLED
3	STATE_CANCELLED
4	STATE_MAX_RECOVERED

DLV_TYPE States

State	Description
1	Invoke Message
2	DLV Message

MEDIATOR States

Mediator state is now derived from SCA_ASYNC_CPNT_STATE table. The state column can take one of the following values:

State	Description
1	RUNNING
2	COMPLETED
3	FAULTED
4	ABORTED
5	STALE

FLOW_INSTANCE States

State	Description
-1	NOT_SET
1	SUSPENDED
2	TERMINATED
3	STALE

  

The Flow State is derived from the Flow Instance table using the following columns:

• Active_Component_Instances
• Unhandled_Faults
• Recoverable_Faults
• Admin_State

Completed Flow  
0 Active_Component_Instances

Completed Faulted Flow
0 Active_Component_Instances
1 or more Unhandled_Faults

Recoverable Flows
1 or more Active_Component_Instances
1 or more Recoverable_Faults

Completed Successfully
0 Active_Component_Instances
0 Unhandled_Faults
0 Recoverable_Faults

  

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

Further to the above Info, I also found a very useful blog that provides more knowledge / usage to the above table. 

Oracle BPEL 12c Data model changes and FAQ

Hope this was helpful to you.

 

SetDb.env - Oracle Database Key environment Settings.

 You need the following environment setting set in your linux environment so that you can start working with the Oracle DB.

ORACLE_SID=<NAME of your DB>

export ORACLE_SID

ORACLE_HOME=<complete path of your oracle home>

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

you can either have them typed every time you log in to the linux environment or create them as an  environment file which you can execute every time you log in to your Linux Environment.

Steps as follows:

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

1. vi <Filename>.env or <Sid_ServerName>.env

2. copy paste the below:

ORACLE_SID=<NAME of your DB>

export ORACLE_SID

ORACLE_HOME=<complete path of your oracle home>

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

3. Replace the parameters <Values> with your Database values.

4. Save the file.

5. Make sure that you have provided execute permissions for the file.

6. Now execute the file (Source the file) as shown below:

.<Space>./<Filename>.env.

Now all your environment variables are set.

If you would like to verify the environment Variables try the following:

$ echo $ORACLE_HOME or $ORACL_SID

You should see the values as set earlier.

Note: When the above environment variables are not set most of the Database utilities will fail to work.

For example when you run sqlplus will return an error such as:

message file spl<lang>.msb not found

SP2-0750 need to set ORACLE_HOME to your ORACLE software directory 

Reference Source: sqlplus not initializing in linux (Oracle Forum)

How to Make Oracle Database Start and Shutdown Automatically - Linux

The automatic startup and shutdown of the Oracle database can be achieved with the files dbstart and dbshut both provided by Oracle.

These files rely on the existance of the file /etc/oratab to work (although by altering the dbshut and dbstart files this can be moved).


The file oratab is used by ORACLE utilities (DbStart and DbShut).  It is created by root.sh and updated by the Database Configuration Assistant when creating a database.

The format of the /etc/oratab file is as follows: 

---

SID:ORACLE_HOME:AUTO

---

An example:

---

orcl:/home/oracle/7.3.3.0.0:Y  -- 'Y' -- To Start Automatically
leaveup:/home/oracle/7.3.2.1.0:N -- 'N' -- Not to start Automatically.

This is not it, there is more to it. The above will work only when the DbStart and DbShut scripts are run during the startup of the linux system. Please read on:

init.d & rc.d

To start and stop the database when the machine comes up and goes down by modifying the startup routines for the Linux machine. This is quite easy, although I should point out here that this may change depending on which flavour of Linux (slackware, debian, redhat, etc). I will show examples which work for Redhat Linux 5.0. To modify these for your own flavour of Linux, please see your Linux documentation sets. (Although it should hold true for any Sys V type UNIX).
Firstly, we need to create the script which will run dbshut and dbstart in the /etc/rc.d/init.d directory.

Create the following file as /etc/rc.d/init.d/OracleDB:

---

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_HOME=<Oracle Home>
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
   'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
        su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
        su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
        rm -f /var/lock/subsys/dbora
        ;;
esac

---

After checking the correctness of the above script, This script needs to be linked to the Linux runlevel directories.

The following commands will ensure that the databases will come up in runlevels 3,4 and 5:

---

$ ln -s /etc/rc.d/init.d/OracleDB /etc/rc.d/rc3.d/S99OracleDB

$ ln -s /etc/rc.d/init.d/OracleDB /etc/rc.d/rc4.d/S99OracleDB
$ ln -s /etc/rc.d/init.d/OracleDB /etc/rc.d/rc5.d/S99OracleDB

---

The following commands will ensure that the databases will shut down in runlevels 0,1,2 and 6:

---

$ ln -s /etc/rc.d/init.d/OracleDB /etc/rc.d/rc0.d/K10OracleDB          # Halting
$ ln -s /etc/rc.d/init.d/OracleDB /etc/rc.d/rc1.d/K10OracleDB
$ ln -s /etc/rc.d/init.d/OracleDB /etc/rc.d/rc2.d/K10OracleDB
$ ln -s /etc/rc.d/init.d/OracleDB /etc/rc.d/rc6.d/K10OracleDB        # Rebooting

---

 Reference: Oracle Database HOWTO

How to Trace or Debug Database for issues / How to setup Tracing in database.

often during integration testing we encounter ORA Data base errors,  In this case we are debugging ORA- 02074 error.

here is how we can get more info about the tracing.

You need to set the command as below to you DB:

alter system set events '2074 trace name errorstack level 3';

If you would like to know more about the alter system set events command please

Oracle event trace types - by Burleson Consulting

When you set the above command and reproduce the issue, what ever that might be, look into the Alert_xxx.log file for statements such as below

Mon Apr 04 10:31:34 2011
Errors in file <Path>/trace/<InstanceName>_ora_20926.trc:
ORA-02074: cannot SET NLS in a distributed transaction
Mon Apr 04 10:31:36 2011
Errors in file <Path>/trace/<InstanceName>_ora_20950.trc:
ORA-02074: cannot SET NLS in a distributed transaction

This file gives the trace file name which contains the session dumps. In order to read the trace file, I believe you need extensive DBA skills. But do give it a shot.

Oracle 11g DB vs Microsoft SQL 2008

Once when I visited one of my friends, My friend and I had a (so called healthy) Debate between Oracle 11g DB vs Microsoft SQL 2008.

I was towards oracle's performance and high cost and my friend was towards MS DB and its benifits of being low cost and may be better performance...

After a while... this kind of continued in a email... My friend sent me this PDF document

Twelve ways to reduce costs with SQL Server 2008 - White paper1.pdf

For my review and reading....

As a counter response or you can say to continue the healthier side of the debate... I sent the below response...

Hi Friend,

Had some time in hand to read the PDF that you sent me... thanks for this... now as this is in front of the table...looks like I was right 100%...

Now the Twelve ways to reduce costs with SQL Server 2008 - White paper1.pdf that you attached talks only about cost and cost reduction... and I believe I agreed that Oracle is very very very expensive compared to MS 2008 and I believe I also stated only Enterprise Architecture budget can only offered Oracle... this document does not tell any thing about performance and benchmark comparison with matching cost ....

Now I believe this PDF that you have provided is a sales pitched document.... now I would like to provide my side of refference:

Comparative Management Costs Stud - By Edison group    ---> this is not an oracle document.... (Thanks to google in helping me find one) ... 

A third party comparative Study, this is cost with performance and DBA Management.... Read the conclusion of both, your reference and my reference, and you will know what I was really about and how they compare.... But do read the report in detail for knowledge sake...

The fact speaks out in terms of comparative  study....

Hope this helps you get some insight in comparison. I believe this was a healthy debate.

Cheers,

Arun.