Database Utilities

Return to menu

The following table lists the database utilities that are supported in Primo.

Supported Database Utilities

Number	Utility Name	Refer to
O/1	Oracle Server	Oracle Server (Util O/1)
O/1/1	Activate Oracle Server	Activate Oracle Server (Util O/1/1)
O/1/2	Close Oracle Server	Close Oracle Server (Util O/1/2)
O/1/3	Show Running Oracle Server	Show Running Oracle Server (Util O/1/3)
O/1/4	Show Oracle Server Status	Show Oracle Server Status (Util O/1/4)
O/2	Oracle Listener	Oracle Listener (Util O/2)
O/2/1	Activate Oracle Listener	Activate Oracle Listener (Util O/2/1)
O/2/2	Close Oracle Listener	Close Oracle Listener (Util O/2/2)
O/2/3	Show Running Oracle Listener	Show Running Oracle Listener (Util O/2/3)
O/2/4	Show Listener Status	Show Listener Status (Util O/2/4)
O/2/5	Show Listener Services	Show Listener Services (Util O/2/5)
O/3	Oracle Logs	Oracle Logs (Util O/3)
O/3/1	View Oracle ALERT LOG	View Oracle ALERT LOG (Util O/3/1)
O/6	NLS	NLS (Util O/6)
O/6/1	Show NLS Parameters	Show NLS Parameters (Util O/6/1)
O/7	Archiving	Archiving (Util O/7)
O/7/1	Turning Archiving On	Turning Archiving On (Util O/7/1)
O/7/2	Turning Archiving Off	Turning Archiving Off (Util O/7/2)
O/7/3	Show Archiving Status	Show Archiving Status (Util O/7/3)
O/9	Database Users	Database Users (Util O/9)
O/9/1	List Database Users	List Database Users (Util O/9/1)
O/9/2	Create a New User	Create a New User (Util O/9/2)
O/11	Oracle user password utility	Oracle User Password Utility (Util O/11)
O/13	Database Files	Database Files (Util O/13)
O/13/1	List of Database Files	List of Database Files (Util O/13/1)
O/13/2	Resize Oracle Datafile	Resize Oracle Datafile (Util O/13/2)
O/13/3	Add File to Tablespace	Add File to Tablespace (Util O/13/3)
O/13/4	Show Datafile Free Blocks by Kbytes	Show Datafile Free Blocks by Kbytes (Util O/13/4)
O/13/5	Show Datafile Free Blocks by BlockID	Show Datafile Free Blocks by BlockID (Util O/13/5)
O/14	Database Free/Used Space	Database Free/Used Space (Util O/14)
O/14/1	All Tablespaces Free Space Summary	All Tablespaces Free Space Summary (Util O/14/1)
O/14/2	Number of Free Extents by Size in a Tablespace	Number of Free Extents by Size in a Tablespace (Util O/14/2)
O/14/3	All Free Extents of Min Size in a Tablespace	All Free Extents of Min Size in a Tablespace (Util O/14/3)
O/14/4	Space Used by a Repository/Repositories in Each Tablespace	Space Used by a Repository/Repositories in Each Tablespace (Util O/14/4)
O/14/5	Space Used by a Group of Repositories in Each Tablespace	Space Used by a Group of Repositories in Each Tablespace (Util O/14/5)
O/14/6	Coalesce Contiguous Free Extents	Coalesce Contiguous Free Extents (Util O/14/6)
O/14/8	Primo Tablespaces Total/Free/Used Space Report	Primo Tablespaces Total/Free/Used Space Report (Util O/14/8)
O/14/9	Clean Temporary Tablespace Free Storage	Clean Temporary Tablespace Free Storage (Util O/14/9)
O/14/10	Space Used by PRM00 Schema	Space Used by a PRM00 Schema (Util O/14/10)
O/17	Database Tablespaces	Database Tablespaces (Util O/17)
O/17/1	Create a Tablespace	Create a Tablespace (Util O/17/1)
O/17/2	List Tablespace Files	List Tablespace Files (Util O/17/2)
O/17/4	Show Tablespaces Definition	Show Tablespaces Definition (Util O/17/4)
O/17/5	Show Tablespace Allocated/Free/Used Space	Show Tablespace Allocated/Free/Used Space (Util O/17/5)
O/18	Oracle Statistics	Oracle Statistics (Util O/18)
O/18/1	Performance Statistics	Performance Statistics (Util O/18/1)
O/18/2	Rollback Segments Definitions	Rollback Segments Definitions (Util O/18/2)
O/18/3	Rollback Segments Dynamic Allocation	Rollback Segments Dynamic Allocation (Util O/18/3)
O/18/4	View Long Operations	View Long Operations (Util O/18/4)
O/18/5	I/O Statistics	IO Statistics (Util O/18/5)
O/18/6	Sort Operations	Sort Operations (Util O/18/6)
O/19	Shared Pool	Shared Pool (Util O/19)
O/19/1	Show SGA Buffers	Show SGA Buffers (Util O/19/1)
O/19/2	Flush Shared Pool	Flush Shared Pool (Util O/19/2)
O/20	Multi Threaded Server	Multi Threaded Server (Util O/20)
O/20/1	Show MTS Parameters	Show MTS Parameters (Util O/20/1)
O/20/2	Show Listener Services	Show Listener Services (Util O/20/2)

