Thursday, October 19, 2023

SCREEN Unix/Linux Command: An Oldie But A Goody!

 Back in the day, when listening to AM Radio to hear all the latest and greatest pop hits, every now and then, the DJ would announce that he would play "an oldie but a goody" - which was a song no longer on the charts, but still very popular.  Here are a few to listen to if you have the time: 

OLDIES_BUT_GOODIES

I recently ran into a spurious issue where normally stable terminal sessions started to detach for no known reason.  It is probably some kind of network instability.  We had the network timeout settings for our sessions set to zero (no maximum). See Keep Alive or Never time out

 Keep in mind that on some systems, security may limit this.  

So what do you do when you have a long running process and there is no way to keep it in the background?  For instance - the process runs for quite some time, but occasionally requires user input such as a password or application information.  

I recently ran into such a situation.  Trying to build a rather large database from a backup can take hours.  Building this database via a tooling script from a cloud provider required intermittent responses with passwords and wallet keys, but no way to input them as parameters in advance (poorly written, if you ask me).  

This brings us to using the SCREEN command.  It certainly serves the purpose, but it can be a little "kludgey" and may be different in each linux/unix variant.  In fact, it is not always present and may need to be installed:

sudo apt install screen

An example of it's use is hard to depict, since it involves a session gone awry, but step-by-step:

1) Create a (saved) screen session:

screen -S mysavedsession

2) Verify its presence:

$ screen -ls


There is a screen on:

61450.savethis (Detached)

1 Socket in /var/run/screen/S-opc.

3) Run a command with continuous output:

ping cnn.com

From 192.168.100.2 (192.168.100.2) icmp_seq=1 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=2 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=3 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=4 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=5 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=6 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=7 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=8 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=9 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=10 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=11 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=12 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=13 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=14 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=15 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=16 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=17 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=18 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=19 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=40 Packet filtered

4) terminate the session....(do not cntrl-c!) ...literally close the terminal window while it is running.

5) Start another session with the same user account: 

ssh opc@myserver

6) Again (optionally) verify existence of the session.  It should appear as "(Detached)" which is what we want!:

screen -ls

There is a screen on:

61450.savethis (Detached)

1 Socket in /var/run/screen/S-opc.


$ screen -r savethis. # output should resume:

From 192.168.100.2 (192.168.100.2) icmp_seq=30 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=31 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=32 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=33 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=34 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=35 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=36 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=37 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=38 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=39 Packet filtered

From 192.168.100.2 (192.168.100.2) icmp_seq=40 Packet filtered

This can be useful whether you have a long running job, or simply decide to get up for a cup of coffee.  You never know when your connection could be lost, either intentionally or due to an unforeseen event!



Tuesday, July 25, 2023

HOW TO: Cloud Exadata Timezone Change

 A client recently realized that over the years, they had timezone inconsistencies across their database systems.  They had more or less adopted and learned to live with them.  While this can be done, complications can arise when using Data-guard, or doing timestamp recovery.  

As they were migrating to Oracle Cloud Infrastructure and CloudExa, I explained this would be an excellent time to make all of their systems consistent.  

They had already built out their test system using an inherited timezone, so we had to figure out how to change it.  There were no cloud-specific documents in the support area on this issue, so we decided to open a Support Request with Oracle.  This is not always known, but an "SR" can be opened to document or verify a procedure when the platform is newer and documents do not specifically mention it.  

It took a few days, but after some research and re-checking with development, a support analyst said that it was believed that an on-premise document and procedure could be followed, but he will leave the request open should we run into problems.  This document link is HERE.  

Unfortunately, while very close to our situation, we found an anamoly or difference on Cloud Exadata that made it impossible to follow verbatim.  It required the existence of the file: 

/etc/sysconfig/clock 

Our system did not have this file on either of it's two nodes!

So we notified Oracle.  Once again, they went back to cloud development to seek direction due to this unplanned situation.  Development was unaware that cloud systems could be delivered without this file, and they considered it a bug and will be opening one to address this.  

So now we needed a new plan.  Between development, the analyst and ourselves, we checked on the format of the file (it was actually very simple).  it consists of this verbiage for our case:

ZONE="America/New_York"  Oracle support did not give us this exact syntax, however, there is a group of files and directories with examples in /usr/share/zoneinfo.  Under subdirectory "America" is a file named "New_York", and that exact set of characters was consistent with the example in the documentation referenced above.  

The system is currently running in Mountain Time, which can be seen by executing this command:

$ date

Tue Jul 25 07:57:53 MDT 2023


So, nothing is changed yet.  We just needed to verify how to proceed.  After arranging for a complete database outage with interested parties, the next step was to shutdown all services in the Grid Infrastructure stack as user root:


$ crsctl stop crs


Messages returned:

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'exa-skyze1'

.........

CRS-4133: Oracle High Availability Services has been stopped.



Next we should verify everything is stopped:


$ crsctl stat res -t


Messages returned:

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4000: Command Status failed, or completed with errors.


Above errors are expected results - the CRS is not running!


Now onto the timezone specific changes:


As root, the file /etc/sysconfig/clock was created with the "ZONE=America/New_York":


[root@exa ~]# date

Tue Jul 25 07:38:51 MDT 2023

[root@exa- ~]# vi /etc/sysconfig/clock

[root@exa ~]# date

Tue Jul 25 07:39:31 MDT 2023

[root@exa- ~]# cat /etc/sysconfig/clock

ZONE="America/New_York"


(please note full system name is obfuscated to maintain client confidentiality).  


This next command will actually change the timezone on the nodes:


ln -sf /usr/share/zoneinfo/America/New_York /etc/localtime


Now for the results!


[root@exa ~]]# date

Tue Jul 25 09:45:14 EDT 2023


Above steps must be repeated on other node(s).  


Once both nodes show the correct local time, we can restart services:



# crsctl start crs

CRS-4123: Oracle High Availability Services has been started.


(We had to wait several minutes until all services were restared).  



Now the OS and all services are running on the new timezone.  


However, when we logged into the oracle account and the databases, we found the task was still incomplete!!!


$ sqlplus / as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 07:54:59 2023

Version 19.18.0.0.0


Copyright (c) 1982, 2022, Oracle.  All rights reserved.



Connected to:

Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

Version 19.18.0.0.0


SQL> select systimestamp from dual;


SYSTIMESTAMP

---------------------------------------------------------------------------

25-JUL-23 07.55.08.703743 AM -06:00



The above time is still in the Mountain Timezone.


What could cause this?  The OS shows the correct time.  


Looking at the environment for the "oracle" user account discloses the reason:


cat .bash_profile

# .bash_profile


# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi


# User specific environment and startup programs



export PATH

export TZ=US/Mountain


At this point, we realize the database is only reflecting a local environment variable setting, so we modify the TZ attribute to "US/Eastern" with the desired result.


$ vi .bash_profile

[oracle@exa ~]$ date

Tue Jul 25 07:57:53 MDT 2023

[oracle@exa ~]$ source .bash_profile

Enter DB Name (TOOLUAT/ACMUAT/FMUAT): TOOLUAT

[oracle@exa1 ~]$ date

Tue Jul 25 09:58:03 EDT 2023

[oracle@exa ~]$ sqlplus / as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 09:58:10 2023

Version 19.18.0.0.0


Copyright (c) 1982, 2022, Oracle.  All rights reserved.



Connected to:

Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production

Version 19.18.0.0.0


SQL> select systimestamp from dual;


SYSTIMESTAMP

---------------------------------------------------------------------------

25-JUL-23 09.58.16.964305 AM -04:00




So as we can see in this vivid example, Oracle Support can only help so much when you have a process change.  They do not know about localized information for your environment.  They can only provide general guidelines.  In our case, we had to look at several configurable attributes to complete the effort.  


So far, testing with the timezone change is going well.  We may still run into an issue or two with the data and backups.  Time will tell, but at least, going forward, all of the systems will be running the same.  


