12c：将非CDB的数据库克隆到CDB中
目标库：pdborcl
源库：非CDB的实例 – orcl
|
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 |
[oracle@rhel7 ~]$ export ORACLE_SID=orcl [oracle@rhel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 20 21:52:55 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2466250752 bytes Fixed Size 2927384 bytes Variable Size 671089896 bytes Database Buffers 1778384896 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. SQL> SQL> select name,cdb from v$database; NAME CDB --------- --- ORCL NO SQL> |
首先，将源库以“Read only”方式打开：
|
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 |
[oracle@rhel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 20 22:40:14 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,open_mode from v$database; NAME OPEN_MODE --------- -------------------- ORCL READ WRITE SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 2466250752 bytes Fixed Size 2927384 bytes Variable Size 671089896 bytes Database Buffers 1778384896 bytes Redo Buffers 13848576 bytes Database mounted. SQL> SQL> alter database open read only; Database altered. SQL> SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- ORCL READ ONLY SQL> |
创建目标库需要的目录结构：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[root@rhel7 ~]# su - oracle Last login: Wed Apr 20 20:33:31 CST 2016 on pts/1 [oracle@rhel7 ~]$ cd $ORACLE_BASE/oradata [oracle@rhel7 oradata]$ ls callah CALLAH CONME ORCL [oracle@rhel7 oradata]$ cd CONME [oracle@rhel7 CONME]$ ls 30D763092B949704E0539EE8A8C048AB controlfile datafile onlinelog [oracle@rhel7 CONME]$ cd datafile/ [oracle@rhel7 datafile]$ ls o1_mf_sysaux_ckdd02ss_.dbf o1_mf_system_ckdd1j94_.dbf o1_mf_temp_ckdd58lj_.tmp o1_mf_users_ckdd3vnj_.dbf pdbshe o1_mf_sysaux_ckdd5ds1_.dbf o1_mf_system_ckdd5ds2_.dbf o1_mf_undotbs1_ckdd3ws0_.dbf pdbseed_temp012016-04-19_09-21-32-PM.dbf pdbshe2 [oracle@rhel7 datafile]$ [oracle@rhel7 datafile]$ mkdir pdborcl [oracle@rhel7 datafile]$ cd pdborcl/ [oracle@rhel7 pdborcl]$ pwd /u01/app/oracle/oradata/CONME/datafile/pdborcl [oracle@rhel7 pdborcl]$ [oracle@rhel7 pdborcl]$ ls [oracle@rhel7 pdborcl]$ |
配置TNSNAMES：
|
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 |
[oracle@rhel7 admin]$ pwd /u01/app/oracle/product/12/db_1/network/admin [oracle@rhel7 admin]$ [oracle@rhel7 admin]$ ls listener.ora samples shrept.lst tnsnames1604204PM3402.bak tnsnames1604204PM3948.bak tnsnames.ora [oracle@rhel7 admin]$ [oracle@rhel7 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PDBME = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.232.158)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdbme) ) ) PDBSHE_1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.232.158)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdbshe) ) ) PDBSHE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.232.158)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdbshe) ) ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.232.158)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) [oracle@rhel7 admin]$ [oracle@rhel7 admin]$ tnsping orcl TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-APR-2016 22:45:12 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.232.158)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (0 msec) [oracle@rhel7 admin]$ [oracle@rhel7 admin]$ |
在CDB上，创建到源库的DB Link：
|
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 |
[oracle@rhel7 admin]$ ps -ef | grep pmon oracle 50373 1 0 13:33 ? 00:00:02 ora_pmon_conme oracle 62072 1 0 22:41 ? 00:00:00 ora_pmon_orcl oracle 62403 62189 0 22:46 pts/0 00:00:00 grep --color=auto pmon [oracle@rhel7 admin]$ [oracle@rhel7 admin]$ export ORACLE_SID=conme [oracle@rhel7 admin]$ [oracle@rhel7 admin]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 20 22:47:01 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 parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string conme SQL> SQL> show user USER is "SYS" SQL> SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> create public database link db_link_orcl connect to system identified by oracle using 'orcl'; Database link created. SQL> |
创建PDB：pdborcl
|
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 |
SQL> create pluggable database pdborcl from NON$CDB@db_link_orcl create_file_dest='/u01/app/oracle/oradata/CONME/datafile/pdborcl'; Pluggable database created. SQL> SQL> select con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 2 PDB$SEED READ ONLY 3 PDBME READ WRITE 4 PDBSHE READ WRITE 5 PDBSHE2 READ WRITE 6 PDBORCL MOUNTED SQL> SQL> !tree /u01/app/oracle/oradata/CONME/datafile/pdborcl /u01/app/oracle/oradata/CONME/datafile/pdborcl └── CONME └── 30ECB50FD574F3CDE0539EE8A8C0EC6D └── datafile ├── o1_mf_sysaux_ckh5rtk2_.dbf ├── o1_mf_system_ckh5rtk1_.dbf ├── o1_mf_temp_ckh5rtk2_.dbf └── o1_mf_users_ckh5rtk3_.dbf 3 directories, 4 files SQL> |
执行脚本：
脚本地址：$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.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 34 35 36 37 38 |
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> alter session set container=pdborcl; Session altered. SQL> SQL> show con_name CON_NAME ------------------------------ PDBORCL SQL> SQL> @?/rdbms/admin/noncdb_to_pdb.sql （... ... etc，过多的输出） 2 execute immediate '&open_sql &restricted_state'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 BEGIN 6 IF (sqlcode <> -900) THEN 7 RAISE; 8 END IF; 9 END; 10 END; 11 / PL/SQL procedure successfully completed. SQL> SQL> SQL> WHENEVER SQLERROR CONTINUE; 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 39 40 41 42 43 44 45 46 47 48 49 50 51 |
SQL> alter pluggable database pdborcl open; Warning: PDB altered with errors. SQL> 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 admin]$ [oracle@rhel7 admin]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 20 23:18:22 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 con_id,name,open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 2 PDB$SEED READ ONLY 3 PDBME READ WRITE 4 PDBSHE READ WRITE 5 PDBSHE2 READ WRITE 6 PDBORCL READ WRITE SQL> SQL> alter session set container=pdborcl; Session altered. SQL> select count(*) from scott.emp; COUNT(*) ---------- 14 SQL> select * from scott.dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> |
——————————————————
Done。
