Create ASM Standby Database for a NON-ASM Primary Database in 11gR2

Create ASM Standby Database for a NON-ASM Primary Database in 11g

In this post I'll create a ASM Physical Database on a different host for a NON-ASM Primary database.
Here:-

Database_Type     Database_Name      Host   
Primary                  db1                         prim.oracle.com
Standby                 sdb1                        stby.oracle.com

I'll follow the below step to configure the same.

1. Take the full backup of Production database including archives as well.

RMAN> backup database format '/u01/bkp/db_%U.bkp';

Starting backup at 10-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0op09ghd_1_1.bkp tag=TAG20140210T153053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0pp09gkd_1_1.bkp tag=TAG20140210T153053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14

RMAN> backup archivelog all format '/u01/bkp/arc_%U.bkp';

Starting backup at 10-FEB-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=7 STAMP=838829575
input archived log thread=1 sequence=10 RECID=8 STAMP=838884652
input archived log thread=1 sequence=11 RECID=9 STAMP=838901252
input archived log thread=1 sequence=12 RECID=10 STAMP=838901331
input archived log thread=1 sequence=13 RECID=11 STAMP=838905416
input archived log thread=1 sequence=14 RECID=12 STAMP=838939737
input archived log thread=1 sequence=15 RECID=13 STAMP=838978232
input archived log thread=1 sequence=16 RECID=14 STAMP=839014248
input archived log thread=1 sequence=17 RECID=15 STAMP=839052021
input archived log thread=1 sequence=18 RECID=16 STAMP=839095566
input archived log thread=1 sequence=19 RECID=17 STAMP=839140243
input archived log thread=1 sequence=20 RECID=18 STAMP=839161908
input archived log thread=1 sequence=21 RECID=19 STAMP=839162150
input archived log thread=1 sequence=22 RECID=20 STAMP=839172779
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/arc_0qp09glc_1_1.bkp tag=TAG20140210T153300 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-FEB-14

RMAN> backup current controlfile for standby format '/u01/bkp/control_%U.ctl';

Starting backup at 10-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/control_0rp09goe_1_1.ctl tag=TAG20140210T153438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14

Step 2.

Copy the backups to the standby server.

[oracle@prim bkp]$ scp -r * oracle@stby:/u01/bkp/

oracle@stby's password:

Permission denied, please try again.

oracle@stby's password:

arc_0qp09glc_1_1.bkp                          100%  269MB  17.9MB/s   00:15

control_0rp09goe_1_1.ctl                      100% 9568KB   9.3MB/s   00:01

db_0op09ghd_1_1.bkp                           100% 1072MB  13.6MB/s   01:19

db_0pp09gkd_1_1.bkp                           100% 9600KB   9.4MB/s   00:00

Step 3. 

Make an entry for both the database servers in the tnsnames.ora file and the file should be similar on both the host.

[oracle@prim dbs]$ more /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora

DB1 =

  (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx )(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db1)

    )

  )

SDB1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = db1)

    )

  )

listener file of db1.

[oracle@prim dbs]$ more /u01/app/oracle/product/11.2.0/db_2/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = db1)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Listener file of sdb1

[oracle@stby dbs]$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.xx.xx.xx )(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = db1)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Step 4.

Create the ora password file on the primary database and copy the same to the standby database server $ORACLE_HOME/dbs.

[oracle@prim dbs]$ orapwd file=orapwdb1 password=oracle force=y

[oracle@prim dbs]$ scp orapwdb1 oracle@stby:/u01/app/oracle/product/11.2.0/db_1/dbs

oracle@stby's password:

orapwdb1                                                                                                                   100% 1536     1.5KB/s   00:00

Step 5.

Copy the parameter file from Primary to Standby database host and change the parameter file accordingly to standby .

db1.__db_cache_size=687865856

db1.__java_pool_size=16777216

db1.__large_pool_size=16777216

db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

db1.__pga_aggregate_target=687865856

db1.__sga_target=1006632960

db1.__shared_io_pool_size=0

db1.__shared_pool_size=268435456

db1.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/db1/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA','+DATA'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain='oracle.com'

*.db_name='db1'

*.db_unique_name='sdb1'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=2147483648

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'

*.fal_client='DB1'

*.fal_server='SDB1'

*.log_archive_config='DG_CONFIG=(db1,sdb1)'

*.log_archive_dest_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sdb1'

*.log_archive_dest_2='SERVICE=db1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=db1'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

Parameter file of Primary database.

[oracle@prim dbs]$ more initdb1.ora

db1.__db_cache_size=687865856

db1.__java_pool_size=16777216

db1.__large_pool_size=16777216

db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

db1.__pga_aggregate_target=687865856

db1.__sga_target=1006632960

db1.__shared_io_pool_size=0

db1.__shared_pool_size=268435456

db1.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/db1/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/db1/control01.ctl','/u01/app/oracle/fast_recovery_area/db1/control02.ctl'

*.db_block_size=8192

*.db_domain='oracle.com'

*.db_name='db1'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'

#*.local_listener='LISTENER'

*.memory_target=1694498816

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.fal_client='DB1'

*.fal_server='SDB1'

*.log_archive_config='DG_CONFIG=(db1,sdb1)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/DB1/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db1'

*.log_archive_dest_2='SERVICE=sdb1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=sdb1'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

Step 6.

Start the Standby database in nomount mode using the new parameter file.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initsdb1.ora';

ORACLE instance started.

Total System Global Area  171581440 bytes

Fixed Size                  1343668 bytes

Variable Size             117444428 bytes

Database Buffers           50331648 bytes

Redo Buffers                2461696 bytes

Database mounted.

Step 7.

Restore the control file using the standby control file backup.

RMAN> restore standby controlfile from '/u01/bkp/control_0rp09goe_1_1.ctl';

Starting restore at 10-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=+DATA/sdb1/controlfile/current.262.839184505

output file name=+DATA/sdb1/controlfile/current.263.839184509

Finished restore at 10-FEB-14

Step 8. Bounce the database and update the parameter file with the control file information.

*.control_files='+DATA/sdb1/controlfile/current.262.839184505','+DATA/sdb1/controlfile/current.263.839184509'

Step 9.

Start the standby database in mount mode and perform the restoration process of the database.

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initsdb1.ora';

ORACLE instance started.

Total System Global Area  171581440 bytes

Fixed Size                  1343668 bytes

Variable Size             117444428 bytes

Database Buffers           50331648 bytes

Redo Buffers                2461696 bytes

Database mounted.

SQL> host rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 10 18:51:22 2014

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

connected to target database: DB1 (DBID=1432532444, not open)

RMAN> run

{

set newname for datafile 1 to '+DATA';

set newname for datafile 2 to '+DATA';

set newname for datafile 3 to '+DATA';

set newname for datafile 4 to '+DATA';

set newname for datafile 5 to '+DATA';

set newname for tempfile 1 to '+DATA';

restore database;

switch datafile all;

switch tempfile all;

}

2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 10-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=33 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DATA

channel ORA_DISK_1: restoring datafile 00002 to +DATA

channel ORA_DISK_1: restoring datafile 00003 to +DATA

channel ORA_DISK_1: restoring datafile 00004 to +DATA

channel ORA_DISK_1: restoring datafile 00005 to +DATA

channel ORA_DISK_1: reading from backup piece /u01/bkp/db_0op09ghd_1_1.bkp

channel ORA_DISK_1: piece handle=/u01/bkp/db_0op09ghd_1_1.bkp tag=TAG20140210T153053

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:56

Finished restore at 10-FEB-14

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=839184818 file name=+DATA/sdb1/datafile/system.259.839184701

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=839184818 file name=+DATA/sdb1/datafile/sysaux.257.839184703

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=839184818 file name=+DATA/sdb1/datafile/undotbs1.260.839184705

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=839184818 file name=+DATA/sdb1/datafile/users.261.839184705

