Modifying collection schedule for OEM metric Tablespace Allocation.

Environment:
Oracle DB 11.2, Standard Edition One with DB console.
Windows 2003 R2, 32bit

Action:
Default metric (Tablespace Allocation) collection interval is "Every 7 Days", which can be found on page "All Metrics" in DB console.

Go to %ORACLE_HOME%\sysman\admin\default_collection
and edit file database.xmlp

Find "Category: Tablespace Allocation" and few rows down:
      IntervalSchedule INTERVAL="7" TIME_UNIT="Day"

Change the interval number. Time unit can also be Hr or Min. Save the file.
Restart the oracle agent.
In this particular case by restarting windows service OracleDBConsoleSID, which bounces the agent as well.

"All Metrics" in DB console will still show 7 days interval. Check the real metric collection by the following SQL.
select * from  SYSMAN.MGMT$METRIC_DETAILS
where METRIC_LABEL in ('Tablespace Allocation') and key_value='SYSTEM'
order by collection_timestamp desc;

Configuring Oracle Database 11g Gateway for ODBC MS SQL Server

Environment

Oracle database 11.2
Oracle Linux Server release 5.7
Microsoft® ODBC Driver 11 for SQL Server - RedHat Linux
Microsoft SQL Server 2005,2008R2

Installing ODBC driver

Install the driver according to MS http://www.microsoft.com/en-us/download/details.aspx?id=36437

To verify that the ODBC Driver on Linux was registered successfully, execute the following command:
odbcinst -q -d -n "ODBC Driver 11 for SQL Server"

edit ~/.odbc.ini
and add

[infraUAT]
Driver=ODBC Driver 11 for SQL Server
Description=My Sample ODBC Database Connection
Trace=Yes
Server=gtpsql2
Port=1433
Database=InfraEnt_ITSD_UAT

"Driver" has to be same as output of odbcinst -q -d, which comes from /etc/odbcinst.ini

Test it
odbcinst -q -s
[infraUAT]

isql -v infraUAT user password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit

Configuring Gateway

cd $ORACLE_HOME/hs/admin

If you use initdg4odbc.ora, then dg4odbc will be SID.

edit initdg4odbc.ora

S_FDS_CONNECT_INFO = infraUAT

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

# you need to match HS_LANGUAGE to SQLS

HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252

# you might need the following HS_

#HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL

#HS_NLS_LENGTH_SEMANTICS=CHAR

#nvarchars of a SQL Server are UCS2 character set

#HS_NLS_NCHAR=UCS2

# ODBC specific environment variables

set ODBCINI=/home/oracle/.odbc.ini

HS_LANGUAGE needs to match code page of SQL Server.

When Oracle use unicode AL32UTF8, it will fail to connect. See Doc ID 756186.1

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[

edit listener.ora and add new SID_DESC, where SID matches initdg4odbc.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

        (SID_NAME = dg4odbc)

        (ORACLE_HOME = /opt/oracle/product/se1/11.2.0.3)

        (PROGRAM = dg4odbc)

        (ENVS='LD_LIBRARY_PATH=/usr/lib64:/opt/microsoft/msodbcsql/lib64:/opt/oracle/product/se1/11.2.0.3/lib')

    )

  )

Restart listener

configure  tnsnames.ora

HS1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = tcp)(HOST = )(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = dg4odbc)

    )

    (HS=OK)

  )

Configure DB Link

CREATE PUBLIC DATABASE LINK INFRA

 CONNECT TO DSUSER

 IDENTIFIED BY password

 USING 'hs1';

Problem 1 - code page conversion

Some symbols are not converted correctly, e.g. Microsoft double quote or single quote. I have not tested non-latin alphabets.

SELECT short_problem  FROM CL_CALL_LOGGING@infra where call_number=4234;

output

The printer on Level 5 is showing ‘perform printer maintenance’. 

Solution 1

as Oracle NLS_CHARACTERSET = AL32UTF8 (check view NLS_DATABASE_PARAMETERS), create a view in SQLS to CAST this column to unicode, then you can select from this view from Oracle.

CREATE VIEW TEST_VIEW1 AS

SELECT CALL_NUMBER, CAST(SHORT_PROBLEM AS NVARCHAR(200)) AS SHORT_PROBLEM

FROM DBO.CL_CALL_LOGGING;

in Oracle

SELECT CALL_NUMBER, SHORT_PROBLEM FROM TEST_VIEW1@INFRA WHERE CALL_NUMBER=4234;

output

The printer on Level 5 is showing ‘perform printer maintenance’.

Problem 2 - multibyte characters

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation {01004}

ORA-02063: preceding 2 lines from INFRA

