<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Victor Mendo on Medium]]></title>
        <description><![CDATA[Stories by Victor Mendo on Medium]]></description>
        <link>https://medium.com/@vmendo?source=rss-ddfe9cbc5a5d------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*9C053eMv4hXDd36w</url>
            <title>Stories by Victor Mendo on Medium</title>
            <link>https://medium.com/@vmendo?source=rss-ddfe9cbc5a5d------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 06:13:45 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@vmendo/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[From SQL Access to Business Workflows with Oracle Database MCP]]></title>
            <link>https://medium.com/@vmendo/from-sql-access-to-business-workflows-with-oracle-database-mcp-8af24c3531d5?source=rss-ddfe9cbc5a5d------2</link>
            <guid isPermaLink="false">https://medium.com/p/8af24c3531d5</guid>
            <dc:creator><![CDATA[Victor Mendo]]></dc:creator>
            <pubDate>Tue, 13 Oct 2026 10:58:46 GMT</pubDate>
            <atom:updated>2026-10-13T10:58:46.124Z</atom:updated>
            <content:encoded><![CDATA[<p>In my previous article, I connected an AI agent to an HR database through OCI Database Tools MCP Server. OCI IAM identified the user, and Deep Data Security applied the regional rules inside Oracle Database. Alice received Iberia data and Bob received UK data, even when they asked the same question.</p><p><a href="https://medium.com/@vmendo/making-oracle-database-mcp-access-safe-for-business-users-47eef6f6688d">Making Oracle Database MCP Access Safe for Business Users</a></p><p>That demo focused on showing how identity and data security work together. But the prompt mixed business questions with technical details that helped explain the setup. A real HR user should be able to ask for a report in simpler terms, without knowing the database schema or how the agent retrieves the information.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*idQ82TdGZLNTw12rTp13rg.png" /></figure><p>A reader’s comment on the article also made me look more closely at the tools we expose. Database security controls access to the data, but we still need to decide which operations should be available to business users. Giving an agent access to a general SQL tool is a different choice from giving it a small set of tools designed for specific HR tasks.</p><p>There is also a practical cost benefit. With a predefined report, the agent can select the right tool and present its results. It does not need to inspect the schema, understand the table relationships, and generate SQL for every request. This can reduce the number of tool calls and the AI tokens needed to complete the task. I have not measured the savings in this demo, but that difference matters when a workflow is used regularly by many people.</p><p>For this continuation, I wanted to bring the demo closer to that everyday experience. I started with two reports that HR users could discover and request in natural language. Then I added an employee directory and an action to register a training request.</p><p>The same IAM identity and Deep Data Security policies remain part of the design. What changes is how the user works with the data: through familiar reports and actions, with simpler prompts and a clear set of available operations.</p><h3><strong>1. Choosing the operations available to business users</strong></h3><h4><strong>Restricting access to general SQL tools</strong></h4><p>The first change was to define which operations business users could invoke through the MCP server. For this workflow, I wanted to expose specific reports and actions that matched their everyday tasks.</p><p>The built-in SQL toolset contains three tools (sql_run, schema_information, and request_status). I kept sql_run and schema_information available to MCP_Operator and MCP_Administrator, but removed MCP_User from their allowed roles. request_status, which retrieves results from asynchronous calls, was already disabled.</p><p>In this demo, business users have only MCP_User, so they can no longer invoke the built-in SQL tools. The technical tools remain available to operators and administrators. I will create a separate set of business tools in the following steps.</p><p>Each tool has its own allowed roles, so we can also make a new tool available to the technical team first, test it, and grant access to business users when it is ready.</p><p>With those permissions in place, the next step was to create the first two reports.</p><h4><strong>Exposing predefined reports as business tools</strong></h4><p>I began with the same business information used in the first demo. The first report, hr_workforce_snapshot, returns department counts, employee headcount, and average current compensation by business region. The second, hr_promotions_and_transfers, returns recorded employee movements with their dates and the departments involved.</p><p>To expose the workforce report, I opened the Toolsets tab of the MCP server and created a toolset called dds-workforce-tool. I selected Custom SQL tool, entered hr_workforce_snapshot as the tool name, and pasted the predefined query into SQL source.</p><p>The query identifies the current compensation record for each employee and produces the summary by business region. It also returns the authenticated identity for display in the report. There is no condition such as business_region = IBERIA in this query. It works with the rows made accessible by the database policies already configured in the first demo. The same tool can serve different users without a separate query for each region.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-UeWvGdhgD8x19hQNnrgmQ.png" /><figcaption><strong><em>Figure 1 Creating the workforce report as a Custom SQL tool</em></strong></figcaption></figure><p>The <strong>tool description</strong> tells the agent what the report contains and when to use it. That is different from the toolset description, which describes the configuration for administrators. <strong>A clear tool description helps the agent choose the report without having to work out its purpose from the SQL</strong>.</p><h4><strong>Making the reports discoverable</strong></h4><p>I created hr_promotions_and_transfers in a second Custom SQL toolset, dds-employment-events-tool, using the same execution mode and allowed roles. Its predefined query selects promotion and transfer records and joins them to the employees and the source and destination departments. This report also requires no input parameters.</p><p>Once the two tools were available, the user could ask what reports the agent could help with.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/774/1*W62uk4UkXAHIx-bxA3n3xQ.png" /><figcaption><strong><em>Figure 2 The user discovers the two available reports</em></strong></figcaption></figure><p>The answer describes the reports in business terms and gives example questions. The user does not need to know the schema or the names of the MCP tools.</p><h3><strong>2. Asking for the report in natural language</strong></h3><p>With the two reports available, I asked for a combined workforce report: a summary by business region, followed by recorded promotions and transfers. I also asked for the authenticated identity at the top to make it clear whose session was being used.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/839/1*YO5IgUyo53VMyLlVfDu8xQ.png" /><figcaption><strong><em>Figure 3 Alice receives the regional workforce report</em></strong></figcaption></figure><p>Alice receives the Iberia summary with three departments, 30 employees, and an average current compensation of 62,620. The recorded promotions and transfers also belong to her accessible scope. The prompt does not include a regional restriction; the database applies the existing security policies.</p><p>There is also a noticeable difference in response time. <strong>In the previous demo, responses took more than a minute. This interaction completed in about 20 seconds</strong>. The agent can call the predefined reports directly, without first inspecting the schema, working out the table relationships, and generating SQL.</p><p>This is an observation from the demo rather than a controlled performance comparison, but it shows the practical value of providing tools for common business questions. The agent has fewer steps to complete before presenting the answer.</p><p>As an additional check, I asked the agent to run a SQL query directly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/803/1*mKmcrr_cg3x9vQa27Do0yw.png" /><figcaption><strong><em>Figure 4 The business user session cannot invoke the SQL execution tool</em></strong></figcaption></figure><p>The SQL tool call was rejected with “Method not found.” The agent explained that it could not execute the query, and no employee count was returned. Business users can request the configured reports, while the general SQL tools remain available only to operators and administrators.</p><h3><strong>3. Completing the workflow with a write action</strong></h3><p>Reading a report is only part of a business workflow. In this example, the next step is to select an employee and register a training request. This required two additional tools: an employee directory and a write action.</p><p>The directory, hr_list_employees, follows the same Custom SQL pattern as the two reports. It returns employee names, IDs, departments, and business regions, using the existing database policies. There is no need to repeat its configuration here. Its role in the workflow is to give the agent the employee ID required by the write action.</p><p>Names are not always unique. If more than one employee matches a name, the agent needs to ask the user which one they mean. Once the employee has been identified, the user can continue with the name and department in the conversation.</p><h4><strong>Configuring a write action with inputs</strong></h4><p>I created the <em>dds-training-request-tool </em>toolset with the Custom SQL tool type and <em>hr_create_training_request</em> as its tool name.</p><p>This tool receives four inputs as bind variables: employee_id, course_name, justification, and request_key. The form below shows their types and descriptions.</p><p>The agent uses the employee selected from the directory, the course, and the reason given by the user. It generates the submission key. These values are bound to the predefined SQL source rather than inserted into a SQL string generated by the agent.</p><p>The tool description explains what the action does, which inputs it needs, and how to handle a repeated submission. The requester is not an input: the code obtains the authenticated IAM identity from the database session.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/911/1*ODqYkClV86OzzC70ses7og.png" /></figure><p>Before inserting, the action validates its inputs and checks that the employee is accessible. It then saves the request and returns its number, employee, course, status, and requester. The code commits a successful operation and rolls back if it fails.</p><p>The submission key has a unique constraint in the database. If the same key is received again, the code checks that the employee, course, reason, and requester match the original submission. If they do, it returns the existing request. This protects retries of one submission; it does not prevent a new submission with a different key and the same business data.</p><p>The action saves its requests in HR_TRAINING_REQUESTS. I enabled mandatory data grants for this table and extended the security configuration with the following two grants, run as HR_OWNER:</p><pre>CREATE OR REPLACE DATA GRANT hr_training_requests_select<br>  AS SELECT ON hr_training_requests<br>  WHEN SELECT GRANTED ON hr_employees<br>  WHERE hr_training_requests.employee_id = hr_employees.employee_id;<br><br>CREATE OR REPLACE DATA GRANT hr_training_requests_insert<br>  AS INSERT ON hr_training_requests<br>  WHEN SELECT GRANTED ON hr_employees<br>  WHERE hr_training_requests.employee_id = hr_employees.employee_id<br>    AND hr_training_requests.requested_by =<br>        JSON_VALUE(<br>          ORA_END_USER_CONTEXT.AUTHENTICATED_IDENTITY,<br>          &#39;$&#39; RETURNING VARCHAR2(512) ERROR ON ERROR<br>        )<br>    AND hr_training_requests.status = &#39;REQUESTED&#39;;</pre><p>The SELECT grant follows access to the related employee. The INSERT grant uses the same relationship and adds two conditions: the requester must match the authenticated identity, and the request must start with the status REQUESTED.</p><p>This extends the existing regional policy to the new operation. We do not need to repeat the data grants for departments, employees, compensation, or employment events. The new grants build on the employee access already established in the first demo.</p><h3><strong>4. Executing the expanded workflow</strong></h3><h4><strong>Discovering the available reports and actions</strong></h4><p>With the additional tools configured for MCP_User, I asked again what reports and actions were available for the demo. The answer now includes the directory and the training-request action alongside the original reports.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/801/1*erYcpNdStbnw-KjTdYsgiw.png" /><figcaption><strong><em>Figure 6 The user discovers reports and the write action</em></strong></figcaption></figure><h4><strong>Selecting the employee</strong></h4><p>The next question is to list employees with their names, IDs, and departments. The directory returns 30 employees in Iberia for Alice. Its SQL does not contain a regional condition for Alice; it uses the same protected employee and department tables.</p><p>The directory gives the agent the ID it needs for the write action. The user can continue with a name and department in the conversation, instead of copying a numeric ID into every request.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/577/1*TGu05VQ4M5WKZWpl23G02A.png" /><figcaption><strong><em>Figure 7 Alice’s employee directory contains Iberia employees only</em></strong></figcaption></figure><h4><strong>Creating the training request</strong></h4><p>For the write test, I selected Lucas Moreno in Technology Iberia and asked for a request to attend Leadership Fundamentals. The reason was preparation for a future team lead role. The agent resolved that employee to ID 27 from the directory.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/764/1*SoWDRMzvr8bClXFLY7OGIw.png" /><figcaption><strong><em>Figure 8 The action returns a receipt for the saved training request</em></strong></figcaption></figure><p>The receipt shows request number 2, the employee, the course, the status, and Alice’s authenticated identity as requester. I then checked the row directly in SQL Developer Web as HR_OWNER. The saved employee ID, course, reason, requester, and status matched the receipt.</p><p>That database check matters. A well-presented answer is useful for the user, but the demo should also verify that the operation actually saved the intended row.</p><h4><strong>Testing the workflow boundaries</strong></h4><p><strong>Trying an employee outside Alice’s scope</strong></p><p>I then asked Alice’s agent to create the same type of request for employee ID 48, an employee in the UK. Providing an ID explicitly should not allow a user to bypass the regional policy.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/777/1*oXJG68tgyusEPQKe7eRbrw.png" /><figcaption><strong><em>Figure 9 An inaccessible employee does not produce a training request</em></strong></figcaption></figure><p>The action returned “Employee not found or not accessible.” The agent explained that no request had been saved and no request number was returned. It did not reveal that the employee belonged to the UK or attempt another route.</p><p>In this test, Deep Data Security made the employee unavailable to the lookup, and the tool stopped before the INSERT. This demonstrates the access check in the complete workflow. It does not separately prove that this attempt reached and was rejected by the INSERT data grant.</p><p>I would not promise that every LLM explains every failure equally well. Clear tool results help the agent communicate what happened, but the database remains responsible for enforcing access even if an agent misunderstands a result or tries again.</p><p><strong>Handling a repeated submission</strong></p><p>A write action also needs to handle retries. A response can be lost after a transaction succeeds, or an agent may repeat an operation while completing the conversation. I used a unique submission key to identify one logical request.</p><p>For this test, I repeated the same business prompt and reused the original submission key with identical inputs. The action returned the existing request number 2 rather than creating another row.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/777/1*acGhJIJvE2xPvmqPLVVWsQ.png" /><figcaption><strong><em>Figure 10 Repeating the same submission returns the existing request</em></strong></figcaption></figure><p>The scope of this protection is important. It prevents a duplicate for the same submission key. It does not reject every future request with the same employee, course, and reason. A new key represents a new submission. The agent must preserve the key when retrying the original operation.</p><p>I checked the matching requests for employees 27 and 48 in SQL Developer Web as HR_OWNER. The query returned one request for employee 27 and no matching request for employee 48.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/759/1*O5-v2MUf_xNZvnaouifwqQ.png" /><figcaption><strong><em>Figure 11 The final database check confirms one matching saved request</em></strong></figcaption></figure><p>This closes the loop: the valid operation was saved, repeating the same submission did not create a second request, and the attempt involving the inaccessible employee produced no matching row.</p><h3><strong>5. Why the tools we expose matter</strong></h3><p>Access to an MCP server and access to data do not have to mean access to every operation. Business users can be given specific reports and actions while general SQL remains restricted to the technical team. In this demo, that means requesting reports and creating training requests. IAM controls which tools they can use, while Deep Data Security controls the data those operations can access.</p><p>Predefined tools also reduce the work the agent needs to do. It can call a report directly instead of inspecting the schema, understanding relationships, and generating a query. This can reduce token use and response time. We did not measure token savings, but the report interaction took about 20 seconds, compared with more than a minute in the previous demo.</p><p>For the business user, the conversation stays familiar. In our example, the requests are to show employees, prepare a report, or create a training request. The user does not need to know table names, write SQL, or include security instructions in the prompt.</p><p>The technical team can also control when changes become available. Operators and administrators can access a new tool, test its behaviour, and make it available to MCP_User when it is ready. This gives them room to improve the workflow without exposing unfinished operations to business users.</p><p>Writing adds another responsibility: the result must be saved correctly, failures must be explained clearly, and retries must not create duplicate requests. We checked these behaviours in the conversation and verified the saved rows directly in the database.</p><p>For me, this is the main value of the approach: a simpler experience for the user, a clear set of permitted operations, and database security that continues to apply behind every tool call.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8af24c3531d5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Making Oracle Database MCP Access Safe for Business Users]]></title>
            <link>https://medium.com/@vmendo/making-oracle-database-mcp-access-safe-for-business-users-47eef6f6688d?source=rss-ddfe9cbc5a5d------2</link>
            <guid isPermaLink="false">https://medium.com/p/47eef6f6688d</guid>
            <category><![CDATA[mcp-server]]></category>
            <category><![CDATA[data-security]]></category>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[oracle-database]]></category>
            <category><![CDATA[oracle-cloud]]></category>
            <dc:creator><![CDATA[Victor Mendo]]></dc:creator>
            <pubDate>Tue, 06 Oct 2026 09:16:20 GMT</pubDate>
            <atom:updated>2026-10-06T09:16:20.312Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b12r_mXqiDahv9M_EITyzA.png" /></figure><h3>Secure AI Agent Access to Business Data: MCP and Deep Data Security</h3><p>For me, the arrival of AI was an opportunity to rethink how I work. It meant preparing demos faster, reducing repetitive boilerplate code, exploring ideas earlier, and spending more time on design and the business story instead of starting from a blank page.</p><p>Model Context Protocol (MCP) made that opportunity practical in the Oracle Database world. It connects AI agents such as Codex or Claude to database workflows I already know: generating SQL, understanding existing code, validating technical ideas, and building richer proof-of-concepts more quickly.</p><p>Naturally, my entry point was the Oracle MCP server built into SQLcl. SQLcl is already central to my daily workflow, so it was the most logical place to start. The SQLcl MCP server runs locally and uses the database connections saved in the user’s SQLcl connection store. For a connection to be available to an MCP client, its credentials must be saved there. <a href="https://docs.oracle.com/en/database/oracle/sql-developer-command-line/26.1/sqcug/preparing-your-environment.html">Oracle’s SQLcl documentation</a> describes this setup. Jeff Smith’s <a href="https://www.thatjeffsmith.com/archive/2025/07/getting-started-with-our-mcp-server-for-oracle-database/">practical guide to SQLcl MCP</a> also explains why the connection and its privileges define what an AI assistant can do.</p><p>As a DBA and developer, this model worked well. I could use an AI agent for real database tasks in a controlled environment while keeping the connection and its privileges under my control. I knew the data, understood the schema, and was aware of the consequences of each generated command.</p><p>That is the strength of SQLcl MCP for individual productivity: the technical user owns the workstation, manages the connection, and is accountable for the database privileges behind it. But it is also why this access model is not the right fit for most business users.</p><p>Once the demos were ready, the second phase of my job began: showing customers what Oracle Database 26ai can do using the MCP. These conversations are more practical and engaging when customers can see AI interacting with real data. The feedback was very positive, but it quickly raised a more important question: <em>who should be allowed to use it?</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IiWSUMHsOnBuxt6A13Pi2g.png" /></figure><h3>1. The questions that changed the design</h3><p>The customer reactions were remarkably consistent:</p><blockquote><em>“I cannot give SQLcl to business users.”</em></blockquote><blockquote><em>“Business users should not manage database connections or have this level of database access.”</em></blockquote><blockquote><em>“Even if I give them access, how do I control which data they can see? I cannot create a database user for every business profile in the company.”</em></blockquote><blockquote><em>“How can I make sure they cannot change anything they should not change, or accidentally break something?”</em></blockquote><p>These are exactly the right questions. Business users should not need to know connection strings, manage credentials, or have the ability to run raw SQL against a production database. At the same time, they increasingly need answers from their data through AI agents.</p><p>We have also learned that the AI agent itself cannot be the security boundary. Instructions such as <em>“do not use this tool”</em> or <em>“do not access that data”</em> are useful guidance, but they are not enforceable authorization. A model can misunderstand them, receive conflicting instructions, or be influenced by prompt injection.</p><p>Security must therefore be enforced outside the LLM and the agent: the MCP service must control which tools a user can invoke, and the data source must control which operations and data each authenticated user is allowed to access.</p><p>The challenge is no longer just connecting an agent to a database. <strong>The real challenge is doing it safely and at scale</strong>.</p><h3>2. A different model: IAM, MCP, and Deep Data Security</h3><p>At about the same time, two conversations helped me frame the answer.</p><p>Jeff Smith’s comparison of <a href="https://www.thatjeffsmith.com/archive/2026/07/ords-now-a-streaming-http-mcp-server-for-oracle-database/">Oracle AI Database MCP servers</a> made the MCP side much clearer. SQLcl is a strong fit for developers and DBAs, while the OCI Database Tools MCP Server is aimed at governed access by business users, analysts, and shared AI agents.</p><p>Thomas Minne introduced me to Deep Data Security through his article, <a href="https://medium.com/@thomas.minne/deep-data-security-in-action-using-custom-end-user-security-context-with-oci-iam-144baa5802de"><em>Deep Data Security in Action: Using Custom End User Security Context with OCI IAM</em></a>. It was particularly relevant to this demo because it shows how an OCI IAM end-user context can be used by Oracle Database when applying Deep Data Security rules. It helped me connect IAM-based identity with data access enforced close to the data.</p><p>Together, these two ideas gave me the model I needed. The answer was not to make SQLcl available to more people. It was to use a different access model, designed for business users.</p><p>Instead of giving every user a database account, a database password, or a locally configured database client, I combined <strong>OCI IAM</strong>, the <strong>Oracle Database Tools MCP Server</strong>, and <strong>Oracle Deep Data Security</strong>, a native capability of Oracle Database 26ai.</p><p>Each component has a distinct responsibility:</p><ul><li><strong>OCI IAM</strong> authenticates the business user and authorizes access to the HR MCP service. The user signs in through the browser with their OCI IAM identity; they do not provide a database password to the AI agent.</li><li><strong>Oracle Database Tools MCP Server</strong> exposes the approved database capabilities as MCP tools. It is the controlled interface between the AI agent and the database: the agent can call only the configured toolset, such as schema inspection and SQL execution.</li><li><strong>Oracle Deep Data Security</strong> enforces fine-grained access inside Oracle Database. It maps IAM groups to database data roles and applies data grants that determine which rows, columns, cells, and related data a user is authorized to access.</li></ul><p>The diagram below shows how these components work together. The user signs in to OCI IAM in the browser, and the MCP client receives an OAuth token for that user. The client sends the token to the Database Tools MCP Server, which verifies that the user is entitled to invoke the HR MCP service.</p><p>For the database connection, the MCP Server exchanges the OAuth token for an OCI IAM auth token issued for that same end user. Oracle Database validates the token and establishes an end-user security context for the session. The data roles mapped to that user’s IAM groups are enabled, and Deep Data Security applies the corresponding data grants before any query result is returned.</p><p>The important distinction is that the agent does not decide what a user may see. It sends a request through the configured MCP toolset; Oracle Database remains responsible for evaluating the authenticated identity and enforcing the data-access rules.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kcsqkQjH_X-tIn77Ug-SKg.png" /></figure><h3>3. A practical HR example: two boundaries, one service</h3><p>For my demo, I created an Oracle Database Tools MCP Server backed by the HR database connection. I configured its SQL toolset and used OCI IAM application roles to control which users can invoke it. Oracle provides a step-by-step tutorial for creating the server, connecting it to a Database Tools connection, configuring the toolset, mapping users or groups to application roles, and integrating an MCP client: <a href="https://docs.oracle.com/en-us/iaas/database-tools/doc/tutorial.html?utm_source=chatgpt.com">Tutorial: Set Up a Database Tools MCP Server and Integrate with an MCP Client</a></p><p>HR database contains five related tables: departments, employees, compensation, employment events, and jobs. The data is split into two business regions: Iberia and UK.</p><p>There are two security boundaries in this example. The first is the service itself: only HR users are entitled to invoke the HR MCP server. The second is the data returned once an HR user is inside the service.</p><p>First one: Sales users must not be able to use the HR service, even if they know its endpoint or try to configure it in an agent. HR users, on the other hand, should be able to invoke the service. The following tests confirm that service-level access is controlled by OCI IAM application roles.</p><h4>Test 1: A Sales user tries to use the HR MCP service</h4><p>Sales users do not receive the HR MCP application role. They may have a separate Sales MCP server, with its own database and IAM permissions, but they must not be able to use the HR service simply because they know its endpoint or can register its configuration in an agent.</p><p>The Sales user submits the following simple prompt:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*R9K7PQgJ3Ai4lxlK0X3_KA.png" /></figure><p>This is the expected response. The user is authenticated with OCI IAM but is not entitled to invoke the HR MCP service. The agent cannot access the HR toolset, and no database session is created.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7XykohB_3hLnco7PcQelzw.png" /></figure><h4>Test 2: HR access works, but the data scope is too broad</h4><p>Alice is an Iberia HR user. Bob is a UK HR user, whom I will use in the second HR test. At the beginning of the demo, however, both can see all HR data. This is the problem statement: the HR MCP service works, but data access is too broad.</p><p>From this point on, <strong>I use the same business prompt for all HR tests</strong>. It asks for a workforce snapshot by business region and for recorded promotions or transfers. First, I run it as an HR user to show that the user can invoke the service, but the data returned is still too broad.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/894/1*cHwC8FotMQzo_iqhym67Vw.png" /></figure><p>Logged as Alice (HR), I submit the prompt. The MCP service accepts it, and the response identifies Alice as the active end user. However, the workforce snapshot and recorded employment events include data from both Iberia and the UK. Alice can use the service, but the data returned is broader than it should be.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vtgfDdashELMYcyc5E8crg.png" /></figure><p>The goal is simple: Alice should see Iberia only, Bob should see UK only, and neither person should need an individual database account. This is why the service boundary alone is not enough: we also need a second security boundary for the data returned by the database.</p><h3>4. Applying the regional policy in the database</h3><p>This is where Deep Data Security comes into play.</p><p>The policy is based on IAM groups, not individual users. I created HR_IBERIA for Alice and HR_UK for Bob. In Oracle Database, I mapped those groups to Deep Data Security data roles:</p><pre>CREATE DATA ROLE hr_iberia_role<br>  MAPPED TO &#39;IAM_GROUP_NAME=&lt;identity-domain&gt;/HR_IBERIA&#39;;<br><br>CREATE DATA ROLE hr_uk_role<br>  MAPPED TO &#39;IAM_GROUP_NAME=&lt;identity-domain&gt;/HR_UK&#39;;</pre><p>This is important because it scales. If another person joins HR_IBERIA in IAM, the same database rules apply automatically. There is no need to create another database user or duplicate grants.</p><p>The database can also see the actual authenticated end user:</p><pre>SELECT ORA_END_USER_CONTEXT.username AS end_user<br>FROM dual;</pre><p>For Alice, the session returns her IAM identity; for Bob, it returns his. This is what allows the same HR MCP server to apply different rules for different people.</p><p>The data grants start with the departments table, filtering business_region for Iberia or UK. Access to employees, compensation, and employment events then follows their relationships with departments and employees. The jobs catalogue can remain visible to both regions because it is common reference data.</p><pre>-- Run as HR_OWNER at the DDS reveal point in the demo.<br>-- Replaces the broad policy with database-enforced regional access.<br>-- Alice (HR_IBERIA) sees Iberia; Bob (HR_UK) sees UK.<br><br>-- 1. Root policy: region is defined on departments.<br>CREATE OR REPLACE DATA GRANT hr_iberia_departments_select<br>  AS SELECT<br>  ON hr_departments<br>  WHERE business_region = &#39;IBERIA&#39;<br>  TO hr_iberia_role;<br><br>CREATE OR REPLACE DATA GRANT hr_uk_departments_select<br>  AS SELECT<br>  ON hr_departments<br>  WHERE business_region = &#39;UK&#39;<br>  TO hr_uk_role;<br><br>-- 2. Inherit department authorization to employees.<br>CREATE OR REPLACE DATA GRANT hr_employees_by_department<br>  AS SELECT<br>  ON hr_employees<br>  WHEN SELECT GRANTED ON hr_departments<br>  WHERE hr_employees.department_id = hr_departments.department_id;<br><br>-- 3. Inherit employee authorization to compensation and lifecycle events.<br>CREATE OR REPLACE DATA GRANT hr_compensation_by_employee<br>  AS SELECT<br>  ON hr_compensation<br>  WHEN SELECT GRANTED ON hr_employees<br>  WHERE hr_compensation.employee_id = hr_employees.employee_id;<br><br>CREATE OR REPLACE DATA GRANT hr_employment_events_by_employee<br>  AS SELECT<br>  ON hr_employment_events<br>  WHEN SELECT GRANTED ON hr_employees<br>  WHERE hr_employment_events.employee_id = hr_employees.employee_id;<br><br>-- 4. Job definitions are a shared reference catalog.<br>CREATE OR REPLACE DATA GRANT hr_jobs_catalog_select<br>  AS SELECT<br>  ON hr_jobs<br>  TO hr_iberia_role, hr_uk_role;</pre><p>The important point is not the exact SQL syntax. It is that the rule is defined once, close to the data model, and Oracle Database enforces it regardless of the query generated by the agent.</p><h4>Same agent. Same MCP server. Same question.</h4><p>This is the part I like most for a demo. I use the same agent, the same MCP server, and the same business prompt before and after enabling the regional policies:</p><p>Before the policy, Alice received Iberia and UK information. After the policy, Alice receives Iberia only.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZL86n3ldzOgq2H3uddRNtg.png" /></figure><p>The prompt does not say “I am Alice”. The agent does not need to know the security rules. The outcome changes because OCI IAM identity and Deep Data Security enforce the correct access when the query reaches the database.</p><p>There is no need to restart the AI agent, reconfigure the MCP, or even for Alice to log out and log back in, because validation takes place the moment the data is accessed.</p><p>And Bob receives UK only.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wpNurnzug1BiPT63hEDsyA.png" /></figure><h3>5. Useful does not mean over-privileged</h3><p>An AI agent can generate SQL. That is why security must not rely on a prompt saying “please do not delete data.”</p><p>In this demo, the HR MCP service is configured for read-only business questions. The database connection behind the MCP server is granted only the privileges required to read the HR data, and end users do not receive database credentials. Deep Data Security then determines which rows and related data Oracle Database returns for each authenticated user.</p><p>The security model therefore has three layers:</p><p>1. OCI IAM controls whether the user can invoke the HR MCP server.</p><p>2. The MCP service exposes a controlled, read-only toolset and uses a least-privilege database connection.</p><p>3. Deep Data Security controls which rows and related data Oracle Database returns to that user.</p><p>If the agent is misconfigured, if a user tries a different query, or if a prompt is manipulated, Oracle Database remains the enforcement point. The agent cannot obtain privileges beyond those of the configured database connection or bypass the Deep Data Security rules applied to the session.</p><h3>Conclusion</h3><p>Oracle Database Tools MCP Server makes it easier to connect agents to enterprise data. But connection is only the first step.</p><p>In this demo, OCI IAM is the identity provider. It manages user sign-in, single sign-on, groups, and application roles. The MCP service integrates with that identity layer, so business users authenticate with their enterprise identity rather than with individual database accounts.</p><p>Once the request reaches Oracle Database, Deep Data Security uses the end-user identity and group context propagated from the identity provider to determine which data that user is allowed to see.</p><p>This is not a replacement for SQLcl or other technical database workflows. It is a complementary pattern for business-facing use cases: enterprise identity establishes who the user is, the MCP service controls which capabilities are available, and Deep Data Security enforces which data Oracle Database returns.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=47eef6f6688d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Build Read-Only REST APIs on Active Data Guard Standby with ORDS]]></title>
            <link>https://medium.com/@vmendo/build-read-only-rest-apis-on-active-data-guard-standby-with-ords-2da654ac90af?source=rss-ddfe9cbc5a5d------2</link>
            <guid isPermaLink="false">https://medium.com/p/2da654ac90af</guid>
            <category><![CDATA[rest-api]]></category>
            <category><![CDATA[microservices]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[development]]></category>
            <dc:creator><![CDATA[Victor Mendo]]></dc:creator>
            <pubDate>Wed, 09 Jul 2025 14:19:31 GMT</pubDate>
            <atom:updated>2025-07-09T14:19:31.986Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s-oi7zFqh25ppKC1dC7hJQ.png" /></figure><p><strong>Oracle REST Data Services (ORDS)</strong> is a powerful tool that enables developers to easily expose database data as RESTful APIs — eliminating the need for custom middleware or complex integration layers. It simplifies the creation of modern web services directly over Oracle Database objects such as tables, views, and stored procedures.</p><p>In many enterprise scenarios, ORDS is adopted to serve data to external systems, mobile applications, or microservices, often under heavy API traffic. In such cases, using the primary database node for these operations can lead to unnecessary load, potentially affecting transactional or real-time performance.</p><p>To address this, ORDS can also be deployed against an <strong>Active Data Guard</strong> standby database. By offloading read-only REST requests to the standby node, organizations can protect their primary database from API-induced stress — maintaining optimal performance for critical workloads while still delivering up-to-date data via REST APIs.</p><h3>Limitations When Using ORDS on a Standby Node</h3><p>While exposing REST APIs through an Active Data Guard standby database can significantly reduce load on the primary, this architecture comes with some limitations:</p><ul><li><strong>Read-only Operations Only</strong><br>Of course, the standby database is opened in <em>read-only</em> mode. Therefore, only HTTP methods that perform read operations — such as GET or POST (when used for querying)—can be safely used. Any attempt to perform inserts, updates, or deletes will result in runtime errors.</li><li><strong>ORDS Internal Features May Fail</strong><br>ORDS requires write access to certain metadata tables (such as ORDS_METADATA and logging tables) for operations like token generation, OAuth flows, and tracking usage statistics. These features will not work on a read-only database.<br>To work around this, authentication and authorization should be <strong>handled externally</strong>—typically by an API Gateway or a centralized identity provider that enforces security policies before requests reach ORDS.</li></ul><h3><strong>Architecture Overview</strong></h3><p>Our setup is based on a typical Active Data Guard configuration with a primary and a physical standby Oracle database. The key goal is to reduce load on the primary database by routing certain RESTful read operations to the standby node.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GtbXQTMuXUhhkZ9N3ZBGDQ.png" /></figure><p>Here’s how the architecture is structured:</p><ul><li><strong>Primary Database</strong>: Hosts the transactional workload and handles both read and write operations.</li><li><strong>Standby Database</strong>: Synchronized via Active Data Guard and opened in <em>read-only</em> mode to support reporting or API queries.</li><li><strong>ORDS Instance</strong>: Installed on a separate compute host. This ORDS deployment is configured with:</li><li><strong>Connection Pool #1</strong>: Points to the <strong>Primary PDB</strong> and handles all APIs that require read/write access or depend on ORDS features that modify metadata (e.g., OAuth2 token generation, SQL Developer Web, etc.).</li><li><strong>Connection Pool #2</strong>: Points to the <strong>Standby PDB</strong>, and is used <em>exclusively</em> for APIs that perform read-only operations, such as GET or POST handlers that internally execute SELECT statements.</li></ul><p>This dual-pool configuration allows us to decouple read-mostly workloads — especially those exposed via REST APIs — from the transactional load on the primary database, improving performance, scalability, and fault isolation.</p><p><strong>Note:</strong> Routing requests to the appropriate connection pool — whether targeting the primary database for read/write operations or the standby for read-only access — requires an external component such as an API Gateway or reverse proxy. Similarly, deploying multiple ORDS instances for high availability is strongly recommended. However, these topics are outside the scope of this blog.</p><h3>Practical Setup</h3><p>In this section, I will walk you through the environment used in this lab to demonstrate how to expose REST APIs from an Active Data Guard standby node using Oracle REST Data Services (ORDS).</p><h4>1. Provisioning the Primary Database</h4><p>I started by provisioning an <strong>Oracle Database Cloud System</strong> in <strong>OCI (Oracle Cloud Infrastructure)</strong>. You can find the documentation <a href="https://docs.oracle.com/en/cloud/paas/base-database/create-dbs-new#GUID-5D864726-41AA-4D77-9FD8-06F8F8D7EFE2">here</a>.<br>To enable <strong>Active Data Guard</strong>, I selected the <strong>Extreme Performance</strong> edition during provisioning.</p><p>This ensures that all necessary features, including Data Guard and advanced workload support, are available in my environment.</p><h4>2. Enabling Active Data Guard</h4><p>Once the primary database was up and running, I used the <strong>OCI Console</strong> to create an <strong>Active Data Guard standby</strong> instance. You can find the details in the documentation <a href="https://docs.oracle.com/en/cloud/paas/base-database/dg-enable/">here</a>.</p><blockquote><em>⚠️ </em><strong><em>Important</em></strong><em>: During this process, ensure that port </em><strong><em>1521</em></strong><em> is open between the primary and standby database subnets. Otherwise, the Data Guard association will silently fail.</em></blockquote><p>After a few minutes, the standby instance was created and automatically synchronized with the primary using real-time redo apply.</p><h4>3. Deploying ORDS</h4><p>On a separate <strong>Compute Instance</strong>, I installed and configured <strong>ORDS 25.1</strong>, using the standalone mode for simplicity.</p><p>From this point onward, I will focus specifically on the <strong>ORDS configuration</strong>. Assuming that the database tier has already been set up with <strong>Active Data Guard</strong> and is running correctly, the next step is to prepare ORDS to take advantage of this architecture.</p><p>We’ll configure two separate <strong>connection pools</strong>, each targeting a different node (primary and standby), and discuss how to expose read-only REST endpoints backed by the standby database.</p><h3>Installing ORDS with a Default Connection Pool to the Primary Database</h3><p>Run the following command to install ORDS and configure the default pool to the active node:</p><pre>ords --config /path/to/myconfig install</pre><p>You’ll be prompted for database connection details. Then it will show us the rest of the options that we can configure, such as protocols, ports, SSL certificates… By default, the ORDS installer generates a password for the ORDS_PUBLIC_USER user. Make sure to set a custom password (<strong>option 3</strong>) for the ORDS_PUBLIC_USER when prompted, as we’ll reuse it for the standby node:</p><pre>[3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): &lt;generate&gt;</pre><p>When you enter your own password, then ORDS will use it:</p><pre>[3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): &lt;specified password&gt;</pre><p>Also, enable Database Actions if you plan to use SQL Developer Web.</p><pre>[4] Additional Feature: Database Actions</pre><p>It will creates the <strong><em>defaul pool</em></strong> to the active database. Thanks to <strong>Active Data Guard</strong>, all changes made during the ORDS installation on the <strong>primary database</strong> — including the creation and configuration of the ORDS_PUBLIC_USER—are automatically <strong>replicated to the standby node</strong>. This means there’s <strong>no need to re-run the ORDS installation</strong> on the standby database. The user, metadata, and configuration objects required by ORDS in the database will be kept <strong>in sync</strong>, ensuring consistency between the two environments.</p><h3>Creating the Connection Pool for the Standby Database</h3><p>Before configuring the second connection pool for the <strong>standby database</strong>, it’s a good idea to verify that the connection is functional using the same ORDS_PUBLIC_USER account defined during the initial ORDS installation.</p><p>In my case, the <strong>standby database</strong> was not open, which is expected behavior in many Data Guard setups. To enable ORDS to connect and serve requests in <strong>read-only mode</strong>, I had to explicitly open both the <strong>CDB</strong> and the <strong>PDB</strong> in READ ONLY mode.</p><h4>Check Current Status</h4><p>First, connect to the <strong>standby CDB</strong> as SYS and run:</p><pre>-- Check if the CDB is open<br>SELECT STATUS FROM V$INSTANCE;<br><br>-- Check the status of all PDBs<br>SELECT NAME, OPEN_MODE FROM V$PDBS;</pre><p>If the CDB is not open and the PDB appears in MOUNTED mode, proceed to open them:</p><pre>-- Open the CDB in read-only mode<br>STARTUP MOUNT;<br>ALTER DATABASE OPEN READ ONLY;<br><br>-- Then open the required PDB<br>ALTER PLUGGABLE DATABASE PDB1 OPEN READ ONLY;</pre><p>Once the database and the pluggable database are in READ ONLY state, verify that you can connect using ORDS_PUBLIC_USER from your ORDS host or SQL client (e.g., SQLcl):</p><pre>sql ORDS_PUBLIC_USER@&quot;//standby-host:1521/your_pdb_service&quot;</pre><p>If the login works and you can query data (but not insert/update), then your standby environment is ready for ORDS.</p><h4>Configuring the pool</h4><p>ORDS is already installed in the <strong>standby database</strong> (thanks to Active Data Guard replication) and we are sure ORDS_PUBLIC_USER can connect, we <strong>ONLY</strong> need to configure an additional <strong>JDBC connection pool</strong> for it. ORDS provides a <strong>config-only</strong> option for this:</p><pre>ords --config /path/to/myconfig install -i --config-only</pre><p>it allows us to update an existing pool or create a new one:</p><pre>  Enter a number to select the database pool to update, or create an additional database pool.<br>    [1] default      jdbc:oracle:thin:@//10.0.2.227:1521/ORDSDB19_pdb1.sub0318...<br>    [C] Create an additional database pool</pre><p>We will chose <strong><em>option C</em></strong> and it will request the new pool name, connection details for the standby database. Here, we will need to provide the ORDS_PUBLIC_USER password we set in the default pool.</p><p>As a result, we will have our ORDS instance configured with two connection pools, <strong><em>default</em></strong> and <strong><em>standby </em></strong>(It was the name I set for the second pool)</p><p>It’s time to validate the configuration and see how we can direct traffic to each pool in a controlled way.</p><h3>Defining and Testing ORDS REST Handlers</h3><p>For this test, I created two REST handlers to demonstrate ORDS behavior on different nodes:</p><h3>1. A Read-Only POST Handler</h3><p>This endpoint receives a department name as part of the JSON request body and returns a list of employees from that department. It uses a simple SELECT statement inside the handler to query the database</p><pre>-- Define REST handler<br>BEGIN<br>    ORDS.DEFINE_HANDLER(<br>        p_module_name =&gt; &#39;com.oracle.demo.api&#39;,<br>        p_pattern =&gt; &#39;selectemp3&#39;,<br>        p_method =&gt; &#39;POST&#39;,<br>        p_source_type =&gt; ords.source_type_query,<br>        p_source =&gt; &#39;SELECT * FROM employees WHERE department = :dep_name&#39;,<br>        p_items_per_page =&gt; 25,<br>        p_comments =&gt; &#39;&#39;<br>    );<br>    COMMIT;<br>END;</pre><p>This operation is <strong>safe</strong> to execute on either database.</p><p><strong>Execution against the default (READ/WRITE) pool:</strong></p><pre>curl -k --location --request POST \<br>&#39;https://ords_server:8443/ords/HR/demo/selectemp3&#39; \<br>--header &#39;Content-Type: application/json&#39; \<br>--data &#39;{&quot;dep_name&quot;: &quot;HR&quot;}&#39;</pre><p><strong>Execution against the READ/ONLY pool:<br></strong>(We use the pool name in the URI)</p><pre>curl -k --location --request POST\<br>&#39;https://ords_server:8443/ords/standby/HR/demo/selectemp3&#39; \<br>--header &#39;Content-Type: application/json&#39; \<br>--data &#39;{&quot;dep_name&quot;: &quot;HR&quot;}&#39;</pre><p>The response is the same for both calls:</p><pre>[<br>  {&quot;emp_id&quot;:3,&quot;name&quot;:&quot;Cathy Wu&quot;,&quot;department&quot;:&quot;HR&quot;,&quot;hire_date&quot;:&quot;2018-03-25T00:00:00Z&quot;},<br>  {&quot;emp_id&quot;:9,&quot;name&quot;:&quot;Irene Nash&quot;,&quot;department&quot;:&quot;HR&quot;,&quot;hire_date&quot;:&quot;2018-03-25T00:00:00Z&quot;},<br>  {&quot;emp_id&quot;:11,&quot;name&quot;:&quot;Victor&quot;,&quot;department&quot;:&quot;HR&quot;,&quot;hire_date&quot;:&quot;2017-06-25T00:00:00Z&quot;},<br>  {&quot;emp_id&quot;:12,&quot;name&quot;:&quot;Carlos Mountain&quot;,&quot;department&quot;:&quot;HR&quot;,&quot;hire_date&quot;:&quot;2017-06-25T00:00:00Z&quot;}<br>]</pre><h3>2. A Write Operation (PUT Handler)</h3><p>The second REST handler is a <strong>PUT</strong> method used to update an employee’s name and department based on the emp_id provided in the URL path. This is a classic write operation and will <strong>only work on the primary database</strong>. Attempting to run it against the standby database will result in an error, as expected in a <strong>read-only</strong> environment.</p><pre>-- Define PUT handler<br>BEGIN<br>    ORDS.DEFINE_HANDLER(<br>        p_module_name =&gt; &#39;com.oracle.demo.api&#39;,<br>        p_pattern =&gt; &#39;update/:emp_id&#39;,<br>        p_method =&gt; &#39;PUT&#39;,<br>        p_source_type =&gt; ords.source_type_plsql,<br>        p_source =&gt; &#39;BEGIN<br>        UPDATE employees<br>        SET name = :name,<br>            department = :department<br>        WHERE emp_id = :emp_id;<br>        COMMIT;<br>      END;&#39;,<br>        p_items_per_page =&gt; 25,<br>        p_comments =&gt; &#39;&#39;<br>    );<br>    COMMIT;<br>END;</pre><p><strong>Execution against the active database (works):</strong></p><pre>curl -k --location --request PUT \<br>&#39;https://ords_server:8443/ords/HR/demo/update/11&#39; \<br>--header &#39;Content-Type: application/json&#39; \<br>--data &#39;{&quot;name&quot;: &quot;Victor&quot;, &quot;department&quot;: &quot;HR&quot;}&#39;</pre><p>The response is empty because we didn’t define an explicit output in the handler. However, after running a SELECT query, we can confirm that the employee record was successfully updated.</p><p><strong>Execution against the standby database (fails):</strong></p><pre>curl -k --location --request PUT \<br>&#39;https://ords_server:8443/ords/standby/HR/demo/update/11&#39; \<br>--header &#39;Content-Type: application/json&#39; \<br>--data &#39;{&quot;name&quot;: &quot;Victor&quot;, &quot;department&quot;: &quot;HR&quot;}&#39;</pre><p>This call will fail with ORA-16000 :</p><pre>{<br>  &quot;code&quot;: &quot;DatabaseError&quot;,<br>  &quot;message&quot;: &quot;ORA-16000: database or pluggable database open for read-only access&quot;,<br>  &quot;type&quot;: &quot;tag:oracle.com,2020:error/DatabaseError&quot;<br>}</pre><h3>Wrapping Up</h3><p>So, is it worth exposing read-only REST APIs through an Active Data Guard standby database? Absolutely — <strong>if you’re dealing with heavy external traffic and want to keep your primary database focused on what it does best: serving core transactional workloads</strong>.</p><p>In this post, we’ve seen that with just a bit of ORDS configuration and a solid understanding of how Active Data Guard works, you can offload read operations safely. Sure, there are some limitations:</p><ul><li>You can’t run write operations on the standby.</li><li>You’ll need to handle security outside ORDS (like via an API Gateway).</li><li>And yes, not everything works out of the box — but once it’s set up, it just works.</li></ul><p>This setup is clean, efficient, and makes the most of your Oracle infrastructure. It’s a great example of how you can <strong>scale smart</strong> — using what you already have, without overcomplicating your architecture.</p><p>Now that everything is in place, you’re ready to start building fast, read-only APIs that don’t slow down your core apps. Give it a try in your next project — <strong>you might be surprised how far it gets you</strong>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2da654ac90af" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Oracle Database and Kubernetes: Managing an Autonomous Database from Kubernetes]]></title>
            <link>https://medium.com/@vmendo/oracle-database-and-kubernetes-managing-an-autonomous-database-from-kubernetes-1c317a468bef?source=rss-ddfe9cbc5a5d------2</link>
            <guid isPermaLink="false">https://medium.com/p/1c317a468bef</guid>
            <category><![CDATA[autonomous-database]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[oracle-cloud]]></category>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[kubernetes-operator]]></category>
            <dc:creator><![CDATA[Victor Mendo]]></dc:creator>
            <pubDate>Fri, 05 Apr 2024 12:41:45 GMT</pubDate>
            <atom:updated>2024-04-05T12:41:45.754Z</atom:updated>
            <content:encoded><![CDATA[<p>We have seen <a href="https://medium.com/@vmendo/oracle-database-and-kubernetes-creating-an-oracle-autonomous-database-from-kubernetes-1637204dffc4">how to provision and access an autonomous database</a> with the Kubernetes database operator. However, this database runs on OCI, not Kubernetes. Can we operate this database from the operator? Let’s see what actions we can perform with this autonomous database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/755/1*r_VdEPtJPZ1tBZDxP9_bbQ.png" /></figure><p>We will explore some of the basic actions, such as start/stop and terminate, and one of the fundamental advantages of autonomous databases, which is the ability to scale.</p><p><strong>STOP/START our Autonomous Database</strong></p><p>We are working in the cloud, so cost efficiency is crucial. I’ll want to stop my database if I’m not using it and start it only when necessary.</p><p>As always, we will use a YAML descriptor file to specify which operation we want to perform and against which database.</p><pre>#<br># Copyright (c) 2022, Oracle and/or its affiliates. <br># Licensed under the Universal Permissive License v 1.0 as shown at http://oss.oracle.com/licenses/upl.<br>#<br>apiVersion: database.oracle.com/v1alpha1<br>kind: AutonomousDatabase<br>metadata:<br>  name: adbsample<br>spec:<br>  details:<br>    autonomousDatabaseOCID: ocid1.autonomousdatabase.oc1.eu-frankfurt-1.antheljteicj2tiand6ds6czoojprstrnim5kgwahcej42m37zboizcsxvhq<br>    # Change the lifecycleState to &quot;AVAILABLE&quot; to start the database<br>    lifecycleState: STOPPED<br>  # Authorize the operator with API signing key pair. Comment out the ociConfig fields if your nodes are already authorized with instance principal.<br>  ociConfig:<br>    configMapName: oci-cred<br>    secretName: oci-privatekey</pre><p>We have all the information we need to configure our file. In the <a href="https://medium.com/@vmendo/oracle-database-and-kubernetes-creating-an-oracle-autonomous-database-from-kubernetes-1637204dffc4">previous entry of this blog</a>, we already explained how to obtain this data: The ConfigMap and the Secret with the necessary OCI credentials to manage our database, and the OCID of the autonomous database we want to operate on.</p><p>We just need to specify the <strong><em>lifecycleState</em></strong> in which we want to leave our autonomous database: STOPPED or AVAILABLE.</p><p>So, we can apply the file, and stop/start our database.</p><pre>$ Kubectl apply -f stop_adb.yaml</pre><h3>Terminating our Autonomous Database</h3><p>We might also have created our database for a test and never need it again. In that case, instead of stopping the autonomous database, what we need is to terminate it.</p><p>We just need to modify the lifecycleState of our autonomous database to Terminated:</p><pre>#<br># Copyright (c) 2022, Oracle and/or its affiliates. <br># Licensed under the Universal Permissive License v 1.0 as shown at http://oss.oracle.com/licenses/upl.<br>#<br>apiVersion: database.oracle.com/v1alpha1<br>kind: AutonomousDatabase<br>metadata:<br>  name: adbsample<br>  namespace: atp<br>spec:<br>  details:<br>    autonomousDatabaseOCID: ocid1.autonomousdatabase.oc1.eu-frankfurt-1.antheljteicj2tiand6ds6czoojprstrnim5kgwahcej42m37zboizcsxvhq<br>    # Change the lifecycleState to &quot;AVAILABLE&quot; to start the database<br>    lifecycleState: TERMINATED<br>  # Authorize the operator with API signing key pair. Comment out the ociConfig fields if your nodes are already authorized with instance principal.<br>  ociConfig:<br>    configMapName: oci-cred<br>    secretName: oci-privatekey</pre><pre>$ kubectl apply -f terminate_adb.yaml <br>autonomousdatabase.database.oracle.com/adbsample configured</pre><p>When our autonomous database is in the Terminated state, it will appear both in the console and in the list of databases provided by the operator, but we can no longer work with it.</p><p>We can see in the console that our database is terminating.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TSDUKxWtRZ-_ilK7k0KaBw.png" /></figure><p>Or we can list our autonomous databases to see the status:</p><pre>$ kubectl get AutonomousDatabase -n atp<br>NAME                 DISPLAY NAME      DB NAME           STATE        DEDICATED   OCPUS   STORAGE (TB)   WORKLOAD TYPE   CREATED<br>adbsample            VMDemoADBOKE2     VMDemoADBOKE2     TERMINATED   false       1       1              OLTP            2024-02-20 11:27:36 UTC</pre><p>With this, we have terminated our database in OCI, but the operator will continue to “remember” this database showing it as Terminated. If we want it to disappear from the list of autonomous databases managed by the operator, we will use the “<strong><em>kubectl delete</em></strong>” command to delete the deployment.</p><pre>kubectl delete adb/adbsample</pre><p>In this case, the order of operations is important. If we execute the “<strong><em>kubectl delete</em></strong>” command before terminating our autonomous database, the operator will “forget” this database, but it will continue to exist in OCI in its current state (Available/Stopped). If this happens, we will have to manage our database using OCI management tools (console, API, OCI CLI…). Alternatively, we can recreate the binding between the operator and the database to operate it from the operator again.</p><p>We can “force” our autonomous database to terminate when we delete the resource from our operator. There is a parameter in the configuration called “Hard Link” which is set to false by default. We can view this parameter and its current value by executing:</p><pre>$ kubectl describe AutonomousDatabase adbsample</pre><pre>Name:         adbsample<br>Namespace:    atp<br>Labels:       &lt;none&gt;<br>Annotations:  lastSuccessfulSpec:<br>                {&quot;details&quot;:{&quot;autonomousDatabaseOCID&quot;:&quot;ocid1.autonomousdatabase.oc1.eu-frankfurt-1.antheljseicj2tiaeb66frurfpye4v3dotjxjyhjlwb5jnuvtqjmmzkf...<br>API Version:  database.oracle.com/v1alpha1<br>Kind:         AutonomousDatabase<br>Metadata:<br>  Creation Timestamp:  2024-03-07T10:25:27Z<br>  Generation:          6<br>  Resource Version:    16950534<br>  UID:                 8f5e4beb-1f7f-43cc-91c7-f634fe6f94ff<br>Spec:<br>  Details:<br>    Admin Password:<br>      k8sSecret:<br>        Name:  admin-password<br>      Oci Secret:<br>    Autonomous Container Database:<br>      k8sACD:<br>      Oci ACD:<br>    Autonomous Database OCID:   ocid1.autonomousdatabase.oc1.eu-frankfurt-1.antheljseicj2tiaeb66frurfpye4v3dotjxjyhjlwb5jnuvtqjmmzkfllpq<br>    Compartment OCID:           ocid1.compartment.oc1..aaaaaaaaghjqv353kzhw6aczab3svffxrxqrpm3g27gabfmqqun2fbr4wjea<br>    Cpu Core Count:             1<br>    Data Storage Size In T Bs:  1<br>    Db Name:                    VMDemoADBOKE1<br>    Db Version:                 19c<br>    Db Workload:                OLTP<br>    Display Name:               VMDemoADBOKE1<br>    Is Auto Scaling Enabled:    false<br>    Is Dedicated:               false<br>    License Model:              BRING_YOUR_OWN_LICENSE<br>    Lifecycle State:            AVAILABLE<br>    Network Access:<br>      Access Type:                  PUBLIC<br>      Is MTLS Connection Required:  true<br>      Private Endpoint:<br>    Wallet:<br>      Name:  adbsample-instance-wallet<br>      Password:<br>        k8sSecret:<br>          Name:  instance-wallet-password<br>        Oci Secret:<br>  Hard Link:  false<br>  Oci Config:<br>Status:<br>  All Connection Strings:<br>    Connection Strings:<br>      Connection String:  (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=g5e60ebadf0d95a_vmdemoadboke1_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))<br>      Tns Name:           vmdemoadboke1_high<br>      Connection String:  (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=g5e60ebadf0d95a_vmdemoadboke1_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))<br>      Tns Name:           vmdemoadboke1_low<br>      Connection String:  (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=g5e60ebadf0d95a_vmdemoadboke1_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))<br>      Tns Name:           vmdemoadboke1_medium<br>      Connection String:  (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=g5e60ebadf0d95a_vmdemoadboke1_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))<br>      Tns Name:           vmdemoadboke1_tp<br>      Connection String:  (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=g5e60ebadf0d95a_vmdemoadboke1_tpurgent.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))<br>      Tns Name:           vmdemoadboke1_tpurgent<br>    Tls Authentication:   Mutual TLS<br>  Lifecycle State:        AVAILABLE<br>  Time Created:           2024-03-07 10:25:28 UTC<br>Events:                   &lt;none&gt;</pre><p>As you can see, Hard Link is set to false. We can modify this value with a descriptor like this:</p><pre>apiVersion: database.oracle.com/v1alpha1<br>kind: AutonomousDatabase<br>metadata:<br>  name: adbsample<br>spec:<br>  details:<br>    autonomousDatabaseOCID: ocid1.autonomousdatabase...<br>  hardLink: true<br>  ociConfig:<br>    configMapName: oci-cred<br>    secretName: oci-privatekey</pre><p>Now, we can apply the change:</p><pre>$ kubectl apply -f adb_delete_hard.yaml</pre><p>And now we can delete the resource:</p><pre>kubectl delete adb/adbsample</pre><p>At this point, you cannot get the database status using the operator, so you will need to use the OCI console to check that the database status is Terminating.</p><p><strong>Personally</strong>, I prefer to do it in two steps (Terminate + delete) than enabling this parameter because it allows me to verify that the autonomous database is terminating from the operator itself, and it is much less likely to terminate the autonomous database (and lose the data) by mistake.</p><h4>Binding the Operator to an existing Autonomos Database</h4><p>As we have seen, we may need to create a binding between the operator and an existing autonomous database in OCI for various reasons:</p><ul><li>We accidentally deleted the binding, as mentioned in the previous paragraph.</li><li>We have an autonomous database that already exists, and now we want to work with it from our applications in Kubernetes.</li></ul><p>Performing the binding is as simple as specifying the OCID of the Autonomous Database (and of course, the OCI credentials with permissions to manage that Autonomous Database).</p><pre>apiVersion: database.oracle.com/v1alpha1<br>kind: AutonomousDatabase<br>metadata:<br>  name: adb-binding-sample<br>spec:<br>  details:<br>    autonomousDatabaseOCID: ocid1.autonomousdatabase.oc1.eu-frankfurt-1.anthel….bcq<br>    ociConfig:<br>      configMapName: oci-cred<br>      secretName: oci-privatekey</pre><p>And apply the descriptor:</p><pre>$ kubectl apply -f bind_adb.yaml</pre><p>And we can verify that the binding has been performed by listing our Autonomous Databases:</p><pre>$ kubectl get AutonomousDatabase<br>NAME                 DISPLAY NAME      DB NAME           STATE          DEDICATED   OCPUS   STORAGE (TB)   WORKLOAD TYPE   CREATED<br>adb-binding-sample   VMDemoADBOKE2     VMDemoADBOKE2     AVAILABLE      false       1       1              OLTP            2024-03-07 10:25:28 UTC</pre><p>Well, we have already seen how to stop, start, and terminate our autonomous database. We have yet to explore one of the most interesting functionalities, which is scaling.</p><h4>Scaling our Autonomous Database</h4><p>Autonomous databases can automatically scale compute capacity up to 3 times their initial configuration. However, by default, this option is disabled. We can verify this by describing our databaseand looking for “<em>Is Auto Scaling Enabled</em>”.</p><p>It’s disabled, so we’ll need to enable it if we want our autonomous database to scale during peak loads.</p><p>We have two options: either edit the deployment using the <strong>edit</strong> command, or create a YAML descriptor file to set &quot;isAutoScalingEnabled: true&quot;, and then ask Kubernetes to apply the change. In this case, the descriptor would look like this:</p><pre>apiVersion: database.oracle.com/v1alpha1<br>kind: AutonomousDatabase<br>metadata:<br>  name: adbsample               <br>spec:<br>  details:<br>    autonomousDatabaseOCID: ocid1.autonomousdatabase...<br>    isAutoScalingEnabled: true<br>ociConfig:<br>    configMapName: oci-cred<br>    secretName: oci-privatekey</pre><pre>$ kubectl apply -f update_adb.yaml <br>autonomousdatabase.database.oracle.com/adbsample configured</pre><p>If we describe our autonomous database again, we should now find “Is Auto Scaling Enabled: true”. Or we can check the value in the OCI Console:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/846/1*8FgUet8HEVCnDMEyy6onIQ.png" /></figure><p>Similarly, we can modify the initial number of CPUs for the autonomous database by either setting the value of the “cpuCoreCount” parameter in the YAML descriptor and applying the changes:</p><pre>apiVersion: database.oracle.com/v1alpha1<br>kind: AutonomousDatabase<br>metadata:<br>  name: adbsample               <br>spec:<br>  details:<br>    autonomousDatabaseOCID: ocid1.autonomousdatabase...<br>    cpuCoreCount: 2<br>ociConfig:<br>    configMapName: oci-cred<br>    secretName: oci-privatekey</pre><p>Or by executing:</p><pre>$ kubectl edit AutonomousDatabase adbsample -n atp</pre><p>This will allow us to edit the YAML descriptor as if we were working with a “vi” editor and apply the changes as soon as we save them.</p><p>In both cases, we can verify that the database scales properly by listing our autonomous databases, where the state should be “SCALE_IN_PROGRESS” if the scaling process is still ongoing, and OCPUS=2.</p><pre>$ kubectl get AutonomousDatabase<br>NAME                 DISPLAY NAME      DB NAME           STATE               DEDICATED   OCPUS   STORAGE (TB)   WORKLOAD TYPE   CREATED<br>adbsample            VMDemoADBOKE1     VMDemoADBOKE1     SCALE_IN_PROGRESS   false       2       1              OLTP            2024-03-07 10:25:28 UTC</pre><p>And the OCI Console should show the updated value:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/576/1*E5GfoQyquLBAInc-Nq_vGg.png" /></figure><p>Similarly to how we’ve done with auto-scaling or the number of CPUs, we can update almost any parameter of our autonomous database configuration: Storage, name, etc. Some parameters, such as the OCID, cannot be modified.</p><h4>Summary</h4><p>As we have seen, despite working in Kubernetes with a database running in Oracle Cloud, we can have control over all operations and take advantage of the benefits of autonomous databases, such as auto-scaling.</p><p>However, it’s not always possible to work with autonomous databases; either because you don’t have access to them by not working in OCI, or simply because you prefer to work with more customizable databases where you have more control. Does this mean that in those cases, I have no choice but to work with databases running inside the Kubernetes cluster?</p><p>No, there is the option of <strong>extending the concept of “containers” to the Oracle database</strong> and working from Kubernetes with those “containers” running in the database rather than in Kubernetes. This functionality of the database is called <strong>MULTITENANT</strong>, and we will explore its significant advantages and how to work with it in the next post of this series.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1c317a468bef" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Oracle Database and Kubernetes: Creating an Oracle Autonomous Database from Kubernetes]]></title>
            <link>https://medium.com/@vmendo/oracle-database-and-kubernetes-creating-an-oracle-autonomous-database-from-kubernetes-1637204dffc4?source=rss-ddfe9cbc5a5d------2</link>
            <guid isPermaLink="false">https://medium.com/p/1637204dffc4</guid>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[kubernetes-operator]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[autonomous-database]]></category>
            <category><![CDATA[kubernetes]]></category>
            <dc:creator><![CDATA[Victor Mendo]]></dc:creator>
            <pubDate>Fri, 05 Apr 2024 10:52:18 GMT</pubDate>
            <atom:updated>2024-04-08T08:24:26.252Z</atom:updated>
            <content:encoded><![CDATA[<p>In the <a href="https://medium.com/@vmendo/oracle-database-and-kubernetes-running-databases-within-k8s-cluster-20a5beadfaaf">previous chapter</a> of this blog series, we’ve seen how to instantiate and manage a containerized database within the Kubernetes cluster.</p><p>Our development team will be delighted; they can provision a database in just a few minutes and start working. However, we still have several issues to resolve. We need to figure out how to secure all those databases, how they scale in case of load increases, what to do in case of failover…and it is great if I have licenses to run the databases but we can’t do pay-as-you-go.</p><p>In summary, we’ve improved the lives of developers, but the lives of architects and DBAs don’t change much.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/530/1*l2evzY6jq6YJBRakI4sctA.png" /></figure><h4>Why Autonomous Database?</h4><p>Autonomous databases in Oracle Cloud solve many of these problems:</p><ul><li>Self-driving: Automate database provisioning, tuning, and scaling</li><li>Self-securing: Automate data protection and security</li><li>Self-repairing: Automate failure detection, failover, and repair</li><li>Bring-Your-Own-License or Pay-as-you-go.</li></ul><p>They seem like the ideal solution. Now we just need our developers to be able to use them in a straightforward way. Of course, Autonomous are cloud databases. We can use <strong>Terraform</strong>, or the <strong>OCI command line</strong> interface, or the <strong>OCI Rest API</strong> to create them within our CI/CD pipelines.</p><p>But our developers are very familiar with the Kubernetes deployment approach; a <strong>declarative mode based on YAML</strong> files.</p><p>Oracle Database Operator for Kubernetes precisely allows us to bridge both worlds. <br>The controller of the operator responsible for autonomous databases will translate that descriptor into calls to the OCI REST API, eliminating complexity for the developer.</p><p><strong>The Autonomous Database descriptor file</strong></p><p>Let’s see how to create our first autonomous database from the Kubernetes cluster. We’ll start with the descriptor we’re going to use:</p><pre>apiVersion: database.oracle.com/v1alpha1<br>kind: AutonomousDatabase<br>metadata:<br>  name: adbsample<br>spec:<br>  details:<br>    # Update compartmentOCID with your compartment OCID.<br>    compartmentOCID: ocid1.compartment.oc1.....jea<br>    # The dbName must begin with an alphabetic character and can contain a maximum of 14 alphanumeric characters. Special characters are not permitted. The database name must be unique in the tenancy.<br>    dbName: VMDemoADBOKE1<br>    displayName: VMDemoADBOKE1<br>    cpuCoreCount: 1<br>    adminPassword:<br>      # Comment out k8sSecret and uncomment ociSecret if you pass the admin password using OCI Secret.<br>      k8sSecret:<br>        # The Name of the K8s secret where you want to hold the password of the ADMIN account.<br>        name: admin-password<br>      # ociSecret:<br>      #   # The OCID of the OCI Secret that holds the password of the ADMIN account. It should start with ocid1.vaultsecret... .<br>      #   ocid: ocid1.vaultsecret...<br>    dataStorageSizeInTBs: 1<br>    isAutoScalingEnabled: true<br><br>    # networkAccess:<br>    # All networking options are possible here<br>    #   - 1.1.0.0/16<br><br>  # Authorize the operator with API signing key pair. Comment out the ociConfig fields if your nodes are already authorized with instance principal.<br>  # ociConfig:<br>    configMapName: oci-cred<br>    secretName: oci-privatekey</pre><p>Let’s examine it in more detail:</p><ul><li><strong><em>kind</em></strong><em>: AutonomousDatabase: </em>In this case we want to create an AutonomousDatabase resource (CRD).</li><li><strong><em>metadata</em></strong><em>: </em>We are setting how Kubernetes will identify this resource <em>(</em><strong><em>name: adbsample</em></strong><em>)</em></li></ul><p>Now, we are going to provide the database details.</p><ul><li><strong><em>compartmentOCID</em></strong><em>: </em>We must provide the OCI compartment where we are going to provision the database</li><li><strong><em>dbName</em></strong><em>: </em>We set the dbName (It must be unique in the OCI Tenancy) and the name to be displayed (<strong><em>displayName</em></strong><em>)</em></li><li><strong><em>cpuCoreCount: </em></strong>We set the initial cpu core count assigned to this DB. Remember, Autonomous could scale x3 this number.</li><li><strong><em>dataStorageSizeInTBs</em></strong>: It will be the initial capacity for our Database.</li><li><strong><em>isAutoScalingEnabled: </em></strong>we need to specify that we want our database to be able to scale automatically, the default is false.</li></ul><p>Of course, we will need to set the admin password we will set for this new database. As usual, we will use a Kubernetes secret:</p><ul><li><strong><em>adminPassword: k8sSecret: name:</em></strong> admin-password</li></ul><p>An important point will be networking. We will have to connect our Autonomous database to a virtual cloud network and set up the routes and rules so that our applications can access and maintain our database securely. Autonomous can be deployed with different access policies (PUBLIC, RESTRICTED, PRIVATE). Explaining all these network topics is beyond the scope of this blog, so we will use the default (PUBLIC) which requires less configuration. However, the operator allows us to configure any of these modes.</p><p>Finally, the operator will invoke the OCI Rest API, and OCI is secure by default. We will need to provide some credentials, and it should give the right permissions to create Autonomous Databases in the given compartment. We will create ConfigMap and a Kubernetes Secret to provide these credentials.</p><pre>ociConfig:<br>     configMapName: oci-cred<br>     secretName: oci-privatekey</pre><p>It seems that before we can create our autonomous database, we need a few things first.</p><ul><li>We need the <strong>Comparment OCID</strong></li><li>We need to create a Kubernetes Secret with the password we want to set for the administration user.</li><li>And lastly, we’ll need another secret with OCI credentials.</li></ul><h4>Obtaining the Comparment OCID</h4><p>An OCI Compartment provides a way to organize and isolate cloud resources within Oracle Cloud Infrastructure. It helps in managing access control, resource placement, and billing. We will search for the compartment where we’ll create our autonomous database to obtain the OCID. Of course, you can create a new one if you have the right permissions.</p><p>To do this, in the Navigation menu, we select the “Identity &amp; Security” option. Now, we can select “Compartments”:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/829/1*kc89EFKU3260pj-6v5Xu0w.png" /></figure><p>Now the console displays the Compartments. We need to locate the one we want to use and click in the compartment name. It will open the compartment details. There is the compartment OCID and we can copy and use it in our descriptor.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/990/1*JT52t9ZJchFfYI0rhT53Rw.png" /></figure><h4>Creating the Admin Password Kuberenetes Secret</h4><p>The Kubernes secret to store the Admin password is an easy task, we only need to run one command:</p><pre>$ kubectl create secret generic admin-password --from-literal=admin-password=&#39;password_here&#39; -n oracle-database-operator-system</pre><h4>Providing the OCI credentials</h4><p>Let’s now tackle the most complicated part. We need to create a config-map to store some configuration parameters and a Kubernetes secret to store the private key for establishing the connection securely.</p><p>Complicated not because of its technical difficulty, but because there are several types of credentials and ways to obtain the necessary data.</p><p>We’ll use a simple and well-documented approach; we’ll collect the data from the OCI command line interface (OCI cli) configuration. There is a lot of documentation, blogs, and videos on how to configure OCI CLI available, so we won’t reinvent the wheel by explaining it again:</p><p><a href="https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm">https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm</a></p><p><a href="https://www.youtube.com/watch?v=bY3jQMMHxdw">https://www.youtube.com/watch?v=bY3jQMMHxdw</a></p><p>At this point, we’ll assume that OCI CLI is installed and configured correctly. The configuration to access to your OCI account should be in your home directory:</p><pre>$HOME/.oci </pre><p>In this folder we will find the config file:</p><pre>$ cat config<br>[DEFAULT]<br>user=ocid1.user.oc1……k4geha<br>fingerprint=68:……………:1f<br>key_file=&lt;home_dir&gt;/.oci/oci_api_key.pem<br>tenancy=ocid1.tenancy.oc1…………………6vqa<br>region=eu-frankfurt-1</pre><p>Now, we can use these are values to create the required config-map:</p><pre>$ kubectl create configmap oci-cred \<br>--from-literal=tenancy=&lt;TENANCY_OCID&gt; \<br>--from-literal=user=&lt;USER_OCID&gt; \<br>--from-literal=fingerprint=&lt;FINGERPRINT&gt; \<br>--from-literal=region=&lt;REGION&gt; </pre><p>And the secret:</p><pre>$ kubectl create secret generic oci-privatekey \<br>--from-file=privatekey=&lt;PATH_TO_PRIVATE_KEY&gt;</pre><h4>Provisioning the Autonomous Database</h4><p>We have everything we need, so we can create our first autonomous database from the Kubernetes operator. We just need to tell Kubernetes to apply the desired configuration from our YAML file.</p><pre>$ kubectl apply -f create_adb.yaml<br>autonomousdatabase.database.oracle.com/ adbsample created</pre><p>Normally, we would now check if the PODs of our application are being created, but remember that this database doesn’t run within Kubernetes. How can we verify if we’re creating the autonomous database correctly?</p><p>The first thing that comes to mind is obvious; let’s go to the OCI console to see if we have anything there. Remember always to look in the appropriate compartment and ensure you’re in the correct region. Alright, let’s take a look out of curiosity:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Zgc83tvG7x2UVfU5VXWy_A.png" /></figure><p>But I’m a developer, and I can’t be checking the console to see the status of the database and how to connect to it. Alright, we’ll use the operator to list the autonomous databases that it knows</p><pre>$ kubectl get AutonomousDatabase<br>NAME                 DISPLAY NAME      DB NAME           STATE          DEDICATED   OCPUS   STORAGE (TB)   WORKLOAD TYPE   CREATED<br>adbsample            VMDemoADBOKE1     VMDemoADBOKE1     PROVISIONING   false       1       1              OLTP            2024-03-07 10:25:28 UTC</pre><p>It is provisioning, so If we try <strong>a few seconds</strong> latter:</p><pre>$ kubectl get AutonomousDatabase<br>NAME                 DISPLAY NAME      DB NAME           STATE       DEDICATED   OCPUS   STORAGE (TB)   WORKLOAD TYPE   CREATED<br>adbsample            VMDemoADBOKE1     VMDemoADBOKE1     AVAILABLE   false       1       1              OLTP            2024-03-07 10:25:28 UTC</pre><h4>Getting access to the Autonomous Database</h4><p>We have information about our autonomous database, but what’s missing for our applications is the most important: the connection URL and the wallet with the credentials.</p><p>To connect to an autonomous database, we’ll need a client wallet. This wallet will be protected by a password that we need to provide at the time of download. As always when we need a password (for the Wallet in this case), we’ll create a Secret.</p><pre>$ kubectl create secret generic instance-wallet-password --from-literal=instance-wallet-password=&#39;password_here&#39;</pre><p>To enable our applications to use the wallet, we’ll need to donwload the wallet using this secret, extract the Wallet (it is a .zip file) and create a second Kubernetes Secret containing the data extracted from the wallet. The operator handle all this tasks, but for that, we need to describe what we want.</p><pre>apiVersion: database.oracle.com/v1alpha1<br>kind: AutonomousDatabase<br>metadata:<br>  name: adbsample<br>spec:<br>  details:<br>    autonomousDatabaseOCID: ocid1.autonomousdatabase.oc1....fllpq<br>    wallet:<br>      # Insert a name of the secret where you want the wallet to be stored. The default name is &lt;metadata.name&gt;-instance-wallet.<br>      name: adbsample-instance-wallet<br>      password:<br>        # Comment out k8sSecret and uncomment ociSecret if you pass the admin password using OCI Secret.<br>        k8sSecret:<br>          # The Name of the K8s secret where you want to hold the password of the ADMIN account.<br>          name: instance-wallet-password<br>        # ociSecret:<br>        #   # The OCID of the OCI Secret that holds the password of the ADMIN account. It should start with ocid1.vaultsecret... .<br>        #   ocid: ocid1.vaultsecret...<br>  # Authorize the operator with API signing key pair. Comment out the ociConfig fields if your nodes are already authorized with instance principal.<br>  ociConfig:<br>    configMapName: oci-cred<br>    secretName: oci-privatekey</pre><p>We are setting that we would like to create a secret “<strong>adbsample-instance-wallet</strong>” for our “<strong>adbsample</strong>” object. We will use the kubernetes secret we have created for the wallet password (<strong>instance-wallet-password</strong>) and we will use the OCI credentials we used to create this database.</p><p>In the current operator version, we need to identify the Autonomous Database with its <strong>autonomousDatabaseOCID</strong>. It is a unique identifier assigned to each autonomous database instance within the Oracle Cloud Infrastructure (OCI) environment, and the operator will use this value in the REST API call to OCI to identify the database we want to work with. Let’s see how we can obtain this value.</p><p>We can ask to Kubernetes to describe the autonomos database running this command:</p><pre>$ kubectl describe AutonomousDatabase adbsample</pre><p>The output will be all the information about our database; number of CPUs, connection strings… and of course the the autonomous database OCID. If we want to get only the OCID, then we can run the command adding some filtering:</p><pre>$ kubectl describe AutonomousDatabase adbsample -n atp | grep &quot;Autonomous Database OCID&quot; | awk &#39;{print $NF}&#39;<br>ocid1.autonomousdatabase.oc1.eu-frankfurt-1.anth…………..llpq</pre><p>Now that we have all the data we need to create the wallet secret in our descriptor, we can apply it:</p><pre>$ kubectl apply -f wallet_adb.yaml</pre><p>We can describe the secret and check that It contains the wallet info:</p><pre>$ kubectl describe secret adbsample-instance-wallet<br>Name:         adbsample-instance-wallet<br>Namespace:    atp<br>Labels:       app=adbsample<br>Annotations:  &lt;none&gt;<br><br>Type:  Opaque<br><br>Data<br>====<br>README:            3057 bytes<br>cwallet.sso:       5349 bytes<br>ewallet.p12:       5304 bytes<br>keystore.jks:      3190 bytes<br>ewallet.pem:       5710 bytes<br>ojdbc.properties:  691 bytes<br>sqlnet.ora:        114 bytes<br>tnsnames.ora:      1350 bytes<br>truststore.jks:    2056 bytes</pre><p>It looks nice! There is the tnsnames.ora, so we can use it to obtain the connection strings for this database:</p><pre>$ kubectl get secret adbsample-instance-wallet -o=jsonpath=&#39;{.data.tnsnames\.ora}&#39; | base64 --decode<br>vmdemoadboke1_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=myadbhost.com))(connect_data=(service_name=myadb_servcie_namehigh.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))<br><br>vmdemoadboke1_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=myadbhost.com))(connect_data=(service_name=myadb_servcie_namelow.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))<br><br>vmdemoadboke1_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=myadbhost.com))(connect_data=(service_name=myadb_servcie_namemedium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))<br><br>vmdemoadboke1_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=myadbhost.com))(connect_data=(service_name=myadb_servcie_nametp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))<br><br>vmdemoadboke1_tpurgent = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=myadbhost.com))(connect_data=(service_name=myadb_servcie_nametpurgent.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))</pre><p>We have been able to provision an autonomous database and have all the information we need for our applications to use it.</p><h4>What is next?</h4><p>However, there are some additional operations we can perform, such as stopping the autonomous database and saving on our consumption if we are not using it. Autonomous databases can auto-scale up to three times the number of CPUs we have set, but we can also change that number of CPUs with the operator. And of course, we can also terminate our autonomous database when we no longer need it.</p><p>We will explore all these operations in the <a href="https://medium.com/@vmendo/oracle-database-and-kubernetes-managing-an-autonomous-database-from-kubernetes-1c317a468bef">next post</a> of this series</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1637204dffc4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Oracle Database and Kubernetes: Running databases within K8S Cluster]]></title>
            <link>https://medium.com/@vmendo/oracle-database-and-kubernetes-running-databases-within-k8s-cluster-20a5beadfaaf?source=rss-ddfe9cbc5a5d------2</link>
            <guid isPermaLink="false">https://medium.com/p/20a5beadfaaf</guid>
            <category><![CDATA[kubernetes-operator]]></category>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[oracle-database]]></category>
            <dc:creator><![CDATA[Victor Mendo]]></dc:creator>
            <pubDate>Tue, 12 Mar 2024 15:15:08 GMT</pubDate>
            <atom:updated>2024-04-05T11:42:14.811Z</atom:updated>
            <content:encoded><![CDATA[<p>In the <a href="https://medium.com/@vmendo/oracle-database-and-kubernetes-overview-6584710228e7">previous chapter of this blog</a>, we saw that the operator offers us several options for running and managing our Oracle database from Kubernetes: Autonomous, DataBase Cloud System (DBCS), Pluggable Database, and containerized databases running within the Kubernetes. In this chapter, we’ll focus on the latter option, which may be the most obvious when we think about databases and Kubernetes.</p><p>As of today, the operator supports three types of deployments as containerized databases within the Kubernetes cluster:</p><ul><li>Single Instance</li><li>Sharded</li><li>Single Instance with Data Guard. In preview status in version 1.0.0 of the operator, the one available as I write this blog.</li></ul><p>In this blog post, we’ll see how to deploy a Single Instance database in Kubernetes.</p><p>Before we continue, a brief note regarding licensing. Oracle has a specific licensing policy for all its products running in the Kubernetes cluster, including the database, which you can check at the following link:</p><p><a href="https://www.oracle.com/a/tech/docs/running-and-licensing-programs-in-containers-and-kubernetes.pdf">https://www.oracle.com/a/tech/docs/running-and-licensing-programs-in-containers-and-kubernetes.pdf</a></p><h4>Oracle Database Docker Image</h4><p>The first thing we’ll need is a Docker image containing the database. Of course, we can work with our own images, but it’s always good to start with a base, and if Oracle provides it, even better. Oracle has a Container Registry where we can download images for many of their products: Oracle Container Registry</p><p><a href="https://container-registry.oracle.com/">Home</a></p><p>Even though they are images, they are still products subject to Oracle’s usual licensing terms, so we’ll need to register and accept the terms and conditions of the licenses. Let’s quickly see how.</p><p>First, we need to Sign In. You can use your already existing oracle account. If You don’t have it, you can create a new one. We’ll also need these credentials to be able to pull images from Kubernetes, as we’ll see later on:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/674/1*kXYLENL7ricYsRKLDoo3Ow.png" /></figure><p>Once registered, we’ll search for the images we want to work with. In our case, under the “Database” section.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/908/1*l2qnaJMpWPXtJ_JX2IwQ0A.png" /></figure><p>This is where we need to review and accept the Oracle Standard Terms and Restrictions. We would need to click the “Continue” button for all the images we want to work with, which would open the window to read and accept the Terms and Restrictions. In my case, I already have it done for the Enterprise image.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/773/1*hIvm_cv7uqblVCDTfyp-Pw.png" /></figure><p>Now we are ready to push this image and work with it to create our database.</p><h4>Database Deployment Descriptor</h4><p>We can now focus on “describing” the database we are going to create in our Kubernetes cluster. In the previous post, I made reference to the YAML file we are going to use:</p><p><em>singleinstancedatabase_create.yaml</em></p><pre>apiVersion: database.oracle.com/v1alpha1<br>kind: SingleInstanceDatabase     <br>metadata:<br>  name: sidb-sample                 <br>spec:                                          <br>  sid: ORCL1                               <br>  edition: enterprise                    <br><br>  adminPassword:                       <br>      secretName: db-admin-secret<br><br>  charset: AL32UTF8                   <br>  pdbName: orclpdb1<br>  archiveLog: true<br><br>  image:                                      <br>    pullFrom: container-registry.oracle.com/database/enterprise:latest<br>    pullSecrets: oracle-container-registry-secret<br><br>  persistence:<br>    size: 100Gi<br>    storageClass: &quot;oci-bv&quot; <br>    accessMode: &quot;ReadWriteOnce&quot;<br><br>  replicas: 1</pre><p>Let’s examine it in more detail:</p><ul><li><em>kind: SingleInstanceDatabase: </em>We set the type of resource we are going to use (remember the CRDs from the previous chapter).</li><li><em>name: sidb-sample: </em>This is how Kubernetes will identify this resource.</li></ul><p>Now we are going to provide the database details</p><ul><li><em>sid: ORCL1</em>: This the Oracle System Identifier. Basically, the database name.</li><li><em>edition: enterprise: </em>The edition we will use (Enterprise, Free, XE…)</li></ul><p>We’ll need to set the password for the Admin user, which we’ll read from a Kubernetes Secret:</p><ul><li><em>adminPassword: secretName: db-admin-secret</em></li></ul><p>We set some additional configuration for the database like charset, pdb name and Archive Log:</p><ul><li><em>charset: AL32UTF8<br>pdbName: orclpdb1<br>archiveLog: true</em></li></ul><p>To download the container image, we will need to provide the image name to be used from the repository and the credentials to authenticate:</p><ul><li><em>image:<br> pullFrom: container-registry.oracle.com/database/enterprise:latest<br> pullSecrets: oracle-container-registry-secret</em></li></ul><p>And since we don’t want to lose data, we need to specify the type of storage we want to use. I am working in OCI with a OKE cluter, so I will set OCI Block Volume for persistence:</p><ul><li><em>persistence:<br> size: 100Gi<br> storageClass: “oci-bv” <br> accessMode: “ReadWriteOnce”</em></li></ul><p>Finally, this is a single instance, so I want only one POD running:</p><ul><li><em>replicas: 1</em></li></ul><p>Well, now that we understand the descriptor, it seems like there are a couple of things we need before creating the database: <strong><em>A Kubernetes Secret to store the repository credentials and another one for the administration user’s password</em></strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cDH9krGMG7-cmUnI4UUeOg.png" /></figure><h4>Creating the required Kubernetes Secrets</h4><p>Let’s start with the password for the administration user. We can do this with the “kubectl create secret” command, or we can create a new descriptor indicating that we want to create a secret:</p><p><em>si_admin_secret.yaml</em></p><pre>apiVersion: v1<br>kind: Secret<br>metadata:<br>  name: db-admin-secret<br>type: Opaque<br>stringData:<br>   ## Specify your DB password here<br>   oracle_pwd: &quot;&lt;MyPasswordHere&gt;&quot;  </pre><p>Remember, your password must meet the minimum security requirements in terms of length, special characters, etc…</p><p>Alright, now we can create the secret by asking Kubernetes to apply the description we’ve created:</p><pre>kubectl apply -f si_admin_secret.yaml </pre><p>We can proceed with the secret we need to download the image from the repository. In this case, we will use the “<em>kubectl create secret</em>” command. Remember, these are the same credentials we use to sign-in to the Oracle Container Registry.</p><pre>$ kubectl create secret docker-registry oracle-container-registry-secret --docker-server=container-registry.oracle.com --docker-username=&#39;&lt;oracle-sso-email-address&gt;&#39; --docker-password=&#39;&lt;oracle-sso-password&gt;&#39; --docker-email=&#39;&lt;oracle-sso-email-address&gt;&#39; </pre><p>We can check if both secrets have been created using the “<em>kubectl get secrets</em>” command:</p><pre>$ kubectl get secrets <br>NAME                               TYPE                             DATA   AGE<br>db-admin-secret                    Opaque                           1      15m<br>oracle-container-registry-secret   kubernetes.io/dockerconfigjson   1      16m</pre><h4>Running our Single Instance in our Kubernetes Cluster</h4><p>We have everything we need now, so we can ask Kubernetes to create our database. We’ll tell it to apply the desired configuration from our descriptor file.</p><pre>$ kubectl apply -f singleinstancedatabase_create.yaml<br>singleinstancedatabase.database.oracle.com/sidb-sample created</pre><p>Now, We should have a POD starting in our namespace:</p><pre>$ kubectl get pods <br>NAME                READY   STATUS     RESTARTS   AGE<br>sidb-sample-pduk4   0/1     Init:0/2   0          43s</pre><p>The operator can also provide us more details about the database. For example, we can ask for all single instance databases running in this namespace:</p><pre>$ kubectl get singleinstancedatabases <br>NAME          EDITION      STATUS     ROLE          VERSION       CONNECT STR                TCPS CONNECT STR   OEM EXPRESS URL<br>sidb-sample   Enterprise   Creating   Unavailable   Unavailable   my_db_ip:1521/ORCL1   Unavailable        Unavailable</pre><p>Right now, we only have one SI configured. If there were more, it would give us the details of all of them. If we only want information about just one, we can specify its name in the command:</p><pre>$ kubectl get singleinstancedatabases sidb-sample <br>NAME          EDITION      STATUS     ROLE          VERSION       CONNECT STR                TCPS CONNECT STR   OEM EXPRESS URL<br>sidb-sample   Enterprise   Creating   Unavailable   Unavailable   my_db_ip:1521/ORCL1   Unavailable        Unavailable</pre><p>Our POD database is still creating, so we will need to <strong>wait a few minutes until it is ready</strong>. This is an image with a large size, and we need to instantiate and configure the database with the specified parameters.</p><p>In my environment, it takes about 8 minutes to be ready.:</p><pre>$ kubectl get pods <br>NAME                READY   STATUS    RESTARTS   AGE<br>sidb-sample-pduk4   1/1     Running   0          8m44s</pre><p>Now our database POD is running. We can check the database status again:</p><pre>$ kubectl get singleinstancedatabases <br>NAME          EDITION      STATUS     ROLE      VERSION      CONNECT STR           TCPS CONNECT STR   OEM EXPRESS URL<br>sidb-sample   Enterprise   Patching   PRIMARY   21.3.0.0.0   my_db_ip:1521/ORCL1   Unavailable        https://my_db_ip:5500/em</pre><p>Note that the status is <strong>Patching</strong>; Before we can use the database, the latest patches available at the time of the image publication are automatically applied. A few seconds later, our database is ready to be use in “Healthy” status:</p><pre>$ kubectl get singleinstancedatabases <br>NAME          EDITION      STATUS    ROLE      VERSION      CONNECT STR           TCPS CONNECT STR   OEM EXPRESS URL<br>sidb-sample   Enterprise   Healthy   PRIMARY   21.3.0.0.0   my_db_ip:1521/ORCL1   Unavailable        https://my_db_ip:5500/em</pre><p><strong>Getting the Connection Details</strong></p><p>It looks like I have everything I need to work with this database; I can get the connection string and I have the administration password in a Kubernetes Secret. I can integrate this process into my CI/CD pipelines to use this database with my application.</p><p>Well, wait!! My DBA (and Oracle) tells me that I shouldn’t use the connection to the database, but to the Pluggable Database that has been created! Where can I see which connection I should use? Ok, let’s ask for the PDB connection String running:</p><pre>$ kubectl get singleinstancedatabase sidb-sample -o jsonpath=&#39;{.status.pdbConnectString}&#39;<br>My_db_ip:1521/ORCLPDB1</pre><p>If you want to check all the configuration available related to this instance, you can run the command “kubectl describe”.</p><pre>kubectl describe singleinstancedatabase sidb-sample </pre><p>This command will display all the configuration we have specified in our descriptor, as well as all the values applied by default for not including them in the description, and the runtime information about the database’s.</p><h4>Operating our Single Instance</h4><p>Of course, the operator allows us to perform many more administrative actions and manage the lifecycle of our instances: Cloning, patching, or changing the settings specific to the database. You could even configure ORDS on this instance.</p><p>I invite you to visit the operator’s documentation regarding Single Instance databases to review everything you can do:</p><p><a href="https://github.com/oracle/oracle-database-operator/blob/main/docs/sidb/README.md">Oracle Database Operator Single Instance Database</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/959/1*mpum0WReX3DO2ICUPMPQXg.png" /></figure><p>Cool, we now have our first database managed by the operator running within the Kubernetes cluster, and everything We need to use it from our applications.</p><p><strong>Now I have to ensure that this database is secure, that it performs as required at all times, apply the necessary patches when they are available…</strong> I wish there was an option that could do all of this for me.</p><p>Wait, that exists and it’s called Autonomous Database. And in the previous chapter, I set that you can work with it from Kubernetes… ;-)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/725/1*3jJFwl8aByVjZiZMRA7KEg.png" /></figure><p>We’ll see this in the <a href="https://medium.com/@vmendo/oracle-database-and-kubernetes-creating-an-oracle-autonomous-database-from-kubernetes-1637204dffc4">next post</a> of this series.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=20a5beadfaaf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Oracle Database and Kubernetes: Overview]]></title>
            <link>https://blog.devops.dev/oracle-database-and-kubernetes-overview-6584710228e7?source=rss-ddfe9cbc5a5d------2</link>
            <guid isPermaLink="false">https://medium.com/p/6584710228e7</guid>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[oracle-cloud]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[oracle]]></category>
            <dc:creator><![CDATA[Victor Mendo]]></dc:creator>
            <pubDate>Mon, 11 Mar 2024 14:52:01 GMT</pubDate>
            <atom:updated>2024-03-14T17:34:27.697Z</atom:updated>
            <content:encoded><![CDATA[<h4>Making Oracle Database Kubernetes Native</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/830/1*idbKJHHfYScynQP9Icwtrw.png" /></figure><p>Recently, I have been working with some customers interested in exploring how to work with databases in Kubernetes. Typically, when discussing running databases in a Kubernetes environment, the first thing that comes to mind is running the database in a pod within the Kubernetes cluster itself. Is this possible? Yes, absolutely.</p><p>However, we have architectures (or platforms) that are much more efficient, agile, with simpler management, and more secure than Kubernetes for running databases. Exadata/ExaCC or databases on Oracle Cloud, including Autonomous Databases, are prime examples.</p><p>It would be great to combine the best of both worlds: working with the database as just another component of our applications in Kubernetes while also leveraging all the advantages offered by autonomous databases, Exadata, or simply utilising existing database deployments within our companies.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/924/1*0eAy83KDAXGmHDwvoRl5pQ.png" /></figure><p>Good news! We can do it. The <strong>Database Operator for Kubernetes (the OraOperator)</strong> allows us not only to manage databases running in Kubernetes but also to manage databases running outside the cluster. We can provision an autonomous database in OCI or create a Pluggable Database in an existing database running, for example, on ExaCC.</p><h4>Why we need an Oracle Database Operator?</h4><p>Let’s start from the beginning. Let’s define what the Oracle Database Operator for Kubernetes is:</p><p><em>“The Oracle Database Operator for Kubernetes is a tool designed to simplify the management of Oracle databases within Kubernetes clusters. It automates various database lifecycle tasks, such as provisioning, scaling, and backup, by leveraging Kubernetes native resources and capabilities. This operator extends Kubernetes’ functionality to handle Oracle Database deployments seamlessly, providing users with a more streamlined and efficient way to manage their database workloads in containerized environments.”</em></p><p>Okay. Interesting, but it could be that it’s not enough to understand why we need an operator and everything it can offer us. Let’s see an example of how to deploy a simple application on Kubernetes. If you’re a developer familiar with Kubernetes, you might skip this part ;-)</p><p>Deploying any component in Kubernetes is done declaratively. For example:<br> “<strong><em>I want to deploy two copies of my hello-world application, which I have available in the Docker image “image-hello-world” and It should be listening on port 80</em></strong>”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/793/1*agNQGVtk_VH_Yvsef30_iw.png" /></figure><p>Kubernetes doesn’t understand (at the moment) human language, so we need to specify it in a YAML file, which could look something like this:</p><pre>apiVersion: apps/v1<br>kind: Deployment<br>metadata:<br>  name: hello-world-deployment<br>  labels:<br>    app: hello-world<br>spec:<br>  replicas: 2<br>  selector:<br>    matchLabels:<br>      app: hello-world<br>  template:<br>    metadata:<br>      labels:<br>        app: hello-world<br>    spec:<br>      containers:<br>      - name: hello-world-container<br>        image: k8s.gcr.io/echoserver:1.4<br>        ports:<br>        - containerPort: 8080<br><br>---<br>apiVersion: v1<br>kind: Service<br>metadata:<br>  name: hello-world-service<br>spec:<br>  type: NodePort<br>  selector:<br>    app: hello-world<br>  ports:<br>    - protocol: TCP<br>      port: 80<br>      targetPort: 8080<br>      nodePort: 30007</pre><p>It seems straightforward, doesn’t it?. So the question is… Can I do the same to deploy my database? This is where the operator comes in.</p><p>Let’s start with something simple: “<strong><em>I want to deploy a single-instance database on my Kubernetes cluster, using my Enterprise license and using the image available in the Oracle Container Repository</em></strong>” We will need the descriptor (yaml):</p><pre>apiVersion: database.oracle.com/v1alpha1<br>kind: SingleInstanceDatabase<br>metadata:<br>  name: sidb-sample<br>  namespace: single-instance<br>spec:<br>  sid: ORCL1<br>  edition: enterprise<br><br>  adminPassword:<br>    secretName: db-admin-secret<br>   charset: AL32UTF8<br>  pdbName: orclpdb1<br>  archiveLog: true<br><br>  image:<br>    pullFrom: container-registry.oracle.com/database/enterprise:latest<br>    pullSecrets: oracle-container-registry-secret<br><br> persistence:<br>    size: 100Gi<br>    storageClass: &quot;oci-bv&quot;<br>    accessMode: &quot;ReadWriteOnce&quot;<br> replicas: 1</pre><p>This way, we describe the database we want to create, and Kubernetes takes care of the rest. It will create a Pod within our cluster. We will look into this descriptor in more detail later on.</p><h4>The OraOperator Architecture</h4><p>But we’ve mentioned that the operator can do much more interesting things, like managing autonomous databases or creating Pluggable Databases in existing databases using Oracle’s Multitenant capabilities. How does it do all of this?.</p><p>Let’s explore a bit more into the architecture of the operator, and in the subsequent posts of this series, we’ll cover the necessary concepts as needed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/876/1*Qbu88eQyigkYR90ruYeFOQ.png" /></figure><p>The Oracle Database Kubernetes Operator is composed of multiple <strong>controllers</strong>, which enable us to manage databases in their various types of deployments.</p><p>In the previous example, the controller will translate the description in our YAML into Kubernetes concepts to create the POD with our database and manage it.</p><p>The controllers that manage databases in OCI (database systems and Autonomous) are responsible for translating the descriptor we provide to our Kubernetes into REST API calls to the OCI API.</p><p>We also use REST to manage Pluggable Databases in a database. Our database doesn’t inherently support REST, unless we use Oracle Rest Data Services (ORDS). We’ll explore what ORDS is in the blog post corresponding to the multitenant controller.</p><h4>Operator Deployment</h4><p>The operator itself is deployed within Kubernetes. Of course, instead of a single operator, we can deploy multiple copies to avoid a single point of failure.</p><p>The installation process is quite straightforward and is well explained in the operator’s documentation:</p><p><a href="https://github.com/oracle/oracle-database-operator">GitHub - oracle/oracle-database-operator: The Oracle Database Operator for Kubernetes (a.k.a. OraOperator) helps developers, DBAs, DevOps and GitOps teams reduce the time and complexity of deploying and managing Oracle Databases. It eliminates the dependency on a human operator or administrator for the majority of database operations.</a></p><p>The only prerequisite is to install cert-manager. Well, of course, I need a Kubernetes cluster… In my case, I’ll be deploying everything from an Oracle Kubernetes Engine running on OCI. Let’s start by deploying the cert-manager.</p><pre>kubectl apply -f https://github.com/cert-manager/cert-manager/releases/download/v1.12.0/cert-manager.yaml</pre><p>And then, we can install the operator using the descriptor available in the operator site:</p><pre>kubectl apply -f https://raw.githubusercontent.com/oracle/oracle-database-operator/main/oracle-database-operator.yaml</pre><p>It will install all the operator components in the oracle-database-operator-system namespace:</p><pre>kubectl get all -n  oracle-database-operator-system<br><br>NAME                                                               READY   STATUS    RESTARTS   AGE<br>pod/oracle-database-operator-controller-manager-665874bd57-dlhls   1/1     Running   0          28s<br>pod/oracle-database-operator-controller-manager-665874bd57-g2cgw   1/1     Running   0          28s<br>pod/oracle-database-operator-controller-manager-665874bd57-q42f8   1/1     Running   0          28s<br> <br>NAME                                                                  TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGE<br>service/oracle-database-operator-controller-manager-metrics-service   ClusterIP   10.96.130.124   &lt;none&gt;        8443/TCP   29s<br>service/oracle-database-operator-webhook-service                      ClusterIP   10.96.4.104     &lt;none&gt;        443/TCP    29s<br><br>NAME                                                          READY   UP-TO-DATE   AVAILABLE   AGE<br>deployment.apps/oracle-database-operator-controller-manager   3/3     3            3           29s<br> <br>NAME                                                                     DESIRED   CURRENT   READY   AGE<br>replicaset.apps/oracle-database-operator-controller-manager-665874bd57   3         3         3       29s</pre><p>We can see the operator deployment, the services and the PODs in Running status. It is ready!</p><p>We can check also the <strong>CRDs</strong> or “<strong><em>Custom Resource Definitions</em></strong>”. A Custom Resource Definition allows us to define custom resources that extend Kubernetes capabilities beyond the default set of resources provided by Kubernetes itself. So, we should have a CRD for each type of Oracle deployment supported:</p><pre>kubectl get crd<br> <br>NAME                                             CREATED AT<br>autonomouscontainerdatabases.database.oracle.com   2024-01-22T13:27:15Z<br>autonomousdatabasebackups.database.oracle.com      2024-01-22T13:27:15Z<br>autonomousdatabaserestores.database.oracle.com     2024-01-22T13:27:15Z<br>autonomousdatabases.database.oracle.com            2024-01-22T13:27:15Z<br>cdbs.database.oracle.com                           2024-01-22T13:27:16Z<br>dataguardbrokers.database.oracle.com               2024-01-29T12:02:11Z<br>dbcssystems.database.oracle.com                    2024-01-22T13:27:16Z<br>oraclerestdataservices.database.oracle.com         2024-01-22T13:27:16Z<br>pdbs.database.oracle.com                           2024-01-22T13:27:16Z<br>shardingdatabases.database.oracle.com              2024-01-22T13:27:16Z<br>singleinstancedatabases.database.oracle.com        2024-01-22T13:27:16Z</pre><p>It seems we have everything we need, so in the next entries, we’ll see how to work with each of the different controllers of the operator. In my upcoming blog entries, we’ll explore each of the options we have for managing databases with the operator.</p><p><a href="https://medium.com/@vmendo/oracle-database-and-kubernetes-running-databases-within-k8s-cluster-20a5beadfaaf">Let’s start creating an Oracle Database within K8S Cluster.</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6584710228e7" width="1" height="1" alt=""><hr><p><a href="https://blog.devops.dev/oracle-database-and-kubernetes-overview-6584710228e7">Oracle Database and Kubernetes: Overview</a> was originally published in <a href="https://blog.devops.dev/">DevOps.dev</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>