A testing environment of the company is based on oracle11.2.0.3 database on the linux platform. It is archived and has not been backed up. On the evening of the 21st, because the space in the directory is % 100, the oracleHOME directory is in the system directory: the oracle database is suspended because the hard disk resources are exhausted and cannot be connected. The maximum usage of undotbs1 is displayed.
A testing environment of the company is based on the oracle 11.2.0.3 database on the linux platform. It is archived and has not been backed up. On the evening of the 21st, because the space in the/directory is % 100, the oracle HOME Directory is in the system/directory: the oracle database is suspended because the hard disk resources are exhausted and cannot be connected. The maximum usage of undotbs1 is displayed.
A testing environment of the company is based on the oracle 11.2.0.3 database on the linux platform. It is archived and has not been backed up. On the evening of the 21st, % 100 is used for space in the/directory. The oracle HOME Directory is in the system/directory:
The oracle database is suspended because hard disk resources are exhausted and cannot be connected.
One person's operation, view undotbs1 occupies the largest space, move to another directory through mv, and the system is restarted, so that the undotbs1 data file is damaged and cannot be used. Finally, another rm operation is performed, restart the database, resulting in a fault!
Error 1:
Wed Jan 22 09:42:50 2014 alter database OPENErrors in file/u01/app/oracle/diag/rdbms/gtadata13/gtadata13/trace/gtadata13_dbw0_1_5.trc: ORA-01157: cannot identify/lock data file 3-see DBWR trace fileORA-01110: data file 3: '/u01/app/oracle/oradata/gtadata13/undotbs01.dbf' ORA-27047: unable to read the header block of fileLinux-x86_64 Error: 25: Inappropriate ioctl for deviceAdditional information: 1Wed Jan 22 09:42:52 2014 Checker run found 1 new persistent data failuresErrors in file/u01/app/oracle/diag/rdbms/gtadata13/gtadata13/trace/gtadata13_ora_4361.trc: ORA-01157: cannot identify/lock data file 3-see DBWR trace fileORA-01110: data file 3: '/u01/app/oracle/oradata/gtadata13/undotbs01.dbf' ORA-1157 signalled: alter database open...
--- After oracle is mounted, it cannot be loaded to the open state.
2. Next, because the undo tablespace data file undotbs1 is missing, you want to restart the database to open by recreating an undo tablespace undotbs2.
Operation:
SQL> show parameter undoNAME TYPE VALUE ------------------------------------ ---------------------------------------- undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/XXXX. DBF 'size 32 m autoextend on next 32 m maxsize 10G; -- recreate a tablespace SQL> SELECT * FROM V $ TABLESAPCE SELECT NAME, status from v $ DATAFILE -- query its STATUS value SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE = BOTH -- check whether it is used through show parameter undo.
3 at this time, the database can be opened, but an error is returned when the database is connected through the client or another user:
Error 2 SQL> conn input/INPUTERROR: ORA-00604: error occurred at recursive SQL level 1ORA-00376: file 3 cannot be read at this timeORA-01110: data file 3: '/u01/app/oracle/oradata/gtadata13/undotbs01.dbf' ORA-02002: error while writing to audit trailORA-00604: error occurred at recursive SQL level 1ORA-00376: file 3 cannot be read at this timeORA-01110: data file 3: '/u01/app/oracle/oradata/gtadata13/undotbs01.dbf'
4. According to the error message, it is found that not only the undotbs1 data file is faulty, but also the audit is Enabled:
Close audit SQL> SHOW PARAMETER AUDITNAME TYPE VALUE detail ----------- audit audit_file_dest string/u01/app/oracle/admin/gtadata1 3/empty boolean FALSEaudit_syslog_level stringaudit_trail string DB
SQL> alter system set audit_trail = none scope = spfile -- restart the database after setting. -- For details, see audit.
5. Then, perform operations on the undotbs1 data file to enable offline processing (check whether the row is correct)
SQL> alter database datafile 3 offline drop;
6. Use v $ logfile, dba_tablespaces, and dba_data_files to view the data table space and Data File status:
SQL> select tablespace_name, file_id, file_name from dba_data_files; TABLESPACE_NAME FILE_ID FILE_NAME ------- ---------- using USERS 4/u01/app/oracle/oradata/gtadata13/users01.dbfUNDOTBS1 3/u01/app/oracle/oradata/gtadata13/USERS> select status, tablespace_name from dba_tablespaces; STATUS TABLESPACE_NAME --------- ------------------------------ online systemonline sysauxonline UNDOTBS1
7. The undotbs1 data file is still found, and the undotbs1 table is empty online.
For example:
ERROR 3 SQL> alter tablespace UNDOTBS1 offline; alter tablespace UNDOTBS1 offline * ERROR at line 1: ORA-01191: file 3 is already offline-cannot do a normal offline ORA-01110: data file 3: '/u01/app/oracle/oradata/gtadata13/undotbs01.dbf' --- at this time, I thought, how can I not offline it and see if I can query the data FILE header by wind temporary offline, select FILE #, checkpoint_change #, recover, fuzzy from v $ datafile_header;
Finally, use SQL> alter system checkpoint; -- to make a checkpoint. Then try again: System altered. SQL> alter tablespace undotbs1 offline temporary; Tablespace altered.
Check the status of undotbs1 again through dba_tablespaces and find whether it is offline. Offline status.
8. Test and see if other users can connect to the client: -- OK is found. You can connect to the client through another user. However, some programs may report an error:
Error 4:
ORA-00376: unable to read file 3ORA-01110: data file 3: "/u01/app/oracle/oradata/gtadata13/undotbs01.dbf 'ora-06512: In" GTA_DATA.SP_QA_TIMELINESS ", line 54ORA-06512: In line 1
If you think about it, it is true that because undotbs1 is physically deleted, oracle consistency will be restored using the recovery:
9. Can I delete the offline statement? (it may be difficult. How can I roll it back when I return it to the offline statement ?)
Through dba_rollback_segs, we found that there are still many undotbs1 segments in the recovery ing to be rolled back and restored, Which is data consistency.
SQL> select segment_name, tablespace_name, status from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME STATUS when loading SYSTEM variable $ UNDOTBS1 variable $ UNDOTBS1 variable $ UNDOTBS1 variable $ UNDOTBS1 variable $ UNDOTBS1 variable $ UNDOTBS1 variable $ unknown NEEDS variable $ UNDOTBS1 NEEDS variable $ response NEEDS quota $ UNDOTBS1 NEEDS quota $ UNDOTBS1 NEEDS quota $ UNDOTBS1 NEEDS quota $ UNDOTBS1 NEEDS quota $ UNDOTBS2 quota $ UNDOTBS2 quota $ UNDOTBS2 quota $ UNDOTBS2 quota $ UNDOTBS2 ONLINE
At this time, Baidu and asked some experts to make a backup: If you want to import and export through expdp:
Error 5:
[Oracle @ gtadata13 dump_dir] $ impdp dcsys/DCSYS directory = dump_dir dumpfile = TBL_CHN_FN_ForecFin.dmpImport: Release 11.2.0.3.0-Production on Wed Jan 22 14:40:30 2014 Copyright (c) 1982,201 1, oracle and/or its affiliates. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORA-31626: job does not existORA-06512: at "SYS. DBMS_SYS_ERROR ", line 79ORA-06512: at" SYS. KUPV $ FT ", line 1042ora-000037: cannot create job SYS_IMPORT_FULL_01 for user DCSYSORA-31632: master table" DCSYS. SYS_IMPORT_FULL_01 "not found, invalid, or inaccessibleORA-31635: unable to establish job resource synchronizationORA-06512: at" SYS. DBMS_SYS_ERROR ", line 79ORA-06512: at" SYS. KUPV $ FT_INT ", line 2401ORA-00376: file 3 cannot be read at this timeORA-01110: data file 3: '/u01/app/oracle/oradata/gtadata13/undotbs01.dbf' -- this does not work either, it seems that we can only do it honestly.
10. Delete the offline undotbs1 tablespace to see if it is skipped:
Error 6:
SQL> drop tablespace undotbs1; drop tablespace undotbs1 * ERROR at line 1: ORA-01548: active rollback segment '_ blank $' found, terminate dropping tablespaceSQL> DROP ROLLBACK SEGMENT "_ syssmu%1240252155 $ "; drop rollback segment "_ syssmu%1240252155 $" * ERROR at line 1: ORA-30025: DROP segment '_ syssmu%1240252155 $' (in undo tablespace) not allowed
Again Through Baidu, you can ask: You need to add the hidden parameter file _ offline_rollback_segments ('xx') and _ Your upted_rollback_segments ('xx') on the pfile and then delete it. Check whether you want to skip this step.
Add the parameter _ offline_rollback_segments = ('') _ corrupted_rollback_segments = ('') to pfile. The bracket parameter is the value "_ blank $" when the undotbs1 status in dba_rollback_segs Is In The need recovery state"
10: You can use pfile to add the shadow parameter or alter system set _ offline_rollback_segments = "value" socpe = spfile
Alter system set _ partition upted_rollback_segments = "value" socpe = spfile.
At that time, I rebuilt the pfile parameter file *. _ offline_rollback_segments = ('_ SYSSMU90_1969094056 $ ',....) *. _ Required upted_rollback_segments = ('_ SYSSMU90_1969094056 $' to operate
Then, after deleting all the values under dba_rollback_segs, In the drop undotbs1 tablespace:
SQL> drop rollback segment "_ syssmu%1240252155 $"; --- Note that the double quotation marks cannot contain spaces. Rollback segment dropped. --- corresponding values are deleted one by one.
11: Finally, delete the undotbs1 tablespace --- OK. You can delete it. Then, you can use dba_rollback_segs to find that the tablespace of undtotbs1 is missing.
SQL> select segment_name, tablespace_name, status from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME STATUS when loading SYSTEM variable $ UNDOTBS2 variable $ UNDOTBS2 variable $ UNDOTBS2 variable $ UNDOTBS2 variable $ UNDOTBS2 variable $ UNDOTBS2 variable $ UNDOTBS2 variable $ UNDOTBS2 ONLINE
12: scanning the end: a: restore the original audit function settings. B: Switch multiple times to view business data. c: this operation is acceptable, but some business data is lost. d: back up e: as the master said, don't rush
