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!
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)