If in SQLS a column is described as VARCHAR(200) and a value length is 200 and it contains some symbols which become multi-byte, then in Oracle it becomes longer than 200 bytes.

Solution 2

The same as Solution 1, CAST it to unicode on SQLS.

If you create similar or the same table in Oracle, define length in chars, e.g. VARCHAR(200 char)

It does not have to be NVARCHAR as in my case oracle DB is already UTF8.

Problem 3 - varchar(max)

When selecting varchar(max) from SQLS:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation {01004}

ORA-02063: preceding 2 lines from INFRA

Solution 3.1

I haven't found a good solution for this. You can CAST(COLNAME AS TEXT) or as NTEXT in SQLS, in thin case you receive LONG type in Oracle (can be saved and converted to CLOB). Which is ok for most values (Latin characters), but it still has wrong conversion for multi-byte/non-latin characters.

Solution 3.2 - other ODBC drivers

try other drivers, e.g. Easysoft or Datadirect. I haven't tested it.

Solution 3.3 - non Oracle ETL

IBM Datastage works ok, when selecting as unicode CAST(col as NVARCHAR(max)) and keeping as LongNVarchar in DataStage to avoid Datastage conversion. It inserts into Oracle CLOB and looks ok.

Microsoft SSIS converts correctly, if destination table in Oracle is defined as NCLOB.

Documentation

Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager (Doc ID 756186.1)

How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1)

Regular expressions for CSV files

SQL Server Import had troubles to import csv file, when some fields contained CRLF(\r\n).
Row delimiter needs to be changed.
Notepad++ was used.

It matches 15 columns (14 comma delimiters). CRLF is end of line. Some text columns contain CRLF(\r\n).
((?:(?:"(?:(?:""|[^"])+)"|(?:[^,]*)),){14}.*?)\r\n
to add a pipe as row delimiter replace with \1|\r\n

it matches $$ delimiter 7 fields (6 delimiters) and end of line \r MAKE sure . dot matches end of LINE!
(?:.*?\$\$){6}.*?\r
((?:.*?\$\$){13}.*?)\r\n   to add a pipe as row delimiter replace with \1|\r\n 

to search for $$ with grep (Linux or Cygwin), use single quotes and escape last dollar
grep -e '$\$' filename.csv

CPU and system info on Windows

Just few examples of wmic utility to get CPU and system info
wmic cpu get /format:list
or
wmic cpu list full /format:list

to get some particular field
wmic cpu get deviceid,SocketDesignation

system info
wmic COMPUTERSYSTEM list full /format:list
wmic COMPUTERSYSTEM get name,model,NumberOfProcessors /format:list

help
wmic /?
wmic cpu /?
etc.

systeminfo
use /S to connect to remote host

SQLNET AUTHENTICATION_SERVICES in Windows environment

Environment:
Windows Server 2003 R2, Oracle 11.2.0.4


windows service OracleServiceSID is up
windows account is added in local group ORA_DBA
Oracle password file is created

SQLNET.AUTHENTICATION_SERVICES in sqlnet.ora on server	none	nts	all
/ as sysdba	ORA-01017: invalid username/password; logon denied	ok	ORA-12641: Authentication service failed to initialize
local: sqlplus sys as sysdba	ok	ok	ORA-12641: Authentication service failed to initialize
local: sqlplus sys@tnsname as sysdba	ok	ok	ORA-12641: Authentication service failed to initialize
remote: sqlplus sys@tnsname as sysdba	ok	ORA-12638: Credential retrieval failed	ORA-12638: Credential retrieval failed
local: sqlplus system	ok	ok	ORA-12641: Authentication service failed to initialize
local: sqlplus system@tnsname	ok	ok	ORA-12641: Authentication service failed to initialize
remote: sqlplus system@tnsname	ok	ORA-12638: Credential retrieval failed	ORA-12638: Credential retrieval failed

for ORA-1031: Insufficient Privileges, check the following Oracle support document:

Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA (Doc ID 730067.1)

Using Oracle collection instead of cursor

Environment: Oracle 11.2.0.3

ORA-01555 "Snapshot too old" occurs when "fetch across commits" is used (committing inside of open cursor loop).
Possible solution is to use Oracle collection instead of cursor:

set serveroutput on
declare
    TYPE nested_typ IS TABLE OF NUMBER;
    t1   nested_typ;
begin
   select AGENT_INSURER_ID BULK COLLECT into t1
     from AGENT_INSURER 
    where 1=1;  -- conditions
--
  FOR i IN t1.FIRST..t1.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('ROW_NUM: '||i||' AGENT_INSURER_ID: '||t1(i));
    procedure1(t1(i)); -- which has COMMIT inside
  END LOOP;
end;
/