Bo's Oracle Station

查看: 2973|回复: 0

课程第65次(2017-12-05星期二)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-12-5 19:31:54 | 显示全部楼层 |阅读模式
1Z0-063第2章
1Z0-05219章(上完19章),1Z0-05321章(上完21章)和1Z0-063多租户部分共9章(上2
总共上完全部49章中的42
在多租户的根容器上查v$视图:

  1. select   d.CON_ID,d.NAME,d.OPEN_MODE,d.CDB
  2. from v$database d;
  3. 显示成容器0,不是容器1。容器0代表公共的。
复制代码
  1. select i.INSTANCE_MODE, i.INSTANCE_NAME,i.CON_ID
  2.   from v$instance i;
  3. 显示成容器0,不是容器1。容器0代表公共的。
复制代码
  1.     select s.CON_ID,s.NAME,s.PDB
  2.    from v$services s;
复制代码
  1.   select  p.CON_ID,p.NAME,p.OPEN_MODE
  2.   from v$pdbs p;
  3.   
  4.   
  5.      select  sys_context('userenv','con_id') from dual;
  6.          
  7.       select  sys_context('userenv','con_name') from dual;
复制代码
  1.   select  * from cdb_pdbs;
复制代码
  1. select  * from v$logfile;
  2. 显示成容器0,不是容器1。容器0代表公共的。
复制代码
  1.           select * from v$controlfile;
  2. 显示成容器0,不是容器1。容器0代表公共的。
复制代码
  1.           select  c.CON_ID,c.FILE_NAME,c.STATUS from
  2.   cdb_data_files c;

  3. 所有CDB视图,都可以在插件数据库中查。如果在根容器上查,都不会显示成容器0,而是显示成容器1。
复制代码
  1.    select t.CON_ID,t.NAME,d.NAME,d.STATUS
  2.   from v$tablespace t, v$datafile d
  3.   where t.TS#=d.TS#  and t.CON_ID=d.CON_ID
  4.   order by 1,2;

  5. 以上语句在插件库中查,能显示包括UNDO表空间在类的插件数据库所有文件,UNDO表空间的文件显示成容器0(公共)。
复制代码
  1.       select t.CON_ID,t.NAME,d.NAME,d.STATUS
  2.   from v$tablespace t, v$tempfile d
  3.   where t.TS#=d.TS#  and t.CON_ID=d.CON_ID
  4.   order by 1,2;

  5. 以上语句能看见种子的临时文件。
复制代码
  1.    select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
  2.   c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
  3.    from cdb_users c
  4.    where c.USERNAME='SYSTEM';

  5. 除了cdb_pdbs以外,其他cdb_视图都不显示种子。
复制代码
  1.       select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
  2.   c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
  3.    from cdb_users c
  4.    where c.COMMON='NO';

  5. 根容器上是没有本地用户和本地角色的。根容器唯一可能有“本地”含义的东西就是:对象权限的本地授权。
复制代码
Screenshot.png

根容器上是没有系统权限的本地授权的:
Screenshot-1.png

  1. select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
  2.   c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
  3.    from cdb_users c
  4.    where c.COMMON='NO';

  5. 种子不算,在没有插件数据库的情况下是没有本地用户,没有本地角色的。
复制代码
  1. select  distinct b.STATUS, b.CON_ID
  2.   from v$bh b
  3.   order by 2;

  4. 内存是各个容器共享的。
复制代码
ASM上和文件系统上的容器数据库文件夹位置的对比:
  1. oracle@station26 oradata]$ ls
  2. cdb2  orcl
  3. [oracle@station26 oradata]$ cd cdb2/
  4. [oracle@station26 cdb2]$ pwd
  5. /u01/app/oracle/oradata/cdb2
  6. [oracle@station26 cdb2]$ ls
  7. control01.ctl  pdbseed  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
  8. [oracle@station26 cdb2]$ cd ..
  9. [oracle@station26 oradata]$ ls
  10. cdb2  orcl
  11. [oracle@station26 oradata]$ cd ..
  12. [oracle@station26 oracle]$ ls
  13. admin  audit  cfgtoollogs  checkpoints  crsdata  diag  fast_recovery_area  log  oradata  product  station26
  14. [oracle@station26 oracle]$ cd fast_recovery_area
  15. [oracle@station26 fast_recovery_area]$ ls
  16. cdb2  CDB2  orcl  ORCL
  17. [oracle@station26 fast_recovery_area]$ cd cdb2
  18. [oracle@station26 cdb2]$ ls
  19. control02.ctl
  20. [oracle@station26 cdb2]$ cd ..
  21. [oracle@station26 fast_recovery_area]$ ls
  22. cdb2  CDB2  orcl  ORCL
  23. [oracle@station26 fast_recovery_area]$ cd ..
  24. [oracle@station26 oracle]$ ls
  25. admin  audit  cfgtoollogs  checkpoints  crsdata  diag  fast_recovery_area  log  oradata  product  station26
  26. [oracle@station26 oracle]$ cd oradata
  27. [oracle@station26 oradata]$ ls
  28. cdb2  orcl
  29. [oracle@station26 oradata]$ cd cdb2
  30. [oracle@station26 cdb2]$ ls
  31. control01.ctl  pdbseed  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
  32. [oracle@station26 cdb2]$ cd pdbseed
  33. [oracle@station26 pdbseed]$ ls
  34. pdbseed_temp012017-12-22_06-04-39-PM.dbf  sysaux01.dbf  system01.dbf
  35. [oracle@station26 pdbseed]$ cd
  36. [oracle@station26 ~]$ . oraenv
  37. ORACLE_SID = [cdb1] ?
  38. The Oracle base remains unchanged with value /u01/app/oracle
  39. [oracle@station26 ~]$ . oraenv
  40. ORACLE_SID = [cdb1] ? +ASM
  41. The Oracle base remains unchanged with value /u01/app/oracle
  42. [oracle@station26 ~]$ asmcmd
  43. ASMCMD> ls
  44. DATA/
  45. FRA/
  46. ASMCMD> cd data
  47. ASMCMD> ls
  48. ASM/
  49. CDB1/
  50. ORCL2/
  51. orapwasm
  52. ASMCMD> cd CDB1/
  53. ASMCMD> ls
  54. 47847425558B6E0FE0531A00A8C0F7C0/
  55. 4784B01598A17301E0531A00A8C082AB/
  56. CONTROLFILE/
  57. DATAFILE/
  58. FD9AC20F64D244D7E043B6A9E80A2F2F/
  59. FD9BD2B44413096FE043B6A9E80ABC28/
  60. ONLINELOG/
  61. PARAMETERFILE/
  62. TEMPFILE/
  63. ASMCMD> cd FD9AC20F64D244D7E043B6A9E80A2F2F/
  64. ASMCMD> ls
  65. DATAFILE/
  66. ASMCMD> cd DATAFILE/
  67. ASMCMD> ls
  68. SYSAUX.266.934887937
  69. SYSTEM.267.934887939
  70. pdbseed_temp012017-02-02_11-06-38-AM.dbf
  71. ASMCMD> ls -l
  72. Type      Redund  Striped  Time             Sys  Name
  73. DATAFILE  MIRROR  COARSE   DEC 22 15:00:00  Y    SYSAUX.266.934887937
  74. DATAFILE  MIRROR  COARSE   DEC 22 15:00:00  Y    SYSTEM.267.934887939
  75. TEMPFILE  MIRROR  COARSE   DEC 22 15:00:00  N    pdbseed_temp012017-02-02_11-06-38-AM.dbf => +DATA/CDB1/47847425558B6E0FE0531A00A8C0F7C0/TEMPFILE/TEMP.268.934887999
  76. ASMCMD> exit
  77. [oracle@station26 ~]$ cd /u01/app/oracle/
  78. [oracle@station26 oracle]$ ls
  79. admin  audit  cfgtoollogs  checkpoints  crsdata  diag  fast_recovery_area  log  oradata  product  station26
  80. [oracle@station26 oracle]$ cd oradata/
  81. [oracle@station26 oradata]$ ls
  82. cdb2  orcl
  83. [oracle@station26 oradata]$ pwd
  84. /u01/app/oracle/oradata
  85. [oracle@station26 oradata]$ ls
  86. cdb2  orcl
  87. [oracle@station26 oradata]$ cd cdb2/
  88. [oracle@station26 cdb2]$ ls
  89. control01.ctl  pdbseed  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
  90. [oracle@station26 cdb2]$ mkdir pdb2_1
  91. [oracle@station26 cdb2]$ cd pdb2_1/
  92. [oracle@station26 pdb2_1]$ ls
  93. [oracle@station26 pdb2_1]$ pwd
  94. /u01/app/oracle/oradata/cdb2/pdb2_1
  95. [oracle@station26 pdb2_1]$
