Announcement

Collapse

http://progeeking.com

See more
See less

Database Migration/Upgrade from 12.1.0.1 (non-CDB) to 12.1.0.2 as (PDB)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database Migration/Upgrade from 12.1.0.1 (non-CDB) to 12.1.0.2 as (PDB)

    Database Migration/Upgrade from 12.1.0.1 (non-CDB) to 12.1.0.2 as (PDB)

    Concept of my upgrade is following:

    Server FX3-1 - holding 12c database ADMIN create as non-CDB with version 12.1.0.1
    Server FX3-2 - holding 12c database ADMIN create as CDB with version 12.1.0.2

    My idea for migration is to copy all data files from non-CDB and create as PDB.

    Migration:

    1.

    [FX3-1@localhost ~]$ sqlplus sys/need2change as sysdba

    SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 1 11:38:56 2015

    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> SHUTDOWN IMMEDIATE;

    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    SQL> startup open read only
    ORACLE instance started.

    Total System Global Area 576221184 bytes
    Fixed Size 2290896 bytes
    Variable Size 289409840 bytes
    Database Buffers 276824064 bytes
    Redo Buffers 7696384 bytes
    Database mounted.
    Database opened.


    SQL> BEGIN
    DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/tmp/ADMIN.xml');
    END;
    /
    2 3 4 5

    PL/SQL procedure successfully completed.

    SQL>

    --- my datafiles
    [FX-1@localhost ~]$ ls -la /oradata/admin/datafile/
    total 2389752
    drwxr-x--- 2 arbor arbor 4096 Mar 20 14:43 .
    drwxr-x--- 5 arbor arbor 97 Mar 20 16:17 ..
    -rw-r----- 1 arbor arbor 1174413312 Apr 1 11:43 o1_mf_sysaux_bjr55vbb_.dbf
    -rw-r----- 1 arbor arbor 859840512 Apr 1 11:43 o1_mf_system_bjr58lpg_.dbf
    -rw-r----- 1 arbor arbor 253763584 Apr 1 06:23 o1_mf_temp_bjr5j29j_.tmp
    -rw-r----- 1 arbor arbor 162537472 Apr 1 11:43 o1_mf_undotbs1_bjr5cr8w_.dbf
    -rw-r----- 1 arbor arbor 5251072 Apr 1 11:43 o1_mf_users_bjr5cncr_.dbf
    -rw-r----- 1 arbor arbor 131080192 Apr 1 11:43 arbor001.dbf
    -rw-r----- 1 arbor arbor 104865792 Apr 1 11:43 arbor_idx001.dbf


    1. Copy all datafiles from server FX3-1 to FX3-2
    scp o1_mf*.* root@FX3-2:/oradata/

    As you can see my files are creates with Oracle Managed Files


    [oracle@fx3-2 ~]$ export ORACLE_SID=CDBFX3
    [oracle@fx3-2 ~]$ sqlplus /nolog

    SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 1 13:22:54 2015

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

    SQL> conn / as sysdba
    Connected.

    ----- option 1 ---- create PDB with FILE_NAME_CONVERT if necessary

    CREATE PLUGGABLE DATABASE ADMIN USING '/oradata/admin.xml'
    COPY FILE_NAME_CONVERT = ('/oradata/admin/datafile/', '/oradata/CDBFX3/ADMIN/');

    ----- option 2 - create PDB without FILE_NAME_CONVERT by simply modify path in the xml

    CREATE PLUGGABLE DATABASE ADMIN USING '/oradata/admin.xml' NOCOPY ;

    SQL> CREATE PLUGGABLE DATABASE ADMIN USING '/oradata/admin.xml'
    COPY FILE_NAME_CONVERT = ('/oradata/admin/datafile/', '/oradata/CDBFX3/ADMIN/'); 2
    CREATE PLUGGABLE DATABASE ADMIN USING '/oradata/admin.xml'
    *
    ERROR at line 1:
    ORA-01276: Cannot add file /oradata/CDBFX3/ADMIN/o1_mf_system_bjr58lpg_.dbf.
    File has an Oracle Managed Files file name.
    SQL>

    ---- in order to escape from this error you have to change xml -> path - > datafile file name and outsource datafile name where was placed

    old name -> o1_mf_system_bjr58lpg_.dbf

    e.g. mv o1_mf_system_bjr58lpg_.dbf system.dbf - you have to do this for all datafiles with OMF

    new name -> system.dbf


    /oradata/CDBFX3/ADMIN/system.dbf
    1
    1
    7
    0
    1
    104960
    8192
    202375168
    708229054
    0
    3050141
    0
    1720082
    874852990


    --- save xml

    --- create PDB
    SQL> CREATE PLUGGABLE DATABASE ADMIN USING '/oradata/admin.xml' NOCOPY ;
    Pluggable database created.


    SQL> alter session set container=CDB$ROOT;
    Session altered.

    SQL>

    after creation you have to validate that pdb was plugged correctly and perform necessary post operation tasks

    SQL> select message, status from pdb_plug_in_violations where type like '%ERR%';

    MESSAGE
    --------------------------------------------------------------------------------
    STATUS
    ---------
    PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
    PENDING

    SQL>

    SQL> select name, open_mode from v$pdbs;

    NAME OPEN_MODE
    ------------------------------ ----------
    PDB$SEED READ ONLY
    ADMIN MOUNTED
    CUST1 MOUNTED
    CTLG MOUNTED

    SQL> alter session set container=ADMIN;

    Session altered.

    ---- execute script as per the above feedback from validation

    SQL> @?/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>################################################## #####################
    DOC>#
    SQL>
    SQL> VARIABLE cdbname VARCHAR2(128)
    SQL> VARIABLE pdbname VARCHAR2(128)
    SQL> BEGIN
    2 SELECT sys_context('USERENV', 'CDB_NAME')
    3 INTO :cdbname
    4 FROM dual
    5 WHERE sys_context('USERENV', 'CDB_NAME') is not null;
    6 SELECT sys_context('USERENV', 'CON_NAME')
    7 INTO dbname
    8 FROM dual
    9 WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
    10 END;
    11 /

    PL/SQL procedure successfully completed.

    SQL>
    SQL> COLUMN pdbname NEW_VALUE pdbname
    SQL> COLUMN pdbid NEW_VALUE pdbid
    SQL>
    SQL> select dbname pdbname from dual;

    PDBNAME
    --------------------------------------------------------------------------------
    ADMIN

    1 row selected.

    SQL>
    SQL> select TO_CHAR(con_id) pdbid from v$pdbs where name='&pdbname';
    old 1: select TO_CHAR(con_id) pdbid from v$pdbs where name='&pdbname'
    new 1: select TO_CHAR(con_id) pdbid from v$pdbs where name='ADMIN'

    PDBID
    ----------------------------------------
    3

    1 row selected.

    SQL>
    SQL> -- save pluggable database open mode
    SQL> COLUMN open_state_col NEW_VALUE open_sql;
    SQL> COLUMN restricted_col NEW_VALUE restricted_state;
    SQL> SELECT decode(open_mode,
    2 'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
    3 'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN',
    4 'MIGRATE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN UPGRADE', '')
    5 open_state_col,
    6 decode(restricted, 'YES', 'RESTRICTED', '')
    7 restricted_col
    8 from v$pdbs where name='&pdbname';
    old 2: 'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
    new 2: 'READ ONLY', 'ALTER PLUGGABLE DATABASE ADMIN OPEN READ ONLY',
    old 3: 'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN',
    new 3: 'READ WRITE', 'ALTER PLUGGABLE DATABASE ADMIN OPEN',
    old 4: 'MIGRATE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN UPGRADE', '')
    new 4: 'MIGRATE', 'ALTER PLUGGABLE DATABASE ADMIN OPEN UPGRADE', '')
    old 8: from v$pdbs where name='&pdbname'
    new 8: from v$pdbs where name='ADMIN'

    OPEN_STATE_COL RESTRICTED
    --------------------------------------------- ----------


    1 row selected.

    SQL>
    SQL> -- if pdb was already closed, don't exit on error
    SQL> WHENEVER SQLERROR CONTINUE;
    SQL>
    SQL> alter pluggable database "&pdbname" close IMMEDIATE instances = all;
    old 1: alter pluggable database "&pdbname" close IMMEDIATE instances = all
    new 1: alter pluggable database "ADMIN" close IMMEDIATE instances = all
    alter pluggable database "ADMIN" close IMMEDIATE instances = all
    *
    ERROR at line 1:
    ORA-65020: pluggable database ADMIN already closed


    SQL>
    SQL> WHENEVER SQLERROR EXIT;
    SQL>
    SQL> alter session set container = CDB$ROOT;

    Session altered.

    SQL> alter system flush shared_pool;

    System altered.

    SQL> /

    System altered.

    SQL> /

    System altered.

    SQL> alter session set container = "&pdbname";
    old 1: alter session set container = "&pdbname"
    new 1: alter session set container = "ADMIN"

    Session altered.

    SQL>
    SQL> alter pluggable database "&pdbname" open upgrade;
    old 1: alter pluggable database "&pdbname" open upgrade
    new 1: alter pluggable database "ADMIN" open upgrade

    Warning: PDB altered with errors.

    SQL>
    SQL> -- initial setup before beginning the script
    SQL> alter session set "_ORACLE_SCRIPT"=true;

    Session altered.

    SQL> alter session set NLS_LENGTH_SEMANTICS=BYTE;

    Session altered.

    SQL> exec dbms_pdb.noncdb_to_pdb(1);
    BEGIN dbms_pdb.noncdb_to_pdb(1); END;

    *
    ERROR at line 1:
    ORA-65166: cannot run noncdb_to_pdb.sql if PDB's version differs from CDB's
    ORA-06512: at "SYS.DBMS_PDB", line 190
    ORA-06512: at line 1


    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    [oracle@fx3-2 ~]$

    As you can see execution of -> exec dbms_pdb.noncdb_to_pdb(1); doesn't succeed

    Also update pdb_plug_in_violations

    SQL> select message, status from pdb_plug_in_violations where type like '%ERR%';

    MESSAGE
    --------------------------------------------------------------------------------
    STATUS
    ---------
    PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
    PENDING

    PDB's version does not match CDB's version: PDB's version 12.1.0.1.0. CDB's vers
    ion 12.1.0.2.0.
    PENDING

    SQL>select pdb_name, status from dba_pdbs;

    PDB_NAME STATUS
    ------------------------- ---------
    PDB$SEED NORMAL
    ADMIN NEW

    ---- in order to upgrade ADMIN pdb you have to perform following operation

    [oracle@fx3-2 ~]$ /app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catctl.pl -d /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/ -l /app/oracle/ -c 'ADMIN' catupgrd.sql

    Argument list for [/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catctl.pl]
    SQL Process Count n = 0
    SQL PDB Process Count N = 0
    Input Directory d = /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/
    Phase Logging Table t = 0
    Log Dir l = /app/oracle/
    Script s = 0
    Serial Run S = 0
    Upgrade Mode active M = 0
    Start Phase p = 0
    End Phase P = 0
    Log Id i = 0
    Run in c = ADMIN
    Do not run in C = 0
    Echo OFF e = 1
    No Post Upgrade x = 0
    Reverse Order r = 0
    Open Mode Normal o = 0
    Debug catcon.pm z = 0
    Debug catctl.pl Z = 0
    Display Phases y = 0
    Child Process I = 0

    catctl.pl version: 12.1.0.2.0
    Oracle Home = /app/oracle/product/12.1.0.2/dbhome_1

    Analyzing file /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catupgrd.sql
    Log files in /app/oracle/
    catcon: ALL catcon-related output will be written to /app/oracle/catupgrd_catcon_32722.lst
    catcon: See /app/oracle//catupgrd*.log files for output generated by scripts
    catcon: See /app/oracle//catupgrd_*.lst files for spool files, if any
    Number of Cpus = 2
    Parallel PDB Upgrades = 2
    SQL PDB Process Count = 2
    SQL Process Count = 0
    New SQL Process Count = 2

    [CONTAINER NAMES]

    CDB$ROOT
    PDB$SEED
    ADMIN
    CUST1
    CTLG
    PDB Inclusion:[ADMIN] Exclusion:[]

    Starting
    [/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catctl.pl -d /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/ -l /app/oracle/ -c 'ADMIN' -I -i admin -n 2 catupgrd.sql]

    Argument list for [/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catctl.pl]
    SQL Process Count n = 2
    SQL PDB Process Count N = 0
    Input Directory d = /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/
    Phase Logging Table t = 0
    Log Dir l = /app/oracle/
    Script s = 0
    Serial Run S = 0
    Upgrade Mode active M = 0
    Start Phase p = 0
    End Phase P = 0
    Log Id i = admin
    Run in c = ADMIN
    Do not run in C = 0
    Echo OFF e = 1
    No Post Upgrade x = 0
    Reverse Order r = 0
    Open Mode Normal o = 0
    Debug catcon.pm z = 0
    Debug catctl.pl Z = 0
    Display Phases y = 0
    Child Process I = 1

    catctl.pl version: 12.1.0.2.0
    Oracle Home = /app/oracle/product/12.1.0.2/dbhome_1

    Analyzing file /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin//catupgrd.sql
    Log files in /app/oracle/
    catcon: ALL catcon-related output will be written to /app/oracle//catupgrdadmin_
    catcon: See /app/oracle//catupgrdadmin*.log files for output generated by script
    catcon: See /app/oracle//catupgrdadmin_*.lst files for spool files, if any
    Number of Cpus = 2
    SQL PDB Process Count = 2
    SQL Process Count = 2

    [CONTAINER NAMES]

    CDB$ROOT
    PDB$SEED
    ADMIN
    CUST1
    CTLG
    PDB Inclusion:[ADMIN] Exclusion:[]

    ------------------------------------------------------
    Phases [0-73]
    Container Lists Inclusion:[ADMIN] Exclusion:[]
    Serial Phase #: 0 Files: 1 Time: 90s ADMIN
    Serial Phase #: 1 Files: 5 Time: 318s ADMIN
    Restart Phase #: 2 Files: 1 Time: 1s ADMIN
    Parallel Phase #: 3 Files: 18 Time: 160s ADMIN
    Restart Phase #: 4 Files: 1 Time: 0s ADMIN
    Serial Phase #: 5 Files: 5 Time: 177s ADMIN
    Serial Phase #: 6 Files: 1 Time: 42s ADMIN
    Serial Phase #: 7 Files: 4 Time: 43s ADMIN
    Restart Phase #: 8 Files: 1 Time: 0s ADMIN
    Parallel Phase #: 9 Files: 62 Time: 395s ADMIN
    Restart Phase #:10 Files: 1 Time: 0s ADMIN
    Serial Phase #:11 Files: 1 Time: 150s ADMIN
    Restart Phase #:12 Files: 1 Time: 0s ADMIN
    Parallel Phase #:13 Files: 91 Time: 45s ADMIN
    Restart Phase #:14 Files: 1 Time: 0s ADMIN
    Parallel Phase #:15 Files: 111 Time: 84s ADMIN
    Restart Phase #:16 Files: 1 Time: 0s ADMIN
    Serial Phase #:17 Files: 3 Time: 5s ADMIN
    Restart Phase #:18 Files: 1 Time: 0s ADMIN
    Parallel Phase #:19 Files: 32 Time: 214s ADMIN
    Restart Phase #:20 Files: 1 Time: 0s ADMIN
    Serial Phase #:21 Files: 3 Time: 50s ADMIN
    Restart Phase #:22 Files: 1 Time: 0s ADMIN
    Parallel Phase #:23 Files: 23 Time: 366s ADMIN
    Restart Phase #:24 Files: 1 Time: 1s ADMIN
    Parallel Phase #:25 Files: 11 Time: 111s ADMIN
    Restart Phase #:26 Files: 1 Time: 0s ADMIN
    Serial Phase #:27 Files: 1 Time: 0s ADMIN
    Restart Phase #:28 Files: 1 Time: 0s ADMIN
    Serial Phase #:30 Files: 1 Time: 0s ADMIN
    Serial Phase #:31 Files: 257 Time: 92s ADMIN
    Serial Phase #:32 Files: 1 Time: 0s ADMIN
    Restart Phase #:33 Files: 1 Time: 0s ADMIN
    Serial Phase #:34 Files: 1 Time: 13s ADMIN
    Restart Phase #:35 Files: 1 Time: 0s ADMIN
    Restart Phase #:36 Files: 1 Time: 0s ADMIN
    Serial Phase #:37 Files: 4 Time: 239s ADMIN
    Restart Phase #:38 Files: 1 Time: 0s ADMIN
    Parallel Phase #:39 Files: 13 Time: 155s ADMIN
    Restart Phase #:40 Files: 1 Time: 0s ADMIN
    Parallel Phase #:41 Files: 10 Time: 35s ADMIN
    Restart Phase #:42 Files: 1 Time: 0s ADMIN
    Serial Phase #:43 Files: 1 Time: 31s ADMIN
    Restart Phase #:44 Files: 1 Time: 0s ADMIN
    Serial Phase #:45 Files: 1 Time: 5s ADMIN
    Serial Phase #:46 Files: 1 Time: 0s ADMIN
    Restart Phase #:47 Files: 1 Time: 0s ADMIN

  • #2
    Originally posted by jackomito View Post
    Database Migration/Upgrade from 12.1.0.1 (non-CDB) to 12.1.0.2 as (PDB)

    Concept of my upgrade is following:

    Server FX3-1 - holding 12c database ADMIN create as non-CDB with version 12.1.0.1
    Server FX3-2 - holding 12c database ADMIN create as CDB with version 12.1.0.2

    My idea for migration is to copy all data files from non-CDB and create as PDB.

    Migration:

    1.

    [FX3-1@localhost ~]$ sqlplus sys/need2change as sysdba

    SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 1 11:38:56 2015

    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> SHUTDOWN IMMEDIATE;

    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    SQL> startup open read only
    ORACLE instance started.

    Total System Global Area 576221184 bytes
    Fixed Size 2290896 bytes
    Variable Size 289409840 bytes
    Database Buffers 276824064 bytes
    Redo Buffers 7696384 bytes
    Database mounted.
    Database opened.


    SQL> BEGIN
    DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/tmp/ADMIN.xml');
    END;
    /
    2 3 4 5

    PL/SQL procedure successfully completed.

    SQL>

    --- my datafiles
    [FX-1@localhost ~]$ ls -la /oradata/admin/datafile/
    total 2389752
    drwxr-x--- 2 arbor arbor 4096 Mar 20 14:43 .
    drwxr-x--- 5 arbor arbor 97 Mar 20 16:17 ..
    -rw-r----- 1 arbor arbor 1174413312 Apr 1 11:43 o1_mf_sysaux_bjr55vbb_.dbf
    -rw-r----- 1 arbor arbor 859840512 Apr 1 11:43 o1_mf_system_bjr58lpg_.dbf
    -rw-r----- 1 arbor arbor 253763584 Apr 1 06:23 o1_mf_temp_bjr5j29j_.tmp
    -rw-r----- 1 arbor arbor 162537472 Apr 1 11:43 o1_mf_undotbs1_bjr5cr8w_.dbf
    -rw-r----- 1 arbor arbor 5251072 Apr 1 11:43 o1_mf_users_bjr5cncr_.dbf
    -rw-r----- 1 arbor arbor 131080192 Apr 1 11:43 arbor001.dbf
    -rw-r----- 1 arbor arbor 104865792 Apr 1 11:43 arbor_idx001.dbf


    1. Copy all datafiles from server FX3-1 to FX3-2
    scp o1_mf*.* root@FX3-2:/oradata/

    As you can see my files are creates with Oracle Managed Files


    [oracle@fx3-2 ~]$ export ORACLE_SID=CDBFX3
    [oracle@fx3-2 ~]$ sqlplus /nolog

    SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 1 13:22:54 2015

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

    SQL> conn / as sysdba
    Connected.

    ----- option 1 ---- create PDB with FILE_NAME_CONVERT if necessary

    CREATE PLUGGABLE DATABASE ADMIN USING '/oradata/admin.xml'
    COPY FILE_NAME_CONVERT = ('/oradata/admin/datafile/', '/oradata/CDBFX3/ADMIN/');

    ----- option 2 - create PDB without FILE_NAME_CONVERT by simply modify path in the xml

    CREATE PLUGGABLE DATABASE ADMIN USING '/oradata/admin.xml' NOCOPY ;

    SQL> CREATE PLUGGABLE DATABASE ADMIN USING '/oradata/admin.xml'
    COPY FILE_NAME_CONVERT = ('/oradata/admin/datafile/', '/oradata/CDBFX3/ADMIN/'); 2
    CREATE PLUGGABLE DATABASE ADMIN USING '/oradata/admin.xml'
    *
    ERROR at line 1:
    ORA-01276: Cannot add file /oradata/CDBFX3/ADMIN/o1_mf_system_bjr58lpg_.dbf.
    File has an Oracle Managed Files file name.
    SQL>

    ---- in order to escape from this error you have to change xml -> path - > datafile file name and outsource datafile name where was placed

    old name -> o1_mf_system_bjr58lpg_.dbf

    e.g. mv o1_mf_system_bjr58lpg_.dbf system.dbf - you have to do this for all datafiles with OMF

    new name -> system.dbf

    <file>
    <path>/oradata/CDBFX3/ADMIN/system.dbf</path>
    <afn>1</afn>
    <rfn>1</rfn>
    <createscnbas>7</createscnbas>
    <createscnwrp>0</createscnwrp>
    <status>1</status>
    <fileblocks>104960</fileblocks>
    <blocksize>8192</blocksize>
    <vsn>202375168</vsn>
    <fdbid>708229054</fdbid>
    <fcpsw>0</fcpsw>
    <fcpsb>3050141</fcpsb>
    <frlsw>0</frlsw>
    <frlsb>1720082</frlsb>
    <frlt>874852990</frlt>
    </file>

    --- save xml

    --- create PDB
    SQL> CREATE PLUGGABLE DATABASE ADMIN USING '/oradata/admin.xml' NOCOPY ;
    Pluggable database created.


    SQL> alter session set container=CDB$ROOT;
    Session altered.

    SQL>

    after creation you have to validate that pdb was plugged correctly and perform necessary post operation tasks

    SQL> select message, status from pdb_plug_in_violations where type like '%ERR%';

    MESSAGE
    --------------------------------------------------------------------------------
    STATUS
    ---------
    PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
    PENDING

    SQL>

    SQL> select name, open_mode from v$pdbs;

    NAME OPEN_MODE
    ------------------------------ ----------
    PDB$SEED READ ONLY
    ADMIN MOUNTED
    CUST1 MOUNTED
    CTLG MOUNTED

    SQL> alter session set container=ADMIN;

    Session altered.

    ---- execute script as per the above feedback from validation

    SQL> @?/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>############################################## #########################
    DOC>#
    SQL>
    SQL> VARIABLE cdbname VARCHAR2(128)
    SQL> VARIABLE pdbname VARCHAR2(128)
    SQL> BEGIN
    2 SELECT sys_context('USERENV', 'CDB_NAME')
    3 INTO :cdbname
    4 FROM dual
    5 WHERE sys_context('USERENV', 'CDB_NAME') is not null;
    6 SELECT sys_context('USERENV', 'CON_NAME')
    7 INTO dbname
    8 FROM dual
    9 WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
    10 END;
    11 /

    PL/SQL procedure successfully completed.

    SQL>
    SQL> COLUMN pdbname NEW_VALUE pdbname
    SQL> COLUMN pdbid NEW_VALUE pdbid
    SQL>
    SQL> select dbname pdbname from dual;

    PDBNAME
    --------------------------------------------------------------------------------
    ADMIN

    1 row selected.

    SQL>
    SQL> select TO_CHAR(con_id) pdbid from v$pdbs where name='&pdbname';
    old 1: select TO_CHAR(con_id) pdbid from v$pdbs where name='&pdbname'
    new 1: select TO_CHAR(con_id) pdbid from v$pdbs where name='ADMIN'

    PDBID
    ----------------------------------------
    3

    1 row selected.

    SQL>
    SQL> -- save pluggable database open mode
    SQL> COLUMN open_state_col NEW_VALUE open_sql;
    SQL> COLUMN restricted_col NEW_VALUE restricted_state;
    SQL> SELECT decode(open_mode,
    2 'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
    3 'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN',
    4 'MIGRATE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN UPGRADE', '')
    5 open_state_col,
    6 decode(restricted, 'YES', 'RESTRICTED', '')
    7 restricted_col
    8 from v$pdbs where name='&pdbname';
    old 2: 'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
    new 2: 'READ ONLY', 'ALTER PLUGGABLE DATABASE ADMIN OPEN READ ONLY',
    old 3: 'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN',
    new 3: 'READ WRITE', 'ALTER PLUGGABLE DATABASE ADMIN OPEN',
    old 4: 'MIGRATE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN UPGRADE', '')
    new 4: 'MIGRATE', 'ALTER PLUGGABLE DATABASE ADMIN OPEN UPGRADE', '')
    old 8: from v$pdbs where name='&pdbname'
    new 8: from v$pdbs where name='ADMIN'

    OPEN_STATE_COL RESTRICTED
    --------------------------------------------- ----------


    1 row selected.

    SQL>
    SQL> -- if pdb was already closed, don't exit on error
    SQL> WHENEVER SQLERROR CONTINUE;
    SQL>
    SQL> alter pluggable database "&pdbname" close IMMEDIATE instances = all;
    old 1: alter pluggable database "&pdbname" close IMMEDIATE instances = all
    new 1: alter pluggable database "ADMIN" close IMMEDIATE instances = all
    alter pluggable database "ADMIN" close IMMEDIATE instances = all
    *
    ERROR at line 1:
    ORA-65020: pluggable database ADMIN already closed


    SQL>
    SQL> WHENEVER SQLERROR EXIT;
    SQL>
    SQL> alter session set container = CDB$ROOT;

    Session altered.

    SQL> alter system flush shared_pool;

    System altered.

    SQL> /

    System altered.

    SQL> /

    System altered.

    SQL> alter session set container = "&pdbname";
    old 1: alter session set container = "&pdbname"
    new 1: alter session set container = "ADMIN"

    Session altered.

    SQL>
    SQL> alter pluggable database "&pdbname" open upgrade;
    old 1: alter pluggable database "&pdbname" open upgrade
    new 1: alter pluggable database "ADMIN" open upgrade

    Warning: PDB altered with errors.

    SQL>
    SQL> -- initial setup before beginning the script
    SQL> alter session set "_ORACLE_SCRIPT"=true;

    Session altered.

    SQL> alter session set NLS_LENGTH_SEMANTICS=BYTE;

    Session altered.

    SQL> exec dbms_pdb.noncdb_to_pdb(1);
    BEGIN dbms_pdb.noncdb_to_pdb(1); END;

    *
    ERROR at line 1:
    ORA-65166: cannot run noncdb_to_pdb.sql if PDB's version differs from CDB's
    ORA-06512: at "SYS.DBMS_PDB", line 190
    ORA-06512: at line 1


    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    [oracle@fx3-2 ~]$

    As you can see execution of -> exec dbms_pdb.noncdb_to_pdb(1); doesn't succeed

    Also update pdb_plug_in_violations

    SQL> select message, status from pdb_plug_in_violations where type like '%ERR%';

    MESSAGE
    --------------------------------------------------------------------------------
    STATUS
    ---------
    PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
    PENDING

    PDB's version does not match CDB's version: PDB's version 12.1.0.1.0. CDB's vers
    ion 12.1.0.2.0.
    PENDING

    SQL>select pdb_name, status from dba_pdbs;

    PDB_NAME STATUS
    ------------------------- ---------
    PDB$SEED NORMAL
    ADMIN NEW

    ---- in order to upgrade ADMIN pdb you have to perform following operation

    [oracle@fx3-2 ~]$ /app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catctl.pl -d /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/ -l /app/oracle/ -c 'ADMIN' catupgrd.sql

    Argument list for [/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catctl.pl]
    SQL Process Count n = 0
    SQL PDB Process Count N = 0
    Input Directory d = /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/
    Phase Logging Table t = 0
    Log Dir l = /app/oracle/
    Script s = 0
    Serial Run S = 0
    Upgrade Mode active M = 0
    Start Phase p = 0
    End Phase P = 0
    Log Id i = 0
    Run in c = ADMIN
    Do not run in C = 0
    Echo OFF e = 1
    No Post Upgrade x = 0
    Reverse Order r = 0
    Open Mode Normal o = 0
    Debug catcon.pm z = 0
    Debug catctl.pl Z = 0
    Display Phases y = 0
    Child Process I = 0

    catctl.pl version: 12.1.0.2.0
    Oracle Home = /app/oracle/product/12.1.0.2/dbhome_1

    Analyzing file /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catupgrd.sql
    Log files in /app/oracle/
    catcon: ALL catcon-related output will be written to /app/oracle/catupgrd_catcon_32722.lst
    catcon: See /app/oracle//catupgrd*.log files for output generated by scripts
    catcon: See /app/oracle//catupgrd_*.lst files for spool files, if any
    Number of Cpus = 2
    Parallel PDB Upgrades = 2
    SQL PDB Process Count = 2
    SQL Process Count = 0
    New SQL Process Count = 2

    [CONTAINER NAMES]

    CDB$ROOT
    PDB$SEED
    ADMIN
    CUST1
    CTLG
    PDB Inclusion:[ADMIN] Exclusion:[]

    Starting
    [/app/oracle/product/12.1.0.2/dbhome_1/perl/bin/perl /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catctl.pl -d /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/ -l /app/oracle/ -c 'ADMIN' -I -i admin -n 2 catupgrd.sql]

    Argument list for [/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catctl.pl]
    SQL Process Count n = 2
    SQL PDB Process Count N = 0
    Input Directory d = /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/
    Phase Logging Table t = 0
    Log Dir l = /app/oracle/
    Script s = 0
    Serial Run S = 0
    Upgrade Mode active M = 0
    Start Phase p = 0
    End Phase P = 0
    Log Id i = admin
    Run in c = ADMIN
    Do not run in C = 0
    Echo OFF e = 1
    No Post Upgrade x = 0
    Reverse Order r = 0
    Open Mode Normal o = 0
    Debug catcon.pm z = 0
    Debug catctl.pl Z = 0
    Display Phases y = 0
    Child Process I = 1

    catctl.pl version: 12.1.0.2.0
    Oracle Home = /app/oracle/product/12.1.0.2/dbhome_1

    Analyzing file /app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin//catupgrd.sql
    Log files in /app/oracle/
    catcon: ALL catcon-related output will be written to /app/oracle//catupgrdadmin_
    catcon: See /app/oracle//catupgrdadmin*.log files for output generated by script
    catcon: See /app/oracle//catupgrdadmin_*.lst files for spool files, if any
    Number of Cpus = 2
    SQL PDB Process Count = 2
    SQL Process Count = 2

    [CONTAINER NAMES]

    CDB$ROOT
    PDB$SEED
    ADMIN
    CUST1
    CTLG
    PDB Inclusion:[ADMIN] Exclusion:[]

    ------------------------------------------------------
    Phases [0-73]
    Container Lists Inclusion:[ADMIN] Exclusion:[]
    Serial Phase #: 0 Files: 1 Time: 90s ADMIN
    Serial Phase #: 1 Files: 5 Time: 318s ADMIN
    Restart Phase #: 2 Files: 1 Time: 1s ADMIN
    Parallel Phase #: 3 Files: 18 Time: 160s ADMIN
    Restart Phase #: 4 Files: 1 Time: 0s ADMIN
    Serial Phase #: 5 Files: 5 Time: 177s ADMIN
    Serial Phase #: 6 Files: 1 Time: 42s ADMIN
    Serial Phase #: 7 Files: 4 Time: 43s ADMIN
    Restart Phase #: 8 Files: 1 Time: 0s ADMIN
    Parallel Phase #: 9 Files: 62 Time: 395s ADMIN
    Restart Phase #:10 Files: 1 Time: 0s ADMIN
    Serial Phase #:11 Files: 1 Time: 150s ADMIN
    Restart Phase #:12 Files: 1 Time: 0s ADMIN
    Parallel Phase #:13 Files: 91 Time: 45s ADMIN
    Restart Phase #:14 Files: 1 Time: 0s ADMIN
    Parallel Phase #:15 Files: 111 Time: 84s ADMIN
    Restart Phase #:16 Files: 1 Time: 0s ADMIN
    Serial Phase #:17 Files: 3 Time: 5s ADMIN
    Restart Phase #:18 Files: 1 Time: 0s ADMIN
    Parallel Phase #:19 Files: 32 Time: 214s ADMIN
    Restart Phase #:20 Files: 1 Time: 0s ADMIN
    Serial Phase #:21 Files: 3 Time: 50s ADMIN
    Restart Phase #:22 Files: 1 Time: 0s ADMIN
    Parallel Phase #:23 Files: 23 Time: 366s ADMIN
    Restart Phase #:24 Files: 1 Time: 1s ADMIN
    Parallel Phase #:25 Files: 11 Time: 111s ADMIN
    Restart Phase #:26 Files: 1 Time: 0s ADMIN
    Serial Phase #:27 Files: 1 Time: 0s ADMIN
    Restart Phase #:28 Files: 1 Time: 0s ADMIN
    Serial Phase #:30 Files: 1 Time: 0s ADMIN
    Serial Phase #:31 Files: 257 Time: 92s ADMIN
    Serial Phase #:32 Files: 1 Time: 0s ADMIN
    Restart Phase #:33 Files: 1 Time: 0s ADMIN
    Serial Phase #:34 Files: 1 Time: 13s ADMIN
    Restart Phase #:35 Files: 1 Time: 0s ADMIN
    Restart Phase #:36 Files: 1 Time: 0s ADMIN
    Serial Phase #:37 Files: 4 Time: 239s ADMIN
    Restart Phase #:38 Files: 1 Time: 0s ADMIN
    Parallel Phase #:39 Files: 13 Time: 155s ADMIN
    Restart Phase #:40 Files: 1 Time: 0s ADMIN
    Parallel Phase #:41 Files: 10 Time: 35s ADMIN
    Restart Phase #:42 Files: 1 Time: 0s ADMIN
    Serial Phase #:43 Files: 1 Time: 31s ADMIN
    Restart Phase #:44 Files: 1 Time: 0s ADMIN
    Serial Phase #:45 Files: 1 Time: 5s ADMIN
    Serial Phase #:46 Files: 1 Time: 0s ADMIN
    Restart Phase #:47 Files: 1 Time: 0s ADMIN
    Hi!
    Thank for your post, its work for me.
    But after all steps, my pdb (from non cdb) is restricted. I can't change this mode by ' DISABLE RESTRICTED '.
    Can you help me ?

    Comment


    • #3
      --- check status of pdb
      select * from v$pdbs;

      --- run the below
      alter pluggable database "&pdbname" close;
      alter pluggable database "&pdbname" open;

      --- check status of pdb
      select * from v$pdbs;

      Please can you add output of the above commands?

      Comment


      • #4
        The prerequisites for cloning a remote PDB or non-CDB are very similar, so I will deal with them together.
        • he user in the local database must have the CREATE PLUGGABLE DATABASE privilege in the root container.
        • The remote database (PDB or non-CDB) must be open in read-only mode.
        • The local database must have a database link to the remote database. If the remote database is a PDB, the database link can point to the remote CDB using a common user, or the PDB using a local or common user.
        • The user in the remote database that the database link connects to must have the CREATE PLUGGABLE DATABASEprivilege.
        • The local and remote databases must have the same endianness, options installed and character sets.
        • If the remote database uses Transparent Data Encryption (TDE) the local CDB must be configured appropriately before attempting the clone. If not you will be left with a new PDB that will only open in restricted mode.
        • The default tablespaces for each common user in the remote PDB *must* exist in local CDB. If this is not true, create the missing tablespaces in the root container of the local PDB. If you don't do this your new PDB will only be able to open in restricted mode (Bug 19174942).
        • When cloning from a non-CDB, both the the local and remote databases must using version 12.1.0.2 or higher.
        In the examples below I have three databases running on the same virtual machine, but they could be running on separate physical or virtual servers.
        • cdb1 : The local database that will eventually house the clones.
        • db12c : The remote non-CDB.
        • cdb3 : The remote CDB, used for cloning a remote PDB (pdb5).

        Comment

        Working...
        X