Schedule SQL Server Trace

 https://www.mssqltips.com/sqlservertip/1715/scheduling-a-sql-server-profiler-trace/

gyrfalcon.bi_test.trc_dexter

job

SqlTrace

Create READ_ONLY on EBS

 FINAL ACTION PLAN

###################
  - Perform pre-health checks including huge pages health check if DB needs bounce - Use ATOM
  - Perform Autoconfig Health Check
  - Ensure that blackout is set
==> Take hot backup

      1. Take GRP backup using below command
         export OHSUPG_TOP=/autofs/upgrade/ohsupg
         $OHSUPG_TOP/bin/backup_db.sh -mode flashback -tag <RFC#>

      2. MT Backup : Take code tree backup.
    
==> Check adop status and check if there are any pending sessions
    - adop -status
==> If there are no pending sessions, please start prepare phase.
- adop phase=prepare
==> Source patch file edition and proceed with below steps to verify user and grants from patch file edition.
1: Login to DB Node as sys user

Note:READ_ONLY is already existing
--enable edition for new user
    alter user READ_ONLY enable editions;

2: GRANT CONNECT,RESOURCE TO READ_ONLY;

3: Grant READ_ONLY only access to READ_ONLY user to view all tables/views/seqence

Log into sqlplus As APPS user

set heading off pages 0 trimspool on lines 120 feedback off echo off termout off
spool /ood_repository/cm/scripts/3-B9YN3GP/createselect.sql
SELECT 'Grant select on APPS.'||object_name||' to READ_ONLY;' from dba_objects where owner='APPS' and object_TYPE in ('TABLE','VIEW','SEQUENCE');
SELECT  'Grant select on '||owner||'.'||table_name||' to READ_ONLY;' FROM dba_tab_privs WHERE privilege='SELECT' and grantee= 'APPS';
SELECT  'Grant execute on '||owner||'.'||table_name||' to READ_ONLY;' FROM dba_tab_privs WHERE privilege='EXECUTE' and grantee= 'APPS';
SELECT  'Grant execute on APPS.'||object_name||' to READ_ONLY;' FROM dba_objects WHERE object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY') and owner= 'APPS';
spool off

4: Create synonym for READ_ONLY

set heading off pages 0 trimspool on lines 120 feedback off echo off termout off
spool /ood_repository/cm/scripts/3-B9YN3GP/createsyn.sql
select 'create or replace synonym READ_ONLY.'||SYNONYM_NAME||' for '||TABLE_OWNER||'.'||table_name||';' from dba_synonyms where owner='APPS' and table_name in (select table_name from dba_tab_privs where privilege in ('SELECT','EXECUTE') and grantee='READ_ONLY');
SELECT  'create or replace synonym READ_ONLY.'||object_name||' for APPS.'||object_name||';' FROM dba_objects WHERE object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TABLE','VIEW','SEQUENCE') and owner= 'APPS';
spool off

6: Login as apps and run below
@/ood_repository/cm/scripts/3-B9YN3GP/createselect.sql

7: Login as apps user and run below
@/ood_repository/cm/scripts/3-B9YN3GP/createsyn.sql

Verifications
###############
Run below commands to verify READ_ONLY  user able to view all granted objects.

Connect READ_ONLY/<password>

SQL> select count(*),object_type from dba_objects where owner='READ_ONLY' group by object_type;
SQL> SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE FROM DBA_USERS WHERE USERNAME='READ_ONLY';
SQL> select count(*) from dba_tab_privs where GRANTEE='APPS';
SQL> select count(*) FROM  dba_objects WHERE OWNER='APPS' and OBJECT_TYPE in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');
SQL> select count(*) FROM  dba_objects WHERE OWNER='READ_ONLY' and OBJECT_TYPE in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');



==> Run finalize
- adop phase=finalize

==> Run cutover ( Offline phase )
- adop phase=cutover mtrestart=yes
- adop phase=cleanup
- adop -status