复制代码
SQLPLUS创建插件数据库的语句:
  1. create pluggable database pdb2_1
  2. admin user pdb2_1_admin
  3. identified by oracle_4U roles=(connect)
  4. file_name_convert=('/u01/app/oracle/oradata/cdb2/pdbseed',
  5. '/u01/app/oracle/oradata/cdb2/pdb2_1');


  6. select  con_id, open_mode , name
  7. from v$pdbs;

  8. alter pluggable database pdb2_1 open ;

  9. select  con_id, open_mode , name
  10. from v$pdbs;
复制代码
观察创建完成后的pdb2_1和种子:
  1. [oracle@station26 admin]$ vim tnsnames.ora
  2. [oracle@station26 admin]$ sqlplus /nolog

  3. SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 25 20:13:44 2017

  4. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  5. SQL> conn sys/oracle_4U@pdb2_1 as sysdba
  6. ERROR:
  7. ORA-12154: TNS:could not resolve the connect identifier specified


  8. SQL> exit
  9. [oracle@station26 admin]$ lsnrctl services

  10. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 25-DEC-2017 20:13:58

  11. Copyright (c) 1991, 2014, Oracle.  All rights reserved.

  12. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=station26.example.com)(PORT=1521)))
  13. Services Summary...
  14. Service "+ASM" has 1 instance(s).
  15.   Instance "+ASM", status READY, has 1 handler(s) for this service...
  16.     Handler(s):
  17.       "DEDICATED" established:3678 refused:0 state:ready
  18.          LOCAL SERVER
  19. Service "cdb1" has 1 instance(s).
  20.   Instance "cdb1", status READY, has 1 handler(s) for this service...
  21.     Handler(s):
  22.       "DEDICATED" established:5810 refused:0 state:ready
  23.          LOCAL SERVER
  24. Service "cdb1XDB" has 1 instance(s).
  25.   Instance "cdb1", status READY, has 1 handler(s) for this service...
  26.     Handler(s):
  27.       "D000" established:0 refused:0 current:0 max:1022 state:ready
  28.          DISPATCHER <machine: station26.example.com, pid: 30310>
  29.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=65185))
  30. Service "cdb2" has 1 instance(s).
  31.   Instance "cdb2", status READY, has 1 handler(s) for this service...
  32.     Handler(s):
  33.       "DEDICATED" established:0 refused:0 state:ready
  34.          LOCAL SERVER
  35. Service "cdb2XDB" has 1 instance(s).
  36.   Instance "cdb2", status READY, has 1 handler(s) for this service...
  37.     Handler(s):
  38.       "D000" established:0 refused:0 current:0 max:1022 state:ready
  39.          DISPATCHER <machine: station26.example.com, pid: 10749>
  40.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=31101))
  41. Service "orcl" has 1 instance(s).
  42.   Instance "orcl", status READY, has 4 handler(s) for this service...
  43.     Handler(s):
  44.       "DEDICATED" established:20 refused:0 state:ready
  45.          LOCAL SERVER
  46.       "D002" established:2269 refused:0 current:2 max:1022 state:ready
  47.          DISPATCHER <machine: station26.example.com, pid: 24484>
  48.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=23886))
  49.       "D001" established:3430 refused:0 current:6 max:1022 state:ready
  50.          DISPATCHER <machine: station26.example.com, pid: 24482>
  51.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=12364))
  52.       "D000" established:3150 refused:0 current:2 max:1022 state:ready
  53.          DISPATCHER <machine: station26.example.com, pid: 24480>
  54.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=23964))
  55. Service "orcl2" has 1 instance(s).
  56.   Instance "orcl2", status READY, has 1 handler(s) for this service...
  57.     Handler(s):
  58.       "DEDICATED" established:33994 refused:0 state:ready
  59.          LOCAL SERVER
  60. Service "orcl2XDB" has 1 instance(s).
  61.   Instance "orcl2", status READY, has 1 handler(s) for this service...
  62.     Handler(s):
  63.       "D000" established:0 refused:0 current:0 max:1022 state:ready
  64.          DISPATCHER <machine: station26.example.com, pid: 29165>
  65.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=41815))
  66. Service "pdb1_1" has 1 instance(s).
  67.   Instance "cdb1", status READY, has 1 handler(s) for this service...
  68.     Handler(s):
  69.       "DEDICATED" established:5810 refused:0 state:ready
  70.          LOCAL SERVER
  71. Service "pdb2_1" has 1 instance(s).
  72.   Instance "cdb2", status READY, has 1 handler(s) for this service...
  73.     Handler(s):
  74.       "DEDICATED" established:0 refused:0 state:ready
  75.          LOCAL SERVER
  76. Service "serv1" has 1 instance(s).
  77.   Instance "orcl", status READY, has 4 handler(s) for this service...
  78.     Handler(s):
  79.       "DEDICATED" established:20 refused:0 state:ready
  80.          LOCAL SERVER
  81.       "D002" established:2269 refused:0 current:2 max:1022 state:ready
  82.          DISPATCHER <machine: station26.example.com, pid: 24484>
  83.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=23886))
  84.       "D001" established:3430 refused:0 current:6 max:1022 state:ready
  85.          DISPATCHER <machine: station26.example.com, pid: 24482>
  86.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=12364))
  87.       "D000" established:3150 refused:0 current:2 max:1022 state:ready
  88.          DISPATCHER <machine: station26.example.com, pid: 24480>
  89.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=23964))
  90. The command completed successfully
  91. [oracle@station26 admin]$ ls
  92. samples  shrept.lst  tnsnames.ora
  93. [oracle@station26 admin]$ echo $TNS_ADMIN

  94. [oracle@station26 admin]$ ls
  95. samples  shrept.lst  tnsnames.ora
  96. [oracle@station26 admin]$ vim tnsnames.ora
  97. [oracle@station26 admin]$ tnsping  pdb2_1

  98. TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 25-DEC-2017 20:14:38

  99. Copyright (c) 1997, 2014, Oracle.  All rights reserved.

  100. Used parameter files:
  101. /u01/app/oracle/product/12.1.0/grid/network/admin/sqlnet.ora

  102. TNS-03505: Failed to resolve name
  103. [oracle@station26 admin]$ cp tnsnames.ora /u01/app/oracle/product/12.1.0/grid/network/admin/         
  104. [oracle@station26 admin]$ tnsping  pdb2_1

  105. TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 25-DEC-2017 20:14:57

  106. Copyright (c) 1997, 2014, Oracle.  All rights reserved.

  107. Used parameter files:
  108. /u01/app/oracle/product/12.1.0/grid/network/admin/sqlnet.ora


  109. Used TNSNAMES adapter to resolve the alias
  110. Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = station26.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb2_1)))
  111. OK (10 msec)
  112. [oracle@station26 admin]$ sqlplus /nolog

  113. SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 25 20:15:00 2017

  114. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  115. SQL> conn sys/oracle_4U@pdb2_1 as sysdba
  116. Connected.
  117. SQL> select  t.con_id, t.name , d.name , d.status , t.status
  118.   2  from v$tablespace t , v$datafile d
  119.   3  where t.ts#=d.ts# and t.con_id=d.con_id
  120.   4  order by 1,2 ;
  121. select        t.con_id, t.name , d.name , d.status , t.status
  122.                                                *
  123. ERROR at line 1:
  124. ORA-00904: "T"."STATUS": invalid identifier


  125. SQL> ed
  126. Wrote file afiedt.buf

  127.   1  select  t.con_id, t.name , d.name , d.status
  128.   2  from v$tablespace t , v$datafile d
  129.   3  where t.ts#=d.ts# and t.con_id=d.con_id
  130.   4* order by 1,2
  131. SQL> /

  132.     CON_ID NAME
  133. ---------- ------------------------------
  134. NAME
  135. --------------------------------------------------------------------------------
  136. STATUS
  137. -------
  138.          0 UNDOTBS1
  139. /u01/app/oracle/oradata/cdb2/undotbs01.dbf
  140. ONLINE

  141.          3 SYSAUX
  142. /u01/app/oracle/oradata/cdb2/pdb2_1/sysaux01.dbf
  143. ONLINE

  144.     CON_ID NAME
  145. ---------- ------------------------------
  146. NAME
  147. --------------------------------------------------------------------------------
  148. STATUS
  149. -------

  150.          3 SYSTEM
  151. /u01/app/oracle/oradata/cdb2/pdb2_1/system01.dbf
  152. SYSTEM


  153. SQL> select  t.con_id, t.name , d.name , d.status
  154.   2  from v$tablespace t , v$tempfile d
  155.   3  where t.ts#=d.ts# and t.con_id=d.con_id
  156.   4  order by 1,2;

  157.     CON_ID NAME
  158. ---------- ------------------------------
  159. NAME
  160. --------------------------------------------------------------------------------
  161. STATUS
  162. -------
  163.          3 TEMP
  164. /u01/app/oracle/oradata/cdb2/pdb2_1/pdbseed_temp012017-12-22_06-04-39-PM.dbf
  165. ONLINE


  166. SQL> !date
  167. Mon Dec 25 20:17:27 CST 2017

  168. SQL> ^Exit
  169. SP2-0042: unknown command "xit" - rest of line ignored.
  170. SQL> exit
  171. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  172. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  173. [oracle@station26 admin]$ cd /u01/app/oracle/oradata/
  174. [oracle@station26 oradata]$ ls
  175. cdb2  orcl
  176. [oracle@station26 oradata]$ cd cdb2
  177. [oracle@station26 cdb2]$ ls
  178. control01.ctl  pdbseed     redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
  179. pdb2_1         redo01.log  redo03.log  system01.dbf  undotbs01.dbf
  180. [oracle@station26 cdb2]$ cd pdbseed
  181. [oracle@station26 pdbseed]$ ls
  182. pdbseed_temp012017-12-22_06-04-39-PM.dbf  sysaux01.dbf  system01.dbf
  183. [oracle@station26 pdbseed]$ cd ..
  184. [oracle@station26 cdb2]$ ls
  185. control01.ctl  pdbseed     redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
  186. pdb2_1         redo01.log  redo03.log  system01.dbf  undotbs01.dbf
  187. [oracle@station26 cdb2]$ cd pdb2_1
  188. [oracle@station26 pdb2_1]$ ls
  189. pdbseed_temp012017-12-22_06-04-39-PM.dbf  sysaux01.dbf  system01.dbf
  190. [oracle@station26 pdb2_1]$
