Convert 19c Non CDB Database as PDB Database into CDB

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

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

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

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

Install EM Agent On Exadata Using Oracle Enterprise Manager 13c

Overview

Oracle Exadata Database Machine consists of several components such as Compute nodes, Storage Cells, ILOM, Infiniband Switches, Cisco Switch and PDUs. So we need a tool that can manage all these components from one single console. Oracle Enterprise manager Cloud Control is the recommended best practices for monitoring and managing Exadata Database machine. Once the Exadata database Machine is installed the next step is enable monitoring for it.

The following Exadata components can be monitored and managed by OEM:

• Compute Nodes
• Storage Cells
• Infiniband Switches
• Cisco Switches
• Power Distribution Units
• KVM

The first step in monitoring and managing Exadata using OEM is to install the EM Agent. You can install EM Agent in several ways, such as:

• Using EM Kit Method
• Using the Agent push method
• Using an RPM file
• Using the AgentPull script
• Using the AgentDeploy script

In this article we will demonstrate how to Install EM Agent on Exadata using Agent Push method using OEM 13c. Agent Software is installed only on Compute nodes.

Environment Details

Here we will be installing EM Agent on a Exadata V2 Full Rack consists of:

• 8 Compute Nodes
• 14 Storage Cells
• 3 Infiniband Switches
• 1 Cisco Switches
• 2 Power Distribution Units
• 1 KVM

Procedure to Install EM Agent on Exadata Compute Nodes

Enter the OEM 13c URL into the web browser and hit enter

Enter the SYSMAN Credentials or any other user that have the necessary permissions to Install Agent Software

We are on the OEM 13c Home page now

From the Home page, click on Setup à Add Target à Add Targets manually

Click on Install Agent on Host

On this page, click on the +Add button to add the Host targets

Enter all fully qualified Hostnames (8 compute nodes) and for Platform “Same for All Hosts” and click next

Enter the Installation Base Directory.

The instance base directory will be populated automatically for you.

Click on the + symbol on the Named credentials and enter oracle user and its password.

Click on the + symbol on the Root credentials line and enter root user and its password.

Click next

Click Deploy Agent

The Agent Installation process started

Initialization in progress

The Remote prerequisite check shows warning. This can be ignored as oracle user doesn’t have sudo permissions to run the root.sh script. The can be ran at the end.

Click Continue à Continue, All Hosts

Installation process completed. Click Done.

Connect to Exadata compute node 1 and run the root.sh script as follows:

[root@dm01db01 ~]# dcli -g dbs_group -l root '/u01/app/oracle/product/Agent13c/agent_13.2.0.0.0/root.sh'

dm01db01: Finished product-specific root actions.

dm01db01: /etc exist

dm01db02: Finished product-specific root actions.

dm01db02: /etc exist

dm01db03: Finished product-specific root actions.

dm01db03: /etc exist

dm01db04: Finished product-specific root actions.

dm01db04: /etc exist

dm01db05: Finished product-specific root actions.

dm01db05: /etc exist

dm01db06: Finished product-specific root actions.

dm01db06: /etc exist

dm01db07: Finished product-specific root actions.

dm01db07: /etc exist

dm01db08: Finished product-specific root actions.

dm01db08: /etc exist

Verify Agent is running on Exadata compute nodes.

dm01db01-orcl1 {/home/oracle}:/u01/app/oracle/product/Agent13c/agent_inst/bin/emctl status agent

Oracle Enterprise Manager Cloud Control 13c Release 2

Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.

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

Agent Version          : 13.2.0.0.0

OMS Version            : 13.2.0.0.0

Protocol Version       : 12.1.0.1.0

Agent Home             : /u01/app/oracle/product/Agent13c/agent_inst

Agent Log Directory    : /u01/app/oracle/product/Agent13c/agent_inst/sysman/log

Agent Binaries         : /u01/app/oracle/product/Agent13c/agent_13.2.0.0.0

Core JAR Location      : /u01/app/oracle/product/Agent13c/agent_13.2.0.0.0/jlib

Agent Process ID       : 4544

Parent Process ID      : 4424

Agent URL              : https://dm01db01.int.thomsonreuters.com:3872/emd/main/

Local Agent URL in NAT : https://dm01db01.int.thomsonreuters.com:3872/emd/main/

Repository URL         : https://oem13c.domain.com:1159/empbs/upload

Started at             : 2016-12-29 04:40:07

Started by user        : oracle

Operating System       : Linux version 2.6.39-400.248.3.el6uek.x86_64 (amd64)

Number of Targets      : 3

Last Reload            : (none)

Last successful upload                       : 2016-12-29 04:44:54

Last attempted upload                        : 2016-12-29 04:44:54

Total Megabytes of XML files uploaded so far : 0.33

Number of XML files pending upload           : 0

Size of XML files pending upload(MB)         : 0

Available disk space on upload filesystem    : 17.84%

Collection Status                            : Collections enabled

Heartbeat Status                             : Ok

Last attempted heartbeat to OMS              : 2016-12-29 04:47:11

Last successful heartbeat to OMS             : 2016-12-29 04:47:11

Next scheduled heartbeat to OMS              : 2016-12-29 04:48:12

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

Agent is Running and Ready

Verify that Exadata computes are visible in OEM 13c

Next, we will discover Exadata Database Machine in OEM 13c

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