Installation Support	Support Home Install Home
Please check/use Support Requests, Bugs and Enhancement Requests

Create/Import Compiere Database

Please check Compiere Support for the more detail and latest update.

Prerequisites

Set up the Environment

Oracle connection

In the first step, you created an Oracle instance and defines the SYS and SYSTEM passwords.

The Global Database Name or TNS Name of the database means the same.
Check that you can connect to the database on operating system level via the tnsping <tns name>.

Environment Setup

The folder $COMPIERE_HOME/utils contains scripts required for server operations.

Check the script myEnvironment

Check the settings for COMPIERE_HOME and JAVA_HOME
Check the value of COMPIERE_DB_TNS (Global Database Name)
Check the passwords for SYSTEM to reflect your database setup
If something is not correct, do NOT change it here, but via the RUN_setup script

Double-check the Environment by runninng RUN_Env
- The connect to the "compiere" user will fail - enter Control-C or exit

Compiere Database Import

Verify Compiere.dmp

The file Compiere.dmp is located in $COMPIERE_HOME/data and contains the seed database for your Compiere installation.

Run Database Import

Run the script RUN_ImportCompiere in the $COMPIERE_HOME/utils directory.

Please note that the import program will issue warnings (IMP-00041: Warning: object created with compilation warnings), due to import sequence problems. 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.

Final Environment check

Execute the script RUN_Env in the $COMPIERE_HOME/utils directory again. This time, you should not get any error.

Next Step

Complete Server Installation

Please check Product Status, Known Issues, Common Problems and Open Items as well as the latest version of the install procedures. If you find any inaccuracies or errors, please help us improving Compiere by filing a bug!

Script Background Info & Tasks

You don't need this information for the installation.

The Server Scripts are located in folder $COMPIERE_HOME/utils (example: D\Compiere2\utils). You use the scripts starting with RUN_* - the other worker scripts are called/executed by the RUN_* scripts.

All RUN_* scripts call the script myEnvironment first and sets the required environment variables. The main environment variables are (windows example in parantesis):

COMPIERE_HOME (D:\Compiere2) - the Application Home directory
COMPIERE_DB_TNS (compiere.compiere.org) - the tnsname of the database
JAVA_HOME (D:\Compiere2\jre) - the Java home

The worker scripts are started by RUN_* scripts, which provide the user IDs and passwords. To ease administration, the following environment variables are used:

COMPIERE_DB_SYSTEM = manager - the system password
COMPIERE_DB_USER = compiere - the db compiere user id
COMPIERE_DB_PASSWORD = compiere - the dB compiere password

If you change the passwords (which is recommended for production environments), you need to change the environment variables above via RUN_setup.

Default database tablespace names

Make sure that the tablespaces for the database user Compiere exist: default tablespace is USER, index tablespace is INDX, temporary tablespace is TEMP. If you use different tablespace names, you need to change the CreateUser.sql script and all DBA_*.sql files (see source download) scripts AND UPDATE THEM WITH EVERY UPDATE.

Updates

Updates may update/overwrite the scripts (without notice). If you change the scripts, we suggest that you rename them (e.g. myDBexport). Please keep a log of the changes and check them after an upgrade.

Output Examples

Executing tnsping

TNS Ping Output
D:\Compiere2>tnsping dev TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 09-OCT-2002 16:40:22 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: D:\Oracle\Ora92\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dev)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev.compiere.org))) OK (70 msec) D:\Compiere>tnsping dev.compiere.org TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 09-OCT-2002 16:41:05 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: D:\Oracle\Ora92\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dev)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev.compiere.org))) OK (80 msec) D:\Compiere2>

If you have problems, check the parameter file mentioned in the output!

Executing RUN_Env

RUN_Env Output
D:\Compiere2\utils>RUN_Env Setting myEnvironment .... The system cannot find the file specified. General ... PATH = D:\j2sdk1.4.1\bin;.... CLASSPTH = D:\Compiere2\lib\CClient.jar;D:\Compiere2\lib\CTools.jar;D:\Compiere2\lib\CServer.jar;D:\Compiere2\lib\oracle.jar; . Homes ... COMPIERE_HOME = D:\Compiere2 JAVA_HOME = D:\j2sdk1.4.1 COMPIERE_DB_HOME = D:\Compiere2\utils\oracle . Database ... COMPIERE_DB_USER = compiere COMPIERE_DB_PASSWORD = compiere COMPIERE_DB_PATH = oracle .. Oracle specifics COMPIERE_DB_TNS = dev.compiere.org COMPIERE_DB_SYSTEM = manager . D:\Compiere2\utils>D:\j2sdk1.4.1\bin\java -version java version "1.4.1" Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1-b21) Java HotSpot(TM) Client VM (build 1.4.1-b21, mixed mode) . Java Version should be "1.4.1" --------------------------------------------------------------- Press any key to continue . . . . --------------------------------------------------------------- Database Connection Test (1) ... dev.compiere.org If this fails, verify the COMPIERE_DB_TNS setting with Oracle Net Manager You should see an OK at the end Press any key to continue . . . D:\Compiere2\utils>tnsping dev.compiere.org TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 09-OCT-2002 16:50:35 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: D:\Oracle\Ora92\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dev)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev.compiere.org))) OK (80 msec) . --------------------------------------------------------------- Database Connection Test (3) ... system/manager in D:\Compiere2\utils\oracle If this test fails, verify the system password in COMPIERE_DB_SYSTEM Press any key to continue . . . D:\Compiere2\utils>sqlplus system/[email protected] @D:\Compiere2\utils\oracle\Test.sql SQLPlus: Release 9.2.0.1.0 - Production on Wed Oct 9 16:50:36 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production CONNECTION ----------- Success* Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production . --------------------------------------------------------------- Checking Database Size Press any key to continue . . . D:\Compiere2\utils>sqlplus "system/[email protected] AS SYSDBA" @D:\Compiere2\utils\oracle\CheckDB.sql SQLPlus: Release 9.2.0.1.0 - Production on Wed Oct 9 16:50:38 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production DOC> The contents of this file are subject to the Compiere License. You may DOC> * obtain a copy of the License at http://www.compiere.org/license.html DOC> * Software is on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either DOC> * express or implied. See the License for details. Code: Compiere ERP&CRM DOC> * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved. DOC> ************************************************************************* DOC> * $Id: installDatabase.html,v 1.18 2003/04/30 16:59:19 jjanke Exp $ DOC> *** DOC> * Title: TabeSpace Sizing DOC> * Description: DOC> * Make sure, that Tablespace DOC> * USERS is 150 MB, 10 MB Autoextend DOC> * INDX is 100 MB, 10 MB Autoextend DOC> * TEMP is 100 MB, 10 MB Autoextend DOC> * Set SGA Size (optional) DOC> ***********************************************************************/ no rows selected no rows selected no rows selected DOC> Changing System Parameters DOC> * directly - (e.g. 400 MB for 10 Users) DOC> DOC> ALTER SYSTEM SET SGA_MAX_SIZE=400M COMMENT='400MB' SCOPE=SPFILE; DOC> ALTER SYSTEM SET SHARED_POOL_SIZE=100M SCOPE=SPFILE; DOC> ALTER SYSTEM SET DB_CACHE_SIZE=200M SCOPE=SPFILE; DOC> ALTER SYSTEM SET JAVA_POOL_SIZE=40M SCOPE=SPFILE; DOC> ALTER SYSTEM SET LARGE_POOL_SIZE=10M SCOPE=SPFILE; DOC> DOC> ** indirectly - sqlplus "system/manager@compiere AS SYSDBA" DOC> DOC> CREATE PFile='pfileCompiere.ora' FROM SPFile; DOC> * creates file in $ORACLE_HOME\database or $ORACLE_HOME/dbs DOC> * edit file and then overwrite the fila via DOC> CREATE SPFile FROM PFile='pfileCompiere.ora'; DOC> ***/ Resize: PL/SQL procedure successfully completed. Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production . --------------------------------------------------------------- Database Connection Test (4) ... compiere/compiere This may fail, if you have not imported the Compiere database yet - Just enter EXIT and run this script again after the import Press any key to continue . . .** D:\Compiere2\utils>sqlplus compiere/[email protected] @D:\Compiere2\utils\oracle\Test.sql SQLPlus: Release 9.2.0.1.0 - Production on Wed Oct 9 16:50:48 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production CONNECTION ----------- Success* Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production . Done Press any key to continue . . .

