CDB

Configure Oracle 12c Dataguard Physical Standby

Oracle dataguard physical standby

Description:

In this post I will describe the step by step configuration of Oracle 12c Dataguard physical standby setup with dataguard broker(DGMGRL) on Oracle Linux 7

EnvironmentDB_NameDB_Unique_name
Primarycdb12ccdb12c
Standbycdb12ccdb12cdr

We will be covering the following steps:

Primary Server setup

  1. Enable archive log mode
  2. Enable Force logging
  3. Create standby redologs SRLs
  4. Check db_name and db_unique_name
  5. Set standby_file_management
  6. TNS and listener Setup
  7. Copy pfile and password to standby

Standby Server setup

  1. Modify the pfile
  2. Create necessary directories
  3. Create standby database using RMAN Duplicate

Configure Dataguard broker

  1. Enable dataguard broker
  2. Register the database with dataguard broker
  3. Add standby database to dataguard broker
  4. Enable configuration

Primary Server setup

1. Enable archive log mode

Ensure the primary database is in archivelog mode. If not enable it.

alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
2. Enable force logging:

Make sure Force logging is enabled on the database.

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;
3. Create standby redo logs on the primary database

Create the standby redo logs to support standby role. The recommended number of SRLs is (Number of redologs + 1). The SRLs size must be same as online redo logs size.

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/oradata/dev12c/std_redo12.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/oradata/dev12c/std_redo13.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/u01/oradata/dev12c/std_redo14.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15 ('/u01/oradata/dev12c/std_redo15.log') SIZE 200M;
4. Check pfile for db_name and db_unique_name

Make sure primary database has DB_UNIQUE_NAME set. If not set it using ALTER SYSTEM SET command.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      dev12c
SQL> show parameter unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      dev12c
SQL>
5. Set STANDBY_FILE_MANAGEMENT to AUTO
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
6. TNS and LISTENER setup(for both primary & standby)

Tnsnames configuration:

DEV12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dev12c)
    )
  )

DEV12CDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db-dr.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dev12c)
    )
  )


LISTENER_DEV12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))
Listener.ora configuration
LISTENER12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

SID_LIST_LISTENER12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dev12c_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
      (SID_NAME = dev12c)
    )
  )
Restart the listener(On both primary & standby)
lsnrctl stop listener12c
lsnrctl start listener12c
7. Copy pfile and password file to standby server
create pfile='/tmp/initdev12c.ora' from spfile;
scp initdev12c.ora oracle@ol7-db-dr:/tmp
scp $ORACLE_HOME/dbs/orapwdev12c oracle@ol7-db-dr:/u01/app/oracle/product/12.2.0/dbhome_1/dbs

Standby Server setup

1. modify the pfile copied from primary and make change for the below parameters
*.db_name=dev12c
*.db_unique_name=dev12cdr
enable_pluggable_database=true
log_file_name_convert='dummy','dummy'
2. Create the necessary directories
mkdir -p /u01/app/oracle/admin/dev12c/adump
mkdir -p /u01/oradata/dev12c/pdbseed
mkdir -p /u01/oradata/dev12c/pdb12c
mkdir -p /u01/app/oracle/fra
mkdir -p /u01/app/oracle/arch
3. Create standby using RMAN DUPLICATE

Start the auxiliary database instance using the pfile

export ORACLE_SID=dev12c
sqlplus "/as sysdba"
startup nomount pfile='/u01/initdev12c.ora';

Connect to RMAN and issue the duplicate command,

rman target sys/idgroup@dev12c auxiliary sys/idgroup@dev12cdr
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Configure Datagurar Broker

1. Enable dataguard broker on both primary & standby
ALTER SYSTEM SET dg_broker_start=true;
2. On primary side: register the database with dataguard broker
dgmgrl sys/idgroup@dev12c
create configuration dgconfig as primary database is dev12c connect identifier is dev12c;

o/p:
[oracle@ol7-db admin]$ dgmgrl sys/idgroup@dev12c
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat Jul 17 12:25:24 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "dev12c"
Connected as SYSDBA.
DGMGRL>
DGMGRL> create configuration dgconfig as primary database is dev12c connect identifier is dev12c;
Configuration "dgconfig" created with primary database "dev12c"
DGMGRL>
3. On primary side: add the standby database
add database dev12cdr as connect identifier is dev12cdr maintained as physical;

