psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "RAYFOX")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "RAYFOX")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@postgresql.org>.
Oracle RAC – Display differences between instance initialization parameter values
SELECT p1.name, p1.value val_inst1, p2.value val_inst2
FROM gv$parameter p1
JOIN gv$parameter p2 ON p1.name = p2.name
WHERE p1.inst_id = 1
AND p2.inst_id = 2
AND p1.value != p2.value
AND p1.name NOT IN ('instance_number', 'instance_name',
'local_listener','thread','undo_tablespace','core_dump_dest')
Oracle – RMAN DROP DATABASE
-- set ORACLE env first
export ORACLE_SID=ORCL12
export ORACLE_HOME=/oracle/.....
oracle@oravm:~/ [ORCL12] rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Dec 27 09:45:33 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup force mount;
Oracle instance started
database mounted
Total System Global Area 4275781632 bytes
Fixed Size 5218048 bytes
Variable Size 2415919360 bytes
Database Buffers 1845493760 bytes
Redo Buffers 9150464 bytes
RMAN> show db_unique_name;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL12 are:
RMAN configuration has no stored or default parameters
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION
RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;
database name is "ORCL12" and DBID is 2242343723
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
specification does not match any backup in the repository
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORCL12
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - --------
1 1 1 A 30.11.17
Name: +ORA_DATA/ORCL12/ARCHIVELOG/2017_11_30/thread_1_seq_1.407.961431639
2 1 2 A 30.11.17
Name: +ORA_DATA/ORCL12/ARCHIVELOG/2017_11_30/thread_1_seq_2.414.961431685
3 1 3 A 30.11.17
Name: +ORA_DATA/ORCL12/ARCHIVELOG/2017_12_01/thread_1_seq_3.316.961561207
deleted archived log
archived log file name=+ORA_DATA/ORCL12/ARCHIVELOG/2017_11_30/thread_1_seq_1.407.961431639 RECID=1 STAMP=961431639
deleted archived log
archived log file name=+ORA_DATA/ORCL12/ARCHIVELOG/2017_11_30/thread_1_seq_2.414.961431685 RECID=2 STAMP=961431684
deleted archived log
archived log file name=+ORA_DATA/ORCL12/ARCHIVELOG/2017_12_01/thread_1_seq_3.316.961561207 RECID=3 STAMP=961561206
Deleted 3 objects
database name is "ORCL12" and DBID is 2242343723
database dropped
RMAN>
Linux – close file descriptors without killing process
-- get list of file descriptors # lsof /dev/mapper/XYZ_* -- example output oracle 31814 oracle 256u BLK 253,7 0t0 20968 /dev/mapper/../dm-7 -- above, fd 256 is open for update (could also be w (write)) -- attach to process with gdb debugger # gdb -p 31814 -- close file descriptor(s) gdb> p close(256)
SQL Server – Generate SQL for Database Snapshot
with
preamble(c) as (select 'create database ' + db_name() + '_Snapshot on'),
files(c) as (select '(name=' + name + ', filename=''' + physical_name + '.ss'')' + char(10) from sys.database_files where type = 0),
filescoalesce (c) as (select c + ',' from files for xml path('')),
lastline(c) as (select 'as snapshot of ' + db_name() + char(10) + char(10) + 'GO' + char(10))
select c [--] from preamble
union all
select left(c, len(c) -2) from filescoalesce
union all
select c from lastline
Linux – Run remote Powershell from Nagios – nrpe_check
I experienced a strange problem when calling Powershell from nagios to
check failover cluster status.
Note – using old NRPE_NT nagios agent – no problem with NSClient++.
The Get-Cluster cmdlet failed each time the script was executed.
I thought this was related to the fact that this cmdlet requires elevated privieges,
but this was not the case. The issue occurred because NRPE_NT is running as a 32-bit program
and this calls 32-bit cmd, which calls 32-bit Powershell.
If you want to run 64-bit Powershell and use 64-bit Powershell commands,
from nagios, 64-bit powershell must be executed. This means that C:\WINDOWS\sysnative
is required.
nrpe.cfg on Windows
….
command[failover-cluster-check]=C:\WINDOWS\sysnative\WindowsPowerShell\v1.0\powershell.exe “& path\check_1.ps1”
….
if not using NSClient++
– the NRPE_NT service is running as a 32-bit executable
– it runs cmd as 32-bit
– 32-bit cmd calls 32-bit powershell unless C:\WINDOWS\sysnative\ is used
Oracle – find sessions using database links
Run on both databases: select /*+ ORDERED */ substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN", substr(g.K2GTITID_ORA,1,35) "GTXID", substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" , s2.username, substr( decode(bitand(ksuseidl,11), 1,'ACTIVE', 0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'), 2,'SNIPED', 3,'SNIPED', 'KILLED' ),1,1 ) "S", substr(s2.event,1,10) "WAITING" from x$k2gte g, x$ktcxb t, x$ksuse s, v$session s2 where g.K2GTDXCB =t.ktcxbxba and g.K2GTDSES=t.ktcxbses and s.addr=g.K2GTDSES and s2.sid=s.indx;
Oracle ASM – Commands
SQL
select * from v$asm_diskgroup order by free_mb
asmcmd
asmcmd lsdg|awk '{sub(/\//,"",$14);print}'|tr -dc '[:print:]\n'|awk -v date="$(date +"%Y-%m-%d %r")" '{print $14 "\t\t" $9"\t\t"$8" : " date}'|sort -k 2 -n
SQL Server – bcp
export bcp <database.schema.tablename> out c:\raytemp\tablename.out -n -T -S”<server,port>” import bcp <database.schema.tablename> in c:\raytemp\tablename.out -n -T -S”<server,port>”
SQL Server/Linux – Install SQL Server 2017 on Linux
Microsoft document: Install SQL Server on Linux
[root@oralintest ~]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo
[root@oralintest ~]# yum install mssql-server
...
Installed:
mssql-server.x86_64 0:14.0.900.75-1
Complete!
[root@oralintest ~]# /opt/mssql/bin/mssql-conf setup
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=852741&clcid=0x409
The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409
Do you accept the license terms? [Yes/No]:Yes
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID)
7) I bought a license through a retail sales channel and have a product key to enter.
Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409
Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.
Enter your edition(1-7): 1
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...
This is an evaluation version. There are [90] days left in the evaluation period.
The licensing PID was successfully processed. The new edition is [Enterprise Evaluation Edition].
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.
[root@oralintest ~]#
[root@oralintest ~]# curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
[root@oralintest ~]# yum install -y mssql-tools unixODBC-devel
...
Installing : msodbcsql-13.1.9.1-1.x86_64 2/4
...
Installed:
mssql-tools.x86_64 0:14.0.6.0-1 unixODBC-devel.x86_64 0:2.3.1-11.el7
[root@oralintest ~]# export PATH="$PATH:/opt/mssql-tools/bin"
[root@oralintest ~]# firewall-cmd --zone=public --add-port=1433/tcp --permanent
success
[root@oralintest ~]# firewall-cmd --reload
success
[root@oralintest ~]# sqlcmd -S localhost -U SA
Password:
1> use master;
2> go
Changed database context to 'master'.
1> select @@version
2> go
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RC2) - 14.0.900.75 (X64)
Jul 27 2017 08:53:49
Copyright (C) 2017 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Linux (Oracle Linux Server 7.4)
(1 rows affected)