Oracle Server (Util O/1)

The Oracle Server menu allows you to manage the Oracle server.

1. Log on to a Primo server (BE, FE, or SE) with the primo user.

   If you are using a dedicated Oracle server, you must log on to a server that has a Primo component (BE, FE, or SE) installed in order to access Util O.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 1 to display the Oracle Server menu.

   O.1 Oracle Server

   ---------------

   0. Exit Procedure

   1. Activate Oracle Server

   2. Close Oracle Server

   3. Show Running Oracle Server

   4. Show Oracle Server Status

   Oracle Server Menu

Activate Oracle Server (Util O/1/1)

In order for Primo to interact with Oracle, the Oracle server must be running. When Primo is installed on the same server as the Primo database, it does not require the Listener. However, the Listener must run on the server if a third-party product is used to connect Primo and the database or if there is a remote server connected to the database. For example, when Primo is installed on one server and the database is on a different server, the Listener must be running on the database server in order for Primo to operate correctly.

Primo and Oracle may start automatically at boot time (if this option is set during installation).

1. Enter option 1 from the Oracle Server menu.

   The following prompt displays:

   To continue you will need to enter PRIMO DBA username/password.

   Username/password: primo_dba/<primo_dba password=""/>

2. Enter the PRIMO_DBA user name and password to activate the Oracle server.

   The PRIMO_DBA user, which is created during installation, is provided with database administration privileges that allow you to start up or shut down the database.

Close Oracle Server (Util O/1/2)

This utility shuts down the Oracle server immediately by activating the Oracle shutdown immediate option. All the clients connected to the server are immediately logged out.

1. Enter option 2 from the Oracle Server menu. To access this menu, see Oracle Server (Util O/1).

   The following prompt displays:

   Do you want to restart Oracle server after closing? yes/[no]

2. Enter yes to shut down and the restart the server automatically.

   The following prompt displays:

   To close Oracle server enter PRIMO DBA username/password.

   username/password:primo_dba/<primo_dba password=""/>

   To restart the server later, see Activate Oracle Server (Util O/1/1).

3. Enter the PRIMO_DBA user name and password to activate the Oracle server.

   The PRIMO_DBA user, which is created during installation, is provided with database administration privileges that allow you to start up or shut down the database.

Show Running Oracle Server (Util O/1/3)

This utility displays the background processes and the dispatchers and shared servers that are used by your Oracle instance (database).

To display Oracle processes, enter option 3 from the Oracle Server menu.

The running processes for your Oracle instance displays. For example:

Show Oracle Server Status (Util O/1/4)

This utility displays the status of the Oracle server.

To display the status of the Oracle server, enter option 4 from the Oracle Server menu.

The following is an example of the output of the Util O/1/4 Show Oracle Server Status utility:

Oracle Listener (Util O/2)

The Oracle Listener menu allows you to manage the Oracle Listener.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 2 to display the Oracle Listener menu.

Activate Oracle Listener (Util O/2/1)

When a user process makes a connection request using a connect string, the Oracle Listener process examines the request and connects the user process to a server process. If Oracle and Primo are installed on the same server and no third-party products are used to connect to the database and no connections are made from a remote server, Primo can work without the Listener. In any other case, both the Oracle server and the Oracle Listener must be running. They may be started automatically at boot time (this is determined during installation) and also controlled by the Primo Oracle Management utilities.

1. Enter option 1 from the Oracle Listener menu.

   The following prompt displays:

   To continue you will need to enter Oracle's password.

   Password:

2. Enter the Oracle password to start up the Oracle Listener.

Close Oracle Listener (Util O/2/2)

This utility shuts down the Oracle Listener immediately. You have an option to restart the listener automatically.

1. Enter option 2 from the Oracle Listener menu.

   The following prompt displays:

   Do you want to restart Oracle Listener after closing? yes/[no]

2. Enter yes to shut down and then restart the Listener automatically.

   The following prompt displays:

   To restart Oracle Listener enter oracle's password.

   Password:

   To start the Listener later, see Activate Oracle Listener (Util O/2/1).

3. Enter the Oracle password to restart the Oracle Listener.

Show Running Oracle Listener (Util O/2/3)

