Converting non-container DB to Pluggble database.
Converting non-container Database to Pluggable database.
How to convert non-cdb 19c to CDB/PDB 19c database
How to Convert Non-CDB to PDB Database on same localhost
Existing Instance & DB name: ORCLP
CDB Instance & DB name: ORCLPG (new)
PDB name: ORCLPDB
Note: Without changing service names, tnsnames.ora entry , non-CDB will be converted to CDB/PDB.
Step 1) Tempfile details in existing DB
| select file_id,file_name,tablespace_name,bytes/1024/1024/1024,status from dba_temp_files;SQL> SQL> SQL> SQL> SQL>
FILE_ID FILE_NAME TABLESPACE BYTES/1024/1024/1024 STATUS |
Step 2) Diskgroup details
Note: Need to makesure that disck group(where tempfile is existing) should have double the amount of existing temp size.
For huge temp tablespace size ,for example 300GB/500GB , capture the temptablespace details and drop it and create new one with 10G or 20G.
Small tempsize 5 or 10 or 20 GB is sufficient for CDB migration.
| NAME STATE TOTAL_MB FREE_MB —————————————- ———– ———- ———-DATA CONNECTED 1310722 785320 |
Step 3) Create CDB database (new skeleton)
Create Container Database
| export ORACLE_HOME=/u00/app/oracle/product/19c/db_1 export PATH=$ORACLE_HOME/bin:$PATH cd /u00/app/oracle/product/19c/db_1/bin ./dbca -silent -createDatabase -templateName /u00/app/oracle/product/19c/db_1/assistants/dbca/templates/General_Purpose.dbc -gdbname ORCLPG -sid ORCLPG -characterSet WE8ISO8859P1 -nationalCharacterSet AL16UTF16 -sysPassword welcome123 -systemPassword welcome123 -storageType ASM -datafileDestination ‘+DATA’ -recoveryAreaDestination ‘+FRA’ -createAsContainerDatabase true -numberOfPDBs 1 -pdbName pdb1 -pdbAdminPassword welcome123 -databaseType MULTIPURPOSE -memoryMgmtType auto_sga -memoryPercentage 20 -redoLogFileSize 100 -emConfiguration NONE -initParams nls_language=AMERICAN,nls_calendar=GREGORIAN,db_block_size=8K |
Step 4) Connect to Source Database and shutdown the database to open in read only
| SQL>SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>SQL> startup open read only; ORACLE instance started.SQL> select name,open_mode from v$database;NAME OPEN_MODE ——— ——————– ORCLP READ ONLY |
Step 5) Describe the database and generate the xml file:
| BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => ‘/home/oracle/ORCLP/ORCLP_CDB/ORCLPX.xml’); END; / |
run the above PL/SQL anonymous block.
| SQL> SQL> BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => ‘/home/oracle/ORCLP/ORCLP_CDB/ORCLPX.xml’); END; / 2 3 4 5PL/SQL procedure successfully completed.SQL> |
Step 6) Check if it is compatible with cdb, run below in target CDB
| SET SERVEROUTPUT ON DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => ‘/home/oracle/ORCLP/ORCLP_CDB/ORCLPX.xml’, pdb_name => ‘ORCLPX’) WHEN TRUE THEN ‘YES’ ELSE ‘NO’ END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; / |
run the above anonymous block.
| SQL> SQL> SET SERVEROUTPUT ON DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => ‘/home/oracle/ORCLP/ORCLP_CDB/ORCLPX.xml’, pdb_name => ‘ORCLPX’) WHEN TRUE THEN ‘YES’ ELSE ‘NO’ END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; / SQL> 2 3 4 5 6 7 8 9 10 11 12 YESPL/SQL procedure successfully completed.SQL> |
Step 7) Check PDB_PLUG_IN_VIOLATIONS view from cdb database if there are any errors
| set line 200 col NAME for a30 col MESSAGE for a30 col ACTION for a30 col CAUSE for a20 select NAME,CAUSE,ERROR_NUMBER,MESSAGE,STATUS,ACTION from PDB_PLUG_IN_VIOLATIONS;NAME CAUSE ERROR_NUMBER MESSAGE STATUS ACTION —————————— ——————– ———— —————————— ——— —————————— PDB$SEED SQL Patch 0 ‘19.12.0.0.0 Release_Update 21 RESOLVED Call datapatch to install in t 07161418’ is installed in the he PDB or the CDB CDB but no release updates are installed in the PDBORCLPX Non-CDB to PDB 0 PDB plugged in is a non-CDB, r PENDING Run noncdb_to_pdb.sql. equires noncdb_to_pdb.sql be r un.ORCLPX Parameter 0 CDB parameter processes mismat PENDING Please check the parameter in ch: Previous 1000 Current 1920 the current CDB NAME CAUSE ERROR_NUMBER MESSAGE STATUS ACTION ORCLPX Parameter 0 CDB parameter cpu_count mismat PENDING Please check the parameter in ORCLPX Parameter 0 CDB parameter sga_target misma PENDING Please check the parameter in ORCLPX Parameter 0 CDB parameter compatible misma PENDING Please check the parameter in NAME CAUSE ERROR_NUMBER MESSAGE STATUS ACTION ORCLPX Parameter 0 CDB parameter pga_aggregate_ta PENDING Please check the parameter in 7 rows selected. SQL> |
Step 8 ) Change parameter values and bounce the database
| sga_target pga_aggregate_target sga_target processes cpu_countalter system set processes=1000 scope=spfile; alter system set cpu_count=2 scope=spfile; alter system set sga_target=2512M scope=spfile; alter system set pga_aggregate_target=1g scope=both; |
Note: No change for below parameter compatible
| SQL>shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>SQL> SQL> startup ORACLE instance started.Total System Global Area 2634020296 bytes Fixed Size 9139656 bytes Variable Size 553648128 bytes Database Buffers 2063597568 bytes Redo Buffers 7634944 bytes Database mounted. Database opened. SQL> |
Step 9) Check if it is compatible with cdb, run below in target CDB
| SQL> SET SERVEROUTPUT ON DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => ‘/home/oracle/ORCLP/ORCLP_CDB/ORCLPX.xml’, pdb_name => ‘ORCLPX’) WHEN TRUE THEN ‘YES’ ELSE ‘NO’ END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; / SQL> 2 3 4 5 6 7 8 9 10 11 12 YESPL/SQL procedure successfully completed.SQL> SQL> SQL> set line 200 col NAME for a30 col MESSAGE for a30 col ACTION for a30 col CAUSE for a20 select NAME,CAUSE,ERROR_NUMBER,MESSAGE,STATUS,ACTION from PDB_PLUG_IN_VIOLATIONS;SQL> SQL> SQL> SQL> SQL>NAME CAUSE ERROR_NUMBER MESSAGE STATUS ACTION —————————— ——————– ———— —————————— ——— —————————— PDB$SEED SQL Patch 0 ‘19.12.0.0.0 Release_Update 21 RESOLVED Call datapatch to install in t 07161418’ is installed in the he PDB or the CDB CDB but no release updates are installed in the PDB ORCLPX Non-CDB to PDB 0 PDB plugged in is a non-CDB, r PENDING Run noncdb_to_pdb.sql. ORCLPX Parameter 0 CDB parameter compatible misma PENDING Please check the parameter in NAME CAUSE ERROR_NUMBER MESSAGE STATUS ACTION SQL> |
Step 10) Connect to the CDB where database has to be plugged in and create pluggable database ORCLPX
| . oraen ORCLPG sqlplus / as sysdbacreate pluggable database ORCLPX using ‘/home/oracle/ORCLP/ORCLP_CDB/ORCLPX.xml’ NOCOPY tempfile reuse;07:34:40 SQL> 07:34:41 SQL> create pluggable database ORCLPX using ‘/home/oracle/ORCLP/ORCLP_CDB/ORCLPX.xml’ NOCOPY tempfile reuse;Pluggable database created. 07:34:50 SQL> 07:35:12 SQL> CON_ID CON_NAME OPEN MODE RESTRICTED |
Step 11) Switch to the PDB container and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql”
| 07:35:28 SQL> ALTER SESSION SET CONTAINER=ORCLPX;
Session altered. Run the script 07:45:01 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED |
Step 12) Open the Pluggable database
| 07:45:05 SQL> ALTER PLUGGABLE DATABASE ORCLPX OPEN;
Pluggable database altered. 07:45:28 SQL> SELECT name, open_mode FROM v$pdbs; NAME OPEN_MODE 1 row selected. 07:45:34 SQL> Step 13) Verify if any compatiblity issues SQL> NAME CAUSE ERROR_NUMBER MESSAGE STATUS ACTION ORCLPX Parameter 0 CDB parameter compatible misma RESOLVED Please check the parameter in ORCLPX Non-CDB to PDB 0 PDB plugged in is a non-CDB, r RESOLVED Run noncdb_to_pdb.sql. NAME CAUSE ERROR_NUMBER MESSAGE STATUS ACTION |
Step 14) Verify Service names and configuration
| SQL> select name from v$services;
NAME 6 rows selected. [oracle@ORCL_SRVR ~]$ srvctl config database -d ORCLPG |
Step 15) Adding service name ( name from old non-cdb) for PDB
| srvctl add service -s ORCLP -d ORCLPG -pdb ORCLPX srvctl start service -s ORCLP -d ORCLPG srvctl status service -s ORCLP -d ORCLPG[oracle@ORCL_SRVR ~]$ srvctl add service -s ORCLP -d ORCLPG -pdb ORCLPX [oracle@ORCL_SRVR ~]$ srvctl start service -s ORCLP -d ORCLPG [oracle@ORCL_SRVR ~]$ srvctl status service -s ORCLP -d ORCLPG Service ORCLP is running [oracle@ORCL_SRVR ~]$ |
Step 16) Verify service configuration
| [oracle@ORCL_SRVR ~]$ srvctl config database -d ORCLPG Database unique name: ORCLPG Database name: ORCLPG Oracle home: /u00/app/oracle/product/19c/db_1 Oracle user: oracle Spfile: +XML_CLOB_LOG/ORCLPG/PARAMETERFILE/spfile.622.1119186559 Password file: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: FRA,DATA,DATA06 Services: ORCLP OSDBA group: dba OSOPER group: Database instance: ORCLPG [oracle@ORCL_SRVR ~]$SQL> sho parameter serviceNAME TYPE VALUE ———————————— ———– —————————— service_names string ORCLPG SQL> SQL> SQL> SQL> select name from v$services;NAME —————————————————————- ORCLP ORCLPx
Verify service configuration [oracle@ORCL_SRVR ~]$ sqlplus TEST19@ORCLP SQL*Plus: Release 19.0.0.0.0 – Production on Fri Oct 28 07:52:52 2022 Copyright (c) 1982, 2021, Oracle. All rights reserved. Enter password: Connected to: SQL> show user |
Step 17) Take full backup after converting to PDB
See Also: