Re-create lost controlfile

Here in this demo i deleted all control files and re-created using trace file.
In Oracle forum OTN related to re-create controlfile, confusion between shutdown immediate and shutdown abort after controlfile lost. this demo helps you to re-create lost control file

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rev1/control01.ctl
/u01/app/oracle/oradata/rev1/control02.ctl
/u01/app/oracle/oradata/rev1/control03.ctl

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rev1/system01.dbf
/u01/app/oracle/oradata/rev1/undotbs01.dbf
/u01/app/oracle/oradata/rev1/sysaux01.dbf
/u01/app/oracle/oradata/rev1/users01.dbf

SQL> col member format a50
SQL> select a.group#,a.member,b.status from v$logfile a, v$log b where a.group#=b.group#;

  GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- ----------------
       3 /u01/app/oracle/oradata/rev1/redo03.log            ACTIVE
       2 /u01/app/oracle/oradata/rev1/redo02.log            CURRENT
       1 /u01/app/oracle/oradata/rev1/redo01.log            ACTIVE

to create control file trace

SQL> alter database backup controlfile to trace;

Database altered.
SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /u01/app/oracle/admin/rev1/udump
                                             
or

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE
2  AS '/home/oracle/Desktop/create_ctl.sql';

Database altered.

you can find the sample controlfile trace file in the location user_dump_dest /u01/app/oracle/admin/rev1/udump the last recently created file.

u01/app/oracle/admin/rev1/udump/rev1_ora_10939.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and OLAP options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: rac1.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: rev1
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 10939, image: oracle@rac1.localdomain (TNS V1-V3)

*** SERVICE NAME:(SYS$USERS) 2010-09-21 16:52:23.473
*** SESSION ID:(142.32) 2010-09-21 16:52:23.473
*** 2010-09-21 16:52:23.472
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="rev1"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "REV1" NORESETLOGS FORCE LOGGING ARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/rev1/redo01.log'  SIZE 5M,
GROUP 2 '/u01/app/oracle/oradata/rev1/redo02.log'  SIZE 5M,
GROUP 3 '/u01/app/oracle/oradata/rev1/redo03.log'  SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/rev1/system01.dbf',
'/u01/app/oracle/oradata/rev1/undotbs01.dbf',
'/u01/app/oracle/oradata/rev1/sysaux01.dbf',
'/u01/app/oracle/oradata/rev1/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/rev1/temp01.dbf'
   SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

copy and paste the following lines in the notepad/text editor from the above trace files and save it as create_ctl.sql

CREATE CONTROLFILE REUSE DATABASE "REV1" RESETLOGS FORCE LOGGING ARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/rev1/redo01.log'  SIZE 5M,
GROUP 2 '/u01/app/oracle/oradata/rev1/redo02.log'  SIZE 5M,
GROUP 3 '/u01/app/oracle/oradata/rev1/redo03.log'  SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/rev1/system01.dbf',
'/u01/app/oracle/oradata/rev1/undotbs01.dbf',
'/u01/app/oracle/oradata/rev1/sysaux01.dbf',
'/u01/app/oracle/oradata/rev1/users01.dbf'
CHARACTER SET WE8ISO8859P1
;

remove the control files(control01.ctl,control02.ctl,control03.ctl) from the location /u01/app/oracle/oradata/rev1/ for testing purpose to re-create it.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/u01/app/oracle/oradata/rev1/control01.ctl
/u01/app/oracle/oradata/rev1/control02.ctl
/u01/app/oracle/oradata/rev1/control03.ctl

SQL> host rm -rf /u01/app/oracle/oradata/rev1/control01.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/rev1/control02.ctl

SQL> ! rm -rf /u01/app/oracle/oradata/rev1/control03.ctl

SQL> shutdown immediate

SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             167772864 bytes
Database Buffers          272629760 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

run the create_ctl.sql script to recreate a lost control files

SQL> @/home/oracle/Desktop/create_ctl.sql

Control file created.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/rev1/system01.dbf'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 557988 generated at 09/21/2010 16:13:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_4_%u_.arc
ORA-00280: change 557988 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_4_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_4_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/rev1/system01.dbf'

apply the current online redolog files for recover the database here in our example the current
logfile is redo02.log

SQL> col member format a50
SQL> select a.group#,a.member,b.status from v$logfile a, v$log b where a.group#=b.group#;

  GROUP# MEMBER                                             STATUS
---------- -------------------------------------------------- ----------------
       3 /u01/app/oracle/oradata/rev1/redo03.log            ACTIVE
       2 /u01/app/oracle/oradata/rev1/redo02.log            CURRENT
       1 /u01/app/oracle/oradata/rev1/redo01.log            ACTIVE

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 557988 generated at 09/21/2010 16:13:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_09_21/o1_mf_1_4_%u_.arc
ORA-00280: change 557988 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/rev1/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1

hope, this will helps you

SYSAUX datafile or tablespace corrupted or lost no backup available

Datafiles of the sysaux tablespace are not as critical as datafiles in the system tablespace but they are part of the Oracle configuration. Anyway you should never delete any datafile directly from the OS.
rman target /

report schema; -- identify number of your datafile
sql 'alter database datafile ? offline';
sql 'alter database open';


You should be able to open the database but you could have problems (depends on Oracle options you use)... If you don't want to experience/solve them export what you need and import in another database.

you can create a tablespace then :

alter user ??? default tablespace ???;


You should also give him quota on this tablespace

alter user ??? quota unlimited on ???;

Replace ??? by adequate values (username , tablespace name)

SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             197132992 bytes
Database Buffers          243269632 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             201327296 bytes
Database Buffers          239075328 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/jay/sysaux01.dbf'

[oracle@rac1 ~]$ rlrman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Sep 2 11:45:07 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: JAY (DBID=3148849783, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    480      SYSTEM               ***     /u01/app/oracle/oradata/jay/system01.dbf
2    25       UNDOTBS1             ***     /u01/app/oracle/oradata/jay/undotbs01.dbf
3    0        SYSAUX               ***     /u01/app/oracle/oradata/jay/sysaux01.dbf
4    5        USERS                ***     /u01/app/oracle/oradata/jay/users01.dbf
5    5        SAMPLE               ***     /u01/app/oracle/oradata/jay/sam01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    5        TEMP                 5           /u01/app/oracle/oradata/jay/newtemp1.dbf

RMAN> sql 'alter database datafile 3 offline';

sql statement: alter database datafile 3 offline

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>



SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
jay              OPEN

SQL> select name,status from v$datafile;


NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/jay/system01.dbf           SYSTEM
/u01/app/oracle/oradata/jay/undotbs01.dbf          ONLINE
/u01/app/oracle/oradata/jay/sysaux01.dbf           OFFLINE
/u01/app/oracle/oradata/jay/users01.dbf            ONLINE
/u01/app/oracle/oradata/jay/sam01.dbf              ONLINE


SQL> select username from dba_users
  2  where default_tablespace='SYSAUX';

USERNAME
------------------------------
SYSMAN
DBSNMP
OLAPSYS
SI_INFORMTN_SCHEMA
ORDPLUGINS
XDB
WMSYS
DMSYS
EXFSYS
ANONYMOUS
CTXSYS

USERNAME
------------------------------
ORDSYS
MDSYS
RAJESH

14 rows selected.


SQL> ALTER USER RAJESH DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

User altered.

reference: from otn