datafile 5 switched to datafile copy

input datafile copy RECID=10 STAMP=839184819 file name=+DATA/sdb1/datafile/test.258.839184703

renamed tempfile 1 to +DATA in control file

RMAN> list backup of archivelog all;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

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

1       137.97M    DISK        00:00:07     06-FEB-14

        BP Key: 1   Status: EXPIRED  Compressed: NO  Tag: TAG20140206T161117

        Piece Name: /u01/bkp/01ovv1d5_1_1.bkp

  List of Archived Logs in backup set 1

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    3       791516     04-FEB-14 816435     05-FEB-14

  1    4       816435     05-FEB-14 858512     05-FEB-14

  1    5       858512     05-FEB-14 859840     05-FEB-14

  1    6       859840     05-FEB-14 859843     05-FEB-14

  1    7       859843     05-FEB-14 869252     05-FEB-14

  1    8       869252     05-FEB-14 898900     06-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

4       6.50K      DISK        00:00:00     06-FEB-14

        BP Key: 4   Status: EXPIRED  Compressed: NO  Tag: TAG20140206T161256

        Piece Name: /u01/bkp/04ovv1g8_1_1.bkp

  List of Archived Logs in backup set 4

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    9       898900     06-FEB-14 898952     06-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

7       177.34M    DISK        00:00:07     07-FEB-14

        BP Key: 7   Status: EXPIRED  Compressed: NO  Tag: TAG20140207T120732

        Piece Name: /u01/bkp/07p017g5_1_1.bkp

  List of Archived Logs in backup set 7

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    3       791516     04-FEB-14 816435     05-FEB-14

  1    4       816435     05-FEB-14 858512     05-FEB-14

  1    5       858512     05-FEB-14 859840     05-FEB-14

  1    6       859840     05-FEB-14 859843     05-FEB-14

  1    7       859843     05-FEB-14 869252     05-FEB-14

  1    8       869252     05-FEB-14 898900     06-FEB-14

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

10      3.00K      DISK        00:00:00     07-FEB-14

        BP Key: 10   Status: EXPIRED  Compressed: NO  Tag: TAG20140207T120851

        Piece Name: /u01/bkp/0ap017ij_1_1.bkp

  List of Archived Logs in backup set 10

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    12      933143     07-FEB-14 933178     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

14      178.69M    DISK        00:00:09     07-FEB-14

        BP Key: 14   Status: EXPIRED  Compressed: NO  Tag: TAG20140207T131656

        Piece Name: /u01/bkp/archivelog_0fp01bi8_1_1.bkp

  List of Archived Logs in backup set 14

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    3       791516     04-FEB-14 816435     05-FEB-14

  1    4       816435     05-FEB-14 858512     05-FEB-14

  1    5       858512     05-FEB-14 859840     05-FEB-14

  1    6       859840     05-FEB-14 859843     05-FEB-14

  1    7       859843     05-FEB-14 869252     05-FEB-14

  1    8       869252     05-FEB-14 898900     06-FEB-14

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

  1    12      933143     07-FEB-14 933178     07-FEB-14

  1    13      933178     07-FEB-14 934988     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

21      263.13M    DISK        00:00:17     10-FEB-14

        BP Key: 21   Status: EXPIRED  Compressed: NO  Tag: TAG20140210T123550

        Piece Name: /u01/bkp/arc_0mp09696_1_1.bkp

  List of Archived Logs in backup set 21

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

  1    12      933143     07-FEB-14 933178     07-FEB-14

  1    13      933178     07-FEB-14 934988     07-FEB-14

  1    14      934988     07-FEB-14 953914     07-FEB-14

  1    15      953914     07-FEB-14 974151     08-FEB-14

  1    16      974151     08-FEB-14 993762     08-FEB-14

  1    17      993762     08-FEB-14 1016061    09-FEB-14

  1    18      1016061    09-FEB-14 1039552    09-FEB-14

  1    19      1039552    09-FEB-14 1062286    10-FEB-14

  1    20      1062286    10-FEB-14 1090918    10-FEB-14

  1    21      1090918    10-FEB-14 1091253    10-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

