月归档：六月 2017

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 | 标签为 | 评论关闭

oracle rac 12.2 执行root.sh报CLSRSC-400

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

在redhat 7.3版本中安装oracle rac 12.2的过程中,执行root.sh脚本的第14步的时候报如下错误,导致无法继续
CLSRSC-400: A system reboot is required to continue installing.
The command ‘/u01/app/grid/product/12.2.0/grid/perl/bin/perl -I/u01/app/grid/product/12.2.0/grid/perl/lib
-I/u01/app/grid/product/12.2.0/grid/crs/install /u01/app/grid/product/12.2.0/grid/crs/install/rootcrs.pl ‘ execution failed
os版本信息

[grid@xifenfei01 ~]$ more /etc/redhat-release 
Red Hat Enterprise Linux Server release 7.3 (Maipo)
[grid@xifenfei01 ~]$ uname -a
Linux xifenfei01 3.10.0-514.el7.x86_64 #1 SMP Wed Oct 19 11:24:13 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

root.sh报错

[root@xifenfei01 ~]# /u01/app/grid/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/grid/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/grid/oraInventory to oinstall.
The execution of the script is complete.
[root@xifenfei01 ~]# /u01/app/grid/product/12.2.0/grid/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/product/12.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/grid/product/12.2.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/grid_bash/crsdata/xifenfei01/crsconfig/rootcrs_xifenfei01_2017-06-11_09-52-55AM.log
2017/06/11 09:53:00 CLSRSC-594: Executing installation step 1 of 19: 'SetupTFA'.
2017/06/11 09:53:00 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.
2017/06/11 09:53:27 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.
2017/06/11 09:53:27 CLSRSC-594: Executing installation step 2 of 19: 'ValidateEnv'.
2017/06/11 09:53:30 CLSRSC-363: User ignored prerequisites during installation
2017/06/11 09:53:30 CLSRSC-594: Executing installation step 3 of 19: 'CheckFirstNode'.
2017/06/11 09:53:31 CLSRSC-594: Executing installation step 4 of 19: 'GenSiteGUIDs'.
2017/06/11 09:53:32 CLSRSC-594: Executing installation step 5 of 19: 'SaveParamFile'.
2017/06/11 09:53:37 CLSRSC-594: Executing installation step 6 of 19: 'SetupOSD'.
2017/06/11 09:53:38 CLSRSC-594: Executing installation step 7 of 19: 'CheckCRSConfig'.
2017/06/11 09:53:38 CLSRSC-594: Executing installation step 8 of 19: 'SetupLocalGPNP'.
2017/06/11 09:53:51 CLSRSC-594: Executing installation step 9 of 19: 'ConfigOLR'.
2017/06/11 09:53:56 CLSRSC-594: Executing installation step 10 of 19: 'ConfigCHMOS'.
2017/06/11 09:53:56 CLSRSC-594: Executing installation step 11 of 19: 'CreateOHASD'.
2017/06/11 09:54:00 CLSRSC-594: Executing installation step 12 of 19: 'ConfigOHASD'.
2017/06/11 09:54:15 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
2017/06/11 09:54:44 CLSRSC-594: Executing installation step 13 of 19: 'InstallAFD'.
2017/06/11 09:54:48 CLSRSC-594: Executing installation step 14 of 19: 'InstallACFS'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'xifenfei01'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'xifenfei01' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2017/06/11 09:55:15 CLSRSC-400: A system reboot is required to continue installing.
The command '/u01/app/grid/product/12.2.0/grid/perl/bin/perl -I/u01/app/grid/product/12.2.0/grid/perl/lib
-I/u01/app/grid/product/12.2.0/grid/crs/install /u01/app/grid/product/12.2.0/grid/crs/install/rootcrs.pl'execution failed

主要报错信息：
2017/06/11 09:55:15 CLSRSC-400: A system reboot is required to continue installing.
The command ‘/u01/app/grid/product/12.2.0/grid/perl/bin/perl -I/u01/app/grid/product/12.2.0/grid/perl/lib -I/u01/app/grid/product/12.2.0/grid/crs/install /u01/app/grid/product/12.2.0/grid/crs/install/rootcrs.pl ‘ execution failed
查询mos发下:ACFS Drivers Install reports CLSRSC-400: A system reboot is required to continue installing (Doc ID 2025056.1),主要是由于12c gi开始，acfs默认是安装的，由于acfs在redhat 7.3中不支持导致上述的错误信息.