o/p:
DGMGRL> add database dev12cdr as connect identifier is dev12cdr maintained as physical;
Database "dev12cdr" added
DGMGRL>
4. On Primary side: enable the configuration
enable configuration;

o/p:
DGMGRL> enable configuration;
Enabled.
DGMGRL>

Check the configuration

DGMGRL> show configuration

Configuration - dgconfig

  Protection Mode: MaxPerformance
  Members:
  dev12c   - Primary database
    dev12cdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 41 seconds ago)
DGMGRL>
DGMGRL> show database dev12c

Database - dev12c

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    dev12c

Database Status:
SUCCESS

DGMGRL>
DGMGRL> show database dev12cdr

Database - dev12cdr

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    dev12c

Database Status:
SUCCESS

DGMGRL>

Hope this helps…

ORA-65093: multitenant container database not set up properly

While starting a container database or mounting a standby container database you may encountered Error: ORA-65093: multitenant container database not set up properly

RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-65093: multitenant container database not set up properly

Solution is add the parameter enable_pluggable_database=true and restart the instance,

enable_pluggable_database=true

Hope this helps…

Unplug and Plugin Pluggable database (PDB)

Unplug and plugin pdb

Oracle has introduced new Multitenant Option from Oracle 12cR1 and this option allows a single container database (CDB) to host multiple pluggable databases.

One of the most powerful feature of Oracle Multitenant is Unplug and Plug In a PDB from one CDB to another CDB.

Refer here for more details on Oracle Multitenant option.

Description:

In this post I will describe how to Unplug and Plugin a PDB from one CDB to another CDB in Oracle 12c. Here I have used 2 CDBs running on the same VM. Below are the details,

Source CDB: CDB2 –> Unplug pdb_db2 from cdb2
Target CDB: CDB1 –> Plug pdb_db2 to cdb1

SQL> select d.name db_name, p.name pdb_name, p.open_Mode from v$pdbs p, v$database d;

DB_NAME   PDB_NAME        OPEN_MODE
--------- --------------- ----------
CDB2      PDB$SEED        READ ONLY
CDB2      PDB1            READ WRITE
CDB2      PDB2            READ WRITE
CDB2      PDB3            READ WRITE
CDB2      PDB_DB2_NEW     READ WRITE
CDB2      PDB_DB2         READ WRITE

SQL> select d.name db_name, p.name pdb_name, p.open_Mode from v$pdbs p, v$database d;

DB_NAME   PDB_NAME        OPEN_MODE
--------- --------------- ----------
CDB1      PDB$SEED        READ ONLY
CDB1      PDB1            READ WRITE

In the below example I will unplug pdb_db2 from CDB2 and plugin to CDB1.

Below are the steps:

Unplug the PDB from CDB
  1. Connect to the source CDB ( as sysdba)
  2. Close the PDB to unplug
  3. Unplug the PDB
  4. Drop the PDB pdb_db2 by preserving the datafiles
  5. Verify the status of the unplugged PDB

Plug In the PDB to CDB

  1. Connect to the target CDB ( as sysdba)
  2. Run DBMS_PDB.CHECK_PLUG_COMPATIBILITY Function
  3. Plug In the PDB using the metadata (xml) file
  4. Open the PDB database

Unplugging the PDB pdb_db2 from CDB cdb2

  1. Connect to the source CDB ( as sysdba)

Set the DB Environment to connect source CDB and connect to CDB$ROOT using sysdba privilege

[oracle@cdb12c ~]$ . oraenv
ORACLE_SID = [cdb2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c ~]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 11 03:44:53 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL>

2. Close the PDB(pdb_db2) to unplug

Before unplugging, the pdb has to be closed.

select name, open_mode from v$pdbs;
alter pluggable database pdb_db2 close;

o/p:
SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE
PDB2            READ WRITE
PDB3            READ WRITE
PDB_DB2_NEW     READ WRITE
PDB_DB2         READ WRITE

