So I want to create a new pluggable database into the CDBTEST container located into vsi08devpom machine (@vsi08devpom in this post) and I want the exact copy of PDB003 pluggable database currently contained into CDB001 located into my local machine (@localhost in this post).
Why not simply unplug it from my local machine and plug it into the target machine ?
When you want to disconnect a pluggable database from a multitenant container you can unplug it, specifying a file containing its metadata information formatted in XML, used eventually if you want to plug it in another container like we want to do.
To successfully unplug a pdb it must be put first in MOUNTED mode otherwise the following error is thrown:
view plainprint?
SOURCE DB=PDB003
SOURCE MACHINE=localhost
TARGET DB=CDBTEST
TARGET MACHINE=vsi08devpom
====================================================================
ps -ef | grep smon
.oraenv
cdbo1
SQL@localhost> select name, open_mode from V$database;
NAME OPEN_MODE
------------------------------ ----------
CDB01 READ-WRITE
SQL@localhost> select name, open_mode from V$PDBS;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB001 READ WRITE
PDB002 READ WRITE
PDB003 READ WRITE
SQL@localhost>alter sesssion set container =PDB003
SQL@localhost>alter sesssion set container =CDB$ROOT
SQL@localhost> alter pluggable database pdb003 close immediate;
Pluggable database altered.
SQL@localhost> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';
Pluggable database altered.
SQL@localhost> select name, open_mode from V$PDBS;
NAME OPEN_MODE
------------- ----------
PDB$SEED READ ONLY
PDB001 READ WRITE
PDB002 READ WRITE
PDB003 MOUNTED
SQL@localhost> select PDB_NAME, STATUS from CDB_PDBS where PDB_NAME = 'PDB003';
PDB_NAME STATUS
---------- -------------
PDB003 UNPLUGGED
To create a new pluggable database on vsi08devpom machine I have to copy the datafiles of the unplugged database to the target machine.
[oracle@localhost PDB003]$ ll
total 921688
-rw-r-----. 1 oracle oinstall 5251072 Aug 1 14:37 PDB001_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Aug 1 14:37 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Aug 1 14:37 system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Aug 1 14:30 temp01.dbf
[oracle@localhost PDB003]$ scp -r ../PDB003 vsi08devpom.mydomain.it:/opt/app/oracle/oradata/CDBTEST/PDB003
oracle@vsi08devpom.mydomain.it's password:
temp01.dbf 100% 20MB 620.9KB/s 00:33
system01.dbf 100% 260MB 543.4KB/s 08:10
PDB001_users01.dbf 100% 5128KB 1.0MB/s 00:05
sysaux01.dbf 100% 635MB 545.1KB/s 19:53
[oracle@localhost PDB003]$ scp /app/oracle/oradata/pdb003.xml vsi08devpom.mydomain.it:/opt/app/oracle/oradata
oracle@vsi08devpom.mydomain.it's password:
pdb003.xml 100% 4003 3.9KB/s 00:00
Log into the target machine.
My current multitenant container CDBTEST located on vsi08devpom is formed by three pluggable databases:
view plainprint?
[oracle@vsi08devpom CDBTEST]$ export ORACLE_SID=CDBTEST
[oracle@vsi08devpom CDBTEST]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 15:32:55 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL@vsi08devpom> select name, open_mode from V$PDBS;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBTEST1 READ WRITE
PDBTEST2 READ WRITE
PDBTEST3 READ WRITE
SQL@vsi08devpom> select pdb_name, status from CDB_PDBS;
PDB_NAME STATUS
---------- -------------
PDB$SEED NORMAL
PDBTEST1 NORMAL
PDBTEST2 NORMAL
PDBTEST3 NORMAL
Now it's time to plug into CDBTEST container on vsi08devpom machine the unplugged database coming from CDB container (running on my local machine). The source location of the datafiles contained into the XML file is different from the target destination, so I cannot use just the following command, because the error "ORA-27041: unable to open file" is thrown as you can see.
view plainprint?
SQL@vsi08devpom> create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse;
create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' nocopy tempfile reuse
*
ERROR at line 1:
ORA-19505: failed to identify file
"/app/oracle/oradata/CDB001/PDB003/system01.dbf"
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9
I need to appropriately use the SOURCE_FILE_NAME_CONVERT clause in the command:
view plainprint?
SQL@vsi08devpom> create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' source_file_name_convert=('/app/oracle/oradata/CDB001','/opt/app/oracle/oradata/CDBTEST') nocopy tempfile reuse;
Pluggable database created.
The new PDB003 pluggable database on vsi08devpom machine has the same DBID of that one...
view plainprint?
SQL@vsi08devpom> select pdb_name, status, DBID, CON_UID, GUID from CDB_PDBS;
PDB_NAME STATUS DBID CON_UID GUID
---------- ------------- ---------- ---------- --------------------------------
PDB$SEED NORMAL 4063610283 4063610283 E1B2A529DB382EACE0430100007F78B8
PDBTEST1 NORMAL 3064465721 3064465721 E1B436871D9E4110E0430100007F9BBC
PDBTEST2 NORMAL 2395404598 2395404598 E1B43A36FA0B41A9E0430100007F6671
PDBTEST3 NORMAL 2434165039 2434165039 E1B43D98C0DC41F6E0430100007F7CE7
PDB003 NEW 1448206714 1448206714 E2B9BE56B8B936CEE045000000000001
unplugged from my local machine
view plainprint?
SQL@localhost> select pdb_name, status, DBID, CON_UID, GUID from CDB_PDBS;
PDB_NAME STATUS DBID CON_UID GUID
---------- ------------- ---------- ---------- --------------------------------
PDB$SEED NORMAL 4063489868 4063489868 E19363E52C005C9AE045000000000001
PDB001 NORMAL 1701599811 1701599811 E1F26215682E1142E045000000000001
PDB002 NORMAL 1497027100 1497027100 E1F329ECE0F411E6E045000000000001
PDB003 UNPLUGGED 1448206714 1448206714 E2B9BE56B8B936CEE045000000000001
The new plugged database is in MOUNTED mode so I need to open it first:
The new plugged database is in MOUNTED mode so I need to open it first:
view plainprint?
SQL@vsi08devpom> select name, open_mode from V$PDBS;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBTEST1 READ WRITE
PDBTEST2 READ WRITE
PDBTEST3 READ WRITE
PDB003 MOUNTED
SQL@vsi08devpom> alter pluggable database PDB003 open;
Pluggable database altered.
My data is all there as expected!!!
view plainprint?
SQL@vsi08devpom> alter session set container=PDB003;
Session altered.
SQL@vsi08devpom> select count(*) from marcov.T1;
COUNT(*)
----------
100
=====================================================================
ps -ef | grep smon
.oraenv
cdb01
sqlplus / as sysdba
SQL@localhost> select name, open_mode from V$database;
SQL@localhost> select name, open_mode from V$PDBS;
SQL@localhost>alter sesssion set container =PDB003
SQL@localhost>alter sesssion set container =CDB$ROOT
SQL@localhost> alter pluggable database pdb003 close immediate;
SQL@localhost> alter pluggable database PDB003 unplug into '/app/oracle/oradata/pdb003.xml';
SQL@localhost> select name, open_mode from V$PDBS;
SQL@localhost> select PDB_NAME, STATUS from CDB_PDBS where PDB_NAME = 'PDB003';
[oracle@localhost PDB003]$ ll
total 921688
-rw-r-----. 1 oracle oinstall 5251072 Aug 1 14:37 PDB001_users01.dbf
-rw-r-----. 1 oracle oinstall 665853952 Aug 1 14:37 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 272637952 Aug 1 14:37 system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Aug 1 14:30 temp01.dbf
[oracle@localhost PDB003]$ scp -r ../PDB003 vsi08devpom.mydomain.it:/opt/app/oracle/oradata/CDBTEST/PDB003
oracle@vsi08devpom.mydomain.it's password:
temp01.dbf 100% 20MB 620.9KB/s 00:33
system01.dbf 100% 260MB 543.4KB/s 08:10
PDB001_users01.dbf 100% 5128KB 1.0MB/s 00:05
sysaux01.dbf 100% 635MB 545.1KB/s 19:53
[oracle@vsi08devpom CDBTEST]$ export ORACLE_SID=CDBTEST
[oracle@vsi08devpom CDBTEST]$ sqlplus / as sysdba
SQL@vsi08devpom> select name, open_mode from V$PDBS;
SQL@vsi08devpom> select pdb_name, status from CDB_PDBS;
SQL@vsi08devpom> create pluggable database PDB003 using '/opt/app/oracle/oradata/pdb003.xml' source_file_name_convert=('/app/oracle/oradata/CDB001','/opt/app/oracle/oradata/CDBTEST') nocopy tempfile reuse;
SQL@vsi08devpom> select name, open_mode from V$PDBS;
No comments:
Post a Comment