This utility displays the active Oracle Listener. To display the active Oracle Listener, enter option 3 from the Oracle Listener menu.

The following example shows the results of this utility:

Show Listener Status (Util O/2/4)

This utility displays the status of the Oracle Listener.

To display the status of the Oracle Listener, enter option 4 from the Oracle Listener menu.

The following example shows the results of this utility:

Show Listener Services (Util O/2/5)

This utility displays the Oracle Listener services. To display the Oracle Listener services, enter option 5 from the Oracle Listener menu.

The following example shows the results of this utility:

Oracle Logs (Util O/3)

The Oracle Logs menu allows you to view the Oracle log file.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 3 to display the Oracle Logs menu.

View Oracle ALERT LOG (Util O/3/1)

This utility displays the latest entries to the Oracle alert log. By default, the last 100 lines are displayed.

1. Enter option 1 from the Oracle Logs menu.

   The following prompt displays:

   The Database Contains the Following Files:

   Enter number of lines to see from log : [100]

2. Enter the number of lines to display from the log.

   The following example displays the last 15 lines of the log:

   Fri Jun 26 00:47:00 2009

   SMON: enabling cache recovery

   Fri Jun 26 00:47:03 2009

   Successfully onlined Undo Tablespace 1.

   Fri Jun 26 00:47:03 2009

   SMON: enabling tx recovery

   Fri Jun 26 00:47:04 2009

   Database Characterset is UTF8

   Opening with Resource Manager plan: SYSTEM_PLAN

   where NUMA PG = 1, CPUs = 2

   replication_dependency_tracking turned off (no async multimaster replication found)

   Starting background process QMNC

   QMNC started with pid=14, OS id=15328

   Fri Jun 26 00:47:12 2009

   Completed: ALTER DATABASE OPEN

   Enter CR to continue...

3. Type enter to return to the Oracle Logs menu.

NLS (Util O/6)

The NLS menu allows you to view the NLS (National Language Support) parameters.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 6 to display the NLS menu.

Show NLS Parameters (Util O/6/1)

Primo uses the UTF8 character set. This utility shows the NLS (National Language Support) definition of the database.

1. Enter option 1 from the NLS menu.

   For example, the utility displays the settings of the parameters:

   PARAMETER VALUE

   ===========================================================

   NLS_LANGUAGE AMERICAN

   NLS_TERRITORY AMERICA

   NLS_CURRENCY $

   NLS_ISO_CURRENCY AMERICA

   NLS_NUMERIC_CHARACTERS .,

   NLS_CALENDAR GREGORIAN

   NLS_DATE_FORMAT DD-MON-RR

   NLS_DATE_LANGUAGE AMERICAN

   NLS_CHARACTERSET UTF8

   NLS_SORT BINARY

   NLS_TIME_FORMAT HH.MI.SSXFF AM

   NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

   NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

   NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

   NLS_DUAL_CURRENCY $

   NLS_NCHAR_CHARACTERSET UTF8

   NLS_COMP BINARY

   NLS_LENGTH_SEMANTICS BYTE

   NLS_NCHAR_CONV_EXCP FALSE

   19 rows selected.

   Enter CR to continue...

2. Type enter to return to the NLS menu.

Archiving (Util O/7)

Primo backup and recovery procedures are based on Oracle. In order to provide complete recovery of data up to the time of failure, the Oracle database mode must be archivelog mode. This ensures full recovery up to the time of failure. Hot backup cannot be performed without first enabling archivelog mode of the database.

If the database is in noarchivelog mode, only cold backups can be performed. A cold backup restores data to the time the last backup was performed. This means that all changes from the time of failure are lost.

Ex Libris highly recommends archivelog mode because in this mode, both cold and hot backups can be used to recover the database right up to the time of the failure. To ensure a complete recovery, all the archive files that were generated from the time of the backup (hot or cold) until the time of failure must be available.

Refer to the Oracle backup manual for more information on the preliminary actions that are required before using Util O/7.

The Archiving menu allows you to manage archiving.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 7 to display the Archiving menu.

Turning Archiving On (Util O/7/1)

When you run your database in ARCHIVELOG mode, you enable the archiving of the redo log.

1. Stop Primo processes (servers and batch procedures) using the startup scripts described in Starting and Stopping the System.

2. From the Archiving menu, enter option 1 to turn on archiving.

3. At the following prompt, enter the PRIMO_DBA user name/password:

   To continue you will need to enter PRIMO_DBA username/password.

   username/password:

4. Restart Primo using the startup scripts described in Starting and Stopping the System.

Turning Archiving Off (Util O/7/2)

When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log.

1. Stop Primo processes (servers and batch procedures) using the startup scripts, as described in Starting and Stopping the System.

2. From the Archiving menu, enter option 2 to turn off archiving.

