分类目录归档：ORACLE 12C

Oracle 12c active dataguard switchover

发表于 2017 年 6 月 25 日 由 惜分飞

从12.1开始adg的切换发生了一些改变,直接使用alter database switchover to [target standby db_unique_name] verify; alter database switchover to [target standby db_unique_name]; 即可完成切换,以下是一次生产环境的具体操作步骤
主库操作

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> alter database switchover to xifenfei verify;

数据库已更改。

--alert日志
Sun Jun 25 09:07:08 2017
diag_adl:SWITCHOVER VERIFY: Send VERIFY request to switchover target xifenfei
diag_adl:SWITCHOVER VERIFY COMPLETE


SQL> alter database switchover to xifenfei;

数据库已更改。

--alert日志
Sun Jun 25 09:07:46 2017
diag_adl:Starting switchover [Process ID: 37024]
Sun Jun 25 09:07:46 2017
diag_adl:ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 37024] (xifenfei)
diag_adl:Waiting for target standby to receive all redo
Sun Jun 25 09:07:46 2017
diag_adl:Waiting for all non-current ORLs to be archived...
Sun Jun 25 09:07:46 2017
diag_adl:All non-current ORLs have been archived.
Sun Jun 25 09:07:46 2017
diag_adl:Waiting for all FAL entries to be archived...
Sun Jun 25 09:07:46 2017
diag_adl:All FAL entries have been archived.
Sun Jun 25 09:07:46 2017
diag_adl:Waiting for dest_id 2 to become synchronized...
Sun Jun 25 09:07:47 2017
diag_adl:Active, synchronized Physical Standby switchover target has been identified
diag_adl:Preventing updates and queries at the Primary
diag_adl:Generating and shipping final logs to target standby
diag_adl:Switchover End-Of-Redo Log thread 1 sequence 96534 has been fixed
diag_adl:Switchover: Primary highest seen SCN set to 0x3.0x109d7502
diag_adl:ARCH: Noswitch archival of thread 1, sequence 96534
diag_adl:ARCH: End-Of-Redo Branch archival of thread 1 sequence 96534
diag_adl:ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
diag_adl:ARCH: Standby redo logfile selected for thread 1 sequence 96534 for destination LOG_ARCHIVE_DEST_2
diag_adl:ARCH: Archiving is disabled due to current logfile archival
diag_adl:Primary will check for some target standby to have received all redo
diag_adl:Waiting for target standby to apply all redo
diag_adl:Backup controlfile written to trace file
            /u01/app/oracle/diag/rdbms/xifenfeildg/xifenfei/trace/xifenfei_ora_37024.trc
diag_adl:Converting the primary database to a new standby database
diag_adl:Clearing standby activation ID 612004791 (0x247a73b7)
diag_adl:The primary database controlfile was created using the
diag_adl:'MAXLOGFILES 16' clause.
diag_adl:There is space for up to 11 standby redo logfiles
diag_adl:Use the following SQL commands on the standby database to create
diag_adl:standby redo logfiles that match the primary database:
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 1073741824;
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 1073741824;
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 1073741824;
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 1073741824;
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 1073741824;
diag_adl:ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 1073741824;
diag_adl:Archivelog for thread 1 sequence 96534 required for standby recovery
diag_adl:Switchover: Primary controlfile converted to standby controlfile succesfully.
diag_adl:Switchover: Complete - Database shutdown required
diag_adl:Sending request(convert to primary database) to switchover target xifenfei
Sun Jun 25 09:07:58 2017
diag_adl:Switchover complete. Database shutdown required
diag_adl:USER (ospid: 37024): terminating the instance
Sun Jun 25 09:07:59 2017
diag_adl:Instance terminated by USER, pid = 37024
diag_adl:Shutting down instance (abort)
diag_adl:License high water mark = 527
Sun Jun 25 09:07:59 2017
Instance shutdown complete

备库alert日志

