Oracle restoration-11gR2 rac restoration case

This is an emergency data recovery case from a customer we received yesterday. Generally, the database cannot be open due to power loss. After preliminary troubleshooting, it is confirmed that the database version is Oracle 11.2.0.3 (linux RAC) and the data volume is small,
About GB. The entire recovery process started to look very smooth, and the database was successfully opened in just 30 minutes. It was found that there were indeed fewer pitfalls in the future. Here we will share with you a brief introduction to the restoration case for working overtime during the Ching Ming Festival.
First, let's take a look at the database's failure to open the report. What is the error?

Sun Apr 03 20:55:36 2016
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000. 5edc85a7 ):
Select ctime, mtime, stime from obj $ where obj # =: 1
Errors in file/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_5o0.trc:
ORA-00704: bootstrap process failure.
ORA-00704: bootstrap process failure.
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 29 with name "_ SYSSMU29_3872709797 $" too small
Errors in file/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_5o0.trc:
ORA-00704: bootstrap process failure.
ORA-00704: bootstrap process failure.
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 29 with name "_ SYSSMU29_3872709797 $" too small
Error 704 happened during db open, shutting down database
USER (ospid: 19990): terminating the instance due to error 704
Instance terminated by USER, pid = 19990
ORA-1092 signalled during: alter database open...
Opiodr aborting process unknown ospid (19990) as a result of ORA-1092
This error is actually very common and has been encountered many times, and it is not difficult to handle it. Generally, there are two methods.
1. Locate the problematic data block through 10046 trace, and then manually shield the transaction;
2. Promote database SCN
Here I chose to use the forward scn method for processing.
You can directly modify the scn through oradebug poke. The first modification may be because the added scn is not large enough. The first error is the same. The second error is changed. It becomes a more familiar error:
 

Errors in file/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_23188.trc (incident = 2108431 ):
ORA-00600: internal error code, arguments: [2662], [0], [2200563965], [0], [2200568242], [20971648], [], [], [], [], [], []
Incident details in:/u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_2108431/orcl1_ora_23188_i2108431.trc
Sun Apr 03 21:09:46 2016
Dumping diagnostic data in directory = [cdmp_20160403238546], requested by (instance = 1, osid = 23188), summary = [incident = 2108431].
Sun Apr 03 21:09:46 2016
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_23188.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2200563965], [0], [2200568242], [20971648], [], [], [], [], [], []
Errors in file/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_23188.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2200563965], [0], [2200568242], [20971648], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 23188): terminating the instance due to error 600
There are actually two ways to handle the preceding errors, which are roughly as follows:
1. Because the scn gap is very small, you can directly promote the scn.
2. Modify the transaction in dba address 20971648 to bypass this error.
Obviously, it is easier to select 1st methods here. Here, I will modify the scn again and increase it a little more. The database is opened smoothly.

SQL> startup mount pfile = '/tmp/pfile. ora ';
ORACLE instance started.
 
Total System Global Area 2.0243E + 10 bytes
Fixed Size 2237088 bytes
Variable Size 7449087328 bytes
Database Buffers 1.2751E + 10 bytes
Redo Buffers 41189376 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> alter system set job_queue_processes = 0;
 
System altered.
 
SQL> oradebug poke 0x060019598 4 0x832B8852
BEFORE: [060019598,060 01959C) = 00000000
AFTER: [060019598,060 01959C) = 832B8852
SQL> alter database open;
 
Database altered.
 
SQL>

It seems that the entire recovery process is very simple, and the database will be opened in less than half an hour. However, when I check the database file status, there are a total of 23 data files in the database, 11 of which are in the missing status,
In other words, database files cannot be identified. In fact, the following error is also reported in the database alert log, telling us that this part of data files cannot be identified:

Sun Apr 03 21:26:09 2016
Minact-scn: Inst 1 is now the master inc #: 2 mmon proc-id: 24523 status: 0x7
Minact-scn status: grec-scn: 0x0000.00000000 gmin-scn: 0x0000.00000000 gcalc-scn: 0x0000.00000000
[2, 24583] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial: 0 start: 123081664 end: 123083044 diff: 1380 (13 seconds)
Dictionary check beginning
Tablespace 'norming _ data' #10 found in DATA dictionary,
But not in the controlfile. Adding to controlfile.
Tablespace 'norming _ TEMP '#11 found in data dictionary,
But not in the controlfile. Adding to controlfile.
Tablespace 'normingtest _ TEMP '#12 found in data dictionary,
But not in the controlfile. Adding to controlfile.
Tablespace 'normingtest _ data' #13 found in DATA dictionary,
But not in the controlfile. Adding to controlfile.
Tablespace 'norminglj _ TEMP '#14 found in data dictionary,
But not in the controlfile. Adding to controlfile.
Tablespace 'norminglj _ data' #15 found in DATA dictionary,
But not in the controlfile. Adding to controlfile.
Tablespace 'tablespace _ XYZH '#16 found in data dictionary,
But not in the controlfile. Adding to controlfile.
File #13 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00013' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #14 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00014' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #15 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00015' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #16 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00016' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #17 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00017' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #18 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00018' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #19 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00019' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #20 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00020' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #21 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00021' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #22 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00022 'in the controlfile.
This file can no longer be recovered so it must be dropped.
File #23 found in data dictionary but not in controlfile.
Creating OFFLINE file 'missing00023' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete

Because the database has been opened, a script for recreating the control file is generated. The script content is as follows:
 

STARTUP NOMOUNT
Create controlfile reuse database "ORCL" RESETLOGS ARCHIVELOG
# MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
# MAXLOGHISTORY 9344
LOGFILE
GROUP 1 '+ DATA/orcl/onlinelog/group_1.273.850670135' SIZE 50 m blocksize 512,
GROUP 2 '+ DATA/orcl/onlinelog/group_2.274.850670135' SIZE 50 m blocksize 512
-- STANDBY LOGFILE
DATAFILE
'+ DATA/orcl/datafile/system.268.850670033 ',
'+ DATA/orcl/datafile/sysaux.269.850670033 ',
'+ DATA/orcl/datafile/undotbs1.270.850670033 ',
'+ DATA/orcl/datafile/users.271.850670033 ',
'+ DATA/orcl/datafile/undotbs2.276.850670237 ',
'+ DATA/orcl/datafile/datacenter ',
'+ DATA/orcl/datafile/partner_platform ',
'+ DATA/orcl/datafile/sw_portal ',
'+ DATA/orcl/datafile/system. Dbf ',
'+ DATA/orcl/datafile/system_02.dbf ',
'+ DATA/orcl/datafile/user_02.dbf ',
'+ DATA/orcl/datafile/user_03.dbf ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/MISSING00013 ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/missing00014 ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/missing00015 ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/missing00016 ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/missing00017 ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/missing00018 ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/MISSING00019 ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/missing00020 ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/missing00021 ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/MISSING00022 ',
'/U01/app/oracle/product/11.2.0/db_1/dbs/missing00023'
Character set ZHS16GBK;

In fact, I asked the customer that their feedback was that the control file was damaged, and the customer re-built the control file, restored it multiple times, and performed the resetlogs operation.
From the above information, it is not difficult to see that the customer missed 11 data files when recreating the control file. Because the information of this part of the file exists in the data dictionary, Oracle will automatically perform offline drop when the file is open.
Some people may say that it is not enough to find the file and recreate the control file? This is true, but it is actually not that simple.
I checked the files in the asm disk group and found several file names were very strange. For example, user_02.dbf actually linked to the system. This is similar.
In this case, errors are very easy. Query dba_data_files to confirm the data files one by one.
After confirming the four files missing from the asm disk group, there are still seven files in the file system. When all the files are added to the script for creation, it is found that these files are completely different from the previous files to resetlogs.
In fact, creating a control file reports an error to the ora-01189.
Therefore, you must manually modify the resetlogs information of these 11 data file headers. After modifying all the resetlogs information, you can create a control file.
However, when I perform reconver, I find that archivelog needs to be waited for. Further checks show that all archived logs are deleted.
Therefore, you must modify the checkpoint information of this data file again, change it to the same as other normal files, and then open the database smoothly,
Check that all database files are in the online status as follows:

Finally, migrate the file system file to the asm disk group, add redo information, and start rac node 2.