C Troubleshooting

C.2.1 Assessing Sequence Sizes

Perform this test to proactively determine if your database is nearing the point where this situation may occur. If the result of this test for the number of Received DCMs, Received DCIs, or discrepancies is well below 9,999,999,999—or 1,000,000,000,000,000 for responses—you do not need to reseed the corresponding sequence.

C.2.2 Reseeding Sequences

If any of your sequence numbers are approaching or exceed the maximum value, you should reseed them. The system generates sequence numbers in increments of 100, so unused numbers are available.

When you reseed a sequence, the system prompts you to provide a previously unused start value. After reseeding, the system uses the same increment value to generate new values, ensuring that the new generated values are unique.

To reseed any of these sequences:

1. Stop all Oracle Clinical activity on the database until this procedure completes.

2. Connect to the database through SQL*Plus as RXC.

3. To determine which seed numbers are already in use for the sequence(s) you are reseeding, issue a command:

   • For RDCMs:

     SELECT distinct mod(received_dcm_id,100) 
     FROM received_dcms;
     

   • For RDCIs:

     SELECT distinct mod(received_dci_id,100) 
     FROM received_dcis;
     

   • For discrepancies:

     SELECT distinct mod(discrepancy_entry_id,100) 
     FROM discrepancy_entries;
     

   • For responses:

     SELECT distinct mod(response_id,100) 
     FROM responses;
     

   Note:

   If this is a replicated environment, run this command in all replicated instances.

4. Choose a new starting seed number value between 0 and 99 (inclusive) that is not in the list returned by the above step.

5. Issue a command to drop the sequence(s) you are reseeding:

   • For RDCMs:

     DROP sequence received_dcm_seq ;
     

   • For RDCIs:

     DROP sequence received_dci_seq ;
     

   • For discrepancies:

     DROP sequence discrepancy_entry_seq ;
     

   • For responses:

     DROP sequence response_seq ;
     

6. Issue a command to recreate the sequence(s) with a new starting value. The system prompts you to enter the new starting value (START WITH &SEQ_START_NO).

   • For RDCMs:

     CREATE SEQUENCE received_dcm_seq 
     INCREMENT BY 100 
     START WITH &SEQ_START_NO 
     MAXVALUE 9999999999 
     MINVALUE 1 
     NOCYCLE 
     CACHE 20 
     NOORDER;
     

   • For RDCIs:

     CREATE SEQUENCE received_dci_seq 
     INCREMENT BY 100 
     START WITH &SEQ_START_NO 
     MAXVALUE 9999999999 
     MINVALUE 1 
     NOCYCLE 
     CACHE 20 
     NOORDER;
     

   • For discrepancies:

     CREATE SEQUENCE discrepancy_entry_seq 
     INCREMENT BY 100 
     START WITH &SEQ_START_NO 
     MAXVALUE 9999999999 
     MINVALUE 1 
     NOCYCLE 
     CACHE 20 
     NOORDER;
     

   • For responses:

     CREATE SEQUENCE response_seq 
     INCREMENT BY 2000 
     START WITH &SEQ_START_NO 
     MAXVALUE 1000000000000000 
     MINVALUE 1 
     NOCYCLE 
     CACHE 20 
     NOORDER;
     

   Note:

   In the case of responses, the sequence uses 2000 as the increment and Oracle Clinical manipulates this value by adding 100 to it 20 times before going back to sequence.

7. Issue this command to grant access:

   • For RDCMs:

     GRANT SELECT on received_dcm_seq to RXCLIN_MOD;
     

   • For RDCIs:

     GRANT SELECT on received_dci_seq to RXCLIN_MOD;
     

   • For discrepancies:

     GRANT SELECT on discrepancy_entry_seq to RXCLIN_MOD;
     

   • For responses:

     GRANT SELECT on response_seq to RXCLIN_MOD;
     

C.3.1 Message: Not Using Named Package

This message may appear in the .log file and is a data replication issue. Oracle Clinical expects to conduct data replication between two locations using named packages rather than synonyms.

