How to check Postgres Schema size using psql query ?

 

Check Postgres Schema size using psql query

Query1 :

SELECT     pg_catalog.pg_namespace.nspname,

           pg_size_pretty(SUM(pg_relation_size(pg_catalog.pg_class.oid))::BIGINT)

FROM       pg_catalog.pg_class

           INNER JOIN pg_catalog.pg_namespace

           ON         relnamespace = pg_catalog.pg_namespace.oid

GROUP BY   pg_catalog.pg_namespace.nspname;

Query 2 : 

with SchemaSize as

(

select ps.nspname as schema_name,

sum(pg_relation_size(pc.oid)) as total_size

from pg_class pc

join pg_catalog.pg_namespace ps

on ps.oid = pc.relnamespace

group by ps.nspname

)

select ss.schema_name,

pg_size_pretty(ss.total_size)

from SchemaSize ss

order by ss.total_size desc

limit 20;

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/

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

}

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"

                ]

            }

        }

    ]

}

How to chnage the global name of a database in AWS oracle RDS ?

 

We have to login as admin user 

exceute below commands 

EXEC rdsadmin.rdsadmin_util.rename_global_name(p_new_global_name => 'new_global_name');

AWS Oracle Classic RDS Features & Limitataion

Edition and licensing => License included (SE2) or BYOL (SE2/EE)

Multitenant PDB in CDB (licence & Maint cost reduction)  => 1 PDB

No of Database => 1 DB per RDS instance

Applications require access to underlying OS =>  N/A

Applications require access to underlying DB Env =>  N/A

sysdba privilege access  => N/A

Huge Pages (better Performance) => N/A

Local Undo => N/A

Data Guard Max Availability => with Multi AZ (no access on secondary) 

RDS database Patching =>  AWS Managed

Create a Multi-AZ deployment => Yes

Change CPU cores and threads per core on DB instance class => Yes

Turn on storage autoscaling => Yes

Automatic minor version upgrade => Yes

Kerberos authentication. => Yes

Performance Insights => Yes

TDE Encryption => Yes

Billing can be stopped => Yes

Scaling AWS          => Yes

High availability =>  AWS Managed

Database backups =>  AWS  Managed

Database software install => AWS Managed

OS patching =>  AWS Managed

OS installation => AWS Managed

Storage Limit => 64TB

Amazon RDS Custom for Oracle 19c database with sysdba login

 Amazon RDS Custom for Oracle

Login as sysdba to oracle database:

-bash-4.2$

-bash-4.2$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 26 20:07:44 2023

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL>

SQL>

SQL> select name,open_mode,cdb from v$database;

NAME      OPEN_MODE            CDB

--------- -------------------- ---

DEV       READ WRITE           NO

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC     CON_ID

---------- ------------------------------ --- --- --- --- ----------

         0 SYSTEM                         YES YES YES              0

         1 SYSAUX                         YES YES YES              0

         2 UNDO_T1                        YES YES YES              0

         4 USERS                          YES YES YES              0

         5 RDSADMIN                       YES YES YES              0

         3 TEMP                           NO  YES YES              0

6 rows selected.

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/rdsdbdata/db/DEV_A/datafile/o1_mf_rdsadmin_l71tz3fk_.dbf

/rdsdbdata/db/DEV_A/datafile/o1_mf_users_l71t0bxh_.dbf

/rdsdbdata/db/DEV_A/datafile/o1_mf_undo_t1_l71t0bof_.dbf

/rdsdbdata/db/DEV_A/datafile/o1_mf_sysaux_l71t09xk_.dbf

/rdsdbdata/db/DEV_A/datafile/o1_mf_system_l71t08j1_.dbf

SQL>

How to create CEV manifest file for oracle 19c AWS RDS custom ?

 CEV for Oracle Database 19c

{

 "mediaImportTemplateVersion":"2020-08-14",

 "databaseInstallationFileNames":[

   "V982063-01.zip"

 ],

 "installationParameters":{

   "oracleHome":"/app/oracle/oracle19c",

   "oracleBase":"/app/oracle",

   "unixUid":10011,

   "unixUname":"oracle",

   "unixGroupId":10012,

   "unixGroupName":"dba"

 }

}

AWS Doc Link : https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-cev.preparing.html#oracle-cev-manifest-19c