设为首页收藏本站

Botang唐波's Oracle Station

查看: 603|回复: 0

课程第66次(2017-12-07星期四)

[复制链接]

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
发表于 2017-12-7 19:36:40 | 显示全部楼层 |阅读模式
1Z0-063第3/4章
1Z0-05219章(上完19章),1Z0-05321章(上完21章)和1Z0-063多租户部分共9章(上4
总共上完全部49章中的44
非容器变成插件以后文件系统或磁盘组上的清理(P77忽略的)

  1. [oracle@station26 admin]$ vim tnsnames.ora
  2. [oracle@station26 admin]$ cp tnsnames.ora   /u01/app/oracle/product/12.1.0/grid/network/admin/
  3. [oracle@station26 admin]$ tnsping pdb_orcl2

  4. TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-DEC-2017 19:20:51

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

  6. Used parameter files:


  7. Used TNSNAMES adapter to resolve the alias
  8. Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = station26.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb_orcl2)))
  9. OK (10 msec)
  10. [oracle@station26 admin]$ cd /u01/app/oracle/product/
  11. 12.1.0/ agent/  
  12. [oracle@station26 admin]$ cd /u01/app/oracle/product/12.1.0/
  13. dbhome_1/ dbhome_2/ grid/     
  14. [oracle@station26 admin]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/
  15. [oracle@station26 admin]$ sqlplus  sys/oracle_4U@pdb_orcl2  as sysdba                     

  16. SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 27 19:22:11 2017

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


  18. Connected to:
  19. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  20. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  21. and Real Application Testing options

  22. SQL> select t.name , d.name ,t.con_id, d.status
  23.   2  from v$tablespace t, v$datafile d
  24.   3  where t.ts#=d.ts#
  25.   4  order by 1,2 ;

  26. SQL> set linesize 10000
  27. SQL> /

  28. NAME                               NAME                                                                                                             CON_ID STATUS
  29. ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- -------
  30. EXAMPLE                        +DATA/ORCL2/DATAFILE/example.284.934891291                                                                  5 ONLINE
  31. SYSAUX                               +DATA/ORCL2/DATAFILE/sysaux.275.934891047                                                                  5 ONLINE
  32. SYSTEM                               +DATA/ORCL2/DATAFILE/system.276.934891113                                                                  5 SYSTEM
  33. UNDOTBS1                       /u01/app/oracle/oradata/cdb2/undotbs01.dbf                                                                  0 ONLINE
  34. USERS                               +DATA/ORCL2/DATAFILE/users.277.934891199                                                                   5 ONLINE


  35. SQL> exit
  36. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  37. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  38. and Real Application Testing options
  39. [oracle@station26 admin]$ . oraenv
  40. ORACLE_SID = [cdb1] ? +ASM
  41. The Oracle base remains unchanged with value /u01/app/oracle
  42. [oracle@station26 admin]$ asmcmd
  43. ASMCMD> ls
  44. DATA/
  45. FRA/
  46. ASMCMD> cd DATA/
  47. ASMCMD> ls
  48. ASM/
  49. CDB1/
  50. CDB2/
  51. ORCL2/
  52. orapwasm
  53. ASMCMD> cd ORCL2/
  54. ASMCMD> ls
  55. CONTROLFILE/
  56. DATAFILE/
  57. ONLINELOG/
  58. PARAMETERFILE/
  59. ASMCMD> cd DATAFILE/
  60. ASMCMD> ls
  61. EXAMPLE.284.934891291
  62. SYSAUX.275.934891047
  63. SYSTEM.276.934891113
  64. UNDOTBS1.278.934891199
  65. USERS.277.934891199
  66. ASMCMD> rm UNDOTBS1.278.934891199
  67. ASMCMD> cd ..
  68. ASMCMD> ls
  69. CONTROLFILE/
  70. DATAFILE/
  71. ONLINELOG/
  72. PARAMETERFILE/
  73. ASMCMD> cd CONTROLFILE/
  74. ASMCMD> ls
  75. Current.279.934891255
  76. ASMCMD> cd ..
  77. ASMCMD> ls
  78. CONTROLFILE/
  79. DATAFILE/
  80. ONLINELOG/
  81. PARAMETERFILE/
  82. ASMCMD> cd ..
  83. ASMCMD> ls
  84. ASM/
  85. CDB1/
  86. CDB2/
  87. ORCL2/
  88. orapwasm
  89. ASMCMD> pwd
  90. +DATA
  91. ASMCMD> cd ORCL2/
  92. ASMCMD> ls
  93. CONTROLFILE/
  94. DATAFILE/
  95. ONLINELOG/
  96. PARAMETERFILE/
  97. ASMCMD> rm -r CONTROLFILE/
  98. You may delete multiple files and/or directories.
  99. Are you sure? (y/n) y
  100. ASMCMD> rm -f ONLINELOG/
  101. ORA-15032: not all alterations performed
  102. ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)
  103. ASMCMD> ls
  104. DATAFILE/
  105. ONLINELOG/
  106. PARAMETERFILE/
  107. ASMCMD> cd ONLINELOG/
  108. ASMCMD> ls
  109. group_1.280.934891259
  110. group_2.281.934891263
  111. group_3.282.934891267
  112. ASMCMD> rm *
  113. You may delete multiple files and/or directories.
  114. Are you sure? (y/n) y
  115. ASMCMD> cd ..
  116. ASMCMD> ls
  117. DATAFILE/
  118. PARAMETERFILE/
  119. ASMCMD> rm PARAMETERFILE/ -r
  120. You may delete multiple files and/or directories.
  121. Are you sure? (y/n) y
  122. ASMCMD> ls
  123. DATAFILE/
  124. ASMCMD> cd ..
  125. ASMCMD> ls
  126. ASM/
  127. CDB1/
  128. CDB2/
  129. ORCL2/
  130. orapwasm
  131. ASMCMD> cd ..
  132. ASMCMD> ls
  133. DATA/
  134. FRA/
  135. ASMCMD> cd fra
  136. ASMCMD> ls
  137. CDB1/
  138. ORCL2/
  139. ASMCMD> cd ORCL2/
  140. ASMCMD> ls
  141. CONTROLFILE/
  142. ONLINELOG/
  143. ASMCMD> cd ..
  144. ASMCMD> ls
  145. CDB1/
  146. ORCL2/
  147. ASMCMD> rm -rf ORCL2/
  148. ASMCMD> ls
  149. CDB1/
  150. ASMCMD> cd ..
  151. ASMCMD> ls
  152. DATA/
  153. FRA/
  154. ASMCMD> cd ..
  155. ASMCMD> cd
  156. usage: cd <dir>
  157. help:  help cd
  158. ASMCMD> ls
  159. DATA/
  160. FRA/
  161. ASMCMD> cd data
  162. ASMCMD> ls
  163. ASM/
  164. CDB1/
  165. CDB2/
  166. ORCL2/
  167. orapwasm
  168. ASMCMD> cd orcl2
  169. ASMCMD> ls
  170. DATAFILE/
  171. ASMCMD> cd datafile
  172. ASMCMD> ls
  173. EXAMPLE.284.934891291
  174. SYSAUX.275.934891047
  175. SYSTEM.276.934891113
  176. USERS.277.934891199
  177. ASMCMD> exit
  178. [oracle@station26 admin]$ sqlplus /nolog

  179. SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 27 19:27:44 2017

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


  181. SQL> conn  sys/oracle_4U@pdb_orcl2  as sysdba
  182. Connected.
  183. SQL> select t.name , d.name ,t.con_id, d.status
  184.   2  from v$tablespace t, v$tempfile d
  185.   3   where t.ts#=d.ts#;

  186. NAME
  187. ------------------------------
  188. NAME
  189. --------------------------------------------------------------------------------
  190.     CON_ID STATUS
  191. ---------- -------
  192. TEMP
  193. +DATA/CDB2/47853795B0E904BAE0531A00A8C0DAD5/TEMPFILE/temp.283.963697221
  194.          5 ONLINE


  195. SQL> exit
  196. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  197. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  198. and Real Application Testing options
  199. [oracle@station26 admin]$ cd /u01/app/oracle/oradata/
  200. [oracle@station26 oradata]$ ls
  201. cdb2  orcl
  202. [oracle@station26 oradata]$ cd cdb2
  203. [oracle@station26 cdb2]$ ls
  204. control01.ctl  pdb2_2   redo01.log  redo03.log    system01.dbf  undotbs01.dbf
  205. pdb2_1         pdbseed  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
  206. [oracle@station26 cdb2]$ cd pdb2_1
  207. [oracle@station26 pdb2_1]$ ls
  208. pdbseed_temp012017-12-22_06-04-39-PM.dbf  sysaux01.dbf  system01.dbf
  209. [oracle@station26 pdb2_1]$ cd ..
  210. [oracle@station26 cdb2]$ ls
  211. control01.ctl  pdb2_2   redo01.log  redo03.log    system01.dbf  undotbs01.dbf
  212. pdb2_1         pdbseed  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
  213. [oracle@station26 cdb2]$ cd pdb2_2
  214. [oracle@station26 pdb2_2]$ ls
  215. CDB2
  216. [oracle@station26 pdb2_2]$ cd CDB2
  217. [oracle@station26 CDB2]$ ls
  218. 612B093C409C4C0FE0531A00A8C013C6
  219. [oracle@station26 CDB2]$ cd 612B093C409C4C0FE0531A00A8C013C6
  220. [oracle@station26 612B093C409C4C0FE0531A00A8C013C6]$ ls
  221. datafile
  222. [oracle@station26 612B093C409C4C0FE0531A00A8C013C6]$ cd datafile
  223. [oracle@station26 datafile]$ ls
  224. o1_mf_sysaux_f41yt1kj_.dbf  o1_mf_system_f41yt1kf_.dbf  o1_mf_temp_f41yt1kl_.dbf
  225. [oracle@station26 datafile]$ cd ..
  226. [oracle@station26 612B093C409C4C0FE0531A00A8C013C6]$ ls
  227. datafile
  228. [oracle@station26 612B093C409C4C0FE0531A00A8C013C6]$ cd ..
  229. [oracle@station26 CDB2]$ ls
  230. 612B093C409C4C0FE0531A00A8C013C6
  231. [oracle@station26 CDB2]$ cd ..
  232. [oracle@station26 pdb2_2]$ ls
  233. CDB2
  234. [oracle@station26 pdb2_2]$ cd ..
  235. [oracle@station26 cdb2]$ ls
  236. control01.ctl  pdb2_2   redo01.log  redo03.log    system01.dbf  undotbs01.dbf
  237. pdb2_1         pdbseed  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
  238. [oracle@station26 cdb2]$ cd pdbseed
  239. [oracle@station26 pdbseed]$ ls
  240. pdbseed_temp012017-12-22_06-04-39-PM.dbf  sysaux01.dbf  system01.dbf
  241. [oracle@station26 pdbseed]$ cd ..
  242. [oracle@station26 cdb2]$ ls
  243. control01.ctl  pdb2_2   redo01.log  redo03.log    system01.dbf  undotbs01.dbf
  244. pdb2_1         pdbseed  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
  245. [oracle@station26 cdb2]$ cd ..
  246. [oracle@station26 oradata]$ ls
  247. cdb2  orcl
  248. [oracle@station26 oradata]$
