Recover a Database when all Control Files and all Redo Log Files are Lost and Database is in no archive log mode and there is no backup

Here we have to create a scenario where we lost all control files and redo log files and there is no backup. We only have data files and initialization file. The database is in no archive log mode.
The steps followed to recover the database: 

Step 1. Login to database and shutdown it.

[oracle@linux1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 17 03:15:59 2015

Copyright (c) 1982, 2013, Oracle.  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

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step 2. Let have delete all redolog files and control files at OS level.

[oracle@linux1 db1]$ ll
total 2076740
-rw-r-----  1 oracle oinstall  10076160 Jan 17 03:16 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Jan 17 03:16 example01.dbf
-rw-r-----  1 oracle oinstall  52429312 Jan 17 03:16 redo01.log
-rw-r-----  1 oracle oinstall  52429312 Jan 17 03:04 redo02.log
-rw-r-----  1 oracle oinstall  52429312 Jan 17 03:04 redo03.log
-rw-r-----. 1 oracle oinstall  52436992 Jan 17 03:16 srcdata01.dbf
-rw-r-----. 1 oracle oinstall 587210752 Jan 17 03:16 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 807411712 Jan 17 03:16 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jan 17 02:41 temp01.dbf
-rw-r-----. 1 oracle oinstall  52436992 Jan 17 03:16 trgdata01.dbf
-rw-r-----. 1 oracle oinstall  68165632 Jan 17 03:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   9183232 Jan 17 03:16 users01.dbf
[oracle@linux1 db1]$ rm redo0*
[oracle@linux1 db1]$ ll
total 1923128
-rw-r-----  1 oracle oinstall  10076160 Jan 17 03:16 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Jan 17 03:16 example01.dbf
-rw-r-----. 1 oracle oinstall  52436992 Jan 17 03:16 srcdata01.dbf
-rw-r-----. 1 oracle oinstall 587210752 Jan 17 03:16 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 807411712 Jan 17 03:16 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jan 17 02:41 temp01.dbf
-rw-r-----. 1 oracle oinstall  52436992 Jan 17 03:16 trgdata01.dbf
-rw-r-----. 1 oracle oinstall  68165632 Jan 17 03:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   9183232 Jan 17 03:16 users01.dbf
[oracle@linux1 db1]$ pwd
/u01/app/oracle/oradata/db1
[oracle@linux1 db1]$ rm control01.ctl
[oracle@linux1 db1]$ ll
total 1913288
-rw-r-----. 1 oracle oinstall 362422272 Jan 17 03:16 example01.dbf
-rw-r-----. 1 oracle oinstall  52436992 Jan 17 03:16 srcdata01.dbf
-rw-r-----. 1 oracle oinstall 587210752 Jan 17 03:16 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 807411712 Jan 17 03:16 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jan 17 02:41 temp01.dbf
-rw-r-----. 1 oracle oinstall  52436992 Jan 17 03:16 trgdata01.dbf
-rw-r-----. 1 oracle oinstall  68165632 Jan 17 03:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   9183232 Jan 17 03:16 users01.dbf

[oracle@linux1 db1]$

[oracle@linux1 db1]$ cd ../..

[oracle@linux1 oracle]$ cd fast_recovery_area/db1/

[oracle@linux1 db1]$ ll

total 19360

-rw-r----- 1 oracle oinstall  9748480 Jan 17 02:41 __control02.ctl

-rw-r----- 1 oracle oinstall 10076160 Jan 17 03:16 control02.ctl

[oracle@linux1 db1]$ rm control02.ctl

[oracle@linux1 db1]$ ll

total 9520

-rw-r----- 1 oracle oinstall 9748480 Jan 17 02:41 __control02.ctl

[oracle@linux1 db1]$ rm __control02.ctl

[oracle@linux1 db1]$ ll

total 0

[oracle@linux1 db1]$

Step 3. Now login again and and startup database.

[oracle@linux1 db1]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 17 03:23:49 2015

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  413372416 bytes

Fixed Size                  2253784 bytes

Variable Size             314575912 bytes

Database Buffers           88080384 bytes

Redo Buffers                8462336 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL>

Step 4. Alert log giving following error that control files missing.

ALTER DATABASE   MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/db1/control02.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/db1/control01.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE   MOUNT...

Step 5. Now again shutdown the database and startup the database in nomount mode.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  413372416 bytes

Fixed Size                  2253784 bytes

Variable Size             314575912 bytes

Database Buffers           88080384 bytes

Redo Buffers                8462336 bytes

SQL>

Step 6. Run following command to create control file.

SQL> CREATE CONTROLFILE REUSE DATABASE "DB1" RESETLOGS NOARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

    MAXDATAFILES 100

  4      MAXINSTANCES 8

  5      MAXLOGHISTORY 292

  6  LOGFILE

  7    GROUP 1 '/u01/app/oracle/oradata/db1/redo01.log'  SIZE 50M,

  8    GROUP 2 '/u01/app/oracle/oradata/db1/redo02.log'  SIZE 50M,  ,

  9    GROUP 3 '/u01/app/oracle/oradata/db1/redo03.log'  SIZE 50M,

 10  DATAFILE

 11   12    '/u01/app/oracle/oradata/db1/example01.dbf',

 13    '/u01/app/oracle/oradata/db1/srcdata01.dbf',

 14    '/u01/app/oracle/oradata/db1/sysaux01.dbf',

 15    '/u01/app/oracle/oradata/db1/system01.dbf',

 16    --'/u01/app/oracle/oradata/db1/temp01.dbf',

 17    '/u01/app/oracle/oradata/db1/trgdata01.dbf',

 18    '/u01/app/oracle/oradata/db1/undotbs01.dbf',

 19    '/u01/app/oracle/oradata/db1/users01.dbf'

 20  CHARACTER SET WE8MSWIN1252;

Control file created.

SQL>

SQL> alter database open resetlogs;

Database altered.

SQL>

You can see that all control files and redo log files has been created.

/u01/app/oracle/fast_recovery_area/db1

[oracle@linux1 db1]$ ll

total 9840

-rw-r----- 1 oracle oinstall 10076160 Jan 17 03:35 control02.ctl

[oracle@linux1 db1]$

[oracle@linux1 db1]$ cd ../..

[oracle@linux1 oracle]$ cd oradata/db1/

[oracle@linux1 db1]$ ll

total 2076740

-rw-r-----  1 oracle oinstall  10076160 Jan 17 03:36 control01.ctl

-rw-r-----. 1 oracle oinstall 362422272 Jan 17 03:34 example01.dbf

-rw-r-----  1 oracle oinstall  52429312 Jan 17 03:36 redo01.log

-rw-r-----  1 oracle oinstall  52429312 Jan 17 03:34 redo02.log

-rw-r-----  1 oracle oinstall  52429312 Jan 17 03:34 redo03.log

-rw-r-----. 1 oracle oinstall  52436992 Jan 17 03:34 srcdata01.dbf

-rw-r-----. 1 oracle oinstall 587210752 Jan 17 03:34 sysaux01.dbf

-rw-r-----. 1 oracle oinstall 807411712 Jan 17 03:34 system01.dbf

-rw-r-----. 1 oracle oinstall  20979712 Jan 17 02:41 temp01.dbf

-rw-r-----. 1 oracle oinstall  52436992 Jan 17 03:34 trgdata01.dbf

-rw-r-----. 1 oracle oinstall  68165632 Jan 17 03:34 undotbs01.dbf

-rw-r-----. 1 oracle oinstall   9183232 Jan 17 03:34 users01.dbf

[oracle@linux1 db1]$

The database opened successfully. The only point to consider is that of the data loss the data in the redo log files,

Dataguard Configuration from RAC to RAC database

Primary Site:
Physical IPs: 192.168.1.1 , 192.168.1.2
VIPs:            192.168.1.3 , 192.168.1.4
DB_Unique_Name: racdb

Standby Site:
Physical IPs: 192.168.2.1 , 192.168.2.2
VIPs:            192.168.2.3 , 192.168.2.4
DB_Unique_Name: racdr

##########################################
Steps on Production/Primary Server

1- Modify or add dataguard related init Parameters on production:

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb,racdr)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb' scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=racdr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdr' scope=both sid='*';


alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
alter system set fal_server=racdr scope=both sid='*';   ----FAL server (typically this is the database running in the primary role).
alter system set FAL_Client='racdb' scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set db_file_name_convert='+DATA/racdb/','+DATA/racdr/' scope=spfile sid='*';
alter system set log_file_name_convert='+DATA/racdb/','+DATA/racdr/' scope=spfile sid='*';


2- Enable Archivelog Mode:

srvctl stop database -d racdb
startup mount                      ----(only one instance)
alter database archivelog;
alter database force logging;
alter database open;
srvctl start database -d racdb  ----This will start the remaining Instances on the cluster.


3- Tns Entries on all servers (copy these entires on both nodes of standby server),
Note that add new entries with VIP instead of scan name:

racdb =
  (DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=ON)
    (ADDRESS= (PROTOCOL=TCP)(HOST=192.168.1.3)(PORT=1521))
    (ADDRESS= (PROTOCOL=TCP)(HOST=192.168.1.4)(PORT=1521))
    (CONNECT_DATA= (SERVER = DEDICATED)(FAILOVER_MODE=(TYPE=select)(METHOD=basic))
      (SERVICE_NAME=racdb)))


racdr =
  (DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=ON)
    (ADDRESS= (PROTOCOL=TCP)(HOST=192.168.2.3)(PORT=1521))
    (ADDRESS= (PROTOCOL=TCP)(HOST=192.168.2.4)(PORT=1521))
    (CONNECT_DATA= (SERVER = DEDICATED)(FAILOVER_MODE=(TYPE=select)(METHOD=basic))
      (SERVICE_NAME=racdr)))


4- Create the Standby Redo Logs (SRLs) on Primary and Standby:
alter system set standby_file_management=manual scope=both sid='*';

alter database add standby logfile thread 1 group 1 '+DATA/racdb/onlinelog/standbyredo1.log' size 52M;
alter database add standby logfile thread 2 group 1 '+DATA/racdb/onlinelog/standbyredo2.log' size 52M;
alter database add standby logfile thread 1 group 2 '+DATA/racdb/onlinelog/standbyredo3.log' size 52M;
alter database add standby logfile thread 2 group 2 '+DATA/racdb/onlinelog/standbyredo4.log' size 52M;
alter database add standby logfile thread 1 group 3 '+DATA/racdb/onlinelog/standbyredo5.log' size 52M;
alter database add standby logfile thread 2 group 3 '+DATA/racdb/onlinelog/standbyredo6.log' size 52M;

alter system set standby_file_management=auto scope=both sid='*';

5- Copy password file to standby nodes.
scp $ORACLE_HOME/dbs/orapwracdb1 oracle@192.168.2.1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs

scp $ORACLE_HOME/dbs/orapwracdb1 oracle@192.168.2.2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs

created password file on production and copied and renamed it with standby database name(orapwracdr1) on both nodes. we do not need to create password file in the standby server.

6- Backup Primary DB:
rman target /
sql "alter system switch logfile";
BACKUP AS COMPRESSED BACKUPSET DATABASE format '/u01/app/oracle/backup/rman_%n_%T_%s_%p.bk' tag 'FORSTANDBY';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u01/app/oracle/backup/ForStandbyCTRL.bck';

7- Compressed the backup folder:
tar -zcvf backup.tar.gz /u01/app/oracle/backup

8- Copy Compressed backup directory to Standby:
scp -r backup.tar.gz oracle@192.168.2.1:/u01/app/oracle/

Steps on Standby Server

9- Modify/add follwoing parameters on Standby site:
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb,racdr)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb' scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=racdr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdr' scope=both sid='*';


alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
alter system set fal_server=racdb scope=both sid='*';
alter system set FAL_Client='racdr' scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set db_file_name_convert='+DATA/racdb/','+DATA/racdr/' scope=spfile sid='*';
alter system set log_file_name_convert='+DATA/racdb/','+DATA/racdr/' scope=spfile sid='*';

9- Un-compressed the backup folder:
tar -zxvf backup.tar.gz -C /u01/app/oracle/

10- Start Standby in no mount mode:
srvctl stop database -d racdr
startup nomount   -----one instance

11- Connect to rman on standby server and restore the backup:
rman target /

11.1- Restore controlfile and mount the database:
restore standby controlfile from '/u01/app/oracle/backup/ForStandbyCTRL.bck';
sql 'alter database mount standby database';

11.2- Now restore the database backup:
run {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK ;
restore database;
switch datafile all ;
}


12-  Open the database in read only mode:
alter database open read only;

13-  Start active recovery on standby database:
recover managed standby database cancel;   ---if media recovery already running
recover managed standby database disconnect using current logfile;

14- Check dataguard status
select status, error from v$archive_dest where dest_id=2;
select name,open_mode,db_unique_name,database_role,protection_mode from v$database;

15- On the primary server, check the latest archived redo log and force a log switch:
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;
ALTER SYSTEM SWITCH LOGFILE;

16- Check the new archived redo log has arrived at the standby server and been applied:
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SELECT sequence#, to_char(first_time,'dd-mon-yy hh:mi:ss am'), next_time, applied FROM v$archived_log ORDER BY sequence#;

Restore RMAN Backup on other Machine having Different Physical Structure/database migration

Restore RMAN Backup on other Machine having Different Physical Structure:

following documentation is a step by step guide and can be used for to restore rman backup on other machine with different directories structure or database migration from one server to other or database version migration from 64/32 bit to 32/64 bit.

Source machine:

OS: Windows Server 2012

Database: Oracle 11.2.0.2.0

Target machine:

OS: Windows Server 2012