Solution: The named packages should have been created during setup for replication by executing the dyna_rxapkirp.sql script. See "Creating the Package for Replicating Investigators and Sites".

C.3.2 Message: ORA-12223

Full message text:

C.3.3 Message: ORA-04020

Full message text:

C.3.4 Message: Unable to Change Mode

323600 Unable to change to test mode, another session may be connected.

323700 Unable to change to production mode, another session may be connected.

325700 Unable to change to test mode (\0), synonyms not created.

325800 Unable to change to production mode (\0), synonyms could not be dropped.

For each of the above error messages, Oracle Clinical users should check with the administrator. This problem could be due to an RXCSYN package error, missing grants to RXC, or synonym conflicts with your schema objects.

These messages may appear if you switch between production and test modes while having another session open under the same userid. It may also appear if a user submitted a reports job before switching modes, and the reports engine is still associated with that user. The system considers this to be another session by the same user.

To check if you are logged on to more than one session, from SQL, you can enter the command:

select username from v$session;

If you do not have access to v$session, consult with a DBA.

In the case of reports jobs, you can prevent future problems by changing the MAXIDLE time of the Reports Server, which controls the length of time a user/engine session is kept open.

To change the MAXIDLE time of the Reports Server:

1. Open up the report queue manager and select the report queue of concern.

2. Select Options, then Privileges, and Administrator and log on as administrator.

3. Choose Queue, then Properties and change the maximum idle time to one minute or some reasonable smaller number (depending on the number of reports, users, and so forth on that queue).

In the case of a user switching modes, you can create a separate test account as described under ORA-04020, above.

C.3.5 Message: ConnectOCL - 970100

See "Managing High Sequence Numbers".

C.3.6 Message: PL/SQL Package Body: Looping Synonyms

If procedure generation fails and the parse error displays: "PL/SQL Package Body: Looping Synonyms" the problem is actually that the account RXC_PD does not have a password set. It may have expired due to Oracle Database security settings. To reset these passwords, log into SQL*Plus and enter:

set_pwd rxc rxc_pd

The system prompts for the RXC password and the new RXC_PD password.

C.5.1 Check the Failure Text in the Submitted Batch Jobs Window

If a problem arises while you are running PSUB, you should first review the Failure Text field of the Submitted Batch Jobs window.

To check this field for your batch job:

1. Open the Submitted Batch Jobs window: select Admin, then PSUB/Reports Jobs, and then Batch Jobs.

2. Locate the relevant Batch Job ID number.

3. Check the Execution Status of the job. If there is an entry in the Failure Text field, make a note of its contents

If the failure text does not help you to resolve the problem, see "Check the PSUB Log Files". If your batch job is hanging because the batch queue is full, see "If Batch Jobs Hang and the Batch Queue Is Full".

C.5.2 Check the PSUB Log Files

The PSUB process log files are cumulative, text-based descriptions of PSUB activity. These files are very helpful when you are troubleshooting problems with PSUB Process log files can include time stamped entries for:

• Error messages returned by the PSUB process

• All jobs submitted by the user; the entry may include each job's:

  • Message ID

  • Batch_job_ID

  • User name

Naming Convention

On both UNIX and Windows systems, PSUB process log file names are in the form:

rxcpsd_product_instance code_environment_1.log

On UNIX systems, there is a second process log file. Whenever you examine the "*_1.log" process log file on UNIX systems, you should also check this second file to see if it contains relevant entries. Its name is identical to the first log file, except that it has an "_2" suffix, rather than "_1". So the second UNIX process log file name is in the form:

rxcpsd_product_instance code_environment_2.log

The "*_2.log" process log files contain error and warning messages that are generated by certain UNIX commands that the PSUB process executes (e.g., non-background commands). These commands are not present in the PSUB service on Windows. Therefore, Windows systems only generate "*_1.log" process files.

Verbose vs. Nonverbose Mode

The [verbose|nonverbose] argument must be included when the PSUB startup command, rxcpsdps, is executed. Oracle recommends that you start PSUB in verbose mode because the process logs that are generated:

