Use DataPump to export the import synonym (export and import synonym using DataPump)

You can use multiple methods to back up synonyms. For example, you can use a script to generate a creation script for synonyms or use dbms_metadata.get_ddl to extract definition scripts for synonyms. However, when using the traditional exp or DataPump expdp for schema-level data migration, public synonyms cannot be exported. However, we can still use the Export and Import method. The difference is that we use the full = y method to export the synonym separately and then import it to the target database. The following is a description of this, and a script for manually creating synonyms is also provided at the end.

 

1. Environment

-- Source database SQL> select * from V $ version where rownum <2; banner orders Oracle Database 10g Release 10.2.0.3.0-64bit productionsql> show parameter db_namname type value values ----------- mongodb_name string xm6320sql> select count (*) from dba_synonyms where table_owner = 'goex _ admin '; count (*) ---------- 1042 -- target database -- all data in the schema goex_admin of the target database comes from the source database. However, after importing data using DataPump, no synonym SQL> select * from V $ version where rownum <2; banner export Oracle Database 10g Release 10.2.0.3.0-64bit productionsql> show parameter db_namename type value values ------------- ---------------------------- db_name string km3625 -- The following query contains only two synonyms, these two synonyms are manually created when the database is created. Instead of using DataPump to import the generated SQL> select count (*) from dba_synonyms where table_owner = 'goex _ admin '; count (*) ---------- 2

2. Export public synonyms from the source database

Oracle @ bkdb01p: ~> Env | grep sidoracle_sid = xm6320 # use the following method to export public synonyms. For exported objects, we can query the data dictionary database_export_objectsoracle @ bkdb01p: ~> Expdp goex_admin/xxx directory = db_dump_dir dumpfile = SYNS. DMP logfile = exp_syns.log full = Y \> include = public_synonym/synonym: \ "in \ (select synonym_name from dba_synonyms where table_owner = \ 'goex _ admin \ '\) \" Export: release 10.2.0.3.0-64bit production on Wednesday, 19 June, 2013 16: 11: 19 copyright (c) 2003,200 5, Oracle. all rights reserved. connected to: Oracle Database 10g Release 10.2.0.3.0-64bit productionstarting "goex_admin ". "sys_export_full_01": goex_admin/******** directory = db_dump_dir dumpfile = SYNS. DMP logfile = exp_syns.log full = y include = public_synonym/synonym: "In (select synonym_name from region where table_owner = 'goex _ admin')" estimate in progress using blocks method... processing object type database_export/Schema/table/table_datatotal estimation using blocks method: 0 kbprocessing object type database_export/Schema/public_synonym/synonymmaster table "goex_admin ". "sys_export_full_01" successfully Loaded/unloaded ********************************* **************************************** ***** dump file set for goex_admin.sys_export_full_01 is: /u02/database/xm6320/BNR/dump/SYNS. dmpjob "goex_admin ". "sys_export_full_01" successfully completed at 16:11:23

3. Import public synonyms to the target database

Oracle @ bkdb01p: ~> CP/u02/database/xm6320/BNR/dump/SYNS. DMP/u02/database/km3625/BNR/dump/Oracle @ bkdb01p: ~> Export oracle_sid = km3625oracle @ bkdb01p: ~> Impdp goex_admin/xxx directory = db_dump_dir dumpfile = SYNS. DMP logfile = imp_syns.log full = y include = synonymimport: Release 10.2.0.3.0-64bit production on Wednesday, 19 June, 2013 16: 15: 52 copyright (c) 2003,200 5, Oracle. all rights reserved. connected to: Oracle Database 10g Release 10.2.0.3.0-64bit productionmaster table "goex_admin ". "sys_import_full_01" successfully loaded/unloadedstarting "goex _ Admin ". "sys_import_full_01": goex_admin/******** directory = db_dump_dir dumpfile = SYNS. DMP logfile = imp_syns.log full = y include = synonymprocessing object type database_export/Schema/public_synonym/SYNONYMORA-31684: Object Type synonym: "public ". & quot; go_ga_sys_datapump_para_tbl & quot; already existsORA-31684: Object Type synonym: & quot; Public & quot ". "bo_sys_datapump_pkg" already existsjob "goex_admin ". "sys_import_full_01" C Ompleted with 2 error (s) at 16:16:12 # As shown in the preceding import process, processing object type database_export/Schema/public_synonym/synonym implements synonym import # because there are two synonyms, A prompt is also provided # verify the imported synonym Oracle @ bkdb01p: ~> Sqlplus/As sysdbasql * Plus: Release 10.2.0.3.0-production on Wed Jun 19 16:16:24 2013 copyright (c) 1982,200 6, Oracle. all rights reserved. connected to: Oracle Database 10g Release 10.2.0.3.0-64bit productionsql> show parameter db_namename type value values ------------- ---------------------------- db_name string km3625sql> select count (*) from dba_synonyms where table_owner = 'goex _ admin'; count (*) ---------- 1042

4. manually create a synonym script

ACCEPT input_owner PROMPT 'Enter the owner of table:'SET HEADING OFF VERIFY OFF FEEDBACK OFF TERMOUT OFF;SPOOL $LOG/create_synonym.sql--Author: Robinson--Blog  : http://blog.csdn.net/robinson_0612SELECT 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || table_name || ';'  FROM dba_synonyms s WHERE s.owner = 'PUBLIC' AND s.table_owner = UPPER ('&input_owner');SET HEADING ON VERIFY ON TERMOUT ON FEEDBACK OFF;SPOOL OFF;--@$LOG/create_synonym.sql;

 

5. References
Use of Data Pump expdp export tool
Use of Data Pump impdp import tool
Use of exclude/include in expdp impdp
Use the Oracle DataPump API to export data
Import and Export Oracle Partition Table Data

Oracle DataPump expdp/impdp hang

 

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)