Setting Up the Inventory Database

The following sections contain detailed information to help you set up your Inventory database for Sybase and Oracle:

• Setting Up the Inventory Database for Sybase
• Setting Up the Inventory Database for Oracle

If you want to replace the Inventory database, always stop the Inventory services before replacing the database. Replace the database and restart the Inventory services. For more information, see Starting and Stopping the Inventory Service .

Setting Up the Inventory Database for Sybase

This section contains the following information:

• Manually Creating the Inventory Database Object for Sybase
• Organizing the Database Spaces for a Sybase Database on NetWare or Windows NT/2000 Servers (AlterDBSpace Tool)
• Understanding the Sybase Database Startup Parameters
• Optimizing the Performance of the Sybase Database
• Backing Up the Inventory Database Running Sybase

Manually Creating the Inventory Database Object for Sybase

To manually create the Inventory database object for Sybase:

1. In ConsoleOne, right-click in the eDirectory tree where you want to create the database object > click New > click Object > click ZENworks Database > click OK.

2. Enter a name for the database object > click OK.

3. Configure the Database server options of the Database object.

   1. In ConsoleOne, right-click the database object > click Properties > click the ZENworks Database tab.

   2. Select the database server object using any of the following methods:

      • If eDirectory is installed on the database server: in the Server DN field, browse for and select the Server object for the server where the database is physically installed and running.

        The server's IP address is automatically populated to the Server IP Address or DNS Name drop-down list. If the selected server object has more than one IP address, select the appropriate IP address.

      • If eDirectory is not installed on the database server, then enter the server's IP address or the DNS name in the Server IP Address or DNS Name field.

      IMPORTANT:  If the ZENworks database is located on a NetWare 4.x server, you must enter the server's IP address in the Server IP Address or DNS Name field instead of adding the server's object to the Server DN field.

   3. Type the values for the following options:

      • Database (Read-Write) User Name: MW_DBA

      • Database (Read-Write) Password: novell

      • Database (Read Only) User Name: MW_READER

      • Database (Read Only) Password: novell

      • Database (Write Only) User Name: MW_UPDATER

      • Database (Write Only) Password: novell

   4. Click Apply.

   5. To configure the JDBC* Driver properties, click the Jdbc Driver Information tab.

   6. Select Sybase > click Default Settings.

      This populates the fields with default JDBC driver information.

      The database settings for Sybase are:

      • Driver: com.sybase.jdbc.SybDriver

      • Protocol: jdbc:

      • SubProtocol: sybase:

      • SubName: Tds:

      • Port: 2638

      • Flags: ?ServiceName=mgmtdb&JCONNECT_VERSION=4

      • Database Service Name: the database name specified against the -n Sybase startup parameter while invoking Sybase.

        NOTE:  By default, the value of the -n switch is the IP address of the database server. If you retain this switch value, you must enter the same IP address as the database service name.

   7. Click Apply > Close.

Organizing the Database Spaces for a Sybase Database on NetWare or Windows NT/2000 Servers (AlterDBSpace Tool)

If a NetWare database server has volumes other than SYS: or a Windows database server has additional hard drives, placing the Sybase database spaces files on separate volumes or drives improves performance while accessing the database.

If you install the Sybase database component of ZfS 3, the system database file and the database spaces files are installed in the location on the database server you specify. On loading the Inventory database server, the system database file (MGMTDB.DB) is loaded. This MGMTDB.DB file references the inventory information in the database spaces files. The database spaces files (MGMTDB1.DB, MGMTDB2.DB, MGMTDB3.DB, MGMTDB4.DB, MGMTDB5.DB, MGMTDB6.DB, MGMTDB7.DB, MGMTDB8.DB, MGMTDB9.DB, MGMTDB10.DB, and MGMTDB11.DB) contain the inventory information.

The ALTERDB.PROPS file is installed on the database server in the Inventory_server_installation_directory\WMINV\PROPERTIES directory. You can modify the sections in the file to specify the location of the database spaces on the volumes or drives.

