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

background

• 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;
  COUNT(1)
----------
       169

• 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@127.0.0.1/LocalSid directory=Localdir 
logfile=impdpFromRemote.txt network_link=LinkToSourece schemas=SchemasInSource 
remap_schema=SchemasInSource:SchemasInLocal remap_tablespace=SchemasInSource:SchemasInLocal 
exclude=statistics

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 11.2.0.1 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