Transcript
OpenLane® SLM 5.4 Oracle Database Administration Instructions Document Number 7800-A2-GZ46-10 April 2001
This document describes the use of commands and scripts provided with the OpenLane® Service Level Management system for Oracle database administration. It is divided into the following sections:
Migrating to 5.4. Shows how to update the schema of an Oracle database defined for an earlier release of OpenLane. Initializing the Database. Shows how to use user_params.sql, ol_init.sql, and dbconfig to set up a new Oracle performance statistics database for OpenLane. Migrating to an Oracle Database. Shows how to use dbconfig and dbcopy to create an Oracle database from a Cloudscape database. Periodic Maintenance. Shows how to check log files, and how to use dbage and ol_rbldidx.sql to reduce and tune a database.
Product Documentation on the World Wide Web Other documentation for this product is available at www.paradyne.com. Select Library → Technical Manuals → OpenLane Network Management Solutions. Document Number
Document Title
—
OpenLane SLM Online Help
7800-A2-GB30
OpenLane SLM Reports Reference Guide
7800-A2-GB31
OpenLane SLM Administrator’s Guide
Conventions Used In This Document In this document, the directory where OpenLane resides is shown as /opt/pdn/OpenLane for Solaris and C:\opt\pdn\openlane for Windows. This is the default and recommendation. Change the procedures appropriately if you have used a different pathname. The notation $ORACLE_HOME means the root directory of the Oracle software owner under Solaris, or the Oracle installation directory under Windows.
7800-A2-GZ46-10
April 2001
1
Migrating to 5.4 If you use an Oracle database for OpenLane 5.2 or 5.3, you must run a migration script to update the schema after OpenLane 5.4 is installed.
Procedure To update the database schema for OpenLane 5.4: 1. If you have not already done so, install OpenLane 5.4 as described in the OpenLane SLM Administrator’s Guide. 2. Locate the Oracle scripts on the OpenLane management server. They reside in the following directories:
Solaris: /opt/pdn/OpenLane/data/sql/scripts/Oracle/Init /opt/pdn/OpenLane/data/sql/scripts/Oracle/Maintenance Windows: \opt\pdn\OpenLane\data\sql\scripts\Oracle\Init \opt\pdn\OpenLane\data\sql\scripts\Oracle\Maintenance 3. Copy all the scripts in the Init and Maintenance directories to $ORACLE_HOME. For example (under Solaris): cd /opt/pdn/OpenLane/data/sql/scripts/Oracle cp Init/*.* $ORACLE_HOME cp Maintenance/*.* $ORACLE_HOME 4. If OpenLane is running, stop it. 5. Start SQL*Plus. 6. Execute the appropriate script for your OpenLane release level: — oracle5.2to5.4.sql (for 5.2), or — oracle5.3.Xto5.4.sql (for 5.3.x) 7. Restart OpenLane.
2
April 2001
7800-A2-GZ46-10
Initializing the Database Scripts that initialize an Oracle database are provided with OpenLane. The ol_init.sql script initializes an Oracle database by defining tablespaces, tables, files, indexes, and a user according to values set in user_params.sql. Before you can run ol_init.sql, you must edit user_params.sql to set the size and locations of the directories.
Database Size First determine the anticipated size of your database based upon the size of your network:
Tiny – Up to 25 devices and 75 PVCs Small – Up to 250 devices and 750 PVCs Medium – Up to 750 devices and 2,250 PVCs Large – Up to 5,000 devices and 15,000 PVCs
The ol_init.sql script contains the following statements: @tiny -- @small -- @medium -- @large In the procedure described in Preparing the Initialization Scripts on page 5 you will remove the comment symbols (two hyphens and a space) from the line with the appropriate size, and ensure that the other lines have comment symbols.
Directory Definitions Following the Directory Size Table in ol_init.sql is a list of directory definitions. By default all directories are based on the location of the tempDir directory, so for a one-disk database, olOraDataPath (the pathname used by tempDir) is the only definition you must supply. However, locating files on separate disks provides better performance and easier recovery. If you use multiple disks, distribute the data according to these recommendations (in order of importance):
Separate the temporary space (tempDir) from other files. Separate the rollback disk (rbackDir) from data and index disks for better performance and easier recovery from a single disk failure. Separate a data disk from its associated index disk for best performance. (For example, put chanDataDir on a different disk than chanIndexDir.)
Spreading files across different directories on the same disk does not help performance.
7800-A2-GZ46-10
April 2001
3
This is a four-disk directory definition that adheres to all three recommendations: define define define define define define define define define define define define define define define
tempDir = ’/disk1/openlane/’ rbackDir = ‘/disk2/openlane/ custDir = ‘/disk3/openlane/’ ifaceDataDir = ‘/disk3/openlane/’ ifaceIndexDir = ‘/disk4/openlane/’ portDataMinDir = ‘/disk3/openlane/’ portIndexMinDir = ‘/disk4/openlane/’ portDataHrDir = ‘/disk4/openlane/’ portIndexHrDir = ‘/disk3/openlane/’ chanDataMinDir = ‘/disk4/openlane/’ chanIndexMinDir = ‘/disk3/openlane/’ chanDataHrDir = ‘/disk3/openlane/’ chanIndexHrDir = ‘/disk4/openlane/’ chanDataDayDir = ‘/disk4/openlane/’ chanIndexDayDir = ‘/disk3/openlane/’
The determination of how many disks to use may be affected by the size of the database. Table 1 shows the disk space required for each directory and network size. Table 1. Disk Space Required for 100 Days’ Data Directory
Tiny
Small
Medium
Large
tempDir
5.0 MB
20.0 MB
50.0 MB
100.0 MB
rbackDir
20.0 MB
50.0 MB
100.0 MB
200.0 MB
custDir
0.7 MB
0.7 MB
0.7 MB
0.7 MB
ifaceDataDir
0.1 MB
0.3 MB
1 MB
7 MB
ifaceIndexDir
0.1 MB
0.3 MB
0.9 MB
6 MB
portDataMinDir
38 MB
380 MB
1132 MB
7549 MB
portIndexMinDir
5 MB
46 MB
137 MB
915 MB
chanDataMinDir
68 MB
508 MB
1523 MB
10156 MB
chanIndexMinDir
8 MB
55 MB
165 MB
1100 MB
chanDataHrDir
242 MB
1814 MB
5500 MB
36274 MB
chanIndexHrDir
27 MB
196 MB
588 MB
3920 MB
chanDataDayDir
4 MB
28 MB
83 MB
541 MB
chanIndexDayDir
1 MB
6 MB
25 MB
91 MB
572 MB (0.6 GB)
4705 MB (5 GB)
13910 MB (14 GB)
91120 MB (92 GB)
Total:
If you will maintain a substantially greater number of days’ data than 100 in your database, you must change the calculations in tiny.sql, small.sql, medium.sql, or large.sql accordingly. Instructions can be found in those scripts. You must also adjust the directory sizes in Table 1. Verify that you have sufficient disk space on all disks before you run ol_init.sql.
4
April 2001
7800-A2-GZ46-10
Preparing the Initialization Scripts Procedure To prepare to run the database initialization scripts: 1. If you have not already done so, install the current version of OpenLane. 2. Determine the number of devices in your network. A device is defined as an SNMP-manageable CSU, DSU, or DSL port. 3. Determine how many disks you will use to accommodate the database. 4. Locate the Oracle scripts on the OpenLane management server. They reside in the following directories:
Solaris: /opt/pdn/OpenLane/data/sql/scripts/Oracle/Init /opt/pdn/OpenLane/data/sql/scripts/Oracle/Maintenance Windows: \opt\pdn\OpenLane\data\sql\scripts\Oracle\Init \opt\pdn\OpenLane\data\sql\scripts\Oracle\Maintenance 5. Copy all the scripts in the Init and Maintenance directories to $ORACLE_HOME. For example (under Solaris): cd /opt/pdn/OpenLane/data/sql/scripts/Oracle cp Init/*.* $ORACLE_HOME cp Maintenance/*.* $ORACLE_HOME 6. Open for edit the script user_params.sql in $ORACLE_HOME. The user_params.sql script defines the size of your database and the location of its directories. 7. Locate the size parameters starting at or near line 25. There are four sizes: Tiny – up to 25 devices and 75 PVCs Small – up to 250 devices and 750 PVCs Medium – up to 750 devices and 2250 PVCs Large – up to 5,000 devices and 15,000 PVCs The default is Tiny. If your network exceeds 25 devices or 75 PVCs, add comment symbols (two hyphens and a space) to @tiny and remove them from the appropriate database size. For example, to initialize a Small database the statements in user_params.sql should like like this: -- @tiny @small -- @medium -- @large
7800-A2-GZ46-10
April 2001
5
8. Locate the directory definitions (immediately following the Directory Size Table). For a one-disk database, you need define only the global variable &olOraDataPath. For example:
Solaris: define olOraDataPath = ‘/opt/oracle/oradata/openlane’ Windows: define olOraDataPath = ‘D:\orant\oradata\openlane’ By default the Solaris define command is active and the Windows define command is rendered as a comment. Verify that you have the appropriate statement active. 9. Create the directory or directories you specified in Step 8 and give Oracle permission to write to the directory of directories. For example: mkdir /opt/oracle/oradata/openlane chown oracle:dba opt/oracle/oradata/openlane chmod 777 opt/oracle/oradata/openlane 10. Verify that you have adequate space (5 GB for the Small database of this example) on the target disk. (See Table 1, Disk Space Required for 100 Days’ Data.) 11. If you will maintain more than 100 days’ data, edit and change the values in tiny.sql, small.sql, medium.sql, or large.sql, as appropriate, to reflect a commensurate amount of extra space.
Running the Initialization Scripts Procedure To initialize the Oracle database for OpenLane: 1. Log in to the Oracle server as oracle. 2. Start SQL*Plus and log in as sys. At the SQL> prompt, enter the following command: @user_params 3. When user_params is finished, enter the following command: @ol_init 4. When initialization is complete, check for errors by viewing any files with a .txt file extension created in $ORACLE_HOME after you entered the ol_init command.
6
April 2001
7800-A2-GZ46-10
Running dbconfig The dbconfig function of OLCmd creates the naming/directory service entry that points to the Oracle performance statistics database. The command requires the following information from your Oracle Database Administrator: Tablespace Fields
Tablespace Field Values
Oracle Server IP Address or Name Oracle Listener TCP Port Oracle SID Database Login Database Password
The following values are used in the example below. When you run dbconfig, substitute your real information from the previous worksheet. Tablespace Fields
Tablespace Field Values Example
Oracle Server IP Address or Name
merlin.paradyne.com
Oracle Listener TCP Port
1521
Oracle SID
OLDB
Database Login
pdyn
Database Password
pdyn
For detailed information about dbconfig, see the OpenLane SLM Administrator’s Guide.
Procedure To configure the Oracle database in the naming/directory service database: 1. Edit the appropriate OLControlConfig file in the OpenLane home directory:
Solaris: /opt/pdn/OpenLane/OLControlConfig Windows: \opt\pdn\OpenLane\OLControlConfig.bat
7800-A2-GZ46-10
April 2001
7
2. Change the entry DATABASE=1 to DATABASE=0 as shown below. Example of the OLControlConfig file (Solaris): OL_HOME=/opt/pdn/OpenLane APACHE=/opt/apache/bin JRE_HOME=/bin/../java/bin/../bin SLEEP=10 DOMAIN=default READER_DOMAIN=default PARAMS= RMI=2099 LDAP=3890 DATABASE=1 ← Change to 0 WEBSERVER=1 MGMTSVCS=1 UHPOLLER=1 UHREADER=1 SNMPPOLLER=0 SCMPOLLER=1 SCHEDRPTS=0 DIRSVCS=1 3. Start OpenLane. 4. Run the dbconfig function of OLCmd to configure the OpenLane system to use the Oracle database. The OLCmd (Solaris) or OLCmd.bat (Windows) file is located in:
Solaris: /opt/pdn/OpenLane Windows: \opt\pdn\OpenLane OLCmd dbconfig options: Command
Option
–d
Database type (c=Cloudscape; o=Oracle)
–m
Host running the Oracle database plus port number
–n
Oracle SID
–u
Database user login name
–p
Database user password
— Solaris dbconfig command example: OLCmd dbconfig ’-d o -m merlin.paradyne.com:1521 -n OLDB -u pdyn -p pdyn’ — Windows dbconfig command example: OLCmd dbconfig ”-d o -m merlin.paradyne.com:1521 -n OLDB -u pdyn -p pdyn”
8
April 2001
7800-A2-GZ46-10
5. Run the dbconfig function of OLCmd with the ’–l’ list option (Solaris) or “–l” list option (Windows) option to verify your entries. The following is an example of the output after dbconfig is run and the file has been updated by the directory services process. The DefaultStatsDB instance entry should resemble the following: Database Instance: DefaultStatsDB rawUrl: jdbc:oracle:thin:@$host$:$dbName$ host: merlin.paradyne.com:1521 dbname: OLDB user: pdyn password: pdyn driver: oracle.jdbc.driver.OracleDriver dupKeyErrorCode: 1 statMapName: defaultStatMap calcMapName: defaultCalcMap rawSqlName: oracleRawSql oidMapName: defaultOidMap statsFeedClass: pdn.uhpoller.feed.UHSqlStatsFeed statsAgeClass: pdn.uhpoller.age.UHSqlStatsAge roll class: pdn.uhpoller.rollup.UHSqlRollup roll offset: 172800000 6. Check connectivity to the Oracle database by configuring a customer profile and running a Network Service report. While no data will be available yet, the report should be produced without error and show the customer’s name.
7800-A2-GZ46-10
April 2001
9
Migrating to an Oracle Database OpenLane’s integrated CloudScape database can accommodate up to 180 PVCs (360 DLCIs). If your network exceeds that size when OpenLane is installed, you must use an Oracle database. If your network grows to that size after you have used OpenLane, you must migrate to an Oracle database. To migrate from CloudScape to an Oracle database:
Procedure 1. Open a Console window (Solaris) or MS-DOS prompt (Windows) and change to the OpenLane home directory. 2. Stop the User History Reader. The User History Poller can continue to run so that no data is lost: OLControl stop UHReader 3. Run the dbage function of OLCmd. (See the OpenLane SLM Administrator’s Guide for more information about dbage.) This will minimize the amount of data that must be copied: OLCmd dbage 4. Initialize an Oracle database as described in Migrating to 5.4 on page 2. 5. Run the dbconfig function of OLCmd to create the database instance: OLCmd dbconfig ’-a OracleStatsDB -m merlin.paradyne.com:1521 -n OLDB -u pdyn -p pdyn’ Double quotation marks must be used under Windows. 6. Run the dbcopy function of OLCmd to copy data from the old database instance to the new: OLCmd dbcopy d OracleStatsDB See the OpenLane SLM Administrator’s Guide for more information about dbcopy. 7. Run the dbconfig function of OLCmd to make the new database the default. For example: OLCmd dbconfig ‘-d o -m merlin.paradyne.com:1521 -n OLDB -u pdyn -p pdyn’ Double quotation marks must be used under Windows. 8. Shut down and restart OpenLane.
10
April 2001
7800-A2-GZ46-10
Periodic Maintenance The following maintenance should be performed at a frequency determined by the database size and activity:
Check log file – The Oracle log file may be the only place database problems are recorded. Delete old records – The dbage command deletes records older than a specified age, keeping the database at a manageable size. Rebuild the indexes – The ol_rbldidx.sql script rebuilds the database indexes for efficiency.
Checking Log Files Presuming an SID of OLDB, the Oracle log file is located at:
Solaris: $ORACLE_HOME/admin/OLDB/bdump/alert_OLDB.log Windows: $ORACLE_HOME\Admin\oldb\bdump\oldbALRT.LOG The log file shows the history of activity on the database. Not all Oracle errors are reported to OpenLane, so the Oracle log may be the only place certain database problems are recorded. The log file should be scanned frequently for errors. To avoid looking at the same log entries repeatedly, it is a good practice to rename the log file after it has been viewed. Oracle automatically creates a new log file with the standard name. For example, to rename the file under Solaris, you might enter the commands: cd $ORACLE_HOME/admin/OLDB/bdump mv alert_OLDB.log alert_OLDB_13may01.log Oracle will subsequently create a new alert_OLDB.log file.
Deleting Older Records from the Database When the dbage function is run periodically, it should be run twice: once to delete records for nonexistent conections with the -dd (dead data) option, and once to force the deletion of outdated records. For example, to delete dead data records and then delete report and raw data records according to device definitions, enter the commands: OLCmd dbage -n OracleStatsDB -dd OLCmd dbage -n OraclStatsDB -d The dbage function can be automated using cron under Solaris, or at under Windows. See Rebuilding the Indexes on page 12 for a cron example. For a complete description of the dbage command, see the OpenLane SLM Administrator’s Guide.
7800-A2-GZ46-10
April 2001
11
Rebuilding the Indexes As records are added to and deleted from the database, indexes may become fragmented and index trees may increase in height, slowing access to the database. The ol_rbldidx.sql script rebuilds all the indexes for the performance statistics database to make it more efficient. The ol_rbldidx.sql script uses ALTER INDEX REBUILD statements. This permits read access to the database while the indexes are being rebuilt. Frequency Indexes should be rebuilt weekly, or, if less frequently, as often as practicable.
Procedure To rebuild the database indexes: 1. Move /opt/pdn/OpenLane/data/sql/scripts/Oracle/Maintenance/ol_rbldidx.sql to the $ORACLE_HOME directory if you have not done so before. 2. Start SQL*Plus and connect to the OpenLane database. 3. At the SQL> prompt, start ol_rbldidx.sql with the following command: @ol_rbldidx The following procedure shows how ol_rbldidx can be automated using cron.
Procedure To rebuild the indexes of the database named OpenLane every Sunday morning starting at 12:15 am on a Solaris system: 1. Create a shell script: ORACLE_HOME=/usr/oracle; export ORACLE_HOME; ORACLE_SID=oldb; export ORACLE_SID; /usr/oracle/bin/sqlplus my_username/my_password @ol_rbldidx For this example, presume the script is saved as /OL_Scripts/rebuild_ix. 2. Create or a modify a crontab file with the entry: 15 0 * * sunday “/OL_Scripts/rebuild_ix“ For this example, presume the script is saved as /OL_Scripts/my_cronfile. 3. Submit the crontab file to cron: crontab /OL_Scripts/my_cronfile
12
April 2001
7800-A2-GZ46-10
Warranty, Sales, Service, and Training Information Contact your local sales representative, service representative, or distributor directly for any help needed. For additional information concerning warranty, sales, service, repair, installation, documentation, training, distributor locations, or Paradyne worldwide office locations, use one of the following methods:
Internet: Visit the Paradyne World Wide Web site at www.paradyne.com. Telephone: Call our automated system to receive current information by fax or to speak with a company representative. — Within the U.S.A., call 1-800-870-2221 — Outside the U.S.A., call 1-727-530-2340
Document Feedback We welcome your comments and suggestions about this document. Please mail them to Technical Publications, Paradyne Corporation, 8545 126th Ave. N., Largo, FL 33773, or send e-mail to
[email protected]. Include the number and title of this document in your correspondence. Please include your name and phone number if you are willing to provide additional information.
Trademarks OpenLane is a registered trademark of Paradyne Corporation. All other products and services mentioned herein are the trademarks, service marks, registered trademarks, or registered service marks of their respective owners.
Copyright © 2001 Paradyne Corporation. Printed in U.S.A.
7800-A2-GZ46-10
April 2001
13
14
April 2001
7800-A2-GZ46-10
