Uncategorized

Raspberry PI – Notes

Posted by

0

  • raspberry pi 4 4g model b (cortex-a72)
  • USB Keyboard & Mouse
  • power supply for raspberry pi (and potentially a plastic case)
  • micro sd card (64G), with adapter so that it can be connected to laptop and initialized with NOOBS files (=OS)
  • copy extracted NOOBS (not the top folder) onto sd card
  • 64G sd card has to be formatted FAT (Not exFAT) – use guiformat-x64.Exe
  • Put the SD Card (with NOOBS on it) into raspberry, cable to monitor (HDMI) + network (ethernet)
  • power on by connecting to power (there is no power button)

Oracle – SQL – demo deadlock

Posted by

0

create table t (processed_flag varchar(1));

create bitmap index t_idx on t(processed_flag);

From Tom Kyte “Expert Oracle Database Architecture – 3rd Edition”

insert into t values (‘N’);

declare
pragma autonomous_transaction;
begin
insert into t values (‘N’);
commit;
end;
/

declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

Oracle – Using ASH to Monitor Temporary Tablespace Usage

Posted by

0

Using ASH to Monitor Temporary Tablespace Usage

To use ASH (Active Session History) to monitor temporary tablespace usage use SQL similar to the query below. Detect the space used by queries.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT ASH.inst_id,
  ASH.user_id,
  ASH.session_id sid,
  ASH.session_serial# serial#,
  ASH.sql_id,
  ASH.sql_exec_id,
  ASH.sql_opname,
  ASH.module,
  MIN(sample_time) sql_start_time,
  MAX(sample_time) sql_end_time,
  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24) etime_secs ,
  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (60*24) etime_mins ,
  MAX(temp_space_allocated)/(1024*1024) max_temp_mb
FROM gv$active_session_history ASH
WHERE ASH.session_type = 'FOREGROUND'
AND ASH.sql_id        IS NOT NULL
--AND sample_time BETWEEN to_timestamp('01-04-2019 10:00', 'DD-MM-YYYY HH24:MI') AND to_timestamp('13-04-2019 12:00', 'DD-MM-YYYY HH24:MI')
AND sample_time > systimestamp - 3
GROUP BY ASH.inst_id,
  ASH.user_id,
  ASH.session_id,
  ASH.session_serial#,
  ASH.sql_id,
  ASH.sql_opname,
  ASH.sql_exec_id,
  ASH.module
HAVING MAX(temp_space_allocated) > 10000
order by 9 desc

Mini Keepass – Database File from iPhone to PC

Posted by

0

Move keepass Database File from iPhone to PC

  • tap Share icon (square/arrow) at bottom centre
  • select “copy to (Google) Drive”
  • save it somewhere on the drive
  • In Google Drive, find the file and tap three dots icon at right side of file
  • Select “Send a copy”
  • the file will be exported
  • rename the file with a “txt” extension if the email destination is likely to remove the attachment due to security settings

iPhone Heart Data – Export and Import into SQL Server for analysis

Posted by

0

Export the data from iphone to an XML file – email to yourself

– Open Heart App
– Tap icon at top right of screen
– Tap “Export Health Data at bottom of screen”
– Tap “Export” – data will be exported, then choose where to send the file.
– For example, email it to yourself.
– The result is a zip file which can be copied to your computer hard drive, unzip to expose the export.xml file.

Copy the file to the SQL Server host server

In my example below, the SQL Server is on Oracle Enterprise Linux 7.4 (SQL Server 2017 can be installed on Linux Install SQL Server on Linux).

Copy the file to /var/opt/mssql/

The file is now /var/opt/mssql/export.xml on the SQL Server.

 

Import the Data into a SQL Server Table

Create a Database

1
2
CREATE DATABASE OPENXMLTesting
GO

Create a Table to Store the Data

1
2
3
4
5
6
7
8
9
10
USE OPENXMLTesting
GO
 
--Create a table to store the data
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)

Insert the file into the Table – Creates one row