3. At the following prompt, enter the PRIMO_DBA user name/password:

   To continue you will need to enter PRIMO_DBA username/password.

   username/password:

4. Restart Primo using the startup scripts described in Starting and Stopping the System.

Show Archiving Status (Util O/7/3)

This utility displays the current archiving status of Oracle.

To view the archiving status, enter option 3 from the Archiving menu to view the status. For example, the following output displays if archiving is off:

In a production database, always set the Database log mode to Archive mode.

Database Users (Util O/9)

The Database Users menu allows you to view and create database users.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 9 to display the Database Users menu.

List Database Users (Util O/9/1)

This utility lists all of the users in the database. To list the database users, enter option 1 from the Database Users menu. For example:

Create a New User (Util O/9/2)

This utility creates a new user with a default password that is the same as the user name.

1. Enter option 2 from the Database Users menu.

2. At the following prompt, enter the new user name:

   Enter User Name to Create New User:

3. At the following prompt, enter yes to continue:

   enter yes to create oracle user <new_user/>

4. At the following prompt, enter no to reconfirm:

   default password is P21_<new_user/>

   if user <new_user/> exists all data will be erased!!!

   enter no to reconfirm

5. At the following prompt, type enter to return to the Database Users menu:

   source create_ora_user_b P21_<new_user/>

   create_ora_user_b P21_<new_user/>

   SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 06:46:35 2009

   Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

   Enter user-name:

   Connected to:

   Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

   With the Partitioning, OLAP, Data Mining and Real Application Testing options

   SQL> EXIT

   Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

   With the Partitioning, OLAP, Data Mining and Real Application Testing options

   SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 06:46:35 2009

   Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

   Enter user-name:

   Connected to:

   Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

   With the Partitioning, OLAP, Data Mining and Real Application Testing options

   SQL> old 1: DROP USER &&1 CASCADE

   new 1: DROP USER P21_<new_user/> CASCADE

   DROP USER P21_<new_user/> CASCADE

   *

   ERROR at line 1:

   ORA-01918: user 'P21_<new_user/>' does not exist

   User created.

   Grant succeeded.

   Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

   With the Partitioning, OLAP, Data Mining and Real Application Testing options

   Enter CR to continue...

Oracle User Password Utility (Util O/11)

This utility allows you to change the passwords for the following Oracle users:

• PRIMO_ADMIN

• PRIMO_DBA

• P<r><n/></r>_PRM00

• P<r><n/></r>_PRM00_SHARED

• P<r><n/></r>_RPT00

• PRIMO_BACKUP

• PRIMO

1. Shut down all Primo servers (BE, FE, and SE).

2. From the BE server, enter the following commands to display the Managing Oracle menu:

   dlib prm00
   util o

3. Enter option 11 to change an Oracle user's password.

4. At the following prompt, enter the user name to update:

   Change Oracle user password.

   The script must be executed on all Primo servers.

   The Primo software must be down before the script execution.

   Enter user name (PRIMO_ADMIN,PRIMO_DBA,P21_PRM00):

5. At the following prompt, enter the new password:

   Password input must be identical in all Primo servers.

   Please, enter new password. Password may contain letters, numbers and _ (underscore).

6. At the following prompt, enter the PRIMO_DBA user name/password. Otherwise, type enter to exit.

   If you want to update this password in Oracle

   Enter PRIMO_DBA user/password i.e. PRIMO_DBA/PRIMO_DBA , or press [Enter] to exit :

   This step is omitted for the FE and SE servers because the password was changed previously on the BE server.

7. At the following prompt, type enter to return to the Managing Oracle menu.

   Change password in Oracle

   Changing DB password in BE global.properties file...

   Changing DB password in FE global.properties file...

   Changing DB password in SE global.properties file...

   Running set globals...Please wait...

   Enter CR to continue...

8. Repeat steps 2 through 7 for the FE and SE servers.

   When changing the password for the PRIMO_ADMIN and PRIMO_DBA users, the system does not apply changes to the global.properties files.

9. Start all Primo servers (BE, FE, and SE).

Database Files (Util O/13)

The Database Files menu contains utilities that allow you to manage database files.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 13 to display the Database Files menu.

List of Database Files (Util O/13/1)

This utility lists the Oracle data files and their sizes. To list the database files, enter option 1 from the Database Files menu. For example:

Resize Oracle Datafile (Util O/13/2)

This utility allows you to enlarge or reduce the size of an Oracle data file.

1. Enter option 2 from the Database Files menu.

2. At the following prompt, enter the PRIMO_DBA user name/password:

   To resize a database file enter PRIMO_DBA username/password.

   username/password:

3. At the following prompt, enter the name of the tablespace:

   Enter Tablespace name:

4. At the following prompt, enter the file name (including the full path) that you want to resize:

   Enter file name to resize:

