create a text file for control file ..
SQL> alter database backup controlfile to trace as '/tmp/control.trc';
Database altered.
or
SQL> alter database backup controlfile to trace ;
Database altered.
SQL> show parameter diag
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
diagnostic_dest string /u01/app/oracle
will be in trace dir <<<<<<<
/u01/app/oracle/diag/rdbms/vihaan/vihaan/trace
-rw-r----- 1 oracle asmadmin 6294 May 26 16:04 vihaan_ora_5960.trc <<<<<<<<<<<----
-rw-r----- 1 oracle asmadmin 418532 May 26 16:04 alert_vihaan.log
SQL> create table test as select sysdate sdate from dual ;
Table created.
SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
Session altered.
SQL> select * from test ;
SDATE
-------------------
26-05-2014 16:17:16
SQL>ALTER SYSTEM ARCHIVE LOG CURRENT
SQL>alter system switch logfile;
SQL> select * from test ;
SDATE
-------------------
26-05-2014 16:17:16
26-05-2014 16:19:46
26-05-2014 16:19:48
26-05-2014 16:19:49
ASMCMD [+DATA/VIHAAN/CONTROLFILE] > ls -s
Block_Size Blocks Bytes Space Name
16384 615 10076160 16777216 current.256.848518731
16384 615 10076160 16777216 current.284.848518731
deleted all the control file ....
ASMCMD [+DATA/VIHAAN/CONTROLFILE] > rm current.256.848518731 current.284.848518731
***************************************************************************
CREATE CONTROLFILE REUSE DATABASE "VIHAAN" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/vihaan/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA/vihaan/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '+DATA/vihaan/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA/vihaan/system01.dbf',
'+DATA/vihaan/sysaux01.dbf',
'+DATA/vihaan/undotbs01.dbf',
'+DATA/vihaan/users01.dbf'
CHARACTER SET WE8MSWIN1252 ;
******************************************************************************
SQL> CREATE CONTROLFILE REUSE DATABASE "VIHAAN" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '+DATA/vihaan/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '+DATA/vihaan/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '+DATA/vihaan/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '+DATA/vihaan/system01.dbf',
14 '+DATA/vihaan/sysaux01.dbf',
15 '+DATA/vihaan/undotbs01.dbf',
'+DATA/vihaan/users01.dbf'
CHARACTER SET WE8MSWIN1252 ;
Control file created.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Disabled
Archive destination +DATA/vihaan/archivelog
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 963360 generated at 05/26/2014 16:24:15 needed for thread 1
ORA-00289: suggestion : +DATA/vihaan/archivelog/1_4_848518930.dbf
ORA-00280: change 963360 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
SQL> alter database open RESETLOGS ;
Database altered.
SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
Session altered.
SQL> select * from test ;
SDATE
-------------------
26-05-2014 16:17:16
26-05-2014 16:19:46
26-05-2014 16:19:48
26-05-2014 16:19:49