Sun Jun 25 09:05:54 2017
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY COMPLETE
Sun Jun 25 09:06:35 2017
RFS[107]: Assigned to RFS process (PID:7330)
RFS[107]: Selected log 12 for thread 1 sequence 96534 dbid 588725663 branch 916962073
Sat Jun 24 20:06:35 2017
Archived Log entry 100576 added for thread 1 sequence 96534 ID 0x247a73b7 dest 1:
Sat Jun 24 20:06:35 2017
Resetting standby activation ID 612004791 (0x247a73b7)
Sat Jun 24 20:06:35 2017
Media Recovery End-Of-Redo indicator encountered
Sat Jun 24 20:06:35 2017
Media Recovery Continuing
Media Recovery Waiting for thread 1 sequence 96535
Sun Jun 25 09:06:36 2017
SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER  TO PRIMARY' from primary database.
Sun Jun 25 09:06:36 2017
ALTER DATABASE SWITCHOVER TO PRIMARY (xifenfei)
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply
Sat Jun 24 20:06:36 2017
MRP0: Background Media Recovery cancelled with status 16037
Sat Jun 24 20:06:36 2017
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_pr00_4590.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Sat Jun 24 20:06:36 2017
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_pr00_4590.trc:
ORA-16037: user requested cancel of managed recovery operation
Sat Jun 24 20:06:37 2017
MRP0: Background Media Recovery process shutdown (xifenfei)
Sun Jun 25 09:06:38 2017
Role Change: Canceled MRP
Killing 2 processes(PIDS:7328,4704)(all RFS)
      in order to disallow current and future RFS connections.Requested by OS process 7334
Stopping Emon pool
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 5428 user 'oracle' program 'oracle@kage7.hk0620.com (TNS V1-V3)'
Active process 5161 user 'oracle' program 'oracle@kage7.hk0620.com'
…………
Active process 5428 user 'oracle' program 'oracle@kage7.hk0620.com (TNS V1-V3)'
Active process 5161 user 'oracle' program 'oracle@kage7.hk0620.com'
Active process 5178 user 'oracle' program 'oracle@kage7.hk0620.com'
CLOSE: all sessions shutdown successfully.
Stopping Emon pool
Sat Jun 24 20:06:43 2017
SMON: disabling cache recovery
Sat Jun 24 20:06:44 2017
Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED 
Sun Jun 25 09:06:44 2017
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_rmi_7334.trc
SwitchOver after complete recovery through change 13163656450
Online logfile pre-clearing operation disabled by switchover
Online log /u01/app/oracle/oradata/xifenfei/redo01n.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/xifenfei/redo01n.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/xifenfei/redo02n.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/xifenfei/redo02n.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/xifenfei/redo03n.log: Thread 1 Group 3 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/xifenfei/redo03n.log: Thread 1 Group 3 was previously cleared
Online log /u01/app/oracle/oradata/xifenfei/redo04n.log: Thread 1 Group 4 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/xifenfei/redo04n.log: Thread 1 Group 4 was previously cleared
Online log /u01/app/oracle/oradata/xifenfei/redo05n.log: Thread 1 Group 5 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/xifenfei/redo05n.log: Thread 1 Group 5 was previously cleared
Standby became primary SCN: 13163656448
Switchover: Complete - Database mounted as primary
SWITCHOVER: completed request from primary database.
Sat Jun 24 20:07:12 2017
ARC0: Becoming the 'no SRL' ARCH

原备库(现主库)操作

SQL> conn / as sysdba
Connected.
SQL>  select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          MOUNTED

SQL> alter database open;

Database altered.

原主库(现备库)操作

[oracle@localhost scripts]$ ss

SQL*Plus: Release 12.1.0.2.0 Production on 星期日 6月 25 09:09:40 2017

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

已连接到空闲例程。

SQL> startup
ORACLE 例程已经启动。

Total System Global Area 8.5899E+10 bytes
Fixed Size                  7654304 bytes
Variable Size            1.2616E+10 bytes
Database Buffers         7.3014E+10 bytes
Redo Buffers              260780032 bytes
数据库装载完毕。
数据库已经打开。
SQL> alter database recover managed standby database disconnect;

数据库已更改。
发表在 Data Guard, ORACLE 12C | 标签为 | 评论关闭

Physically Addressed Metadata Redundancy on 12c ASM ( PHYS_META_REPLICATED )

