rac2rac dataguard遇到ORA-12514解决办法

环境:

源端是linux rac 11.2.0.3

目标端是linux rac 11.2.0.4

在搭建dataguard,发起duplicate的时候主库报错:

RMAN-03015: Memory ScriptRMAN-04006: : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

检查备库日志,发现备库在停止后,没有自动启动,正常的流程应该是主库发起控制文件的copy,然后备库数据库获取到控制文件后进行重启,然后再进行数据文件的copy.很显然这里是无法自动启动造成了ORA-12514.

查询MOS文档,发现有两篇文档写的很详细:

RMAN Active Duplication Failing with RMAN-05501 ORA-17629 ORA-17627 ORA-12514 (文档 ID 1509895.1)

SCAN / DataGuard: ORA-12514 Error When Reconnecting To Instance After Startup (文档 ID 1638605.1)

其中第二篇文章写的特别详细,如下:

APPLIES TO:

Oracle Net Services - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

SYMPTOMS

After creating a physical standby database using RMAN, attempts to connect to the standby fail with the following error:

RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

This error occurs AFTER the standby database is shutdown during the duplicate database procedure.  

There is a static SID_DESC in the listener.ora file for the auxiliary database.

SID_LIST_LISTENER =
   (SID_LIST =
       (SID_DESC =
        (GLOBAL_DBNAME=MYSTBY.oracle.com)<=======Static handler
        (ORACLE_HOME=/u01/app/oracle/product/11.2.0)
        (SID_NAME=MYSTBY)
      ) 
   )

 

CHANGES

 This is likely a new implementation of DataGuard.

CAUSE

The TNS connect string used by the primary database contains a SCAN host address in the HOST field:

MYSTBY= 
     (DESCRIPTION =   (ADDRESS = (PROTOCOL = TCP)(HOST = myscan-scan)(PORT = 1521))  <SCAN host in HOST field is incorrect
          (CONNECT_DATA =  
             (SERVER = DEDICATED)   
               (SERVICE_NAME = MYSTBY.oracle.com)   
                  (UR=A)
             ) 
          )

 

The HOST field in the listener.ora file was correctly configured for the VIP host:

LISTENER =
    (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp) (HOST=MY_VIP_HOST) (PORT=1521))<===VIP hostname in HOST field
     )
  )

SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
           (GLOBAL_DBNAME=MYSTBY.oracle.com)<=======Static handler
           (ORACLE_HOME=/u01/app/oracle/product/11.2.0)
           (SID_NAME=MYSTBY)
          ) 
       )

 

The SCAN listener is not capable of handing off a connection to a statically defined service. In this case, the ORA-12514 error would be expected when the service is not dynamically registered.  After the Standby instance was shut down, the service MYSTBY.oracle.com would no longer be dynamically registered against the SCAN listener.  

 

下面是两个文档的解决办法:
///

SOLUTION

The primary database should be using the VIP hostname in the TNS connect string to connect to the standby database for archive log shipments.   




Note here in the tnsnames.ora file:

MYSTBY=      
    (DESCRIPTION =   
       (ADDRESS = (PROTOCOL = TCP)(HOST = myscan-scan)(PORT = 1521))  <===SCAN host in HOST field is incorrect         
           (CONNECT_DATA =               
              (SERVER = DEDICATED)                  
                (SERVICE_NAME = MYSTBY.oracle.com)              
                  (UR=A)
             ) 
        )

Should be:

 

MYSTBY=
   (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = MY_VIP_HOST)(PORT = 1521))<====Use VIP host in HOST field.
           (CONNECT_DATA =
             (SERVER = DEDICATED)
               (SERVICE_NAME = MYSTBY.oracle.com)
                 (UR=A)
           )
       )

The statically defined handler would be available ONLY for the listener running on the VIP, not the SCAN listener.   It is not possible to use a statically defined listener handler at a SCAN listener
since the SCAN listeners are not "fixed" on a specific node.

///

SOLUTION

Create new temporary listener on the Target without using Listener Scan to run RMAN Duplicate or change the current listener for target so its not using Listener Scan.

The Solution applies to both Backup-Based Duplication and Active Database Duplication

 

Details example, steps to duplicate from RAC:

STEP BY STEP RMAN DUPLICATE Database From RAC ASM To RAC ASM (Doc ID 1913937.1)

====================================================================================

结合两篇文章,提到几个注意点:

1.在主库的tnsnamea.ora中不能写备库的scanip,需要写vip

2.备库不能使用grid下的监听.需要在oracle下重新启动一个监听.所以先要把grid下的listener和scan_listener关闭

3.备库新建的监听必须使用静态监听

静态监听配置示例如下:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.16.6)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

 

SID_LIST_LISTENER=  

(SID_LIST =  

    (SID_DESC =  

      (global_dbname= oradb1)     ##这个是服务名

      (ORACLE_HOME = /oracle/app/oracle/product/11g)

      (SID_NAME = oradb1)         ##这个是实例名

    )  

  )

经过上面修改之后,重新发起duplicate同步.同步正常

在完成duplicate之后,可以恢复集群监听等.