1. I am planning to start the REDEF about a week before the actual "event" and am planning to introduce multiple SYNCs during weekdays, with FINISH to be done during the "event". What challenges, if any, do you see with this approach?
The big question really is volume. You can think of the SYNC as being like a materialized view refresh using mview logs. So ideally, less volume (ie, a quiet time) is ideal. And don't forget to factor in the time for subordinate objects (indexes, constraints etc)
a) How will DB going down/ is restarted during weekdays, affect REDEF? I am guessing no impact but happy to be proved wrong
Shouldn't make any difference, but I strongly encourage you to test in your own environment!
SQL> conn /@db19_pdb1 as sysdba
Connected.
SQL> create table scott.old_emp as select * from scott.emp;
Table created.
SQL> alter table scott.old_emp add primary key ( empno );
Table altered.
SQL> create table scott.new_emp as select * from scott.emp where 1=0;
Table created.
SQL>
SQL> EXEC DBMS_REDEFINITION.start_redef_table('SCOTT', 'OLD_EMP', 'NEW_EMP');
PL/SQL procedure successfully completed.
SQL>
SQL> EXEC DBMS_REDEFINITION.sync_interim_table('SCOTT', 'OLD_EMP', 'NEW_EMP');
PL/SQL procedure successfully completed.
SQL> shutdown immediate
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> EXEC DBMS_REDEFINITION.sync_interim_table('SCOTT', 'OLD_EMP', 'NEW_EMP');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_REDEFINITION.finish_redef_table('SCOTT', 'OLD_EMP', 'NEW_EMP');
PL/SQL procedure successfully completed.
b) Is there a way to monitor the progress of any of the REDEF operations? I believe 12.2. provides a view v$online_redef but is there any way to monitor REDEF on 11.2?The first one is equivalent to a big CTAS, so you should be able to use v$session_longops or sql monitoring if you are licensed for it.