Oracle VM – Collect diagnostic info

Posted by

0

Collect diagnostic info for Oracle Virtual Machine Manager and Servers

If you are experiencing issues with OVM, and an SR is opened, Oracle support will request diagnostic information. The shell session below shows collection of this information for OVM 3.4.x

[root@ovm-manager support]# ./vmpinfo3.sh --help
Usage:
    vmpinfo3.sh --username=ADMIN_USER [listservers | servers=SERVERS]
    vmpinfo3.sh --wallet=WALLET [listservers | servers=SERVERS]

    Collect info from all owned servers:
        vmpinfo3.sh --username=admin
    List owned servers:
        vmpinfo3.sh --username=admin listservers
    Collect info from specific server(s):
        vmpinfo3.sh --username=admin servers=server3
        vmpinfo3.sh --username=admin servers=server1,server5,server9

On PCA the --wallet option retrieves the credential from the PCA wallet

Data collected is aggregated in your system temporary directory.
If there is not enough free space in this filesystem you can override
the location by setting $TMPDIR -- see mktemp(1).

# cd /u01/app/oracle/ovm-manager-3/ovm_tools/support/
[root@ovm-manager support]# ./vmpinfo3.sh --username=admin
Enter OVM Manager Password:

Starting data collection
Gathering files from all servers. This process may take some time.
Gathering OVM Model Dump files
Gathering sosreports from servers
The following server(s) will get info collected: [ovs1]
Gathering sosreport from ovs1
Data collection complete
Gathering OVM Manager Logs
Clean up metrics
Copying model files
Copying DB backup log files
Running lightweight sosreport
Archiving vmpinfo3-20181211-080334
=======================================================================================
 Please send /tmp/vmpinfo3-3.4.5.1919-20181211-080334.tar.gz to Oracle support
=======================================================================================

Oracle – Identify Locked Objects

Posted by

0

Identify Locked Database Objects – Oracle

select b.owner, b.object_name, a.oracle_username, a.os_user_name  
from gv$locked_object a, all_objects b
where a.object_id = b.object_id


select * from gv$locked_object

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
and a.inst_id = b.inst_id

SELECT Decode(request, 0, 'Holder: ',
  'Waiter: ')
  ||vl.sid sess,
  status,id1,id2,lmode,request,vl.TYPE
FROM v$lock vl,v$session vs
WHERE ( id1, id2, vl.TYPE ) IN 
(SELECT id1, id2, TYPE FROM v$lock
  WHERE request > 0)
  AND vl.sid = vs.sid
ORDER BY id1, request

Oracle – 18c XE Flashback Pluggable Database Session

Posted by

0

18c XE Flashback Pluggable Database Session

SQL> sho parameters reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL> host df -h
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             4.8G     0  4.8G   0% /dev
tmpfs                4.8G     0  4.8G   0% /dev/shm
tmpfs                4.8G   26M  4.8G   1% /run
tmpfs                4.8G     0  4.8G   0% /sys/fs/cgroup
/dev/mapper/ol-root   45G   24G   21G  54% /
/dev/xvda1          1014M  399M  616M  40% /boot
tmpfs                974M   12K  974M   1% /run/user/42
tmpfs                974M     0  974M   0% /run/user/0
tmpfs                974M     0  974M   0% /run/user/54321

SQL> sho parameters pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/oracle/product/18c/dbhome
                                                 XE/dbs/spfileXE.ora

SQL> alter system set db_recovery_file_dest_size=20G scope=BOTH;

System altered.

SQL> alter system set db_recovery_file_dest='/opt/oracle/oradata/XE/FRA' scope=BOTH;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612704 bytes
Fixed Size                  8896480 bytes
Variable Size             520093696 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7880704 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> set lines 170
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                         1.95                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
YES

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                         1.95                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
YES

SQL> alter session set container=xepdb1;

Session altered.

SQL> create restore point PDB1_GRP1 guarantee flashback database;

Restore point created.

SQL> create table tw (id number);

Table created.

SQL> insert into tw values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> shutdown immediate
Pluggable Database closed.
SQL> flashback pluggable database xepdb1 to restore point PDB1_GRP1;

Flashback complete.

SQL> show con_name