1
2
3
4
5
6
7
-- The file is an XML file with internal subset DTDs
-- For SQL Server, it has to be parsed and imported with style option 2
 
-- insert the whole file into the table
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn,2) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK '/var/opt/mssql/export.xml', SINGLE_BLOB) AS x;

Query the data from the table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Query the data from the table
-- run this all at same time
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
 
 
SELECT @XML = XMLData FROM XMLwithOpenXML
 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
 
select T2.RecordType, T2.Unit, Value,
[creationDate]=convert(datetime,left(T2.creationDate,19)),
[startDate]=convert(datetime,left(T2.startDate,19))
from
(
SELECT T.RecordType, T.Unit, T.Value, T.creationDate, T.startDate
FROM OPENXML(@hDoc, 'HealthData/Record')
WITH
(
RecordType [varchar](50) '@type',
Unit [varchar](10) '@unit',
Value [varchar](10) '@value',
creationDate [varchar](30) '@creationDate',
startDate [varchar](30) '@startDate'
) T
) T2
 
EXEC sp_xml_removedocument @hDoc
GO

Create a Table to Analyze the Data (Example)

Once the xml data is in a database table, many queries can be executed on it to extract useful information.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT @XML = XMLData FROM XMLwithOpenXML
 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
 
-- creates a table at same time
-- converts date strings into dates
select T2.RecordType, T2.Unit, Value,
[creationDate]=convert(datetime,left(T2.creationDate,19)),
[startDate]=convert(datetime,left(T2.startDate,19))
into HeartData_22102018
from
(
SELECT T.RecordType, T.Unit, T.Value, T.creationDate, T.startDate
FROM OPENXML(@hDoc, 'HealthData/Record')
WITH
(
RecordType [varchar](50) '@type',
Unit [varchar](10) '@unit',
Value decimal '@value',
creationDate [varchar](30) '@creationDate',
startDate [varchar](30) '@startDate'
) T
) T2
 
EXEC sp_xml_removedocument @hDoc
GO

Query the Data (Example – steps per day)

1
2
3
4
5
6
7
8
9
10
11
12
SELECT [Day]=datepart(day, startDate),
       [Month]=datepart(month, startDate),
       [Year]=datepart(year, startDate),
       [Steps]=sum([Value])
  FROM [OPENXMLTesting].[dbo].[HeartData_22102018]
  where [RecordType]='HKQuantityTypeIdentifierStepCount'
  group by datepart(day, startDate),
       datepart(month, startDate),
       datepart(year, startDate)
  order by datepart(year, startDate) desc,
       datepart(month, startDate) desc,
       datepart(day, startDate) desc
1
2
3
4
5
6
7
8
Day Month   Year    Steps
22  10  2018    1180
21  10  2018    15034
20  10  2018    23323
19  10  2018    3224
18  10  2018    10083
17  10  2018    7774
16  10  2018    3262

Oracle – Monitor TEMP Tablespace

Posted by

0

Monitor TEMP Tablespace with a DBMS_SCHEDULER job

I was faced with a situation in which a process (Oracle pl/sql procedure within a package, scheduled outside Oracle in the evenings) was exhausting the TEMP tablespace. In order to investigate, the following procedure was used to monitor TEMP tablespace usage during the period of time the job was executed. This allowed quick identification of the offending user and SQL that was causing TEMP tablesace exhaustion.

Code is quick and dirty, no exception handling. I’m assuming the executing user has all the required privileges. There are other ways to investigate this, but this works.

Create a table to store data

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE TEMPDATA
(
  CTIME       TIMESTAMP(6) WITH TIME ZONE,
  TABLESPACE  VARCHAR2(30),
  TEMP_SIZE   VARCHAR2(41),
  INSTANCE    NUMBER,
  SID_SERIAL  VARCHAR2(81),
  USERNAME    VARCHAR2(30),
  PROGRAM     VARCHAR2(48),
  STATUS      VARCHAR2(8),
  SQL_ID      VARCHAR2(13)
)

Create the procedure

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE OR REPLACE procedure colltempdata as
begin
 
