Oracle database creation for 1C:Enterprise using Oracle Database Configuration Assistant
This article describes how to create Oracle Database version 10.2 for 1C:Enterprise 8.2.
It should be noted that the Oracle database is a more extensive entity than Microsoft SQL Server database. The most suitable analogy looks as follows:
- The Oracle database is equivalent to MS SQL Server instance.
- The "data scheme" concept in the Oracle database is equivalent to the concept of "database" in MS SQL Server.
1C:Enterprise 8.2 does not create the Oracle database. The system administrator has to create the Oracle database independently using Oracle Database Configuration Assistant (dbca). To start this utility, select Start – Programs – Oracle-OraHome10 – Configuration and Migration Tools – Database Configuration Assistant.
The database creation consists of several steps.
To create an Oracle database, during the first step click Create a Database, and then click Next.
During step 2, select Custom Database in the list of templates for database creation. Click Next.
During step 3, specify a name for your Oracle database (e.g. ORA1C) in the Global Database Name field. Click Next.
The Oracle database name must not exceed 8 characters.
By default, the Oracle database is managed via a local Enterprise Manager.
The standard settings will be fine for this, so just click Next.
For safety reasons, in the Password box, set a password for Oracle database users.
Repeat the password in the Confirm Password box. Click Next.
During step 6, select a file storage to be used by the DBMS. The file system is selected by default. Click Next.
During step 7, you can select the location for database files on the disk. The database file location specified in the template is set by default.
During step 8, set the options for database failure recovery. You can accept the default parameters and click Next.
During step 9, select the components to be used in the database. Click Standard Database Components. The following dialog box will be displayed.
1C:Enterprise 8.2 does not use Oracle JVM, Oracle XML DB, or Oracle Intermedia. Therefore clear the Oracle JVM and Oracle XML DB check boxes, and then the Oracle Intermedia check box will be cleared automatically.
The dialog box for component selection will change to look as follows:
Set the data scheme for the Enterprise Manager Repository where its support structures will be created.
Click Next to proceed with the database creation.
During step 10, set the database parameters. The memory capacity available for this Oracle database can be set either as a percentage of the server's overall physical memory or in detail (by clicking Custom and setting the SGA Size and PGA Size parameters).
You can keep the default parameter values on the Sizing and Connection mode tabs intact.
You must set the Database Character Set parameter to Use Unicode (AL32UTF8) on the Character Sets tab.
Click Next to proceed.
During step 11, you can change the location of Oracle database files, set the size of REDO logs, and create tablespaces. You do not have to create tablespaces for 1C:Enterprise as the platform will create them automatically when creating the first infobase.
During step 12, you can select the Save as a Database Template check box to save the template as it can be used to create other Oracle databases.
It takes 10–20 minutes to create a database.
When the process is completed, the system will get a new service, OracleServiceORA1C, which represents the Oracle database you have created.
To connect to the Oracle database, you need the Oracle TNS Listener service. This will not pose any problem, as the service can be created using Oracle Net Configuration Assistant (netca) tools. To start the utility, on the Start menu, point to Programs, point to Oracle-OraHome10, point to Configuration and Migration Tools, and click Oracle Net Configuration Assistant.
Working with Oracle Database data files
When working with the Oracle Database DBMS, 1C:Enterprise creates the following tablespaces:
- V81C_DATA – for data,
- V81C_INDEX – for indexes,
- V81C_LOB – for strings of unlimited length and ValueStorage objects,
- V81C_TEMP – for temporary data.
Tablespaces are created during the infobase creation process only if they do not exist. This means that tablespaces are created during the creation of the first infobase that uses the Oracle Database DBMS, and all subsequent infobases will use those tablespaces.
Each tablespace has a single datafile. Since the created tablespaces have ordinary type (no bigfile option), the datafile size is limited to 32 GB (this value implies using blocks of 8 KB, which is a default value).
The table below illustrates the difference between an ordinary tablespace and a bigfile tablespace.
|Ordinary tablespace||Bigfile tablespace|
Can consist of multiple datafiles.
Consists of a single datafile.
Each datafile is limited to 32 GB (by default).
The datafile size is limited to 32 TB (by default).
Multiple disks can be used for a single tablespace.
A single file implies using a single logical disk, therefore, allocation across several disks is impossible.
When the datafile is 85% full (it is the default value), the Oracle Database DBMS generates an alert to the alert log. At this time you need to add a data file to the tablespace. If no action is taken when the file is 97% full (it is also the default value), an error message informing that the DBMS is unable to extend the segment is generated.
A typical Oracle Database DBMS usage scenario implies that the administrator monitors the alert log status and takes all required actions to prevent any downtime in the information system operations. To add a datafile to the tablespace, run the following command:
SQL> ALTER TABLESPACE V81C_DATA ADD DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORA1C\V81C_DATA2.DBF' SIZE 40M AUTOEXTEND ON NEXT 10M;
A tablespace with the bigfile option can also be created, but in this case the administrator would not be able to manage space allocation for Oracle Database datafiles (the tablespace with the bigfile option can only have a single file). To create a tablespace with the bigfile option, run the following command:
SQL> CREATE BIGFILE TABLESPACE V81C_LOB DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORA1C\V81C_LOB.dbf' SIZE 64G;
Remember these specifics when you are loading large infobases. When the infobase to be loaded exceeds 32 GB, you have to extend the tablespaces before loading the infobase. If you create a tablespace with the bigfile option before loading the infobase, the limit of 32 GB shifts to the next limit, which is 33 TB (terabytes).
Setting up multilingual collation in Oracle Database Server
Oracle Database Server provides support of multilingual collation to let you sort data in more than one language in one sort. This is useful for regions or languages having complex sorting rules and for multilingual databases.
To enable the multilingual collation support in your 1C:Enterprise infobase that you run on Oracle Database Server, perform the following steps before using the infobase:
- Copy the lx327c6.nlt file from the "Additional\OracleDatabase" folder of 1C:Enterprise's setup package to an empty folder on the same disk where Oracle Database Server is installed.
- Run Oracle Locale Builder (lbuilder).
- In Oracle Locale Builder, from the Tools menu, select Generate NLB and select the folder containing the lx327c6.nlt file.
The tool creates the lx1boot.nlb and lx327c6.nlb files in the folder.
- Stop all Oracle-related services.
- Make a backup copy of the lx0boot.nlb and lx1boot.nlb files from the "$ORACLE_HOME/nls/data" folder.
- Copy the lx1boot.nlb and lx327c6.nlb files created by Oracle Locale Builder to the "$ORACLE_HOME/nls/data" folder with replacing existing files.
- Run all Oracle-related services.