课程第42次（2018-10-12星期五）

botang

OEM Express：
查询xdb的端口与11g 远程Scheduler是一样:

1. SYS@PRODCDB>select dbms_xdb_config.gethttpport() from dual;
   
2. 
   
3. DBMS_XDB_CONFIG.GETHTTPPORT()
   
4. -----------------------------
   
5.                             0
   
6. 
   
7. SYS@PRODCDB>select dbms_xdb.gethttpport() from dual;
   
8. 
   
9. DBMS_XDB.GETHTTPPORT()
   
10. ----------------------
    
11.                      0
    
12. 
    
13.     BEGIN
    
14.     DBMS_XDB.SETHTTPPORT(5501);
    
15.   3      END;
    
16.   4  /
    
17. 
    
18. PL/SQL procedure successfully completed.
    
19. 
    
20. SYS@PRODCDB>select dbms_xdb.gethttpport() from dual;
    
21. 
    
22. DBMS_XDB.GETHTTPPORT()
    
23. ----------------------
    
24.                   5501
    
25. 
    
26. SYS@PRODCDB>BEGIN
    
27.   2   DBMS_XDB_config.SETHTTPPORT(5501);
    
28.   3  end;
    
29.   4  /
    
30. 
    
31. PL/SQL procedure successfully completed.
    
32. 
    
33. 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. 
   
10. root    ALL=(ALL) ALL
    
11. ORA     ALL=(SUSER)     NOPASSWD: ALL
    
12. [root@station17 ~]#
    

复制代码

keep forever 的不能放在闪回区：

1. [oracle@station90 ~]$ rman target /  catalog  rc_admin/oracle_4U@emrep
   
2. 
   
3. Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 12 20:58:16 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. connected to target database: PRODCDB (DBID=2986132469)
   
8. connected to recovery catalog database
   
9. 
   
10. RMAN> backup  section size 10M keep forever  tablespace pdbprod1:system;
    
11. 
    
12. Starting backup at 12-OCT-18
    
13. starting full resync of recovery catalog
    
14. full resync complete
    
15. 
    
16. allocated channel: ORA_DISK_1
    
17. channel ORA_DISK_1: SID=708 device type=DISK
    
18. backup will never be obsolete
    
19. archived logs required to recover from this backup will be backed up
    
20. channel ORA_DISK_1: starting full datafile backup set
    
21. channel ORA_DISK_1: specifying datafile(s) in backup set
    
22. input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
    
23. backing up blocks 1 through 1280
    
24. channel ORA_DISK_1: starting piece 1 at 12-OCT-18
    
25. RMAN-00571: ===========================================================
    
26. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    
27. RMAN-00571: ===========================================================
    
28. RMAN-03002: failure of backup command at 10/12/2018 20:58:28
    
29. ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes
    
30. 
    
31. RMAN>
    

复制代码

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

复制代码

在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. 
    
20. Table created.
    

复制代码