25      268.79M    DISK        00:00:18     10-FEB-14

        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20140210T153300

        Piece Name: /u01/bkp/arc_0qp09glc_1_1.bkp

  List of Archived Logs in backup set 25

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

  1    12      933143     07-FEB-14 933178     07-FEB-14

  1    13      933178     07-FEB-14 934988     07-FEB-14

  1    14      934988     07-FEB-14 953914     07-FEB-14

  1    15      953914     07-FEB-14 974151     08-FEB-14

  1    16      974151     08-FEB-14 993762     08-FEB-14

  1    17      993762     08-FEB-14 1016061    09-FEB-14

  1    18      1016061    09-FEB-14 1039552    09-FEB-14

  1    19      1039552    09-FEB-14 1062286    10-FEB-14

  1    20      1062286    10-FEB-14 1090918    10-FEB-14

  1    21      1090918    10-FEB-14 1091253    10-FEB-14

  1    22      1091253    10-FEB-14 1096401    10-FEB-14

Step 10.

Perform the recover of database.

RMAN> run

2> {

3> set until sequence 22 thread 1;

4> recover database;

5> }

executing command: SET until clause

Starting recover at 11-FEB-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=42 device type=DISK

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

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

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

RMAN-03002: failure of recover command at 02/11/2014 12:36:18

RMAN-06556: datafile 1 must be restored from backup older than SCN 1091253

Then I looked into the Oracle community and found the similar case and the solution given by user 491476 worked here.

https://community.oracle.com/thread/646983?start=0&tstart=0

SQL> select group#, first_change#, status, archived from v$log;

    GROUP# FIRST_CHANGE# STATUS           ARC

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

         1          1161769                  CLEARING         YES

         3          1090918                  INACTIVE         YES

         2          1175084                  CURRENT          YES

RMAN> run

2> {

3> set until sequence 1175084 thread 1;

4> recover database;

5> }

executing command: SET until clause

Starting recover at 11-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=43 device type=DISK

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_23.264.839248363

archived log for thread 1 with sequence 24 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_24.276.839248363

archived log for thread 1 with sequence 25 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_25.265.839248361

archived log for thread 1 with sequence 26 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_26.275.839248367

archived log for thread 1 with sequence 27 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_27.274.839248369

archived log for thread 1 with sequence 28 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_28.273.839248369

archived log for thread 1 with sequence 29 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_29.272.839248371

archived log for thread 1 with sequence 30 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_30.283.839248371

archived log for thread 1 with sequence 31 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_31.281.839248371

archived log for thread 1 with sequence 32 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_32.289.839248373

archived log for thread 1 with sequence 33 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_33.288.839248373

archived log for thread 1 with sequence 34 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_34.287.839248375

archived log for thread 1 with sequence 35 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_35.286.839248375

archived log for thread 1 with sequence 36 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_36.284.839248375

archived log for thread 1 with sequence 37 is already on disk as file +DATA/sdb1/archivelog/2014_02_11/thread_1_seq_37.282.839248379

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=22

channel ORA_DISK_1: reading from backup piece /u01/bkp/arc_0qp09glc_1_1.bkp

channel ORA_DISK_1: piece handle=/u01/bkp/arc_0qp09glc_1_1.bkp tag=TAG20140210T153300

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_22.280.839249075 thread=1 sequence=22

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_23.264.839248363 thread=1 sequence=23

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_24.276.839248363 thread=1 sequence=24

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_25.265.839248361 thread=1 sequence=25

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_26.275.839248367 thread=1 sequence=26

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_27.274.839248369 thread=1 sequence=27

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_28.273.839248369 thread=1 sequence=28

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_29.272.839248371 thread=1 sequence=29

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_30.283.839248371 thread=1 sequence=30

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_31.281.839248371 thread=1 sequence=31

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_32.289.839248373 thread=1 sequence=32

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_33.288.839248373 thread=1 sequence=33

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_34.287.839248375 thread=1 sequence=34

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_35.286.839248375 thread=1 sequence=35

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_36.284.839248375 thread=1 sequence=36

