Database fails to start after deleting Undotbs

Source: Internet
Author: User

sql> archive log list;
Database log mode No Archive mode
Automatic Archival Disabled
Archive Destination Use_db_recovery_file_dest
Oldest online log sequence 744
Current log sequence 746

Sql> select name from V$datafile;

NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/yoon/system01.dbf
/u01/oracle/oradata/yoon/sysaux01.dbf
/u01/oracle/oradata/yoon/users01.dbf
/u01/oracle/oradata/yoon/vpro.dbf
/u01/oracle/oradata/yoon/yoon01.dbf
/u01/oracle/oradata/yoon/svrmg1_oid.dbf
/u01/oracle/oradata/yoon/system02.dbf
/u01/oracle/oradata/yoon/system03.dbf
/u01/oracle/oradata/yoon/system04.dbf
/u01/oracle/oradata/yoon/undotbs_01.dbf

Rows selected.

[[email protected] yoon]$ ls
Control01.ctl control03.ctl.bak redo02.log sysaux01.dbf system03.dbf system04.dbf.bak undotbs_01.dbf yo On01.dbf
Control01.ctl.bak control04.ctl redo03.log system01.dbf system03.dbf.bak temp01.dbf users01.dbf
Control03.ctl redo01.log svrmg1_oid.dbf system02.dbf system04.dbf temp02.dbf vpro.dbf

[Email protected] yoon]$ MV UNDOTBS_01.DBF Undotbs_01.dbf.bak

sql> shutdown Immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Sql> Startup
ORACLE instance started.

Total System Global area 3340451840 bytes
Fixed Size 2232960 bytes
Variable Size 2432699776 bytes
Database buffers 889192448 bytes
Redo buffers 16326656 bytes
Database mounted.
Ora-01157:cannot identify/lock data file 12-see DBWR trace file
Ora-01110:data file: '/u01/oracle/oradata/yoon/undotbs_01.dbf '


sql> shutdown abort;
ORACLE instance shut down.

Sql> Startup Mount
ORACLE instance started.

Total System Global area 3340451840 bytes
Fixed Size 2232960 bytes
Variable Size 2432699776 bytes
Database buffers 889192448 bytes
Redo buffers 16326656 bytes
Database mounted.


sql> ALTER DATABASE datafile '/U01/ORACLE/ORADATA/YOON/UNDOTBS_01.DBF ' offline drop;

Database altered.


sql> ALTER DATABASE open;

Database altered.

sql> Create undo tablespace undotbs1 datafile '/u01/oracle/oradata/yoon/undotbs01.dbf ' size 1g;

Tablespace created.

Sql> alter system set undo_tablespace= ' UNDOTBS1 ' scope=spfile;

System altered.


Sql> drop tablespace undotbs01 including contents and datafiles;

Tablespace dropped.

sql> shutdown Immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Sql> create Pfile from SPFile;

File created.

Sql> Startup
ORACLE instance started.

Total System Global area 3340451840 bytes
Fixed Size 2232960 bytes
Variable Size 2432699776 bytes
Database buffers 889192448 bytes
Redo buffers 16326656 bytes
Database mounted.
Database opened.

Sql> show parameter undo;

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
Undo_management string AUTO
Undo_retention Integer 900
Undo_tablespace string UNDOTBS1


If:
Select Tablespace_name,segment_name,status from Dba_rollback_segs; Have needs recovery

[Email protected] dbs]$ pwd
/u01/oracle/product/11.2.0/db_1/dbs

[[email protected] dbs]$ ls
Hc_yoon.dat Init.ora inityoon.ora lkyoon orapwyoon snapcf_yoon.f Spfileyoon.ora

Edit the Inityoon.ora and add the implied parameters as follows:
*._corrupted_rollback_segments= ' _syssmu1_1240252155$ ', ' _syssmu2_111974964$ ', ' _syssmu3_4004931649$ ', ' _SYSSMU4_ 1126976075$ '


Sql>startup pfile= '/u01/oracle/product/11.2.0/db_1/dbs/inityoon.ora ';


Sql>drop tablespace undotbs01 including contents and datafiles;


Sql>shutdown Immediate


Will *._corrupted_rollback_segments= ' _syssmu1_1240252155$ ', ' _syssmu2_111974964$ ', ' _syssmu3_4004931649$ ', ' _SYSSMU4_ 1126976075$ ' Delete


Rebuilding Pfile,spfile

Sql>startup

Database fails to start after deleting Undotbs

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.