The Parameterized Submission process (PSUB) schedules and runs jobs, reports, and batch processing for Oracle Clinical. In order to support using Oracle Real Application Clusters (RAC), the implementation of PSUB changed in Oracle Clinical 5.0. See the 5.0.1 Installation Guide for more information.
You must start one PSUB service for each Oracle Clinical database on the same server as the database.
This chapter includes:
The following steps are required to set up the parameterized submission process:
Section 6.1.1, "Create Directories and Enter Values in OCL_STATE Local Reference Codelist"
Section 6.1.3, "Enable opapps to Use the at Command (UNIX Only)"
Create several directories on the PSUB server.
Enter paths of the new directories in the OCL_STATE local reference codelist, with a few related values.
See the "Setting Up Batch Job File Viewing" section of the "Setting Up File and Image Viewing" chapter of the Oracle Clinical Administrator's Guide for more information.
Note:
See Chapter 7, "Set Up SAS" for information about new OCL_STATE settings related to SAS configuration.Beginning in Release 5.0, PSUB users no longer need:
their own OS account
a user name beginning with OPS$
their own directory for PSUB outputs
To add new users:
Use the ocl_add_user.sql script, indicating which new users need to run PSUB jobs.
See the Oracle Clinical Administrator's Guide for more information.
If you are upgrading from a pre-5.0 version of Oracle Clinical, to give users who need to run PSUB jobs access to the opapps account:
Use the migration script oclupg50migrateusers.sql.
See the Oracle Clinical Administrator's Guide for more information.
You can give a user the role RXC_VWJOBS (new in 5.0) to allow him or her to:
View all users' jobs
View the output from those jobs.
Stop any job.
The following scripts can grant this role to a user:
ocl_add_user—Use this script for new users.
ocl_grant_revoke_rxc_vwjobs.sql—Use this script for existing users. (This script can also be used to revoke the role.)
See the Oracle Clinical Administrator's Guide for more information.
To use the at command to schedule jobs on behalf of another user, the owner's user account must be listed in the at.allow file. The owner is opapps.
To edit the at.allow file:
Change to the appropriate directory location depending on your operating system and open the at.allow file:
Oracle Linux x86-64: /etc/at.allow
Oracle Solaris SPARC: /usr/lib/cron/at.allow
HP Itanium: /usr/lib/cron/at.allow
Add the following line to the at.allow file:
opapps
If you are running PSUB on a Windows server that is a primary domain controller running Active Directory:
Add the PSUB account (typically opapps) to the domain.
Reassign the following domain user privileges to the PSUB account, through the domain controller security policy and the domain security policy:
Load and unload device drivers
Act as part of operating system
Increase Quotas
Log on as a Service
Replace a process-level token
The Installer automatically installs the PSUB service on the database server. You must start it using the instructions below.
Instructions for stopping PSUB manually are included in the Oracle Clinical Administrator's Guide.
On UNIX systems, you can automate the process of starting and stopping PSUB.
The following example shell scripts for Sun Solaris show how to make the process start automatically at system startup:
# File: /etc/init.d/dbora ORA_HOME=/u01/app/oracle/product/12.1.0.2.0 ORA_OWNER=oracle if [ ! -f $ORA_HOME/bin/dbstart -o ! -d $ORA_HOME ] then echo 'Oracle startup: cannot start' exit fi case "$1" in 'start') echo 'Starting Oracle...' su - $ORA_OWNER -c $ORA_HOME/bin/dbstart su - $ORA_OWNER -c "lsnrctl start" su - opapps -c start_psub ;; 'stop') echo 'Stopping Oracle...' su - opapps -c stop_psub su - $ORA_OWNER -c $ORA_HOME/bin/dbshut ;; esac # File: start_psub # Start database 1 start_psub db_name1 code_environment wallet_alias # Start database 2 start_psub db_name2 code_environment wallet_alias
For example:
# Start database 1 start_psub venus 52 venus # Start database 2 start_psub pluto 52 pluto
where the database names are venus and pluto and your code environment is 52.
You can automate the shutdown of the PSUB service on UNIX so that it does not require the entry of a password.
The following example shell scripts for Sun Solaris show how.
# File: /etc/init.d/dbora ORA_HOME=/u01/app/oracle/product/12.1.0.2.0 ORA_OWNER=oracle if [ ! -f $ORA_HOME/bin/dbstart -o ! -d $ORA_HOME ] then echo 'Oracle startup: cannot start' exit fi case "$1" in 'start') echo 'Starting Oracle...' su - $ORA_OWNER -c $ORA_HOME/bin/dbstart su - $ORA_OWNER -c "lsnrctl start" su - opapps -c start_psub ;; 'stop') echo 'Stopping Oracle...' su - opapps -c stop_psub su - $ORA_OWNER -c $ORA_HOME/bin/dbshut ;; esac # File: stop_psub # Stop database 1 set TNS_ADMIN= opapps_home_dir export TNS_ADMIN stop_psub venus 52 wallet_alias # Stop database 2 stop_psub pluto 52 wallet_alias
where the database names are venus and pluto and your code environment is 52.
This section includes:
The batch file is required in large databases that take so much time to come up during a server reboot so that the system tries to start PSUB before the database is fully up. In this case the PSUB service does not start and an error like the following appears in the PSUB log file (found in the drive:\opapps\oc\52\log directory):
ERROR:Daemon error while connecting:/@devoc ORA-1033: ORACLE initialization or shutdown in progress
You can specify that the PSUB service starts automatically when the Windows PSUB server re-boots. The service parameters are read from a system environment variable whose name concatenates PSUBSERVICE with the database name.
To create a system environment variable in Windows:
Navigate from Start to the Control Panel, then click on the System and Security link. The System and Security window opens.
Click the System link. The System window opens.
Click the Advanced System Settings link at the top left corner. The System Properties window opens.
Select the Advanced tab, then click Environment Variables.
Click New under System Variables in the lower portion of the window to define the variable.
For the variable name, enter the string PSUBSERVICE concatenated with the database name; for example, for the database sun6x2:
PSUBSERVICESUN6X2
For the variable value, use the format: database_id code_environment verbose RXC_ROOT wallet_alias; for example:
pluto 52 verbose t:\\opapps\\oc\\52 wallet_alias
where pluto is the database ID and RXC_ROOT is t:\opapps\oc\52.
Note that if you need a backslash (\) in the text box, you must double it (\\).
Create a batch file called 'psub_start1.bat' in the %RXC_ROOT%/log directory (for example, D:\opapps\oc\52\log) with the following contents:
cmd /c echo Current Date/time= %DATE% %TIME% > psub_start1.log
cmd /c echo Starting Time Delay > psub_start1.log
ping localhost -n 180 > nul
cmd /c net start "PSUB Service database_id" > psub_start1.log
Note:
"PSUB Service database_id" is the PSUB service name that appears in the Services window (under Administrative Tools from the Control Panel).
You can repeat the following command for different databases if needed:
cmd /c net start "PSUB Service database_id" > psub_start1.log
The 'ping localhost' command introduces a time delay to ensure that the database is up before starting PSUB. You can increase this value—set to 180 seconds (3 minutes) in the example above—if required.
To schedule batch file execution:
Navigate to Start, then Administrative Tools, then Services. Scroll down and make sure that the Task Scheduler service is started.
Navigate to Start, then Administrative Tools, then Task Scheduler. The Task Scheduler window opens.
Click the Create Basic Task link on the right. The Create Basic Task wizard appears.
Enter a Name and Description and click Next.
In the Task Trigger window, select When the computer starts and click Next. The Action window appears.
In the Action window select Start a program and click Next. The Start a Program window appears.
In the Start a Program window, click Browse. The Browse window appears.
In the Browse window, browse to the directory where psub_start1.bat is saved and then select psub_start1.bat. Leave the other boxes empty and click Next.
Select Open the Properties dialog for this task when I click Finish and click Finish. The Properties dialog box opens.
In the Properties dialog box, General tab, Security options section, click Change User or Group and select Administrator.
To test, shut down the service if necessary (instructions are in the Oracle Clinical Administrator's Guide) and double-click on file psub_start1.bat to test that it starts the PSUB service. Verify that the log file psub_start1.log is created in the same directory unless a different path was specified.
In the Services window under Administrative Tools in the Control Panel, right-click the PSUB Service, click Properties, and change its Startup Type to Manual.
To test, restart the computer and check the Services window to see if the PSUB service has started. If it has, submit a PSUB job such as Batch Validation and check if it runs.
For convenience add a shortcut for psub_start1.bat on the desktop to manually start PSUB by double-clicking the icon.
To start the PSUB service on UNIX:
Log in as the opapps user. By default, the opapps uses the C shell.
Set up the environment:
opa_setup <database_name> <code_environment>
For example, where prod is the database name:
opa_setup prod 52
Go to the PSUB location:
cd $RXC_PSUB
Start the PSUB service:
start_psub database_name code_environment wallet_alias
For PSUB, wallet_alias is the same as the database name. For example:
start_psub prod 52 prod
where prod is the connect string for the database instance to which the PSUB service connects;
where 52 is the name of the code environment;
where wallet_alias is the name of the Wallet specified during installation. By default it is the same as the database name.
If there are any errors, check the following log files in the $RXC_CENTRAL_LOG directory:
rxcpsd_instance_environment_1.log
rxcpsd_instance_environment_2.log
To start the PSUB service on Windows:
Log in as opapps. (You set up the PSUB service to start as the opapps user, but in Windows you can start the service when logged on as another user.)
Set the PSUB service parameters:
In the Start menu, navigate to Administrative Tools, then Services.
From the list of services in the Services dialog box, double-click the name of the database for this service. It is in this form:
PSUB Service database
Enter values for the Log On parameters:
database code_environment [verbose | noverbose] value-of-RXC_ROOT wallet_alias
For example: prod 52 verbose c:\\opapps\\oc\\52 <wallet_alias>
where prod is the connect string for the database instance to which the PSUB service connects;
where 52 is the name of the code environment;
where wallet_alias is the name of the Wallet specified during installation.
Note:
If your entry requires a backslash (\), you must enter two (\\). Alternatively, you can enter the path using single forward slashes, for example, c:/OPA_HOME/oc/52.Click Start.
Exit from the Services dialog box.
Check the PSUB service log file in <RXC_ROOT>\log for any warning or error messages.
To test your PSUB installation:
Open Oracle Clinical.
Submit a 3GL job such as Batch Validation or a PL/SQL job such as Study Unfreeze.
Verify that Oracle Clinical creates the log and output files by clicking on the View log and View output buttons.
If you encounter problems or errors, review the messages in the PSUB log files created in the following directory:
Customize the PSUB service as follows:
Automatic Startup — By default, the PSUB service does not start automatically when you restart a server computer. However, you can configure the PSUB service to start automatically.
Job Numbering — You can change Oracle Clinical's default job numbering algorithm.
For more information about managing and customizing the PSUB servicePSUB service, see the Oracle Clinical Administrator's Guide.
PSUB considerations in a RAC installation:
In a RAC installation, Oracle recommends installing the Oracle Clinical database server, which includes the PSUB Server, on at least two RAC nodes. If PSUB goes down on one node, or if the node itself goes down, you can start PSUB on the other node with little interruption of service.
You must start one and only one PSUB service for each database. Oracle Clinical cannot detect if more than one PSUB service is running, so results are unpredictable in that case.
Create the same directory structure for PSUB files on each computer where PSUB may run. That way you do not have to change the OCL_STATE reference codelist value for the five PSUB directories.
Note:
However, you do have to change the OCL_STATE SERVER_NAME value when you start PSUB on a different server.If you use NFS to share the files, users will still be able to access files for jobs performed on the other node unless the node itself fails.
PSUB supports the following scenarios in a RAC environment:
If the database instance on the PSUB server goes down, but its server is still active, PSUB will transparently continue to process jobs for the other database instances in the RAC environment.
If one PSUB service or server fails, PSUB can be started for that database on another server.
If another node is added or removed, PSUB continues to work.
If the PSUB server goes down or is removed, an administrator can manually start the PSUB service on another server with minimum downtime.
In the event of a PSUB failure, current PSUB jobs are affected differently based on their state at the time of PSUB failure:
SCHEDULED or ENTERED jobs are picked up by the newly started PSUB service.
SUBMITTED jobs will likely need to be re-submitted.
STARTED jobs continue to run, regardless of PSUB status.
Note:
There is no notification if a PSUB service fails. In this case, a user's job does not complete and he or she must contact the administrator. The administrator checks the log file, starts PSUB on another node, and changes the SERVER_NAME value in the OCL_STATE reference codelist.For more information, see Section 1.6.1, "Supported Configurations with Oracle Real Application Clusters (RAC)".
If you cloned a database, manually restart the AQ process:
Stop PSUB. See the Oracle Clinical Administrator's Guide for instructions.
Log in as sysdba.
Enter:
EXEC DBMS_AQADM.START_QUEUE('RXC.PSUB_REPLY_QUEUE',TRUE,TRUE);
EXEC DBMS_AQADM.START_QUEUE('RXC.PSUB_SEND_QUEUE',TRUE,TRUE);
Start PSUB.