archived log file name=+DATA/sdb1/archivelog/2014_02_11/thread_1_seq_37.282.839248379 thread=1 sequence=37

unable to find archived log

archived log thread=1 sequence=38

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

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

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

RMAN-03002: failure of recover command at 02/11/2014 12:45:24

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 38 and starting SCN of 1175084

RMAN> alter database open resetlogs;

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

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

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

RMAN-03002: failure of alter db command at 02/11/2014 12:46:12

ORA-01666: control file is for a standby database

SQL> alter database open;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

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

DB1       READ ONLY            PHYSICAL STANDBY

SQL> select process from v$managed_standby;

PROCESS

---------

ARCH

ARCH

ARCH

ARCH

RFS

RFS

RFS

RFS

8 rows selected.

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

SQL> select process from v$managed_standby;

PROCESS

---------

ARCH

ARCH

ARCH

ARCH

RFS

RFS

RFS

RFS

MRP0

9 rows selected.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

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

DB1       READ ONLY WITH APPLY PHYSICAL STANDBY

Creating an ASM database from NON-ASM database backup in 11gR2

Creating an ASM database from NON-ASM database backup in 11gR2

In this post I'll create an ASM database over a host from the backup of a NON-ASM database located on a different host using RMAN utility.

Here, I'll create new ASM managed database on host 'stby' with the backup of database 'db1' which is OMF.

Database_Name     HOST     File_Management
db1                         prim        OMF
db1(new database)  stby        ASM



Step 1.

Take the Datafile, Archivelog and Controlfile backup of the NON-ASM database in '/u01/bkp/'. 

RMAN> backup database format '/u01/bkp/db_%U.bkp';

Starting backup at 10-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0kp092u5_1_1.bkp tag=TAG20140210T113845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0lp09307_1_1.bkp tag=TAG20140210T113845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 10-FEB-14

RMAN> backup archivelog all format '/u01/bkp/arc_%U.bkp';

Starting backup at 10-FEB-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=7 STAMP=838829575
input archived log thread=1 sequence=10 RECID=8 STAMP=838884652
input archived log thread=1 sequence=11 RECID=9 STAMP=838901252
input archived log thread=1 sequence=12 RECID=10 STAMP=838901331
input archived log thread=1 sequence=13 RECID=11 STAMP=838905416
input archived log thread=1 sequence=14 RECID=12 STAMP=838939737
input archived log thread=1 sequence=15 RECID=13 STAMP=838978232
input archived log thread=1 sequence=16 RECID=14 STAMP=839014248
input archived log thread=1 sequence=17 RECID=15 STAMP=839052021
input archived log thread=1 sequence=18 RECID=16 STAMP=839095566
input archived log thread=1 sequence=19 RECID=17 STAMP=839140243
input archived log thread=1 sequence=20 RECID=18 STAMP=839161908
input archived log thread=1 sequence=21 RECID=19 STAMP=839162150
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/arc_0mp09696_1_1.bkp tag=TAG20140210T123550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-FEB-14

RMAN> backup current controlfile format '/u01/bkp/control_%U.ctl';

Starting backup at 10-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/control_0np096bf_1_1.ctl tag=TAG20140210T123703 comment=NO
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14

Step 2.

Copy the backup to the hosts where needs to create the ASM database.

[oracle@prim bkp]$ scp -r * oracle@stby:/u01/bkp/
oracle@stby's password:
arc_0mp09696_1_1.bkp                                                                                                       100%  263MB  23.9MB/s   00:11
control_0np096bf_1_1.ctl                                                                                                   100% 9568KB   9.3MB/s   00:00
db_0kp092u5_1_1.bkp                                                                                                        100% 1070MB  14.3MB/s   01:15
db_0lp09307_1_1.bkp                                                                                                        100% 9600KB   3.1MB/s   00:03