[grid@xifenfei01 ~]$ acfsdriverstate -orahome $ORACLE_HOME supported
ACFS-9459: ADVM/ACFS is not supported on this OS version: '3.10.0-514.el7.x86_64'
ACFS-9201: Not Supported

处理方法
停掉crs,kill 进程(如果有不能停掉的，通过kill处理),执行root.sh

[root@xifenfei01 ~]# /u01/app/grid/product/12.2.0/grid/bin/crsctl status res -t -init
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        OFFLINE OFFLINE                               STABLE
ora.cluster_interconnect.haip
      1        OFFLINE OFFLINE                               STABLE
ora.crf
      1        OFFLINE OFFLINE                               STABLE
ora.crsd
      1        OFFLINE OFFLINE                               STABLE
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.cssdmonitor
      1        OFFLINE OFFLINE                               STABLE
ora.ctssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.drivers.acfs
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        OFFLINE OFFLINE                               STABLE
ora.gipcd
      1        OFFLINE OFFLINE                               STABLE
ora.gpnpd
      1        OFFLINE OFFLINE                               STABLE
ora.mdnsd
      1        OFFLINE OFFLINE                               STABLE
ora.storage
      1        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------
[root@xifenfei01 ~]# /u01/app/grid/product/12.2.0/grid/bin/crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'xifenfei01'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'xifenfei02' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@xifenfei02 ~]# ps -ef|grep d.bin
root      29155  11754  0 10:46 pts/0    00:00:00 grep --color=auto d.bin

[root@xifenfei01 ~]# /u01/app/grid/product/12.2.0/grid/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/product/12.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/grid/product/12.2.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/grid_bash/crsdata/xifenfei01/crsconfig/rootcrs_xifenfei01_2017-06-11_10-33-57AM.log
2017/06/11 10:33:59 CLSRSC-594: Executing installation step 1 of 19: 'SetupTFA'.
2017/06/11 10:33:59 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.
2017/06/11 10:34:00 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.
2017/06/11 10:34:00 CLSRSC-594: Executing installation step 2 of 19: 'ValidateEnv'.
2017/06/11 10:34:01 CLSRSC-363: User ignored prerequisites during installation
2017/06/11 10:34:01 CLSRSC-594: Executing installation step 3 of 19: 'CheckFirstNode'.
2017/06/11 10:34:02 CLSRSC-594: Executing installation step 4 of 19: 'GenSiteGUIDs'.
2017/06/11 10:34:02 CLSRSC-594: Executing installation step 5 of 19: 'SaveParamFile'.
2017/06/11 10:34:03 CLSRSC-594: Executing installation step 6 of 19: 'SetupOSD'.
2017/06/11 10:34:04 CLSRSC-594: Executing installation step 7 of 19: 'CheckCRSConfig'.
2017/06/11 10:34:04 CLSRSC-594: Executing installation step 8 of 19: 'SetupLocalGPNP'.
2017/06/11 10:34:06 CLSRSC-594: Executing installation step 9 of 19: 'ConfigOLR'.
2017/06/11 10:34:06 CLSRSC-594: Executing installation step 10 of 19: 'ConfigCHMOS'.
2017/06/11 10:34:53 CLSRSC-594: Executing installation step 11 of 19: 'CreateOHASD'.
2017/06/11 10:34:54 CLSRSC-594: Executing installation step 12 of 19: 'ConfigOHASD'.
2017/06/11 10:35:09 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
2017/06/11 10:35:31 CLSRSC-594: Executing installation step 13 of 19: 'InstallAFD'.
2017/06/11 10:35:33 CLSRSC-594: Executing installation step 14 of 19: 'InstallACFS'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'xifenfei01'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'xifenfei01'
CRS-2673: Attempting to stop 'ora.evmd' on 'xifenfei01'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'xifenfei01'
CRS-2677: Stop of 'ora.mdnsd' on 'xifenfei01' succeeded
CRS-2677: Stop of 'ora.evmd' on 'xifenfei01' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'xifenfei01' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'xifenfei01'
CRS-2677: Stop of 'ora.gipcd' on 'xifenfei01' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'xifenfei01' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2017/06/11 10:35:57 CLSRSC-594: Executing installation step 15 of 19: 'InstallKA'.
2017/06/11 10:36:01 CLSRSC-594: Executing installation step 16 of 19: 'InitConfig'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'xifenfei01'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'xifenfei01' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.evmd' on 'xifenfei01'
CRS-2672: Attempting to start 'ora.mdnsd' on 'xifenfei01'
CRS-2676: Start of 'ora.mdnsd' on 'xifenfei01' succeeded
CRS-2676: Start of 'ora.evmd' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'xifenfei01'
CRS-2676: Start of 'ora.gpnpd' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'xifenfei01'
CRS-2672: Attempting to start 'ora.gipcd' on 'xifenfei01'
CRS-2676: Start of 'ora.cssdmonitor' on 'xifenfei01' succeeded
CRS-2676: Start of 'ora.gipcd' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'xifenfei01'
CRS-2672: Attempting to start 'ora.diskmon' on 'xifenfei01'
CRS-2676: Start of 'ora.diskmon' on 'xifenfei01' succeeded
CRS-2676: Start of 'ora.cssd' on 'xifenfei01' succeeded

