Schema Refresh (Using Export /Import)

1. In PROD Database (Source Database name: Prod1)

1.1 CHECK THE NUMBER OF OBJECTS IN THE SOURCE DATABASE:

SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='PROD_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)

------------------------------ ------------------- ------------------

PROD_USER123                   TABLE                               45

PROD_USER123                   INDEX                              120

PROD_USER123                   VIEW                                 1

PROD_USER123                   TRIGGER                              5

PROD_USER123                   SEQUENCE                            10

PROD_USER123                   LOB                                 19

6 rows selected.

1.2 CHECK THE INVALID OBJECTS IN THE SOURCE DATABASE:

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER='PROD_USER123';

no rows selected

1.3 TAKE EXPORT BACKUP OF PROD_USER123 SCHEMA FROM THE SOURCE DATABASE:

exp userid=system/xxxxxxx file=/p02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.dmp log=/p02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.log owner=PROD_USER123 rows=y indexes=y grants=y constraints=y

1.4 TAKE THE REQUIRED INFORMATION FROM THE SOURCE DATABASE:

SQL> set line 1000

SQL> select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='PROD_USER123';

USERNAME        PASSWORD   DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE   PROFILE

--------------- ---------- -------------------- ---------------------- --------

PROD_USER123               USERDATA             TEMP                   DEFAULT

SQL> select * from dba_role_privs where grantee in('PROD_USER123');

no rows selected

SQL> select * from dba_sys_privs where grantee in('PROD_USER123');

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

PROD_USER123                   CREATE TRIGGER                           NO

PROD_USER123                   CREATE PROCEDURE                         NO

PROD_USER123                   CREATE SEQUENCE                          NO

PROD_USER123                   CREATE SESSION                           NO

PROD_USER123                   GLOBAL QUERY REWRITE                     NO

PROD_USER123                   CREATE MATERIALIZED VIEW                 NO

PROD_USER123                   CREATE DATABASE LINK                     NO

PROD_USER123                   ALTER  SESSION                           NO

PROD_USER123                   CREATE INDEXTYPE                         NO

PROD_USER123                   CREATE OPERATOR                          NO

PROD_USER123                   CREATE SYNONYM                           NO

PROD_USER123                   CREATE TABLE                             NO

PROD_USER123                   CREATE TYPE                              NO

PROD_USER123                   CREATE VIEW                              NO

PROD_USER123                   CREATE CLUSTER                           NO

15 rows selected.
   
SQL> select * from dba_ts_quotas where username='PROD_USER123';

TABLESPACE_NAME  USERNAME        BYTES      MAX_BYTES  BLOCKS     MAX_BLOCKS DRO

---------------- --------------- ---------- ---------- ---------- ---------- ---

USERDATA         PROD_USER123    12582912   -1         1536       -1         NO

USERINDX         PROD_USER123    4980736    -1         608        -1         NO


1.4 SEND THE FULL EXPORT BACKUP TO THE TARGET DATABASE SERVER:

[oracle@proddb1 export]$ scp prod1.exp_PROD_USER123_11DEC.dmp oracle@devdb1:/d02/app/oracle/export

 ***********************************Target Database-Start******************************************************** 

2. In wmdevdb1 DEV Database server:(dev1)

2.1 CHECK THE NUMBER OF OBJECTS IN THE TARGET DATABASE :

SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='DEV_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)

------------------------------ ------------------- ------------------

DEV_USER123                    TRIGGER                              5

DEV_USER123                    SEQUENCE                             3

DEV_USER123                    LOB                                 12

DEV_USER123                    INDEX                               65

DEV_USER123                    VIEW                                 1

DEV_USER123                    TABLE                               26

6 rows selected.

2.2 CHECK THE INVALID OBJECTS IN THE TARGET DATABASE:

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER ='DEV_USER123';

no rows selected

2.3 TAKE EXPORT BACKUP OF DEV_USER123 SCHEMA FROM THE TARGET DATABASE :

[oracle@devdb1 export]$ exp userid=system/xxxxxx file=/d02/app/oracle/export/exp_DEV_USER123_11DEC.dmp log=/d02/app/oracle/export/exp_DEV_USER123_11DEC.log owner=DEV_USER123 rows=y indexes=y grants=y constraints=y

2.4 TAKE THE REQUIRED INFORMATION FROM THE TARGET DATABASE:
SQL> set line 1000    
SQL> select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='DEV_USER123';

USERNAME    PASSWORD   DEFAULT_TABLESPACE TEMP_TABLESPACE PROFILE

----------- ---------- ------------------ --------------- ------------

DEV_USER123            USERDATA           TEMP            DEV_USER123_DEFAULT

SQL> select * from dba_role_privs where grantee in ('DEV_USER123');

no rows selected

SQL> select * from dba_sys_privs where grantee in ('DEV_USER123');

GRANTEE                        PRIVILEGE                                ADM

------------------------------ ---------------------------------------- ---

DEV_USER123                    CREATE OPERATOR                          NO

DEV_USER123                    CREATE MATERIALIZED VIEW                 NO

DEV_USER123                    CREATE TYPE                              NO

DEV_USER123                    CREATE SEQUENCE                          NO

DEV_USER123                    CREATE TABLE                             NO

DEV_USER123                    GLOBAL QUERY REWRITE                     NO

DEV_USER123                    CREATE INDEXTYPE                         NO

DEV_USER123                    ALTER  SESSION                           NO

DEV_USER123                    CREATE SESSION                           NO

DEV_USER123                    CREATE DATABASE LINK                     NO

DEV_USER123                    CREATE SYNONYM                           NO

DEV_USER123                    CREATE PROCEDURE                         NO

DEV_USER123                    CREATE VIEW                              NO

DEV_USER123                    CREATE CLUSTER                           NO

DEV_USER123                    CREATE TRIGGER                           NO

15 rows selected.

SQL> select * from dba_ts_quotas where username='DEV_USER123';

TABLESPACE_NAME  USERNAME        BYTES      MAX_BYTES  BLOCKS     MAX_BLOCKS DRO

---------------- --------------- ---------- ---------- ---------- ---------- ---

USERDATA         DEV_USER123     38928384   -1         4752       -1         NO

USERINDX         DEV_USER123     8847360    -1         1080       -1         NO

2.5 DROP THE DEV_USER123 USERS FROM THE TARGET DATABASE:
SQL> drop user DEV_USER123 cascade;

2.6 CREATE THE DEV_USER123 USER AS PER THE SOURCE DATABASE :
SQL> create user DEV_USER123 identified by DEV_USER123

default tablespace USERDATA

temporary tablespace TEMP

profile DEV_USER123_DEFAULT

quota unlimited on USERDATA

quota unlimited on USERINDX;

2.7 GRANT THE PRIVILAGES TO THE PRODAPP USER AS PER SOURCE DATABASE :
SQL> grant connect,resource to DEV_USER123;
SQL> grant CREATE TABLE,CREATE SESSION,CREATE TRIGGER,CREATE SEQUENCE,UNLIMITED TABLESPACE,SELECT ANY DICTIONARY,CREATE VIEW to DEV_USER123;

2.8 IMPORT THE DATA TO THE TARGET DATABASE FROM THE SOURCE DATABASE :

[oracle@devdb1 export]$ imp userid=system/xxxxxxx file=/d02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.dmp 

log=/d02/app/oracle/export/prod1.exp_PROD_USER123_11DEC_imp.log 

fromuser=PROD_USER123 touser=DEV_USER123 ignore=y

2.9 CHECK THE NUMBER OF OBJECTS IN THE TARGET DATABASE :
SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='DEV_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                         OBJECT_TYPE         COUNT(OBJECT_TYPE)

----------------------------- ------------------- ------------------

DEV_USER123                   TABLE                               45

DEV_USER123                   INDEX                              120

DEV_USER123                   VIEW                                 1

DEV_USER123                   TRIGGER                              5

DEV_USER123                   SEQUENCE                            10

DEV_USER123                   LOB                                 19

6 rows selected.
Note: Output of above command should be same as production.

2.10 CHECK THE INVALID OBJECTS IN THE TARGET DATABASE :

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER ='DEV_USER123'; 

2.11 IF ANY INVALID OBJECTS THEN RUN UTLRP.SQL
    
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

**********************************END**********************************