A Data Pump network import reads a source database through a database link and creates the selected objects and data in the target. The import job runs in the database to which impdp connects. That target database opens the link to the source. Rows and metadata return to the target. The client starts and monitors the job.
Oracle DBA Lesson 87 Part 2 — Import through a database link
This part covers the server connection, the source identity and what that login can see, the import command, and the source state a trustworthy application copy needs. Part 1 covered a completed dump file and the client cutover. A network import replaces that intermediate dump file. The target still needs preparation and validation. The command is a conventional schema-mode import of ordinary relational objects between two existing PDBs. It does not use transportable mode, and it does not clone a PDB.
Establish the two directions
| Operation | Direction |
|---|---|
| Open the database link | Target database to source database |
| Read application data and definitions | Source database to the target database job |
| Start and monitor the job | Client to the target database |
The source keeps its objects and rows. Import creates a separate copy in the target. In this conventional network import there is no intermediate Data Pump dump file. The target writes the application data into its own database storage, and it can write a server log.
A client connection to TARGET_SERVICE and a database link owned in the target are separate connections. TARGET_SERVICE is the client alias for target TGT_PDB. A source alias that works from a laptop leaves the server route unproved: the target database server must resolve SOURCE_SERVICE and reach that listener. Check that route, and check that traffic between the two database servers is encrypted. See the Data Pump overview.
Check the link as its owner
Connect to the target PDB as the account that will run the import. Confirm that session's database, container, and service with the identity check from Part 1. Then list the links that account owns:
SELECT db_link, username, host
FROM user_db_links;| DB_LINK | USERNAME | HOST |
|---|---|---|
SOURCE_LINK | APP_USER | SOURCE_SERVICE |
USER_DB_LINKS lists links owned by the current user. DB_LINK is the stored link name. USERNAME is the remote login. HOST is the Oracle Net connect string. That string can be a naming alias. Resolve the actual destination from the naming configuration on the target server.
APP_USER is a separate local account in each PDB. For this same-owner import, target APP_USER owns SOURCE_LINK, a private fixed-user link that logs in as source APP_USER. Data Pump supports that private fixed-user link for its owner. Private connected-user links and current-user links are not supported for this Data Pump use. Check the link type and the owner in the data dictionary. The name alone does not establish either one. Use the stored link name, and keep a domain suffix when the name has one. The name also has to satisfy GLOBAL_NAMES. The import command names that stored link and does not include a password. The link itself is created separately.
When several links are listed, use the exact intended name. A successful connection proves connectivity for that login. The job still needs the matching scope and authority. See USER_DB_LINKS, ALL_DB_LINKS, and managing database links.
Verify remote identity and visibility
From the target, through the selected link:
SELECT global_name AS source_global_name
FROM global_name@source_link;| SOURCE_GLOBAL_NAME |
|---|
SRC_PDB.EXAMPLE.COM |
Compare the returned global name with the intended source. Then connect directly to that source service and confirm its container:
CONNECT app_user@SOURCE_SERVICE
SHOW CON_NAME| CON_NAME |
|---|
SRC_PDB |
Also record that direct connection's database unique name and connected service, as in Part 1. A SYS_CONTEXT expression inside a distributed query does not identify the remote container. Use the remote GLOBAL_NAME check, the direct source connection, and the resolved link configuration together. See GLOBAL_NAME.
Back on the target, as the link owner, count the source objects visible to the link account:
SELECT owner, COUNT(*) AS objects
FROM all_objects@source_link
WHERE owner = 'APP_USER'
GROUP BY owner;| OWNER | OBJECTS |
|---|---|
APP_USER | 42 |
ALL_OBJECTS at the remote database reports objects visible to that login. For this source the count is 42. A low count can mean the login sees only part of the schema. Compare object names, types, and the external dependencies the application needs with the source account's own inventory. Equal counts still leave object identity and application behavior to check. When no matching objects are visible, this grouped query returns no row.
Match release, authority, and capacity
| Area | What to establish |
|---|---|
| Source scope | The link login can export the required application objects and data |
| Target scope | The import account can create the intended objects and has quota and control-table space |
| Releases and features | The network-import release distance, and the supported object types and options |
| Storage | Required target tablespaces, capacity, character representation, and object dependencies |
| Transport | Database-to-database encryption, reachability, and the authentication the link uses |
| Source state | A frozen source, or a supported query point with retained history and a cutover or catch-up design |
Oracle Database 19c documents a maximum difference of two major versions for a network import. Check the exact releases, COMPATIBLE, the object types, and the release-update rules that apply. That version window still leaves feature limits. Network import restricts some cases, including evolved types and specified SecureFiles and DBFS links. Adding PARALLEL does not parallelize a network metadata import.
When the target import account has DATAPUMP_IMP_FULL_DATABASE, the remote identity needs DATAPUMP_EXP_FULL_DATABASE, as the network-import rules require. Source authority and target authority each have to match the intended scope. An unexplained visibility error calls for a check of that scope. Granting a broad role does not explain it. Keep SYSDBA out of the routine import login. This command is a same-owner schema import, so schema-owner authority is the scope. A cross-schema or full import needs a privilege set reviewed for that wider job.
Data encrypted in storage can cross an unencrypted database link as clear text. Set the Oracle Net encryption or TLS arrangement between the database servers. Storage encryption covers the files at rest. The link path needs its own protection. See Data Pump Import.
Start the import on the target
In this example the target account owns the link, and the target schema does not yet contain the application tables or other conflicting application objects. The account needs privileges to create the intended objects, tablespace quota, space for the Data Pump control table, a grant on DP_DIR, and operating-system access to that directory path. DP_DIR is the target DIRECTORY object for the server log.
With the same-owner schema prepared and the source held stable:
impdp app_user@TARGET_SERVICE \
network_link=SOURCE_LINK \
schemas=APP_USER directory=DP_DIR \
logfile=network_import.log| Element | Purpose |
|---|---|
app_user@TARGET_SERVICE | Selects the account and the target database connection |
NETWORK_LINK=SOURCE_LINK | Uses the link available to that target account to reach the source |
SCHEMAS=APP_USER | Selects the source schema scope |
DIRECTORY=DP_DIR | Selects a target server directory object for output such as the log |
LOGFILE | Names the target server job log |
This command has no DUMPFILE. The target database reads the source data and definitions directly and creates the target objects. A DIRECTORY object names a location on the database server. The database grant and the operating-system permission are both required. Creating the directory object leaves the operating-system folder to be created separately.
Before the load, review the planned metadata, privileges, tablespaces, and dependencies. Where a preview applies, use the earlier SQLFILE procedure with these network-import parameters. SQLFILE writes the proposed DDL and leaves the application data unloaded. Keep unexpected objects out of the import schema. The ordinary existing-table default can skip objects. Resolve that collision before you retry, rather than adding a destructive replacement option as the routine retry. See Data Pump Import.
Choose the source state
For an outage, stop the writers and scheduled jobs that can change the selected data. Finish their transactions, or resolve those transactions deliberately. Prevent relevant DDL. Capture the source baseline, and keep that state stable through the copy and the validation. Data Pump does not freeze the application.
When source writes continue, name a supported consistent query point. On a network import the optional parameter is:
FLASHBACK_SCN=80000008000000 shows the parameter form. Replace it with an SCN taken from the source. The target database's SCN belongs to a different database. FLASHBACK_SCN tells the import to read source data consistently at that point. The mechanism is Flashback Query. Flashback Database is a separate operation and is not what this parameter runs. FLASHBACK_TIME selects an SCN near the time you specify. The two parameters are mutually exclusive. Both require NETWORK_LINK.
Retained source undo supplies the older row versions when rows have changed since the chosen point. That history has to remain available for the whole read. Check undo capacity, retention behavior, access, and support for the objects, LOBs, and DDL involved. A retention number is one setting. It does not by itself keep every required older version available. If the history is gone, the read can fail with a snapshot-too-old condition. Leave the relevant schema unchanged during the operation.
The loaded data matches the selected point. Commits after that point stay outside the copy. An application that keeps writing needs a supported catch-up, or a new valid final copy, before clients move. A consistent snapshot leaves the target short of the source at cutover unless that later work is included. See Flashback Query and Data Pump Import.
Read a failed job and validate the target
Read the final completion messages and the job log. Reconcile required objects and data with the source baseline. Check invalid objects, constraints, indexes, grants, and dependencies. Test representative queries and stored code as the application account. Use the validation checks from Part 1.
If the network connection fails, determine the state of the existing job and of objects already created in the target. As the target account:
SELECT job_name, operation, job_mode, state, attached_sessions
FROM user_datapump_jobs;The rows describe jobs owned by the current user, including control-table records that remain. Read the state, the log, and the errors. When the job accepts an attach, attach to that existing job name and read its status before you resume, repair, or reset the partial target. A failed link can leave target objects in place. Some jobs can resume, and some cannot. Decide from the recorded state.
Each imported object is applied as the job goes. ROLLBACK does not reverse the import. Inspect the existing job before any retry. A second overlapping import, or REPLACE or TRUNCATE used to conceal the partial copy, skips that inspection. Keep clients on the source until target validation is complete and the final source state, including any catch-up, is satisfied. Reconnect and drain clients under the planned handoff. Later writes on the target belong in the backout plan. See USER_DATAPUMP_JOBS.
No comments:
Post a Comment