Step3.

Copy the parameter file of the OMF database to the new database server and modify the parameter for the ASM.

[oracle@stby dbs]$ more initdb1.ora

db1.__db_cache_size=687865856

db1.__java_pool_size=16777216

db1.__large_pool_size=16777216

db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

db1.__pga_aggregate_target=687865856

db1.__sga_target=1006632960

db1.__shared_io_pool_size=0

db1.__shared_pool_size=268435456

db1.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/db1/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA','+DATA'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain='oracle.com'

*.db_name='db1'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

Step 4.

Start the database in nomount mode using the newly modified parameter.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdb1.ora';

ORACLE instance started.

Total System Global Area  171581440 bytes

Fixed Size                  1343668 bytes

Variable Size             117444428 bytes

Database Buffers           50331648 bytes

Redo Buffers                2461696 bytes

Step5. 

Restore the control file from the the controlfile backup.

RMAN> restore controlfile from '/u01/bkp/control_0np096bf_1_1.ctl';

Starting restore at 10-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:08

output file name=+DATA/db1/controlfile/current.283.839163177

output file name=+DATA/db1/controlfile/current.281.839163181

Finished restore at 10-FEB-14

Step 5.

Open the database in mount mode.

RMAN> sql 'alter database mount';

sql statement: alter database mount

released channel: ORA_DISK_1

Step 6. 

Bounce the database and update the pfile for the control file location and then open the database in mount mode. Otherwise in future after bounce the database Oracle will throw the error as below.

SQL> alter database mount;

alter database mount

*

ERROR at line 1:

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

excerpts of the updated pfile with the controlfile location.

*.audit_file_dest='/u01/app/oracle/admin/db1/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA/DB1/CONTROLFILE/current.281.839163181','+DATA/DB1/CONTROLFILE/current.283.839163177'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain='oracle.com'

*.db_name='db1'

*.diagnostic_dest='/u01/app/oracle'

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdb1.ora';

ORACLE instance started.

Total System Global Area  171581440 bytes

Fixed Size                  1343668 bytes

Variable Size             117444428 bytes

Database Buffers           50331648 bytes

Redo Buffers                2461696 bytes

Database mounted.

Step7.

Restore the datafile.

Check in the v$datafile and v$tempfile for the file id.

SQL> select FILE#,NAME from v$datafile;

SQL> select FILE#,NAME from v$tempfile;

RMAN> run

