STEP BY STEP DATABASE REFRESH.

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.

0 views

ADDRESS :

LOCATION :

701, Linden, Rosalie Complex Khadakpada, Kalyan (W) Mumbai, Maharashtra 421301)

CONTACT US :

+919322846017  &   +919867493019

EMAIL: 

QUICK LINKS :

SUBSCRIBE HERE :

Don’t Miss a Lecture. Subscribe Today. 

telegram-512.png

© 2019 DBA Trainings. Proudly Managed By Pearl Organisation

  • Grey LinkedIn Icon
  • Grey YouTube Icon
  • Grey Twitter Icon
  • Grey Facebook Icon