Oracle DBA Lesson 52A — Access Remote Data Through a Proxy

A proxy PDB gives a local container context for access to another PDB, called the referenced PDB. In this example, LABPDB_PROXY belongs to the local container database and references LABPDB in another container database.

Oracle DBA Lesson 52A — Access Remote Data Through a Proxy

The main design question is where application work and data should live. A proxy supports local access while application SQL normally executes in the referenced PDB. A regular clone supplies an independent copy. Relocation moves the database to a new location.

Follow the connection

There are two different connection stages:

1. Creation: the local root uses a database link to establish the reference. The link can connect to the remote CDB root or directly to the referenced PDB, with the appropriate account and privileges.
2. Ordinary operation: the proxy communicates directly with the referenced database through its listener host and port. The database link used for creation leaves this communication path.

For the example, draw the operating path as:

Client → local LABPDB_PROXY → referenced listener → remote LABPDB
                                  host + port       executes SQL
Client ← local LABPDB_PROXY ← results from remote execution

A client still needs a correctly configured local service for access to the proxy. The local database also needs a working route to the referenced listener. A successful client connection to the local listener establishes only the first part of this path.

When investigating a failure, record which connection stage failed. Check the local service and proxy state for a local connection problem. For a remote execution problem, check the referenced listener, the network route, the referenced PDB's open state, and the session's applicable permissions. Record the actual error and time. The error depends on the failing component and the release update.

The usual defaults described in Oracle's proxy guide are listener port 1521 and the host of the CDB containing the referenced PDB. A different endpoint requires the documented proxy-related listener settings. An Oracle Net alias that successfully establishes the creation link does not automatically validate the later direct endpoint. See creating a proxy PDB for direct communication after creation, and CREATE PLUGGABLE DATABASE for the referenced listener settings.

Understand where reads and writes execute

When the proxy is the current container, queries, data manipulation language (DML), and data definition language (DDL) normally execute in the referenced PDB. Results return through the proxy.

For example, suppose COURSE_OWNER.ORDERS resides in the referenced LABPDB. A query submitted in the proxy reads that remote table:

SELECT order_id, status, amount
FROM course_owner.orders
WHERE order_id = 1001;

The condition selects an example key. It does not guarantee that the row exists. Capture the actual result in the designated lab. Access requires the applicable table privileges. If an authorized application session updates an order through the proxy and commits, the application data changes in the referenced PDB. Treat that action with the same ownership, transaction, and recovery conditions as a direct write to the referenced database.

A proxy is not inherently read-only. Supported work depends on privileges and the referenced database's open mode. Initial creation requires the reference to be READ WRITE. Its open mode can change after creation. An application DML operation will need a write-capable reference and appropriate permissions.

Local administration has an important exception. ALTER PLUGGABLE DATABASE and ALTER DATABASE issued with the proxy as the current container affect the proxy itself. An ALTER PLUGGABLE DATABASE issued from the local root to open or close the proxy also acts on that proxy. Manage the referenced PDB's open state separately. For example, closing LABPDB_PROXY does not close LABPDB in the remote CDB.

Keep local administrative identity evidence separate from application query evidence. An application query in a proxy is normally executed remotely. Establish the local CDB and exact proxy GUID from the local root, and establish the referenced CDB and PDB independently through the instructor's direct reference connection. See creating a proxy PDB for where application SQL executes and for this administration exception.

Interpret storage and availability

Oracle creates local metadata files for the proxy. Its example copies SYSTEM and SYSAUX files into locations determined by Oracle Managed Files or the configured file-name conversion. Include these files in local capacity and recovery planning.

The application tables remain in the referenced database. The proxy does not supply an independent cached application database for a remote outage. If the referenced PDB is unavailable, application SQL that needs it cannot continue serving rows through the proxy. Restoring the local proxy alone therefore cannot restore the referenced application data.

Plan recovery for both responsibilities: the local proxy's configuration and files, and the referenced PDB's application data. Oracle's creation procedure includes a backup of the newly integrated PDB. The instructor must determine and test the applicable backup and recovery procedure for the actual setup. Keep the reference's existing recovery protection in place. See creating a proxy PDB for the local metadata files and that creation-time backup.

Choose the result you need

Required result	Suitable choice	What happens to application data and work
A separately usable copy for isolated work	Regular clone	Copy and source can develop independently after cloning
Application database runs in another CDB	Relocation	Reviewed cutover transfers work to the destination
Local access context for an existing remote database	Proxy	Application SQL and data remain dependent on the reference

The clone comparison means a regular full clone, rather than a storage snapshot copy or a refreshable clone. Those variants have additional dependencies and lifecycle rules. Relocation requires its own cutover, client, and recovery checks. Choosing a proxy does not perform that cutover.

Choose by the required outcome before writing a command. For example, a team needing to test changes against an isolated copy should select the clone design. A team moving its application's database to a new CDB should select a relocation plan. A team needing local container access to an existing remote database can evaluate a proxy, including the remaining network and remote availability dependencies. See introduction to the multitenant architecture for clone, relocation, and proxy outcomes.

