mercredi 17 février 2016

Restauration d'un DB Node Exadata

En cas de perte d'un DB Node (Disques HS / remplacement du DB Node), Oracle propose de faire un reimaging du serveur si il n'y a pas de de Snaphost du serveur (Reinstaller le DB Node de 0).
Mais si il existe une sauvegarde du DB Node, alors il est possible de restaurer le snaphshot. Pour voir comment réaliser un snaphshot d'un dbnode, voir "Sauvegarde d'un DB Node"

Cette solution a été testée et validée sur un Exadata X5-2. Sur les versions plus anciennes, il se peut qu'il y ait des actions supplémentaires, voir dans le Guide de maintenance de l'Exadata - e51951.

Voici le déroulement des opérations qui sera ensuite détaillé pas à pas :
- Monter un serveur NFS sur le serveur sur lequel se trouve le fichier de sauvegarde.
- Sur un autre noeud de l'Exadata (db node survivant), récupérer le fichier Diagnostics.iso
- Envoyer ce fichier .iso à travers l'ILOM du db node à restaurer
- Démarrer le db node sur ce fichier
- Restaurer le DB Node
- Redémarrer le db node.

/!\ Attention /!\
/!\ ------------ /!\
  • Cette operation est irreversible et l'ensemble du DB Node sera formatté. Toute les données seront perdues.
  • De plus, cette opération est possible, si et seulement si, le snaphshot a été réalisé dans les conditions définies dans le Guide de Maintenance de l'Exadata à savoir, si seulement les 3 partitions suivantes ont été sauvegardées : /root /u01 /boot. Si d'autres partitions ont été sauvegardées dans ce Snaphshot (Snaphost with Customized Partitoins), alors une autre méthode de Restauration est à utiliser.
  • Enfin, si l'exadata est une machine 1/8, alors une autre action est à réaliser. Elle consiste à vérifier le bridage logiciel du nombre de coeurs activés sur le db node.
/!\ ------------ /!\