6 rows selected.

SQL> alter pluggable database pdb_db2 close immediate;

Pluggable database altered.

3. Unplug the PDB

Unplug the closed PDB and then specify the path and name of the XML file.

alter pluggable database pdb_db2 unplug into '/u01/pdb_clone/pdb_db2.xml';
select name, open_mode from v$pdbs;

o/p:
SQL> alter pluggable database pdb_db2 unplug into '/u01/pdb_clone/pdb_db2.xml';

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE
PDB2            READ WRITE
PDB3            READ WRITE
PDB_DB2_NEW     READ WRITE
PDB_DB2         MOUNTED

6 rows selected.

4. Drop the PDB pdb_db2 by preserving the datafiles

Drop the closed PDB and keep the data files.
Specify KEEP DATAFILES to retain the data files associated with the PDB after the PDB is dropped. The temp file for the PDB is deleted because it is no longer needed. This is the default.

drop pluggable database pdb_db2 keep datafiles;
select name, open_mode from v$pdbs;

o/p:

SQL> drop pluggable database pdb_db2 keep datafiles;

Pluggable database dropped.

SQL>
SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE
PDB2            READ WRITE
PDB3            READ WRITE
PDB_DB2_NEW     READ WRITE

5. Verify the status of the unplugged PDB

select name, open_mode from v$pdbs where name ='PDB_DB2';

SQL> select name, open_mode from v$pdbs where name ='PDB_DB2';

no rows selected

Successfully unplugged the PDB pdb_db2 from CDB cdb2, Now let see how to plug this PDB to CDB1.

Plug In the PDB pdb_db2 to CDB1

  1. Connect to the target CDB ( as sysdba)

Set the environment variable of target CDB and connect to CDB$ROOT as sysdba

.oraenv
sqlplus "/as sysdba"
show con_name

o/p:

[oracle@cdb12c admin]$ . oraenv
ORACLE_SID = [cdb2] ? cdb1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c admin]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 11 04:12:40 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

2. Run DBMS_PDB.CHECK_PLUG_COMPATIBILITY Function

Before Plug In the database make sure the unplugged PDB is compatible with the new host.

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

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

PL/SQL procedure successfully completed.

3. Plug In the PDB using the metadata (xml) file

create pluggable database pdb_db2 using '/u01/pdb_clone/pdb_db2.xml' NOCOPY TEMPFILE REUSE;
select con_id, name, open_mode from v$pdbs;
select name from v$datafile where con_id=4;

o/p:

SQL> create pluggable database pdb_db2 using '/u01/pdb_clone/pdb_db2.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

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

    CON_ID NAME            OPEN_MODE
---------- --------------- ----------
         2 PDB$SEED        READ ONLY
         3 PDB1            READ WRITE
         4 PDB_DB2         MOUNTED

SQL> select name from v$datafile where con_id=4;

NAME
------------------------------------------------
/u01/app/oracle/oradata/cdb2/pdb_db2/CDB2/B92E25114D543C30E053D138A8C0059C/datafile/o1_mf_system_j0c54z1f_.dbf
/u01/app/oracle/oradata/cdb2/pdb_db2/CDB2/B92E25114D543C30E053D138A8C0059C/datafile/o1_mf_sysaux_j0c54z1o_.dbf
/u01/app/oracle/oradata/cdb2/pdb_db2/CDB2/B92E25114D543C30E053D138A8C0059C/datafile/o1_mf_undotbs1_j0c54z1o_.dbf
/u01/app/oracle/oradata/cdb2/pdb_db2/CDB2/B92E25114D543C30E053D138A8C0059C/datafile/o1_mf_users_j0c54z1p_.dbf

4. Open the PDB database

Open and check the status of the plugged PDB.

alter pluggable database pdb_db2 open;
conn faheem/faheem@cbd12c:1521/pdb_db2
select * from tab;

o/p:
SQL> alter pluggable database pdb_db2 open;

Pluggable database altered.

SQL>  conn faheem/faheem@cdb12c:1521/pdb_db2
Connected.

SQL> select * from test;

        ID
----------
         1

