Preparing the Oracle External Database

This chapter describes how to configure an external Oracle database for use with Prime Network. Make sure you have the most recent version of the Oracle software documentation (see the Oracle Corporation website).

Using an External Database: General Guidelines

  • Installation and sizing:

–blank.gif The external Oracle server can be installed on the Prime Network gateway or on any other remote workstation.

–blank.gif Preallocate the database storage by creating all required data files to their full sizes in advance. With the exception of pnuser and pnuser _admin, Prime Network tablespace data files are generated in 1 GB sizes with autoextend set to 256 MB and no size limit. (The data file can grow to 32 GB.)

–blank.gif If the Oracle server is installed on the Prime Network gateway, no Oracle services can be installed on port 2100. If an Oracle listener is installed on port 2100, you must disable it or change the port number (see Table 4-2 ). By default, this port is used by the Oracle XML DB service.

–blank.gif The mount directory should be available in both the active and standby nodes. If the mount folder directory does not exists in the standby node, then the installation fails.

–blank.gif For deployment information and recommendations, contact your Cisco account representative.

  • Starting and stopping processes:

–blank.gif Prime Network does not manage the starting and stopping of Oracle processes for an external database. The database administrator is responsible for automatically restarting Oracle processes in the event of a power failure.

–blank.gif If your system is configured for gateway high availability, start and stop Prime Network and Oracle using CLI commands. Stopping the applications using the regular application commands (without the awareness of the cluster software) can cause a failover.

–blank.gif If you restart Oracle, you must also restart AVM 25 on both the gateway and units.

  • Usernames:

–blank.gif The Oracle user should be called oracle, and it should be part of a group called dba.

–blank.gif The database username and password that are related to the Prime Network application are created automatically during installation.

  • Clocks: In Prime Network, the clocks on the gateway and units must be synchronized. If Oracle is running on a separate workstation, that remote database workstation’s clock must be synchronized with the gateway and unit clocks.

Creating an External Oracle Database

The database instance installation can be performed as part of the Oracle installation or separately using the Oracle database configuration assistant (DBCA) utility. This sect ion describes how to create an Oracle database instance using the DBCA utility (in ORACLEHOME/bin).

Download the Oracle patches from http://metalink.oracle.com.

note.gif

Noteblank.gif After installing an Oracle patch, you must change the permissions for the newly installed files to enable all OS users to use Oracle on the workstation. You can do this by running the script ORACLEHOME\install\changePerm.sh.

The Prime Network database size is determined by the usage patterns and the expected load in your deployment. Contact your Cisco account representative for assistance in determining your database load profile and calculating your database memory and storage size requirements.

Prime Network provides sizing estimates for the following usage profiles, which vary according to the maximum expected rate of actionable events per second that your deployment can support:

  • Up to 250—Represents a high-scale production environment with a maximum supported rate of database operations, including up to 250 actionable events per second, a maximum amount of services, and the highest rate of configuration archive and provisioning operations.
  • Up to 200—Represents a high-scale production environment with a maximum supported rate of database operations, including up to 200 actionable events per second, a maximum amount of services, and the highest rate of configuration archive and provisioning operations.
  • Up to 100—Represents a high-scale production environment with a maximum supported rate of database operations, including up to 100 actionable events per second, a maximum amount of services, and the highest rate of configuration archive and provisioning operations.
  • Up to 50—Represents a medium- to high-scale production environment with a medium rate of database operations, including up to 50 actionable events per second, a medium amount of services, and a medium rate of configuration archive and provisioning operations.
  • Up to 20—Represents a medium- to small-scale production environment with a low rate of database operations, including up to 20 actionable events per second, a small amount of services, and a low rate of configuration archive and provisioning operations.
  • Up to 5—Represents a small environment with a low rate of database operations, including up to 5 actionable events per second, a small amount of services, and a low rate of configuration archive and provisioning operations.
  • Up to 1—Represents a very small test or proof-of-concept environment with a single machine acting as a gateway, unit, and database (or with a separate unit), with no more than 50 VNEs and a low rate of events.

When installing a database instance, use the values in Table 4-1 for the Oracle initialization parameters. The values are in bytes.

 

Table 4-1 Database Initialization Parameters

Profile Name
Up to 5 Actionable Events per Second
Up to 20 Actionable Events per Second
Up to 50 Actionable Events per Second
Up to 100 Actionable Events per Second
Up to 200-250 Actionable Events per Second

sga_max_size

4412407808

6509559808

6509559808

10301210624

10301210624

shared_pool_size

1258291200

2147483648

2147483648

2147483648

2147483648

large_pool_size

134217728

134217728

134217728

134217728

134217728

java_pool_size

218103808

335544320

335544320

335544320

335544320

pga_aggregate_target

1048576000

1887436800

1887436800

1887436800

1887436800

sga_target

0

0

0

0

0

memory_target

0

0

0

0

0

memory_max_target

0

0

0

0

0

db_cache_size

1048576000

2684354560

2684354560

2684354560

2684354560

db_keep_cache_size

318767104

872415232

872415232

3690987520

3690987520

db_recycle_cache_size

167772160

167772160

167772160

838860800

838860800

db_file_multiblock_read_count

16

16

16

16

16

open_cursors

2000

2000

2000

2000

2000

optimizer_index_cost_adj

10

10

10

10

10

optimizer_index_caching

50

50

50

50

50

For better performance, make sure you generate statistics for all tables in the database. Prime Network issues alerts if no statistics are generated, or if the current statistics are more than 2 weeks old.

note.gif

Note The pnuser_admin user is a user with database administrator permissions who can run maintenance tasks—such as gathering statistics—on the other Prime Network database schemas. After the pnuser_admin user is created, a cron job runs every 24 hours to gather statistics on the pnuser (Fault Database). You no longer have to gather statistics manually.

If you expect a high scale in the first 24 hours, it might be necessary to manually force statistics gathering twice during the first day, 1 and 5 hours after noise start. To force statistics gathering, enter the following command as pnuser :

cd $PRIME_NETWORK_HOME/Main/scripts;./call_update_ana_stats.pl >& /dev/null

 

Table 4-2 describes the steps involved in creating an Oracle 12c database using DBCA.
If you are using an Oracle 12c database, please refer to the Cisco Prime Network 5.2 Installation Guide.

 

Table 4-2 Creating an Oracle database Using DBCA

Procedure
Recommended Action

Step 1

In the Operations window, select the operation that you want to perform.

Choose Create a Database.

Step 2

Select a creation mode in the Creation Mode window.

Choose Advanced Mode.

Step 3

In the Database Template window, select the template.

Choose Custom Database.

Step 4

In the Database Identification window, enter the Global Database Name and SID field.

Enter mcdb. Do not check the Create As Container Database check box, and do not fill in any of the related data.

Note Do not modify the default Service, instead, you can add additional Service (s) along with the default service.

Step 5

In the Management Options window, configure the fields in Enterprise Manager as required.

Do not set any of the fields in Enterprise Manager and Automatic Maintenance task, and proceed to the next step.

Step 6

Enter the passwords for the Oracle administrative accounts

Enter passwords for SYS and SYSTEM users.

Step 7

In the Listener Selection window, check the relevant listener check box.

Enter the name and port.

Step 8

In Database File Locations window, specify the storage type and location for the database. Set recovery settings if you intend to configure a database backup policy.

1.blank.gif Select Storage Type as File System.

2.blank.gif For Storage Location, browse for the common location for all database files (Example: /export/home/oracle/oradata/mcdb).

3.blank.gif In Recovery Related Files, choose File System as the Storage Type.

4.blank.gif Check the Enable Archiving check box.

5.blank.gif Click Edit Archive Mode Parameters to set the location for archive logs.

Step 9

In the Database Options window, use the default options.

Make sure that the Oracle Label Security and Oracle Database Vault check boxes are not checked. The rest of the check boxes should be checked.

Step 10

In the Initialization Parameters window, configure the memory settings for the database.

1.blank.gif Select Custom.

2.blank.gif For the Memory Management field, select the Manual Shared Memory Management from the drop down.

3.blank.gif Configure other initialization parameters. Refer to Table 4-1 for the values of various database initialization parameters based on profiles.

4.blank.gif In the Sizing tab, change processes to 1000.

5.blank.gif In the Character Sets tab, choose your preferred character set (AL32UTF8 is recommended).

