DMSQL WITH FUNCTION子句

WITH FUNCTION子句
WITH FUNCTION子句用于在SQL语句中临时声明并定义存储函数,这些存储函数可以在其作用域内被引用。相比模式对象中的存储函数,通过WITH FUNCTION定义的存储函数在对象名解析时拥有更高的优先级。和公用表表达式CTE类似,WITH FUNCTION定义的存储函数对象也不会存储到系统表中,且只在当前SQL语句内有效。

WITH FUNCTION子句适用于偶尔需要使用存储过程的场景。和模式对象中的存储函数相比,它可以清楚地看到函数定义并避免了DDL操作带来的开销。

语法格式
WITH < 函数> {< 函数>}
参数
1.< 函数> 语法遵照《DMSQL程序设计》中存储过程的语法规则。
语句功能
供用户定义同一语句内临时使用的存储函数。
使用说明
1.中定义的函数的作用域为所在的查询表达式内;
2.同一
中函数名不得重复;
3.
中定义的函数不能是外部函数。

该语句的使用者并不需要CREATE PROCEDURE数据库权限。

举例说明
例如WITH FUNCTION中定义的函数优先级高于模式对象的例子。

SQL> WITH FUNCTION f1(C INT) RETURN INT AS BEGIN RETURN C * 10; END;
2   SELECT f1(5236) FROM DUAL;
3   /

LINEID     F1(5236)   
---------- -----------
1          52360

used time: 1.352(ms). Execute id is 34061.

例如WITH FUNCTION和公用表表达式混合的例子。

SQL> WITH FUNCTION f21(C1 INT) RETURN INT AS BEGIN RETURN C1; END;
2   SELECT f21(1) FROM dual WHERE 100 IN
3   (
4   WITH FUNCTION f22(C1 INT) RETURN INT AS BEGIN RETURN C1 + 2; END;
5   FUNCTION f23(C1 INT) RETURN INT AS BEGIN RETURN C1 - 2; END;
6   v21(C) AS (SELECT 50 FROM dual)
7   SELECT f22(C) +f23(C) FROM v21
8   );
9   /

LINEID     F21(1)     
---------- -----------
1          1

used time: 12.313(ms). Execute id is 34092.

公用表表达式子句
嵌套SQL语句如果层次过多,会使SQL语句难以阅读和维护。如果将子查询放在临时表中,会使SQL语句更容易维护,但同时也增加了额外的I/O开销,因此,临时表并不太适合数据量大且频繁查询的情况。为此,在DM7中引入了公用表表达式(CTE,COMMON TABLE EXPRESSION),使用CTE可以提高SQL语句的可维护性,同时CTE要比临时表的效率高很多。CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次。

WITH AS短语,也叫做子查询部分(SUBQUERY FACTORING),它定义一个SQL片断,该SQL片断会被整个SQL语句所用到。它可以有效提高SQL语句的可读性,也可以用在UNION ALL的不同部分,作为提供数据的部分。

公用表表达式的作用
公用表表达式(CTE)是一个在查询中定义的临时命名结果集,将在FROM子句中使用它。每个CTE仅被定义一次(但在其作用域内可以被引用任意次),并且在该查询生存期间将一直生存,而且可以使用CTE来执行递归操作。

因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用WITH AS短语,则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上,则优化器会自动将WITH AS短语所获取的数据放入一个临时表里,如果只是被调用一次则不会,很多查询通过这种方法都可以提高速度。

公用表表达式的使用
语法格式
WITH < 公用表表达式子句>{, < 公用表表达式子句>}
< 公用表表达式子句>::=< 公用表表达式名[ ( <列名>{,< 列名>} ) ] AS ( 公用表表达式子查询语句)>
参数
1.< 公用表表达式名> 公用表表达式的有效标识符;
2.< 列名> 指明被创建的公用表表达式中列的名称;
3.< 公用表表达式子查询语句> 标识公用表表达式所基于的表的行和列,其语法遵照SELECT语句的语法规则。

语句功能
供用户定义公用表表达式,也就是WITH AS语句。
使用说明
1.< 公用表表达式名>必须与在同一WITH子句中定义的任何其他公用表表达式的名称不同,但公用表表达式名可以与基表或基视图的名称相同。在查询中对公用表表达式名的任何引用都会使用公用表表达式,而不使用基对象;
2.< 列名>在一个CTE 定义中不允许出现重复的列名。指定的列名数必须与< 公用表表达式子查询语句>结果集中列数匹配。只有在查询定义中为所有结果列都提供了不同的名称时,列名称列表才是可选的;
3.< 公用表表达式子查询语句>指定一个结果集填充公用表表达式的SELECT 语句。除了CTE不能定义另一个CTE 以外,< 公用表表达式子查询语句> 的SELECT 语句必须满足与创建视图时相同的要求;
4.公用表表达式后面必须直接跟使用CTE的SQL语句,否则无效。
该语句的使用者必须对< 查询说明>中的每个表均具有SELECT权限。

举例说明
公用表表达式可以认为是在单个SELECT、INSERT、UPDATE、DELETE 或CREATE VIEW 语句的执行范围内定义的临时结果集。
例如创建一个表TEST1和表TEST2,并利用公用表表达式对它们进行连接运算。

SQL> CREATE TABLE TEST1(I INT);
executed successfully
used time: 17.257(ms). Execute id is 34224.
SQL> INSERT INTO TEST1 VALUES(1);
affect rows 1

used time: 1.008(ms). Execute id is 34226.
SQL> INSERT INTO TEST1 VALUES(2);
affect rows 1

used time: 0.712(ms). Execute id is 34227.
SQL> CREATE TABLE TEST2(J INT);
executed successfully
used time: 42.221(ms). Execute id is 34229.
SQL> INSERT INTO TEST2 VALUES(5);
affect rows 1

used time: 1.104(ms). Execute id is 34230.
SQL> INSERT INTO TEST2 VALUES(6);
affect rows 1

used time: 0.696(ms). Execute id is 34232.
SQL> INSERT INTO TEST2 VALUES(7);
affect rows 1

used time: 0.664(ms). Execute id is 34234.
SQL> WITH CTE1(K) AS(SELECT I FROM TEST1 WHERE I > 1),
2   CTE2(G) AS(SELECT J FROM TEST2 WHERE J > 5)
3   SELECT K, G FROM CTE1, CTE2;

LINEID     K           G          
---------- ----------- -----------
1          2           6
2          2           7

used time: 1.692(ms). Execute id is 34237.

例如利用公用表表达式将表TEST1中的记录插入到TEST2表中。

SQL> INSERT INTO TEST2 WITH CTE1 AS(SELECT * FROM TEST1)
2   SELECT * FROM CTE1;
affect rows 2

used time: 1.048(ms). Execute id is 34247.
SQL> SELECT * FROM TEST2;

LINEID     J          
---------- -----------
1          5
2          6
3          7
4          1
5          2

used time: 1.135(ms). Execute id is 34249.

私有IP丢失造成Oracle 12C RAC集群节点不能启动

某生产环境Oracle Linux 7.1,Oracle 12C RAC显示节点1的集群资源没有启动,信息如下:

[grid@cs2 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       cs2                      STABLE
ora.CRS.dg
               ONLINE  ONLINE       cs2                      STABLE
ora.DATA.dg
               ONLINE  ONLINE       cs2                      STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       cs2                      STABLE
ora.chad
               ONLINE  OFFLINE      cs2                      STABLE
ora.net1.network
               ONLINE  ONLINE       cs2                      STABLE
ora.ons
               ONLINE  OFFLINE      cs2                      STABLE
ora.proxy_advm
               OFFLINE OFFLINE      cs2                      STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       cs2                      STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       cs2                      STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       cs2                      STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       cs2                      169.254.255.92 88.88
                                                             .88.191,STABLE
ora.asm
      1        ONLINE  OFFLINE                               STABLE
      2        ONLINE  ONLINE       cs2                      Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cs.db
      1        ONLINE  OFFLINE                               STABLE
      2        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.cs1.vip
      1        ONLINE  INTERMEDIATE cs2                      FAILED OVER,STABLE
ora.cs2.vip
      1        ONLINE  ONLINE       cs2                      STABLE
ora.cvu
      1        ONLINE  ONLINE       cs2                      STABLE
ora.gns
      1        ONLINE  OFFLINE                               STABLE
ora.gns.vip
      1        ONLINE  OFFLINE                               STABLE
ora.mgmtdb
      1        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.qosmserver
      1        ONLINE  ONLINE       cs2                      STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       cs2                      STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       cs2                      STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       cs2                      STABLE
--------------------------------------------------------------------------------

如果查看节点1上的alert.log文件,信息如下:

[root@cs1 soft]# tail -100 /u01/app/grid/diag/crs/cs1/crs/trace/alert.log

2020-01-14 20:45:50.226 [CVUD(2366CRS-10051: CVU found following errors with Clusterware setup : PRVE-3191 :
(DESCRIPTION = (LOAD_BALANCE=on)  (ADDRESS = (PROTOCOL = TCP)(HOST = cs-cluster-scan.cs-cluster.jy.net)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = cs)))
Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
 

2020-02-17 14:49:28.563 [ORAROOTAGENT(15500)]CRS-5822: Agent '/u01/app/product/12.2.0/crs/bin/orarootagent_root' disconnected from server. Details at (:CRSAGF00117:) {0:5:63832} in /u01/app/grid/diag/crs/cs1/crs/trace/crsd_orarootagent_root.trc.
2020-02-17 14:49:28.584 [ORAAGENT(15466)]CRS-5822: Agent '/u01/app/product/12.2.0/crs/bin/oraagent_grid' disconnected from server. Details at (:CRSAGF00117:) {0:1:36} in /u01/app/grid/diag/crs/cs1/crs/trace/crsd_oraagent_grid.trc.
2020-02-17 14:49:29.607 [GPNPD(3293)]CRS-2329: GPNPD on node cs1 shut down. 
2020-02-17 14:49:30.123 [OSYSMOND(6077)]CRS-8504: Oracle Clusterware OSYSMOND process with operating system process ID 6077 is exiting
2020-02-17 14:49:31.377 [MDNSD(3254)]CRS-5602: mDNS service stopping by request.
2020-02-17 14:49:31.747 [MDNSD(3254)]CRS-8504: Oracle Clusterware MDNSD process with operating system process ID 3254 is exiting
2020-02-17 14:49:46.650 [OCTSSD(5883)]CRS-2405: The Cluster Time Synchronization Service on host cs1 is shutdown by user
2020-02-17 14:49:46.651 [OCTSSD(5883)]CRS-8504: Oracle Clusterware OCTSSD process with operating system process ID 5883 is exiting
2020-02-17 14:49:47.651 [OCSSD(3616)]CRS-1603: CSSD on node cs1 has been shut down.
2020-02-17 14:49:47.958 [OCSSD(3616)]CRS-1660: The CSS daemon shutdown has completed
2020-02-17 14:49:47.959 [OCSSD(3616)]CRS-8504: Oracle Clusterware OCSSD process with operating system process ID 3616 is exiting
2020-02-17 14:49:49.726 [ORAAGENT(3235)]CRS-5822: Agent '/u01/app/product/12.2.0/crs/bin/oraagent_grid' disconnected from server. Details at (:CRSAGF00117:) {0:9:818} in /u01/app/grid/diag/crs/cs1/crs/trace/ohasd_oraagent_grid.trc.
2020-02-17 14:49:49.727 [ORAROOTAGENT(3029)]CRS-5822: Agent '/u01/app/product/12.2.0/crs/bin/orarootagent_root' disconnected from server. Details at (:CRSAGF00117:) {0:1:21} in /u01/app/grid/diag/crs/cs1/crs/trace/ohasd_orarootagent_root.trc.

根据上面的错误信息提示查看crsd_oraagent_grid.trc跟踪文件:

[root@cs1 ~]# more /u01/app/grid/diag/crs/cs1/crs/trace/crsd_oraagent_grid.trc
Trace file /u01/app/grid/diag/crs/cs1/crs/trace/crsd_oraagent_grid.trc
Oracle Database 12c Clusterware Release 12.2.0.1.0 - Production Copyright 1996, 2016 Oracle. All rights reserved.

*** TRACE CONTINUED FROM FILE /u01/app/grid/diag/crs/cs1/crs/trace/crsd_oraagent_grid_910.trc ***

2020-02-17 14:39:22.129 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] LsnrAgent::regEndpoint 110 reset regEndPointDone:0
2020-02-17 14:39:22.129 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] generateEndPointStrings:generate endpoints
2020-02-17 14:39:22.129 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] VendorType=0
2020-02-17 14:39:22.129 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] VendorType=0
2020-02-17 14:39:22.129 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] VendorType=0
2020-02-17 14:39:22.129 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] VendorType=0
2020-02-17 14:39:22.129 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] generateEndPointStrings:No IP matching the subnet 88.88.88.0
2020-02-17 14:39:22.130 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] Ocr Context init default level 1814629536
2020-02-17 14:39:22.133 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] Agent::getDeploymentPlatformId return 
2020-02-17 14:39:22.133 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] LsnrAgent::generateEndPoints 000 entry {nonPriv
2020-02-17 14:39:22.133 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] LsnrAgent::generateEndPoints 000 entry { lsnrResName: endpAttr:TCP:1526 type:4
2020-02-17 14:39:22.133 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] LsnrAgent::generateEndPoints 040 Listener ResName:
2020-02-17 14:39:22.133 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] LsnrAgent::removeDuplicates 
2020-02-17 14:39:22.133 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] LsnrAgent::generateEndPoints 999 exit nonPriv}
2020-02-17 14:39:22.133 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] LsnrAgent::regEndpoint 200 endpStrings empty
2020-02-17 14:39:22.134 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] AsmLsnrAgent::init
2020-02-17 14:39:22.154 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] VendorType=0
2020-02-17 14:39:22.154 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] VendorType=0
2020-02-17 14:39:22.154 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] VendorType=0
2020-02-17 14:39:22.154 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] VendorType=0
2020-02-17 14:39:22.154 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] Cannot find an IP address matching the subnet 88.88.88.0
2020-02-17 14:39:22.154 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] Ocr Context init default level 1814629536
2020-02-17 14:39:22.157 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] Agent::getDeploymentPlatformId return 
2020-02-17 14:39:22.158 : CLSCEVT:2462013184: (:CLSCE0028:)clsce_unsubscribe 0x7f725c004510 successfully unsubscribed : 974833
2020-02-17 14:39:22.158 : USRTHRD:2462013184: {0:1:18} WorkerThread::removeWorker total workers: 2
2020-02-17 14:39:22.158 : USRTHRD:2462013184: {0:1:18} Worker::schedule adding worker 'ReconnSub-LsnrAgentSub-ASMNET1LSNR_ASM' to thread 'ReconnectingSubscribers'
2020-02-17 14:39:22.158 : USRTHRD:2462013184: {0:1:18} WorkerThread::addWorker total workers: 3
2020-02-17 14:39:22.158 :CLSDYNAM:2462013184: [ora.ASMNET1LSNR_ASM.lsnr]{0:1:18} [check] Skipping CSS Initialization for rebootless recovery
2020-02-17 14:39:22.159 : CLSCEVT:2478823168: clsce_subscribe 0x7f72600d06b0 filter='(^CRS_RESOURCE_PROFILE_CHANGE.*RESOURCE_CLASS='(scan_vip|vip)')|(^CRS_RESOURCE_PROFILE_CHANGE.*TYPE='ora\.network\.type')', flags=1, handler=0xc6ad0c, 
arg=0x7f726016de10

