Advertisements
Skip to content
April 28, 2018 / Shivananda Rao P

PDB Cloning in a Dataguard Environment

In this article, I’m demonstrating on how or what impact would be seen on the Physical standby database when you clone a Pluggable Database on the Primary.

 

Here is the environment detail.

 

Primary database name: targetdb
Standby database name: tarstdb
PDB name already plugged into CDB: targetpdb
PDB name to be created: targetpdb1

 

Please note that both TARGETPDB and TARGETPDB1 would be under the same container.

 

Before I start, let me give a brief outcome of the setup.
I have DG broker configured for the setup and I could see below that the standby database is in sync with the primary database.

 

[oracle@ora1-1 ~]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration - dgtest

  Protection Mode: MaxPerformance
  Members:
  targetdb - Primary database
    tarstdb  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

 

TARGETPDB1 will be created from TARGETPDB and would be plugged into the same ROOT container as TARGETPDB
Below shows the list of datafiles that TARGETPDB has.

 

SYS@targetdb> select status,instance_Name,database_role from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         targetdb         PRIMARY

 

SYS@targetdb> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TARGETPDB                      READ WRITE NO

		 
SYS@targetdb> alter session set container=targetpdb;

Session altered.

 

SYS@targetdb> col file_name for a99
SYS@targetdb> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/system.272.906204413
+DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/sysaux.290.906204413
+DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/test.271.907334287
+DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/myobjects.291.930751499
+DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/migtbs1.279.930751523
+DATA/TARGETDB/2DBEA8AC28E81AACE0536538A8C0F5B7/DATAFILE/users.273.913039191

6 rows selected.

 

Let me clone TARGETPDB to create TARGETPDB1. In order to clone, I need the TARGETPDB to be opened in READ ONLY mode which is done in the below steps.
Logging into the CDB$ROOT container, I OPEN TARGETPDB in READ ONLY mode followed by which I issue the CREATE PLUGGABLE DATABASE command. Please note that while creating the PDB, I’m also using the clause “STANDBY=NONE”.

 

In case if I was cloning from PDB$SEED, then the datafiles of the newly created PDB would automatically get replicated on the physical standby side too without using the “STANDBY=NONE” clause. But, when a PDB is cloned from another PDB under the same container in a dataguard environment, then, the datafiles may not get replicated to the Physical standby side if the physical standby database is in MOUNT mode. In such a case, use the “STANDBYS=NONE” clause which would defer the recover of the newly created PDB on the physical standby database. The datafiles for the new PDB would be marked as OFFLINE/RECOVER in physical standby database and any additional redo for that PDB will be ignored. This is interpreted as at some point in the future, the PDBs files will be copied to the physical standby database and recovery for the PDB will be enabled.

 

Here are the cloning and creation steps:
On Primary, place the “targetpdb” in read only mode.

 

SYS@targetdb> alter pluggable database targetpdb close immediate;

Pluggable database altered.

SYS@targetdb> alter pluggable database targetpdb open read only;

Pluggable database altered.

SYS@targetdb>

 

Now, create pluggable database “targetpdb1” with the clause “standbys=none”.

 

SYS@targetdb> create pluggable database targetpdb1 from targetpdb standbys=none;

Pluggable database created.

SYS@targetdb>		 

SYS@targetdb> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TARGETPDB                      READ ONLY  NO
         4 TARGETPDB1                     MOUNTED
SYS@targetdb>

 

As seen above, since “targetpdb” is still in READ ONLY mode, we need to revert it back to “READ WRITE” mode. Also, we need to open the newly created pdb “targetpdb1”.

 

SYS@targetdb> alter pluggable database targetpdb close immediate;

Pluggable database altered.

SYS@targetdb> alter pluggable database targetpdb open;

Pluggable database altered.

SYS@targetdb> alter pluggable database targetpdb1 open;

Pluggable database altered.

SYS@targetdb> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TARGETPDB                      READ WRITE NO
         4 TARGETPDB1                     READ WRITE NO
SYS@targetdb>

 

Moving on to the physical standby database side, let’s check the status of the PDBs.

 

SYS@tarstdb> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 TARGETPDB                      MOUNTED
         4 TARGETPDB1                     MOUNTED
SYS@tarstdb>

 

On checking the recovery status of each of the PDBs that are plugged into the physical standby container database, we see that the recovery of the newly created PDB “targetpdb1” is “DISABLED” and this is because we had used the clause “standbys=none” while creating the pdb.

 

SYS@tarstdb> select name, recovery_status from v$pdbs;

