Enable online status for all Oracle database datafiles

brunors

I have executed a database migration by transitioning from a physical active data guard to a primary database. Subsequent to the migration, I observed that several data files were offline, as evidenced below:


SQL> select count(*) from v$datafile where status='OFFLINE';

 

  COUNT(*)
----------
      3575

Modifying the status of each individual data file would require a substantial amount of time. To provide an illustrative example:


SQL> alter tablespace techdatabasket_20221101 online;

 

Tablespace altered.

 

SQL> ALTER DATABASE DATAFILE '+DATA/oradata/data/techdatabasket/techdatabasket_20221101.dbf' online;

 

Database altered.

 

SQL> select count(*) from v$datafile where status='OFFLINE';

 

  COUNT(*)
----------
      3574

In order to streamline the operation, I devised a PL/SQL procedure that effectively places all data files offline across all tablespaces within the database, subsequently modifying their statuses to online, as illustrated below:

-- SQL code
begin
  for tbs in ( SELECT DISTINCT(TABLESPACE_NAME) FROM dba_data_files WHERE ONLINE_STATUS='OFFLINE')
  loop
    execute immediate 'alter tablespace '||tbs.tablespace_name||' ONLINE';
  end loop;
end;
/


-- SQL execution
SQL> begin
  for tbs in ( SELECT DISTINCT(TABLESPACE_NAME) FROM dba_data_files WHERE ONLINE_STATUS='OFFLINE')
  loop
    execute immediate 'alter tablespace '||tbs.tablespace_name||' ONLINE';
  end loop;
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT(FILE_NAME) FROM dba_data_files WHERE ONLINE_STATUS='OFFLINE';

no rows selected

SQL> select count(*) from v$datafile where status='OFFLINE';

  COUNT(*)
----------
         0

I hope this post helps you out!

Related posts

Leave a Comment