ASM shell script add file

Oracle ASM add file ..

#!/usr/bin/ksh
clear
(
export ORACLE_SID=XYZ

print "Adding datafile to the tablespace\n"
sqlplus -s / as sysdba <<+
WHENEVER SQLERROR EXIT 1;
WHENEVER OSERROR EXIT 1;
ALTER TABLESPACE "ANUJ" ADD DATAFILE '+DATA' SIZE 2g AUTOEXTEND on;
+
)

=======

   alter tablespace xxxx add datafile '+DATA' SIZE 2g AUTOEXTEND on;

alter tablespace xxxx add datafile '+DATA' size 1024m autoextend on maxsize unlimited;

set serveroutput on linesize 300

declare 

v_stmt2 varchar2(600);

begin 

FOR i IN 2..1023 loop

v_stmt2:= 'ALTER TABLESPACE ' ||'TX_DATA'|| ' ADD DATAFILE ' || '''+DATA'''||  ' SIZE 1m AUTOEXTEND on' ;

--dbms_output.put_line(v_stmt2);

execute immediate v_stmt2;

end loop;

end ;

/

ERROR at line 1:

ORA-01686: max # files (1023) reached for the tablespace TX_DATA

ORA-06512: at line 9

=====

alter tablespace TEST_DATA add datafile '+DATA/irac/test_data02.dbf' size 1G;

Tablespace altered.




datafile backup 

run {
        backup as compressed backupset
        incremental level 0
        format '/u01/irac/df_%T_@_%s_%p_%t'
        datafile 16
        include current controlfile
        ;
}

 alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
col unrecoverable_time for a25
col NAME for a70
select NAME,file#, unrecoverable_change#, unrecoverable_time,CREATION_TIME from v$datafile
where 1=1
and  unrecoverable_time is not null and unrecoverable_change# > 0
;


NAME                                                                        FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME        CREATION_TIME
---------------------------------------------------------------------- ---------- --------------------- ------------------------- ----------------
+DATA/irac/apex_data01.dbf                                                     2            2487391149 11-08-2023 22:00          06-06-2017 08:58

select df.*
from   v$datafile df, v$backup bk 
where  df.file#=bk.file# 
  and  df.unrecoverable_change# <> 0 
  and  df.UNRECOVERABLE_TIME >
   (select max(end_time)
    from   v$rman_backup_job_details
    where  input_type in ('DB FULL' ,'DB INCR')
   );