My Oracle DBA notes

Adding Pluggable Databases to an existing Multitenant Data Guard environment.

We can use below document to add a PDB to a RAC Container database which has RAC standby database.

1. Check the source database information.

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. Note down the GUID of the source database incase if we need it check something in standbydatabase as all the datafiles in standby will be created in a directory created with GUID

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

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

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

PL/SQL procedure successfully completed

5. Shutdown the source non-cdb database

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;

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;

NAME OPEN_MODE RECOVERY
---------- ----------------- --------- --------
PDB$SEED MOUNTED ENABLED
BUZZ18C MOUNTED ENABLED

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

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
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

DATABASE_HOST DB_NAME DATABASE_ROLE OPEN_MODE STARTUP_TIME
------------------------------ ---------------- ---------------- -------------------- --------------------
virtualdr-19crac1.localdomain test19cstdby1 PHYSICAL STANDBY MOUNTED 03-FEB-21
virtualdr-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
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.

Alert log 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

10b) Connect to PDB database as belowS

CON_ID CON_NAME OPEN MODE RESTRICTED
--------- ------------------------   ------ ---------- ----------
2 PDB$SEED     MOUNTED
3 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

To address this issue, 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 and bounce the database.

SQL > alter system set cluster_database=false scope=spfile sid='*';
srvctl stop database -d test19c

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
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 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 BUZZ18C READ WRITE NO

12) 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
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
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
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00037 to +DATA
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service test19c
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00038 to +DATA
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service test19c
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00039 to +DATA
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service test19c
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00040 to +DATA
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service test19c
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00041 to +DATA
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 04-FEB-21
RMAN>

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

13) Stop the log apply process on the standby database as below.

DGMGRL> edit database test19cstdby set state='APPLY-OFF';
Succeeded.

DGMGRL> show database test19cstdby
Database - test19cstdby
   Role: PHYSICAL STANDBY
   Intended State: APPLY-OFF
   Transport Lag: 23 minutes 48 seconds (computed 8 seconds ago)
   Apply Lag: 23 minutes 47 seconds (computed 8 seconds ago)
   Average Apply Rate: (unknown)
   Real Time Query: OFF
   Instance(s):
   test19cstdby1 (apply instance)
   test19cstdby2
   Database Warning(s):
   ORA-16855: transport lag has exceeded specified threshold
Database Status:
WARNING

14) Since the replication stopped now, lets switch the standby pdb datafiles as below.

RMAN> switch pluggable database BUZZ18C to copy;

[oracle@chopstdby-19crac1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 4 11:18:34 2021
Version 19.9.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST19C (DBID=2437448537, not open)

RMAN> switch pluggable database BUZZ18C to copy;
using target database control file instead of recovery catalog
datafile 37 switched to datafile copy "+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/system.1316.1063624317"
datafile 38 switched to datafile copy "+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/sysaux.1312.1063624331"
datafile 39 switched to datafile copy "+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/undotbs1.1313.1063624339"
datafile 40 switched to datafile copy "+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/users.1306.1063624353"
datafile 41 switched to datafile copy "+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/undotbs2.1304.1063624355"
RMAN>

14) Enable the PDB recovery on the Standby Container

SQL> ALTER SESSION SET container=BUZZ18C;

Session altered.

SQL> ALTER pluggable DATABASE enable recovery;

Pluggable database altered.

15) Connect to PDB database in Standby CDB and create a dynamic script to move the datafiles to ONLINE.

15a) Generate dynamic SQL. SQL> ALTER SESSION SET container=BUZZ18C;
Session altered.

SQL> set lines 200
scol name for a99
select name,status from v$datafile
select 'ALTER DATABASE datafile ''' || name || ''' online ;' from v$datafile;
Output
-------------------------------------------------------------------------------------------------
NAME STATUS
--------------------------------------------------------------------------------------------------- ---------------

+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/system.1316.1063624317 RECOVER

+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/sysaux.1312.1063624331 RECOVER

+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/undotbs1.1313.1063624339 RECOVER

+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/users.1306.1063624353 RECOVER

+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/undotbs2.1304.1063624355 RECOVER

ALTER DATABASE datafile '+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/system.1316.1063624317' online ;

ALTER DATABASE datafile '+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/sysaux.1312.1063624331' online ;

ALTER DATABASE datafile '+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/undotbs1.1313.1063624339' online ;

ALTER DATABASE datafile '+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/users.1306.1063624353' online ;

ALTER DATABASE datafile '+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/undotbs2.1304.1063624355' online ;

15b) Now run the above ONLINE commands in PDB.

SQL> ALTER DATABASE datafile '+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/system.1316.1063624317' online ;
Database altered.

ALTER DATABASE datafile '+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/sysaux.1312.1063624331' online ;

Database altered.

ALTER DATABASE datafile '+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/undotbs1.1313.1063624339' online ;

Database altered.

ALTER DATABASE datafile '+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/users.1306.1063624353' online ;
Database altered.

ALTER DATABASE datafile '+DATA/TEST19CSTDBY/BA3968236D0B4A71E0535018A8C08092/DATAFILE/undotbs2.1304.1063624355' online ;

Database altered.

16) Now connect to the Standby container and restart the Apply Process as below.

DGMGRL> edit database test19cstdby set state='APPLY-ON';

Succeeded.

DGMGRL> show database test19cstdby;

   Database - test19c
   stdby Role: PHYSICAL STANDBY
     Intended State: APPLY-ON
   Transport Lag: 35 minutes 53 seconds (computed 24 seconds ago)
Apply Lag: 35 minutes 52 seconds (computed 24 seconds ago)
Average Apply Rate: 23.00 KByte/s
Real Time Query: OFF
Instance(s):
test19cstdby1 (apply instance)
test19cstdby2
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
ORA-16855: transport lag has exceeded specified threshold
Database Status:
WARNING

17 ) Now stop the standby CDB database and start it in READ ONLY mode and check if its receiving the logs and if its in SYNC with primary database.

Hope this doc helps you.

Thanks
Sam

My Oracle DBA notes

Search This Blog

Monday, March 8, 2021

No comments:

Post a Comment