We have succesfully unplug and plug pdb_db2 from cdb2 to cdb1.

Hope this helps..

Clone a non-CDB into CDB in Oracle 12c

clone a non-cdb into cdb

Oracle has introduced new Multitenant Option from Oracle 12cR1 and this option allows a single container database (CDB) to host multiple pluggable databases.

Refer here for more details on Oracle Multitenant option.

Related articles:

Create Container Database (CDB) in Oracle 12c using DBCA
Create Container database (CDB) in silent mode
Drop a container database in silent mode
Create a pluggable database manually in Oracle 12c
Clone a pluggable database (PDB) within a CDB in Oracle 12c

Description:

In this post I will describe how to clone a non-CDB database into a CDB in Oracle 12c.

In the below example I have two databases running on the same VM, and will create a new PDB to clone the non-cdb database
db2 – Non-CDB database
cdb2 – CDB database used to clone the non-cdb.
pdb_db2 – new pdb database name which has to clone from non-cdb.

Below are the steps:

  1. Open the non-CDB database into read-only mode
  2. Create directory for new PDB and add new TNS entry to connect non-cdb
  3. Create Database link to allow connection to non-CDB database
  4. Create a new PDB database by cloning the non-cdb using dblink
  5. Execute the noncdb_to_pdb.sql script
  6. Check the newly created PDB status
  7. Open newly created PDB database
1. Open the non-CDB database into read-only mode
export ORACLE_SID=db2
sqlplus "/as sysdba"
shutdown immediate;
startup mount;
alter database open read only;

O/P:
----
[oracle@cdb12c tmp]$ . oraenv
ORACLE_SID = [cdbdev] ? db2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c tmp]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 06:46:34 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

Total System Global Area 2432696320 bytes
Fixed Size                  8623592 bytes
Variable Size             654314008 bytes
Database Buffers         1761607680 bytes
Redo Buffers                8151040 bytes
Database mounted.
SQL>
SQL> alter database open read only;

Database altered.

SQL>
SQL>
2. Create a directory to store new PDB Datafiles and add new TNS entry to connect the non-CDB database
mkdir /u01/app/oracle/oradata/cdb2/pdb_db2

--Add the TNS entry in $ORACLE_HOME/network/admin
db2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cdb12c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db2)
    )
  )
3. Create Database link to allow connection to non-CDB database
export ORACLE_SID=cdb2
sqlplus "/as sysdba"
create database link to_db2_link connect to system identified by idgroup using 'db2';

select name from v$database@to_db2_link;

O/P:
----
[oracle@cdb12c pdb_db2]$ . oraenv
ORACLE_SID = [db2] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c pdb_db2]$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 15:45:17 2021
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>  create database link to_db2_link connect to system identified by idgroup using 'db2';

Database link created.

SQL> select name from v$database@to_db2_link;
NAME
---------
DB2
SQL>
4. Create a new PDB database by cloning the non-CDB using dblink
export ORACLE_SID=cdb2
sqlplus "/as sysdba"
create pluggable database PDB_DB2 from NON$CDB@to_db2_link create_file_dest='/u01/app/oracle/oradata/cdb2/pdb_db2';

Output:
[oracle@cdb12c ~]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 15:50:26 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create pluggable database PDB_DB2 from NON$CDB@to_db2_link
  2  create_file_dest='/u01/app/oracle/oradata/cdb2/pdb_db2';

Pluggable database created.

SQL>
5. Execute the noncdb_to_pdb.sql script
export ORACLE_SID=cdb2
sqlplus "/as sysdba"
alter session set container=PDB_DB2;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

OP:
[oracle@cdb12c ~]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 15:54:02 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=PDB_DB2;

Session altered.

SQL>
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
.
.
<output truncated>
.
.
SQL> set verify OFF
SQL> set wrap ON
SQL> set xmloptimizationcheck OFF
SQL>
4. Check the newly created pdb status

Status of the PDBs can be checked using v$pdbs view,

select name, open_mode from v$pdbs;
show pdbs;

SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE
PDB2            READ WRITE
PDB3            READ WRITE
PDB_DB2         MOUNTED ---> Newly created pdb

