Watch, Follow, &
Connect with Us

ID: 22833, StarTeam Service Startup/Shutdown with Oracle 9i Database Trigge

by Nanci Hansen Email: nanci.hansen.ctr@randolph.af.mil


StarTeam Server service not starting automatically, because the Oracle 9i database was not completely up before the service was trying to start.
Download Details
0 bytes
CDN Login Required to Download. (You will be redirected to the login page if you click on the Download Link)
To download this, you must have registered:
A free membership

For StarTeam, Version 1.0  to 1.0 0 downloads
Copyright: No significant restrictions


Size: 0 bytes
Updated on Mon, 29 Nov 2004 06:25:56 GMT
Originally uploaded on Mon, 29 Nov 2004 06:20:40 GMT
Description
Author : Srinivas Reddy Karla
Oracle Database Administrator,

Ref : Oracle Metalink note# 165256.1: Calling Operating System
Commands from PL/SQL - The Java Way

Problem : StarTeam service was not starting on Windows server reboot.

Solution: The solution was implemented by using java code and Oracle
Database triggers.

Requirements:
1. Oracle 9i Database.
2. JVM installed in your Oracle instance.
3. OS Windows 2000 server.

Step 1.
-------
Login to SQL*Plus as system.

C:\sqlplus system
SQL >

Step 2.
-------
Create a JAVA class to execute OS commands.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS
import java.io.*;
public class OSCmd{
public static String Run(String Command){
try{
Runtime.getRuntime().exec(Command);
return("0");
}
catch (Exception e){
System.out.println("Error running command: " + Command + "\n" + e.getMessage());
return(e.getMessage());
}
}
}

Step 3.
-------
Create a PL/SQL function to execute the OS command.

CREATE or REPLACE FUNCTION OSCmd_Run(Command IN STRING)
RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'OSCmd.Run(java.lang.String) return int';

Step 4.
-------
Grant the access privileges to the user system.
Login to sqlplus as sysdba

C:\ sqlplus “/as sysdba”

SQL> execute dbms_java.grant_permission( 'SYSTEM','SYS:java.io.FilePermission','<>','execute');

SQL> execute dbms_java.grant_permission( 'SYSTEM','SYS:java.lang.RuntimePermission','writeFileDescriptor','*' );

SQL> execute dbms_java.grant_permission( 'SYSTEM','SYS:java.lang.RuntimePermission','readFileDescriptor','*' );

SQL> Commit;

Step 5.
-------
Create before shutdown trigger.

CREATE OR REPLACE TRIGGER "SYS"."STOP_STARTEAM_SRVC" BEFORE
SHUTDOWN ON DATABASE Declare
x Varchar2(2000);
Begin
x := system.OSCommand_Run('Net stop ""');
End;

Step 6.
-------
Create after startup trigger.

CREATE OR REPLACE TRIGGER "SYS"."START_STARTEAM_SRVC" AFTER STARTUP ON DATABASE Declare
x Varchar2(2000);
Begin
x := system.OSCommand_Run('Net start ""');
End;



Step 6.
-------
Change the StarTeam srvce to manual.
If the Star Team service (Example: "StarTeam Server - STARDEV") is
set to Automatic, then change it to Manual in the services window.

Test:
-----

After the above mentioned setup, test the following scenarios.

1. Shutdown and startup the database from services window and see
the StarTeam service is stopped.
2. Shutdown and startup the database from command prompt and
verify the StarTeam service is stopped and started.
3. Reboot the Windows server and see StarTeam team service
started after the reboot.

   Latest Comments  View All Add New

Move mouse over comment to see the full text

Server Response from: ETNACDC02