oracle

tnsnames.ora

définie le nom de connexion à une base

exemple :

  • nom de connexion PRESTO
  • SID de l’instance PRESTOT sur le serveur localhost port d’écoute 1521
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

PRESTO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = PRESTOT)
    )
  )

listener.ora

définie les ports d’écoute

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SRVPZSQL03.Proplast.fr)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

liste des utilisateurs connectés

SELECT username,
  osuser,
  machine,
  schemaname,
  PROGRAM,
  status
FROM v$session
WHERE type = 'USER'

info sur la base

SELECT instance_name,
  host_name,
  version,
  status,
  archiver
FROM v$instance;

liste des tables

SELECT table_name FROM dba_tables;

deblocage d’un utilisateur

exemple avec le compte system

>sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Lun. Juin 24 16:57:20 2013
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
SQL> conn presto/prestop@PRESTO
Connecté
SQL> ALTER USER system ACCOUNT UNLOCK;

Utilisateur modifié.

SQL> commit;

Validation effectuée.
SQL> disconn

pour modifier le mot de passe d’un user

ALTER USER name_user IDENTIFIED BY motdepasse ;

Taille de la base

SELECT sum(bytes)/1024/1024 "Tailletbs en MO",
   tablespace_name
FROM dba_data_files
GROUP BY tablespace_name

Calcul de statistique

exec DBMS_STATS.gather_schema_stats( ownname => 'PRESTO' , cascade => TRUE)

Dernière calcul de statistique

SELECT TABLE_NAME, LAST_ANALYZED
FROM DBA_TABLES
WHERE OWNER='PRESTO'

Sauvegarde d’une base oracle

Un exemple très simple de script de sauvegarde à froid des fichiers de données, des redolog, des fichiers de contrôles, et temporary tablespace d’une base de données.

Très utile et rapide en terrain inconnu, où les fichiers sont un peu dispersés à droite et à gauche.

(Ne pas tester en environnement de Production, mais en test d’abord !!!!)

1 Construction dynamiques des ordres de host copy par interrogations des tables

  • v$datafile
  • v$logfile
  • v$controlfile
  • v$tempfile

2 Insertion de ces lignes dans un fichier backup.bat dans c:save

3 Creation backup control files pour re-création des control files si besoin

4 Arrêt de la base

5 Auto exécution de la copie à froid (backup.bat)

6 Redémarrage de la base

Lancer ce script directement en mode console sans vous connecter à SQL*PLUS par

Copier/Coller.

Prenez le soin de changer la chaine de connexion et vérifiez la présence du répertoire C:cold_backup et C:save pour ce test.

Si vous avez peur du script !! Vous pouvez mettre en commentaire les 4 dernières lignes, il y aura uniquement la construction du fichier .bat dans c:save sans shutdown immediate etc.

sqlplus -s "sys/mdp@NomTNS as sysdba"
SET pagesize 0
SET linesize 500
SET verify off

define folder ='c:\cold_backup'
define batch='c:\save\backup.bat'

spool &batch;
select 'host copy '|| name|| ' &folder' from v$datafile order by 1 ;
select 'host copy '|| member || ' &folder' from v$logfile order by 1 ;
select 'host copy '|| name || ' &folder' from v$controlfile order by 1 ;
select 'host copy '|| name || ' &folder' from v$tempfile order by 1 ;
spool off

alter database backup controlfile to trace as 'C:\cold_backup\BckCtl.sql' REUSE;

shutdown immediate;

@&batch;

startup;
exit;

Contenu du fichier C:savebackup.bat en sortie qui sera exécuté en auto

host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\SYSAUX01.DBF c:\cold_backup
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\SYSTEM01.DBF c:\cold_backup
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\UNDOTBS01.DBF c:\cold_backup
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\USERS01.DBF c:\cold_backup
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\DATA_01.DBF c:\cold_backup
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\DATA_02.DBF c:\cold_backup

host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\REDO01.LOG c:\cold_backup
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\REDO02.LOG c:\cold_backup
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\REDO03.LOG c:\cold_backup

host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\CONTROL01.CTL c:\cold_backup
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\CONTROL02.CTL c:\cold_backup
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\CONTROL03.CTL c:\cold_backup

host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\TEMP01.DBF c:\cold_backup

Export / import d’une base oracle

Pré-requis pour utiliser DataPump

Avant de pouvoir utiliser DataPump, vous devez créer une Directory Oracle où vous allez stocker vos exports. Pour ceci, ouvrez une commande SQL*Plus et exécutez cette commande :

CREATE DIRECTORY exports as '/home/exports/' ;

Ici, nous avons créé un répertoire appelé « Exports » qui sera bindé au répertoire système /home/exports . Bien sûr, vous pouvez choisir le nom de répertoire et le répertoire bindé que vous voulez ;-)

Ensuite, il vous faudra donner les droits d’écriture et de lecture à l’utilisateur que vous allez utiliser pour faire vos exports/imports sur le répertoire précédemment créé.

GRANT read,write ON DIRECTORY exports TO scott ;

Export

L’export le plus simple que vous pouvez faire, c’est l’export de toute votre base de données. Pour ceci, vous pouvez lancer cette ligne de commande :

$ORACLE_HOME/bin/expdp scott/tiger DIRECTORY=exports DUMPFILE=exportfull.dmp FULL=y LOGFILE=exportfull.log

Vous remarquerez l’utilisation de notre utilisateur Scott à qui on a donné les droits précédemment sur la directory exports utilisée ci-dessus aussi. Cette commande va créer un dump qui s’appellera exportfull.dmp et un log exportfull.log dans le répertoire /home/exports de votre serveur.