==> Check and compile new invalids
==>  Perform post checks.

- Perform post-health checks including huge pages health check if DB needs bounce - Use ATOM
- Ensure that blackout is unset

- Take a count/list of invalid objects

SQL SERVER grant user

 grant select on ps_lake_demand_utilization  to [engg_demand_util];

Oracle EBS domain change

 https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=446100599746504&id=759225.1&_afrWindowMode=0&_adf.ctrl-state=ewe2g0oce_53

E-Business Suite Applications Technology Stack Steps To Change the Domain Name for an E-Business Suite 11i / R12 Instance (Doc ID 759225.1)

Oracle ebs recreate context_file

https://abduulwasiq.blogspot.com/2013/09/if-we-lost-contextfile.html

Oracle display current time to a different timezone

 SELECT username, TO_CHAR(
         FROM_TZ( CAST( created AS TIMESTAMP ), 'AMERICA/PHOENIX' )
           AT TIME ZONE 'ASIA/MANILA',
         'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR'
       ) AS MANILA_time, created
FROM   dba_users where username like 'RO_%' or username='ORA_AUDIT' order by 1;

SELECT  TO_CHAR(
         FROM_TZ( CAST( sysdate AS TIMESTAMP ), 'AMERICA/PHOENIX' )
           AT TIME ZONE 'ASIA/MANILA',
         'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR'
       ) AS manila_time, sysdate
FROM   dual;

eTrace delete test

create table Rowcounter (
rowcountt int,
timestampcol datetime default current_timestamp);

create job

Batchdelete

declare @row as int

declare @ctr as int = 0

declare @subj as varchar(300)

select @row = count(tdid) from dxT_TDHeader

while @ctr <= @row

begin

delete top(50000) from dxt_TDItem where tdid in (select tdid from dxT_TDHeader)

insert into rowcounter(rowcountt) values(@@rowcount)

set @ctr = @ctr + 50000

end

select * from rowcounter;

SQL PAR LIST

 --List of Sysadmin

SELECT 'Name' = sp.NAME

    ,sp.is_disabled AS [Is_disabled]

FROM sys.server_role_members rm

    ,sys.server_principals sp

WHERE rm.role_principal_id = SUSER_ID('Sysadmin')

    AND rm.member_principal_id = sp.principal_id;

 --List of role members

 SELECT DP1.name AS DatabaseRoleName,   

    isnull (DP2.name, 'No members') AS DatabaseUserName   

FROM sys.database_role_members AS DRM  

RIGHT OUTER JOIN sys.database_principals AS DP1  

    ON DRM.role_principal_id = DP1.principal_id  

LEFT OUTER JOIN sys.database_principals AS DP2  

    ON DRM.member_principal_id = DP2.principal_id  

WHERE DP1.type = 'R'

--dumps

select * from sys.server_role_members;

select * from sys.server_principals;

select * from  sys.database_role_members;

select * from  sys.database_principals;

How to migrate oracle audit logs to a new tablespace

 There was this situation when our oracle audit log suddenly exploded when one of the users ran a procedure. This caused the system tablespace to grow from 1G to 32G in a span of 8 hours.

This was caused by the audit table is in the system tablespace.

Below are the following steps to move the default audit log to a different tablespace.

Migrate the AUDIT tables to a dedicated tablespace

STEP 1

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME           TABLESPACE_NAME

-------------------- ------------------------------

AUD$                 SYSTEM

FGA_LOG$             SYSTEM

STEP 2

CREATE TABLESPACE

CREATE TABLESPACE ORA_AUDIT 

DATAFILE '/logs/oradata/EPWEBP/ora_audit1.dbf' 

SIZE 3G;

   

CREATE TABLESPACE ORA_AUDIT_ARCHIVE 

DATAFILE '/logs/oradata/EPWEBP/audit_archive1.dbf' 

SIZE 3G;

STEP 3

