PDB_PLUG_IN_VIOLATIONS in ORACLE 12C

PDB_PLUG_IN_VIOLATIONS view is useful view if there is any issues with PDB.

SQL> alter pluggable database PDB1 open;

Warning: PDB altered with errors.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE YES

SQL> desc PDB_PLUG_IN_VIOLATIONS

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TIME                                      NOT NULL TIMESTAMP(6)

 NAME                                      NOT NULL VARCHAR2(30)

 CAUSE                                              VARCHAR2(22)

 TYPE                                               VARCHAR2(9)

 ERROR_NUMBER                                       NUMBER

 LINE                                      NOT NULL NUMBER

 MESSAGE                                   NOT NULL VARCHAR2(4000)

 STATUS                                             VARCHAR2(9)

 ACTION                                             VARCHAR2(4000)

SQL> select NAME,ERROR_NUMBER,MESSAGE,STATUS,ACTION from PDB_PLUG_IN_VIOLATIONS;

NAME  ERROR_NUMBER MESSAGE                                            STATUS    ACTION

----- ------------ -------------------------------------------------- --------- ------------------------------------------------------------

PDB1             0 CDB parameter sga_target mismatch: Previous 740M C RESOLVED  Please check the parameter in the current CDB

                   urrent 712M

PDB1             0 CDB parameter pga_aggregate_target mismatch: Previ RESOLVED  Please check the parameter in the current CDB

                   ous 245M Current 236M

PDB1             0 CDB parameter enable_pluggable_database mismatch:  RESOLVED  Please check the parameter in the current CDB

                   Previous FALSE Current TRUE

PDB1             0 PDB plugged in is a non-CDB, requires noncdb_to_pd RESOLVED  Run noncdb_to_pdb.sql.

                   b.sql be run.

PDB1             0 Database option RAC mismatch: PDB installed versio PENDING   Fix the database option in the PDB or CDB n 12.1.0.1.0. CDB installed version NULL.

PDB1             0 SQL patch 19769486 mismatch: Installed in the PDB  PENDING   Install the SQL patch in the PDB or the CDB.Please refer to

                   but not in the CDB.                                          the Known Issues section of MOS note Doc ID 1585822.1 for more details.

PDB1             0 SQL patch 20299016 mismatch: Installed in the PDB  PENDING   Install the SQL patch in the PDB or the CDB.Please refer to

                   but not in the CDB.                                          the Known Issues section of MOS note Doc ID 1585822.1 for more details.

PDB1             0 SQL patch 20831107 mismatch: Installed in the PDB  PENDING   Install the SQL patch in the PDB or the CDB.Please refer to

                   but not in the CDB.                                          the Known Issues section of MOS note Doc ID 1585822.1 for more details.

PDB1             0 SQL patch 21352619 mismatch: Installed in the PDB  PENDING   Install the SQL patch in the PDB or the CDB.Please refer to

                   but not in the CDB.                                          the Known Issues section of MOS note Doc ID 1585822.1 for more details.

PDB1             0 SQL patch 21951844 mismatch: Installed in the PDB  PENDING   Install the SQL patch in the PDB or the CDB.Please refer to

                   but not in the CDB.                                          the Known Issues section of MOS note Doc ID 1585822.1 for more details.

PDB1             0 SQL patch 22291141 mismatch: Installed in the PDB  PENDING   Install the SQL patch in the PDB or the CDB.Please refer to

                   but not in the CDB.                                          the Known Issues section of MOS note Doc ID 1585822.1 for more details.

11 rows selected.

SQL>

Convert NON CDB to CDB database Oracle 12c

Convert NON CDB to CDB database

Note 1:-We can’t convert NON CDB to CDB directly. Create a CDB database and plug NON CDB database as PDB to new CDB database.

Testdb1è NON CDB database

TESTCDB è CDB database.

PDB1 è Cloning of NON CDB database (We can use the same name of NON CDB database. But I used different name)

1)Mount NON-CDB db as read only.

SQL> startup  mount;

ORACLE instance started.

Total System Global Area  772472832 bytes

Fixed Size                  2855328 bytes

Variable Size             310382176 bytes

Database Buffers          452984832 bytes

Redo Buffers                6250496 bytes