The contents of the ALTERDB.PROPS file are as follows:

#Database Space Properties

count=11

mgmtdb1=location_of_mgmtdb1

mgmtdb2=location_of_mgmtdb2

mgmtdb3=location_of_mgmtdb3

mgmtdb4=location_of_mgmtdb4

mgmtdb5=location_of_mgmtdb5

mgmtdb6=location_of_mgmtdb6

mgmtdb7=location_of_mgmtdb7

mgmtdb8=location_of_mgmtdb8

mgmtdb9=location_of_mgmtdb9

mgmtdb10=location_of_mgmtdb10

mgmtdb11=location_of_mgmtdb11

.....

To organize the database spaces:

1. Ensure that the database is not loaded.

2. Ensure that the Inventory Service Manager is not running on the Inventory server.

3. Manually move the database spaces files on the Inventory server.

   Arrange the database spaces files as follows for better performance:

   • MGMTDB1 and MGMTDB2 in the same location
   • MGMTDB3 and MGMTDB6 in the same location
   • MGMTDB5 and MGMTDB7 in the same location
   • MGMTDB8 and MGMTDB4 in the same location
   • MGMTDB9 and MGMTDB10 in the same location
   • MGMTDB11 in a location

   IMPORTANT:  If you move MGMTDB.DB to another directory or volume on a NetWare server, update the SYS:\SYSTEM\MGMTDBS.NCF file with the new location of the MGMTDB.DB.

   If you move MGMTDB.DB to another directory or volume on a Windows NT/2000 server, run the NTDBCONFIG.EXE located in ZENWORKS\DBENGINE directory. In the NTDBCONFIG dialog box, enter the new path of the MGMTDB.DB.

4. Modify the location of the eleven database spaces files in the ALTERDB.PROPS file.

   For example, for NetWare, enter:

   mgmtdb3=SYS:\\ZENWORKS\\INV\\DB

   or for Windows NT/2000, enter:

   mgmtdb3=C:\\ZENWORKS\\INV\\DB

5. Load the database > enter mgmtdbs on NetWare servers, or on Windows NT/2000 servers, run the database service.

   Ignore the error messages displayed on the console. These messages are displayed because the database spaces files are not loaded.

6. Ensure that the Database Location policy has been configured.

7. On the Inventory server console, run the AlterDBSpace service > enter StartSer AlterDBSpace.

   On the Inventory server, the AlterDBSpace tool runs as a service.

   You will see a message that the database is adjusted.

8. Exit the database and then load the database.

   Ensure that there are no errors while loading the database. Errors indicate that the specified location of the database spaces files are incorrect or does not exist. Ensure that the path to the database spaces files is correct in the ALTERDB.PROPS file and repeat the procedure to organize the database spaces files.

   IMPORTANT:  If you place the database spaces files in different volumes or drives, the log file should be placed in the same volume or drive as the System database file (MGMTDB.DB).

Understanding the Sybase Database Startup Parameters

The startup parameters of the Sybase database are as follows:

• -c: Sets the initial memory reserves for caching database pages and other server information. For example, -c 32M reserves 32 MB cache size.

• -gc: Sets the maximum length of time in minutes that the database server runs without doing a checkpoint on each database. The default value is 60 minutes. For example, -gc sets the checkpoint time as 120 minutes.

• -m: Deletes the transaction log when a checkpoint is done, either at shutdown or as a result of a checkpoint scheduled by the server.

• -n: Specifies the host name of the database server. For example, -n IP_address.

• -ti: Disconnects the connections that have not submitted a request for a certain number of minutes. The default is 240 (4 hours). A client machine in the middle of the database transaction locks until the transaction ends or the connection terminates. The -ti option is provided to disconnect inactive connections and to free their locks. For example, specify -ti 400.

• -x: Specifies a communication link. For example, -x tcpip indicates a TCP/IP link.

• database_installation_path: Specifies the installation path of the Inventory database. For example, C:\ZENWORKS\INV\DB\MGMTDB.DB.

Optimizing the Performance of the Sybase Database

Increasing the database cache size improves database performance.

You can improve the performance of the Inventory database maintained in Sybase on NetWare or Windows NT/2000 Inventory servers. The default database cache size is 32 MB; however, this database cache size may not be adequate for large databases.

You should change the database cache size to an optimum size. You must also consider server memory size while assigning a cache size. For example, if you have 128 MB RAM, then a cache size of 32 MB is recommended.

To change the database cache size on the NetWare database server:

1. Close all connections to the Inventory database.

2. Quit the Sybase server.

3. Open the MGMTDBS.NCF file in the SYS:\SYSTEM directory.

4. Modify the -c parameter.

   For example, -c 64M sets the cache size to 64 MB.

5. Save the file.

6. On the server console, load the Inventory database. Enter MGMTDBS.

To change the database cache size on a Windows NT/2000 database server:

1. Stop the Sybase service.

   On Windows NT, in the Control Panel, double-click Services > select Novell Database - Sybase > click Stop.

   On Windows 2000, in the Control Panel, double-click Administrative Tools > double-click Services > select Novell Database - Sybase > click Stop.

2. On the database server, run the NTDBCONFIG.EXE file from the DBENGINE directory.

   NTDBCONFIG.EXE is a ZENworks database configuration utility for the ZENworks database using Sybase on Windows NT/2000 servers. This utility enables you to reconfigure the Sybase service. For the list of parameters recommended by Sybase, see Understanding the Sybase Database Startup Parameters .

3. Modify the -c parameter.

4. Click OK.

5. Restart the Sybase service.

   On Windows NT, in the Control Panel, double-click Services > select Novell Database - Sybase > click Start.

   On Windows 2000, in the Control Panel, double-click Administrative Tools > double-click Services > select Novell Database - Sybase > click Start.

Backing Up the Inventory Database Running Sybase

ZfS provides an option to back up the Inventory database running Sybase from the ConsoleOne and Inventory database running Oracle from the server. We recommend that you back up the database on a weekly basis. However, if you are tracking the inventory of servers frequently, increase the frequency of backup.

To back up the database on NetWare or Windows NT/2000 servers:

1. In ConsoleOne, click Tools > ZENworks Inventory > Database Backup.

   If you want to back up the latest information in the Inventory database, right-click the database object > click ZENworks Inventory > click Database Backup.

2. Enter the path to the directory where the database backup will be saved.

   If the Inventory database is running on a NetWare server, you can either enter the path or click Browse to browse for and select a directory. If you just enter the database backup directory name without specifying the complete path, the backup directory will be created in the SYS: directory.

   If the Inventory database is running on a Windows machine, you must manually enter the backup directory path. If you just enter the database backup directory name without specifying the complete path, the backup directory will be created in the \WINNT\SYSTEM32 directory.

   NOTE:  If you want to back up the database to a non-existent directory, only one level of the new directory will be created. To back up the database to subdirectory, ensure that the primary directory already exists. For example, if you want to back up the database to a new C:\BACKUP directory, the BACKUP directory will be created and the database will be backed up. But if you want to back up the database to a new DATABASE directory, located under C:\BACKUP, the BACKUP directory must already exist.

3. Click Start Backup.

   This backs up the database to the specified directory on the server running the database and overwrites any existing files without prompting about the overwrite.

To restore the database:

1. If the Inventory database server is up, stop the Storer service. At the database server console, enter StopSer Storer.

2. Exit the Sybase database.

   On NetWare servers: At the database server prompt, enter q to stop the Sybase database.

   On Windows NT, in the Control Panel, double-click Services > select Novell Database - Sybase > click Stop.

   On Windows 2000, in the Control Panel, double-click Administrative Tools > double-click Services > select Novell Database - Sybase > click Stop.

3. Copy the backup files, overwriting the working database files.

4. Restart the database server.

