RDS Oracle Switchover Activity

 

ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.

 

RPM required to enable X11 display on Linux
---------------------------------------------

yum install  xorg-x11-server-Xorg xorg-x11-xauth xorg-x11-apps -y

Enabling SSH login as root user on Red Hat Enterprise Linux 9 - AWS EC2

Enabling SSH login as root user on Red Hat Enterprise Linux 9 (AWS EC2)

login as root user 

cd /etc/ssh/sshd_config.d

[root@ip-110.1.1.1.1 sshd_config.d]# ls -lrt

total 8

-rw-------. 1 root root 719 Jan  8 12:31 50-redhat.conf

-rw-------. 1 root root  27 Mar 14 16:19 50-cloud-init.conf

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]# vi 50-cloud-init.conf

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]# systemctl status sshd

● sshd.service - OpenSSH server daemon

     Loaded: loaded (/usr/lib/systemd/system/sshd.service; enabled; preset: enabled)

     Active: active (running) since Thu 2024-03-14 16:17:41 UTC; 1min 49s ago

       Docs: man:sshd(8)

             man:sshd_config(5)

   Main PID: 1301 (sshd)

      Tasks: 1 (limit: 50306)

     Memory: 1.4M

        CPU: 39ms

     CGroup: /system.slice/sshd.service

             └─1301 "sshd: /usr/sbin/sshd -D [listener] 0 of 10-100 startups"

Mar 14 16:17:41 ip-110.1.1.1.1.ec2.internal systemd[1]: Starting OpenSSH server daemon...

Mar 14 16:17:41 ip-110.1.1.1.1.ec2.internal sshd[1301]: Server listening on 0.0.0.0 port 22.

Mar 14 16:17:41 ip-110.1.1.1.1.ec2.internal sshd[1301]: Server listening on :: port 22.

Mar 14 16:17:41 ip-110.1.1.1.1.ec2.internal systemd[1]: Started OpenSSH server daemon.

Mar 14 16:17:51 ip-110.1.1.1.1.ec2.internal sshd[1302]: error: Received disconnect from 150.242.66.235 port 9857:14: No supp>

Mar 14 16:17:51 ip-110.1.1.1.1.ec2.internal sshd[1302]: Disconnected from authenticating user oracle 150.242.66.235 port 985>

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]# systemctl stop sshd

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]# systemctl status sshd

○ sshd.service - OpenSSH server daemon

     Loaded: loaded (/usr/lib/systemd/system/sshd.service; enabled; preset: enabled)

     Active: inactive (dead) since Thu 2024-03-14 16:19:45 UTC; 2s ago

   Duration: 2min 3.674s

       Docs: man:sshd(8)

             man:sshd_config(5)

    Process: 1301 ExecStart=/usr/sbin/sshd -D $OPTIONS (code=exited, status=0/SUCCESS)

   Main PID: 1301 (code=exited, status=0/SUCCESS)

        CPU: 41ms

Mar 14 16:17:41 ip-110.1.1.1.1.ec2.internal systemd[1]: Starting OpenSSH server daemon...

Mar 14 16:17:41 ip-110.1.1.1.1.ec2.internal sshd[1301]: Server listening on 0.0.0.0 port 22.

Mar 14 16:17:41 ip-110.1.1.1.1.ec2.internal sshd[1301]: Server listening on :: port 22.

Mar 14 16:17:41 ip-110.1.1.1.1.ec2.internal systemd[1]: Started OpenSSH server daemon.

Mar 14 16:17:51 ip-110.1.1.1.1.ec2.internal sshd[1302]: error: Received disconnect from 150.242.66.235 port 9857:14: No supp>

Mar 14 16:17:51 ip-110.1.1.1.1.ec2.internal sshd[1302]: Disconnected from authenticating user oracle 150.242.66.235 port 985>

Mar 14 16:19:45 ip-110.1.1.1.1.ec2.internal sshd[1301]: Received signal 15; terminating.

Mar 14 16:19:45 ip-110.1.1.1.1.ec2.internal systemd[1]: Stopping OpenSSH server daemon...

Mar 14 16:19:45 ip-110.1.1.1.1.ec2.internal systemd[1]: sshd.service: Deactivated successfully.

Mar 14 16:19:45 ip-110.1.1.1.1.ec2.internal systemd[1]: Stopped OpenSSH server daemon.

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]# systemctl start sshd

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]# systemctl status sshd

