Oracle 11g EXPDP backup resolves empty table cannot be backed up

Previous Oracle backups were all exp, but exp has a problem with 11g, that is, the empty table cannot be exported.

Recent Oracle Data migration, you need to export the empty table together, after searching, found expdb so this article.

This article only records one of the issues, and my personal understanding of EXPDP

Before you can use EXPDP IMPDP, you need to set up the directory object and give the user permissions. This is because EXPDP IMPDP can only store data in the system directory through directory object relationships.

Note: The red part should be replaced with the actual value

EXPDP Export

1. Create directory objects and OS path mappings, and give permissions

   >connect/as Sysdba;
   

   >create OR REPLACE DIRECTORY directory_name as 'directory_ospath ';
   

>grant Read,write on the DIRECTORY directory_name to user_name;

2. Querying the Directory

>select * from Dba_directories;

>select * from All_directories;

3.EXPDP Export

>EXPDP user_name/user_passwd schemas=user_name dumpfile=expdp.dmp directory=directory_name

Schema is a collection of database objects, a user generally corresponds to a schema, the user's schema name equals the user name, and as the user's default schema. Reference http://blog.csdn.net/kimsoft/article/details/4627520

directory objects used by directory for dump files and log files.
DumpFile List of destination dump files (expdat.dmp)

Keyword description (default) reference http://blog.csdn.net/engledb/article/details/8979910

------------------------------------------------------------------------------

ATTACH Connect to an existing job, such as ATTACH [= Job name].
COMPRESSION reduce the size of valid dump file contents
The keyword values are: (metadata_only) and NONE.
CONTENT specifies the data to unload, where the valid keywords are:
(all), Data_only and Metadata_only.
directory objects used by directory for dump files and log files.
DumpFile a list of destination dump files (expdat.dmp),
such as Dumpfile=scott1.dmp, Scott2.dmp, Dmpdir:scott3.dmp.
Encryption_password The password keyword used to create encrypted column data.
ESTIMATE calculates the job estimate, where the valid keyword is:
(BLOCKS) and STATISTICS.
Estimate_only calculates job estimates without performing an export.
EXCLUDE excludes specific object types, such as exclude=table:emp.
FILESIZE specifies the size of each dump file in bytes.
FLASHBACK_SCN is used to set the session snapshot back to the SCN of the previous state.
The flashback_time is used to obtain the time of the SCN closest to the specified time.
Full exports the entire database (N).
Help displays the aid message (N).
Include includes specific object types, such as Include=table_data.
Job_name the name of the export job to create.
LOGFILE log file name (Export.log).
Network_link the name of the remote database that is linked to the source system.
Nologfile does not write to the log file (N).
PARALLEL changes the number of active workers for the current job.
PARFILE Specifies the parameter file.
QUERY a predicate clause that is used to export a subset of tables.
The percentage of data to be exported by SAMPLE;
SCHEMAS a list of scenarios to export (login scenario).
Status at the default value (0) will display when the new state is available,
The job status of the frequency (in seconds) to monitor.
TABLES identifies the list of tables to be exported-there is only one scenario.
Tablespaces identifies the list of tablespace to be exported.
Transport_full_check validates the bucket (N) of all tables.
Transport_tablespaces the list of tablespaces to unload metadata from.
Version of the object to be exported, where the valid keyword is:
(COMPATIBLE), LATEST, or any valid database version.

IMPDP Import

Upload the expdb exported backup file to the new library host

1. Before importing the data into the new library, you need to create the user on the new library and give the relevant permissions

Create user user_name

$sqlplus Sys/as sysdba;

>create USER user_name identified by password;

2. Give user_name users the ability to log in and create tables

>grant Create table,create session to user_name;

3. Assigning Users table quotas

SYS user rights:

>grant UNLIMITED tablespace to user_name;

4. Create directory objects and OS path mappings, and give permissions

>create OR REPLACE DIRECTORY directory_name as 'directory_ospath ';

>grant Read,write on the DIRECTORY directory_name to user_name;

5. Import

>IMPDP user_name/password directory=directory_name dumpfile=backup_name.dmp schemas= user_name

EXPDP Usage Examples:

1) According to the user guide
EXPDP Scott/[email protected] Schemas=scott dumpfile=expdp.dmp directory=dpdata1;
2) Parallel Process parallel
EXPDP Scott/[email protected] directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3) According to the table name guide
EXPDP Scott/[email protected] tables=emp,dept dumpfile=expdp.dmp directory=dpdata1;
4) Guided by query criteria
EXPDP Scott/[email protected] directory=dpdata1 dumpfile=expdp.dmp tables=emp query= ' WHERE deptno=20 ';
5) According to the Table space Guide
EXPDP System/manager directory=dpdata1 dumpfile=tablespace.dmp tablespaces=temp,example;
6) Guide the entire database
EXPDP System/manager directory=dpdata1 dumpfile=full.dmp full=y;

IMPDP usage Examples:

1) leads to the specified user
IMPDP Scott/tiger directory=dpdata1 dumpfile=expdp.dmp Schemas=scott;
2) Change the owner of the table
IMPDP system/manager directory=dpdata1 dumpfile=expdp.dmp tables=scott.dept remap_schema=scott:system;
3) Import Table space
IMPDP System/manager directory=dpdata1 dumpfile=tablespace.dmp tablespaces=example;
4) Import the database
impdb System/manager directory=dump_dir dumpfile=full.dmp full=y;
5) Append Data
IMPDP system/manager directory=dpdata1 dumpfile=expdp.dmp schemas=system table_exists_action

Error

Q:

Ora-31626:job does not exist
Ora-31687:error creating worker process with worker ID 1
Ora-31687:error creating worker process with worker ID 1
Ora-31688:worker process failed during startup.

A:

Citation official

Changesin the first situation aq_tm_processes=0for the second situation  aq_tm_processes should not be 0.causefor the first situation, aq _tm_processes init.ora parameter was set to zero  (AQ_TM_PROCESSES=0) Once  removed this parameter from the init.ora file, and bounced  the database the problem was resolvedfor the second situation,  There is likely a lack of memory for the streams_pool_size. solutionfor the first situation:o  remove aq_tm_processes init.ora  parameter  (aq_tm_processes=0)  from the init.ora.for the second situation:o   allocate between 50-100mb for the streams_pool_size in order  for datapump to&Nbsp;function since it is dependent on streams processing. 

In short, query the aq_tm_processes value

1. If 0, the value is removed from the Init.ora and then the Oracle service is restarted
   

2. If 1, 50-100MB memory is allocated for streams_pool_size
   

View aq_tm_processes values

Sql>show parameter Process

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Aq_tm_processes integer 0
Db_writer_processes Integer 1
Gcs_server_processes integer 0
Job_queue_processes Integer 10
Log_archive_max_processes Integer 2
Processes integer 150

Modify Streams_pool_size

Sql>alter system set streams_pool_size=50m scope=spfile;

Q:

Sql> Connected to an idle instance.
sql> ora-01078:failure in processing system parameters
Lrm-00109:could not open parameter file '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora '
Sql> disconnected

A:

cp/oracle/app/oracle/admin/orcl/pfile/init.ora.4262015194529/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/ Initorcl.ora
When encountered can not start, be sure to look at the start log error message!

/oracle/app/oracle/product/11.2.0/dbhome_1/startup.log

Q:

Ora-31626:job does not exist
Ora-31633:unable to create master table "SERVER. Sys_import_full_05 "
Ora-06512:at "SYS. Dbms_sys_error ", line 95
Ora-06512:at "SYS. Kupv$ft ", line 1020
Ora-01031:insufficient Privileges

A:

The user does not have permission to create the table, give the User Creaate table permission can

>grant CREATE TABLE to user_name;

Q:

Ora-39006:internal Error
Ora-39068:invalid master table data in row with process_order=-4
Ora-01950:no privileges on tablespace ' USERS '
A:
The users table has insufficient space to give the user table space to

>grant UNLIMITED tablespace to user_name;

Or:
>alter user Youruse quota 100m on users;  

User_name Users:

> CREATE TABLE Test (A varchar2 (100));

Modify User Password

ALTER USER user_name identified by New_password;

Delete User

DROP USER user_name Cascade

This article is from the "Morrowind" blog, make sure to keep this source http://morrowind.blog.51cto.com/1181631/1786008

Oracle 11g EXPDP backup resolves empty table cannot be backed up