Create RMAN Catalog and Register a Database in 12c

Create tablespace
create tablespace rec_catalog
datafile ‘/data/oradata/CDBRCAT/rec_catalog.dbf’
size 10M autoextend on
extent management local uniform size 1M;

alter session set “_ORACLE_SCRIPT”=true;

Create user
create user rcat identified by randompassword
default tablespace rec_catalog
quota unlimited on rec_catalog;

Grant Privileges 
grant recovery_catalog_owner to rcat;
select privilege from dba_sys_privs where grantee = ‘RECOVERY_CATALOG_OWNER’;

PRIVILEGE
—————————————-
CREATE TABLE
ALTER SESSION
CREATE VIEW
CREATE TYPE
CREATE SYNONYM
CREATE CLUSTER
CREATE TRIGGER
CREATE SESSION
CREATE PROCEDURE
CREATE DATABASE LINK
CREATE SEQUENCE

connect to the Catalog

Create an entry in TNS names CDBRCAT

rman target / catalog rcat@CDBRCAT

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Aug 10 10:19:12 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: CDBRCAT (DBID=1512138245)
recovery catalog database Password:
connected to recovery catalog database

Create RMAN Catalog

RMAN> create catalog tablespace rec_catalog;
recovery catalog created

Register the current database
RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

 

Verify if Database is registered in RMAN Catalog

sqlplus rcat@CDBRCAT

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 10 10:31:20 2018

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

Enter password:
Last Successful login time: Fri Aug 10 2018 10:26:06 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select * from rc_database;

DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
———- ———- ———- ——– —————– ———
FINAL_CHANGE#
————-
1 2 1512138245 CDBRCAT 6766569 20-JUN-18

Setup SQL Agent alerts for User Error Messages

Setup a simple alert with SQL agent

1

When I test the alert it does not trigger the alert

Capture 2

 

Since error is not being logged, we can verify in the sys.messages

Capture 3

Lets alter the message to log

Capture 4

 

SELECT * FROM sys.messages WHERE
language_id = 1033 AND severity = 16
and message_id = 2812
EXEC sp_altermessage @message_id = 2812, @parameter = ‘WITH_LOG’ ,@parameter_value = ‘True’

Test the alert again, now we can see number of occurrences increases to 1

Capture 5

Failed to initialize sqlcmd library with error number -2147467259 [solved]

 

Setup trace

Send mail again.

Check trace output

SELECT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Change the send dmail command to include QUOTED_IDENTIFIER.

 

 

Log SQL Errors using Extended Events

First create the extended event session to monitor for any errors with severity grater than equal to 11

CREATE EVENT SESSION [ErrorCapture] ON SERVER ADD EVENT sqlserver.error_reported (ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.sql_text, sqlserver.username) WHERE ([severity] >= (11))) ADD TARGET package0.event_file (
	SET filename = N'ErrorCapture'
	,max_file_size = (100)
	,max_rollover_files = (5)
	)
	WITH (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = OFF
,STARTUP_STATE = ON
)
GO

 

How to view them in TSQL

DECLARE @error_log VARCHAR(max) = convert(VARCHAR(max), SERVERPROPERTY('ErrorLogFileName'))
DECLARE @error_log_dir VARCHAR(max) = reverse(substring(reverse(@error_log), charindex('\', reverse(@error_log)), len(@error_log)))

SELECT *
FROM sys.fn_xe_file_target_read_file(@error_log_dir + 'errorcap*.xel', NULL, NULL, NULL)

 

Simple powershell script to Bulk Load csv into a SQL Server table.

#Read csv file 
#open connection to database using bulkcopy
#convert array to data table
#bulkload data into table
#note: column sequence and data types should match

function Get-Type 
{ 
    param($type) 
 
$types = @( 
'System.Boolean', 
'System.Byte[]', 
'System.Byte', 
'System.Char', 
'System.Datetime', 
'System.Decimal', 
'System.Double', 
'System.Guid', 
'System.Int16', 
'System.Int32', 
'System.Int64', 
'System.Single', 
'System.UInt16', 
'System.UInt32', 
'System.UInt64') 
 
    if ( $types -contains $type ) { 
        Write-Output "$type" 
    } 
    else { 
        Write-Output 'System.String' 
         
    } 
}


function Out-DataTable 
{ 
    [CmdletBinding()] 
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject) 
 
    Begin 
    { 
        $dt = new-object Data.datatable   
        $First = $true  
    } 
    Process 
    { 
        foreach ($object in $InputObject) 
        { 
            $DR = $DT.NewRow()   
            foreach($property in $object.PsObject.get_properties()) 
            {   
                if ($first) 
                {   
                    $Col =  new-object Data.DataColumn   
                    $Col.ColumnName = $property.Name.ToString()   
                    if ($property.value) 
                    { 
                        if ($property.value -isnot [System.DBNull]) { 
                            $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 
                         } 
                    } 
                    $DT.Columns.Add($Col) 
                }   
                if ($property.Gettype().IsArray) { 
                    $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 
                }   
               else { 
                    $DR.Item($property.Name) = $property.value 
                } 
            }   
            $DT.Rows.Add($DR)   
            $First = $false 
        } 
    }  
      
    End 
    { 
        Write-Output @(,($dt)) 
    } 
 
} #Out-DataTable

$file="C:\samplecsv.csv"
$dbserver="ENTER DB SERVER NAME"
$database="ENTER DB NAME"
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$dbserver;Integrated Security=SSPI;Initial Catalog=$database");
$cn.Open()
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
$bc.BatchSize = 10000;
$bc.BulkCopyTimeout = 1000
$bc.DestinationTableName = "ENTER TABLE NAME WITH SCHEMA NAME"

$data = Import-Csv $file | Out-DataTable

$bc.WriteToServer($data)

[solved] Docker and Windows 10 hyper-v Containers, byLinuxVM’ failed to start.. . Virtual machine ‘MobyLinuxVM’ could not be started because the hypervisor is not running

If you are like me trying to run windows Docker containers in a hyper-v vm windows 10 guest on a windows 10 host, you might have come across above issue, we can fix this issue in two steps.

  • First make sure you are running on most up to date windows 10, prerequisite for OS requirements is to have Windows 10 Anniversary Update.
  • Second enable nested virtualization, why do we need to do that ?

nesteddiagram2

In this case, Hyper-V has been configured to expose virtualization extensions to its guest VM. A guest VM can take advantage of this, and install its own hypervisor. It can then run its own guest VMs, more info visit msdn blog

So how to enable nested virtualization,

first turn off the vm, run below command on the host OS, I would say the root OS.

Set-VMProcessor -VMName <VMName> -ExposeVirtualizationExtensions $true

Enable networking for nested virtual machine, can be done in two ways Mac spoofing or NAT mode.

will go with Mac Spoofing, to enable MAC spoofing, run below command on host os.

Get-VMNetworkAdapter -VMName <VMName> | Set-VMNetworkAdapter -MacAddressSpoofing On

Make sure the guest OS has enough memory to run MobyLinuxVm docker container to initialize, I would say at least 4 gigs.