• Contribute to efficient troubleshooting

• Do not pose significant disk space concerns

On UNIX systems, rxcpsdps is 'wrapped' in the OPA script start_psub. By default, the start_psub script executes rxcpsdps in verbose mode.

On Windows systems, the PSUB service requires that you explicitly provide the [verbose|nonverbose] argument. See "Managing the PSUB Process".

If you cannot check the Failure text or the .out and .log files because the batch queue is hung, see "If Batch Jobs Hang and the Batch Queue Is Full".

Verify that the process log files for the relevant Batch Job ID exist.

Review the .out and .log files. The following table summarizes these files. Make a note of any error messages.

Check the job-specific log and output files first, then the central log file. In the central log file, search for the batch job ID number to find the relevant entry. See if the database and code environment settings are correct.

C.5.3 If Batch Jobs Hang and the Batch Queue Is Full

If all PSUB jobs hang (that is, they do not reach a completed execution status), and the batch queue is full, attempt to clear the queue and submit a single job to PSUB. If a problem then occurs with a single job, it may be clearer which area is causing the problem. The method for clearing the queue is either: stop all of the hung batch jobs (on UNIX systems) or stop the PSUB service (on Windows systems).

The queue may become full and PSUB jobs may hang under the following circumstances:

• PSUB is waiting, either for an operating system resource, or a database resource

• the operating system is overloaded, for example, a built-in limitation, such as maximum number of processes, is exceeded.

C.5.4 Determining if PSUB Is Running for a Database

To find out if a PSUB process is listening to a particular database, and if it is, what code environment it is running in, enter this query:

