[Undo tablespace loss-restore-1], undo table loss-1

Source: Internet
Author: User

[Undo tablespace loss-restore-1], undo table loss-1
Restore with rman -- undo loss
Restore restores the file back;
Recover redo with log files;
Critical file loss and non-critical file loss (out of system/undo)
1>
Delete the undo file:
[Oracle @ oracle ~] $ Rm/u01/oracle/oradata/jadl10g/undotbs01.dbf
[Oracle @ oracle ~] $ Sqlplus/as sysdba
SQL * Plus: Release 10.2.0.5.0-Production on Thu Nov 6 14:41:40 2014
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options

SQL> conn scott/tiger --- there is no error in connection at this time, because the undo has been cached and the cache is cleared;
Connected.
SQL> conn/as sysdba
Connected.
SQL> alter system flush shared_pool;
System altered.

SQL> alter system flush buffer_cache;
System altered.

SQL> alter system flush global context;
System altered.

SQL> conn scott/tiger ---- An error occurred while connecting to scott, prompting undo to be lost
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/oracle/oradata/jadl10g/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
SQL> conn/as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options
[Oracle @ oracle ~] $ Rman target/
Recovery Manager: Release 10.2.0.5.0-Production on Thu Nov 6 14:45:54 2014
Copyright (c) 1982,200 7, Oracle. All rights reserved.
Connected to target database: JADL10G (DBID = 2011530396)

RMAN> shutdown abort
Using target database control file instead of recovery catalog
Oracle instance shut down

RMAN> startup mount
Connected to target database (not started)
Oracle instance started
Database mounted
Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 163580992 bytes
Database Buffers 427819008 bytes
Redo Buffers 6287360 bytes

RMAN> restore datafile 2;
Starting restore at 06-NOV-14
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 157 devtype = DISK
Channel ORA_DISK_1: starting datafile backupset restore
Channel ORA_DISK_1: specifying datafile (s) to restore from backup set
Restoring datafile 00002 to/u01/oracle/oradata/jadl10g/undotbs01.dbf
Channel ORA_DISK_1: reading from backup piece/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_tag20141106t1351__b5p32dhf _. bkp
Channel ORA_DISK_1: restored backup piece 1
Piece handle =/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/empty _. bkp tag = TAG20141106T135107
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 06-NOV-14

RMAN> recover database;
Starting recover at 06-NOV-14
Using channel ORA_DISK_1
Starting media recovery
Media recovery complete, elapsed time: 00:00:01
Finished recover at 06-NOV-14

RMAN> alter database open;
Database opened

* ***** You do not need to back up the database again after using this restoration method. This method can be used for undo loss or system tablespace loss. ****

2> Create a New undo tablespace and change it to the new tablespace, provided that the command for creating the new undo tablespace is correctly executed;
[Oracle @ oracle ~] $ Rm/u01/oracle/oradata/jadl10g/undotbs01.dbf
[Oracle @ oracle ~] $ Sqlplus/as sysdba
SQL * Plus: Release 10.2.0.5.0-Production on Thu Nov 6 14:52:24 2014
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options

SQL> select name from v $ datafile;
NAME
--------------------------------------------------------------------------------
/U01/oracle/oradata/jadl10g/system01.dbf
/U01/oracle/oradata/jadl10g/undotbs01.dbf
/U01/oracle/oradata/jadl10g/sysaux01.dbf
/U01/oracle/oradata/jadl10g/users01.dbf
/U01/oracle/oradata/jadl10g/example01.dbf

SQL> ho ls/u01/oracle/oradata/jadl10g/undotbs01.dbf
Ls: cannot access/u01/oracle/oradata/jadl10g/undotbs01.dbf: No such file or directory

SQL> create undo tablespace undotbs2 datafile '/u01/oracle/oradata/jadl10g/undotbs02.dbf' size 10 m autoextend on;
Create undo tablespace undotbs2 datafile '/u01/oracle/oradata/jadl10g/undotbs02.dbf' size 10 m autoextend on
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/oracle/oradata/jadl10g/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

If the create undo tablespace command cannot be executed normally, it can only be restored using the above method; if it can be created successfully, you can not close the database;

Alter system set undo_tablespace = undotbs2;

An error occurs when you run the following command:
Drop tablespace undotbs01;
Alter tablespace undotbs01 offline;

You can run the following command to offline files that are not in use:
Alter database datafile 2 offline;
You can also restore the original file back:
Enter rman:
Restore datafile 2;
Recover datafile 2;
Go to sqlplus:
Alter database datafile 2 online;
Alter system set undo_tablespace = undotbs1;

* ** At this time, you need to back up the database again. Note that the undo in use cannot be taken offline.
* *** It can be created without clearing the cache. There is also the establishment of two undo to prevent the loss of one, you can switch to another.
* *** The tablespace in the system must be shut down.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.