Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ How do I delete a NEEDS RECOVERY rollback state of undo tablespace     - C ++ stderr / stdout redirected to a file (Programming)

- CentOS yum configuration under local sources (Linux)

- Fedora 20, Fedora 19, CentOS 6 and RHEL6 users how to install Wine 1.7.15 (Linux)

- How to configure AWStats in Ubuntu Server (Server)

- Linux Hard Disk Partition and file system management (Linux)

- Find details block device with Linux blkid command (Linux)

- CentOS 7 How to install MySQL Server (Database)

- How to use systemd timer (Linux)

- Ten to improve the efficiency of the Linux bash tricks (Linux)

- Spring Integration ehcache annotation implement the query cache and cache update or delete instant (Programming)

- Configuring xdebug debugging environment in Ubuntu 14.04 under the PhpStorm (Linux)

- How Bluetooth turned off by default in Ubuntu 14.04 (Linux)

- Build your own Python coding environment (Linux)

- How to monitor network usage by nload in Linux (Linux)

- How to install Docker and basic usage on Ubuntu 15.04 (Server)

- CentOS 6.6 installation certification system based on the ftp service (Server)

- Ubuntu 14.04 install Sublime Text 3 plug and use SublimeClang (Linux)

- grep command output highlighted word (Linux)

- Linux --- file descriptors and redirection (Linux)

- Java is simple to read and write HDFS Demo (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:      
 
- Three methods easy data encryption on Linux (Linux)
- The direct insertion sort algorithm (Programming)
- You may not know the Linux command-line network monitoring tool (Linux)
- Category prevent DoS attacks against Linux (Linux)
- MySQL in order by inaccurate results in problems and solutions (Database)
- How to create SWAP files in Ubuntu 14.04 (Linux)
- CentOS iptables firewall enabled (Linux)
- Install Open vSwitch under CentOS 6.5 (Linux)
- WebLogic 12c Configuration Node Manager Managed Server (Database)
- Use mysqldump backup performed MariaDB (Database)
- Linux kernel to achieve soft RPS network to receive soft interrupt load balancing to distribute (Linux)
- Expert advice: Do not use the computer security IE browser (Linux)
- Java String type time compare the size (Programming)
- Use smartmontools view the health status of hard disk (Linux)
- Build and verify MongoDB3.0.7 version (shard + replica) Cluster (Database)
- Change the kernel boot sequence after CentOS 7 kernel upgrade (Linux)
- C ++ function object (Programming)
- Ten minutes to find out the character set and character encoding (Linux)
- DNF Command Tutorial (Linux)
- Debian (Wheezy) Install Java environment / replace OpenJDK as the SUN JDK (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.