Pourquoi OEM "cherche" le SPFILE dans ASM

Pourquoi OEM "cherche" le SPFILE dans ASM :

[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;

[SYSTEM]

This script uses the local tnsnames.ora file to create a connection, and requires the variable ${p} to hold the current SYSTEM password.

[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;

 

[ORACLE] Preupgrade fixups

fixup_b4_mig.pdf

Running Health Checks Manually

Managing Diagnostic Data (oracle.com)

Installation Oracle19c

Installation Oracle 19c

[Oracle]Top shell script pour monitoring BDD

Top DBA Shell Scripts for Monitoring the Database – BMC Software | Blogs