Database mounted.

SQL>  ALTER DATABASE OPEN READ ONLY;

Database altered.

2)Create db description file

SQL> BEGIN

  2  DBMS_PDB.DESCRIBE(

  3  pdb_descr_file => '/home/oracle/ora12c.xml');

  4  END;

  5  /

PL/SQL procedure successfully completed.

SQL> ! ls -lrth /home/oracle/ora12c.xml

-rw-r--r-- 1 oracle dba 4.4K Apr 25 14:13 /home/oracle/ora12c.xml

3)Create new CDB database(TESTCDB)

 [oracle@rac1 dbs]$ ps -ef | grep pmon

oracle    3016     1  0 11:01 ?        00:00:01 asm_pmon_+ASM1

oracle    8346     1  0 11:14 ?        00:00:01 mdb_pmon_-MGMTDB

oracle   16164     1  0 14:03 ?        00:00:00 ora_pmon_testdb1

oracle   19226     1  0 14:55 ?        00:00:00 ora_pmon_TESTCDB

oracle   19410 12423  0 14:57 pts/0    00:00:00 grep pmon

4)Shutdown NON-CDB database.

5)Connect to new CDB database and clone the NON CDB db as PDB in new CDB db.

[oracle@rac1 dbs]$ . oraenv

ORACLE_SID = [testdb1] ? TESTCDB

[oracle@rac1 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Mon Apr 25 15:03:16 2016

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, Real Application Clusters, OLAP, Advanced Analytics

and Real Application Testing options

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME      OPEN_MODE

--------- --------------------

TESTCDB   READ WRITE

SQL> CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/ora12c.xml' COPY FILE_NAME_CONVERT = ('+DATA/PDB1/DATAFILE/', '+DATA/PDB1/DATAFILE/');

CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/ora12c.xml' COPY FILE_NAME_CONVERT = ('+DATA/PDB1/DATAFILE/', '+DATA/PDB1/DATAFILE/')

*

ERROR at line 1:

ORA-65005: missing or invalid file name pattern for file -

+DATA/TESTDB/DATAFILE/system.270.908822475

SQL>  CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/ora12c.xml' COPY FILE_NAME_CONVERT = ('+DATA/TESTDB/DATAFILE/', '+DATA/PDB1/DATAFILE/');

 CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/ora12c.xml' COPY FILE_NAME_CONVERT = ('+DATA/TESTDB/DATAFILE/', '+DATA/PDB1/DATAFILE/')

*

ERROR at line 1:

ORA-65005: missing or invalid file name pattern for file -

+DATA/TESTDB/DATAFILE/sysaux.269.908822371

SQL>CREATE PLUGGABLE DATABASE PDB1 USING

 '/home/oracle/ora12c.xml' COPY FILE_NAME_CONVERT = ('+DATA', '+DATA');

Pluggable database created.

SQL> SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

PDB1                           MOUNTED

6)Connect to new PDB and execute the script.This will take time.So,Better execute in the backend.

SQL> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

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

SQL> SET SERVEROUTPUT ON

SQL> SET FEEDBACK 1

SQL> SET NUMWIDTH 10

SQL> SET LINESIZE 80

SQL> SET TRIMSPOOL ON

SQL> SET TAB OFF

SQL> SET PAGESIZE 100

SQL>

SQL> WHENEVER SQLERROR EXIT;

SQL>

SQL> DOC

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01403: no data found"

DOC>   error if we're not in a PDB.

DOC>   This script is intended to be run right after plugin of a PDB,

DOC>   while inside the PDB.

DOC>#######################################################################

DOC>#######################################################################

"""SKIPPED THE BIG OUTPUT""

SQL>

SQL> -- leave the PDB in the same state it was when we started

SQL> 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>

SQL> WHENEVER SQLERROR CONTINUE;

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SQL>

SQL>

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 PDB1                           MOUNTED

7)Connect to CDB and open PDB

SQL> conn / as sysdba

Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           MOUNTED

SQL> alter pluggable database PDB1 open;

Warning: PDB altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE YES

SQL> show parameter ENABLE_PLUGGABLE_DATABASE

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

enable_pluggable_database            boolean     TRUE

Note:-The old NON-CDB database need to delete manually