RPE-02062: Itemtype cannot be dropped as it has a running process.

When deploying a process flow using the control center of OWB (11r1), following error is raised:

Error: RPE-02062: ItemType PKG cannot be dropped as it has running Processes. You must first abort all the running processes using the Oracle Workflow Monitor.
INFORMATIONALRPE-02071: Deployment has been aborted due to a previously reported critial error.

I used following steps to solve the issue:


Step1: Find the running processes:

SQL*Plus: Release 11.1.0.6.0 – Production on Tue Jul 1 10:46:10 2008

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

Enter user-name: owf_mgr@DWHD
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

OWF_MGR> SELECT item_type, item_key, begin_date, end_date, activity_status
2 FROM wf_item_activity_statuses
3 WHERE activity_status’COMPLETE’
4 AND item_type = ‘PKG’;

ITEM_TYP···ITEM_KEY·································BEGIN_DAT··· END_DATE···ACTIVITY
—————————————————————————————-
PKG·········WB_IK_20080627_200542_108294···27-JUN-08·····················ACTIVE
PKG·········WB_IK_20080627_200542_108294···27-JUN-08····················· NOTIFIED

OWF_MGR>

Step 2: Remove the active process

OWF_MGR>
OWF_MGR> BEGIN
2 WF_ENGINE.ABORTPROCESS(‘PKG’, ‘WB_IK_20080627_200542_108294′);
3 END;
4 /

PL/SQL procedure successfully completed.

OWF_MGR> commit;

Commit complete.

OWF_MGR>

Step 3 Redeploy the process flow

From the logs:

PKG···Create······Success

ORA-01400 during refresh of MVIEW

 

When trying to refresh a materialized view based on a query that uses a mssql table (using the SqlServer Gateway), I run into the following error:

ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from MSSQLDB1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712

 

After some investigation I found this was caused by a “NOT NULL” constraint on the mview-table.

A small scenario:

STEP 1: Create a Materialized View

SQL> CREATE MATERIALIZED VIEW COUNTY_IS_NULL
2 REFRESH FORCE ON DEMAND
3 AS
4 SELECT a.agreementnum
5 ,a.county county_id
6 ,a.createddate agree_cre_date
7 FROM bmssa.EXU_AGREEMENTTABLE@mssqldb1 a
8 WHERE a.agreementnum = ' E00000001';

 

Materialized view created.

 

STEP 2: View Content of the Materialized View (County_Id is null!)

SQL>
SQL> SELECT * FROM county_is_null;

 

AGREEMENTN COUNTY_ID  AGREE_CRE
---------- ---------- ---------
E00000001 05-JUL-07
 

SQL>
SQL> SELECT NVL(county_id, 0) FROM county_is_null;

NVL(COUNTY
----------
0

 

 STEP 3: Refresh the Materialized View

SQL>
SQL> BEGIN
2 dbms_mview.refresh('COUNTY_IS_NULL');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from MSSQLDB1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 2

 

STEP 4: Disable the NOT NULL Constraint on COUNTY_ID

SQL>
SQL> SELECT constraint_name, search_condition FROM user_constraints c
WHERE c.table_name='COUNTY_IS_NULL';

CONSTRAINT_NAME SEARCH_CONDITION
------------------------------ -------------------------------------------------
SYS_C0029024 "AGREEMENTNUM" IS NOT NULL
SYS_C0029025 "COUNTY_ID" IS NOT NULL
SYS_C0029026 "AGREE_CRE_DATE" IS NOT NULL

 

SQL>
SQL> ALTER TABLE county_is_null MODIFY CONSTRAINT sys_c0029025 DISABLE;
Table altered.

 

STEP 5: Refresh the Materialized View

SQL>
SQL> BEGIN
2 dbms_mview.refresh('COUNTY_IS_NULL');
3 END;
4 /

 

PL/SQL procedure successfully completed.
SQL>

  

Connect from RHEL5 to a MSSQLServer Database using ODBC

For a data warehouse project I’m working on, I like to add a MS SQLServer database as a datasource to OWB. So the first thing I need is an ODBC Datasource. No problem, but there just one small issue which makes the creation of this ODBC datasource a bit less “default”: My database is running on a linux machine. J
So, A few more steps are needed:

  • Check the unixodbc packages are installed
  • Install the KDE driver manager components for ODBC
  • Install and configure FreeTDS
  • Add the FreeTDS Driver to the ODDBC Configuration Files
  • Add the MSSQLServer Datasource


STEP 1: Check the unixodbc-packages are installed

[oracle@lnx-srv-dwh-dev ~]$ odbcinst -j
unixODBC 2.2.11
DRIVERS…………: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/oracle/.odbc.ini
[oracle@lnx-srv-dwh-dev ~]$

If the unixODBC packages aren’t installed, you can install them using yum or using the “rpms” located at the RHEL5 installation disks.

STEP 2: Install the KDE driver manager component for ODBC

This package will install the graphical “ODBCConfig” tool which we can use to add ODBC drivers and Data Sources. You can compare this tool with the “ODBC Data Source Administrator” in MS Windows.

[root@lnx-srv-dwh-dev rpm]# yum install unixODBC-kde-2.2.11-7.1.x86_64
Loading “installonlyn” plugin
Loading “rhnplugin” plugin
Setting up Install Process
Setting up repositories
rhel-x86_64-server-vt-5 100% |=========================| 1.4 kB 00:00
rhn-tools-rhel-x86_64-ser 100% |=========================| 1.2 kB 00:00
rhel-x86_64-server-5 100% |=========================| 1.4 kB 00:00
Reading repository metadata in from local files
primary.xml.gz 100% |=========================| 1.3 MB 00:02
################################################## 4235/4235
Parsing package install arguments
Resolving Dependencies
–> Populating transaction set with selected packages. Please wait.
—> Downloading header for unixODBC-kde to pack into transaction set.
unixODBC-kde-2.2.11-7.1.x 100% |=========================| 12 kB 00:00
—> Package unixODBC-kde.x86_64 0:2.2.11-7.1 set to be updated
–> Running transaction check

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
unixODBC-kde x86_64 2.2.11-7.1 rhel-x86_64-server-5 594 k

Transaction Summary
=============================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)

