Running dbms_scheduler within a timing interval

To run a statspack snap within a scheduled timing , you can use the below, it will start the job at 8 am and run until 8pm from monday until friday.


exec dbms_scheduler.create_job(JOB_NAME => 'JOB_STATSPACK_SNAP',job_type=> 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN STATSPACK.SNAP; END; ', START_DATE => trunc(sysdate) + 8/24 , REPEAT_INTERVAL => 'FREQ=HOURLY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8,9,10,11,12,13,14,15,16,17,18,19,20', ENABLED => TRUE, AUTO_DROP =>FALSE, COMMENTS =>'Statpacks snap') ;

another example , would be below running in between the time 8am til 8pm with interval 15 mins


exec dbms_scheduler.create_job(JOB_NAME => 'JOB_STATSPACK_SNAP',job_type=> 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN STATSPACK.SNAP; END; ', START_DATE => trunc(sysdate) + 8/24 , REPEAT_INTERVAL => FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=8,9,10,11,12,13,14,15,16,17,18,19;BYMINUTE=0,15,30,45', ENABLED => TRUE, AUTO_DROP =>FALSE, COMMENTS =>'Statpacks snap') ;

To check if your calendar string is correct , you can use below to do this.


declare
L_start_date TIMESTAMP;
l_next_date TIMESTAMP;
l_return_date TIMESTAMP;
begin
l_start_date := trunc(SYSTIMESTAMP);
L_RETURN_DATE := L_START_DATE;
for ctr in 1..100 loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8,20',
l_start_date, l_return_date, l_next_date
);
dbms_output.put_line('Next Run on: '
to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
);
l_return_date := l_next_date;
end loop;
end;
/

ORACLE :- Query remote LOB data ORA-22992

There's a issue in querying the LOB object prior Ora 10gr2.

Prior Ora10gr2,

You need to create a view in the source DB to convert the lob to string using either view or materialized view.
or same as below, create a function / procedure to convert the lob to string.

For Ora10gr2,
you can use the following to workaround the issue.

create or replace function lobconverterproc
( col1 in varchar2) return varchar2
is
var1 varchar2(1000);
BEGIN
SELECT FYDSCR INTO var1 FROM tablename
WHERE col1=v_col1;

RETURN var1;
END;
/

select lobconverterproc('Test') from dual;

Migrating from IAS 10.1.2 to 10.1.3.4

Other than the different in Application Server control GUI, if your application ear is compiled using JDK 1.42 , it might has error if it used the JDK 1.5.

http://download-west.oracle.com/docs/cd/B25221_04/web.1013/b14432/diagnosis.htm#sthref517

http://forums.oracle.com/forums/thread.jspa?threadID=361021

Other issue, is that it could be a bug as in the note “java.lang.NoClassDefFoundError: org/apache/log4j/Category After Upgrading from 10.1.3.1.0” ,Note ID : 460448.1
Which mentioned

http://forums.oracle.com/forums/thread.jspa?threadID=616870

To resolved this follow the guide here

http://www.pascalalma.net/2008/07/14/using-commons-logging-with-oc4j/

Opmn logs: -

Caused by: com.evermind.server.ejb.deployment.InvalidEJBAssemblyException: Unable to load ejb-class com.bidm.misc.ejb.session.ejbeans.MiGenBean, see section 23.2 of the EJB 2.1 specificationjava.lang.NoClassDefFoundError: org.apache.log4j.Logger

at com.evermind.server.ejb.exception.ValidationExceptions.unableToLoadEJBClass(ValidationExceptions.java:36)

Caused by: java.lang.NoClassDefFoundError: org/apache/log4j/Categoryat java.lang.Class.getDeclaredConstructors0(Native Method)at java.lang.Class.privateGetDeclaredConstructors(Class.java:2328)at java.lang.Class.getConstructor0(Class.java:2640)

Different between IAS 10.1.2 and 10.1.3

1. In 10.1.3 the dcmctl tool is no longer available, to performed the task that normally using the dcmctl you need to use the oc4j_admin at $ORACLE_HOME/j2ee/home or download the admin_client.jar in http://download.oracle.com/otn/java/oc4j/10131/oc4j_admin_client_101310.zip.

2. For the Oracle® Containers for J2EE Configuration and Administration Guide, Using the admin_client.jar Utility please refered the link here.

http://download.oracle.com/docs/cd/B31017_01/web.1013/b28950/adminclient.htm#i675530

E.g. normally to deploy application in 10.1.2 , you use

dmctl deployapplication -file xxx.ear -a TestInstance -co TestName

In 10.1.3 ,

java -jar admin_client.jar deployer:oc4j:opmn://test-cycle.oracle.com/TestName oc4jadmin welcome1 -deploy -file d:\temp\xxx.ear -deploymentName TestInstance –bindAllWebApps


3. Another important consideration in when migrating is that the default behaviour of the 10.1.3. 10.1.10 Invocation by Servlet Name without Mapping Is Disabled by Default

The 10.1.3.x Oracle Containers for J2EE Servlet Developer's Guide notes that by default, servlet invocation by class name is now disabled, but can be enabled through the property setting -Dhttp.webdir.enable=true. (Servlet invocation by class name was enabled by default in versions 10.1.2.x and prior.)

What is similar, but undocumented, is that this also applies to invoking a servlet through its value when there is no corresponding entry. Invoking a servlet through its servlet name in this way was enabled by default in 10.1.2.x and prior, but requires the setting -Dhttp.webdir.enable=true in 10.1.3.x versions.

For example, consider the following web.xml entry, with no corresponding entry:

    
 TestIt
 mypackage.TestIt

In 10.1.2.x, you could access this servlet using /servlet/TestIt without any property setting. In 10.1.3.x, you must set -Dhttp.webdir.enable=true to access the servlet in this way with the given configuration.

IAS error - REP-56040: Server rep_ already exists in the network

If you encounter these error "REP-56040: Server rep_ already exists in the network", then there's a possiblity that the report services detected the same services name in other server. To find out if it can detect other report services , run below command.

In MS Windows: %ORACLE_HOME%\bin\rwdiag.bat -findall
In Unix: $ORACLE_HOME/bin/rwdiag.sh -findAll

Broadcast mechanism used to locate servers------------------------------------------
Channel address = 228.4.6.6 Channel port = 14021
(1) Name = : Type = server : Host = .oracle.com
(2) Name = : Type = server : Host = .oracle.com
(3) Name = : Type = server : Host = .oracle.com
(4) Name = : Type = server : Host = .oracle.com

notice, that the report server services appear on the different hostname. In the rwservlet.trc,
Exception 51002 (): Bind to Reports Server failed.

There's two way to resolve this , 1 way is to set different report_server name compare with other server or 2 , changing the naming method in the $ORACLE_HOME/reports/rwnetwork.conf

Uncoment the multicast and enabled the naming method. e.g.

multicast channel="228.5.6.7" port="14021" timeout="1000" retry="3"/
!--namingService name="Cos" host="%NAMING_HOST%" port="%NAMING_PORT%"/--

to

discoveryService

!-- multicast channel="228.5.6.7" port="14021" timeout="1000" retry="3"/--
namingService name="Cos" host="testserver" port="14021"
/discoveryService


Metalink note:
- Intermittent REP-51002 or REP-56040 and Same Repserver is Displayed Multiple Times in Rwdiag.sh Output, Note ID :563367.1
-REP-51002 And Rep-56040 if More than one OC4J_BI_FORMS Instance is Created, Note ID : 390412.1

Oracle 10g enqueue waits descriptions

Here is a nice blog regarding oracle 10g enqueue waits, http://venzi.wordpress.com/2009/05/08/oracle-10g-enqueue-waits/

How TO: change the sql server service account

This internet link, show how you can changed the SQL Server service account without using SQL entereprise Manager, it can also helps you if you want to troubleshoot any problem once you changed the SQL server service account .


HOW TO: Change the SQL Server Service Account Without Using SQL Enterprise Manager in SQL Server 2000
http://www.myitforum.com/articles/18/view.asp?id=6724