A client restored a database without the undo tablespace. How can we open the database ?
You can do this by forcing oracle to use the system tablespace rollback segments until you fix the problem. This is where the database has been closed cleanly, it will not work if the datafiles are not at the same SCN, at which point you will have to look to oracle support, _allow_resetlogs_corruption and rebuilding the database. DON’T TRY THAT WITHOUT ORACLE SUPPORT.
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- C:APPORADATAMYDBDATAFILEO1_MF_USERS_7B64CSCZ_.DBF C:APPORADATAMYDBDATAFILEO1_MF_UNDOTBS1_7B64CS8W_.DBF C:APPORADATAMYDBDATAFILEO1_MF_SYSAUX_7B64CS8F_.DBF C:APPORADATAMYDBDATAFILEO1_MF_SYSTEM_7B64CS45_.DBF C:APPORADATAMYDBDATAFILEMYTBS.DBF SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string C:APPPRODUCT11.2.0DB1 DATABASESPFILEMYDB.ORA SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1
First create pfile from spfile, then set undo_management to MANUAL.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:Windowssystem32>move C:APPORADATAMYDBDATAFILEO1_MF_UNDOTBS1_7B64CS8W_.DBF C:APPORADATAMYDBDATAFILEO1_MF_UNDOTBS1_7B64CS8W_.DBF1 1 file(s) moved. C:Windowssystem32>sqlplus sys/password@myhost:1522/MYDB as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 19:10:19 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 1043886080 bytes Fixed Size 2182344 bytes Variable Size 780141368 bytes Database Buffers 255852544 bytes Redo Buffers 5709824 bytes SQL> create pfile from spfile; File created. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL>
Change the UNDO in the init file. Note that you have to offline the datafile to open the database else you will get the error below. In this case it does not matter, you can mount the database, offline the tablespace and open the database. I’m forcing it to open to show the error.
#*.undo_tablespace='UNDOTBS1' *.undo_management=MANUAL C:APPproduct11.2.0db1database>sqlplus sys/password@myhost:1522/MYDB as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 19:12:18 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile="C:APPPRODUCT11.2.0DB1databaseinitMYDB.ora"; ORACLE instance started. Total System Global Area 1043886080 bytes Fixed Size 2182344 bytes Variable Size 780141368 bytes Database Buffers 255852544 bytes Redo Buffers 5709824 bytes Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: 'C:APPORADATAMYDBDATAFILEO1_MF_UNDOTBS1_7B64CS8W_.DBF' SQL> alter database datafile 'C:APPORADATAMYDBDATAFILEO1_MF_UNDOTBS1_7B64CS8W_.DBF' offline; Database altered.
Now we should be able to open the database using the system tablespace rollback segments.
SQL> alter database open; Database altered. SQL> create undo tablespace UNDO2; Tablespace created.
Change the parameters in the pfile to point to UNDO2.
*.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDO2'
and restart the database.
C:APPproduct11.2.0db1database>sqlplus sys/password@myhost:1522/MYDB as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 19:15:34 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup; ORACLE instance started. Total System Global Area 1043886080 bytes Fixed Size 2182344 bytes Variable Size 780141368 bytes Database Buffers 255852544 bytes Redo Buffers 5709824 bytes Database mounted. Database opened.
Done. Now we have to drop the old datafile.
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- C:APPORADATAMYDBDATAFILEO1_MF_USERS_7B64CSCZ_.DBF C:APPORADATAMYDBDATAFILEO1_MF_UNDOTBS1_7B64CS8W_.DBF C:APPORADATAMYDBDATAFILEO1_MF_SYSAUX_7B64CS8F_.DBF C:APPORADATAMYDBDATAFILEO1_MF_SYSTEM_7B64CS45_.DBF C:APPORADATAMYDBDATAFILEMYTBS.DBF C:APPORADATAMYDBDATAFILEO1_MF_UNDO2_7NZCMO6K_.DBF 6 rows selected. SQL> alter database datafile 'C:APPORADATAMYDBDATAFILEO1_MF_UNDOTBS1_7B64CS8W_.DBF' offline drop; Database altered. SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS MYTBS UNDO2 7 rows selected. SQL> drop tablespace undotbs1; Tablespace dropped. SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- C:APPORADATAMYDBDATAFILEO1_MF_USERS_7B64CSCZ_.DBF C:APPORADATAMYDBDATAFILEO1_MF_SYSAUX_7B64CS8F_.DBF C:APPORADATAMYDBDATAFILEO1_MF_SYSTEM_7B64CS45_.DBF C:APPORADATAMYDBDATAFILEMYTBS.DBF C:APPORADATAMYDBDATAFILEO1_MF_UNDO2_7NZCMO6K_.DBF
