Check IOPS of your Oracle Database Storage for Cloud Migration

Method 1:

To do a quick check of Maximum IO per second (IOPS) of your underlying storage system use Oracle supplied IO Calibration tool. I believe this is available from Oracle 10g, but i have used it on Oracle 11g and it works well.
This tool will generate random single and large block reads to generate I/O and measure the IOPS and Mbps.
A very handy tool to estimate the required IOPS when migrating to cloud to build or select the proper storage on your cloud volumes.
Procedure to run the tool:
Note: Use this tool during a quiet time or quiese mode, so no other connections are made to database other than your sqlplus session.
This will give near accurate results.

Login as sysdba to server sqlplus session. Make a local connection.
Run below:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
l_latency PLS_INTEGER;
l_iops PLS_INTEGER;
l_mbps PLS_INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 1,
max_latency => 20,
max_iops => l_iops,
max_mbps => l_mbps,
actual_latency => l_latency);
DBMS_OUTPUT.put_line(‘Max IOPS = ‘ || l_iops);
DBMS_OUTPUT.put_line(‘Max MBPS = ‘ || l_mbps);
DBMS_OUTPUT.put_line(‘Latency = ‘ || l_latency);
END;
/

Note: The above script will take 2 inputs,
Max Latency – in milli seconds, select the acceptable latency in block reads (default 20 milli seconds is good for most storage types)
Num_Physical_disks – Set it to 1, if you have datafiles spread across multiple physical disks (like in ASM) select the correct number of disks attached to your +Data disk group, otherwise input 1.

This will run for few minutes, leave the session to run.

You can monitor the progress by querying V$IO_CALIBRATION_STATUS.

SQL> select * from V$IO_CALIBRATION_STATUS;

Once the IO calibration is completed, it will give you the results as below:

Max IOPS = 667
Max MBPS = 179
Latency = 21

The above information is also recorded in a table DBA_RSRC_IO_CALIBRATE.

Using the max iops and max mbps you may select the right storage and instance type.
Example: To support 667*10%* around 700 IOPS you may select a GP2 SSD type storage in AWS with 250 GB (it gives approximately 750 IOPS) or you can lock down 250 GB IO storage with 1000 PIOPS.
* To cover for any unforeseen spikes, it would be a good idea to add an additional 10 percent to this peak IOPS number to account for the actual IOPS needed for your database. Also you may add for writes as well.

Method 2:

Another handy method to calculate the IOPS is by using below script and schedule to run it every day or multiple times in a day for a period and check the results.

-Modify the values of run_duration and capture_gap to best fit your needs

– First create a table to store I/O results
CREATE TABLE peak_iops_measurement (capture_timestamp date,total_read_io number, total_write_io number, total_io number,total_read_bytes number, total_write_bytes number, total_bytes number);

– Schedule below script using shell or run it few times on the database as sysdba

The script runs for 1 hour (3600 secs) and captures data every 5 minutes.

DECLARE
run_duration number := 3600;
capture_gap number := 5;
loop_count number :=run_duration/capture_gap;
rdio number;
wtio number;
prev_rdio number :=0;
Prev_wtio number :=0;
rdbt number;
wtbt number;
prev_rdbt number;
Prev_wtbt number;
BEGIN
FOR i in 1..loop_count LOOP
SELECT SUM(value) INTO rdio from gv$sysstat WHERE name =’physical read total IO requests’;
SELECT SUM(value) INTO wtio from gv$sysstat WHERE name =’physical write total IO requests’;
SELECT SUM(value)* 0.000008 INTO rdbt from gv$sysstat WHERE name =’physical read total bytes’;
SELECT SUM(value)* 0.000008 INTO wtbt from gv$sysstat WHERE name =’physical write total bytes’;
IF i > 1 THEN
INSERT
INTO PEAK_IOPS_MEASUREMENT
(
CAPTURE_TIMESTAMP,
TOTAL_READ_IO,
TOTAL_WRITE_IO,
TOTAL_IO,
TOTAL_READ_BYTES,
TOTAL_WRITE_BYTES,
TOTAL_BYTES
)
VALUES
(
SYSDATE,
( RDIO-PREV_RDIO)/5,
(WTIO-PREV_WTIO)/5,
((RDIO-PREV_RDIO)/5)+((WTIO-PREV_WTIO))/5,
(RDBT-PREV_RDBT)/5,
(WTBT-PREV_WTBT) /5,
((RDBT-PREV_RDBT)/5)+((WTBT-PREV_WTBT))/5);

END IF;

prev_rdio := rdio;
prev_wtio := wtio;
prev_rdbt := rdbt;
prev_wtbt := wtbt;

DBMS_LOCK.SLEEP(capture_gap);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/

– Check the IOPS with below query
SELECT MAX(total_io) PeakIOPS FROM peak_iops_measurement;

Using the max iops and max mbps you may select the right storage and instance type.
Example: To support 667*10%* around 700 IOPS you may select a GP2 SSD type storage in AWS with 250 GB (it gives approximately 750 IOPS) or you can lock down 250 GB IO storage with 1000 PIOPS.
* To cover for any unforeseen spikes, it would be a good idea to add an additional 10 percent to this peak IOPS number to account for the actual IOPS needed for your database. Also you may add for writes as well.

 

 

Oracle Database Security Feature in 11g “REDACTION”

Oracle has introduced redaction feature in database 12c and added later to 11g in 11.2.0.4 patchset.

Redaction feature is available with dbms_redact.

Some examples:

a. If you want to redact credit card data in table:

Define a policy with functions of your choice. You can use FULL redaction or Partial or Regular Expression mode. This example is for partial. You should know the max length of the column for correct input/output parameters.

select max(length(COLNAME)) from <schema>.<table_name>;

Now, lets check the userenv for session_user.

SELECT SYS_CONTEXT (‘userenv’, ‘SESSION_USER’)    FROM DUAL;

So, go ahead and define a policy for a table which has max length of 19 char and column defined as varchar..

begin
dbms_redact.add_policy(
object_schema => ‘SCOTT’,
object_name => ‘TABLENAME’,
column_name => ‘COLNAME’,
policy_name => ‘CREDITCARD_MASKIT’,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => ‘VVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVV,*,1,10’,
expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”)
= ”SCOTT”’);
end;

 

Check it:

select colname from scott.tablename;

You will get out put as * for first 10 digits and after 10 digits the orig. values of data is displayed.

**********714894028

—————————————————————————————————————–

b. If you want to redact a number with length as 10.

I want to write a policy, the first 5 numbers will be replaced with digit 9 and rest all ignored in my select query.

begin
dbms_redact.add_policy(
object_schema => ‘TEST’,
object_name => ‘TABLEA’,
column_name => ‘NUMBERA’,
policy_name => ‘MASK_PARTIAL_A’,
function_type => DBMS_REDACT.PARTIAL,
function_parameters => ‘9,1,5’,
expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”)
= ”TEST”’);
end;
/

You can query below dictionary views to see the redact column and function defined in a database.

select * from redaction_policies;

select * from redaction_columns;

——————————————————————————————-

You can alter, drop policies.

begin

DBMS_REDACT.DROP_POLICY (
object_schema => ‘TEST’,
object_name => ‘TABLEA’,
policy_name => ‘MASK_PARTIAL_A’);

end;

References:

Some examples of implementing Data Redaction using DBMS_REDACT (Doc ID 1588270.1)
sys_context: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm
dbms_redact: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_redact.htm#ARPLS73812

Monitor datapump jobs

Want to check what the datapump export or import is doing, execute the below queries to get the information from database.

select * from dba_datapump_jobs;

select * from dba_datapump_sessions;

select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS;

Tip: Improve the process time by increasing the parallel process. Define option with expdp command ‘PARALLEL=x’

Oracle Executables