Il est possible d’exporter seulement un schéma ou un object

$ORACLE_HOME/bin/expdp scott/tiger DIRECTORY=exports DUMPFILE=exportschemascott.dmp SCHEMAS=scott

$ORACLE_HOME/bin/expdp scott/tiger DIRECTORY=exports DUMPFILE=exportschemascott.dmp SCHEMAS=scott,hr,sh

Import

Ce n’est pas le tout d’exporter votre base de données, il vous faudra aussi l’importer Cette fois-ci, au lieu d’utiliser l’exécutable expdp , nous allons utiliser l’exécutable impdp pour Import DataPump. 3 Il s’utilise quasiment de la même façon que expdp. Voici un exemple d’utilisation pour importer toute sa base :

$ORACLE_HOME/bin/impdp scott/tiger DIRECTORY=exports DUMPFILE=exportfull.dmp FULL=y LOGFILE=exportfull.log

il est parfois utile d’ajouter en paramètre

TABLE_EXISTS_ACTION=REPLACE

pour que les tables soient remplacés si elles existent

Attention

si une séquence existe déjà il faut la mettre à jour

Methode complète export avec création d’un utilisateur

exemple de méthode complète avec création d’un utilisateur forbkp ayant pour mot de passe forbkp

> sqlplus system/manager@PRESTO
create user forbkp identified by forbkp;
grant connect,resource to forbkp;
grant exp_full_database to forbkp;
grant imp_full_database to forbkp;

create directory datapump as 'E:/SVG/SGBD';
grant read, write on directory  datapump to forbkp;

la sauvegarde

D:\oracle\product\10.2.0\db_1\BIN\expdp forbkp/forbkp@PRESTO DIRECTORY=datapump DUMPFILE=exportfull.dmp FULL=y LOGFILE=exportfull.log

Suppression d’une base

Nous allons supprimer une base et enlever sous windows les services liés

C:\Documents and Settings\Administrateur>set ORACLE_SID=PRESTODEV

C:\Documents and Settings\Administrateur>sqlplus /nolog

    SQL*Plus: Release 10.2.0.4.0 - Production on Mer. Juil. 17 13:59:50 2013

    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

    SQL> connect / as sysdba
    Connecté.
    SQL> shutdown abort
    Instance ORACLE arrêtée.
    SQL> drop database
      2
    SQL> exit
    DÚconnectÚ de Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

C:\Documents and Settings\Administrateur>sqlplus /nolog

    SQL*Plus: Release 10.2.0.4.0 - Production on Mer. Juil. 17 14:00:22 2013

    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

    SQL> connect / as sysdba
    Connecté à une instance inactive.
    SQL> disconnect
    Déconnecté
    SQL> ^Z

C:\Documents and Settings\Administrateur>oradim -delete -sid PRESTODEV
Instance supprimée.

Etat des tables spaces

select
    a.TABLESPACE_NAME,
    a.CONTENTS,
    a.EXTENT_MANAGEMENT,
    a.ALLOCATION_TYPE,
    a.SEGMENT_SPACE_MANAGEMENT,
    a.BIGFILE,
    a.STATUS,
    nvl(sum(b.count_files),0) FILES,
    nvl(sum(b.bytes),0) "SIZE",
    nvl(sum(b.maxbytes),0) MAX_SIZE,
    nvl(sum(b.bytes),0)-nvl(sum(c.free_bytes),0) "USED"
from DBA_TABLESPACES a,
    (
    select TABLESPACE_NAME,
        sum(BYTES) bytes,
        count(*) count_files,
        sum(greatest(MAXBYTES,BYTES)) maxbytes
    from DBA_DATA_FILES
    group by TABLESPACE_NAME
    union all
    select TABLESPACE_NAME,
        sum(BYTES),
        count(*),
        sum(greatest(MAXBYTES,BYTES)) maxbytes
    from DBA_TEMP_FILES
    group by TABLESPACE_NAME
    ) b,
    (
    select TABLESPACE_NAME,
        sum(BYTES) free_bytes
    from DBA_FREE_SPACE
    group by TABLESPACE_NAME
    union all
    select TABLESPACE_NAME,
        sum(BYTES_FREE) free_bytes
    from V$TEMP_SPACE_HEADER
    group by TABLESPACE_NAME
    ) c
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
    and a.TABLESPACE_NAME = c.TABLESPACE_NAME (+)
group by
    a.TABLESPACE_NAME,
    a.CONTENTS,
    a.EXTENT_MANAGEMENT,
    a.ALLOCATION_TYPE,
    a.SEGMENT_SPACE_MANAGEMENT,
    a.BIGFILE,
    a.STATUS
order by a.TABLESPACE_NAME;

TABLESPA CONTENTS  EXTENT_MAN ALLOCATIO SEGMEN BIG STATUS FILES      SIZE   MAX_SIZE      USED
-------- --------- ---------- --------- ------ --- ------ ----- --------- ---------- ---------
SYSAUX   PERMANENT LOCAL      SYSTEM    AUTO   NO  ONLINE     3 397410304 6442450944 395771904
SYSTEM   PERMANENT LOCAL      SYSTEM    MANUAL NO  ONLINE     1 471859200 2147483648 470548480
TEMP     TEMPORARY LOCAL      UNIFORM   MANUAL NO  ONLINE     1  20971520 2147483648  17039360
UNDOTBS1 UNDO      LOCAL      SYSTEM    MANUAL NO  ONLINE     1 182452224 2147483648  13041664
USERS    PERMANENT LOCAL      SYSTEM    MANUAL NO  ONLINE     1  26214400 2147483648    524288