Uncategorized
Raspberry PI – Notes
- 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
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 OVM – How to create a local Unbreakable Linux Network mirror
Assumptions: a ULN subscription – Oracle Support for oracle Linux – i.e a valid CSI
use the link:
Oracle – Using ASH to Monitor Temporary Tablespace Usage
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.
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
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
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
CREATE DATABASE OPENXMLTesting GO
Create a Table to Store the Data
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
-- 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
-- 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.
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)
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
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
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
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
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
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
-- 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
lspatches – list patches
[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
[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
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"