RMAN>Restore rman from Dataguard ASM to Non-asm Environment (Part 1)!

Link : Restore RMAN How To

Restore RAC Dataguard RMAN backup to a new server :

 - Take the RMAN full database backup from Dataguard End.

 - Transfer the backup with the logfile to remote server on the SAME LOCATION .



Step 1 : Create pfile for ISLDB database :

[oracle@PRIM ~]$ cat initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldb/control01.ctl','/u01/app/oracle/oradata/isldb/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
#_allow_resetlogs_corruption = true
#_no_recovery_through_resetlogs=TRUE



Step 2:

   [oracle@PRIM dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 28 15:22:46 2014

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

Connected to an idle instance.

SQL> startup nomount pfile='initisldb.ora'
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
SQL>


Step 3 : Restore the Controlfile

[oracle@PRIM dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 28 15:30:33 2014

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

connected to target database: ISLDB (not mounted)

RMAN> restore controlfile from '/cloudfs/rmanback/isl/20140827_172834/controlfile.ctlbk';

Starting restore at 28-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=266 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlisldb.dbf
Finished restore at 28-AUG-14

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>


Step 4 : Restore all the datafiles :

  1)

run {
set newname for datafile '+DATA/isldg/datafile/system.298.849413815'  to  '/u01/app/oracle/oradata/isldb/system1.dbf' ;
restore datafile 1 ;
}

executing command: SET NEWNAME

Starting restore at 28-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=266 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 /u01/app/oracle/oradata/isldg/system1.dbf
channel ORA_DISK_1: reading from backup piece /cloudfs/rmanback/isl/20140827_172834/856718916_67_%r.dbf
channel ORA_DISK_1: piece handle=/cloudfs/rmanback/isl/20140827_172834/856718916_67_%r.dbf tag=TAG20140827T172835
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 28-AUG-14

RMAN>

2)

run {
set newname for datafile '+DATA/isldg/datafile/sysaux.295.849413813'  to  '/u01/app/oracle/oradata/isldb/sysaux1.dbf' ;
restore datafile 2 ;
}


3)

run {
set newname for datafile '+DATA/isldg/datafile/undotbs1.296.849413813'  to  '/u01/app/oracle/oradata/isldb/undotbs1.dbf' ;
restore datafile 3 ;
}


4)

run {
set newname for datafile '+DATA/isldg/datafile/undotbs2.297.849413815'  to  '/u01/app/oracle/oradata/isldb/undotbs2.dbf' ;
restore datafile 4 ;
}


5)

run {
set newname for datafile '+DATA/isldg/datafile/users.299.849413849'  to  '/u01/app/oracle/oradata/isldb/users1.dbf' ;
restore datafile 5 ;
}


6)

run {
set newname for datafile '+DATA/isldg/datafile/bu_system_tbs.291.849413617'  to '/u01/app/oracle/oradata/isldb/bu_system_tbs1.dbf';
restore datafile 6 ;
}


7)

run {
set newname for datafile '+DATA/isldg/datafile/bu_his_log_tbs.292.849413617'  to '/u01/app/oracle/oradata/isldb/bu_his_log_tbs1.dbf';
restore datafile 7;
}


8)

run {
set newname for datafile '+DATA/isldg/datafile/bu_index_tbs.293.849413617'  to '/u01/app/oracle/oradata/isldb/bu_index_tbs1.dbf';
restore datafile 8 ;
}


9)

run {
set newname for datafile '+DATA/isldg/datafile/bu_image_tbs.294.849413619'  to '/u01/app/oracle/oradata/isldb/bu_image_tbs1.dbf';
restore datafile 9 ;
}


10)

run {
set newname for datafile '+DATA/isldg/tempfile/temp.301.849513611'  to '/u01/app/oracle/oradata/isldb/temp1.dbf';
restore datafile 10 ;
}


Step 5 :

  Run the SWITCH command :

