Problème avec Database link sous oracle

**shayn** · 15/10/2015, 22h44

Bonjour Chers amis

Je veux configurer un database link sous mes bases Oracle 10g/11g

--Voici le fichier de configuration

Gestion des Bases Oracel DATABASE LINK

@SRV-DC2 : 172.16.0.250 SID=POOLTPV sytem/Password2015
Oracle: 11g Instance: POOLTVP

@IP XP--CMEVoIP: 172.16.0.125 SID=XE sytem/cmevoip
Oracle: 10g Instance: XE

@IP XP--VoIPCME: 172.16.0.100 SID=XE sytem/voipcme
Oracle: 11g Instance: XE

1) Check the connection between SRV-DC2(Server) and Site(XP--CMEVoIP et XP--VoIPCME)

-Attribuer des @IP statiques, désactiver le FireWall et tester(ping)

2) Config listener on Site

a-Config XP-CMEVoIP

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
      (PROGRAM = extproc)
    )
#Ajout de ligne
   (SID_DESC =
      (SID_NAME = xe)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
      )
#Fin de ligne
 
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = cme--voip1)(PORT = 1521))
#Ajout de ligne
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.125)(PORT = 1522))
 
    )
  )
 
DEFAULT_SERVICE_LISTENER = (XE)

--Après l'ajout, taper les commandes suivantes en invite de commande:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
lsnrctl stop   --- Pour arrêter listener
lsnrctl start  --- Pour démarrer listener

b-Config XP-VoIPCME

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
#Ajout de ligne
   (SID_DESC =
      (SID_NAME = xe)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      )
#Fin de ligne
 
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = cme--voip2)(PORT = 1521))
#Ajout de ligne
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.100)(PORT = 1522))
 
    )
  )
 
DEFAULT_SERVICE_LISTENER = (XE)

--Après l'ajout, taper les commandes suivantes en invite de commande:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
lsnrctl stop   --- Pour arrêter listener
lsnrctl start  --- Pour démarrer listener

3) Config service_name(tnsname) on Server

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#Gestion cme--voip1
cme--voip1  =
   (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP )( POST = 172.16.0.125)(PORT  = 1521 ))
         (CONNECT_DATA =
             (SID = XE )
   )
   )
#Gestion cme--voip2
cme--voip1  =
   (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP )( POST = 172.16.0.100)(PORT   = 1522 ))
         (CONNECT_DATA =
             (SID = XE )
   )
   )

4) Create User and Table on Site

Se connecter aux différents Sites et créer les users et Tables

a- Site: CME--VOIP(172.16.0.125)

--Se connecter en tant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
-- USER SQL
CREATE USER shayn IDENTIFIED BY shayn 
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
 
-- QUOTAS
ALTER USER shayn QUOTA UNLIMITED ON USERS;
ALTER USER shayn QUOTA UNLIMITED ON TEMP;
 
-- ROLES
GRANT "RECOVERY_CATALOG_OWNER" TO shayn ;
GRANT "GATHER_SYSTEM_STATISTICS" TO shayn ;
GRANT "DBA" TO shayn ;
GRANT "AQ_ADMINISTRATOR_ROLE" TO shayn ;
GRANT "AUTHENTICATEDUSER" TO shayn ;
GRANT "CONNECT" TO shayn ;
GRANT "XDBWEBSERVICES" TO shayn ;
GRANT "DELETE_CATALOG_ROLE" TO shayn ;
GRANT "OEM_MONITOR" TO shayn ;
GRANT "PLUSTRACE" TO shayn ;
GRANT "LOGSTDBY_ADMINISTRATOR" TO shayn ;
GRANT "EXECUTE_CATALOG_ROLE" TO shayn ;
GRANT "XDBADMIN" TO shayn ;
GRANT "SCHEDULER_ADMIN" TO shayn ;
GRANT "EXP_FULL_DATABASE" TO shayn ;
GRANT "IMP_FULL_DATABASE" TO shayn ;
GRANT "HS_ADMIN_ROLE" TO shayn ;
GRANT "RESOURCE" TO shayn ;
GRANT "AQ_USER_ROLE" TO shayn ;
GRANT "SELECT_CATALOG_ROLE" TO shayn ;
GRANT "CTXAPP" TO shayn ;
GRANT "OEM_ADVISOR" TO shayn ;
 