begin
    insert into tempdata
    SELECT systimestamp ctime,
           b.tablespace,
           ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
           a.inst_id as Instance,
           a.sid||','||a.serial# AS sid_serial,
           NVL(a.username, '(oracle)') AS username,
           a.program,
           a.status,
           a.sql_id
    FROM   sys.gv$session a,
           sys.gv$sort_usage b,
           sys.gv$parameter p
    WHERE  p.name  = 'db_block_size'
    AND    a.saddr = b.session_addr
    AND    a.inst_id=b.inst_id
    AND    a.inst_id=p.inst_id;
end;
 
end;
/

Create a job to execute the procedure

In this example, execute every minute for a specfied time period

1
2
3
4
5
6
7
8
9
10
begin
    DBMS_SCHEDULER.CREATE_JOB (
         job_name             => 'colltempdatajob6',
         job_type             => 'PLSQL_BLOCK',
         job_action           => 'begin colltempdata;commit;end;',
         start_date           => timestamp '2018-07-04 21:30:00',
         repeat_interval      => 'FREQ=MINUTELY;INTERVAL=1;',
         end_date             => timestamp '2018-07-04 22:45:00',
         enabled              => TRUE);
end;

SQL to display jobs, check size of data, and query the collected data

1
2
3
4
5
6
7
8
-- display job details
select job_name, start_date, run_count, last_start_date, next_run_date  from dba_scheduler_jobs where job_name like 'COLLTEMPDATAJOB%' order by start_date
 
-- check size of data
select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'TEMPDATA'
 
-- Query data collected
select * from tempdata order by ctime desc

Oracle – opatch examples

Posted by

0

lspatches – list patches

1
2
3
4
5
6
[grid@oraserver1 ~]$ /oracle/grid/12.2.0/OPatch/opatch lspatches
27674384;Database Apr 2018 Release Update : 12.2.0.1.180417 (27674384)
27464465;OCW APR 2018 RELEASE UPDATE 12.2.0.1.0(180129) (27464465)
27458609;ACFS APR 2018 RELEASE UPDATE 12.2.0.1.0(180129) (27458609)
27144050;Tomcat Release Update 12.2.0.1.0(ID:171023.0830) (27144050)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277)

-all_nodes – RAC system

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
[grid@oraserver1 ~]$ /oracle/grid/12.2.0/OPatch/opatch lsinventory -all_nodes
Oracle Interim Patch Installer version 12.2.0.1.12
Copyright (c) 2018, Oracle Corporation.  All rights reserved.
 
 
Oracle Home       : /oracle/grid/12.2.0
Central Inventory : /oracle/oraInventory
   from           : /oracle/grid/12.2.0/oraInst.loc
OPatch version    : 12.2.0.1.12
OUI version       : 12.2.0.1.4
Log file location : /oracle/grid/12.2.0/cfgtoollogs/opatch/opatch2018-05-02_11-29-57AM_1.log
 
Lsinventory Output file location : /oracle/grid/12.2.0/cfgtoollogs/opatch/lsinv/lsinventory2018-05-02_11-29-57AM.txt
 
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: oraserver1
ARU platform id: 226
ARU platform description:: Linux x86-64
 
 
Patch level status of Cluster nodes :
 
 Patching Level                  Nodes
 --------------                  -----
 626559184                       oraserver2,oraserver1
 
 
Installed Patch List(s)
======================
 
Node Name : oraserver1
---------------------
 
Interim patches (5) :
Patch  27674384     : applied on Wed Apr 25 07:54:46 CEST 2018
Unique Patch ID:  22098633
Patch description:  "Database Apr 2018 Release Update : 12.2.0.1.180417 (27674384)"
   Created on 9 Apr 2018, 00:43:55 hrs PST8PDT
   Bugs fixed:
     23026585, ...27502420