复制代码
创建的第一个插件数据库容器ID为3:
  1. [oracle@station26 pdb2_1]$ sqlplus /nolog

  2. SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 25 20:29:12 2017

  3. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  4. SQL> conn sys/oracle_4U@pdb2_1 as sysdba
  5. Connected.
  6. SQL> show con_id

  7. CON_ID
  8. ------------------------------
  9. 3
  10. SQL> show con_name

  11. CON_NAME
  12. ------------------------------
  13. PDB2_1
复制代码

ADMIN用户都是本地的:
  1.          select c.COMMON,c.CON_ID,c.DEFAULT_TABLESPACE,
  2.   c.TEMPORARY_TABLESPACE,c.INITIAL_RSRC_CONSUMER_GROUP,c.USERNAME
  3.    from cdb_users c
  4.    where c.COMMON='NO';
  5.    
复制代码

Screenshot-2.png

Screenshot-3.png

SQLPLUS命令删除插件数据库:

  1. select  con_id, open_mode , name from v$pdbs;

  2. drop pluggable database pdb2_1 including datafiles;

  3. alter pluggable database pdb2_1  close immediate ;

  4. drop pluggable database pdb2_1 including datafiles;
复制代码

Screenshot-3.png

P60以下这句是完全不需要的:
  1. alter system set db_create_file_dest =
  2. '/u01/app/oracle/oradata/cdb2/pdb2_1' scope=both;
复制代码
原因是因为我们在SQL Developer中制定和命令行等效的语句:
file_name_convert=('/u01/app/oracle/oradata/cdb2/pdbseed',
'/u01/app/oracle/oradata/cdb2/pdb2_1')

Screenshot-3.png

  1. select  con_id, name , open_mode
  2. from v$pdbs;

  3. select  t.con_id, t.name , d.name , d.status
  4. from v$tablespace t, v$datafile d
  5. where t.ts#=d.ts# and t.con_id=d.con_id
  6. order by 1,2;

  7. select  t.con_id, t.name , d.name , d.status
  8. from v$tablespace t, v$tempfile d
  9. where t.ts#=d.ts# and t.con_id=d.con_id
  10. order by 1,2;
复制代码
准备做pdb2_2,书P64页莫名其妙选用OMF方式,非常怪:
以下演示与P64页不同。

  1. [oracle@station26 cdb2]$ mkdir pdb2_2
复制代码
在12.1.0.2后,克隆时,源头库已经可以不停机了(P65页有停机)

  1. select con_id, name , open_mode
  2. from v$pdbs;

  3. create pluggable database pdb2_2 from pdb2_1
  4. file_name_convert=('/u01/app/oracle/oradata/cdb2/pdb2_1',
  5. '/u01/app/oracle/oradata/cdb2/pdb2_2');

  6. alter pluggable database pdb2_2 open ;

  7. select con_id, name , open_mode
  8. from v$pdbs;
复制代码

书上做PDB2_2是这样OMF的:
  1. [oracle@station26 cdb2]$ ls
  2. control01.ctl  pdbseed     redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
  3. pdb2_1         redo01.log  redo03.log  system01.dbf  undotbs01.dbf
  4. [oracle@station26 cdb2]$ mkdir pdb2_2
  5. [oracle@station26 cdb2]$ ls
  6. control01.ctl  pdb2_2   redo01.log  redo03.log    system01.dbf  undotbs01.dbf
  7. pdb2_1         pdbseed  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
  8. [oracle@station26 cdb2]$ cd pdb2_2
  9. [oracle@station26 pdb2_2]$ ls
  10. sysaux01.dbf  system01.dbf
  11. [oracle@station26 pdb2_2]$ ls
  12. sysaux01.dbf  system01.dbf
  13. [oracle@station26 pdb2_2]$ ls
  14. sysaux01.dbf  system01.dbf
  15. [oracle@station26 pdb2_2]$
  16. [oracle@station26 pdb2_2]$ s
  17. -bash: s: command not found
  18. [oracle@station26 pdb2_2]$ ls
  19. pdbseed_temp012017-12-22_06-04-39-PM.dbf  sysaux01.dbf  system01.dbf
  20. [oracle@station26 pdb2_2]$ . oraenv
  21. ORACLE_SID = [cdb2] ?
  22. The Oracle base remains unchanged with value /u01/app/oracle
  23. [oracle@station26 pdb2_2]$ ls
  24. [oracle@station26 pdb2_2]$ cd
  25. [oracle@station26 ~]$ cd -
  26. /u01/app/oracle/oradata/cdb2/pdb2_2
  27. [oracle@station26 pdb2_2]$ ls
  28. [oracle@station26 pdb2_2]$ sqlplus /nolog

  29. SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 25 21:20:10 2017

  30. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  31. SQL> conn / as sysdba
  32. Connected.
  33. SQL> show parameter db_create

  34. NAME                                     TYPE         VALUE
  35. ------------------------------------ ----------- ------------------------------
  36. db_create_file_dest                     string
  37. db_create_online_log_dest_1             string
  38. db_create_online_log_dest_2             string
  39. db_create_online_log_dest_3             string
  40. db_create_online_log_dest_4             string
  41. db_create_online_log_dest_5             string
  42. SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/cdb2/pdb2_2';

  43. System altered.

  44. SQL> create pluggable database  pdb2_2 from pdb2_1;  

  45. Pluggable database created.

  46. SQL> exit
  47. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  48. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  49. [oracle@station26 pdb2_2]$ ls
  50. CDB2
  51. [oracle@station26 pdb2_2]$ cd CDB2
  52. [oracle@station26 CDB2]$ ls
  53. 612B093C409C4C0FE0531A00A8C013C6
  54. [oracle@station26 CDB2]$ sqlplus /nolog

  55. SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 25 21:21:39 2017

  56. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  57. SQL> conn / as sysdba
  58. Connected.
  59. SQL> dersc cdb_pdbs
  60. SP2-0734: unknown command beginning "dersc cdb_..." - rest of line ignored.
  61. SQL> desc cdb_pdbs
  62. Name                                           Null?    Type
  63. ----------------------------------------- -------- ----------------------------
  64. PDB_ID                                    NOT NULL NUMBER
  65. PDB_NAME                                   NOT NULL VARCHAR2(128)
  66. DBID                                           NOT NULL NUMBER
  67. CON_UID                                   NOT NULL NUMBER
  68. GUID                                                    RAW(16)
  69. STATUS                                             VARCHAR2(9)
  70. CREATION_SCN                                            NUMBER
  71. VSN                                                    NUMBER
  72. LOGGING                                            VARCHAR2(9)
  73. FORCE_LOGGING                                            VARCHAR2(3)
  74. FORCE_NOLOGGING                                    VARCHAR2(3)
  75. CON_ID                                    NOT NULL NUMBER

  76. SQL> select  PDB_NAME, GUID  from cdb_pdbs;

  77. PDB_NAME
  78. --------------------------------------------------------------------------------
  79. GUID
  80. --------------------------------
  81. PDB$SEED
  82. 60EBF1DA54AB2474E0531A00A8C014ED

  83. PDB2_1
  84. 612ACFF6C21F435FE0531A00A8C023A2

  85. PDB2_2
  86. 612B093C409C4C0FE0531A00A8C013C6


  87. SQL> exit
  88. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  89. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  90. [oracle@station26 CDB2]$ ls
  91. 612B093C409C4C0FE0531A00A8C013C6
  92. [oracle@station26 CDB2]$ cd 612B093C409C4C0FE0531A00A8C013C6
  93. [oracle@station26 612B093C409C4C0FE0531A00A8C013C6]$ ls
  94. datafile
  95. [oracle@station26 612B093C409C4C0FE0531A00A8C013C6]$ cd datafile
  96. [oracle@station26 datafile]$ ls
  97. o1_mf_sysaux_f41yt1kj_.dbf  o1_mf_system_f41yt1kf_.dbf  o1_mf_temp_f41yt1kl_.dbf
  98. [oracle@station26 datafile]$ pwd
  99. /u01/app/oracle/oradata/cdb2/pdb2_2/CDB2/612B093C409C4C0FE0531A00A8C013C6/datafile
  100. [oracle@station26 datafile]$
复制代码
  1. [oracle@station26 admin]$ cp tnsnames.ora   /u01/app/oracle/product/12.1.0/
  2. dbhome_1/ dbhome_2/ grid/     
  3. [oracle@station26 admin]$ cp tnsnames.ora   /u01/app/oracle/product/12.1.0/grid/network/admin/
  4. [oracle@station26 admin]$
  5. [oracle@station26 admin]$ sqlplus /nolog

  6. SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 25 21:25:42 2017

  7. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  8. SQL> conn sys/oracle_4U@pdb2_2 as sysdba
  9. Connected.
  10. SQL> show con_id

  11. CON_ID
  12. ------------------------------
  13. 4
复制代码
  1. SQL>
  2. SQL>   select  p.CON_ID,
  3.            p.NAME,
  4.                    p.OPEN_MODE,
  5.                    p.CON_UID
  6.   from v$pdbs p;
  7.   2    3    4    5  
  8.     CON_ID NAME                           OPEN_MODE        CON_UID
  9. ---------- ------------------------------ ---------- ----------
  10.          4 PDB2_2                          MOUNTED    2662063752

  11. SQL> alter pluggable database pdb2_2 open ;

  12. Pluggable database altered.

  13. SQL> select  con_id, name , open_mode from v$pdbs;

  14.     CON_ID NAME                           OPEN_MODE
  15. ---------- ------------------------------ ----------
  16.          4 PDB2_2                          READ WRITE
