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,并重新修改用户密码。

Proudly powered by WordPress | Indrajeet by Sus Hill.