I was working on an activity that required me to set some datafile offline when I received the following error:
SQL> alter database datafile 99 offline; alter database datafile 99 offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled
After some analysis, I realized that the database was in no archive mode, and to be able to make a datafile offline, the database must be in archive mode. Therefore, I enabled the archive mode in the database by restarting the database in the MOUNT state, allowing the archive to mode option, and after it was possible to change the status of the database as below:
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1068937216 bytes Fixed Size 2235208 bytes Variable Size 436208824 bytes Database Buffers 624951296 bytes Redo Buffers 5541888 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +RECO Oldest online log sequence 49 Next log sequence to archive 50 Current log sequence 50 SQL> show parameters recov NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 SQL> ALTER SYSTEM SET db_recovery_file_dest_size=100G SCOPE=BOTH; System altered. SQL> ALTER SYSTEM SET db_recovery_file_dest='+RECO' SCOPE=BOTH; System altered. SQL> alter database open; Database altered. SQL> SQL> alter database datafile 99 offline; Database altered.
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)