Patch  27464465     : applied on Wed Apr 25 07:51:27 CEST 2018
Unique Patch ID:  22045693
Patch description:  "OCW APR 2018 RELEASE UPDATE 12.2.0.1.0(180129) (27464465)"
   Created on 31 Mar 2018, 05:33:42 hrs PST8PDT
   Bugs fixed:
     13250991, ...27757979
Patch  27458609     : applied on Wed Apr 25 07:48:27 CEST 2018
Unique Patch ID:  21914305
Patch description:  "ACFS APR 2018 RELEASE UPDATE 12.2.0.1.0(180129) (27458609)"
   Created on 23 Mar 2018, 00:25:23 hrs PST8PDT
   Bugs fixed:
     21129279, ...27573409
Patch  27144050     : applied on Wed Apr 25 07:47:08 CEST 2018
Unique Patch ID:  21771994
Patch description:  "Tomcat Release Update 12.2.0.1.0(ID:171023.0830) (27144050)"
   Created on 26 Dec 2017, 23:52:30 hrs PST8PDT
   Bugs fixed:
     25728967, 26934551
Patch  26839277     : applied on Wed Apr 25 07:46:56 CEST 2018
Unique Patch ID:  21578760
Patch description:  "DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277)"
   Created on 21 Sep 2017, 03:13:10 hrs PST8PDT
   Bugs fixed:
     26584906
 
Node Name : oraserver2
---------------------
 
Interim patches (5) :
Patch  27674384     : applied on Wed Apr 25 07:54:46 CEST 2018
Unique Patch ID:  22098633
Patch description:  "Database Apr 2018 Release Update : 12.2.0.1.180417 (27674384)"
   Created on 9 Apr 2018, 00:43:55 hrs PST8PDT
   Bugs fixed:
     23026585, ...27502420
Patch  27464465     : applied on Wed Apr 25 07:51:27 CEST 2018
Unique Patch ID:  22045693
Patch description:  "OCW APR 2018 RELEASE UPDATE 12.2.0.1.0(180129) (27464465)"
   Created on 31 Mar 2018, 05:33:42 hrs PST8PDT
   Bugs fixed:
     13250991, ...27757979
Patch  27458609     : applied on Wed Apr 25 07:48:27 CEST 2018
Unique Patch ID:  21914305
Patch description:  "ACFS APR 2018 RELEASE UPDATE 12.2.0.1.0(180129) (27458609)"
   Created on 23 Mar 2018, 00:25:23 hrs PST8PDT
   Bugs fixed:
     21129279, 22591010, ...27573409
Patch  27144050     : applied on Wed Apr 25 07:47:08 CEST 2018
Unique Patch ID:  21771994
Patch description:  "Tomcat Release Update 12.2.0.1.0(ID:171023.0830) (27144050)"
   Created on 26 Dec 2017, 23:52:30 hrs PST8PDT
   Bugs fixed:
     25728967, 26934551
Patch  26839277     : applied on Wed Apr 25 07:46:56 CEST 2018
Unique Patch ID:  21578760
Patch description:  "DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277)"
   Created on 21 Sep 2017, 03:13:10 hrs PST8PDT
   Bugs fixed:
     26584906
 
 
Binary & Checksum Information
==============================
 
 Binary Location : /oracle/grid/12.2.0/bin/oracle
 
 Node                   Size                    Checksum
 ----                   ----                    --------
 oraserver1              373215900               F178954A3A31D768B83209539ECACD13278065B6FE07B4101BE81A74A0B28F0D
 oraserver2              373215900               F178954A3A31D768B83209539ECACD13278065B6FE07B4101BE81A74A0B28F0D
--------------------------------------------------------------------------------
 
OPatch succeeded.

Linux – test whether port open using nc

Posted by

0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
yum install nc on server1 and server2  ----> install netcat
 
on server1
nc -ul 38893 >> test                   ----> listen for udp on port 38893, append to file "test"
 
on server2
echo "hello test2" > /tmp/testfile
cat testfile2 | nc -u server1 38893    ----> send contents of testfile2 over udp to port 38893 on server1
ctrl-c
 
on server1
ctrl-c
cat test
 
"hello test2" is appended to file "test"