Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ How do I delete a NEEDS RECOVERY rollback state of undo tablespace     - The basic principles of AIX system security (Linux)

- Hardware Firewall Basics (Linux)

- GNU Linux system variables (sysctl configuration commands) integrated use (Linux)

- Linux and Unix systems really do network more secure (Linux)

- Linux input and output redirection (Linux)

- Depth understanding of the TCP protocol (Database)

- Ubuntu ADSL dial-up Internet access (Linux)

- MySQL partition table Comments (Database)

- Java singleton mode (Singleton mode) (Programming)

- Java, extends and implements Usage (Programming)

- CentOS / Linux kernel upgrade (Linux)

- OpenGL Superb Learning Notes - New Patterns (Programming)

- MySQL main and backup replication structures (using mysqld_multi) (Database)

- Android start automatically and add and delete a desktop shortcut (Programming)

- TOAD connect DB2 error SQL1460N solve (Database)

- Ubuntu 14.04 install PostgreSQL 9.2 (Database)

- Define and modify strings principle in Python (Programming)

- The difference Docker save and export commands (Linux)

- OpenJDK7 source compiler installation on CentOS 6.5 (Linux)

- On the PC goes heavy security watch your startup items (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:      
 
- CentOS / RHEL 6 was repeated prohibited under the SNMP connection log (Server)
- Linux more command Detailed (Linux)
- Python type way of comparison (Programming)
- MySQL full-index scan bug (Database)
- Let 32 Linux / CentOS system to support more than 4G memory (Linux)
- JavaScript event handling Detailed (Programming)
- PHP call a Python program (Programming)
- Linux netstat command to get started (Linux)
- DELL D630 Wireless LAN Driver Installation CentOS6 (Linux)
- Compression software on a simple comparison of zip and gz (Linux)
- MariaDB 10.0.X, the dynamic column support JSON format to obtain data (Database)
- Ubuntu 14.10 users to install Audio Recorder 1.5.7 (Linux)
- Mac Docker deploy development environment (Server)
- Java Annotation Comments (Programming)
- Shell command line operation (Linux)
- ORA-38856: Unable instance UNNAMED_INSTANCE_2 (redo thread 2) marked enabled (Database)
- SLF4J Tutorial (Programming)
- To use Linux to create a secure managed gateway (Linux)
- MongoDB3.0.6 wiredtiger MMAPv1 (Database)
- Linux iostat command example explanation (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.