Thanks for sharing the BCM activity details.
To proceed further, please raise the self-service task along with the associated change record. This task will be processed by automation and will enable your application team to invoke the workflow independently as needed.
You can find more details and steps in the following link: (link)
If you’d like, feel free to schedule a quick call—I’ll be happy to walk you through the workflow and answer any questions.
Thank you for confirming that the PostgreSQL database is hosted in Switzerland.
I wanted to clarify the context of the Business Continuity Management (BCM) exercise:
The scheduled BCM activation applies to Singapore operations only.
Could you please help clarify why the Switzerland‑hosted PostgreSQL database has been included in scope? Specifically:
Is there a cross‑border dependency where Swiss components are identified as part of critical business services supporting Singapore?
Does the application’s end‑to‑end dependency map include this foreign database as essential for Singapore continuity?
The Monetary Authority of Singapore’s BCM Guidelines emphasise that critical business services must include identification of all dependencies—including those in other jurisdictions
I’m writing regarding the BCM (Business Continuity Management) activity scheduled in Singapore. The application user has included a PostgreSQL database that is currently hosted in Switzerland as part of the continuity plan.
To properly coordinate this, could you please clarify:
Is this an application‑level failover only, where traffic shifts to a standby application instance automatically, without any DBA intervention on the database?
Or is it a full PostgreSQL switchover (planned role reversal between primary and standby), which would require tasks to be performed by a DBA—such as promotion, demotion, WAL sync checks, or repmgr/dbvisit orchestration?
If it is indeed a switchover, could you confirm whether any DBA involvement is required during the Singapore BCM window?
Understanding this will help us confirm the scope, assign the right resources, and ensure our runbook covers the necessary steps.
Thanks in advance, and please let me know if you need any additional details from our end.
Best regards,
[Your Name]
[Your Role/Team]
[Contact Information]
Switchover is a planned, zero‑data‑loss process where standby becomes primary and primary becomes standby; typically involves role promotion, WAL sync, and shutdown/start commands docs.oracle.com+1sg.jobstreet.com+1jobleads.com+3severalnines.com+3support.dbvisit.com+3support.dbvisit.com.
As checked and as discussed, the mapping does not exist correctly, which is likely causing the authentication issue.
Upon review, it appears that the authorization, subsystem, and operational environment values are not aligned or configured correctly.
Request you to review and correct the mapping details accordingly. Once updated, feel free to loop me back in for verification or further assistance.
Received a great feedback from one of the SREs — quite a rare scenario but definitely worth considering for our automation logic.
🧩 Scenario:
During the switchover, the system initially shows an error message indicating failure (see red error in the image below).
However, upon inspection, the site actually appears to have successfully switched over (see highlighted portion in the image).
📌 Next Steps / Action Items:
🔍 I’ll be reviewing this case thoroughly to confirm if the site is indeed healthy and safe for handover despite the error.
🤖 Once confirmed, we’ll look to enhance our BCM automation logic to handle this edge case intelligently.
📷 [Attach the screenshot with error and highlight]
Will keep you all posted once the analysis is complete.
n a production environment configured with Patroni, a clean switchover (from primary to a healthy replica) typically takes:
🔹 5 to 15 seconds under normal conditions
🔹 May vary slightly depending on:
Replication lag (ideally should be 0 or minimal)
Size of WALs to be applied
Hardware and network latency
Application connectivity retries
If you're finding the default Atlas view a bit difficult to navigate, you can use this alternate List View which closely resembles GSNOW:
🔹 This view allows for:
Easier navigation
Opening tickets in a new browser tab, not within the same tab like default Atlas.
A layout more familiar to GSNOW users.
🛠️ Tip: You can also create custom lists and add them to your Favourites for quicker access.
📸 See image below for how to do it:
[Attach image here showing how to create a custom list and mark it as a favourite. Maybe highlight the steps or use arrows for clarity]
Let me know if you need help customizing your view!
Sure! Here’s a polished and concise email version you can send:
Subject: BCM Tomorrow – Task Flow & Next Steps
Hi all,
Thanks for raising the automated RITM requests ahead of tomorrow’s BCM.
✅ It’s perfectly fine to get the two approvals done today — that will generate the precheck task for today, which runs transparently in the background.
🔔 Important:
Let’s ensure that only the highlighted task (see image below) is closed tomorrow, once we’re ready to proceed with the failover (BCP activation).
👉 Closure of that task is the actual trigger point.
Appreciate everyone’s support and coordination 🙂
Thanks,
[Your Name]
Let me know if you'd like a Teams message version too.
function processLines(input, e_status) {
e_status = e_status - 1;
var lines = input.split('\n').filter(line => line.trim() !== "");
for (var i = 0; i < lines.length; i++) {
var line = lines[i].trim();
var lagValue = parseFloat(line);
if (isNaN(lagValue)) {
return `Invalid lag value detected: "${line}"`;
}
if (lagValue > 0) {
return `Cluster has lag (${lagValue}MB). Exiting...`;
}
}
return "Cluster is in sync, proceeding";
}
function processLines(input, e_status) {
e_status = e_status - 1;
var lines = input.split('\n').filter(line => line.trim() !== "");
var threshold = 1.0; // Max acceptable lag in MB
for (var i = 0; i < lines.length; i++) {
var line = lines[i].trim();
var lagValue = parseFloat(line);
if (isNaN(lagValue)) {
return `Invalid lag value detected: "${line}"`;
}
if (lagValue > threshold) {
return `Cluster has lag (${lagValue}MB). Exiting...`;
}
}
return "Cluster is in sync, proceeding";
}
structurally duplicate
Same datatype but different column names.
WITH table_defs AS (
SELECT
c.owner,
c.table_name,
LISTAGG(UPPER(c.data_type), ',')
WITHIN GROUP (ORDER BY c.column_id) AS type_signature
FROM all_tab_columns c
WHERE c.owner NOT IN (
'SYS', 'SYSTEM', 'XDB', 'OUTLN', 'MDSYS', 'ORDDATA', 'ORDSYS',
'CTXSYS', 'DBSNMP', 'APPQOSSYS', 'AUDSYS', 'DVSYS',
'GSMADMIN_INTERNAL', 'ANONYMOUS', 'WMSYS', 'OLAPSYS',
'LBACSYS', 'SI_INFORMTN_SCHEMA'
)
GROUP BY c.owner, c.table_name
),
table_sizes AS (
SELECT
owner,
segment_name AS table_name,
ROUND(SUM(bytes)/1024/1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
GROUP BY owner, segment_name
),
duplicates AS (
SELECT type_signature
FROM table_defs
GROUP BY type_signature
HAVING COUNT(*) > 1
)
SELECT
d.type_signature,
d.owner,
d.table_name,
NVL(s.size_mb, 0) AS size_mb
FROM table_defs d
JOIN duplicates dup
ON d.type_signature = dup.type_signature
LEFT JOIN table_sizes s
ON d.owner = s.owner AND d.table_name = s.table_name
ORDER BY d.type_signature, d.owner, d.table_name;
WITH table_defs AS (
SELECT
c.owner,
c.table_name,
LISTAGG(c.column_name || ':' || c.data_type || ':' || c.data_length, ',')
WITHIN GROUP (ORDER BY c.column_id) AS col_signature
FROM all_tab_columns c
WHERE c.owner NOT IN (
'SYS', 'SYSTEM', 'XDB', 'OUTLN', 'MDSYS', 'ORDDATA', 'ORDSYS',
'CTXSYS', 'DBSNMP', 'APPQOSSYS', 'AUDSYS', 'DVSYS',
'GSMADMIN_INTERNAL', 'ANONYMOUS', 'WMSYS', 'OLAPSYS',
'LBACSYS', 'SI_INFORMTN_SCHEMA'
)
GROUP BY c.owner, c.table_name
),
table_sizes AS (
SELECT
owner,
segment_name AS table_name,
ROUND(SUM(bytes)/1024/1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
GROUP BY owner, segment_name
),
duplicates AS (
SELECT col_signature
FROM table_defs
GROUP BY col_signature
HAVING COUNT(*) > 1
)
SELECT
d.col_signature,
d.owner,
d.table_name,
NVL(s.size_mb, 0) AS size_mb
FROM table_defs d
JOIN duplicates dup
ON d.col_signature = dup.col_signature
LEFT JOIN table_sizes s
ON d.owner = s.owner AND d.table_name = s.table_name
ORDER BY d.col_signature, d.owner, d.table_name;
WITH table_defs AS (
SELECT
c.owner,
c.table_name,
LISTAGG(c.column_name || ':' || c.data_type || ':' || c.data_length, ',')
WITHIN GROUP (ORDER BY c.column_id) AS col_signature
FROM all_tab_columns c
WHERE c.owner NOT IN (
'SYS', 'SYSTEM', 'XDB', 'OUTLN', 'MDSYS', 'ORDDATA', 'ORDSYS',
'CTXSYS', 'DBSNMP', 'APPQOSSYS', 'AUDSYS', 'DVSYS',
'GSMADMIN_INTERNAL', 'ANONYMOUS', 'WMSYS', 'OLAPSYS',
'LBACSYS', 'SI_INFORMTN_SCHEMA'
)
GROUP BY c.owner, c.table_name
),
table_sizes AS (
SELECT
owner,
segment_name AS table_name,
ROUND(SUM(bytes)/1024/1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
GROUP BY owner, segment_name
)
SELECT
d.col_signature,
COUNT(*) AS table_count,
LISTAGG(d.owner || '.' || d.table_name || ' (' || NVL(s.size_mb, 0) || ' MB)', ', ')
WITHIN GROUP (ORDER BY d.owner, d.table_name) AS tables_with_size
FROM table_defs d
LEFT JOIN table_sizes s
ON d.owner = s.owner AND d.table_name = s.table_name
GROUP BY d.col_signature
HAVING COUNT(*) > 1
ORDER BY table_count DESC;
SELECT
t.owner,
t.table_name,
s.tablespace_name,
ROUND(s.bytes / 1024 / 1024, 2) AS size_mb,
t.num_rows,
t.last_analyzed,
o.last_ddl_time AS last_modified
FROM dba_tables t
JOIN dba_segments s
ON t.owner = s.owner
AND t.table_name = s.segment_name
AND s.segment_type = 'TABLE'
JOIN dba_objects o
ON t.owner = o.owner
AND t.table_name = o.object_name
AND o.object_type = 'TABLE'
WHERE t.owner NOT IN (
'SYS', 'SYSTEM', 'XDB', 'OUTLN', 'MDSYS', 'ORDDATA', 'ORDSYS',
'CTXSYS', 'DBSNMP', 'APPQOSSYS', 'AUDSYS', 'DVSYS',
'GSMADMIN_INTERNAL', 'ANONYMOUS', 'WMSYS', 'OLAPSYS',
'LBACSYS', 'SI_INFORMTN_SCHEMA'
)
AND REGEXP_LIKE(t.table_name,
'(\d+$|' ||
'(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)|' ||
'(JANUARY|FEBRUARY|MARCH|APRIL|JUNE|JULY|AUGUST|SEPTEMBER|OCTOBER|NOVEMBER|DECEMBER)|' ||
'(20[0-9]{2}[_]?(0[1-9]|1[0-2]))$|' ||
'((0[1-9]|1[0-2])(20[0-9]{2}))$)',
'i'
)
ORDER BY size_mb DESC;
SELECT
t.owner,
t.table_name,
s.tablespace_name,
ROUND(s.bytes / 1024 / 1024, 2) AS size_mb,
t.num_rows,
t.last_analyzed,
o.last_ddl_time AS last_modified
FROM dba_tables t
JOIN dba_segments s
ON t.owner = s.owner
AND t.table_name = s.segment_name
AND s.segment_type = 'TABLE'
JOIN dba_objects o
ON t.owner = o.owner
AND t.table_name = o.object_name
AND o.object_type = 'TABLE'
WHERE t.owner NOT IN (
'SYS', 'SYSTEM', 'XDB', 'OUTLN', 'MDSYS', 'ORDDATA', 'ORDSYS',
'CTXSYS', 'DBSNMP', 'APPQOSSYS', 'AUDSYS', 'DVSYS',
'GSMADMIN_INTERNAL', 'ANONYMOUS', 'WMSYS', 'OLAPSYS',
'LBACSYS', 'SI_INFORMTN_SCHEMA'
)
AND REGEXP_LIKE(t.table_name,
'(\d+$|' ||
'(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)|' ||
'(JANUARY|FEBRUARY|MARCH|APRIL|JUNE|JULY|AUGUST|SEPTEMBER|OCTOBER|NOVEMBER|DECEMBER)|' ||
'(20[0-9]{2}[_]?(0[1-9]|1[0-2])$|' ||
'(0[1-9]|1[0-2])(20[0-9]{2})$)',
'i'
)
ORDER BY size_mb DESC;
SELECT
t.owner,
t.table_name,
s.tablespace_name,
ROUND(s.bytes / 1024 / 1024, 2) AS size_mb,
t.num_rows,
t.last_analyzed,
o.last_ddl_time AS last_modified
FROM dba_tables t
JOIN dba_segments s
ON t.owner = s.owner
AND t.table_name = s.segment_name
AND s.segment_type = 'TABLE'
JOIN dba_objects o
ON t.owner = o.owner
AND t.table_name = o.object_name
AND o.object_type = 'TABLE'
WHERE t.owner = 'YOUR_SCHEMA'
AND REGEXP_LIKE(t.table_name,
'(\d+$|' || -- ends with number
'(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)|' || -- short months
'(JANUARY|FEBRUARY|MARCH|APRIL|JUNE|JULY|AUGUST|SEPTEMBER|OCTOBER|NOVEMBER|DECEMBER)|' ||
'(20[0-9]{2}[_]?(0[1-9]|1[0-2])$|' || -- ends in YYYYMM or YYYY_MM
'(0[1-9]|1[0-2])(20[0-9]{2})$)', 'i')
ORDER BY size_mb DESC;
Hi [Colleague's Name],
As discussed, I’ve gathered the data — please find the attached Excel. Tab #7 includes the heuristic analysis.
It contains an expanded and categorized list of common table name patterns (prefixes/suffixes like %_BAK, TMP_%, etc.) that often indicate duplicates, backups, temporary, or staging tables.
We’ve also included rough size estimates and the last analyzed date (note that size is only an approximation).
Let me know if you need anything refined or explored further.
SELECT
t.owner,
t.table_name,
s.tablespace_name,
ROUND(s.bytes / 1024 / 1024, 2) AS size_mb,
t.num_rows,
t.last_analyzed,
o.last_ddl_time AS last_modified
FROM dba_tables t
JOIN dba_segments s
ON t.owner = s.owner
AND t.table_name = s.segment_name
AND s.segment_type = 'TABLE'
JOIN dba_objects o
ON t.owner = o.owner
AND t.table_name = o.object_name
AND o.object_type = 'TABLE'
WHERE t.owner = 'YOUR_SCHEMA'
AND REGEXP_LIKE(t.table_name,
'(_BAK$|_TMP$|_COPY$|_OLD$|_TEST$|_ARCH$|_STAGE$|_SNAP$|_DUP$|^TMP_|^STG_|^BK_|^OLD_|^NEW_|^TEST_)',
'i')
ORDER BY size_mb DESC;
SELECT
t.owner,
t.table_name,
s.tablespace_name,
ROUND(s.bytes / 1024 / 1024, 2) AS size_mb,
t.num_rows,
t.last_analyzed,
MAX(hs.end_time) AS last_access_time
FROM dba_tables t
JOIN dba_segments s
ON t.owner = s.owner AND t.table_name = s.segment_name AND s.segment_type = 'TABLE'
LEFT JOIN dba_hist_seg_stat_obj hso
ON t.owner = hso.owner AND t.table_name = hso.object_name
LEFT JOIN dba_hist_seg_stat hs
ON hso.obj# = hs.obj#
WHERE t.owner = 'YOUR_SCHEMA'
AND REGEXP_LIKE(t.table_name,
'(_BAK$|_TMP$|_COPY$|_OLD$|_TEST$|_ARCH$|_STAGE$|_SNAP$|_DUP$|^TMP_|^STG_|^BK_|^OLD_|^NEW_|^TEST_)', 'i')
GROUP BY t.owner, t.table_name, s.tablespace_name, s.bytes, t.num_rows, t.last_analyzed
ORDER BY size_mb DESC;
SELECT
t.owner,
t.table_name,
ROUND(t.blocks * 8 / 1024, 2) AS size_mb,
MAX(s.END_TIME) AS last_access_time
FROM dba_tables t
JOIN dba_hist_seg_stat_obj o
ON t.owner = o.owner AND t.table_name = o.object_name
JOIN dba_hist_seg_stat s
ON o.obj# = s.obj#
WHERE t.owner = 'YOUR_SCHEMA'
AND REGEXP_LIKE(t.table_name,
'(_BAK$|_TMP$|_COPY$|_OLD$|_TEST$|_ARCH$|_STAGE$|_SNAP$|_DUP$|^TMP_|^STG_|^BK_|^OLD_|^NEW_|^TEST_)',
'i')
GROUP BY t.owner, t.table_name, t.blocks
ORDER BY last_access_time DESC NULLS LAST;
heuristic analysis
LIKE '%XYZ')| Pattern | Meaning / Use Case |
|---|---|
_BAK | Backup copy of the table |
_BACKUP | Same as above |
_TMP | Temporary version of the table |
_TEMP | Temporary or interim data |
_COPY | Copy of an original table |
_OLD | Old version, before a structural/data change |
_NEW | New version during migration/ETL |
_TEST | Used for testing |
_ARCHIVE | Archived data |
_STAGE | Staging table before transformation |
_HIST | Historical data |
_HISTORY | Extended historical storage |
_SNAP | Snapshot of a live table |
_MIG | Migration purposes |
_EXPORT | Table used for export/dump |
_IMPORT | Table used during import |
_VALIDATION | For data validation during ETL |
_DUP | Duplicate copy |
_ROLLBACK | Used to rollback or undo a change |
LIKE 'XYZ%')
| Pattern | Meaning / Use Case |
|---|---|
TMP_ | Temporary table |
STG_ | Staging table |
BK_ | Backup copy |
OLD_ | Old version |
NEW_ | New version |
ARCH_ | Archived data |
COPY_ | Duplicate |
TEST_ | Testing tables |
SNAP_ | Snapshots |
VAL_ | Validation tables |
DUP_ | Duplicate |
ROLLBACK_ | Used for undo / rollback testing |
T_ | Often used as temp or test table |
Here’s a professional and concise meeting invite you can use:
Subject: Walkthrough: BCM Automation Task for Upcoming BCM Activity
Body:
Dear Application Owners,
As part of the upcoming BCM activity, we have developed an automation workflow to streamline the BCP activation and related tasks.
We would like to walk you through this BCM automation process, highlight key checkpoints, and ensure alignment across teams.
Meeting Details:
📅 Date: [Insert Date]
🕒 Time: [Insert Time]
📍 Location/Link: [Insert MS Teams/Zoom link]
Agenda:
Your participation is important to ensure a smooth execution during the BCM event. Looking forward to your attendance.
Best regards,
[Your Name]
[Your Team/Title]
Let me know if you'd like a version with more technical details or if the invite is going to a broader audience.
Sure! Here's your updated MS Teams message with that phrasing:
Hi Team,
I did a simulation and noticed that Patroni allows a switchover even if the replica is on a different timeline.
Upon checking a few documentations, I learned that timeline mismatch can indicate WAL history divergence, and promoting such a replica may lead to data loss if it's not fully in sync.
📋 Quick pre-switchover checklist:
pg_stat_replication)pg_controldata)Let me know if anyone needs a quick script to validate these before doing a switchover.
Let me know if you'd like to push this to Confluence as well.
Hello Friends! I am aliena part time blogger from Bihar. I am a student and I manage my study as well as my blog. I am 14 years old and I want to help those people who really want to do something on internet.
Copyright © 201. Metro UI Theme. Designed by: Templateism
