(读书笔记/练习)12c：从seed创建PDB
首先，创建存放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 |
[root@rhel7 ~]# su - oracle Last login: Wed Apr 20 15:01:26 CST 2016 on pts/0 [oracle@rhel7 ~]$ cd $ORACLE_BASE/oradata [oracle@rhel7 oradata]$ [oracle@rhel7 oradata]$ cd CONME/ [oracle@rhel7 CONME]$ ls 30D763092B949704E0539EE8A8C048AB controlfile datafile onlinelog [oracle@rhel7 CONME]$ [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 o1_mf_sysaux_ckdd5ds1_.dbf o1_mf_system_ckdd5ds2_.dbf o1_mf_undotbs1_ckdd3ws0_.dbf pdbseed_temp012016-04-19_09-21-32-PM.dbf [oracle@rhel7 datafile]$ [oracle@rhel7 datafile]$ mkdir pdbshe [oracle@rhel7 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 [oracle@rhel7 datafile]$ [oracle@rhel7 datafile]$ cd pdbshe/ [oracle@rhel7 pdbshe]$ [oracle@rhel7 pdbshe]$ pwd /u01/app/oracle/oradata/CONME/datafile/pdbshe [oracle@rhel7 pdbshe]$ [oracle@rhel7 pdbshe]$ ls [oracle@rhel7 pdbshe]$ |
然后，SQL*Plus，连入CDB，创建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 |
[oracle@rhel7 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 20 15:01:44 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 con_id,dbid,name,open_mode,total_size from v$pdbs; CON_ID DBID NAME OPEN_MODE TOTAL_SIZE ---------- ---------- ------------------------------ ---------- ---------- 2 2468944387 PDB$SEED READ ONLY 775946240 3 4156428944 PDBME READ WRITE 833617920 SQL> SQL> create pluggable database pdbshe admin user pdbadmin identified by oracle roles=(connect) create_file_dest='/u01/app/oracle/oradata/CONME/datafile/pdbshe'; Pluggable database created. SQL> SQL> select con_id,dbid,name,open_mode,total_size from v$pdbs; CON_ID DBID NAME OPEN_MODE TOTAL_SIZE ---------- ---------- ------------------------------ ---------- ---------- 2 2468944387 PDB$SEED READ ONLY 775946240 3 4156428944 PDBME READ WRITE 833617920 4 3494399911 PDBSHE MOUNTED 0 SQL> SQL> alter pluggable database pdbshe open; Pluggable database altered. SQL> SQL> select con_id,dbid,name,open_mode,total_size from v$pdbs; CON_ID DBID NAME OPEN_MODE TOTAL_SIZE ---------- ---------- ------------------------------ ---------- ---------- 2 2468944387 PDB$SEED READ ONLY 775946240 3 4156428944 PDBME READ WRITE 833617920 4 3494399911 PDBSHE READ WRITE 796917760 SQL> |
查看下文件系统中的变化：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[oracle@rhel7 ~]$ tree /u01/app/oracle/oradata/CONME/datafile/pdbshe /u01/app/oracle/oradata/CONME/datafile/pdbshe └── CONME └── 30E638A72C4AD737E0539EE8A8C0CA9A └── datafile ├── o1_mf_sysaux_ckgbl8bn_.dbf ├── o1_mf_system_ckgbl8bh_.dbf └── o1_mf_temp_ckgbl8bo_.dbf 3 directories, 3 files [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ du -sh /u01/app/oracle/oradata/CONME/datafile/pdbshe 741M /u01/app/oracle/oradata/CONME/datafile/pdbshe [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ du -sh /u01/app/oracle/oradata/CONME/datafile/pdbshe/CONME/30E638A72C4AD737E0539EE8A8C0CA9A/datafile/* 491M /u01/app/oracle/oradata/CONME/datafile/pdbshe/CONME/30E638A72C4AD737E0539EE8A8C0CA9A/datafile/o1_mf_sysaux_ckgbl8bn_.dbf 251M /u01/app/oracle/oradata/CONME/datafile/pdbshe/CONME/30E638A72C4AD737E0539EE8A8C0CA9A/datafile/o1_mf_system_ckgbl8bh_.dbf 56K /u01/app/oracle/oradata/CONME/datafile/pdbshe/CONME/30E638A72C4AD737E0539EE8A8C0CA9A/datafile/o1_mf_temp_ckgbl8bo_.dbf [oracle@rhel7 ~]$ |
查看监听器的服务状态：
|
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 |
[oracle@rhel7 ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-APR-2016 16:16:53 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 17-APR-2016 14:12:29 Uptime 3 days 2 hr. 4 min. 24 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /u01/app/oracle/diag/tnslsnr/rhel7/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel7)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rhel7)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/conme/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "conme" has 1 instance(s). Instance "conme", status READY, has 1 handler(s) for this service... Service "conmeXDB" has 1 instance(s). Instance "conme", status READY, has 1 handler(s) for this service... Service "pdbme" has 1 instance(s). Instance "conme", status READY, has 1 handler(s) for this service... Service "pdbshe" has 1 instance(s). Instance "conme", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ lsnrctl services LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-APR-2016 16:16:59 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) Services Summary... Service "conme" has 1 instance(s). Instance "conme", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "conmeXDB" has 1 instance(s). Instance "conme", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER <machine: rhel7, pid: 50413> (ADDRESS=(PROTOCOL=tcp)(HOST=rhel7)(PORT=11915)) Service "pdbme" has 1 instance(s). Instance "conme", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdbshe" has 1 instance(s). Instance "conme", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully [oracle@rhel7 ~]$ |
创建PDB的时候，自动注册的服务列表：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> set linesize 400 SQL> set pagesize 400 SQL> SQL> col network_name for a30 SQL> col name for a18 SQL> SQL> select service_id,name,network_name,con_id,pdb from v$services order by con_id,service_id; SERVICE_ID NAME NETWORK_NAME CON_ID PDB ---------- ------------------ ------------------------------ ---------- ------------------------------ 1 SYS$BACKGROUND 1 CDB$ROOT 2 SYS$USERS 1 CDB$ROOT 5 conmeXDB conmeXDB 1 CDB$ROOT 6 conme conme 1 CDB$ROOT 6 pdbme pdbme 3 PDBME 7 pdbshe pdbshe 4 PDBSHE 6 rows selected. SQL> |
配置连接到PDB的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 |
[oracle@rhel7 ~]$ cat $ORACLE_HOME/network/admin/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 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.232.158)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdbshe) ) ) [oracle@rhel7 ~]$ [oracle@rhel7 ~]$ tnsping pdbshe TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-APR-2016 16:21:18 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 = pdbshe))) OK (0 msec) [oracle@rhel7 ~]$ [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 |
[oracle@rhel7 ~]$ sqlplus sys@pdbshe as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 20 16:22:08 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Enter password: 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 ------------------------------ PDBSHE SQL> SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ conme OPEN 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 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
SQL> set linesize 400 SQL> set pagesize 400 SQL> SQL> col file_name for a120 SQL> col tablespace_name for a16 SQL> SQL> select file_name,tablespace_name,file_id,con_id from cdb_data_files order by con_id,file_id; FILE_NAME TABLESPACE_NAME FILE_ID CON_ID ------------------------------------------------------------------------------------------------------------------------ ---------------- ---------- ---------- /u01/app/oracle/oradata/CONME/datafile/o1_mf_system_ckdd1j94_.dbf SYSTEM 1 1 /u01/app/oracle/oradata/CONME/datafile/o1_mf_sysaux_ckdd02ss_.dbf SYSAUX 3 1 /u01/app/oracle/oradata/CONME/datafile/o1_mf_undotbs1_ckdd3ws0_.dbf UNDOTBS1 4 1 /u01/app/oracle/oradata/CONME/datafile/o1_mf_users_ckdd3vnj_.dbf USERS 6 1 /u01/app/oracle/oradata/CONME/30D763092B949704E0539EE8A8C048AB/datafile/o1_mf_system_ckddcb6h_.dbf SYSTEM 8 3 /u01/app/oracle/oradata/CONME/30D763092B949704E0539EE8A8C048AB/datafile/o1_mf_sysaux_ckddcb6q_.dbf SYSAUX 9 3 /u01/app/oracle/oradata/CONME/30D763092B949704E0539EE8A8C048AB/datafile/o1_mf_users_ckddctk3_.dbf USERS 10 3 /u01/app/oracle/oradata/CONME/datafile/pdbshe/CONME/30E638A72C4AD737E0539EE8A8C0CA9A/datafile/o1_mf_system_ckgbl8bh_.dbf SYSTEM 11 4 /u01/app/oracle/oradata/CONME/datafile/pdbshe/CONME/30E638A72C4AD737E0539EE8A8C0CA9A/datafile/o1_mf_sysaux_ckgbl8bn_.dbf SYSAUX 12 4 9 rows selected. SQL> SQL> col file_name for a70 SQL> select file_name,tablespace_name,file_id from dba_data_files; FILE_NAME TABLESPACE_NAME FILE_ID ---------------------------------------------------------------------- ---------------- ---------- /u01/app/oracle/oradata/CONME/datafile/o1_mf_users_ckdd3vnj_.dbf USERS 6 /u01/app/oracle/oradata/CONME/datafile/o1_mf_undotbs1_ckdd3ws0_.dbf UNDOTBS1 4 /u01/app/oracle/oradata/CONME/datafile/o1_mf_system_ckdd1j94_.dbf SYSTEM 1 /u01/app/oracle/oradata/CONME/datafile/o1_mf_sysaux_ckdd02ss_.dbf SYSAUX 3 SQL> SQL> col file_name for a120 SQL> select file_name,tablespace_name,file_id from cdb_temp_files; FILE_NAME TABLESPACE_NAME FILE_ID ------------------------------------------------------------------------------------------------------------------------ ---------------- ---------- /u01/app/oracle/oradata/CONME/datafile/pdbshe/CONME/30E638A72C4AD737E0539EE8A8C0CA9A/datafile/o1_mf_temp_ckgbl8bo_.dbf TEMP 4 /u01/app/oracle/oradata/CONME/30D763092B949704E0539EE8A8C048AB/datafile/o1_mf_temp_ckddcb6r_.dbf TEMP 3 /u01/app/oracle/oradata/CONME/datafile/o1_mf_temp_ckdd58lj_.tmp TEMP 1 SQL> SQL> col file_name for a70 SQL> SQL> select file_name,tablespace_name,file_id from dba_temp_files; FILE_NAME TABLESPACE_NAME FILE_ID ---------------------------------------------------------------------- ---------------- ---------- /u01/app/oracle/oradata/CONME/datafile/o1_mf_temp_ckdd58lj_.tmp TEMP 1 SQL> |
————————————————————
Done。
