Thursday, September 4, 2025

DMA Part 2

 

Step 1: Create the ts_mappings Table

The table design remains as previously defined, with added comments for clarity.

sql
-- Create a table to store old-to-new tablespace mappings and size metrics for migration
-- Reusable for multiple operations; stored in a custom schema (e.g., ADMIN_SCHEMA)
CREATE TABLE ts_mappings (
old_tablespace VARCHAR2(30) NOT NULL,
new_tablespace VARCHAR2(30) NOT NULL,
allocated_size_mb NUMBER, -- Allocated size of old tablespace (from dba_data_files)
used_size_mb NUMBER, -- Used size of old tablespace (from dba_segments)
estimated_target_size_mb NUMBER, -- Estimated size for new tablespace (with 10% growth buffer)
description VARCHAR2(200), -- Notes on consolidation logic
last_updated DATE DEFAULT SYSDATE,
CONSTRAINT pk_ts_mappings PRIMARY KEY (old_tablespace)
);
-- Index for efficient queries by new_tablespace
CREATE INDEX idx_ts_mappings_new ON ts_mappings (new_tablespace);

Step 2: Populate ts_mappings with Dynamic Mapping

To address the suggestion for dynamic mapping, we’ll use a separate mapping table (ts_mapping_rules) to define the old-to-new relationships, making the logic more flexible and maintainable. This avoids a static CASE statement, which can be unwieldy for 56 tablespaces. The ELSE 'NEW_DATA8' is retained as a catch-all for unmapped tablespaces, ensuring completeness.

Create Mapping Rules Table

sql
-- Create a table to define old-to-new tablespace mapping rules
CREATE TABLE ts_mapping_rules (
old_tablespace VARCHAR2(30) NOT NULL,
new_tablespace VARCHAR2(30) NOT NULL,
CONSTRAINT pk_ts_mapping_rules PRIMARY KEY (old_tablespace)
);
-- Populate mapping rules (example; customize with your 56 tablespaces)
INSERT INTO ts_mapping_rules (old_tablespace, new_tablespace) VALUES ('TS01', 'NEW_DATA1');
INSERT INTO ts_mapping_rules (old_tablespace, new_tablespace) VALUES ('TS02', 'NEW_DATA1');
INSERT INTO ts_mapping_rules (old_tablespace, new_tablespace) VALUES ('TS11', 'NEW_DATA2');
-- Add all 56 tablespaces, mapping to one of NEW_DATA1 to NEW_DATA8
COMMIT;

Validate and Populate ts_mappings

Before populating, check for missing or invalid tablespaces to ensure all source tablespaces exist.

sql
-- Validate: Check for tablespaces in mapping rules not in dba_data_files
SELECT tmr.old_tablespace
FROM ts_mapping_rules tmr
MINUS
SELECT tablespace_name FROM dba_data_files;
-- Validate: Ensure no duplicate mappings
SELECT old_tablespace, COUNT(DISTINCT new_tablespace) AS mapping_count
FROM ts_mapping_rules
GROUP BY old_tablespace
HAVING COUNT(DISTINCT new_tablespace) > 1;
-- Populate ts_mappings with size data, using mapping rules
INSERT INTO ts_mappings (old_tablespace, new_tablespace, allocated_size_mb, used_size_mb, description)
SELECT
ddf.tablespace_name,
NVL(tmr.new_tablespace, 'NEW_DATA8') AS new_tablespace, -- ELSE 'NEW_DATA8' as fallback
SUM(ddf.bytes)/1024/1024 AS allocated_size_mb,
NVL(SUM(ds.bytes)/1024/1024, 0) AS used_size_mb,
'Consolidated via mapping rules' AS description
FROM dba_data_files ddf
LEFT JOIN dba_segments ds ON ds.tablespace_name = ddf.tablespace_name
LEFT JOIN ts_mapping_rules tmr ON tmr.old_tablespace = ddf.tablespace_name
GROUP BY ddf.tablespace_name, NVL(tmr.new_tablespace, 'NEW_DATA8');
COMMIT;
-- Validate: Check for unmapped tablespaces (assigned to NEW_DATA8)
SELECT old_tablespace, new_tablespace
FROM ts_mappings
WHERE new_tablespace = 'NEW_DATA8';
  • Role of ELSE 'NEW_DATA8': The NVL(tmr.new_tablespace, 'NEW_DATA8') acts as the ELSE equivalent, mapping any tablespace not found in ts_mapping_rules to NEW_DATA8. This ensures all tablespaces are assigned, preventing Data Pump errors (e.g., ORA-00959: tablespace does not exist). Review the validation query’s output to confirm only expected tablespaces fall into NEW_DATA8.