Dans le jeux de test ci dessous, le db node 1 (hostname NODE_1) est à restaurer, il reste 3 db node up & running. Le NODE_2 sera utilisé pour la partie NFS.
  1. Montage du serveur NFS

  2. La premiere chose a faire est de recupérer le fihcier de Sauvegarde du db node. Dans ce environement, pour simplifier l'operation, les Snaphosts sont sur un espace ACFS, accessible par tous les autres noeuds.
    Ce montage NFS doit être accessible par l'ILOM.

    • Demarrer les différents services si besoin (rpcbind et nfs)
    • NODE_2 # /etc/init.d/rpcbind start
      NODE_2 # /etc/init.d/nfs start

    • Créer le point de montage
    • NODE_2 # ls /backups/
      backup.tar.bz2
      NODE_2 # exportfs -o rw IP_DB_NODE_1 :/backups/


  3. Recuperer le fichier Diagnostics.iso
  4. Sur tous les serveurs db de l'exadata, le fichier Diagnostics.iso est présent. Ce fichier de Diagnostics permet d'effectuer des taches de recover, en cas de restauration (comme ici) ou encore, en cas de probleme qui necessite une réparation du système (Ex : DB Node qui ne démarre plus suite à une modification du fichier fstab...).
    Dans ce second cas, un shell de type BusyBox est ouvert et permet quelques commandes unix.
    Télécharger sur votre poste ce fichier, il se trouve dans le repertoire suivant :

    NODE_2 # pwd
    /opt/oracle.SupportTools
    NODE_2 # ls *.iso
    diagnostics.iso

  5. Envoyer fichier ISO vers l'ILOM du db node
  6. Ce fichier .iso va permettre de simuler un CDRom dans le serveur et lors du prochain démarrage, le serveur passera par le fichier .iso plutot que par le disque.

    • Accéder a l'ILOM du NODE_1 en https. Dans le menu de gauche, sélectionner "Redirection" dans Remote Control. Cocher la case "Use Serial Redirection" et lancer la console.


    • Dans le menu KVMS de l'ILOM, choisir Storage. Cliqer sur Add et selectionner votre fichier Diagnostics.iso sur votre poste. Cliquer ensuite sur Connect pour monter le fichier .iso













  7. Redémarrer le db node en utilisant le fichier .iso

    • Dans le menu de l'ILOM, selectionner Host Control dans "Host Management". Dans la liste, sélectionner "CD Rom" et enregistrer.


    • Initialiser un redémarrage du serveur via le menu "Host Management", selectionner "Power Control". Dans la liste, sélectionner "Power Cycle" et cliquer sur "Save". Un redémarrage du serveur va être lancé, il devra normalement redémarrer sur le fichier .iso monté dans l'étape précédente. Le redémarrage d'un db node peut prendre de 5 à 10 minutes.

    • Il est possible de voir le BootLog pour voir l'état du démarrage du serveur, en utilisation la redirection video de la console. Il est possible de lancer cette redirection en parallèle de la redirection Série.




  8. Restauration

    • Sur la console série, taper 'r' pour lancer la restauration à partir du NFS. Le mot de passe de secours (rescue password) est demandé : sos1exadata. Attention en tapant le mot de passe, le retour arrière ne fonctionne pas et le clavier est en AZERTY.




    • Plusieurs questions sont posées afin de définir l'emplacement du fichier .iso ainsi que les informations réseaux de la machine à restaurer.

      • NFS Line : IP_DB_NODE_2:/backups/backup.tar.bz2
      • Use LVM based schema : y (Si vous êtes sur une installation standard de l'exadata, répondre "Y")
      • Ethernet Interface : eth0
      • L'interface réseau peut être différente en fonctione de la configuration. De plus, cette étape peut prendre du temps
      • IP Address of this host : IP_DB_NODE_1
      • Netmask of this hosts : DEMASK_DB_NODE_1
      • Default Gateway : GTW_DB_NODE_1
      • /!\Après avoir appuyé sur "Entrée", la restauration commence. Aucun message sur la console pour prévenir du démarrage de l'opération./!\

      Pour vérifier les actions qui sont en train de se dérouler, il est possible de se connecter à la console OEM pour voir le traffic réseaux, on peut y voir une consommation réseaux importante entre les 2 serveurs, dû à la copie du fichier en NFS.

    • Une fois le transfert NFS terminé, l'extration du fichier de sauvegarde démarre automatiquement


    • A la fin de la restauration, l'écran devrait basculer sur le prompt de login. Si ce n'est pas le cas mais que l'affichage est le même que ci dessous, alors, appuyer sur "Entrée"




  9. Redemarrage du db node

  10. Avant d'effectuer le redémarrage, il est possible de passer des commandes sur la couche système afin, par exemple, d'anticiper certaines actions.
    Par exemple, la couche OHASD peut être désactivée, le fichier de partition fstab peut être modifié pour ne pas monter certains points de montage au redémarrage...
    Pour terminer, lancer un reboot du serveur via la commande "Reboot". Lors de ce reboot, le serveur va automatiquement démarrer sur disque dur et pas CDRom. En effet, la modification effectuée précédement pour démarrer sur le CD Rom n'est pas valable que pour le premiere reboot du serveur.
    Il suffit maintenant de déconnecter le fichier .iso en retournant dans le menu Storage.
    Au redémarrage du serveur, pour vérifier que la restauration s'est bien déroulée, passer la commande suivante :

    # /usr/local/bin/imagehistory
    Version : 12.1.2.2.0.xxxxx
    Image activation date : 2016-01-01 10:00:00 +0100
    Imaging mode : fresh
    Imaging status : success

    Version : 12.1.2.2.0.xxxxx
    Image activation date : 2016-01-10 10:00:00 +0100
    Imaging mode : restore from nfs backup
    Imaging status : success



Sauvegarde d'un DB Node Exadata


La sauvegarde du db node d'un Exadata va permettre de restaurer rapidement le serveur, à l'instant où la sauvegarde a été effectuée. Ce Snapshot doit suivre certaines conditions définies par Oracle afin d'être utilisable pour la restauration.
Avant tout, cette opération a été testée et validée sur un Exadata X5-2. Sur les versions plus anciennes, il se peut qu'il y ait des actions supplémentaires, voir dans le Guide de maintenance de l'Exadata - e51951.

Voici le déroulement des opérations qui sera ensuite détaillé pas à pas :
- Pre Requis
- Creations des snapshots
- Sauvegarde du DB Node
- Suppression des snapshots

/!\ Attention /!\
/!\ ------------ /!\
  • Cette operation est validée et fonctionnelle, seulement si les partitions suivantes sont sauvegardées : /boot /root /u01 (Conditions Oracle). Si d'autres partitions sont montées (Spécifique au projet par exemple), il est conseillé de faire :
    - Une sauvegarde conforme Oracle (partitions /boot /root /u01)
    - Une sauvegarde différente pour les autres partitions.
    En effet, dans la doc Oracle, la méthode de restauration se complique si il y a d'autres partitions que /boot /root et /u01 dans la sauvegarde. Le fait de séparer ces 2 sauvegardes permet donc de simplifier la restauration. Si aucune partition a été ajouté à l'Exadata, alors la méthode de sauvegarde ci dessous est conforme. Si d'autres partitions sont présente, alors il faudra sauvegarder ces partitions en plus, en suivant la même méthode ou avec tout autre outil de sauvegarde.

  • Très important, après la sauvegarde (Réussie ou en Echec), il faut démonter les snapshots. Dans le cas contraire, si il y a un reboot de l'Exadata, il redémarrera en boucle car il y aura un blocage pendant le chargement des partitions. Il faudra alors utiliser le mode Rescue du Diagnostics.iso pour supprimer ces snapshots.
/!\ ------------ /!\
  1. Pre requis

  2. Le but est de faire une sauvegarde des 3 partitions principales, qui seront nécessaire à la restauration du db node :
    /root /boot et /u01.
    Pour faire ça, des snapshots temporaires vont être créés, afin d'avoir une sauvegarde consistente.
    Première opération, créer un repertoire où stocker les snapshots :

    mkdir -p /root/mnt/u01

    Cet emplacement ne définit pas où sera stockée la sauvegarde, mais où la sauvegarde ira chercher les fichiers à sauvegarde. (voir plus bas)

    Conseil pratique :
    Ce fichier de sauvegarde devra être disponible depuis un autre serveur en cas de restauration. Il est donc possible de créer un point de montage ACFS dans l'ASM et de placer la sauvegarde dans cet emplacement. Dans ce cas, si un serveur doit être restauré, la sauvegarde sera disponible rapidement sur les db nodes survivants.

  3. Sauvegarde du DB Node

    • Création des snapshots

    • Il faut créer 2 snapshots. Un pour /root et un pour /u01. Pas besoin de créer un snapshots pour /boot, car il ne sera pas modifié pendant la sauvegarde. Il sera simplement ajouté à la commande tar lors de création de la sauvegarde.
      Création des snapshots et labelisation :

      # lvcreate -L1G -s -n root_snap /dev/VGExaDb/LVDbSys1
      # e2label /dev/VGExaDb/root_snap DBSYS_SNAP
      # lvcreate -L5G -s -n u01_snap /dev/VGExaDb/LVDbOra1
      # e2label /dev/VGExaDb/u01_snap DBORA_SNAP


    • Montage des snapshots

    • Avant le montage, il faut vérifier le type de montage des partitions source. Par defaut, sur les Exadata X5, le type de système de fichier est ext4. Sur les versions plus ancienne, ext3.

      # mount -l

      Montage des snapshots avec la commande mount :

      # mount /dev/VGExaDb/root_snap /root/mnt -t ext4
      # mount /dev/VGExaDb/u01_snap /root/mnt/u01 -t ext4


    • Création du fichier de sauvegarde

    • Pour sauvegarder le db node, passer la commande tar suivante :

      # cd /root/mnt
      # tar -pjcvf /backups/backup_db_node.tar.bz2 * /boot > /backups/backup_db_node.stdout 2> /backups/backup_db_node.stderr


      Cette opération peut être très longue (2 à 4h pour un fichier de 25GB environ). La charge est à surveiller, cependant, l'outil TAR ne fonctionne pas en multi threading, d'où les temps de réponses un peu long.

  4. Suppression des snapshots

  5. Même si la sauvegarde a échoué, sauf si une autre action de sauvegarde doit être réalisée à la suite, il faut démonter les snapshots et supprimer les partitions.

    # umount /root/mnt/u01
    # umount /root/mnt
    # lvremove /dev/VGExaDb/u01_snap -f
    # lvremove /dev/VGExaDb/root_snap -f


    Cette sauvegarde respecte les conditions Oracle et pourra donc être utilisée pour la restauration du db node.
    Pour plus d'informations et utiliser des partitions customisées, voir la doc Oracle Guide de maintenance de l'Exadata - e51951.

mardi 14 avril 2015

Configuration du Broker sur Oracle DataGuard 11.2


Pour faire suite à l'article précédent sur la mise en place d'une Standby physique avec Oracle Data Guard 11.2, on va aborder ici la configuration du Broker.
Le broker va permettre de gérer toute la partie transport des archives ainsi que les bascules (Switchover et failover) plus simplement (Voir automatiquement avec le Fast Start Fail Over, à voir dans un prochain article). L'utilisation du Broker n'est pas une option payante, cependant, il y a certaines options payantes pour le broker, comme la compression.

- Sur la primaire et la secondaire, créer les fichiers de configuration qui vont être utilisés par le broker
touch /u01/app/oracle/product/11.2.0.3/db/dbs/db_brokerfile.dat
touch /u03/fra/copy_db_brokerfile.dat

SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0.3/db/dbs/db_brokerfile.dat';
SQL> alter system set dg_broker_config_file2='/u03/fra/copy_db_brokerfile.dat';
A faire sur les deux serveurs

- Ajouter une entrée pour le broker dans les listener.ora sur chaque serveur :
Sur la primaire
(SID_DESC =
      (GLOBAL_DBNAME=DBPRIM_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db)
      (SID_NAME=DBPRIM)
      (SERVICE_NAME=DBPRIM)
 )


Sur la secondaire
(SID_DESC =
  (GLOBAL_DBNAME=DBSEC_DGMGRL)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db)
  (SID_NAME=DBSEC)
  (SERVICE_NAME=DBSEC)
)



- Démarrer le broker avec la commande suivante sur la primaire et la secondaire :
SQL> alter system set dg_broker_start=true;

System altered.

- Se connecter au broker depuis la primaire, en utilisant le compte sys
[oracle@primary fra]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.

- Supprimer l'ancienne configuration si il y e a déjà une, et créer la nouvelle :
DGMGRL> create configuration dgDB as primary database is DBPRIM connect identifier is DBPRIM;
Configuration "dgdb" created with primary database "dbprim"
DGMGRL> add database DBSEC as connect identifier is DBSEC maintained as physical;
Database "dbsec" added
DGMGRL> show configuration

Configuration - dgdb

  Protection Mode: MaxPerformance
  Databases:
    dbprim - Primary database
    dbsec  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

- Activer la configuration
DGMGRL> enable configuration
Enabled.

- Vérifier la configuration
DGMGRL> show configuration

Configuration - dgdb

  Protection Mode: MaxPerformance
  Databases:
    dbprim - Primary database
    dbsec  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database dbprim

Database - dbprim

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    DBPRIM

Database Status:
SUCCESS

DGMGRL> show database dbsec

Database - dbsec

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    DBSEC

Database Status:
SUCCESS

DGMGRL>

Nous pouvons ici détecter si il y a un lag entre les 2 bases, avec les valeurs des paramètres Apply Lag et Transport Lag

- Si on regarde la valeur du paramètre log_archive_dest_2, on voit que la configuration a été écrasée par le Broker :
SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service="dbsec", LGWR ASYNC NO
                                                 AFFIRM delay=0 optional compre
                                                 ssion=disable max_failure=0 ma
                                                 x_connections=1 reopen=300 db_
                                                 unique_name="dbsec" net_timeou
                                                 t=30, valid_for=(all_logfiles,
                                                 primary_role)

- On peut maintenant faire un switchover, attention à bien se connecter au broker avec "sys" et non pas avec "/"
[oracle@primary fra]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> switchover to 'DBSEC';
Site "DBSEC" was not found
DGMGRL> switchover to DBSEC;
Performing switchover NOW, please wait...
New primary database "dbsec" is opening...
Operation requires shutdown of instance "DBPRIM" on database "dbprim"
Shutting down instance "DBPRIM"...
ORACLE instance shut down.
Operation requires startup of instance "DBPRIM" on database "dbprim"
Starting instance "DBPRIM"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "dbsec"


- On peut vérifier en regardant la configuration :
DGMGRL> show configuration

Configuration - dgdb

  Protection Mode: MaxPerformance
  Databases:
    dbsec  - Primary database
    dbprim - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


Si la base primaire tombe, on peut aussi faire un failover, mais il faudra reconstruire la standby ensuite ou la reinstancier (en fonction du retard).

jeudi 19 mars 2015

Mise en place d'une standby physique avec Oracle Data Guard 11.2




Dans cet article, nous allons voir comment configurer une standby en mode physical avec Oracle Data Guard, en utilisant RMAN Duplicate. De plus, nous allons activer le Real Time Apply grâce aux standby redo log.

Dans un précédent article, la mise en place d'une standby sans Data Guard est abordée. En effet, Oracle Data Guard n'est présent qu'avec la version Enterprise Edition, si vous n'avez Enterprise Edition, il faut alors partir sur une standby manuelle, voir : article standby

Deux modes existent : Physique ou Logique.

En mode Logique, les schemas de la standby ne correspondent pas forcement à la base primaire. La logique utilise LogMiner pour transformer les redos logs en langage DML afin que la standby puisse les rejouter.

En mode Physique, les schemas correspondent exactement, les redos logs sont envoyés à la secondaire depuis la primaire, et sont appliqués sur la secondaire qui tourne en mode Recover.


Environnement utilisé dans cet article :
Red Hat 5.3, avec Oracle Database 11.2.0.3
Serveur primaire : primary
Base primaire : DBPRIM
Serveur secondaire : secondary
Base secondaire : DBSEC


- Créer la base primaire si ce n'est pas déjà fait :
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname DBPRIM -sid DBPRIM -responseFile NO_VALUE -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 -totalMemory 1024 -emConfiguration NONE -SysPassword oracle -SystemPassword oracle -redoLogFileSize 50 -datafileDestination /u02/


- Modifier le pfile :
Ajouter les valeur suivantes :
fal_client='DBPRIM'
fal_server='DBSEC'
log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBPRIM'
log_archive_dest_2='SERVICE=DBSEC ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSEC'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='enable'
log_file_name_convert='/u03/fra/DBPRIM/onlinelog/','/u03/fra/DBSEC/onlinelog/'
db_file_name_convert='/u02/DBPRIM/','/u02/DBSEC/'
log_archive_config='DG_CONFIG=(DBPRIM,DBSEC)'


De mon côté, j'ajuste aussi le paramètre suivant :
db_recovery_file_dest='/u03/fra'


SQL> create pfile='/tmp/pfile_DBPRIM.ora' from spfile;

SQL>!vi /tmp/pfile_DBPRIM.ora
DBPRIM.__db_cache_size=402653184
DBPRIM.__java_pool_size=16777216
DBPRIM.__large_pool_size=16777216
DBPRIM.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DBPRIM.__pga_aggregate_target=436207616
DBPRIM.__sga_target=637534208
DBPRIM.__shared_io_pool_size=0
DBPRIM.__shared_pool_size=184549376
DBPRIM.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DBPRIM/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/DBPRIM/control01.ctl','/u01/app/oracle/fast_recovery_area/DBPRIM/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBPRIM'
*.db_recovery_file_dest='/u03/fra'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBPRIMXDB)'
*.memory_max_target=2097152000
*.memory_target=1073741824
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
fal_client='DBPRIM'
fal_server='DBSEC'
log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBPRIM'
log_archive_dest_2='SERVICE=DBSEC ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSEC'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='enable'
log_file_name_convert='/u03/fra/DBPRIM/onlinelog/','/u03/fra/DBSEC/onlinelog/'
db_file_name_convert='/u02/DBPRIM/','/u02/DBSEC/'
log_archive_config='DG_CONFIG=(DBPRIM,DBSEC)'


- Redémarrer en utilisant le pfile
SQL> startup mount pfile='/tmp/pfile_DBPRIM.ora';
ORACLE instance started.

Total System Global Area 2087780352 bytes
Fixed Size 2229944 bytes
Variable Size 1677723976 bytes
Database Buffers 402653184 bytes
Redo Buffers 5173248 bytes
Database mounted.

SQL> create spfile from pfile='/tmp/pfile_DBPRIM.ora';


- Passer en mode Archivelog et Force logging
SQL> alter database archivelog;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> alter database open;

Database altered.


- Configurer les fichiers tnsnames.ora sur les deux serveurs :
vi $ORACLE_HOME/network/admin/tnsnames.ora

DBSEC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = secondary)(Port = 1521))
)
(CONNECT_DATA =
(SID = DBSEC)
)
)

DBPRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = primary)(Port = 1521))
)
(CONNECT_DATA =
(SID = DBPRIM)
)
)


- Créer un password file et le copier sur la secondaire (respecter le nommage pour le fichier : orapwSID)
[oracle@primary tmp]$ orapwd file=orapwDBPRIM password=oracle entries=5
[oracle@primary tmp]$ scp orapwDBPRIM oracle@secondary:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/orapwDBSEC


- Sur la secondaire, faire un NO MOUNT d'une instance avec le minimum de paramètre en créant un nouveau pfile :
[oracle@secondary tmp]$ vi $ORACLE_HOME/dbs/initDBSEC.ora
DB_NAME=DBSEC
SHARED_POOL_SIZE=1G

[oracle@secondary dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 19 15:39:37 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1169227776 bytes
Fixed Size 2227704 bytes
Variable Size 1107296776 bytes
Database Buffers 50331648 bytes
Redo Buffers 9371648 bytes


- Sur la secondaire, il faudra probablement ajouter une entrée statique dans le listener.ora puis faire un reload du listener.
[oracle@secondary dbs]$ vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=DBSEC)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db)
(SID_NAME=DBSEC))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db)
(PROGRAM=extproc)))


- Création des standbys redo logs, autant que de membre de redo :

SQL> select GROUP#, MEMBER from v$logfile
GROUP# MEMBER
---------- ----------------------------------------
3 /u02/DBPRIM/redo03.log
2 /u02/DBPRIM/redo02.log
1 /u02/DBPRIM/redo01.log

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u03/fra/DBPRIM/onlinelog/standby01.log' SIZE 52428800;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u03/fra/DBPRIM/onlinelog/standby02.log' SIZE 52428800;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u03/fra/DBPRIM/onlinelog/standby03.log' SIZE 52428800;
Database altered.

SQL> select GROUP#, MEMBER from v$logfile;

GROUP# MEMBER
---------- ----------------------------------------
3 /u02/DBPRIM/redo03.log
2 /u02/DBPRIM/redo02.log
1 /u02/DBPRIM/redo01.log
4 /u03/fra/DBPRIM/onlinelog/standby01.log
5 /u03/fra/DBPRIM/onlinelog/standby02.log
6 /u03/fra/DBPRIM/onlinelog/standby03.log


- Créer l'arborescence sur le serveur secondaire
=> /u01/app/oracle/admin/DBSEC/adump
=> /u02/DBSEC/
=> /u03/fra/DBSEC/onlinelog/


- Créer un fichier rman avec les valeurs suivantes :
[oracle@primary tmp]$ vi /tmp/duplicate.rman
run {
allocate channel c1 type disk;
allocate auxiliary channel stby type disk;
duplicate target database
for standby
from active database
spfile
parameter_value_convert 'DBPRIM','DBSEC'
set db_unique_name='DBSEC'
set db_file_name_convert='/u02/DBPRIM/','/u02/DBSEC/'
set log_file_name_convert='/u03/fra/DBSEC/onlinelog/','/u03/fra/DBSEC/onlinelog/'
set control_files='/u02/DBSEC/control01.ctl','/u03/fra/DBSEC/control02.ctl'
set log_archive_max_processes='10'
set fal_client='DBSEC'
set fal_server='DBPRIM'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(DBPRIM,DBSEC)'
set log_archive_dest_2='service=DBPRIM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DBPRIM'
;
}


- Se connecter sur l'auxiliary via RMAN et lancer la commande duplicate :
[oracle@primary tmp]$ rman target / auxiliary sys/oracle@DBSEC

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 19 16:19:23 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: DBPRIM (DBID=636757780)
connected to auxiliary database: DBSEC (not mounted)
RMAN> @/tmp/duplicate.rman

RMAN> run {
2> allocate channel c1 type disk;
3> allocate auxiliary channel stby type disk;
4> duplicate target database
5> for standby
6> from active database
7> spfile
8> parameter_value_convert 'DBPRIM','DBSEC'
9> set db_unique_name='DBSEC'
10> set db_file_name_convert='/u02/DBPRIM/','/u02/DBSEC/'
11> set log_file_name_convert='/u03/fra/DBSEC/onlinelog/','/u03/fra/DBSEC/onlinelog/'
12> set control_files='/u02/DBSEC/control01.ctl','/u03/fra/DBSEC/control02.ctl'
13> set log_archive_max_processes='10'
14> set fal_client='DBSEC'
15> set fal_server='DBPRIM'
16> set standby_file_management='AUTO'
17> set log_archive_config='dg_config=(DBPRIM,DBSEC)'
18> set log_archive_dest_2='service=DBPRIM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DBPRIM'
19> ;
20> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=39 device type=DISK

allocated channel: stby
channel stby: SID=19 device type=DISK

Starting Duplicate Db at 19-MAR-15

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/orapwDBPRIM' auxiliary format
'/u01/app/oracle/product/11.2.0.3/db/dbs/orapwDBSEC' targetfile
'/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/spfileDBPRIM.ora' auxiliary format
'/u01/app/oracle/product/11.2.0.3/db/dbs/spfileDBSEC.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.3/db/dbs/spfileDBSEC.ora''";
}
executing Memory Script

Starting backup at 19-MAR-15
Finished backup at 19-MAR-15

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.3/db/dbs/spfileDBSEC.ora''

contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/DBSEC/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=DBSECXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''DBSEC'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/u02/DBPRIM/'', ''/u02/DBSEC/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/u03/fra/DBSEC/onlinelog/'', ''/u03/fra/DBSEC/onlinelog/'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u02/DBSEC/control01.ctl'', ''/u03/fra/DBSEC/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
10 comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''DBSEC'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''DBPRIM'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(DBPRIM,DBSEC)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=DBPRIM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DBPRIM'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/DBSEC/adump'' comment= '''' scope=spfile

sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=DBSECXDB)'' comment= '''' scope=spfile

sql statement: alter system set db_unique_name = ''DBSEC'' comment= '''' scope=spfile

sql statement: alter system set db_file_name_convert = ''/u02/DBPRIM/'', ''/u02/DBSEC/'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/u03/fra/DBSEC/onlinelog/'', ''/u03/fra/DBSEC/onlinelog/'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/u02/DBSEC/control01.ctl'', ''/u03/fra/DBSEC/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set log_archive_max_processes = 10 comment= '''' scope=spfile

sql statement: alter system set fal_client = ''DBSEC'' comment= '''' scope=spfile

sql statement: alter system set fal_server = ''DBPRIM'' comment= '''' scope=spfile

sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set log_archive_config = ''dg_config=(DBPRIM,DBSEC)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_2 = ''service=DBPRIM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DBPRIM'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 2087780352 bytes

Fixed Size 2229944 bytes
Variable Size 1677723976 bytes
Database Buffers 402653184 bytes
Redo Buffers 5173248 bytes
allocated channel: stby
channel stby: SID=19 device type=DISK

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u02/DBSEC/control01.ctl';
restore clone controlfile to '/u03/fra/DBSEC/control02.ctl' from
'/u02/DBSEC/control01.ctl';
}
executing Memory Script

Starting backup at 19-MAR-15
channel c1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/snapcf_DBPRIM.f tag=TAG20150319T162326 RECID=1 STAMP=874772607
channel c1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 19-MAR-15

Starting restore at 19-MAR-15

channel stby: copied control file copy
Finished restore at 19-MAR-15

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u02/DBSEC/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u02/DBSEC/system01.dbf";
set newname for datafile 2 to
"/u02/DBSEC/sysaux01.dbf";
set newname for datafile 3 to
"/u02/DBSEC/undotbs01.dbf";
set newname for datafile 4 to
"/u02/DBSEC/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u02/DBSEC/system01.dbf" datafile
2 auxiliary format
"/u02/DBSEC/sysaux01.dbf" datafile
3 auxiliary format
"/u02/DBSEC/undotbs01.dbf" datafile
4 auxiliary format
"/u02/DBSEC/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/DBSEC/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 19-MAR-15
channel c1: starting datafile copy
input datafile file number=00001 name=/u02/DBPRIM/system01.dbf
output file name=/u02/DBSEC/system01.dbf tag=TAG20150319T162340
channel c1: datafile copy complete, elapsed time: 00:00:45
channel c1: starting datafile copy
input datafile file number=00002 name=/u02/DBPRIM/sysaux01.dbf
output file name=/u02/DBSEC/sysaux01.dbf tag=TAG20150319T162340
channel c1: datafile copy complete, elapsed time: 00:00:35
channel c1: starting datafile copy
input datafile file number=00003 name=/u02/DBPRIM/undotbs01.dbf
output file name=/u02/DBSEC/undotbs01.dbf tag=TAG20150319T162340
channel c1: datafile copy complete, elapsed time: 00:00:15
channel c1: starting datafile copy
input datafile file number=00004 name=/u02/DBPRIM/users01.dbf
output file name=/u02/DBSEC/users01.dbf tag=TAG20150319T162340
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 19-MAR-15

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=874772710 file name=/u02/DBSEC/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=874772710 file name=/u02/DBSEC/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=874772710 file name=/u02/DBSEC/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=874772710 file name=/u02/DBSEC/users01.dbf
ORACLE error from auxiliary database: ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 19-MAR-15
released channel: c1
released channel: stby



Les warnings peuvent être ignorés, ils sont dû à la commande standby_file_management='AUTO'

- La standby est maintenant créée, nous pouvons démarrer le recover :
Pour utiliser le Real Time Apply
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.


Sans utiliser le Real Time Apply
SQL>alter database recover managed standby database disconnect;
Database altered.


- Attention à ne pas faire de ALTER DATABASE OPEN sur la secondaire, ce qui activerait l'option Active Dataguard

- Pour vérifier que la standby a correctement été créée, depuis la standby, se connecter en sql :
SQL> select protection_mode,protection_level,database_role,name from v$database;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE NAME
-------------------- -------------------- ---------------- ---------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY DBPRIM


- Pour vérifier que les données sont bien appliquées sur la standby :
Sur la primaire :

SQL> SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
MAX(SEQUENCE#)
--------------
23
SQL> alter system switch logfile;
SQL> SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
MAX(SEQUENCE#)
--------------
24


Sur la secondaire
SQL> SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence#
FROM v$archived_log
WHERE sequence# =
(SELECT MAX(sequence#) FROM v$archived_log );

LOGS TIME THREAD# SEQUENCE#
---------------- --------------------------- ---------- ----------
Last Received : 19-MAR-15:16:59:47 1 24


Notre dataguard fonctionne correctement, la prochaine étape sera de configurer le broker et de faire des tests de basculer.
A voir dans les prochains articles...

mardi 26 août 2014

Déplacer une base de donnée sur une autre partition

Ci dessous, nous allons voir comment déplacer une base sur une nouvelle partition manuellement sans passer par RMAN.


Partition saturée ? Disque physique endommagé ?

Il y a plusieurs raisons de vouloir déplacer une base de données sur une nouvelle partition. Si vous ne souhaitez déplacer que certains fichiers, vous pouvez tout aussi bien suivre cet article, car l'opération va s'effectuer en plusieurs étapes.

Nous allons déplacer la base sur la partition D:\ dans le répertoire D:\EASYDB
Ces opérations s'effectuent base arrêtée.

/!\ Toujours penser à faire un backucp full de la base + controlfiles + spfile /!\

1 - Déplacement du ou des controlfiles

  • Chercher les controlfiles :


SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string C:\oracle\EASYDB\CONTROL.DBF


  • Créer un pfile


SQL> create pfile='C:\initEASY.ora' from spfile;


File created.

  • Arrêter la base et déplacer le ou les controlfiles


SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down.

C:\Users\BSO>move C:\oracle\EASYDB\CONTROL.DBF D:\EASYDB\


Ouvrir le fichier C:\initEASY.ora, et modifier la destination du ou des controlfiles


*.control_files='D:\EASYDB\CONTROL.DBF'


  • Monter la base à partir du pfile


SQL> startup mount pfile='C:\initEASY.ora';


  • On oublie pas créer le spfile à partir du pfile :


SQL> create spfile from pfile='C:\initEASY.ora';

File created.

2 - Déplacement des logs files

  • Chercher les fichiers de logs :


SQL> select member from v$logfile;

MEMBER
--------------------------------------------

C:\RMAN\EASY\ONLINELOG\O1_MF_2_8YLTNZQ5_.LOG
C:\RMAN\EASY\ONLINELOG\O1_MF_1_8YLTNYXK_.LOG


  • En gardant la base en mount, les déplacer vers D:\EASYDB


C:\Users\BSO>move C:\RMAN\EASY\ONLINELOG\O1_MF_1_8YLTNYXK_.LOG D:\EASYDB\
1 fichier(s) déplacé(s).

C:\Users\BSO>move C:\RMAN\EASY\ONLINELOG\O1_MF_2_8YLTNZQ5_.LOG D:\EASYDB\
1 fichier(s) déplacé(s).


  • Utiliser la commande RENAME FILE sur les 2 fichiers :


ALTER DATABASE RENAME FILE 'C:\RMAN\EASY\ONLINELOG\O1_MF_1_8YLTNYXK_.LOG' TO 'D:\EASYDB\O1_MF_1_8YLTNYXK_.LOG';
ALTER DATABASE RENAME FILE 'C:\RMAN\EASY\ONLINELOG\O1_MF_2_8YLTNZQ5_.LOG' TO 'D:\EASYDB\O1_MF_2_8YLTNZQ5_.LOG';


  • Vérification :


SQL> select member from v$logfile;

MEMBER
----------------------------------

D:\EASYDB\O1_MF_2_8YLTNZQ5_.LOG
D:\EASYDB\O1_MF_1_8YLTNYXK_.LOG

3 - Déplacement des datafiles

L'opération est identique à l'opération précédente

  • Chercher l'emplacement de tous les fichiers :


SQL> SELECT name FROM v$datafile;

NAME
------------------------------------------------

C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSTEM.DBF
C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\UNDOTBS1.DBF
C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSAUX.DBF
C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\USERS.DBF

SQL> select FILE_NAME from dba_temp_files;

FILE_NAME
--------------------------------------------

C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\TEMP.DBF


  • Déplacer les fichiers après avoir arrêté la base


move C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSTEM.DBF D:\EASYDB\SYSTEM.DBF;
move C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\UNDOTBS1.DBF D:\EASYDB\UNDOTBS1.DBF;
move C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSAUX.DBF D:\EASYDB\SYSAUX.DBF;
move C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\USERS.DBF D:\EASYDB\USERS.DBF;
move C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\TEMP.DBF D:\EASYDB\TEMP.DBF;


  • Monter la base et "renommer" les fichiers :



SQL> startup mount;
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size 1388352 bytes
Variable Size 666894528 bytes
Database Buffers 398458880 bytes
Redo Buffers 4591616 bytes
Database mounted.

SQL> alter database rename file 'C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSTEM.DBF' to 'D:\EASYDB\SYSTEM.DBF';
Database altered.

SQL> alter database rename file 'C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\UNDOTBS1.DBF' to 'D:\EASYDB\UNDOTBS1.DBF';
Database altered.

SQL> alter database rename file 'C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\SYSAUX.DBF' to 'D:\EASYDB\SYSAUX.DBF';
Database altered.

SQL> alter database rename file 'C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\USERS.DBF'to 'D:\EASYDB\USERS.DBF';
Database altered.

SQL> alter database rename file 'C:\ORACLEXE\APP\ORACLE\ORADATA\EASY\TEMP.DBF'to 'D:\EASYDB\TEMP.DBF';
Database altered.


  • Ouvrir la base et vérifier la destination des fichiers :


SQL> alter database open;
Database altered.

SQL> SELECT name FROM v$datafile;

NAME
---------------------------------

D:\EASYDB\SYSTEM.DBF
D:\EASYDB\UNDOTBS1.DBF
D:\EASYDB\SYSAUX.DBF
D:\EASYDB\USERS.DBF

SQL> select FILE_NAME from dba_temp_files;

FILE_NAME
--------------------------------------------
D:\EASYDB\TEMP.DBF

mardi 27 mai 2014

Activer le mode Archive Log dans un environnement RAC

L'activation du mode Archivelog est assez simple et rapide sur une base standalone, mais sur un environnement RAC, il y a quelques manipulations supplémentaires.
Cette procédure fonctionne pour un RAC 10 ou 11g, avec un ou plusieurs noeuds.
Le but de l'opération est de désactiver le mode Cluster, Arrêter la base, activer le mode archivelog et redémarrer le tout.
  • Sur le noeud1, désactiver le mode cluster en modifiant le paramètre "cluster_database"
$ sqlplus / as sysdba
SQL> alter system set cluster_database=FALSE scope=spfile sid='DBRAC1';
  • Arrêter toutes les instances de la base
$ srvctl stop database -d DBRAC
  • Monter l'instance un et activer l'archivage
$ export ORACLE_SID = RACDB1
$ sqlplus "/ as sysdba"
SQL> startup mount
SQL> alter database archivelog
  • Ré activer le mode cluster sur l'instance
SQL> alter system set cluster_database=true scope=spfile sid='DBRAC1';
  • Arrêter l'instance
SQL> shutdown immediate
  • Redémarrer toutes les instances de la base
$ srvctl start database -d DBRAC
  • Redémarrer toutes les services (si besoin)
$ srvctl start service -d DBRAC
  • Vérifier si l'archivage est activé
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 65
Next log sequence to archive 66
Current log sequence 66

vendredi 14 mars 2014

Libérer de l'espace dans les fichiers de données Oracle

Les fichiers de bases de données Oracle s'agrandissent automatiquement tant que la taille maximale n'a pas été atteinte (et si l'auto extension en activée).
Cependant, ce n'est pas la même chose dans l'autre sens. En effet, si vous supprimez des données, la taille sur le disque physique restera inchangée.

Voici un jeu de test qui présente ce scénario, et qui va permettre de récupérer l'espace "perdue" lors de la suppression de données :
(Pré requis pour le test : le tablespace est  en ASSM et non en MANUAL (sinon le shrink n'est pas possible) être en version 10 ou supérieure)

Création d'un tablespace, d'un utilisateur et d'une table.
create tablespace TBS_TEST_SIZE datafile 'C:\TBS_TEST_SIZE.dbf' size 1M autoextend on maxsize 10G SEGMENT SPACE MANAGEMENT AUTO;
create user MY_USER identified by "ORACLE" default tablespace TBS_TEST_SIZE;
grant connect to MY_USER;
grant resource to MY_USER;
grant unlimited tablespace to MY_USER;
create table MY_USER.TEST_TABLE_SIZE (col1 VARCHAR2(250), col2 NUMBER);

Vérification de l’espace occupé dans le tablespace avant génération des données :
SELECT dbasegment.segment_libre AS "Espace Utilise en Mo",
   (SELECT ROUND(SUM(bytes)/1024/1024) Mo
      FROM dba_free_space
      WHERE tablespace_name = 'TBS_TEST_SIZE'
   ) AS "Espace libre en Mo"
FROM
   (SELECT ROUND(SUM(bytes)/1024/1024) segment_libre
      FROM dba_segments
      WHERE tablespace_name = 'TBS_TEST_SIZE'
   ) dbasegment;

Espace Utilise en Mo   Espace libre en Mo 
---------------------- ---------------------- 
0                      1

Insertion de près de 25Mo de données physique :
DECLARE
   compteur NUMBER := 0;
BEGIN
  WHILE compteur > 1000000 LOOP
     compteur:=compteur+1;
     insert into MY_USER.TEST_TABLE_SIZE values ('Jeu de test',compteur);
  END LOOP;
COMMIT;
END;
/

Vérification de l’espace occupé dans le tablespace après génération des données
SELECT dbasegment.segment_libre AS "Espace Utilise en Mo",
   (SELECT ROUND(SUM(bytes)/1024/1024) Mo
      FROM dba_free_space
      WHERE tablespace_name = 'TBS_TEST_SIZE'
   ) AS "Espace libre en Mo"
FROM
   (SELECT ROUND(SUM(bytes)/1024/1024) segment_libre
      FROM dba_segments
      WHERE tablespace_name = 'TBS_TEST_SIZE'
   ) dbasegment;
Espace Utilise en Mo   Espace libre en Mo 
---------------------- ---------------------- 
26                     2

Taille sur le disque :
C:\Users\BSO>dir C:\TBS_TEST_SIZE.DBF
14/03/2014 12:09 28 385 280 TBS_TEST_SIZE.DBF
==> 27 Mo

Maintenant, vidons la table complètement
(Une table qui a subit un cycle de vie normal (suppression modification ajout...) peut aussi bien faire l'affaire).
Pour ça, nous avons 2 méthodes, un DELETE ou un TRUNCATE.

  • Si vous utilisez un TRUNCATE (et que vous êtes en 11.2), vous pouvez directement retailler le fichier de données.

  • Si vous utilisez un DELETE, il faudra alors effectuer un SHRINK de la table.

En effet, le TRUNCATE en 11.2 a évolué, il récupère automatiquement l'espace libre dans le tablespace. Alors que le DELETE ne récupère pas cet espace directement.
Démonstration :

  • Truncate
TRUNCATE TABLE MY_USER.TEST_TABLE_SIZE;
table MY_USER.TEST_TABLE_SIZE vidé(e).
Espace Utilise en Mo   Espace libre en Mo 
---------------------- ---------------------- 
0                      28

  • Delete
DELETE FROM MY_USER.TEST_TABLE_SIZE;
COMMIT;
Espace Utilise en Mo    Espace libre en Mo 
---------------------- ---------------------- 
26                      2

Dans le cas du DELETE il faudra effectuer un SHRINK de la table.
Le SHRINK va générer du trafic sur la base et s'il s'agit du grosse table, ça risque de prendre du temps (ce n'est pas vraiment une référence, mais pour info, sur ma XE pour récupérer 26Mo, il aura fallu moins de 5 secondes).
alter table MY_USER.TEST_TABLE_SIZE enable row movement;
alter table MY_USER.TEST_TABLE_SIZE shrink space;
alter table MY_USER.TEST_TABLE_SIZE disable row movement;
Espace Utilise en Mo   Espace libre en Mo 
---------------------- ---------------------- 
0                      28

/!\ Attention /!\
Avant d'utiliser le SHRINK, vérifier certains "pre requis" sur la doc Oracle concernant l'utilisation du shrink : http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#i2192484

Dès que vous avez de l'espace libre dans un fichier de données, vous pourrez redimensionner le fichier.
Vous pouvez utiliser la requete ci dessous pour connaitre la taille minimale que vous pouvez attribuer à votre fichier de données :
set serveroutput on; 
DECLARE
   v_stmt VARCHAR2(500);
   tbs VARCHAR2(50) := 'MON_TABLESPACE';
   CURSOR c1 IS
      SELECT file_id FROM dba_data_files WHERE tablespace_name=tbs; 
BEGIN 
  FOR line IN c1 LOOP SELECT 'ALTER DATABASE DATAFILE ' || '''' ||
    D.file_name || '''' || ' RESIZE ' || 
    NVL(CEIL(D.bytes/1024/1024 - take_back.take_back_mb), D.bytes/1024/1024) || 'M;' SQL 
    INTO v_stmt FROM dba_data_files D, (SELECT SUM (BYTES) / 1024 / 1024 take_back_mb
    FROM dba_free_space
    WHERE tablespace_name = tbs
    AND file_id = line.file_id
    AND block_id <=
    NVL ((SELECT (block_id + (BYTES / 32768))
    FROM dba_extents
    WHERE block_id =
    (SELECT MAX (block_id)
      FROM dba_extents
      WHERE file_id = line.file_id
      AND tablespace_name = tbs)
      AND file_id = line.file_id
      AND tablespace_name = tbs),
     0
    )) take_back 
    WHERE D.file_id =line.file_id; DBMS_OUTPUT.PUT_LINE(v_stmt); 
 END LOOP; 
END; 
/
bloc anonyme terminé
ALTER DATABASE DATAFILE 'C:\TBS_TEST_SIZE.DBF' RESIZE 1M;

Il n'y a plus qu'à exécuter le ALTER DATABASE :
ALTER DATABASE DATAFILE 'C:\TBS_TEST_SIZE.DBF' RESIZE 1M;
database datafile 'C:\TBS_TEST_SIZE.DBF' modifié(e).
Espace Utilise en Mo   Espace libre en Mo 
---------------------- ---------------------- 
0                      1

Vérification de la taille du fichier de données sur le disque :
C:\Users\BSO>dir C:\TBS_TEST_SIZE.DBF
14/03/2014 13:06 1 056 768 TBS_TEST_SIZE.DBF
==> 1M

Mais comment peut on savoir quel objet nécessite un shrink ?
En utilisant une requête de ce type :
set serveroutput on
declare
 unf number;
 unfb number;
 fs1 number;
 fs1b number;
 fs2 number;
 fs2b number;
 fs3 number;
 fs3b number;
 fs4 number;
 fs4b number;
 full number;
 fullb number;
begin
   dbms_space.space_usage('MY_USER','TEST_TABLE_SIZE','TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
   dbms_output.put_line('Nombre total de blocks non formates: '||unf);
   dbms_output.put_line('Nombre de blocks qui ont entre 0 et 25% d espace libre: '||fs1);
   dbms_output.put_line('Nombre de blocks qui ont entre 25 to 50% d espace libre: '||fs2);
   dbms_output.put_line('Nombre de blocks qui ont entre 50 to 75% d espace libre: '||fs3);
   dbms_output.put_line('Nombre de blocks qui ont entre 75 to 100% d espace libre: '||fs4);
   dbms_output.put_line('Nombre de blocks plein dans le segment : '||full);
end;
/

En ajoutant un curseur, on peut par exemple faire une boucle pour interroger tous les objets d'un schéma / d'un tablespace etc...