NAME                           RECOVERY
------------------------------ --------
PDB$SEED                       ENABLED
TARGETPDB                      ENABLED
TARGETPDB1                     DISABLED

SYS@tarstdb> alter session set container=targetpdb1;

Session altered.

SYS@tarstdb> select name, status from v$datafile;

NAME                                                    STATUS
------------------------------------------------------- -------
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00021  SYSOFF
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00022  RECOVER
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00023  RECOVER
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00024  RECOVER
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00025  RECOVER
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00026  RECOVER

6 rows selected.

 

When I query the v$recovery_file, I see that all the files of the newly created PDB seem to be errored out as “Missing” on the standby.

 

SYS@tarstdb> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                             CHANGE# TIME          CON_ID
---------- ------- ------- ------------------------------ ---------- --------- ----------
        21 OFFLINE OFFLINE FILE MISSING                            0                    4
        22 OFFLINE OFFLINE FILE MISSING                            0                    4
        23 OFFLINE OFFLINE FILE MISSING                            0                    4
        24 OFFLINE OFFLINE FILE MISSING                            0                    4
        25 OFFLINE OFFLINE FILE MISSING                            0                    4
        26 OFFLINE OFFLINE FILE MISSING                            0                    4

6 rows selected.

 

Ok, so how do we get these files on to the standby ? Simple option would be to restore them from the Primary PDB (Targetpdb) through RMAN using the “net service”.

 

I have DG Broker configured for my setup, so just making sure that the standby database is in sync with the primary database.

 

[oracle@ora1-1 ~]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration - dgtest

  Protection Mode: MaxPerformance
  Members:
  targetdb - Primary database
    tarstdb  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 9 seconds ago)

 

Now, it’s time for me to restore the files of the newly created PDB (targetpdb1) from primary to the standby site. I restore it using the “service” clause connecting to the primary.

 

I use the “set newname for pluggable database targetpdb1 to new” to get the OMF format for the datafiles to be restored which would be done to the value specified in the “db_create_file_dest” parameter. Followed by this, is the “restore pluggable database targetpdb1 from service targetdb” suggesting to restore that particular PDB using the NET SERVICE to connect to primary. And finally switching the datafiles restored.

 

[oracle@ora1-4 dbs]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Mar 17 12:22:59 2018

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

connected to target database: TARGETDB (DBID=1297732342, not open)

RMAN> run
2> {
3> set newname for pluggable database targetpdb1 to new;
4> restore pluggable database targetpdb1 from service targetdb;
5> switch datafile all;
6> }

executing command: SET NEWNAME

Starting restore at 17-MAR-18
Starting implicit crosscheck backup at 17-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 17-MAR-18

Starting implicit crosscheck copy at 17-MAR-18
using channel ORA_DISK_1
Crosschecked 11 objects
Finished implicit crosscheck copy at 17-MAR-18

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00021 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00022 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00023 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00024 to +DATA_NEW
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 targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00025 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service targetdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00026 to +DATA_NEW
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 17-MAR-18

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch command at 03/17/2018 12:26:38
ORA-19563: datafile copy header validation failed for file +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/myobjects.282.971012125

RMAN>

 

Quite strange !! The datafiles of “targetpdb1” are restored but errors out while performing the switch operation. This datafile was plugged in as part of transportable tablespace migration. Suspecting that could be the case but unsure why it failed during the execution of the switch command. I shall investigate on that and post in the upcoming days.

 

As a workaround, I thought to rename those files manually. In order to do that, I set the “StandbyFileManagement” parameter to “Manual” on standby database and stop the RECOVERY on the standby database (tarstdb).

 

DGMGRL> edit database tarstdb set property StandbyFileManagement =Manual;
Property "standbyfilemanagement" updated
DGMGRL> edit database tarstdb set state='apply-off';
Succeeded.

 

The datafile status and it’s name for the newly created PDB (targetpdb1) on standby site needs to be captured.

 

SYS@tarstdb> select name,status from v$datafile;

NAME                                                                             STATUS
-------------------------------------------------------------------------------- -------
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00021                           SYSOFF
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00022                           RECOVER
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00023                           RECOVER
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00024                           RECOVER
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00025                           RECOVER
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00026                           RECOVER

6 rows selected.

 

From the alert log of the standby database, I get the OMF format of the files restored in the above step for the new PDB (targetpdb1).

 

