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

Advertisements

Get Server Lifecycle Info from MS Support into a table

As part of database life cycle management process organizations ensure database is patched and kept up to date to ensure database system is secured from any vulnerabilities and to stay in support life cycle.

Microsoft Support Life cycle provides a consistent and predictable guidelines for product support availability when a product releases and throughout that product’s life. By understanding the product support available, customers are will be able to maximize the management of their IT investments and strategically plan for a successful IT future.

To know the current status of Microsft life cycle is not easy, MS provides an Excel file that contains all the build versions together with their current support life cycle stage for 2005 through the current version is available.(please note as we speak MS ended support for 2005)

So put to together a powershell script that will load the excel file into SQL Server table.

  1. First step download the excel file.
  2. Second create a table and procedure in any Database to hold the SQL Lifecycle data
  3. load the Excel file into the Table.

Link is available on this page.(https://support.microsoft.com/en-us/kb/321185) (http://download.microsoft.com/download/7/C/1/7C1BA242-14B5-48AE-9A99-7CE4FE9DAAF9/SQL%20Server%20Builds%20V3.xlsx)

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SQLServerBuilds](
	[Version] [varchar](100) NOT NULL,
	[BuildNumber] [varchar](100) NOT NULL,
	[KBNumber] [varchar](100) NOT NULL,
	[ServicePackRTM] [varchar](100) NULL,
	[ReleaseDate] [date] NULL,
	[UpdateNumber] [varchar](100) NULL,
	[IncrementalServicing] [varchar](100) NULL,
	[IncludeSecurity] [varchar](1000) NULL,
	[LifeCycleStatus] [varchar](100) NULL,
	[Notes] [varchar](max) NULL,
	[EOS] [date] NULL,
	[inserted_on] [datetime] NOT NULL,
	[inserted_by] [varchar](100) NOT NULL,
 CONSTRAINT [PK_SQLServerBuilds] PRIMARY KEY CLUSTERED 
(
	[Version] ASC,
	[BuildNumber] ASC,
	[KBNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[SQLServerBuilds] ADD  CONSTRAINT [DF_SQLServerBuilds_inserted_on]  DEFAULT (getdate()) FOR [inserted_on]
GO

ALTER TABLE [dbo].[SQLServerBuilds] ADD  CONSTRAINT [DF_SQLServerBuilds_inserted_by]  DEFAULT (suser_sname()) FOR [inserted_by]
GO

  Create  procedure [dbo].[wmsp_CUSQLServerBuilds] (
  @version varchar(100)
  ,@buildNumber varchar(100)
  ,@Kbnumber varchar(100)
  ,@ServicePackRTM varchar(100)
  ,@ReleaseDate varchar(100)
  ,@UpdateNumber varchar(100)
  ,@IncrementalServicing varchar(100)
  ,@IncludeSecurity varchar(1000)
  ,@LifeCycleStatus Varchar(100)
  ,@Notes varchar(100)
  )
  as
  begin 
  declare @eos date;
  set @ReleaseDate = case when  PATINDEX('%[0-9]%',@ReleaseDate)  = 1 and PATINDEX('%[A-Z]%',@ReleaseDate) = 0 then dateadd(dd,convert(int,@ReleaseDate),'12/31/1899 00:00:00') else @ReleaseDate end
  set @eos = case when  PATINDEX('%[0-9]%',@Notes)  = 1 and PATINDEX('%[A-Z]%',@Notes) = 0 then dateadd(dd,convert(int,@Notes),'12/31/1899 00:00:00') else null end
  if not  exists (select 1 from SQLServerBuilds where [version] = @version and BuildNumber = @buildNumber and KBNumber = @Kbnumber)
  Begin 
  Insert into SQLServerBuilds(
       [version]
       ,[BuildNumber]
      ,[KBNumber]
      ,[ServicePackRTM]
      ,[ReleaseDate]
      ,[UpdateNumber]
      ,[IncrementalServicing]
      ,[IncludeSecurity]
      ,[LifeCycleStatus]
      ,[Notes]
      ,EOS)
   Values (
   @version
   ,@buildNumber 
  ,@Kbnumber 
  ,@ServicePackRTM 
  ,@ReleaseDate 
  ,@UpdateNumber 
  ,@IncrementalServicing 
  ,@IncludeSecurity 
  ,@LifeCycleStatus 
  ,@Notes
  ,@eos
  )
  end 
  else 
  begin 
  
  update SQLServerBuilds
  set LifeCycleStatus= @LifeCycleStatus
  ,UpdateNumber = @UpdateNumber
  ,IncrementalServicing = @IncrementalServicing
  ,IncludeSecurity = @IncludeSecurity
  ,Notes =  @Notes
  ,ReleaseDate = @ReleaseDate
  ,ServicePackRTM = @ServicePackRTM
  ,eos = @eos
 where [version] = @version and BuildNumber = @buildNumber and KBNumber = @Kbnumber 
  end 
  
  end    

 

Powershell Script to load the excel

 $srv = "Server Name"
$Dbname = "Database Name"
$filelocation = "Excel File Location"

add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$cscn = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $srv 


$files = Get-ChildItem $filelocation *.xlsx

foreach ($file in $files) { 
 Get-ExcelSheetInfo $file.FullName  | ForEach-Object {
           $Sheet =$_.Name; 
                  $SQLserver = Import-Excel $file.FullName -Sheet $Sheet; ForEach ($SQL in $SQLserver) {
                       $Buldnumber = $SQL.'Build number'; 
                        $kbnumber = $SQL.'KB number';  
                        $lifecycle = if($SQL.'Lifecycle stage' -EQ $null){$SQL.'Lifecycle status (Service Pack Support End Date) '}else{$SQL.'Lifecycle stage'}	
                        $Updatenumber = $SQL.'Cumulative Update number/Security ID'; 
                        $IncrementalServicing = $SQL.'Incremental Servicing Model';
                        $inlcudeSecurity = $SQL.'Includes latest security fixes?';
	                    [String]$notes =  $SQL.'Notes regarding Lifecycle stage'+$SQL.'Notes regarding Lifecycle stage/ Service Pack support end date'+$SQL.'Lifecycle status (Service Pack Support End Date) ';
	                    if($notes -ne $null){ $notes=$notes.replace("'","''")}
                        $releasedate =  $SQL.'Release Date';
	                    $servicepack = $SQL.'Service Pack/RTM';
                     if($Buldnumber -NE $null){ 
                            $cscn.Databases[$Dbname].ExecuteNonQuery("exec wmsp_cuSQLServerBuilds '$Sheet','$Buldnumber','$kbnumber','$servicepack','$releasedate','$Updatenumber','$IncrementalServicing','$inlcudeSecurity','$lifecycle','$notes'")}
                     }
        }

 

Final Result

sqlserverbuilds

Tips and Tricks to avoid common pit falls with SQL CLR deployment

  1. If you are using Visual Studio to build and deploy the SQL CLR solution ensure you have below settings

SQLCLR

SQL CLR2

SQLCLR3.jpg

2) First things first, Ensure CLR Integration is enabled at instance level.

EXEC sp_configure 'show advanced options' , '1';
reconfigure;

EXEC sp_configure 'clr enabled' , '1' ;
reconfigure;

EXEC sp_configure 'show advanced options' , '0';
reconfigure;

 

3) Ensure Appropriate CLR Integration Code Access Security levels are set when creating assembly’s in SQL Server

CREATE ASSEMBLY [SOAPSQLSPCLR]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.WMVDSQL01\MSSQL\Binn\SQLCLRSoapConsumer.dll'
WITH PERMISSION_SET = [EXTERNAL_ACCESS,SAFE,UNSAFE]
GO

 

  • SAFE : Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry
  • EXTERNAL_ACCESS: These assemblies have the same permissions as SAFE assemblies, with the additional ability to access external system resources such as files, networks, environmental variables, and the registry
  • UNSAFE:  unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can also call unmanaged code.

Please refer to msdn documentation here https://msdn.microsoft.com/en-us/library/ms345101.aspx

4) Ensure the database is setup correctly

If you are using assemblies with External access and Unsafe the database property TRUSTWORTHY needs to be turned on.

USE database_name
GO
EXEC sp_changedbowner 'sa'
ALTER DATABASE database_name SET TRUSTWORTHY ON 

 

Refer to MSDN documentation on TRUSTWORTHY https://msdn.microsoft.com/en-us/library/ms187861.aspx

Variable WMI not found…

When you are setting up alerts using WMI and if your intention is to capture the event data with sql agent job make sure you change below settings in SQL Server Agent Configuration.

dba_sqlagent

or

EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1
GO

 

This will allow SQL Server Agent to replace the tokens with running values (what database changed, who changed it etc.). Read more about using Tokens in Job Steps here (make sure you read the security note in the article and understand the security risk).

Clone your SQL Database instantly with new DBCC command CLONEDATABASE

 

Based on MS documentation this is a quick way to create a copy of database (only Schema) including statistics and Indexes of source database this was released in SQL 14 service pack 2.

When this command is issued SQL Server creates an internal snapshot of source database just like how it creates for checkdb and drops this snapshot when the cloning process is done but during the cloning process it holds a shared lock on source database and X lock on target database and it leaves target database in read only mode although you can change the state of the target database if you intent to add data or modify.

So why do you need to clone database.

According to MS DBCC CLONEDATABASE should be used to create a schema and statistics only copy of a production database in order to investigate query performance issues.”. The original intention of the feature is to diagnose any performance issues of a production database with out needing to effect the production database. Although this is so late in the game its never to late for new feature.

Don’t confuse with Database Snapshots that’s totally different concept.
dbcc clonedatabase([AdventureWorks2014],[AdventureWorks2014_Clone])

 

dbcc clone 1

 

dbcc clone 2

https://support.microsoft.com/en-us/kb/3177838

Bottom line: Saves a lot of time when you are debugging execution plans and performance related issues.

High CPU Usage SQL Server (One Bad Query)

My colleague reported to me that one of our database server is reporting consistent high CPU usage so I looked at it I noticed CPU was at 100% from last one week when I contacted the application owner and I foundthat they implemented a new feature that polls the database for every second to ensure the data collection process is running properly as it was necessary to ensure that we are under compliance in terms of reporting and auditing. So I ran a query to pull the queries with high cpu utilization with execution count. I certainly noticed a query running more often with high cpu usage.

 

Exec stats

I know that above highlighted query is causing the high cpu usage, next I looked at query stats and noticed this query is running twice every second, so I looked at the plan

Select top 1 col1 from table order by 1

Table is clustered and col1 is not part of clustered index and does not have an index. simple enough SQL server decides to do Clustered index scan and sorts(fully blocking) col1 and selects 1 row with no predicate SQL server doesn’t think its missing an index.

PLan 1

PLan 1 properties .jpg
SQL Server Execution Times: with out non clustered index
CPU time = 2296 ms, elapsed time = 658 ms.

So I created a non clustered index on col1 in desc on the table

plan 2.jpg
SQL Server Execution Times: with non clustered index
CPU time = 0 ms, elapsed time = 0 ms.

Cpu Usage dramatically reduced

Cpu usage.jpg

Bottom line:

Its very important to understand no matter how much physical resources you might have on a  server its very important understand that one bad query can literally bring the server down to it knees.

 

Search a Column from a table in SQL Database

SELECT
SCHEMA_NAME(schema_id)+'.'+[name] as objectname
,type_desc
,referenced_schema_name AS SchemaName
,referenced_entity_name AS TableName
,referenced_minor_name  AS ColumnName
FROM [sys].[all_objects] ob cross apply sys.dm_sql_referenced_entities ( SCHEMA_NAME(schema_id)+'.'+[name], 'OBJECT') e
where is_ms_shipped = 0 and type_desc in ('AGGREGATE_FUNCTION'
,'SQL_SCALAR_FUNCTION'
,'SQL_INLINE_TABLE_VALUED_FUNCTION'
,'SQL_STORED_PROCEDURE'
,'SQL_TABLE_VALUED_FUNCTION'
,'SQL_TRIGGER'
,'VIEW')
and name !='sp_upgraddiagrams'
and referenced_entity_name  = 'table name'
and referenced_minor_name = 'columnname'