从上面的错误信息中可以找到Cannot find an IP address matching the subnet 88.88.88.0,这说明私有IP丢失了。

如是查看网络IP地址
节点2私有IP正常

[grid@cs2 ~]$ ifconfig -a
ens160: flags=4163  mtu 1500
        inet 10.13.13.191  netmask 255.255.255.0  broadcast 10.13.13.255
        inet6 fe80::250:56ff:fea0:92af  prefixlen 64  scopeid 0x20
        ether 00:50:56:a0:92:af  txqueuelen 1000  (Ethernet)
        RX packets 127560233  bytes 10186683662 (9.4 GiB)
        RX errors 0  dropped 353  overruns 0  frame 0
        TX packets 18954431  bytes 1554204413 (1.4 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160:1: flags=4163  mtu 1500
        inet 10.13.13.130  netmask 255.255.255.0  broadcast 10.13.13.255
        ether 00:50:56:a0:92:af  txqueuelen 1000  (Ethernet)

ens160:2: flags=4163  mtu 1500
        inet 10.13.13.147  netmask 255.255.255.0  broadcast 10.13.13.255
        ether 00:50:56:a0:92:af  txqueuelen 1000  (Ethernet)

ens160:3: flags=4163  mtu 1500
        inet 10.13.13.141  netmask 255.255.255.0  broadcast 10.13.13.255
        ether 00:50:56:a0:92:af  txqueuelen 1000  (Ethernet)

ens160:4: flags=4163  mtu 1500
        inet 10.13.13.138  netmask 255.255.255.0  broadcast 10.13.13.255
        ether 00:50:56:a0:92:af  txqueuelen 1000  (Ethernet)

ens160:5: flags=4163  mtu 1500
        inet 10.13.13.137  netmask 255.255.255.0  broadcast 10.13.13.255
        ether 00:50:56:a0:92:af  txqueuelen 1000  (Ethernet)

ens160:6: flags=4163  mtu 1500
        inet 10.13.13.136  netmask 255.255.255.0  broadcast 10.13.13.255
        ether 00:50:56:a0:92:af  txqueuelen 1000  (Ethernet)

ens192: flags=4163  mtu 1500
        inet 88.88.88.191  netmask 255.255.255.0  broadcast 88.88.88.255
        inet6 fe80::250:56ff:fea0:a05c  prefixlen 64  scopeid 0x20
        ether 00:50:56:a0:a0:5c  txqueuelen 1000  (Ethernet)
        RX packets 294716840  bytes 215309349272 (200.5 GiB)
        RX errors 0  dropped 355  overruns 0  frame 0
        TX packets 63703465  bytes 363393728693 (338.4 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens192:1: flags=4163  mtu 1500
        inet 169.254.255.92  netmask 255.255.0.0  broadcast 169.254.255.255
        ether 00:50:56:a0:a0:5c  txqueuelen 1000  (Ethernet)

lo: flags=73  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 0  (Local Loopback)
        RX packets 90029207  bytes 223833448058 (208.4 GiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 90029207  bytes 223833448058 (208.4 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0: flags=4099  mtu 1500
        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 52:54:00:d0:6e:0b  txqueuelen 0  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0-nic: flags=4098  mtu 1500
        ether 52:54:00:d0:6e:0b  txqueuelen 500  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0


[grid@cs2 ~]$ cat /etc/hosts

127.0.0.1      localhost
10.13.13.190  cs1
10.13.13.191  cs2

#10.13.13.192 cs1-vip.jy.net cs1-vip
#10.13.13.193 cs2-vip.jy.net cs2-vip

88.88.88.190   cs1-priv.jy.net cs1-priv
88.88.88.191   cs2-priv.jy.net cs2-priv


10.13.13.141 cs-cluster-scan 
10.13.13.142 cs-cluster-scan 
10.13.13.143 cs-cluster-scan 

节点1的私有IP丢失

[root@cs1 ~]# ifconfig -a
ens160: flags=4163  mtu 1500
        inet 10.13.13.190  netmask 255.255.255.0  broadcast 10.13.13.255
        inet6 fe80::250:56ff:fea0:4e69  prefixlen 64  scopeid 0x20
        inet6 2018::2  prefixlen 64  scopeid 0x0
        ether 00:50:56:a0:4e:69  txqueuelen 1000  (Ethernet)
        RX packets 53266410  bytes 3937179306 (3.6 GiB)
        RX errors 0  dropped 143  overruns 0  frame 0
        TX packets 2988621  bytes 284679938 (271.4 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens192: flags=4163  mtu 1500
        inet6 fe80::250:56ff:fea0:17dc  prefixlen 64  scopeid 0x20
        ether 00:50:56:a0:17:dc  txqueuelen 1000  (Ethernet)
        RX packets 70361859  bytes 5602168856 (5.2 GiB)
        RX errors 0  dropped 141  overruns 0  frame 0
        TX packets 24902418  bytes 195063342601 (181.6 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 0  (Local Loopback)
        RX packets 30235303  bytes 10958756943 (10.2 GiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 30235303  bytes 10958756943 (10.2 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0: flags=4099  mtu 1500
        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 52:54:00:d0:6e:0b  txqueuelen 0  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0-nic: flags=4098  mtu 1500
        ether 52:54:00:d0:6e:0b  txqueuelen 500  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0



设置私有IP

[root@cs1 network-scripts]# cat ifcfg-Ethernet_connection_ens192
TYPE=Ethernet
BOOTPROTO=none
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=no
NAME=ens192
UUID=8ec45d90-c1fb-4924-84a0-72f58fc9b82f
DEVICE=ens192
ONBOOT=yes
IPADDR=88.88.88.190
PREFIX=24
NETMASK=255.255.255.0

[root@cs1 network-scripts]# ifconfig -a
ens160: flags=4163  mtu 1500
        inet 10.138.130.190  netmask 255.255.255.0  broadcast 10.138.130.255
        inet6 fe80::250:56ff:fea0:4e69  prefixlen 64  scopeid 0x20
        inet6 2018::2  prefixlen 64  scopeid 0x0
        ether 00:50:56:a0:4e:69  txqueuelen 1000  (Ethernet)
        RX packets 53277081  bytes 3937945677 (3.6 GiB)
        RX errors 0  dropped 143  overruns 0  frame 0
        TX packets 2989099  bytes 284806540 (271.6 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens192: flags=4163  mtu 1500
        inet 88.88.88.190  netmask 255.255.255.0  broadcast 88.88.88.255
        inet6 fe80::250:56ff:fea0:17dc  prefixlen 64  scopeid 0x20
        ether 00:50:56:a0:17:dc  txqueuelen 1000  (Ethernet)
        RX packets 70371906  bytes 5602881459 (5.2 GiB)
        RX errors 0  dropped 141  overruns 0  frame 0
        TX packets 24902428  bytes 195063343317 (181.6 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 0  (Local Loopback)
        RX packets 30236949  bytes 10959017396 (10.2 GiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 30236949  bytes 10959017396 (10.2 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0: flags=4099  mtu 1500
        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 52:54:00:d0:6e:0b  txqueuelen 0  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0-nic: flags=4098  mtu 1500
        ether 52:54:00:d0:6e:0b  txqueuelen 500  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@cs1 ~]# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘cs2’
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘cs2’
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘cs2’
CRS-2673: Attempting to stop ‘ora.crf’ on ‘cs2’
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘cs2’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘cs2’
CRS-2673: Attempting to stop ‘ora.asm’ on ‘cs2’
CRS-2673: Attempting to stop ‘ora.drivers.acfs’ on ‘cs2’
CRS-2677: Stop of ‘ora.drivers.acfs’ on ‘cs2’ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘cs2’ succeeded
CRS-2677: Stop of ‘ora.crf’ on ‘cs2’ succeeded
CRS-2677: Stop of ‘ora.gpnpd’ on ‘cs2’ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘cs2’ succeeded
CRS-2677: Stop of ‘ora.mdnsd’ on ‘cs2’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘cs2’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘cs2’
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘cs2’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘cs2’
CRS-2677: Stop of ‘ora.cssd’ on ‘cs2’ succeeded
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘cs2’
CRS-2673: Attempting to stop ‘ora.driver.afd’ on ‘cs2’
CRS-2677: Stop of ‘ora.driver.afd’ on ‘cs2’ succeeded
CRS-2677: Stop of ‘ora.gipcd’ on ‘cs2’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘cs2’ has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@cs1 ~]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

手动启动节点1的集群软件

[root@cs1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       cs1                      STABLE
               ONLINE  ONLINE       cs2                      STABLE
ora.CRS.dg
               ONLINE  ONLINE       cs1                      STABLE
               ONLINE  ONLINE       cs2                      STABLE
ora.DATA.dg
               ONLINE  ONLINE       cs1                      STABLE
               ONLINE  ONLINE       cs2                      STABLE
ora.DN.dg
               OFFLINE OFFLINE      cs1                      STABLE
               OFFLINE OFFLINE      cs2                      STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       cs1                      STABLE
               ONLINE  ONLINE       cs2                      STABLE
ora.chad
               ONLINE  ONLINE       cs1                      STABLE
               ONLINE  ONLINE       cs2                      STABLE
ora.net1.network
               ONLINE  ONLINE       cs1                      STABLE
               ONLINE  ONLINE       cs2                      STABLE
ora.ons
               ONLINE  ONLINE       cs1                      STABLE
               ONLINE  ONLINE       cs2                      STABLE
ora.proxy_advm
               OFFLINE OFFLINE      cs1                      STABLE
               OFFLINE OFFLINE      cs2                      STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       cs1                      STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       cs2                      STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       cs1                      STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       cs1                      169.254.207.24 88.88
                                                             .88.190,STABLE
ora.asm
      1        ONLINE  ONLINE       cs1                      Started,STABLE
      2        ONLINE  ONLINE       cs2                      Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cs.db
      1        ONLINE  ONLINE       cs1                      Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
      2        ONLINE  ONLINE       cs2                      Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
ora.cs1.vip
      1        ONLINE  ONLINE       cs1                      STABLE
ora.cs2.vip
      1        ONLINE  ONLINE       cs2                      STABLE
ora.cvu
      1        ONLINE  ONLINE       cs1                      STABLE
ora.gns
      1        ONLINE  ONLINE       cs2                      STABLE
ora.gns.vip
      1        ONLINE  ONLINE       cs2                      STABLE
ora.mgmtdb
      1        ONLINE  OFFLINE      cs1                      Open,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       cs1                      STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       cs1                      STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       cs2                      STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       cs1                      STABLE
--------------------------------------------------------------------------------

到此恢复正常状态。

DM7 SQL域

管理SQL域
为了支持SQL标准中的域对象定义与使用,DM支持DOMAIN的创建、删除以及授权DDL语句,并支持在表定义中使用DOMAIN。域(DOMAIN)是一个可允许值的集合。域在模式中定义,并由< 域名>标识。域是用来约束由各种操作存储于基表中某列的有效值集。域定义说明一种数据类型,它也能进一步说明约束域的有效值的< 域约束>,还可说明一个< 缺省子句>,该子句规定没有显式指定值时所要用的值或列的缺省值。

创建DOMAIN
CREATE DOMAIN 创建一个新的数据域。定义域的用户成为其所有者。DOMAIN为模式类型对象,其名称在模式内唯一。

举例说明

SQL> CREATE DOMAIN DA INT CHECK (VALUE < 100);
executed successfully
used time: 11.754(ms). Execute id is 148.

SQL> select name,id from sysobjects where name='DA';

LINEID     NAME ID         
---------- ---- -----------
1          DA   419430401

used time: 0.945(ms). Execute id is 149.

使用 DOMAIN
在表定义语句中,支持为表列声明使用域。如果列声明的类型定义使用域引用,则此列定义直接继承域中的数据类型、缺省值以及CHECK 约束。如果列定义使用域,然后又自己定义了缺省值,则最终使用自己定义的缺省值。用户可以使用自己的域。如果要使用其它用户的域,则必须被授予了该域的USAGE 权限。DBA 角色默认拥有此权限。
例如在 T 表中使用上面创建的域DA。

SQL> CREATE TABLE T(ID DA);
executed successfully
used time: 12.655(ms). Execute id is 149.

SQL> select name,id from sysobjects where name='T';

LINEID     NAME ID         
---------- ---- -----------
1          T    1540

used time: 1.255(ms). Execute id is 157.
SQL> select * from syscolumns where id=1540;

LINEID     NAME ID          COLID       TYPE$   LENGTH$     SCALE       NULLABLE$ DEFVAL INFO1       INFO2      
---------- ---- ----------- ----------- ------- ----------- ----------- --------- ------ ----------- -----------
1          ID   1540        0           INTEGER 4           0           Y         NULL   0           0

used time: 1.249(ms). Execute id is 158.

SQL> insert into t values(1);   
affect rows 1

used time: 1.180(ms). Execute id is 160.
SQL> insert into t values(100);
insert into t values(100);
[-6604]:Violate check constraint of [CONS134218952].
used time: 1.176(ms). Execute id is 0.

列定义虽然使用了域后,其SYSCOLUMNS 系统表中类型相关字段记录域定义的数据类型。也就是说,从SYSCOLUMNS 系统表中不会表现出对域的引用。使用某个域的用户必须具有该域的USAGE DOMAIN 或USAGE ANY DOMAIN 权限。

删除DOMAIN
删除一个用户定义的域。用户可以删除自己拥有的域,具有DROP ANY TABLE系统权限的用户则可以删除任意模式下的域。RESTRICT表示仅当DOMAIN未被表列使用时才可以被删除; CASCADE表示级联删除。
例如:

SQL> DROP DOMAIN DA CASCADE;
executed successfully
used time: 29.834(ms). Execute id is 163.

DM7 数组索引

数组索引指在一个只包含单个数组成员的对象列上创建的索引。

数组索引定义语句
语法格式
CREATE ARRAY INDEX < 索引名> ON [< 模式名>.] < 表名> (< 索引列定义>)
使用说明
1) 暂不支持在水平分区表上创建数组索引;
2) 暂时不支持在有数组索引表上进行批量装载(数组索引失效的例外);
3) 支持创建数组索引的对象只能包含数组一个成员。数组可以是DM静态数组、动态数组或者ORACLE兼容的嵌套表或VARRAY;
4) 数组项类型只能是可比较的标量类型,不支持复合类型、对象类型或大字段类型;
5) 临时表、垂直分区表不支持;
6) 数组索引不支持改名;
7) 数组索引列不支持改名;
8) 数组索引只能是单索引,不能为组合索引;
9) 不支持空值的检索
10) MPP环境不支持数组索引。

数组索引修改语句
数组索引修改语句与普通索引用法相同,请参考3.7节。与普通索引不同的是,数组索引不支持NOSORT和ONLINE用法。

数组索引使用
使用数组索引进行查询,必须使用谓词CONTAINS。
语法格式
CONTAINS(< 索引列名>,)
或者
CONTAINS(< 索引列名>,arr_var_exp)
参数
val:必须为与对象列数组项相同或可转换的标量类型表达式。
arr_var_exp:必须为数组类型(DM静态数组、动态数组或者ORACLE兼容的嵌套表或VARRAY),其数组项类型必须与对象列数组项类型相同或可转换。

举例说明

SQL> CREATE TYPE ARR_NUM1 IS VARRAY(1024) OF NUMBER; --VARRAY数组
2   /
executed successfully
used time: 17.012(ms). Execute id is 81.
SQL> CREATE TYPE ARR_NUM2 IS TABLE OF NUMBER; --嵌套表
2   /
executed successfully
used time: 11.795(ms). Execute id is 82.
SQL> CREATE TYPE ARR_NUM3 IS ARRAY NUMBER[]; --动态
2   /
executed successfully
used time: 14.784(ms). Execute id is 83.
SQL> CREATE TYPE ARR_NUM4 IS ARRAY NUMBER[3]; --静态
2   /
executed successfully
used time: 14.017(ms). Execute id is 84.
SQL> CREATE CLASS CLS1 AS V ARR_NUM1;END;
2   /
executed successfully
used time: 12.893(ms). Execute id is 85.

SQL> CREATE TABLE TEST (C1 CLS1);
executed successfully
used time: 16.078(ms). Execute id is 87.
SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(1,2,3)));
affect rows 1

