BI Publisher Scheduler Error :oracle.apps.xdo.service.delivery.DeliveryException: oracle.apps.xdo.delivery.MissingRequiredPropertyException: Required property missing. Property name :[HOST]

An annoying error often occurs with BI Publisher especially when using the proprietary quartz scheduler.When faced with an error such as : oracle.apps.xdo.service.delivery.DeliveryException: oracle.apps.xdo.delivery.MissingRequiredPropertyException: Required property missing. Property name :[HOST] Login as XMLP Admin Go to Admin Panel Click on Delivery Configuration Make sure the output location you have defined in your plsql scheduler or webservice is the one thats specified here. This should get rid of this error

Tracing Oracle Sessions using Trace Files:

Oracle 10046 trace can be used to identify the problematic SQL and its behavior.

Start New Session:
First of all we will start new session by starting SQL* PLUS.

Enable Session Trace:
Now we will run enable tracing for the current session by running below command.
ALTER SESSION SET SQL_TRACE=TRUE;

Enable Timed Statistics:
Now we will run below command to enable the timed statistics for our current session.
ALTER SESSION SET TIMED_STATISTICS=TRUE;

Set Dump File Size:

Now we will set the max_dump_file_size parameter to a value of unlimited so as to have a complete trace with all necessary information.
ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;

Add Identification Suffix:
Now we will set an identification suffix for our trace file so that we can identify the trace file for our current session amongst other trace files in the same directory.
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TRACE1';

Turn On Oracle 10046 Trace Event:
Now we will turn on Oracle 10046 Trace Event on for our current session. The valid values for the Trace Level are as under:

Level 1 Standard SQL trace. Traces all activities until the end of the session.
Level 4 Trace the SQL statements for binds only.
Level 8 Trace the SQL statements for waits only.
Level 12 Trace the SQL statements for both binds and waits.

Attempting to run ALTER SESSION SET EVENTS…. directly may result in ORA-01031: insufficient privileges error. Therefore you will have to log in as SYS, grant privileges and then run the ALTER SESSION command.
LOGGED IN AS SYS..
GRANT ALTER SESSION TO MYTF1;
LOGGED IN AS MYTF1..
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Turn Off Oracle Trace:
Now run some simple query and then turn off Oracle trace.
SELECT * FROM MYTABLE;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Look for Trace File:

Now we will look for your trace file by finding the value specified in USER_DUMP_DEST parameter.
SELECT VALUE FROM V$PARAMETER WHERE NAME='USER_DUMP_DEST';
Once you have found the value then change to that directory in your OS.
TRACE1.TRC
You can find the correct trace file by running below command in UNIX prompt.
find . -name "*TRACE1*" –print
Finally we will use the TKPROF utility to create a human readable file of our trace file.
TKPROF TRACE1.TRC

ORA-38760: This database instance failed to turn on flashback database

ORA-38760: This database instance failed to turn on flashback database
As per Oracle :

Error: ORA-38760

Cause: Database flashback is on but this instance failed to start
generating flashback data. Look in alert log for more specific
errors. Action: Correct the error or turn off database flashback.


Solution A:

To Enable flashback:

1.Shutdown the database either in immediate or normal mode.

2.Mount the database.

3.Enable flashback.

SQL>Alter database flashback on;

4.Open the database.

Sometimes even after solution A you are still getting errors:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.4798E+10 bytes
Fixed Size 2046472 bytes
Variable Size 671090168 bytes
Database Buffers 1.4110E+10 bytes
Redo Buffers 14729216 bytes
Database mounted.
SQL> alter database flashback off;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

SQL>

Then you should apply solution B.

Solution B:

1. Startup mount.

2. Check db_recovery_file_dest parameter and ensure this directory exists.

3. Check restore points:

select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION#
from v$restore_point;

4. Drop restore point:

drop restore point ;

5. alter database flashback off;

6. Shutdown immediate;

7. Startup

Mining Information from the listener Log

Mining Information from the Listener Log

An Oracle database infrastructure has several components — the database, the listener, and the clustering components (CRS, CSS, ONS, and so on, if it’s a RAC database). All these components produce extensive logs to let you know what’s happening behind the scenes. These logs show information vital to understanding the working of the database. Perhaps the most popular and most commonly used is the database alert log, which offers a running commentary on the operation of the database. You may find many utilities and tools, including the Grid Control and Database Console interfaces from Oracle itself, to parse the alert log and reveal valuable information.

However, a very useful source of information is often overlooked — the listener log. The listener log shows some information that is not available anywhere else (for example, the service names used by the clients). Some of the information can also be obtained by other means, such as via the IP address of the clients recorded in audit trails.
But even in such cases, the listener log provides a non-intrusive source for which you don’t have to place instrumentation inside the database, as you must do when turning on auditing. In addition, listener logs also record the listener operations, both successful and unsuccessful, which can show attacks against the listener. Since listener is usually the target of many database attacks, this information can reveal valuable clues and help you build better defenses. In summary, listener logs provide far too much valuable information to be ignored.

Part 1
Part 2
Part 3

Diagnosing Oracle® Database Performance on AIX®

For Oracle Database environments, ongoing performance monitoring and tuning activities are essential to getting the most of your systems investment. In order to be most effective, monitoring and tuning must be performed at multiple levels of the solution stack. This includes the hardware, Operating System, Database and Application levels.

This paper discusses two commonly available performance monitoring tools – Oracle Statspack and IBM NMON – and how they can be used to monitor and analyze possible performance issues for Oracle Database applications running in an AIX/System p™ server environment. In Oracle10g environments, Automatic Workload Repository (AWR) reports can be used in place of Statspack. A number of real-world examples of Statspack and NMON data are provided.

This paper assumes that the reader is familiar with Oracle Database as well as the AIX/System p-server environment.

SQL> select * from smt008@sprint.world;select * from smt008@sprint.world *ERROR at line 1:ORA-28545: error diagnosed by Net8 when connecting to an agentUnable to retrieve text of NETWORK/NCR message 65535ORA-02063: preceding 2 lines from SPRINT.WOR

If the link was working before , quickly check and verify the following :

1.Confirm whether the dblink password on the remote database wasnt changed.
2.Check whether the table you are trying to query exists on the remote database.
you can "desc remote_table@dblink"
3.If the password was changed , recreate the link using the new password.

Recreate the Oracle database link with the proper username and password. Note that the username and password must be in double quotes.

Example:

create public database link ODBC connect to "sa" identified by "pencil" using 'hsodbc'.


Rgds

FSN

Getting Away with Oracle SOX 404 Audits

Hi There

I have to admit that this paper :"Project Lockdown" by Arup Nanda is the best to prepare for any SOX audit http://www.oracle.com/technetwork/articles/project-lockdown-133916.pdf Read it and enjoy .