The backup tool creates a log file, BACKUPST.TXT, located in the CONSOLEONE\ConsoleOne_version\BIN directory on NetWare and Windows NT/2000 servers. The log records the status of the backup operation. Open this text file to view the status of the backup. This file increases in size for every backup operation. Remove the existing contents of the file if you do not require the details.

Setting Up the Inventory Database for Oracle

The following sections explain how to configure the Inventory database for Oracle:

• Creating the Inventory Database for Oracle on a NetWare Server
• Creating the Inventory Database on Oracle 8i for UNIX
• Creating the Inventory Database for Oracle on a Windows NT/2000 Server
• Manually Creating the Inventory Database Object for Oracle
• Loading the Inventory Database as a Separate Oracle Instance
• Optimizing the Performance of the Oracle Database
• Backing Up the Inventory Database Running Oracle

Creating the Inventory Database for Oracle on a NetWare Server

You must manually create the Inventory database for Oracle on NetWare servers.

Prerequisites for configuring the database include the following:

• Oracle 8i (8.1.5.0.4) Enterprise Edition on NetWare must be installed on the server before configuring the Inventory database.
• To maintain the Inventory database in Oracle, Server Inventory requires that you have a minimum of twenty five Oracle user licenses.
• Oracle files should not be installed on an NFS-mounted volume on the file server.
• Oracle data files must reside on volumes that have block suballocation turned off.

Perform the following procedure to create the Inventory database on Oracle 8i for NetWare:

1. Create a directory SYS:\SCHEMA and copy the following files from the ZENworks for Servers 3 product CD to the SCHEMA directory:

   • Product_CD\ZFS\RMINV\DATABASE\ORACLE\COMMON
   • Product_CD\ZFS\RMINV\ORACLE\NETWARESPECIFIC

2. Create the user_specified_volumepath\ZENWORKS\INVENTORY\ORACLE\
   DATABASE\TRACE directory structure. Here user_specified_volumepath refers to the user selected directory to create the database.

3. In SYS:\SCHEMA\_CREATE.SQL, replace all instances of oracle:with user_specified_volumepath.

4. In SYS:\SCHEMA\INIT.ORA, replace all instances of oracle: with user_specified_volumepath.

5. In SYS:\SCHEMA\_START.SQL, replace all instances of oracle: with user_specified_volumepath.

6. Copy the file SYS:\SCHEMA\INIT.ORA to user_specified_volumepath\ZENWORKS\INVENTORY\ORACLE\
   DATABASE.

7. Copy the file SYS:\SCHEMA\_START.SQL to user_specified_volumepath\ZENWORKS.

8. At the command prompt, enter ORALOAD to start Oracle, if not started.

9. Ensure that no Oracle database is mounted.

10. Load the Oracle Server Manager by entering svrmgr31.

11. At the server manager prompt, enter @sys:\schema\schema.sql.

    Review the SYS:\SCHEMA\INV.LOG file to ensure that the database has been created successfully. If the database has not been successfully created, INV.LOG will contain the one or more of the following error messages: Oracle not available, Out of space, Compilation error.

12. At the Oracle Server Manager prompt, enter @<volumepath>\zenworks\_start.sql to start the Inventory database.

Creating the Inventory Database on Oracle 8i for UNIX

Ensure that the following requirements are met:

• Oracle version

  On Linux* 6.0 or above: Oracle 8i (8.1.5 or above) Enterprise Edition

  On Solaris* 6.2 or above on Sparc*/Intel*: Oracle 8i (8.1.5 or above) Enterprise Edition

• System requirements

  Hard disk free space: 700 MB or above

  Primary memory: 512 MB or above

• To maintain the Inventory database in Oracle, Server Inventory requires that you have a minimum of twenty five Oracle user licenses.

You must manually create the Inventory database for Oracle 8i on the UNIX* server by following the procedure below:

1. Log in as Oracle user.