Database: Oracle 11.2.0.2.0

-------------------------------------------------------------------------------

Steps:

1.1- Create Required Directories:

Create required directories on target machine.

1.2- Copy Pfile from source:

create pfile='d"\pfile.ora' from spfile;

1.3- Copy this pfile to target and start database in nomount state from this pfile:

startup nomount pfile='d'\pfile.ora';

1.4- Set DBID:

set dbid=1275888

1.5- Restore control File:

restore controlfile from 'D:\test\control_ORCL_2m9KC0e_92';

1.6- Mount Database:

alter database mount;

1.7- Generating the SET NEWNAME command:

Generating the SET NEWNAME command for each tempfile in the database using below script:

select 'set newname for datafile '||file_id||' to '''||'+DATA'||''';' from dba_data_files;

1.8- RMAN Block to rename Datafiles and restore database:

RUN

{

SET NEWNAME FOR DATAFILE 1 TO 'D:\app\Administrator\oradata\ORCL\SYSTEM01.DBF';

SET NEWNAME FOR DATAFILE 2 TO 'D:\app\Administrator\oradata\ORCL\SYSAUX01.DBF';

SET NEWNAME FOR DATAFILE 3 TO 'D:\app\Administrator\oradata\ORCL\UNDOTBS01.DBF';

SET NEWNAME FOR DATAFILE 4 TO 'D:\app\Administrator\oradata\ORCL\USERS01.DBF';

SET NEWNAME FOR DATAFILE 5 TO 'D:\app\Administrator\oradata\ORCL\EXAMPLE01.DBF';

SET NEWNAME FOR DATAFILE 6 TO 'D:\app\Administrator\oradata\ORCL\APP_USER1.DBF';

SET NEWNAME FOR DATAFILE 7 TO 'D:\app\Administrator\oradata\ORCL\APP_USER2.DBF';

SET NEWNAME FOR DATAFILE 8 TO 'D:\app\Administrator\oradata\ORCL\APP_USERS_INDEXES.DBF';

SQL "ALTER DATABASE RENAME FILE ''S:\app\Administrator\oradata\ORCL\redo01.log''

      TO ''D:\app\Administrator\oradata\ORCL\redo01.log'' ";

SQL "ALTER DATABASE RENAME FILE ''S:\app\Administrator\oradata\ORCL\redo02.log''

      TO ''D:\app\Administrator\oradata\ORCL\redo02.log'' ";

SQL "ALTER DATABASE RENAME FILE ''S:\app\Administrator\oradata\ORCL\redo03.log''

      TO ''D:\app\Administrator\oradata\ORCL\redo03.log'' ";

  # Do a SET UNTIL to prevent recovery of the online logs

  #set until time "to_date('2011-13-04:11:40:00', 'yyyy-dd-mm:hh24:mi:ss')";

  # restore the database and switch the datafile names

  RESTORE DATABASE;

  SWITCH DATAFILE ALL;

  # recover the database

  RECOVER DATABASE;

}

----------------------------------Output of RMAN command-------------------

RMAN-00571: ========================================

RMAN-00569: ==== ERROR MESSAGE STACK FOLLOWS =====

RMAN-00571: ========================================

RMAN-03002: failure of recover command at 01/25/2004 15:59:41

RMAN-06054: media recovery requesting unknown log: thread 1 scn 140830

-------------------------------------------------------------------------------

1.9- Resolving of this issue:

First shutdown the database.

shu immediate;

startup database;

then open database with resetlogs

alter database open resetlogs;

Your database successfully migrated from one machine to other with different directories structure.

-------------------------------------------------------------------------------

Convert DB from 64Bit to 32Bit or vice versa

If you want to migrate from a 64/32 bit machine to 32/64 bit then follow all above steps and also use following extra steps

-------------------------------------------------------------------------------

 2.1- Resoloving virsion problem:

SHUTDOWN IMMEDIATE;

2.2- Startup database with upgrade option

startup upgrade;

2.3- Now run do following steps

1) startup upgrade;

2) @?/rdbms/admin/utlirp.sql

3) shutdown immediate;

4) startup;

5) drop user QCSITEADMIN_DB0 cascade;

6) shutdown immediate;

7) startup upgrade;

8) @?/rdbms/admin/utlirp.sql

9) shutdown immediate;

10) startup;

 Now database successfully migrated between different version.

Convert and Migrate Single Instance to RAC

For getting detail documentation and step by step guide please go to following links.

http://allthingsoracle.com/convert-single-instance-to-rac-part-1-duplicate-db-using-rman/

http://allthingsoracle.com/convert-single-instance-to-rac-part-2-manually-convert-to-rac/