If you get

ERROR: ORA-01017: invalid username/password; logon denied
Enter user-name:

Enter Control-C - As the compiere database was not imported, you cannot connect. If you ran it after the database import, it must complete without errors

Executing RUN_ImportCompiere

RUN_Import output
D:\Compiere2\utils>RUN_ImportCompiere Setting myEnvironment .... Re-Create Compiere User and import D:\Compiere2\data\compiere.dmp - (dev.compiere.org) Volume in drive D is Dev Volume Serial Number is 1096-FA3C Directory of D:\Compiere2\data 09/16/2002 22:25 8,808,448 Compiere.dmp 1 File(s) 8,808,448 bytes 0 Dir(s) 2,628,481,024 bytes free == The import will show warnings. This is OK == Press any key to continue . . . Compiere Database Import $Revision: 1.18 $ Importing Compiere DB from D:\Compiere2\data\compiere.dmp (dev.compiere.org) ------------------------------------- Re-Create DB user ------------------------------------- SQLPlus: Release 9.2.0.1.0 - Production on Wed Oct 9 17:02:17 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> /************************************************************************ DOC> * The contents of this file are subject to the Compiere License. You may DOC> * obtain a copy of the License at http://www.compiere.org/license.html DOC> * Software is on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either DOC> * express or implied. See the License for details. Code: Compiere ERP&CRM DOC> * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved. DOC> ************************************************************************* DOC> * $Id: installDatabase.html,v 1.18 2003/04/30 16:59:19 jjanke Exp $ DOC> *** DOC> * Title: Drop User and re-create new DOC> * Description: DOC> * Parameter: UserID UserPwd DOC> * Run as system DOC> **********************************************************************/ SQL> DROP USER &1 CASCADE 2 / old 1: DROP USER &1 CASCADE new 1: DROP USER compiere CASCADE User dropped. SQL> CREATE USER &1 IDENTIFIED BY &2 2 DEFAULT TABLESPACE USERS 3 TEMPORARY TABLESPACE TEMP 4 PROFILE DEFAULT 5 ACCOUNT UNLOCK 6 / old 1: CREATE USER &1 IDENTIFIED BY &2 new 1: CREATE USER compiere IDENTIFIED BY compiere User created. SQL> GRANT CONNECT TO &1 2 / old 1: GRANT CONNECT TO &1 new 1: GRANT CONNECT TO compiere Grant succeeded. SQL> GRANT DBA TO &1 2 / old 1: GRANT DBA TO &1 new 1: GRANT DBA TO compiere Grant succeeded. SQL> GRANT RESOURCE TO &1 2 / old 1: GRANT RESOURCE TO &1 new 1: GRANT RESOURCE TO compiere Grant succeeded. SQL> GRANT AQ_ADMINISTRATOR_ROLE TO &1 2 / old 1: GRANT AQ_ADMINISTRATOR_ROLE TO &1 new 1: GRANT AQ_ADMINISTRATOR_ROLE TO compiere Grant succeeded. SQL> GRANT JAVAUSERPRIV TO &1 2 / old 1: GRANT JAVAUSERPRIV TO &1 new 1: GRANT JAVAUSERPRIV TO compiere Grant succeeded. SQL> GRANT JAVASYSPRIV TO &1 2 / old 1: GRANT JAVASYSPRIV TO &1 new 1: GRANT JAVASYSPRIV TO compiere Grant succeeded. SQL> GRANT EXECUTE ON DBMS_AQ TO &1 2 / old 1: GRANT EXECUTE ON DBMS_AQ TO &1 new 1: GRANT EXECUTE ON DBMS_AQ TO compiere Grant succeeded. SQL> GRANT EXECUTE ON DBMS_AQADM TO &1 2 / old 1: GRANT EXECUTE ON DBMS_AQADM TO &1 new 1: GRANT EXECUTE ON DBMS_AQADM TO compiere Grant succeeded. SQL> GRANT UNLIMITED TABLESPACE TO &1 2 / old 1: GRANT UNLIMITED TABLESPACE TO &1 new 1: GRANT UNLIMITED TABLESPACE TO compiere Grant succeeded. SQL> ALTER USER &1 DEFAULT ROLE AQ_ADMINISTRATOR_ROLE, CONNECT, RESOURCE, DBA, JAVAUSERPRIV, JAVASYSPRIV 2 / old 1: ALTER USER &1 DEFAULT ROLE AQ_ADMINISTRATOR_ROLE, CONNECT, RESOURCE, DBA, JAVAUSERPRIV, JAVASYSPRIV new 1: ALTER USER compiere DEFAULT ROLE AQ_ADMINISTRATOR_ROLE, CONNECT, RESOURCE, DBA, JAVAUSERPRIV, JAVASYSPRIV User altered. SQL> EXIT Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production ------------------------------------- Import Compiere.dmp ------------------------------------- Import: Release 9.2.0.1.0 - Production on Wed Oct 9 17:05:51 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Export file created by EXPORT:V09.02.00 via conventional path Warning: the objects were exported by COMPIERE, not by you import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing COMPIERE's objects into COMPIERE . . importing table "AD_ATTACHMENT" 0 rows imported . . importing table "AD_ATTRIBUTE" 0 rows imported . . importing table "AD_ATTRIBUTE_VALUE" 0 rows imported . . importing table "AD_CLIENT" 2 rows imported . . importing table "AD_CLIENTINFO" 2 rows imported : : . . importing table "T_REPLENISH" 0 rows imported . . importing table "T_SPOOL" 0 rows imported . . importing table "W_BASKET" 0 rows imported . . importing table "W_COUNTER" 0 rows imported . . importing table "W_REQUEST" 0 rows imported IMP-00041: Warning: object created with compilation warnings "CREATE FORCE VIEW "COMPIERE"."RV_CASH_DETAIL" ("C_CA" "SH_ID","C_CASHLINE_ID","AD_CLIENT_ID","AD_ORG_ID","ISACTIVE","CREATED","CRE" : : About to enable constraints... Import terminated successfully with warnings.** ------------------------------------- Check System Import may show some warnings. This is OK as long as the following does not show errors ------------------------------------- SQLPlus: Release 9.2.0.1.0 - Production on Wed Oct 9 17:14:16 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production DOC> The contents of this file are subject to the Compiere License. You may DOC> * obtain a copy of the License at http://www.compiere.org/license.html DOC> * Software is on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either DOC> * express or implied. See the License for details. Code: Compiere ERP&CRM DOC> * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved. DOC> ************************************************************************* DOC> * $Id: installDatabase.html,v 1.18 2003/04/30 16:59:19 jjanke Exp $ DOC> *** DOC> * Title: After Import DOC> * Description: DOC> * Check Imported User DOC> * You can ignore import warnings as long as this does not DOC> * return error messages or records. DOC> **********************************************************************/ Context created. DBInfo -------------------------------------------------------------------------------- DB_Name=dev, Language=AMERICAN_AMERICA.AL32UTF8, Host=COMPIERE\DEV , IP=192.168. 168.201, User=COMPIERE, ID=101, Session=7931 DBA_Cleanup Invoices Paid=0 T_Spool=0 T_InventoryValue=0 T_Replenish=0 AD_PInstance=0 AD_Find0 AD_Error=0 AD_Trace=0 AD_Note=0 All valid Statistics computed: 281 All valid PL/SQL procedure successfully completed. no rows selected Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Press any key to continue . . .**

The import is successful, if you see "All valid" and "no rows selected" as the last step.

We respect your Privacy

Installation Support