Topic/Questions: Oracle Character Set/Database 11 to 12 Migration Options
Task: Migrating production Oracle EE 220.127.116.11 single instance database with NLS_CHARACTERSET WE8ISO8859P1 to existing remote server hosting Oracle EE 12c (18.104.22.168.0) RAC DB with NLS_CHARACTERSET AL32UTF8 (The desired outage window is 4 hours or less.)
Current production environment is as follows:
Oracle EE 11gR2 (22.214.171.124) single instance DB with 1 physical standby implementing DR/Active Data Guard 2 Terabyte Database, Oracle Linux Server release 7.3 NLS_CHARACTERSET WE8ISO8859P1, NLS_NCHAR_CHARACTERSET AL16UTF16
We plan on moving this production environment to new server Oracle 12c two node RAC environment (Oracle Data Appliance [ODA X62 HA] ) which is our current testing environment consisting of:
Oracle EE RAC 2 node 12c (126.96.36.199.0) Database Oracle Linux Server release 6.8 NLS_CHARACTERSET AL32UTF8 NLS_NCHAR_CHARACTERSET AL16UTF16 (NOTE: There is second identical 12c RAC ODA appliance that is slated to be the EE 12c RAC 2 node physical standby in a DR/Active Data Guard configuration.)
OPTIONS BEING CONSIDERED/EVALUATED FOR ORACLE 11G SINGLE INSTANCE TO 12C RAC DATABASE MIGRATION: (Note: Outage window is planned to be 4 hours or less)
(1) Transportable Database - this is a feature of 12c and appears N/A because source database is 11gR2 (please confirm).
(2) Transportable Tablespaces - NLS_CHARACTERSETS are different (source 11gr2 DB is NLS_CHARACTERSET WE8ISO8859P1 and destination 12c RAC database is NLS_CHARACTERSET AL32UTF8) Both databases have the same NLS_NCHAR_CHARACTERSET (AL16UTF16). Oracle states the Database Migration Assistant for Unicode tool (DMU) can be used to convert WE8ISO8859P1 to ALT32UTF8.) If this tool/approach is used - it would have to be on a live 11gR2 production system which has its inherent risks (yes, do the full backup first before using the tool). Does the database have to be stopped to use this tool and how long, in general, would a 2 Terabyte DB conversion take (concern about the limited 4-hour outage window)? If this method is chosen - would I just need to transport all user tablespaces only (not system/sysaux)?
(3) Creating an additional transient 12c physical standby and conducting an immediate switchover to the 12c RAC database once synched and turning off the production 11gR2 (former primary) database and existing 11gR2 standby. (The first step would be to drop the existing 12C RAC test database via DBCA and then run the RMAN Active Duplicate Database command and immediately complete the physical standby configuration process and synch the new 12c RAC standby. The next step would be to set up DR/Active Dataguard with the 2nd 12c RAC ODA.)
Please confirm since the existing 12C RAC test database was dropped and a 12c transient standby database was created there would not be any NLS Character set differences.
Please confirm that there would be no need for a 11gR2 to 12c database upgrade processsince the standby was created with 12c binaries.
Any other options or advice/recommendations would be appreciated.