6.blank.gif In the Connection Mode tab, choose Dedicated Server Mode.

Step 11

In the Creation Options window, select the database creation options.

Choose Create Database and click Customize Storage Locations to configure redo log settings.

Contact your Cisco account representative for assistance in estimating the database size.

To support high event rates, redo log files must be six online 2 GB files on raw devices or on a dedicated disk partition mounted with the directio option. The redo log files must reside on a physical disk separate from the Oracle data files.

Configuring the External Database

This section includes details on configuring the database, such as the Oracle initialization parameters, ports, database size, and so forth.

Configuring the cursor_sharing System Parameter

The cursor_sharing system parameter must be set to FORCE. To configure the cursor_sharing system parameter:

Step 1blank.gif As the SYS user, enter the following command:

ALTER SYSTEM SET cursor_sharing='FORCE' SCOPE=BOTH;
 

Step 2blank.gif Enter the following SQL*PLUS command to verify that the parameter is set correctly:

SQL> show parameter cursor_sharing
 

In the command output, you should see:

NAME TYPE VALUE
----- ----- ------
cursor_sharing string FORCE
 

 

Retaining Partitioning Storage Behavior

To ensure that partitioning storage behavior is retained, ensure that the partitions are created with an extent size of 64 KB. To create partitions with extent size of 64 KB, set the hidden parameter (_partition_large_extents) to false.

Enter the following command as the SYS user:

ALTER SYSTEM SET "_partition_large_extents" =FALSE SCOPE=BOTH;

note.gif

Noteblank.gif The partitioning storage behavior is not retained if the partitions are created with the default extent size (8 MB).

Configuring the job_queue_processes System Parameter

The job_queue_processes parameter specifies the maximum number of processes that can be created for the execution of jobs. It must be set to 1000. To configure the job_queue_processes parameter:

Step 1blank.gif As the SYS user, enter the following command:

alter system set job_queue_processes=1000 scope=both;
 

Step 2blank.gif Enter the following SQL*PLUS command to verify that the parameter is set correctly:

SQL> show parameter job_queue_processes
 

In the command output, you should see:

NAME TYPE VALUE
----- ----- ------
job_queue_processes integer 1000
 

 

Configuring the audit_trail System Parameter

Disable Oracle auditing by setting the audit_trail system parameter to NONE. To configure the audit_trail system parameter:

Step 1blank.gif As the SYS user, enter the following command:

ALTER SYSTEM SET audit_trail=NONE SCOPE=spfile;
 

Step 2blank.gif As the SYS user, enter the following command to start the database:

Startup
 

Step 3blank.gif As the SYS user, enter the following SQL*PLUS command to verify that the parameter is set correctly:

SQL> show parameter audit_trail
 

In the command output, you should see:

NAME TYPE VALUE
----- ----- ------
audit_trail string NONE
 

 

Disabling the Recycle Bin Option

If enabled, the Oracle recycle bin feature retains a version of each dropped object, which can lead to an accumulation of junk information in the Prime Network DB Segments table. To disable the recycle bin option:

Step 1blank.gif As the SYS user, enter the following command:

ALTER SYSTEM SET recyclebin = OFF DEFERRED scope=both;
 

Step 2blank.gif Enter the following SQL*PLUS command to verify that the parameter has been disabled:

SQL> show parameter recyclebin
 

In the command output, you should see:

NAME TYPE VALUE
----- ----- ------
recyclebin string OFF DEFERRED
 

Step 3blank.gif (Optional) As pnuser, enter the following command to see the objects that are currently saved in the recycle bin:

show recyclebin
 

Step 4blank.gif (Optional) As pnuser, enter the following command to empty the recycle bin:

purge recyclebin;
 

 

Setting the open_cursors Parameter

Open cursors enable the reading and writing of data between the Oracle database and Cisco Prime Network. The open_cursors parameter defines the maximum number of cursors that can be opened concurrently, per session. The recommended maximum number of open cursors for use with Cisco Prime Network is 2000. An error is generated if the number of open cursors in a session exceeds the specified number.

To set the open_cursors parameter:

Step 1blank.gif To check the value of the open_cursors parameter, enter:

SQL> show parameter open_cursors
 