5. At the following prompt, enter the new file size:

   Enter new file size (MB):

6. At the following prompt, enter y to continue:

   confirm (y/[n]):

7. At the following prompt, type enter to continue:

   resizing

   SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 00:19:27 2009

   Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

   idle> Connected.

   idle> idle>

   Database altered.

   idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

   With the Partitioning, OLAP, Data Mining and Real Application Testing options

   Enter CR to continue...

Add File to Tablespace (Util O/13/3)

Tablespaces are composed of one or more data files. This utility allows you to add files to tablespaces. If a tablespace does not have enough free space for these files, it needs to be expanded. For information on expanding tablespaces, see Database Tablespaces (Util O/17).

1. Enter option 3 from the Database Files menu.

2. At the following prompt, enter the PRIMO_DBA user name/password:

   To add a file to a tablespace enter PRIMO_DBA username/password.

   username/password:

3. At the following prompt, enter the name of the tablespace:

   Enter Tablespace name:

4. At the following prompt, enter the new file name (including the full path):

   Enter new file name:

5. At the following prompt, enter the file size:

   Enter file size (MB):

6. At the following prompt, enter y to continue:

   confirm (y/[n]):

7. At the following prompt, type enter to continue:

   SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 00:44:50 2009

   Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

   idle> Connected.

   idle> idle>

   Tablespace altered.

   idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

   With the Partitioning, OLAP, Data Mining and Real Application Testing options

   Enter CR to continue...

Show Datafile Free Blocks by Kbytes (Util O/13/4)

This utility displays the free data file blocks. To display the free data file blocks, enter option 4 to display from the Database Files menu. For example:

Show Datafile Free Blocks by BlockID (Util O/13/5)

This utility displays the free data file blocks per tablespace.

1. Enter option 5 from the Database Files menu.

2. At the following prompt, enter the name of the tablespace:

   Tablespace Name:

3. At the following prompt, specify the datafile number of the tablespace:

   Datafile Number:

   To determine the datafile number, see List of Database Files (Util O/13/1).

4. At the following prompt, type enter to return to the Database Files menu:

   Enter value for ts: old 4: where TABLESPACE_NAME = '&&ts'

   new 4: where TABLESPACE_NAME = 'TS_P_DAT'

   Enter value for fl_no: old 5: and FILE_ID = &&fl_no

   new 5: and FILE_ID = 9

   BLOCK_ID BYTES

   ---------- ----------

   33149737 93782016

   33068329 665845760

   33013801 445644800

   32946601 550502400

   32847913 807403520

   32786729 500170752

   32761001 208666624

   32760361 3145728

   32758697 2097152

   32758441 1048576

   32758185 1048576

   Enter CR to continue...

   List of Free Data Blocks by Block ID

Database Free/Used Space (Util O/14)

The Database Free/Used Space menu allows you to display information about the free and used space in the tablespace.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 14 to display the Database Free/Used Space menu.

All Tablespaces Free Space Summary (Util O/14/1)

This utility provides details about free space in the Oracle DBA_FREE_SPACE table. It generates a report with the following four columns:

• TABLESPACE_NAME: The name of the tablespace.

• TOTAL_FREE_SPACE: The total amount of free space in the tablespace (in megabytes).

• MAX_EXTENT: The size of the largest contiguous extent of the tablespace (in megabytes).

• NUM_FREE_EXTENTS: The number of free extents in the tablespace.

If a tablespace has no free space left, it will not appear in the report.

To display a summary of the free space for tablespaces, enter option 1 from the Database Free/Used Space menu. For example:

Number of Free Extents by Size in a Tablespace (Util O/14/2)

This utility lists the number of extents of a certain size (truncated in megabytes) in the tablespace.

1. Enter option 2 from the Database Free/Used Space menu

2. At the following prompt, enter the tablespace name:

   Enter Tablespace name:

3. At the following prompt, type enter to return to the Database Free/Used Space menu:

   SIZE IN MB NUM OF EXTENTS

   ---------- --------------

   3970 28

   3944 1

   3737 1

   3496 1

   3445 1

   3388 1

   3257 1

   3170 1

   3098 1

   3084 1

   3063 1

   Enter CR to continue...

   List of Free Extents

All Free Extents of Min Size in a Tablespace (Util O/14/3)

This utility lists the exact size (in megabytes) of all free extents that are larger than a given size. You are prompted for the tablespace name and the minimum size (in megabytes) that you wish to investigate.

1. Enter option 3 from the Database Free/Used Space menu.

2. At the following prompt, enter the tablespace name:

   Enter Tablespace name:

3. At the following prompt, enter the minimum size of the extents to display. Enter to 0 to display all of the extents.

   Enter Min size (MB) of free extent [0=ALL]:

