Skip to content

Posts from the ‘Multitenant’ Category

Nov 9

ORA-65036: pluggable database HRPDB not open in required mode

when i trying to clone the pluggable database, i am getting below error

SQL> CREATE PLUGGABLE DATABASE salespdb FROM hrpdb;
CREATE PLUGGABLE DATABASE salespdb FROM hrpdb
*
ERROR at line 1:
ORA-65036: pluggable database HRPDB not open in required mode

Solution :

If a PDB is in local undo mode, then the source PDB can be open in read/write mode during the operation, referred to as hot cloning.

local undo mode
The use of a separate set of undo data files for each PDB in a CDB.

hot cloning
Cloning a PDB while the source PDB is open in read/write mode.

SQL> show pdbs;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

     2 PDB$SEED                       READ ONLY  NO
     3 ORAPDB                         READ WRITE NO
     4 HRPDB                          MOUNTED

SQL> alter pluggable database hrpdb open;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

     2 PDB$SEED                       READ ONLY  NO
     3 ORAPDB                         READ WRITE NO
     4 HRPDB                          READ WRITE NO

SQL> CREATE PLUGGABLE DATABASE salespdb FROM hrpdb;

Pluggable database created.

Nov 1

Container database creation in 19c

You need to download oracle 19c software and setup software installation. After installation, you can able to create a container database with have a pdb.

C:\Users\mazar>set ORACLE_SID=oradb

C:\Users\mazar>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Nov 1 15:55:13 2020
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, CDB, CON_ID FROM V$DATABASE;

NAME CDB CON_ID

ORADB YES 0

SQL> select pdb_name from dba_pdbs;

PDB_NAME

ORAPDB
PDB$SEED

Oct 3

creating a pluggable database from an existing non-CDB Using the DBMS_PDB package

There are three ways of creating a pluggable database from an existing non-CDB:
• Using the DBMS_PDB package to generate metadata and then creating the pluggable database
with CREATE PLUGGABLE DATABASE SQL statement
• Data Pump (using the transportable tablespace feature)
• GoldenGate replication

Note :

When using the DBMS_PDB package to convert a non-CDB to a pluggable database, the non-CDB must be Oracle12c or higher.

Take care of your Non CDB and CDB chraceter set before pluggable database

In Oracle Database 12c, all pluggable databases (PDBs) in a container database (CDB) must have
* the same Database character set (NLS_CHARACTERSET) or the NLS_CHARACTERSET need to be a (Plug-in compatible) binary subset of the CDB NLS_CHARACTERSET
* the same National character set (NLS_NCHAR_CHARACTERSET)  as the CDB’s root container
in order to be able to plug in.

If PDB NLS_CHARACTERSET is a (Plug-in compatible) binary subset of the CDB’s NLS_CHARACTERSET the NLS_CHARACTERSET of the PDB is automatically changed to the NLS_CHARACTERSET of the CDB at the first open.

 

more detail support id : 12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1)

Step 1:  Non CDB in read only mode

SQL> select name from v$database;

NAME
———
ORA12C

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 6710886400 bytes
Fixed Size 5298856 bytes
Variable Size 2835350872 bytes
Database Buffers 3858759680 bytes
Redo Buffers 11476992 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

Step 2: run the DBMS_PDB package to create an XML file that describes the structure of the non-CDB database

SQL> BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => ‘/u01/app/12c/ncdb.xml’);
END;
/ 2 3 4

PL/SQL procedure successfully completed.

Step 3: shutdown the non cdb database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

 

Step 4: connect to cdb container

SQL> show user con_id con_name
USER is “SYS”

CON_ID
——————————
1

CON_NAME
——————————
CDB$ROOT

Step 5: Now, you can optionally check to see if the non-CDB is compatible with the CDB in which it will be plugged.
When you run this code provide the directory and name of the XML file that was created previously

SQL> SET SERVEROUTPUT ON
DECLARE
hold_var boolean;
begin
hold_var := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>’/u01/app/12c/ncdb.xml’);
if hold_var then
dbms_output.put_line(‘YES’);
else
dbms_output.put_line(‘NO’);
end if;
end;SQL> 2 3 4 5 6 7 8 9 10
11 /
YES

PL/SQL procedure successfully completed.

If there are no compatibility issues, a YES is displayed by the prior code; a NO is displayed if the pluggable database
is not compatible. You can query the contents of the PDB_PLUG_IN_VIOLATIONS view for details on why a pluggable
database is not compatible with a CDB.

Step 6: create pluggable database

SQL> CREATE PLUGGABLE DATABASE ora12c
USING ‘/u01/app/12c/ncdb.xml’
2 3 COPY
4 FILE_NAME_CONVERT = (‘/premia/app/12c/oracle/oradata/ora12c/’,
5 ‘/premia/app/12c/oracle/oradata/CDB/ora12c/’,’/premia/app/12c/oracle/product/dbhome_1/dbs/’,’/premia/app/12c/oracle/oradata/CDB/ora12c/’);

Pluggable database created.

Step 7:

SQL> select con_id, name from v$containers;

CON_ID NAME
———- ——————————
1 CDB$ROOT
2 PDB$SEED
3 HRPDB
4 ORA12C

Step 8:  Run the below script

SQL> alter session set container=ora12c;

Session altered.

SQL> show user con_id con_name user
USER is “SYS”

CON_ID
——————————
4

CON_NAME
——————————
ORA12C
USER is “SYS”

SQL> @/premia/app/12c/oracle/product/dbhome_1/rdbms/admin/noncdb_to_pdb.sql

Step 9:

SQL> select con_id,name,open_mode from v$containers;

CON_ID NAME OPEN_MODE
———- —————————— ———-
4 ORA12C MOUNTED

SQL> alter pluggable database ORA12C open;

Pluggable database altered.

Step 10:

SQL> conn test/test@ora12c
Connected.
SQL> select * from emp;

EMPNO
———-
1
2
3
4

4 rows selected.