1) Edit your *init<sid>.ora file to change undo_management and add two parameters. + Change UNDO_MANAGEMENT=AUTO to UNDO_MANAGEMENT=MANUAL + Remove or comment out UNDO_TABLESPACE and UNDO_RETENTION. + Add _ALLOW_RESETLOGS_CORRUPTION = TRUE _CORRUPTED_ROLLBACK_SEGMENTS =(comma separated list of Automatic Undo segments) Example: _CORRUPTED_ROLLBACK_SEGMENTS = (_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$) Note, sometimes the alert log will tell you what Automatic Undo segments are in use. Search the alert log for SYSS. If the alert log does not contain that information then use _SYSSMU1$ through _SYSSMU10$ as shown in the example above. In UNIX you can issue this command to get the undo segment names: $ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u From the output of the strings command above, add a $ to end of each _SYSSMU undo segment name. + If you only have a spfile available, you can from the closed, nomount or the mount stage create an init<sid>.ora file as follows: SQL> CREATE PFILE FROM SPFILE; Do NOT edit the SPFILE. 2) Invoke SQL*Plus, startup mount, check that correct init<sid>.ora was used and all datafiles are in the status of online or system. $ sqlplus "/as sysdba" SQL> startup mount pfile = (full path / file name to init<sid>.ora) Confirm that the hidden parameters from step 3 were used: SQL> show parameters corrupt You should see both hidden parameters listed. If not, the wrong init<sid>.ora may have been modified. Do not continue until "show parameters corrupt" shows both hidden parameters. SQL> show parameters undo You should see undo management set to manual. If not, the wrong init<sid>.ora may have been modified. Do not continue until "show parameters undo" shows undo management as manual. Check that all files you want to open with are listed as ONLINE or as SYSTEM. SQL> select name, file#, status from v$datafile where status not in ('SYSTEM', 'ONLINE'); If any rows are returned from the query above, bring the file(s) online with: SQL> ALTER DATABASE DATAFILE file# ONLINE; and repeat until there are no files in an OFFLINE status. 3) Perform a fake incomplete recovery then open the database with resetlogs. SQL> recover database until cancel; or SQL> recover database using backup controlfile until cancel; WHEN PROMPTED FOR AN ARCHIVELOG FILE TYPE cancel THEN PRESS ENTER. SQL> ALTER DATABASE OPEN RESETLOGS;
Karthik, "Deleted the 0 bytes UNDO TBS and tried to restore UNDO TBS from Production to Test " It is not supported to copy individual Oracle database data files between databases like this. Each database file header contains information as to which database the file belongs to. You can restore an entire database to another server or instance but just moving one file would require using transportable tablespaces, or taking another approach to just move the data like export/import.Yes, you are right. I even tried to copy the UNDO TBS from Production to Test and I face the issue that it says DBID is not belongs to this DATABASE.
Undo tablespaces can be re-created however if you lost the single active undo tablespace while the database was in use then both physical and logical corruption of the database is possible after following the instructions for switching to a new undo tablespace since required undo operations will not be possible. You will need to check out the recovered database very carefully to be sure you are not in a situation where you need to extract your data and reload it into a new/re-created database.Here I'm not getting you. You mean to install a fresh database and perform a restore right?
Kindly suggest me what should I do now? To proceed with the next steps as suggested?Did you note the disclaimer "at your own risk "?
SQLPLUS statusAt this point, you overwrote your edited pfile with a new one, from the spfile. That is why you do not see those CORRUPT parameters. Why did you do it?
Connected to an idle instance.
SQL> create pfile from spfile;
A quick Google search gave thisI think it is the doc in ALL SOURCES which took to a community id 158632. Is this the right one?