Oracle Utilities
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Utility Usage Note If you are handicapped by working on a Microsoft Windows operating system most of these utilities will not function unless you open a command prompt window "AS ADMINISTRATOR" before running the utility. This is definitely true with OPATCH which must obtain a lock on the Oracle Inventory.
Executable Purpose
acfsroot  
adrci Automated Diagnostic Repository Command Interpreter
agtctl A multithreaded extproc agent is started, stopped, and configured by an agent control utility called agtctl, which works like lsnrctl. However, unlike lsnrctl, which reads a configuration file (listener.ora), agtctl takes configuration information from the command line and writes it to a control file.
amdu Undocumented
aqxmlctl  
asmcmd ASM Command Line Utility
asmtool ASM utility to stamp a disk with a header
asmtoolg ASM utility to stamp a disk with a header
chopt Undocumented
cluvfy Cluster Verify (RAC)
commonenv  
crsdiag Cluster Ready Services Diagnostics
crtsrv  
csscan Database Character Set Scanner
ctxhx The Ultra Search crawler uses the Oracle Text AUTO_FILTER, ctxhx, for processing of binary files. These are non-text, non-HTML files such as PDF files, Microsoft Word files, and so on. For Ultra Search to use the AUTO_FILTER, the shared library path environment variable must contain the $ORACLE_HOME/ctx/lib path.
ctxkbtc The knowledge base is the information source that Oracle Text uses to perform theme analysis, such as theme indexing, processing ABOUT queries, and to document theme extraction with the CTX_DOC package. A knowledge base is supplied for English and French.

With the ctxkbtc compiler, you can:
  • Extend your knowledge base by compiling one or more thesauri with the Oracle Text knowledge base. The extended information can be application-specific terms and relationships. During theme analysis, the extended portion of the knowledge base overrides any terms and relationships in the knowledge base where there is overlap.
  • Create a new user-defined knowledge base by compiling one or more thesauri. In languages other than English and French, this feature can be used to create a language-specific knowledge base.
ctxlc The Lexical Compiler (ctxlc) is a command-line utility that enables creation of Chinese and Japanese lexicons (dictionaries). Such a lexicon may either be generated from a user-supplied word list or from the merging of a word list with the system lexicon for that language.
ctxload Used to import a thesaurus file into the Oracle Text thesaurus tables
dbca Database Configuration Assistant
dbfs_client  
dbgeu_run_action  
dbua DataBase Upgrade Assistant
dbv DataBase Verify
deploymentLibraryExport  
deploymentLibraryImport  
deploync  
dg4odbc  
dg4pwd Constructs the password file for a gateway SID
dgmgrl Data Guard Manager
diagsetup  
diskmon  
dropjava  
dsml2ldif LDAP
dvca Shell script to run Data Vault Configuration Assistant
e2eme  
emagent Enterprise Manager Agent
emagentdeploy  
emagtm Enterprise Manager
emagtmc Enterprise Manager
emda Enterprise Manager
emctl Enterprise Manager
emdctl Enterprise Manager
EMDeploy Enterprise Manager
emdrollogs Enterprise Manager
emdwgrd Script that saves and restores dbControl information if the database was downgraded. Run this script prior to upgrade to save DB Control information. After downgrading the database run this script again to restore the DB Control information.
emtgctl Enterprise Manager
emtgtctl2 Enterprise Manager
emutil Registers job types with Enterprise Manager
emwd Enterprise Manager: Provides the Process Monitor (watchdog) functionality for the console and the agents
eusm Enterprise Manager User Security: Runs the enterprise user security admin tool
execjavatemporabase  
exp Export
expdp DataPump Export
extjob  
extjobo  
extproc External Procedure
extusrupgrade  
findAvailableSpace  
genezi  
getcrshome Locates the Cluster ready Services home directory
gnsd  
hsalloci Heterogeneous Server Agent driver for OCI
hsdepxa Heterogeneous Server Agent with XA Compliant Distributed External Process Driver
hsots Heterogeneous Server Agent with XA Compliant OTS Driver
imp Import
impdp DataPump Import
jssu  
kfed  
kfod OSM Discovery Utility
launch Program Launcher
lbuilder  
LCSSCAN  
ldapadd LDAP
ldapaddmt LDAP
ldapbind LDAP
ldapcompare LDAP
ldapdelete LDAP
ldapmoddn LDAP
ldapmodify LDAP
ldapmodifymt LDAP
ldapsearch LDAP
ldiffmigrator  
lmsgen NLS Binary Message File Generation
loadjava  
loadpsp  
lsnodes List RAC Nodes
LSNRCTL Listener Control
lxegen NLS Calendar
lxinst NLS Data Installation
mkstore  
ncomp  
netca Network Configuration Assistant
netca_deinst  
nid Tool for setting a New IDentifier for a database
nmcbufp  
nmccollector  
nmefwmi  
nmei  
nmeo  
nmesrvc  
nmesrvops  
nmetm  
nmo  
nmocat  
nmupm  
ocopy Oracle Copy
odisrvreg  
oidca  
oidprovtool  
oifcfg Oracle Interface Configuration. Intended for use with RAC clusters
ojmxtool  
ojvmjava  
ojvmtc  
OKDSTRY  
OKINIT  
OKLIST  
olsadmintool  
olsoidsync  
omsdown  
omsstart  
omsstop  
omtsreco  
onsctl  
OO4OCODEWIZ  
opatch Oracle Patch Application/Removal Utility
operfcfg  
orabase  
oracg  
oracle  
OracleAdNetConnect  
OracleAdNetTest  
OraClrAgnt  
oradim Utility for configuring Windows services. Not present in any non-Windows installation
oradnfs  
orajaxb  
orakill Utility for killing Oracle sessions from the operating system command line
oramts_deinst  
orapipe  
orapki  
orapwd Tool for creating and managing password files
orastack  
oravssw  
orion  
ott  
pafctl  
PARDeploy  
plshprof  
proc  
racgimon  
racgmdb  
racgwrap  
rawutl  
rconfig  
remotedbua  
RemoteExecService  
renamedg Rename Data Guard (broker configuration?)
rman  
sbttest  
schema  
schemasync  
sclsspawn  
selecthome  
setsso  
sqlldr SQL*Loader used to load files from the file system into the database and by External Tables
sqlplus Command line SQL*Plus user interface
sqlplusw Command line SQL*Plus user interface for Windows. No longer shipped after 10.2.0.5.
srvconfig  
srvctl  
StartAgent  
statusnc  
targetdeploy not in 12c Beta
tkprof  
tnnfg new?
tnslsnr  
tnsping Ping connections via SQL*Net. Determines the fact that SQL*Net can see a host and service
transx  
trcasst Network connections are traced to provide a detailed description of the operations performed by Oracle's internal components. The trace data is stored in an output trace file that can then be analyzed.
trcldr new?
trcroute A non-Windows utility for tracing tnsping information
trcsess Combines multiple trace files into a single file for submission to TKPROF
tstshm new ?
uidrvci  
umu User migration
unzip Unzip utility used by the Oracle installer
wrap Tool for encrypting PL/SQL source code prior to loading into the database
wrc  
xml  
xmlcg  
xmlwf  
xsl  
xsql  
xsqlproxy  
xvm  
zip Zip utility used by the Oracle installer
 
DBV
Database Verify dbv USERID=username/password
segment_id=<tablespace_name.segfile.segblock>
logfile=<logging_file_name_and_path>
feedback=<integer>
help=<Y/N>
parfile=<parameter_file_name_and_path>
/*The following example shows a sample use of the command-line interface to this mode of DBVERIFY.

SEGMENT_ID specifies the segment to be verified. It is composed of the tablespace ID number (tsn), segment header file number (segfile), and segment header block number (segblock). These can be obtained by querying TABLESPACE_ID, HEADER_FILE, and HEADER_BLOCK from sys_user_segs. */


SELECT tablespace_id, header_file, header_block
FROM sys_user_segs
ORDER BY 1,2,3;

dbv USERID=uwclass/uwclass SEGMENT_ID=2.3.38451

DBVERIFY - Verification starting : SEGMENT_ID = 2.3.38451

DBVERIFY - Verification complete

Total Pages Examined         : 32
Total Pages Processed (Data) : 28
Total Pages Failing (Data)   : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index)  : 0
Total Pages Processed (Other): 3
Total Pages Processed (Seg)  : 1
Total Pages Failing (Seg)    : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 30027821 (0.30027821)
 
NID
DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database.

Prior to the introduction of the DBNEWID utility, you could manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, you could not give the database a new identifier (DBID). The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem by allowing you to change any of the following:

