Monday, June 24, 2024

Grant Access / Assign Users to OIC Integration Instance

Grant Access / Assign Users to OIC Integration Instance


OIC has a different set of roles that can be assigned to the users.
Here is the list of roles


Pre-requisites.
- Must have user under Identity ->  Domains -> <Your Domain Name> -> Users
Search if the required user is created or not. If not, Create a user first.

Now, Steps to assign different roles to OIC instances so that users can access and develop the services.

Step 1 : Go to Identity ->  Domains -> <Your Domain Name> -> Oracle Cloud Services



Step 2 : Search for the instance that is created and select and click on the instance.
Here instance name includes your tenancy name as well.



Step 3 : Under Resources, Click on Application Roles tab located in left side of the page




Step 4 : Now you can see all the application roles. Click on the down arrow key on the right side of the ServiceAdministrator role.


Step 5 : Click on the "Manage" link of assigned users. You can see assigned users.



Step 6 : Now click on "+ Show available users link". It will show all the users created in your tenancy. Search user you want to assign Service Administrator role.



Step 7 : Select the required user and click on the "Assign" button. and close the window.



Similarly you can assign a user to multiple roles.

Also you can bulk upload users and groups.

Click on the "Import" button and download the sample.csv file.



Edit file with required information and upload it.




Thanks & Regards,
Chandan Tanwani

Sunday, June 16, 2024

My First Oracle ACE Award

 My First Oracle ACE Award

🎉 Thrilled to Share Exciting News 🎉

I am honored and proud to announce that I have received the Oracle ACE Associate award 🎁. This recognition is a testament to my contributions to the Oracle community in India and my advocacy of Oracle technology. This is my first Oracle ACE recognition, and I am incredibly excited about it.




I have been a part of the All India Oracle User Group (AIOUG) for quite a long time. AIOUG has provided me with a platform to share my knowledge as a speaker and to volunteer in the Oracle community. I have also had the opportunity to speak in the APACOUC and AUSOUG online webinar series.

I want to express my sincere gratitude to Sai Penumuru, who has always inspired, mentored, and guided me at every stage of my life. Your advice has been invaluable, and I am so grateful to have you as a mentor. Thank you for believing in me and helping me grow. 

Special thanks to Sandesh Rao, Connor McDonald, and Basheer Khan for their continuous guidance and support throughout my journey.

Many thanks to Jennifer Nicholson and Oana-Aurelia Bonu for their support and for recognizing my contributions to the Oracle community.

Please visit my profile on the Oracle ACE Portal.


Thanks & Regards,
Chandan Tanwani

Saturday, May 4, 2024

Oracle Database 23ai - What is coming and Where to find related Documents?

Oracle Database 23ai - What is coming and Where to find related Documents?

You might know that last year on 3rd April, 2023, Oracle announced the Oracle Database 23c and FREE version of 23c that was called Developer Release. Oracle has introduce many features, I can say 300+ features for DBAs and Developers that enhance productivity with Oracle Database.

As Oracle mentioned in their documents, the Oracle database has new functionality that allows you to access the power of LLMs in conjunction with your databases without compromising the security of your data. You can now “chat” in natural language with your Oracle Database (or via APIs) to get it to answer complex queries using the context of your data.

Expanding the Oracle Database’s AI capabilities further, Oracle added support for AI vector search within the database. Vectors are used in AI to represent the content of unstructured data such as images, documents, and videos. In Oracle Database 23c a native Vector data type was added along with optimized vector similarity search indexing to greatly enhance query performance. New SQL functions and operators will make it easier to create, manipulate, and query vectors in combination with  other data models already supported in Oracle Database, including JSON, Spatial, Graph, etc.

This AI feature is a game changer. I think Oracle has improved a lot in In-Database AI and ML last year. All their innovations are now part of Oracle Database 23 version. Due to this change in database, Oracle renamed the database version from 23c to 23ai. You can say, 23ai = AI + Converged Data

