Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ How do I delete a NEEDS RECOVERY rollback state of undo tablespace     - Find details block device with Linux blkid command (Linux)

- KVM virtualization of nested virtualization (Linux)

- Manual cleaning Oracle audit records (Database)

- A drop datafile Oracle bug (Database)

- Large computer network security policy Experience (Linux)

- CentOS7 yum install third-party source EPEL (Linux)

- Install Redis 2.6 5.5 32 position CentOS error resolved (Linux)

- Android in the coordinate system and a method to obtain the coordinates (Programming)

- Ubuntu 15.04 / 14.04 install Ubuntu After Install 2.6 (Linux)

- How to Start a Linux application running in the background using the terminal mode (Linux)

- Configuring Eclipse Note Templates (Linux)

- MySQL 5.7.9 source compiler installation instructions (Database)

- Use rfkill soft-switching and Bluetooth wireless capabilities in Linux (Linux)

- Binary Packages Golang (Linux)

- Linux, grep, sed usage (Linux)

- CentOS7 installed MySQL (Database)

- Camera-based face recognition OpenCV crawl and storage format (Python) (Linux)

- Build Eclipse + Maven + Scala-IDEA the Scala Web development environment (Server)

- To protect the temporary files for Linux security (Linux)

- Lenovo E431 notebook CentOS system is installed wireless network card driver (Linux)

 
         
  How do I delete a NEEDS RECOVERY rollback state of undo tablespace
     
  Add Date : 2017-09-20      
         
       
         
  Environment: RHEL 6.4 + Oracle 11.2.0.4
Background: backup and recovery test libraries after an incomplete recovery, and do not have to prepare an effective whole, once again database failure resulting database can not properly open.
Offline only part of the data file to open the database that contains the undo tablespace data files.
Applicable scene: no valid backup, you can lose data, delete NEEDS RECOVERY rollback status of undo table space.

A database of the current situation

1.1 Symptom

1.2 Check the status of the data file

1.3 Try online data file failed

Second, remove the damaged table space data file

4 2.1 Common data file where the table space users can delete

3 2.2 undo the data file where the table space try to remove undotbs1

3 2.3 undo the data file where the table space deletion method undotbs1

A database of the current situation

1.1 Symptom

open resetlogs open the database error ORA-01152, ORA-01110, the error data files offline, first open the database.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 3 was not restored from a sufficiently old backup
ORA-01110: data file 3:
'/u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf'

SQL> alter database datafile 3 offline;
Database altered.

SQL> alter database open resetlogs;
 alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 4 was not restored from a sufficiently old backup
ORA-01110: data file 4: '/u02/oracle/JINGYU/datafile/o1_mf_users_bwp1b12d_.dbf'

SQL> alter database datafile 4 offline;
Database altered.

SQL> alter database open resetlogs;
Database altered.

1.2 Check the status of the data file

Data files 3,4OFFLINE. Wherein the data file 3 is undo tablespace data files.
SQL> set linesize 150
SQL> col file_name for a56
SQL> select FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES / 1024/1024 "MB", MAXBYTES / 1024/1024/1024 "GB", AUTOEXTENSIBLE, STATUS, ONLINE_STATUS from dba_data_files;

  FILE_ID FILE_NAME TABLESPACE_NAME MB GB AUT STATUS ONLINE_
---------- ---------------------------------------- ---------------- ------------------------------ ---- ------ ---------- --- --------- -------
        1 /u02/oracle/JINGYU/datafile/o1_mf_system_bwp198r7_.dbf SYSTEM 700 31.9999847 YES AVAILABLE SYSTEM
        2 /u02/oracle/JINGYU/datafile/o1_mf_sysaux_bwp19hl8_.dbf SYSAUX 600 31.9999847 YES AVAILABLE ONLINE
        3 /u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf UNDOTBS1 AVAILABLE OFFLINE
        4 /u02/oracle/JINGYU/datafile/o1_mf_users_bwp1b12d_.dbf USERS AVAILABLE OFFLINE
        5 /u02/oracle/JINGYU/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf DBS_D_JINGYU 100 31.9999847 YES AVAILABLE ONLINE

1.3 Try online data file failed
SQL> alter database datafile 3 online;
alter database datafile 3 online
*
ERROR at line 1:
ORA-01190: control file or data file 3 is from before the last RESETLOGS
ORA-01110: data file 3: '/u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf'

SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: '/u02/oracle/JINGYU/datafile/o1_mf_users_bwp1b12d_.dbf'

