DBLink implements the method of importing other Oracle database instances without dropping backup files

Posted by Muddy_Funster on Tue, 11 Jan 2022 16:22:24 +0100

DBLink implements the method of importing other Oracle database instances without dropping backup files


  • Companies often need to back up database instances from other servers
  • Previous operations generally require that the backup source server use expdp to export the source dump file
  • Copy to the target server, and then import the exported dump file using imdp
  • Generally, the system authority of two machines is required It's a little cumbersome
  • Recently, I saw that dblink can be used for learning and use. Here is a brief summary

Create dblink

  • There are many ways to create DBlink, and the simplest creation syntax is used here
  • Note that the following SQL statement needs to be copied into a row to perform operations
create public database link linkToSource  
connect to system identified by passwordOnSourece 
using '//sourceip:1521/sourcesid'
  • Explain
establish dblink There are many ways,It feels like the easiest way.
It can also be modified tnsname, However, it may affect the existing system

Simple verification of DBlink

  • View database information
SQL> alter session set container=ora19cpdb01;
Session altered.
SQL> select count(1) from YourSourceDBname.YourBizTable@linkToSource;
  • Verify that there is no problem with the database

Restore the database without dropping the disk

  • The first step is to create users and tablespaces that need to be restored in the local library
  • Note: if remap is required, it is recommended to set user information
  • Note that none of the following semicolons are separated into one line and need to be executed completely
create tablespace SchemasInLocal datafile '/path/to/your/datafile.dbf' 
size 1024m autoextend on next 1024m ; 
create user SchemasInLocal default tablespace SchemasInLocal
identified by LocalPassword;
grant dba,resource,connect to SchemasInLocal;

Restore the database without dropping the disk

  • Give the specific script directly
  • Similarly, the following is a line of commands that need to be executed together
impdp system/LocalPassword@ directory=Localdir 
logfile=impdpFromRemote.txt network_link=LinkToSourece schemas=SchemasInSource 
remap_schema=SchemasInSource:SchemasInLocal remap_tablespace=SchemasInSource:SchemasInLocal 

The backup effect is:

# file size
Total estimation using BLOCKS method: 6.295 GB
# execution time
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) 
at Tue Jan xx xxxxx 2022 elapsed 0 00:46:59
# 6G database takes about 45 minutes to complete

Restore the database without dropping the disk

  • Brief description of parameters
1. directory Is a local path,Used to store import logs
2. logfile Specify the import log name under the local path
3. network_link Specify a locally created point source Source side dblink
4. schemas Specify that you need to source Database instance number of source side recovery
5. remap_xxxx conduct remap Replace the number and tablespace of the database instance
6. exclude Do not import statistics to improve import speed

Expand knowledge

  • In fact, you can create a dblink for local sid to quickly backup and restore your existing database instances
  • In fact, there is no need to create users and empower users user creation and grant permission will be automatically performed during impdp
  • However, you need to create a table space. impdp does not have permission to create a table space (at least I do not have this command)
  • The backup and recovery speed of local non falling disk is much faster. The backup and recovery of 6G database can be completed in about 25min

Personal perception

  • Thoughts on training
I have been in contact since I started working in 2008 Oracle database.
First use imp/exp Backup and recovery methods.
stay Oracle11g Used only when impdp/expdp Data pump mode
 At first I remember of deferred_segment_creation of bug Failed to export the empty table
 But always use create tablespace, create user, grant privileges Create initial user by
 Today, I found that it can not be used user and grant jurisdiction,impdp It will be created automatically.
So I feel that training is very important, Especially the training of the original factory. Their own people's understanding is not comprehensive after all
 Unable to achieve rapid and correct technology dissemination. 
I hope the company can strengthen the technical training of the original factory in the future. Knowledge is priceless

Topics: Oracle