● sshd.service - OpenSSH server daemon

     Loaded: loaded (/usr/lib/systemd/system/sshd.service; enabled; preset: enabled)

     Active: active (running) since Thu 2024-03-14 16:19:58 UTC; 3s ago

       Docs: man:sshd(8)

             man:sshd_config(5)

   Main PID: 1327 (sshd)

      Tasks: 1 (limit: 50306)

     Memory: 1.3M

        CPU: 11ms

     CGroup: /system.slice/sshd.service

             └─1327 "sshd: /usr/sbin/sshd -D [listener] 0 of 10-100 startups"

Mar 14 16:19:58 ip-110.1.1.1.1.ec2.internal systemd[1]: Starting OpenSSH server daemon...

Mar 14 16:19:58 ip-110.1.1.1.1.ec2.internal sshd[1327]: Server listening on 0.0.0.0 port 22.

Mar 14 16:19:58 ip-110.1.1.1.1.ec2.internal sshd[1327]: Server listening on :: port 22.

Mar 14 16:19:58 ip-110.1.1.1.1.ec2.internal systemd[1]: Started OpenSSH server daemon.

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

[root@ip-110.1.1.1.1 sshd_config.d]#

Oracle SHMMAX and SHMALL parameters

SHMMAX and SHMALL parameters

QUESTION 1

===========

What is the maximum value of SHMMAX for a 32-bit (x86) Linux system?

Oracle Global Customer Support officially recommends a " maximum" for SHMMAX of just less than 4Gb, or 4294967295.

The maximum size of a shared memory segment is limited by the size of the available user address space. On 32-bit systems, this is a theoretical 4GB. The maximum possible value for SHMMAX is just less than 4Gb, or 4294967295. Setting SHMMAX to 4GB exactly will give you 0 bytes as max, as this value is interpreted as a 32-bit number and it wraps around.

QUESTION 2

===========

What is the maximum value of SHMMAX for a 64-bit (x86-64) Linux system?

Oracle Global Customer Support officially recommends a " maximum" for SHMMAX of "1/2 of physical RAM".

System Parameters

SHMMAX - kernel parameter controlling maximum size of one shared memory segment

SHMMNI - kernel parameter controlling maximum number of shared memory segments in the system

SHMSEG - kernel parameter controlling maximum number of shared memory segments a process can attach

SEMMNS - kernel parameter controlling maximum number of semaphores in the system

SEMMNI - kernel parameter controlling maximum number of semaphore sets. Semaphores in Unix are allocated in sets of 1 to SEMMSL.

SEMMSL - kernel parameter controlling maximum number of semaphores in a semaphore set.

SHMLBA - kernel parameter controlling alignment of shared memory segments; all segments must be attached at multiples of this value.

Typically, non-tunable.

SHMMAX is the maximum size of a single shared memory segment set in "bytes"

[root@ip-10.1.1.1.1 ~]# cat /proc/sys/kernel/shmmax

18446744073692774399

[root@ip-10.1.1.1.1 ~]#

SHMALL is the total size of Shared Memory Segments System wide set in "pages".

[root@ip-10.1.1.1.1 ~]#  cat /proc/sys/kernel/shmall

18446744073692774399

[root@ip-10.1.1.1.1 ~]#

The key thing to note here is the value of SHMMAX is set in “bytes” but the value of SHMMALL is set in “pages”.

As SHMALL is the total size of Shard Memory Segments System wide, it should always be less than the Physical Memory on the System and should be greater than sum of SGA’s of all the oracle databases on the server. Once this value (sum of SGA’s) hit the limit, i.e. the value of shmall, then any attempt to start a new database (or even an existing database with a resized SGA) will result in an “out of memory” error (below). This is because there won’t be any more shared memory segments that Linux can allocate for SGA.

ORA-27102: out of memory

Linux-x86_64 Error: 28: No space left on device.

Setting the value for SHMALL to optimal is straight forward. All you want to know is how much “Physical Memory” (excluding Cache/Swap) you have on the system and how much of it should be set aside for Linux Kernel and to be dedicated to Oracle Databases

example 

[root@ip-10.1.1.1.1 ~]# free -g

               total        used        free      shared  buff/cache   available

Mem:               7           0           5           0           1           7