Second, remove the damaged table space data file

4 2.1 Common data file where the table space users can delete
SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

SQL> alter database default tablespace DBS_D_JINGYU;

Database altered.

SQL> drop tablespace users including contents and datafiles;

Tablespace dropped.

SQL> select FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES / 1024/1024 "MB", MAXBYTES / 1024/1024/1024 "GB", AUTOEXTENSIBLE, STATUS, ONLINE_STATUS from dba_data_files;

  FILE_ID FILE_NAME TABLESPACE_NAME MB GB AUT STATUS ONLINE_
---------- ---------------------------------------- ---------------- ------------------------------ ---- ------ ---------- --- --------- -------
        1 /u02/oracle/JINGYU/datafile/o1_mf_system_bwp198r7_.dbf SYSTEM 700 31.9999847 YES AVAILABLE SYSTEM
        2 /u02/oracle/JINGYU/datafile/o1_mf_sysaux_bwp19hl8_.dbf SYSAUX 600 31.9999847 YES AVAILABLE ONLINE
        3 /u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf UNDOTBS1 AVAILABLE OFFLINE
        5 /u02/oracle/JINGYU/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf DBS_D_JINGYU 100 31.9999847 YES AVAILABLE ONLINE

3 2.2 undo the data file where the table space try to remove undotbs1

Try to remove direct error ORA-01548. At the same time can not properly close the database, you can not delete the active rollback segments. details as follows:

2.2.1 create a new undo tablespace undotbs2 and set as the default undo tablespace
SQL> create undo tablespace undotbs2;

Tablespace created.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

SQL> alter system set undo_tablespace = 'undotbs2';

System altered.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs2

2.2.2 Delete the old table space failed undotbs1
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1401565358 $' found, terminate dropping tablespace

2.2.3 check the status of rollback, rollback segments to determine the status of the table space are undotbs1 NEEDS RECOVERY
SQL> select segment_id, segment_name, status, tablespace_name from dba_rollback_segs where status not in ( 'ONLINE', 'OFFLINE');

SEGMENT_ID SEGMENT_NAME STATUS TABLESPACE_NAME
---------- ------------------------------ ---------- ------ ------------------------------
        1 _SYSSMU1_1401565358 $ NEEDS RECOVERY UNDOTBS1
        2 _SYSSMU2_3125365238 $ NEEDS RECOVERY UNDOTBS1
        3 _SYSSMU3_1538315859 $ NEEDS RECOVERY UNDOTBS1
        4 _SYSSMU4_1640924022 $ NEEDS RECOVERY UNDOTBS1
        5 _SYSSMU5_2892967416 $ NEEDS RECOVERY UNDOTBS1
        6 _SYSSMU6_3276341082 $ NEEDS RECOVERY UNDOTBS1
        7 _SYSSMU7_387283697 $ NEEDS RECOVERY UNDOTBS1
        8 _SYSSMU8_2299136685 $ NEEDS RECOVERY UNDOTBS1
        9 _SYSSMU9_909303715 $ NEEDS RECOVERY UNDOTBS1
        10 _SYSSMU10_1695440836 $ NEEDS RECOVERY UNDOTBS1

10 rows selected.

2.2.4 At this time off normal library prompts fail
SQL> select file #, status from v $ datafile;

    FILE # STATUS
---------- -------
        1 SYSTEM
        2 ONLINE
        3 OFFLINE
        4 ONLINE
        5 ONLINE

SQL> shutdown immediate;
ORA-00376: file 3 can not be read at this time
ORA-01110: data file 3: '/u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf'

2.2.5 At this time remove the rollback will fail tips

Currently no effective database backup, these NEEDS RECOVERY delete the undo rollback segs
SQL> drop rollback segment "_SYSSMU10_1695440836 $";
drop rollback segment "_SYSSMU10_1695440836 $"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU10_1695440836 $' (in undo tablespace) not allowed

3 2.3 undo the data file where the table space deletion method undotbs1

2.3.1 modify this file pfile few lines

Wherein _offline_rollback_segments parameter name in the above-rollback found before.
* .undo_management = 'MANUAL'
* ._ Offline_rollback_segments = (_ $ SYSSMU1_1401565358, _ $ SYSSMU2_3125365238, _ SYSSMU3_1538315859 $, _ SYSSMU4_1640924022 $, _ SYSSMU5_2892967416 $, _ SYSSMU6_3276341082 $, _ SYSSMU7_387283697 $, _ SYSSMU8_2299136685 $, _ SYSSMU9_909303715 $, _ SYSSMU10_1695440836 $)
# *. Undo_tablespace = 'undotbs2'

2.3.2 Use pfile file to start the database
SQL> startup pfile = '/ tmp / pfile.ora';
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
Database opened.

2.3.3 Remove rollback success
SQL> drop rollback segment "_SYSSMU10_1695440836 $";

Rollback segment dropped.

2.3.4 Remove the rollback table space undotbs1 success
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

2.3.5 while the relevant information database query
SQL> select segment_id, segment_name, status, tablespace_name from dba_rollback_segs where status not in ( 'ONLINE', 'OFFLINE')
  2 ;

no rows selected

SQL> select FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES / 1024/1024 "MB", MAXBYTES / 1024/1024/1024 "GB", AUTOEXTENSIBLE, STATUS, ONLINE_STATUS from dba_data_files;

  FILE_ID FILE_NAME TABLESPACE_NAME MB GB AUT STATUS ONLINE_
---------- ---------------------------------------- ---------------- ------------------------------ ---- ------ ---------- --- --------- -------
        1 /u02/oracle/JINGYU/datafile/o1_mf_system_bwp198r7_.dbf SYSTEM 700 31.9999847 YES AVAILABLE SYSTEM
        2 /u02/oracle/JINGYU/datafile/o1_mf_sysaux_bwp19hl8_.dbf SYSAUX 600 31.9999847 YES AVAILABLE ONLINE
        4 /u02/oracle/JINGYU/datafile/o1_mf_undotbs2_bwqxbnxo_.dbf UNDOTBS2 100 31.9999847 YES AVAILABLE ONLINE
        5 /u02/oracle/JINGYU/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf DBS_D_JINGYU 100 31.9999847 YES AVAILABLE ONLINE

In this case all data files normally found in this case has been properly shut Library
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2.3.6 database with spfile start properly, check all normal
SQL> startup
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs2
SQL> set linesize 150
SQL> col file_name for a56
SQL> select FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES / 1024/1024 "MB", MAXBYTES / 1024/1024/1024 "GB", AUTOEXTENSIBLE, STATUS, ONLINE_STATUS from dba_data_files;

  FILE_ID FILE_NAME TABLESPACE_NAME MB GB AUT STATUS ONLINE_
---------- ---------------------------------------- ---------------- ------------------------------ ---- ------ ---------- --- --------- -------
        1 /u02/oracle/JINGYU/datafile/o1_mf_system_bwp198r7_.dbf SYSTEM 700 31.9999847 YES AVAILABLE SYSTEM
        2 /u02/oracle/JINGYU/datafile/o1_mf_sysaux_bwp19hl8_.dbf SYSAUX 600 31.9999847 YES AVAILABLE ONLINE
        4 /u02/oracle/JINGYU/datafile/o1_mf_undotbs2_bwqxbnxo_.dbf UNDOTBS2 100 31.9999847 YES AVAILABLE ONLINE
        5 /u02/oracle/JINGYU/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf DBS_D_JINGYU 100 31.9999847 YES AVAILABLE ONLINE

Finally, the current recovery immediately do a good database full backup of it.
     
         
       
         
  More:      
 
- How to use the on-screen keyboard in Linux (Linux)
- Linux Fundamentals of the text, data flow processing orders (Linux)
- UNIX file permissions in the "set user ID bit" (Linux)
- Ubuntu 14.04 Solution login interface infinite loop (Linux)
- Using 30 seconds to write a detailed analysis of garbage removal system (Linux)
- Python context managers (Programming)
- Five programming fallacy (Programming)
- Analysis JavaBean (Programming)
- Ubuntu 14.04 next upgrade to GNOME 3.12 (Linux)
- Configuring a Linux operating system security management services (Linux)
- Apache Tomcat integration and resin (Server)
- Binary search is really easy as you think you do (Programming)
- Learn about EditText little depth (Programming)
- Security Knowledge: redirection command application security (Linux)
- Java data structures - the single linked list LinkedList linear table (Programming)
- How Linux system password security guarantee (Linux)
- Linux CPU Monitoring Index (Linux)
- MySQL 5.5 on master-slave copy filter (Database)
- To setup NOTRACK and TRACK of conntrack in iptables (Linux)
- JBPM6 Installation and Configuration Tutorial (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.