2> {

3> set newname for datafile 1 to '+DATA';

4> set newname for datafile 2 to '+DATA';

5>

6> set newname for datafile 3 to '+DATA';

7> set newname for datafile 4 to '+DATA';

8> set newname for datafile 5 to '+DATA';

9> set newname for tempfile 1 to '+DATA';

10> restore database;

11> switch datafile all;

12> switch tempfile all;

13> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 10-FEB-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DATA

channel ORA_DISK_1: restoring datafile 00002 to +DATA

channel ORA_DISK_1: restoring datafile 00003 to +DATA

channel ORA_DISK_1: restoring datafile 00004 to +DATA

channel ORA_DISK_1: restoring datafile 00005 to +DATA

channel ORA_DISK_1: reading from backup piece /u01/bkp/db_0kp092u5_1_1.bkp

channel ORA_DISK_1: piece handle=/u01/bkp/db_0kp092u5_1_1.bkp tag=TAG20140210T113845

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:36

Finished restore at 10-FEB-14

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=839163435 file name=+DATA/db1/datafile/system.279.839163339

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=839163435 file name=+DATA/db1/datafile/sysaux.285.839163339

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=839163435 file name=+DATA/db1/datafile/undotbs1.280.839163343

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=839163436 file name=+DATA/db1/datafile/users.282.839163343

datafile 5 switched to datafile copy

input datafile copy RECID=10 STAMP=839163436 file name=+DATA/db1/datafile/test.284.839163341

renamed tempfile 1 to +DATA in control file

Step8.

Recover the database.

RMAN> list backup of archivelog all;

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

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

1       137.97M    DISK        00:00:07     06-FEB-14

        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20140206T161117

        Piece Name: /u01/bkp/01ovv1d5_1_1.bkp

  List of Archived Logs in backup set 1

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    3       791516     04-FEB-14 816435     05-FEB-14

  1    4       816435     05-FEB-14 858512     05-FEB-14

  1    5       858512     05-FEB-14 859840     05-FEB-14

  1    6       859840     05-FEB-14 859843     05-FEB-14

  1    7       859843     05-FEB-14 869252     05-FEB-14

  1    8       869252     05-FEB-14 898900     06-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

4       6.50K      DISK        00:00:00     06-FEB-14

        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20140206T161256

        Piece Name: /u01/bkp/04ovv1g8_1_1.bkp

  List of Archived Logs in backup set 4

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    9       898900     06-FEB-14 898952     06-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

7       177.34M    DISK        00:00:07     07-FEB-14

        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20140207T120732

        Piece Name: /u01/bkp/07p017g5_1_1.bkp

  List of Archived Logs in backup set 7

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    3       791516     04-FEB-14 816435     05-FEB-14

  1    4       816435     05-FEB-14 858512     05-FEB-14

  1    5       858512     05-FEB-14 859840     05-FEB-14

  1    6       859840     05-FEB-14 859843     05-FEB-14

  1    7       859843     05-FEB-14 869252     05-FEB-14

  1    8       869252     05-FEB-14 898900     06-FEB-14

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

10      3.00K      DISK        00:00:00     07-FEB-14

        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20140207T120851

        Piece Name: /u01/bkp/0ap017ij_1_1.bkp

  List of Archived Logs in backup set 10

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    12      933143     07-FEB-14 933178     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

14      178.69M    DISK        00:00:09     07-FEB-14

        BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20140207T131656

        Piece Name: /u01/bkp/archivelog_0fp01bi8_1_1.bkp

  List of Archived Logs in backup set 14

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    3       791516     04-FEB-14 816435     05-FEB-14

  1    4       816435     05-FEB-14 858512     05-FEB-14

  1    5       858512     05-FEB-14 859840     05-FEB-14

  1    6       859840     05-FEB-14 859843     05-FEB-14

  1    7       859843     05-FEB-14 869252     05-FEB-14

  1    8       869252     05-FEB-14 898900     06-FEB-14

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

  1    12      933143     07-FEB-14 933178     07-FEB-14

  1    13      933178     07-FEB-14 934988     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time

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

21      263.13M    DISK        00:00:17     10-FEB-14

        BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20140210T123550

        Piece Name: /u01/bkp/arc_0mp09696_1_1.bkp

  List of Archived Logs in backup set 21

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    9       898900     06-FEB-14 898952     06-FEB-14

  1    10      898952     06-FEB-14 926119     07-FEB-14

  1    11      926119     07-FEB-14 933143     07-FEB-14

  1    12      933143     07-FEB-14 933178     07-FEB-14

  1    13      933178     07-FEB-14 934988     07-FEB-14

  1    14      934988     07-FEB-14 953914     07-FEB-14

  1    15      953914     07-FEB-14 974151     08-FEB-14

  1    16      974151     08-FEB-14 993762     08-FEB-14

  1    17      993762     08-FEB-14 1016061    09-FEB-14

  1    18      1016061    09-FEB-14 1039552    09-FEB-14

  1    19      1039552    09-FEB-14 1062286    10-FEB-14

  1    20      1062286    10-FEB-14 1090918    10-FEB-14

  1    21      1090918    10-FEB-14 1091253    10-FEB-14

RMAN>list backup of database;

RMAN>report schema;

RMAN> run

2> {

3> set until sequence 21 thread 1 ;

4> recover database;

5> }

executing command: SET until clause

Starting recover at 10-FEB-14

using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=20

channel ORA_DISK_1: reading from backup piece /u01/bkp/arc_0mp09696_1_1.bkp

channel ORA_DISK_1: piece handle=/u01/bkp/arc_0mp09696_1_1.bkp tag=TAG20140210T123550

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_20_838668830.dbf thread=1 sequence=20

media recovery complete, elapsed time: 00:00:01

Finished recover at 10-FEB-14

Step 9.

Open the database in resetlog mode.

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

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

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

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

RMAN-03009: failure of sql command on default channel at 02/10/2014 13:03:07

RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlogs

ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/db1/redo01.log'

ORA-27040: file create error, unable to create file

Linux Error: 2: No such file or directory

Additional information: 1

SQL> select GROUP#,MEMBERS,STATUS from v$log;

    GROUP#    MEMBERS STATUS

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

         1          1 CLEARING_CURRENT

         3          1 CLEARING

         2          1 CLEARING

SQL> alter database add logfile

  2  group 4 '+DATA' size 50M;

Database altered.

SQL> alter database add logfile

  2  group 5 '+DATA' size 50M;

Database altered.

SQL> alter database add logfile

  2  group 6 '+DATA' size 50M;

Database altered.

SQL> alter system switch logfile;

alter system switch logfile

*

ERROR at line 1:

ORA-01109: database not open

SQL> select GROUP#,STATUS,MEMBER from v$logfile;

    GROUP# STATUS  MEMBER

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

         3         /u01/app/oracle/oradata/db1/redo03.log

         2         /u01/app/oracle/oradata/db1/redo02.log

         1         /u01/app/oracle/oradata/db1/redo01.log

         4         +DATA/db1/onlinelog/group_4.286.839164409

         5         +DATA/db1/onlinelog/group_5.287.839164465

         6         +DATA/db1/onlinelog/group_6.288.839164475

6 rows selected.

SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;

    GROUP#    MEMBERS STATUS           ARC

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

         1          1 CLEARING_CURRENT NO

         2          1 CLEARING         YES

         6          1 UNUSED           YES

         4          1 UNUSED           YES

         5          1 UNUSED           YES

         3          1 CLEARING         YES

6 rows selected.

I tried to recover again the database using the controlfile but it didn't work.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1090918 generated at 02/10/2014 12:31:44 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_21_838668830.dbf
ORA-00280: change 1090918 for thread 1 is in sequence #21


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/db1/redo01.log'

Since the group 1 log file was not able to archived so it prevent database to open.

We need to clear that unarchived log.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;

    GROUP#    MEMBERS STATUS           ARC

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

         1          1 CURRENT          NO

         2          1 UNUSED           YES

         3          1 UNUSED           YES

         4          1 UNUSED           YES

         5          1 UNUSED           YES

         6          1 UNUSED           YES

6 rows selected.

SQL> alter database open resetlogs;

Database altered.

Now the database is OPEN.

SQL> select NAME,STATUS from v$datafile;

NAME                                               STATUS

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

+DATA/db1/datafile/system.279.839163339            SYSTEM

+DATA/db1/datafile/sysaux.285.839163339            ONLINE

+DATA/db1/datafile/undotbs1.280.839163343          ONLINE

+DATA/db1/datafile/users.282.839163343             ONLINE

+DATA/db1/datafile/test.284.839163341              ONLINE

SQL> select  NAME,STATUS from v$tempfile;

NAME                                               STATUS

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

+DATA/db1/tempfile/temp.289.839165941              ONLINE

SQL> select GROUP#,MEMBERS,STATUS from v$log;

    GROUP#    MEMBERS STATUS

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

         1          1 CURRENT

         2          1 UNUSED

         3          1 UNUSED

         4          1 UNUSED

         5          1 UNUSED

         6          1 UNUSED

6 rows selected.