课程第42次（2018-10-12星期五）
OEM Express：查询xdb的端口与11g 远程Scheduler是一样:
SYS@PRODCDB>select dbms_xdb_config.gethttpport() from dual;
DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
0
SYS@PRODCDB>select dbms_xdb.gethttpport() from dual;
DBMS_XDB.GETHTTPPORT()
----------------------
0
BEGIN
DBMS_XDB.SETHTTPPORT(5501);
3 END;
4/
PL/SQL procedure successfully completed.
SYS@PRODCDB>select dbms_xdb.gethttpport() from dual;
DBMS_XDB.GETHTTPPORT()
----------------------
5501
SYS@PRODCDB>BEGIN
2 DBMS_XDB_config.SETHTTPPORT(5501);
3end;
4/
PL/SQL procedure successfully completed.
SYS@PRODCDB>select dbms_xdb.gethttpport() from dual;
CloudControl 推agent要sudo：
# man 5 sudoers
!# cat /etc/sudo
sudo.conf sudoers sudoers.d/ sudo-ldap.conf
# cat /etc/sudo
sudo.conf sudoers sudoers.d/ sudo-ldap.conf
# cat /etc/sudoers
Runas_Alias SUSER = root
User_Alias ORA = oracle
root ALL=(ALL) ALL
ORA ALL=(SUSER) NOPASSWD: ALL
#
keep forever 的不能放在闪回区：
$ rman target /catalogrc_admin/oracle_4U@emrep
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 12 20:58:16 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
connected to target database: PRODCDB (DBID=2986132469)
connected to recovery catalog database
RMAN> backupsection size 10M keep forevertablespace pdbprod1:system;
Starting backup at 12-OCT-18
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=708 device type=DISK
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 1 through 1280
channel ORA_DISK_1: starting piece 1 at 12-OCT-18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 10/12/2018 20:58:28
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes
RMAN>
RMAN> backupsection size 10M keep forevertablespace pdbprod1:systemformat '/home/oracle/pdbprod1_system_%U.bks';
Starting backup at 12-OCT-18
using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 1 through 1280
channel ORA_DISK_1: starting piece 1 at 12-OCT-18
channel ORA_DISK_1: finished piece 1 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_1_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 1281 through 2560
channel ORA_DISK_1: starting piece 2 at 12-OCT-18
channel ORA_DISK_1: finished piece 2 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_2_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 2561 through 3840
channel ORA_DISK_1: starting piece 3 at 12-OCT-18
channel ORA_DISK_1: finished piece 3 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_3_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 3841 through 5120
channel ORA_DISK_1: starting piece 4 at 12-OCT-18
channel ORA_DISK_1: finished piece 4 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_4_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 5121 through 6400
channel ORA_DISK_1: starting piece 5 at 12-OCT-18
channel ORA_DISK_1: finished piece 5 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_5_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 6401 through 7680
channel ORA_DISK_1: starting piece 6 at 12-OCT-18
channel ORA_DISK_1: finished piece 6 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_6_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 7681 through 8960
channel ORA_DISK_1: starting piece 7 at 12-OCT-18
channel ORA_DISK_1: finished piece 7 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_7_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 8961 through 10240
channel ORA_DISK_1: starting piece 8 at 12-OCT-18
channel ORA_DISK_1: finished piece 8 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_8_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 10241 through 11520
channel ORA_DISK_1: starting piece 9 at 12-OCT-18
channel ORA_DISK_1: finished piece 9 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_9_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 11521 through 12800
channel ORA_DISK_1: starting piece 10 at 12-OCT-18
channel ORA_DISK_1: finished piece 10 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_10_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 12801 through 14080
channel ORA_DISK_1: starting piece 11 at 12-OCT-18
channel ORA_DISK_1: finished piece 11 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_11_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 14081 through 15360
channel ORA_DISK_1: starting piece 12 at 12-OCT-18
channel ORA_DISK_1: finished piece 12 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_12_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 15361 through 16640
channel ORA_DISK_1: starting piece 13 at 12-OCT-18
channel ORA_DISK_1: finished piece 13 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_13_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 16641 through 17920
channel ORA_DISK_1: starting piece 14 at 12-OCT-18
channel ORA_DISK_1: finished piece 14 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_14_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 17921 through 19200
channel ORA_DISK_1: starting piece 15 at 12-OCT-18
channel ORA_DISK_1: finished piece 15 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_15_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 19201 through 20480
channel ORA_DISK_1: starting piece 16 at 12-OCT-18
channel ORA_DISK_1: finished piece 16 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_16_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 20481 through 21760
channel ORA_DISK_1: starting piece 17 at 12-OCT-18
channel ORA_DISK_1: finished piece 17 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_17_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 21761 through 23040
channel ORA_DISK_1: starting piece 18 at 12-OCT-18
channel ORA_DISK_1: finished piece 18 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_18_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 23041 through 24320
channel ORA_DISK_1: starting piece 19 at 12-OCT-18
channel ORA_DISK_1: finished piece 19 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_19_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 24321 through 25600
channel ORA_DISK_1: starting piece 20 at 12-OCT-18
channel ORA_DISK_1: finished piece 20 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_20_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 25601 through 26880
channel ORA_DISK_1: starting piece 21 at 12-OCT-18
channel ORA_DISK_1: finished piece 21 at 12-OCT-18
piece handle=/home/oracle/pdbprod1_system_02tfgnj7_21_1.bks tag=TAG20181012T210022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PRODCDB/pdbprod1/system01.dbf
backing up blocks 26881 through 28160
channel ORA_DISK_1: starting piece 22 at 12-OCT-18
配置PDBPROD1 数据库，使其中的物化视图支持查询重写，即使在约束没有被验证的情况下。
@>conn / as sysdba
Connected.
SYS@PRODCDB>show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_query string ENABLE
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
SYS@PRODCDB>alter system set query_rewrite_integrity=trusted;
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
CREATE TABLE sales_history_2014
(
prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL,
channel_id NUMBER NOT NULL,
promo_id NUMBERNOT NULL,
quantity_soldNUMBER(10,2) NOT NULL,
amount_sold NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'YEAR')) store in (sales_tbs1,sales_tbs2,sales_tbs3,sales_tbs4)
(
PARTITION sa1 VALUES LESS THAN (TO_DATE('2012-1-1', 'YYYY-MM-DD')) tablespace sales_tbs1,
PARTITION sa2 VALUES LESS THAN (TO_DATE('2013-1-1', 'YYYY-MM-DD')) tablespace sales_tbs2,
PARTITION sa3 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD')) tablespace sales_tbs3,
PARTITION sa4 VALUES LESS THAN (TO_DATE('2015-1-1', 'YYYY-MM-DD')) tablespace sales_tbs4
);
Table created.
页:
[1]