Sreenu Oracle DBA: Non CDB To PDB

Steps to migrate Non-CDB-to-PDB

Oracle 12c database version

Contents

Version history 3

1. Introduction 5

2. Prerequisite 5

3. Non-CDB to PDB Migration 6

4. Post pdb migration TASKS .12

1. Introduction

This document will provide the steps to migrate Non-CDB database to PDB.

2. Prerequisite

 Create Blackout Window from OEM for the DB which we are going to migrate (3hrs-5hrs)

 Ensure consistent (Offline Backup) database backup before activity.

 Verify the database invalid objects count before migration

col OWNER for a30

select Owner,Object_type, count(*) count_of_invalid

from dba_objects where status='INVALID'

group BY Owner,Object_type ORDER by Owner,Object_type;

col OBJECT_NAME for a45

set linesize 999

select Owner,Object_name,object_type,created, Last_DDL_Time, status

from dba_objects

where status='INVALID'

ORDER by Owner desc,Last_DDL_Time desc;

 Check database size and ASM diskgroups free space(+DATA DG free space should be>DB Physical Size).

SQL>

SELECT 'Physical Size (GB):' ||TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99) FROM DBA_DATA_FILES union

SELECT 'Actual Size (GB) :' || TO_CHAR(SUM(BYTES)/1024/1024/1024,9999999.99) FROM DBA_SEGMENTS;

'PHYSICALSIZE(GB):'||TO_CHAR(S

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

Actual Size (GB): 130

Physical Size (GB): 224

SQL>

select name, total_mb/1024,free_mb/1024 from v$asm_diskgroup;

NAME TOTAL_MB/1024 FREE_MB/1024

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

ARCHIVE 510 461,542969

DATA 1400 447,554688

KEYSTORE 10 9,8984375

LOG 20 8,3203125

3. Non-CDB to PDB Migration

Following are the steps:

In this test case setup:

Name of the Non-CDB Database is : CARS

Name of the CDB Database is: CONTCDB

1. To convert non-CDB to PDB, you have to cleanly shutdown the DB:

. oraenv

set environment to CARS

sqlplus / as sysdba

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

CARS READ WRITE

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Note: Create a Directory (PDB) in /tmp directory to generate Database xml file (Ignore if directory already exists)

[oracle@Ora11g-CARS] /home/oracle > cd /tmp

[oracle@Ora11g-CARS] /tmp > mkdir PDB

[oracle@Ora11g-CARS] /tmp >cd PDB

[oracle@Ora11g-CARS] /tmp/PDB >pwd

/tmp/PDB

2. Once the DB is shutdown cleanly, open it in read only mode:

SQL> startup open read only

ORACLE instance started.

Total System Global Area 2.1475E+10 bytes

Fixed Size 6081440 bytes

Variable Size 1.4697E+10 bytes

Database Buffers 6710886400 bytes

Redo Buffers 61026304 bytes

Database mounted.

Database opened.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

CARS READ ONLY

3. Describe the database and generate the xml file:

SQL> BEGIN

DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/PDB/CARS.xml’);

END;

PL/SQL procedure successfully completed.

SQL> !ls -lrt /tmp/PDB/CARS.xml

-rw-r--r--. 1 oracle oinstall 13555 Apr 6 10:19 /tmp/PDB/CARS.xml

4. Shutdown the database.

sql> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

5. Check if it is compatible with cdb, run below in target CDB

. oraenv

set environment to CONTCDB

sqlplus / as sysdba

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

CONTCDB READ WRITE

SQL> SET SERVEROUTPUT ON;

DECLARE

compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/PDB/CARS.xml')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

PL/SQL procedure successfully completed.

Note: Output should be “YES”

Check PDB_PLUG_IN_VIOLATIONS view from cdb database if error raised

SQL> col cause for a20

SQL> col name for a20

SQL> col message for a35 word_wrapped

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='<noncdb database name>';

NAME CAUSE TYPE MESSAGE STATUS

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

CARS Non-CDB to PDB WARNING PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING

CARS OPTION WARNING Database option APS mismatch: PDB installed version NULL. CDB installed version PENDING

12.1.0.2.0.

CARS OPTION WARNING Database option CONTEXT mismatch: PDB installed version NULL. CDB installed PENDING

version 12.1.0.2.0.

CARS OPTION WARNING Database option DV mismatch: PDB installed version NULL. CDB installed version PENDING

12.1.0.2.0.

CARS OPTION WARNING Database option OLS mismatch: PDB installed version NULL. CDB installed version PENDING

12.1.0.2.0.

CARS OPTION WARNING Database option ORDIM mismatch: PDB installed version NULL. CDB installed PENDING

version 12.1.0.2.0.

CARS OPTION WARNING Database option SDO mismatch: PDB installed version NULL. CDB installed version PENDING

12.1.0.2.0.

CARS OPTION WARNING Database option XOQ mismatch: PDB installed version NULL. CDB installed version PENDING

12.1.0.2.0.

CARS APEX WARNING APEX mismatch: PDB installed version NULL CDB installed version 4.2.5.00.08 PENDING

CARS Parameter WARNING CDB parameter nls_language mismatch: Previous 'SWEDISH' Current 'AMERICAN' PENDING

CARS Parameter WARNING CDB parameter nls_territory mismatch: Previous 'SWEDEN' Current 'AMERICA' PENDING

CARS Parameter WARNING CDB parameter memory_target mismatch: Previous 15G Current 35G PENDING

CARS Parameter WARNING CDB parameter memory_max_target mismatch: Previous 20G Current 40G PENDING

CARS Parameter WARNING CDB parameter undo_retention mismatch: Previous 43200 Current 3600 PENDING

CARS Parameter WARNING CDB parameter recyclebin mismatch: Previous 'OFF' Current 'on' PENDING

CARS Parameter WARNING CDB parameter session_cached_cursors mismatch: Previous 100 Current 50 PENDING

CARS Parameter WARNING CDB parameter job_queue_processes mismatch: Previous 50 Current 1000 PENDING

CARS Service Name Conflic WARNING Service name or network name of service PUTDMYXDB in the PDB is invalid or PENDING

t conflicts with an existing service name or network name in the CDB.

CARS Service Name Conflic WARNING Service name or network name of service PUTDMY in the PDB is invalid or PENDING

t conflicts with an existing service name or network name in the CDB.

CARS Service Name Conflic WARNING Service name or network name of service PUTXDB in the PDB is invalid or PENDING

t conflicts with an existing service name or network name in the CDB.

CARS Service Name Conflic WARNING Service name or network name of service PUT in the PDB is invalid or PENDING

t conflicts with an existing service name or network name in the CDB.

CARS Service Name Conflic WARNING Service name or network name of service PUTDMOXDB in the PDB is invalid or PENDING

t conflicts with an existing service name or network name in the CDB.

CARS Service Name Conflic WARNING Service name or network name of service PUTDMO in the PDB is invalid or PENDING

t conflicts with an existing service name or network name in the CDB.

CARS Service Name Conflic WARNING Service name or network name of service SIT in the PDB is invalid or conflicts PENDING

t with an existing service name or network name in the CDB.

CARS Service Name Conflic WARNING Service name or network name of service GGADMIN.OGG$Q_INEDW in the PDB is PENDING

t invalid or conflicts with an existing service name or network name in the CDB.

25 rows selected.

In case you got warnings like above we can ignore those warnings (ERRORS to be consider and fix those errors before proceeding to next step)

6. Connect to the CDB where database has to be plugged in:

. oraenv

set environment to CONTCDB

sqlplus / as sysdba

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

CONTCDB READ WRITE

7. Create a pluggable database Using xml file

SQL> CREATE PLUGGABLE DATABASE CARS AS CLONE USING '/tmp/PDB/CARS.xml';

Pluggable database created.

8. Switch to the PDB container and run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql"

SQL> ALTER SESSION SET CONTAINER=CARS;

Session altered.

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

This step may take several minutes to complete, Meanwhile monitor alert logfile for STABLCDB database.

9. Startup the PDB and check the open mode.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

6 CARS MOUNTED

SQL>ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE

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

CARS READ WRITE

4.Post PDB MIGRATION TASKS

Perform sanity check post migration

Check PDB_PLUG_IN_VIOLATIONS view from cdb database post migration

Run below in target CDB

. oraenv

set environment to CONTCDB

sqlplus / as sysdba

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

CONTCDB READ WRITE

SQL> col cause for a30;

col name for a20;

col message for a80 word_wrapped;

col CAUSE for a20

SET PAGESIZE 100;

set linesize 300;

select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='CARS'; SQL> SQL> SQL> SQL> SQL> SQL>

NAME CAUSE TYPE MESSAGE STATUS

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

CARS Parameter WARNING CDB parameter nls_language mismatch: Previous 'SWEDISH' Current 'AMERICAN' RESOLVED

CARS Parameter WARNING CDB parameter nls_territory mismatch: Previous 'SWEDEN' Current 'AMERICA' RESOLVED

CARS Parameter WARNING CDB parameter memory_target mismatch: Previous 15G Current 35G RESOLVED

CARS Parameter WARNING CDB parameter memory_max_target mismatch: Previous 20G Current 40G RESOLVED

CARS Parameter WARNING CDB parameter undo_retention mismatch: Previous 43200 Current 3600 RESOLVED

CARS Parameter WARNING CDB parameter recyclebin mismatch: Previous 'OFF' Current 'on' RESOLVED

CARS Parameter WARNING CDB parameter session_cached_cursors mismatch: Previous 100 Current 50 RESOLVED

CARS Parameter WARNING CDB parameter job_queue_processes mismatch: Previous 50 Current 1000 RESOLVED

CARS Service Name Conflic WARNING Service name or network name of service CARS in the PDB is invalid or conflicts RESOLVED

t with an existing service name or network name in the CDB.

CARS Non-CDB to PDB ERROR PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. RESOLVED