How To Automate DB Refresh Through Import

[padmam]

Hi,

This Document Details the Automated Scheduling of a DBRefresh through a Batch file.
=====================================================================

1)This topic Assumes you have an export file to be refreshed in to the Target Production DB.

2)Also after Import it Validates all Invalid Objects due to Import.

3)Also the scripts uses the svrmgrl .You can call sqlplus and avoid the select of date and time by embedding "set time on" once.

4)Experiment on sample test case before you implement it on Production DB.

5)You can have this complete steps changed according to your DB needs standardized and each and every time just change the schema names, connect string and paths to run in automated scheduled time.

Steps to follow
================

1)In the Local server create some additional table in Scott Schema exp the Schema Scott as c:\scottdel.dmp

2)Drop the Schema

3)Place 5 More files save it with Proper extensions .SQL or .BAT in c:\ as mentioned below.

i)scottpre.sql
ii)scottpost.sql
iii)createseq.sql
iv)Createseq1.sql
v)Automation_Import_test.bat

Contents of the above files
===========================

i)scottpre.sql
==============

connect internal@orcl

select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;
select username,PASSWORD,ACCOUNT_STATUS from dba_users where USERNAME='SCOTT';

drop user SCOTT cascade;

CREATE USER "SCOTT" PROFILE "DEFAULT"
IDENTIFIED BY TIGER
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED
ON USERS ACCOUNT UNLOCK;

GRANT CONNECT, RESOURCE, DBA
TO SCOTT;

GRANT CREATE ANY INDEX,
CREATE ANY TRIGGER,
CREATE SEQUENCE,
CREATE TABLE,
DROP ANY INDEX,
DROP ANY SEQUENCE,
DROP ANY TABLE,
UNLIMITED TABLESPACE
TO SCOTT;

select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;

exit

ii)scottpost.sql
================

connect internal@orcl
select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;
execute DBMS_UTILITY.COMPILE_SCHEMA('SCOTT');
execute DBMS_UTILITY.COMPILE_SCHEMA('SCOTT',FALSE);
select count(*) from dba_objects where owner = 'SCOTT' and status = 'INVALID';
execute DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE');
execute DBMS_UTILITY.Analyze_schema('SCOTT','COMPUTE',null,null,'FOR ALL INDEXES');
execute DBMS_UTILITY.Analyze_schema('SCOTT','COMPUTE',null,null,'FOR ALL INDEXES');
select count(*) from dba_indexes where owner = 'SCOTT' and status <> 'VALID';
exit

iii)createseq.sql
=================

connect scott/tiger
select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;
create table temptab( table_name varchar(30), nextval number);
create sequence delseq;
@@c:\create1.sql
exit

iv)Createseq1.sql
=================

create table pad(a number);
exit

v)Automation_Import_test.bat
=============================
echo Importing Schema Scott ... >c:\SchemaImportAutomation.out
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\scottpre.sql" >>c:\SchemaImportAutomation.out
call c:\oracle\ora81\bin\imp.exe scott/tiger file=c:\scottdel.dmp log=c:\scottdel.log rows=y commit=y full=yes >>c:\SchemaImportAutomation.out
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\createseq.sql" >>c:\SchemaImportAutomation.out
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\scottpost.sql" >>c:\SchemaImportAutomation.out


After saving all this files

Just create the Schema Scott (after export drop the scott schema)as told in scottpre.sql alone.There will be no Objects in that.

Now schedule the Job to run auotomatically at scheduled time as follows.
========================================================================

Now go to Start==>Settings==>Control Panel==>Scheduled Tasks==>Add Scheduled Task==>Open(double click)==>A Scheduled Task Wizard is Opened==>Click Next==>Browse==>Choose the Batfile (Automation_Import_test.bat in c:\) ==>Now Plan how you want the DBrefresh Should Happen Daily,Weekly as per your requirement(In our case Once)==>Set the timing in the same screen(In our case 2 Mints Ahead of the current time) It will ask for the O/S Uname/Pwd twice==>
If you want open the Advance option to have a Look(you can..no need)

After the Scheduled Task is run we can find 2 Trace files as

i)c:\SchemaImportAutomation.out
ii)c:\scottdel.dmp log

The above 2 files are to check whether any errors are reported.

Check the count of objects in the scott schema.The old exported dmp objects as well as new objects would have been created.

Note:
=====

i)Before Scheduling we need to Check all the Checklist steps are Satisfied.If you have any Manual DB refresh Checklist.

ii)We need to change where ever required with appropriate the Schema Names ,Connect Strings as per the DB server on which DB refresh Happens.

iii)Still we can Tailor the Number of file to less.Have added additional files to check how one sql script file is executed from another.

iv)If you have some tasks of re sequencing after import you can call that script file too.

iv)Ensure you are not logged in to Scott Schema after scheduling.

Suggestions are most welcome.Make DBA Job Cool.Have a Great Time.

Cheers

Padmam