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)
Oracle Linux – Install vnc server
[root@oralintest ~]# yum install tigervnc-server Loaded plugins: langpacks, ulninfo Resolving Dependencies --> Running transaction check ---> Package tigervnc-server.x86_64 0:1.8.0-1.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved =============================================================================================================================================================================== Package Arch Version Repository Size =============================================================================================================================================================================== Installing: tigervnc-server x86_64 1.8.0-1.el7 ol7_latest 213 k Transaction Summary =============================================================================================================================================================================== Install 1 Package Total download size: 213 k Installed size: 504 k Is this ok [y/d/N]: y Downloading packages: warning: /var/cache/yum/x86_64/7Server/ol7_latest/packages/tigervnc-server-1.8.0-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY Public key for tigervnc-server-1.8.0-1.el7.x86_64.rpm is not installed tigervnc-server-1.8.0-1.el7.x86_64.rpm | 213 kB 00:00:00 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle Importing GPG key 0xEC551F03: Userid : "Oracle OSS group (Open Source Software group)" Fingerprint: 4214 4123 fecf c55b 9086 313d 72f9 7b74 ec55 1f03 Package : 7:oraclelinux-release-7.4-1.0.4.el7.x86_64 (@anaconda/7.4) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle Is this ok [y/N]: y Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : tigervnc-server-1.8.0-1.el7.x86_64 1/1 Verifying : tigervnc-server-1.8.0-1.el7.x86_64 1/1 Installed: tigervnc-server.x86_64 0:1.8.0-1.el7 Complete! [root@oralintest ~]#