Moving the ZAM schema from one Oracle server to another

  • 3000293
  • 11-Apr-2006
  • 27-Apr-2012

Environment

Novell ZENworks Asset Management 7 - ZAM7

Situation

This document outlines the process used to move the ZENworks Asset Management schema from one Oracle server to another.

Resolution

1)  On each system that contains a ZENworks Asset Management component that accesses the database:

a) Define a local service name for the new Oracle server
     b) Alter the ZENworks Asset Management ODBC data source to use the new service name

2)  Moving the schema can be accomplished via Oracle’s export and import utilities, which are included on an Oracle client or server installation. In this example of how to move a ZAM schema from one Oracle server to another, it is assumed that all ZAM schema installation defaults, such as the username of NCSystem and its default password, were used.

3)  Stop the Inventory Process from a ZENworks Asset Management Manager. As long as the parameter "consistent = y” is used in the export utility the ZAM services do not have to be stopped during the schema move process. When the schema move process is complete the ZAM services will then have to be stopped and restarted.

4)  From the Oracle server/client that has a local service name defined for the server on which the ZAM schema resides, run the Oracle export utility from a Command Prompt.  The following export statement parameters are for a server whose Oracle service name is ORCOldServer, the export file will be created on the root of the local C: drive and the export file will be named ORCExportFile_Currentdate.dmp

exp NCSystem/tally@ORCOldServer owner=NCSystem consistent=y file=C:\ORCExportFile_Currentdate.dmp feedback=1000

5)  From DBA Studio create three tablespaces on the new Oracle server. Select the Storage branch - Tablespaces - Create.  It is recommended that tablespaces of the same size and with the same options (such as Automatic Allocation) as the original ones be created.  In this example, the installation default sizes are used.

a) TSCData 1000MB
     b) TSCIDX 1000MB
     c) TSCTemp 500MB
6)  From DBA Studio create two users, one named NCSTARTUP and the other named NCSYSTEM. To create a user select the Security branch – Users – Create.
On the General tab of the Create User dialog enter these edit values for NCSTARTUP:

Name: NCSTARTUP
     Profile: DEFAULT
     Password: tally
     Tablespaces/Default: TSCDATA
     Tablespaces/Temporary: TSCTEMP

On the General tab of the Create User dialog enter these edit values for NCSYSTEM:

Name: NCSYSTEM
     Profile: DEFAULT
     Password: tally
     Tablespaces/Default: TSCDATA
     Tablespaces/Temporary: TSCTEMP
Grant these users the following system privileges:
NCSTARTUP - CREATE SESSION, CREATE SYNONYM
NCSYSTEM - CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE TRIGGER, CREATE PROCEDURE, UNLIMITED TABLESPACE

7)  Copy the export file created in step 3 to an Oracle server/client that has a local service name defined for the new Oracle server. From a Command Prompt on this machine run the Oracle import utility. The following import statement parameters are for a server whose Oracle service name is ORCNewServer, the file to be imported is on the root of the C drive and the file to be imported is named ORCExportFile_Currentdate.dmp.

imp NCSystem/tally@ORCNewServer file=C:\ORCExportFile_Currentdate.dmp fromuser=NCSystem feedback = 1000

8)  From DBA Studio create two Synonyms:  NC_LOGININFO and NC_NETCENSUSINFO.  To create the Synonyms select the Schema branch – Synonym – NCSTARTUP – Create.
On the General tab of the Create Synonym dialog enter these edit values for NC_LOGININFO:

Name: NC_LOGININFO
     Schema: NCSTARTUP
     Object Type: Table 
     Schema: NCSYSTEM
     Object: NC_LOGININFO

On the General tab of the Create Synonym dialog enter these edit values for NC_NETCENSUSINFO:

Name: NC_NETCENSUSINFO
     Schema: NCSTARTUP
     Object Type: Table
     Schema: NCSYSTEM
     Object: NC_NETCENSUSINFO

Note: if DBA Studio is used to create the Synonyms NC_LOGININFO AND NC_NETCENSUSINFO the correct privileges for these Synonyms will be granted automatically.