SQL Server TDE Database Encryption

This is Data-at-rest encryption. TDE is a feature that encrypts data files to protect them at rest.

Databases to be encrypted: testdb, SAM

1) Create a Master Key

Oracle DB migration - Information collection for Primary server

 OS level info:

Oracle_Home for grid :

Oracle_Home for RDBMS :

Oracle_base :

cat /etc/sysctl.conf |grep -v \# |sort

cat /etc/security/limits.conf  |grep oracle

uname -a

cat /etc/redhat-release

cat /etc/hosts

Files required for DB configuration :

Init.ora, TNSNAMES.ORA, Listener.ora , Sqlnet.ora from both grid and rdbms home.

Patch Details of Oracle grid   "opatch lspatches"

Patch details of Oracle RDBMS  "opatch lspatches"

Yesterday awr report of 10AM to 11AM AND 3pm TO 4pm in html format

init.ora, tnsnames.ora, listener.ora , sqlnet.ora from both grid and rdbms home.

output of "asmcmd lsdg"

output of os command "uname -mrs"

output of df-h of running database server

Database Info:

SET lines 170 NUMWIDTH 12 PAGES 10000 LONG 2000000000

COL version FORMAT a12

COL comp_id FORMAT a8

COL schema LIKE version

COL status FORMAT a12

COL comp_name FORMAT a35

COL value FORMAT a15

COL member FORMAT a100

COL file_name FORMAT a100

COL owner FORMAT a25

COL ddl FORMAT a100

col PLATFORM_NAME format a50;

define fileName=dba_snapshot

COLUMN spool_time NEW_VALUE _spool_time NOPRINT

SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual;

COLUMN dbname NEW_VALUE _dbname NOPRINT

SELECT name dbname FROM v$database;

spool &FileName._&_dbname._&_spool_time..txt

ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';

select instance_name,status from gv$instance;

SELECT * FROM v$version;

var OHM varchar2(100);

EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;

PRINT OHM

SELECT comp_id,schema,status,version,comp_name  FROM dba_registry ORDER BY 1;

SELECT owner, count(*) FROM dba_objects WHERE owner IN ('CTXSYS', 'OLAPSYS', 'MDSYS', 'DMSYS', 'WKSYS', 'LBACSYS','ORDSYS', 'XDB', 'EXFSYS', 'OWBSYS', 'WMSYS', 'SYSMAN')  OR owner LIKE 'APEX%' and status='INVALID' GROUP BY owner  ORDER BY 1;

SELECT owner, object_type, COUNT(*)  FROM dba_objects WHERE object_type LIKE 'JAVA%' GROUP BY owner, object_type ORDER BY 1,2;

SELECT owner, object_type,status, COUNT(*)  FROM dba_objects GROUP BY owner, object_type,status ORDER BY 1,2;

SELECT * FROM nls_database_parameters  WHERE  parameter LIKE '%SET'  ORDER  BY 1;

SELECT group#,bytes,blocksize,members,status FROM v$log ORDER BY 1;

SELECT group#,bytes/1024/1024,members,status FROM v$log ORDER BY 1;

SELECT * FROM v$logfile  ORDER BY 1,3;

SELECT * FROM v$pwfile_users;

select filename, status, bytes from v$block_change_tracking;

Patch details:

set pagesize 1000

set linesize 300

col comments format a50;

col ACTION_TIME format a60;

select ACTION_TIME,ACTION,VERSION,COMMENTS from registry$history;

select PATCH_ID,ACTION,ACTION_TIME,DESCRIPTION from DBA_REGISTRY_SQLPATCH;

select ENDIAN_FORMAT,PLATFORM_NAME from V$TRANSPORTABLE_PLATFORM where PLATFORM_NAME=(select PLATFORM_NAME from v$database);

select status,instance_name,database_role,open_mode,platform_name,platform_id from v$database,v$instance;

select sum(bytes)/1024/1024/1024 from dba_data_files;

select sum(bytes)/1024/1024/1024 from dba_temp_files;

col value format a100;

col name format a50;

