12c:将一个CDB的所有PDB,导入到另一个CDB中
本文一样是读书时的练习:
开始前,说明下:
在我的环境中有两个CDB:
源:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
[oracle@rhel7 trace]$ env | grep SID ORACLE_SID=conme [oracle@rhel7 trace]$ [oracle@rhel7 trace]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 13:41:52 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select name,cdb from v$database; NAME CDB --------- --- CONME YES SQL> SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 2 PDB$SEED READ ONLY 3 PDBME MOUNTED 4 PDBSHE MOUNTED 5 PDBSHE2 MOUNTED 6 PDBORCL MOUNTED SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@rhel7 trace]$ |
目标:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
[oracle@rhel7 controlfile]$ env | grep SID ORACLE_SID=callah [oracle@rhel7 controlfile]$ [oracle@rhel7 controlfile]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 13:42:40 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> SQL> select name,cdb from v$database; NAME CDB --------- --- CALLAH YES SQL> SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 2 PDB$SEED READ ONLY SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@rhel7 controlfile]$ |
在源端操作:
Unplug PDB:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
[oracle@rhel7 trace]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 14:03:45 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string conme SQL> SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBME MOUNTED PDBSHE MOUNTED PDBSHE2 MOUNTED PDBORCL MOUNTED SQL> SQL> alter pluggable database pdbme unplug into '/home/oracle/pdbme.xml'; Pluggable database altered. SQL> SQL> col pdb_name for a20 SQL> SQL> select pdb_name,status from cdb_pdbs; PDB_NAME STATUS -------------------- --------- PDBME UNPLUGGED PDB$SEED NORMAL PDBSHE NORMAL PDBSHE2 NORMAL PDBORCL NORMAL SQL> SQL> drop pluggable database pdbme keep datafiles; Pluggable database dropped. SQL> select pdb_name,status from cdb_pdbs; PDB_NAME STATUS -------------------- --------- PDB$SEED NORMAL PDBSHE NORMAL PDBSHE2 NORMAL PDBORCL NORMAL SQL> SQL> !ls -ltr /home/oracle total 20 -rw-r--r-- 1 oracle oinstall 364 Apr 19 14:49 cdb_callah.ora.env drwxr-xr-x 2 oracle oinstall 92 Apr 23 09:25 backup -rw-r--r-- 1 oracle oinstall 5524 Apr 23 14:05 pdbme.xml SQL> |
按照上面的方法,将其他的计哥PDB(在我的环境里,它们是:pdbshe/pdbshe1/pdborcl),依次下线。
注意,上面的unplug操作需要在PDB处于“Close”的状态才能够执行成功。
如果当前的PDB的状态不是“Close”,你可以执行下面的命令,关闭它:
alter pluggable database [pdb_name] close immediate;
最后,操作完成后的状态:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select pdb_name,status from cdb_pdbs; PDB_NAME STATUS -------------------- --------- PDB$SEED NORMAL SQL> SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY SQL> |
目标端操作:
在正式对PDB做plug操作之前,你可以检查下PDB与当前的CDB的兼容性:
XML文件的位置:
1 2 3 4 5 6 7 8 9 10 11 12 |
[oracle@rhel7 ~]$ pwd /home/oracle [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ ls -ltr total 36 -rw-r--r-- 1 oracle oinstall 364 Apr 19 14:49 cdb_callah.ora.env drwxr-xr-x 2 oracle oinstall 92 Apr 23 09:25 backup -rw-r--r-- 1 oracle oinstall 5524 Apr 23 14:05 pdbme.xml -rw-r--r-- 1 oracle oinstall 5006 Apr 23 14:09 pdbshe.xml -rw-r--r-- 1 oracle oinstall 5008 Apr 23 14:11 pdbshe2.xml -rw-r--r-- 1 oracle oinstall 5817 Apr 23 14:12 pdborcl.xml [oracle@rhel7 ~]$ |
查看下XML里面到底存放了些什么:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 |
[oracle@rhel7 ~]$ cat pdbme.xml <?xml version="1.0" encoding="UTF-8"?> <PDB> <xmlversion>1</xmlversion> <pdbname>PDBME</pdbname> <cid>3</cid> <byteorder>1</byteorder> <vsn>202375680</vsn> <vsns> <vsnnum>12.1.0.2.0</vsnnum> <cdbcompt>12.1.0.2.0</cdbcompt> <pdbcompt>12.1.0.2.0</pdbcompt> <vsnlibnum>0.0.0.0.22</vsnlibnum> <vsnsql>22</vsnsql> <vsnbsv>8.0.0.0.0</vsnbsv> </vsns> <dbid>4156428944</dbid> <ncdb2pdb>0</ncdb2pdb> <cdbid>3441657303</cdbid> <guid>30D763092B949704E0539EE8A8C048AB</guid> <uscnbas>2323072</uscnbas> <uscnwrp>0</uscnwrp> <rdba>4194824</rdba> <tablespace> <name>SYSTEM</name> <type>0</type> <tsn>0</tsn> <status>1</status> <issft>0</issft> <file> <path>/u01/app/oracle/oradata/CONME/30D763092B949704E0539EE8A8C048AB/datafile/o1_mf_system_ckddcb6h_.dbf</path> <afn>8</afn> <rfn>1</rfn> <createscnbas>1602896</createscnbas> <createscnwrp>0</createscnwrp> <status>1</status> <fileblocks>33280</fileblocks> <blocksize>8192</blocksize> <vsn>202375680</vsn> <fdbid>4156428944</fdbid> <fcpsw>0</fcpsw> <fcpsb>2323068</fcpsb> <frlsw>0</frlsw> <frlsb>1594143</frlsb> <frlt>909609625</frlt> </file> </tablespace> <tablespace> <name>SYSAUX</name> <type>0</type> <tsn>1</tsn> <status>1</status> <issft>0</issft> <file> <path>/u01/app/oracle/oradata/CONME/30D763092B949704E0539EE8A8C048AB/datafile/o1_mf_sysaux_ckddcb6q_.dbf</path> <afn>9</afn> <rfn>4</rfn> <createscnbas>1602899</createscnbas> <createscnwrp>0</createscnwrp> <status>1</status> <fileblocks>66560</fileblocks> <blocksize>8192</blocksize> <vsn>202375680</vsn> <fdbid>4156428944</fdbid> <fcpsw>0</fcpsw> <fcpsb>2323068</fcpsb> <frlsw>0</frlsw> <frlsb>1594143</frlsb> <frlt>909609625</frlt> </file> </tablespace> <tablespace> <name>TEMP</name> <type>1</type> <tsn>2</tsn> <status>1</status> <issft>0</issft> <bmunitsize>128</bmunitsize> <file> <path>/u01/app/oracle/oradata/CONME/30D763092B949704E0539EE8A8C048AB/datafile/o1_mf_temp_ckddcb6r_.dbf</path> <afn>3</afn> <rfn>1</rfn> <createscnbas>1602897</createscnbas> <createscnwrp>0</createscnwrp> <status>0</status> <fileblocks>2560</fileblocks> <blocksize>8192</blocksize> <vsn>202375680</vsn> <autoext>1</autoext> <maxsize>4194302</maxsize> <incsize>80</incsize> </file> </tablespace> <tablespace> <name>USERS</name> <type>0</type> <tsn>3</tsn> <status>1</status> <issft>0</issft> <file> <path>/u01/app/oracle/oradata/CONME/30D763092B949704E0539EE8A8C048AB/datafile/o1_mf_users_ckddctk3_.dbf</path> <afn>10</afn> <rfn>10</rfn> <createscnbas>1603352</createscnbas> <createscnwrp>0</createscnwrp> <status>1</status> <fileblocks>640</fileblocks> <blocksize>8192</blocksize> <vsn>202375680</vsn> <fdbid>4156428944</fdbid> <fcpsw>0</fcpsw> <fcpsb>2323068</fcpsb> <frlsw>0</frlsw> <frlsb>1594143</frlsb> <frlt>909609625</frlt> </file> </tablespace> <optional> <ncdb2pdb>0</ncdb2pdb> <csid>178</csid> <ncsid>2000</ncsid> <options> <option>APS=12.1.0.2.0</option> <option>CATALOG=12.1.0.2.0</option> <option>CATJAVA=12.1.0.2.0</option> <option>CATPROC=12.1.0.2.0</option> <option>CONTEXT=12.1.0.2.0</option> <option>DV=12.1.0.2.0</option> <option>JAVAVM=12.1.0.2.0</option> <option>OLS=12.1.0.2.0</option> <option>ORDIM=12.1.0.2.0</option> <option>OWM=12.1.0.2.0</option> <option>SDO=12.1.0.2.0</option> <option>XDB=12.1.0.2.0</option> <option>XML=12.1.0.2.0</option> <option>XOQ=12.1.0.2.0</option> </options> <olsoid>0</olsoid> <dv>0</dv> <APEX>4.2.5.00.08:1</APEX> <parameters> <parameter>processes=300</parameter> <parameter>sga_target=2466250752</parameter> <parameter>db_block_size=8192</parameter> <parameter>compatible='12.1.0.2.0'</parameter> <parameter>open_cursors=300</parameter> <parameter>pga_aggregate_target=817889280</parameter> <parameter>enable_pluggable_database=TRUE</parameter> </parameters> <tzvers> <tzver>primary version:18</tzver> <tzver>secondary version:0</tzver> </tzvers> <walletkey>0</walletkey> <hasclob>1</hasclob> <awr> <loadprofile>CPU Usage Per Sec=0.000000</loadprofile> <loadprofile>DB Block Changes Per Sec=0.000000</loadprofile> <loadprofile>Database Time Per Sec=0.000000</loadprofile> <loadprofile>Executions Per Sec=0.000000</loadprofile> <loadprofile>Hard Parse Count Per Sec=0.000000</loadprofile> <loadprofile>Logical Reads Per Sec=0.000000</loadprofile> <loadprofile>Logons Per Sec=0.000000</loadprofile> <loadprofile>Physical Reads Per Sec=0.000000</loadprofile> <loadprofile>Physical Writes Per Sec=0.000000</loadprofile> <loadprofile>Redo Generated Per Sec=0.000000</loadprofile> <loadprofile>Total Parse Count Per Sec=0.000000</loadprofile> <loadprofile>User Calls Per Sec=0.000000</loadprofile> <loadprofile>User Rollbacks Per Sec=0.000000</loadprofile> <loadprofile>User Transaction Per Sec=0.000000</loadprofile> </awr> <hardvsnchk>0</hardvsnchk> </optional> </PDB> [oracle@rhel7 ~]$ |
PDB的数据文件的存放位置:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
[oracle@rhel7 ~]$ tree $ORACLE_BASE/oradata/CONME/30D763092B949704E0539EE8A8C048AB /u01/app/oracle/oradata/CONME/30D763092B949704E0539EE8A8C048AB └── datafile ├── o1_mf_sysaux_ckddcb6q_.dbf ├── o1_mf_system_ckddcb6h_.dbf └── o1_mf_users_ckddctk3_.dbf 1 directory, 3 files [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ tree $ORACLE_BASE/oradata/CONME/datafile /u01/app/oracle/oradata/CONME/datafile ├── o1_mf_sysaux_ckdd02ss_.dbf ├── o1_mf_sysaux_ckdd5ds1_.dbf ├── o1_mf_system_ckdd1j94_.dbf ├── o1_mf_system_ckdd5ds2_.dbf ├── o1_mf_temp_ckdd58lj_.tmp ├── o1_mf_undotbs1_ckdd3ws0_.dbf ├── o1_mf_users_ckdd3vnj_.dbf ├── pdborcl │ └── CONME │ └── 30ECB50FD574F3CDE0539EE8A8C0EC6D │ └── datafile │ ├── o1_mf_sysaux_ckh5rtk2_.dbf │ ├── o1_mf_system_ckh5rtk1_.dbf │ └── o1_mf_users_ckh5rtk3_.dbf ├── pdbseed_temp012016-04-19_09-21-32-PM.dbf ├── pdbshe │ └── CONME │ └── 30E638A72C4AD737E0539EE8A8C0CA9A │ └── datafile │ ├── o1_mf_sysaux_ckgbl8bn_.dbf │ └── o1_mf_system_ckgbl8bh_.dbf └── pdbshe2 └── CONME └── 30EB9B5B884AE8FEE0539EE8A8C0A893 └── datafile ├── o1_mf_sysaux_ckh152vx_.dbf └── o1_mf_system_ckh152vv_.dbf 12 directories, 15 files [oracle@rhel7 ~]$ |
可以看到,四个PDB的数据文件都被保留了,没有因为“drop pluggable database”的操作而一并被删除。
执行兼容性检查:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
[oracle@rhel7 ~]$ ps -ef | grep pmon oracle 5670 1 0 06:19 ? 00:00:01 ora_pmon_conme oracle 15649 1 0 10:52 ? 00:00:00 ora_pmon_callah oracle 23409 11618 0 14:27 pts/0 00:00:00 grep --color=auto pmon [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ env | grep SID ORACLE_SID=callah [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 14:27:23 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ callah OPEN SQL> SQL> set serveroutput on SQL> SQL> declare 2 compat BOOLEAN := FALSE; 3 begin 4 compat := DBMS_PDB.check_plug_compatibility( 5 pdb_descr_file => '/home/oracle/pdbme.xml', pdb_name=>'pdbme'); 6 if compat then 7 dbms_output.put_line('is pluggable compatible? Yes'); 8 else dbms_output.put_line('is pluggable compatible? NO'); 9 end if; 10 end; 11 / is pluggable compatible? NO PL/SQL procedure successfully completed. SQL> |
要查看具体的问题:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SQL> set linesize 400 SQL> set pagesize 300 SQL> SQL> col name for a8 SQL> col action for a65 SQL> col message for a100 SQL> SQL> select name,action,message from pdb_plug_in_violations; NAME ACTION MESSAGE -------- ----------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- PDBME Convert the character set of the PDB to match the CDB or plug the Character set mismatch: PDB character set WE8MSWIN1252. CDB character set AL32UTF8. PDB in a CDB with compatible character set PDBME Fix the database option in the PDB or the CDB Database option APS mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Fix the database option in the PDB or the CDB Database option CATJAVA mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Fix the database option in the PDB or the CDB Database option CONTEXT mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Fix the database option in the PDB or the CDB Database option DV mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Fix the database option in the PDB or the CDB Database option JAVAVM mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Fix the database option in the PDB or the CDB Database option OLS mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Fix the database option in the PDB or the CDB Database option ORDIM mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Fix the database option in the PDB or the CDB Database option OWM mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Fix the database option in the PDB or the CDB Database option SDO mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Fix the database option in the PDB or the CDB Database option XML mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Fix the database option in the PDB or the CDB Database option XOQ mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL. PDBME Please contact Oracle Support. APEX mismatch: PDB has installed common APEX. CDB has not installed APEX. PDBME Please check the parameter in the current CDB CDB parameter sga_target mismatch: Previous 2352M Current 2368M PDBME Please check the parameter in the current CDB CDB parameter pga_aggregate_target mismatch: Previous 780M Current 480M PDBME Please check the parameter in the current CDB CDB parameter enable_pluggable_database mismatch: Previous TRUE Current FALSE 16 rows selected. SQL> |
有的警告是可以忽略的。
将PDB数据库plug到CDB中:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SQL> select name,cdb from v$database; NAME CDB -------- --- CALLAH YES SQL> SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- -------- ---------- 2 PDB$SEED READ ONLY SQL> SQL> col pdb_name for a12 SQL> SQL> select pdb_name,status from cdb_pdbs; PDB_NAME STATUS ------------ --------- PDB$SEED NORMAL SQL> SQL> create pluggable database pdbme using '/home/oracle/pdbme.xml' nocopy; Pluggable database created. SQL> SQL> select pdb_name,status from cdb_pdbs; PDB_NAME STATUS ------------ --------- PDB$SEED NORMAL PDBME NEW SQL> SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- -------- ---------- 2 PDB$SEED READ ONLY 3 PDBME MOUNTED SQL> SQL> |
按照上面的方法,将其他的PDB插入到当前CDB(callah)中。
全部插入完成后的状态是这样的:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> select pdb_name,status from cdb_pdbs; PDB_NAME STATUS ------------ --------- PDB$SEED NORMAL PDBME NEW PDBSHE NEW PDBSHE2 NEW PDBORCL NEW SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- -------- ---------- 2 PDB$SEED READ ONLY 3 PDBME MOUNTED 4 PDBSHE MOUNTED 5 PDBSHE2 MOUNTED 6 PDBORCL MOUNTED SQL> |
启动PDB:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SQL> alter pluggable database pdbme open; Warning: PDB altered with errors. SQL> SQL> set linesize 400 SQL> SQL> set linesize 400 SQL> col action for a70 SQL> col name for a10 SQL> SQL> select distinct name from pdb_plug_in_violations; NAME ---------- PDB$SEED PDBME SQL> select name,action,message from pdb_plug_in_violations where name='PDBME'; NAME ACTION MESSAGE ---------- ---------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- PDBME Convert the character set of the PDB to match the CDB or plug the PDB Character set mismatch: PDB character set WE8MSWIN1252. CDB character set AL32UTF8. in a CDB with compatible character set SQL> SQL> select pdb_name,status from cdb_pdbs; PDB_NAME STATUS ------------ --------- PDB$SEED NORMAL PDBME NORMAL PDBSHE NEW PDBSHE2 NEW PDBORCL NEW SQL> |
可以看到,上面PDB的启动之所以出问题,是因为字符集的原因造成的。
————————————————
Done。