Author Archives: kuperusm

remove last git push

Posted on by
2

Show last commit comment first:
git log –name-status HEAD^..HEAD

Then, remove the last commit and push:
git reset –hard HEAD~1

See also: https://git-scm.com/book/en/v2/Git-Tools-Rewriting-History

ActiveMQ Illegal character +0x16

Posted on by
Reply

ActiveMQ Illegal character +0x16 HttpChannelOverHttp {\x16<<<\x03\x01\x00\xD6\x01\x00\x00\xD2\x03\x03\xDd#\x13\xBb\xA3…\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00}

Reason is, you’re listening HTTP on an HTTPS port. Unfortunately restarting the ActiveMQ won’t help.

For me, what helped was:

  1. stop the ActiveMQ
  2. clear the maps in the map “..\activemq\data\tmp”
  3. clear the files in the map “..\activemq\data\kahadb”

Oracle TimeZone and daylight savings

Posted on by
Reply

With the upcoming daylight savings weekend, it’s always nice to (re-)discuss datetime and timezone problems.

In our current project we receive EDI files with the customers estimated time of arrival and estimated time of departure. The EDI file contains strings (not dates). Note: they indicate the localtime for which the customer will arrive and depart.

In the database we store dates as timestamps in UTC format. Now, on our different test servers we get different results for some testcases for when they arrive on saturday and leave on monday. Note: on our client side application the localtime is displayed, so a conversion from UTC to the screen is also necessary.

Consinder the following (simplified) table and row indicating arrival and departure at locatime 8:00 AM

CREATE TABLE EDITABLE
( eta_str VARCHAR2(50)
, etd_str VARCHAR2(50)
);
INSERT INTO EDITABLE(eta_str, etd_str) VALUES ('20180324080000','20180326080000');

During the conversion from EDI strings to timestamp we use the following:

set serveroutput on size 100000
declare
  lc_FormatMask CONSTANT VARCHAR2(50) := 'YYYYMMDDHH24MISS';
  l_eta_ts      TIMESTAMP(6);
  l_etd_ts      TIMESTAMP(6);
  l_eta_gui     DATE;
  l_etd_gui     DATE;
begin
  /* get the EDI strings as TimeStamps */
  select Sys_Extract_UTC(To_Timestamp(to_char(to_date(eta_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
       , Sys_Extract_UTC(To_Timestamp(to_char(to_date(etd_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
  into   l_eta_ts
       , l_etd_ts
  from   EDITABLE;

  /* show what is stored */
  dbms_output.put_line('Eta in: ' || to_char(l_eta_ts, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd in: ' || to_char(l_etd_ts, 'YYYYMMDDHH24MISS'));

  /* conversion used by GUI */
  l_eta_gui := CAST(from_tz(l_eta_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);
  l_etd_gui := CAST(from_tz(l_etd_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);

  /* show what is displayed */
  dbms_output.put_line('Eta gui: ' || to_char(l_eta_gui, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd gui: ' || to_char(l_etd_gui, 'YYYYMMDDHH24MISS'));
end;
/

Now, on one server there was some interesting output shown:
Eta in: 20180324070000
Etd in: 20180326070000
Eta gui: 20180324080000
Etd gui: 20180326090000
The input datetime is not converted to the proper UTC datetime, while the output converts it correctly to locatime. Resulting in the wrong time.

After looking too long to the output of SELECT * FROM SYS.NLS_SESSION_PARAMETERS; noticing nothing strange, the following statement was issued:

SELECT SESSIONTIMEZONE FROM DUAL;

Resulting in ‘+01:00’ for the particular server. On all other servers the result was ‘Europe/Amsterdam’

After adding

EXECUTE IMMEDIATE 'ALTER SESSION SET TIME_ZONE = '''||l_time_zone||'''';

in the code:

set serveroutput on size 100000
declare
  lc_FormatMask CONSTANT VARCHAR2(50) := 'YYYYMMDDHH24MISS';
  l_eta_ts      TIMESTAMP(6);
  l_etd_ts      TIMESTAMP(6);
  l_eta_gui     DATE;
  l_etd_gui     DATE;
  l_time_zone   CONSTANT VARCHAR2(30) := 'Europe/Amsterdam';
begin
  EXECUTE IMMEDIATE  'ALTER SESSION SET TIME_ZONE = '''||l_time_zone||'''';

  /* get the EDI strings as TimeStamps */
  select Sys_Extract_UTC(To_Timestamp(to_char(to_date(eta_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
       , Sys_Extract_UTC(To_Timestamp(to_char(to_date(etd_str,lc_FormatMask),'DDMMYYYY HH24:MI:SS'),'DDMMYYYY HH24:MI:SS'))
  into   l_eta_ts
       , l_etd_ts
  from   EDITABLE;

  /* show what is stored */
  dbms_output.put_line('Eta in: ' || to_char(l_eta_ts, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd in: ' || to_char(l_etd_ts, 'YYYYMMDDHH24MISS'));

  /* conversion used by GUI */
  l_eta_gui := CAST(from_tz(l_eta_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);
  l_etd_gui := CAST(from_tz(l_etd_ts, 'UTC') AT TIME ZONE 'Europe/Amsterdam' AS DATE);

  /* show what is displayed */
  dbms_output.put_line('Eta gui: ' || to_char(l_eta_gui, 'YYYYMMDDHH24MISS'));
  dbms_output.put_line('Etd gui: ' || to_char(l_etd_gui, 'YYYYMMDDHH24MISS'));
end;
/

The proper result was shown:
Eta in: 20180324070000 (UTC-1)
Etd in: 20180326060000 (UTC-2)
Eta gui: 20180324080000
Etd gui: 20180326080000

So, 2 lessons learned :)
-Read the Manual (Chapter 11 page 54 of Oracle® Database SQL Language Reference 11g Release 2):

Note: Time zone region names are needed by the daylight saving feature. These names are stored in two types of time zone files: one large and one small. One of these files is the default file, depending on your environment and the release of Oracle Database you are using. For more information regarding time zone files and names, see Oracle Database Globalization Support Guide.

-Make sure all your environments are created and configured the same

datapump expdp without access to expdp tool nor filesystem

Posted on by
Reply

This article is about getting a database export dump without having access to the expdp tool nor the filesystem. In some cases you just don’t have access to the databaseserver and no one is available (like a dba), still you need sufficient accessrights. Note: this is merely an excersize, if you’re planning on doing this notify your dba anyway, this article is not about secretly getting a database dump :-)

For this to work, you don’t need access to the expdp tool or the filesystem but you need some rights on a directory object (preferable read/write) and have execute rights on the DBMS_DATAPUMP package.

Every step is mentioned here, maybe in your situation you can skip some steps.

1. Create a test user

CREATE USER TSTUSER
 IDENTIFIED BY "testing"
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP
 ACCOUNT UNLOCK;

GRANT UNLIMITED TABLESPACE TO TSTUSER;
GRANT CREATE SESSION TO TSTUSER;
GRANT CREATE TABLE TO TSTUSER;
GRANT EXECUTE ON SYS.DBMS_DATAPUMP TO TSTUSER;
GRANT EXP_FULL_DATABASE TO TSTUSER;

2. Create a directory object where the output will get written to

CREATE OR REPLACE DIRECTORY DATADIR_FLATF AS '[D:\directorypath]';
GRANT READ, WRITE ON DIRECTORY DATADIR_FLATF TO TSTUSER;

3. Create a controltextfile. In this file the filenames should be present which represent your dump files. Save your controlfile ‘lob_data.txt’ to the DATADIR_FLATF location.

DECLARE
  out UTL_FILE.FILE_TYPE;
BEGIN
  out := UTL_FILE.FOPEN('DATADIR_FLATF', 'lob_data.txt' , 'W');
  UTL_FILE.PUT_LINE(out , '1,TWO_SCHEMAS.DMP,');
  UTL_FILE.PUT_LINE(out , '2,TWO_SCHEMAS.LOG,');
  UTL_FILE.FCLOSE(out);
END;
/

content of file is now (extra , at the end is not a typo):
1,TWO_SCHEMAS.DMP,
2,TWO_SCHEMAS.LOG,

4. Create a datapump export using DBMS_DATAPUMP

DECLARE
  handle NUMBER;
  status VARCHAR2(20);
BEGIN
  handle := DBMS_DATAPUMP.OPEN ('EXPORT', 'SCHEMA');
  DBMS_DATAPUMP.ADD_FILE (handle, 'TWO_SCHEMAS.DMP', 'DATADIR_FLATF');
  DBMS_DATAPUMP.ADD_FILE (handle, 'TWO_SCHEMAS.LOG', 'DATADIR_FLATF', null, DBMS_DATAPUMP.ku$_file_type_log_file);
  DBMS_DATAPUMP.METADATA_FILTER (handle, 'SCHEMA_EXPR', 'IN (''SCHEMA1'',''SCHEMA2'')');
  DBMS_DATAPUMP.START_JOB (handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB (handle, status);
  DBMS_OUTPUT.PUT_LINE('Status : ' || status);
END;
/

5. Create an external table to gain access

CREATE TABLE lob_tab (
  number_content    NUMBER(10),
  blob_content      BLOB
)
ORGANIZATION EXTERNAL
(
  TYPE    ORACLE_LOADER
  DEFAULT DIRECTORY DATADIR_FLATF
  ACCESS  PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    BADFILE DATADIR_FLATF:'lob_tab_%a_%p.bad'
    LOGFILE DATADIR_FLATF:'lob_tab_%a_%p.log'
    FIELDS  TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      number_content    CHAR(10),
      blob_filename     CHAR(100)
    )
    COLUMN TRANSFORMS (blob_content FROM LOBFILE (blob_filename) FROM (DATADIR_FLATF) BLOB)
  )
  LOCATION ('lob_data.txt')
)
PARALLEL 2
REJECT LIMIT UNLIMITED
/

6. Test query

SELECT number_content, DBMS_LOB.getlength(blob_content) AS blob_length
FROM   lob_tab;

7. If you encounter the following

ORA-12801: error signaled in parallel query server P000
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
error opening file D:\directorypath\TWO_SCHEMAS.DMP

place extra , at the end of each line maybe?

8. Download the files to your local OS using your favorite tool

datapump with flatfile

And there you have it. A complete dump of your schema choice locally available without having the expdp tool installed on your system.

9. Note, if you do have access to the filesystem, it looks similar to this

datapump with flatfile 2

10. Drop tstuser and objects

DROP USER TSTUSER CASCADE;