--this moves table FGA_LOG$

Run as system

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'ORA_AUDIT');
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'ORA_AUDIT');
END;
/

STEP 4 ARCHIVE CURRENT AUDIT LOG

CREATE TABLE AUDIT_10272020 TABLESPACE ORA_AUDIT_ARCHIVE as select * from SYS.AUD$;

TRUNCATE TABLE sys.aud$; 

ALTER TABLESPACE

 ALTER DATABASE 

  TEMPFILE '/logs/oradata/EPCHRS/temp01.dbf'

  AUTOEXTEND ON

  NEXT 10M

  MAXSIZE UNLIMITED;

TNS-01189: The listener could not authenticate the user

 This happened to me when I cloned a VM.

[oracle@serpentcustom logs]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 21-OCT-2020 20:19:59

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serpentcustom.ecp.priv)(                                                                             PORT=1521)))

TNS-01189: The listener could not authenticate the user

SOLUTION:

[oracle@serpentcustom admin]$ cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.131.128.78 serpentcustomclone.ecp.priv serpentcustomclone

edit your listener.ora  to the correct host

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = serpentcustomclone.ecp.priv)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

How to check if you are able to connect to PART1i

 portalgun:/dbapps/webapps/reports/Loader>load_ep_delivery_documents.sh

-bash: load_ep_delivery_documents.sh: command not found

portalgun:/dbapps/webapps/reports/Loader>

Create Oracle READ ONLY account

CREATE USER READ_ONLY IDENTIFIED BY "password";

GRANT CREATE SESSION TO READ_ONLY;

SET HEADING OFF

SET PAGES 9999 LINES 400

SPOOL TABLE_GRANTS.sql

SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'

FROM DBA_TABLES

WHERE OWNER IN ('PRESS')

ORDER BY 1;

SELECT 'CREATE SYNONYM RO_CAPEX.'||TABLE_NAME|| ' FOR ' || OWNER || '.' || TABLE_NAME || ';'

FROM DBA_TABLES

WHERE OWNER IN ('PRESS')

ORDER BY 1;

SPOOL OFF

@TABLE_GRANTS.sql

!rm TABLE_GRANTS.sql

Oracle SOX Users

--Password History

select

  name,

   password_date

from

   sys.user$,

   sys.user_history$

where

   user$.user# = user_history$.user#;

   

--Password Expired

   

 select name,type#,ptime,exptime,ltime, lcount from sys.user$ where type#=1;

 

 select name,type#,ptime,exptime from sys.user$ where type#=1 order by exptime;

 

 

--Password history 

 select

  name,

   password_date

from

   sys.user$,

   sys.user_history$

where

name='DAV4635'

and

user$.user# = user_history$.user#;

--show logon failures

select dbusername,client_program_name,terminal,os_username,action_name, unified_audit_policies from unified_audit_trail 

where unified_audit_policies='ORA_LOGON_FAILURES' order by event_timestamp desc ;

 

How to connect mariadb to powerbi

 

Dennis asked me to help in connection POWER BI to Nagios

 

Follow this steps

 

1.       Download MariaDB connector https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.9/  or use the attached one.

2.       Install mariadb

3.       Choose typical >Install>finish

4.       Open ODBC 64 bit

5.       Add> Maria DB ODBC> Finish

6.       Name: Nagios_Test

7.       

8.      

9.       User:ro_chow/dropwater

10.   Click Next until finish

11.   Connect using Power BI

12.   

13.  

 

Have fun!!!

How to create user in mysql

 mysql -u root -p'nagiosxi'

  

  MariaDB [nagiosxi]> create user 'ro_chow'@'%' identified by 'dropwater';

Query OK, 0 rows affected (0.05 sec)

MariaDB [nagiosxi]> grant select on nagiosxi.* to 'ro_chow'@'%';

Query OK, 0 rows affected (0.00 sec)

MariaDB [nagiosxi]> flush privileges;

