Full Database Refresh

Environment: - Oracle 11gR2 on Windows2008 Server

• First I will clear some misconceptions which most DBA’s will have about Database Refresh and Database clone.

            Database Clone:-  It’s a complete copy of production data which include Oracle-Home.

            Database Refresh:-  Also referred as a clone with only fresh data but not Oracle-Home.

• Maintaining Backups is one of the important job of a DBA, if you lose your backup you could very well lose your job.
• I used expdp utility for transferring data from production to test.

Prerequisites Checks on Source Database :-

• Check the number of users, tables, constraints, indexes, objects & Tablespaces on production server.
• In our production environment we have only one tablespace and all the users are mapped to this tablespace.’USERS’

SQL> select name from v$database;

           IECLIVE

SQL> select name from v$tablespace;

          SYSTEM

          SYSAUX

          UNDOTBS1

          USERS

          TEMP

          UNDOTBS2

       6 rows selected.

SQL> select owner, count(1), object_type from dba_objects group by owner, object_type having owner in (select username from dba_users where default_tablespace='USERS') and object_type='TABLE';

OWNER         COUNT(1)      OBJECT_TYPE

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

HMIS            1929          TABLE

ARCHIVEDB         1           TABLE

CMIS             410          TABLE

IDMS7            1902         TABLE

SQL> select count(*) from dba_objects;

           88782

SQL> select count(*) from dba_users;

           484

SQL> select count(*) from tab;

          4900

SQL> select count(*) from dba_indexes;

           8668

SQL> select count(*) from dba_constraints;

          2261

SQL> select count(*) from dba_objects where status='INVALID';

           605

•  Now Start Exporting your Source database using Data-pump expdp utility using full=y option.

            D:\ mkdir exp_fulldb

SQL> Create directory exp_fulldb as 'D:\exp_fulldb';

SQL> grant read,write on directory exp_fulldb to public;

D:\exp_fulldb] expdp directory=exp_fulldb dumpfile=24_DEC_2014FULL log=expfull.log full=y

STEPS ON TARGET SERVER 

• We already have old database in our target server.
• We need to Drop the existing users using the cascade option because existing users contains objects which contains old data.
• Find the existing users.

SQL> select owner, count(1), object_type from dba_objects group by owner, object_type having owner in  (select username from dba_users where default_tablespace = 'USERS') and object_type = 'TABLE';

OWNER        OBJECT_TYPE   COUNT(1)
----------------------------------------

HMIS          TABLE         1967  

ARCHIVEDB     TABLE          1 

CMIS          TABLE         410
IDMS7         TABLE         1936                            

SQL> DROP USER HMIS CASCADE.

drop user hmis cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02429: cannot drop index used for enforcement of unique/primary key

•  Here we can observe some user is working, If you not able to drop the user then inform them and kill their sessions.

SQL>  select sid,serial# ,username from v$session where username like '%HMIS%';

  SID         SERIAL#       USERNAME
---------------------------------------

  514          8277           HMIS
  642          3671           HMIS
  707          50325          HMIS

  772          1109           HMIS
  835          1395           HMIS

  1027         4399           HMIS

  1345        36245           HMIS

7 rows selected.

SQL> alter system kill session '514,8277' immediate; ( Kill all the users sessions)

SQL> drop user hmis cascade;

           User dropped.

SQL> drop user idms7 cascade;

           User dropped.

SQL> drop user cmis cascade;

           User dropped.

SQL> drop user archivedb cascade;

           User dropped.

•  Move .dmp file from Source to Target Server and start Importing.

D:\mkdir imp_fulldb

SQL> create directory imp_fulldb as 'D:\imp_fulldb';

SQL > Grant read write on directory imp_fulldb to public;

D:\ imp_fulldb] impdp directory=imp_fulldb dumpfile=24_DEC_2014FULL log=impfull.log full=y

• Now compile invalid objects.

 SQL> select count(*) from dba_objects where status='INVALID';

 SQL> @D:\app\oracle\product\11.2.0\dbhome_1\rdbms\admin\utlrp.sql

• Now cross check all the tables, indexes, constraints, objects are properly exported or not on the target database.
• Run the same queries which you executed on production server, all the tables, users, indexes, and constraints counting should be match with production or source database.

SQL> select count(*) from dba_objects;

SQL> select count(*) from dba_users;

SQL> select count(*) from tab;

SQL> select count(*) from dba_indexes;

SQL> select count(*) from dba_constraints;

Happy Learning…..