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>

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 ~]#