Full Database Refresh Using Export Dump

1. Input from Requester :

1.1 Source and Target database Name.

If not present, check with requester.

1.2        Export dump timestamp.

2. Pre-Check :

2.1        Check for the existence/In-Scope of the Source and Target Databases in PAC.

2.2        Check for existence of Export dump in Source Database.

Check for successful completion of Export from export log.

2.3        Check for Database size in Source Database.

“Select tablespace_name,sum(bytes)/1024/1024 MB from dba_segments group by tablespace_name;”

2.4        Check for Space availability in Target Database.

( Available Space for refresh =  Total allotted space in Tablespace(s) ) check this file

Note : If there is not enough free space for refresh then tablespace(s)

can be resized. Disk space can also be increased by Opening GRS request to SA.

                                   

3. Activities in Source Database/Server:

3.1        Scp export dump from Source server to target server

scp target_unix_user@target_hostname:<$BACKUP/export/hcl/

     

4. Activities  in Target Database/Server:

( Default activity location $backup/export/hcl )

4.1        Restart database in Restricted Mode.

SQL> alter system enable restricted session;

System altered.

4.2        Take Structure export of the database. Take same script for schema refresh

R:\DBA\paresh\DB-TASKS\Database-Refresh\precheck_details_main_for_schema_SCRIPT.sql

exp  /  file=${ORACLE_SID}_norows.dmp log=${ORACLE_SID}_norows.log full=y rows=n statistics=none

4.3        Take backup of passwords of all the users.

Set line 160

Set pages 1000

spool user_passwd.sql

select 'alter user ' ||u.username|| ' identified by values '''||u.password||''';'

from   sys.dba_users u;

spool off

4.4        Drop all Schema except SYS,SYSTEM,OUTLN,RMAN,DBSNMP,

  SCOTT,PERFSTAT,FOGLIGHT,WMSYS AND EXTERNAL USER.

Set line 160

Set pages 1000

Spool drop_users.sql

select 'drop user '||username||' cascade;' from dba_users where username not in 

('SYS','SYSTEM','OUTLN','RMAN','DBSNMP','SCOTT',

'PERFSTAT','FOGLIGHT','WMSYS') and username not like 'OPS%';

spool off

Set line 160

Set pages 1000

Spool drop_users.sql

select 'drop user '||username||' cascade;' from dba_users

where username not in ('SYS','SYSTEM','OUTLN','RMAN',

'DBSNMP','ACCT_ADMIN','ACC_RELEASE','AUDTASK','AUD_OWNER','CM_PROD',

'CTXSRV','DBA_MON','DMG','SCOTT','PERFSTAT',

'FOGLIGHT','WMSYS','ANONYMOUS','CTXSYS','DB_TOOLS','DIP',

'ESMDBA','EXFSYS','HIDS_AUDITOR','MDDATA','MDSYS',

'MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS',

'OUTLN','SI_INFORMTN_SCHEMA','SYSMAN','TSMSYS','WKSYS',

'XDB','HEALTH_CHECKS','OLAP_USER','ORACLE','PUBLIC','QUEST_CENTRAL',

'TRACESVR','WEBSYS''WKUSER') and

username not like 'OPS%';

spool off

4.5        Make Sure sufficient space available for Full refresh ( step 2.3 and 2.4)

4.6        Start import

mknod imp_pipe  p

uncompress < > imp_pipe &

imp / file=imp_pipe buffer=10000000 full=y  log=imp_full_SID_mmddyy.log  feedback=10000

4.7        Moniter import ( tail –f  nohup.out )

4.8        Check import log for errors (if any) and resolve.

4.9        Compile invalid objects by running utlrp.sql as sysdba.

@?/rdbms/admin/utlrp.sql

4.10      Drop all database link.

set serveroutput on size 100000

spool drop_link.sql

 declare v_paswd  varchar2(30) ;

 begin

 for i in ( select distinct owner  from dba_db_links where owner != 'PUBLIC' ) loop

 select password into v_paswd from dba_users where username = i.owner;

 dbms_output.put_line('connect / ' ) ;

 dbms_output.put_line ( ' alter user '||i.owner||' identified by a; ') ;

 dbms_output.put_line('connect '||i.owner||'/a' ) ;

 dbms_output.put_line(' alter user '||i.owner||' identified by values '''||v_paswd||''' ;' );

    for ii in ( select db_link from dba_Db_links where owner=i.owner ) loop

     dbms_output.put_line(' drop database link '||ii.db_link||' ; ');

   end loop;

 end loop;

 end;

/

spool off

@drop_link.sql

4.11      Apply password from backup ( step 4.3).

4.12      Change database to normal mode.

                        Alter system disable restricted session;