Large Oracle database recovery (caused by misoperation)

The following articles mainly introduce the large recovery of the Oracle database caused by an incorrect operation. I checked dbms_backup_restore.zerodbid (0) in the script for creating a 10 Gb manual database in Oracle two days ago, the main function is to modify the dbid of the Oracle database.

Therefore, you want to use this storage to directly modify the dbid in sqlplus.

Record its dbid before modification

Reference

 
 

  
  
SQL> select dbid from v$database;  
  
  
DBID  
  
  
1488207495  
 
 

Modify dbid

Reference

 
 

  
  
SQL> exec dbms_backup_restore.zerodbid(0);  
  
  
PL/SQL procedure successfully completed.  
 
 

It seems that the execution is successful, but then the alert log shows that the ckpt process terminated the data instance.

Reference

 
 

  
  
Tue Mar 9 01:43:22 2010  
  
  
CKPT: terminating instance due to error 1242  
  
  
Instance terminated by CKPT, pid = 16653 
  
  
Tue Mar 9 01:43:53 2010  
 
 

An error is reported when Oracle database is started again.

Reference

 
 

  
  
Tue Mar 9 01:56:09 2010  
  
  
Errors in file /ora10g/app/admin/ldbra/udump/ldbra_ora_12275.trc:  
  
  
ORA-01221: data file 1 is not the same file to a background process  
  
  
ORA-1221 signalled during: ALTER DATABASE OPEN...  
 
 

Dump Oracle Data File Header

Reference

 
 

  
  
SQL> ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 3'; 
 
 

The trace file shows that dbid is reset to 0.

Reference

 
 

  
  
V10 STYLE FILE HEADER:  
  
  
Compatibility Vsn = 169870080=0xa200300  
  
  
Db ID=0=0x0, Db Name='LDBRA' 
  
  
Activation ID=0=0x0  
  
  
Control Seq=8122=0x1fba, File size=65280=0xff00  
  
  
File Number=1, Blksiz=8192, File Type=3 DATA  
 
 

Another way is to use the bbed tool to observe

Reference

 
 

  
  
struct kcvfhhdr, 76 bytes @20  
  
  
ub4 kccfhswv @20 0x00000000  
  
  
ub4 kccfhcvn @24 0x0a200300  
  
  
ub4 kccfhdbi @28 0x00000000  
 
 

Of course, the first response is to re-create the control file to see if it can be restored successfully.

Reference

 
 

  
  
SQL> alter database backup controlfile to trace;  
  
  
Database altered.  
  
  
STARTUP NOMOUNT  
  
  
CREATE CONTROLFILE REUSE DATABASE "LDBRA" RESETLOGS ARCHIVELOG  
  
  
MAXLOGFILES 16  
  
  
MAXLOGMEMBERS 3  
  
  
MAXDATAFILES 100  
  
  
MAXINSTANCES 8  
  
  
MAXLOGHISTORY 292  
  
  
LOGFILE  
  
  
GROUP 1 '/ora10g/app/oradata/ldbra/redo01.log' SIZE 50M,  
  
  
GROUP 2 '/ora10g/app/oradata/ldbra/redo02.log' SIZE 50M,  
  
  
GROUP 3 '/ora10g/app/oradata/ldbra/redo03.log' SIZE 50M  
  
  
-- STANDBY LOGFILE  
  
  
DATAFILE  
  
  
'/ora10g/app/oradata/ldbra/system01.dbf',  
  
  
'/ora10g/app/oradata/ldbra/undotbs01.dbf',  
  
  
'/ora10g/app/oradata/ldbra/sysaux01.dbf',  
  
  
'/ora10g/app/oradata/ldbra/users01.dbf',  
  
  
'/ora10g/app/oradata/ldbra/example01.dbf',  
  
  
'/ora10g/app/product/10.2.0/db_1/dbs/company.dbf',  
  
  
'/ora10g/app/product/10.2.0/db_1/dbs/streams.dbf'  
  
  
CHARACTER SET ZHS16GBK