Disk groups created successfully. Check /u01/app/grid/grid_bash/cfgtoollogs/asmca/asmca-170611AM103637.log for details.


2017/06/11 10:37:40 CLSRSC-482: Running command: '/u01/app/grid/product/12.2.0/grid/bin/ocrconfig -upgrade grid oinstall'
CRS-2672: Attempting to start 'ora.crf' on 'xifenfei01'
CRS-2672: Attempting to start 'ora.storage' on 'xifenfei01'
CRS-2676: Start of 'ora.storage' on 'xifenfei01' succeeded
CRS-2676: Start of 'ora.crf' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'xifenfei01'
CRS-2676: Start of 'ora.crsd' on 'xifenfei01' succeeded
CRS-4256: Updating the profile
Successful addition of voting disk 49af246c7d2e4f5dbf0d9ea09cc047d5.
Successfully replaced voting disk group with +DATA.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   49af246c7d2e4f5dbf0d9ea09cc047d5 (/dev/mapper/data1) [DATA]
Located 1 voting disk(s).
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'xifenfei01'
CRS-2673: Attempting to stop 'ora.crsd' on 'xifenfei01'
CRS-2677: Stop of 'ora.crsd' on 'xifenfei01' succeeded
CRS-2673: Attempting to stop 'ora.storage' on 'xifenfei01'
CRS-2673: Attempting to stop 'ora.crf' on 'xifenfei01'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'xifenfei01'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'xifenfei01'
CRS-2677: Stop of 'ora.crf' on 'xifenfei01' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'xifenfei01' succeeded
CRS-2677: Stop of 'ora.storage' on 'xifenfei01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'xifenfei01'
CRS-2677: Stop of 'ora.mdnsd' on 'xifenfei01' succeeded
CRS-2677: Stop of 'ora.asm' on 'xifenfei01' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'xifenfei01'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'xifenfei01' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'xifenfei01'
CRS-2673: Attempting to stop 'ora.evmd' on 'xifenfei01'
CRS-2677: Stop of 'ora.ctssd' on 'xifenfei01' succeeded
CRS-2677: Stop of 'ora.evmd' on 'xifenfei01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'xifenfei01'
CRS-2677: Stop of 'ora.cssd' on 'xifenfei01' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'xifenfei01'
CRS-2677: Stop of 'ora.gipcd' on 'xifenfei01' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'xifenfei01' has completed
CRS-4133: Oracle High Availability Services has been stopped.
2017/06/11 10:38:40 CLSRSC-594: Executing installation step 17 of 19: 'StartCluster'.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.mdnsd' on 'xifenfei01'
CRS-2672: Attempting to start 'ora.evmd' on 'xifenfei01'
CRS-2676: Start of 'ora.mdnsd' on 'xifenfei01' succeeded
CRS-2676: Start of 'ora.evmd' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'xifenfei01'
CRS-2676: Start of 'ora.gpnpd' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'xifenfei01'
CRS-2676: Start of 'ora.gipcd' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'xifenfei01'
CRS-2674: Start of 'ora.drivers.acfs' on 'xifenfei01' failed
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'xifenfei01'
CRS-2676: Start of 'ora.cssdmonitor' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'xifenfei01'
CRS-2672: Attempting to start 'ora.diskmon' on 'xifenfei01'
CRS-2676: Start of 'ora.diskmon' on 'xifenfei01' succeeded
CRS-2676: Start of 'ora.cssd' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'xifenfei01'
CRS-2672: Attempting to start 'ora.ctssd' on 'xifenfei01'
CRS-2676: Start of 'ora.ctssd' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'xifenfei01'
CRS-2674: Start of 'ora.drivers.acfs' on 'xifenfei01' failed
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'xifenfei01'
CRS-2676: Start of 'ora.asm' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'xifenfei01'
CRS-2676: Start of 'ora.storage' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.crf' on 'xifenfei01'
CRS-2676: Start of 'ora.crf' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'xifenfei01'
CRS-2676: Start of 'ora.crsd' on 'xifenfei01' succeeded
CRS-6023: Starting Oracle Cluster Ready Services-managed resources
CRS-6017: Processing resource auto-start for servers: xifenfei01
CRS-6016: Resource auto-start has completed for server xifenfei01
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2017/06/11 10:40:23 CLSRSC-343: Successfully started Oracle Clusterware stack
2017/06/11 10:40:23 CLSRSC-594: Executing installation step 18 of 19: 'ConfigNode'.
CRS-2672: Attempting to start 'ora.ASMNET1LSNR_ASM.lsnr' on 'xifenfei01'
CRS-2676: Start of 'ora.ASMNET1LSNR_ASM.lsnr' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'xifenfei01'
CRS-2676: Start of 'ora.asm' on 'xifenfei01' succeeded
CRS-2672: Attempting to start 'ora.DATA.dg' on 'xifenfei01'
CRS-2676: Start of 'ora.DATA.dg' on 'xifenfei01' succeeded
2017/06/11 10:42:19 CLSRSC-594: Executing installation step 19 of 19: 'PostConfig'.
2017/06/11 10:43:16 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