4. At the following prompt, type enter to return to the Database Free/Used Space menu:

   EXTENT_SIZE

   -----------

   3970.18089

   3970.18089

   3970.18089

   3970.18089

   3970.18089

   3970.18089

   Enter CR to continue...

Space Used by a Repository/Repositories in Each Tablespace (Util O/14/4)

This utility lists the amount of space that each repository occupies in each tablespace. If a truncated name is used, all of the repositories starting with the given characters are listed, and the occupied space is listed for each one of them.

1. Enter option 4 from the Database Free/Used Space menu.

2. At the following prompt, enter the unit name (such as prm or prm00):

   Enter unit name (full or truncated, e.g. primo):

3. At the following prompt, type enter to return to the Database Free/Used Space menu:

   OWNER TABLESPACE_NAME SIZE_MB

   -------------------------- --------------------------- -----------

   P21_PRM00 TS_P_DAT 8008.27408

   Enter CR to continue...

Space Used by a Group of Repositories in Each Tablespace (Util O/14/5)

This utility lists the total amount of space that all of the repositories whose names start with the given characters occupy in each tablespace.

1. Enter option 5 from the Database Free/Used Space menu.

2. At the following prompt, enter the unit name (such as prm):

   Enter first 3 characters of unit code (e.g. primo):

3. At the following prompt, type enter to return to the Database Free/Used Space menu:

   TABLESPACE_NAME SIZE_MB

   ------------------------------ ----------

   TS_P_DAT 8008.27408

   Enter CR to continue...

Coalesce Contiguous Free Extents (Util O/14/6)

This utility is not required when using a locally managed tablespace. It is retained for backward compatibility (and will be removed in future versions).

Free space in a database may be composed of extents of various sizes. It is worthwhile to use this procedure to coalesce the contiguous free extents to create larger free extents. Perform this procedure periodically.

1. Enter option 6 from the Database Free/Used Space menu.

2. At the following prompt, enter the PRIMO_DBA user name and password:

   To Coalesce Tablespaces enter PRIMO_DBA username/password.

   username/password:

3. At the following prompt, type enter to return to the Database Free/Used Space menu:

   SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 20:37:29 2009

   Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

   Connected to:

   Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

   With the Partitioning, OLAP, Data Mining and Real Application Testing options

   SQL> alter tablespace LOG coalesce;

   alter tablespace SYSAUX coalesce;

   alter tablespace SYSTEM coalesce;

   alter tablespace TS0 coalesce;

   alter tablespace TS1 coalesce;

   alter tablespace TSLOB coalesce;

   alter tablespace TS_P_DAT coalesce;

   alter tablespace TS_P_IDX coalesce;

   alter tablespace USERS coalesce;

   Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

   With the Partitioning, OLAP, Data Mining and Real Application Testing options

   SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 20:37:29 2009

   Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

   SQL> Connected.

   SQL> SQL>

   'ALTERTABLESPACE'||TABLESPACE_NAME||'COALESCE;'

   -----------------------------------------------------------

   alter tablespace LOG coalesce;

   alter tablespace SYSAUX coalesce;

   alter tablespace SYSTEM coalesce;

   alter tablespace TS0 coalesce;

   alter tablespace TS1 coalesce;

   alter tablespace TSLOB coalesce;

   alter tablespace TS_P_DAT coalesce;

   alter tablespace TS_P_IDX coalesce;

   alter tablespace USERS coalesce;

   9 rows selected.

   Tablespace altered.

   Tablespace altered.

   SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

   With the Partitioning, OLAP, Data Mining and Real Application Testing options

   Enter CR to continue...

Primo Tablespaces Total/Free/Used Space Report (Util O/14/8)

This utility lists the total space that each repository occupies in each tablespace and the amount of free space in the table. To display the total space each repository occupies, enter option 8 from the Database Free/Used Space menu.

For example:

Clean Temporary Tablespace Free Storage (Util O/14/9)

In some cases, the temporary tablespace does not free non-used pages quickly enough. This utility manually frees these pages.

1. Enter option 9 from the Database Free/Used Space menu.

2. At the following prompt, enter the name of the temporary tablespace:

   To Free Temporary Segments Enter Temporary Tablespace:

3. At the following prompt, enter the PRIMO_DBA user name and password:

   Enter PRIMO_DBA username/password:

4. At the following prompt, type enter to return to the Database Free/Used Space menu:

   QL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 12:52:41 2009

   Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

   idle> Connected.

   idle>

   Tablespace altered.

   idle> idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

   With the Partitioning, OLAP, Data Mining and Real Application Testing options

   Enter CR to continue...

Space Used by a PRM00 Schema (Util O/14/10)

This utility displays the space used by the PRM00 schema. To display the space used by the PRM00 schema, enter option 10 from the Database Free/Used Space menu. For example:

Database Tablespaces (Util O/17)

