Due to the loss of control file caused by a power failure, the startup database reported ORA-00205 error:

SQL> startup
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size          2925120 bytes
Variable Size         285216192 bytes
Database Buffers      125829120 bytes
Redo Buffers            5459968 bytes
ORA-00205: error in identifying control file, check alert log for more info

Through v$instance, you can see that the database status is started, but you cannot operate on the database. The show pdbs command does not display any information. Due to the loss of both control documents, the control documents can only be rebuilt manually.

Get parameter file information

Create pfile through spfile to view the control file location and database name in initialization parameters.

SQL> create pfile from spfile;

File created.

The pfile file generated by the Linux environment is in the $ORACLE_HOME/dbs directory to view the initialization parameter information.

[oracle@localhost dbs]$ cat initorcl.ora
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=125829120
orcl.__java_pool_size=4194304
orcl.__large_pool_size=8388608
orcl.__oracle_base='/u01/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=41943040
orcl.__sga_target=377487360
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=134217728
orcl.__streams_pool_size=0
*._catalog_foreign_restore=FALSE
*.aq_tm_processes=0
*.audit_file_dest='/u01/app/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oradata/orcl/control01.ctl','/u01/app/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.job_queue_processes=0
*.memory_target=397m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Get database character set

The database only needs to start to nomount state to execute the following SQL to get character set.

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.US7ASCII

Get data file

All data files are in the directory / u01/app/oradata /. Using find $PWD | xargs ls -ld can easily get the full path name of the data file. We only need redo log file and data file to rebuild the control file.

[oracle@localhost orcl]$ find $PWD | xargs ls -ld
drwxr-x---. 4 oracle oinstall        232 9month   8 16:06 /u01/app/oradata/orcl
-rw-r-----. 1 oracle oinstall  104865792 9month   8 16:01 /u01/app/oradata/orcl/interface_1.dbf
-rw-r-----. 1 oracle oinstall  104865792 9month   8 16:00 /u01/app/oradata/orcl/interface.dbf
drwxr-x---. 2 oracle oinstall        148 4month  18 17:49 /u01/app/oradata/orcl/pdborcl
-rw-r-----. 1 oracle oinstall 1304174592 9month   8 16:01 /u01/app/oradata/orcl/pdborcl/example01.dbf
-rw-r-----. 1 oracle oinstall   20979712 4month  26 13:00 /u01/app/oradata/orcl/pdborcl/pdborcl_temp012017-04-18_05-49-05-PM.dbf
-rw-r-----. 1 oracle oinstall    5251072 9month   8 16:01 /u01/app/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf
-rw-r-----. 1 oracle oinstall  555753472 9month   8 16:01 /u01/app/oradata/orcl/pdborcl/sysaux01.dbf
-rw-r-----. 1 oracle oinstall  272637952 9month   8 16:01 /u01/app/oradata/orcl/pdborcl/system01.dbf
drwxr-x---. 2 oracle oinstall         94 4month  18 17:18 /u01/app/oradata/orcl/pdbseed
-rw-r-----. 1 oracle oinstall   20979712 4month  26 16:57 /u01/app/oradata/orcl/pdbseed/pdbseed_temp012017-04-18_05-18-30-PM.dbf
-rw-r-----. 1 oracle oinstall  534781952 4month  26 17:24 /u01/app/oradata/orcl/pdbseed/sysaux01.dbf
-rw-r-----. 1 oracle oinstall  262152192 4month  26 17:24 /u01/app/oradata/orcl/pdbseed/system01.dbf
-rw-r-----. 1 oracle oinstall   52429312 9month   8 16:00 /u01/app/oradata/orcl/redo01.log
-rw-r-----. 1 oracle oinstall   52429312 9month   8 16:11 /u01/app/oradata/orcl/redo02.log
-rw-r-----. 1 oracle oinstall   52429312 9month   8 16:00 /u01/app/oradata/orcl/redo03.log
-rw-r-----. 1 oracle oinstall  765468672 9month   8 16:07 /u01/app/oradata/orcl/sysaux01.dbf
-rw-r-----. 1 oracle oinstall  838868992 9month   8 16:08 /u01/app/oradata/orcl/system01.dbf
-rw-r-----. 1 oracle oinstall   62922752 9month   8 16:03 /u01/app/oradata/orcl/temp01.dbf
-rw-r-----. 1 oracle oinstall  162537472 9month   8 16:08 /u01/app/oradata/orcl/undotbs01.dbf
-rw-r-----. 1 oracle oinstall    5251072 9month   8 16:00 /u01/app/oradata/orcl/users01.dbf

The necessary information for rebuilding the control file has been obtained. Now, execute the command to rebuild the control file

Execute CREATE CONTROLFILE to rebuild the control file

CREATE CONTROLFILE REUSE DATABASE 'ORCL' NORESETLOGS ARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/u01/app/oradata/orcl/redo01.log' SIZE 50M,
  GROUP 2 '/u01/app/oradata/orcl/redo02.log' SIZE 50M,
  GROUP 3 '/u01/app/oradata/orcl/redo03.log' SIZE 50M
DATAFILE
  '/u01/app/oradata/orcl/interface_1.dbf',
  '/u01/app/oradata/orcl/interface.dbf',
  '/u01/app/oradata/orcl/pdborcl/example01.dbf',
  '/u01/app/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf',
  '/u01/app/oradata/orcl/pdborcl/sysaux01.dbf',
  '/u01/app/oradata/orcl/pdborcl/system01.dbf',
  '/u01/app/oradata/orcl/pdbseed/sysaux01.dbf',
  '/u01/app/oradata/orcl/pdbseed/system01.dbf',
  '/u01/app/oradata/orcl/sysaux01.dbf',
  '/u01/app/oradata/orcl/system01.dbf',
  '/u01/app/oradata/orcl/undotbs01.dbf',
  '/u01/app/oradata/orcl/users01.dbf'
CHARACTER SET US7ASCII;

After the control file is rebuilt, the database cannot be opened directly. It is necessary to recover the data file system01.dbf from the media, and directly execute recover database.

SQL> recover database;
Media recovery complete.
SQL> alter database open;


Database altered.

SQL> SQL> show pdbs;

    CON_ID CON_NAME                           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2       PDB$SEED                            READ ONLY  NO
     3       PDBORCL                              MOUNTED
SQL> alter pluggable database pdborcl open;

Pluggable database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oradata/orcl/system01.dbf
/u01/app/oradata/orcl/sysaux01.dbf
/u01/app/oradata/orcl/undotbs01.dbf
/u01/app/oradata/orcl/pdbseed/system01.dbf
/u01/app/oradata/orcl/users01.dbf
/u01/app/oradata/orcl/pdbseed/sysaux01.dbf
/u01/app/oradata/orcl/pdborcl/system01.dbf
/u01/app/oradata/orcl/pdborcl/sysaux01.dbf
/u01/app/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf
/u01/app/oradata/orcl/pdborcl/example01.dbf
/u01/app/oradata/orcl/interface.dbf

NAME
--------------------------------------------------------------------------------
/u01/app/oradata/orcl/interface_1.dbf

12 rows selected.