发表于 2017 年 4 月 22 日 由 惜分飞

从版本12.1开始，ASM会对某些物理元数据做一份复制，具体的说是每个磁盘的第一个AU（0号AU）上元数据。这意味着，ASM同时维护着两份磁盘头、FST（Free Space Table）表、AT（Allocation table）表的数据。需要注意的是ASM对这些数据采用的是复制（replicate），而不是镜像（mirror）。ASM镜像（mirror）意味着把一份数据，拷贝到不同磁盘上；而物理元数据的副本位于相同的磁盘，因此使用的术语复制（replicate）。这意味着在external冗余的磁盘组中，物理元数据也会被复制。PST也是物理元数据，但是ASM是通过镜像，而不是复制来提供数据保护。因此只有在normal和high冗余的磁盘组中，PST表存在数据的冗余。物理元数据位于每块ASM磁盘的0号AU。元数据复制的特性打开后，ASM会把0号AU的内容拷贝到11号AU，然后同时维护这两份副本。创建磁盘组时如果指定或修改了一个已经存在的磁盘组的compatibility属性为12.1及以上，该特性会自动被打开。当提升ASM compatibility属性值为12.1及以上时，如果11号AU有数据，ASM将把这些数据移动到别处，然后将物理元数据复制到11号AU。从版本11.1.0.7开始，ASM在1号AU的倒数第二个块维护了一份磁盘头的副本。在版本12.1中，ASM仍然维护着这个副本数据。也就是说，现在每个ASM磁盘，有磁盘头的三个副本。
au 0中具体数据
au0

命令行创建diskgroup

