Ryalli's world: DMS

I understand you want to modify the ts_mappings table to incorporate a 10% growth buffer (instead of the 20% previously suggested) for the estimated_target_size_mb when consolidating 56 tablespaces into 8 new ones for an Oracle RDS migration. Below, I’ll provide a polished, streamlined solution that adjusts the size estimation to use a 10% growth buffer, while maintaining the reusable ts_mappings table, addressing user and index mappings, and covering other objects. The approach remains optimized for Oracle Data Pump in an Amazon RDS context, with concise SQL scripts and RDS-specific considerations. If you meant something else by "10% growth" (e.g., a different calculation or scope), please clarify.

Objective

Consolidate 56 source tablespaces into 8 new bigfile tablespaces on Oracle RDS.
Create a reusable ts_mappings table with size metrics (allocated, used, and estimated target sizes with a 10% growth buffer).
Map users, indexes, and other objects (e.g., LOBs, partitions) for the migration.
Ensure scripts are reusable for multiple operations and tailored for RDS constraints.

Step 1: Create the ts_mappings Table

Design a table to store old-to-new tablespace mappings with size metrics, optimized for RDS and reuse.

sql

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
    used_size_mb NUMBER,               -- Used size of old tablespace
    estimated_target_size_mb NUMBER,   -- Estimated size for new tablespace (with 10% growth)
    description VARCHAR2(200),         -- Consolidation rationale
    last_updated DATE DEFAULT SYSDATE,
    CONSTRAINT pk_ts_mappings PRIMARY KEY (old_tablespace)
);
CREATE INDEX idx_ts_mappings_new ON ts_mappings (new_tablespace);

Purpose: The primary key ensures unique old tablespaces; the index optimizes queries by new_tablespace. Store in a custom schema (e.g., ADMIN_SCHEMA) on the source or target RDS.

Step 2: Populate ts_mappings with Size Data

Populate the table with mappings and size data from the source database, using dba_data_files for allocated size and dba_segments for used size. Customize the mapping logic for your 56-to-8 consolidation.

sql

INSERT INTO ts_mappings (old_tablespace, new_tablespace, allocated_size_mb, used_size_mb, description)
SELECT 
    ddf.tablespace_name,
    CASE 
        WHEN ddf.tablespace_name IN ('TS01', 'TS02' /*, ...*/) THEN 'NEW_DATA1'
        WHEN ddf.tablespace_name IN ('TS11', 'TS12' /*, ...*/) THEN 'NEW_DATA2'
        WHEN ddf.tablespace_name IN ('TS21', 'TS22' /*, ...*/) THEN 'NEW_DATA3'
        WHEN ddf.tablespace_name IN ('TS31', 'TS32' /*, ...*/) THEN 'NEW_DATA4'
        WHEN ddf.tablespace_name IN ('TS41', 'TS42' /*, ...*/) THEN 'NEW_DATA5'
        WHEN ddf.tablespace_name IN ('TS51', 'TS52' /*, ...*/) THEN 'NEW_DATA6'
        WHEN ddf.tablespace_name IN ('TS61', 'TS62' /*, ...*/) THEN 'NEW_DATA7'
        ELSE 'NEW_DATA8'
    END AS new_tablespace,
    SUM(ddf.bytes)/1024/1024 AS allocated_size_mb,
    NVL(SUM(ds.bytes)/1024/1024, 0) AS used_size_mb,
    'Consolidated tablespace' AS description
FROM dba_data_files ddf
LEFT JOIN dba_segments ds ON ds.tablespace_name = ddf.tablespace_name
WHERE ddf.tablespace_name IN ('TS01', 'TS02' /*, ...list all 56...*/)
GROUP BY ddf.tablespace_name;
COMMIT;

Customization: Replace TS01, TS02, etc., with your actual tablespace names and adjust the CASE logic to map to the 8 new tablespaces (e.g., NEW_DATA1 to NEW_DATA8).
RDS Note: If dba_ views are restricted, use user_data_files or user_segments for schema-level data.

Step 3: Estimate Target Sizes with 10% Growth Buffer

Calculate estimated_target_size_mb for the 8 new tablespaces by aggregating allocated sizes and applying a 10% growth buffer.

sql

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;

-- View aggregated sizes
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;

Why 10%?: The 1.1 multiplier adds a 10% buffer for growth, fragmentation, or index rebuilds, as requested. This is more conservative than the previous 20% but sufficient for most migrations.
Output Example:
text
NEW_TABLESPACE | OLD_TS_COUNT | TOTAL_ALLOCATED_MB | TOTAL_USED_MB | TARGET_SIZE_MB ---------------|--------------|--------------------|---------------|---------------- NEW_DATA1 | 10 | 50000 | 30000 | 55000 NEW_DATA2 | 12 | 60000 | 40000 | 66000 ...
Use target_size_mb to size RDS bigfile tablespaces.

Step 4: Create New Tablespaces on RDS