CON_NAME
------------------------------
XEPDB1

SQL> startup nomount

-- we are in the xepdb1 container

SQL> select name from v$pdbs;

NAME
--------------------------------------------------------
XEPDB1


SQL> alter pluggable database open resetlogs;

Pluggable database altered.

SQL> select * from dual;

D
-
X

SQL> desc tw
ERROR:
ORA-04043: object tw does not exist



SQL> select name from v$pdbs;

NAME
----------------------------------------------------------------
XEPDB1


SQL> ALTER SESSION SET container = cdb$root;

Session altered.

SQL> select name from v$pdbs;

NAME
---------------------------------------------------------------
PDB$SEED
XEPDB1
PDB2

iPhone Heart Data – Export and Import into SQL Server for analysis

Posted by

0

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

SQL Server – Add Powershell dbatools module

Posted by

0

Add Powershell dbatools module

Execute an elevated powershell session (Execute as administrator) and run the command:

Import-Module dbatools 

Windows PowerShell
Copyright (C) 2016 Microsoft Corporation. All rights reserved.

Loading personal and system profiles took 756ms.
PS C:\WINDOWS\system32> Install-Module dbatools

NuGet provider is required to continue
PowerShellGet requires NuGet provider version '2.8.5.201' or newer to interact with NuGet-based repositories. The NuGet provider must be available in 'C:\Program Files\PackageManagement\ProviderAssemblies' or
 'C:\Users\hell\AppData\Local\PackageManagement\ProviderAssemblies'. You can also install the NuGet provider by running 'Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force'. Do you want
 PowerShellGet to install and import the NuGet provider now?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): Y

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its InstallationPolicy value by running the Set-PSRepository cmdlet. Are you sure you want to install the
modules from 'PSGallery'?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): Y
PS C:\WINDOWS\system32>

Security Aspects

This article Security Aspects by Chrissy Lemaire explains security-related issues (for the dbatools module and PowerShell in general).

SQL Server – Query Plans, Plan handles, DBCC FREEPROCCACHE

Posted by

0

select
[Average Duration (msec]=total_elapsed_time/execution_count/1000, 
creation_time,last_execution_time, 
execution_count from sys.dm_exec_query_stats
where plan_handle in 
(
SELECT plan_handle
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
where text like '%<sql text>%'
)

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan, plan_handle
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
where text like '%<sql text>%'
order by text

DBCC FREEPROCCACHE(<plan_handle>)

SQL Server – Display Statistics for a Database

Posted by

0

SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
FROM sys.stats s JOIN sys.stats_columns sc 
              ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1)
order by LastUpdated desc; 

sp_msforeachtable 'sp_autostats ''?'''

Oracle – RMAN backups

Posted by

0

select object_name from dba_objects where object_name like '%RMAN%'

select * from gv$backup_set order by completion_time desc

select * from v$rman_backup_job_details order by start_time desc


select start_time, end_time, input_bytes,output_bytes, status, input_type,elapsed_seconds,
input_bytes_display from v$rman_backup_job_details order by start_time desc

Oracle – Install Clusterware – Test udp multicast on Private Interconnect (and get maximum MTU)

Posted by

0

Test Multicast on Private Interconnect (and get maximum MTU)

There are several ways to test multicast communication using udp. I was debugging an oracle Clusterware installation – the clusterware on the second node would not completely start (specifically ocssd and crsd would not start on node2).

All standard/published Oracle tests passed. See Oracle – Clusterware Installation and Configuration

Without going into too much detail, the Clusterware daemons would not start on the second node because the MTU on some of the switches in the network was not configured correctly.

The two java programs below can be used to test whether multicast is working on the network between two servers. More specifically, they can be used to determine the maximum MTU that can be used between the two servers, taking into consideration all network elements (NICs, switches, routers).

Switches/routers may be configured to support Jumbo frames. MTU 9000 (maybe set to 9150 on switch/routers)

The default MTU on switches may be 1400!

Normally, the NICs on the Linux server are set to 1500 MTU.

Private interconnect NICs/bond can be set to 9000 if all intermediate network elements support and are configured for Jumbo frames

MultiCastTestReceiveLoop.java

This program is used to receive udp datagrams on a socket (max. 9150 – covers Jumbo frames).

The communication follows a similar path as that used between Oracle Clusterware processes.

To compile: /jdk/bin/javac MultiCastTestReceiveLoop.java

To execute: /jdk/bin/java -classpath . MultiCastTestReceiveLoop 230.0.1.0 42424

import sun.net.*;
import java.net.*;

public class MultiCastTestReceiveLoop {
    public MultiCastTestReceiveLoop() {
        MultiCastTestReceiveLoop MultiCastTestReceiveLoop = new MultiCastTestReceiveLoop(42424,"230.0.1.0");
    }
    
    public MultiCastTestReceiveLoop(int port,String group) {

	try {
	while (true) { 
	
        // Create the socket and bind it to port 'port'.
        MulticastSocket s = new MulticastSocket(port);

        // join the multicast group
	s.setNetworkInterface(NetworkInterface.getByName("<interconnect interface>"));
        s.joinGroup(InetAddress.getByName(group));
 
        byte buf[] = new byte[9500];
        DatagramPacket pack = new DatagramPacket(buf, buf.length);


	s.receive(pack);
	System.out.println("Received data from: " + pack.getAddress().toString() + ":" + pack.getPort() + " with length: " + pack.getLength());
	System.out.write(pack.getData(),0,pack.getLength());
	System.out.println();

        s.leaveGroup(InetAddress.getByName(group));
        s.close();
	} // end while true

        }


        // Adding just a catch-all 
        catch (Exception e)
        {
           e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        if (args.length == 2) { 
        MultiCastTestReceiveLoop MultiCastTestReceiveLoop = new MultiCastTestReceiveLoop(Integer.parseInt(args[1]),args[0]);
        }
        else {
            MultiCastTestReceiveLoop MultiCastTestReceiveLoop = new MultiCastTestReceiveLoop();
        }
    }
}

MultiCastTestSend.java

This program is used to send udp datagrams on a socket. The buffer is filled with the letter ‘a’ (length set with the nbytes argument on the command line

The communication follows a similar path as that used between Oracle Clusterware processes.

To compile: /jdk/bin/javac MultiCastTestSend.java

To execute: /jdk/bin/java -classpath . MultiCastTestSend 230.0.1.0 42424

import sun.net.*;
import java.net.*;

public class MultiCastTestSend {
    public MultiCastTestSend() {
        MultiCastTestSend multiCastTestSend = new MultiCastTestSend(42424,"230.0.1.0",10);
    }

    public MultiCastTestSend(int port,String group,int nbytes) {

        int ttl = 1;
        try {
        // Create the socket
        MulticastSocket s = new MulticastSocket();

	// set LAN interface to send on
	s.setNetworkInterface(NetworkInterface.getByName("<interconnect interface>"));

        // Fill the buffer with some data
        byte buf[] = new byte[nbytes];
        for (int i=0; i<buf.length; i++) buf[i] = (byte)i;
        // Create a DatagramPacket 
        DatagramPacket pack = new DatagramPacket(buf, buf.length,
                                                 InetAddress.getByName(group), port);

        s.setTimeToLive(ttl);
        s.send(pack);
        // And when we have finished sending data close the socket
        System.out.println("Sent bytes to " + pack.getAddress().toString());
        s.close();
          }
        catch (Exception e)
        {
        e.printStackTrace();
        }


    }

    public static void main(String[] args) {
        if (args.length == 3) { 
        MultiCastTestSend multiCastTestSend = new MultiCastTestSend(Integer.parseInt(args[1]),args[0],Integer.parseInt(args[2]));
        }
        else {
            MultiCastTestSend multiCastTestSend = new MultiCastTestSend();
        }
    }
}

Windows – Start DOS prompt from Windows Explorer. Start Windows Explorer from DOS prompt

Posted by

0

Start DOS prompt from Windows Explorer

DOS prompt (cmd) can be launched from Wondows Explorer directly by typing cmd in the address bar. The result is a DOS prompt located at the same directory as Windows Explorer

Start Windows Explorer from DOS prompt

Windows Explorer can be launched from DOS prompt by typing “start .” at the DOS prompt

DOS prompt output directly to clipboard

The output of a command in DOS prompt can be piped directly to the clipboard, example: dir | clip