The Database Tablespace menu allows you to manage tablespaces.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 17 to display the Database Tablespaces menu.

Create a Tablespace (Util O/17/1)

Typically, all the required tablespaces are created during system installation. This utility allows you to create additional tablespaces.

1. Enter option 1 from the Database Tablespaces menu.

2. At the following prompt, enter the PRIMO_DBA user name and password:

   To Create a new Tablespace, Enter PRIMO_DBA username/password.

   username/password:

3. At the following prompt, enter the name of the new tablespace:

   Enter Tablespace name:

4. At the following prompt, enter the new file name (include full path):

   Enter new file name (full path) :

5. At the following prompt, enter the size of the file:

   Enter new file size (MB):

6. At the following prompt, enter the allocation type (AUTO or UNIFORM):

   ==============================================================

   Tablespaces can be created with a UNIFORM size for all extents

   or with allocation type AUTOALLOCATE which means

   Oracle will decide how to define extents

   Util o 17 4 can be used to see current definitions

   for existing tablespaces

   ==============================================================

   Tablespace Allocation Type : [AUTO/UNIFORM]

7. If you have selected UNIFORM, enter the uniform size of each extent:

   UNIFORM SIZE : [128K/1M/4M/128M/1920M]

8. Athe following prompt, enter y to confirm:

   Tablespace: TEST1

   File: /exlibris/oradata/prm0/test1_01.dbf

   File size: 100MB

   Allocation : UNIFORM SIZE 4M

   confirm (y/[n]):

9. At the following prompt, enter y to continue:

   CREATE TABLESPACE TEST1

   DATAFILE '/exlibris/oradata/prm0/test1_01.dbf' SIZE 100M

   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M

   SEGMENT SPACE MANAGEMENT AUTO

   ONLINE;

   Enter CR to continue...

10. At the following prompt, enter y to continue:

    SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 15:13:43 2009

    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    idle> Connected.

    idle> idle> 2 3 4 5

    Tablespace created.

    idle> idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    Enter CR to continue...

11. At the following prompt, type enter to return to the Database Tablespaces menu:

    SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 15:13:43 2009

    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    idle> Connected.

    idle> idle> 2 3 4 5

    Tablespace created.

    idle> idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    Enter CR to continue...

List Tablespace Files (Util O/17/2)

This utility lists the tablespaces in the database.

1. Enter option 2 from the Database Tablespaces menu.

2. At the following prompt, enter the name of the tablespace:

   Enter Tablespace name:

3. At the following prompt, type enter to return to the Database Tablespaces menu:

   Tablespace TS_P_DAT consist of the following files:

   NAME SIZE K F

   -------------------------------------------------- ---------- ---

   /exlibris/oradata/prm1/prm1_ts_p_dat_01.dbf 7340032 7

   Enter CR to continue...

Show Tablespaces Definition (Util O/17/4)

You may need to increase the space that certain database tables use. All tablespaces in Primo are created as locally-managed tablespaces. When working with locally-managed tablespaces, the word local appears in the EXT-MGMT (extent management) column.

When using locally-managed tablespaces, the following types of extent allocation appear in the ALLOC_TYP column:

• SYSTEM(Auto Allocate)—In auto allocation, Oracle automatically assumes full control, allocating extents as needed and taking into account the initial allocation of the table/index as configured in the create table/index command. For example, if initial allocation of the table/index is 1GB, Oracle might split the 1GB into 50 extents, a single extent, or any other combination of extents.

• UNIFORM—In uniform allocation, the DBA determines a standard size for all the extents in the tablespace. All the extents in the tablespace are of that size, without regard to the extent definitions in the Create Table command. The DBA determines which table is assigned to which tablespace depending on the table size. Hence, in uniform allocation, there is no fragmentation, and space utilization is optimal. For example, when creating a tablespace with a uniform extent size of 10 MB, five extents will be used for a table of 50 MB.

To display the tablespace definitions, enter option 4 from the Database Tablespaces menu.

In addition to the extent management types, this utility display the following information for each tablespace:

• Segment allocation

• Tablespace (for permanent or temporary objects or for undo segments)

• Tablespace status (online or offline)

Show Tablespace Allocated/Free/Used Space (Util O/17/5)

This utility displays the following parameters for a tablespace:

• Total tablespace size

• Amount of free space

• Amount of used space

1. Enter option 5 from the Database Tablespaces menu.

2. At the following prompt, enter the name of the tablespace:

   Enter Tablespace name :

3. At the following prompt, type enter to return to the Database Tablespaces menu:

   Tablespace TS_P_DAT :

   _____________________________

   TOTAL SIZE M

   ------------

   7168

   TOTAL FREE M

   ------------

   5597

   TOTAL USED M

   ------------

   1570

   Enter CR to continue...

