Oracle expdp/impdp Test Library Build tutorial, expdpimpdp

Oracle expdp/impdp Test Library Build tutorial, expdpimpdp

Recently, the customer needs to build a test database, so they just need to organize their experience.

I. Test Database creation:

To create a database in windows, it is best to run the database with the Administrator permission, even if the Administrator logs on to the database. In general, it is best to ensure that the database is created using the Administrator. Otherwise, you may not be able to create a listener later. Whether the database is OLAP or OLTP. It is better for OLAP to modify the data block character set to be consistent with that of the production database, including Language. At the same time, make sure that the language in nls_database_parameter and nls_instance_parameter are consistent. Modify the following parameters as needed:

alter database noarchivelog;alter database flashback off;alter system set processes=500 scope=spfile;alter system set sessions=555 scope=spfile ;alter profile default limit password_life_time unlimited;alter profile default limit failed_login_attempts unlimited;

Ii. export production database data

Before exporting data, we need to evaluate how much space is required to export the data. If the data file is too large, it is best to partition the data file, and the export speed cannot be too slow.

create tablespace test datafile '/opt/app/oracle/data/test01.dbf'  size 30G;               alter tablespace test add datafile '/opt/app/oracle/data/test02.dbf'  size 30G;                              ........

-The following SQL statement is used to check the size of each tablespace in the production database.

select a.tablespace_name,round(total_size,2),round(free_size,2),round(total_size-free_size,2) as  use_size from (select tablespace_name,sum(bytes)/power(1024,3) total_size from dba_data_files a group by tablespace_name) a,(select tablespace_name,sum(bytes)/power(1024,3) free_size from dba_free_space a group by tablespace_name) bwhere a.tablespace_name=b.tablespace_nameorder by 2 desc

Impdp automatically creates a schema without manual creation. The degree of parallelism for import and export should be the same impdp. Of course, you also need to create a directory. The precautions will not be repeated. If the database version of the production database is higher than that of the test database, you must specify the version. Ensure compatibility.

Import command

sqlplus sys/test@test as sysdbaSQL>create directory dumpdir as '~/dumpdir'SQL>grant read,write on directory dumpdir to systemSQL>exit impdp system/test@test schemas=(hr,scott) directory=dumpdir dumpfile=expdb_%U.dmp logfile=impdp.log parallel=5

Troubleshooting:

Impdp reports the following error:

ORA-39002: invalid operation                                                                   ORA-39059: dump file set is incomplete                          ORA-39246: cannot locate master table within provided dump files  

When the expdp file is exported, the system prompts that the file has been exported successfully, and the file has not been copied. Later, the directory was re-created in the production database, and then re-exported. Then, the import was okay. The expdp may have been executed multiple times in this directory before, but it is not guaranteed that the last expdp process is completely completed and the new expdp is started. It is also said that oracle cannot access some dump files.