Swap:              0           0           0

[root@ip-10.1.1.1.1 ~]#

 Let say the Physical Memory of a system is 7GB, out of which you want to set aside 2GB for Linux Kernel for OS Operations and dedicate the rest of 5GB to Oracle Databases. Then here’s how you will get the value for SHMALL.

 

[root@ip-10.1.1.1.1 ~]# getconf PAGE_SIZE

4096

[root@ip-10.1.1.1.1 ~]#

or

[root@ip-10.1.1.1.1 ~]# cat /proc/sys/kernel/shmmni

4096

[root@ip-10.1.1.1.1 ~]#

Convert 5GB into bytes and divide by page size

 5gb = 5*1024*1024*1024 =  5,368,709,120 bytes /4096

 

 = 1310720

 

[root@ip-10.1.1.1.1 ~]# ipcs -lm

------ Shared Memory Limits --------

max number of segments = 4096

max seg size (kbytes) = 18014398509465599

max total shared memory (kbytes) = 18446744073709551612

min seg size (bytes) = 1

What’s the optimal value for SHMMAX?

Oracle makes use of one of the 3 memory management models to create the SGA during database startup and it does this in following sequence. First Oracle attempts to use the one-segment model and if this fails, it proceeds with the next one which’s the contiguous multi-segment model and if that fails too, it goes with the last option which is the non-contiguous multi-segment model.

So let’s say if you know the max size of SGA of any database on the server stays below 2GB, you can set shmmax to 2 GB. But say if you have SGA sizes for different databases spread between 512MB to 5GB, then set shmmax to 5Gigs and so on.

Half the size of physical memory in bytes

SHMMNI: parameter recommended as 4096.

SHMALL: parameter recommended as %40 size of physical memory in pages.

Display Kernel Parameter Values for Red-hat Linux (RHEL 9)

 

Command to see Kernel Parameter Values 

Parameter	Command
semmsl, semmns, semopm, and semmni	# /sbin/sysctl -a | grep sem This command displays the value of the semaphore parameters in the order listed.
shmall, shmmax, and shmmni	# /sbin/sysctl -a | grep shm This command displays the details of the shared memory segment sizes.
file-max	# /sbin/sysctl -a | grep file-max This command displays the maximum number of file handles.
ip_local_port_range	# /sbin/sysctl -a | grep ip_local_port_range This command displays a range of port numbers.
rmem_default	# /sbin/sysctl -a | grep rmem_default
rmem_max	# /sbin/sysctl -a | grep rmem_max
wmem_default	# /sbin/sysctl -a | grep wmem_default
wmem_max	# /sbin/sysctl -a | grep wmem_max
aio-max-nr	# /sbin/sysctl -a | grep aio-max-nr

Minimum Kernel Parameter Settings for Oracle Database 19c Installation - Redhat Linux

 

For Oracle Database kernel parameter and shell limit values

Parameter	Value	File
semmsl semmns semopm semmni	250 32000 100 128	/proc/sys/kernel/sem
shmall	Greater than or equal to the value of shmmax, in pages.	/proc/sys/kernel/shmall
shmmax	Half the size of physical memory in bytes See My Oracle Support Note 567506.1 for additional information about configuring shmmax.	/proc/sys/kernel/shmmax
shmmni	4096	/proc/sys/kernel/shmmni
panic_on_oops	1	/proc/sys/kernel/panic_on_oops
file-max	6815744	/proc/sys/fs/file-max
aio-max-nr	1048576 Note: This value limits concurrent outstanding requests and should be set to avoid I/O subsystem failures.	/proc/sys/fs/aio-max-nr
ip_local_port_range	Minimum: 9000 Maximum: 65500	/proc/sys/net/ipv4/ip_local_port_range
rmem_default	262144	/proc/sys/net/core/rmem_default
rmem_max	4194304	/proc/sys/net/core/rmem_max
wmem_default	262144	/proc/sys/net/core/wmem_default
wmem_max	1048576	/proc/sys/net/core/wmem_max

How to Initiate the AWS Oracle RDS Data Guard switchover using the AWS CLI

Switch primary to read replica (standby ) and vice-versa

AWS CLI :

aws rds switchover-read-replica --db-instance-identifier ora-orcl-demo2 --region us-west-2

aws rds switchover-read-replica --db-instance-identifier ora-orcl-demo1  --region us-east-1

