OAC can seamlessly use the ADW wallet downloaded in the primary region's database after ADW ADG swit

Aaron Zhong-Oracle

Description

Mentioned in Oracle blog announcement, ADW cross-region autonomous data guard can support “After a switchover/failover, the database wallet downloaded in the primary region's database can seamlessly be used in the remote region too, and vice versa.”

I have successfully tested it in SQL Developer. After ADW cross-region ADG switchover, I can still do SQL query with my previous database connection without any changes.

When I tested in OAC, I need to download new wallet file from standby database after the database have been switchover to primary role and update the OAC ADW connection with new wallet file.

Could OAC have the same capability as SQL Developer? 

Use Case and Business Need

Better DR Solution 

More details

Detailed Steps to Reproduce and Test

 

1. Create one new ADW shared instance.

 (For my example, I created one ADW shared instance in Mumbai data center called 'ADGTesting')

 

2. Enable cross-region autonomous data guard (ADG) in ADW OCI console.

 (For my example, the cross-region standby ADW instance was created in Hyderabad data center. The instance name 'ADGTesting_Remote' was given by ADG automatically.)

 

3. Download ADW instance wallet file from primary instance in Mumbai Data Center.

(For my example, the wallet file was Wallet_ADGTesting.zip)

 

4. Create Oracle connection in SQL Developer (21.2.1.204.1703)

 (For my example, I created oracle connection named ADGTesting-SQL)

 

5. In SQL Developer, connect ADW and do one query. 

(For my example, I connected ADGTesting-SQL. My ADW query was 'select * from sh.channels'. The result was returned.)

 

6. Create ADW connection in OAC with the wallet file. 

(For my example, I created OAC ADW connection named ADGTesting-OAC)

 

7. In OAC, create one live table data set from ADW connection. 

(For my example, I created 'ADGTesting-Channels' ADW live data set through ADW connection 'ADGTesting-OAC'. The 'ADGTesting-Channels' pointed to SH (schema).Channels table.)

 

8. In OAC, create data visualization project on this ADW data set. 

(For my example, I created table visualization for 'ADGTesting-Channels' and saved the project named 'ADGTesting-DV'. The data result was returned successfully.)

 

9. In ADW OCI console, click switchover from ADW ADG remote instance. 

(For my example, I clicked switchover on 'ADGTesting_Remote' in in Hyderabad.)

 

10. After the role of ADW standby instance changed to primary and ADW status changed to available. Do the same SQL query in SQL Developer, I can successfully run and get the data result. I don’t need to update previous oracle connection.

 

11. But in OAC, we will get error. 

(For my example when we opened 'ADGTesting-DV' again.)

 

The error message was:

Odbc driver returned an error (SQLExecDirectW).

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.

(HY000)

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS.

(HY000)

State: HY000. Code: 43119. [nQSError: 43119] Query Failed:

(HY000)

State: HY000. Code: 17026. [nQSError: 17026] Oracle Error code: 12514, message: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

at OCI call OCIServerAttach.

(HY000)

State: HY000. Code: 17014. [nQSError: 17014] Could not connect to Oracle database. (HY000)

Original Idea Number: 0c669b78f8