其他剩余节点也是类似处理,最终跳过acfs安装成功

[grid@xifenfei01 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       xifenfei01               STABLE
               ONLINE  ONLINE       xifenfei02               STABLE
ora.DATA.dg
               ONLINE  ONLINE       xifenfei01               STABLE
               ONLINE  ONLINE       xifenfei02               STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       xifenfei01               STABLE
               ONLINE  ONLINE       xifenfei02               STABLE
ora.chad
               ONLINE  ONLINE       xifenfei01               STABLE
               ONLINE  ONLINE       xifenfei02               STABLE
ora.net1.network
               ONLINE  ONLINE       xifenfei01               STABLE
               ONLINE  ONLINE       xifenfei02               STABLE
ora.ons
               ONLINE  ONLINE       xifenfei01               STABLE
               ONLINE  ONLINE       xifenfei02               STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       xifenfei01               STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       xifenfei01               169.254.20.214 192.1
                                                             68.1.20 192.168.2.20
                                                             ,STABLE
ora.asm
      1        ONLINE  ONLINE       xifenfei01               Started,STABLE
      2        ONLINE  ONLINE       xifenfei02               Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       xifenfei01               STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       xifenfei01               Open,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       xifenfei01               STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       xifenfei01               STABLE
ora.xifenfei01.vip
      1        ONLINE  ONLINE       xifenfei01               STABLE
ora.xifenfei02.vip
      1        ONLINE  ONLINE       xifenfei02               STABLE
--------------------------------------------------------------------------------

最新官方处理方案:CLSRSC-400: A system reboot is required to continue installing.

发表在 Oracle RAC, Oracle安装升级 | 标签为 , , | 一条评论

_optimizer_null_aware_antijoin和not in效率

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

准备两个测试表

SQL> conn chf/oracle
Connected.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> create table t_xifenfei
  2  as select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     86259

SQL> create table t_xifenfei1
  2  as select * from dba_objects;

Table created.

SQL> select count(*) from t_xifenfei1;

  COUNT(*)
----------
     86260

--删除部分记录，用来做not in的内部表
SQL> delete from t_xifenfei where object_id>86200;

918 rows deleted.

SQL> commit;

Commit complete.

查询_optimizer_null_aware_antijoin隐含参数默认值

SQL> conn / as sysdba
Connected.
SQL> col name for a52
SQL> col value for a24
SQL> col description for a50
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  from x$ksppi a,x$ksppcv b
 where a.inst_id = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
   and a.indx = b.indx
   and upper(a.ksppinm) LIKE upper('%&param%')
order by name
SQL> SQL>   2    3    4    5    6    7    8  /
Enter value for param: _optimizer_null_aware_antijoin
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_optimizer_null_aware_antijoin%')

NAME                                                 VALUE                    DESCRIPTION
---------------------------------------------------- ------------------------ -----------------------------
_optimizer_null_aware_antijoin                       TRUE                     null-aware antijoin parameter

_optimizer_null_aware_antijoin从11.1.0.6开始引进,默认为true

_optimizer_null_aware_antijoin为true,执行not in

SQL> conn chf/oracle 
Connected.
SQL> set autot trace
SQL> set timing on
SQL> set lines 150
SQL> set pages 1000
SQL>  select count(*) from t_xifenfei1 where object_id not in(select object_id from t_xifenfei);

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 4048525918

----------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |     1 |    10 |   688   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE          |             |     1 |    10 |            |          |
|*  2 |   HASH JOIN RIGHT ANTI NA|             |  1137 | 11370 |   688   (1)| 00:00:09 |
|   3 |    TABLE ACCESS FULL     | T_XIFENFEI  | 85341 |   416K|   344   (1)| 00:00:05 |
|   4 |    TABLE ACCESS FULL     | T_XIFENFEI1 | 86260 |   421K|   344   (1)| 00:00:05 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"="OBJECT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2472  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

_optimizer_null_aware_antijoin为false,执行not in

SQL> alter session set "_optimizer_null_aware_antijoin"=false;

Session altered.

Elapsed: 00:00:00.00
SQL> select count(*) from t_xifenfei1 where object_id not in(select object_id from t_xifenfei);

Elapsed: 00:02:29.64

Execution Plan
----------------------------------------------------------
Plan hash value: 2503880249

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     1 |     5 |    25M  (1)| 86:20:57 |
|   1 |  SORT AGGREGATE     |             |     1 |     5 |            |          |
|*  2 |   FILTER            |             |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T_XIFENFEI1 | 86260 |   421K|   344   (1)| 00:00:05 |
|*  4 |    TABLE ACCESS FULL| T_XIFENFEI  |     1 |     5 |   344   (1)| 00:00:05 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( NOT EXISTS (SELECT 0 FROM "T_XIFENFEI" "T_XIFENFEI" WHERE
              LNNVL("OBJECT_ID"<>:B1)))
   4 - filter(LNNVL("OBJECT_ID"<>:B1))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
   52982891  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这里很明显,当 _optimizer_null_aware_antijoin为false的时候not in效率非常低(当in里面记录多，使用FILTER效率肯定低下).

_optimizer_null_aware_antijoin为false,执行not exists

SQL> alter session set "_optimizer_null_aware_antijoin"=false;

Session altered.

SQL>select count(*) from t_xifenfei1 b where not exists 
 2  (select 1 from t_xifenfei a where a.object_id=b.object_id);

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 2976307246

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |    10 |   688   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE       |             |     1 |    10 |            |          |
|*  2 |   HASH JOIN RIGHT ANTI|             |  1137 | 11370 |   688   (1)| 00:00:09 |
|   3 |    TABLE ACCESS FULL  | T_XIFENFEI  | 85341 |   416K|   344   (1)| 00:00:05 |
|   4 |    TABLE ACCESS FULL  | T_XIFENFEI1 | 86260 |   421K|   344   (1)| 00:00:05 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2472  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

对于11g的版本可以通过_optimizer_null_aware_antijoin参数开启NULL-aware Anti join特性来提高not in的效率,对于11g以下版本可以通过not exists来提高效率

发表在 Oracle性能优化 | 标签为 , | 评论关闭