run
{
set newname for datafile '+DATA/isldg/datafile/system.298.849413815'  to  '/u01/app/oracle/oradata/isldb/system1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/sysaux.295.849413813'  to  '/u01/app/oracle/oradata/isldb/sysaux1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/undotbs1.296.849413813'  to  '/u01/app/oracle/oradata/isldb/undotbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/undotbs2.297.849413815'  to  '/u01/app/oracle/oradata/isldb/undotbs2.dbf' ;
set newname for datafile '+DATA/isldg/datafile/users.299.849413849'  to  '/u01/app/oracle/oradata/isldb/users1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_system_tbs.291.849413617'  to '/u01/app/oracle/oradata/isldb/bu_system_tbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_his_log_tbs.292.849413617'  to '/u01/app/oracle/oradata/isldb/bu_his_log_tbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_index_tbs.293.849413617'  to '/u01/app/oracle/oradata/isldb/bu_index_tbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_image_tbs.294.849413619'  to '/u01/app/oracle/oradata/isldb/bu_image_tbs1.dbf' ;
set newname for tempfile 1  to  '/u01/app/oracle/oradata/isldb/temp1.dbf' ;
switch datafile all;
switch tempfile all;
}


Step 6 :  Recover the Database :

Error :--------

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10219039198 generated at 08/27/2014 11:05:14 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf
ORA-00280: change 10219039198 for thread 1 is in sequence #1000


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 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/isldg/system1.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10219039198 generated at 08/27/2014 11:05:14 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf
ORA-00280: change 10219039198 for thread 1 is in sequence #1000


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
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/isldg/system1.dbf'


ORA-01112: media recovery not started


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/isldg/system1.dbf'

-------



Solution :

I manually put the archivelog to '/u01/app/oracle/product/11.2.0/db_1/dbs/thread_1_seq_1000.652.856710149'
directory and rename to is as 'arch1_1000_839681983.dbf' .

After that it checks , 'arch2_771_839681983.dbf' . I put it manually also



 ---
ERROR :
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10219039198 generated at 08/27/2014 11:05:14 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf
ORA-00280: change 10219039198 for thread 1 is in sequence #1000


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 10219039198 generated at 08/27/2014 11:00:42 needed for
thread 2
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_771_839681983.dbf
ORA-00280: change 10219039198 for thread 2 is in sequence #771


ORA-00308: cannot open archived log
'/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_771_839681983.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 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/isldg/system1.dbf'

.
.
.
And so on . And After some few logs , make cancel :
-----

SQL>  recover database until cancel using backup controlfile;
ORA-00279: change 10230768222 generated at 08/27/2014 19:01:58 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1002_839681983.dbf
ORA-00280: change 10230768222 for thread 1 is in sequence #1002


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
--


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '+red



Step 7 : Open the Database


During open got the below error :

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


Sol :

 a) Backup the logfile and recreate it like below :

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/control.sql' resetlogs ;

Database altered.


 b) Edit the controlfile :

   [oracle@PRIM ~]$ cat control.sql
CREATE CONTROLFILE REUSE DATABASE "ISLDB" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/isldg/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/isldg/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 5 '/u01/app/oracle/oradata/isldg/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/isldg/system1.dbf',
  '/u01/app/oracle/oradata/isldg/sysaux1.dbf',
  '/u01/app/oracle/oradata/isldg/undotbs1.dbf',
  '/u01/app/oracle/oradata/isldg/undotbs2.dbf',
  '/u01/app/oracle/oradata/isldg/users1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_system_tbs1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_his_log_tbs1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_index_tbs1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_image_tbs1.dbf'
CHARACTER SET WE8ISO8859P1
;


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


---
[oracle@PRIM ~]$ vi initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldb/control01.ctl','/u01/app/oracle/oradata/isldb/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
#_allow_resetlogs_corruption = true
#_no_recovery_through_resetlogs=TRUE
--


SQL> startup nomount pfile='/home/oracle/initisldb.ora'
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
               4706304 bytes
SQL> @/home/oracle/control.sql

Control file created.

SQL> select status from v$instance;

STATUS
------------
MOUNTED



SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled




Step 8 :

 Open the database with the "_no_recovery_through_resetlogs=TRUE"

 cat initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldb/control01.ctl','/u01/app/oracle/oradata/isldb/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
#_allow_resetlogs_corruption = true
_no_recovery_through_resetlogs=TRUE


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/oracle/initisldb.ora'
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.


Step 9 : Create TEMP File :

SQL>  create temporary tablespace TEMP1  tempfile '/u01/app/oracle/oradata/isldg/temp01.dbf' size 100M;

Tablespace created.

SQL> alter DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

Database altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/isldg/temp01.dbf


SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> create spfile from pfile='/home/oracle/initisldb.ora';

File created.


Step 10 : RESTART DATABASE :

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
Database mounted.
Database opened.


Fianlly , Change the required Parameter Like : 'sga_target' ,'log_archive' etc ....