复制代码
把非容器插入容器数据库时,在执行noncdb_t0_pdb.sql脚本前,不要手工打开库:
Screenshot.png

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

  3. select  con_id, pdb_name,    status
  4. from cdb_pdbs;

  5. alter pluggable database pdb_orcl2 open ;
复制代码

插入非cdb如果要拷贝,语法是:
  1. create pluggable database xxx  using '...xml'
  2. file_name_convert=('源头',‘目的地’);
复制代码

如果要改插件数据库的名称,  要在插件中改,插件要打开成startup restrict:
  1. alter pluggable database xxx rename global_name to yyy
复制代码

外面插入的数据库,是没有pdb_admin..这类的本地用户的:
  1. select    u.USERNAME
  2. from cdb_users u
  3. where con_id=5 and common='NO';


  4. select    u.USERNAME
  5. from cdb_users u
  6. where con_id=3 and common='NO';


  7.   select    u.USERNAME
  8. from cdb_users u
  9. where con_id=4 and common='NO';
复制代码

检查别的插件数据库,是否能插入本容器的语法:
  1. QL> conn / as sysdba
  2. Connected.
  3. SQL> show con_name

  4. CON_NAME
  5. ------------------------------
  6. CDB$ROOT
  7. SQL> set serveroutput on
  8. SQL> declare
  9.   2    v_1  boolean  := false;
  10.   3  begin
  11.   4   v_1 := dbms_pdb.CHECK_PLUG_COMPATIBILITY('/home/oracle/pdb1_1.xml','pdb1_1') ;
  12.   5    if v_1 then
  13.   6     dbms_output.put_line('OK');
  14.   7     end if;
  15.   8  end;
  16.   9  /
  17. OK

  18. PL/SQL procedure successfully completed.

复制代码
如果以上检查显示成false请看以下视图:
  1. SQL> select message, action from pdb_plug_in_violations
  2.   2  ;

  3. MESSAGE
  4. --------------------------------------------------------------------------------
  5. ACTION
  6. --------------------------------------------------------------------------------
  7. CDB parameter memory_target mismatch: Previous 2G Current 3104M
  8. Please check the parameter in the current CDB

  9. PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
  10. Run noncdb_to_pdb.sql.

  11. CDB parameter sga_target mismatch: Previous 1536M Current 0
  12. Please check the parameter in the current CDB

  13. Service name or network name of service orcl in the PDB is invalid or conflicts
  14. with an existing service name or network name in the CDB.
  15. Drop the service and recreate it with an appropriate name.

  16. CDB parameter pga_aggregate_target mismatch: Previous 512M Current 0
  17. Please check the parameter in the current CDB

  18. PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
  19. Run noncdb_to_pdb.sql.

  20. CDB parameter _catalog_foreign_restore mismatch: Previous FALSE Current TRUE
  21. Please check the parameter in the current CDB


  22. 7 rows selected.

  23. SQL> select message, action from pdb_plug_in_violations  where pdb_name ='PDB1_1';
  24. select message, action from pdb_plug_in_violations  where pdb_name ='PDB1_1'
  25.                                                           *
  26. ERROR at line 1:
  27. ORA-00904: "PDB_NAME": invalid identifier


  28. SQL> select message, action from pdb_plug_in_violations  where  name='PDB1_1';

  29. MESSAGE
  30. --------------------------------------------------------------------------------
  31. ACTION
  32. --------------------------------------------------------------------------------
  33. CDB parameter _catalog_foreign_restore mismatch: Previous FALSE Current TRUE
  34. Please check the parameter in the current CDB


  35. 1 row selected.

  36. SQL>
复制代码
Screenshot.png

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

  2. select  con_id , pdb_name , status from cdb_pdbs;

  3. alter pluggable database pdb1_1 open ;

  4. select t.con_id , t.name ,  d. name , d.status
  5.   from v$tablespace t, v$datafile d
  6.   where t.con_id=d.con_id and t.ts#=d.ts#
  7.    order by 1 ,2;
  8.    
  9.    alter pluggable database all except pdb2_2 open ;
  10.    
  11.    
  12.    create or replace trigger trg1
  13.    after startup on database
  14.    begin
  15.     execute immediate 'alter pluggable database all open';
  16. end;
复制代码


回复

使用道具 举报

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

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-9-20 12:45 , Processed in 0.196456 second(s), 27 queries .

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