Oracle Goldengate Veridata- How to establish Veridata connection with Database

Hi everyone. Welcome to our Oracle Golden Gate very data training. My name is Ashish Ashish Agarwal and I will be delivering this lecture to you. So before commencing the session just a quick round of update regarding my YouTube channel, which is youtube.com slash Ashish Agarwal underscore GG. So if you haven't visited the channel, I would request you to do visit the channel and do like share and subscribe the channel. The channel is dedicated to Oracle Golden Gate. Very data with the database. You need to go to connection configuration. Okay, you have to ensure first of all that the agent on the database server should be up and running. If your agent is down you won't be able to how do you check whether your agent is running or not? So go to G get one. So this is very data agent directory. So we'll do dot slash agent dot sh space run. So this shows that it will give you the message that address already in use that means it is already. Do you see address already? So it means it is already running. So this is just the notification. Okay, it is okay. So now another thing what we did was to verify whether it is running or not. You can do next that hyphen a n grep port number 7 8 5 0. So this will show you that listening is happening on this. So this port is working. Okay, if you have to stop you can do agent dot sh space stop. So once this is stopped then try to do next that see now it is not listening. It says time we you see the status has changed. So it's no longer so once you start it will start. So let let us keep it stop for now just for your reference. Now, we are into connection configuration in very day. So click a new button. So name we are going to connect this to PDB one database. So as I told you very data agent needs to be connected to each database on each database over the agent should be installed. So in our case, we have got two servers one is G gate one in G gate one. We have a database pluggable database name as PDB one and there we have application schemas created and tables created will discuss about that. Same way we have got G gate to server where we have got the database pluggable database name is PDB. So on both these servers, we have got agent installed in agent. Okay. So first of all, we'll create the connection with PDB one database. So guys for your reference today, we are following activity guide number 5 or create data source connection. So whatever we are doing it is mentioned in this activity guide number 5. Okay, so we'll give the name as PDB one underscore connection. You can give any name if you want to give any description. So this connection establishes connectivity with PDB one database on G gate one server. Okay, click on next. Okay. Now the next is it will ask for host name or IP address to which this will establish the connectivity. So the host name is 192. You can provide IP address or hosting whatever way you want. I prefer to provide IP address. So I just I always provide it. So the PDB one reside on this particular server and the port number 7 8 5 0 can anyone tell me how come this port number provided 7 8 5 0? How come it got this particular port number anyone? How did how did we get this port number 7 8 5 0? It's a different one. Now, what do you mean by default? So which port number it is? What is running on this particular? Why have we given so does the database run on this port or what runs on this port very data? Maybe it's a manager for does Golden Gate very data uses manager they have we discussed anything about that. Okay. Sorry, this agent is running exactly correct. So agent is running on this particular port, right? And where have we defined that this agent should be running on this port? Which file it is agent dot properties file, right? So if you do agent dot properties file, we have defined here that it should run on port 7 8 5 0 the the agent on G get here we have provided server port is 7 8 5 0. Right? So if you have defined any other port you can provide any other port as well just ensure that you update or provide connection. Okay, the port number is 7 8 5 0 now data source type. So this very data connects to different sources like it can establish your connectivity to Oracle NSK SQL Server cyber ZB to inform its hive Teradata. So this is Oracle. So we'll click on that now verify this connection. So so it says cannot connect to this. Port 7 8 5 0 connection refused. Why see what happens is as I told you so how the Golden Gate establishes connectivity is first of all very data server has to establish connectivity with database now to establish the connectivity with the database. It has to establish connectivity. It establishes connectivity with the database using very data agent. So you can consider very data agent is middleman between very data server and database. So very data server will establish the connectivity with very data agent and which term will help in establishing connectivity with the database. So you have to ensure that agent is running on the this particular server. So let us see if agent is running now agent is not running. So let us start agent. How do we start? We'll run agent dot SH space star. Okay now verify whether it is running see now listening is happening. So now what we'll do will verify it now. See this data source type verify. So once the agent is running so the connection has successfully we've been established with the agent. So this connection right now is not established with the database. Remember it is established with agent. Okay. Now this agent has details about the database. You see we have provided the database URL in this agent dot properties file. So now once we click on next on the next screen now, you need to provide the username and password for the database. So basically now Golden Gate very data is a product which is external to the database right now. It is it if it has to establish connectivity with the database. How can I do that? It requires some username and password for right that username that schema should be present in database. So at the database in database, what we need to do is we need to create one user one schema basically. Okay. So what is the schema name? Let me verify from activity guide. We can we can provide the same connection. Okay, so we are going to create the user with this name very data and welcome. So, how do we do it? So what we need to do is we need to create the user to go above we have must have done it. Okay here you see so we have connected to the database. We created the user very data. So this username can be anything except we have to provide the privilege. You can either provides DBA privilege. It's not mandatory but select catalog role and select all table privilege should be provided. Okay, so we won't provide DBA. That's fine. So I'll connect to database now. I'm connecting a sysdb then connecting to pluggable alter session set container equal to PDD one. Now here I'll create create user very data identified by welcome one and will grant we won't grant DBA will grant select catalog role and select any table. Very data. Okay, so why we require it because see when so say for example, you have application schema is GG training. So very data should be able to read the data because it compares the data between source and target, right? So it should be able to read the data from the source table first of all. So, how can it read it if it has selected? So we'll provide this user now the user is very data. And password we are creating is welcome. That's the connection. If everything is successful. It says very data could not connect to database shared memory real does not exist. Let us try to connect first of all, what we'll do SQL plus very data. Slash welcome one at the rate PDD one. Oh, we need to grant create session as well. So SQL plus slash and assess the altered session. That's been taken away to PDD one. Grant create session to very data. Now, let us try again. So I like this SQL plus. It says shared memory real does not exist. No such file or directory basically this message come comes if user does not exist. Right? Let us see one second. We are connecting to this. IP address everything is fine. 192 168 56 50. 7 8 5 0 this is very fine. Next. Okay, let I assume there is one thing. Let me try that. So what I'll do I'll do vi agent dot properties. Yeah, let me use flash instead of Poland. I tell you what I'm doing if it is successful. Okay, so let us test connection. Now you see it is successful. Okay. Same thing what I was assuming is fine. Okay, so I'll tell you what I did. This is fine. And if I test connection now now, it's okay. Okay. I'll tell you what I did. Okay. So in agent dot properties file, I was assuming this. This is good to know. So the thing is if you see agent dot properties file, so it says that database URL if you check this file, it says like the SID or service name give it like cooler. Okay, but the thing is we have to use if we are using service name if we are using SID, so we have to give like slash if we give service name, then it's okay. We can give colon but if we are giving SID we have to give it like backslash in that scenario. Okay earlier. We had given it as a colon for it wasn't working. So there is one solution to it. If you want to use or if you want to use SID as well as a with colon there is a parameter in listener dot or a file you can use you use SID as service name. So SID will also can SID can also be used as a service name in that scenario. So the thing is just for your understanding, this is good to know in this file. It is mentioned that you have to give it as colon and then this database name. So basically this is if you want to give colon you have to give service name but because we were using SID this pdb1 is SID. So we have to use slash if you want to use SID as service name then in listener dot or a file. There is a parameter which you need to use which is use SID as service name then here even if you use SID it will use okay. Okay, so just to let you know, there are two solutions for the error which we are getting either if you want to use colon then use service name. That is one option second option is if you want to use SID use this slash as we have. Okay, so now our very data server is successfully being able to establish connectivity with data database connection is successful. Now, you have another thing data source connection for repair. So as you know, very data not only compares the data. It also has capability to repair the discrepancies which it has either so for repair purpose, you need to grant separate privileges and you need to have you can use the same user but you need to grant separate separate privilege, but we are not discussing about repair right now. We are only creating regular user for verifying the data for comparison repair part. We can discuss late during later part. Okay, once you are done click on so your connection is success. So very data server is now able to create or establish connectivity with the database. Okay. Now we have established the connection with G gate 1 now on G gate 2 as well our agent is right. So let us verify where on which port it is running on. So on this G gate 2 we have PDB to database. So now let us verify on which port very data agent so on 7 8 5 0 it's not running on 7 8 5 1 it is so you can verify by visiting the agent dot properties file. So see here if you check this on G gate 2 agent is running on port number 7 8 5 2 and here will also provide this URLF. So if you want I can show one more thing. Okay, let us do let us use service. So, let me check so let us use service name as so let us use slash this is G. So what we'll do we'll do agent dot properties. Here what we'll do is we'll change the URL and you slash instead of it in the URL. Now you would say how did I identify this error? So the thing is experience. I have failed this issue multiple times. So I was wondering like this is the latest very data version 12 dot 2 dot 1 dot 4 we are using so I thought they might have corrected something here, but it's the same thing which we were facing in the previous version. So that's fine. So that's why how I identify so this is good to know if you are facing this issue try to change your URL instead of colon try to use. Okay, so we have edited agent dot properties file. Let us restart the agent. So I stop agent and then I'll start so this is we what we are doing on G gate to server second server. Okay, so agent has been started. So now what we'll do we'll create another connection. So pdb2 connection will do this connects to pdb2 database on G gate to click on next. So now it is asking for hostname or IP address the IP address of the server where's G get to is running 192.68.56.51. It is running on different and the port number is 7851 data source type is over verify. Yeah data source type verified click on next. We need to provide username and password. So what we'll do we'll create one user in the database. So this is pdb2 pluggable database alter session that container equal to pdb2. So here we'll create user very data identified by welcome one. Grant DB not DBA select catalog role. Select any table comma create session to very data where is it under 50 year? Grand succeed. So now what we'll do we'll go to again data source connection here will provide very data. Underscore TRG. So what we have done so far this very data server running on IP address ending in 50 is connecting to agent running on 51 server. And now we are establishing connectivity with the so now you see this our source connection was successful because it is successful now you can click on so your data source connection is now successfully created on both source and target. So basically now very data is now successfully able to connect to both source as well as target. So this finishes your create data source connection on source and time.