Bo's Oracle Station

查看: 3019|回复: 0

课程第42次(2018-10-12星期五)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-10-12 19:51:55 | 显示全部楼层 |阅读模式
OEM Express:
查询xdb的端口与11g 远程Scheduler是一样:
  1. SYS@PRODCDB>select dbms_xdb_config.gethttpport() from dual;

  2. DBMS_XDB_CONFIG.GETHTTPPORT()
  3. -----------------------------
  4.                             0

  5. SYS@PRODCDB>select dbms_xdb.gethttpport() from dual;

  6. DBMS_XDB.GETHTTPPORT()
  7. ----------------------
  8.                      0

  9.     BEGIN
  10.     DBMS_XDB.SETHTTPPORT(5501);
  11.   3      END;
  12.   4  /

  13. PL/SQL procedure successfully completed.

  14. SYS@PRODCDB>select dbms_xdb.gethttpport() from dual;

  15. DBMS_XDB.GETHTTPPORT()
  16. ----------------------
  17.                   5501

  18. SYS@PRODCDB>BEGIN
  19.   2   DBMS_XDB_config.SETHTTPPORT(5501);
  20.   3  end;
  21.   4  /

  22. PL/SQL procedure successfully completed.

  23. SYS@PRODCDB>select dbms_xdb.gethttpport() from dual;
复制代码




CloudControl 推agent要sudo:
  1. [root@station17 ~]# man 5 sudoers
  2. ![root@station17 ~]# cat /etc/sudo
  3. sudo.conf       sudoers         sudoers.d/      sudo-ldap.conf  
  4. [root@station17 ~]# cat /etc/sudo
  5. sudo.conf       sudoers         sudoers.d/      sudo-ldap.conf  
  6. [root@station17 ~]# cat /etc/sudoers
  7. Runas_Alias     SUSER = root
  8. User_Alias      ORA = oracle

  9. root    ALL=(ALL) ALL
  10. ORA     ALL=(SUSER)     NOPASSWD: ALL
  11. [root@station17 ~]#
复制代码

keep forever 的不能放在闪回区:
  1. [oracle@station90 ~]$ rman target /  catalog  rc_admin/oracle_4U@emrep

  2. Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 12 20:58:16 2018

  3. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

  4. connected to target database: PRODCDB (DBID=2986132469)
  5. connected to recovery catalog database

  6. RMAN> backup  section size 10M keep forever  tablespace pdbprod1:system;

  7. Starting backup at 12-OCT-18
  8. starting full resync of recovery catalog
  9. full resync complete

  10. allocated channel: ORA_DISK_1
  11. channel ORA_DISK_1: SID=708 device type=DISK
  12. backup will never be obsolete
  13. archived logs required to recover from this backup will be backed up
  14. channel ORA_DISK_1: starting full datafile backup set
  15. channel ORA_DISK_1: specifying datafile(s) in backup set
  16. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  17. backing up blocks 1 through 1280
  18. channel ORA_DISK_1: starting piece 1 at 12-OCT-18
  19. RMAN-00571: ===========================================================
  20. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  21. RMAN-00571: ===========================================================
  22. RMAN-03002: failure of backup command at 10/12/2018 20:58:28
  23. ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

  24. RMAN>
复制代码
  1. RMAN> backup  section size 10M keep forever  tablespace pdbprod1:system  format '/home/oracle/pdbprod1_system_%U.bks';

  2. Starting backup at 12-OCT-18

  3. using channel ORA_DISK_1
  4. backup will never be obsolete
  5. archived logs required to recover from this backup will be backed up
  6. channel ORA_DISK_1: starting full datafile backup set
  7. channel ORA_DISK_1: specifying datafile(s) in backup set
  8. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  9. backing up blocks 1 through 1280
  10. channel ORA_DISK_1: starting piece 1 at 12-OCT-18
  11. channel ORA_DISK_1: finished piece 1 at 12-OCT-18
  12. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_1_1.bks tag=TAG20181012T210022 comment=NONE
  13. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  14. channel ORA_DISK_1: starting full datafile backup set
  15. channel ORA_DISK_1: specifying datafile(s) in backup set
  16. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  17. backing up blocks 1281 through 2560
  18. channel ORA_DISK_1: starting piece 2 at 12-OCT-18
  19. channel ORA_DISK_1: finished piece 2 at 12-OCT-18
  20. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_2_1.bks tag=TAG20181012T210022 comment=NONE
  21. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  22. channel ORA_DISK_1: starting full datafile backup set
  23. channel ORA_DISK_1: specifying datafile(s) in backup set
  24. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  25. backing up blocks 2561 through 3840
  26. channel ORA_DISK_1: starting piece 3 at 12-OCT-18
  27. channel ORA_DISK_1: finished piece 3 at 12-OCT-18
  28. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_3_1.bks tag=TAG20181012T210022 comment=NONE
  29. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  30. channel ORA_DISK_1: starting full datafile backup set
  31. channel ORA_DISK_1: specifying datafile(s) in backup set
  32. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  33. backing up blocks 3841 through 5120
  34. channel ORA_DISK_1: starting piece 4 at 12-OCT-18
  35. channel ORA_DISK_1: finished piece 4 at 12-OCT-18
  36. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_4_1.bks tag=TAG20181012T210022 comment=NONE
  37. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  38. channel ORA_DISK_1: starting full datafile backup set
  39. channel ORA_DISK_1: specifying datafile(s) in backup set
  40. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  41. backing up blocks 5121 through 6400
  42. channel ORA_DISK_1: starting piece 5 at 12-OCT-18
  43. channel ORA_DISK_1: finished piece 5 at 12-OCT-18
  44. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_5_1.bks tag=TAG20181012T210022 comment=NONE
  45. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  46. channel ORA_DISK_1: starting full datafile backup set
  47. channel ORA_DISK_1: specifying datafile(s) in backup set
  48. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  49. backing up blocks 6401 through 7680
  50. channel ORA_DISK_1: starting piece 6 at 12-OCT-18
  51. channel ORA_DISK_1: finished piece 6 at 12-OCT-18
  52. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_6_1.bks tag=TAG20181012T210022 comment=NONE
  53. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  54. channel ORA_DISK_1: starting full datafile backup set
  55. channel ORA_DISK_1: specifying datafile(s) in backup set
  56. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  57. backing up blocks 7681 through 8960
  58. channel ORA_DISK_1: starting piece 7 at 12-OCT-18
  59. channel ORA_DISK_1: finished piece 7 at 12-OCT-18
  60. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_7_1.bks tag=TAG20181012T210022 comment=NONE
  61. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  62. channel ORA_DISK_1: starting full datafile backup set
  63. channel ORA_DISK_1: specifying datafile(s) in backup set
  64. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  65. backing up blocks 8961 through 10240
  66. channel ORA_DISK_1: starting piece 8 at 12-OCT-18
  67. channel ORA_DISK_1: finished piece 8 at 12-OCT-18
  68. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_8_1.bks tag=TAG20181012T210022 comment=NONE
  69. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  70. channel ORA_DISK_1: starting full datafile backup set
  71. channel ORA_DISK_1: specifying datafile(s) in backup set
  72. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  73. backing up blocks 10241 through 11520
  74. channel ORA_DISK_1: starting piece 9 at 12-OCT-18
  75. channel ORA_DISK_1: finished piece 9 at 12-OCT-18
  76. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_9_1.bks tag=TAG20181012T210022 comment=NONE
  77. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  78. channel ORA_DISK_1: starting full datafile backup set
  79. channel ORA_DISK_1: specifying datafile(s) in backup set
  80. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  81. backing up blocks 11521 through 12800
  82. channel ORA_DISK_1: starting piece 10 at 12-OCT-18
  83. channel ORA_DISK_1: finished piece 10 at 12-OCT-18
  84. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_10_1.bks tag=TAG20181012T210022 comment=NONE
  85. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  86. channel ORA_DISK_1: starting full datafile backup set
  87. channel ORA_DISK_1: specifying datafile(s) in backup set
  88. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  89. backing up blocks 12801 through 14080
  90. channel ORA_DISK_1: starting piece 11 at 12-OCT-18
  91. channel ORA_DISK_1: finished piece 11 at 12-OCT-18
  92. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_11_1.bks tag=TAG20181012T210022 comment=NONE
  93. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  94. channel ORA_DISK_1: starting full datafile backup set
  95. channel ORA_DISK_1: specifying datafile(s) in backup set
  96. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  97. backing up blocks 14081 through 15360
  98. channel ORA_DISK_1: starting piece 12 at 12-OCT-18
  99. channel ORA_DISK_1: finished piece 12 at 12-OCT-18
  100. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_12_1.bks tag=TAG20181012T210022 comment=NONE
  101. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  102. channel ORA_DISK_1: starting full datafile backup set
  103. channel ORA_DISK_1: specifying datafile(s) in backup set
  104. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  105. backing up blocks 15361 through 16640
  106. channel ORA_DISK_1: starting piece 13 at 12-OCT-18
  107. channel ORA_DISK_1: finished piece 13 at 12-OCT-18
  108. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_13_1.bks tag=TAG20181012T210022 comment=NONE
  109. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  110. channel ORA_DISK_1: starting full datafile backup set
  111. channel ORA_DISK_1: specifying datafile(s) in backup set
  112. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  113. backing up blocks 16641 through 17920
  114. channel ORA_DISK_1: starting piece 14 at 12-OCT-18
  115. channel ORA_DISK_1: finished piece 14 at 12-OCT-18
  116. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_14_1.bks tag=TAG20181012T210022 comment=NONE
  117. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  118. channel ORA_DISK_1: starting full datafile backup set
  119. channel ORA_DISK_1: specifying datafile(s) in backup set
  120. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  121. backing up blocks 17921 through 19200
  122. channel ORA_DISK_1: starting piece 15 at 12-OCT-18
  123. channel ORA_DISK_1: finished piece 15 at 12-OCT-18
  124. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_15_1.bks tag=TAG20181012T210022 comment=NONE
  125. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  126. channel ORA_DISK_1: starting full datafile backup set
  127. channel ORA_DISK_1: specifying datafile(s) in backup set
  128. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  129. backing up blocks 19201 through 20480
  130. channel ORA_DISK_1: starting piece 16 at 12-OCT-18
  131. channel ORA_DISK_1: finished piece 16 at 12-OCT-18
  132. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_16_1.bks tag=TAG20181012T210022 comment=NONE
  133. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  134. channel ORA_DISK_1: starting full datafile backup set
  135. channel ORA_DISK_1: specifying datafile(s) in backup set
  136. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  137. backing up blocks 20481 through 21760
  138. channel ORA_DISK_1: starting piece 17 at 12-OCT-18
  139. channel ORA_DISK_1: finished piece 17 at 12-OCT-18
  140. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_17_1.bks tag=TAG20181012T210022 comment=NONE
  141. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  142. channel ORA_DISK_1: starting full datafile backup set
  143. channel ORA_DISK_1: specifying datafile(s) in backup set
  144. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  145. backing up blocks 21761 through 23040
  146. channel ORA_DISK_1: starting piece 18 at 12-OCT-18
  147. channel ORA_DISK_1: finished piece 18 at 12-OCT-18
  148. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_18_1.bks tag=TAG20181012T210022 comment=NONE
  149. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  150. channel ORA_DISK_1: starting full datafile backup set
  151. channel ORA_DISK_1: specifying datafile(s) in backup set
  152. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  153. backing up blocks 23041 through 24320
  154. channel ORA_DISK_1: starting piece 19 at 12-OCT-18
  155. channel ORA_DISK_1: finished piece 19 at 12-OCT-18
  156. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_19_1.bks tag=TAG20181012T210022 comment=NONE
  157. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  158. channel ORA_DISK_1: starting full datafile backup set
  159. channel ORA_DISK_1: specifying datafile(s) in backup set
  160. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  161. backing up blocks 24321 through 25600
  162. channel ORA_DISK_1: starting piece 20 at 12-OCT-18
  163. channel ORA_DISK_1: finished piece 20 at 12-OCT-18
  164. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_20_1.bks tag=TAG20181012T210022 comment=NONE
  165. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  166. channel ORA_DISK_1: starting full datafile backup set
  167. channel ORA_DISK_1: specifying datafile(s) in backup set
  168. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  169. backing up blocks 25601 through 26880
  170. channel ORA_DISK_1: starting piece 21 at 12-OCT-18
  171. channel ORA_DISK_1: finished piece 21 at 12-OCT-18
  172. piece handle=/home/oracle/pdbprod1_system_02tfgnj7_21_1.bks tag=TAG20181012T210022 comment=NONE
  173. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  174. channel ORA_DISK_1: starting full datafile backup set
  175. channel ORA_DISK_1: specifying datafile(s) in backup set
  176. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
  177. backing up blocks 26881 through 28160
  178. channel ORA_DISK_1: starting piece 22 at 12-OCT-18
复制代码

配置PDBPROD1 数据库,使其中的物化视图支持查询重写,即使在约束没有被验证的情况下。
  1. @>conn / as sysdba
  2. Connected.
  3. SYS@PRODCDB>show parameter query

  4. NAME                                     TYPE         VALUE
  5. ------------------------------------ ----------- ------------------------------
  6. inmemory_query                             string         ENABLE
  7. query_rewrite_enabled                     string         TRUE
  8. query_rewrite_integrity              string         enforced
  9. SYS@PRODCDB>alter system set query_rewrite_integrity=trusted;

  10. System altered.

复制代码

在PDBPROD1 中,SH 用户下创建间隔分区
   表名:sales_history_2014,按照2011,2012,2013,2014 年份来分区 ;
   分区名:SAL1,SAL2,SAL3,SAL4
   基于time_id 分区
  列:
PROD_ID number not null
CUST_ID number not null
TIME_ID DATE not null
CHANNEL_ID number not null
PROMO_ID number not null
QUANTITY_SOLD number(10,2) not null
AMOUNT_SOLD number(10,2) not null


  1. CREATE TABLE sales_history_2014
  2. (
  3. prod_id        NUMBER NOT NULL,
  4. cust_id        NUMBER NOT NULL,
  5. time_id        DATE NOT NULL,
  6. channel_id     NUMBER NOT NULL,
  7. promo_id       NUMBER  NOT NULL,
  8. quantity_sold  NUMBER(10,2) NOT NULL,
  9. amount_sold    NUMBER(10,2) NOT NULL
  10. )
  11.         PARTITION BY RANGE (time_id)
  12.         INTERVAL(NUMTOYMINTERVAL(1, 'YEAR')) store in (sales_tbs1,sales_tbs2,sales_tbs3,sales_tbs4)
  13.         (
  14.         PARTITION sa1 VALUES LESS THAN (TO_DATE('2012-1-1', 'YYYY-MM-DD')) tablespace sales_tbs1,
  15.         PARTITION sa2 VALUES LESS THAN (TO_DATE('2013-1-1', 'YYYY-MM-DD')) tablespace sales_tbs2,
  16.         PARTITION sa3 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD')) tablespace sales_tbs3,
  17.         PARTITION sa4 VALUES LESS THAN (TO_DATE('2015-1-1', 'YYYY-MM-DD')) tablespace sales_tbs4
  18.         );

  19. Table created.
复制代码



回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-11-1 07:53 , Processed in 0.037262 second(s), 25 queries .

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