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.
(Oracle)
m (Installation Details: Added info about migrations.)
 
(30 intermediate revisions by 11 users not shown)
Line 1: Line 1:
==README==
+
{{Breadcrumb|Table of Contents#System Administrator's Guide|Installation Details|next=Launching the Application Server}}
 +
[[Category:Documentation]]
 +
[[Category:Installation]]
 +
[[Category:System Admin documentation]]
  
*Further requests for assistance please direct to the [http://sourceforge.net/forum/forum.php?forum_id=610547 HELP Forum]. This wiki provides only written and publishable support.
+
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.
*Further private, local, or commercial assistance can be sought at [[World-wide Directory]] where consultants all over register themselves. The fee charges are up to the respective consultants and under their own responsilibity or control.
+
{{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.}}
*Please adhere to our bazaar [[etiquette]] to enjoy better support from us.
+
== Prerequisites ==
 +
Before starting with the database creation, you should have the following installed:
 +
* 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]].
  
=== Introduction ===
+
== Database Creation ==
This '''Create Database HELP''' is for create the database for your ADempiere installation.
+
After this you can [[InstallComplete|Complete ADempiere Server Install]].
+
  
== Pre-requisite Utilities ==
+
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.
Before starting with the database creation, you should have the following installed:
+
* Database supported by ADempiere
+
* Java [http://java.sun.com/javase/downloads/index.jsp JDK1.5.x]
+
* [[InstallServer|ADempiere Server Installation]]
+
  
== Start with Database Creation==
+
=== Database Creation from the Seed ===
The ADempiere initial database is into the Adempiere.dmp file, located at ''$ADEMPIERE_HOME/data'' directory; this file contains the seed database for your ADempiere installation.
+
To start, open a console window and go to this directory. Verify the Adempiere.dmp file is located there.
+
  
=== Oracle ===
+
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.  
Run the script '''''RUN_ImportAdempiere''''' (.bat or .sh in directory './utils').
+
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 =='''
+
  
 +
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|The following script will <b>DROP</b> 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 =='''
 +
{{Space}}
 
[[Image:CD_Run_ImportAdempiere.PNG|center]]
 
[[Image:CD_Run_ImportAdempiere.PNG|center]]
 +
{{Space}}
 
Press any key to start the process or Ctrl-C to cancel.
 
Press any key to start the process or Ctrl-C to cancel.
  
No worry if you see some warnings (such as Warning: object created with compilation warnings) ; this is normal and you can ignore it. After the import program, a SQL procedure makes sure that everything is imported correctly and will list all invalid objects if they exists.
+
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.
 
+
{{Space}}
 
[[Image:CD_Run_ImportAdempiere2.PNG|center]]
 
[[Image:CD_Run_ImportAdempiere2.PNG|center]]
 
+
{{Space}}
At the process end, you should see: no (error) rows selected
+
At the process end, you should see a message similar to the one displayed below, with the text "no rows selected".
 +
{{Space}}
 
[[Image:CD_Run_ImportAdempiere3.PNG|center]]
 
[[Image:CD_Run_ImportAdempiere3.PNG|center]]
 
+
{{Space}}
Sharad Sharma
+
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.
In case or error in installing Oracle database or to deinstall Oracle Pls follow this like
+
{{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.}}
 
+
http://download-east.oracle.com/docs/cd/B25329_01/doc/install.102/b25143/toc.htm#CIHDDHJD
+
 
+
=== PostgreSQL===
+
Same as the Oracle step above.Note that the database dump for PostgreSQL is Adempiere_pg.jar and Adempiere_pg.dmp.
+
 
+
 
== Next Step ==
 
== Next Step ==
 
The next step is [[InstallComplete|Complete ADempiere Server Install]].
 
The next step is [[InstallComplete|Complete ADempiere Server Install]].
  
 
== Notes ==
 
== Notes ==
* <u>Be careful</u>: the script '''''RUN_AdempiereImport''''' <u>DROP</u> the database, so if you run the script all your datas will be losed!. Don't execute this script if you have data and you need for them.
 
 
* For Oracle Users:  
 
* For Oracle Users:  
 
** Please make sure that the tablespaces for the database user Adempiere exist. The Default database tablespace names are:  
 
** Please make sure that the tablespaces for the database user Adempiere exist. The Default database tablespace names are:  
Line 51: Line 50:
 
*** index tablespace= '''INDX''' (100 MB, 10 MB Autoextend),  
 
*** index tablespace= '''INDX''' (100 MB, 10 MB Autoextend),  
 
*** temporary tablespace= '''TEMP''' (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 have the following entries: NAMES.DIRECTORY_PATH = (EZCONNECT,TNSNAMES)
+
** 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)
  
==Links==
+
== 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==
 
*[[InstallComplete|Complete ADempiere Server Install]] is the next thing after Create ADempiere Database.
 
*[[InstallComplete|Complete ADempiere Server Install]] is the next thing after Create ADempiere Database.
 
*[[ManPageX_InitialClientSetup|Initial Client Setup]] is the starting business setup within ADempiere.
 
*[[ManPageX_InitialClientSetup|Initial Client Setup]] is the starting business setup within ADempiere.
*[[ADempiere_Installing|Installing ADempiere]] Tutorial on how to setup and configure ADempiere.
+
*[[Getting Started]] Tutorial on how to setup and configure ADempiere.
 
*[[Tutorials|Tutorials]] on many things from basic to advanced.
 
*[[Tutorials|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: [http://download-east.oracle.com/docs/cd/B25329_01/doc/install.102/b25143/toc.htm#CIHDDHJD]
[[Category:OnLineHelp]]
+

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]