Back up
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Back up

  1. #1
    Join Date
    Sep 2000
    Posts
    56
    I have ORACLE 8.1.6 Running on Windows N/T.There is a database instance called TEST.The database is meant for training & rarely the data grows.I have never done the back up of this databse.So I thought as a precautionary I will do the back up.I would like to know is it ok if I do a logical backup I mean doing a export of the database by logging as DBA or I need to do a physical backup.If so what all files I should do a back up & how to do that
    Thanks
    Suresh

  2. #2
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Since it is a test Db, simply do a cold backup. Shutdown the database.

    Copy all DBF or ORA files.
    Copy Init<SID>.ora file
    Copy all controlfiles
    Copy all redolog files

    Doing export also will help.

    Hope this helps
    Thanks
    Kishore Kumar

  3. #3
    Join Date
    Sep 2000
    Posts
    56
    Thanks Kishore.But say if abc.dbf(IS 2 GB) AGAIN WE NEED 2GB FOR BACKUP.If I am wrong correct me.So I guess in dos prompt u need to do copy abc.dbf xyz.dbf.
    But the problem is that I am running out of space in my server.So please send the best solution.Sorry for the trouble

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You are Right. you need equivalent space on the server to save the file unless you compress it after copying(Imean one datafile after another, assuming you have multiple big datafiles for your data)

    If you are running outta space,

    1)either ftp unwanted files to some other server where you have space
    2)zip your backup files, Imean to comressed mode
    3)Use full=y and export the dmp file and save it after zipping it/Ftp it to a server where you have enough room.

    Note: Make sure you are using 'BINARY' mode for your ftp.




    [Edited by sreddy on 12-28-2000 at 02:37 PM]

  5. #5
    Join Date
    Nov 2000
    Posts
    344

    stored procedure to make compressed cold backup

    One thing you can do is instead of just an ordinary copy, you can use PKZIP to compress as it copies.

    I am enclosing the source code from a stored procedure I wrote which will build a batch file to do this.

    I am not promising that this will work in your environment, so please inspect it carefully before you run it. You will probably need to change a few things. Running the stored procedure does not perform the backup, instead it spools a batch file via DBMS_OUTPUT which will actully perform the backup.

    You will need to get a copy of pkzip25 from [url]http://www.pkware.com[/url] to use this.


    CREATE OR REPLACE
    Procedure create_cold_backup
    (in_source_dir varchar2,
    in_dest_dir varchar2,
    in_start_stop_db boolean) as

    -- Author : John Dorlon
    -- Date : 8/17/2000

    -- Comments :
    -- It is outside the scope of this procedure to copy the
    -- INIT.ORA file and files in the NETWORK\ADMIN directory.
    -- Be sure not to leave them out of your backup routine.

    -- if in_start_stop_db is true:
    -- 1) The output from this stored procedure will :
    -- a) stop the database
    -- b) zip and copy the database files in parallel
    -- to the in_dest_dir directory
    -- c) start the database
    -- 2) The actual datafiles are copied and in_source_dir
    -- is not used.

    -- if in_start_stop_db is false
    -- 1) The output from this stored procedure will zip
    -- and copy the database files in parallel from
    -- the in_source_dir directory to the in_dest_dir
    -- directory
    -- 2) The procedure assumes that you are using copies of
    -- the database files which already reside in the
    -- in_source_dir directory.

    -- assumes 3 character file extensions!
    cursor c_copy_files(source_dir varchar2, dest_dir varchar2) is
    select 'del ' || dest_dir || f.no_ext || '.zip' del,
    'start /min /low pkzip25 -add -lev=1 ' || dest_dir || f.no_ext || '.zip ' || source_dir || f.file_name copy_zip,
    'start /min /low pkzip25 -add -lev=1 ' || dest_dir || f.no_ext || '.zip ' || f.full_path db_zip,
    'rem start /min /low pkzip25 -extract ' || dest_dir || f.no_ext || '.zip' extract_cmd
    from (select file_name full_path,
    substr(file_name, lastchar(file_name, '\')+1) file_name,
    substr(substr(file_name, lastchar(file_name, '\')+1), 1, length(substr(file_name, lastchar(file_name, '\')+1)) -4) no_ext,
    bytes file_size
    from sys.dba_data_files
    union
    select name full_path,
    substr(name, lastchar(name, '\')+1),
    substr(substr(name, lastchar(name, '\')+1), 1, length(substr(name, lastchar(name, '\')+1)) -4) no_ext,
    1 file_size
    from sys.v_$controlfile
    union
    Select MEMBER full_path,
    substr(MEMBER, lastchar(MEMBER, '\')+1),
    substr(substr(MEMBER, lastchar(MEMBER, '\')+1), 1, length(substr(MEMBER, lastchar(MEMBER, '\')+1)) -4) no_ext,
    BYTES file_size
    From SYS.V_$LOGFILE LF, SYS.V_$LOG L
    WHERE L.GROUP# = LF.GROUP#
    ) f
    order by f.file_size desc;

    v c_copy_files%rowtype;
    ver sys.v_$version.banner%type;
    dbname sys.v_$database.name%type;

    begin

    if in_start_stop_db then
    select banner
    into ver
    from sys.v_$version
    where rownum = 1;

    if instr(ver,' 8.1') > 0 then
    select name
    into dbname
    from sys.v_$database;
    dbms_output.put_line('NET STOP OracleService' || dbname || ' /y');
    if instr(ver,' 8.1.5') > 0 then
    dbms_output.put_line('NET STOP Oracle815TNSListener /y');
    elsif instr(ver,' 8.1.6') > 0 then
    dbms_output.put_line('NET STOP Oracle816TNSListener /y');
    end if;
    end if;
    if instr(ver, ' 7.') > 0 then
    dbms_output.put_line('NET STOP OracleServiceORCL /y');
    dbms_output.put_line('NET STOP OracleTNSListener /y');
    end if;
    end if;

    for v in c_copy_files(in_source_dir, in_dest_dir) loop
    dbms_output.put_line(v.del);
    dbms_output.put_line('sleep 1');
    if in_start_stop_db then
    dbms_output.put_line(v.db_zip);
    else
    dbms_output.put_line(v.copy_zip);
    end if;
    dbms_output.put_line('sleep 5');
    end loop;
    dbms_output.put_line('REM ***************************************************');
    dbms_output.put_line('REM * The next several lines can be uncommented to *');
    dbms_output.put_line('REM * unzip the files in parallel when needed. *');
    dbms_output.put_line('REM ***************************************************');

    for v in c_copy_files(in_source_dir, in_dest_dir) loop
    dbms_output.put_line(v.extract_cmd);
    end loop;

    if in_start_stop_db then
    dbms_output.put_line(':WaitForZip');
    dbms_output.put_line('Sleep 60');
    dbms_output.put_line('pstat | find "PKZIP25" && Goto WaitForZip');

    if instr(ver,' 8.1') > 0 then
    dbms_output.put_line('NET START OracleService' || dbname || ' /y');
    if instr(ver,' 8.1.5') > 0 then
    dbms_output.put_line('NET START Oracle815TNSListener /y');
    elsif instr(ver,' 8.1.6') > 0 then
    dbms_output.put_line('NET START Oracle816TNSListener /y');
    end if;
    end if;
    if instr(ver, ' 7.') > 0 then
    dbms_output.put_line('NET START OracleServiceORCL /y');
    dbms_output.put_line('NET START OracleTNSListener /y');
    end if;
    end if;

    end;
    /



  6. #6
    Join Date
    Nov 2000
    Posts
    344
    another trick you can do before a cold backup to save space is to shrink the datafiles for the temporary and rollback tablespaces before the backup, and then grow them back to normal after the backup.




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