Blogroll

Get a website with Godaddy


Logo designer Fiverr Frelancer

Friday, March 27, 2015

Oracle - how to delete RAC database backup set

Hi, If you used Enterprise manager database console to do the backup and wanted to know how to remove existing backup sets..Pls refer below steps :

1.open sqlplus :  sqlplus sys/password@ORCL as sysdba;
2. Run below script to spool the output into html file

spool full_path_alias_directory.html
-- ASM Versions 10.1, 10.2, 11.1  & 11.2
SET MARKUP HTML ON
set echo on

set pagesize 200

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " "  from dual;


select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';

SELECT CONCAT('+'||GNAME, SYS_CONNECT_BY_PATH(ANAME, '/'))
 FULL_PATH, SYSTEM_CREATED, ALIAS_DIRECTORY, FILE_TYPE
 FROM ( SELECT B.NAME GNAME, A.PARENT_INDEX PINDEX,
 A.NAME ANAME, A.REFERENCE_INDEX RINDEX,
 A.SYSTEM_CREATED, A.ALIAS_DIRECTORY,
 C.TYPE FILE_TYPE
 FROM V$ASM_ALIAS A, V$ASM_DISKGROUP B, V$ASM_FILE C
 WHERE A.GROUP_NUMBER = B.GROUP_NUMBER
 AND A.GROUP_NUMBER = C.GROUP_NUMBER(+)
 AND A.FILE_NUMBER = C.FILE_NUMBER(+)
 AND A.FILE_INCARNATION = C.INCARNATION(+)
 )
 START WITH (MOD(PINDEX, POWER(2, 24))) = 0
 CONNECT BY PRIOR RINDEX = PINDEX;


spool off

exit

--------------------- end of script -----------------------------


3. Once finished, open the html and check the full path of the backup set made
This html is to show all existing backup sets and file type used in ASM storage..
Eg..  

FULL PATH 
+ARCH/ORCL/BACKUPSET/2015_01_11/nnndf0_BACKUP_ORCL_000021_0111150830_0.458.86873 9507
FILE TYPE 
BACKUPSET


4. And then issue this sql as :

alter diskgroup ARCH drop file '+ARCH/ORCL/BACKUPSET/2015_01_11/nnndf0_BACKUP_ORCL_000021_0111150830_0.458.868739507'; 

5. If success, it would show message "Altered "

Hope that helps....

Cheers,


No comments:

Post a Comment

Amazon store

alidropship plugin

alidropship plugin
alidropship plugin

Total Pageviews

About