Author: dbalifeeasy

Oracle Enterprise Manager Corrective Action (CA) Execution Stats

How many times Corrective Action (CA) has been executed one year so far ?

SQL> select CA_NAME,  count(*) 
       from sysman.MGMT$CA_EXECUTIONS 
      where START_TIME>=sysdate-365  
   group by CA_NAME order by 1

CA_NAME                                  COUNT(*)
---------------------------------------- ----------
...
..
.
EXTEND_TABLESPACE_NORAC                         3
EXTEND_TABLESPACE_PDB                          16
EXTEND_TABLESPACE_RAC                          70
NON-RAC_RMAN_AUTO_BACKUP_ARCHIVELOGS           56
RMAN_AUTO_BACKUP_ARCHIVELOGS                  533
...
..
.

10 rows selected.

About view MGMT$CA_EXECUTIONS , Oracle official documentation is here or just click the following link.

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.5/emvws/job-views.html#GUID-CBDDC556-498B-4458-BCB4-CF28CAB3A834

How to Upgrade AWS RDS Microsoft SQL Server DB Engine

This blog demonstrates how to do minor version upgrades and major version upgrades against SQL server RDS on AWS.

This post also addresses some errors like ‘InvalidParameterCombination’, etc.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

How to Get All Oracle Databases Size From OEM Repository

Database manager asks you as a DBA about all the database size in the supported environment .

On 13.1 OMS repository:

SQL> select host_name,target_name, sum( FILE_SIZE/1024/1024/1024)
     from mgmt$db_datafiles 
     group by host_name,target_name 
     order by host_name, target_name;

HOST_NAME                                TARGET_NAME                              SUM(FILE_SIZE/1024/1024/1024)
---------------------------------------- ---------------------------------------- -----------------------------
HOST1                                    DB1                                                    38.65
HOST2                                    DB2                                                    385.88
HOST3                                    DB3                                                    27.52
...
..
.

OR we can use another another view mgmt$db_tablespaces :

SQL> select host_name,target_name,sum( TABLESPACE_SIZE/1024/1024/1024) 
     from mgmt$db_tablespaces 
     group by host_name,target_name 
     order by 1,2;

How to Write Errors into SQL Server Log

The following T-SQL writes errors into SQL log when there are databases have not been backed up in last 12 hours:

IF EXISTS (
    SELECT bs.database_name,  MAX(bs.backup_finish_date) AS LatestDatabaseBackupDate
    FROM  msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
    INNER JOIN master.sys.databases d ON d.name = bs.database_name
    WHERE bs.type = 'D' and d.name <> 'tempdb' and d.state = 0 and d.source_database_id is null
    GROUP BY bs.database_name HAVING DATEDIFF(HH, MAX(bs.backup_finish_date), GETDATE()) > 12
) RAISERROR(N'On TESTSERVER\TESTINST one or more databases have not been backed up in last 12 hours, please check..', 17, 1) with log

Check the log as below:

ALso we can check spid 55 details:

select * from sys.sysprocesses where spid =55;

How to remotely kill a Remote Desktop Session

 Check all Current RDP Sessions

C:\>qwinsta /server:SERVERNAME

 SESSIONNAME       USERNAME                 ID  STATE   TYPE        DEVICE
 console                                     0  Conn    wdcon
 rdp-tcp                                 65536  Listen  rdpwd
 rdp-tcp#1         orace                     1  Active  rdpwd

Kill a Remote Desktop Session

C:\>rwinsta /server:SERVERNAME 1

Here “1” is the ID from previous checking of RDP sessions.

The killed connection will get the following message: