A Appendix
Oracle Database Client and ODAC Installation
SPMS Application Clients and SPMS Web Servers are database clients. They need to connect to the SPMS Database to operate. As such, the installation of Oracle Full Client and ODAC setup is required.
Where to download Oracle Database Client
-
For instructions, download a copy of the installation manual from the official Oracle Help Center website at https://docs.oracle.com/en/database/ and refer to the manual for steps to install.
-
Similarly, to install the Oracle Database Setup file, refer to the website at https://www.oracle.com/database/technologies/instant-client/downloads.html and download the appropriate Microsoft Windows installation file.
-
Alternatively, you could obtain a specific setup file from Oracle Support/DBA. For example, these are some of the installers made available:
-
OracleClientSetup_12201_FULL.exe and ODTwithODAC122011, or
-
OracleClientSetup_12201_FULL_ODAC_v1.exe; or
-
Oracle Client 19c
-
How to register Oracle Database Client DLLs to the Local Environment
The Oracle Database Setup file downloaded from the Oracle website will not automatically register the above DLLs to the local client. Oracle provides a tool (OraProvCfg.exe) to help you to register the DLLs. See instructions at https://docs.oracle.com/cd/E71422_01/doc.212/E72234/index.htm?toc.htm?207666.htm on how to register the DLLs. The specific Setup file from Oracle Support/DBA will normally register DLLs automatically.
-
Oracle.DataAccess.dll
- Folder:
ORACLE_HOME\odp.net\bin\2.x
- Folder:
-
All DLLs under PublisherPolicy folder: Policy.X.XXX.Oracle.DataAccess.dll
- Folder:
ORACLE_HOME\odp.net\PublisherPolicy\2.x
- Folder:
-
Oracle.ManagedDataAccess.dll
- Folder:
ORACLE_HOME\odp.net\managed\common
- Folder:
-
All DLLs under PublisherPolicy folder: Policy.X.XXX.Oracle.ManagedDataAccess.dll
- Folder:
ORACLE_HOME\odp.net\managed\PublisherPolicy\4
- Folder:
Note:
-
Once the Oracle Client installation is successful, launch the Oracle Net Manager to establish the connection between the Database Client and Database Server.
-
When installing the ODAC, ensure the following components for Oracle 19c are installed:
-
Oracle Data Provider for .NET
-
Oracle Providers for ASP.NET
-
Oracle Services For Microsoft Transaction Server
-
Definition of SPMS Seed Database
An SPMS Seed Database is a template database imported from a suitable Database DMP file. As in SPMS 7.30 and prior, a new database setup has always been a process of importing from a suitable Database DMP by DBA and then the new database will undergo a purge process (FCSPMS_Clean_UP.SQL) to ensure the database is empty. Similarly, for SPMS 8.0 / 20.X and above, a new database template will follow the same process in SPMS 7.30. A suitable Database DMP will be chosen and then the data will be purged to ensure the database is empty.
General Steps to Troubleshoot an Issue
Common Errors in SPMS Database Installation
Shown below are some of the common errors encountered during the Database Upgrade process using the OHC Tools.
-
This could be due to the fact that the FIDELIOBK does not exist or the account is locked or the password is not a default password. Unlock the account or update the password manually.
-
This is because the database is yet to upgrade to version 20.X / 23.1. Run DB Installer version 20.X / 23.1.
-
Ensure the latest Updater.exe, UpdaterWatchdog.exe is in the Program file folder and the task scheduler is up and running. Restart the PC to allow Updater to launch and perform the update.
-
Ensure the .NET Framework 3.5 is enabled in Microsoft Windows Features before installing Oracle Client. If an error occurs in the Web Server, reinstall the Oracle Client after performing the Web Server Setup.
-
Place the 'Oracle.DataAccess.dll' into
c:\inetpub\OHCTransactionsService\bin
folder.
-
Ensure the DB SID <> ‘Fidelio’ and OHCSettings.par exist in Public Folder or DB SID <> ‘Last Server’ in OHCSettings.par
-
Ensure the IIS Server can connect to the database. Use the SQLPLUS or RESTART IIS Server to test the connection.
-
Ensure the DB SID defined in Web.config / Tnsnames.ora / OHCSettings.par are the same as IIS Server and Client PC.
-
The issue is caused by a connection time out during the encryption process and OHCSecurity.par were not properly created in the IIS Server. Manually remove OHCSecurity.par from the IIS Server and run Change Encryption Key to recreate the file.
-
Ensure the IIS Server Firewall Port 443 is turned off.
-
The database password is case sensitive. Ensure the database password is correct. You can change the case sensitivity using an SQL statement if the error persists.
-
The issue is caused by SYS_REPORTDIR being empty. The workaround is to place a value to this parameter or obtain the latest Fidelio*.dll.
-
The issue might be caused if the schema created uses the wrong password when backing up and restoring the database. Manually drop the FIDELIOBK from the schema.
-
This warning indicates the minimum database version (7.30.878 or 8.0.12) was not run prior to upgrading to 20.X. See Prerequisites in Setting Up SPMS Database Server.
Common Errors in SPMS .NET Web Server Installation
Web Server Is Corrupted
If the SPMS web services server is corrupted, the OHCSecurity.par is no longer valid.
-
Ensure the OHCSecurity.par exist on the client PC.
-
Change the securelogin.txt to point to the new IIS Server.
-
Run OHC Tools, Change Password function to generate a new key.
-
Run OHC Tools, Change Password and perform a password change to copy all of the entry with value in OHCSecurity.par to new IIS Server, then log in to SPMS application on a different client PC to download OHCSecurity.par from IIS Server.
-
Log in to the IIS Server with a user from an administrator group instead of the standard user group, and restart the installation.
-
A secure certificate is not present in your environment. You are required to purchase the certificate from a certificate authority and apply the said certificate. For more information on how to apply a security certificate, see https://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/a2f35fcd-d3b6-4f39-ba93-041a86f7e17f.mspx?mfr=true.
-
Ensure the Database is up and running.
-
This error is caused by OHCSecurity.par being manually deleted and the application already connects to DB, and is comparing the old password with IIS Server. Restart the IIS Server to enable the application to re-establish its connection.
-
Launch the MS-DOS Command with “Run as Administrator” and use RegAsm in
C:\Windows\Microsoft.NET\Framework\v.4.0.30319
to register the Wrapper.dll file.
-
The system prompts this message when the user attempts to load the .asmx file through Internet Explorer. Some of the DLL should not exist in the BIN folder, for example. SingPlusNet.dll. Remove them and retry.
-
This occurs when App_Code.dll, OHCSPMS*.dll version is wrong. Ensure these DLL versions are the same.
-
This occurs when logging in to JsonGet. Ensure both OHCSPMS*.dll versions are the same as the Database version.
-
This occurs when performing a transaction through Oracle WebServices. Ensure the Oracle Services for Microsoft Transaction Server (MTS) is installed on the Web Server.
-
This occurs when running Infogenis Point-of-Sale (IGPOS). Install ODAC and ensure the MTS is enabled and running properly as well as the distributed transaction coordinator service.
-
The error occurs when running IGPOS. Ensure the OHCSPMS*.dll version in XAPP table is as the copy in the BIN folder.
-
This error occurs when performing a web service transaction like Check-In or Book Excursion. In the Component Services, Distributed Transaction, Load DTC Properties, Security tab, and select both the ‘Allow Inbound and Allow Outbound checkbox.
Figure A-1 Windows Component Services
-
Ensure the IIS, WebService Extensions are set to Allowed in Web Service Extensions.
-
This error occurs when testing the web service through Internet Explorer and it returns a blank page.
-
Navigate to the IIS Manager and select OHCWebServices.
-
In the Default Document, manually add the OHCWebServices.asmx file.
Figure A-2 IIS Manager - Default Document
-
-
This error occurs during OHCWebServices Setup and requires Internet Information System (IIS) 6.0 or higher in Control Panel, Windows Features to be turned on.
Figure A-3 WebServices Error Message
![This figure shows the error message returned by WebServices This figure shows the error message returned by WebServices](img/webservices_error_msg.png)
-
This error occurs when browsing the web page. Comment of the related thread is mentioned in web.config.
-
This error occurs when browsing the web page. Ensure the .NET Framework version is .NET Framework v4.0.30319 in the Application Pool, Default Pool setting.
Common Errors in SPMS Desktop Application Client Installation
-
Run OHC Tools, Change Password and perform password change to copy all entries with a value in OHCSecurity.par to the new IIS Server, then log in to the SPMS application on a different client PC to download the OHCSecurity.par from the IIS Server.
-
The issue may be due to the PC connecting to different DB, and that DB password is the same as the previously connected DB, resulting in a mismatched encryption key in OHCSecurity.par. Delete the OHCSecurity.par and re-download the file.
-
The issue may be caused by SPMS 8.0 application running on a database version below 8.0.x, OHCSettings.par is not found in the public folder (where SID <> Fidelio) or securelogin.txt file does not exist or invalid.
- Ensure the above files exist and are in the correct location, and the database is updated, and the <endpoint address> points to the correct web server if the above message prompts in OHCDemoApp.
-
Set up the new IIS Server and connect to the new IIS Server using an existing client PC. Ensure the OHCSecurity.par exists on the client PC. Change the securelogin.txt to point to the new IIS Server, then run OHC Tools, and use the Change Password function to generate a new key.
-
Ensure the Interface program is uploaded under the Interface Files group in XAPP (XAPP_SYSTEMS_FILES =3).
-
Run Launch Panel to auto-save the Custom.dic file into the Public Documents folder.
-
This issue is due to the DB Installer having an issue with the conversion of Point-of-Sale (POS) table from CHAR to VARCHAR2. Ensure you do not End the task of the DB Installer during an upgrade process.
-
The issue is caused by OHCSecurity.par, which does not exist or does not have the security access rights to download the file. Ensure the Wrapper.dll is the latest version.
-
Ensure the Wrapper.dll is the correct version and is properly registered.
-
Ensure the Launch Panel is in XAPP table with XAPP_ID=2.
-
This may be due to an issue with ODAC installation. Run .NET program to verify
Re-register the wrapper.dll using MS-DOS Command C:\Windows\Microsoft.NET\Framework\v4.0.30319>regasm "C:\Program Files (x86)\Oracle Hospitality Cruise\wrapper.dll”.
-
The system creates a scheduled task in Task Scheduler with the Oracle Hospitality Cruise SPMS Updater. Verify that the Oracle Hospitality Cruise SPMS Updater is running in the Task Scheduler by navigating to the Control Panel, Administrative Tool, Task Scheduler.
- If the Task is not created, manually create the task by running the createtask.bat followed by runtask.bat from the Oracle Hospitality Cruise program files folder.
-
This may be due to the IIS not being available. Reset the IIS or browse the IIS Server from IE to check the availability
- Ensure Port 443 is added in the Window Firewall. Try to define IP or Server Name in SecureLogin.txt.
-
This is caused by Oracle Wallet. Ensure the Net Manager is connected to the DB successfully. Ensure the wallet folder has granted IIS_USER.
-
This is due to OHCSecurity.par is either missing or invalid.
-
Ensure the OHCSecurity.par is correct in both IIS and the local client PC
-
Ensure checkbox "Configure ODP.NET and/or Oracle Providers for ASP.net at machine-wide level" is selected while installing ODAC.
-
Run the SQL statement to alter the case sensitivity.
-
This is due to the Crystal Report Template being ticked on the ‘No Printer’ checkbox and the Zebra printer is not configured as Windows default printer.
-
To resolve this, there are 2 alternate ways:
-
Untick the ‘No Printer’ and choose the printer option as Zebra Printer from the printer list and set the Windows default printer as Zebra printer.
-
Remain the ‘No Printer’ checkbox tick, but Zebra Printer has to be the Windows default printer.
-
Common Errors in SPMS Mustering Web Application Installation
-
If the device browser storage threshold limit exceeded 20 Gigabytes, a warning banner will be showing on the SPMS Mustering screen. In order to prevent the storage usage reaches the maximum and impact the apps operation, you need to free up space for the device. When storage usage reaches the maximum, mustering application will not able to functional correctly and affects the offline usage. Here are the ways that you can apply to free up the device’s space:
-
Uninstall unused PWA apps.
-
Press and hold on the icon of unused Apps. One second later, a popup menu will be shown. Choose “Uninstall” or “Remove” to remove the unused apps.
-
-
Clear the unwanted sites cookies and data. There are vary ways to do this, it depends on the device’s operation system and browser.
-
After freeing up the space and storage usage goes below 20 Gigabytes, then the browser storage limit threshold warning banner will disappear.
-
-
Common Errors in SPMS REST API/Web Application
-
Navigate to ‘C:\Program Files (x86)\Oracle Hospitality Cruise\PropertyManagementAPI\reception_node1_xxxx\logs\ reception_node1_xxxx_service.out.log’. Open ‘reception_node1_xxxx_service.out.log’ and log displays ‘Error Message = ORA-00942: table or view does not exist’.
-
Run Database Installer to create table or views needed by the application.
-
Navigate to ‘C:\Program Files (x86)\Oracle Hospitality Cruise\PropertyManagementAPI\reception_node1_xxxx\logs\ reception_node1_xxxx_service.out.log’. Open ‘reception_node1_xxxx_service.out.log’ and log displays ‘ Error Message = BadCredentialsException:Invalid Client Id.
-
Client Id is not generated. Restart Oracle Hospitality Cruise SPMS Platform API to generate new Client Id.
Setting Up Load Balancer for SPMS OPI WebServices
SPMS OPI WebServices requires the load balancer to be configured with session persistence for it to work correctly.
Load Balancer Session Persistence or Session Stickiness
Session persistence or session stickiness is a method to direct all requests originating from a single logical client to a single backend web server.
Figure A-4 Without Session Persistence
![This figure shows Without Session Persistence This figure shows Without Session Persistence](img/setting_up_load_balancer_without_session_persistence.png)
Figure A-5 With Session Persistence
![This figure shows With Session Persistence This figure shows With Session Persistence](img/setting_up_load_balancer_with_session_persistence.png)
Important:
As an example, we are using NGINX Plus installed on Oracle Linux as the load balancer that is configured with session persistence. You should select the right load balancer that meets your organization’s security policy.-
Install the Load Balancer
Install the load balancer on the target machine. As an example, we install NGINX Plus on an Oracle Linux machine to act as a load balancer.
-
Ensure Firewall Rules Allow HTTP/HTTPS Connection
Ensure that the port 80 (HTTP) and port 443 (HTTPS) are enabled and not blocked by firewall.
-
Configure the Load Balancing Method
Generally, most load balancer supports load balancing mechanisms such as round-robin, least-connected and ip-hash. For SPMS OPI WebServices, we should use ip-hash load balancing mechanism.
In an ip-hash load balancing mechanism, a hash-function is used to determine which server should be selected for the next request (based on the client’s IP address).
As an NGINX Plus load balancer example, we set:
upstream opi { ip_hash; server <IP_ADDRESS>:<PORT>; server <IP_ADDRESS>:<PORT>; }
-
Configure the Session Persistence
Session persistence means that load balancer identifies user sessions and routes all requests in a given session to the same upstream server. For SPMS OPI WebServices, we should add a session cookie to the first response from the upstream group and identifies the server that sent the response. The client’s next request contains the cookie value and the load balancer routes the request to the upstream server that responded to the first request.
upstream opi { server <IP_ADDRESS>:<PORT>; server <IP_ADDRESS:<PORT>; sticky cookie sry_id expires=1h domain= oracle.com path=/; }
Then we should configure the load balancer to first find session identifiers by inspecting requests and responses so that it learns which upstream server corresponds to which session identifier.
As an NGINX Plus load balancer example, we set the “sticky learn” directive:
upstream opi {
server <IP_ADDRESS>:<PORT>;
server <IP_ADDRESS>:<PORT>;
sticky learn create=$upstream_http_LogLinkID
lookup=$http_LogLinkID
zone=client_sessions:1m;
}