Database administrator workshop: 08/07/13

To test this scenario I have recently created using DBCA a non container database named ORCL. CDB001 and CDBTEST are instead two container databases.

[oracle@vsi08devpom ~]$ ps -ef|grep smon
oracle    4081     1  0 Jul15 ?        00:01:30 ora_smon_CDB001
oracle   11394     1  0 10:20 ?        00:00:00 ora_smon_ORCL
oracle   12586  9609  0 10:46 pts/0    00:00:00 grep smon
oracle   16455     1  0 Jul17 ?        00:01:34 ora_smon_CDBTEST

I want to consolidate this database plugging it into a container database.
In this scenario I'm going to use the DBMS_PDB package to create the XML file with the metadata of ORCL database.
Before using the DBMS_PDB.DESCRIBE procedure, the ORCL database needs to be in READ ONLY mode.

[oracle@vsi08devpom ~]$ export ORACLE_SID=ORCL
[oracle@vsi08devpom ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 7 10:47:29 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@ORCL> show con_name;

CON_NAME
------------------------------
Non Consolidated
SQL@ORCL> select name, open_mode from V$DATABASE;

NAME   OPEN_MODE
--------- --------------------
ORCL   READ WRITE

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

SQL@ORCL> startup mount;
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size      2289688 bytes
Variable Size    264245224 bytes
Database Buffers   197132288 bytes
Redo Buffers      8163328 bytes
Database mounted.
SQL@ORCL> alter database open read only;

Database altered.

SQL@ORCL> begin
  2  dbms_pdb.describe( pdb_descr_file => '/opt/app/oracle/oradata/orcl.xml');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL@ORCL> host ls /opt/app/oracle/oradata/orcl*
/opt/app/oracle/oradata/orcl.xml

SQL@ORCL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL@ORCL> exit

Now connect to the container database. In my case I want to plug ORCL database into the CDBTEST container. It currently contains 4 pluggable databases.

[oracle@vsi08devpom ~]$ export ORACLE_SID=CDBTEST
[oracle@vsi08devpom ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 7 11:07:12 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@CDBTEST> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL@CDBTEST> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDBTEST1         READ WRITE
PDBTEST2         READ WRITE
PDBTEST3         READ WRITE
PDB003          READ ONLY

Here is the situation of the directories for the CDBTEST container database:

[oracle@vsi08devpom ~]$ cd /opt/app/oracle/oradata/CDBTEST/
[oracle@vsi08devpom CDBTEST]$ ll
total 3459784
-rw-r----- 1 oracle oinstall   17973248 Aug  7 11:13 control01.ctl
drwxr-x--- 2 oracle oinstall       4096 Aug  1 14:54 PDB003
drwxr-x--- 2 oracle oinstall       4096 Jul 17 11:25 pdbseed
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:16 PDBTEST1
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:17 PDBTEST2
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:18 PDBTEST3
-rw-r----- 1 oracle oinstall   52429312 Aug  7 11:12 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 00:16 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 06:00 redo03.log
-rw-r----- 1 oracle oinstall 1310728192 Aug  7 11:12 sysaux01.dbf
-rw-r----- 1 oracle oinstall  828383232 Aug  7 11:12 system01.dbf
-rw-r----- 1 oracle oinstall   63971328 Aug  7 11:01 temp01.dbf
-rw-r----- 1 oracle oinstall 1205870592 Aug  7 11:12 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug  7 06:05 users01.dbf

Now you can plug the ORCL database into the CDBTEST container database using the usual syntax (have a look at this post)

SQL@CDBTEST> create pluggable database ORCL using '/opt/app/oracle/oradata/orcl.xml' copy file_name_convert=('/opt/app/oracle/oradata/ORCL','/opt/app/oracle/oradata/CDBTEST/ORCL');

Pluggable database created.

The Oracle software copies under the directory /opt/app/oracle/oradata/CDBTEST/ORCL all the datafiles coming from /opt/app/oracle/oradata/ORCL location.

[oracle@vsi08devpom CDBTEST]$ ll
total 3459788
-rw-r----- 1 oracle oinstall   17973248 Aug  7 12:10 control01.ctl
drwxr-x--- 2 oracle oinstall       4096 Aug  7 11:49 ORCL
drwxr-x--- 2 oracle oinstall       4096 Aug  1 14:54 PDB003
drwxr-x--- 2 oracle oinstall       4096 Jul 17 11:25 pdbseed
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:16 PDBTEST1
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:17 PDBTEST2
drwxr-x--- 2 oracle oinstall       4096 Jul 17 13:18 PDBTEST3
-rw-r----- 1 oracle oinstall   52429312 Aug  7 12:10 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 00:16 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Aug  7 06:00 redo03.log
-rw-r----- 1 oracle oinstall 1310728192 Aug  7 12:10 sysaux01.dbf
-rw-r----- 1 oracle oinstall  828383232 Aug  7 12:07 system01.dbf
-rw-r----- 1 oracle oinstall   63971328 Aug  7 12:03 temp01.dbf
-rw-r----- 1 oracle oinstall 1205870592 Aug  7 12:10 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug  7 06:05 users01.dbf

A new pluggable database is created with a NEW status

SQL@CDBTEST> select pdb_id, pdb_name, dbid, status, creation_scn, con_id from CDB_PDBS;

    PDB_ID PDB_NAME     DBID STATUS        CREATION_SCN CON_ID
---------- ---------- ---------- ------------- ------------ ----------
  2 PDB$SEED   4063610283 NORMAL   217      1
  3 PDBTEST1   3064465721 NORMAL      1547881      1
  4 PDBTEST2   2395404598 NORMAL      1548944      1
  5 PDBTEST3   2434165039 NORMAL      1550036      1
  6 PDB003     1448206714 NORMAL      2744910      1
  7 ORCL       1350603571 NEW      3226095      1

It's still not the time to open the new PDB.
You need first to execute, while connected to the new pluggable database, the script $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql:

[oracle@vsi08devpom CDBTEST]$ ll /opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql 
-rw-r--r-- 1 oracle oinstall 19191 Apr 15 22:27 /opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql

SQL@CDBTEST> alter session set container=ORCL;

Session altered.

SQL@CDBTEST> @/opt/app/oracle/product/12.1.0/db_1/rdbms/admin/noncdb_to_pdb.sql
SQL@CDBTEST> SET SERVEROUTPUT ON
SQL@CDBTEST> SET FEEDBACK 1
SQL@CDBTEST> SET NUMWIDTH 10
...
...
...
SQL@CDBTEST> -- leave the PDB in the same state it was when we started
SQL@CDBTEST> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL@CDBTEST> 
SQL@CDBTEST> WHENEVER SQLERROR CONTINUE;
SQL@CDBTEST>

Now you can open the new ORCL pluggable database.

SQL@CDBTEST> show con_name;

CON_NAME
------------------------------
ORCL
SQL@CDBTEST> alter database open;

Database altered.

The database is so available and ready to be used.

SQL@CDBTEST> select pdb_id, pdb_name, dbid, status, creation_scn, con_id from CDB_PDBS;

    PDB_ID PDB_NAME     DBID STATUS        CREATION_SCN CON_ID
---------- ---------- ---------- ------------- ------------ ----------
  2 PDB$SEED   4063610283 NORMAL   217      1
  3 PDBTEST1   3064465721 NORMAL      1547881      1
  4 PDBTEST2   2395404598 NORMAL      1548944      1
  5 PDBTEST3   2434165039 NORMAL      1550036      1
  6 PDB003     1448206714 NORMAL      2744910      1
  7 ORCL       1350603571 NORMAL      3226095      1

That's all.

You have just unplugged a PDB and want to know how to eventually plug it back into the same container.
Let's assume I have a pluggable database named PDB003 and I am just able to unplug it using the following commands:

SQL> alter pluggable database pdb003 close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';

Pluggable database altered.

If you query the CDB_PDBS view you can receive useful information about all PDBs contained: id, name, unique identifiers, SCN at the time of creation and the status.
The STATUS column has several values:

NEW, the pdb has been created but never opened
NORMAL, pdb available to be used
UNPLUGGED, the pdb has been unplugged
NEEDS UPGRADE, the pdb must be upgraded
CONVERTING, a non-CDB is going to be converted into a pdb
UNUSABLE, the pdb cannot be used (creation in progress)

In my case the PDB003 pluggable database is in an UNPLUGGED status as expected:

SQL> select * from CDB_PDBS;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  2 PDB$SEED   4063489868 4063489868 E19363E52C005C9AE045000000000001 NORMAL      233   1
  3 PDB001     1701599811 1701599811 E1F26215682E1142E045000000000001 NORMAL  1821405   1
  4 PDB002     1497027100 1497027100 E1F329ECE0F411E6E045000000000001 NORMAL  1844390   1
  5 PDB003     1448206714 1448206714 E2B9BE56B8B936CEE045000000000001 UNPLUGGED  1997215   1

To plug again the pluggable database PDB003 inside the original container (CDB001) you cannot use a syntax like: ALTER PLUGGABLE DATABASE ... PLUG (it doesn't exist in the "SQL Language Reference" manual a PLUG clause) ...

SQL> alter pluggable database PDB003 plug;
alter pluggable database PDB003 plug
                                *
ERROR at line 1:
ORA-00922: missing or invalid option

... neither you can simply use CREATE PLUGGABLE DATABASE ... USING ...

SQL> create pluggable database PDB003 using '/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse;
create pluggable database PDB003 using '/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse
*
ERROR at line 1:
ORA-65012: Pluggable database PDB003 already exists.

You need first to drop the pluggable database (DROP PLUGGABLE DATABASE command) and then create it again (CREATE PLUGGABLE DATABASE command).
In this way the pluggable database is plugged again into the same CDB and using the same PDB name.
Have a look at the following commands:

SQL> drop pluggable database PDB003;

Pluggable database dropped.

SQL> create pluggable database PDB003 using '/app/oracle/oradata/pdb003.xml' nocopy;               

Pluggable database created.

SQL> select PDB_NAME, STATUS from CDB_PDBS;

PDB_NAME   STATUS  
---------- ------------- 
PDB$SEED   NORMAL 
PDB001     NORMAL 
PDB002     NORMAL 
PDB003     NEW  

SQL> alter session set container=PDB003;

Session altered.

SQL> alter database open;

Database altered.

SQL> select count(*) from marcov.T1;

  COUNT(*)
----------
       100

Have a look again at the CDB_PDBS view and note the differences compared with the previous results.
All the unique identifiers of PDB003 have been recreated.

SQL> select * from CDB_PDBS;

    PDB_ID PDB_NAME     DBID    CON_UID GUID        STATUS    CREATION_SCN     CON_ID
---------- ---------- ---------- ---------- -------------------------------- ------------- ------------ ----------
  2 PDB$SEED   4063489868 4063489868 E19363E52C005C9AE045000000000001 NORMAL      233   1
  3 PDB001     1701599811 1701599811 E1F26215682E1142E045000000000001 NORMAL  1821405   1
  4 PDB002     1497027100 1497027100 E1F329ECE0F411E6E045000000000001 NORMAL  1844390   1
  5 PDB003     1448206714 1448206714 E2B9BE56B8B936CEE045000000000001 NORMAL  3110422   1

That's all.

Database administrator workshop

Pages

Wednesday, August 7, 2013

How to create a PDB from a Non-CDB using DBMS_PDB package

How to unplug a PDB and plug it back into the same CDB