aws rds describe-db-instances --db-instance-identifier ora-orcl-demo1 

AWS Link 

https://aws.amazon.com/blogs/database/managed-oracle-data-guard-switchover-with-amazon-rds-for-oracle/

AWS DMS Mapping Table (MSSQL) : Loading Table data using boundaries

 MSSQL query boundaries conditions  

SELECT Max(Id)  nt

FROM (SELECT Id,(Ntile(160) over( ORDER BY Id)) nt FROM demo)

test

GROUP BY nt

ORDER BY nt

option (maxDOP 1024)

Oracle Sql developer error: could not install some modules

 Oracle SQL Developer Error  : 

Solutions :

Go to command prompt

C:\Users\akpandey\AppData\Roaming\SQL Developer\system22.2.1.234.1810\system_cache\config\Modules

• delete all files here 
• restart the SQLDeveloper .

This solution work in my case all connection all there . 

DMS mapping JSON ( one schema (DB1) to other schema (DB2)) - AWS

 DMS mapping task JSON - AWS 

{

    "rules": [

        {

            "rule-type": "transformation",

            "rule-id": "828748134",

            "rule-name": "828748134",

            "rule-target": "schema",

            "object-locator": {

                "schema-name": "dbo"

            },

            "parallel-load": null,

            "rule-action": "rename",

            "value": "pra_mns_pss",

            "old-value": null

        },

        {

            "rule-type": "transformation",

            "rule-id": "828155163",

            "rule-name": "828155163",

            "rule-target": "table",

            "object-locator": {

                "schema-name": "dbo",

                "table-name": "demo2"

            },

            "parallel-load": null,

            "rule-action": "rename",

            "value": "demo2",

            "old-value": null

        },

        {

            "rule-type": "selection",

            "rule-id": "828120150",

            "rule-name": "828120150",

            "object-locator": {

                "schema-name": "dbo",

                "table-name": "demo2"

            },

            "rule-action": "include",

            "filters": [],

            "parallel-load": null,

            "isAutoSegmentationChecked": false

        },

        {

            "rule-type": "selection",

            "rule-id": "820918688",

            "rule-name": "820918688",

            "object-locator": {

                "schema-name": "dbo",

                "table-name": "demo1"

            },

            "rule-action": "include",

            "filters": [],

            "parallel-load": null,

            "isAutoSegmentationChecked": false

        },

        {

            "rule-type": "transformation",

            "rule-id": "1",

            "rule-name": "1",

            "rule-target": "table",

            "object-locator": {

                "schema-name": "dbo",

                "table-name": "demo1"

            },

            "rule-action": "rename",

            "value": "demo1",

            "old-value": null

        }

    ]

}

How to check all node are serving in RAC - 4 node cluster

 

Query to check , all node are serving connection in RAC

Query-1

select inst_id,count(*)
from
   gv$session
where
   username is not null
group by
   inst_id;

Query-2

select
   inst_id,
   count(*)
from
   gv$session
where
   status='ACTIVE' and username is not null
group by
   inst_id;

How to Terminates or kill a session in AWS Oracle RDS

Terminates or kill a session in AWS Oracle RDS

 

BEGIN

    rdsadmin.rdsadmin_util.kill(

        sid    => sid, 

        serial => serial_number,

        method => 'IMMEDIATE');

END;

/

BEGIN

    rdsadmin.rdsadmin_util.kill(

        sid    => 1231, 

        serial => 54321,

        method => 'IMMEDIATE');

END;

/

AWS DMS Task Parameters to Improve Performance

DMS Tasks Parameters to Improve Performance  