used time: 1.537(ms). Execute id is 88.
SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(1,2)));
affect rows 1

used time: 1.074(ms). Execute id is 89.
SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(2,1)));
affect rows 1

used time: 1.149(ms). Execute id is 90.
SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(1,5)));
affect rows 1

used time: 1.110(ms). Execute id is 91.
SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(2,4)));
affect rows 1

used time: 1.068(ms). Execute id is 92.
SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(4,5,6)));
affect rows 1

used time: 1.152(ms). Execute id is 93.
SQL> commit;
executed successfully
used time: 17.231(ms). Execute id is 94.
SQL> select * from test;

LINEID     C1
---------- ------------------------
1          JY.CLS1(ARR_NUM1(1,2,3))
2          JY.CLS1(ARR_NUM1(1,2))
3          JY.CLS1(ARR_NUM1(2,1))
4          JY.CLS1(ARR_NUM1(1,5))
5          JY.CLS1(ARR_NUM1(2,4))
6          JY.CLS1(ARR_NUM1(4,5,6))

6 rows got

used time: 1.063(ms). Execute id is 95.
SQL> CREATE ARRAY INDEX IDX ON TEST(C1); --创建数组索引
2   /
executed successfully
used time: 20.879(ms). Execute id is 97.
SQL> SELECT * FROM TEST WHERE CONTAINS(C1,1,2,3); --使用数组索引查询
2   /

LINEID     C1
---------- ------------------------
1          JY.CLS1(ARR_NUM1(1,2,3))
2          JY.CLS1(ARR_NUM1(1,2))
3          JY.CLS1(ARR_NUM1(2,1))
4          JY.CLS1(ARR_NUM1(1,5))
5          JY.CLS1(ARR_NUM1(2,4))

used time: 3.400(ms). Execute id is 98.

SQL> --嵌套表
2   DECLARE
3   X ARR_NUM2;
4   BEGIN
5   X := ARR_NUM2();
6   X.EXTEND(3);
7   X(1) := 1;
8   X(2) := 2;
9   X(3) := 3;
10  SELECT * FROM TEST WHERE CONTAINS(C1,X);
11  END;
12  /

LINEID     C1
---------- ------------------------
1          JY.CLS1(ARR_NUM1(1,2,3))
2          JY.CLS1(ARR_NUM1(1,5))
3          JY.CLS1(ARR_NUM1(2,1))
4          JY.CLS1(ARR_NUM1(1,2))
5          JY.CLS1(ARR_NUM1(2,4))

used time: 7.179(ms). Execute id is 100.
SQL> --动态数组
2   DECLARE
3   X ARR_NUM3;
4   BEGIN
5   X := NEW NUMBER [3];
6   X[1]:= 1;
7   X[2]:= 2;
8   X[3]:= 3;
9   SELECT * FROM TEST WHERE CONTAINS(C1,X);
10  END;
11  /

LINEID     C1
---------- ------------------------
1          JY.CLS1(ARR_NUM1(1,2,3))
2          JY.CLS1(ARR_NUM1(1,5))
3          JY.CLS1(ARR_NUM1(2,1))
4          JY.CLS1(ARR_NUM1(1,2))
5          JY.CLS1(ARR_NUM1(2,4))

used time: 6.016(ms). Execute id is 102.

SQL> --静态数组
2   DECLARE
3   X ARR_NUM4;
4   BEGIN
5   X[1]:= 1;
6   X[2]:= 2;
7   X[3]:= 3;
8   SELECT * FROM TEST WHERE CONTAINS(C1,X);
9   END;
10  /