In the command output, you should see:

open_cursors integer 2000
 

Step 2blank.gif If the integer value is less than 2000, enter:

SQL> ALTER SYSTEM SET open_cursors = 2000 SCOPE=BOTH;
 

Step 3blank.gif To verify that the value has changed, enter:

SQL> show parameter open_cursors
 
note.gif

Noteblank.gif If the open_cursors integer value is still less than 2000, contact your local database administrator.

 

Disabling Automatic Maintenance Jobs

If you deploy Prime Network to handle a high event rate, it is recommended that you disable Oracle’s automatic maintenance jobs. Automatic maintenance significantly affects Oracle performance and increases event processing time.

caut.gif
Caution blank.gif These commands disable all scheduler maintenance activities. Complete the following procedure after implementing an alternative method of gathering database statistics. Some of the commands will fail in some versions of Oracle; you can ignore any failures.

Connect to the Oracle database as the SYS user and enter the following commands:

execute DBMS_SCHEDULER.disable (name => 'GATHER_STATS_PROG',force => TRUE);
execute DBMS_SCHEDULER.disable (name => 'AUTO_SPACE_ADVISOR_PROG',force => TRUE);
execute dbms_scheduler.disable(name =>'GATHER_STATS_JOB',force => TRUE);
execute dbms_scheduler.disable(name =>'BSLN_MAINTAIN_STATS_JOB',force => TRUE);
execute DBMS_SCHEDULER.disable(name => 'SYS.MAINTENANCE_WINDOW_GROUP', force => TRUE);
execute DBMS_SCHEDULER.disable(name => 'SYS.ORA$AT_WGRP_SA', force => TRUE);
execute DBMS_SCHEDULER.disable(name => 'SYS.ORA$AT_WGRP_SQ', force => TRUE);
execute DBMS_SCHEDULER.disable(name => 'SYS.ORA$AT_WGRP_OS', force => TRUE);
EXECUTE DBMS_SCHEDULER.disable (name =>'SYS.MONDAY_WINDOW', force => TRUE);
EXECUTE DBMS_SCHEDULER.disable (name =>'SYS.TUESDAY_WINDOW', force => TRUE);
EXECUTE DBMS_SCHEDULER.disable (name =>'SYS.WEDNESDAY_WINDOW', force => TRUE);
EXECUTE DBMS_SCHEDULER.disable (name =>'SYS.THURSDAY_WINDOW', force => TRUE);
EXECUTE DBMS_SCHEDULER.disable (name =>'SYS.FRIDAY_WINDOW', force => TRUE);
EXECUTE DBMS_SCHEDULER.disable (name =>'SYS.SATURDAY_WINDOW', force => TRUE);
EXECUTE DBMS_SCHEDULER.disable (name =>'SYS.SUNDAY_WINDOW', force => TRUE);
EXECUTE DBMS_SCHEDULER.disable (name =>'SYS.WEEKNIGHT_WINDOW', force => TRUE);
EXECUTE DBMS_SCHEDULER.disable (name =>'SYS.WEEKEND_WINDOW', force => TRUE);
 

Changing Database Ports

If Oracle is installed on the Prime Network gateway, the Oracle services will be installed on port 2100. If an Oracle listener was installed on port 2100, you must disable it or change the port number. By default, this port is used by the Oracle XML DB service.

note.gif

Noteblank.gif You must change the FTP port number if an Oracle listener was installed by default on port 2100.

Use this procedure to change the port numbers of the XML DB listeners, if required. This procedure applies only if you installed the Oracle XML DB service. If you disabled the Oracle XML DB service, skip this section.

Step 1blank.gif To log into Oracle SQL, enter:

sqlplus user/password
 

Step 2blank.gif To change the HTTP port from 8080 to 8083, enter:

sql> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()', 8083));
 

Step 3blank.gif To change the FTP port from 2100 to 2111, enter:

sql> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()', 2111));
 

Step 4blank.gif To commit the update, enter:

sql> COMMIT;
 

Step 5blank.gif To refresh the settings, enter:

sql> exec dbms_xdb.cfg_refresh
 

Step 6blank.gif To exit SQL Command Line, enter:

sql> exit
 

 

Configuring the Database Size and Disk Structure

