oracle 11g – load dump exported with expdp

Identifed steps to import dump on Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production

Step 1. log in as sysadmin

all steps below are to be done when logged as sysadmin (username: SYS, pwd:XXX)
in case password is forgotten, log into oracle box with user used to install oracle and no password will be asked when connecting using below command line:

sqlplus / as sysadmin

note: if password for unix user used to install oracle is also forgottern, log in with any user then su to be root then su <user_used_to_install_oracle>

Step 1. create same user as used in dump (otherwise can use remap_* options at import on impdp, it is easier simply to create same user

create user NEW_USER identified by password1;

Step 2. grant import rights and all privileges to recently created user

-- grant connect, create session, imp_full_database to NEW_USER;
--GRANT ALL PRIVILEGES TO NEW_USER;
grant ALL PRIVILEGES, imp_full_database to NEW_USER;

Step 3. create a dedicated tablespace for this user (i.e a place on hdd where data will be saved)

note: update datafile path to match oracle configuration on target environment

create tablespace NEW_USER datafile '/u01/app/oracle/oradata/XE/newuser_data.dbf' size 100M AUTOEXTEND ON NEXT 100M online;
select * from dba_data_files ; -- ensure newly create tablespace is found

Step 3b. [OPTIONAL] if a big db is loaded you may end up with process becoming idle during import and ultimately following message in log:

ORA-01654: unable to extend index SYS.I_CDEF2 by 128 in tablespace SYSTEM

If this is the case, restart oracle (see above) + drop created user + drop created table space + drop physical table space file + run below query to extend max space for system table + restart all steps

ALTER TABLESPACE system ADD DATAFILE '/u01/app/oracle/oradata/XE/system2.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M;

Step 4. at this stage, no table was created for new user so no schema should be visible as returned by below query

This is not an issue, schema will automatically be created by oracle as soon as first table is created

select distinct owner from dba_tables where owner not in ('SYS','SYSTEM');

Step 5. deactivate audit

Deactivate audit

show parameter AUDIT_TRAIL;
ALTER SYSTEM SET AUDIT_TRAIL=none SCOPE=SPFILE;
truncate table sys.AUD$;

note: restart oracle after updating value via

sudo /etc/init.d/oracle-xe restart

Step 6. create oracle directory object that points to folder where dump is located and to be read from

create directory dump_location as '<path_to_dump_location>';

Step 7. trigger import from shell

note: update value of parameter directory to match directory name created in oracle
note: monitor log file to search for errors, esp. hanging processes in which case see below queries in [useful queries] section

impdp NEW_USER/password1@XE directory=dump_location dumpfile=expdp_database-dump.dmp full=y logfile=import.log exclude=user

useful queries:
list currently running queries to identify locks (WARNING: TYPE=BACKGROUND is a system process)

SELECT O.OBJECT_NAME, S.TYPE, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME, S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS-- and S.TYPE != 'BACKGROUND';

kill above listed running/locked query

-- form: alter system kill session '<sid>,<serial>';
alter system kill session '32,21';

sources

Post a Comment

Your email is never published nor shared. You're allow to say what you want...