This is a good argument for running all systems in the UTC (greenwich mean time) zone.  However, that would have required massive application changes and procedural adjustments for this particular system.  



Monday, April 24, 2023

PostgreSQL vs. Oracle SQL

 I once wrote a paper called "Postgres Goofiness" to help professionals familiar with Oracle become familiar with the quirks and idiosyncrasies of the Postgres implementation of SQL.  To be fair, and remove all bias, some of the Postgres implementation may be more ANSI-standard at times than Oracle, but not always (ANSI is a governing body that approves valid SQL syntax).  Still, Oracle (and Microsoft SQL Server and IBM DB2) are certainly still more prevalent, and most folks are far more familiar with them.  

Here is a less-than-comprehensive list.  Perhaps you can think of some more and comment.  I picked these because they immediately stand out and are often quite different and less than intuitive.


Compared to Oracle, Postgres SQL is quite similar, and is based upon ANSI standards which have been around for 40+ years.


However, the implementations of the standard are often curiously different, especially to an Oracle professional.  


For example:


1 - The LIMIT clause.


Select * from huge_table limit 100:  100 rows are returned in no particular order.  ROWNUM virtual column concept from Oracle does not exist.


More details on this clause and it's variations in this excellent POST.  



2 - Query Parallelism 


3 - Create index abx_index on schema name.abx (column_name);  - Notice schema_name is not specified for the index?  

It is assumed the same as the table.  Specifying it generates an error!


4 - SELECT clause without FROM.   


Select version();  


Postgres versions are (roughly) moved up a notch yearly.  

Postgres 9 was a longstanding, stable release, much like Oracle 11g.  

Companies are finding it hard to justify upgrades.

Security vulnerabilities are numerous with 9, and no patches available.



5 - SYSDATE becomes NOW?



master=> select now();

              now              

-------------------------------

 2022-06-26 13:16:29.143339+00

(1 row)



5a A more conventional syntax:


master=> select current_date;

 current_date 

--------------

 2022-08-03

(1 row)


5b. 


master=> select current_time;

    current_time    

--------------------

 02:07:19.259544+00

(1 row)


5c.


master=> select 6;

6

----------

6


Above is completely off-the-rails illegal in Oracle but will work in SQLServer!  


A more detailed post on this issue:  SQL Without FROM?



6 - backslash commands replace many queries in other SQL flavors.  

Examples:


\q.   Quits “psql”

\d    Same as describe in sqlplus

\timing same as “set timing on” - not too bad!  I can remember that!!!


See the documentation for a comprehensive list.  Keep in mind Postgresql originates from the eighties, when every key stroke was expensive, and every programming language strived for brevity.  It was also designed to run on much smaller systems.


7 - SQL errors often have “hints” to help resolve the problem.  Very similar to oerr without asking!


8 - The equivalent of sql*loader is built-in to the sql command line tool.


psql> \copy owner.table from ‘c:\local_file_name’;


Once again note the ubiquitous backslash!


9 - Dictionary views and catalog tables are similar, but have different names and often derivative functions - but they get the job done:


Select pg_size_pretty(pg_relation_size(‘your_object_name’));


10- Sometimes, these combinations of catalog views and functions must be combined in odd ways to produce the same output that one Oracle dictionary view produces, but they DO WORK!


SELECT

    relname AS "relation",

    pg_size_pretty (

        pg_total_relation_size (C .oid)

    ) AS "total_size"

FROM

    pg_class C

LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)

WHERE

    nspname NOT IN (

        'pg_catalog',

        'information_schema'

    )

AND C .relkind <> 'i'

AND nspname !~ '^pg_toast'

ORDER BY

    pg_total_relation_size (C .oid) DESC

LIMIT 5;


11 - Other oddities - a clause to use results of a subquery - sort of like “with”?


execute query into result;


12 - The term “table” and “relation” are often interchangeable in the documentation.


13 - “Tuples” are the equivalent to blocks, more or less.


I will cut it short at a "baker's dozen".  I am sure you will encounter many, many more.  


Feel free to comment or suggest still more, and I will revise or write another post on this subject!