6 rows selected.


SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
         6 PDB_DB2                        MOUNTED   -->
SQL>
5. Open the PDB database in read-write mode
--Open newly created pdb:
alter pluggable database pdb_db2 open;
--connect to new pdb
alter session set container=pdb_db2;
select * from faheem.test;

Output:
SQL> alter pluggable database pdb_db2 open;

Pluggable database altered.

SQL> alter session set container=pdb_db2;

Session altered.

SQL> select * from faheem.test;

        ID
----------
         1

1 row selected.

SQL>

We have successfully clone a non-CDB database into a CDB.

Hope this helps…

Clone a pluggable database (PDB) within a CDB in Oracle 12c

clone a pdb within a cdb

Oracle has introduced new Multitenant Option from Oracle 12cR1 and this option allows a single container database (CDB) to host multiple pluggable databases.

Refer here for more details on Oracle Multitenant option.

Related articles:

Create Container Database (CDB) in Oracle 12c using DBCA
Create Container database (CDB) in silent mode
Drop a container database in silent mode
Create a pluggable database manually in Oracle 12c

In this post I will describe how to clone a pluggable database (PDB) within the same CDB.

Here Cloning a new PDB pdb3 from pdb2 within CDB cdb2, we will cover the following steps:

  1. Create a directory to store new PDB datafiles
  2. Set PDB2 (source pdb) in Open Read only mode
  3. Clone PDB from PDB2 to PDB3
  4. Check the newly created PDB status
  5. Open the PDB database
1. Create a directory to store new PDB Datafiles
mkdir /u01/app/oracle/oradata/cdb2/pdb3
2. Set pdb2(source PDB) in Open Read only mode
sqlplus "/as sysdba"
alter pluggable database pdb2 close;
alter pluggable database pdb2 open read only;

o/p:

[oracle@cdb12c /]$ . oraenv
ORACLE_SID = [cdb2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c /]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 18 15:51:40 2021
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter pluggable database pdb2 close;
Pluggable database altered.
SQL> alter pluggable database pdb2 open read only;
Pluggable database altered.
SQL>
3. Clone PDB from PDB2 to PDB3
create pluggable database pdb3 from pdb2
create_file_dest='/u01/app/oracle/oradata/cdb2/pdb3';

Output:

SQL> create pluggable database pdb3 from pdb2
  2  create_file_dest='/u01/app/oracle/oradata/cdb2/pdb3';

Pluggable database created.

SQL>
4. Check the newly created pdb status

Status of the PDBs can be checked using v$pdbs view,

select name, open_mode from v$pdbs;
show pdbs;

SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE
PDB2            READ ONLY
PDB3            MOUNTED  -->newly created pdb

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ ONLY  NO
         5 PDB3                           MOUNTED
SQL>

5. Open the PDB database in read-write mode
--Open source pdb if required:
alter pluggable database pdb2 close;
alter pluggable database pdb2 open;

--Open newly created pdb:
alter pluggable database pdb3 open;

Output:
SQL> alter pluggable database pdb2 close;
Pluggable database altered.
SQL> alter pluggable database pdb2 open;
Pluggable database altered.

SQL> alter pluggable database pdb3 open;
Pluggable database altered.
SQL>

We have successfully cloned PDB3 from PDB2 database and opened it in read-write mode.

Hope this helps…

Create Container database (CDB) in silent mode

create cdb in silent mode

Oracle has introduced new Multitenant Option from Oracle 12cR1 and this option enables an Oracle database to function as a container database(CDB). A CDB includes zero, one, or many pluggable databases. A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

Refer here for more details on Oracle Multitenant option.

Refer here to create the database using DBCA in graphical mode.

In this article I will demonstrate an overview of creating a CDB and PDB database using DBCA in silent mode

1. Set the environmental variables

export ORACLE_SID=cdb2
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin

2. Run dbca in silent mode

dbca -silent -createDatabase -templateName General_Purpose.dbc  -gdbname  cdb2 -sid cdb2 -characterSet AL32UTF8 -sysPassword Oracle_#123 -systemPassword Oracle_#123 -storageType FS -datafileDestination "/u01/app/oracle/oradata" -createAsContainerDatabase true -numberOfPDBs 1 -pdbName pdb1 -pdbAdminPassword Oracle_#123 -databaseType MULTIPURPOSE -memoryMgmtType auto_sga -memoryPercentage 20 -redoLogFileSize 50 -emConfiguration NONE

Output:

[oracle@cdb12c /]$ dbca -silent -createDatabase -templateName General_Purpose.dbc  -gdbname  cdb2 -sid cdb2 -characterSet AL32UTF8 -sysPassword Oracle_#123 -systemPassword Oracle_#123 -storageType FS -datafileDestination "/u01/app/oracle/oradata" -createAsContainerDatabase true -numberOfPDBs 1 -pdbName pdb1 -pdbAdminPassword Oracle_#123 -databaseType MULTIPURPOSE -memoryMgmtType auto_sga -memoryPercentage 20 -redoLogFileSize 50 -emConfiguration NONE
Copying database files
1% complete
13% complete
25% complete
Creating and starting Oracle instance
26% complete
30% complete
31% complete
35% complete
38% complete
39% complete
41% complete
Completing Database Creation
42% complete
43% complete
44% complete
46% complete
49% complete
50% complete
Creating Pluggable Databases
55% complete
75% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb2/cdb2.log" for further details.

3. Check the cdb and pdb status

[oracle@cdb12c oradata]$ . oraenv
ORACLE_SID = [cdbdev] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c oradata]$ sqlplus
Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

NAME      OPEN_MODE
--------- --------------------
CDB2      READ WRITE
SQL>
SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE

SQL>
SQL> select name, pdb from v$services;

NAME            PDB
--------------- ---------------
SYS$BACKGROUND  CDB$ROOT
SYS$USERS       CDB$ROOT
cdb2            CDB$ROOT
pdb1            PDB1
cdb2XDB         CDB$ROOT

SQL>

Hope this helps…

Create Container Database (CDB) in Oracle 12c using DBCA

Create cdb using dbca

Oracle has introduced new Multitenant Option from Oracle 12cR1 and this option enables an Oracle database to function as a container database(CDB). A CDB includes zero, one, or many pluggable databases. A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

Refer here for more details on Oracle Multitenant option.

In this article I will demonstrate an overview of creating a CDB and PDB database using DBCA.

Refer here to Create Container database (CDB) in silent mode

Here I am using VNC viewer for database creation in graphical mode. Refer the link to Install and configure VNC Server on Linux 7

Create CDB using DBCA

Select Create a database and click next

Select Advanced Configuration

Select Oracle Single instance database, leave the template section with default one selected and click on Next

Provide the details as per your requirement,
Global database name:
SID:
Select Create as Container datbase
Number of PDBs:
PDB Name:

Provide the Datafile storage type as per your environment

Specify the Recovery files storage type, Flash Recover Area and Size and click Next

Select the listener if already have one or Create a new one as below

Choose Automatic memory management and provide the Memory Target Size,

Provide the processes Count

Choose the Character set and click on Next

Select Configure Enterprise management option and provide the EM express port. If EM cloud control is in place, provided the details. Then click on Next

Provide the Password and click on Next

Select Create database and click next

Verify the parameters and click on Finish to start the DB creation.

Database has been successfully created, click on close

Connect to database and verify,

[oracle@cdb12c /]$ . oraenv
ORACLE_SID = [cdbdev] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c /]$
[oracle@cdb12c /]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 17 20:47:35 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDBDEV    READ WRITE           YES

SQL>

Check the PDB stauts

SQL> col name for a15
SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE

SQL> select name, pdb, network_name from v$services;

NAME                   PDB             NETWORK_NAME
--------------------   --------------- -------------------------
pdb1.localdomain       PDB1             pdb1.localdomain
SYS$BACKGROUND         CDB$ROOT
SYS$USERS              CDB$ROOT
cdbdevXDB              CDB$ROOT         cdbdevXDB
cdbdev.localdomain     CDB$ROOT         cdbdev.localdomain

SQL>

Container database CDBDEV and Pluggable database PDB1 created successfully.

Hope this helps…