Oracle Database operational considerations

Perform a dictionary dump on the primary database. (This will not wait for open transactions.)

exec DBMS_LOGMNR_D.BUILD(OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

On the primary database, perform table instantiation for all tables to be read. This command will not complete until all open transactions related to those tables are completed.

for non-CDB (replace <schema.table> with the appropriate value for your environment):

exec DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
  table_name => ‘<schema.table>',
  supplemental_logging => 'NONE',
  container => 'CURRENT');

for CDB (replace <schema.table> and <container> with the appropriate value for your environment):

DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
    table_name => ‘<schema.table>',
    supplemental_logging => 'NONE',
    container => '<container>’);

Run the following command on the primary database and note the SCN returned. Replace <table owner> with the appropriate value for your environment and <tables> with the same string used in step 2.

select max(SCN) from DBA_CAPTURE_PREPARED_TABLES 
  where TABLE_OWNER = '<table owner>'
  and TABLE_NAME in ( <tables>)

Perform initial load.

Start OJet from the SCN returned in step 3.

Perform a dictionary dump:

exec DBMS_LOGMNR_D.BUILD(OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

Perform instantiation of the tables to be added:

-- Create Table for tracking replicated tables needed for instantiation 
-- and procedure will read the table list from there 
CREATE TABLE ojet_table_instantiation (
pdb_name VARCHAR2(100),
schema_name VARCHAR2(100),
table_name VARCHAR2(100)
);
-- Add schema and table names to the configuration
INSERT INTO ojet_table_instantiation VALUES ('DEMOPDB', 'STRIIMSCHMA', 'SAMPLE_TABLE');
INSERT INTO ojet_table_instantiation VALUES ('SOMEPDB', 'ASCHMA', 'SAMPLE_TABLE2');
INSERT INTO ojet_table_instantiation VALUES ('ANOTHERPDB', 'YOURSCHMA', 'SAMPLE_TABLE3');

-- Below PL/SQL need to be executed under CDB level
set serveroutput on
DECLARE
   saved_sync VARCHAR2(4);
BEGIN
   SELECT dbms_xstream_gg_adm.synchronization INTO saved_sync FROM dual;
   dbms_xstream_gg_adm.synchronization := 'NONE';
   FOR r IN (SELECT pdb_name, schema_name, table_name FROM ojet_table_instantiation)
   LOOP
      DBMS_OUTPUT.PUT_LINE('Preparing table: ' || r.pdb_name || '.'  || r.schema_name || '.' || r.table_name);
      DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
         table_name => r.schema_name || '.' || r.table_name,
         supplemental_logging => 'NONE',
         container => r.pdb_name);
   END LOOP;
   dbms_xstream_gg_adm.synchronization := saved_sync;
END;

Replace the three INSERT INTO lines with one line for each of the tables to be added. If reading from Oracle 11g, omit , container => r.pdb_name) from the DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION command.

Get the maximum instantiation SCN:

SELECT MAX(SCN) FROM DBA_CAPTURE_PREPARED_TABLES
WHERE TABLE_OWNER = ‘<table owner>’ AND TABLE_NAME IN (<table name>)

Replace <table name> with the name of the table in the last INSERT INTO clause from step 2 above.