Total download size: 594 k
Is this ok [y/N]: y
Downloading Packages:
(1/1): unixODBC-kde-2.2.1 100% |=========================| 594 kB 00:02
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: unixODBC-kde ######################### [1/1]

Installed: unixODBC-kde.x86_64 0:2.2.11-7.1
Complete!
[root@lnx-srv-dwh-dev rpm]#

STEP 3: Install and configure FreeTDS

FreeTDS is a set of libraries that allows your programs to natively talk to Microsoft SQL Server and Sybase databases. Those libraries will used to create an ODBC driver used to connect to our SQLServer database. You can download the software (freetds-stable.tgz) from ftp://ibiblio.unc.edu/pub/Linux/ALPHA/freetds/stable/
Following log shows the install and configuration steps:

[oracle@lnx-srv-dwh-dev freetds]$ pwd
/tmp/freetds
[oracle@lnx-srv-dwh-dev freetds]$ ls
freetds-stable.tgz
[oracle@lnx-srv-dwh-dev freetds]$tar zxvf freetds-stable.tgz

[oracle@lnx-srv-dwh-dev freetds]$ cd freetds-0.64/
[oracle@lnx-srv-dwh-dev freetds-0.64]$
[oracle@lnx-srv-dwh-dev freetds-0.64]$ ./configure –with-tdsver=7.0 –prefix=/usr/local/freetds

config.status: creating include/config.h
config.status: executing depfiles commands
[oracle@lnx-srv-dwh-dev freetds-0.64]$ make

make[1]: Entering directory `/tmp/freetds/freetds-0.64′
make[1]: Nothing to be done for `all-am’.
make[1]: Leaving directory `/tmp/freetds/freetds-0.64′
if test ! -f PWD; then cp ./PWD.in PWD; fi
[oracle@lnx-srv-dwh-dev freetds-0.64]$ su
Password:
[root@lnx-srv-dwh-dev freetds-0.64]# make install

make[2]: Nothing to be done for `install-exec-am’.
/bin/sh ./mkinstalldirs /usr/local/freetds/etc
if test ! -f /usr/local/freetds/etc/freetds.conf; then \
/usr/bin/install -c -m 644 ./freetds.conf /usr/local/freetds/etc/freetds.conf; \
fi
if test ! -f /usr/local/freetds/etc/locales.conf; then \
/usr/bin/install -c -m 644 ./locales.conf /usr/local/freetds/etc/locales.conf; \
fi
make[2]: Leaving directory `/tmp/freetds/freetds-0.64′
make[1]: Leaving directory `/tmp/freetds/freetds-0.64′

STEP 4: add the FreeTDS driver to the ODBC Drivers-file

Edit the “/ect/odbcinst.ini” file and add following lines:
[FreeTDS]
Description = v0.64 with protocol v8.0
Driver = /usr/local/freetds/lib/libtdsodbc.so
Setup = /usr/local/freetds/lib/libtdsodbc.so
FileUsage = 1

[root@lnx-srv-dwh-dev etc]# pwd
/etc
[root@lnx-srv-dwh-dev etc]# cp odbcinst.ini odbcinst.ini.ori
[root@lnx-srv-dwh-dev etc]# vi odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1

