|
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);
- 3 end;
- 4 /
- PL/SQL procedure successfully completed.
- SYS@PRODCDB>select dbms_xdb.gethttpport() from dual;
复制代码
CloudControl 推agent要sudo:
- [root@station17 ~]# man 5 sudoers
- ![root@station17 ~]# cat /etc/sudo
- sudo.conf sudoers sudoers.d/ sudo-ldap.conf
- [root@station17 ~]# cat /etc/sudo
- sudo.conf sudoers sudoers.d/ sudo-ldap.conf
- [root@station17 ~]# cat /etc/sudoers
- Runas_Alias SUSER = root
- User_Alias ORA = oracle
- root ALL=(ALL) ALL
- ORA ALL=(SUSER) NOPASSWD: ALL
- [root@station17 ~]#
复制代码
keep forever 的不能放在闪回区:
- [oracle@station90 ~]$ rman target / catalog rc_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> backup section size 10M keep forever tablespace 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> backup section size 10M keep forever tablespace pdbprod1:system format '/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 NUMBER NOT NULL,
- quantity_sold NUMBER(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.
复制代码
|
|