Cloning a PDB with ASM and Data Guard (no ADG) without network transfer

Ok, if you’re reading this post, you may want to read also the previous one that explains something more about the problem.

Briefly said, if you have a CDB running on ASM in a MAA architecture and you do not have Active Data Guard, when you clone a PDB you have to “copy” the datafiles somehow on the standby. The only solution offered by Oracle (in a MOS Note, not in the documentation) is to restore the PDB from the primary to the standby site, thus transferring it over the network. But if you have a huge PDB this is a bad solution because it impacts your network connectivity. (Note: ending up with a huge PDB IMHO can only be caused by bad consolidation. I do not recommend to consolidate huge databases on Multitenant).

So I’ve worked out another solution, that still has many defects and is almost not viable, but it’s technically interesting because it permits to discover a little more about Multitenant and Data Guard.

The three options

At the primary site, the process is always the same: Oracle copies the datafiles of the source, and it modifies the headers so that they can be used by the new PDB (so it changes CON_ID, DBID, FILE#, and so on).

On the standby site, by opposite, it changes depending on the option you choose:

Option 1: Active Data Guard

If you have ADG, the ADG itself will take care of copying the datafile on the standby site, from the source standby pdb to the destination standby pdb. Once the copy is done, the MRP0 will continue the recovery. The modification of the header block of the destination PDB is done by the MRP0 immediately after the copy (at least this is what I understand).

ADG_PDB_copy

Option 2: No Active Data Guard, but STANDBYS=none

In this case, the copy on the standby site doesn’t happen, and the recovery process just add the entry of the new datafiles in the controlfile, with status OFFLINE and name UNKNOWNxxx.  However, the source file cannot be copied anymore, because the MRP0 process will expect to have a copy of the destination datafile, not the source datafile. Also, any tentative of restore of the datafile 28 (in this example) will give an error because it does not belong to the destination PDB. So the only chance is to restore the destination PDB from the primary.
NOADG_PDB_STANDBYS_NONE_copy

Option 3: No Active Data Guard, no STANDBYS=none

This is the case that I want to explain actually. Without the flag STANDBYS=none, the MRP0 process will expect to change the header of the new datafile, but because the file does not exist yet, the recovery process dies.
We can then copy it manually from the source standby pdb, and restart the recovery process, that will change the header. This process needs to be repeated for each datafile. (that’s why it’s not a viable solution, right now).

NOADG_PDB_copy

Let’s try it together:

The Environment

Primary

08:13:08 SYS@CDBATL_2> select db_unique_name, instance_name from v$database, gv$instance;

DB_UNIQUE_NAME                 INSTANCE_NAME
------------------------------ ----------------
CDBATL                         CDBATL_2
CDBATL                         CDBATL_1

Standby

07:35:56 SYS@CDBGVA_2> select db_unique_name, instance_name from v$database, gv$instance;

DB_UNIQUE_NAME                 INSTANCE_NAME
------------------------------ ----------------
CDBGVA                         CDBGVA_1
CDBGVA                         CDBGVA_2

The current user PDB (any resemblance to real people is purely coincidental 😉 #haveUSeenMaaz):

08:14:31 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ';

OPEN_MODE  NAME
---------- ------------------------------
OPEN       MAAZ
OPEN       MAAZ

Cloning the PDB on the primary

First, make sure that the source PDB is open read-only

08:45:54 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;

Pluggable database altered.

08:46:20 SYS@CDBATL_2> alter pluggable database maaz open read only instances=all;

Pluggable database altered.

08:46:32 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ' ;

OPEN_MODE  NAME
---------- ------------------------------
READ ONLY  MAAZ
READ ONLY  MAAZ

Then, clone the PDB on the primary without the clause STANDBYS=NONE:

08:46:41 SYS@CDBATL_2> create pluggable database LUDO from MAAZ;

Pluggable database created.

Review the clone on the Standby

At this point, on the standby the alert log show that the SYSTEM datafile is missing, and the recovery process stops.

Mon Dec 15 17:46:11 2014
Recovery created pluggable database LUDO
Mon Dec 15 17:46:11 2014
Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:
ORA-01565: error in identifying file '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form
Recovery was unable to create the file as:
'+DATA'
MRP0: Background Media Recovery terminated with error 1274
Mon Dec 15 17:46:11 2014
Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765'
Mon Dec 15 17:46:11 2014
Managed Standby Recovery not using Real Time Apply
Mon Dec 15 17:46:11 2014
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.34).
Datafiles are recovered to a consistent state at change 10433175 but controlfile could be ahead of datafiles.
Mon Dec 15 17:46:11 2014
Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765'
Mon Dec 15 17:46:11 2014
MRP0: Background Media Recovery process shutdown (CDBGVA_2)

One remarkable thing, is that in the standby controlfile, ONLY THE SYSTEM DATAFILE exists:

18:02:50 SYS@CDBGVA_2> select con_id from v$pdbs where name='LUDO';

    CON_ID
----------
         4

18:03:10 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME
---------------------------------------------------------------------------
+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765

We need to fix the datafiles one by one, but most of the steps can be done once for all the datafiles.

Copy the source PDB from the standby

What do we need to do? Well, the recovery process is stopped, so we can safely copy the datafiles of  the source PDB from the standby site because they have not moved yet. (meanwhile, we can put the primary source PDB back in read-write mode).

-- on primary
08:58:07 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;

Pluggable database altered.

08:58:15 SYS@CDBATL_2> alter pluggable database maaz open read write instances=all;

Pluggable database altered.

Copy the datafiles:

## on the standby:
RMAN> backup as copy pluggable database MAAZ;

Starting backup at 15-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 instance=CDBGVA_2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00029 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.463.857404625
output file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043 tag=TAG20141215T175041 RECID=54 STAMP=866397046
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00028 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.283.857404623
output file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049 tag=TAG20141215T175041 RECID=55 STAMP=866397051
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 15-DEC-14

Starting Control File and SPFILE Autobackup at 15-DEC-14
piece handle=+DATA/CDBGVA/AUTOBACKUP/2014_12_15/s_866396771.865.866397053 comment=NONE
Finished Control File and SPFILE Autobackup at 15-DEC-14

Do the magic

Now there’s the interesting part: we need to assign the datafile copies of the maaz PDB to LUDO.

Sadly, the OMF will create the copies on the bad location (it’s a copy, to they are created on the same location as the source PDB).

We cannot try to uncatalog and recatalog the copies, because they will ALWAYS be affected to the source PDB. Neither we can use RMAN because it will never associate the datafile copies to the new PDB. We need to rename the files manually.

RMAN> list datafilecopy all;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
55 28 A 15-DEC-14 10295232 14-DEC-14
 Name: +DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.86639709
 Tag: TAG20141215T175041

54 29 A 15-DEC-14 10295232 14-DEC-14
 Name: +DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.86639703
 Tag: TAG20141215T175041


RMAN> select name, guid from v$pdbs;

NAME       GUID
---------- --------------------------------
PDB$SEED   FFBCECBB503D606BE043334EA8C019B7
MAAZ       0243BF7B39D4440AE053334EA8C0E471
LUDO       0A4A0048D5321597E053334EA8C0E40A

It’s better to uncatalog the datafile copies before, so we keep the catalog clean:

RMAN> change datafilecopy '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049' uncatalog;

uncataloged datafile copy
datafile copy file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049 RECID=55 STAMP=866397051
Uncataloged 1 objects


RMAN> change datafilecopy '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043' uncatalog;

uncataloged datafile copy
datafile copy file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043 RECID=54 STAMP=866397046
Uncataloged 1 objects

Then, because we cannot rename files on a standby database with standby file management set to AUTO, we need to put it temporarily to MANUAL.

10:24:21 SYS@CDBGVA_2> alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049';
alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049'
*
ERROR at line 1:
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.
10:27:49 SYS@CDBGVA_2> select name, ispdb_modifiable from v$parameter where name like 'standby%';

NAME                                                         ISPDB
------------------------------------------------------------ -----
standby_archive_dest                                         FALSE
standby_file_management                                      FALSE

standby_file_management is not PDB modifiable, so we need to do it for the whole CDB.

10:31:42 SYS@CDBGVA_2> alter system set standby_file_management=manual;

System altered.

18:05:04 SYS@CDBGVA_2> alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049';

Database altered.



then we need to set back the standby_file_management=auto or the recover will not start:

10:34:24 SYS@CDBGVA_2> alter system set standby_file_management=auto;
System altered.

We can now restart the recovery.

The recovery process will:
– change the new datafile by modifying the header for the new PDB
– create the entry for the second datafile in the controlfile
– crash again because the datafile is missing

18:11:30 SYS@CDBGVA_2> alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 61 is unknown - rename to correct file
ORA-01110: data file 61: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061'
ORA-01157: cannot identify/lock data file 61 - see DBWR trace file
ORA-01111: name for data file 61 is unknown - rename to correct file
ORA-01110: data file 61: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061'


18:11:33 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME
---------------------------------------------------------------------------
+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049
/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061

We already have the SYSAUX datafile, right? So we can alter the name again:

18:14:21 SYS@CDBGVA_2> alter system set standby_file_management=manual;

System altered.

18:14:29 SYS@CDBGVA_2> alter database rename file '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043';

Database altered.

18:14:31 SYS@CDBGVA_2> alter system set standby_file_management=auto;

System altered.

18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;

This time all the datafiles have been copied (no user datafile for this example) and the recovery process will continue!! 🙂 so we can hit ^C and start it in background.

18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-16043: Redo apply has been canceled.
ORA-01013: user requested cancel of current operation

 

18:18:10 SYS@CDBGVA_2> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

18:18:19 SYS@CDBGVA_2>

The Data Guard configuration reflects the success of this operation.

Do we miss anything?

Of course, we do!! The datafile names of the new PDB reside in the wrong ASM path. We need to fix them!

18:23:07 SYS@CDBGVA_2> alter database recover managed standby database cancel;

Database altered.

RMAN> backup as copy pluggable database ludo;

Starting backup at 15-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=60 instance=CDBGVA_2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00061 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043
output file name=+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933 tag=TAG20141215T182213 RECID=56 STAMP=866398937
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00060 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049
output file name=+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941 tag=TAG20141215T182213 RECID=57 STAMP=866398943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 15-DEC-14

Starting Control File and SPFILE Autobackup at 15-DEC-14
piece handle=+DATA/CDBGVA/AUTOBACKUP/2014_12_15/s_866398689.868.866398945 comment=NONE
Finished Control File and SPFILE Autobackup at 15-DEC-14

RMAN> switch pluggable database ludo to copy;

using target database control file instead of recovery catalog
datafile 60 switched to datafile copy "+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941"
datafile 61 switched to datafile copy "+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933"

18:23:54 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941
+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933

 

I know there’s no practical use of this procedure, but it helps a lot in understanding how Multitenant has been implemented.

I expect some improvements in 12.2!!

Cheers

Ludo

 

Tales from the Demo Grounds part 2: cloning a PDB with ASM and Data Guard (no ADG)

In my #OOW14 presentation about MAA and Multitenant, more precisely at slide #59, “PDB Creation from other PDB without ADG*”, I list a few commands that you can use to achieve a “correct” Pluggable Database clone in case you’re not using Active Data Guard.

What’s the problem with cloning a PDB in a MAA environment without ADG? If you’ve attended my session you should know the answer…

If you read the book “Data Guard Concepts and Administration 12c Release 1 (12.1)“, paragraph 3.5 Creating a PDB in a Primary Database, you’ll see that:

If you plan to create a PDB as a clone from a different PDB, then copy the data files that belong to the source PDB over to the standby database. (This step is not necessary in an Active Data Guard environment because the data files are copied automatically when the PDB is created on the standby database.)

But because there are good possibilities (99%?) that in a MAA environment you’re using ASM, this step is not so simple: you cannot copy the datafiles exactly where you want, it’s OMF, and the recovery process expects the files to be where the controlfile says they should be.

So, if you clone the PDB, the recovery process on the standby doesn’t find the datafiles at the correct location, thus the recovery process will stop and will not start until you fix manually. That’s why Oracle has implemented the new syntax “STANDBYS=NONE” that disables the recovery on the standby for a specific PDB: it lets you disable the recovery temporarily while the recovery process continues to apply logs on the remaining PDBs. (Note, however, that this feature is not intended as a generic solution for having PDBs not replicated. The recommended solution in this case is having two distinct CDBs, one protected by DG, the other not).

With ADG, when you clone the PDB on the primary, on the standby the ADG takes care of the following steps, no matter if on ASM or FS:

  1. recover up to the point where the file# is registered in the controlfile
  2. copy the datafiles from the source DB ON THE STANDBY DATABASE (so no copy over the network)
  3. rename the datafile in the controlfile
  4. continue with the recovery

If you don’t have ADG, and you’re on ASM, Oracle documentation says nothing with enough detail to let you solve the problem. So in August I’ve worked out the “easy” solution that I’ve also included in my slides (#59 and #60):

  1. SQL> create pluggable database DEST from SRC standbys=none;
  2. RMAN> backup as copy pluggable database DEST format ‘/tmp/dest%f.dbf’;
  3. $ scp  /tmp/dest*.dbf remote:/tmp
  4. RMAN> catalog start with ‘/tmp/dest’
  5. RMAN> set newnamefor pluggable database DEST to new;
  6. RMAN> restore pluggable database DEST;
  7. RMAN> switch pluggable database DEST to copy;
  8. DGMGRL> edit database ‘STBY’ set state=’APPLY-OFF’;
  9. SQL> Alter pluggable database DEST enable recovery;
  10. DGMGRL> edit database ‘STBY’ set state=’APPLY-ON’;

Once at #OOW14, after endless conversations at the Demo Grounds, I’ve discovered that Oracle has worked out the very same solution requiring network transfer and that it has been documented in a new note.

Making Use of the STANDBYS=NONE Feature with Oracle Multitenant (Doc ID 1916648.1)

This note is very informative and I recommend to read it carefully!

What changes (better) in comparison with my first solution, is that Oracle suggests to use the new feature “restore from service”:

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

I’ve questioned the developers at the Demo Grounds about the necessity to use network transfer (I had the chance to speak directly with the developer who has written this piece of code!! :-)) and they said that they had worked out only this solution. So, if you have a huge PDB to clone, the network transfer from the primary to standby may impact severely your Data  Guard environment and/or your whole infrastructure, for the time of the transfer.

Of course, I have a complex, undocumented solution, I hope I will find the time to document it, so stay tuned if you’re curious! 🙂

Tales from Demo Grounds part 1: Clone PDBs while open READ-WRITE

DISCLAIMER: I’ve got this information by chatting with Oracle developers at the Demo Grounds. The functionality is not documented yet and Oracle may change it at its sole discretion. Please refer to the documentation if/when it will be updated 😉

In one of my previous posts named “A PDB is cloned while in read-write, Data Guard loose its marbles (12.1.0.2, ORA-19729)” I’ve blogged about a weird behaviour:

The documentation states that you can create a pluggable database from another one only if the source PDB is open read-only.

Indeed, If I try to clone it when the source PDB is MOUNTED, I get error ORA-65036:

15:44:24 SYS@CDBATL_1> select inst_id, name, open_mode from gv$pdbs where name='MAAZ';

INST_ID NAME OPEN_MODE
---------- ------------------------------ ----------
1 MAAZ MOUNTED
2 MAAZ MOUNTED

15:53:43 SYS@CDBATL_1> create pluggable database ludo from maaz;
create pluggable database ludo from maaz
*
ERROR at line 1:
ORA-65036: pluggable database MAAZ not open in required mode

The weird behavior is that if you do it when the source is in read-write mode, it works from release 12.1.0.2 (onward?)

I’ve questioned the developers at the DEMO Grounds and they have confirmed that:

  • With the 12.1.0.2, they have initially planned to disclose this functionality (clone PDBS in READ-WRITE).
  • That they had problems in making it work with an Active Data Guard environment (a-ah! so my post was not completely wrong)
  • Finally they have released it as undocumented feature
  • In the next release “they will fix it, maybe” and document it
  • The process of cloning the PDB anyway freeze the transactions on the source

I hope that this update helps clarifying both the behavior and my previous post about this problem! 🙂

Cheers

Ludo