Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ How do I delete a NEEDS RECOVERY rollback state of undo tablespace     - Ubuntu Series Installation Docker (Linux)

- Iptables command in detail (Linux)

- Lambda expressions of evolution (Programming)

- AIX system file security aspects (Linux)

- Linux in order to make NMAP hide and seek with the firewall (Linux)

- Installation Strongswan: on a Linux IPsec-based VPN tool (Linux)

- Oracle Sql Loader tool has shown signs (Database)

- Infinispan 8 new Redis cache storage implementation (Linux)

- Stunning exclamation point at the Linux command line (Linux)

- Encounter ORA-00600: internal error code, arguments: [4194] ORA-1552 (Database)

- To install the latest version of Shotwell 0.18 under Ubuntu (Linux)

- Windows Server 2012 R2 Datacenter install SQL Server 2016 CTP (Database)

- ActiveMQ memory settings and flow control (Linux)

- Ora-1092: OPI colleague K aborting process --- killed by OO Well killer (Database)

- Spring MVC Exception Handling (Programming)

- Examples of Python any parameters (Programming)

- How to Install Puppet in the Ubuntu 15.04 (Server)

- KVM virtualization of nested virtualization (Linux)

- Gentoo: existing preserved libs problem solving (Linux)

- Using PHP MySQL library (Programming)

 
         
  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:      
 
- Upgrade Goldengate 11.1.1.1.2 to 11.2.1.0.1 (Database)
- How to find on Linux and delete duplicate files: FSlint (Linux)
- Ubuntu 14.10 Install Ubuntu Touch Music App 2.0 (Linux)
- FileZilla 3.10.1.1 install on Ubuntu 14.10 (Linux)
- rsync server set up (Server)
- I use the desktop environment in GNU / Linux combination tool (Linux)
- Rails 4.1.6 start being given Could not find a JavaScript runtime (Linux)
- In the case of using cgroups Ubuntu 14.04 and Docker (Linux)
- VMware difference in three network connection (Linux)
- OpenSUSE GPG Comments (Linux)
- Those things packaged using Gradle to Android (Programming)
- Proper use Core Data multithreaded 3 ways (Programming)
- SUSE Linux install Oracle 10g and problem solving (Linux)
- The best tools and techniques to find data on a Unix system (Linux)
- A drop datafile Oracle bug (Database)
- Killall five options you might not know (Linux)
- Offline (local) Yum source structures (Linux)
- Ubuntu Backup and Recovery (Linux)
- How do you temporarily emptied Bash environment variable before running the command on Linux (Linux)
- ImageMagick Tutorial: How to cut images in Linux command line (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.