FINDING ORACLE HOMES AND DATABASES ON LINUX

Reason: 
After DB consolidation on one host it is hard to remember how many $ORACLE_HOME and home many databases under each oracle home.

How quickly to identify which Oracle process runs out of which $ORACLE_HOME on Linux(put this script to findhomes_bibo.sh file and execute chmod +x findhomes_bibo.sh)

Link to file findhomes_bibo.sh

echo -e "\033[1mDB_NAME\t\tORACLE_HOME\033[0m"
for ora in $(pgrep -lf _pmon_ | awk '{print $1}'); do for pid in $(pgrep -lf _pmon_ | grep $ora | awk '{print $2}' | cut -d_ -f 3); \
do echo -n $pid; done && ls -l /proc/$ora/exe | awk -F'>' '{ print $2 }' | sed 's/bin\/oracle$//'; done | sort -k2,2 | sed 's/\s/\'$'\t\t''/g'

You can find some other scripts in Internet and Oracle written script also, but I prefer my script because output is simple for understanding.

UPDATING THE RDBMS DST VERSION IN 11GR2 USING DBMS_DST

Reason: there is one BUG with Time Zone(TZ or DST_PRIMARY_TT_VERSION) and Oracle Streams technology. Well between two databases should be the same TZ version. 

In my case on one DB there was TZ 11 and on another 14 and there was ORA-600. Oracle server versions and editions are the same 11.2.0.2 x64 on RHEL 5. Information applies to any platform.

So how to update TZ from 11 to 14:

My ENV

USERNAME              HOST_NAME           VERSION

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

SYS                             lv-oracle-test1             11.2.0.2.0

SQL >

  SELECT   PROPERTY_NAME, SUBSTR (property_value, 1, 30) VALUE
    FROM   DATABASE_PROPERTIES
   WHERE   PROPERTY_NAME LIKE 'DST_%'
ORDER BY   PROPERTY_NAME;

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         11

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL>  exec DBMS_DST.BEGIN_PREPARE(14);

PL/SQL procedure successfully completed.

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         11

DST_SECONDARY_TT_VERSION       14

DST_UPGRADE_STATE              PREPARE

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

TRUNCATE TABLE sys.dst$affected_tables;

TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL>

Table truncated.

SQL>

Table truncated.

set serveroutput on

BEGIN

DBMS_DST.FIND_AFFECTED_TABLES

(affected_tables => 'sys.dst$affected_tables',

log_errors => TRUE,

log_errors_table => 'sys.dst$error_table');

END;

SQL>   2    3    4    5    6    7  /

PL/SQL procedure successfully completed.

SQL> SQL> SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

SQL> EXEC DBMS_DST.END_PREPARE;

A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

 ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         11

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL>

SQL>

SQL> shutdown immediate

startup upgrade;

set serveroutput on

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

Database closed.

Database dismounted.

purge dba_recyclebin;

ORACLE instance shut down.

SQL> ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2233088 bytes

Variable Size             784338176 bytes

Database Buffers          251658240 bytes

Redo Buffers                5656576 bytes

Database mounted.

Database opened.

SQL> SQL>   2    3    4

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         11

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL>

DBA Recyclebin purged.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL>

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

 ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       11

DST_UPGRADE_STATE              UPGRADE

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

OWNER                          TABLE_NAME                     UPG

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

SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES

SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES

SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES

SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES

SYSMAN                         MGMT_PROV_ASSIGNMENT           YES

SYSMAN                         MGMT_PROV_BOOTSERVER           YES

SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES

SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES

SYSMAN                         MGMT_PROV_IP_RANGE             YES

SYSMAN                         MGMT_PROV_NET_CONFIG           YES

SYSMAN                         MGMT_PROV_OPERATION            YES

SYSMAN                         MGMT_PROV_RPM_REP              YES

SYSMAN                         MGMT_PROV_STAGING_DIRS         YES

SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES

STREAMADMIN                    AQ$_DST_BOOK_ENTRIES_T_L       YES

STREAMADMIN                    AQ$_DST_BOOK_ENTRIES_T_S       YES

STREAMADMIN                    AQ$_DST_MSGLOG_MESSAGES_T_L    YES

STREAMADMIN                    AQ$_DST_MSGLOG_MESSAGES_T_S    YES

18 rows selected.

SQL>

SQL>

shutdown immediate

startup

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2233088 bytes

Variable Size             784338176 bytes