复制代码
P76页,插进一个非容器的12c,利用用现成的数据文件:

注意以下脚本最后几行说明,创建插件数据库时都要新建临时文件。

  1. [oracle@station26 admin]$ . oraenv
  2. ORACLE_SID = [cdb2] ? orcl2
  3. The Oracle base remains unchanged with value /u01/app/oracle
  4. [oracle@station26 admin]$ sqlplus /nolog

  5. SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 25 21:34:13 2017

  6. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  7. SQL> conn / as sysdba
  8. Connected.
  9. SQL> select  open_mode from v$database;

  10. OPEN_MODE
  11. --------------------
  12. READ WRITE

  13. SQL> shutdown immediate
  14. Database closed.
  15. Database dismounted.
  16. ORACLE instance shut down.
  17. SQL> startup mount
  18. ORACLE instance started.

  19. Total System Global Area 2147483648 bytes
  20. Fixed Size                    3712904 bytes
  21. Variable Size                 1291847800 bytes
  22. Database Buffers          838860800 bytes
  23. Redo Buffers                   13062144 bytes
  24. Database mounted.
  25. SQL> alter database open read only ;

  26. Database altered.

  27. SQL> exec dbms_pdb.describe('/home/oracle/orcl2.xml') ;

  28. PL/SQL procedure successfully completed.

  29. SQL> shutdown immediate
  30. Database closed.
  31. Database dismounted.
  32. ORACLE instance shut down.
  33. SQL> exit
  34. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  35. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  36. and Real Application Testing options
  37. [oracle@station26 admin]$ . oraenv
  38. ORACLE_SID = [orcl2] ? +ASM
  39. The Oracle base remains unchanged with value /u01/app/oracle
  40. [oracle@station26 admin]$ asmcmd
  41. ASMCMD> ls
  42. DATA/
  43. FRA/
  44. ASMCMD> cd data
  45. ASMCMD> ls
  46. ASM/
  47. CDB1/
  48. ORCL2/
  49. orapwasm
  50. ASMCMD> cd ORCL2/
  51. ASMCMD> sl
  52. ASMCMD-9487: 'sl' cannot be run with 'sysasm' privilege
  53. ASMCMD> ls
  54. CONTROLFILE/
  55. DATAFILE/
  56. ONLINELOG/
  57. PARAMETERFILE/
  58. TEMPFILE/
  59. ASMCMD> cd TEMPFILE/
  60. ASMCMD> ls
  61. TEMP.283.934891281
  62. ASMCMD> rm TEMP.283.934891281
  63. ASMCMD> pwd
  64. +data/ORCL2/TEMPFILE
  65. ASMCMD> cd ..
  66. ASMCMD> ls
  67. CONTROLFILE/
  68. DATAFILE/
  69. ONLINELOG/
  70. PARAMETERFILE/
  71. ASMCMD>
