Oracle Certified Master's - Blogs: Converting Non CDB to PDB

Oracle 12c

Converting Non CDB to PDB

After upgrade from 11.2.0.4.6 to 12.1.0.1, we are planning to plug into cdb.

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES

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

24-AUG-13 12.03.45.119862 PM APPLY SERVER 11.2.0.4 0 Patchset 11.2.0.2.0 PSU

15-AUG-15 01.47.26.575232 AM APPLY SERVER 11.2.0.4 6 PSU 11.2.0.4.6 PSU

15-AUG-15 02.35.21.118449 AM VIEW 8289601 view invalidation

INVALIDATE

15-AUG-15 02.37.41.906179 AM UPGRADE SERVER 12.1.0.1.0 Upgraded from 11.2.0.4.0

15-AUG-15 02.43.12.703450 AM APPLY SERVER 12.1.0.1 0 Patchset 12.1.0.0.0 PSU

Checking CDB and Existing PDB’s database

[oracle@vm211 ~]$ . oraenv

ORACLE_SID = [oracle] ? NOKIA

The Oracle base has been set to /u01/app/oracle

[oracle@vm211 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 15 04:48:24 2015

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> show con_name

CON_NAME

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

CDB$ROOT

SQL> col open_time for a14

SQL> col guid for a10

SQL> col name for a10

SQL> set lin200 pages 200

SQL> select * from v$pdbs ;

CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE

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

2 4130373951 4130373951 1D4DC0EF22 PDB$SEED READ ONLY NO 15-AUG-15 03.5 1720762 283115520

6E1AB2E053 1.39.833 AM

D301A8C0CC

3 1010268563 1010268563 1D4DE481E6 E7DB READ WRITE NO 15-AUG-15 03.5 1915708 393216000

F91DB3E053 6.11.724 AM

D301A8C07F

Checking Compatibility before plugging

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

compatible CONSTANT VARCHAR2(3) :=

CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

pdb_descr_file => '/u01/unplug_12c_PROD_15Aug2015.xml',

pdb_name => 'PROD')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

PL/SQL procedure successfully completed.

Verify the Violations. If there first fix it and then move. In my case I got only warning. So I am ignoring this.

SQL> COL MESSAGE FOR A40

SQL> COL NAME FOR A14

SQL> COL TIME FOR A32

SQL> COL CAUSE FOR A14

SQL> select name,cause,type,message,status

from PDB_PLUG_IN_VIOLATIONS

where name='PROD' ;

NAME CAUSE TYPE MESSAGE STATUS

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

PROD OPTION WARNING Database option DV mismatch: PDB install PENDING

ed version NULL. CDB installed version 1

2.1.0.1.0.

PROD OPTION WARNING Database option OLS mismatch: PDB instal PENDING

led version NULL. CDB installed version

12.1.0.1.0.

PROD Non-CDB to PDB WARNING PDB plugged in is a non-CDB, requires no PENDING

ncdb_to_pdb.sql be run.

PROD Parameter WARNING CDB parameter sga_target mismatch: Previ PENDING

ous 805306368 Current 0

PROD Parameter WARNING CDB parameter memory_target mismatch: Pr PENDING

evious 1073741824 Current 1258291200

PROD Parameter WARNING CDB parameter compatible mismatch: Previ PENDING

ous 11.2.0.4.0 Current 12.1.0.0.0

6 rows selected.

SQL> SHOW CON_NAME

CON_NAME

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

CDB$ROOT

Plugging PROD into root container

SQL> CREATE PLUGGABLE DATABASE PROD USING '/u01/unplug_12c_PROD_15Aug2015.xml' NOCOPY ;

CREATE PLUGGABLE DATABASE PROD USING '/u01/unplug_12c_PROD_15Aug2015.xml' NOCOPY

ERROR at line 1:

ORA-27038: created file already exists

ORA-01119: error in creating database file '/u01/app/oracle/oradata/PROD/temp01.dbf'

To Fix the existence issue use the “reuse” command.

SQL> CREATE PLUGGABLE DATABASE PROD USING '/u01/unplug_12c_PROD_15Aug2015.xml' NOCOPY TEMPFILE REUSE ;

Pluggable database created.

SQL> col open_time for a14

col guid for a10

col name for a10

set lin200 pages 200