Generate and execute CREATE TABLESPACE statements for the 8 bigfile tablespaces on the target RDS instance, using the estimated sizes.

sql

SELECT 'CREATE BIGFILE TABLESPACE ' || new_tablespace || 
       ' DATAFILE SIZE ' || ROUND(MAX(estimated_target_size_mb)) || 
       'M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;'
FROM ts_mappings
GROUP BY new_tablespace;

Execution: Run the generated SQL on RDS via SQL*Plus or SQL Developer.
RDS Tip: Bigfile tablespaces are preferred; AUTOEXTEND ensures scalability (RDS max: 64 TiB).

Step 5: Map Users, Indexes, and Other Objects

Users:
- Preconfigure user default tablespaces and quotas on the target:
  sql
  SELECT 'ALTER USER ' || username || ' DEFAULT TABLESPACE ' || (SELECT new_tablespace FROM ts_mappings WHERE old_tablespace = du.default_tablespace AND ROWNUM = 1) || ' QUOTA UNLIMITED ON ' || (SELECT new_tablespace FROM ts_mappings WHERE old_tablespace = du.default_tablespace AND ROWNUM = 1) || ';' FROM dba_users du WHERE default_tablespace IN (SELECT old_tablespace FROM ts_mappings);
- Execute on RDS before import to align settings.
Indexes:
- Data Pump remaps indexes via REMAP_TABLESPACE. Post-import, rebuild invalid indexes:
  sql
  SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE ' || (SELECT new_tablespace FROM ts_mappings WHERE old_tablespace = di.tablespace_name AND ROWNUM = 1) || ';' FROM dba_indexes di WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings) AND status != 'VALID';
Other Objects:
- LOBs: Handled by REMAP_TABLESPACE. Verify placement:
  sql
  SELECT owner, table_name, column_name, tablespace_name FROM dba_lobs WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings);
- Partitions/Subpartitions: Remapped automatically; check dba_tab_partitions.
- Temporary Tablespaces: If consolidating, update:
  sql
  ALTER USER username TEMPORARY TABLESPACE new_temp_ts;
- Others: Sequences, constraints, triggers, materialized views are remapped by Data Pump. Recreate database links and directories on RDS (e.g., via rdsadmin.rdsadmin_util.create_directory).

Step 6: Execute the Migration

Export from Source:
bash
expdp system/password@source_db DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_export_%U.dmp LOGFILE=export.log FULL=Y PARALLEL=4
- Upload dump files to S3 (use rdsadmin.rdsadmin_s3_tasks.upload_to_s3).
Generate REMAP_TABLESPACE Clause:
sql
SELECT LISTAGG('REMAP_TABLESPACE=' || old_tablespace || ':' || new_tablespace, ',') WITHIN GROUP (ORDER BY old_tablespace) AS remap_clause FROM ts_mappings;
Import to RDS:
bash
impdp system/password@target_rds DIRECTORY=DATA_PUMP_DIR DUMPFILE=full_export_%U.dmp LOGFILE=import.log [paste remap_clause] PARALLEL=4
Post-Import:
- Validate objects:
  sql
  SELECT object_type, COUNT(*) FROM dba_objects WHERE status = 'INVALID' GROUP BY object_type;
- Gather statistics:
  sql
  EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'YOUR_SCHEMA', cascade => TRUE);

Step 7: Reuse for Multiple Operations

Update Size Metrics:
sql
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 Scripts:
- Manual table moves (if not using Data Pump):
  sql
  SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' MOVE TABLESPACE ' || (SELECT new_tablespace FROM ts_mappings WHERE old_tablespace = dt.tablespace_name AND ROWNUM = 1) || ';' FROM dba_tables dt WHERE tablespace_name IN (SELECT old_tablespace FROM ts_mappings);
- Monitor usage:
  sql
  SELECT new_tablespace, ROUND(SUM(used_size_mb), 2) AS current_used_mb FROM ts_mappings GROUP BY new_tablespace;

RDS-Specific Considerations

Storage: Use estimated_target_size_mb (with 10% buffer) to size tablespaces. RDS auto-scales, but proper initial sizing minimizes overhead.
S3 Integration: Manage dump files via S3 (see AWS docs for rdsadmin procedures).
Permissions: Run as RDS master user or a custom user with DBA role. Use user_* views if dba_* access is restricted.
Limits: Max 64 TiB per instance; bigfile tablespaces only.
Best Practices: Test on a snapshot. Enable Multi-AZ for HA. Monitor via CloudWatch.

Key Enhancement

10% Growth Buffer: The estimated_target_size_mb now uses a 1.1 multiplier (10% growth) instead of 1.2, providing a leaner estimate while still accounting for growth and fragmentation.
Output: The aggregated view in Step 3 shows precise sizing for RDS planning.

If you need specific tablespace names, Oracle version (e.g., 19c for wildcard remaps), or additional scripts (e.g., for a subset of schemas), please provide details for further customization!

Ryalli's world

Thursday, September 4, 2025

DMS - Part 1