Appendix E. RIL Oracle Database Features
RIL uses an embedded Oracle 12c database provided under a special licensing agreement. Both RIL and Oracle have licensing models divided into "Standard" and "Enterprise"; however, there is no direct connection between the two models. An RIL Enterprise license is more a reflection of the size of the retailer's business and data replication requirements than it is a specific feature set. For larger customers, we recommend installing Enterprise Edition at the HQ. Stores should always be on RIL Standard Edition.
This topic has information about:
- Advanced Oracle database features available in RIL
- Advanced Oracle features not available in RIL and RIL status of those features
Oracle DB Features Available in RIL
This table has information about key Oracle database features that are available in RIL Standard Edition (SE) and Enterprise Edition (EE). Most of these features are available in both Standard and Enterprise Editions via the RIL Technician's Toolkit.
|Table-level partitioning||N||Y|| In Enterprise Edition, partitioning is applied to most large tables and indexes (transactions, inventory, customers, inventory quantities and prices, and delta tables). They are partitioned by subsidiary and in some cases a greater level detail For example, between active and inactive records. DOC_SYNC is partitioned to the individual station and delta table level for a year.
The benefits of table-level partitioning are seen mostly in installations with many ECM stations to process data more quickly; therefore the feature is enabled by default on EE.
Note: This feature applies to the RIL rproods database, not the Prism rpsods database
|Result Cache||N||Y||Query result cache and PL/SQL function result cache. This feature caches the results of queries and puts it into a slice of the shared pool. For queries that are executed often and for which the data rarely changes, this feature can increase performance significantly.
Note: This feature applies to the RIL rproods database, not the Prism rpsods database
|16+ CPU Cores||N||Y||Standard Edition is licensed for no more than two processor sockets and no more than 16 CPU cores. There is no limit for EE.|
|Materialized Views||Y||Y|| The basic difference between Views and Materialized Views is that Views are not stored physically on the disk. A Materialized View is a physical snapshot of the base table. Using materialized views enables more efficient access at the cost of some data being potentially out-of-date. The materialized views used by RIL are automatically updated with the latest data from the original base tables at 6 a.m. everyday. A technician can manually update the materialized views in Tech Toolkit.
The following materialized views are available:
UDF_MV: User-defined fields.
RETURN_INVC_MV: Return receipt.
INVENTORY_LT_MV: Inventory item lead time.
VENDOR_LT_MV: Vendor lead time.
COST_LEDGER_MV: Ledger tracking changes to inventory cost
|Diagnosis||Y||Y||Diagnostic reports contain a detailed analysis of system hardware, software, and configuration settings as well as information about the environment and variables.
Path: Tech Toolkit > Monitoring and Tuning > Diagnosis
|Reindexing||Y||Y||Data is usually inserted into a table in an unsorted fashion. As data is inserted, the table's index is extended to accommodate the new data. This is done by adding leaf nodes to the index. If data is deleted from the table then the corresponding leaf nodes are cleared from the index. Over time, the leaf depth grows as the index inserts leaf nodes in-between other leaf nodes and performance may degrade. To restore performance, the index must be rebuilt. Rebuilding the index flattens the index out so that the leaf depth is reduced to the smallest possible depth.
Path: Tech Toolkit > Monitoring and Tuning > Reindexing
|Tuning||Y||Y||Tuning optimizes the database by streamlining the execution of SQL statements. Oracle assigns cost values to each table and index object in the database. The cost represents the system resources required to retrieve the information and is based on the amount of data contained in the table and the type and number of indexes associated with it. As the data in the database is modified, these cost values can become obsolete and, in some cases, performance can suffer. When you tune the database, Oracle analyzes and assigns new cost values to each table and index object.
Path: Tech Toolkit > Monitoring and Tuning > Tuning
|Tracing||Y||Y||Oracle typically runs a standard version of tracing during normal operations to collect statistics about performance and resource utilization (SQL Parse, Execute, Fetch Statistics, etc.). This however is not verbose logging and typically only captures serious events and changes to the system. When you select Enable Tracing in Technician's Toolkit, Oracle tracing is run in verbose mode that captures every piece of activity in the system. This will considerably decrease the performance of the database and is not recommended for databases running in production mode. Instead, turn on the Enable Tracing option for brief periods of time to capture a snapshot of the recent activity.
Path: Tech Toolkit > Monitoring and Tuning > Database Tracing
|Logs||Y||Y||The View Logs feature provides a UI for viewing, searching and purging of Oracle logs.
Path: Tech Toolkit > Monitoring and Tuning > View Logs
|Archive Logs||Y||Y||By default, automatic archiving is enabled with archive logs saved to the C:\Oracle \oradata\RProODS\Archive folder. You can manually archive by clicking the Archive Now button.
Path: Tech Toolkit > Managing Storage Solutions > Archive Logs
|Audit||Y||Y||Auditing enables you to select the data types that you want to audit, and whether to store the audit log in a database table, in a log file, or both. The actions in the database that can be audited are organized into useful categories called classes and subclasses. For example, the Document class contains subclasses for each of the Retail Pro document types (receipts, memos, vouchers, transfer orders, etc.). You can select the specific classes and subclasses of data that you want to audit.
Path: Tech Toolkit > Monitoring and Tuning > Audit
|Oracle Audit Cleanup||Y||Y||Audit trails need to be kept to a manageable size (and old records purged) if they are to be used effectively in forensic analysis. The Tech Toolkit has a UI for purging audit logs with the option of archiving the logs before purging. (Monitoring and Tuning > Audit > Purge). This option will truncate all entries in the SYS.AUD$ table to free SYSAUX tablespace.
Path: Tech Toolkit > Monitoring and Tuning > Oracle Audit Cleanup
|Control Files||Y||Y||A control file is a small binary file used to ensure the database is kept in a consistent state. This file is read when the database is started. If the file is consistent, then the database is opened. If inconsistencies are found, the database will not open, and you must either rebuild the control file or perform a database recovery. In Tech Toolkit, use the Rebuild button to rebuild the control file and the Backup button to back up the control file.
Path: Tech Toolkit > Managing Storage Structures > Control File
|Data Files||Y||Y||Data is written to data files in an Oracle proprietary format. The size of Oracle data files is limited to 32 GB. If any data files are approaching the 32 GB size, consider adding data files.
You can resize or add data files in Technician's Toolkit. When adding data files, the default location is the \oracle\oradata\rproods\ directory. The data file name is read-only. After a data file is added, Oracle will know to use it right away.
Path: Tech Toolkit > Managing Storage Structures > Data Files
|Password File Rebuild||Y||Y||The Password file is the PWDRproODS.ora file, located in the \oracle\ODS901\database directory. This file is used to authenticate Oracle administrators for tasks such as creating a dataset or staring/shutting down the database. SYSDBA and SYSOPER are key privileges granted through this file.
If the Password file is damaged or deleted, then rebuild it in Tech Toolkit.
Path: Tech Toolkit > Managing Storage Structures > Password File.
|Application History||Y||Y||Knowing which version of various system components is being used or was used in the past helps with troubleshooting. You can view a list of recent updates to the system components, including version, install date and update date.
Path: Tech Toolkit > Sessions > Application Info
|Data Backup and Recovery and Data File Recovery||Y||Y||RIL uses Oracle's Backup and Recovery features to perform incremental (recommended) or full backups.
Path: Tech Toolkit > Backup and Restore
Oracle DB Features not Available in RIL
This table lists some popular Oracle features (primarily Enterprise license-level features) and a brief statement about its relevance to RIL. In most cases, these advanced features are not available in RIL, or are not relevant to RIL.
|Multi-tenant||This is a new feature in Oracle 12c that uses container technology to host multiple Oracle databases on a single machine.
RIL Status: This feature is not available for RIL.
|Data Guard||Oracle Data Guard is a high-availability, data protection, and disaster recovery feature set for enterprise data. Data Guard maintains, manages, and monitors one or more standby databases to enable production Oracle databases to survive disasters and data corruptions.
RIL Status: Data Guard is not available in RIL.
|Sharding||Sharding is a scalability and high-availability feature that distributes and replicates data across a pool of discrete Oracle databases. Each database in the elastic pool is referred to as a shard.
RIL Status: This feature requires Oracle Active Data Guard and is not available in RIL.
|Fail Safe||Fail Safe ensures that if a failure occurs on one cluster node, then the databases and applications running on that node fail over (move) automatically and quickly to a surviving node.
RIL Status: This feature is not available in RIL.
|Rolling Status||Allows one or more nodes in the cluster to have a different software version than the other nodes in the cluster for a short period of time.
RIL Status: RIL does not utilize the Oracle Rolling Upgrades feature. As a best practice, all databases in the enterprise should update RIL at the same time and stay on the same version.
|Multiplexed backup sets||Read multiple files from disk and then write their blocks into the same backup set. For example, RMAN can read from two data files simultaneously, and then combine the blocks from these data files into a single backup piece. Make multiple copies of a backup set.
RIL Status: This feature is not available in RIL. RIL Backup sets consist of a single backup file.
|Oracle Tuning Pack||The Oracle Tuning Pack provides advanced tuning features to identify and resolve performance problems.
RIL Status: Although RIL Tech Toolkit includes tuning features, the full Oracle Tuning Pack is not available.
|Oracle Diagnostics Pack|| Oracle Diagnostics Pack provides advanced diagnostic and monitoring features.
RIL Status: Although RIL Tech Toolkit includes diagnostics features, the full Oracle Diagnostics Pack is not available.
|Oracle Advanced Compression||Oracle Advanced Compression provides advanced compression capabilities to help improve database performance and reduce storage costs.
RIL Status: This feature is not available in RIL.
|Automatic Database Diagnostic Monitor||Oracle saves statistical data needed for accurate diagnosis of a problem in the Automatic Workload Repository (AWR). The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data on a regular basis to locate the root causes of performance problems, provide recommendations to fix and identifies non-problem areas of the system.
RIL Status: RIL provides similar functionality in Tech Toolkit > Monitoring and Tuning > Diagnosis.
|Virtual Private Database||VPD enables you to create simple or complex security when the standard object privileges and associated database roles are insufficient (e.g. for regulatory or compliance requirements).
RIL Status: The enhanced security features of VPD are not applicable to RIL. RIL utilizes role-based access control that involves assigning security permissions to groups (roles) and then assigning individual users to the groups.
|Parallel Backup and Recovery||Oracle Database automatically selects the optimum degree of parallelism for instance, crash, and media recovery. Oracle Database applies archived redo logs using an optimal number of parallel processes based on the availability of CPUs.
RIL Status: This feature uses Real Application Clusters and is not supported for RIL.
|Flashback Table/Flashback DB||Flashback Table allows users to query data "AS OF" an earlier time/date, allowing companies to archive data for auditing and regulatory compliance. Flashback Database restores the entire database to a specific point-in-time, using Oracle-optimized flashback logs, rather than via backups.
RIL Status: RIL uses Oracle's standard Backup and Restore features.
|Oracle Total Recall||Total recall is an Oracle enterprise feature. The total recall feature allows you to perform "time travel" and query the database as it existed in a previous point in time. Using Total Recall, data is permanently stored in an archive tablespace and will only age out after a user-defined retention time.
RIL Status: RIL uses Oracle's legacy Backup and Restore features.
|Oracle Real Application Clusters||Supports the transparent deployment of a single database across a cluster of servers, providing fault tolerance from hardware failures or planned outages.
RIL Status: This feature is not supported.
|Oracle Automatic Storage Management||Oracle ASM is an advanced volume manager, disk manager and file system for Oracle databases.
RIL Status: The RIL Oracle database uses standard volumes, disks and file systems.