select * from v$pdbs ;

CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE

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

2 4130373951 4130373951 1D4DC0EF22 PDB$SEED READ ONLY NO 15-AUG-15 03.5 1720762 283115520

6E1AB2E053 1.39.833 AM

D301A8C0CC

3 1010268563 1010268563 1D4DE481E6 E7DB READ WRITE NO 15-AUG-15 03.5 1915708 393216000

F91DB3E053 6.11.724 AM

D301A8C07F

4 299604649 299604649 1D4CD9BC62 PROD MOUNTED 15-AUG-15 05.0 2299813 0

25155FE053 0.34.575 AM

D301A8C0D4

SQL> ALTER SESSION SET CONTAINER=PROD ;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

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

PROD

SQL> SHOW CON_ID

CON_ID

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

SQL> select * from v$pdbs ;

CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE

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

4 299604649 299604649 1D4CD9BC62 PROD MOUNTED 15-AUG-15 05.0 2299813 0

25155FE053 0.34.575 AM

D301A8C0D4

Executing post script to migrate noncdb to pdb.

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

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 :pdbname

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 :pdbname pdbname from dual;

PDBNAME

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

PROD

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='PROD'

PDBID

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

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 open_state_col,

5 decode(restricted, 'YES', 'RESTRICTED', '')

6 restricted_col

7 from v$pdbs where name='&pdbname';

old 2: 'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',

new 2: 'READ ONLY', 'ALTER PLUGGABLE DATABASE PROD OPEN READ ONLY',

old 3: 'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN', '')

new 3: 'READ WRITE', 'ALTER PLUGGABLE DATABASE PROD OPEN', '')

old 7: from v$pdbs where name='&pdbname'

new 7: from v$pdbs where name='PROD'

OPEN_STATE_COL RESTRICTED

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

1 row selected.

SQL>

SQL> -- save value for _system_trig_enabled parameter

SQL> COLUMN sys_trig NEW_VALUE sys_trig_enabled NOPRINT;

…

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;

Open database in READ/WRITE Mode

SQL> sho con_id

CON_ID

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

SQL> sho parameter compatible

NAME TYPE VALUE

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

compatible string 12.1.0.0.0

noncdb_compatible boolean FALSE

plsql_v2_compatibility boolean FALSE

SQL> alter database open read write ;

Database altered.

Now prod database has open in read write mode without any sync issue.

If you have experience the issue related to open read write like below then follow below required step. In my case I haven’t got any issue for opening in read write mode. But still I am writing below step as most of the case we will face synchronization issue while opening.

SQL> alter database open read write;

alter database open read write

ERROR at line 1:

ORA-24344: success with compilation error

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

1 row selected.

In alertlog you will see

********************************************** ******************

WARNING: Pluggable Database PRODwith pdb id - 4 is altered with warnings.

Please look into PDB_PLUG_IN_VIOLATIONS view for more details.

****************************************************************

SOLUTION

ORA-24344 check PDB_PLUG_IN_VIOLATIONS, correct them and resync

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> alter pluggable database PROD close;

Pluggable database altered.

SQL> alter pluggable database PROD open restricted;

Pluggable database altered.

SQL> exec dbms_pdb.sync_pdb();

PL/SQL procedure successfully completed.

SQL> alter pluggable database PROD close immediate;

Pluggable database altered.

SQL> alter pluggable database PROD open;

Pluggable database altered.

Login to PDB and validate the conversion

SQL> select * from v$pdbs ;

CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE

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

4 299604649 299604649 1D4CD9BC62 PROD READ WRITE NO 15-AUG-15 05.4 2299813 1053818880

25155FE053 0.34.657 AM

D301A8C0D4

1 row selected.

SQL> col message for a14

SQL> col name for a10

SQL> select name,cause,type,message,status

from PDB_PLUG_IN_VIOLATIONS

where name='PROD';

no rows selected

SQL> conn / as sysdba

Connected.

SQL> sho con_name

CON_NAME

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

CDB$ROOT

SQL> col message for a40

SQL> select name,cause,type,message,status

from PDB_PLUG_IN_VIOLATIONS

where name='PROD' ;

NAME CAUSE TYPE MESSAGE STATUS

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

PROD Parameter WARNING CDB parameter sga_target mismatch: Previ RESOLVED

ous 805306368 Current 0

PROD Parameter WARNING CDB parameter memory_target mismatch: Pr RESOLVED

evious 1073741824 Current 1258291200

PROD Parameter WARNING CDB parameter compatible mismatch: Previ RESOLVED

ous 11.2.0.4.0 Current 12.1.0.0.0

PROD Non-CDB to PDB ERROR PDB plugged in is a non-CDB, requires no RESOLVED

ncdb_to_pdb.sql be run.

PROD OPTION WARNING Database option DV mismatch: PDB install PENDING

ed version NULL. CDB installed version 1

2.1.0.1.0.

PROD OPTION WARNING Database option OLS mismatch: PDB instal PENDING

led version NULL. CDB installed version

12.1.0.1.0.

6 rows selected.

Now its high time to take backup for any disaster

[oracle@vm211 ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sat Aug 15 05:43:33 2015

connected to target database: NOKIA (DBID=391421380)

RMAN> backup as compressed backupset database tag '12c_full_backup_15aug2015' ;

Starting backup at 15-AUG-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=55 device type=DISK

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle/oradata/NOKIA/undotbs01.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/NOKIA/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/NOKIA/sysaux01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/NOKIA/users01.dbf

channel ORA_DISK_1: starting piece 1 at 15-AUG-15

channel ORA_DISK_1: finished piece 1 at 15-AUG-15

piece handle=/u01/app/oracle/fast_recovery_area/NOKIA/backupset/2015_08_15/o1_mf_nnndf_12C_FULL_BACKUP_15AU_bwx124ox_.bkp tag=12C_FULL_BACKUP_15AUG2015 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00014 name=/u01/app/oracle/oradata/PROD/system01.dbf

input datafile file number=00015 name=/u01/app/oracle/oradata/PROD/sysaux01.dbf

input datafile file number=00017 name=/u01/app/oracle/oradata/PROD/example01.dbf

input datafile file number=00016 name=/u01/app/oracle/oradata/PROD/users01.dbf

channel ORA_DISK_1: starting piece 1 at 15-AUG-15

channel ORA_DISK_1: finished piece 1 at 15-AUG-15

piece handle=/u01/app/oracle/fast_recovery_area/NOKIA/1D4CD9BC6225155FE053D301A8C0D491/backupset/2015_08_15/o1_mf_nnndf_12C_FULL_BACKUP_15AU_bwx14rp1_.bkp tag=12C_FULL_BACKUP_15AUG2015 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00009 name=/u01/app/oracle/oradata/NOKIA/E7DB/sysaux01.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/NOKIA/E7DB/example01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/NOKIA/E7DB/system01.dbf

input datafile file number=00010 name=/u01/app/oracle/oradata/NOKIA/E7DB/SAMPLE_SCHEMA_users01.dbf

channel ORA_DISK_1: starting piece 1 at 15-AUG-15

channel ORA_DISK_1: finished piece 1 at 15-AUG-15

piece handle=/u01/app/oracle/fast_recovery_area/NOKIA/1D4DE481E6F91DB3E053D301A8C07FE4/backupset/2015_08_15/o1_mf_nnndf_12C_FULL_BACKUP_15AU_bwx17fw8_.bkp tag=12C_FULL_BACKUP_15AUG2015 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/u01/app/oracle/oradata/NOKIA/pdbseed/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/NOKIA/pdbseed/system01.dbf

channel ORA_DISK_1: starting piece 1 at 15-AUG-15

channel ORA_DISK_1: finished piece 1 at 15-AUG-15

piece handle=/u01/app/oracle/fast_recovery_area/NOKIA/1D4DC0EF226E1AB2E053D301A8C0CCD0/backupset/2015_08_15/o1_mf_nnndf_12C_FULL_BACKUP_15AU_bwx18v4s_.bkp tag=12C_FULL_BACKUP_15AUG2015 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

Finished backup at 15-AUG-15

Starting Control File and SPFILE Autobackup at 15-AUG-15

piece handle=/u01/app/oracle/fast_recovery_area/NOKIA/autobackup/2015_08_15/o1_mf_s_887780886_bwx19z0g_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 15-AUG-15

RMAN>

Oracle Certified Master's - Blogs

Home

Saturday, August 15, 2015

Converting Non CDB to PDB - Oracle 12c

No comments:

Post a Comment