Protocol (B28317-02/4)
| Protocol | Parameter | Description | |
|---|---|---|---|
| IPC | PROTOCOL | Specify ipc as the value. | A protocol used by client applications that resides on the same node as the listener to communicate with the database. IPC can provide a faster local connection than TCP/IP. |
| KEY | Specify a unique name for the service. Oracle recommends using the service name or the Oracle System Identifier (SID) of the service. | ||
| Example: (PROTOCOL=ipc)(KEY=sales) | |||
| TCP/IP | HOST | Specify the host name or IP address of the computer. | |
| PORT | Specify the listening port number. | ||
| Example:(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521) |
lsnrctl
| COMMAND | DESCRIPTION |
|---|---|
| show displaymode | |
| set displaymode compat/normal/verbose/raw | |
| services | information about the database services, instances, and service handlers (dispatchers and dedicated servers) to which the listener forwards client connection requests. |
| lsnrctl [command] [listener_name] | |
| lsnrctl help | |
| lsnrctl help [command] |
Misc
- Allways verify service which status is READY and then at client side, put SERVICE_NAME value in tnsnames.ora
MsSQL (Database Gateway)
- http://docs.oracle.com/cd/B28359\_01/gateways.111/b31043/conf\_sql.htm#CIHGDGFC
- Install gateway from database package ../gateways/runInstaller
-
edit $ORACLE_HOME/dg4msql/admin/initdg4msql.ora
- It is possible to create many files ex.: initdg4msql.ora, initdg4msql1.ora, initdg4msql2.ora for many connections
-
HS params: http://docs.oracle.com/cd/B28359\_01/gateways.111/e10311/apd.htm
HS_FDS_CONNECT_INFO=[MsSQL_hostname]:1433//[db_name] HS_FDS_RECOVERY_ACCOUNT=RECOVER MsSQL account HS_FDS_RECOVERY_PWD=RECOVER MsSQL account password HS_FDS_TRANSACTION_LOG= MsSQL table name for transaction logs HS_FDS_TRACE_LEVEL=OFF/ON/DEBUG
- MsSQL - create RECOVER login
- MsSQL - create RECOVER schema
-
MsSQL - create RECOVER user
- Owned Schemas: RECOVER
- Database role membership: db_datareader, db_datawriter, db_ddladmin
-
create MsSQL table HS_TRANSACTION_LOG at RECOVER schema, which comprise columns below:
(if table name is different it must be set parameter HS_FDS_TRANSACTION_LOG at initdg4msql.ora)with columns:
- GLOBAL_TRAN_ID char(64) not null
- TRAN_COMMENT char(255)
add ALL role for above table to PUBLIC
For automatization above steps use script $ORACLE_HOME/dg4msql/admin/dg4msql_tx.sql as RECOVER user on MsSQL
- isql -U[recover_account] -P[recover_account_pass] [-Sserver] -i[dg4msql_tx.sql]
- osql -U[recover_account] -P[revocer_accout_pass] -d[dbname] -i[script]
- MsSQL do not accept semicolon at the end of line, at last line put "go"
-
run script for superadmin tranlation views:
- create view dbo.ALL_CONS_COLUMNS_vw and grant SELECT to PUBLIC
- create view dbo.ALL_IND_COLUMNS_vw and grant SELECT to PUBLIC
- create view dbo.USER_CONS_COLUMNS_vw and grant SELECT to PUBLIC
- create view dbo.USER_IND_COLUMNS_vw and grant SELECT to PUBLIC
$ORACLE_HOME/dg4msql/admin/dg4msql_cvw.sql isql -U[system_admin_user] -P[system_admin_pass] [-Sserver] [-ddatabase] -e -i dg4msql_cvw.sql
-
edit listener.ora
SID_LIST_MSSQL= (SID_LIST=(SID_DESC=(SID_NAME=[gateway_sid])(ORACLE_HOME=[oracle_home_path])(PROGRAM=dg4msql))) MSSQL= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=[local_hostname])(PORT=[port#])))
-
edit tnsnames.ora
- for CONNECT_DATA=(SID=[gateway_sid]) # gateway_sid must be the same as listener.ora SID
- add parameter HS=OK (Oracle Heterogeneous Service)
[ALIAS]= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=[local_hostname])(PORT=[port#])) (CONNECT_DATA=(SID=[gateway_sid])) (HS=OK))
ERROR
ORA-28545
[ symptom ]
ORA-28545: error diagnosed by Net8 when connectiong to an agent error Unable to retrieve text of NETWORK/NCR message 65535 ORA-02063: preceding 2 lines from...
[ solution ]
- listener is not active
ORA-28513
[ symptom ]
ORA-28545: error diagnosed by Net8 when connecting to an agent Unable to retrieve text of NETWORK/NCR message 65535 ORA-02063: preceding 2 lines from...
[ solution ]
- check file name $ORACLE_HOME/dg4msql/admin/initdg4…
ERROR
| ORA-01150 | check listener.ora |
| ORA-12154 | check tnsnames.ora, client does not even try to connect to remote server |
| Linux Error: 110: Connection timed out dla lsnrctl status | compare /etc/hosts via listener.ora |
| ORA-12560 | for winshit check service at services.msc |
| ORA-12514 TNS:listener does not currently know of service requested in connect | check lsnrctl status at server side, care about name of services |
| ORA-12545 client can't connect to server | for cluster check client side /etc/hosts, and put VIP addresses |