select 'Alter system set '|| name || '=' || value || ' scope=spfile sid=*;'  from v$parameter where ISDEFAULT='FALSE';

select name, value from v$parameter where ISDEFAULT='FALSE';

Redo Logs if RAC:

select thread#, group#,MEMBERS, count(1) from gv$log group by thread#, group#,MEMBERS order by 1,2 ; 

select thread#, group#,MEMBERS, (bytes/1024/1024/1024)as "Size in GB"  from gv$log order by 1,2; 

select thread#,group#,(bytes/1024/1024/1024)"Size in GB" from gv$standby_log order by 1,2;

Redo if Single Instance:

col MEMBER format a50;

select member from v$logfile;

select thread#, group#,MEMBERS, (bytes/1024/1024/1024)"Size in GB" from v$log order by 1,2;

select thread#,group#,(bytes/1024/1024/1024)"Size in GB" from v$standby_log order by 1,2;

Archive location 

select * from v$recovery_area_usage where file_type='ARCHIVED LOG';

select round(sum(blocks*block_size)/1024/1024/1024) as "Archive_per_day_GB" from v$archived_log where first_time>sysdate-7 ;

select * from (select round(sum(blocks*block_size)/1024/1024/1024) "Archive_per_day_GB", to_char(first_time,'dd-mm-yyyy') "Date" from v$archived_log where first_time>sysdate-7 group by to_char(first_time,'dd-mm-yyyy')) order by 2 asc;

Users profile:

col profile format a20;

col RESOURCE_NAME format a40;

col LIMIT format a20;

select distinct profile,RESOURCE_NAME,LIMIT from dba_profiles where profile in (select profile from dba_users where username not in('SCOTT','ORACLE_OCM','OJVMSYS','SYSKM',

'XS\$NULL','GSMCATUSER','MDDATA','SYSBACKUP','DIP','SYSDG','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','GSMUSER','AUDSYS','FLOWS_FILES',

'DVF','MDSYS','ORDSYS','DBSNMP','WMSYS','APEX_040200','APPQOSSYS','GSMADMIN_INTERNAL','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','DVSYS','SI_INFORMTN_SCHEMA',

'OLAPSYS','LBACSYS','OUTLN','SYSTEM','SYS','MGMT_VIEW','SYSMAN','APEX_030200','OWBSYS_AUDIT','EXFSYS','OWBSYS')) order by profile,RESOURCE_NAME;

Select 'alter PROFILE '||  profile || ' limit ' ||  RESOURCE_NAME  ||' '|| limit ||';'  from dba_profiles where profile in (select profile from dba_users where username not in('SCOTT','ORACLE_OCM','OJVMSYS','SYSKM',

'XS\$NULL','GSMCATUSER','MDDATA','SYSBACKUP','DIP','SYSDG','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','GSMUSER','AUDSYS','FLOWS_FILES',

'DVF','MDSYS','ORDSYS','DBSNMP','WMSYS','APEX_040200','APPQOSSYS','GSMADMIN_INTERNAL','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','DVSYS','SI_INFORMTN_SCHEMA',

'OLAPSYS','LBACSYS','OUTLN','SYSTEM','SYS','MGMT_VIEW','SYSMAN','APEX_030200','OWBSYS_AUDIT','EXFSYS','OWBSYS')) order by profile,RESOURCE_NAME;

Tablespace Information:

set linesize 300

select TABLESPACE_NAME, round(sum(bytes)/1024/1024/1024) TBS_GB  from dba_data_files group by TABLESPACE_NAME order by 2;

select TABLESPACE_NAME, round(sum(bytes)/1024/1024) TBS_MB from dba_data_files group by TABLESPACE_NAME order by 2;

select TABLESPACE_NAME, round(sum(bytes)/1024/1024/1024)  from dba_temp_files group by TABLESPACE_NAME order by 2;

select 'CREATE TABLESPACE '||TABLESPACE_NAME||' DATAFILE ''/oradata01/oraclesid/'||lower(TABLESPACE_NAME)||'_01.dbf'''||' SIZE '||(round(sum(Bytes)/1024/1024/1024)+1) ||'G AUTOEXTEND off '|| 'EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M ONLINE PERMANENT;' from dba_data_files  where TABLESPACE_NAME not in('SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS') and TABLESPACE_NAME not in (select TABLESPACE_NAME from dba_temp_files) group by tablespace_name order by TABLESPACE_NAME; 

select 'CREATE TEMPORARY TABLESPACE '||TABLESPACE_NAME||' tempFILE ''/oradata01/oraclesid/'||lower(TABLESPACE_NAME)||'_01.dbf'''||' SIZE '||(round(sum(Bytes)/1024/1024/1024)+1)||'G AUTOEXTEND off EXTENT MANAGEMENT'  || ' LOCAL UNIFORM SIZE 8M ONLINE PERMANENT;' from dba_temp_files  where TABLESPACE_NAME in (select TABLESPACE_NAME from dba_temp_files) group by tablespace_name order by TABLESPACE_NAME; 

select username,profile,account_status  from dba_users where username not in('SCOTT','ORACLE_OCM','OJVMSYS','SYSKM','XS\$NULL','GSMCATUSER','MDDATA','SYSBACKUP','DIP','SYSDG','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','GSMUSER','AUDSYS','FLOWS_FILES','DVF','MDSYS','ORDSYS','DBSNMP','WMSYS','APEX_040200','APPQOSSYS','GSMADMIN_INTERNAL','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','DVSYS','SI_INFORMTN_SCHEMA','OLAPSYS','LBACSYS','OUTLN','SYSTEM','SYS','MGMT_VIEW','SYSMAN','APEX_030200','OWBSYS_AUDIT','EXFSYS','OWBSYS') order by 3;

col PATH format a60;

select name,path,  disk_number disk_#, mount_status,header_status, state, round(total_MB/1024), round(free_MB/1024) from v$asm_disk order by group_number;

select NAME, BLOCK_SIZE, round(TOTAL_MB/1024), round(FREE_MB/1024) ,state,type from  v$ASM_DISKGROUP;

var OHM varchar2(100);

EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;

PRINT OHM

> host df -h

To Determine MongoDB Enterprise or Community version

 To determine if you are using the MongoDB Enterprise or Community (free) version:

Using the MongoDB Shell

1. Check the Version Information:

   • Open your terminal or command prompt.
   • Run the command:

     mongod --version
     

   • Look for the modules section in the output. If it includes enterprise, you are using the MongoDB Enterprise version. If it is empty or not present, you are using the Community version.

2. Check for Enterprise Features:

   • Connect to your MongoDB instance using the MongoDB shell.
   • Run the command:

     db.runCommand({buildInfo: 1})
     

   • Look for the modules field in the output. If it lists enterprise, you are using the Enterprise version.

Using SSL Connection

• If you can connect to your MongoDB instance using SSL, it is likely the Enterprise version, as SSL support is a feature of MongoDB Enterprise.

Checking for Specific Features

• Run the following command to check for SNMP support, which is available only in the Enterprise version:

  mongod -h | grep snmp
  

  If you see output related to SNMP, you are using the Enterprise version

MongoDB useful commands

Installation:

mongod --config "D:\MongoDB\bin\mongod.cfg" --install

net start MongoDB

User control:

db.createUser({user: "Bkp",pwd: passwordPrompt(), roles: [ "readWriteAnyDatabase" ]})

db.revokeRolesFromUser("Bkp", [{role:"readWriteAnyDatabase", db:"admin"}])

db.grantRolesToUser("Bkp",[{role:"backup",db:"admin"}])

db.createUser({user: "BACKUP_USER",pwd: "BKP", roles: [ "backup" ]})

db.dropUser("BACKUP_USER1")

db.createUser({user: "BACKUP_USER1",pwd: "BKP", roles: [ "read" ]})

db.grantRolesToUser("BACKUP_USER1",[{role:"readWriteAnyDatabase"}])

db.auth('webdev', 'xxxxx')

db.runCommand({connectionStatus : 1})