Sat Mar 17 13:34:38 2018
Full restore complete of datafile 21 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/system.268.971012063.  Elapsed time: 0:00:13
  checkpoint is 10105360
  last deallocation scn is 8421271
  Undo Optimization current scn is 10090649
Sat Mar 17 13:35:19 2018
Full restore complete of datafile 22 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/sysaux.270.971012089.  Elapsed time: 0:00:30
  checkpoint is 10105405
  last deallocation scn is 4478083
Full restore complete of datafile 23 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/test.281.971012125.  Elapsed time: 0:00:00
  checkpoint is 10105471
  last deallocation scn is 1594145
Full restore complete of datafile 24 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/myobjects.282.971012125.  Elapsed time: 0:00:01
  checkpoint is 10105481
  last deallocation scn is 907776
Full restore complete of datafile 25 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/migtbs1.283.971012127.  Elapsed time: 0:00:00
  checkpoint is 10105484
  last deallocation scn is 907776
Full restore complete of datafile 26 to datafile copy +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/users.263.971012129.  Elapsed time: 0:00:01
  checkpoint is 10105489
  last deallocation scn is 3
Sat Mar 17 13:37:45 2018
Checker run found 13 new persistent data failures

 

I then rename each of these datafiles manually by connecting to the CDB$ROOT of the standby database. I understand that this is a tedious process, but for time being I had to go with this.

 

SYS@tarstdb>  alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00021' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/system.268.971012063';

Database altered.

SYS@tarstdb>  alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00022' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/sysaux.270.971012089';

Database altered.

SYS@tarstdb> alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00023' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/test.281.971012125';

Database altered.

SYS@tarstdb> alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00024' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/myobjects.282.971012125';

Database altered.

SYS@tarstdb> alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00025' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/migtbs1.283.971012127';

Database altered.

SYS@tarstdb> alter database rename file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00026' to '+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/users.263.971012129';

Database altered.

 

Now, let me check the datafile names after renaming them. All looks good as below.

 

SYS@tarstdb>  select name,status from v$datafile;

NAME                                                                                   	STATUS
-------------------------------------------------------------------------------------  	-------
+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/system.268.971012063       	SYSOFF
+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/sysaux.270.971012089       	RECOVER
+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/test.281.971012125         	RECOVER
+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/myobjects.282.971012125		RECOVER                                              +DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/migtbs1.283.971012127		 RECOVER
+DATA_NEW/TARSTDB/6796FDF7366926AEE0536538A8C0B057/DATAFILE/users.263.971012129         RECOVER

6 rows selected.

 

We now need to enable the recovery for the newly created PDB targetpdb1 on standby database. For this, connect to the targetpdb1 container and enable the recovery.

 

SYS@tarstdb> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 TARGETPDB                      MOUNTED
         4 TARGETPDB1                     MOUNTED
SYS@tarstdb>
SYS@tarstdb> alter session set container=targetpdb1;

Session altered.

SYS@tarstdb> alter pluggable database enable recovery;

Pluggable database altered.

 

The changes made above to the parameter “StandbyFileManagement” needs to be reverted back to the value “AUTO” and start MRP on the standby database.

 

DGMGRL> edit database tarstdb set property StandbyFileManagement =AUTO;
Property "standbyfilemanagement" updated
DGMGRL> edit database tarstdb set state='apply-on';
Succeeded.

 

Crosscheck if all looks fine.

 

SYS@tarstdb> select name, recovery_status from v$pdbs;

NAME                           RECOVERY
------------------------------ --------
PDB$SEED                       ENABLED
TARGETPDB                      ENABLED
TARGETPDB1                     ENABLED

 

SYS@tarstdb> alter session set container=targetpdb1;

Session altered.

SYS@tarstdb> col name for a90
SYS@tarstdb>
SYS@tarstdb> select * from v$recovery_status;

no rows selected

 

Verfiy if the standby database is in sync with the primary database.

 

On Primary:

 

SYS@targetdb> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           335

SYS@targetdb> alter system switch logfile;

System altered.

SYS@targetdb> /

System altered.

SYS@targetdb> /

System altered.

SYS@targetdb> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           338

 

On standby:

 

SYS@tarstdb> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING             337
ARCH      CONNECTED             0
ARCH      CLOSING             336
ARCH      CLOSING             338
RFS       IDLE                  0
RFS       IDLE                339
MRP0      APPLYING_LOG        339

7 rows selected.

 

SYS@tarstdb> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
           337

 

All looks good !!

 

 

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

 

 

 

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organisation. The views expressed by visitors on this blog are theirs solely and may not reflect mine

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: