Friday, August 15, 2008

Copy archivelogs from ASM to OS

# To copy archivelogs from ASM to OS using rman :-

rman
connect target /
backup as copy archivelog from logseq=78 until logseq=78 format ‘/tmp/%t%r%s%p’;

# To copy archivelogs from ASM to OS using DBMS_FILE_TRANSFER (can copy to other servers using database link) :-

CREATE DIRECTORY archdir AS '+DG1/orcl/arch';
CREATE DIRECTORY tempdir AS '/archivelog';
set serverout on
DECLARE
v_archivedir VARCHAR2(30) := 'ARCHDIR';
v_tempdir VARCHAR2(30) := 'TEMPDIR';
v_asm_logname VARCHAR2(100);
v_unix_logname VARCHAR2(100);
v_first_log_seq NUMBER := 12;
v_last_log_seq NUMBER := 14;
v_log_seq VARCHAR2(5);
CURSOR c_logs IS
SELECT name
FROM v$archived_log
WHERE sequence# BETWEEN v_first_log_seq AND v_last_log_seq
ORDER BY sequence#;
BEGIN
FOR i IN c_logs LOOP
v_asm_logname := SUBSTR(i.name, 16);
v_log_seq := SUBSTR(v_asm_logname,4,5);
v_unix_logname := 'orcl_arc'||v_log_seq||'.log';
DBMS_FILE_TRANSFER.COPY_FILE(v_archivedir,
v_asm_logname,
v_tempdir,
v_unix_logname);
DBMS_OUTPUT.PUT_LINE(v_asm_logname||' copied to '||v_unix_logname||'.');
END LOOP;
END;
/