Resize table DBRSCOL$

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
2 min read

Problem Description

Warning: This is modification to Dbvisit Replicate internal repository. Use at your own risk. If this method fails for any reason, the only remediation is to re-instantiate the replication

 

The table DBRSCOL$ captures and records creation of all tables in database. If any table gets created in database there will be a new record in table DBRSCOL$. If you drop any table in the database and create it again, there will be another record created.

 

If there is frequent re-creation of tables in the database, then table DBRSCOL$ can be very big. This is not a problem for running replication, but it may cause a problem with MINE startup time. In such cases the mine startup can take over an hour. To fix this issue, we need to re-size the table DBRSCOL$. The steps are following:

 

Solution

  1. Get the current status of table DBRSCOL$:

SQL> select bytes/1024/1024 mb from user_segments where segment_name='DBRSCOL$'; SQL> select count(*) from DBRSCOL$;

2. Re-create the table DBRSCOL$ with smaller size. On source database as DBVREP user

First, we have to find a reasonable SCN which we will use as a base for re-creation of table DBRSCOL$.

select last_scn from DBRSMINE_PLOG_HISTORY where sequence = (select min(last_plog_seq) - 50 from dbrsmine_plog_current) and DDC_ID = 1 and MINE_PROCESS_NAME = 'MINE';

You sholud be careful with determining the SCN and ensure it will be in all cases lower than current APPLY SCN. You can also use table DBRSMINE_PLOG_HISTORY to choose some less recent SCN.

 

If you wish, you can check how many rows will be in resized table:

select count(*) from DBRSCOL$ where VALID_TO_SCN => <last_scn_from_previous_step>;

Then run following statements - this will re-create the table DBRSCOL$ with data from valid SCN. Before proceeding, consider following:

  • CTAS statement will usually take long, as it need to process lot of data (should be similar to your MINE startup time). Following insert as select will also take some time.

  • Double check your dbrep repository, if DDL is the same for the primary key recreatio

  • Ensure, that your MINE & APPLY are in sync.

Once are ready to go, perform following:

  • Shut down MINE and APPLY

  • Then run following SQL statements:

3. Check again the status of table DBRSCOL$:

5. Gather statistics on DBRSCOL$

6. Startup the MINE & APPLY processes again.

If there is no additional space management and DB is kept being used the same way it was before we would get into same situation again soon. Therefore, directly after the table is re-sized, some deletion job should be scheduled in source database to prevent this from happening again.