oracle| data
Problem Description:
This is a scenario in which a rollback segment tablespace data file is lost or corrupted, and Oracle does not recognize the corresponding data file. When you try to startup the data file, you will report ora-1157,ora-1110, and may be accompanied by an error identifying the operating system level, such as ORA-7360. When you try to close the database in shutdown normal or shutdown immediate mode, it leads to ora-1116,ora-1110 and may be accompanied by an error that identifies the operating system level, such as ORA-7368, Sometimes shutdown the database in the normal way is not shutdown.
Warning:
The steps mentioned in this article are for Oracle's global technical support. In particular, the _corrupted_rollback_segments parameter in step 6 requires that you rebuild the database after use, and look at all the other options before using this parameter.
Workaround Explanation:
The following workaround takes the state of the database when the detection problem occurs:
I. The database is in a closed state.
An attempt was made to open the database times ORA-1157 and ORA-1110 errors, at which point the solution was taken to whether the database was normal shutdown (using the normal or immediate option.
I.A. The database is a normal shutdown.
If the data database is normal shutdown, the simplest solution is to delete the missing or corrupted data file with the Offline drop option, and to restriceted the database to delete and reconstruct the rollback table space to which the data file belongs. Do not follow this process if the database is shutdown abort or crashes.
The steps are as follows:
1, confirm the database is normal shutdown. You can check alter.log this file, navigate to the last few lines and see if you can see the following information:
"ALTER DATABASE dismount
Completed:alter Database Dismount "
This, of course, also includes the normal way of shutdown, and then attempts to start the database failed. If the last time you shut down the database in shutdown abort or your database is crashed, you apply the following I.B method.
2. In Init<sid>.ora, the rollback segment belonging to the lost data file is removed from the rollback_segments parameter. If you're not sure which rollback segment you're in, you can simply comment out the rollback_segments parameter.
3. Mount database in restricted mode
STARTUP RESTRICT MOUNT;
4, Offline drop is missing or damaged that data file.
ALTER DATABASE datafile ' <full_path_file_name> ' OFFLINE DROP;
5, open the database
ALTER DATABASE OPEN;
If you return to the message "Statement processed", go to step 7th.
If you get ora-604,ora-376, and ORA-1110 error, go to step 6th.
6, because the database failed to open, shutdown the database and edit Int<sid>.ora this file. Comment out rollback_segments This parameter and add the following line to the Init<sid>.ora file:
_corrupted_rollback_segments = (<rollback1>,..., <rollbackN>)
This parameter should contain all the rollback segments in the rollback_segments.
Note that this parameter should only be used under specified circumstances or under the guidance of Oracle's global support, and then open the database in restricted mode:
STARTUP RESTRICT
7, delete the file belongs to the rollback segment table space.
DROP tablespace <tablespace name> including CONTENTS;
8. Rebuild the rollback segment table space and rollback segment, create them online after creation.
9. Make all users of the database available.
ALTER SYSTEM DISABLE restricted session;
10. Once again, include the rollback segment you recreated in Init<sid>.ora, if you use step 6th to remove the corrupted_rollback_segments parameter.
I.B. The database is not a normal shutdown.
In this case, the database was most recently closed with shutdown abort or crashed, and the rollback segment almost certainly contained an active transaction. Therefore, the bad data file cannot be taken offline (offline) or dropped, you must restore the file from backup. If the data is in a non-archive mode, you will be able to recover the file only if the most recent transaction log has not been rewritten. If the backup of this file is also invalid, contact Oracle's technical support.
The steps are as follows:
1, recover the missing data file from the backup.
2, mount the database
3, execute the following query:
SELECT file#,name,status from V$datafile;
If the status of the data file is offline, you must first bring it online:
ALTER DATABASE datafile ' <full_path_file_name> ' ONLINE;
4, execute the following query:
SELECT V1. group#, member, sequence#, first_change#
From V$log V1, V$logfile V2
WHERE V1. group# = V2. group#;
This will list all the online redo logs and their serial number and first change numbers.
5, if this database is not archived mode, the following query is executed:
SELECT file#, change# from V$recover_file;
If the chang# is larger than the first_change# of the smallest in 4, then the recovery can be done with the online log.
6, if the chang# is smaller than the smallest first_change# in 4, then the database is not recoverable, you can contact Oracle's technical support.
Translator Insert: If you really do not archive and the backup of this file is invalid, if you think you can lose the transaction in the rollback section, you can use the I.A in the 6th step, you can open the database, you should do a backup immediately, because the data in the library is somewhat inconsistent.
RECOVER datafile ' <full_path_file_name> '
7. Verify that all logs are restored, only to the information you receive "Media recovery complete".
8, open the database
Cond