-- SYSTEM PRIVILEGES
GRANT ALTER TABLESPACE TO shayn ;
GRANT EXECUTE ANY PROCEDURE TO shayn ;
GRANT DROP ANY TRIGGER TO shayn ;
GRANT DROP ANY VIEW TO shayn ;
GRANT CREATE USER TO shayn ;
GRANT FLASHBACK ANY TABLE TO shayn ;
GRANT CREATE ANY OUTLINE TO shayn ;
GRANT DROP ANY CONTEXT TO shayn ;
GRANT FORCE TRANSACTION TO shayn ;
GRANT ALTER ANY SEQUENCE TO shayn ;
GRANT ALTER ANY LIBRARY TO shayn ;
GRANT DROP ANY ROLE TO shayn ;
GRANT CREATE ANY JOB TO shayn ;
GRANT DROP ANY CLUSTER TO shayn ;
GRANT UPDATE ANY TABLE TO shayn ;
GRANT CREATE ANY INDEXTYPE TO shayn ;
GRANT CREATE TRIGGER TO shayn ;
GRANT DROP ANY EVALUATION CONTEXT TO shayn ;
GRANT ADMINISTER SQL TUNING SET TO shayn ;
GRANT EXECUTE ANY PROGRAM TO shayn ;
GRANT DROP PROFILE TO shayn ;
GRANT GRANT ANY PRIVILEGE TO shayn ;
GRANT CREATE TABLESPACE TO shayn ;
GRANT DEBUG CONNECT SESSION TO shayn ;
GRANT DROP ANY DIRECTORY TO shayn ;
GRANT ALTER ANY PROCEDURE TO shayn ;
GRANT MERGE ANY VIEW TO shayn ;
GRANT CREATE ANY EVALUATION CONTEXT TO shayn ;
GRANT ALTER ANY OPERATOR TO shayn ;
GRANT CREATE SEQUENCE TO shayn ;
GRANT ALTER ANY ROLE TO shayn ;
GRANT ON COMMIT REFRESH TO shayn ;
GRANT SELECT ANY SEQUENCE TO shayn ;
GRANT EXECUTE ANY RULE SET TO shayn ;
GRANT ALTER ANY TRIGGER TO shayn ;
GRANT UNDER ANY TABLE TO shayn ;
GRANT DROP ANY SQL PROFILE TO shayn ;
GRANT CREATE ANY SQL PROFILE TO shayn ;
GRANT BACKUP ANY TABLE TO shayn ;
GRANT CREATE SYNONYM TO shayn ;
GRANT ADVISOR TO shayn ;
GRANT ADMINISTER ANY SQL TUNING SET TO shayn ;
GRANT ALTER DATABASE TO shayn ;
GRANT EXECUTE ANY OPERATOR TO shayn ;
GRANT EXECUTE ANY TYPE TO shayn ;
GRANT ALTER PROFILE TO shayn ;
GRANT ALTER ANY TABLE TO shayn ;
GRANT EXECUTE ANY LIBRARY TO shayn ;
GRANT CREATE ANY DIRECTORY TO shayn ;
GRANT CREATE TABLE TO shayn ;
GRANT CREATE VIEW TO shayn ;
GRANT CREATE ANY INDEX TO shayn ;
GRANT BECOME USER TO shayn ;
--GRANT ADMINISTER RESOURCE MANAGER TO shayn ;
GRANT CREATE RULE SET TO shayn ;
GRANT MANAGE TABLESPACE TO shayn ;
GRANT EXEMPT ACCESS POLICY TO shayn ;
GRANT DROP ANY INDEX TO shayn ;
GRANT CREATE ANY CLUSTER TO shayn ;
GRANT CREATE TYPE TO shayn ;
GRANT EXECUTE ANY EVALUATION CONTEXT TO shayn ;
GRANT ALTER RESOURCE COST TO shayn ;
GRANT SELECT ANY TABLE TO shayn ;
GRANT ALTER ANY CLUSTER TO shayn ;
GRANT DROP ROLLBACK SEGMENT TO shayn ;
GRANT ALTER ANY INDEX TO shayn ;
GRANT CREATE PUBLIC SYNONYM TO shayn ;
GRANT CREATE OPERATOR TO shayn ;
GRANT GLOBAL QUERY REWRITE TO shayn ;
GRANT CREATE ANY PROCEDURE TO shayn ;
GRANT CREATE ANY RULE SET TO shayn ;
GRANT DROP ANY INDEXTYPE TO shayn ;
GRANT CREATE ROLE TO shayn ;
GRANT EXECUTE ANY CLASS TO shayn ;
GRANT RESTRICTED SESSION TO shayn ;
GRANT DROP ANY PROCEDURE TO shayn ;
GRANT CREATE ANY MATERIALIZED VIEW TO shayn ;
GRANT SELECT ANY TRANSACTION TO shayn ;
GRANT ALTER USER TO shayn ;
GRANT CREATE ANY CONTEXT TO shayn ;
GRANT ANALYZE ANY DICTIONARY TO shayn ;
GRANT CREATE ANY SYNONYM TO shayn ;
GRANT CREATE EXTERNAL JOB TO shayn ;
GRANT INSERT ANY TABLE TO shayn ;
GRANT CREATE LIBRARY TO shayn ;
GRANT GRANT ANY OBJECT PRIVILEGE TO shayn ;
GRANT CREATE JOB TO shayn ;
GRANT CREATE ANY OPERATOR TO shayn ;
GRANT ENQUEUE ANY QUEUE TO shayn ;
GRANT ALTER ANY OUTLINE TO shayn ;
GRANT CREATE ANY TABLE TO shayn ;
GRANT ALTER ANY RULE TO shayn ;
GRANT CREATE ANY LIBRARY TO shayn ;
GRANT DROP PUBLIC SYNONYM TO shayn ;
GRANT CREATE ANY SEQUENCE TO shayn ;
GRANT ALTER ANY EVALUATION CONTEXT TO shayn ;
GRANT CREATE SESSION TO shayn ;
GRANT QUERY REWRITE TO shayn ;
GRANT DEQUEUE ANY QUEUE TO shayn ;
GRANT EXPORT FULL DATABASE TO shayn ;
GRANT CREATE CLUSTER TO shayn ;
GRANT RESUMABLE TO shayn ;
GRANT CREATE PUBLIC DATABASE LINK TO shayn ;
GRANT FORCE ANY TRANSACTION TO shayn ;
GRANT UNLIMITED TABLESPACE TO shayn ;
GRANT UNDER ANY VIEW TO shayn ;
GRANT DROP ANY OUTLINE TO shayn ;
GRANT CREATE EVALUATION CONTEXT TO shayn ;
GRANT ALTER ANY INDEXTYPE TO shayn ;
GRANT DROP ANY MATERIALIZED VIEW TO shayn ;
GRANT DROP USER TO shayn ;
GRANT DROP ANY OPERATOR TO shayn ;
GRANT ALTER ANY SQL PROFILE TO shayn ;
GRANT CREATE INDEXTYPE TO shayn ;
GRANT ALTER SYSTEM TO shayn ;
GRANT EXECUTE ANY INDEXTYPE TO shayn ;
GRANT DROP ANY SYNONYM TO shayn ;
GRANT GRANT ANY ROLE TO shayn ;
GRANT LOCK ANY TABLE TO shayn ;
GRANT DROP ANY TYPE TO shayn ;
GRANT CREATE MATERIALIZED VIEW TO shayn ;
GRANT DROP ANY RULE SET TO shayn ;
GRANT MANAGE SCHEDULER TO shayn ;
GRANT CHANGE NOTIFICATION TO shayn ;
GRANT CREATE ANY DIMENSION TO shayn ;
GRANT DROP ANY DIMENSION TO shayn ;
GRANT DROP TABLESPACE TO shayn ;
GRANT READ ANY FILE GROUP TO shayn ;
GRANT SELECT ANY DICTIONARY TO shayn ;
GRANT CREATE ANY RULE TO shayn ;
GRANT IMPORT FULL DATABASE TO shayn ;
GRANT DELETE ANY TABLE TO shayn ;
GRANT AUDIT SYSTEM TO shayn ;
GRANT ALTER ANY MATERIALIZED VIEW TO shayn ;
GRANT DEBUG ANY PROCEDURE TO shayn ;
GRANT EXEMPT IDENTITY POLICY TO shayn ;
GRANT ALTER ROLLBACK SEGMENT TO shayn ;
GRANT CREATE RULE TO shayn ;
GRANT CREATE ANY VIEW TO shayn ;
GRANT SYSOPER TO shayn ;
GRANT CREATE PROCEDURE TO shayn ;
GRANT CREATE PROFILE TO shayn ;
GRANT SYSDBA TO shayn ;
GRANT ANALYZE ANY TO shayn ;
GRANT ALTER ANY TYPE TO shayn ;
GRANT UNDER ANY TYPE TO shayn ;
GRANT CREATE ANY TRIGGER TO shayn ;
GRANT MANAGE ANY FILE GROUP TO shayn ;
GRANT COMMENT ANY TABLE TO shayn ;
GRANT ALTER ANY DIMENSION TO shayn ;
GRANT CREATE ANY TYPE TO shayn ;
GRANT DROP ANY RULE TO shayn ;
GRANT DROP PUBLIC DATABASE LINK TO shayn ;
GRANT CREATE DIMENSION TO shayn ;
GRANT CREATE ROLLBACK SEGMENT TO shayn ;
GRANT DROP ANY SEQUENCE TO shayn ;
GRANT ALTER ANY RULE SET TO shayn ;
GRANT CREATE DATABASE LINK TO shayn ;
GRANT DROP ANY TABLE TO shayn ;
GRANT MANAGE ANY QUEUE TO shayn ;
GRANT ALTER SESSION TO shayn ;
GRANT ADMINISTER DATABASE TRIGGER TO shayn ;
GRANT EXECUTE ANY RULE TO shayn ;
GRANT AUDIT ANY TO shayn ;
GRANT DROP ANY LIBRARY TO shayn ;
GRANT MANAGE FILE GROUP TO shayn ;
 
 
b-
-- USER SQL
CREATE USER shannon IDENTIFIED BY shannon 
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
 
-- QUOTAS
 
-- ROLES
GRANT "RECOVERY_CATALOG_OWNER" TO shannon ;
GRANT "GATHER_SYSTEM_STATISTICS" TO shannon ;
GRANT "DBA" TO shannon ;
GRANT "AQ_ADMINISTRATOR_ROLE" TO shannon ;
GRANT "AUTHENTICATEDUSER" TO shannon ;
GRANT "CONNECT" TO shannon ;
GRANT "XDBWEBSERVICES" TO shannon ;
GRANT "DELETE_CATALOG_ROLE" TO shannon ;
GRANT "OEM_MONITOR" TO shannon ;
GRANT "PLUSTRACE" TO shannon ;
GRANT "LOGSTDBY_ADMINISTRATOR" TO shannon ;
GRANT "EXECUTE_CATALOG_ROLE" TO shannon ;
GRANT "XDBADMIN" TO shannon ;
GRANT "SCHEDULER_ADMIN" TO shannon ;
GRANT "EXP_FULL_DATABASE" TO shannon ;
GRANT "IMP_FULL_DATABASE" TO shannon ;
GRANT "HS_ADMIN_ROLE" TO shannon ;
GRANT "RESOURCE" TO shannon ;
GRANT "AQ_USER_ROLE" TO shannon ;
GRANT "SELECT_CATALOG_ROLE" TO shannon ;
GRANT "CTXAPP" TO shannon ;
GRANT "OEM_ADVISOR" TO shannon ;
 