[FreeTDS]
Description = v0.64 with protocol v8.0
Driver = /usr/local/freetds/lib/libtdsodbc.so
Setup = /usr/local/freetds/lib/libtdsodbc.so
FileUsage = 1

As an alternative you can add the driver using the “ODBCConfig”-tool;

STEP 5: Add an MSSQLServer Datasource:

Start de” ODBCConfig” Administration Tool
Click the “System DSN”-add
Click the “Add”-button
Select the “FreeTDS”-driver
Click “OK”

Fill in the datasource properties

As an alternative, you can add the datasources to the “/etc/odbc.ini”-file

[oracle@lnx-srv-dwh-dev etc]$ pwd
/etc
[oracle@lnx-srv-dwh-dev etc]$ cat odbc.ini
[mySQLTest]
Description = my SQLServer Test Database
Driver = FreeTDS
Servername = mySQLTestServer
Server =
Address =
Port = 1433
Database =
TDS_Version = 8.0
Language = us_english
TextSize =
Domain =
PacketSize =

STEP 6: Test the brand new datasource!

Use the “isql”-tool to connect to our database.

[oracle@lnx-srv-dwh-dev etc]$ isql -v mySQLTest myusername mypassword
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL>

STEP 7: What’s next

From now on we can use this ODBC datasource in an Oracle Heterogeneous Service which we need when adding the SQL Server as a Source in Oracle Warehouse Builder.

SYSMAN Account is Locked

This morning I opened Enterprise Manager and instead of the regular “login”-screen, I found following screen:

 

Everything is up and running but Enterprise Manager is not able to connect to the database instance. Sounds like a connection problem, not?

These are the steps I followed to solve this issue and to reset the “sysman”-account:

STEP1: Check EM log file:

[oracle@myserver log]$ pwd


/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD
/sysman/log

 [oracle@ myserver log]$ tail -50 emoms.log

2008-02-11 23:12:04,968 [ApplicationServerThread-11]
ERROR app.SessionObjectManager sessionDestroyed.128 -
java.sql.SQLException: ORA-28000: the account is locked

java.sql.SQLException: ORA-28000: the account is locked

STEP 2: Check database users:

SYSTEM> select username, account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ -------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
SYSMAN LOCKED(TIMED)

 

STEP 3: Stop the Database Console

[oracle@myserver dbascripts]$ emctl stop dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0

Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.

https://myserver.mydomain:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...

 ...  Stopped.

[oracle@myserver dbascripts]$

STEP 4: Reset the SYSMAN-account


[oracle@myserver log]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 12 09:51:59 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

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

SYS> alter user sysman identified by <new_password> account unlock;
User altered.

STEP 5: Reset the SYSMAN-account in the “targets.xml”-file ($ORACLE_HOME/myserver.mydomain _mySID/sysman/emd) and modify the “Username”-Property and the “Password”-property as follows:

[oracle@myserver emd]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/emd
[oracle@myserver emd]$ cp targets.xml targets.xml.ori
[oracle@myserver emd]$ vi targets.xml

<Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="FALSE"/>
<Property NAME="password" VALUE="<new_password>" ENCRYPTED="FALSE"/>

STEP 6: Reset the SYSMAN-account in the “emoms.properties”-file ($ORACLE_HOME/myserver.my_domain_mySID/sysman.config) and modify the “oracle.sysman.eml.mntr.emdRepPwd”-parameter and the “oracle.sysman.eml.mntr.emdRepPwdEncrypted” in the following way:

[oracle@myserver config]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/config
[oracle@myserver config]$ cp emoms.properties emoms.properties.ori
[oracle@myserver config]$ vi emoms.properties

oracle.sysman.eml.mntr.emdRepPwd=<new_password>

oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

STEP 7: Start the Database Console

[oracle@myserver dbascripts]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
https://myserver.mydomain:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ...... started.
------------------------------------------------------------------
Logs are generated in directory
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/log

 

STEP 8: Test

Dba: Capturing a time frame from the ALERT.LOG

A thing I did not know about my database….

The alert.log file chronologically records messages and errors about database operations such as startups and shutdowns, internal errors (ora-00600), links to trace files and much more.
This often makes the alert.log-file, the primary file that needs to be consulted in order to identify and solve issues concerning your database.

Unfortunately there’s no default “auto-cleanup” of this file and so the file can grow to an unmanageable size (e.g. impossible to edit using VI) and that makes it hard to search for message within a certain time frame.
By using Enterprise Manager, it is very easy to browse through this alert.log file and to capture/select only those message you’re interested in by filtering on a start and end date.


An example:

STEP 1: Connect to the EM Database Console:

STEP 2: Click the “Alert Log Content” hyperlink (on the Home-tab)


. . .

STEP 3: Fill in search criteria and push “Go”-button

STEP 4: Result