Amit Oracle DBA Blog

STEPS :

Overview
Prerequisites
Environment
Shutdown NON-CDB (TESTDB )
Open Non-CDB in read only (this is for consistency)
Run DBMS_PDB.DESCRIBE to create an XML file
Shutdown the Non-CDB
Connect to Target
Check whether Non-CDB (TESTDB ) can be plugged into CDB(TESTDBPDB)
Create target directory for datafiles
Plug-in Non-CDB (TESTDB ) as PDB (TESTDBPDB) into ORADB
Verify newly created PDB TESTDB on ORADB
Run noncdb_to_pdb.sql script on new PDB (ORADBPDB)
Verify table PDB_PLUG_IN_VIOLATIONS

Overview

There are 4 methods to move Non-CDB into a Pluggable Database (PDB) in a Container Database

- Create PDB by cloning a Non-CDB

- Use DBMS_PDB package

- Use Oracle Data Pump export/import

- Use GoldenGate Replication

Note: If status is UNUSABLE state it must be dropped before a PDB with the same name as the unusable PDB can be created. YOu can view the status from CBD_PDBS OR DBA_PDBS views

Prerequisites

Make sure One container database already exists.

Environment

Platform : Linux x86_64

Server Name : srv1.localdomain, IP: 192.168.1.120

DB Version : Oracle 19.0.0.0, File system: ASM

Source NON-CDB : TESTDB

Target CDB : ORADB

Target PDB : TESTDBPDB

Oracle Home Path: /u01/app/oracle/product/19.0.0/db_1

Shutdown NON-CDB (TESTDB) and create a test table for validation.

[oracle@srv1 ~]$ . oraenv

ORACLE_SID = [oradb] ? testdb

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@srv1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 16 17:37:55 2023

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select name, open_mode, cdb from v$database;

NAME OPEN_MODE CDB

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

TESTDB READ WRITE NO

SQL> create user amit identified by amit default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to amit;

Grant succeeded.

SQL> alter user amit quota unlimited on users;

User altered.

SQL> conn amit/amit

Connected.

SQL> show user

USER is "AMIT"

SQL> create table sugi(Name varchar2(10), Role varchar2(10));

Table created.

SQL> insert into sugi values ('&a','&b');

Enter value for a: amit

Enter value for b: dba

old 1: insert into sugi values ('&a','&b')

new 1: insert into sugi values ('amit','dba')

1 row created.

SQL> /

Enter value for a: praving

Enter value for b: dba

old 1: insert into sugi values ('&a','&b')

new 1: insert into sugi values ('pravin','dba')

1 row created.

SQL> select * from sugi;

NAME ROLE

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

amit dba

pravin dba

SQL> CONN / AS SYSDBA

Connected.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> select name from v$datafile;

NAME

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

+DATADISK/TESTDB/DATAFILE/system.279.1147705287

+DATADISK/TESTDB/DATAFILE/sysaux.280.1147705321

+DATADISK/TESTDB/DATAFILE/undotbs1.281.1147705337

+DATADISK/TESTDB/DATAFILE/users.282.1147705337

Open Non-CDB in read only (this is for consistency)

SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down.

SQL> STARTUP MOUNT EXCLUSIVE; ORACLE instance started. Total System Global Area 2432695144 bytes Fixed Size 8899432 bytes Variable Size 536870912 bytes Database Buffers 1879048192 bytes Redo Buffers 7876608 bytes Database mounted. SQL> SQL> SQL> SQL>

SQL> alter database open read only; Database altered. SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- TESTDB READ ONLY NO

Run DBMS_PDB.DESCRIBE to create an XML file

SQL> SQL> BEGIN DBMS_PDB.DESCRIBE(pdb_descr_file => '/u01/noncdb19c.xml'); END; / 2 3 4 PL/SQL procedure successfully completed. SQL>

[oracle@srv1 u01]$ pwd /u01 [oracle@srv1 u01]$ ls -lrth noncdb19c.xml -rw-r--r--. 1 oracle asmadmin 6.7K Sep 16 17:47 noncdb19c.xml [oracle@srv1 u01]$

Shutdown the Non-CDB(TESTDB)

SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- TESTDB READ ONLY NO SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL>

Now connect to the container database, where it needs to be plugged.

On Target Side :

Connect to the target Container Database (ORADB)

SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- ORADB READ WRITE YES

Check whether non-cdb (TESTDB) can be plugged into CDB(ORADB)

SQL> SET SERVEROUTPUT ON DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/u01/noncdb19c.xml', pdb_name => 'TESTDB') WHEN TRUE THEN 'YES' ELSE 'NO' END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; /

SQL>

YES PL/SQL procedure successfully completed. SQL>

-- Incase output is NO, then check PDB_PLUG_IN_VIOLATIONS view

SQL> col cause for a10 col name for a10 col message for a35 word_wrapped select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TESTDB';SQL> SQL> SQL> SQL> NAME CAUSE TYPE MESSAGE STATUS ---------- ---------- --------- ----------------------------------- --------- TESTDB Non-CDB to WARNING PDB plugged in is a non-CDB, PENDING PDB requires noncdb_to_pdb.sql be run. TESTDB Parameter WARNING CDB parameter sga_target mismatch: PENDING Previous 2320M Current 3840M TESTDB Parameter WARNING CDB parameter pga_aggregate_target PENDING mismatch: Previous 771M Current 1280M

Create target directory for datafiles

In case you want to restore data files on different location then create a new directory structure to restore the datafile and use SOURCE_FILE_NAME_CONVERT with COPY option. This option allows us to specify a new location for the datafiles

SQL> !mkdir -p u01/app/oracle/oradata/CDB1/TESTDBPDB

Plug-in Non-CDB (TESTDB) as PDB (TESTDBPDB) into ORADB

The NOCOPY option keeps the original location of the datafiles as the database is Plugged-In

I am plugging the database in to a CDB on the same server with NOCOPY clause which will use the same datafile location witout copying the datafile.

SQL> CREATE PLUGGABLE DATABASE TESTDBPDB USING '/u01/noncdb19c.xml' NOCOPY 2 3 ; Pluggable database created.

Verify newly created PDB TESTDBPDB on ORADB

SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- ORADB READ WRITE YES SQL> col pdb_name for a30 SQL> SELECT pdb_name , status from cdb_pdbs; PDB_NAME STATUS ------------------------------ ---------- PDB1 NORMAL PDB$SEED NORMAL TESTDBPDB NEW SQL> select pdb_name, status from dba_pdbs; PDB_NAME STATUS ------------------------------ ---------- PDB1 NORMAL PDB$SEED NORMAL TESTDBPDB NEW

Run noncdb_to_pdb.sql script on new PDB (TESTDBPDB)

You have to convert the plugged non-CDB to a proper PDB by deleting unnecessary metadata from PDB SYSTEM tablespace. For this purpose, you execute the $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script when connected to the PDB.

SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- ORADB READ WRITE YES SQL> ALTER SESSION SET CONTAINER=TESTDBPDB; Session altered. SQL> show con_name CON_NAME ------------------------------ TESTDBPDB SQL> show con_id CON_ID ------------------------------ 4 SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql; 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> SET VERIFY OFF SQL> SQL> -- save settings SQL> STORE SET ncdb2pdb.settings.sql REPLACE Wrote file ncdb2pdb.settings.sql SQL> SQL> SET TIME ON 18:13:46 SQL> SET TIMING ON 18:13:46 SQL> 18:13:46 SQL> WHENEVER SQLERROR EXIT; 18:13:46 SQL> 18:13:46 SQL> DOC 18:13:46 DOC>####################################################################### 18:13:46 DOC>####################################################################### 18:13:46 DOC> The following statement will cause an "ORA-01403: no data found" 18:13:46 DOC> error if we're not in a PDB. 18:13:46 DOC> This script is intended to be run right after plugin of a PDB, 18:13:46 DOC> while inside the PDB. 18:13:46 DOC>####################################################################### 18:13:46 DOC>####################################################################### 18:13:46 DOC># 18:13:46 SQL> 18:13:46 SQL> VARIABLE cdbname VARCHAR2(128) 18:13:46 SQL> VARIABLE pdbname VARCHAR2(128) 18:13:46 SQL> BEGIN 18:13:46 2 SELECT sys_context('USERENV', 'CDB_NAME') 18:13:46 3 INTO :cdbname 18:13:46 4 FROM dual 18:13:46 5 WHERE sys_context('USERENV', 'CDB_NAME') is not null; 18:13:46 6 SELECT sys_context('USERENV', 'CON_NAME') 18:13:46 7 INTO :pdbname 18:13:46 8 FROM dual 18:13:46 9 WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT'; 18:13:46 10 END; 18:13:46 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 18:13:46 SQL> 18:13:46 SQL> @@?/rdbms/admin/loc_to_common0.sql 18:13:47 SQL> Rem 18:13:47 SQL> Rem $Header: rdbms/admin/loc_to_common0.sql /main/9 2018/07/25 13:49:55 surman Exp $ 18:13:47 SQL> Rem 18:13:47 SQL> Rem loc_to_common0.sql

.trimmed output

Verify table PDB_PLUG_IN_VIOLATIONS

Check out the table PDB_PLUG_IN_VIOLATIONS in order to see if there are error related to the plug in process, but read first the note "OPTION WARNING Database option mismatch: PDB installed version NULL" in PDB_PLUG_IN_VIOLATIONS (Doc ID 2020172.1)"

[oracle@srv1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 16 18:23:19 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL>col cause for a10 col name for a10 col message for a35 word_wrapped select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TESTDBPDB';SQL> SQL> SQL> SQL> SQL> NAME CAUSE TYPE MESSAGE STATUS ---------- ---------- --------- ----------------------------------- --------- TESTDBPDB Parameter WARNING CDB parameter sga_target mismatch: RESOLVED Previous 2320M Current 3840M TESTDBPDB Parameter WARNING CDB parameter pga_aggregate_target RESOLVED mismatch: Previous 771M Current 1280M TESTDBPDB Non-CDB to ERROR PDB plugged in is a non-CDB, PENDING PDB requires noncdb_to_pdb.sql be run. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 TESTDBPDB MOUNTED SQL> alter session set container=TESTDBPDB; Session altered.

SQL> show con_name CON_NAME ------------------------------ TESTDBPDB SQL> alter database open; Database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 TESTDBPDB READ WRITE NO SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@srv1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 16 18:24:27 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name, open_mode, cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- ORADB READ WRITE YES SQL> col pdb_name for a30 SQL> SELECT pdb_name , status from cdb_pdbs; PDB_NAME STATUS ------------------------------ ---------- PDB1 NORMAL PDB$SEED NORMAL TESTDBPDB NORMAL SQL> col cause for a10 col name for a10 col message for a35 word_wrapped select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TESTDBPDB';SQL> SQL> SQL> SQL> NAME CAUSE TYPE MESSAGE STATUS ---------- ---------- --------- ----------------------------------- --------- TESTDBPDB Parameter WARNING CDB parameter sga_target mismatch: RESOLVED Previous 2320M Current 3840M TESTDBPDB Parameter WARNING CDB parameter pga_aggregate_target RESOLVED mismatch: Previous 771M Current 1280M TESTDBPDB Non-CDB to ERROR PDB plugged in is a non-CDB, RESOLVED PDB requires noncdb_to_pdb.sql be run. SQL> alter session set container=TESTDBPDB; Session altered. SQL> select * from amit.sugi; NAME ROLE ---------- ---------- amit dba pravin dba

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

Amit Oracle DBA Blog

MYSQL

Thursday, 19 October 2023

Install Oracle GoldenGate 19c on Linux 7

Keep learning... Have a great day!!!

Saturday, 16 September 2023

Convert 19c Non CDB Database as PDB Database into CDB

Keep learning... Have a great day!!!