LINEID     C1
---------- ------------------------
1          JY.CLS1(ARR_NUM1(1,2,3))
2          JY.CLS1(ARR_NUM1(1,5))
3          JY.CLS1(ARR_NUM1(2,1))
4          JY.CLS1(ARR_NUM1(1,2))
5          JY.CLS1(ARR_NUM1(2,4))

used time: 5.688(ms). Execute id is 104.

数组索引删除语句
数组索引删除语句与普通索引用法相同

SQL> drop index idx;
executed successfully
used time: 402.153(ms). Execute id is 106.
SQL> SELECT * FROM TEST WHERE CONTAINS(C1,1,2,3);
SELECT * FROM TEST WHERE CONTAINS(C1,1,2,3);
[-3252]:Error in line: 1
The array index is not created on the column[C1].
used time: 1.016(ms). Execute id is 0.

PLSQL通过Oracle 11g客户端连接Oracle 12c服务器错误 ORA-28040

PLSQL通过Oracle 11g客户端连接Oracle 12c服务器错误 ORA-28040
环境描述:
oracle服务器端版本:oracle 12.2.0.1.0
oracle客户端版本:oracle 11.2.0.1.0
PLSQL是11.4

因为PLSQL连接数据库也是要通过Oracle客户端,那么使用11g客户端访问oracle 12c应该也会得到如下错误:

C:\Users\Administrator>sqlplus sys/abcd@shardcat as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:22:07 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-28040: No matching authentication protocol

查看关于错误的详细描述:

[oracle@shard1 admin]$ oerr ora 28040
28040, 0000, "No matching authentication protocol"
// *Cause:  There was no acceptable authentication protocol for
//          either client or server.
// *Action: The administrator should set the values of the
//          SQLNET.ALLOWED_LOGON_VERSION_SERVER and
//          SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the
//          client and on the server, to values that match the minimum
//          version software supported in the system.
//          This error is also raised when the client is authenticating to
//          a user account which was created without a verifier suitable for
//          the client software version. In this situation, that account's
//          password must be reset, in order for the required verifier to
//          be generated and allow authentication to proceed successfully.

在服务器端查看表:dba_users,PASSWORD_VERSIONS 列是:11G 12C

SQL> select username, account_status,password_versions from dba_users where account_status='OPEN';

USERNAME                       ACCOUNT_STATUS                                                   PASSWORD_VERSIONS
------------------------------ ---------------------------------------------------------------- ----------------------------------
SYS                            OPEN                                                             11G 12C
SYSTEM                         OPEN                                                             11G 12C
GSMCATUSER                     OPEN                                                             11G 12C
JY                             OPEN                                                             11G 12C
MYGDSADMIN                     OPEN                                                             11G 12C
APP_SCHEMA                     OPEN                                                             11G 12C

6 rows selected.

服务器端:修改 sqlnet.ora 配置:(配置修改后,不需要重启oracle服务器)我这里是新创建的sqlnet.ora,因为原来没有创建

[oracle@shard1 admin]$ ls -lrt
总用量 12
-rw-r--r--. 1 oracle oinstall 1441 8月  28 2015 shrept.lst
drwxr-xr-x. 2 oracle oinstall   61 10月 12 2017 samples
-rw-r-----. 1 oracle oinstall  960 1月  18 2018 tnsnames.ora
-rw-r--r--  1 oracle oinstall  504 2月  17 12:58 listener.ora
[oracle@shard1 admin]$ vi sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11

客户端:再次尝试连接,提示用户名密码错误:

C:\Users\Administrator>sqlplus sys/abcd@shardcat as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:16:06 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

修改服务器端 sqlnet.ora 后,需要重新登录sqlplus,再修改用户密码,否则修改用户密码后,标记的密码版本仍然为11G 12C;重新登录sqlplus,修改scott用户密码,并查看 PASSWORD_VERSIONS,多了一个 10G

[oracle@shard1 ~]$ sqlplus sys/abcd@shardcat as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 17 13:07:28 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter user sys identified by "abcd";

User altered.


SQL> select username, account_status,password_versions from dba_users where account_status='OPEN';

USERNAME                       ACCOUNT_STATUS                                                   PASSWORD_VERSIONS
------------------------------ ---------------------------------------------------------------- ----------------------------------
SYS                            OPEN                                                             10G 11G 12C
SYSTEM                         OPEN                                                             11G 12C
GSMCATUSER                     OPEN                                                             11G 12C
JY                             OPEN                                                             11G 12C
MYGDSADMIN                     OPEN                                                             11G 12C
APP_SCHEMA                     OPEN                                                             11G 12C

6 rows selected.

客户端:再次尝试登录oracle 12c,成功:

C:\Users\Administrator>sqlplus sys/abcd@shardcat as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:22:39 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

总结:oracle11g 客户端连接 oracle 12c服务器,需要在服务器端配置 sqlnet.ora,并重新修改用户密码。

DM7 外部表

DM7 外部表
需指定如下信息:
1. 表名、表所属的模式名;
2. 列定义;
3. 控制文件路径。
语法格式
CREATE EXTERNAL TABLE < 表名定义> < 表结构定义>;
< 表名定义> ::=[< 模式名>.]< 表名>
< 表结构定义> ::= (< 列定义> {,< 列定义>})
< 列定义> ::= < 列名> < 数据类型>
FROM子句 = FROM子句1 | FROM子句2 | FROM子句3| FROM子句4
FROM子句1 ::= FROM ‘< 控制文件路径>‘
FROM子句2 ::= FROM DATAFILE ‘< 数据文件路径>‘ [< 数据文件参数列表>]
FROM子句3 ::= FROM < 控制文件目录> < 控制文件选项>
FROM子句4 ::= FROM DATAFILE < 数据文件目录> < 数据文件选项> [< 数据文件参数列表>]
< 数据文件参数列表> ::= PARMS(< 参数选项> {,< 参数选项>})
< 参数选项> ::= [FIELDS DELIMITED BY < 表达式>] | [RECORDS DELIMITED BY < 表达式>]|[ ERRORS ]|[BADFILE ‘< 数据文件路径>‘]| |[LOG < 日志文件路径>]|[NULL_STR ]|[SKIP < 跳过行数>]|[CHARACTER_CODE < 文件字符集>]
< 控制文件目录> ::= DEFAULT DIRECTORY < 控制文件目录名>
< 控制文件选项> ::= LOCATION (‘< 控制文件名>‘)
< 数据文件目录> ::= DEFAULT DIRECTORY < 数据文件目录名>
< 数据文件选项> ::= LOCATION (‘< 数据文件名>‘)

参数
1. < 模式名> 指明该表属于哪个模式,缺省为当前模式;
2. < 表名> 指明被创建的外部基表名;
3. < 列名> 指明基表中的列名;
4. < 数据类型> 指明列的数据类型,暂不支持多媒体类型;
5. < 控制文件路径> 指明使用的控制文件的路径的字符串;
6. < 数据文件路径> 指明使用的数据文件路径的字符串;
7. < 参数选项> FIELDS表示列分隔符;RECORDS表示行分隔符;ERRORS表示忽略外部表数据转换中出现错误的行数,取值范围为大于0的正整
数,缺省为0,表示不忽略错误。此处ERRORS 和控制文件中的ERRORS写一个就行,如果都写,以控制文件中的为主;LOG表示日志文件路径,
默认日志文件名为fldr.log;NULL_STR指定数据文件中NULL值的表示字符串,默认忽略此参数;SKIP指定跳过数据文件起始的逻辑行数,默
认为0;CHARACTER_CODE指定数据文件中数据的编码格式,默认为GBK,可选项有GBK,UTF-8,SINGLE_BYTE和EUC-KR;
8. < 表达式> 字符串或十六进制串类型表达式,列分隔符只允许字符串类型;
9. < 控制文件目录名> 指数据库对象目录的名称。
图例



语句功能
供DBA或具有CREATE_TABLE权限的用户定义外部基表。MPP环境下不支持创建外部表。
使用说明
1. < 表名>指定了所要建立的外部基表名。如果< 模式名>缺省,则缺省为当前模式。表名需要是合法的标识符。且满足SQL语法要求;
2. 外部表的表名最大长度为128个字符;
3. 所建外部基表至少要包含一个< 列名>指定的列,在一个外部基表中,各< 列名>不得相同。一张外部基表中至多可以包含2048列;
4. 外部基表不能存在大字段列;
5. 外部基表不能存在任何约束条件;
6. 外部基表不能为临时表,不能建立分区;
7. 外部基表上不能建立任何索引;
8. 外部基表是只读的,不存在表锁,不允许任何针对外部表的增删改数据操作,不允许truncate外部表操作;
9. 控制文件路径,以及数据文件路径建议采用绝对路径;
10. 控制文件的格式为:

[OPTIONS(
errors=
)]
LOAD [DATA]
INFILE [LIST] |
INTO TABLE tablename
FIELDS 

其中:
OPTIONS选项:可选部分,目前OPTIONS中仅支持errors选项,用于指定忽略数据转换出现错误的行数;此处errors 和< 参数选项>中的errors写一个就行,如果都写,以此处控制文件中的为主。 :指明使用的数据文件列表;
tablename:指明表名,创建外部表时,表名可以与此不同;
:指明同一行中各个列的分隔符;
11. 数据文件中一行数据必须以回车结束;
12. 外部表支持查询ROWID、USER和UID伪列,不支持查询TRXID伪列。

举例说明
例1 指定操作系统的一个文本文件作为数据文件,编写控制文件及建表语句。数据文件(/home/dmdba/data.txt),数据如下:

a|abc|varchar_data|12.34|12.34|12.34|12.34|0|1|1|1234|1234|1234|100|11|1234|1|1|14.2|12.1|12.1|1999-10-01|9:10:21|2002-12-12|15

控制文件(/home/dmdba/ctrl.txt)如下:

load data
infile '/home/dmdba/data.txt'
into table ext
fields '|'

建表:

SQL> create external table ext (
2   l_char char(1),
3   l_character character(3),
4   l_varchar varchar(20),
5   l_numeric numeric(6,2),
6   l_decimal decimal(6,2),
7   l_dec dec(6,2),
8   l_money decimal(19,4),
9   l_bit bit,
10  l_bool bit,
11  l_boolean bit,
12  l_integer integer,
13  l_int int,
14  l_bigint bigint,
15  l_tinyint tinyint,
16  l_byte byte,
17  l_smallint smallint,
18  l_binary binary,
19  l_varbinary varbinary,
20  l_float float,
21  l_double double,
22  l_real real,
23  l_date date,
24  l_time time,
25  l_timestamp timestamp,
26  l_interval interval year
27  )from '/home/dmdba/ctrl.txt';
executed successfully
used time: 62.123(ms). Execute id is 312.

系统执行建表语句后,就在数据库中建立了相应的外部基表。查询ext表:

SQL> select * from ext;

LINEID     L_CHAR L_CHARACTER L_VARCHAR    L_NUMERIC L_DECIMAL L_DEC L_MONEY L_BIT L_BOOL L_BOOLEAN L_INTEGER   L_INT       L_BIGINT             L_TINYINT   L_BYTE      L_SMALLINT  L_BINARY L_VARBINARY L_FLOAT                   L_DOUBLE                  L_REAL             L_DATE     L_TIME          L_TIMESTAMP                 L_INTERVAL           
---------- ------ ----------- ------------ --------- --------- ----- ------- ----- ------ --------- ----------- ----------- -------------------- ----------- ----------- ----------- -------- ----------- ------------------------- ------------------------- ------------------ ---------- --------------- --------------------------- ---------------------
1          a      abc         varchar_data 12.34     12.34     12.34 12.3400 0     1      1         1234        1234        1234                 100         11          1234        0x01     0x01        1.420000000000000E+01     1.210000000000000E+01     1.2100000E+01      1999-10-01 09:10:21        2002-12-12 00:00:00.000000  INTERVAL '15' YEAR(2)

used time: 7.248(ms). Execute id is 314.

例 2 指定操作系统的一个文本文件作为数据文件(/home/dmdba/data1.txt),数据如下:

10|9|7
4|3|2

建表:

SQL> create external table ext_2(c1 int,c2 int,c3 int) from datafile '/home/dmdba/data1.txt' parms(fields delimited by '|');
executed successfully
used time: 11.008(ms). Execute id is 331.

查询结果:

SQL> select * from ext_2;

LINEID     C1          C2          C3         
---------- ----------- ----------- -----------
1          10          9           7
2          4           3           2

used time: 8.222(ms). Execute id is 332.

DM7 AWR报告

DM有类似于Oracle的AWR报告,这里将给大家介绍如何生成数据库快照以及AWR报告。本文演示环境:DM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT,启用DM快照需要调用DBMS_WORKLOAD_REPOSITORY包(DM MPP环境下不支持DBMS_WORKLOAD_REPOSITORY包)
1.创建DBMS_WORKLOAD_REPOSITORY系统包。

SQL> call SP_INIT_AWR_SYS(1);
DMSQL executed successfully
used time: 647.666(ms). Execute id is 254.

2.启用状态检测。

SQL> SELECT SF_CHECK_AWR_SYS;

LINEID     SF_CHECK_AWR_SYS
---------- ----------------
1          1

used time: 5.590(ms). Execute id is 255.

3.下面语句设置间隔为60分钟,也可以是其他值:

SQL> CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(60);
DMSQL executed successfully
used time: 18.797(ms). Execute id is 256.

4.手动创建快照:

SQL> call DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
DMSQL executed successfully
used time: 61.528(ms). Execute id is 258.

等待几分钟后再次执行

SQL> call DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
DMSQL executed successfully
used time: 49.152(ms). Execute id is 259.

这里我们可以多执行几遍试试,创建几个不同的快照。

5.查看创建的快照信息,包括快照id:

SQL> SELECT * FROM SYS.WRM$_SNAPSHOT;

LINEID     SNAP_ID     DBID        INSTANCE_NUMBER STARTUP_TIME                BEGIN_INTERVAL_TIME         END_INTERVAL_TIME           SNAP_LEVEL 
---------- ----------- ----------- --------------- --------------------------- --------------------------- --------------------------- -----------
1          1           NULL        1               2020-02-04 18:25:32.000000  NULL                        2020-02-04 18:28:35.586240  1
2          2           NULL        1               2020-02-04 18:25:32.000000  NULL                        2020-02-04 18:31:21.207610  1

如果此时我们想知道,对应各个时点数据库的运行状况,我们可以利用DBMS_WORKLOAD_REPOSITORY包中的AWR_REPORT_HTML方法来生成HTML格式的AWR报表。
6.生成 snapshot 的 id 在 1-2 范围内的 AWR 分析报告的带 html 格式的内容。

SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2));

7.把snapshot的id在1-2范围内的AWR分析报告生成到/home/dmdba目录下的awr1_2.html文件内。

SQL> call sys.awr_report_html(1,2,'/home/dmdba','awr1_2.html');
DMSQL executed successfully
used time: 317.395(ms). Execute id is 260.

生成的报表格式如下



从AWR报告来说,熟悉Oracle的朋友基本上可以无缝衔接到DM数据库

Oracle 12C Formatted Block Dump

Oracle 12C Formatted Block Dump
.可以从RDBMS中dump数据块
.在用户dump目录中会生成一个跟踪文件包含dump信息

对7号文件中的135号数据块执行dump

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/shardcat/shardcat/trace/shardcat_ora_13182.trc
SQL> alter system dump datafile 7 block 135;

System altered.

对于任何数据块dump都有一个数据块头信息。它决定了剩余dump的格式。在任何Oracle块dump的头部应该可以看到块头信息:
Rdba:数据块的相对DBA
Scn:SCN序列号
Seq:序列号:
SEQ->0 /* non-logged changes – do not advance seq# */
SEQ->(UB1MAXVAL-1) /* maximum possible sequence number */
SEQ->(UB1MAXVAL) /* seq# to indicate a block is corrupt, equal to FF */

Flg: Flag(在kcbh.h中定义)
Tail:一致性数据用来验证数据块的开始与结束是否是相同版本(它由SCNBase的低阶两字节加上数据块类型加上SCN的序列号)
Frmt:数据块格式:在Oracle 12c中是2
Chkval数据块的选项检查值:如果db_block_checksum=true
Type:数据块类型(在kcb.h中定义)
Rdba:相对数据块地址
Seg/Obj:Seg/Obj ID dump输出的Seg/Obj为0x1af27

SQL> select to_number('1af27','xxxxxx') from dual;

TO_NUMBER('1AF27','XXXXXX')
---------------------------
                     110375

SQL> select object_id,object_name from dba_objects where owner='JY' and object_name='T1';

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
    110375 T1

Csc:最近数据块清除
itc: itl插槽数量
Flg: E
Typ: 1=DATA;2=INDEX
Itl:相关事务列表索引(ITLs由initrans和maxtrans来决定)
Xid:事务ID(UndoSeg.Slot.Wrap)
Uba:Undo段地址(UndoDBA.SeqNo.RecordNo)
Flg:C=Committed;U=Commit Upper Bound;T=Active at CSC;B=Rollback of this UBA gives before image of the ITL.
Lck:这个事务所影响的行数
Scn/Fsc:Scn=提交事务的SCN号;Fsc=Free space credit(bytes)
例如:

*** 2020-02-07T14:35:53.082746+08:00
Start dump data blocks tsn: 4 file#:7 minblk 135 maxblk 135
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=4 rdba=29360263
Block dump from disk:
buffer tsn: 4 rdba: 0x01c00087 (7/135)
scn: 0x1e1174a seq: 0x09 flg: 0x06 tail: 0x174a0609
frmt: 0x02 chkval: 0xba74 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
......省略......

Block header dump:  0x01c00087
 Object id on Block? Y
 seg/obj: 0x1af27  csc:  0x0000000001e11748  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1c00080 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.003.00006d30  0x010001c2.2499.09  --U-    8  fsc 0x0000.01e1174a
0x02   0x0008.01b.00003a9a  0x010002b5.114f.20  C---    0  scn  0x0000000001286184
bdba: 0x01c00087

Tsiz:总的数据区域大小

SQL> select to_number('1f98','xxxxxx') from dual;

TO_NUMBER('1F98','XXXXXX')
--------------------------
                      8088

Hsiz:数据块头大小

SQL> select to_number('22','xxxxxx') from dual;

TO_NUMBER('22','XXXXXX')
------------------------
                      34

Pbl:持有数据块的缓存指针
Flag:N=pctfree hit(clusters);F=do not put on free list;K=flushable cluster keys
Ntab:表的数量(在clusters中大于1)
Nrow:行记录数
Frre:第一个可用行索引项;-1=没有添加
Fsbo:可用空间开始偏移量
Fseo:可用空间结束偏移量
Avsp:数据块中可用空间
Tosp:当所有事务提交后总的可用空间
Nrow:第一个表的行数
例如:

data_block_dump,data header at 0x7f799676f064
===============
tsiz: 0x1f98
hsiz: 0x22
pbl: 0x7f799676f064
     76543210
flag=--------
ntab=1
nrow=8
frre=-1
fsbo=0x22
fseo=0x1efa
avsp=0x1f29
tosp=0x1f2b
0xe:pti[0]      nrow=8  offs=0
0x12:pri[0]     offs=0x1f47
0x14:pri[1]     offs=0x1f3d
0x16:pri[2]     offs=0x1f33
0x18:pri[3]     offs=0x1f29
0x1a:pri[4]     offs=0x1f1f
0x1c:pri[5]     offs=0x1f15
0x1e:pri[6]     offs=0x1f08
0x20:pri[7]     offs=0x1efa

Oracle Formatted Block Dump: Data Layer
Tab: tab 0,row 0,offset
Cc:在这个行片段中的列数
Lb:锁字节:这个行被锁定的ITL条目
Fb:Flag字节
H=行片段头;K=集簇键;C=集簇表成员;D=删除的行记录;F=第一个数据片;L=最后数据片;P=紧接前一个数据片的第一个列
N=紧接下一个数据片的最后一个列
Tl:行大小(字节数加上数据)
Col:列数据
例如:

block_row_dump:
tab 0, row 0, @0x1f47
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 3]  6d 5f 41
tab 0, row 1, @0x1f3d
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 3]  6d 5f 42
tab 0, row 2, @0x1f33
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [ 3]  6d 5f 43
tab 0, row 3, @0x1f29
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 05
col  1: [ 3]  6d 5f 44
tab 0, row 4, @0x1f1f
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 06
col  1: [ 3]  6d 5f 45
tab 0, row 5, @0x1f15
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 07
col  1: [ 3]  6d 5f 46
tab 0, row 6, @0x1f08
tl: 13 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 08
col  1: [ 6]  6d 5f 4a 59 48 59
tab 0, row 7, @0x1efa
tl: 14 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 09
col  1: [ 7]  6d 5f 4a 59 59 59 4c
end_of_block_dump
End dump data blocks tsn: 4 file#: 7 minblk 135 maxblk 135