-- SYSTEM PRIVILEGES
GRANT ALTER TABLESPACE TO shannon ;
GRANT EXECUTE ANY PROCEDURE TO shannon ;
GRANT DROP ANY TRIGGER TO shannon ;
GRANT DROP ANY VIEW TO shannon ;
GRANT CREATE USER TO shannon ;
GRANT FLASHBACK ANY TABLE TO shannon ;
GRANT CREATE ANY OUTLINE TO shannon ;
GRANT DROP ANY CONTEXT TO shannon ;
GRANT FORCE TRANSACTION TO shannon ;
GRANT ALTER ANY SEQUENCE TO shannon ;
GRANT ALTER ANY LIBRARY TO shannon ;
GRANT DROP ANY ROLE TO shannon ;
GRANT CREATE ANY JOB TO shannon ;
GRANT DROP ANY CLUSTER TO shannon ;
GRANT UPDATE ANY TABLE TO shannon ;
GRANT CREATE ANY INDEXTYPE TO shannon ;
GRANT CREATE TRIGGER TO shannon ;
GRANT DROP ANY EVALUATION CONTEXT TO shannon ;
GRANT ADMINISTER SQL TUNING SET TO shannon ;
GRANT EXECUTE ANY PROGRAM TO shannon ;
GRANT DROP PROFILE TO shannon ;
GRANT GRANT ANY PRIVILEGE TO shannon ;
GRANT CREATE TABLESPACE TO shannon ;
GRANT DEBUG CONNECT SESSION TO shannon ;
GRANT DROP ANY DIRECTORY TO shannon ;
GRANT ALTER ANY PROCEDURE TO shannon ;
GRANT MERGE ANY VIEW TO shannon ;
GRANT CREATE ANY EVALUATION CONTEXT TO shannon ;
GRANT ALTER ANY OPERATOR TO shannon ;
GRANT CREATE SEQUENCE TO shannon ;
GRANT ALTER ANY ROLE TO shannon ;
GRANT ON COMMIT REFRESH TO shannon ;
GRANT SELECT ANY SEQUENCE TO shannon ;
GRANT EXECUTE ANY RULE SET TO shannon ;
GRANT ALTER ANY TRIGGER TO shannon ;
GRANT UNDER ANY TABLE TO shannon ;
GRANT DROP ANY SQL PROFILE TO shannon ;
GRANT CREATE ANY SQL PROFILE TO shannon ;
GRANT BACKUP ANY TABLE TO shannon ;
GRANT CREATE SYNONYM TO shannon ;
GRANT ADVISOR TO shannon ;
GRANT ADMINISTER ANY SQL TUNING SET TO shannon ;
GRANT ALTER DATABASE TO shannon ;
GRANT EXECUTE ANY OPERATOR TO shannon ;
GRANT EXECUTE ANY TYPE TO shannon ;
GRANT ALTER PROFILE TO shannon ;
GRANT ALTER ANY TABLE TO shannon ;
GRANT EXECUTE ANY LIBRARY TO shannon ;
GRANT CREATE ANY DIRECTORY TO shannon ;
GRANT CREATE TABLE TO shannon ;
GRANT CREATE VIEW TO shannon ;
GRANT CREATE ANY INDEX TO shannon ;
GRANT BECOME USER TO shannon ;
GRANT CREATE RULE SET TO shannon ;
GRANT MANAGE TABLESPACE TO shannon ;
GRANT EXEMPT ACCESS POLICY TO shannon ;
GRANT DROP ANY INDEX TO shannon ;
GRANT CREATE ANY CLUSTER TO shannon ;
GRANT CREATE TYPE TO shannon ;
GRANT EXECUTE ANY EVALUATION CONTEXT TO shannon ;
GRANT ALTER RESOURCE COST TO shannon ;
GRANT SELECT ANY TABLE TO shannon ;
GRANT ALTER ANY CLUSTER TO shannon ;
GRANT DROP ROLLBACK SEGMENT TO shannon ;
GRANT ALTER ANY INDEX TO shannon ;
GRANT CREATE PUBLIC SYNONYM TO shannon ;
GRANT CREATE OPERATOR TO shannon ;
GRANT GLOBAL QUERY REWRITE TO shannon ;
GRANT CREATE ANY PROCEDURE TO shannon ;
GRANT CREATE ANY RULE SET TO shannon ;
GRANT DROP ANY INDEXTYPE TO shannon ;
GRANT CREATE ROLE TO shannon ;
GRANT EXECUTE ANY CLASS TO shannon ;
GRANT RESTRICTED SESSION TO shannon ;
GRANT DROP ANY PROCEDURE TO shannon ;
GRANT CREATE ANY MATERIALIZED VIEW TO shannon ;
GRANT SELECT ANY TRANSACTION TO shannon ;
GRANT ALTER USER TO shannon ;
GRANT CREATE ANY CONTEXT TO shannon ;
GRANT ANALYZE ANY DICTIONARY TO shannon ;
GRANT CREATE ANY SYNONYM TO shannon ;
GRANT CREATE EXTERNAL JOB TO shannon ;
GRANT INSERT ANY TABLE TO shannon ;
GRANT CREATE LIBRARY TO shannon ;
GRANT GRANT ANY OBJECT PRIVILEGE TO shannon ;
GRANT CREATE JOB TO shannon ;
GRANT CREATE ANY OPERATOR TO shannon ;
GRANT ENQUEUE ANY QUEUE TO shannon ;
GRANT ALTER ANY OUTLINE TO shannon ;
GRANT CREATE ANY TABLE TO shannon ;
GRANT ALTER ANY RULE TO shannon ;
GRANT CREATE ANY LIBRARY TO shannon ;
GRANT DROP PUBLIC SYNONYM TO shannon ;
GRANT CREATE ANY SEQUENCE TO shannon ;
GRANT ALTER ANY EVALUATION CONTEXT TO shannon ;
GRANT CREATE SESSION TO shannon ;
GRANT QUERY REWRITE TO shannon ;
GRANT DEQUEUE ANY QUEUE TO shannon ;
GRANT EXPORT FULL DATABASE TO shannon ;
GRANT CREATE CLUSTER TO shannon ;
GRANT RESUMABLE TO shannon ;
GRANT CREATE PUBLIC DATABASE LINK TO shannon ;
GRANT FORCE ANY TRANSACTION TO shannon ;
GRANT UNLIMITED TABLESPACE TO shannon ;
GRANT UNDER ANY VIEW TO shannon ;
GRANT DROP ANY OUTLINE TO shannon ;
GRANT CREATE EVALUATION CONTEXT TO shannon ;
GRANT ALTER ANY INDEXTYPE TO shannon ;
GRANT DROP ANY MATERIALIZED VIEW TO shannon ;
GRANT DROP USER TO shannon ;
GRANT DROP ANY OPERATOR TO shannon ;
GRANT ALTER ANY SQL PROFILE TO shannon ;
GRANT CREATE INDEXTYPE TO shannon ;
GRANT ALTER SYSTEM TO shannon ;
GRANT EXECUTE ANY INDEXTYPE TO shannon ;
GRANT DROP ANY SYNONYM TO shannon ;
GRANT GRANT ANY ROLE TO shannon ;
GRANT LOCK ANY TABLE TO shannon ;
GRANT DROP ANY TYPE TO shannon ;
GRANT CREATE MATERIALIZED VIEW TO shannon ;
GRANT DROP ANY RULE SET TO shannon ;
GRANT MANAGE SCHEDULER TO shannon ;
GRANT CHANGE NOTIFICATION TO shannon ;
GRANT CREATE ANY DIMENSION TO shannon ;
GRANT DROP ANY DIMENSION TO shannon ;
GRANT DROP TABLESPACE TO shannon ;
GRANT READ ANY FILE GROUP TO shannon ;
GRANT SELECT ANY DICTIONARY TO shannon ;
GRANT CREATE ANY RULE TO shannon ;
GRANT IMPORT FULL DATABASE TO shannon ;
GRANT DELETE ANY TABLE TO shannon ;
GRANT AUDIT SYSTEM TO shannon ;
GRANT ALTER ANY MATERIALIZED VIEW TO shannon ;
GRANT DEBUG ANY PROCEDURE TO shannon ;
GRANT EXEMPT IDENTITY POLICY TO shannon ;
GRANT ALTER ROLLBACK SEGMENT TO shannon ;
GRANT CREATE RULE TO shannon ;
GRANT CREATE ANY VIEW TO shannon ;
GRANT SYSOPER TO shannon ;
GRANT CREATE PROCEDURE TO shannon ;
GRANT CREATE PROFILE TO shannon ;
GRANT SYSDBA TO shannon ;
GRANT ANALYZE ANY TO shannon ;
GRANT ALTER ANY TYPE TO shannon ;
GRANT UNDER ANY TYPE TO shannon ;
GRANT CREATE ANY TRIGGER TO shannon ;
GRANT MANAGE ANY FILE GROUP TO shannon ;
GRANT COMMENT ANY TABLE TO shannon ;
GRANT ALTER ANY DIMENSION TO shannon ;
GRANT CREATE ANY TYPE TO shannon ;
GRANT DROP ANY RULE TO shannon ;
GRANT DROP PUBLIC DATABASE LINK TO shannon ;
GRANT CREATE DIMENSION TO shannon ;
GRANT CREATE ROLLBACK SEGMENT TO shannon ;
GRANT DROP ANY SEQUENCE TO shannon ;
GRANT ALTER ANY RULE SET TO shannon ;
GRANT CREATE DATABASE LINK TO shannon ;
GRANT DROP ANY TABLE TO shannon ;
GRANT MANAGE ANY QUEUE TO shannon ;
GRANT ALTER SESSION TO shannon ;
GRANT ADMINISTER DATABASE TRIGGER TO shannon ;
GRANT EXECUTE ANY RULE TO shannon ;
GRANT AUDIT ANY TO shannon ;
GRANT DROP ANY LIBRARY TO shannon ;
GRANT MANAGE FILE GROUP TO shannon ;

5) Create dblink on Server

a) Create VOIP1_SHAYN

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE DATABASE LINK VOIP1_SHAYN 
CONNECT TO shayn IDENTIFIED BY shayn 
USING 'cme-voip1';

b) Create VOIP2_SHANNON

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE DATABASE LINK VOIP2_SHANNON
CONNECT TO shannon IDENTIFIED BY shannon
USING 'cme-voip2';

6) Query using dblink

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT * FROM customer@VOIP2_SHANNON
 
            ---- OU
 
SELECT * FROM dept@VOIP1_SHAYN

Et voici le message d'erreur que j'ai. Tous mes listener sont démarrés et les requêtes fonctionnent parfaitement..
J'attends votre aide

ORA-12505: TNS : le processus d'écoute ne connaît pas actuellement le SID indiqué dans le descripteur de connexion
12505. 00000 - "TNS:listener does not currently know of SID given in connect descriptor"
*Cause: The listener received a request to establish a connection to a
database or other service. The connect descriptor received by the listener
specified a SID for an instance (usually a database instance) that either
has not yet dynamically registered with the listener or has not been
statically configured for the listener. This may be a temporary condition
such as after the listener has started, but before the database instance
has registered with the listener.
*Action:
- Wait a moment and try to connect a second time.
- Check which instances are currently known by the listener by executing:
lsnrctl services <listener name>
- Check that the SID parameter in the connect descriptor specifies
an instance known by the listener.
- Check for an event in the listener.log file.
Erreur à la ligne 1, colonne 24

**Pozzo** · 19/10/2015, 17h51

Bonjour,

As tu tenté de te connecter par sqlplus sur une des bases cible en utilisant sqlnet ?
La partie du tnsnames POST = 172.16.0.100 me fait tiquer.

J'aurais mis HOST = 172.16.0.100

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
sqlplus xx/mdpxxx@cme--voip1

Pozzo

**dell68** · 22/10/2015, 18h01

Dans cette partie :

2) Config listener on Site

a-Config XP-CMEVoIP

Il vau t mieux définir dans HOST=adresse_ip ou bien le nom du serveur !
et vérifie bien la cohérence entre les deux fichiers listener.ora et tnsnames.ora sur les ports et les hosts

Problème avec Database link sous oracle

Administration Oracle

Discussions similaires

Partager

Partager