2. Create a directory SCHEMA and copy the following files from the ZENworks for Servers 3 product CD to the SCHEMA directory:

   • Product_CD\ZFS\RMINV\DATABASE\ORACLE\COMMON
   • Product_CD\ZFS\RMINV\ORACLE\UNIXSPECIFIC

3. Create the user_specified_directory_path/ZENWORKS/INVENTORY/ORACLE/DATABASE/TRACE directory structure.

4. In SCHEMA/INIT.ORA, replace all instances of $HOME by the selected user_specified_directory_path.

5. In SCHEMA/_START.SQL, replace all instances of $HOME by the selected user_specified_directory_path.

6. In SCHEMA/_CREATE.SQL, replace all instances of $HOME by the selected user_specified_directory_path.

7. Copy the file from SCHEMA/INIT.ORA to user_specified_directory_path/ZENWORKS/INVENTORY/ORACLE/DATABASE.

8. Copy the file from SCHEMA/_START.SQL to user_specified_directory_path/ZENWORKS.

9. Ensure the Oracle services are up and running and no database is mounted.

10. Load the Oracle Server Manager by entering svrmgrl

11. At the server manager prompt, enter @$HOME/schema/schema.sql

    Review the SCHEMA/INV.LOG file to ensure that the database has been created successfully. If the database has not been successfully created, SCHEMA/INV.LOG will contain the following error messages: Oracle not available, Out of space, Compilation error.

12. At the Oracle Server Manager prompt, enter @user_specified_directory_path/zenworks/_start.sql to start the Inventory database.

Creating the Inventory Database for Oracle on a Windows NT/2000 Server

You must manually create the Inventory database for Oracle on Windows NT/2000 servers.

Prerequisites for configuring the database include the following:

• Oracle 8i Enterprise Edition must be installed on the server before configuring the Inventory database.
• To maintain the Inventory database in Oracle, Server Inventory requires that you have a minimum of twenty five Oracle user licenses.

Perform the following procedure to create the Inventory database on Oracle 8i for Windows NT/2000:

1. Create a directory C:\SCHEMA and copy the following files from the ZENworks for Servers 3 product CD to the SCHEMA directory:

   • Product_CD\ZFS\RMINV\DATABASE\ORACLE\COMMON
   • Product_CD\ZFS\RMINV\ORACLE\WINNTSPECIFIC

2. Create the user_specified_path\ZENWORKS\INVENTORY\ORACLE\
   DATABASE\TRACE directory structure.

3. In C:\SCHEMA\_CREATE.SQL, replace all instances of d: with user_specified_path.

4. In C:\SCHEMA\INIT.ORA, replace all instances of d: with user_specified_path.

5. In C:\SCHEMA\_START.SQL, replace all instances of d: with user_specified_path.

6. Copy the file C:\SCHEMA\INIT.ORA to user_specified_path\ZENWORKS\INVENTORY\ORACLE\
   DATABASE.

7. Copy the file C:\SCHEMA\_START.SQL to user_specified_path\ZENWORKS.

8. Ensure that Oracle services are loaded correctly and no database is mounted.

9. Load the Oracle Server Manager by entering within a dos box: svrmgrl

10. At the server manager prompt, enter @c:\schema\schema.sql

    Review the SCHEMA/INV.LOG file to ensure that the database has been created successfully. If the database has not been successfully created, SCHEMA/INV.LOG will contain the following error messages: Oracle not available, Out of space, Compilation error

11. At the Oracle Server Manager prompt, enter @<path>\zenworks\_start.sql to start the Inventory database.

Manually Creating the Inventory Database Object for Oracle

To manually create the Inventory database object for Oracle:

1. In ConsoleOne, right-click a location in the eDirectory tree for the database object > click New > Object > ZENworks Database > OK.

2. Type a name for the database object > click OK.