[grid@localhost ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 22 08:13:31 2017

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


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

SQL> create diskgroup xifenfei  external redundancy disk '/dev/xifenfei-sdg','/dev/xifenfei-sdh';

Diskgroup created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

查看xifenfei磁盘组属性

[grid@localhost ~]$ asmcmd lsattr -l -G XIFENFEI
Name                     Value       
access_control.enabled   FALSE       
access_control.umask     066         
au_size                  1048576     
cell.smart_scan_capable  FALSE       
compatible.asm           11.2.0.2.0  
compatible.rdbms         10.1.0.0.0  
disk_repair_time         3.6h        
idp.boundary             auto        
idp.type                 dynamic     
sector_size              512

这里可以看到目前compatible.asm为11.2,没有phys_meta_replicated属性

查看磁盘头信息
ASM磁盘头的fdhdb.flags条目指代了物理元数据的复制状态：
· kfdhdb.flags = 0 — 元数据没有复制
· kfdhdb.flags = 1 — 元数据已经复制完毕
· kfdhdb.flags = 2 — 元数据在复制过程中

[grid@localhost ~]$ for disk in `asmcmd lsdsk -G XIFENFEI --suppressheader`; 
> do kfed read $disk | egrep "dskname|flags"; done
kfdhdb.dskname:           XIFENFEI_0000 ; 0x028: length=13
kfdhdb.flags:                         0 ; 0x0fc: 0x00000000
kfdhdb.dskname:           XIFENFEI_0001 ; 0x028: length=13
kfdhdb.flags:                         0 ; 0x0fc: 0x00000000

[grid@localhost ~]$ kfed read /dev/xifenfei-sdg|grep kfbh.type
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
[grid@localhost ~]$ kfed read /dev/xifenfei-sdg aun=11|grep kfbh.type
kfbh.type:                            8 ; 0x002: KFBTYP_CHNGDIR
[grid@localhost ~]$ kfed read /dev/xifenfei-sdg blkn=254 aun=1|grep kfbh.type
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD

这里也比较明显,在aun 11的位置没有第一个au的备份
修改compatible.asm为12.2

[grid@localhost ~]$  asmcmd setattr -G XIFENFEI compatible.asm 12.2.0.0.0
[grid@localhost ~]$ asmcmd lsattr -l -G XIFENFEI
Name                        Value         
access_control.enabled      FALSE         
access_control.umask        066           
appliance._partnering_type  NULL          
au_size                     1048576       
cell.smart_scan_capable     FALSE         
cell.sparse_dg              allnonsparse  
compatible.asm              12.2.0.0.0    
compatible.rdbms            10.1.0.0.0    
content.check               FALSE         
content.type                data          
disk_repair_time            3.6h          
failgroup_repair_time       24.0h         
idp.boundary                auto          
idp.type                    dynamic       
logical_sector_size         512           
phys_meta_replicated        true          
preferred_read.enabled      FALSE         
scrub_async_limit           1             
scrub_metadata.enabled      FALSE         
sector_size                 512           
thin_provisioned            FALSE

这里可以看到修改为compatible.asm=12.2之后，出现phys_meta_replicated属性

查看au的备份

[grid@localhost ~]$ for disk in `asmcmd lsdsk -G XIFENFEI --suppressheader`;
> do kfed read $disk | egrep "dskname|flags"; done
kfdhdb.dskname:           XIFENFEI_0000 ; 0x028: length=13
kfdhdb.flags:                         1 ; 0x0fc: 0x00000001
kfdhdb.dskname:           XIFENFEI_0001 ; 0x028: length=13
kfdhdb.flags:                         1 ; 0x0fc: 0x00000001

[grid@localhost ~]$ kfed read /dev/xifenfei-sdg aun=11|grep kfbh.type
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
[grid@localhost ~]$ kfed read /dev/xifenfei-sdg blkn=254 aun=1|grep kfbh.type
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
[grid@localhost ~]$ kfed read /dev/xifenfei-sdg|grep kfbh.type
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD

这里就可以看到kfdhdb.flags为1，在au 11的地方也变为了磁盘头信息

模拟第一个au彻底损坏

[grid@localhost ~]$ dd if=/dev/zero of=/dev/xifenfei-sdg bs=1024k count=1 conv=notrunc 
1+0 records in
1+0 records out
[grid@localhost ~]$ kfed read /dev/xifenfei-sdg
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
000000000 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

尝试mount磁盘组

SQL> alter diskgroup xifenfei mount;
alter diskgroup xifenfei mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "XIFENFEI" cannot be mounted
ORA-15040: diskgroup is incomplete

alert日志信息

SQL> alter diskgroup xifenfei mount
2017-04-22T08:30:00.889037-04:00
NOTE: cache registered group XIFENFEI 1/0xB15C368B
NOTE: cache began mount (first) of group XIFENFEI 1/0xB15C368B
NOTE: Assigning number (1,1) to disk (/dev/xifenfei-sdh)
2017-04-22T08:30:01.001544-04:00
ERROR: no read quorum in group: required 1, found 0 disks
2017-04-22T08:30:01.001737-04:00
NOTE: cache dismounting (clean) group 1/0xB15C368B (XIFENFEI)
NOTE: messaging CKPT to quiesce pins Unix process pid: 20894, image: oracle@localhost.localdomain (TNS V1-V3)
NOTE: dbwr not being msg'd to dismount
NOTE: LGWR not being messaged to dismount
NOTE: cache dismounted group 1/0xB15C368B (XIFENFEI)
NOTE: cache ending mount (fail) of group XIFENFEI number=1 incarn=0xb15c368b
NOTE: cache deleting context for group XIFENFEI 1/0xb15c368b
2017-04-22T08:30:01.028825-04:00
GMON dismounting group 1 at 2 for pid 23, osid 20894
2017-04-22T08:30:01.029146-04:00
NOTE: Disk XIFENFEI_0001 in mode 0x8 marked for de-assignment
ERROR: diskgroup XIFENFEI was not mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "XIFENFEI" cannot be mounted
ORA-15040: diskgroup is incomplete

2017-04-22T08:30:01.036014-04:00
ERROR: alter diskgroup xifenfei mount

很明显由于xifenfei-sdg第一个au 已经被完全dd掉，xifenfei磁盘组无法mount，提示ORA-15040: diskgroup is incomplete

使用备份au还原

[grid@localhost ~]$ dd if=/dev/xifenfei-sdg skip=11 bs=1024k count=1 of=/tmp/sdg_header
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.048576 s, 21.6 MB/s

[grid@localhost ~]$ kfed read /tmp/sdg_header |more
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          2 ; 0x003: 0x02
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  3085718230 ; 0x00c: 0xb7ec52d6
kfbh.fcn.base:                       41 ; 0x010: 0x00000029
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                203423744 ; 0x020: 0x0c200000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:           XIFENFEI_0000 ; 0x028: length=13
kfdhdb.grpname:                XIFENFEI ; 0x048: length=8
kfdhdb.fgname:            XIFENFEI_0000 ; 0x068: length=13
[grid@localhost ~]$ dd if=/tmp/sdg_header of=/dev/xifenfei-sdg bs=1024k count=1 conv=notrunc
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0262761 s, 39.9 MB/s
[grid@localhost ~]$ kfed read /dev/xifenfei-sdg|more
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          2 ; 0x003: 0x02
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  3085718230 ; 0x00c: 0xb7ec52d6
kfbh.fcn.base:                       41 ; 0x010: 0x00000029
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                203423744 ; 0x020: 0x0c200000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:           XIFENFEI_0000 ; 0x028: length=13
kfdhdb.grpname:                XIFENFEI ; 0x048: length=8
kfdhdb.fgname:            XIFENFEI_0000 ; 0x068: length=13

xifenfei磁盘组mount成功

[grid@localhost ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 22 08:34:53 2017

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 diskgroup xifenfei mount;

Diskgroup altered.

asm alert日志

SQL> alter diskgroup xifenfei mount
2017-04-22T08:34:59.298838-04:00
NOTE: cache registered group XIFENFEI 1/0xFA6C368E
NOTE: cache began mount (first) of group XIFENFEI 1/0xFA6C368E
NOTE: Assigning number (1,0) to disk (/dev/xifenfei-sdg)
NOTE: Assigning number (1,1) to disk (/dev/xifenfei-sdh)
2017-04-22T08:35:05.447528-04:00
NOTE: GMON heartbeating for grp 1 (XIFENFEI)
GMON querying group 1 at 5 for pid 23, osid 21195
2017-04-22T08:35:05.449557-04:00
NOTE: cache is mounting group XIFENFEI created on 2017/04/22 08:13:39
NOTE: cache opening disk 0 of grp 1: XIFENFEI_0000 path:/dev/xifenfei-sdg
NOTE: 04/22/17 08:35:04 XIFENFEI.F1X0 found on disk 0 au 2 fcn 0.0 datfmt 2
NOTE: cache opening disk 1 of grp 1: XIFENFEI_0001 path:/dev/xifenfei-sdh
2017-04-22T08:35:05.450316-04:00
NOTE: cache mounting (first) external redundancy group 1/0xFA6C368E (XIFENFEI)
NOTE: cache recovered group 1 to fcn 0.352
NOTE: redo buffer size is 256 blocks (1056768 bytes)
2017-04-22T08:35:05.504356-04:00
NOTE: LGWR attempting to mount thread 1 for diskgroup 1 (XIFENFEI)
NOTE: LGWR found thread 1 closed at ABA 2.63 lock domain=0 inc#=0 instnum=1
NOTE: LGWR mounted thread 1 for diskgroup 1 (XIFENFEI)
2017-04-22T08:35:05.555647-04:00
NOTE: LGWR opened thread 1 (XIFENFEI) at fcn 0.352 ABA 3.64 lock domain=1 inc#=0 instnum=1 
  gx.incarn=4201395854 mntstmp=2017/04/22 08:35:05.510000
2017-04-22T08:35:05.556006-04:00
NOTE: cache mounting group 1/0xFA6C368E (XIFENFEI) succeeded
NOTE: cache ending mount (success) of group XIFENFEI number=1 incarn=0xfa6c368e
2017-04-22T08:35:05.596616-04:00
NOTE: Instance updated compatible.asm to 12.2.0.0.0 for grp 1 (XIFENFEI).
2017-04-22T08:35:05.599181-04:00
NOTE: Instance updated compatible.rdbms to 10.1.0.0.0 for grp 1 (XIFENFEI).
2017-04-22T08:35:05.608332-04:00
SUCCESS: diskgroup XIFENFEI was mounted
2017-04-22T08:35:05.635588-04:00
SUCCESS: alter diskgroup xifenfei mount
发表在 ORACLE 12C, Oracle ASM | 标签为 , , | 评论关闭

ORA-28040: No matching authentication protocol

发表于 2016 年 7 月 20 日 由 惜分飞

电脑上面安装了三个版本的数据库10.2.0.3,11.2.0.1,12.1.0.2版本,使用他们分别尝试连接另外一个12.2.0.3的环境数据库发现只有12.1的版本客户端可以连接到12.2上面，其他版本报ORA-28040错误
分别测试连接，报ORA-28040错误

C:\Users\XIFENFEI>sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 星期三 7月 20 00:03:01 2016

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


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL>
SQL>

C:\Users\XIFENFEI>D:\app\FAL\product\11.2.0\dbhome_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 7月 20 00:10:33 2016

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

ERROR:
ORA-28040: No matching authentication protocol


C:\Users\XIFENFEI>D:\app\product\10.2.0\db_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 7月 20 00:09:30 2016

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-28040: 没有匹配的验证协议


请输入用户名:

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.

解决方法
在服务端的sqlnet.ora文件中加入上如下信息，然后重启监听

[oracle@ora1221 admin]$ vi sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

[oracle@ora1221 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.2.0.0.3 - Production on 17-JUN-2016 06:36:13

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@ora1221 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.0.3 - Production on 17-JUN-2016 06:36:17

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

Starting /u01/app/oracle/product/12.2.0/db_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.0.3 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/ora1221/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1221)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.0.3 - Production
Start Date                17-JUN-2016 06:36:17
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora1221/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1221)(PORT=1521)))
The listener supports no services
The command completed successfully

