Convert a non PDB to a PDB


puz1

When you want to convert a Single Instanz (non PDB) to a PDB (for example after a pre 12c Database upgrade) then this example is usefull for you.

First here is a overview of the task:

  • Open the database in read only mode
  • Create a xml File for the conversion
  • Login into your CDB
  • Plug in your database
  • Do the convert
  • Open your converted PDB

Step 1: Open your database in read only mode

SQL> startup mount
ORACLE instance started.

Total System Global Area  279752704 bytes
Fixed Size            2287480 bytes
Variable Size          201328776 bytes
Database Buffers       71303168 bytes
Redo Buffers            4833280 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

Step 2: Create a xml File for the conversion

SQL> exec dbms_pdb.describe('/home/oracle/xmldb1211.xml');

PL/SQL procedure successfully completed.

You can open the file in a editor. The File generated here describes the metadata (Tablespaces, Options, Parameters, …) for the database. If this step is completed, shutdown your database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 3: Login into your CDB

[oracle@# ~]$ . oraenv
ORACLE_SID = [db1211] ? CDB1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@# ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 13 15:41:20 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Step 4: Plug in your database

SQL> create pluggable database PDB_db1211 using '/home/oracle/xmldb1211.xml' NOCOPY;
create pluggable database PDB_db1211 using '/home/oracle/xmldb1211.xml' NOCOPY
*
ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file
'/u01/app/oracle/oradata/db1211/temp01.dbf'

The error shown above occurs when the temp tablespace for this database already exists. In this case there are two possibilities. Either delete the file or use the TEMPFILE REUSE clause.

SQL> !rm -rf /u01/app/oracle/oradata/db1211/temp01.dbf

SQL> create pluggable database PDB_db1211 using '/home/oracle/xmldb1211.xml' NOCOPY;

Pluggable database created.

Step 5: Do the convert

The convert is done from the PDB. So you need to login into it. For that you need a new entry in your tnsnames.ora file. The PDB should already have registerd with your Listener.

PDB_db1211 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb_db1211.example.com)
    )
  )

Connect to the PDB

[oracle@# admin]$ sqlplus sys@pdb_db1211 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 13 15:50:24 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Start the convert

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Step 6: Open your converted PDB

Connect to the CDB and open the PDB. After then check if you can login.

SQL> alter pluggable database pdb_db1211 open;

Pluggable database altered.
[oracle@# admin]$ sqlplus scott/tiger@server1:1521/PDB_db1211.example.com

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 13 16:07:14 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Sat Jul 13 2013 16:06:58 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/db1211/temp01.dbf
Advertisements

2 Gedanken zu “Convert a non PDB to a PDB

  1. Append TEMPFILE REUSE to the „create pluggable database“ command to avoid the error and eliminate the step to remove the temp db file.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s