archivelog/noarchivelog ?

Le mode archivelog est obligatoire pour les sauvegardes a chaud (sans arrêter la base)

SELECT LOG_MODE FROM SYS.V$DATABASE;

Passage archivelog/noarchivelog

sqlplus / as sysdba
select host_name, instance_name from v$instance;
archive log list;
shutdown immediate;
startup mount	
-- pour passer la base en archivelog
alter database archivelog;
-- pour passer la base en noarchivelog
--alter database noarchivelog;
alter database open;
archive log list;
exit

Archivelogs trop petits ?

Compte le temps entre deux générations d’archive logs afin de vérifier si on a bien au moins 20 minutes.

SELECT TO_CHAR(FIRST_TIME,’YYMMDD HH24:MI:SS’) « Debut »,

TO_CHAR(NEXT_TIME,’YYMMDD HH24:MI:SS’) « Fin »,

TO_CHAR(((NEXT_TIME – FIRST_TIME)*60* 24),’99’) « Minute »

FROM  v$archived_log

WHERE (NEXT_TIME – FIRST_TIME) * 60 * 24 < 19

ORDER BY 3 DESC;

 

Si vous n’avez pas 20 minutes entres deux archives logs, il faut augmenter leur taille.

Quelques selects

set linesize 132
set pagesize 132
column name for a40
prompt "liste des archivelogs par date d'archivage"
SELECT name "Name",
       sequence#                                   "Sequence",
       thread#                                     "Thread",
       backup_count                                "Backup Count" ,
       first_change#                               "First Change#",
       to_char(first_time, 'YYYYMMDD:HH24MI')      "First Time",
       to_char(completion_time, 'YYYYMMDD:HH24MI') "Archival Time"
FROM   v$archived_log
WHERE  deleted='NO'
order by  completion_time
;

prompt "liste des archivelogs et de leur taille en Go par date d'archivage"
select 	name "Name", 
	BLOCKS*BLOCK_SIZE/1024/1024/1024  "Taille Go"    
FROM   v$archived_log
WHERE  deleted='NO'
order by  completion_time
;


prompt "somme des tailles des archivelogs en Go par jours"
select 	to_char(completion_time, 'YYYYMMDD'), 
	sum(BLOCKS*BLOCK_SIZE/1024/1024/1024)  "Taille Go"    
FROM   v$archived_log
WHERE  deleted='NO'
group by  to_char(completion_time, 'YYYYMMDD') 
;

SELECT name "Name",sequence# "Sequence", thread# "Thread", backup_count "Backup Count",
   first_change# "First Change#", first_time "First Time", completion_time "Archival Time" FROM
   v$archived_log
order by completion_time;



SELECT COUNT(*), TRUNC(completion_time) 
FROM   v$archived_log
GROUP  BY TRUNC(completion_time)
ORDER  BY 2 DESC
;
  

et la compression: 
#
## Compression des archivelogs
#
Compression()
{
typeset -i N=`find . -name "*log" -mtime +${NbJours} -exec ls -l {} \;|wc -l|awk '{print $1}'`
echo "Compression en cours de $N fichiers ..."
find . -name "*log" -mtime +${NbJours} -exec gzip {} \;
if [ $? -ne 0 ];then
   echo "Impossible de compresser les $N fichiers archivelog (repertoire=${Repertoire})!"
   return 8
fi
}

Metre la base en archivelog : 
arret de la base :

Pour modifier le log_archive_dest_1 
sqlplus /nolog
connect / as sysdba;
CREATE PFILE='/apps/oracle/product/db/10.2.0/dbs/initaabqxx01.ora' FROM SPFILE; 
exit
sudo vi /apps/oracle/product/db/10.2.0/dbs/pfileaabqxx01.ora
*.log_archive_dest_1='LOCATION=/data/oracle/archivelogs/aabqxx01'
*.log_archive_format='arch_aabqxx01_%t_%s_%r.dbf'

connect / as sysdba;
shutdown immediate
startup PFILE='/apps/oracle/product/db/10.2.0/dbs/pfileaabqxx01.ora' mount;
alter database archivelog;
alter database open;
create create spfile from pfile;
shutdown immediate;
startup;
show parameter pfile
show parameter archive
ALTER SYSTEM SWITCH LOGFILE;
ho ls -tlr /data/oracle/archivelogs/aabqxx01
total 67392
-rw-r-----    1 uqaora0  gqaora0    34501120 Nov  8 14:22 arch_aabqxx01_1_662_698325944.dbf