3. Configure the database server options of the database object.

   1. In ConsoleOne, right-click the database object > click Properties > click the ZENworks Database tab.

   2. Select the database server object using any of the following methods:

      • If eDirectory is installed on the database server: in the Server DN field, browse for and select the Server object of the server where the database is physically installed and running.

        The server's IP address is automatically populated to the Server IP Address or DNS Name drop-down list. If the selected server object has more than one IP address, select the appropriate IP address.

      • If eDirectory is not installed on the database server, then enter the server's IP address or the DNS name in the Server IP Address or DNS Name field.

      IMPORTANT:  If the ZENworks database is located on a NetWare 4.x server, you must enter the server's IP address in the Server IP Address or DNS Name field instead of adding the server's object to the Server DN field.

   3. Type the values for the following options:

      • Database (Read-Write) User Name: MW_DBA

      • Database (Read-Write) Password: novell

      • Database (Read Only) User Name: MWO_READER

      • Database (Read Only) Password: novell

      • Database (Write Only) User Name: MWO_UPDATER

      • Database (Write Only) Password: novell

   4. Click Apply.

   5. To configure the JDBC Driver properties, click the JDBC Driver Information tab.

   6. Select Oracle > click Default Settings.

      This populates the fields with default JDBC driver information.

      The database settings for Oracle are:

      • Driver: oracle.jdbc.driver.OracleDriver

      • Protocol: jdbc:

      • SubProtocol: oracle:

      • SubName: thin:@

      • Port: 1521

      • Flags: Not applicable for Oracle

      • Database Service Name: orcl. (The value for the SID is the same as assigned for the database instance.)

   7. Click Apply > Close.

Loading the Inventory Database as a Separate Oracle Instance

The following sections explain the steps for configuring and running multiple Oracle 8i database instances:

• Configuring and Running Multiple Oracle Database Instances on a NetWare Server
• Configuring and Running Multiple Oracle Database Instances on a Windows NT/2000 Server

Configuring and Running Multiple Oracle Database Instances on a NetWare Server

To configure and run multiple Oracle database instances:

1. Unload Oracle. At the database server prompt, enter oraunld.

2. Invoke the Net8 configuration utility. At the database server prompt, run easycfg.ncf to load the Net8 Easy configuration window.

3. Define a unique Oracle instance.

   1. Click Config > Listener > Database > Add.

   2. Assign values for Database Instance and Database Name in the Adding Instances Address window.

      For example, assign Database Instance=Indy and Database Name=mgmtdb. In this configuration, the database instance is zfs. You can specify any database instance name. The Database Domain field should be left blank.

   3. Click Accept > Save.

4. Configure the Listener for IPC. To run an Oracle system, the IPC and TCP addresses should be already be configured.

   1. Click Config > Listener > Address. Ensure that IPC and TCP addresses are configured for the server.

      The setting for IPC is servername_LSNR, and TCP is IPaddress or hostname. If these settings exist, click Cancel. Otherwise, assign the values for these settings > click Save.

5. Create an IPC alias.

   1. Click Config > Database Alias. The window will list the aliases for IPC, SPX, TCP, and others. Click Add to add an alias name for the new instance.

      Enter the following details:

      • Database Alias: servername-databaseinstance-IPC. For example, the database alias is austr, where austr is the server name, and indy is the database instance created earlier.

      • Protocol: IPC

      • Service/Host Name or Key Name: server_name_LSNR

      • Database Instance: Indy

   2. Click Accept > Save.

   3. To verify the configured alias name in the list window: Click Config > Database Alias > select the newly created alias > click View.

      View the properties of the database alias. Ensure that the properties are correct. If the property settings are incorrect, delete the alias (click Delete) and repeat Step 5.

6. Exit the EasyCfg tool. Click Config > Exit.

7. Create a password file for logging as Internal user for this instance. Enter load orapwd81 file=oracle_volume:oracle_home\database\pwddatabase_instance.ora password=password entries=2 where oracle_volume is the NetWare volume name of your Oracle installation, PWDdatabase_instance.ORA is the password file name, and password is any password that you specify.

   For example, load orapwd81 file=oracle:\orahome1\database\pwdindy.ora password=mgmtdb entries=2. This password file will be created in the oracle_volume:\DATABASE directory. Ensure that the file exists in the directory.

8. Load the Oracle NLM^TM software. At the database server prompt, enter oraload.

9. To set the newly created ZfS instance, load the Oracle Server Manager. At the database server prompt, enter svrmgr31.

10. Enter the following commands: set instance servername-databaseinstance. For example, set instance austr-indy-ipc. This displays that the newly created instance is started.

11. Enter connect internal/password where password is the password created in Step 7.

12. Mount the Inventory database.

13. Modify the _START.SQL file located in Volumepath\ZENWORKS. Enter the following lines in the file:

    set instance servername-databaseinstance-IPC

    shutdown normal

14. Create the Database object. In ConsoleOne, right-click a location in the tree for the Database object > click New > Object > ZENworks Database > OK.

15. Type a name for the Database object > click OK

16. Configure the Database server options of the Database object. For more information, see Step 3 in Manually Creating the Inventory Database Object for Oracle

If you are loading multiple databases in separate Oracle instances, then each database reserves a separate Oracle SGA memory, where Oracle keeps all the database resources. In such environments, you should increase the amount of memory on the server. Refer to the documentation provided by Oracle.

Configuring and Running Multiple Oracle Database Instances on a Windows NT/2000 Server

To configure and run Oracle instances:

1. At the database server, run the Oracle Database Configuration Assistant. From the desktop Start menu, click Programs > Oracle > Database Administration > Oracle Database Configuration Assistant.

2. Click Create a Database > Next > Typical > Next > Copy Existing Database Files from the CD > Next.

3. Enter the following details:

   • Global Database Alias: mgmtdb.your_windows_NT/2000_name

   • SID: The value is automatically filled as mgmtdb.

4. Click Finish.

   This allows for Oracle database creation. This process takes a significant amount of time. Ensure that the OracleServiceMGMTDB service is created and started.

5. Load the Inventory database.

   Run the Oracle Server Manager. From the desktop menu, click Start > Run > SVRMGRL. Enter the following commands:

   set instance mgmtdb

   connect internal/password_for_administrator

Optimizing the Performance of the Oracle Database

If you have an Inventory database on Oracle, you can improve the performance of the database when you generate the inventory reports or query the database.

You use the database buffer cache to store the most recently used data blocks. The database cache is determined as DB_BLOCK_BUFFERS * DB_BLOCK_SIZE. These parameters are specified in the INIT.ORA file in the ZENWORKS\DATABASE directory on the database server.

DB_BLOCK_BUFFERS specifies the number of database buffers. DB_BLOCK_SIZE specifies the size of each database buffer in bytes.

The size of each buffer in the buffer cache is equal to the size of the data block.

Oracle recommends that the database buffer cache for any Online Transaction Processing Application (OLTP) should have a hit ratio of about 90%, which is optimal.

The ZfS Inventory database on Oracle has an approximate 88% hit ratio with a database cache size of 24 MB for 128 MB RAM, which is about 20% of total memory.

If there is additional memory, you configure the database cache size by increasing the DB_BLOCK_BUFFERS parameter in the INIT.ORA file.

Backing Up the Inventory Database Running Oracle

To back up the database running Oracle:

1. If the database server is up, stop the Storer service. At the database server console, enter StopSer Storer.

2. Load the Oracle Server Manager.

   On NetWare server with Oracle 8i, enter svrmgr31.

   On Windows NT/2000 server with Oracle 8i Enterprise Edition, from the taskbar, click Start > Run > enter svrmgrl.

3. Enter the following commands:

   set instance databaservername-databaseinstance-IPC, where databaseinstance refers to the database instance that you have set up earlier. See Loading the Inventory Database as a Separate Oracle Instance .

   For example, set instance austr-zfs3-ipc.

