My Oracle DBA notes: February 2021

Adding Pluggable Databases to an existing Multitenant Data Guard environment

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

1. Check the source database information.

set lines 200
col DATABASE_HOST for a60;
col HOST_NAME for a15;
col DATABASE_ROLE for a10
col OPEN_MODE for a10
col STARTUP_TIME for a20
SELECT i.HOST_NAME "DATABASE_HOST" ,i.INSTANCE_NAME "DB_NAME", d.DATABASE_ROLE " DATABASE_ROLE", d.OPEN_MODE " OPEN_MODE ", STARTUP_TIMEfrom GV$DATABASE d, gv$instance i where i.INST_ID=d.INST_ID;

DATABASE_HOST DB_NAME DATABASE_ROLE OPEN_MODE STARTUP_TIME

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

virtual-19crac1.localdomain buzz18c1 PRIMARY READ WRITE 04-FEB-21
virtual-19crac2.localdomain buzz18c2 PRIMARY READ WRITE 04-FEB-21

2. Notedown the GUID of the source databsae incase if we need it

SQL> select guid from v$containers;

GUID
--------------------------------
BA3968236D0B4A71E0535018A8C08092

3. Shutdown the non-CDB primary cleanly and then restart only one instance in READ ONLY mode

[oracle@virtual-19crac1 ~]$ srvctl stop database -d buzz18c

[oracle@virtual-19crac1 ~]$ srvctl status database -d buzz18c
Instance buzz18c1 is not running on node virtual-19crac1
Instance buzz18c2 is not running on node virtual-19crac2

[oracle@virtual-19crac1 ~]$ srvctl start instance -d buzz18c -n virtual-19crac1 -o "READ ONLY"

[oracle@virtual-19crac1 ~]$ srvctl status database -d buzz18c
Instance buzz18c1 is running on node virtual-19crac1
Instance buzz18c2 is not running on node virtual-19crac2

[oracle@virtual-19crac1 ~]$sqlplus / as sysdba

set lines 200

col DATABASE_HOST for a30;

col HOST_NAME for a15;

col DATABASE_ROLE for a10

col OPEN_MODE for a10

col STARTUP_TIME for a20

SELECT i.HOST_NAME "DATABASE_HOST" ,i.INSTANCE_NAME "DB_NAME", d.DATABASE_ROLE " DATABASE_ROLE", d.OPEN_MODE " OPEN_MODE ", STARTUP_TIME

from GV$DATABASE d, gv$instance i where i.INST_ID=d.INST_ID;

DATABASE_HOST DB_NAME DATABASE_ROLE OPEN_MODE STARTUP_TIME

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

virtual-19crac1.localdomain buzz18c1 PRIMARY READ ONLY 04-FEB-21

4. In the source non-cdb database, creat a manifest file to plug in.

mkidr -p /u01/software/migration

exec dbms_pdb.describe('/u01/software/migration/buzz18c.xml');

Output:

----------

SQL> exec dbms_pdb.describe('/u01/software/migration/buzz18c.xml');

PL/SQL procedure successfully completed

5. Shutdown the source non-cdb database

[oracle@virtual-19crac1 ~]$ srvctl stop database -d buzz18c

[oracle@virtual-19crac1 ~]$ srvctl status database -d buzz18c
Instance buzz18c1 is not running on node virtual-19crac1
Instance buzz18c2 is not running on node virtual-19crac2

6. Create the PDB in the target CDB database as below using "Standbys=NONE" options.

SQ> create pluggable database buzz18c USING '/u01/software/migration/buzz18c.xml' tempfile reuse NOCOPY standbys=NONE;

Output:
-------------

SQL> create pluggable database buzz18c USING '/u01/software/migration/buzz18c.xml' tempfile reuse NOCOPY standbys=NONE;

Pluggable database created.

7. Check the status of the PDB

set lines 200
col open_mode for a15
col name for a10
SELECT name,open_mode,recovery_status FROM v$pdbs;

Output

---------

NAME OPEN_MODE RECOVERY

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

PDB$SEED MOUNTED ENABLED

TESTPDB READ WRITE ENABLED

TEST121 READ WRITE ENABLED

WALLE121 READ WRITE ENABLED

BUZZ18C MOUNTED ENABLED

8. Open the database in upgrade mode and check the status again.

ALTER pluggable DATABASE BUZZ18C OPEN upgrade;

output

------------------
SQL> ALTER pluggable DATABASE BUZZ18C OPEN upgrade;

Pluggable database altered.

SQL> SELECT name,open_mode,recovery_status FROM v$pdbs;

NAME OPEN_MODE RECOVERY

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

PDB$SEED MOUNTED ENABLED

TESTPDB READ WRITE ENABLED

TEST121 READ WRITE ENABLED

WALLE121 READ WRITE ENABLED

BUZZ18C MIGRATE ENABLED

9. AS you can see that the PDB was plugged and it is in MIGRATE mode in Primary CDB database. Lets check its status in CDB Standby database

set lines 200

col DATABASE_HOST for a30;

col HOST_NAME for a15;

col DATABASE_ROLE for a10

col OPEN_MODE for a10

col STARTUP_TIME for a20

SELECT i.HOST_NAME "DATABASE_HOST" ,i.INSTANCE_NAME "DB_NAME", d.DATABASE_ROLE " DATABASE_ROLE", d.OPEN_MODE " OPEN_MODE ", STARTUP_TIME

from GV$DATABASE d, gv$instance i where i.INST_ID=d.INST_ID;

DATABASE_HOST DB_NAME DATABASE_ROLE OPEN_MODE STARTUP_TIME

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

chopstdby-19crac1.localdomain test19cstdby1 PHYSICAL STANDBY MOUNTED 03-FEB-21

chopstdby-19crac2.localdomain test19cstdby2 PHYSICAL STANDBY MOUNTED 03-FEB-21

SQL> set lines 200

col open_mode for a15

col name for a10

SELECT name,open_mode,recovery_status FROM v$pdbs;

NAME OPEN_MODE RECOVERY

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

PDB$SEED MOUNTED ENABLED

TESTPDB MOUNTED ENABLED

TEST121 MOUNTED ENABLED

WALLE121 MOUNTED ENABLED

BUZZ18C MOUNTED DISABLED

Note: As you can see that in standby database of CDB , the status of newly created PDB is MOUNT and the RECOVERY status is DISABLED.

If You check the alert log of standby database, you can see that the PDB datafiles are stored as 'UNAMED' . Check below.

output

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

Resize operation completed for file# 3, old size 1249280K, new size 1259520K

2021-02-04T10:08:34.296502-06:00

MRP0 (PID:1903): Media Recovery Log +DATA/TEST19CSTDBY/ARCHIVELOG/2021_02_04/thread_1_seq_255.1633.1063616535

Recovery created pluggable database BUZZ18C

BUZZ18C(6):File #37 added to control file as 'UNNAMED00037'. Originally created as:

BUZZ18C(6):'+DATA/BUZZ18C/DATAFILE/system.1969.1063291795'

BUZZ18C(6):because the pluggable database was created with nostandby

BUZZ18C(6):or the tablespace belonging to the pluggable database is

BUZZ18C(6):offline.

BUZZ18C(6):File #38 added to control file as 'UNNAMED00038'. Originally created as:

BUZZ18C(6):'+DATA/BUZZ18C/DATAFILE/sysaux.1762.1063291831'

BUZZ18C(6):because the pluggable database was created with nostandby

BUZZ18C(6):or the tablespace belonging to the pluggable database is

BUZZ18C(6):offline.

BUZZ18C(6):File #39 added to control file as 'UNNAMED00039'. Originally created as:

BUZZ18C(6):'+DATA/BUZZ18C/DATAFILE/undotbs1.1756.1063291857'

BUZZ18C(6):because the pluggable database was created with nostandby

BUZZ18C(6):or the tablespace belonging to the pluggable database is

BUZZ18C(6):offline.

10) At this stage, lets focus on PRIMARY PDB database . Since the PDB database has been plugged , lets continue with the steps to convert the non-CDB into a PDB by

connecting to the PDB and running $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql.

10a) Connect to Primary CDB database

SQL> set lines 200

col DATABASE_HOST for a30;
col HOST_NAME for a15;
col DATABASE_ROLE for a10
col OPEN_MODE for a10
col STARTUP_TIME for a20

SELECT i.HOST_NAME "DATABASE_HOST" ,i.INSTANCSQL> SQL> SQL> SQL> SQL> SQL> E_NAME "DB_NAME", d.DATABASE_ROLE " DATABASE_ROLE", d.OPEN_MODE " OPEN_MODE ", STARTUP_TIME

from GV$DATABASE d, gv$instance i where i.INST_ID=d.INST_ID; 2

DATABASE_HOST DB_NAME DATABASE_ROLE OPEN_MODE STARTUP_TIME

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

virtual-19crac1.localdomain test19c1 PRIMARY READ WRITE 03-FEB-21

virtual-19crac2.localdomain test19c2 PRIMARY READ WRITE 03-FEB-21

10b) Connect to PDB database as belowS

QL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED MOUNTED

3 TESTPDB READ WRITE NO

4 TEST121 READ WRITE NO

5 WALLE121 READ WRITE NO

6 BUZZ18C MIGRATE YES

SQL> alter session set container=BUZZ18C;
Session altered.

10c) Run the below script to convert non-cdb to pdb.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Generally this script wont fail, but in my case, I had one parameter 'STRING_MAX_SIZE' set in CDB level and because of this parameter my command failed at the last step. Below is the error message.

Last few lines from above command

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

10:19:47 SQL> alter pluggable database "&pdbname" open restricted;

alter pluggable database "BUZZ18C" open restricted

ERROR at line 1:

ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database

BUZZ18C

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.9.0.0.0

[oracle@virtual-19crac1 migration]$ sqlplus / as sysdba

PLEASE FOLLOW below sub steps ONLY if you got the above error. If you didnt get the above error, you can go the step 11.

So I have start the CDB primary database in upgrade mode and then open this PDB in upgrade mode and run the below script.

10c1) Connect to primary database CDB and set cluster_database to false

SQL > alter system set cluster_database=false scope=spfile sid='*';

10c2) start the CDB primary database in upgrade mode

SQL> startup upgrade

10c) Open the new PDB in upgrade mode

SQL> alter pluggable database BUZZ18C open upgrade;

10c) Connect to the new PDB and run below script related to MAX_STRING_SIZE

SQL> alter session set container=BUZZ18C;

SQL> show con_name

CON_NAME

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

BUZZ18C

@?/rdbms/admin/utl32k.sql

10d) set cluster_database back to TRUE and shutdown the CDB primary

SQL> alter system set cluster_database=true scope=spfile sid='*';

SQL> shut immediate;

10e) Start the CDB primary database using srvctl

[oracle@virtual-19crac1 migration]$ srvctl start database -d test19c
[oracle@virtual-19crac1 migration]$ srvctl status database -d test19c

Instance test19c1 is running on node virtual-19crac1
Instance test19c2 is running on node virtual-19crac2

10d) open the PDB in upgrade and re-run the noncdb_to_pdb scrip

SQL> ALTER pluggable DATABASE BUZZ18C OPEN upgrade;

Pluggable database altered.

SQL> SELECT name,open_mode,recovery_status FROM v$pdbs;

NAME OPEN_MODE RECOVERY

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

PDB$SEED MOUNTED ENABLED

TESTPDB READ WRITE ENABLED

TEST121 READ WRITE ENABLED

WALLE121 READ WRITE ENABLED

BUZZ18C MIGRATE ENABLED

SQ>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

11) Connect the to Primary CDB and open the new PDB database.SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED MOUNTED

3 TESTPDB READ WRITE NO

4 TEST121 READ WRITE NO

5 WALLE121 READ WRITE NO

6 BUZZ18C MOUNTED

SQL> alter pluggable database BUZZ18c OPEN;
Pluggable database altered.

SQL>show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED MOUNTED

3 TESTPDB READ WRITE NO

4 TEST121 READ WRITE NO

5 WALLE121 READ WRITE NO

6 BUZZ18C READ WRITE NO

11) In the Standby Container Restore PDB from Primary as below.

run

{

set newname for pluggable database BUZZ18C to new;

restore pluggable database BUZZ18C from service test19c;

}

Output

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

[oracle@chopstdby-19crac1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 4 11:11:50 2021

Version 19.9.0.0.0

connected to target database: TEST19C (DBID=2437448537, not open)

RMAN> run

{

set newname for pluggable database BUZZ18C to new;

restore pluggable database BUZZ18C from service test19c;

}

2> 3> 4> 5>

executing command: SET NEWNAME

Starting restore at 04-FEB-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=71 instance=test19cstdby1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: using network backup set from service test19c