24 September 2020

Move PDB from standby-CDB to a new CDB within 5 minutes

Recently, we had a case were we had the following (simplified) setup:

host 1: CDB01 with PDB01, PDB02

host 2: CDB01-standby with PDB01 and PDB02

Now we wanted to move the PDB02 to a new container, that had to run from host 2. 

host 1: CDB01 with PDB01

host 2: CDB01-standby with PDB01

        CDB02 with PDB02

We wanted to use the standby database for this, because then you don't have to copy the datafiles from host 1 to host 2. In our case, saving about 500Gb scp which would take a lot of time.

We are working with Oracle 12.1 on Linux 7, with regular filesystem (non-ASM) in Single Instance (no RAC).

The following steps have been made. 

Create a new empty container CDB02 on host 2 with responsefile new.rsp:

[GENERAL]

RESPONSEFILE_VERSION = "12.1.0"

OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]

GDBNAME = "CDB02"

SID = "CDB02"

CREATEASCONTAINERDATABASE = true

NUMBEROFPDBS = 0

TEMPLATENAME = "General_Purpose.dbc"

SYSPASSWORD = "Secret#01"

SYSTEMPASSWORD = "Secret#02"

DATAFILEDESTINATION =/u01/oradata

RECOVERYAREADESTINATION=/u02/fast_recovery_area

STORAGETYPE=FS

CHARACTERSET = "AL32UTF8"

NATIONALCHARACTERSET= "AL16UTF16"

 

dbca -createDatabase -silent -responseFile new.rsp

 

After this, you can change memory settings and such whatever you need.

 

On host 1 in CDB01:

alter pluggable database PDB02 close immediate;

alter pluggable database PDB02 unplug into '/home/oracle/PDB02.xml';

drop pluggable database PDB02;

Do NOT drop the datafiles! You will need them on the standby host.

 

Now scp the created xml file to host 2 /home/oracle/.


On host 2 in CDB01-standby:

select name from v$pdbs order by name;

Check that PDB02 is not there.


Now move the datafiles to the new folder:

mv /u01/oradata/CDB01/PDB02 /u01/oradata/CDB02/

 

On host 2 in CDB02:

create pluggable database PDB02 using '/home/oracle/PDB02.xml' source_file_name_convert=('/u01/oradata/CDB01/PDB02','/u01/oradata/CDB02/PDB02') NOCOPY TEMPFILE REUSE; 

Note the NOCOPY keyword. That tells us that the files are already there, we moved them with a linux mv command, and because it is on the same mountpoint, it will be done in blink of an eye.

alter pluggable database PDB02 open; 

select name,open_mode from v$pdbs order by name;

alter pluggable database PDB02 save state;

On host 1:

Cleanup (can be done later) the old datafiles in /u01/oradata/CDB01/PDB02 


No comments:

Post a Comment