Database Buffers          251658240 bytes

Redo Buffers                5656576 bytes

Database mounted.

Database opened.

SQL> SQL> SQL>

SQL>

SQL>

SQL>

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> set serveroutput on

VAR numfail number

log_errors_table => 'SYS.DST$ERROR_TABLE',

BEGIN

DBMS_DST.UPGRADE_DATABASE(:numfail,

parallel => TRUE,

log_errors => TRUE,

log_errors_table => 'SYS.DST$ERROR_TABLE',

log_triggers_table => 'SYS.DST$TRIGGER_TABLE',

error_on_overlap_time => FALSE,

error_on_nonexisting_time => FALSE);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

END;

SQL> SQL>   2    3    4    5    6    7    8    9   10   11  /

Table list: "SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_RPM_REP"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_OPERATION"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_IP_RANGE"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_CLUSTER_NODES"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_ASSIGNMENT"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"

Number of failures: 0

Table list: "STREAMADMIN"."AQ$_DST_MSGLOG_MESSAGES_T_S"

Number of failures: 0

Table list: "STREAMADMIN"."AQ$_DST_MSGLOG_MESSAGES_T_L"

Number of failures: 0

Table list: "STREAMADMIN"."AQ$_DST_BOOK_ENTRIES_T_S"

Number of failures: 0

Table list: "STREAMADMIN"."AQ$_DST_BOOK_ENTRIES_T_L"

Number of failures: 0

Failures:0

PL/SQL procedure successfully completed.

VAR fail number

BEGIN

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);

END;

  /

An upgrade window has been successfully ended.

Failures:0

PL/SQL procedure successfully completed.

SQL>

SQL>

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

 ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION

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

timezlrg_14.dat              14

more [ID 977512.1]

MULTIPLE QUERIES WITH EXPDP DATA PUMP

Reason:
If you don't want to pump big tables you can use multiple QUERY predicate.
expdp  "'/ as sysdba'"   parfile=expdp.dat
or
expdp  system/manager@DB parfile=expdp.dat 

Note:
cat expdp.dat file

DUMPFILE="dat.dmp"
LOGFILE="exp_dat.log"
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=('RMS_ACQ_B')
QUERY='RMS_ACQ_B.RISKMAN_AUTH_TMP:"WHERE rownum<1"'
QUERY='RMS_ACQ_B.MSG_HEAD_ARMS:"WHERE rownum<1"'
QUERY='RMS_ACQ_B.RISKMAN_SLIPS_TMP:"WHERE rownum<1"'
QUERY='RMS_ACQ_B.RISKMAN_SLP_FULL:"WHERE rownum<1"'
CONTENT=ALL
JOB_NAME='v1'

REUSE_DUMPFILES=Y #COMPATIBLE=11.2 CAN BE OVERWRITTEN
#COMPRESSION=ALL #COMPATIBLE=11.2

example:

[oracle@test ~]$ expdp SYSTEM/manager parfile=expdp.dat

Export: Release 11.2.0.2.0 - Production on Mon Sep 26 17:19:27 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security and Real Application Testing options
Starting "RMS_PREV"."V2":  system/******** parfile=expdp.dat
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 32.11 GB
Processing object type SCHEMA_EXPORT/USER
[...]
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
. . exported "RMS_ACQ_B"."RISKMAN_AUTH_TMP"              16.21 KB       0 rows
. . exported "RMS_ACQ_B"."MSG_HEAD_ARMS"                 20.10 KB       0 rows
. . exported "RMS_ACQ_B"."RISKMAN_SLIPS_TMP"             14.89 KB       0 rows
. . exported "RMS_ACQ_B"."RISKMAN_SLP_FULL"              13.17 KB       0 rows
. . exported "RMS_ACQ_B"."RISKMAN_AUTH_TMP_NEW"          320.7 MB  723385 rows
 

If you need to  exclude TEST tables
(syntax note add \ if QUERY= not on Windows or not in parfile; on Linux must prefix with \ most punctuation charactersexample:expdp user/password QUERY='user.table:\"WHERE rownum\<1\"')

example:

expdp SYSTEM/manager DUMPFILE=dat.dmp EXCLUDE=table:\"like \'%TEST%\'\" QUERY=RMS_ACQ_B.RISKMAN_AUTH_TMP:\"WHERE rownum\<1\" LOGFILE=exp_dat.log SCHEMAS=RMS_ACQ_B