Archive Logs Generated in a Day

This is just one of those questions I get asked from customers, not regularly, but frequently, however, not frequently enough to remember where I put the script………

SELECT trunc(completion_time) TIME, sum(blocks * block_size)/1024/1024/1024 SIZE_GB
FROM v$archived_log
GROUP BY trunc (completion_time)
ORDER BY 1;

Move datafile script (unix or windows)

Remember to change the disk name.

ARCHIVE LOGGING (UNIX):

set linesize 150
column file_name format a60
set pagesize 200
set head off

select file_id, file_name
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘alter database datafile ‘||file_id||’ offline;’
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘!mv ‘||file_name||’ ‘||file_name
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘alter database rename file ”’||file_name||”’ to ”’||file_name||”’;’
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘recover datafile ‘||file_id||’;’
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘alter database datafile ‘||file_id||’ online;’
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘select status from dba_data_files;’ from dual;

NO ARCHIVE LOGGING (UNIX):

set linesize 150
column file_name format a60
set pagesize 200
set head off

select ‘shutdown immediate’ from dual;
select file_id, file_name
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘!mv ‘||file_name||’ ‘||file_name
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘startup mount’ from dual;
select ‘alter database rename file ”’||file_name||”’ to ”’||file_name||”’;’
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘alter database open;’ from dual;
select ‘select status from dba_data_files;’ from dual;

ARCHIVE LOGGING (WINDOWS):

set linesize 150
column file_name format a60
set pagesize 200
set head off

select file_id, file_name
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘alter database datafile ‘||file_id||’ offline;’
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘host move ‘||file_name||’ ‘||file_name
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘alter database rename file ”’||file_name||”’ to ”’||file_name||”’;’
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘recover datafile ‘||file_id||’;’
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘alter database datafile ‘||file_id||’ online;’
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘select status from dba_data_files;’ from dual;

NO ARCHIVE LOGGING (WINDOWS):

set linesize 150
column file_name format a60
set pagesize 200
set head off

select ‘shutdown immediate’ from dual;
select file_id, file_name
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘host move ‘||file_name||’ ‘||file_name
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘startup mount’ from dual;
select ‘alter database rename file ”’||file_name||”’ to ”’||file_name||”’;’
from dba_data_files
where file_name like ‘%disk8%’
/
select ‘alter database open;’ from dual;
select ‘select status from dba_data_files;’ from dual;