Oracle Statistics (Util O/18)

The Oracles Statistics menu allows you to display Oracle statistics.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 18 to display the Oracle Statistics menu.

Performance Statistics (Util O/18/1)

This utility provides Oracle performance statistics for fine-tuning your database. To display the performance statistics, enter option 1 from the Oracle Statistics menu.

Rollback Segments Definitions (Util O/18/2)

This utility displays the rollback segments definitions.

1. Enter option 2 from the Oracle Statistics menu.

2. At the following prompt, type enter to continue:

   SYSTEM GLOBAL AREA (sga)

   BYTES

   734003200

   =======================================================

   BUFFER CACHE HIT RATIO (db_block_buffers)

   GETS MISSES RATIO

   4054336 225243 94.44%

   =======================================================

   STATISTIC (db_block, DBWR, sort_area)

   NAME VALUE

   opened cursors current 32

   db block gets 937066

   consistent gets 3117272

   physical reads 225243

   physical writes 92082

   DBWR checkpoints 37

   redo log space requests 0

   sorts (memory) 105305

   sorts (disk) 1

   Enter CR to continue...

3. At the following prompt, type enter to return to the Oracle Statistics menu:

   =======================================================

   DATA DICTIONARY CACHE (shared_pool_size)

   GETS MISSES RATIO

   1442213 22892 98.41%

   =======================================================

   LIBRARY CACHE (shared_pool_size)

   EXECUTIONS MISSES LIBCACHEPROZ

   552613 1160 99.79%

   Enter CR to continue...

Rollback Segments Dynamic Allocation (Util O/18/3)

This utility displays the rollback segments dynamic allocation. To display the rollback segments dynamic allocation, enter option 3 from the Oracle Statistics menu. For example:

View Long Operations (Util O/18/4)

This utility displays Oracle long operations, if they occur in the system at the time the utility is run. The following information displays:

• SID: session identifier.

• OPNAME: operation name.

• TARGET: the object on which the operation is being performed.

• DONE SO FAR: percentage of work already done.

1. Enter the following commands to display the Managing Oracle menu:

   dlib prm00
   util o

2. Enter option 18 to display the Oracle Statistics menu.

3. Enter option 4.

4. At the following prompt, type cntl-c to exit the display:

   Long Operation Currently running :

   To stop do Ctrl C

IO Statistics (Util O/18/5)

This utility displays the following information:

• BLOCK_GETS—block gets for this session.

• CONSISTENT_GETS—consistent gets for this session.

• PHYSICAL_READS—physical reads for this session.

• BLOCK_CHANGES—block changes for this session.

• CONSISTENT_CHANGES—consistent changes for this session.

To display I/O statistics, enter option 5 from the Oracle Statistics menu. For example:

Type cntl-c to exit the display.

Sort Operations (Util O/18/6)

This utility displays sort operations if they occur in the system when the utility is running. To display sort operations, enter option 6 from the Oracle Statistics menu. For example:

Type cntl-c to exit the display

Shared Pool (Util O/19)

The Shared Pool menu allows you to display information associated with shared pools.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 19 to display the Oracle Statistics menu.

Show SGA Buffers (Util O/19/1)

This utility lists the various SGA buffers. To display SGA buffers, enter option 1 from the Shared Pool menu. For example:

Flush Shared Pool (Util O/19/2)

This utility removes all of the Oracle objects from the shared pool.

1. Enter option 2 from the Shared Pool menu.

2. At the following prompt, enter the PRIMO_DBA user name and password:

   To continue you will need to enter PRIMO_DBA username/password.

   username/password:

Multi Threaded Server (Util O/20)

In a standard Oracle configuration, a separate server process is created on behalf of each user process. This is called a Dedicated Server Process (or Shadow process) because it acts only on behalf of the associated user process.

Oracle also supports the Shared Server Architecture (or Multi Threaded Server Architecture - MTS) in which there are several server processes, each serving several user processes.

In Primo, The MTS infrastructure exists in the database but is only implemented in special cases, and in coordination with Ex Libris. This group of utilities supports MTS.

The following menu allows you to manage these servers.

1. Log on to the server with the primo user.

2. Enter the following commands to display the Managing Oracle menu.

   dlib prm00
   util o

3. Enter option 20 to display the Multi Threaded Server menu.

Show MTS Parameters (Util O/20/1)

This utility lists the MTS parameters. To display the MTS parameters, enter option 1 from Multi Threaded Server menu. For example:

Show Listener Services (Util O/20/2)

This utility lists the MTS dispatchers per instance. To display the MTS dispatchers, enter option 2 from the Multi Threaded Server menu. For example:

Page tags: article:topicProd:Primocontype:documSystem Administration GuideManaging the Primo Databaseproduct:primocontent:Documentationlang:english