ORACLE DATABASE PROBLEM AND SOLUTIONS

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.

Wednesday, December 27, 2023

How to push from local repo to remote ?

push from local repo to remote

PS C:\git-lab\demo1> git remote -v

origin https://github.com/anuragkumarjoy/terraform-code.git (fetch)

origin https://github.com/anuragkumarjoy/terraform-code.git (push)

PS C:\git-lab\demo1>

PS C:\git-lab\demo1>

PS C:\git-lab\demo1> git push origin master

Enumerating objects: 5, done.

Counting objects: 100% (5/5), done.

Writing objects: 100% (3/3), 282 bytes | 282.00 KiB/s, done.

Total 3 (delta 0), reused 0 (delta 0), pack-reused 0

To https://github.com/anuragkumarjoy/terraform-code.git

ae8129e..ea488b5 master -> master

PS C:\git-lab\demo1>

PS C:\git-lab\demo1>

git push -f origin master (also work )

How to add git remote origin ?

Add git remote origin

PS C:\git-lab\demo1> git remote -v

PS C:\git-lab\demo1>

PS C:\git-lab\demo1>

PS C:\git-lab\demo1> git remote add origin https://github.com/anuragkumarjoy/terraform-code.git

PS C:\git-lab\demo1>

PS C:\git-lab\demo1>

PS C:\git-lab\demo1> git remote -v

origin https://github.com/anuragkumarjoy/terraform-code.git (fetch)

origin https://github.com/anuragkumarjoy/terraform-code.git (push)

PS C:\git-lab\demo1>

PS C:\git-lab\demo1>

Subscribe to: Posts (Atom)