we can use below steps to add a new PDB to an existing RAC CDB which has Physical Standby database configured.
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 standby database 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
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
[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;
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
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 below
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 helps
Thanks
Sambaiah Sammeta
Appreciate it as very informative, clear-cut and practical POST!! Thank you.
ReplyDeleteJust want to confirm "title" says Covert. Hope it is not typo for Convert. Please confirm,
Thank you for pointing it out, I have corrected it :)
Delete