Dataguard 11.4 ORA-12154: TNS:could not resolve the connect identifier 處理

ORA-12154: TNS:could not resolve the connect identifier specified 處理

SQL> select dest_name,status,error from v$archive_dest  where rownum <3;

DEST_NAME                  STATUS    ERROR

--------------------------------------- -------------------------------

LOG_ARCHIVE_DEST_1         VALID

LOG_ARCHIVE_DEST_2         ERROR     ORA-12154: TNS:could not resolve the connect identifier specified

其中：

Tnsping tnsname 是通的，但是這里依舊報錯排錯原因：

Primary端：

log_archive_dest_2                   string      service=orcl_dg async

                                                   valid_for=(online_logfiles,p

                                                 rimary_role)

                                                   db_unique_name=orcl_dg

standby 端：

SQL> show parameter fal

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fal_client                           string      orcl_primary

fal_server                           string      orcl_standby

SQL> show parameter service_name          

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

service_names                        string      orcl_dg

SQL>

檢查兩端Tnsnames.ora 配置：

orcl_primary =

(DESCRIPTION = 

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

    (CONNECT_DATA = 

             (SERVER = DEDICATED) 

                 (SERVICE_NAME = orcl_pm)

    )

)

orcl_standby =

(DESCRIPTION = 

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

    (CONNECT_DATA = 

             (SERVER = DEDICATED) 

                 (SERVICE_NAME = orcl_dg) 

    )

)

注：參數LOG_ARCHIVE_DEST_n Parameter Attributes里面的service是指：net_service_name

When you specify a SERVICE attribute:

You identify remote destinations by specifying the SERVICE attribute with a valid Oracle Net service name (SERVICE=net_service_name) that identifies the remote Oracle database instance to which the redo data will be sent.

The Oracle Net service name that you specify with the SERVICE attribute is translated into a connection descriptor that contains the information necessary for connecting to the remote database.

因此：log_archive_log_2中的service是指tnsnames.ora中的net_service_name,并不是standby庫中的service_name

在主庫中修改log_archive_log_2參數：

alter system set  log_archive_dest_2='service=orcl_standby async

  valid_for=(online_logfiles,primary_role)

  db_unique_name=orcl_dg'  scope=spfile;

修改備庫：

alter system set log_archive_dest_2='service=orcl_primary async

 valid_for=(online_logfiles,primary_role) 

db_unique_name=orcl_pm' scope=spfile;

重啟庫和監聽OK!

以前配置時，service_name,unique_name,tnsname 都是一樣，沒有清楚的理解這里log_archive_dest_2中的service的概念。