Type CREATE USER 'troy'@'%' IDENTIFIED BY 'troypassword'; and press Enter

o Type GRANT ALL PRIVILEGES ON *.* TO 'troy'@'%' WITH GRANT OPTION; and press Enter

o Type CREATE USER 'troy'@'localhost' IDENTIFIED BY 'troy'; and press Enter

o Type GRANT ALL PRIVILEGES ON *.* TO 'troy'@'localhost' WITH GRANT OPTION; and press Enter

o Type exit and press Enter

CREATE USER 'tony'@'%' IDENTIFIED BY 'Password'; and press Enter

o Type GRANT ALL PRIVILEGES ON *.* TO 'tony'@'%' WITH GRANT OPTION; and press Enter

o Type CREATE USER 'tony'@'localhost' IDENTIFIED BY Password'; and press Enter

o Type GRANT ALL PRIVILEGES ON *.* TO 'tony'@'localhost' WITH GRANT OPTION; and press Enter

o Type exit and press Enter

Aerial Combat- Hornets - ASMR

R3d Ta1l$

How to run sqlcmd as another network user

 Open a command prompt

C:\WINDOWS\system32>runas /user:ecp\admdvelasco cmd.exe

this will open another window

Microsoft Windows [Version 10.0.16299.1087]

(c) 2017 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>sqlcmd -S spectre

1> select name from sys.databases

2> go

name                                                                                                                    

--------------------------------------------------------------------------------------------------------------------------------

master                                                                                                                  

tempdb                                                                                                                  

model                                                                                                                   

msdb                                                                                                                    

eTraceOneToOne                           

There is insufficient system memory in resource pool 'internal' to run this query. SQL SERVER cannot start

 While playing with the DB, I was curious what will happen if I set the max memory of the sql server instance to 10mb.

So it crashed.

Now I cannot restart it.

This is how I fixed it.

Step 1 start the sqlserver instance with minimum settings.

1. open a command prompt as an administrator 

2. Go to the directory where SQL server is installed

    cd C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn

    then run 'sqlservr.exe -f -s <instance name>'

3. open a separate admin window like in step 1

4. type sqlcmd

EXEC sys.sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sys.sp_configure 'min server memory', 1024;

GO

EXEC sys.sp_configure 'max server memory', 4096;

GO

RECONFIGURE;

GO

5. Go back to the first window and do a ctrl c to shutdown the instance

6. Startup normal in the sql server configuration manager.

7. you are back in business

02-Baselining Hardware

 

02-Baselining Hardware

Monday, January 28, 2019

2:10 PM

-- Hardware and Storage Configuration Queries

-- Glenn Berry

-- Make sure you are using the correct version of these diagnostic queries for your version of SQL Server

-- Check the major product version to see if it is SQL Server 2017 CTP 1 or greater

IF NOT EXISTS (SELECT * WHERE CONVERT(varchar(128), SERVERPROPERTY('ProductVersion')) LIKE '14%')

BEGIN

DECLARE @ProductVersion varchar(128) = CONVERT(varchar(128), SERVERPROPERTY('ProductVersion'));

RAISERROR ('Script does not match the ProductVersion [%s] of this instance. Many of these queries may not work on this version.' , 18 , 16 , @ProductVersion);

END

ELSE

PRINT N'You have the correct major version of SQL Server for this diagnostic information script';

-- Hardware information from SQL Server 2017  (Query 1) (Hardware Info)

SELECT cpu_count AS [Logical CPU Count], scheduler_count, 

       (socket_count * cores_per_socket) AS [Physical Core Count], 

       socket_count AS [Socket Count], cores_per_socket, numa_node_count,

       physical_memory_kb/1024 AS [Physical Memory (MB)], 

       max_workers_count AS [Max Workers Count], 

   affinity_type_desc AS [Affinity Type], 

       sqlserver_start_time AS [SQL Server Start Time], 

   virtual_machine_type_desc AS [Virtual Machine Type], 

       softnuma_configuration_desc AS [Soft NUMA Configuration], 

   sql_memory_model_desc, process_physical_affinity -- New in SQL Server 2017

FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

------

-- Gives you some good basic hardware information about your database server

-- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean 

--       you are running SQL Server inside of a VM

-- It merely indicates that you have a hypervisor running on your host

-- sys.dm_os_sys_info (Transact-SQL)

-- https://bit.ly/2pczOYs

-- Soft NUMA configuration was a new column for SQL Server 2016

-- OFF = Soft-NUMA feature is OFF

-- ON = SQL Server automatically determines the NUMA node sizes for Soft-NUMA

-- MANUAL = Manually configured soft-NUMA

-- Configure SQL Server to Use Soft-NUMA (SQL Server)

-- https://bit.ly/2HTpKJt

-- sql_memory_model_desc values (Added in SQL Server 2016 SP1)

-- CONVENTIONAL

-- LOCK_PAGES

-- LARGE_PAGES

-- Get System Manufacturer and model number from SQL Server Error log (Query 2) (System Manufacturer)

EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';

------ 

-- This can help you determine the capabilities and capacities of your database server

-- Can also be used to confirm if you are running in a VM

-- This query might take a few seconds if you have not recycled your error log recently

-- This query will return no results if your error log has been recycled since the instance was started

-- Get BIOS date from Windows Registry (Query 3) (BIOS Date)

EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\BIOS', N'BiosReleaseDate';

------

-- Helps you understand whether the main system BIOS is up to date, and the possible age of the hardware

-- Not useful for virtualization

-- Get processor description from Windows Registry  (Query 4) (Processor Description)

EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';

------

-- Gives you the model number and rated clock speed of your processor(s)

-- Your processors may be running at less than the rated clock speed due

-- to the Windows Power Plan or hardware power management

-- You can use CPU-Z to get your actual CPU core speed and a lot of other useful information

-- https://www.cpuid.com/softwares/cpu-z.html

-- You can learn more about processor selection for SQL Server by following this link

-- https://bit.ly/2F3aVlP

-- SQL Server NUMA Node information  (Query 5) (SQL Server NUMA Info)

SELECT node_id, node_state_desc, memory_node_id, processor_group, cpu_count, online_scheduler_count, 

       idle_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state

FROM sys.dm_os_nodes WITH (NOLOCK) 

WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);

------

-- Gives you some useful information about the composition and relative load on your NUMA nodes

-- You want to see an equal number of schedulers on each NUMA node

-- Watch out if SQL Server 2017 Standard Edition has been installed 

-- on a physical or virtual machine with more than four sockets or more than 24 physical cores

-- sys.dm_os_nodes (Transact-SQL)

-- https://bit.ly/2pn5Mw8

-- Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes

-- https://bit.ly/2vfC4Rq

-- Get memory-optimized checkpoint mode from SQL Server Error log (Query 6) (Memory-Optimized Checkpoint Mode)

EXEC sys.xp_readerrorlog 0, 1, N'In-Memory OLTP initialized on';

------ 

-- Possible values:

-- In-Memory OLTP initialized on standard machine

-- In-Memory OLTP initialized on highend machine      (Large Checkpoint mode)

-- Checkpoint process for memory-optimized tables in SQL 2016 and implications on the log

-- https://bit.ly/2c8jKj1

-- A "highend machine" must meet all of these criteria:

-- The server has 16 or more logical processors

-- The server has 128GB or greater memory

-- The server is capable of greater than 200MB/sec I/O measured for the IO subsystem of that database

-- FIX: Slow database recovery in SQL Server 2016 due to large log 

--      when you use In-Memory OLTP on a high-end computer

-- https://bit.ly/2FlOEUL

-- In SQL Server 2016 CU1 and later, global TF 9912 must be enabled to get large checkpoint mode