On 2nd May-2024, Oracle announced the Oracle Database 23ai Enterprise Grade Database FREE release. The resource limits for Oracle Database Free are up to 2 CPUs for foreground processes, 2 GB of RAM and 12 GB of user data on disk. It is packaged for ease of use and simple download.

Where to find Oracle Database 23ai info?
You can find all new features resource link of Oracle Database 23ai under one roof with below link,

Get Oracle Database 23ai FREE
Must remember that the 23c is now 23ai. Oracle will update all the links and content from 23c to 23ai. 
This will take sometime, meanwhile you will see 23c and 23ai both the keywords everywhere.

Download Oracle Database 23ai FREE

Database 23ai: Feature Highlights


Thanks & Regards,
Chandan Tanwani

Friday, March 22, 2024

Connect ATP Private EndPoint Database via Public Load Balancer using SQL Developer

Connect ATP Private EndPoint Database via Public Load Balancer 

In my previous post, we have seen How to create ATP Database with Private Endpoint aka with private dedicated IP of ATP database. Click here to refer previous post.

Now, in this post we will see how we can connect an ATP database (created with a private endpoint) via public load balancer.

Click on Networking -> Load balancer -> load balancer
Click on "Create load balancer" button

  • Provide Load balancer name = CBTPubLB
  • Choose visibility type = Public
  • Assign a public IP address = Ephemeral IP Address (This will automatically assign a public IP address from the available IP address from the pool.)


  • Choose VCN = cbtvcn (Your VCN)
  • Choose subnet = public subnet-cbtvcn (Your public subnet)
Now Click Next.


Note that in my case I will add backends after creating the Load balancer. If you want to add a backend now, you can.

Now, specify the health check policy
  • Choose Protocol as TCP from the dropdown menu.
  • Choose Port = 1522
Leave other fields as default

In advance tab. I am not changing anything. Keeping all the fields as default values.

below screenshots are just for your references purpose.




Click Next to configure Listener
  • Provide proper Listener name, In my case, it is "DB_LSNR"
  • Specify the type of traffic as TCP
  • Specify the port as 1522
I am not providing any SSL certificate here.


Click Next 

Now, You can enable the Error log and access log,
Here in my case I have enabled only Error log, please refer below screenshot


Click on Submit button to create load balancer

It will take a couple of minutes to create a load balancer.



Now you can see the load balancer is created but the health is Critical. This is due to backendset. Yet we have not configured the backend set.

Now Click on the Backend sets -> Backends -> Add backends

Provide the IP address of your ATP database which is shown under the Network section of your ATP detail page. Please refer to the screenshot below for your reference.


Now Click on CBT_BS bankend set. 
Here you can see number of backends are zeero.



Click on Add backends button

Provide IP address and port number.

Click Add button


Click on close button to close the window.


Once added, health will be in critical state, It will take a couple of minutes to change the health status.


You can see backend is added but health is in Critical state.

You can see more details by moving your mouse pointer on Critical text.

Must remember that, you need to configure VCN ingress rule for your private and public subnet.

In my example, refer below screenshot of default security list - public subnet and Private security list for private subnet.





Once done, check the status of your public load balancer. 
Backend health must be OK. This means that the Load balancer is able to ping the ATP database.

Now you can see the backend health in Green Icon as OK.

After adding ingress rule it will take 1-2 minutes to reflect in load balancer to make the health OK. 


You can see the overall load balancer health is now also in Green- OK



Now, Click on the Autonomous database CBTATP1 -> click on the "Database connection" button.

Click on Download wallet button -> provide the wallet password.


It will download the zip file. Unzip the wallet.zip file.
Open the tnsnames.ora file
Replace your ATP host name with the Public Load Balancer IP.

refer below screenshot for your reference.

Save tnsnames.ora file


Zip all the files again and give another name of zip file. Here in my case it is CBTATP_LB_IP.zip.

Open the SQL developer -> click on new connection
  • Provide connection name - CBTATP1DB
  • username = admin
  • password = <admin password> (This is the same password which you given during creation of your ATP database)
  • choose connection Type= Cloud Wallet
Browse and select the .zip file which you created just now.

click on the test button to test.


This is it.


Hope this article will help you.
Thanks for reading.


Thanks & Regards,
Chandan Tanwani


Saturday, March 9, 2024

How to connect to ATP Database from SQL Developer

How to connect to ATP Database from SQL Developer

In this blog, I am covering how to connect to an ATP database from SQL developer. Many times we face difficulties connecting to ATP databases from SQL developer; this is because we miss the prerequisite of connecting databases. In this blog I am going to highlight the same.

In this blog I will cover specifically the ATP database which created where "access type: 


Let see the first our Network access type is "Allow secure access from specified IPs and VCNs"


Now, the prerequisite is to configure the access control list. This is a very important an crucial part.

Click on Edit link under Network -> Access Control List. It will open a Pop-up window to add an IP address.
Here in my case I have added my laptop IP address from where I will connect to my ATP database.
Please refer to the screenshot below for your reference.

Click on Save button.


Once you add your specific IP address. Now let's look at the default security list of your VCN.
In my case this is the default security list which was configured during the creation of my VCN.
Below screenshot is just for your reference.


Now, Download the wallet file.
Click on the "Database connection" button highlighted in the screenshot below.


Click on "Download Wallet" button.


Provide wallet password. Must note this password somewhere in your notepad, This will be used whenever you will connect to the ATP database using a wallet file.


Click on the Download button to download the wallet file.

Till this point, we have completed the prerequisites. Now Open your SQL developer.

Click on "New Connection"



Fist give the connection Name,
  • Provide Username as admin
  • Password as < password > (This is the same password that you had given at the time of creating ATP database.)
  • Connection typ = Cloud Wallet 
  • Configuration File = < Path of your Cloud Wallet that you have just downloaded from ATP database connection page. 
  • Service = Choose which service type you want to select. In my case it is cbtatp_tp
Now, click on "Test" button.

Hope this article will help you.
Thanks for reading.


Thanks & Regards,
Chandan Tanwani

Thursday, March 7, 2024

Create Autonomous Database (ATP) with Priavte EndPoint

Create Autonomous Database (ATP) with Private EndPoint

As you know ATP database is a PaaS service, you will only get the hostname of the ATP database. Whatif we need the IP address of the ATP database. One scenario for this requirement is to set ATP as a backend set in a public load balancer. How do we keep the ATP database as a backend set in a public load balancer? It is not possible without Private endpoint IP as on date. You must require a private endpoint IP address to add as a backend set of load balancer.

I will cover the following things in my blog,
1) Create ATP database with private endpoint.
2) Create Public Load Balancer and Configure with ATP database as Backend set.
3) Configure Networking security rules to access ATP via Load Balancer.

In this blog, I am covering, "Create ATP database with private endpoint." 1st point only. 2nd and 3rd point will be seen in separate blogs articles respectively. 

Let's start,
Click on Create Autonomous Database button.


Enter Compartment Name = cbtrm

Provide Display Name = CBTATP

Database Name = CBTATP

You can choose different display name and database name. I am giving the same for both the fields for simplicity purposes.


As of date there is only one option available to choose for the database version i.e. 19c
Here minimum ECPU count is 2 and storage is 1024 gb.

Backup retention you can change. But I kept the default one.



Provide password for admin user.

And now in the Network access section. Choose "Private endpoint access only", select your VCN and Private subnet from the dropdown list.



Now, choose the license option.

I kept other things as default settings. Nothing changed. Below screenshots are for your reference only.





This is it. 
Click on "Create Autonomous Database" button


It will take 2-3 minutes to create Autonomous database.



You can see the ATP database is nor provisioned. 
You should note couple of things, like, Database Name, Resource aloocation, Network (here you can see the private endpoint IP address), backup etc.

In next section we will see how to connect to the ATP database using cloud shell and SQL Developer.

Hope this article will help you.



Thanks & Regards,
Chandan Tanwani