Wednesday, September 20, 2017

How to migrate a non-CDB database to a PDB on the same host

There are many ways to migrate your non-cdb Oracle databases to the new Multitenant Architecture. Here I will show you how to clone a non-cdb database to a PDB running in a container database

Asumptions:
You have two Oracle databases of version 12.1 or higher running on the same server:

1. Your original, non-cdb database called db01
2. Your new container database called cdb01
Both of these databases are running out of Oracle Home installe in /u01/oracle/product/12c

Step 1: For Oracle 12.1, open your non-CDB in read only mode (not needed from version 12.2 and onwards):
shutdown immedate
startup mount
alter database open read only;

Step 2: create an xml file that describes the non-CDB database using the package dbms_pdb:
export ORACLE_SID=db01
sqlplus / as sysdba
Generate the file:
set serveroutput on
begin
  dbms_pdb.describe( pdb_descr_file => '/tmp/ncdb.xml');
end;
/

Step 3: Create the pluggable database

Connect to your CDB, and create the PDB using the script you created in step 2.

export ORACLE_SID=cdb01
sqlplus / as sysdba
create pluggable database pdb01
using '/tmp/ncdb.xml'
copy
file_name_convert = ('/u02/oradata/db01/', '/u02/oradata/cdb01/PDBS/pdb01/');
Note that I am choosing to copy the files from the original location of the non-CDB database, to a brand new one, using the directive file_name_convert. There are other options, too: MOVE and NOCOPY

Step 4: execute $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql


sqlplus / as sysdba
alter session set container=PDB01;
@?/rdbms/admin/noncdb_to_pdb.sql

I did receive errors during this phase:
ORA-65172: cannot run noncdb_to_pdb.sql unless pluggable database is an
unconverted non-container database
ORA-06512: at "SYS.DBMS_PDB", line 154
ORA-06512: at line 1

The error message seemed to be harmless:
oracle@myserver.mydomain.com:[cdb1]# oerr ora 65172
65172, 00000, "cannot run noncdb_to_pdb.sql unless pluggable database is an unconverted non-container database"
// *Cause:  An attempt was made to run 'noncdb_to_pdb.sql' on a pluggable
//          database (PDB) that was not an unconverted non-container database.
// *Action: 'noncdb_to_pdb.sql' is not necessary for this PDB.
//


Further research showed 1) the Pdb was indeed created, and 2) that there were errors in PDB_PLUG_IN_VIOLATIONS:
select PDB_ID,PDB_NAME,STATUS,CON_ID from cdb_pdbs

    PDB_ID PDB_NAME                       STATUS                          CON_ID
---------- ------------------------------ --------------------------- ----------
         2 PDB01                          NEW                                  2

alter session set container=cdb$root;


SELECT TO_CHAR(TIME,'dd.mm.yyyy hh24:mi') "time",NAME,STATUS,MESSAGE 
FROM PDB_PLUG_IN_VIOLATIONS;

Result:
time NAME STATUS MESSAGE
20.09.2017 14:17 PDB01 PENDING Database option CATJAVA mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17 PDB01 PENDING Database option CONTEXT mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17 PDB01 PENDING Database option JAVAVM mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17 PDB01 PENDING Database option ORDIM mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17 PDB01 PENDING Database option XML mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.
20.09.2017 14:17 PDB01 PENDING Sync PDB failed with ORA-65177 during 'alter user sys identified by *'
20.09.2017 14:17 PDB01 PENDING Sync PDB failed with ORA-65177 during 'alter user system identified by *'

A quick search on Oracles support site revealed that these errors can be ignored. See Doc ID 2020172.1 "OPTION WARNING Database option mismatch: PDB installed version NULL" in PDB_PLUG_IN_VIOLATIONS"

Finally, remember to open your pdb in read write mode. It was in MIGRATE mode after the noncdb_to_pdb.sql script had been run and failed:
 select con_id,name,open_mode from v$containers;

    CON_ID NAME      OPEN_MODE
---------- --------- -----------------------------
         1 CDB$ROOT  READ WRITE
         2 PDB01     MIGRATE

alter session set container=PDB01;

alter pluggable database close;

Pluggable database altered.

alter pluggable database open;

 select con_id,name,open_mode from v$containers;

    CON_ID NAME      OPEN_MODE
---------- --------- -----------------------------
         2 PDB01     READ WRITE

No comments:

Post a Comment