* Only the DBID of a database
* Only the DBNAME of a database
* Both the DBNAME and DBID of a database

Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 (see the Oracle11g Database Administrator's Guide). Consequently, you should make a backup of the whole database immediately after changing the DBID.

Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. You must change the DB_NAME initialization parameter after a database name change to reflect the new name. Also, you may have to re-create the Oracle password file. If you restore an old backup of the control file (before the name change), then you should use the initialization parameter file and password file from before the database name change.

Syntax:
Keyword Description Default
TARGET Username / Password None
DBNAME New database name None
LOGFILE Output log None
REVERT Revert failed change NO
SETNAME Set a new database name only NO
APPEND Append to output log NO
HELP Displays help messages NO
/* Ensure that you have a recoverable whole database backup and ensure that the target database is mounted but not open, and that it was shut down consistently prior to mounting. */

SHUTDOWN IMMEDIATE
STARTUP MOUNT

/* Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege. */

% nid TARGET=sys/oracle@test_db

/* To change the database name in addition to the DBID, specify the DBNAME parameter. This example changes the name to orabase: */

% nid TARGET=sys/oracle@test DBNAME=orabase

/* The DBNEWID utility performs validations in the headers of the datafiles and control files before attempting I/O to the files. If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt), changes the DBID for each datafile (including offline normal and read-only datafiles), and then exits. The database is left mounted but is not yet usable. */

DBNEWID: Release 12.1.0.0.2 - Beta on Tue Jun 18 22:16:46 2013
Copyright (c) 1982, 2012, Oracle and/or its affiliates. All rights reserved.

Connected to database TEST_DB (DBID=3942195360)

Control Files in database:
/oracle/dbs/cf1.f
/oracle/dbs/cf2.f

Change database id of database SOLARIS? (Y/[N]) => y

Proceeding with operation
Datafile /oracle/dbs/tbs_01.f - changed
Datafile /oracle/dbs/tbs_02.f - changed
Datafile /oracle/dbs/tbs_11.f - changed
Datafile /oracle/dbs/tbs_12.f - changed
Datafile /oracle/dbs/tbs_21.f - changed

/* New DBID for database TEST_DB is 3942196782.
All previous backups and archived redo logs for this database are unusable
Proceed to shutdown database and open with RESETLOGS option.
DBNEWID - Database changed.

If validation is not successful, then DBNEWID terminates and leaves the target database intact. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing its DBID.
After DBNEWID successfully changes the DBID, shut down the database */


SHUTDOWN IMMEDIATE

-- mount the database

STARTUP MOUNT

-- open the database in RESETLOGS mode and resume normal use

ALTER DATABASE OPEN RESETLOGS;

/* make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database. */
/* The following steps describe how to change the database name without changing the DBID. */

-- 1. Ensure that you have a recoverable whole database backup
-- 2. Ensure that the target database is mounted but not open, and that it was shut down consistently prior to mounting


SHUTDOWN IMMEDIATE
STARTUP MOUNT

-- 3. Invoke the utility on the command line, specifying a valid user with the SYSDBA privilege. You must specify both the DBNAME and SETNAME parameters. This example changes the name to orabase

% nid TARGET=SYS/oracle@test_db DBNAME=orabase SETNAME=YES

/* DBNEWID performs validations in the headers of the control files (not the datafiles) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, and exits. After DBNEWID completes successfully, the database is left mounted but is not yet usable. */

DBNEWID: Release 12.1.0.0.2 - Beta on Tue Jun 18 22:16:46 2013
Copyright (c) 1982, 2012, Oracle and/or its affiliates. All rights reserved.

Connected to database TEST_DB (DBID=3942196782)

Control Files in database:
/oracle/dbs/cf1.f
/oracle/dbs/cf2.f

Change database name of database TEST_DB to ORABASE? (Y/[N]) => Y

Proceeding with operation

Database name changed from TEST_DB to ORABASE - database needs to be shutdown.
Modify parameter file and generate a new password file before restarting.

DBNEWID - Successfully changed database name

/* If validation is not successful, then DBNEWID terminates and leaves the target database intact. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing the database name. */

-- 4. Shut down the database.


SHUTDOWN IMMEDIATE

-- 5. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name.
-- 6. Create a new password file.
-- 7. Start up the database and resume normal use.


STARTUP
/* To revert a stalled DBID change operation, run the DBNEWID utility again, specifying the REVERT keyword. */

% nid TARGET=SYS/oracle REVERT=YES LOGFILE=$HOME/nid.log
/* connects with operating system authentication and changes only the DBID */

% nid TARGET=/

/* changing the DBID and Database Name. The following example connects as user SYS and changes the DBID and also changes the database name to test2 */

% nid TARGET=SYS/oracle@test_db DBNAME=orabase
noterow
 
OPATCH
APPLY: Applies an interim patch to an Oracle Home from the current directory opatch apply [-connectString <connect_string_list>] [-delay <value>] [-force]
[-init <parameters for the init script in escaped double quotes>
[-opatch_init_end]] [-invPtrLoc <path_to_oraInst.loc>] [-jre <location>]
TBD
CHECKAPPLICABLE opatch checkApplicable ...
TBD
CHECKINSTALLEDONEOFFS: opatch checkInstalledOneOffs ...
TBD
COMPARE: opatch compare ...
TBD
DEPLOY: opatch deploy ...
TBD
HELP: Display OPatch help opatch help
c:\> set oracle_home=c:\oracle\product\12.1.0\dbhome_1

c:\> cd c:\oracle\product\12.1.0\dbhome_1\OPatch\opatch help

c:\> cd c:\oracle\product\12.1.0\dbhome_1\OPatch\opatch -h

c:\> cd c:\oracle\product\12.1.0\dbhome_1\OPatch\opatch -help

c:\> cd c:\oracle\product\12.1.0\dbhome_1\Opatch\opatch deploy -help
LISTCOMPOSITES: opatch listComposites ...
TBD
LSDOMAINS:  opatch lsDomains ...
TBD
LSHOMES:  opatch lsHomes ...
TBD
LSINV: Crosscheck installed products and Oracle home directories opatch lsinv [-all_nodes | -delay | -details | -invPtrLoc | -jre |
-group_by_date  | -local | -oh | -patch | -ptlConnect | -ptlPassword |
-ptlSchema | -property_file | -retry]
c:\> set oracle_home=c:\oracle\product\12.1.0\db_1

c:\> cd c:\oracle\product\12.1.0\db_1\OPatch

opatch lsinv
LSINVENTORY: Crosscheck installed products, patches,  and Oracle home directories opatch lsinventory [-all | -all_nodes | -bugs_fixed | -delay | -details | -invPtrLoc | -jre | -group_by_date | -local | -oh | -patch | -ptlConnect | -ptlPassword | -ptlSchema | -property_file | -retry]
-- windows
c:\> set oracle_home=c:\oracle\product\12.1.0\db_1

c:\> cd c:\oracle\product\12.1.0\db_1\OPatch\opatch lsinventory -all

-- linux
sudo su - oracle

export ORACLE_HOME=/app/oracle/product/12.1.0/db_1

cd $ORACLE_HOME/OPatch

./opatch lsinventory -details

./opatch lsinventory -bugs_fixed
LSPATCHES: opatch lspatches ...
TBD
NAPPLY: Applies a set of patches at one time opatch napply ...
TBD
NROLLBACK: Rollback a set of patches at one time opatch nrollback ...
TBD
PREREQ: Run patch prerequisite checks on an ORACLE_HOME opatch prereq ...
TBD
QUERY: Loads and prints patch information opatch query [-all] [-get_base_bug] [-get_component] [-get_os] [-get_date]
[-has_sql] [-is_online_patch] [-is_portal_patch] [-is_rolling_patch]
[-jre <location>] [-oh <location] <patch_location>
TBD
ROLLBACK: Rollback a one-off patch as indicated by the reference id opatch rollback ...
TBD
START: opatch start ...
TBD
STARTCOMPOSITES: opatch startComposites ...
TBD
STOP: opatch stop ...
TBD
STOPCOMPOSITES: opatch stopComposites ...
TBD
UTIL: Invokes a utility on an ORACLE_HOME opatch util ...
c:\> set oracle_home=c:\oracle\product\12.1.0\db_1

c:\> cd c:\oracle\product\12.1.0\db_1\OPatch\opatch util verify -ph /stage/patch
VERSION: Oracle Patch Utility: Version opatch version
c:\> set oracle_home=c:\oracle\product\12.1.0\db_1

c:\> cd c:\oracle\product\12.1.0\db_1\OPatch\opatch version
 
ORASTACK
Windows Modify Reserved Stack Size orastack <executable> <stack_size>
orastack
-- read the comments from the above command

orastack oracle.exe 500000
orastack tnslsnr.exe 500000
 
TRCASST
Format listener trace files trcasst <switch> <tracefile_name>

Syntax:
Switch Description
-e Displays error information. Valid options to use with –e include: Valid options listed below.

Switch Description Default
0 Translate NS error numbers Yes
1 Display only error translation  
2 Display error numbers without translation  
-l Displays services and TTC information. Valid options listed below.

Switch Description Default
-a Displays data for all connections in trace file  
-i Displays the trace data for a particular ID from the –la option.  
-s Displays a summary of statistics. This includes total bytes sent and received, maximum open cursors, total calls, parse counts, and more.  
-o Displays services and TTC (Two Task Common) information. Valid options listed below.

Switch Description Default
-c Summary of connection information  
-d Detailed connection information Yes
-q SQL commands (used in combination with "u")  
-t Detailed TTC information Yes
-u Summary of TTC information  
-s Statistics Default
C:\Documents and settings\oracle>set trc_level ADMIN

-- connect to the database and perform some actions via SQL*Net

C:\Documents and settings\oracle>set trc_level OFF

C:\Documents and settings\oracle> trcasst c:\oracle\diag\tnslsnr\perrito4\listener\trace\ora_7520_10328.trc
 
TRCSESS
Trace file consolidation. Merges multiple trace files into a single file for running through TKPROF The code for this utility is in $ORACLE_HOME/bin.

trcsess [output=output_file_name]
        [session=session_id]
        [clientid=client_id]
        [service=service_name]
        [action=action_name]
        [module=module_name]
        [trace_files]
conn uwclass/uwclass

SQL> SELECT sid, serial#
2 FROM v$session
3 WHERE sid IN (SELECT sid FROM v$mystat WHERE rownum = 1);

       SID    SERIAL#
---------- ----------
       147       8613

-- create two trace files ... first a 10053
ALTER SESSION SET tracefile_identifier = 'test_trace1';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
select count(*) from airplanes where delivered_date > sysdate+1000;
select count(*) from airplanes where delivered_date <= sysdate+1000;
ALTER SESSION SET EVENTS '10053 trace name context OFF';

-- then a 10046
ALTER SESSION SET tracefile_identifier = 'test_trace2';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
select count(*) from airplanes where delivered_date > sysdate+1000;
select count(*) from airplanes where delivered_date <= sysdate+1000;
ALTER SESSION SET EVENTS '10046 trace name context OFF';

-- finally use trcsess to merge them for TKPROF analysis
trcsess output=c:\tmp\mytrace.trc session=147.8613 c:\oracle\diag\rdbms\orabase\orabase\trace\*test_trace*.trc
 
WRAP
Wrap a PL/SQL object

See also DBMS_UTILITY
wrap iname=input_file oname=output_file
SELECT ascii('D') FROM dual;
SELECT ascii(' ') FROM dual;

-- create as a text file c:\temp\unwrapped.sql
CREATE OR REPLACE FUNCTION unwrapped (namein VARCHAR2)
RETURN NUMBER IS
 c  PLS_INTEGER := 0;
 j  PLS_INTEGER;
 l  PLS_INTEGER;
BEGIN
  l := LENGTH(namein);

  FOR i IN 1.. l LOOP
    c := c + ASCII(SUBSTR(namein, i, 1));
  END LOOP;
  RETURN c;
END unwrapped;
/

-- create as a text file c:\temp\wrapped.sql
CREATE OR REPLACE FUNCTION wrapped (namein VARCHAR2)
RETURN NUMBER IS
 c  PLS_INTEGER := 0;
 j  PLS_INTEGER;
 l  PLS_INTEGER;
BEGIN
  l := LENGTH(namein);

  FOR i IN 1.. l LOOP
    c := c + ASCII(SUBSTR(namein, i, 1));
  END LOOP;
  RETURN c;
END wrapped;
/

wrap iname=c:\temp\wrapped.sql oname=c:\temp\output.sql

SQL> @c:\temp\unwrapped.sql
SQL> @c:\temp\output.sql

set pagesize 0

SELECT text
FROM user_source
WHERE name = 'UNWRAPPED'
ORDER BY line;

SELECT text
FROM user_source
WHERE name = 'WRAPPED'
ORDER BY line;

Related Topics
DBMS_DDL
DBMS_DB_VERIFY
DBMS_QOPATCH
DBMS_UTILITY
Functions
Procedures