This table lists executable programs in $ORACLE_HOME/bin (or %ORACLE_HOME%\bin on Windows) for Oracle Enterprise Edition, including shell scripts, Windows .bat files but excluding Windows DLLs or any that can’t execute directly. It’s not a complete list since what programs are in that directory depends on what options I chose at the time of software installation. I’m doing my best to give descriptions about each program and add some comments I consider useful or merely interesting. Many of them show Usage by typing the program name, or the name followed by some garbage argument, or -h, or sometimes help=y. “Not in 9i” means not in 9i and up (i.e. only in 8i). Files of 0 in size are ignored (such as grdcscan, tnnfg, trcldr in my installation, plus those ending with 0 or O). [2010-01,2011-02 Update] 11g and up binaries are in separate tables: 11g Legend `: Only exists on UNIX *: Only exists on Windows

Program	Name	Notes
acro*		Not in 9i
adapters`		Running with no argument returns info about installed Oracle Net (SQL*Net) adapters. Running with path to an Oracle binary file (oracle, sqlplus,…) shows whether the binary has Oracle Net symbols in it.
agent*		Possibly used in SNMP
agentctl		Oracle%oracle_home%Agent service Control (not in 10g)
agntsvc*		Oracle%oracle_home%Agent service, used by EM
agtctl	(Oracle Heterogeneous Services) Agent Control Utility	See ORA-28591
asmtool		10g only
asmtoolg		GUI version of ASM tool
bbed*	Block Browser and Editor	Oracle internal use only. Not in 9i. On UNIX, you have to make it. Ref: 1, 2, 3.
bulkmodify		See Oracle Internet Directory Administrator’s Guide, Appendix A “Syntax for LDIF and Command Line Tools”. Not in 10g
cemutls		10g
cfo*	Configure FAN ONS	FAN ONS Publisher program, 10.2.0.3 and later (see Note:405120.1)
clscfg		10g
clsfmt		10g
cmadmin`		Not in 9i
cmctl`		Not in 9i
cmgw`		Not in 9i
coraenv`	Oracle Environment C Shell	csh script to set up Ora Env; csh version of oraenv
crsctl, crssetup	Cluster Ready Services Control and Setup utilities	10g
csscan	Character Set Scanner
ctxhx*		Called by intermedia server
ctxkbtc	Context Knowledge Base exTension Compiler	Oracle Text Executable
ctxlc	Lexical Compiler	See above. 10g
ctxload	Thesaurus Loader	See above
ctxsrv	Intermedia server	Not in 10g
ctxsvc*	Intermedia service	Not in 10g
cursize`	Cursor Size	cursize username/password
dbca.bat	Database Config Assistant	Used to create and configure a database (called dbassist in 8i)
DBConsole.pm		Perl Module to provide start, stop, status functionality (10g)
dbfmig`
dbfsize`	Database File Size Info	If run on redo logfiles, also reports redo block size (the same value as x$kccle.lebsz or if in archivelog mode, v$archived_log.block_size). Run on controlfiles, it also reports controlfile block size. dbfsize also tells you whether the file is filesystem or raw partition based (Quick I/O reported as raw). Not ASM aware.
dbhome`
dbma.bat		10g
dbshut`	Database Shutdown Script
dbsnmp	Database Intelligent (SNMP) Agent	According to marty.grinstead at oracle.com, “spawned when the IA is running a scheduled job”. If the agent is not configured correctly, running it causes database shutdown immediate to hang. Not in 10g
dbsnmpj*	Database Intelligent (SNMP) Agent. Not in 10g
dbsnmpwd.bat	Not in 10g
dbstart`	Database Startup Script
dbua`
dbv	Datafile Verification	See Oracle Utilities
debugproxy		Not in 10g
demobld`		Not in 10g
demodrop`		Not in 10g
deploync	Deploy NComp
dgmgrl		Data Guard Manager
dropjava
dumpsga`
dsml2ldif*		XML Parser utility to generate LDAP ldif files
e2eme		10g
echodo`		In shell script, echo the command followed by run it
ela		10g
emagent, emagtm, emca.bat, emctl.bat, emdctl, emtgtctl, emwd.bat		10g, EM-related
encaps*		Not in 10g
encsvc*	Oracle SNMP Peer Encapsulator service
esm	Enterprise Security Manager	Not in 10g
exp	Export	See Oracle Utilities
expdb	Data Pump Export	10g
extjob		10g
extjobo		10g
extractlib`
extproc	External Procedure
extusrupgrade		Upgrade externally authenticated SSL users. 10gR2
fmputl`	File Mapping Utility
fmputlhp`
gatekeeper		Not in 9i
genagtsh`
genautab`		Not in 9i
genclntsh`	Generate Client Shared Libraries
genclntst`
genezi	Generate EZ Instantclisent?	One use of this UNIX/Linux utility is the basic Instant Client (i.e. no sqlplus) from 10.2.0.3 up can run LD_LIBRARY_PATH=$ORACLE_HOME/lib genezi -v to get client version. (Ref: Note:818454.1)
genksms`		Generate ksms.s, the assembly language source file listing contents of SGA fixed region. E.g., .set kcrfal_,sgabeg+19072 says kcrfl (redo allocation) structure is stored in the fixed region, 19072 bytes from the beginning of SGA. (J.Morle Scaling Oracle8i, pp.260-1)
gennfgt`
gennttab`
genoccish`
gensyslib`
helpins	SQL*Plus Help Installation	Beginning with Oracle8, only Help for SQL*Plus, not SQL, is installed.
hsalloci	Heterogeneous Agent (Server) with Driver for Oracle using OCI
hsdepxa	Heterogeneous Agent (Server) with XA compliant Distributed External Procedure driver
hsodbc	Heterogeneous Agent (Server) ODBC driver
hsolefs*	Heterogeneous Agent (Server) OLE driver
hsolesql*	Heterogeneous Agent (Server) OLE driver
hsots	Heterogeneous Agent (Server) with XA compliant OTS driver
idl2ir	Interface Definition Language to Interface Repository converter	Not in 9i
i*.flt*	various Graphic Import Filters	To find out, copy them to another filename with EXE as filename extension and look at their properties in Windows Explorer. They’re all from Stellent, Inc.
imp	Import utility	See Oracle Utilities
impdb	Data Pump Import	10g
irep	Interface Repository	Not in 9i
isqlplusctl, isqlplussvc*	iSqlplus Control or Service	10g only. In 9i, you simply uncomment the line in oracle_apache.conf and start Apache. In 10g, you have to run isqlplusctl start to start the J2EE process (Windows service).
java2idl	Java to Interface Definition Language converter (not in 9i)
java2iiop	Java to Internet Inter-Orb Protocol converter (not in 9i)
java2rmi_iiop`	Java to Remote Method Invocation Internet Inter-Orb Protocol converter (not in 9i)
jpub	JPublisher	Not in 10g
kfed	Kernel Files Editor	Read ASM header e.g.: kfed read /dev/oracleasm/disks/VOL1. Type kfed -h for help. Also see below for kfod
kfod	“OSM Discovery  utility” (from oraInventory/logs/installActions.log or make output)	10g. Probably should be called ASM Disk Discovery utility. Note:359266.1 and Bug:4531822 have a little more info. Miladin Modrakovic has info about both kfod and kfed (Kernel File EDitor); kfed can be built per Note:553319.1
kgmgr`		Forum:486682.995: “a proprietary utility used by several of our client products”
kgpmon`
launch*	Program Launcher	E.g., on Windows, Oracle Net Assistant is not started by netasst program, which doesn’t exist; instead it’s launched by: %ORACLE_HOME%\bin\launch.exe “%ORACLE_HOME%\network\tools” netasst.cl. There’re many other .cl files scattered around which basically call jrew somejarfile.
launchem*	EM Launcher	Not in 10g
lbuilder	Locale Builder	Link in 10g to $ORACLE_HOME/nls/lbuilder/lbuilder; $ORACLE_HOME/ocommon/nls/lbuilder/lbuilder before 10g
lcsscan		10g
ldapadd		See Oracle Internet Directory Administrator’s Guide, Chapter “Managing Directory Entries”
ldapaddmt		see above
ldapbind		see above
ldapcompare		see above
ldapdelete		see above
ldapmoddn		see above
ldapmodify		see above
ldapmodifymt		see above
ldapsearch		see above
ldifmigrator	OID Migration tool	9iUNIX or 10g. Migrates data from application-specific repositories into Oracle Internet Directory.
ldifwrite		See Oracle Internet Directory Administrator’s Guide, Appendix A “Syntax for LDIF and Command Line Tools”. Not in 10g
lmsgen	NLS Binary Message File Generation	Used to generate .msb files like oraus.msb
loadjava		To load java classes into the database
loadpsp
localconfig		10g
locserv
lsnodes	List Nodes	lists all nodes or a specific node in a RAC configuration if RAC is installed (10g or conditionally installed in 9i)
lsnrctl	TNS Listener Control	Launches tnslsnr process. See tnslsnr below. Be careful when you type lsnrctl stop (or reload) on client side, unless DBA has set password on the server side (see Item 15 on my Idiosyncrasies page).
lxchknlb`
lxegen	NLS Calendar
lxinst	NLS Data Installation
mapsga`
maxmem`	Maximum Memory for a process	Oracle process virtual memory size is limited by the smaller of maxmem and ulimit -m. Windows doesn’t have this utility simply because a process on Windows can potentially use all memory minus kernel non-paged pool.
mergelib`		Not in 9i
mig	Oracle Migration/Upgrading	Not in 10g. 9i documentation says mig “converts the data dictionary and structures of an Oracle7 database into Oracle9i format”. 10g doesn’t support direct upgrade from Oracle7 so it stops shipping this tool.
migprep	Migration/Upgrading Preparation	Not in 10g. See above comments.
mkstore		10g
modifyprops*		Not in 9i
NAEGEN*	Diffie Hellman Parameter Generation	Not in 10g
namesctl	Oracle Names Server Control	Not in 10g
ncomp	Native Compiler
netasst`	Network Assistant	See launch above for how to start it on Windows (Not in 9i)
netca	Net Configuration Assistant	9iUNIX or 10g
netca_deinst	Net Configuration Assistant Deinstall	10g
netlaunch`
netmgr`
nid	DB New ID	Change DBID so a database cloned using the traditional method (instead of DUPLICATEd by RMAN) can register with the same RMAN catalog as the source DB. The syntax is nid target=sys/syspassword. (It’s not nid target=”sys/syspassword as sysdba” even if you need as sysdba for sqlplus). After running nid, you have to open resetlogs, and database resetlogs time and v$thread.enable_time will both be updated.
nmei, nmelproc, nmeo, nmesrvc, nmesrvcops, nmetm, nmo, nmocat, nmuct, nmupm		10g. “The nmo executable is responsible for authentication for the Agent installed with DB Control.” (812054.1)
nmudg		Not in 10g
nmumigrate (8i), nmumigr8 (9i)	Oracle Data Gatherer or DG Cartridge Migration	Not in 10g
oadj		Not in 9i
oadutil	Object Activation Daemon Utility	In Discoverer, type oadutil list to check services registered with OAD (not in 9i)
ociconv`	OCI Conversion
ocm		Not in 10g
ocm_oratclsh`		Not in 10g
ocopy*	Oracle file copy program	Allegedly the only tool you should use to copy open datafiles. There’s no difference between filesystem files copied by this and by DOS copy command. But if you use raw “files” (raw partitions used as Oracle datafiles), ocopy (or RMAN) has to be used.
ocrcheck, ocrconfig, ocrdump	Oracle Cluster Registry Check, Config and Dump utilities	10g. Run ocrcheck as root to do logical corruption check. Run ocrdump as root to get much more (perhaps all) info from OCR. Ocrconfig can only be run as root.
ocssd		10g
odisrv		Not in 10g
odisrvreg		Not in 10g
odma`		Not in 9i
odpreg		10g
oemapp
oemautil.bat*	Oracle Intelligent Agent
oemctl.bat*
oemevent	Oracle Intelligent Agent
oemPingOmsInternal.bat*
oerr`	Oracle Error	See my freeware Windowsoerr
oidadmin`	Oracle Internet Directory Admin	On Windows, you launch Oracle Directory Manager (a jrew application) in Integrated Management Tools program group.
oidca		10g
oidpasswd
oidprovtool	OID Provisioning Tool	9i UNIX or 10g. Used to create a subscription profile for OID subscribers such as Portal.
oidreconcile
ojspc	Oracle JSP Compiler	Not in 10g
ojvmjava		9i UNIX or 10g
okdstry	Kerberos utility
okinit	Kerberos utility
oklist	Kerberos utility
olsadmintool		10g
olsoidsync		10g
onrsd	Oracle Names client cache	Not in 10g
onsctl		10g
oo4ocodewiz*	Oracle Objects for OLE Code Wizard for stored procedures
operfcfg*	Oracle Perfmon Config
oracg	Oracle Class Generator	XML related. Ref.
oracle	Oracle RDBMS Kernel Executable	On UNIX, if dedicated configuration, every client process such as sqlplus or Toad, connects to tnslsnr, which fork()’s and then exec()’s oracle kernel program, $ORACLE_HOME/bin/oracle. The oracle process thus created is called a server process, or a shadow process to emphasize the client-server two-task nature. This server process is renamed to oracleSID (DESCRIPTION=(LOCAL=… for easy identification and troubleshooting. But its program (process text) is still this oracle binary. You should never directly run this binary; or you may get ORA-600 [12235] (see Note:33174.1; true for pre-11g). Oracle does not recommend changing the process priority. But some tests conclude it may improve DBWn performance if its priority is increased. You should also avoid stripping the oracle binary or you’ll lose symbol information in trace files. But you can certainly run nm on it to get symbols such as sgabeg in this file. You can find all undocumented SQL hints by running strings on it, but 11g provides v$sql_hint view.
		On Windows, there’s always only one oracle.exe process for one instance. Each server “process” is just a Windows thread inside this process. If you have multiple Oracle instances running in Windows, you can identify the process for a specific instance with tools such as tlist. Oracle supports changing process priority on Windows.
OracleAdNetConnect*	OracleAdNetConnect MFC Application
OracleAdNetTest*
oradim*		Used to create a Windows service named OracleServiceSID
oradism`	Oracle Dynamic Intimate Shared Memory	Used on Solaris to configure Dynamic ISM, so as to get the benefit of shared page table between processes while allowing for SGA to be not completely locked in physical memory
oraenv`		Setup Oracle Environment; run as . oraenv and pass SID to it
orakill*		Usage: orakill instance_name v$process.spid. Used to kill a specific thread inside oracle.exe process. Apparently orakill does a little more than just killing the thread because using a non-Oracle tool such as Sysinternals’s Process Explorer to kill a thread in oracle.exe doesn’t always make a hanging session and its lock go away; you may have to manually run oradebug wakeup 1 to wake up pmon (which should clear the lock and v$session / v$process, but the client app may still hang). If you can’t run orakill for any reason, use a tool like Process Explorer, followed by manual pmon wakeup.
orapipe		In 10g. Read Oracle XML Developer’s Kit Programmer’s Guide, Chapter “Pipeline Definition Language for Java”
orapki	Oracle Public Key Infrastructure utility	In 10g. Read Oracle Database Advanced Security Administrator’s Guide, Chapter “Configuring Secure Sockets Layer Authentication”
orapwd	Oracle Password utility	Used to create a password file for SYSDBA and SYSOPER connection. On Windows, you can also use oradim as a second choice (you have to delete and recreate if the password already exists).
orastack*	Oracle Stack modification	Used to resize the stack of an Oracle executable file such as oracle.exe, tnslsnr.exe (one way of preventing ORA-12500). Type the command at DOS to see an excellent description. (Not equivalent to pstack available on some UNIXes used to dump stack trace of a running process, but more like Solaris command ppgsz -o stack=… except it works on file not process)
oratclsh	Oracle TCL Shell	TCL programming language shell for Oracle. In spite of Oracle’s advocacy, few people actually used it and now it’s silently deprecated by Oracle, probably in favor of Perl. One trivial usage is to retrieve error messages on any OS (see examples here). Extensive use can be found in J. Morle’s Scaling Oracle8i. Not in 10g Windows and not working in 10g Linux/UNIX.
oraxml
oraxsl
osagent*	Visibroker OS Agent	Type osagent -? (or -h) to see Usage (not in 9i)
osdbagrp`	OS DBA Group	Returns DBA group name
osfind*		Visibroker utility to find OS agents (not in 9i)
osh`	Operator (or Oracle?) Shell	Some old versions of oraenv call this. Shell limits may be less if run under osh.
otrccol	Oracle Trace Collection	See Oracle Designing and Tuning for Performance, Chapter “Using Oracle Trace”. Not in 10g since Oracle Trace is deprecated.
otrccref		see above
otrcfmt		see above
otrcrep		see above
ott
ott8		Not in 10g
owhat`	Oracle what	Runs UNIX what command on programs listed in arguments to find patches; doesn’t work well; suggest you run what directly. Not in 10g
owm`	Oracle Wallet Manager	Windows has owm.dll and owm.cl, but not owm.exe or .bat
pagntsrv*		Not in 10g
passwdconvert		Not in 10g
printIOR*		Not in 9i
proc	Proc*C/C++ compiler
procob, procob18, procob18_32, procob32		Not in 10g
procui*		Not in 10g
profor`		Not in 10g
proxyserv`		Not in 10g
publish		Not in 9i
pupbld	Product User Profile Build	Creates Product User Profile tables for SQL*Plus
rawutl	Raw disk Utility	10g. rawutl -s /dev/xxx to view size
relink`		Not in 10g
remove		Not in 9i
remtool`		Not in 10g
repo_mig		10g
rman	Recover Manager
rtsora`, rtsora32`		Not in 10g
runInstaller`		Not in 10g. 10g Windows uses %oracle_home%\oui\bin\setup.exe.
sbttest`	SBT (Serial Backup Tape) Test	Note:942418.1
schema		XML schema validation
schemasync		Not in 10g
sclsspawn		10g
searchctl		10g
selecthome.bat* and others		10g only. 10g doesn’t have Home Selector in Start menu. Instead launch OUI to select Home. OUI runs selecthome.bat internally.
sess_sh		Not in 9i
setlinks*		You can use setlinks /d to show Windows devices. Not in 9i
sqlj	SQLJ engine	Not in 10g (SqlJ is deprecated)
sqlldr	SQL*Loader	See Oracle Utilities
sqlplus	SQL*Plus	10g has two new command line options: -C sets SQL*Plus compatibility version; -L attempts log on just once.
sqlplusw*	SQL*Plus for Windows	Not in 11g
statusnc	Status of Native Compiler	Probably checks sys.jaccelerator$status. Ref: Note:4889370
svrmgrl	Server Manager	Not in 9i. Use sqlplus ‘/ as sysdba’ instead (substitute ” for ‘ in Windows)
symfind`	Symbol Finder.	Documented in 10g Admin’s Reference for UNIX.
sysresv`	System Resources Verifier	Shows (or removes) IPC resources: shared memory segments and semaphores. Safer than ipcrm in removing the resources.
tdvapp*		Not in 10g
tkprof		Used to get more meaningful output from a session trace file
tnslsnr	TNS listener	You run this program from lsnrctl. Only run this program directly, as root, if you start listener on a port below 1024 on UNIX; even in this case, stopping the listener can be done with lsnrctl.
tnsping	TNS Ping	Note you can have two arguments, the second being the number of times the listener is pinged.
transx	XML Translator	See XML Developer’s Kit Programmer’s Guide, Chapter “TransX Utility”. Ref: Note:394803.1.
trcasst	SQL*Net Trace Assistant	Used to analyze SQL*Net trace file
trcsess	Session Trace	In 10g. See Database Performance Tuning Guide, Chapter “Using Application Tracing Tools”. It merges multiple SQL trace files for a session to be used by tkprof. Useful in Shared Server session trace. Before 10g, try OraSRP.
trcfmt`		See otrcfmt. Not in 10g
trcroute`	Trace Route	Hopefully it can offer more insight than tnsping into a failed Oracle Net connection.
tstshm`	Test Shared Memory	Note:50364.1
umu	User Migration Utility	Migrate database users to a directory. Documentation, Note:247744.1.
unzip		decompressing tool
vbdebug	VisiDebugger	Not in 9i
vbj		Not in 9i
vbj_convert		Not in 9i
vbjc		Not in 9i
vdoshell*		Not in 10g
vipca		10g RAC only
vmq*		Not in 10g
vppcntl	Oracle Data Gatherer Executable	“Control utility for the Data Gatherer” (marty.grinstead at oracle.com). Not in 9i
vppdc	Oracle Intelligent Agent Executable	“Data Gatherer – collects data for Performance Manager and Capacity Planner” (marty.grinstead at oracle.com). Not in 9i
vtushell*		Not in 10g
wrap	PL/SQL code Wrapper	Used to encrypt code to protect source code. Until 10g, string literals are not protected; “Hello world!” will be shown as “1Hello world!” in xxx_SOURCE. Also see Pete Finnigan’s unwrapper.
xml
xmlcg
xpautune*		Not in 10g
xpcoin*		Not in 10g
xpksh*		Not in 10g
xpui*		Not in 10g
xsl		10g
xsql
xsqlproxy*
xvm		10g
zip	Zip	Compression tool, the same as under /usr/bin (if on *NIX)

From 11g on, I no longer add new entries to the above table. Instead, a separate table is created for each version. Again, only interesting comments are made. Mostly based on binaries in $ORACLE_HOME/bin on x86_64 Red Hat Enterprise Linux 5.3 running Oracle 11.2.0.1.0 RAC, supplemented with 11.2.0.2.0.

adrci	Automatic Diagnostic Repository Command Interpreter	See numerous articles in documentation, and its Incident Packaging Service (IPS) in Note:738732.1
amdu	ASM Disk Usage	Creates amdu_time/report.txt for ASM disk usage. Get help by amdu help=y
aqxmlctl	AQ XML Control	See Deploying the Oracle Database XML Servlet
bndlchk	Bundle Check	Shell script to check what libvns contains to determine if you have Enterprise Edition or Standard Edition installed. Run ls -l $ORACLE_HOME/install/.ee $ORACLE_HOME/install/.std after you run this script to see which file was created.
chopt	Change Option	Shell script to call chopt.pl to enable or disable optional components (as seen in v$option). See chopt.ini for detailed make commands to change these options
cluvfy	Cluster Verification
commonenv	Common Environment	Seems for EM only
crsdiag.pl	CRS Diagnostics	Creates gzipped tar files for OCR, OS and CRS data to send to Oracle support. Run as root
db2gc		Script to configure Oracle EM dbconsole mode for a software only install
dbfs_client	DBFS Client	Using the DBFS Command Interface
dg4odbc	Database Gateway for ODBC	Configure Oracle Net Listener for the Gateway
diagsetup	11g installation Diag Setup	See Note:818727.1 for usage
diskmon.bin	I/O Fencing and SKGXP HA monitoring daemon	Logs are at $CRS_HOME/log/`hostname -s`/diskmon
dsml2ldif	Directory Services Mark-up Language to LDIF conversion
dvca	Database Vault Configuration Assistant
eusm	?	?
extjob	External Job Process on UNIX/Linux
genorasdksh	Shell script to Generate Oracle SDK	Creates $ORACLE_HOME/lib/liborasdk.so*
gnsd	Grid Naming Service Daemon	No need to use it
jssu	Job Scheduler related	Note:976049.1: “Jssu is called by the dbms_scheduler when credentials are used”
mkpatch	Make Patch	Creates a patch file
olsnodes	Oracle List Nodes	The new -t option shows whether you pinned the node with crsctl pin css -n (used when pre-10g database is installed with 11g clusterware), and -c shows the clustername.
orabase	Oracle Base	Returns ORACLE_BASE; first appears in 11gR1; checks ORACLE_BASE in $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml on UNIX/Linux (Ref)
orion	Oracle IO Numbers	I/O benchmark tool. Type orion -help for help
pafctl	Provisioning Advisor Framework Control	Oracle Application Management Pack for Oracle E-Business Suite related. Ref:
platform_common`	Platform Common	“Script to export platform specific variables”, called by dbca, dbua, netca, rconfig.
plshprof	PL/SQL Hotspot Profiler	Note:763944.1 and Documentation
racgeut	?	Looks like a command execution wrapper. Usage: racgeut [-e environment_setting] timeout command. Try racgeut -e A=B 30 /bin/sleep 20 &, followed by ps eww $!. You’ll see the added environment variable and assigned value A=B. You can add more environment variables with more “-e name=value” strings.
racgmain	RAC Global Main script	It’s better to run it with racgwrap. You’re supposed to be able to run it to check resource status (not recommended, documented way preferred, e.g. srvctl|crsctl status): racgmain resource_full_name check, followed by echo $?, and read $ORACLE_HOME/log/`hostname -s`/racg/resourcename.log. But 11gR2 seems to miss the required racgimon so it always throws error.
racgwrap	RAC Global Wrapper script	Wrapper for racgmain. Control tracing by passing _USR_ORA_DEBUG to racgmain (see above).
renamedg	Raname Disk Group	New in 11gR2. See Note:948040.1 for usage.
setasmgid	Set ASM Group ID	Ref
skgxpinfo	OS Kernel Generic Inter-Process-communication Info	New in 11.2.0.2. Returns IPC protocol, udp by default
srvconfig	Server Config	Before 11gR2, you can get some config info about server and database components: srvconfig -exp tmpfile. Beginning with 11gR2, it’s only used during upgrade and downgrade of a database. See Note:953769.1.
trcsess	Trace Sessions	Enable SQL trace from command line
uidrvci	?	ADR related. E.g., uidrvci /u01/app/oracle/diag/rdbms/db_name/instance_name/trace getSchema INC_METER_SUMMARY, where INC_METER_SUMMARY is an .ams file under the trace/metadata subdir of the directory in the second arg. This getSchema command shows the table definition of v$incmeter_summary (summary of incident meters). Not sure what use this command really has.
wrc	Workload Replay Client	Documentation, Note:445116.1, Note:748895.1.
xmlwf	XML Well-Formedness check	An extra copy of standard UNIX/Linux command (just like unzip)

11.2 New Features

1. ENABLE_DDL_LOGGING

ENABLE_DDL_LOGGING enables or disables the writing of DDL statements to the alert log.

By default the parameter is set to False, when you set it to True you can capture the DDL statements in the alertlog of the database, which is very helpful when you want to audit the DDL statements.

 

11.2 Hidden Parameters

In patch set 11.2.0.2 a new behaviour for datafile write errors has been implemented. With this release ANY write error to a datafile will cause the instance to abort. Before 11.2.0.2 those errors usually led to an offline datafile if the database operates in archivelog mode (your production database do, don’t they?!) and the datafile does not belong to the SYSTEM tablespace. Internal discussion found this behaviour not up-to-date and alligned with RAC systems and modern storages. Therefore it has been changed and a new underscore parameter got introduced.

_DATAFILE_WRITE_ERRORS_CRASH_INSTANCE=TRUE
This is the default setting´and the new behaviour beginning with Oracle 11.2.0.2

If you would like to revert to the pre-11.2.0.2 behaviour you’ll have to set in your init.ora/spfile this parameter to false. But keep in mind that there’s a reason why this has been changed.
You’ll find more info in MOS Note: 7691270.8 and this topic in the current version of the slides on slide 255.

 

Information shared from Oracle Blog, link to the blog:

http://blogs.oracle.com/UPGRADE/entry/new_hidden_parameters_in_oracl

 

Handy Database Queries

I. Query to List the transactions and associated session information on the database:

 SELECT  vs.sid,vs.serial#,vt.status, vt.start_time, vs.username, vs.module,
         vs.action, vs.event
    FROM v$transaction vt, v$session vs
   WHERE vt.addr = vs.taddr
ORDER BY vt.start_time ASC;

II. Query to find hidden parameters in 11g database:

 SELECT a.ksppinm NAME, b.ksppstvl VALUE, b.ksppstdf deflt,DECODE (a.ksppity,1, ‘boolean’,2, ‘string’,3, ‘number’,4, ‘file’,a.ksppity) TYPE,a.ksppdesc descriptionFROM SYS.x$ksppi a, SYS.x$ksppcv b WHERE a.indx = b.indx ORDER BY NAME;