SQL> select host, code_environment, stop_ts
  2  from psub_process_log
  3  where start_ts = (
  4  select max (start_ts) from psub_process_log;

This query returns the:

• computer on which PSUB was last started against the database

• code environment

• state of the process:

  • if stop_ts is null, the PSUB process is currently active

  • if stop_ts is not null, the PSUB process is stopped.

  • new code list

rxcpsdps [verbose|nonverbose]product_instance code_environment 

opapps 15685 1 0 Apr 04 ? 0:00 rxcpsdps verbose example_db ssuneja_oc40_sun
opapps  4143 1 0 Apr 02 ? 0:00 rxcpsdps verbose example_db 40102_8163

C.5.5 Troubleshooting PSUB Based on the Batch Job's Execution Status

Execution status as reported in the Submitted Batch Jobs form is shown below. You can take various actions depending on execution status.

Examine the Submitted Batch Jobs window, look for the Execution Status and Failure Text for your Batch Job ID, and take one of the following actions, depending on the circumstances.

C.5.6 Handling PSUB Failures that Return "Fatal two-task communication protocol" Error

If you submit a PSUB job that fails and returns a "Fatal two-task communication protocol" error (this failure is sometimes followed by the "End-of-communication-channel" in the core dump information on your console), you might have the environment variable NLS_LANG set inconsistently with the settings in the database.

To verify that the environment variable NLS_LANG matches the actual database settings:

1. Execute the following query:

   SQL> select parameter, value from V$NLS_PARAMETERS
        where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
   

2. Open opa_settings and search for the following string:

   db_env_setting:database_name:NLS_LANG
   

   1. If you do not find this string, add a line with the following syntax:

      db_env_setting:database_name:NLS_LANG:NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET
      

      where NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET are the values returned in Step 1.

   2. If you find the string, correct the values to match the values in Step 1 in the following syntax:

      db_env_setting:database_name:NLS_LANG:NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET
      

      where NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET are the values returned in Step 1.

C.5.7 Handling PSUB Failure that Returns "Illegal use of PSLAUNCH…" Error

In a UNIX environment, you may see the following error when you submit a PSUB job (3GL or PLSQL):

Illegal use of PSLAUNCH by user. Job ID=batch_job_ID. Exiting... 

C.5.8 Tracking Previous PSUB Process Connections

To find out specific information about PSUB connections to a given database, query the table RXC.PSUB_PROCESS_LOG. This will return the:

• instance

• environment

• time a PSUB process started

• time a PSUB process stopped.

SQL> SELECT start_ts, host, code_environment, server_os
  2  from psub_process_log
  3  where start_ts= (select max(start_ts) from psub_process_log);

SQL> SELECT start_ts, stop_ts, host, code_environemnt
  2  from psub_process_log order by 1;

C.5.9 PSUB Fails to Start

If PSUB does not start; for example, after installing or upgrading:

1. Check one line in sqlnet.ora (in drive:\app\oracle\product\11.2.0.2.0\NETWORK\ADMIN):

   • UNIX: Edit sqlnet.ora by commenting out the following line (add # at the beginning of the line) and save it.

     # SQLNET.AUTHENTICATION_SERVICES = (NTS)
     

   • Windows: Make sure the same line in sqlnet.ora is not commented out (if there is a pound sign (#) at the beginning of the line, remove it and save):

     SQLNET.AUTHENTICATION_SERVICES = (NTS)
     

2. Locate and ensure that these lines in init.ora are not commented out and the values are as specified:

   remote_os_authent=null

   os_authent_prefix=""

   (a null string)

3. Shutdown the database.

4. Start the database.

5. Start PSUB.

C.5.10 PSUB Remains at Entered Status

If PSUB remains at Entered status, you may need to manually restart the AQ process:

1. Stop PSUB, see "Starting and Stopping PSUB.".

2. Log in as sysdba.

3. Enter:

   EXEC DBMS_AQADM.START_QUEUE('RXC.PSUB_REPLY_QUEUE',TRUE,TRUE); 
   EXEC DBMS_AQADM.START_QUEUE('RXC.PSUB_SEND_QUEUE',TRUE,TRUE); 
   

4. Restart PSUB.

5. Rerun the job.

C.5.11 Troubleshooting PSUB on a Windows Database

If you have difficulty starting PSUB on a Windows database after upgrading to or installing Oracle Clinical:

1. Open the sqlnet.ora file and confirm that following line is not commented (that is, there is no '#' at the beginning of the line). If there is, uncomment the line (remove the #):

   sqlnet.authentication_service=(NTS)
   

2. Attempt to start PSUB.

If PSUB fails to start:

1. Open the init.ora file. Ensure that the following lines are not commented out and have the values specified. If not, uncomment and/or change the values.

   remote_os_authen=NULL
   os_authent_prefix="OPS$"
   

2. Shut down any databases on the Windows server, then start the databases.

C.7.1 Truncated Field Display

Oracle Clinical reports may:

• display field values that appear truncated

• display dates as ******

To fix this issue, change the display setting on Windows to 150%.

From the Windows Control Panel, navigate to Ease of Access, then Optimize, then Visual Display.

If the Windows 2008 R2 application server is accessed using remote desktop, you may need to apply a Microsoft Windows patch - 2726399 to be able to modify the setting.

C.7.2 Preview for Form Layout Editor or DCI Form Generation Fails on Standalone Reports Servers

This problem can arise if you add separate Reports Servers to an installation with no Forms Servers configured to support them. For separate Reports Servers to work, you must set up registry settings on each separate Reports Server and a Forms Server, and share directories on the Forms Server. See "Setting Up Image Viewing" and OPA_XMLTEMP_UNC and OPA_XMLTEMP_HTTP for more information.

C.7.3 Unable to Stop a Reports Job

Check if this is a scheduled job. If it is, use the Oracle AS10gR2 Enterprise Manager to stop it. Navigate to Action, Report Queue Manager from any screen, or Admin, PSUB/Report Jobs, Enterprise Manager. The Report Queue Manager window opens. The instructions vary by manager version, so follow the instructions in the window to stop the job.

C.7.4 PDR Fails with "Generating Cover Page" Error

The Patient Data Report fails if the patient code contains a slash (/) and gives an error beginning:

Generating Cover Page for patient 770/001

where the patient code is 770/001.