Step 3: Estimate Target Sizes with 10% Growth Buffer

Calculate estimated_target_size_mb using allocated_size_mb with a 10% buffer, as requested. A comment clarifies the choice of allocated_size_mb vs. used_size_mb.

sql
-- Calculate estimated size for new tablespaces with 10% growth buffer
-- Uses allocated_size_mb to account for current data and free space for future growth
-- Alternative: Use SUM(used_size_mb) * 1.1 for a leaner estimate based on actual data
UPDATE ts_mappings
SET estimated_target_size_mb = (
SELECT SUM(allocated_size_mb) * 1.1 -- 10% growth buffer
FROM ts_mappings sub
WHERE sub.new_tablespace = ts_mappings.new_tablespace
);
COMMIT;
-- Summarize sizes for RDS planning
SELECT
new_tablespace,
COUNT(old_tablespace) AS old_ts_count,
ROUND(SUM(allocated_size_mb), 2) AS total_allocated_mb,
ROUND(SUM(used_size_mb), 2) AS total_used_mb,
ROUND(MAX(estimated_target_size_mb), 2) AS target_size_mb
FROM ts_mappings
GROUP BY new_tablespace
ORDER BY new_tablespace;


SET SERVEROUTPUT ON; DECLARE v_total_old_ts_count NUMBER; BEGIN -- Get the total count of old tablespaces from the table SELECT COUNT(old_tablespace) INTO v_total_old_ts_count FROM ts_mappings; -- Add the requested line to the output DBMS_OUTPUT.PUT_LINE('Total number of old tablespaces: ' || v_total_old_ts_count); DBMS_OUTPUT.PUT_LINE(''); -- Print a header for the output DBMS_OUTPUT.PUT_LINE('--------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('New Tablespace | Old Tablespaces Count'); DBMS_OUTPUT.PUT_LINE('--------------------------------------------------'); -- Use a FOR loop with the updated query for custom sorting FOR rec IN ( SELECT new_tablespace, COUNT(old_tablespace) AS old_ts_count FROM ts_mappings GROUP BY new_tablespace ORDER BY CASE new_tablespace WHEN 'TS_1' THEN 1 WHEN 'TS_2' THEN 2 WHEN 'TS_3' THEN 3 ELSE 999 -- Assign a high number to non-specified tablespaces END, new_tablespace -- Secondary sort for remaining tablespaces ) LOOP -- Print each row's data with consistent padding DBMS_OUTPUT.PUT_LINE( RPAD(rec.new_tablespace, 20) || ' | ' || rec.old_ts_count ); END LOOP; DBMS_OUTPUT.PUT_LINE('--------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('Script finished successfully.'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END; /


SET SERVEROUTPUT ON;

DECLARE
    v_total_old_ts_count NUMBER;

    -- Define the tablespaces to be excluded from the count
    -- Replace the example names with your actual tablespace names
    v_excluded_tablespaces CONSTANT VARCHAR2(4000) := '''SYSTEM'', ''SYSAUX'', ''UNDOTBS1''';

BEGIN
    -- Get the total count of old tablespaces, excluding the specified ones
    EXECUTE IMMEDIATE '
        SELECT COUNT(old_tablespace)
        FROM ts_mappings
        WHERE old_tablespace NOT IN (' || v_excluded_tablespaces || ')'
    INTO v_total_old_ts_count;

    -- Add the requested line to the output
    DBMS_OUTPUT.PUT_LINE('Total number of old tablespaces (excluding ' || v_excluded_tablespaces || '): ' || v_total_old_ts_count);
    DBMS_OUTPUT.PUT_LINE('');

    -- Print a header for the output
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('New Tablespace       | Old Tablespaces Count');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');

    -- Use a FOR loop with the updated query for filtering and custom sorting
    FOR rec IN (
        SELECT
            new_tablespace,
            COUNT(old_tablespace) AS old_ts_count
        FROM
            ts_mappings
        WHERE
            old_tablespace NOT IN ('SYSTEM', 'SYSAUX', 'UNDOTBS1') -- Customize this list
        GROUP BY
            new_tablespace
        ORDER BY
            CASE new_tablespace
                WHEN 'TS_1' THEN 1
                WHEN 'TS_2' THEN 2
                WHEN 'TS_3' THEN 3
                ELSE 999
            END,
            new_tablespace
    )
    LOOP
        -- Print each row's data with consistent padding
        DBMS_OUTPUT.PUT_LINE(
            RPAD(rec.new_tablespace, 20) || ' | ' || rec.old_ts_count
        );
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Script finished successfully.');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
  • Size Calculation Note: Using allocated_size_mb ensures space for current data and free space in the source tablespaces. If you prefer a leaner estimate (e.g., SUM(used_size_mb) * 1.1), let me know, and I can adjust the script.

Step 4: Create New Tablespaces on RDS

Generate CREATE TABLESPACE statements for the 8 bigfile tablespaces on RDS.

sql
-- Generate CREATE BIGFILE TABLESPACE statements for RDS
SELECT 'CREATE BIGFILE TABLESPACE ' || new_tablespace ||
' DATAFILE SIZE ' || CEIL(MAX(estimated_target_size_mb)) ||
'M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;' AS create_script
FROM ts_mappings
GROUP BY new_tablespace;
  • Execution: Run these on the RDS instance via SQL*Plus or SQL Developer. Bigfile tablespaces align with RDS best practices.

Step 5: Map Users, Indexes, and Other Objects

Enhanced scripts support multiple schemas and include validation for LOBs and partitions.

sql
-- 5.1: Generate ALTER USER statements for default tablespaces and quotas
-- Run on target RDS before import; handles multiple schemas
SELECT 'ALTER USER "' || du.username || '" DEFAULT TABLESPACE ' ||
NVL((SELECT tm.new_tablespace FROM ts_mappings tm WHERE tm.old_tablespace = du.default_tablespace AND ROWNUM = 1), 'NEW_DATA8') ||
' QUOTA UNLIMITED ON ' ||
NVL((SELECT tm.new_tablespace FROM ts_mappings tm WHERE tm.old_tablespace = du.default_tablespace AND ROWNUM = 1), 'NEW_DATA8') || ';'
FROM dba_users du
WHERE du.default_tablespace IN (SELECT old_tablespace FROM ts_mappings)
OR du.default_tablespace NOT IN (SELECT tablespace_name FROM dba_tablespaces); -- Catch invalid defaults
-- 5.2: Generate index rebuild scripts for post-import validation
-- Data Pump remaps indexes; rebuild only if invalid
SELECT 'ALTER INDEX "' || di.owner || '"."' || di.index_name || '" REBUILD TABLESPACE ' ||
NVL((SELECT tm.new_tablespace FROM ts_mappings tm WHERE tm.old_tablespace = di.tablespace_name AND ROWNUM = 1), 'NEW_DATA8') || ';'
FROM dba_indexes di
WHERE di.tablespace_name IN (SELECT old_tablespace FROM ts_mappings)
AND di.status != 'VALID';
-- 5.3: Validate LOBs and partitions post-import
-- LOBs (Data Pump remaps automatically)
SELECT owner, table_name, column_name, tablespace_name
FROM dba_lobs
WHERE tablespace_name IN (SELECT new_tablespace FROM ts_mappings);
-- Partitions (Data Pump remaps automatically)
SELECT table_owner, table_name, partition_name, tablespace_name
FROM dba_tab_partitions
WHERE tablespace_name IN (SELECT new_tablespace FROM ts_mappings);
  • Multi-Schema Support: The scripts use dba_users and dba_indexes to handle all relevant schemas. Filter by specific schemas (e.g., WHERE du.username IN ('SCHEMA1', 'SCHEMA2')) if needed.
  • LOBs/Partitions: Data Pump’s REMAP_TABLESPACE handles remapping; the queries validate placement post-import.

Step 6: Execute the Migration

Incorporate RDS-specific S3 workflow for Data Pump.

bash
-- 6.1: Export from source
expdp system/password@source_db DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_export_%U.dmp LOGFILE=export.log FULL=Y PARALLEL=4
-- 6.2: Upload dump files to S3
aws s3 cp /path/to/dump/files s3://your-rds-s3-bucket/dmp_files/
-- 6.3: Generate REMAP_TABLESPACE clause
SELECT LISTAGG('REMAP_TABLESPACE=' || old_tablespace || ':' || new_tablespace, ',') WITHIN GROUP (ORDER BY old_tablespace) AS remap_clause
FROM ts_mappings;
-- 6.4: Import to RDS
-- Download dump files from S3 to RDS Data Pump directory
BEGIN
rdsadmin.rdsadmin_s3_tasks.download_from_s3(
p_bucket_name => 'your-rds-s3-bucket',
p_s3_prefix => 'dmp_files/',
p_directory_name => 'DATA_PUMP_DIR'
);
END;
/
-- Run impdp on RDS
impdp system/password@target_rds DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_export_%U.dmp LOGFILE=import.log [paste remap_clause] PARALLEL=4
  • Post-Import:
    sql
    -- Validate objects
    SELECT object_type, COUNT(*)
    FROM dba_objects
    WHERE status = 'INVALID'
    GROUP BY object_type;
    -- Gather statistics
    EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => NULL, cascade => TRUE);

Step 7: Reuse for Multiple Operations

Update size metrics and generate scripts for ongoing tasks.

sql
-- Update size metrics for reuse
UPDATE ts_mappings tm
SET allocated_size_mb = (
SELECT SUM(bytes)/1024/1024 FROM dba_data_files WHERE tablespace_name = tm.old_tablespace
),
used_size_mb = (
SELECT NVL(SUM(bytes)/1024/1024, 0) FROM dba_segments WHERE tablespace_name = tm.old_tablespace
),
last_updated = SYSDATE;
COMMIT;
-- Generate manual move scripts (if needed)
SELECT 'ALTER TABLE "' || owner || '"."' || table_name || '" MOVE TABLESPACE ' ||
NVL((SELECT new_tablespace FROM ts_mappings WHERE old_tablespace = dt.tablespace_name AND ROWNUM = 1), 'NEW_DATA8') || ';'
FROM dba_tables dt
WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings);
-- Monitor usage
SELECT new_tablespace, ROUND(SUM(used_size_mb), 2) AS current_used_mb
FROM ts_mappings
GROUP BY new_tablespace;

Response to Suggestions

  1. Input Validation: Added checks for missing tablespaces and duplicate mappings in Step 2.
  2. Dynamic Mapping: Introduced ts_mapping_rules for flexible, maintainable mapping logic. The ELSE 'NEW_DATA8' ensures unmapped tablespaces are caught, with validation to review assignments.
  3. Size Calculation: Clarified use of allocated_size_mb vs. used_size_mb with a comment; retained allocated_size_mb * 1.1 per your request for 10% growth.
  4. LOBs/Partitions: Emphasized Data Pump’s automatic remapping, with validation queries.
  5. Multi-Schema: Generalized scripts to handle all schemas, with option to filter.
  6. RDS Procedures: Explicitly included rdsadmin.rdsadmin_s3_tasks.download_from_s3 for clarity.
  7. Clarity: Added detailed comments to explain each step and logic.

RDS-Specific Notes

  • Storage: Use estimated_target_size_mb for initial sizing; RDS auto-scales up to 64 TiB.
  • Permissions: Run as RDS master user or a privileged user. Use user_* views if dba_* access is restricted.
  • Best Practices: Test on a snapshot, enable Multi-AZ, monitor via CloudWatch.

Clarification on ELSE 'NEW_DATA8'

The NVL(tmr.new_tablespace, 'NEW_DATA8') in Step 2 ensures every tablespace is mapped, defaulting to NEW_DATA8 if not specified in ts_mapping_rules. This is critical for Data Pump’s REMAP_TABLESPACE and prevents migration failures. Validate NEW_DATA8 assignments to ensure they’re intentional.

If you provide your 56 tablespace names, specific schemas, or Oracle version (e.g., 19c for wildcard remaps), I can further tailor the scripts. Let me know if you need additional refinements or have specific concerns!

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)