| Oracle® Clinical Administrator's Guide Release 4.6 Part Number A83791-06 |
|
|
View PDF |
| Oracle® Clinical Administrator's Guide Release 4.6 Part Number A83791-06 |
|
|
View PDF |
This section offers solutions to known Oracle Clinical issues in the following categories:
If you upgraded to Oracle Clinical 4.6 from either release 4.5.1, 4.5.2, or 4.5.3, 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 reference 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 certain error messages that users may encounter. It includes the following topics:
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.
Follow these steps:
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, follow these steps:
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:
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, refer to the "Check the PSUB Log Files" section. If your batch job is hanging because the batch queue is full, refer to the "If Batch Jobs Hang and the Batch Queue Is Full" section.
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 daemon 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. We recommend that you start PSUB in verbose mode because the process logs that are generated:
contribute to efficient troubleshooting, and
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. Refer to the "Managing the PSUB Process" section.
If you cannot check the Failure text or the .out and .log files because the batch queue is hung, refer to the "If Batch Jobs Hang and the Batch Queue Is Full" section.
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".
Follow these steps 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. Refer to the Stopping PSUB on a UNIX system question.
Identify and then stop the processes that are hanging:
To identify the process that is hanging, use either:
ps -ef|grep rxcprod
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. (Refer to the at man pages for additional information.)
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 daemon 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 daemons 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 daemon is up and running, if your .log file says:
ERROR:Error while connecting:
ORA-01017: invalid username/password; logon denied
Exiting...
Edit the file sqlnet.ora by commenting out the following line, and save it.
SQLNET.AUTHENTICATION_SERVICES = (NTS)
If the PSUB daemon is still running you are all set to resubmit the PSUB jobs. Otherwise, stop, and then start the PSUB daemon.
PSUB does not start after installing or upgrading
Locate the file, sqlnet.ora.
Locate and comment out the line (i.e., place '#' at the beginning of the line):
sqlnet.authentication_service=(NTS)
Start PSUB.
If PSUB fails to start:
Locate and uncomment the line in sqlnet.ora:
sqlnet.authentication_service=(NTS)
Locate and ensure that these lines in init.ora are not commented out:
remote_os_authent=true
os_authent_prefix="OPS$"
Note:
On UNIX, if SAS (and SQL*Plus and PSUB) reside on the same computer as the Oracle Clinical database, and RXC_SAS_CONNECT is not defined, then you can set remote_os_authent to false. On Windows, remote_os_authent must be set to false.Shutdown any databases on the Windows machine.
Start the databases.
Locate and comment out the line in sqlnet.ora.
sqlnet.authentication_service=(NTS)
If the Execution Status of your batch job is SUBMIT_FAILED, examine the Failure Text. If this action gives no possible cause, perhaps:
The command rsh (remsh on HP-UX) 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 entry SERVER_OS in the local reference codelist OCL_STATE is correct for your operating systemæthat is, set to Windows or UNIX. SERVER_NAME in OCL_STATE should be set to the database/PSUB server.
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 one 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 generally does not occur each time you run a job.
If above problem happens frequently in your environment the workaround is to modify $RXC_PSUB/launchps.sh as follows:
Logon to the UNIX computer on which the PSUB daemon is running, as owner of the file launchps.sh (this 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 just before the 'pslaunch' command:
sleep 2 pslaunch $4 $5 $6 $7 $3 $8
This introduces a 2-second delay before the system calls pslaunch. You may increase the delay if you still have a problem.
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.
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 Clinical user working in the Maintain DCM form:
SQL> 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:
SQL> 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:
SQL> exec dbms_system.set_sql_trace_in_session(8,12,FALSE)
Find the trace file out in your USER_DUMP_DEST directory. For example,
SQL> 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.
|
 Copyright © 1996, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|