复制代码
  1. create pluggable database pdb_orcl2 using
  2. '/home/oracle/orcl2.xml' nocopy;
复制代码
新的临时文件:
  1. [oracle@station26 admin]$ asmcmd
  2. ASMCMD> ls
  3. DATA/
  4. FRA/
  5. ASMCMD> cd DATA/
  6. ASMCMD> ls
  7. ASM/
  8. CDB1/
  9. CDB2/
  10. ORCL2/
  11. orapwasm
  12. ASMCMD> cd ORCL2/
  13. ASMCMD> ls
  14. CONTROLFILE/
  15. DATAFILE/
  16. ONLINELOG/
  17. PARAMETERFILE/
  18. ASMCMD> cd ..
  19. ASMCMD> ls
  20. ASM/
  21. CDB1/
  22. CDB2/
  23. ORCL2/
  24. orapwasm
  25. ASMCMD> cd CDB2/
  26. ASMCMD> ls
  27. 47853795B0E904BAE0531A00A8C0DAD5/
  28. ASMCMD>
  29. TEMPFILE/
  30. ASMCMD> cd TEMPFILE/
  31. ASMCMD> ls
  32. TEMP.283.963697221
  33. ASMCMD>
复制代码
数据文件用现成的:
Screenshot-4.png

临时文件新建的:
Screenshot-5.png




回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 20:29 , Processed in 0.042362 second(s), 27 queries .

快速回复 返回顶部 返回列表