We will unplug database into pdb, create new database using that archive and convert the characterset of the new DB.
Character set of the DB that will be unplugged:
SYS@ORCL19W SQL> select * from nls_database_parameters;
PARAMETER VALUE
---------------------------------------- ----------------------------------------------------------------
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET WE8MSWIN1252
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
We will unplug the database into the pdb archive:
SYS@ORCL19W SQL> alter pluggable database plug19w close immediate;
Pluggable database altered.
SYS@ORCL19W SQL> alter pluggable database plug19w unplug into '/tmp/plug19w.pdb';
Pluggable database altered.
SYS@ORCL19W SQL> select * from nls_database_parameters;
PARAMETER VALUE
---------------------------------------- ----------------------------------------------------------------
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET WE8MSWIN1252
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
We will unplug the database into the pdb archive:
SYS@ORCL19W SQL> alter pluggable database plug19w close immediate;
Pluggable database altered.
SYS@ORCL19W SQL> alter pluggable database plug19w unplug into '/tmp/plug19w.pdb';
Pluggable database altered.
We will create the new database from the archive in another CDB wit AL32UTF8 character set.
SYS@ORCL19 SQL> create pluggable database plug19wn using '/tmp/plug19w.pdb';
Pluggable database created.
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 READ WRITE NO
5 PLUG19_4 READ WRITE NO
6 PLUG19WN MOUNTED
SYS@ORCL19 SQL> alter pluggable database plug19wn open read write;
Pluggable database altered.
SYS@ORCL19 SQL> alter session set container=plug19wn;
Session altered.
SYS@ORCL19 SQL> show con_name
CON_NAME
------------------------------
PLUG19WN
SYS@ORCL19 SQL> select * from nls_database_parameters;
PARAMETER VALUE
---------------------------------------- ----------------------------------------------------------------
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET WE8MSWIN1252
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
SYS@ORCL19 SQL> create pluggable database plug19wn using '/tmp/plug19w.pdb';
Pluggable database created.
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 READ WRITE NO
5 PLUG19_4 READ WRITE NO
6 PLUG19WN MOUNTED
SYS@ORCL19 SQL> alter pluggable database plug19wn open read write;
Pluggable database altered.
SYS@ORCL19 SQL> alter session set container=plug19wn;
Session altered.
SYS@ORCL19 SQL> show con_name
CON_NAME
------------------------------
PLUG19WN
SYS@ORCL19 SQL> select * from nls_database_parameters;
PARAMETER VALUE
---------------------------------------- ----------------------------------------------------------------
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET WE8MSWIN1252
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
Now, we can convert the WE8MSWIN1252 into the AL32UTF8 using oracle dmu utility.
[ORCL19 oramni@exhdbadm01:/oracle/oramni/product/19c/dmu]> cd $ORACLE_HOME/dmu
[ORCL19 oramni@exhdbadm01:/oracle/oramni/product/19c/dmu]> ./dmu.sh
Database Migration Assistant for Unicode
Copyright (c) 2011, 2017, Oracle and/or its affiliates. All rights reserved.
[ORCL19 oramni@exhdbadm01:/oracle/oramni/product/19c/dmu]> cd $ORACLE_HOME/dmu
[ORCL19 oramni@exhdbadm01:/oracle/oramni/product/19c/dmu]> ./dmu.sh
Database Migration Assistant for Unicode
Copyright (c) 2011, 2017, Oracle and/or its affiliates. All rights reserved.
Create new db connection:
Connect to the container and run prvtdumi.plb and create new tablespace for repository:
SYS@ORCL19 SQL> alter session set container=plug19wn;
Session altered.
SYS@ORCL19 SQL> @?/rdbms/admin/prvtdumi.plb
Library created.
Package created.
No errors.
Package body created.
No errors.
Create new tablespace for repository:
SYS@ORCL19 SQL> create tablespace repo datafile size 100m autoextend on maxsize 10G;
Tablespace created.
SYS@ORCL19 SQL> alter session set container=plug19wn;
Session altered.
SYS@ORCL19 SQL> @?/rdbms/admin/prvtdumi.plb
Library created.
Package created.
No errors.
Package body created.
No errors.
Create new tablespace for repository:
SYS@ORCL19 SQL> create tablespace repo datafile size 100m autoextend on maxsize 10G;
Tablespace created.
Click next
Clik next and choose the tablespace we have created
Click finish.
Repository will be installed.
Repository will be installed.
First step was successful, we can proceed to the next step. Scan the database..
Scan will be started. You can see the progress in the progress bar
There should be no unresolvable errors after this step.
Now, we can start with the migration. Click on the "Convert database"
Click on the convert button:
You can track the update in the Conversion steps panel.
The tool was not able to convert the unified audit taables, so I have skipped them:
The tool was not able to convert the unified audit taables, so I have skipped them:
Migration was successfull
We can check the result
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 READ WRITE NO
5 PLUG19_4 READ WRITE NO
6 PLUG19WN READ WRITE YES
SYS@ORCL19 SQL> alter pluggable database plug19wn close immediate;
Pluggable database altered.
SYS@ORCL19 SQL> alter pluggable database plug19wn open read write;
Pluggable database altered.
SYS@ORCL19 SQL> alter session set container=plug19wn;
Session altered.
SYS@ORCL19 SQL> select * from nls_database_parameters;
PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 MOUNTED
4 ORCL11 READ WRITE NO
5 PLUG19_4 READ WRITE NO
6 PLUG19WN READ WRITE YES
SYS@ORCL19 SQL> alter pluggable database plug19wn close immediate;
Pluggable database altered.
SYS@ORCL19 SQL> alter pluggable database plug19wn open read write;
Pluggable database altered.
SYS@ORCL19 SQL> alter session set container=plug19wn;
Session altered.
SYS@ORCL19 SQL> select * from nls_database_parameters;
PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN