STEPS - DATABASE REFRESH

Database refresh with EXP/IMP & EXPDP/IMPDP 

===================================
Backup
======
Backup must be taken in the source side at the backup location using EXPORT or EXPDP.If required, at target side too.

EXP/IMP
----------
Full Database export
nohup exp <userid>/<password>  file=filename.dmp log=filename.log CONSISTENT=Y full=y &

Pre-checks
========
SOURCE SIDE & TARGET SIDE (STEP 1 AND STEP2)
============================================
step 1:
------
source and target users must me same.
NOTE: Otherwise, create new user in target side before import

To Check Database
---------------------
sqlplus "/as sysdba"
set pages 200 lines 170
col HOST_NAME for a15
select instance_name,name db_name,host_name,version db_version from gv$instance,v$database;

SQL>select count(*) from dba_users;
SQL>select username from dba_users;

step 2:
------
source and target tablespaces must be same and check the space avilabilty too.
NOTE: Otherwise, need to create new tablespace in target side before import

Tablespace size including pct free
----------------------------------
select
   fs.tablespace_name                          "Tablespace",
  df.totalspace                               "TOT_SIZE",
  fs.freespace                                "TOT_FREE",
  (round(100 * (fs.freespace / df.totalspace))) pct_Free
from
   (select      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace  
   from
      dba_free_space
   group by
      tablespace_name  ) fs
where   (df.tablespace_name = fs.tablespace_name ) ORDER BY pct_free DESC ;

Target side
===========
step 3:
------
passwords bkp:
-------------
select 'alter user '||username||' identified by values '||password||';' from dba_users; --10g

select 'alter user '||name||' identified by values '||password||';' from SYS.USER$;  --11g

Before droping all users take DDL of each user in the target.
set long 100000
select dbms_metadata.get_ddl('USER','USERNAME') from dual;

Before droping user:(super Script---> colecting all grants of user):
--------------------------------------------------------------------
set long 100000

select dbms_metadata.get_ddl('USER',upper('&username')) from dual;
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', upper('&username') ) from dual;
select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',upper('&username')) FROM dual;
select GRANTEE,TABLE_NAME from dba_tab_privs where GRANTEE=upper('&username');
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', upper('&username')) from dual;

Drop the all users except the default users (sys,system,DBSNMP,OUTLN,DIP,TSMSYS,)
DROP USER <USERNAME> CASCADE

OTHER QUERIES
-----------------------
SELECT 'alter system kill session ''' || SID || ',' || serial# || ''' immediate;' FROM v$session where username=upper('&username') and status ='INACTIVE';

SELECT 'alter system kill session ''' || SID || ',' || serial# || ''' immediate;' FROM v$session where username=upper('&username') and status ='ACTIVE';

select sid,username,terminal,program,logon_time,status from v$session where username=upper('&username');

select SID,USERNAME,STATUS from v$session where status='ACTIVE';

step 4:
------
create all users using DDLs taken before.

step 5:
------
EXP/IMP
-------
Full Database import
nohup imp <userid>/<password>  file=filename.dmp log=filename.log buffer=2000000 full=y &

step 6:
------
Take DDL of all users in the target.


Gather schema stats after schema refresh
----------------------------------------
set timing on

set long 100000
select dbms_metadata.get_ddl('USER','USERNAME') from dual;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


EXPDP/IMPDP
-----------------
create or replace directory <EXPDP_DIR> as '/location/of/directory';
grant read,write ON DIRECTORY <EXPDP_DIR> to <USER>;

select * from dba_directories;

Full Database export
nohup expdp <userid>/<password> directory=<EXPDP_DIR> logfile=filename.log dumpfile=dumpfile_`date +\%b\%d`_%U.dmp PARALLEL=6 full=y &

Full Database import
nohup impdp userid=<userid>/<password> directory=<EXPDP_DIR> logfile=filename.log dumpfile=filename.dmp PARALLEL=6 full=y &

Pre-checks
----------
All steps same as EXP/IMP.

NOTE: Before doing Full database refresh with EXP/IMP & EXPDP/IMPDP, need to confirm with requestor whether they need Full database refresh or only some schemas refresh.

NOTE: ONCE TASK COMPLETED AT PRODUCTION SERVER IMMEDIATLY DO EXIT. 

"Hope for the best, prepare for the worst"

-Dharmendra