The size of the stored data is determined mainly by the number of stored events. By default, Prime Network is configured to archive events for up to 14 days. The archive size, the supported event rates, and the average event size dictate the expected database growth on a daily basis. Events that are archived for a long time cause a significant load on the database and require additional disk space. You can change the default archive period using the Administration GUI, if necessary (see the Cisco Prime Network 5.2 Administrator Guide). Contact your Cisco account representative for assistance with sizing calculations.

Recommended Disk Structure

Following is the recommended disk structure for an Oracle server based on the number of disks that the server holds:

  • Oracle data files—The optimal location is an external disk array (preferably RAID 10).
  • Online redo log files—The optimal location is an internal disk partition. The redo log files should not reside on the same disk as the data files.
  • Archive files—Should not reside on the same disk as the data files.
  • Backup files—Should not reside on the same disk as the data files.

Configuring Oracle to Start Automatically When Prime Network Restarts

By default, the Oracle application does not start automatically when Prime Network is rebooted. This is because the best practice is for the system database administrator to manually start the database in a controlled environment. However, if you want Oracle to start when the system is rebooted, there are multiple ways to accomplish this task. The following is one example; see the Oracle documentation for other implementations.

As the Oracle UNIX root user, create a file in the /etc/rc2.d directory named S99OracleDB, with the following contents:

ORA_OWNER=oracle
DBLOG=$INSTALL_DIR/log/dbop.log
TZ=GMT
if [ -f /var/opt/oracle/oratab ]; then
orahome=`grep -v "^#" /var/opt/oracle/oratab | grep. | sed -ne '1p' | awk -F: '{print $2}'`
else
echo "/var/opt/oracle/oratab file doesn't exist. Please check if Oracle is installed " >> $DBLOG
echo "dbora $1 aborted...." >> $DBLOG
exit
fi
ORA_HOME=$orahome
TNS_ADMIN=$ORA_HOME/network/admin
if [ ! -f $ORA_HOME/bin/dbstart -o ! -d $ORA_HOME ]
then
echo "Oracle startup cannot start"
exit
fi
if [ ! -d $INSTALL_DIR/log ]; then
mkdir $INSTALL_DIR/log
chmod 777 $INSTALL_DIR/log
fi
if [ ! -f $DBLOG ]; then
touch $DBLOG
fi
 
#start the Oracle databases
echo "Invoking dbstart at `date` " >> $DBLOG
echo >> $DBLOG
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
echo "Invoking Listener start at `date`" >> $DBLOG
echo >> $DBLOG
su - $ORA_OWNER -c "lsnrctl start"
echo "Listener started." >> $DBLOG
echo >> $DBLOG
 

Preventing Passwords in the Default Profile from Expiring

When you create a database, passwords of users that belong to the default profile expire after 180 days. Because Prime Network database users receive the default profile, their database password will expire after 180 days. To prevent this from occurring, complete the following steps:

Step 1blank.gif Log into the Oracle SQL as the sysdba.

Step 2blank.gif Enter:

alter profile default limit PASSWORD_LIFE_TIME unlimited;
 

 

Maintaining the External Database

After installation, maintaining the database can involve:

note.gif

Noteblank.gif Refer to your Oracle documentation for instructions on how to back up the Oracle database.

Maintaining Archive Log File Disk Space

The large number of Prime Network updates to the database causes the size of the archive log to expand rapidly and consume a large amount of space on the disk partition. To maintain space on the disk partition, an Oracle database administrator should delete the archive log files periodically.

Adding Data Files to the Tablespace

After you install Prime Network and its database, you might need to add more data files, depending on the event rate per second. Here is an example showing how to add a data file to the existing tablespace:

alter tablespace tablespace-name add datafile 'new-data-file-full-path' size 32G autoextend off;
 

where tablespace-name is pnuser _TABLESPACE, dwe_TABLESPACE.

The size can be changed and is subject to actual needs and availability.

note.gif

Noteblank.gif With the exception of pnuser and pnuser_admin, Prime Network tablespace data files are generated in 1 GB sizes with autoextend set to 256 MB and no size limit. (The data file can grow to 32 GB.) It is recommended that you preallocate the database storage by creating all required data files to their full sizes in advance.