How to recover database after data files lost

Symptoms

The database lost all data files except list below.

-rw-r----- 1 oracle oinstall  11M Aug 20 22:05 cjc01.dbf
-rw-r----- 1 oracle oinstall 1.2G Aug 21 12:15 system01.dbf

Solution

Find database compatible version.

~]# strings system01.dbf | grep "RDBMS version"
11.2.0.4.0 RDBMS version for NLS parameters,

Find database db_name.

BBED> set file 1 block 1
        FILE#           1
        BLOCK#          1

BBED> map /v
BBED> p kcvfh
   struct kcvfhhdr, 76 bytes                @20
......
      text kccfhdbn[0]                      @32      C
      text kccfhdbn[1]                      @33      J
      text kccfhdbn[2]                      @34      C
      text kccfhdbn[3]                      @35

Find database rollback segments.

[oracle@cjc-db-01 cjc]$ strings system01.dbf |grep -i _SYSSMU|cut -d $ -f 1|sort -u |awk '{ print $1"$"}'
_SYSSMU10_1197734989$
_SYSSMU10_3470984480$
_SYSSMU11_894599432$
_SYSSMU12_1573055333$
_SYSSMU1_2603659607$
_SYSSMU13_3860906822$
_SYSSMU1_3724004606$
_SYSSMU14_3319140121$
_SYSSMU15_1436577151$
_SYSSMU16_1689093467$
_SYSSMU17_1049158485$
_SYSSMU18_1557221903$
_SYSSMU19_2284825117$
_SYSSMU20_2312497597$
_SYSSMU2_2996391332$
_SYSSMU2_73114111$
_SYSSMU3_1723003836$
_SYSSMU3_596277271$
_SYSSMU4_1254879796$
_SYSSMU4_2523322691$
_SYSSMU5_4008018903$
_SYSSMU5_898567397$
_SYSSMU6_1263032392$
_SYSSMU6_4235600416$
_SYSSMU7_2070203016$
_SYSSMU7_2271882308$
_SYSSMU8_517538920$
_SYSSMU8_854328387$
_SYSSMU9_1650507775$
_SYSSMU9_508477954$

Create database parameter file.

[oracle@cjc-db-01 dbs]$ vi initcjc.ora
*.compatible='11.2.0.4.0'
*.db_name='cjc'
*.control_files='/db/oradata/cjc/control01.ctl'
*.pga_aggregate_target=1593835520
*.sga_target=4783603712
*.undo_management='MANUAL'
*._allow_resetlogs_corruption=TRUE
*._corrupted_rollback_segments='_SYSSMU10_1197734989$','_SYSSMU10_3470984480$','_SYSSMU11_894599432$','_SYSSMU12_1573055333$','_SYSSMU1_2603659607$','_SYSSMU13_3860906822$','_SYSSMU1_3724004606$','_SYSSMU14_3319140121$','_SYSSMU15_1436577151$','_SYSSMU16_1689093467$','_SYSSMU17_1049158485$','_SYSSMU18_1557221903$','_SYSSMU19_2284825117$','_SYSSMU20_2312497597$','_SYSSMU2_2996391332$','_SYSSMU2_73114111$','_SYSSMU3_1723003836$','_SYSSMU3_596277271$','_SYSSMU4_1254879796$','_SYSSMU4_2523322691$','_SYSSMU5_4008018903$','_SYSSMU5_898567397$','_SYSSMU6_1263032392$','_SYSSMU6_4235600416$','_SYSSMU7_2070203016$','_SYSSMU7_2271882308$','_SYSSMU8_517538920$','_SYSSMU8_854328387$','_SYSSMU9_1650507775$','_SYSSMU9_508477954$'
*.job_queue_processes=0

Startup database with nomount.

SQL> startup nomount

Find other normal database props base table location of block.

select distinct
dbms_rowid.rowid_relative_fno(rowid) file#,
dbms_rowid.rowid_block_number(rowid) block#
from props$;
###
FILE# = 1 BLOCK# = 801

Find database character set.