db.runCommand({connectionStatus : 1,showPrivileges:true})

Replicaset

mongod --bind_ip 192.168.56.1 --port 27019 --replSet rs0 --dbpath D:\MongoDB\Replicaset\data1 --oplogSize 200 --logpath D:\MongoDB\Replicaset\log1\log1.txt

mongod --bind_ip 192.168.56.1 --port 27020 --replSet rs0 --dbpath D:\MongoDB\Replicaset\data2 --oplogSize 200 --logpath D:\MongoDB\Replicaset\log2\log2.txt

mongod --bind_ip 192.168.56.1 --port 27021 --replSet rs0 --dbpath D:\MongoDB\Replicaset\data3 --oplogSize 200 --logpath D:\MongoDB\Replicaset\log3\log3.txt

rconfig={

_id:"rs0",

members:[

{_id:0,host:"192.168.56.1:27019"},

{_id:1,host:"192.168.56.1:27020"},

{_id:2,host:"192.168.56.1:27021"}]

}

rconfig

rs.initiate(rconfig)

rs.status()

rs.isMaster()

Setup MongoDB ReplicaSet in Windows Machine

We're using the same machine for setting up the MongoDB ReplicaSet quickly, but with different port numbers.

1) Create these directories as below

mkdir D:\MongoDB\ReplicaSet

2) Start the mongod process for each replica.

mongod --bind_ip 192.168.56.1 --port 27019 --replSet rs0 --dbpath D:\MongoDB\Replicaset\data1 --oplogSize 200 --logpath D:\MongoDB\Replicaset\log1\log1.txt

mongod --bind_ip 192.168.56.1 --port 27020 --replSet rs0 --dbpath D:\MongoDB\Replicaset\data2 --oplogSize 200 --logpath D:\MongoDB\Replicaset\log2\log2.txt

mongod --bind_ip 192.168.56.1 --port 27021 --replSet rs0 --dbpath D:\MongoDB\Replicaset\data3 --oplogSize 200 --logpath D:\MongoDB\Replicaset\log3\log3.txt

--Bind_ip - is used to accept connections on a particular node. By specifying the IP address with --bind-ip, you're telling MongoDB to listen for incoming connections on that specific network interface of the node.

--replSet - to indicate the node is part of the replicaSet, but yet to configure command using rs.initiate after the processes from all the nodes started.

--OplogSize - option is used in the mongod process to specify the size of the oplog (operations log) for a replica set member. The oplog is a special capped collection that keeps a rolling record of all operations that modify the data stored in your databases. 

3) Connect to Replica 1 and set variable:

>mongosh –port 27019

Store the current configuration of ReplicaSet in a variable and check it is set correctly:

4) Execute rs.initiate(rconfig) command to start the replica set configuration process. 

>rs.initiate(rconfig)

5) Check the status of ReplicaSet after initialization

>rs.status()

6) Run db.isMaster() command to see node status

Configured a MongoDB ReplicaSet successfully!

Configure TDE on Oracle 19c Multitenant-Database Server on Windows

 1) Set Parameters

<CDB>

-- Wallet root location: E:\oracle\base\admin\tdedb\wallet

sqlplus / as sysdba

SQL> alter system set WALLET_ROOT="E:\oracle\base\admin\tdedb\wallet" scope=spfile;

System altered.

SQL> alter system set "_tablespace_encryption_default_algorithm" = 'AES256' scope=both;

-- The following parameter setting is required for patch level 19.25 (Oct-24)

 

Failed to restart network.service: Unit network.service not found” in CENT OS 9

Network scripts are deprecated from Cent OS/Red Hat Enterprise Linux 8 and onward, no longer available by default. The new installation provides ifup and ifdown scripts which call "NetworkManager " service and use nmcli interface to control network manager.

Version:  Linux localhost.localdomain 5.14.0-354.el9.x86_64

 

 

So, you can use network manager instead and nmcli interface.

 

 You may use ifdown/ifup commands. If these commands are not working, Please Install package 'NetworkManager-initscripts-updown' and then try again.