A proxy also differs from an ad hoc database-link query such as SELECT ... FROM orders@some_link. The proxy provides a local PDB context and then uses direct referenced-PDB communication. Avoid assuming either design is always faster; assess the actual workload and topology.

Optional provisioning example and conditions

The primary practice is a design exercise and needs no database connection. Provisioning is optional, off-video, and restricted to instructor-approved disposable databases. Complete the course recovery preparation and PDB cleanup material before this exercise. Use ordinary, single-instance Oracle Database 19c CDBs. Application containers, RAC, encrypted transfer, and application-root synchronization require separate reviewed procedures.

The instructor must establish:

• Exact release update, edition, licensed feature eligibility, available permitted PDB capacity, compatible database requirements, and a tested reset and recovery point. An installed feature is not evidence of license entitlement. See Oracle Database licensing information. Actual edition, offering, and PDB eligibility require instructor verification.
• Local CDB open READ WRITE, the authorized root account with commonly granted CREATE PLUGGABLE DATABASE, and separately authorized open, close, and cleanup identities. Creating a PDB does not itself grant the administrative authentication needed for these later operations.
• Referenced LABPDB open READ WRITE at creation, source CDB in local undo and ARCHIVELOG mode, and recorded reference identity and recovery coverage.
• A private COURSE_PROXY_SRC database link owned by the selected local-root account. For this example it connects directly to the referenced LABPDB using an approved account with CREATE PLUGGABLE DATABASE there. A root-connected link uses the separately documented common-account requirements.
• Approved local Oracle Managed Files destination and physical capacity for proxy metadata files. Record DB_CREATE_FILE_DEST in the relevant root scope. Do not substitute arbitrary paths if the destination is missing.
• Correct referenced listener host and port, and network reachability for direct communication after creation, as well as the local client service configuration. Check service names for collisions in the listener namespace.
• Password-authenticated proxy sessions, as required by Oracle's proxy guide. The instructor supplies aliases and credentials through the approved local process. See creating a proxy PDB for those authentication and creation prerequisites.

Confirm local administrative identity before provisioning:

SHOW USER
SHOW CON_NAME
SELECT db_unique_name FROM v$database;
SELECT pdb_name, RAWTOHEX(guid) AS pdb_guid, status, is_proxy_pdb
FROM dba_pdbs
WHERE pdb_name = 'LABPDB_PROXY';

SHOW commands are SQL*Plus client commands. The queries require the approved administrative dictionary access. At the initial name check, an existing row is a stop condition: resolve ownership rather than overwrite someone else's proxy. Match the local CDB identity to the instructor's inventory before using root commands.

After the instructor has validated all prerequisites, the local-root creation example is:

CREATE PLUGGABLE DATABASE labpdb_proxy
  AS PROXY FROM labpdb@course_proxy_src;

AS PROXY selects the proxy design. LABPDB_PROXY is the new local identity. FROM labpdb@course_proxy_src identifies the reference through the creation link. File locations come from the already validated OMF configuration in this example. See CREATE PLUGGABLE DATABASE for AS PROXY FROM, privileges, managed files, and service collisions.

After successful creation, Oracle documents the local proxy as MOUNTED with catalog status NEW. The authorized administrator then performs its first open in read/write mode:

ALTER PLUGGABLE DATABASE labpdb_proxy OPEN READ WRITE;

This open completes initial integration and establishes catalog status NORMAL. It changes the proxy's state. Check the reference independently. The first open must be read/write, rather than read-only. See creating a proxy PDB for that first open.

Inspect the proxy in the local root:

SELECT pdb_name, is_proxy_pdb
FROM dba_pdbs
WHERE pdb_name = 'LABPDB_PROXY';

SELECT pdb_name, RAWTOHEX(guid) AS pdb_guid, status
FROM dba_pdbs
WHERE pdb_name = 'LABPDB_PROXY';

SELECT name, open_mode
FROM v$pdbs
WHERE name = 'LABPDB_PROXY';

IS_PROXY_PDB identifies whether the catalog entry is a proxy. Record the actual indicator and match the name and GUID to the newly created entry. STATUS reports integration state. OPEN_MODE reports the current open state. Record actual rows and any plug-in or creation errors. A NEW, UNUSABLE, restricted, or failed-open condition needs administrator review before application use. Do not retry creation under the same name without establishing the target's actual state. See DBA_PDBS and V$PDBS for catalog identity, the proxy indicator, integration state, and open state.

Oracle creates a default service with the PDB's name. Actual client reachability requires the correct Oracle Net configuration. The instructor should provide a verified alias for the local proxy and a separate alias for the referenced PDB. Proxy application sessions use password authentication. Query only the approved course objects and collect observed results.

For a later referenced listener change, Oracle's administration guide describes ALTER PLUGGABLE DATABASE CONTAINERS HOST or PORT at the referenced PDB, followed by dropping and recreating the referencing proxies to reestablish communication. The broader named-PDB execution contexts described there begin with 19.10. Treat this as a separate, version-specific administrator procedure requiring ALTER DATABASE, exact identities, interruption planning, and recovery. Do not apply an unreviewed host/port mutation as a general troubleshooting shortcut. See administering PDBs for the host and port procedures and the release-specific contexts.

Independent practice

1. Draw both CDBs and label the proxy and reference. Show the creation link in one diagram and the steady-state direct listener connection in a second.
2. Trace a read and a permitted write. Mark where application rows reside and where a committed change would occur. Identify the local administration exception.
3. Given an unavailable reference, state whether the proxy can return application rows, then name the remote listener, network route, and PDB state checks.
4. Select clone, relocation, or proxy for three new requests: isolated testing, moving application ownership, and local access to an existing remote database. Explain your selection using the required data and execution location.
5. If the optional lab is available, capture the actual root identities, proxy GUID, catalog indicator, open states, service configuration, and a permitted read of a known course key. Compare that read with the independently verified reference connection. A deliberate outage or write requires a separately approved isolated test and its recovery and cleanup plan.

Pass when you can draw the path without the creation link, predict the remote-outage consequence, explain the location of writes and local open/close behavior, and choose the design from a new requirement. If licensing, privileges, or the optional lab are unavailable, complete the diagrams and decisions and record the execution limitation.

Cleanup for an instructor-owned optional proxy

Read-only observations and the design exercise need no cleanup. For a newly provisioned disposable proxy, retain the evidence and required backups before cleanup. Confirm from the local root that the exact CDB, LABPDB_PROXY name, GUID, catalog proxy indicator, and local metadata files match this exercise. Agree interruption for any proxy clients and confirm the referenced LABPDB belongs to the remote CDB and remains separately protected.

The separately authorized administrator can close the exact local proxy. Verify it is mounted before the irreversible drop:

ALTER PLUGGABLE DATABASE labpdb_proxy CLOSE IMMEDIATE;

SELECT name, open_mode
FROM v$pdbs
WHERE name = 'LABPDB_PROXY';

-- Only after identity, ownership, mounted-state and recovery checks:
DROP PLUGGABLE DATABASE labpdb_proxy INCLUDING DATAFILES;

CLOSE IMMEDIATE interrupts work through this proxy. Resolve application transactions and the isolated window beforehand. INCLUDING DATAFILES deletes files associated with the selected local proxy and removes its catalog entry. DROP cannot be rolled back. Never substitute the referenced LABPDB name or use a broad PDB list. The cleanup selects the local proxy and its associated metadata files. The remote application's data and recovery assets remain independently owned. See ALTER PLUGGABLE DATABASE and DROP PLUGGABLE DATABASE for administrative authentication, open and close, mounted-state drop conditions, and file consequences.

Verify the local proxy entry and service are gone, and independently verify the remote reference's intended state and data. Have the instructor resolve any remaining local service registration. Remove a creation link only if this exercise exclusively owns it and it is no longer required by any other work. Ordinary proxy operation does not use that link after creation. Leave shared links, accounts, remote application rows, reference files, and backups alone.

All commands and interpretations here are documentation-backed teaching examples. No database connection, provisioning, outage, write, or cleanup was executed while preparing these notes. Capture real results, the exact release update, identity, error details, and the cleanup outcome in the designated lab.

Recap

A proxy PDB gives local access while application SQL and data stay in the referenced PDB. After creation, the proxy communicates directly with the referenced listener host and port. Closing the local proxy does not close the reference. If the reference is unavailable, the proxy cannot serve application rows. Choose a regular clone, a relocation, or a proxy from the required outcome before writing a command.

Quiz

1. Where does a query normally execute when the proxy is the current container?

Entirely in a local cached application copy In the referenced PDB, returning its result through the proxy In the local listener process In the database link after every client request

2. What communication path does a proxy ordinarily use after creation?

The creation database link for every statement A copied local application tablespace Direct communication to the referenced listener host and port The backup destination

3. Will the proxy keep serving application rows if the referenced PDB is unavailable?

Yes, from its local SYSTEM file Yes, until the creation link expires Yes, whenever the local listener is running No, application SQL still depends on the referenced PDB

4. A team needs an independent database copy for isolated testing. Which design best matches that outcome?

A regular full clone A proxy to the existing reference Closing the local proxy Renaming a client alias

5. What does opening LABPDB_PROXY with ALTER PLUGGABLE DATABASE from its local root change?

Both CDBs' listener ports The referenced LABPDB open state only The local proxy's open state The location of all application data

Commands and interpretations in this lesson are documentation-backed teaching examples. No database connection, provisioning, outage, write, or cleanup was executed while preparing these notes. Capture real results, the exact release update, identity, error details, and the cleanup outcome in the designated lab.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro