Database Utilities
The following table lists the database utilities that are supported in Primo.
Number | Utility Name | Refer to |
---|---|---|
O/1
|
Oracle Server
|
|
O/1/1
|
Activate Oracle Server
|
|
O/1/2
|
Close Oracle Server
|
|
O/1/3
|
Show Running Oracle Server
|
|
O/1/4
|
Show Oracle Server Status
|
|
O/2
|
Oracle Listener
|
|
O/2/1
|
Activate Oracle Listener
|
|
O/2/2
|
Close Oracle Listener
|
|
O/2/3
|
Show Running Oracle Listener
|
|
O/2/4
|
Show Listener Status
|
|
O/2/5
|
Show Listener Services
|
|
O/3
|
Oracle Logs
|
|
O/3/1
|
View Oracle ALERT LOG
|
|
O/6
|
NLS
|
|
O/6/1
|
Show NLS Parameters
|
|
O/7
|
Archiving
|
|
O/7/1
|
Turning Archiving On
|
|
O/7/2
|
Turning Archiving Off
|
|
O/7/3
|
Show Archiving Status
|
|
O/9
|
Database Users
|
|
O/9/1
|
List Database Users
|
|
O/9/2
|
Create a New User
|
|
O/11
|
Oracle user password utility
|
|
O/13
|
Database Files
|
|
O/13/1
|
List of Database Files
|
|
O/13/2
|
Resize Oracle Datafile
|
|
O/13/3
|
Add File to Tablespace
|
|
O/13/4
|
Show Datafile Free Blocks by Kbytes
|
|
O/13/5
|
Show Datafile Free Blocks by BlockID
|
|
O/14
|
Database Free/Used Space
|
|
O/14/1
|
All Tablespaces Free Space Summary
|
|
O/14/2
|
Number of Free Extents by Size in a Tablespace
|
|
O/14/3
|
All Free Extents of Min Size in a Tablespace
|
|
O/14/4
|
Space Used by a Repository/Repositories in Each Tablespace
|
|
O/14/5
|
Space Used by a Group of Repositories in Each Tablespace
|
|
O/14/6
|
Coalesce Contiguous Free Extents
|
|
O/14/8
|
Primo Tablespaces Total/Free/Used Space Report
|
|
O/14/9
|
Clean Temporary Tablespace Free Storage
|
|
O/14/10
|
Space Used by PRM00 Schema
|
|
O/17
|
Database Tablespaces
|
|
O/17/1
|
Create a Tablespace
|
|
O/17/2
|
List Tablespace Files
|
|
O/17/4
|
Show Tablespaces Definition
|
|
O/17/5
|
Show Tablespace Allocated/Free/Used Space
|
|
O/18
|
Oracle Statistics
|
|
O/18/1
|
Performance Statistics
|
|
O/18/2
|
Rollback Segments Definitions
|
|
O/18/3
|
Rollback Segments Dynamic Allocation
|
|
O/18/4
|
View Long Operations
|
|
O/18/5
|
I/O Statistics
|
|
O/18/6
|
Sort Operations
|
|
O/19
|
Shared Pool
|
|
O/19/1
|
Show SGA Buffers
|
|
O/19/2
|
Flush Shared Pool
|
|
O/20
|
Multi Threaded Server
|
|
O/20/1
|
Show MTS Parameters
|
|
O/20/2
|
Show Listener Services
|
Oracle Server (Util O/1)
The Oracle Server menu allows you to manage the Oracle server.
O. Managing ORACLE
------------------
0. Exit Procedure
1. Oracle Server
2. Oracle Listener
3. Oracle Logs
4. Resumable Space Allocation
6. Nls
7. Archiving
9. Database Users
10. SQL*Plus Session
11. Oracle user password utility
12. Database Verification Utility
13. Database Files
14. Database Free/Used Space
16. Database Links
17. Database Tablespaces
18. Oracle Statistics
19. Shared Pool
20. Multi Threaded Server
21. Create/Recreate reports(RPT00) schema
Please select [exit]:
Managing Oracle Menu
To access the Oracle Server menu:
-
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.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
Enter option 1 to display the Oracle Server menu.O.1 Oracle Server---------------0. Exit Procedure1. Activate Oracle Server2. Close Oracle Server3. Show Running Oracle Server4. Show Oracle Server StatusOracle 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).
-
This operation requires the PRIMO_DBA user name and password.
-
This utility will not be available if the Oracle database and the Primo application are installed on separate servers.
To start up the Oracle server:
-
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=""/>
-
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 operation requires the PRIMO_DBA user name and password.
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.
To shut down the Oracle server:
-
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]
-
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).
-
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).
If the utility fails to generate any output, activate the Oracle server (see Activate Oracle Server (Util O/1/1)).
To display Oracle processes, enter option 3 from the Oracle Server menu.
The running processes for your Oracle instance displays. For example:
oracle 22017 1 0 Jan01 ? 00:00:34 ora_pmon_prm1
oracle 22019 1 0 Jan01 ? 00:00:01 ora_psp0_prm1
oracle 22021 1 0 Jan01 ? 00:00:05 ora_mman_prm1
oracle 22024 1 0 Jan01 ? 00:36:37 ora_dbw0_prm1
oracle 22026 1 0 Jan01 ? 00:13:56 ora_lgwr_prm1
oracle 22028 1 0 Jan01 ? 00:02:23 ora_ckpt_prm1
oracle 22030 1 0 Jan01 ? 00:01:18 ora_smon_prm1
oracle 22032 1 0 Jan01 ? 00:00:01 ora_reco_prm1
oracle 22034 1 0 Jan01 ? 00:00:45 ora_cjq0_prm1
oracle 22044 1 0 Jan01 ? 00:00:48 ora_mmon_prm1
oracle 22069 1 0 Jan01 ? 00:00:41 ora_mmnl_prm1
oracle 22071 1 0 Jan01 ? 00:34:52 ora_d000_prm1
oracle 22073 1 0 Jan01 ? 00:43:00 ora_d001_prm1
oracle 22075 1 0 Jan01 ? 00:44:03 ora_d002_prm1
oracle 22077 1 0 Jan01 ? 00:42:52 ora_d003_prm1
oracle 22079 1 0 Jan01 ? 00:46:16 ora_d004_prm1
oracle 22081 1 0 Jan01 ? 00:42:26 ora_d005_prm1
oracle 22099 1 0 Jan01 ? 00:43:38 ora_d006_prm1
oracle 22101 1 0 Jan01 ? 00:43:22 ora_d007_prm1
oracle 22422 1 0 Jan01 ? 00:00:00 ora_qmnc_prm1
oracle 22662 1 0 Jan01 ? 00:00:00 ora_q000_prm1
oracle 22697 1 0 Jan01 ? 00:00:05 ora_q001_prm1
oracle 25373 1 28 Jan05 ? 13:23:52 ora_s000_prm1
oracle 29099 1 31 Jan05 ? 13:23:30 ora_s001_prm1
This utility is relevant only if you are running the Oracle server on the same node as the Primo server.
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:
INSTANCE_N HOST_NAME VERSION STARTUP_TI STATUS LOGINS
---------- --------------- ------------ ---------- ---------- ----------
prm1 il-primo05.corp 10.2.0.1.0 01-JAN-07 OPEN ALLOWED
.exlibrisgroup.
com
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Oracle Listener (Util O/2)
The Oracle Listener menu allows you to manage the Oracle Listener.
O.2 Oracle Listener
-----------------
0. Exit Procedure
1. Activate Oracle Listener
2. Close Oracle Listener
3. Show Running Oracle Listener
4. Show Listener Status
5. Show Listener Services
Oracle Listener Menu
This utility will not be available if the Oracle database and the Primo application are installed on separate servers.
To access the Oracle Listener menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
Enter option 2 to display the Oracle Listener menu.
Activate Oracle Listener (Util O/2/1)
This utility requires the Oracle software owner password.
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.
To start up the Oracle Listener:
-
Enter option 1 from the Oracle Listener menu.The following prompt displays:To continue you will need to enter Oracle's password.Password:
-
Enter the Oracle password to start up the Oracle Listener.
Close Oracle Listener (Util O/2/2)
This utility requires the Oracle software owner password.
This utility shuts down the Oracle Listener immediately. You have an option to restart the listener automatically.
To shut down the Oracle Listener:
-
Enter option 2 from the Oracle Listener menu.The following prompt displays:Do you want to restart Oracle Listener after closing? yes/[no]
-
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).
-
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:
oracle 5127 1 0 Jan01 ? 00:08:09 /exlibris/app/oracle/product/102/bin/tnslsnr LISTENER -inherit
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:
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 07-JAN-2007 14:06:58
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 01-JAN-2007 07:14:47
Uptime 6 days 6 hr. 52 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Log File /exlibris/app/oracle/product/102/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=1521)))
Services Summary...
Service "prm1.il-primo05.corp.exlibrisgroup.com" has 1 instance(s).
Instance "prm1", status READY, has 9 handler(s) for this service...
Service "prm1_XPT.il-primo05.corp.exlibrisgroup.com" has 1 instance(s).
Instance "prm1", status READY, has 9 handler(s) for this service...
The command completed successfully
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:
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 07-JAN-2007 14:07:33
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "prm1.il-primo05.corp.exlibrisgroup.com" has 1 instance(s).
Instance "prm1", status READY, has 9 handler(s) for this service...
Handler(s):
"DEDICATED" established:3 refused:0 state:ready
LOCAL SERVER
"D007" established:72296 refused:0 current:5 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22101>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22101.1))
"D006" established:71245 refused:0 current:5 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22099>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22099.1))
"D005" established:71779 refused:0 current:3 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22081>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22081.1))
"D004" established:72279 refused:0 current:2 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22079>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22079.1))
"D003" established:71468 refused:0 current:1 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22077>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38790))
"D002" established:72525 refused:0 current:0 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22075>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38789))
"D001" established:71704 refused:0 current:4 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22073>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38788))
"D000" established:60036 refused:0 current:1 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22071>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38787))
Service "prm1_XPT.il-primo05.corp.exlibrisgroup.com" has 1 instance(s).
Instance "prm1", status READY, has 9 handler(s) for this service...
Handler(s):
"DEDICATED" established:3 refused:0 state:ready
LOCAL SERVER
"D007" established:72296 refused:0 current:5 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22101>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22101.1))
"D006" established:71245 refused:0 current:5 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22099>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22099.1))
"D005" established:71779 refused:0 current:3 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22081>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22081.1))
"D004" established:72279 refused:0 current:2 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22079>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22079.1))
"D003" established:71468 refused:0 current:1 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22077>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38790))
"D002" established:72525 refused:0 current:0 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22075>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38789))
"D001" established:71704 refused:0 current:4 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22073>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38788))
"D000" established:60036 refused:0 current:1 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22071>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38787))
The command completed successfully
Services Summary...
Service "prm1.il-primo05.corp.exlibrisgroup.com" has 1 instance(s).
Instance "prm1", status READY, has 9 handler(s) for this service...
Handler(s):
"DEDICATED" established:3 refused:0 state:ready
LOCAL SERVER
"D007" established:72296 refused:0 current:5 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22101>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22101.1))
"D006" established:71245 refused:0 current:5 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22099>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22099.1))
"D005" established:71779 refused:0 current:3 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22081>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22081.1))
"D004" established:72279 refused:0 current:2 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22079>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22079.1))
"D003" established:71468 refused:0 current:1 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22077>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38790))
"D002" established:72525 refused:0 current:0 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22075>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38789))
"D001" established:71704 refused:0 current:4 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22073>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38788))
"D000" established:60036 refused:0 current:1 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22071>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38787))
Service "prm1_XPT.il-primo05.corp.exlibrisgroup.com" has 1 instance(s).
Instance "prm1", status READY, has 9 handler(s) for this service...
Handler(s):
"DEDICATED" established:3 refused:0 state:ready
LOCAL SERVER
"D007" established:72296 refused:0 current:5 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22101>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22101.1))
"D006" established:71245 refused:0 current:5 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22099>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22099.1))
"D005" established:71779 refused:0 current:3 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22081>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22081.1))
"D004" established:72279 refused:0 current:2 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22079>
(ADDRESS=(PROTOCOL=ipc)(KEY=#22079.1))
"D003" established:71468 refused:0 current:1 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22077>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38790))
"D002" established:72525 refused:0 current:0 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22075>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38789))
"D001" established:71704 refused:0 current:4 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22073>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38788))
"D000" established:60036 refused:0 current:1 max:972 state:ready
DISPATCHER <machine: il-primo05.corp.exlibrisgroup.com, pid: 22071>
(ADDRESS=(PROTOCOL=tcp)(HOST=il-primo05.corp.exlibrisgroup.com)(PORT=38787))
The command completed successfully
Oracle Logs (Util O/3)
The Oracle Logs menu allows you to view the Oracle log file.
O.3 Oracle Logs
---------------
0. Exit Procedure
1. View Oracle ALERT LOG
Oracle Logs Menu
To access the Oracle Logs menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
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.
To view the Oracle alert log:
-
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]
-
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 2009SMON: enabling cache recoveryFri Jun 26 00:47:03 2009Successfully onlined Undo Tablespace 1.Fri Jun 26 00:47:03 2009SMON: enabling tx recoveryFri Jun 26 00:47:04 2009Database Characterset is UTF8Opening with Resource Manager plan: SYSTEM_PLANwhere NUMA PG = 1, CPUs = 2replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCQMNC started with pid=14, OS id=15328Fri Jun 26 00:47:12 2009Completed: ALTER DATABASE OPENEnter CR to continue...
-
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.
O.6 NLS
---------------
0. Exit Procedure
1. Show NLS Parameters
NLS Menu
To access the NLS menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
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.
To view the NLS parameter settings:
-
Enter option 1 from the NLS menu.For example, the utility displays the settings of the parameters:PARAMETER VALUE===========================================================NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_CHARACTERSET UTF8NLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY $NLS_NCHAR_CHARACTERSET UTF8NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSE19 rows selected.Enter CR to continue...
-
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.
-
Changing the archiving mode shuts down the database and restarts it in archivelog mode.When running pipes (such as pipes set to No Harvesting - Update Data Source) that add or change a large amount of data, it is recommended that you stop Oracle archiving, as this slows down the process and fills up the disk. Immediately after the process is complete, perform a full cold backup and then turn archiving back on.
-
Always perform a full cold backup immediately after switching to archivelog mode. If you do not do this, there will be a gap in the archivelog files, which will prevent a full recovery.
The Archiving menu allows you to manage archiving.
O.7 Archiving
---------------
0. Exit Procedure
1. Turning Archiving On
2. Turning Archiving Off
3. Show Archiving Status
Archiving Menu
To access the Archiving menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
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.
-
Turning archiving on requires a Primo DBA user name and password.
-
Changing the archiving mode shuts down the database and restarts it in archivelog mode.
To turn on Oracle archiving mode:
-
Stop Primo processes (servers and batch procedures) using the startup scripts described in Starting and Stopping the System.
-
From the Archiving menu, enter option 1 to turn on archiving.
-
At the following prompt, enter the PRIMO_DBA user name/password:To continue you will need to enter PRIMO_DBA username/password.username/password:
-
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.
-
Requires Primo DBA user name and password.
-
Changing the archiving mode shuts down the database and restarts it with archivelog mode off.
To turn off Oracle archiving mode:
-
Stop Primo processes (servers and batch procedures) using the startup scripts, as described in Starting and Stopping the System.
-
From the Archiving menu, enter option 2 to turn off archiving.
-
At the following prompt, enter the PRIMO_DBA user name/password:To continue you will need to enter PRIMO_DBA username/password.username/password:
-
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.
You will need a Primo DBA user name and password to view the status.
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:
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 7 14:09:12 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
idle> Connected.
idle> idle> Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /exlibris/oradata/prm1/arch/
Oldest online log sequence 6717
Current log sequence 6721
idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options
When archiving is on, the following two lines are displayed, as follows:
SQL> SQL> Database log mode Archive Mode
Automatic archival Enabled
Archiving Status
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.
O.9. Database Users
-------------------
0. Exit Procedure
1. List Database Users
2. Create a New User
Please select [exit]:
Database Users Menu
To access the Database Users menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
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:
The Database prm1 Contains the Following Users:
======================================================
ANONYMOUS
CTXSYS
DBSNMP
DIP
DMSYS
EXFSYS
MDSYS
MGMT_VIEW
ORACLE_OCM
ORDPLUGINS
ORDSYS
OUTLN
P21_PRM00
P23_PRM00
PRIMO
PRIMO_ADMIN
PRIMO_BACKUP
PRIMO_DBA
SI_INFORMTN_SCHEMA
SYS
SYSMAN
SYSTEM
TSMSYS
WMSYS
XDB
Enter CR to continue...
List of Database Users
Some of the users are Primo collection users and others are administrative users.
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.
If the name of the user already exists, all the tables and data of that user are deleted. The user is created with empty tables.
To create a new database user:
-
Enter option 2 from the Database Users menu.
-
At the following prompt, enter the new user name:Enter User Name to Create New User:
-
At the following prompt, enter yes to continue:enter yes to create oracle user <new_user/>
-
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
-
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 2009Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Enter user-name:Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> EXITDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 06:46:35 2009Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Enter user-name:Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> old 1: DROP USER &&1 CASCADEnew 1: DROP USER P21_<new_user/> CASCADEDROP USER P21_<new_user/> CASCADE*ERROR at line 1:ORA-01918: user 'P21_<new_user/>' does not existUser created.Grant succeeded.Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsEnter 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
The password for each Oracle user must be identical on all Primo servers (BE, FE, and SE).
To change the password of an Oracle user:
-
Shut down all Primo servers (BE, FE, and SE).
-
From the BE server, enter the following commands to display the Managing Oracle menu:dlib prm00util o
-
Enter option 11 to change an Oracle user's password.
-
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):
-
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).
-
At the following prompt, enter the PRIMO_DBA user name/password. Otherwise, type enter to exit.If you want to update this password in OracleEnter 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.
-
At the following prompt, type enter to return to the Managing Oracle menu.Change password in OracleChanging 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...
-
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.
-
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.
O.13 Database Files
---------------
0. Exit Procedure
1. List of Database Files
2. Resize Oracle Datafile
3. Add File to Tablespace
4. Show Datafile Free Blocks by KBytes
5. Show Datafile Free Blocks by BlockID
Please select [exit]:
Database Files Menu
To access the Database Files menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
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:
The Database prm1 Contains the Following Files:
======================================================
T NAME SIZE K F
---------- -------------------------------------------------- ---------- ---
LOG /exlibris/oradata/prm1/prm1_log01.dbf 71680 6
SYSAUX /exlibris/oradata/prm1/prm1_sysaux01.dbf 1048576 3
SYSTEM /exlibris/oradata/prm1/prm1_system01.dbf 655360 1
TSLOB /exlibris/oradata/prm1/prm1_tslob01.dbf 512000 5
TS_P_DAT /exlibris/oradata/prm1/prm1_ts_p_dat_01.dbf 7340032 7
UNDOTBS1 /exlibris/oradata/prm1/prm1_undotbs01.dbf 2097152 2
USERS /exlibris/oradata/prm1/prm1_users01.dbf 204800 4
T NAME SIZE K F
---------- -------------------------------------------------- ---------- ---
TEMP /exlibris/oradata/prm1/prm1_temp01.dbf 4194304 1
Enter CR to continue...
List of Database Files
Resize Oracle Datafile (Util O/13/2)
This utility allows you to enlarge or reduce the size of an Oracle data file.
Requires the PRIMO_DBA user name and password.
To change the size of an Oracle data file:
-
Enter option 2 from the Database Files menu.
-
At the following prompt, enter the PRIMO_DBA user name/password:To resize a database file enter PRIMO_DBA username/password.username/password:
-
At the following prompt, enter the name of the tablespace:Enter Tablespace name:
-
At the following prompt, enter the file name (including the full path) that you want to resize:Enter file name to resize:
-
At the following prompt, enter the new file size:Enter new file size (MB):
-
At the following prompt, enter y to continue:confirm (y/[n]):
-
At the following prompt, type enter to continue:resizingSQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 00:19:27 2009Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsEnter 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).
This utility requires the PRIMO_DBA user name and password.
To add a data file to a tablespace:
-
Enter option 3 from the Database Files menu.
-
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:
-
At the following prompt, enter the name of the tablespace:Enter Tablespace name:
-
At the following prompt, enter the new file name (including the full path):Enter new file name:
-
At the following prompt, enter the file size:Enter file size (MB):
-
At the following prompt, enter y to continue:confirm (y/[n]):
-
At the following prompt, type enter to continue:SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 00:44:50 2009Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsEnter 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:
TABLES F BLOCK_ID KBYTES NAME
------ --- ---------- ---------- -----------------------------------
TS_P_D 7 162169 128 /exlibris/oradata/prm1/prm1_ts_p_da
AT t_01.dbf
SYSTEM 1 55681 64 /exlibris/oradata/prm1/prm1_system0
1.dbf
SYSAUX 3 24977 64 /exlibris/oradata/prm1/prm1_sysaux0
1.dbf
SYSAUX 3 24993 64 /exlibris/oradata/prm1/prm1_sysaux0
1.dbf
TABLES F BLOCK_ID KBYTES NAME
------ --- ---------- ---------- -----------------------------------
SYSAUX 3 25185 64 /exlibris/oradata/prm1/prm1_sysaux0
1.dbf
SYSAUX 3 32889 64 /exlibris/oradata/prm1/prm1_sysaux0
1.dbf
UNDOTB 2 81 64 /exlibris/oradata/prm1/prm1_undotbs
S1 01.dbf
Enter CR to continue...
List Free Blocks of Data
Show Datafile Free Blocks by BlockID (Util O/13/5)
This utility displays the free data file blocks per tablespace.
To display the free data file blocks:
-
Enter option 5 from the Database Files menu.
-
At the following prompt, enter the name of the tablespace:Tablespace Name:
-
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).
-
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_nonew 5: and FILE_ID = 9BLOCK_ID BYTES---------- ----------33149737 9378201633068329 66584576033013801 44564480032946601 55050240032847913 80740352032786729 50017075232761001 20866662432760361 314572832758697 209715232758441 104857632758185 1048576Enter 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.
O.14. Database Free/Used Space
-------------------------
0. Exit Procedure
1. All Tablespaces Free Space Summary
2. Number of Free Extents by Size in a Tablespace
3. All Free Extents of Min Size in a Tablespace
4. Space Used by a Repository/Repositories in Each Tablespace
5. Space Used by a Group of Repositories in Each Tablespace
6. Coalesce Contiguous Free Extents
7.
8. Primo Tablespaces Total/Free/Used Space Report
9. Clean Temporary Tablespace Free Storage
Database Free/Used Space Menu
To access the Database Free/Used Space menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
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.
It is important to review this report from time to time to prepare additional resources for the database.
To display a summary of the free space for tablespaces, enter option 1 from the Database Free/Used Space menu. For example:
TABLESPACE_NAME TOTAL_FREE_SPACE MAX_EXTENT NUM_FREE_EXTENTS
------------------ ---------------- ---------- ----------------
TS0 9.94296183 9.94296183 1
TS_P_IDX 1024.50027 1024.50027 1
UNDOTBS1 2861.94711 729.338137 33
SYSAUX 69.9759389 21.6994198 17
TSLOB 71.9770382 71.9770382 1
USERS 200.031756 200.031756 1
LOG 63.9726412 63.9726412 1
SYSTEM 159.96287 159.96287 1
TS1 9.94296183 9.94296183 1
TS_P_DAT 229289.89 3970.18089 438
Temporary Tablespace Space Usage
TABLESPACE_NAME SIZE M
------------------------------ ----------
TEMP 0
Sort Segments Usage (in Temporary Tablespace)
TABLESPACE_NAME Total M Used M Free M
------------------------------- ---------- ---------- ----------
TEMP 16381 0 16381
Enter CR to continue...
Display of Free Space
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.
To display the free extents for a tablespace, enter
-
Enter option 2 from the Database Free/Used Space menu
-
At the following prompt, enter the tablespace name:Enter Tablespace name:
-
At the following prompt, type enter to return to the Database Free/Used Space menu:SIZE IN MB NUM OF EXTENTS---------- --------------3970 283944 13737 13496 13445 13388 13257 13170 13098 13084 13063 1Enter 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.
To display the free extents for a tablespace:
-
Enter option 3 from the Database Free/Used Space menu.
-
At the following prompt, enter the tablespace name:Enter Tablespace name:
-
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]:
-
At the following prompt, type enter to return to the Database Free/Used Space menu:EXTENT_SIZE-----------3970.180893970.180893970.180893970.180893970.180893970.18089Enter 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.
To display the size of each repository per tablespace:
-
Enter option 4 from the Database Free/Used Space menu.
-
At the following prompt, enter the unit name (such as prm or prm00):Enter unit name (full or truncated, e.g. primo):
-
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.27408Enter 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.
To display the total amount of space for all of the repositories per tablespace:
-
Enter option 5 from the Database Free/Used Space menu.
-
At the following prompt, enter the unit name (such as prm):Enter first 3 characters of unit code (e.g. primo):
-
At the following prompt, type enter to return to the Database Free/Used Space menu:TABLESPACE_NAME SIZE_MB------------------------------ ----------TS_P_DAT 8008.27408Enter 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).
This utility requires the PRIMO_DBA user name and password.
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.
To coalesce free extents:
-
Enter option 6 from the Database Free/Used Space menu.
-
At the following prompt, enter the PRIMO_DBA user name and password:To Coalesce Tablespaces enter PRIMO_DBA username/password.username/password:
-
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 2009Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 20:37:29 2009Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsEnter 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:
NAME TOTAL SIZE M
------------------------------ ------------
TS_P_IDX 1024
TS0 10
TSLOB 72
TS_P_DAT 259072
TS1 10
NAME TOTAL FREE M
------------------------------ ------------
TS0 9
TS_P_IDX 1023
TSLOB 71
TS1 9
TS_P_DAT 229163
NAME TOTAL USED M
------------------------------ ------------
TS_P_DAT 29907
Enter CR to continue...
Tablespace Report
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.
To clean temporary tablespace:
-
Enter option 9 from the Database Free/Used Space menu.
-
At the following prompt, enter the name of the temporary tablespace:To Free Temporary Segments Enter Temporary Tablespace:
-
At the following prompt, enter the PRIMO_DBA user name and password:Enter PRIMO_DBA username/password:
-
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 2009Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsEnter 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:
NAME TOTAL SIZE M
------------------------------ ------------
P21_PRM00 8003
Enter CR to continue...
Database Tablespaces (Util O/17)
The Database Tablespace menu allows you to manage tablespaces.
O.17. Database Tablespaces
--------------------------
0. Exit Procedure
1. Create a Tablespace
2. List Tablespace Files
4. Show Tablespace Definitions
5. Show Tablespace Allocated/Free/Used Space
Please select [exit]:
Database Tablespace Menu
To access the Database Tablespaces menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
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.
To create a tablespace:
-
Enter option 1 from the Database Tablespaces menu.
-
At the following prompt, enter the PRIMO_DBA user name and password:To Create a new Tablespace, Enter PRIMO_DBA username/password.username/password:
-
At the following prompt, enter the name of the new tablespace:Enter Tablespace name:
-
At the following prompt, enter the new file name (include full path):Enter new file name (full path) :
-
At the following prompt, enter the size of the file:Enter new file size (MB):
-
At the following prompt, enter the allocation type (AUTO or UNIFORM):==============================================================Tablespaces can be created with a UNIFORM size for all extentsor with allocation type AUTOALLOCATE which meansOracle will decide how to define extentsUtil o 17 4 can be used to see current definitionsfor existing tablespaces==============================================================Tablespace Allocation Type : [AUTO/UNIFORM]
-
If you have selected UNIFORM, enter the uniform size of each extent:UNIFORM SIZE : [128K/1M/4M/128M/1920M]
-
Athe following prompt, enter y to confirm:Tablespace: TEST1File: /exlibris/oradata/prm0/test1_01.dbfFile size: 100MBAllocation : UNIFORM SIZE 4Mconfirm (y/[n]):
-
At the following prompt, enter y to continue:CREATE TABLESPACE TEST1DATAFILE '/exlibris/oradata/prm0/test1_01.dbf' SIZE 100MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 4MSEGMENT SPACE MANAGEMENT AUTOONLINE;Enter CR to continue...
-
At the following prompt, enter y to continue:SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 15:13:43 2009Copyright (c) 1982, 2007, Oracle. All Rights Reserved.idle> Connected.idle> idle> 2 3 4 5Tablespace created.idle> idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsEnter CR to continue...
-
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 2009Copyright (c) 1982, 2007, Oracle. All Rights Reserved.idle> Connected.idle> idle> 2 3 4 5Tablespace created.idle> idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsEnter CR to continue...
List Tablespace Files (Util O/17/2)
This utility lists the tablespaces in the database.
To list a tablespace’s files:
-
Enter option 2 from the Database Tablespaces menu.
-
At the following prompt, enter the name of the tablespace:Enter Tablespace name:
-
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 7Enter 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.
TS_NAME EXT_MGMT ALLOC_TYP INIT_EXT NEXT_EXT TYPE STAT
------------ ---------- --------- ---------- ---------- ---- ----
LOG LOCAL SYSTEM 65536 PERM ONL
SYSAUX LOCAL SYSTEM 65536 PERM ONL
SYSTEM LOCAL SYSTEM 65536 PERM ONL
TEMP LOCAL UNIFORM 1048576 1048576 TEMP ONL
TEST1 LOCAL UNIFORM 4194304 4194304 PERM ONL
TSLOB LOCAL SYSTEM 65536 PERM ONL
TS_P_DAT LOCAL SYSTEM 65536 PERM ONL
UNDOTBS1 LOCAL SYSTEM 65536 UNDO ONL
USERS LOCAL UNIFORM 40960 40960 PERM ONL
Enter CR to continue...
Tablespace Definitions Example
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
To display allocated, free, and used space for a tablespace:
-
Enter option 5 from the Database Tablespaces menu.
-
At the following prompt, enter the name of the tablespace:Enter Tablespace name :
-
At the following prompt, type enter to return to the Database Tablespaces menu:Tablespace TS_P_DAT :_____________________________TOTAL SIZE M------------7168TOTAL FREE M------------5597TOTAL USED M------------1570Enter CR to continue...
Oracle Statistics (Util O/18)
The Oracles Statistics menu allows you to display Oracle statistics.
O.18. Oracle Statistics
-----------------------
0. Exit Procedure
1. Performance Statistics
2. Rollback Segments Definitions
3. Rollback Segments Dynamic Allocation
4. View Long Operations
5. IO Statistics
6. Sort Operations
Please select [exit]:
Oracle Statistics Menu
To access the Oracle Statistics menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
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.
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
Enter CR to continue...
Rollback Segments Definitions (Util O/18/2)
This utility displays the rollback segments definitions.
To display the rollback segments definitions:
-
Enter option 2 from the Oracle Statistics menu.
-
At the following prompt, type enter to continue:SYSTEM GLOBAL AREA (sga)BYTES734003200=======================================================BUFFER CACHE HIT RATIO (db_block_buffers)GETS MISSES RATIO4054336 225243 94.44%=======================================================STATISTIC (db_block, DBWR, sort_area)NAME VALUEopened cursors current 32db block gets 937066consistent gets 3117272physical reads 225243physical writes 92082DBWR checkpoints 37redo log space requests 0sorts (memory) 105305sorts (disk) 1Enter CR to continue...
-
At the following prompt, type enter to return to the Oracle Statistics menu:=======================================================DATA DICTIONARY CACHE (shared_pool_size)GETS MISSES RATIO1442213 22892 98.41%=======================================================LIBRARY CACHE (shared_pool_size)EXECUTIONS MISSES LIBCACHEPROZ552613 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:
NAME EXT RSSIZE WRITES SHRN AVGSHR WRAPS CUREXT WAITS
SYST 6 385024 5160 0 0 0 0 0
_SYS 69 121757696 4661252 2 2097152 4 2 0
_SYS 72 146923520 4952028 2 2097152 2 68 0
_SYS 68 142729216 11195842 3 2796202 6 0 1
_SYS 72 168943616 7438446 3 2446677 1 69 0
_SYS 64 101834752 2825568 3 2446677 2 2 0
_SYS 73 162652160 6144544 3 1398101 0 70 0
_SYS 72 154263552 11230104 3 1747626 3 3 0
_SYS 64 101834752 3382754 2 3145728 4 2 0
_SYS 64 175235072 6435860 3 3495253 5 2 1
_SYS 70 152166400 5199072 3 2097152 2 2 0
Enter CR to continue...
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.
To display the Oracle long operations:
-
Enter the following commands to display the Managing Oracle menu:dlib prm00util o
-
Enter option 18 to display the Oracle Statistics menu.
-
Enter option 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:
TO STOP DO ctrl C
BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
34881 418724 4141 34949 2
34881 418727 4141 34949 2
.
.
.
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:
Sort Operations in the system:
.
.
.
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.
O.19. Shared Pool
-----------------
0. Exit Procedure
1. Show SGA Buffers
2. Flush Shared Pool
Please select [exit]:
Shared Pool Menu
To access the Shared Pool menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
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:
.
.
.
NAME BYTES
------------------------------ ----------
fixed_sga 2043456
buffer_cache 461373440
log_buffer 6340608
ENQUEUE STATS 11928
VIRTUAL CIRCUITS 3736
transaction 417296
table definiti 896
KGSKI scheduler heap 2 de 232
KTCN: Obj Invalidation Se 2336
kgl lock hash table state 9800
ksunfy: nodes of hierarch 320
NAME BYTES
------------------------------ ----------
ASM file 19200
qmn tasks 4128
kwqmncal: allocate buffer 4048
kspd run-time context 16
kzekm heap descriptor 304
incr ckpt write count arr 168
kglsim main lru size 151040
FileOpenBlock 1447104
Core dump directory 520
log_checkpoint_timeout 12360
PX subheap 130616
NAME BYTES
------------------------------ ----------
partitioning d 352520
message pool freequeue 954768
sched job queue 3616
LGWR-network Server info 27648
Parameter Handle 1656
PARAMETER TABLE 2048
state objects 5680
pso tbs: ksunfy 78000
recov_kgqbtctx 4392
Cursor Stats 1290728
enqueue 355224
Enter CR to continue...
Flush Shared Pool (Util O/19/2)
This utility removes all of the Oracle objects from the shared pool.
To remove the Oracle objects from the shared pool:
-
Enter option 2 from the Shared Pool menu.
-
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.
O.20. Multi Threaded Server
---------------------------
0. Exit Procedure
1. Show MTS Parameters
2. Show Listener Services
Please select [exit]:
Multi Threaded Server Menu
To access the Multi Threaded Server menu:
-
Log on to the server with the primo user.
-
Enter the following commands to display the Managing Oracle menu.dlib prm00util o
-
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:
PRIMO_DBA/PRIMO_DBA
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 16:45:09 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
idle> Connected.
idle> 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...
Show MTS Parameters
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:
MTS Dispatchers by Instance
===========================
Service "ENC" has 1 instance(s).
Instance "ENC", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
Service "prm1" has 1 instance(s).
Instance "prm1", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
Service "prm1.rattlesnake.corp.exlibrisgroup.com" has 1 instance(s).
Instance "prm1", status READY, has 1 handler(s) for this service...
Handler(s):
Service "prm1_XPT.rattlesnake.corp.exlibrisgroup.com" has 1 instance(s).
Instance "prm1", status READY, has 1 handler(s) for this service...
Handler(s):
Enter CR to continue...
Show Listener Services