What is the difference between a BEQUEATH connection and an IPC connection?

A bequeath connection

runs on your local host

bypasses the listener

the protocol creates the server process for you directly

An IPC (Inter-Process Communication) connection

will use the native protocol on each OS, but uses the generic term "IPC" for all of them

can only be used when the Client and Server reside on the same host

can only be used by having the Client connect through the Oracle Listener

the Database Listener must be configured to listen on an IPC endpoint

the listener spawns the server process for you

Example setup:

Listener.ora
 LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     )
   )

tnsnames.ora:

proddb01_ipc =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = proddb01)
     )
   )

Connect to your database locally:

sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0. Production on Thu Jan 22 15:35:44 2015

Copyright (c) 1982,2013, Oracle. All rights reserved.

SQL> connect scott/tiger
Connected.

From another window, create another session:

sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0. Production on Thu Jan 22 15:35:44 2015

Copyright (c) 1982,2013, Oracle. All rights reserved.

SQL> connect scott/tiger@proddb01_ipc
Connected.

Check the connections and their types:

SELECT S.SID, S.OSUSER,S.PROGRAM,S.USERNAME,S.MACHINE, SCI.NETWORK_SERVICE_BANNER,S.LOGON_TIME,S.STATUS
 FROM V$SESSION S INNER JOIN V$SESSION_CONNECT_INFO SCI
 ON S.SID = SCI.SID
 WHERE S.USERNAME = UPPER('scott')
 AND SCI.NETWORK_SERVICE_BANNER LIKE '%IPC%'
 OR  SCI.NETWORK_SERVICE_BANNER LIKE INITCAP('%BEQ%')
 AND S.TYPE <> 'BACKGROUND'
 ORDER BY LOGON_TIME;

And here is the output. Notice how the first session (.. / as sysdba) results in a Bequeath session, while the other one ( ...@proddb_ipc) results in a session using IPC:

SID	OSUSER	PROGRAM	USERNAME	MACHINE	NETWORK_SERVICE_BANNER	LOGON_TIME	STATUS
9	oracle	sqlplus@myserver.mydomain.com (TNS V1-V3)	SCOTT	myserver.mydomain.com	Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production	22.01.2015 15:35:49	INACTIVE
1160	oracle	sqlplus@myserver.mydomain.com (TNS V1-V3)	SCOTT	myserver.mydomain.com	Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 11.2.0.4.0 - Production	22.01.2015 15:40:44	INACTIVE

A complete script for gathering system, data dictionary and fixed objects stats

alter session set nls_language='AMERICAN';
set timing on
set serveroutput on
set lines 200
col pname format a20

BEGIN
DBMS_STATS.GATHER_DICTIONARY_STATS (
Estimate_Percent  => DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt        => 'FOR ALL COLUMNS SIZE AUTO'
,Degree            => NULL
,Cascade           => DBMS_STATS.AUTO_CASCADE
,granularity       => 'AUTO'
,No_Invalidate     => DBMS_STATS.AUTO_INVALIDATE);
END;
/

BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/

SELECT PNAME, PVAL1 FROM SYS.AUX_STATS$;

PROMPT Gather workload system stats, sample for 1 hour
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS (
GATHERING_MODE=>'INTERVAL',
interval => 60,
statid   => 'DAYTIME');
END;
/

SELECT PNAME, PVAL1 FROM SYS.AUX_STATS$;
exit

Tim Hall has written a good article about how the gather_system_stats procedure works, read it at www.oracle-base.com

How to relocate the block change tracking file

To relocate the block change tracking file you have two options:

1) shutdown database, mount database, update control file, open database

sqlplus / as sysdba
shutdown immediate
exit
 -- Move the block change tracking file to the new location using the appropriate os utility. --
sqlplus / as sysdba
startup mount
ALTER DATABASE RENAME FILE 'ora_home/dbs/change_trk.f' TO '/new_disk/change_trk.f'; 
ALTER DATABASE OPEN;

OR

2) disable and re-enable block change tracking, and point to the new location when re-enabling.

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';

See Section "4.4.4.3 Moving the Change Tracking File" in the Oracle Documentation regarding this feature.

Query the registry!

To view the different options installed in the database, you should use DBA_REGISTRY, as follows:

set lines 200 pages 100
col comp_name format a40
SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY;

Example output:

SQL> SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY;

COMP_NAME                                COMP_ID                        VERSION                        STATUS
---------------------------------------- ------------------------------ ------------------------------ --------------------------------------------
Oracle Text                              CONTEXT                        11.2.0.4.0                     VALID
Oracle Application Express               APEX                           3.2.1.00.12                    VALID
Oracle Multimedia                        ORDIM                          11.2.0.4.0                     VALID
Oracle XML Database                      XDB                            11.2.0.4.0                     VALID
Oracle Expression Filter                 EXF                            11.2.0.4.0                     VALID
Oracle Rules Manager                     RUL                            11.2.0.4.0                     VALID
Oracle Workspace Manager                 OWM                            11.2.0.4.0                     VALID
Oracle Database Catalog Views            CATALOG                        11.2.0.4.0                     VALID
Oracle Database Packages and Types       CATPROC                        11.2.0.4.0                     VALID
JServer JAVA Virtual Machine             JAVAVM                         11.2.0.4.0                     VALID
Oracle XDK                               XML                            11.2.0.4.0                     VALID
Oracle Database Java Packages            CATJAVA                        11.2.0.4.0                     VALID

12 rows selected.

How to use regexp_substring to extract the 5-digit version

SELECT BANNER, REGEXP_SUBSTR(BANNER, '[[:digit:]]+[[:punct:]]+.[^-]{1,}',1,1) "5 digit Version" 
FROM V$VERSION 
WHERE BANNER LIKE 'Oracle Database%';

Getting ORA-01031: insufficient privileges when data dictionary table is being used in a view

I must admit I have been consulted in these situations before, but since then I had forgotten how it worked and failed to take notes on how to solve it.

So here it is: a user is getting a run-time error ORA-01031: insufficient privileges when accessing his view.
The view is based on his own objects and a lookup to the dynamic performance view V$DATABASE.

Example:

connect scott/tiger

CREATE VIEW MYVIEW AS
SELECT 
FROM MYTABLE MT,
     V$DATABASE DB
WHERE....
AND... ;

If the user has only SELECT ANY TABLE, Oracle will return runtime error ORA-01031 when the view is compiled.

However, if you give user scott the SELECT privilege on the table directly:

GRANT SELECT ON V_$DATABASE TO SCOTT;

then Oracles rules for object creation is honored and the runtime error will disappear.

How to use multiple delimiters in awk

When creating a script for cloning, I wanted to perform some initial checks. One of them was to make sure the listener for the source database was up. For this, I wanted to use the tnsping utility and grep for the result:

tnsping mydb

where mydb corresponds to the tnsnames.ora alias of interest.

* If there was an error, exit script.
* If listener was up, continue.

I directed the output to a file, and after that grepped for the string that would indicate success or failure.
Problem was that the files would look very different and the interesting string would need to be grepped for using two different delimiters.

When successful, the file would look as follows:

OK (10 msec)

When not successful, the output would be:

TNS-03505: Failed to resolve name

So how to pull out either "TNS" or "OK" and use these values programmatically further on in the script?

I accomplished this task simply by using the notation '[-(]'in my awk statement:

REMOTE_LSNR_STATUS=`cat ${LOG_DIR}/test_tnsping.log | egrep -e 'TNS-|OK' | awk -F '[-(]' '{ print $1 }'`