Monday, October 17, 2011

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)


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.

Thursday, October 6, 2011

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]