You can create a before-insert trigger that updates the existing row as needed:

create table t (
  c1 int, ins_date date, is_active varchar2(1)
);

insert into t values ( 1, sysdate, 'Y' );
commit;

create or replace trigger t_trig
before insert on t 
for each row
begin
  update t 
  set    is_active = 'N'
  where  c1 = :new.c1
  and    is_active = 'Y';
end;
/

insert into t values ( 1, sysdate, 'Y' );

select * from t;

C1    INS_DATE                IS_ACTIVE   
    1 11-FEB-2021 17:26:51    N            
    1 11-FEB-2021 17:26:54    Y  

But...

I would avoid this method!

It only works for single-row inserts. If you try an insert-select, you'll hit:

insert into t 
  select 1, sysdate, 'Y' from dual;
  
ORA-04091: table CHRIS.T is mutating, trigger/function may not see it

There are various workaround for this problem. But it's better to create an API that you call to add a new row and update the existing rows.

For example:

create or replace procedure ins_row ( v1 int ) as
begin
  update t 
  set    is_active = 'N'
  where  c1 = v1
  and    is_active = 'Y';
  
  insert into t values ( v1, sysdate, 'Y' );
end ins_row;
/

exec ins_row ( 1 );
select * from t;

C1    INS_DATE                IS_ACTIVE   
    1 11-FEB-2021 17:26:51    N            
    1 11-FEB-2021 17:26:54    N            
    1 11-FEB-2021 17:27:18    Y  

This is clearer; there's no "surprising" behaviour where an insert does more than you were expecting.