-- Good basic information about OS memory amounts and state  (Query 7) (System Memory)

SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], 

       available_physical_memory_kb/1024 AS [Available Memory (MB)], 

       total_page_file_kb/1024 AS [Total Page File (MB)], 

   available_page_file_kb/1024 AS [Available Page File (MB)], 

   system_cache_kb/1024 AS [System Cache (MB)],

       system_memory_state_desc AS [System Memory State]

FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

------

-- You want to see "Available physical memory is high" for System Memory State

-- This indicates that you are not under external memory pressure

-- Possible System Memory State values:

-- Available physical memory is high

-- Physical memory usage is steady

-- Available physical memory is low

-- Available physical memory is running low

-- Physical memory state is transitioning

-- sys.dm_os_sys_memory (Transact-SQL)

-- https://bit.ly/2pcV0xq

-- See if buffer pool extension (BPE) is enabled (Query 8) (BPE Configuration)

SELECT [path], state_description, current_size_in_kb, 

CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]

FROM sys.dm_os_buffer_pool_extension_configuration WITH (NOLOCK) OPTION (RECOMPILE);

------

-- BPE is available in both Standard Edition and Enterprise Edition

-- It is a more interesting feature for Standard Edition

-- Buffer Pool Extension to SSDs in SQL Server 2014

-- https://bit.ly/1bm08m8

-- Buffer Pool Extension

-- https://bit.ly/2oBuieO

-- sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)

-- https://bit.ly/2qSF4xr

-- Drive information for all fixed drives visible to the operating system (Query 9) (Fixed Drives)

SELECT fixed_drive_path, drive_type_desc, 

CONVERT(DECIMAL(18,2), free_space_in_bytes/1073741824.0) AS [Available Space (GB)]

FROM sys.dm_os_enumerate_fixed_drives WITH (NOLOCK) OPTION (RECOMPILE);

------

-- This shows all of your fixed drives, not just LUNs with SQL Server database files

-- Volume info for all LUNS that have database files on the current instance (Query 10) (Volume Info)

SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name, 

CONVERT(DECIMAL(18,2), vs.total_bytes/1073741824.0) AS [Total Size (GB)],

CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],  

CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %],

vs.supports_compression, vs.is_compressed, 

vs.supports_sparse_files, vs.supports_alternate_streams

FROM sys.master_files AS f WITH (NOLOCK)

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs 

ORDER BY vs.volume_mount_point OPTION (RECOMPILE);

------

-- Shows you the total and free space on the LUNs where you have database files

-- Being low on free space can negatively affect performance with some types of storage

-- sys.dm_os_volume_stats (Transact-SQL)

-- https://bit.ly/2oBPNNr

SQL Server Basic Monitoring

In monitoring servers you should first know what to look for. Based on my experience database problems can be categorized into five categories.

1. CPU
2. Memory
3. Network
4. I/O Bottlenecks
5. Slow Queries

These are the queries we can  use to troubleshoot where the problem is.

CPU

DECLARE @BatchRequests BIGINT;

 

SELECT @BatchRequests = cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec';

 

WAITFOR DELAY '00:00:10';

 

SELECT (cntr_value - @BatchRequests) / 10 AS 'Batch Requests/sec'

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec';

The Batch Requests/sec value depends on hardware used, but it should be under 1000. The recommended value for SQL Compilations/sec is less than 10% of Batch Requests/sec and for SQL Re-Compilations/sec is less than 10% of SQL Compilations/sec

Oracle How to drop all schema objects

 select 'drop '||object_type||' &owner..'|| object_name || ';' from dba_objects WHERE OWNER=UPPER('&owner') and object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX');

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 150G SCOPE=BOTH

CREATE READ_ONLY USER IN ORACLE DATABASE

CREATE USER READ_ONLY IDENTIFIED BY "password";

GRANT CREATE SESSION TO READ_ONLY;

SET HEADING OFF

SET PAGES 9999 LINES 400

