How To Automate DB Refresh Through Import
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: How To Automate DB Refresh Through Import

  1. #1
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    How To Automate DB Refresh Through Import

    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
    Last edited by padmam; 12-04-2002 at 03:07 PM.
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width