Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ How do I delete a NEEDS RECOVERY rollback state of undo tablespace     - MyCAT log analysis (Database)

- CentOS 7.0 Close firewall is enabled by default iptables firewall (Linux)

- Yii2 Advanced Version Copy New Project Problem Solved (Programming)

- Linux firewall iptables beginner tutorial (Linux)

- Linux install and configure Heartbeat (Server)

- Nginx introduced Dynamic Module Architecture (Server)

- MySQL use the integer type (Database)

- NFS-based services and service utilization Corosync DRBD high availability cluster configuration, respectively (Server)

- CentOS of NFS (Server)

- Node.js Getting the basics: Helloworld! (Linux)

- Two strokes to improve development productivity Struts2 (Programming)

- Spring AOP for logging (Programming)

- The three-way division of the sorting algorithm Quicksort (Programming)

- Give your photos Instagram style filters plus program in ubuntu (Linux)

- Python maketrans () method (Programming)

- Oracle 11g maintenance partitions (Seven) - Modifying Real Attributes of Partitions (Database)

- Using open source software to build XWiki Wiki system installed within the company (Linux)

- CentOS 6.6 shortcut command execution (Linux)

- Oracle 11g statistics collection - collection of multi-column statistics (Database)

- VirtualBox virtual machine can not start to solve under Ubuntu (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:      
 
- Linux environment Duplex (multi-machine) automatic mutual backup scheme (Linux)
- CentOS 6.6 install JDK7 (Linux)
- Linux operating system must know the security command (Linux)
- DRBD + Heartbeat solve NFS single point of failure (Server)
- Talk about the Linux folder permissions issue again (Linux)
- Hadoop connection failed or stuck processing (Server)
- VirtualBox virtual machine to install Linux (Linux)
- Github Remote Assistance (Linux)
- Analysis of Java reflection mechanism (Programming)
- File permissions under Linux (Linux)
- Inherent limitations of Linux systems network security (Linux)
- HDFS Hadoop Distributed File System Works (Server)
- Linux SSH commands (Linux)
- JavaScript prototype and prototype chain and project combat (Programming)
- Linux system installation Gitlab (Server)
- Java development environment to build under Ubuntu (Linux)
- Android in the coordinate system and a method to obtain the coordinates (Programming)
- C # get the current screenshot (Programming)
- Linux directory structure (Linux)
- High-performance Linux system firewall detailed analysis of double-effect (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.