In this post, I am going to describe to you, step by step process for database refresh using datapump (EXPDP/IMPDB). In this example, my source database name is MYDB where the target database is AVANI
I found many DBA forget to restore old password while import or not checking invalid object before giving ok to a requester who requested for database refresh from production to test or development databases.
Please find the step by step process which you can follow in your environments.
1. Always remember to check the database name before export or import.
SQL> select name from v$database;
NAME ——— MYDB
2. Check the space utilize by schema before export.
SQL> select owner, sum(bytes)/1024/1024 “MB” from dba_segments group by the owner;
OWNER MB —————————— ——————– SYSTEM 30 SYS 715.0625 WMSYS 1.1875 RAJEEV 111 WKSYS 1.625 OUTLN .5625 DBSNMP .75
3. Check the username and tablespace before export so you can compare things before import.
SQL> col ACCOUNT_STATUS for a10 SQL> col profile for a15 SQL> set lines 150
SQL> select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where username=’RAJEEV’;
USERNAME ACCOUNT_ST DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
—————————— ———- —————————— —————————— —————
RAJEEV OPEN DATA01 TEMP DEFAULT
4. Note the password of the schema for export and do the same on the target database before import, it can help you when you need to maintain an old password.
SQL> set long 2000
SQL> select dbms_metadata.get_ddl(‘USER’,’RAJEEV’) from dual;
DBMS_METADATA.GET_DDL(‘USER’,’RAJEEV’) ————————————————————-
CREATE USER “RAJEEV” IDENTIFIED BY VALUES ‘S:3AFD2F1934CCFB63E4793D84FC8
F1D663550D0D07EEADEE4005F8C46807F;5BE571F865FDADE0′
DEFAULT TABLESPACE “DATA01”
TEMPORARY TABLESPACE “TEMP”
5. Check the tablespaces used be schema before export
SQL> select distinct tablespace_name from dba_segments where owner=’RAJEEV’;
TABLESPACE_NAME —————————— DATA01
6. Check all objects and their status before export. It can help you compare your object after import.
SQL> select object_type, count(1) from dba_objects where owner=’RAJEEV’ group by rollup(object_type);
OBJECT_TYPE COUNT(1) ————— ———- FUNCTION 32 INDEX 134 PACKAGE 2 PACKAGE BODY 2 PROCEDURE 494 SEQUENCE 1 TABLE 93 TRIGGER 57 TYPE 45 VIEW 9
869
11 rows selected.
SQL> select object_type,status,count(1) from dba_objects where owner=’RAJEEV’ group by object_type,status;
OBJECT_TYPE STATUS COUNT(1) ——————- ——- ———- TYPE VALID 45 PROCEDURE VALID 494 FUNCTION VALID 32 PACKAGE BODY VALID 2 PACKAGE VALID 2 VIEW VALID 9 INDEX VALID 134 SEQUENCE VALID 1 TRIGGER VALID 57 TABLE VALID 93
7. Check the object's status if any invalid before export. In this example no invalid object on the source.
SQL> select distinct status from dba_objects where owner=’RAJEEV’;
STATUS ——- VALID
8. Check the database dictionary before export. So you have an idea where you have to export and which location your export file will be kept.
SQL> SELECT owner, directory_name, directory_path FROM all_directories;
9. I prefer to create a par file for export and import it can be done without par file also. Create a par file with the help of vi editors or any text file. Here I am using a simple par file for demo purpose we can use complex queries as well in par file.
vi expdp_RAJEEV.par
DIRECTORY=DP_MYDB SCHEMAS=RAJEEV DUMPFILE=MYDB_RAJEEV_04DEC2013.DMP LOGFILE=EXPDP_MYDB_RAJEEV_04DEC2013.log
10. Now I am going to call par file for this export. It can be done from the command prompt also.
nohup expdp \’/ as sysdba\’ parfile=expdp_RAJEEV.par &
Or
nohup expdp system/password DIRECTORY=DP_MYDB DUMPFILE=MYDB_RAJEEV_04DEC2013.DMP SCHEMAS=RAJEEV LOGFILE=EXPDP_MYDB_RAJEEV_04DEC2013.log &
11. Now copy the export file on the target server for import. Go to the location where your export file is available and copy it. Make sure you have the privilege of copy the dump on the target server at the mentioned location.
scp EXPDP_MYDB_RAJEEV_04DEC2013.log rajeev@targetserver:/orasav/AVANI/bkp/exp .
*************************************targetserver************************************************** 12. Check the database name before import. Don’t forget to check database name, we found sevral dba done mistake and import will happen in deafult database which is pre selected.
SQL> select name from v$database;
NAME ——— AVANI
13. Check the scheme available on the target database or not if yes space utilization needs to check before import.
SQL> select owner, sum(bytes)/1024/1024 from dba_segments group by the owner;
OWNER SUM(BYTES)/1024/1024 ——————— ——————– SYSTEM 29.875 SYS 683.3125 RAJEEV 106.25 OUTLN .5625 DBSNMP ,6875
14. Check the tablespaces used to be schema before import. If required you can create tablespace or use remap_schema
SQL> col ACCOUNT_STATUS for a10 SQL> col profile for a15 SQL> set lines 150 SQL> select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where username=’RAJEEV’;
USERNAME ACCOUNT_ST DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE —————————— ———- —————————— —————————— ————— RAJEEV OPEN DATA01 TEMP DEFAULT
15. As communicated in source database export you must copy the password before import if you need to maintain an old password.
Note the password of schema before dropping the schema, it can help you to maintain old password if required.
SQL> set long 2000
SQL> select dbms_metadata.get_ddl(‘USER’,’RAJEEV’) from dual;
DBMS_METADATA.GET_DDL(‘USER’,’RAJEEV’) ——————————————————————————–
CREATE USER “RAJEEV” IDENTIFIED BY VALUES ‘S:7BD39503F6BE84405B049490125F74E398DB1B6D80306F07F12699FF81B4;14F7A8346F70B02D’ DEFAULT TABLESPACE “DATA01” TEMPORARY TABLESPACE “TEMP”
16. Drop schema if not required. Datapump automatically creates schema while import. Else you can execute below command after import to maintain an old password.
Note: Below syntax is optional.
SQL> alter user RAJEEV IDENTIFIED BY VALUES ‘S:7BD39503F6BE84405B049490125F74E398DB1B6D80306F07F12699FF81B4;14F7A8346F70B02D’;
17. Check the database dictionary before import if source and target diffent please create par file accordingly.
SQL> SELECT owner, directory_name, directory_path FROM all_directories;
18. Create a par file with the help of vi editor or any text file for import the schema.
vi impdp_RAJEEV.par
DIRECTORY=DP_AVANI SCHEMAS=RAJEEV DUMPFILE=EXPORT_RAJEEV_MYDBP_12-17-14.dmp LOGFILE=IMPDP_MYDB_RAJEEV_12-17-14.log
19. Now I am going to call par file for import. It can be done from the command prompt also as we have done during export.
nohup impdp \’/ as sysdba\’ parfile=impdp_RAJEEV.par &
20 Check all objects and their status after import. If any invalid object please recompile it.
SQL> select object_type,status,count(1) from dba_objects where owner=’HR’ group by object_type,status;
OBJECT_TYPE STATUS COUNT(1) ——————- ——- ———- FUNCTION VALID 75 PACKAGE BODY VALID 1 PROCEDURE VALID 2 PACKAGE VALID 1 VIEW VALID 35 INDEX VALID 586 SEQUENCE VALID 120 FUNCTION INVALID 1 TRIGGER VALID 121 VIEW INVALID 1 TABLE VALID 211
OBJECT_TYPE STATUS COUNT(1) ——————- ——- ———- TRIGGER INVALID 6
12 rows selected.
21. Check objects status if any invalid after import. In this example, I have an invalid object and we need to recompile it if required as we know my source doesn’t have any invalid object.
SQL> select distinct status from dba_objects where owner=’RAJEEV’;
STATUS ——- VALID INVALID
ALTER TRIGGER AUDUPD_TM_PROFILE COMPILE;
******************************* END of database refresh ******************************
If any other object is invalid you can try to compile like below mentioned steps.
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = ‘INVALID’
and owner=’RAJEEV’;
SQL> SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = ‘INVALID’ and owner=’RAJEEV’;
OWNER OBJECT_TYPE OBJECT_NAME STATUS —————————— ——————- —————————————- ——- RAJEEV PROCEDURE USP_REPORT_DONNEES_CAP_PROVCRE INVALID RAJEEV PROCEDURE USP_REPORT_DONNEES_CAP_PROVTIT INVALID RAJEEV PROCEDURE USP_TRACE_EXPORT_CAP_MODULE INVALID RAJEEV PROCEDURE USP_REPORT_PROVCREANCEHG INVALID RAJEEV PROCEDURE USP_REPORT_PROVCREANCERECAP INVALID RAJEEV PROCEDURE USP_SELECT_ALL_DONNEES_CAP INVALID
6 rows selected.
SQL> select ‘alter ‘||object_type||’ ‘||object_name||’ compile;’ from user_objects where object_type=’VIEW’ and status=’INVALID’;
ALTER FUNCTION GETMACHINE COMPILE;
ALTER TRIGGER AUDINS_TM COMPILE;
ALTER VIEW VDK_SYNC_TRG COMPILE; EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PROCEDURE’, ‘RAJEEV’, ‘USP_SELECT_ALL_DONNEES_CAP’);
EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘TMS’, ‘GETMACHINE’); EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘TMS’, ‘AUDDEL_TM_PROFILE’);
I hope you enjoyed the demo.