sqlnet中参数说明
SQLNET.ALLOWED_LOGON_VERSION_SERVER 是服务端参数对于jdbc和oci都生效,该参数不是只具体数据库版本,而是指授权协议的版本
SQLNET.ALLOWED_LOGON_VERSION_CLIENT 是指作为客户端连接其他实例的时候生效,也是只授权协议版本,而且该参数只对oci生效,jdbc 需要通过在代码中类似实现

OracleDataSource ods = new OracleDataSource();
ods.setURL(jdbcURL);
ods.setUser("scott");
ods.setPassword("tiger");
Properties props = new Properties();
props.put("oracle.jdbc.allowedLogonVersion", 12);
ods.setConnectionProperties(props);
Connection con = ods.getConnection();

上述两个参数可以填写值
12a for Oracle Database 12c release 12.1.0.2 or later authentication protocols (strongest protection)
12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended)
11 for Oracle Database 11g authentication protocols (default)
10 for Oracle Database 10g authentication protocols
9 for Oracle9i Database authentication protocol
8 for Oracle8i Database authentication protocol
allowed_logon_version_server

具体描述请见:http://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF2010

再次测试连接

C:\Users\XIFENFEI>D:\app\FAL\product\11.2.0\dbhome_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 7月 20 00:20:21 2016

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


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production 断开

C:\Users\XIFENFEI>D:\app\product\10.2.0\db_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 7月 20 00:20:28 2016

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL>

C:\Users\XIFENFEI>sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 星期三 7月 20 00:20:55 2016

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


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

该问题在jdbc中也表现明显，建议参考Starting With Oracle JDBC Drivers (文档 ID 401934.1)和Client / Server Interoperability Support Matrix for Different Oracle Versions (文档 ID 207303.1)选择完全兼容性的客户端和jdbc版本,另外可以关注相关文章:
ORA-28040 and SQLNET.ALLOWED_LOGON_VERSION_CLIENT for JDBC Thin Clients (文档 ID 2000339.1)
ORA-28040 Using JDBC Connection to 12c Database (文档 ID 2111118.1)
JDBC Version 10.2.0.4 Produces ORA-28040 Connecting To Oracle 12c (12.1.0.2) Database (文档 ID 2023160.1)
ORA-28040 and SQLNET.ALLOWED_LOGON_VERSION_CLIENT for JDBC Thin Clients (文档 ID 2000339.1)

发表在 ORACLE 12C | 标签为 , | 评论关闭