ORACLE DATABASE PROBLEM AND SOLUTIONS

Wednesday, February 14, 2024

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

}

]

}

Monday, February 5, 2024

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;

Thursday, February 1, 2024

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;

/

Friday, January 19, 2024

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

}

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

Loading Table data using boundaries

Query to get table boundaries

SELECT /*+ parallel(256) */ '["' ||Max(ID)||'"],'

FROM (SELECT ID, Ntile(5) over( ORDER BY ID) nt FROM HR.DEMO)

GROUP BY nt

ORDER BY nt;

Mapping JSON

{

"rules": [

{

"rule-type": "transformation",

"rule-id": "113859112",

"rule-name": "113859112",

"rule-target": "table",

"object-locator": {

"schema-name": "HR",

"table-name": "DEMO"

},

"rule-action": "convert-uppercase",

"value": null,

"old-value": null

},

{

"rule-type": "selection",

"rule-id": "101",

"rule-name": "101",

"object-locator": {

"schema-name": "HR",

"table-name": "DEMO"

},

"rule-action": "include"

},

{

"rule-type": "table-settings",

"rule-id": "103",

"rule-name": "103",

"object-locator": {

"schema-name": "HR",

"table-name": "DEMO"

},

"parallel-load": {

"type": "ranges",

"columns": [

"ID"

],

"boundaries": [

[

"24016177"

],

[

"41650518"

],

[

"56874846"

],

[

"71834843"

],

[

"86760524"

]

]

},

"rule-action": "include",

"filters": []

}

]

}

AWS DMS Table Mapping : Loading data using partition

Loading data using partition

{

"rules": [

{

"rule-type": "transformation",

"rule-id": "482482221",

"rule-name": "482482221",

"rule-target": "table",

"object-locator": {

"schema-name": "HR",

"table-name": "DEMO"

},

"parallel-load": null,

"rule-action": "convert-uppercase",

"value": null,

"old-value": null

},

{

"rule-type": "selection",

"rule-id": "1",

"rule-name": "1",

"object-locator": {

"schema-name": "HR",

"table-name": "DEMO"

},

"rule-action": "include"

},

{

"rule-type": "table-settings",

"rule-id": "2",

"rule-name": "2",

"object-locator": {

"schema-name": "HR",

"table-name": "DEMO"

},

"parallel-load": {

"type": "partitions-list",

"partitions": [

"P20240124",

"P20240123",

"P20240122",

"P20240121",

"P20240120",

"P20240119",

"P20240118",

"P20240117",

"P20240116",

"P20240115",

"P20240114"

]

}

}

]

}

Friday, December 29, 2023

Oracle Critical Patch Updates (CPU's)

Critical Patch Updates

Critical Patch Updates provide security patches for supported Oracle on-premises products. They are available to customers with valid support contracts.

Starting in April 2022, Critical Patch Updates are released on the third Tuesday of January, April, July, and October (They were previously published on the Tuesday closest to the 17th day of January, April, July, and October).

The next four dates are:

16 January 2024
16 April 2024
16 July 2024
15 October 2024

A pre-release announcement will be published on the Thursday preceding each Critical Patch Update release.

Subscribe to: Comments (Atom)