{

    "Logging": {

        "EnableLogging": true,

        "EnableLogContext": false,

        "LogComponents": [

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "TRANSFORMATION"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "SOURCE_UNLOAD"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "IO"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "TARGET_LOAD"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "PERFORMANCE"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "SOURCE_CAPTURE"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "SORTER"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "REST_SERVER"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "VALIDATOR_EXT"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "TARGET_APPLY"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "TASK_MANAGER"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "TABLES_MANAGER"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "METADATA_MANAGER"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "FILE_FACTORY"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "COMMON"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "ADDONS"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "DATA_STRUCTURE"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "COMMUNICATION"

            },

            {

                "Severity": "LOGGER_SEVERITY_DEFAULT",

                "Id": "FILE_TRANSFER"

            }

        ],

        "CloudWatchLogGroup": "dms-tasks-dms-repl-demo-hr-inst-prd-1",

        "CloudWatchLogStream": "dms-task-IJSGJUCFA37KTMQLEH46GTN3SYH4I35DRTOGBWI"

    },

    "StreamBufferSettings": {

        "StreamBufferCount": 12,

        "CtrlStreamBufferSizeInMB": 8,

        "StreamBufferSizeInMB": 128

    },

    "ErrorBehavior": {

        "FailOnNoTablesCaptured": true,

        "ApplyErrorUpdatePolicy": "LOG_ERROR",

        "FailOnTransactionConsistencyBreached": false,

        "RecoverableErrorThrottlingMax": 1800,

        "DataErrorEscalationPolicy": "SUSPEND_TABLE",

        "ApplyErrorEscalationCount": 0,

        "RecoverableErrorStopRetryAfterThrottlingMax": true,

        "RecoverableErrorThrottling": true,

        "ApplyErrorFailOnTruncationDdl": false,

        "DataTruncationErrorPolicy": "LOG_ERROR",

        "ApplyErrorInsertPolicy": "LOG_ERROR",

        "EventErrorPolicy": "IGNORE",

        "ApplyErrorEscalationPolicy": "LOG_ERROR",

        "RecoverableErrorCount": 5,

        "DataErrorEscalationCount": 0,

        "TableErrorEscalationPolicy": "STOP_TASK",

        "RecoverableErrorInterval": 5,

        "ApplyErrorDeletePolicy": "IGNORE_RECORD",

        "TableErrorEscalationCount": 0,

        "FullLoadIgnoreConflicts": true,

        "DataErrorPolicy": "LOG_ERROR",

        "TableErrorPolicy": "SUSPEND_TABLE"

    },

    "TTSettings": {

        "TTS3Settings": null,

        "TTRecordSettings": null,

        "EnableTT": false

    },

    "FullLoadSettings": {

        "CommitRate": 50000,

        "StopTaskCachedChangesApplied": false,

        "StopTaskCachedChangesNotApplied": false,

        "MaxFullLoadSubTasks": 49,

        "TransactionConsistencyTimeout": 600,

        "CreatePkAfterFullLoad": false,

        "TargetTablePrepMode": "DO_NOTHING"

    },

    "TargetMetadata": {

        "ParallelApplyBufferSize": 0,

        "ParallelApplyQueuesPerThread": 0,

        "ParallelApplyThreads": 0,

        "TargetSchema": "HR",

        "InlineLobMaxSize": 0,

        "ParallelLoadQueuesPerThread": 0,

        "SupportLobs": true,

        "LobChunkSize": 0,

        "TaskRecoveryTableEnabled": false,

        "ParallelLoadThreads": 0,

        "LobMaxSize": 64,

        "BatchApplyEnabled": false,

        "FullLobMode": false,

        "LimitedSizeLobMode": true,

        "LoadMaxFileSize": 0,

        "ParallelLoadBufferSize": 0

    },

    "BeforeImageSettings": null,

    "ControlTablesSettings": {

        "historyTimeslotInMinutes": 5,

        "HistoryTimeslotInMinutes": 5,

        "StatusTableEnabled": false,

        "SuspendedTablesTableEnabled": false,

        "HistoryTableEnabled": false,

        "ControlSchema": "",

        "FullLoadExceptionTableEnabled": false

    },

    "LoopbackPreventionSettings": null,

    "CharacterSetSettings": null,

    "FailTaskWhenCleanTaskResourceFailed": false,

    "ChangeProcessingTuning": {

        "StatementCacheSize": 50,

        "CommitTimeout": 1,

        "BatchApplyPreserveTransaction": true,

        "BatchApplyTimeoutMin": 1,

        "BatchSplitSize": 0,

        "BatchApplyTimeoutMax": 30,

        "MinTransactionSize": 1000,

        "MemoryKeepTime": 120,

        "BatchApplyMemoryLimit": 500,

        "MemoryLimitTotal": 2048

    },

    "ChangeProcessingDdlHandlingPolicy": {

        "HandleSourceTableDropped": true,

        "HandleSourceTableTruncated": true,

        "HandleSourceTableAltered": true

    },

    "PostProcessingRules": null

}