1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
Dismiss Notice
Welcome to our Education website, plz like our page facebook to support us. Thank You and wish you good navigation

TP2 - Programmation PL/SQL

abdelouafiOct 18, 2016

    1. abdelouafi

      abdelouafi Administrator Staff Member

      Messages:
      181
      Likes Received:
      9
      Trophy Points:
      18
      Joined
      Sep 13, 2016
      ******TP2******

      (Sauvegarde à froid et restauration complète)



      • Script de sauvegarde de la BD
      Code:
      set feedback off heading off verify off
      
      define dir='E:\savefroid'
      
      define fichier= 'E:\tp2\scriptsf.sql'
      
      spool &fichier
      
      select 'host copy ' || Name || ' &dir' from v$datafile;
      
      select 'host copy ' || Name || ' &dir' from v$controlfile;
      
      select 'host copy ' || Member || ' &dir' from v$logfile;
      
      select ' host copy' || Name ||  ' &dir' from v$tempfile;
      
      spool off;
      
      shutdown immediate;
      
      @ &fichier;
      
      startup;

      • suppression des fichiers de la BD


      Code:
      shutdown immediate;
      
      host Del D:\oracle\product\10.2.0\oradata\iga\*.*
      • Restauration de la BD

      Code:
      host copy e:\savefroid\*.* D:\oracle\product\10.2.0\oradata\iga
      
      startup;



      *******TP3*********

      (Récupération complète et incomplète)

      -- execution si la base est en mode no archive log

      -- host copy D:\oracle\product\10.2.0\oradata\iga/*.* D:\archive

      --commuter le journal de reprise


      Code:
      shutdown immediate;
      
      startup mount;
      
      alter database archivelog;
      
      alter system  set log_archive_dest_1='location=d:\archive' scope=both;
      
      alter system set log_archive_start=true scope=spfile;
      
      alter database open;
      Creation d'une tablespace


      Code:
      create tablespace IGA datafile 'e:\tpora\iga02.dbf' size 30M;
      
      create table t2 tablespace IGA as select * from scott.emp;
      • Remplissage de la table t2

      Code:
      begin
      
        for i in 1..10 loop
      
        insert into t2 select * from t2;
      
        end loop;
      
        commit;
      
      end;
      
      /
      
      host copy e:\tpora\iga02.dbf d:\archive
      
      begin
      
        for i in 1..10 loop
      
        insert into t2 select * from t2;
      
        end loop;
      
        commit;
      
      end;
      
      /
      
      select count(*) from t2;


      • Forcer le changement des fichiers log pour les archiver

      Code:
      alter system switch logfile;
      
      alter system switch logfile;
      
      alter system switch logfile;
      
      
      alter system switch logfile;
      
      
      Drop tablespace IGA including contents and datafiles;
      Essai car ce ne va pas marché

      Code:
      begin
      
        for i in 1..2 loop
      
        insert into t2 select * from t2;
      
        end loop;
      
        commit;
      
      end;
      
      /
      
      select count(*) from t2;
      
      select first_change# from v$log_history where sequence#= X ;
      • Restauration des fichiers de données et de contrôles

      Code:
      shutdown immediate;
      
      host copy D:\archive e:\tpora\iga02.dbf
      • Recuperation incomplete

      Code:
      Alter database RECOVER Automatic until scn X;
      • Ouverture de la base

      Code:
      Alter Database open Resetlogs
      • Verification des compteurs

      Code:
      select group#, squence# from v$log;

      ********TP4*********


      • Passer en mode archivelog

      Création d'un tablespace

      Code:
      create tablespace ts1 datafile 'e:\tpora\chaud.dbf' size 30M;
      
      create table t5 tablespace ts1 as select * from scott.emp;

      • pl/sql pour generer de l'activité

      Code:
      begin
      
        for i in 1..10 loop
      
        insert into t5 select * from t5;
      
        end loop;
      
        commit;
      
      end;
      
      /

      • Tablespace en mode backup

      Code:
      alter tablespace ts1 begin backup;

      • Pour voir les fichiers qui sont en mode backup

      Code:
      select tablespace_name, file#, b.status, change#, time from dba_data_files d, v$backup b where d.file_id=b.file# ;
      • sauvegarder le fichier de données de le table space
      Code:
      host copy e:\tpora\chaud.dbf D:\archive
      alter tablespace ts1 end backup;
      • Sauvegarder le fichier de control courant

      Code:
      alter database backup controlfile to 'd:\archive\control4.ctl' reuse;


      *****Mettre les tablespaces en mode de sauvegarde et sauvegarder ses fichiers*****

      Code:
      set serveroutput ON
      
      
      spool D:\tp-4\script.sql
      
      
      declare
      
        Cursor c_ts is select tablespace_name from dba_tablespaces where status<>'READ ONLY';
      
        cursor c_dbf (ts varchar2) is select file_name from dba_data_files where tablespace_name=ts;
      
      begin
      
        For ct in c_ts loop
      
            dbms_output.put_line('alter tablespace '||ct.tablespace_name||' begin backup');
      
           For cd in c_dbf (ct.tablespace_name) loop
      
            dbms_output.put_line('host copy '||cd.file_name||' D:\archive');
      
           end loop;
      
          dbms_output.put_line('alter tablespace '||ct.tablespace_name||' end backup');
      
        end loop;
      
      end;
      
      /
      
      spool off;
       

      Attached Files:

      Last edited: Mar 11, 2017
      Loading...

Share This Page

Share