SPOOL TABLE_GRANTS.sql

SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'

FROM DBA_TABLES

WHERE OWNER IN ('ZEDDBA')

ORDER BY 1;

SPOOL OFF

@TABLE_GRANTS.sql

!rm TABLE_GRANTS.sql 

9 common mistakes you are doing right now

  10 Mistakes We Are Doing

1. Poor Handshake
2. No Eye Contact
3. Being Dismissive
4. Failing to Ask Good Questions
5. Neglecting your Reputation
6. Showing signs of disinterest
7. Talking first, giving Later
8. Failing to showcase your strongest assets
9. Speaking before you listen.
10. Trying too hard

Who is doing the backup on your sql server

; WITH bus AS (

 SELECT database_name, machine_name, backup_finish_date, media_set_id, type,msdb.dbo.backupset.software_vendor_id,

 msdb.dbo.backupset.user_name,

        row_number () OVER(PARTITION BY database_name ORDER BY backup_finish_date DESC) AS rowno

 FROM   msdb.dbo.backupset

)

SELECT bus.machine_name AS ServerName, sdb.Name AS DatabaseName,

       COALESCE(convert(varchar(23), bus.backup_finish_date), 'No backup') AS BackupTime ,

      CASE bus.type 

      WHEN 'D' THEN 'Database' 

      WHEN 'L' THEN 'Log File'

      WHEN 'I' THEN 'Differential'

      END AS BackupType,software_vendor_id,user_name,

busf.physical_device_name AS Location

FROM sys.databases sdb

LEFT OUTER JOIN bus ON bus.database_name = sdb.name AND rowno <= 2

LEFT OUTER JOIN msdb.dbo.backupmediafamily busf ON busf.media_set_id = bus.media_set_id

WHERE database_id > 4

ORDER BY bus.machine_name

my smtp server

 ecphksmtp.ecp.priv

How to find out the TOMCAT version in LINUX

 cd tomcat/lib 

java -cp catalina.jar org.apache.catalina.util.ServerInfo

FAILED LOGIN ATTEMPTS ORACLE

 

! HPRD:atlas.ecp.priv:/dbapps/oracle> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 18 02:40:33 2020

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> audit create session whenever not successful;

select os_username,  username, userhost,  to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') 

logon_time,  action_name, returncode from dba_audit_session order by 4 desc;

LINUX SWITCH USER

 sudo visudo

oracle  ALL=(root) ALL

admrcavan   ALL=(applmgr) NOPASSWD:ALL

admrcavan@apollo's password:

Last login: Mon Aug 10 18:02:52 2020 from 10.162.91.247

[admrcavan@apollo ~]$ sudo -iu applmgr

 apollo:/dbapps/applmgr/HRD2/apps/apps_st/appl>

MANAGING UNIFIED_AUDIT_TRAIL

Archiving the Unified and Traditional Database Audit Trails

You should periodically archive and then purge the audit trail to prevent it from growing too large.

Archiving and purging both frees audit trail space and facilitates the purging of the database audit trail.

You can create an archive of the unified and traditional database audit trail by using Oracle Audit Vault and Database Firewall. You install Oracle Audit Vault and Database Firewall separately from Oracle Database.

After you complete the archive, you can purge the database audit trail contents.

• To archive the unified, traditional standard, and traditional fine-grained audit records, copy the relevant records to a normal database table.

  For example:

  INSERT INTO table SELECT ... FROM UNIFIED_AUDIT_TRAIL ...;
  INSERT INTO table SELECT ... FROM SYS.AUD$ ...;
  INSERT INTO table SELECT ... FROM SYS.FGA_LOG$ ...; 

Unable to perform application-aware processing of Oracle database Veeam Backup

Your database is running in noarchivelog that is the reason for this error

Microsoft SQL Server, Error: 15023

USE MY_DB;

EXEC sp_change_users_login 'Auto_Fix', 'sa_web';

The row for user 'sa_web' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

How to purge audit logs on Oracle 11g

https://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG472

Manually Purging the Audit Trail

You can manually purge the audit trail right away, without scheduling a purge job. Similar to a purge job, you can purge audit trail records that were created before an archive timestamp date or all the records in the audit trail.

Note the following about the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure:

• Only the current audit directory is cleaned up when you run this procedure.
• On Microsoft Windows, because the DBMS_AUDIT_MGMT package does not support cleanup of Windows Event Viewer, setting the AUDIT_TRAIL_TYPE property to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS has no effect. This is because operating system audit records on Windows are written to Windows Event Viewer. The DBMS_AUDIT_MGMT package does not support this type of cleanup operation.
• On UNIX platforms, if you set the AUDIT_SYSLOG_LEVEL initialization parameter to a valid value as listed in Oracle Database Reference, then Oracle Database writes the operating system log files to syslog files. If you set the AUDIT_TRAIL_TYPE property to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, then the procedure only removes .aud files under audit directory (This directory is specified by the AUDIT_FILE_DEST initialization parameter).
• When the AUDIT_TRAIL_TYPE parameter is set to DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, this procedure only cleans up XML audit files (.xml) in the current audit directory. Oracle Database maintains an index file, called adx_$ORACLE_SID.txt, which lists the XML files that were generated by the XML auditing. The cleanup procedure does not remove this file.

For database audit trails, you must initialize the cleanup infrastructure by running the DBMS_AUDIT_MGMT.INIT_CLEANUP procedure, and then purging the database audit trail by using the method described in "Purging a Subset of Records from the Database Audit Trail".

To manually purge the audit trail:

1. Follow these steps under "Scheduling an Automatic Purge Job for the Audit Trail":
   • Step 1: If Necessary, Tune Online and Archive Redo Log Sizes
   • Step 2: Plan a Timestamp and Archive Strategy
   • Step 3: Initialize the Audit Trail Cleanup Operation
   • Step 4: Optionally, Set an Archive Timestamp for Audit Records
   • Step 5: Create and Schedule the Purge Job
   • Step 6: Optionally, Configure the Audit Trail Records to be Deleted in Batches
2. Purge the audit trail records by running the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure.
   For example:

   BEGIN
     DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
      AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      USE_LAST_ARCH_TIMESTAMP    =>  TRUE );
   END;
   /
   

   In this example:
   • AUDIT_TRAIL_TYPE: Enter one of the following values:
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML audit trail files
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types
   • USE_LAST_ARCH_TIMESTAMP: Enter either of the following settings:
     • TRUE: Deletes audit records created before the last archive timestamp. To set the archive timestamp, see "Step 4: Optionally, Set an Archive Timestamp for Audit Records". The default (and recommended) value is TRUE. Oracle recommends that you set USE_LAST_ARCH_TIMESTAMP to TRUE.
     • FALSE: Deletes all audit records without considering last archive timestamp. Be careful about using this setting, in case you inadvertently delete audit records that should have been deleted.

Purging a Subset of Records from the Database Audit Trail

You can manually remove records from the database audit trail tables. This method can be useful if you want to remove a specific subset of records. You can use this method if the database audit trail table is in any tablespace, including the SYSTEM tablespace.

For example, to delete audit records that were created later than the evening of February 28, 2009 but before March 28, 2009, enter the following statement:

DELETE FROM SYS.AUD$
   WHERE NTIMESTAMP# > TO_TIMESTAMP ('28-FEB-09 09.07.59.907000 PM') AND
   NTIMESTAMP# < TO_TIMESTAMP ('28-MAR-09 09.07.59.907000 PM');

Alternatively, to delete all audit records from the audit trail, enter the following statement:

DELETE FROM SYS.AUD$;

Only the user SYS or a user to whom SYS granted the DELETE privilege on SYS.AUD$ can delete records from the database audit trail.