Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

Oracle12C,19C

  • [ORACLE] ASM

    clean_asm_disks.sh

    #!/bin/bash

    # =================================================================

    # clean_asm_disks.sh → À exécuter en oracle AVANT tout delete Azure

    # Auteur : toi + moi

    # Objectif : garantir à 100 % que tu peux supprimer un disque sans crasher la base

    # =================================================================

     

    export ORACLE_SID=+ASM1 # ou +ASM2 sur l'autre nœud RAC

    export ORAENV_ASK=NO

    . oraenv > /dev/null 2>&1

     

    echo "================================================================="

    echo "VÉRIFICATION DE SÉCURITÉ ASM - $(date)"

    echo "Instance : ( ORACLE_SID - Host : )(hostname)"

    echo "================================================================="

     

    sqlplus -S / as sysasm <<EOF

     

    SET PAGESIZE 200 LINESIZE 200

    COL dg_name FORMAT A12

    COL disk_path FORMAT A50

    COL header_status FORMAT A12

    COL mount_status FORMAT A12

    COL mode_status FORMAT A12

    COL state FORMAT A10

    COL failgroup FORMAT A20

     

    PROMPT 

    PROMPT 1. État des diskgroups

    SELECT name, state, type FROM v$asm_diskgroup;

     

    PROMPT 

    PROMPT 2. Opérations de rebalance en cours ou en erreur

    SELECT group_number, operation, state, power, est_minutes FROM v$asm_operation;

    SELECT * FROM gv$asm_operation WHERE state!='DONE';

     

    PROMPT 

    PROMPT 3. Disques à risque (FORMER mais rebalance pas fini, ou MISSING)

    SELECT group_number,

           name,

           path disk_path,

           header_status,

           mount_status,

           mode_status,

           state,

           failgroup

    FROM v$asm_disk

    WHERE header_status IN ('FORMER','MISSING')

       OR (header_status='MEMBER' AND mode_status!='ONLINE')

    ORDER BY header_status, group_number;

     

    PROMPT 

    PROMPT 4. Disques vraiment supprimables en toute sécurité (rebalance 100% terminé)

    PROMPT → HEADER_STATUS = CANDIDATE ou PROVISIONED uniquement

    SELECT 'SUPPRIMABLE EN TOUTE SÉCURITÉ' statut,

           path disk_path,

           header_status,

           failgroup

    FROM v$asm_disk

    WHERE header_status IN ('CANDIDATE','PROVISIONED')

    UNION ALL

    SELECT 'ATTENTION - encore dans le diskgroup' statut,

           path, header_status, failgroup

    FROM v$asm_disk

    WHERE header_status = 'FORMER';

     

    PROMPT 

    PROMPT ================================================================

    PROMPT RÈGLE D’OR :

    PROMPT Seuls les disques en CANDIDATE ou PROVISIONED peuvent être

    PROMPT détachés/supprimés dans Azure sans aucun risque.

    PROMPT Si tu vois du FORMER → attends la fin du rebalance ou force le DROP DISK

    PROMPT ================================================================

    EOF

     

    echo

    echo "Fin du check : $(date)"

    echo "Tu peux supprimer dans Azure UNIQUEMENT les disques marqués SUPPRIMABLE EN TOUTE SÉCURITÉ"

    echo

     

    safe_clean_asm_disks.sh

    #!/bin/bash

    # ================================================================

    # safe_clean_asm_disks.sh → Version AUTO (nettoyage intelligent)

    # Exécuter en user oracle sur un nœud où +ASM est vivant

    # ================================================================

     

    export ORACLE_SID=+ASM1 # change en +ASM2 si tu es sur le nœud 2

    export ORAENV_ASK=NO

    . oraenv > /dev/null 2>&1

     

    LOG=/tmp/safe_clean_asm_$(date +%Y%m%d_%H%M%S).log

    exec > $LOG 2>&1

     

    echo "============================================================"

    echo "SAFE CLEAN ASM DISKS - Démarrage $(date)"

    echo "Instance : ( ORACLE_SID - Host : )(hostname)"

    echo "============================================================"

     

    # 1. Vérification préalable

    sqlplus -S / as sysasm <<EOF

    SET PAGESIZE 0 FEEDBACK OFF HEADING OFF

    SELECT COUNT(*) FROM v$asm_operation WHERE state IN ('RUN','WAIT');

    EXIT

    EOF

    REBAL_IN_PROGRESS=$(sqlplus -S / as sysasm <<EOF

    SET PAGESIZE 0 FEEDBACK OFF HEADING OFF

    SELECT COUNT(*) FROM v$asm_operation WHERE state IN ('RUN','WAIT');

    EXIT

    EOF

    )

     

    if [ "$REBAL_IN_PROGRESS" -gt 0 ]; then

        echo "ERREUR : Un rebalance est encore en cours !"

        echo " → Attends qu'il soit terminé avant de relancer ce script."

        exit 1

    fi

     

    # 2. Liste des disques FORMER (ceux qu’on peut nettoyer proprement)

    FORMER_DISKS=$(sqlplus -S / as sysasm <<EOF

    SET PAGESIZE 0 FEEDBACK OFF HEADING OFF

    SELECT name

    FROM v$asm_disk

    WHERE header_status = 'FORMER';

    EXIT

    EOF

    )

     

    if [ -z "$FORMER_DISKS" ]; then

        echo "Aucun disque en statut FORMER → Rien à nettoyer dans ASM."

        echo "Tu peux supprimer directement dans Azure les disques CANDIDATE/PROVISIONED si tu veux."

        echo "Fin du script."

        exit 0

    fi

     

    echo "Disques en statut FORMER détectés (on va les DROP proprement) :"

    echo "$FORMER_DISKS"

     

    # 3. DROP automatique par diskgroup avec rebalance en parallèle

    echo

    echo "Lancement du DROP DISK + rebalance (power 8 pour aller vite)..."

     

    sqlplus -S / as sysasm <<EOF

    WHENEVER SQLERROR EXIT FAILURE

    SET FEEDBACK ON ECHO ON

     

    -- On regroupe par diskgroup pour faire un seul ALTER par DG

    SELECT 'ALTER DISKGROUP ' || dg.name || ' DROP DISK ' ||

           LISTAGG(d.name, ',') WITHIN GROUP (ORDER BY d.name) || 

           ' REBALANCE POWER 8;'

    FROM v$asm_disk d

    JOIN v$asm_diskgroup dg ON d.group_number = dg.group_number

    WHERE d.header_status = 'FORMER'

    GROUP BY dg.name;

     

    -- Exécution réelle

    DECLARE

      v_sql VARCHAR2(4000);

    BEGIN

      FOR rec IN (

        SELECT dg.name dg_name,

               LISTAGG(d.name, ''',''') WITHIN GROUP (ORDER BY d.name) disk_list

        FROM v$asm_disk d

        JOIN v$asm_diskgroup dg ON d.group_number = dg.group_number

        WHERE d.header_status = 'FORMER'

        GROUP BY dg.name

      ) LOOP

        v_sql := 'ALTER DISKGROUP ' || rec.dg_name || 

                 ' DROP DISK ''' || rec.disk_list || ''' REBALANCE POWER 8';

        EXECUTE IMMEDIATE v_sql;

        DBMS_OUTPUT.PUT_LINE('Exécuté : ' || v_sql);

      END LOOP;

    END;

    /

     

    SET FEEDBACK OFF

    PROMPT

    PROMPT Nettoyage terminé.

    PROMPT Les disques viennent de passer en CANDIDATE/PROVISIONED.

    PROMPT Tu peux maintenant les détacher/supprimer dans Azure en toute sécurité.

    PROMPT

    EOF

     

    echo

    echo "============================================================"

    echo "Tout est propre dans ASM !"

    echo "Tu peux maintenant supprimer les disques dans Azure sans aucun risque."

    echo "Log complet : $LOG"

    echo "============================================================"

  • Oracle backup batch file

    Oracle backup batch file - Stack Overflow

    need to split in two files: a batch file and RMAN script

    Batch file: backup.bat

    @echo off
echo Automatic Backup Oracle
pushd .
SET ORACLE_HOME=C:apporacleproduct12.2.0dbhome_1
SET ORACLE_SID=ORCL
echo ----------------------------------------------------
echo ORACLE_HOME : %ORACLE_HOME%
echo ORACLE_SID  : %ORACLE_SID%
echo ----------------------------------------------------
RMAN TARGET / @file.rman log=backup.log


    Script RMAN called file.rman or another name

    run {
backup database;
backup archivelog all delete input;
}

    This part is setup:

    CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:backup%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'c:backup%d_%T_%u';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'c:backupSNCFORCL.ORA';

    Can be removed from the script and run once for each bank. Once configured, the settings are permanent.

    script need two files: backup.bat and file.rman

  • Résoudre l'ORA-600 doc id 2523249.1

    Optimizer expression statistics monitoring is introduced in 12.2 database version.

     For specific applications, this might end up in growing repository segments. Monitoring expression statistics is introduced for a new optimizer feature in 12.2 to collect expression usage statistics in SQL queries.

    Monitoring feature is controlled by parameter "_column_tracking_level". High growth have been noticed when monitoring expression statistics is active, enabled by default.

     

     

    Solution:

    Disable future monitoring:

    alter system set "_column_tracking_level"=17 scope=both;

    --wait 10 minutes

     

    To purge data from tables:

    exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

    truncate table sys.exp_head$ drop storage;

    truncate table sys.exp_obj$ drop storage;

    truncate table sys.exp_stat$ drop storage;

    alter index SYS.I_EXP_HEAD$ rebuild tablespace sysaux online;

    alter index SYS.I_EXP_STAT$ rebuild tablespace sysaux online;

    alter index SYS.I_EXP_OBJ$ rebuild tablespace sysaux online;

     

    To re-enable expression statistics monitoring later :

    alter system set "_column_tracking_level"=21 scope=both;

  • [ORACLE]Evaluer une base de données

    Il peut être utile d'évaluer une base de données avant de penser à la migrer dans un autre SGBD.

    On va alors s'intéresser aux types d'objets qu'elle contient pour valider ou invalider la migration dans tel ou tel SGBDR.

    Exemple le plus probant : une migration d'une base oracle dans une base postgreSQL.

    La requête suivante générique et passe partout pourra aider à faire l'évaluation.

    select (select name from v$database) as database_name, owner,sum(bytes)/1024/1024 as "Number", 'Size in MB' as object_type
    from dba_segments 
    where owner not in ('WMSYS','APPQOSSYS','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','ORACLE_OCM','OUTLN','PUBLIC','REMOTE_SCHEDULER_AGENT','XDB','SYS','SYSTEM')              
    group by owner              
    union
    select (select name from v$database) as database_name,
    col.owner as schema_name, count(distinct col.table_name),
    'Lobs' as object_type
    from dba_tab_columns col 
    inner join dba_tables t on col.owner = t.owner and col.table_name = t.table_name 
    where col.data_type in ('BLOB', 'CLOB', 'NCLOB', 'BFILE')
    and col.owner not in ('OAUTHDBSCHEMA','AUDSYS','GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC') 
    group by col.owner 
    union    
    SELECT 
    (select name from v$database) as database_name,
    TABLE_OWNER,count(distinct table_name) as partitionned_tables,'Partitions'              
    FROM   dba_tab_partitions                                    
    WHERE TABLE_OWNER not in('WMSYS','SYS','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')                    
    group BY TABLE_OWNER
    union    
    select (select name from v$database) as database_name,
    owner,
    count(distinct object_name),
    'Packages' as object_type 
    from dba_objects where object_type='PACKAGE' and owner not in ('WMSYS','SYS','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')               
    group by owner
    union
    select (select name from v$database) as database_name,
    owner,
    count(distinct object_name),
    'Procedures' as object_type 
    from dba_objects where object_type='PROCEDURE' 
    and owner not in ('WMSYS','SYS','OUTLN','REMOTE_SCHEDULER_AGENT','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')
    group by owner
    union
    select (select name from v$database) as database_name,
    owner,
    count(distinct object_name) , 'Functions'
    from dba_objects 
    where object_type='FUNCTION' 
    and owner not in ('WMSYS','SYS','OUTLN','REMOTE_SCHEDULER_AGENT','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM') 
    group by owner
    union
    select (select name from v$database) as database_name,
    owner ,
    count(distinct trigger_name),'Triggers' as object_type  
    from dba_triggers
    where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
    group by owner
    union
    select (select name from v$database) as database_name,
    owner,count(distinct table_name),'Global Temporary Tables' 
    from dba_tables 
    where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
       and TEMPORARY = 'Y'
       group by owner
    union 
    select (select name from v$database) as database_name, OWNER,count(distinct TABLE_NAME) ,'External Tables' 
     from dba_external_tables 
    where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
       group by owner
    union
    SELECT (select name from v$database) as database_name,schema,count(distinct namespace),'Context' as object_type
      FROM dba_context
      where schema not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','OCOR_OWN')
       group by schema;