[oracle@cjc-db-01 trace]$ dd if=/oracle/app/oracle/oradata/cjc/system01.dbf of=/home/oracle/tmp/props bs=8192 skip=801 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000220592 s, 37.1 MB/s

[oracle@cjc-db-01 trace]$ strings /home/oracle/tmp/props | more
......
NLS_CHARACTERSET
AL32UTF8
Character set

Create control file script.

[oracle@cjc-db-01 trace]$ vi /home/oracle/tmp/0821/ctl01.sql
CREATE CONTROLFILE REUSE DATABASE "CJC" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/db/oradata/cjc/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/db/oradata/cjc/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/db/oradata/cjc/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/db/oradata/cjc/system01.dbf',
  '/db/oradata/cjc/cjc01.dbf'
CHARACTER SET AL32UTF8;

rebuilding control file.

SQL> @/home/oracle/tmp/0821/ctl01.sql
Control file created.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
cjc              MOUNTED

SQL> col name for a50
SQL> select file#,name,status from v$datafile;

     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /db/oradata/cjc/system01.dbf             SYSTEM
         5 /db/oradata/cjc/cjc01.dbf                RECOVER

Startup database with resetlogs.

SQL> alter database open RESETLOGS;
Database altered.

Check data.

SQL> col name for a55
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                                    STATUS
---------- ------------------------------------------------------- -------
         1 /db/oradata/cjc/system01.dbf                            SYSTEM
         2 /db/oracle/app/oracle/product/11.2/db/dbs/MISSING00002  RECOVER
         3 /db/oracle/app/oracle/product/11.2/db/dbs/MISSING00003  RECOVER
         4 /db/oracle/app/oracle/product/11.2/db/dbs/MISSING00004  RECOVER
         5 /db/oradata/cjc/cjc01.dbf                               ONLINE


SQL> select * from cjc.t1;
        ID NAME         XXX
---------- ------------ ----------
         1 cjc          2

Drop and create undo tablespace.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

SQL> create undo tablespace UNDOTBS1 datafile '/db/oradata/cjc/undotbs01.dbf' size 100M autoextend on;
Tablespace created.

Change parameter file and restart database.

[oracle@cjc-db-01 dbs]$ vi initcjc.ora
......
*.undo_tablespace='UNDOTBS1'
#*.undo_management='MANUAL'
#*._allow_resetlogs_corruption=TRUE
#*._corrupted_rollback_segments='_SYSSMU10_1197734989$','_SYSSMU10_3470984480$','_SYSSMU11_894599432$','_SYSSMU12_1573055333$','_SYSSMU1_2603659607$','_SYSSMU13_3860906822$','_SYSSMU1_3724004606$','_SYSSMU14_3319140121$','_SYSSMU15_1436577151$','_SYSSMU16_1689093467$','_SYSSMU17_1049158485$','_SYSSMU18_1557221903$','_SYSSMU19_2284825117$','_SYSSMU20_2312497597$','_SYSSMU2_2996391332$','_SYSSMU2_73114111$','_SYSSMU3_1723003836$','_SYSSMU3_596277271$','_SYSSMU4_1254879796$','_SYSSMU4_2523322691$','_SYSSMU5_4008018903$','_SYSSMU5_898567397$','_SYSSMU6_1263032392$','_SYSSMU6_4235600416$','_SYSSMU7_2070203016$','_SYSSMU7_2271882308$','_SYSSMU8_517538920$','_SYSSMU8_854328387$','_SYSSMU9_1650507775$','_SYSSMU9_508477954$'
#*.job_queue_processes=0


SQL> shutdown immediate
SQL> startup

SQL> update cjc.t1 set name='CJC' where id=1;
1 row updated.

SQL> commit;
Commit complete.

Export data.

oracle@cjc-db-01$exp cjc/***** file=/home/oracle/tmp/t1a.dmp log=/home/oracle/tmp/t1a.log tables=t1

Export: Release 11.2.0.4.0 - Production on Wed Aug 21 12:49:55 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                             T1          1 rows exported
Export terminated successfully without warnings.