Difference between revisions of "Initialize the ADempiere Database"

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.
(Installation Details: Added detail on Oracle.)
m (Installation Details: Added info about migrations.)
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{Breadcrumb|Table of Contents#System Administrator's Guide|Installation Details}}
+
{{Breadcrumb|Table of Contents#System Administrator's Guide|Installation Details|next=Launching the Application Server}}
 
[[Category:Documentation]]
 
[[Category:Documentation]]
 
[[Category:Installation]]
 
[[Category:Installation]]
Line 8: Line 8:
 
== Prerequisites ==
 
== Prerequisites ==
 
Before starting with the database creation, you should have the following installed:
 
Before starting with the database creation, you should have the following installed:
* a suitable database (i.e. Oracle 10g, Oracle 10gXE, PostgreSQL, MySQL) and that the database server is running.  See [[Database Server Installation & Setup]].
+
* a suitable database (i.e. Oracle XXg, Oracle XXgXE, PostgreSQL, MySQL) and that the database server is running.  See [[Database Server Installation & Setup]].
 
* the application server installation is complete.  See [[Application Server Installation & Setup]].
 
* the application server installation is complete.  See [[Application Server Installation & Setup]].
  
 
== Database Creation ==
 
== Database Creation ==
  
The database created by the installation procedure you followed has no specific ADempiere information included.  Before the application can run, a suitable database must be created.  This can be done by installing the Seed database provided with the software or by restoring a previously created database.
+
The database created when you installed Oracle/Postgres/MySQL has no ADempiere data.  Before the ADempiere application can run, a suitable database must be created.  This can be done by installing the '''Seed Database''' provided with the software or by restoring a previously created database.
  
 
=== Database Creation from the Seed ===
 
=== Database Creation from the Seed ===
Line 39: Line 39:
 
[[Image:CD_Run_ImportAdempiere3.PNG|center]]
 
[[Image:CD_Run_ImportAdempiere3.PNG|center]]
 
{{Space}}
 
{{Space}}
A common cause of problems when running this script is not having the environment variables set properly. The script will not run if ADEMPIERE_HOME, JAVA_HOME or POSTGRES_HOME or ORACLE_HOME are set incorrectly. It may also be necessary to add the Postgres/Oracle bin directory to the PATH environment variable in some environments.  
+
A common cause of problems when running this script is not having the environment variables set properly. The script will not run if ADEMPIERE_HOME, JAVA_HOME or POSTGRES_HOME or ORACLE_HOME are set incorrectly. It may also be necessary to add the Postgres/Oracle bin directory to the PATH environment variable in some environments.
 
+
{{Note|Since 3.8.0, the seed database will be automatically migrated to the current release by loading and applying all xml migration scripts found in the $ADEMPIERE_HOME/migration directory.}}
 
== Next Step ==
 
== Next Step ==
 
The next step is [[InstallComplete|Complete ADempiere Server Install]].
 
The next step is [[InstallComplete|Complete ADempiere Server Install]].
Line 55: Line 55:
  
 
The script RUN_ImportAdempiere simply calls the script ImportAdempiere in the $ADEMPIERE_HOME/utils/<database> directory.  The version of ImportAdempiere called deals with the specific setup needs of the various databases.
 
The script RUN_ImportAdempiere simply calls the script ImportAdempiere in the $ADEMPIERE_HOME/utils/<database> directory.  The version of ImportAdempiere called deals with the specific setup needs of the various databases.
 +
 +
Since 3.8.0, the RUN_ImportAdempiere script will also import and apply any migrations found in the $ADEMPIERE_HOME/migrations directory.
 +
 +
Following the database import, the database is '''signed''' - to indicate the version of the database.
  
 
The ImportAdempiere script is called with the following parameters:
 
The ImportAdempiere script is called with the following parameters:

Latest revision as of 06:21, 21 August 2014

Table of Contents#System Administrator's Guide{{#if: Installation Details| | Installation Details }}{{#if: | | [[{{{3}}}]] }}{{#if: | | [[{{{4}}}]] }}{{#if: | | [[{{{5}}}]] }} | Initialize the ADempiere Database{{#if: Launching the Application Server| | Launching the Application Server }} ⇒

After having installed the database and setup the ADempiere Application Server, the database needs to be initialized prior to launching the Application Server and any client software.

Note.gif Note:

The utility scripts provided with the Application software need to have access to the database functions in order to run. In a network installation, you may have to copy the database executable files to the Application Server machine, install a local copy of the database on the application server or manually install the seed database on the database server. Detailed instructions are provided below.

Prerequisites

Before starting with the database creation, you should have the following installed:

Database Creation

The database created when you installed Oracle/Postgres/MySQL has no ADempiere data. Before the ADempiere application can run, a suitable database must be created. This can be done by installing the Seed Database provided with the software or by restoring a previously created database.

Database Creation from the Seed

The initial ADempiere Seed database is imported from the Adempiere.dmp file for Oracle or Adempiere_pg.dmp for PostgreSQL, located in the $ADEMPIERE_HOME/data directory.

To start, open a console window and go to the $ADEMPIERE_HOME/data directory. Verify that the Adempiere.dmp file is located there.

Change directories to the $ADEMPIERE_HOME/utils directory.

Caution.gif Caution!

The following script will DROP any existing adempiere database. Do not run this command if you already have data loaded.

Run the script RUN_ImportAdempiere (.bat or .sh).

You will see information about adempiere.dmp file (such as date creation, size, etc.) and the message: == The import will show warnings. This is Ok ==

 

CD Run ImportAdempiere.PNG

 

Press any key to start the process or Ctrl-C to cancel.

Don't worry if you see warnings (such as "Warning: object created with compilation warnings"). This is normal and can be ignored. After the import has finished, an SQL procedure makes sure that everything has been imported correctly and will list any invalid objects.

 

CD Run ImportAdempiere2.PNG

 

At the process end, you should see a message similar to the one displayed below, with the text "no rows selected".

 

CD Run ImportAdempiere3.PNG

 

A common cause of problems when running this script is not having the environment variables set properly. The script will not run if ADEMPIERE_HOME, JAVA_HOME or POSTGRES_HOME or ORACLE_HOME are set incorrectly. It may also be necessary to add the Postgres/Oracle bin directory to the PATH environment variable in some environments.

Note.gif Note:

Since 3.8.0, the seed database will be automatically migrated to the current release by loading and applying all xml migration scripts found in the $ADEMPIERE_HOME/migration directory.

Next Step

The next step is Complete ADempiere Server Install.

Notes

  • For Oracle Users:
    • Please make sure that the tablespaces for the database user Adempiere exist. The Default database tablespace names are:
      • default tablespace= USER (150 MB, 10 MB Autoextend),
      • index tablespace= INDX (100 MB, 10 MB Autoextend),
      • temporary tablespace= TEMP (100 MB, 10 MB Autoextend).
    • The setup script have been changed to use the EZCONNECT naming method instead of TNSNAMES. Open your Oracle Net Manager, under profile -> Naming, make sure EZCONNECT is one of the selected methods. Alternatively, verify that the SQLNET.ORA file has the following entries: NAMES.DIRECTORY_PATH = (EZCONNECT,TNSNAMES)

Installation Details

The script RUN_ImportAdempiere simply calls the script ImportAdempiere in the $ADEMPIERE_HOME/utils/<database> directory. The version of ImportAdempiere called deals with the specific setup needs of the various databases.

Since 3.8.0, the RUN_ImportAdempiere script will also import and apply any migrations found in the $ADEMPIERE_HOME/migrations directory.

Following the database import, the database is signed - to indicate the version of the database.

The ImportAdempiere script is called with the following parameters:

  • system/%ADEMPIERE_DB_SYSTEM% (not used in PostgreSQL installation)
  •  %ADEMPIERE_DB_USER% (typically Adempiere)
  •  %ADEMPIERE_DB_PASSWORD% (typically Adempiere)
  •  %ADEMPIERE_DB_SYSTEM% (typically postgres - not used in the oracle installation)

In addition, the following environment variables are required and should have been set by the Application Setup process:

  • ADEMPIERE_HOME e.g. D:\ADEMPIERE2
  • ADEMPIERE_DB_NAME e.g. adempiere or xe
  • ADEMPIERE_DB_SERVER e.g. dbserver.adempiere.org
  • ADEMPIERE_DB_PORT e.g. 5432 or 1521

The descriptions below are not correct code and are intended for information purposes only. See the actual scripts for the details.

Postgres

The $ADEMPIERE_HOME/utils/postgresql/ImportAdempiere script will run the following commands:

-- Drop the ADempiere database if it exists
dropdb -h %ADEMPIERE_DB_SERVER% -p %ADEMPIERE_DB_PORT% -U postgres %ADEMPIERE_DB_NAME%

-- Drop the ADempiere user if it exists
dropuser -h %ADEMPIERE_DB_SERVER% -p %ADEMPIERE_DB_PORT% -U postgres %ADEMPIERE_DB_PASSWORD%

-- Recreate the ADempiere user
set ADEMPIERE_CREATE_ROLE_SQL=CREATE ROLE %ADEMPIERE_DB_USER% SUPERUSER LOGIN PASSWORD '%ADEMPIERE_DB_PASSWORD%'
psql -h %ADEMPIERE_DB_SERVER% -p %ADEMPIERE_DB_PORT% -U postgres -c "%ADEMPIERE_CREATE_ROLE_SQL%"

-- Create the ADempiere database (empty)
set PGPASSWORD=%ADEMPIERE_DB_PASSWORD%
createdb -h %ADEMPIERE_DB_SERVER% -p %ADEMPIERE_DB_PORT% -E UNICODE -O %ADEMPIERE_DB_USER% -U %ADEMPIERE_DB_USER% %ADEMPIERE_DB_NAME%

-- Import the seed data
@psql -h %ADEMPIERE_DB_SERVER% -p %ADEMPIERE_DB_PORT% -d %ADEMPIERE_DB_NAME% -U %ADEMPIERE_DB_USER% -f %ADEMPIERE_HOME%/data/Adempiere_pg.dmp

Oracle and OracleXE

The $ADEMPIERE_HOME/utils/oracle(XE)/ImportAdempiere script will run the following commands:

-- Re-Create DB user
sqlplus system/%ADEMPIERE_DB_SYSTEM%@%ADEMPIERE_DB_SERVER%:%ADEMPIERE_DB_PORT%/%ADEMPIERE_DB_NAME% _
   @%ADEMPIERE_HOME%\Utils\%ADEMPIERE_DB_PATH%\CreateUser.sql %ADEMPIERE_DB_USER% %ADEMPIERE_DB_SYSTEM%

-- Import Adempiere.dmp
imp system/%ADEMPIERE_DB_SYSTEM%@%ADEMPIERE_DB_SERVER%:%ADEMPIERE_DB_PORT%/%ADEMPIERE_DB_NAME% _
   FILE=%ADEMPIERE_HOME%\data\Adempiere.dmp FROMUSER=(reference) TOUSER=%ADEMPIERE_DB_USER% STATISTICS=RECALCULATE

-- Create SQLJ 
call %ADEMPIERE_HOME%\Utils\%ADEMPIERE_DB_PATH%\create %ADEMPIERE_DB_USER%/%ADEMPIERE_DB_PASSWORD%

-- System Check - The Import phase showed warnings. 
-- This is OK as long as the following does not show errors
sqlplus %ADEMPIERE_DB_USER%/%ADEMPIERE_DB_PASSWORD%@%ADEMPIERE_DB_SERVER%:%ADEMPIERE_DB_PORT%/%ADEMPIERE_DB_NAME% _
   @%ADEMPIERE_HOME%\Utils\%ADEMPIERE_DB_PATH%\AfterImport.sql

See Also

  • Complete ADempiere Server Install is the next thing after Create ADempiere Database.
  • Initial Client Setup is the starting business setup within ADempiere.
  • Getting Started Tutorial on how to setup and configure ADempiere.
  • Tutorials on many things from basic to advanced.
  • If you have any further problems installing the Oracle database or you would like to remove it, additional information can be found in here: [1]