4. Connect as an administrator. For example, if the administrator's internal name is internal, at the Server Manager prompt, enter connect internal/password.

   where password is the password created earlier. See Loading the Inventory Database as a Separate Oracle Instance .

   1. At the Server Manager prompt, enter select name from v$datafile;

      This displays the list of the data files that Server Inventory uses.

5. Ensure that no other databases are mounted. At the prompt, enter shutdown normal.

6. Disconnect and exit from the Server Manager. At the Server Manager prompt, enter disconnect;

   Enter exit;

7. Copy the complete SCHEMA directory to a backup volume or disk.

After the backup is done, ensure that the backup copy of the database matches the original copy. Perform database verification to verify the integrity of the backup.

To verify the database integrity on a NetWare server with Oracle 8i, enter load DBV81.NLM FILE=path_to_the_database_file BLOCKSIZE=4096

To verify the database integrity on a Windows NT/2000 server with Oracle 8i, enter DBV.EXE FILE=path_to_the_database_file BLOCKSIZE=4096

Example: enter DBV.EXE FILE=c:\schema\database\cim1.ora BLOCKSIZE=4096

Also, run this command for the following files: CIM1.ORA, CIM2.ORA, CIM3.ORA, CIM4.ORA, CIM5.ORA, CIM6.ORA, CIM7.ORA, CIM8.ORA, CIM9.ORA, CIM10.ORA, CIM11.ORA, SYS1.ORA, and CTL1.ORA.

If the database backup is successful, ensure that there are no error messages on the verified pages. Ensure that the following displayed parameters display a zero value: TOTAL PAGES FAILING (DATA)=0, TOTAL PAGES FAILING (INDEX)=0, and TOTAL PAGES MARKED CORRUPT=0.

To restore the database:

1. If the Inventory database server is up, stop the Storer service. At the database server console, enter StopSer Storer.

2. Load the Oracle Server Manager.

   On a NetWare server with Oracle 8i, enter svrmgr31.

   On a Windows NT/2000 server with Oracle 8i Enterprise Edition, from the taskbar, click Start > Run > enter svrmgrl.

3. Connect as an administrator. For example, if the administrator's internal name is internal, at the Server Manager prompt, enter connect internal/password_for_administrator.

4. Ensure that no other databases are mounted. Enter shutdown normal.

5. Disconnect and exit from the Server Manager. At the Server Manager prompt, enter disconnect;

   Enter exit;

6. Copy the database from the backup location.

   If you copy the database to a different location than the earlier location, modify the location in the following files to specify the new path:

   • Edit the INIT.ORA file located in \ZFD3\ORACLE\DATABASE to specify the new path for the following parameters:

     control_files=location_of_CTLl.ORA\CTL1.ORA

     background_dump_dest=location_of_TRACE_dir\TRACE

     user_dump_dest=location_of_TRACE_dir\TRACE

   • Edit the _START.SQL file in the SYS:\SYSTEM to specify the location of INIT.ORA file in the following parameter:

     startup pfile=location_of_the_INIT.ORA\INIT.ORA

   • Modify the location in the ALTERCTRL.SQL to specify new path.

     For example, modify the existing DATA:\ZFD3\ORACLE\DATABASE path to ORACLE:\ZFD3\ORACLE\DATABASE in ALTERCTRL.SQL.

     In this .SQL file, modify the path for the following parameters, if required.

     startup nomount pfile=database_path\INIT.ORA

     logfile group 1 'database_path\log1.ora' size 256K,

     logfile group 2 'database_path\log2.ora' size 256K

     datafile 'database_path\sys1.ora',

     'database_path\rbs1.ora',

     'database_path\cim1.ora',

     'database_path\cim2.ora',

     'database_path\cim3.ora',

     'database_path\cim4.ora',

     'database_path\cim5.ora',

     'database_path\cim6.ora',

     'database_path\cim7.ora',

     'database_path\cim8.ora',

     'database_path\cim9.ora',

     'database_path\cim10.ora',

     'database_path\cim11.ora',

     'database_path\tmp1.ora'

     Save the changes.

7. Load the restored database.