Location>code7788 >text

PanWeiDB] Oracle (Transparent Gateway) Access to PanWeiDB

Popularity：339 ℃/2024-10-23 14:52:19

PanWeiDB is a self-developed database product built by China Mobile based on China's local open source database, openGauss, which is mainly oriented to ICT infrastructure. It is characterized by high performance, high reliability, high security and high compatibility, and can support centralized, distributed, cloud-native, all-in-one and other application scenarios. Currently, Granville Database has been deployed in many provinces (autonomous regions and municipalities) and specialized companies of China Mobile. In the future, Granville Database plans to continue to strengthen in-depth independent research and development in cloud native, Serverless, intelligent operation and maintenance, ecological compatibility, etc., to further build data management solutions for the whole industry. As Granville Database continues to penetrate into the deep water of database localization replacement, more and more core business systems are undergoing code transformation in preparation for going live with Granville Database. Due to historical reasons, program developers are very keen to use database link to access other Oracle instances (B, C, D...) from Oracle instance A to complete data operations. However, with the migration to Granville Database, data interaction between heterogeneous databases has become an issue that cannot be ignored.

This article describes how the Jiangxi Mobile Company, from the Oracle database using a transparent gateway to access Granville database data.

Unless otherwise specified, they are executed as the oracle user.

Test Environment Information

Oracle 19C         -- 192.168.11.151
PanWeiDB 3.0.0     -- 192.168.0.120

1, upload odbc driver package to oracle server and decompress it

cd /home/oracle
mkdir pwodbc
cd pwodbc
tar xf  PanWeiDB-V2.0-ODBC-2.0.3_B01.

2. Configure the odbc instance driver

cat<<+>/home/oracle/pwodbc/unixODBC-2.3.9/etc/
[PWMPP]
Driver64=/home/oracle/pwodbc/odbc/lib/
setup=/home/oracle/pwodbc/odbc/lib/
+

3, configure odbc client connection Granville data information

cat <<+>/home/oracle/pwodbc/unixODBC-2.3.9/etc/
[pwdb]
Description=Test
Driver=PWMPP
Servername=192.168.0.120
Database=testdb
Username=tpcc
Password=tpcc@123
Port=17700
Sslmode=allow
+

4. Configure Oracle and grid user environment variables to use the specified odbc driver

cat <<+>>~/.bash_profile
export LD_LIBRARY_PATH=/home/oracle/pwodbc/lib:/home/oracle/pwodbc/odbc/lib:/home/oracle/pwodbc/unixODBC-2.3.9/lib:$LD_LIBRARY_PATH
export ODBCSYSINI=/home/oracle/pwodbc/unixODBC-2.3.9/etc
export ODBCINI=/home/oracle/pwodbc/unixODBC-2.3.9/etc/
+
source ~/.bash_profile

5, for the driver path to add appropriate permissions to ensure that grid and oracle have access to permissions

chmod 755 -R /home/oracle/pwodbc

6, verify that the odbc driver can normally access the PanWeiDB library, Oracle and grid users are verified!

odbcinst  -j
odbcinst -q -d
isql -v pwdb

The results are similar to the following:

[oracle@ora19c1 ~]$ isql -v pwdb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

7, Oracle Configuration Transparent Gateway service, here to PanWeiDB for sid example
Configure oracle user hs, configure init<sid>.ora in $ORACLE_HOME/hs/admin/init<sid>.ora

cat <<+>$ORACLE_HOME/hs/admin/
HS_FDS_CONNECT_INFO = pwdb
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME = /home/oracle/pwodbc/unixODBC-2.3.9/lib/
HS_NLS_NCHAR = UCS2
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
set LD_LIBRARY_PATH=/home/oracle/pwodbc/lib:/home/oracle/pwodbc/odbc/lib:/home/oracle/pwodbc/unixODBC-2.3.9/lib
set ODBCSYSINI = /home/oracle/pwodbc/unixODBC-2.3.9/etc
+

8. Increase listening

grid:  $ORACLE_HOME/network/admin/  rise 
SID_LIST_LISTENER=
(SID_LIST=
   (SID_DESC=
     (SID_NAME=pwdb)
     (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
     (PROGRAM=dg4odbc)
     (ENVS="LD_LIBRARY_PATH=/home/oracle/pwodbc/lib:/home/oracle/pwodbc/odbc/lib:/home/oracle/pwodbc/unixODBC-2.3.9/lib")
   )
)

Reload Listening

lsnrctl reload
lsnrctl status

9、Configure tns
oracle: $ORACLE_HOME/network/admin/ add

pwdb =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.151)(PORT=1521))
    (CONNECT_DATA=(SID=pwdb))
    (HS=OK)
  )
tnsping pwdb

10. Create dblink validation

create database link  pg_link connect to "tpcc" identified by "tpcc@123" using 'pwdb';
select * from "t1"@pg_link ;

The result is similar to the following

SQL> select * from "t1"@pg_link ;
	id
----------
	 1
SQL>

Attention:
To access PanWeiDB's database tables you need to add adouble quote(because in oracle it will be converted to uppercase by default, while PanWeiDB defaults to lowercase, which will cause the table to be inaccessible)

<Previous Next>