Preparation before impdp operation in an new oracle database
Confirm size of data tablespace and temp tablespace in original database
check data tablespace of original database
SQL>set line 500;
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB – free.MB, 2) AS Used_MB,
Round(( 1 – free.MB / total.MB ) * 100, 2)
|| ‘%’ AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
Confirm size of temp tablespace in original database
SQL>select c.tablespace_name,
to_char(c.bytes/1024/1024/1024,’99,999.999′) total_gb,
to_char( (c.bytes-d.bytes_used)/1024/1024/1024,’99,999.999′) free_gb,
to_char(d.bytes_used/1024/1024/1024,’99,999.999′) use_gb,
to_char(d.bytes_used*100/c.bytes,’99.99′) || ‘%’use
from (select tablespace_name,sum(bytes) bytes
from dba_temp_files GROUP by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool GROUP by tablespace_name) d
where c.tablespace_name = d.tablespace_name;
Create data tablespace and temp tablespace,You’d better create tablespace with the same size with original table space
For data tablespace
SQL>set line 500;
SQL>select name form v$datafile
SQL>create tablespace TB datafile ‘/path/…/tb.dbf’ size xxxM autoextend on NEXT xxxM maxsize unlimited;
If data tablespace is huge, for example it is lager than 30GB even loager then 100GB, you can create extended table space
SQL>alter tablespace TB add datafile ‘/path/…/tb_1.dbf’ SIZE 30000M AUTOEXTEND ON NEXT 1000M maxsize unlimited;
SQL>alter tablespace TB add datafile ‘/path/…/tb_2.dbf’ SIZE 30000M AUTOEXTEND ON NEXT 1000M maxsize unlimited;
…………..
…………..
For temp tablespace
SQL>set line 500;
SQL>col file_name for a80;
SQL>SELECT file_name,
file_id,
tablespace_name,
status,autoextensible,
bytes/1024/1024 “file_size(M)” FROM dba_temp_files;
SQL>create temporary tablespace TB_TEMP tempfile ‘/path/…/tb_temp.dbf’size xxxM autoextend on next xxxM maxsize unlimited;
If temp tablespace is huge, for example it is lager than 30GB even loager then 100GB, you can create extended table space.
SQL>alter tablespace TB_TEMP add tempfile ‘/path/…/tb_temp_1.dbf’ SIZE 30000M AUTOEXTEND ON NEXT 1000M maxsize unlimited;
SQL>alter tablespace TB_TEMP add tempfile ‘/path/…/tb_temp_2.dbf’ SIZE 30000M AUTOEXTEND ON NEXT 1000M maxsize unlimited;
…………..
…………..
Extend some system tablespaces
alter tablespace SYSAUX add datafile ‘/path/…/sysaux01_1.dbf’ SIZE 5000M AUTOEXTEND ON NEXT 100M maxsize unlimited;
alter tablespace SYSTEM add datafile ‘/path/…/system01_1.dbf’ SIZE 5000M AUTOEXTEND ON NEXT 100M maxsize unlimited;
alter tablespace UNDOTBS1 add datafile ‘/path/…/undotbs01_1.dbf’ SIZE 30000M AUTOEXTEND ON NEXT 500M maxsize unlimited;
alter tablespace USERS add datafile ‘/path/…/users01_1.dbf’ SIZE 500M AUTOEXTEND ON NEXT 200M maxsize unlimited;
Confirm users of original data tablespace and original temp tablespace
SQL>set line 500;
SQL>col DIRECTORY_PATH for a80;
SQL>select * from dba_users;
Create new user and same users with original data tablespace and temp tablespace.
Create new user
SQL>create user new_user identified by passwd1 default tablespace TB temporary tablespace TB_TEMP;
Create same user with original tablespace
SQL>create user original_user1 identified by passwd2 default tablespace TB temporary tablespace TB_TEMP;
SQL>create user original_user2 identified by passwd3 default tablespace TB temporary tablespace TB_TEMP;
Confirm logical directories in original database
SQL>set line 500;
SQL>select * from dba_directories;
Create logical directories by the same name with original database(real path can be different from original directories,but you should pay attention to permission in order it can be accessed.)
SQL>create or replace directory original_name1 as ‘/path/../path1’;
SQL>create or replace directory original_name2 as ‘/path/../path2’;
SQL>create or replace directory original_name3 as ‘/path/../path3’;
………………..
………………..
Create new logical directory
SQL>create or replace directory new_directory as ‘/path1/../path3’;
Grant permission to logical directories
chown -R oracle:oinstall /path1/../path3
chown -R oracle:oinstall /path/../path1
chown -R oracle:oinstall /path/../path2
chown -R oracle:oinstall /path/../path3
Grant permissions to new users
SQL>grant connect to new_user;
SQL>grant resource to new_user;
SQL>grant dba to new_user;
Put dmp file to new logical directory.and grant permission
chown oracle:oinstall export_all_data.date.dmp
Import dmp file
impdp new_user/paswd1 directory=new_directory dumpfile=export_all_data.date.dmp logfile=impdp_all_data.date.log remap_schema=original_user:new_user table_exists_action=replace full=y exclude=statistics
Attachment
How to import a table from a dmp file?
example:
impdp dest_user/password directory=logical_directory dumpfile=full_backup.date.dmp logfile=impdp_table1.date.log remap_schema=source_user:dest_user tables=source_user.table1 table_exists_action=replace exclude=statistics
Other questions
Why create logical directories which are same with original database?( path can be different,name must be same)
Answer: To aviod the following error.
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:”user”.”ET$08E100290002″ failed to create with error:
ORA-06564: object directory does not exist
Why create an user which is related whth original tablespace?
Answer: To avoid the following error.
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role ‘original_user‘ does not exist
Failing sql is:
GRANT EXECUTE ON “new_user”.”E_FO_PKG” TO “original_user“
Why add “exclude=statistics” behind impdp command ?
Answer: To avoid the following error.
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name “DW00” prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-04030: out of process memory when trying to allocate 16048 bytes (session heap,kuxLpxAlloc)
ORA-06512: at “SYS.KUPW$WORKER”, line 1887
ORA-06512: at line 2
ORA-39097: Data Pump job encountered unexpected error -1427
ORA-39065: unexpected master process exception in MAIN
ORA-01427: single-row subquery returns more than one row
Job “new_user”.”SYS_IMPORT_FULL_01″ stopped due to fatal error at Wed Nov 13 08:23:44 2019 elapsed 0 17:24:47