Oracle® Clinical Administrator's Guide Release 4.6.2 E18818-02 |
|
|
PDF · Mobi · ePub |
This section offers solutions to known Oracle Clinical issues in the following categories:
If you upgraded to Oracle Clinical 4.6.2 from release 4.5.1, 4.5.2, 4.5.3, or 4.6, it is important to ensure that the internal identifier for each of the following does not exceed 2,147,483,647 (that is, (2^31-1)):
Received DCMs (RDCMs)
Received DCIs (RDCIs)
Discrepancies
When the internal identifier for these tables exceeds 2,147,483,647, the system incorrectly processes the identifiers in other tables, such as the Responses table. You can still view and update the responses, however batch validation, data extract, replication, and procedure execution operations fail or run incorrectly.
In Oracle Clinical 4.5.1 and after, there is code to prevent sequence numbers from exceeding 2,147,483,647. The OCL_DE_CONFIG Local Codelist includes a short value entry, "SEQUENCEBUFFER", which is assigned an initial long value of 1,000,000. At this setting, when a sequence number is within 1,000,000 of 2,147,483,647, the system displays a warning message when a user attempts to use a relevant subsystem and the system exits the current screen.
Perform this test to determine if your database is nearing the point where this situation may occur. If the results of this test for the number of Received DCMs, Received DCIs, and discrepancies is well below 2,147,483,647, you do not have to perform the other tasks described in this section.
To determine the sequence number for RDCMs in your system:
Connect to your database through SQL*Plus as RXC.
Issue this command to assess the number of Received DCMs:
SELECT received_dcm_seq.nextval FROM dual;
The system returns a number.
Compare the number returned to 2,147,483,647:
If the internal identifier number is approaching 2,147,483,647, you should reseed the received_dcm_seq sequence after you complete this task.
If the number exceeds 2147483647, reseed received_dcm_seq, identify data errors, and repair data errors according to the instructions in the following sections.
If neither of the above conditions apply, run this command from SQL connected as RXC:
alter sequence received_dcm_seq maxvalue 2147483646;
To determine the sequence number for RDCIs in your system:
Connect to your database through SQL*Plus as RXC.
Issue this command to assess the number of Received DCMs:
SELECT received_dci_seq.nextval FROM dual;
The system returns a number.
Compare the number returned to 2,147,483,647:
If the internal identifier number is approaching 2,147,483,647, you should should reseed the received_dci_seq sequence after you complete this task.
If the number exceeds 2147483647, reseed received_dci_seq, using the instructions in the following sections. If you use RDC or DCAPI, contact Oracle Support.
If neither of the above conditions apply, run this command from SQL connected as RXC:
alter sequence received_dci_seq maxvalue 2147483646;
Assess the Number of Discrepancies
To determine the sequence number for discrepancies in your system:
Connect to your database through SQL*Plus as RXC.
Issue this command to assess the number of Received DCMs:
SELECT discrepancy_entry_seq.nextval FROM dual;
The system returns a number.
Compare the number returned to 2,147,483,647:
If the internal identifier number is approaching 2147483647, you should reseed the discrepancy_entry_seq sequence after you complete this task.
If the number exceeds 2147483647, reseed the discrepancy_entry_seq sequence after you complete this task.
If neither of the above conditions apply, run this command from SQL connected as RXC:
alter sequence discrepancy_entry_seq maxvalue 2147483646;
If any of your response numbers are approaching or exceed 2,147,483,647, you should reseed them.
To reseed the Received DCM sequence:
Stop all Oracle Clinical activity on the database until this procedure completes.
Connect to the database through SQL*Plus as RXC.
To determine which seed numbers are in use, issue this command.
SELECT distinct mod(received_dcm_id,100) FROM received_dcms;
Note:
If this is a replicated environment, run this command in all replicated instances.Choose a new starting seed number value between 0 and 99 (inclusive) that is not in the list returned by the above step.
Issue this command to drop the received_dcm_seq sequence:
DROP sequence received_dcm_seq ;
Issue this command to recreate the Received DCM sequence with this value as the new starting value (for example, SEQ_START_NO):
CREATE SEQUENCE received_dcm_seq INCREMENT BY 100 START WITH &SEQ_START_NO MAXVALUE 2147483646 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
Issue this command to grant access:
GRANT SELECT on received_dcm_seq to RXCLIN_MOD;
To reseed the Received DCI sequence:
Stop all Oracle Clinical activity on the database until this procedure completes.
Connect to the database through SQL*Plus as RXC.
To determine which seed numbers are in use, issue this command.
SELECT distinct mod(received_dci_id,100) FROM received_dcis;
Note:
If this is a replicated environment, run this command in all replicated instances.Choose a new starting seed number value between 0 and 99 (inclusive) that is not in the list returned by the above step.
Issue this command to drop the received_dci_seq sequence:
DROP sequence received_dci_seq ;
Issue this command to recreate the Received DCI sequence with this value as the new starting value (for example, SEQ_START_NO):
CREATE SEQUENCE received_dci_seq INCREMENT BY 100 START WITH &SEQ_START_NO MAXVALUE 2147483646 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
Issue this command to grant access:
GRANT SELECT on received_dci_seq to RXCLIN_MOD;
Reseed Discrepancies Sequence Numbers
To reseed the discrepancies sequence:
Stop all Oracle Clinical activity on the database until this procedure completes.
Connect to the database through SQL*Plus as RXC.
To determine which seed numbers are in use, issue this command.
SELECT distinct mod(discrepancy_entry_id,100) FROM discrepancy_entries;
Note:
If this is a replicated environment, run this command in all replicated instances.Choose a new starting seed number value between 0 and 99 (inclusive) that is not in the list returned by the above step.
Issue this command to drop the discrepancy_entry_seq sequence:
DROP sequence discrepancy_entry_seq ;
Issue this command to recreate the Discrepancy sequence with this value as the new starting value (for example, SEQ_START_NO):
CREATE SEQUENCE discrepancy_entry_seq INCREMENT BY 100 START WITH &SEQ_START_NO MAXVALUE 2147483646 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
Issue this command to grant access:
GRANT SELECT on discrepancy_entry_seq to RXCLIN_MOD;
This section offers fixes or workarounds for the following error messages:
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".
Full message text:
Full message text:
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:
Open up the report queue manager and select the report queue of concern.
Select Options, then Privileges, and Administrator and log on as administrator.
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.
When a general protection fault (GPF) occurs during data entry, the system creates file rxcdecde.dbg, which contains a description of the cause of the GPF. The file resides in the RXC_ROOT directory.
On HP-UX and Compaq Tru64 UNIX, if the operating system parameter ulimit is set to unlimited, you get the following error when you run opa_setup or oraenv:
sh: unlimited: The specified number is not valid for this command
The workaround is to edit the oraenv shell script, adding a test for the word "unlimited." To modify this script:
Change your location to the bin directory:
% cd $ORACLE_HOME/bin
Make a backup copy of oraenv named oraenv.O
Use a text editor to add the two lines shown below in bold to the file oraenv.
if [ $? = 0 -a "$ULIMIT" != "unlimited" ]; then # added line
if [ $? = 0 -a "$ULIMIT" -lt 2113674 ] ; then
if [ -f $ORACLE_HOME/bin/osh ] ; then
exec $ORACLE_HOME/bin/osh
else
for D in 'echo $PATH | tr : " "'
do
if [ -f $D/osh ] ; then
exec $D/osh
fi
done
fi
fi
fi #added line
...
Save and exit the file oraenv
This section describes steps you should take, in order, when you troubleshoot PSUB problems.
To troubleshoot a PSUB job:
Troubleshooting PSUB Based on the Batch Job's Execution Status
Handling PSUB Failures that Return "Fatal two-task communication protocol" Error
Handling PSUB Failure that Returns "Illegal use of PSLAUNCH…" Error
See also "If PSUB Fails to Start".
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:
Open the Submitted Batch Jobs window: select Admin, then PSUB/Reports Jobs, and then Batch Jobs.
Locate the relevant Batch Job ID number.
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".
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
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.
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.
Name | Description | Directory |
---|---|---|
lbatch_job_id.log | Log file of a specific job | RXC_LOG |
obatch_job_id.out | Output file of a specific job | RXC_LOG |
*.log | Log file of the PSUB process (see "Viewing Log and Output Files on the Screen" for naming convention) | RXC_CENTRAL_LOG |
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.
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.
This section describes how to stop batch jobs on UNIX systems. See also "Starting and Stopping PSUB Manually in UNIX".
To stop an individual batch job:
Navigate to Admin, PSUB/Reports Jobs, and Batch Jobs).
Locate and select the row associated with the relevant Batch Job ID.
Click the Stop button.
On UNIX servers, a series of hanging jobs can cause the batch queue to become full. When the queue fills and is backlogged with hanging jobs, all jobs are eventually given an execution status of SUBMIT_FAILED or SUBMITTED. If this type of problem occurs frequently, it may be advantageous to fine-tune the existing queues or add more queues.
If, after trying all relevant solutions, you are unable to stop the jobs on a UNIX server, contact your Administrator so that she may use the solutions described here.
Only Administrator-level personnel should attempt to stop PSUB jobs using these solutions. Use these strategies, in the order they are listed, to stop PSUB jobs.
Use the stop_psub utility.
Identify and then stop the processes that are hanging:
To identify the process that is hanging, use either:
ps -ef|grep rxcpro
d
or
ps -ef|grep userid
To stop all of the hanging processes that you identified in Step 2a, use this command:
kill -9 pid
Log in as rxcprod and, at a command prompt, enter:
at -l [-q]
This command lists all of the jobs that are currently in all of the queues. Each job has a unique ID number.
If there are jobs pending in the queue, the following command, which uses the unique ID number to remove specific jobs from the queue, may be of use:
at -r
id
If you are able to stop all PSUB jobs, stop and then restart the PSUB process and submit one job. If it hangs, try to isolate whether one particular module is the cause or if any PSUB job hangs, regardless of module.
If you are unable to determine a module that is causing the problem and jobs are still hanging, the only recourse is to reboot the computer.
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.
Note:
As an alternative, to find out on which computer PSUB is set up, query either the:OCL_STATE local reference codelist, or
RXC.PSUB_PROCESS_LOG table
Use this information, in conjunction with the operating system-specific instructions, below, to determine if PSUB is running on a given computer.
Use this command to find out if PSUB is running on particular UNIX server:
% ps -ef | grep -i rxcpsdps
The process search command, ps, returns descriptions of the PSUB processs that are currently running. Each row that is returned represents one PSUB process running on the server. Each process has a unique product_instance and code_environment pair. The format of the response to the process search command listed above is:
rxcpsdps [verbose|nonverbose]product_instance code_environment
Use this procedure to find out if the PSUB service is running on a given Windows server.
Open the Control Panel.
Double-click the Services icon.
In the Services window, note the status of the PSUB service with the relevant database name. The status will be "Started" if the service is running.
Execution status as reported in the Submitted Batch Jobs form is shown below. You can take various actions depending on execution status.
Value | Meaning |
---|---|
ENTERED | The user has requested a job submission. |
SUBMITTED | The process submitted the job to the batch queue; it may be pending. |
SUBMIT_FAILED | The process attempted to submit the job to the batch queue but failed. |
STARTED | The job is executing on the batch queue. |
SUCCESS | The job completed with SUCCESS status. |
FAILURE | The job completed with FAILURE status. Look at the Failure Text in the Submitted Batch Jobs window for possible reasons. |
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.
If the Execution Status of your batch job remains at ENTERED, perhaps:
The PSUB process is not running on the server, or it is not receiving the request from the client.
The corresponding Oracle user's operating system account does not exist.
If the Execution Status of your batch job remains at SUBMITTED, perhaps:
The user is not connected through an OPS$USER Oracle account.
The job is pending in the batch queue, or the batch queue is stopped.
The PSUB process does not have Write permission for the user's RXC_LOG directory on the PSUB server.
A log file exists with the same number as the one for the submitted job. This is a rare situation. Delete the old log file and resubmit the job.
In the case of a PSUB job that stays in SUBMITTED status even though the PSUB process is up and running, if your .log file says:
ERROR:Error while connecting: ORA-01017: invalid username/password; logon denied Exiting...
Edit sqlnet.ora (in drive:\app\oracle\product\11.2.0.2.0\NETWORK\ADMIN):
UNIX: Comment out the following line (add # at the beginning of the line) and save.
# 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)
If the PSUB process is still running you can now resubmit the PSUB jobs. Otherwise, stop and then start the PSUB process.
If the Execution Status of your batch job is SUBMIT_FAILED, examine the Failure Text. If this action gives no possible cause, perhaps:
The ssh command cannot be executed by the RXCPROD user. Check that the host name in the /etc/hosts.equiv file is the official name of the host as specified in /etc/hosts.
The user's password is not correct.
The batch queue does not exist. Check the Long Value of the BATCH QUEUE NAME local reference codelist.
The batch queue is in a stopped state.
If the Execution Status of your batch job remains at STARTED, perhaps:
The job is executing and waiting for some resource.
The job is hung.
If the Execution Status is FAILED, examine the Failure Text. If this action gives no possible cause, perhaps:
The report or command exited with error status.
The report or executable file does not exist.
The print command exited with failure status, because, for example, the specified print queue does not exist.
Make sure the Long Value of the SERVER_OS entry in the OCL_STATE local reference codelist is correct for your operating system. You can enter one of the following values:
NT — Indicates the server is running one of the Windows operating systems currently supported by Oracle Clinical.
UNIX — Indicates the server is running the UNIX operating system.
Check that the SERVER_NAME in OCL_STATE is set to the database/PSUB server.
Note: On UNIX systems, the Long Value of the SERVER_NAME entry (also in OCL_STATE) must be in lowercase letters. |
Check that your RXC_LOG is correctly defined or modify the user's log directory via the menu path Admin, then Users, and Oracle Accounts.
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:
Execute the following query:
SQL> select parameter, value from V$NLS_PARAMETERS where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
Open opa_settings and search for the following string:
db_env_setting:database_name:NLS_LANG
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.
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.
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...
This error can occur if the PSUB user is not using the C Shell (csh). The default shell gets set up when you create the user account. For example, on Linux, the bash shell is set by default.
Verify that all user accounts that run PSUB jobs are configured to use the C Shell (csh).
If the error continues to occur after you verify that PSUB account uses C Shell, modify $RXC_PSUB/launchps.sh
as follows:
Log on to the UNIX computer on which the PSUB process is running, as owner of the file launchps.sh
. (The owner is usually OPAPPS.)
Run opa_setup
as appropriate for your shell environment.
Change to the $RXC_PSUB directory.
Edit launchps.sh
by adding the following line immediately before the pslaunch command:
sleep 2 pslaunch $4 $5 $6 $7 $3 $8
This command introduces a 2-second delay before the system calls pslaunch
. You may increase the delay if the error continues to occur.
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.
If PSUB does not start , for example, after installing or upgrading:
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)
Locate and ensure that these lines in init.ora are not commented out and the values are as specified:
If SAS and PSUB reside on the same computer:
remote_os_authent=FALSE
os_authent_prefix="OPS$"
If SAS and PSUB reside on different computers:
remote_os_authent=TRUE
os_authent_prefix="OPS$"
See the Oracle Clinical Installation Guide for more information.
Shutdown the database.
Start the database.
Start PSUB.
You can trace a session connected to the Oracle Clinical Database and generate a log file. The following example explains how to run a trace while in the Maintain DCM form.
Start a SQL*Plus session as SYS, or another user with the DBMS_SYSTEM role.
Find the session ID and serial number of the Oracle Clinical user working in the Maintain DCM form:
select sid, serial# FROM v$session where username = 'OPS$
userid
';
Assume that 8 and 12 are returned for sid and serial#, enable SQL trace for the user as follows:
exec dbms_system.set_sql_trace_in_session(8,12,TRUE)
Have user perform the operation that causes the error. After the error is returned disable SQL trace:
exec dbms_system.set_sql_trace_in_session(8,12,FALSE)
Find the trace file out in your USER_DUMP_DEST directory. For example,
select value from v$parameter where name = 'user_dump_dest';
where value is the path, something like, /ind/oraclelogs/maria/db/udump. The trace file is placed in this directory.