Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, August 24, 2012

Oracle Full DB restore from TAPE to new server



DR Scenario – Full DB restore from TAPE


Scenario Overview:
Server crashed and is in unusable state i.e. you cannot use the SAME server to restore the DB.
Lost all files including SPFILE, controlfile, archivelog and database files

Given:
Unix Team gave you a new server and storage.( OS mount point names are different)
Netbackup client software installed and libraries linked with oracle.

To verify check if libobk file exist in the Netbackup install directory

oracle@[labrman01]-> ls -lrt /usr/openv/netbackup/bin/libobk*
/usr/openv/netbackup/bin/libobk*: No such file or directory


Goal:
Restore the database including SPFILE, Controlfile from TAPE.

Must Have:
DBID of the crashed database (without DBID we cannot restore the database from TAPE in DR situation)
And the client name “NB_ORA_CLIENT” (client name with which backup has been taken)

1)      Now set the environment on the new server- we will call the new server as LAB- labrman01.

labrman01 /export/home/oracle>
>export ORACLE_HOME=/adm02/u0001/oracle/product/10.2.0/db_1
>export PATH=$PATH:/adm02/u0001/oracle/product/10.2.0/db_1/bin
>export NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS'
>export ORACLE_SID=HYPP

2)      Invoke RMAN

We are going to do the restore of SID=HYPP. Since we lost the whole server and restoring onto a new server our first goal is to restore the SPFILE and Controlfile.

 labrman01 /export/home/oracle
>rman
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jul 8 12:03:43 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.


3)      Now connect to target

RMAN> connect target /
connected to target database:  (not mounted)
RMAN> set dbid=2563884143
executing command: SET DBID

4)      Since we don’t have the PFILE/SPFILE we are going to start the database without a PFILE In nomount

RMAN> startup force nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/adm02/u0001/oracle/product/10.2.0/db_1/dbs/initHYPP.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     2039056 bytes
Variable Size                 67109616 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6348800 bytes

5)      what if I don’t know the backup piece name for spfile? how do I get them from TAPE?
We have to use”restore from autobackup” in this case.


RMAN> run{allocate channel ch01 type sbt;
2> SEND 'NB_ORA_CLIENT= hypprdbv1-bn;
3> restore  spfile from autobackup;
4> }


allocated channel: ch01
channel ch01: sid=36 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2007072323)
sent command to channel: ch01
Starting restore at 06-JUL-10
channel ch01: looking for autobackup on day: 20100706
channel ch01: looking for autobackup on day: 20100705
channel ch01: looking for autobackup on day: 20100704
channel ch01: looking for autobackup on day: 20100703
channel ch01: looking for autobackup on day: 20100702
channel ch01: looking for autobackup on day: 20100701
channel ch01: looking for autobackup on day: 20100630
channel ch01: no autobackup in 7 days found
channel ch01: no autobackup in 7 days found
released channel: ch01
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/06/2010 14:30:38
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

If we use restore from autobackup, By default  RMAN will look for the file for the past 7 days starting from the current day, assuming the current day is 07/06 so RMAN did go back until 06/30 and errored  out  saying it couldn’t able to find the SPFILE in the past 7 days.
“here  we are trying to restore the backup which is older than 7 days i.e we took the backup on 06/21 to TAPE and we need to restore this backup”

6)       So how do I get over the 7 day restriction ?

By default SPFILE will be restored onto $ORACLE_HOME/dbs if you want to restore it to specific location use “TO PFILE ” see below:

RMAN> run {
1>allocate channel ch01 type sbt;
2> send 'NB_ORA_CLIENT=hypprdbv1-bn';
3> RESTORE SPFILE to pfile '/export/home/oracle/sshaik/initHYPP.ora' from autobackup maxdays 20;
4> }

using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: sid=36 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2007072323)
sent command to channel: ch01
Starting restore at 07/08/2010 12:06:21
channel ch01: looking for autobackup on day: 20100708
channel ch01: looking for autobackup on day: 20100707
channel ch01: looking for autobackup on day: 20100706
channel ch01: looking for autobackup on day: 20100705
channel ch01: looking for autobackup on day: 20100704
channel ch01: looking for autobackup on day: 20100703
channel ch01: looking for autobackup on day: 20100702
channel ch01: looking for autobackup on day: 20100701
channel ch01: looking for autobackup on day: 20100630
channel ch01: looking for autobackup on day: 20100629
channel ch01: looking for autobackup on day: 20100628
channel ch01: looking for autobackup on day: 20100627
channel ch01: looking for autobackup on day: 20100626
channel ch01: looking for autobackup on day: 20100625
channel ch01: looking for autobackup on day: 20100624
channel ch01: looking for autobackup on day: 20100623
channel ch01: looking for autobackup on day: 20100622
channel ch01: looking for autobackup on day: 20100621
channel ch01: autobackup found: c-2563884143-20100621-03
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/07/2010 11:38:29
RMAN-03009: failure of IRESTORE command on ch01 channel at 07/07/2010 11:38:29
ORA-19870: error reading backup piece c-2563884143-20100621-03
ORA-19507: failed to retrieve sequential file, handle="c-2563884143-20100621-03", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
   Failed to process backup file


Here RMAN found the SPFILE but unable to read or restore the file due to some Netbackup glitches and we raised this issue with Netbackup Team and were resolved from the Netbackup side.( Don’t ask me what they did since it was never revealed what has been done to fix the issue)


7)      We restarted the restore after Netbackup team fixed the above issue.

RMAN> run {
1>allocate channel ch01 type sbt;
2> send 'NB_ORA_CLIENT=hypprdbv1-bn';
3> RESTORE SPFILE to pfile '/export/home/oracle/sshaik/initHYPP.ora' from autobackup maxdays 20;
4> }

using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: sid=36 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2007072323)
sent command to channel: ch01
Starting restore at 07/08/2010 12:06:21
channel ch01: looking for autobackup on day: 20100708
channel ch01: looking for autobackup on day: 20100707
channel ch01: looking for autobackup on day: 20100706
channel ch01: looking for autobackup on day: 20100705
channel ch01: looking for autobackup on day: 20100704
channel ch01: looking for autobackup on day: 20100703
channel ch01: looking for autobackup on day: 20100702
channel ch01: looking for autobackup on day: 20100701
channel ch01: looking for autobackup on day: 20100630
channel ch01: looking for autobackup on day: 20100629
channel ch01: looking for autobackup on day: 20100628
channel ch01: looking for autobackup on day: 20100627
channel ch01: looking for autobackup on day: 20100626
channel ch01: looking for autobackup on day: 20100625
channel ch01: looking for autobackup on day: 20100624
channel ch01: looking for autobackup on day: 20100623
channel ch01: looking for autobackup on day: 20100622
channel ch01: looking for autobackup on day: 20100621
channel ch01: autobackup found: c-2563884143-20100621-03
channel ch01: autobackup found: c-2563884143-20100621-03
channel ch01: SPFILE restore from autobackup complete
Finished restore at 07/08/2010 12:42:12
released channel: ch01

RMAN>

8)      Now edit the parameter file to reflect the new directory locations
 i.e since the HYPP is going to be restored onto a LAB server, change udump,bdump,cdump,controlfile and archive dest locations as per the mount point directory locations on the LAB server

>vi initHYPP.ora
*.aq_tm_processes=0
*.audit_trail='DB'
*.background_dump_dest='/adm02/u0001/oracle/admin/HYPP/bdump'
*.compatible='10.2.0.3'
*.control_files='/adm02/u0001/HYPP/control/control01.ctl'



9)      Now shutdown and start the database in nomount to make sure no errors in restored PFILE:

labrman01(HYPP)  /export/home/oracle
>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 8 13:11:08 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size                  2041368 bytes
Variable Size            1258297832 bytes
Database Buffers          553648128 bytes
Redo Buffers               14729216 bytes
SQL> exit

Now we restored the SPFILE from TAPE and started the database in nomount.
10)  Next step is to restore the Controlfile from TAPE

labrman01(HYPP)  /export/home/oracle
>rman

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jul 8 15:13:12 2010

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

RMAN> connect target /
connected to target database: HYPP (not mounted)

RMAN> set dbid=2563884143
executing command: SET DBID

RMAN>  run {allocate channel ch01 type sbt;
2>  send 'NB_ORA_CLIENT=hypprdbv1-bn';
3> restore controlfile to '/adm02/u9001/HYPP/control/control01.ctl' from autobackup maxdays 20;
4> }

using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: sid=1095 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2007072323)
sent command to channel: ch01
Starting restore at 08-JUL-10
channel ch01: looking for autobackup on day: 20100708
channel ch01: looking for autobackup on day: 20100707
channel ch01: looking for autobackup on day: 20100706
channel ch01: looking for autobackup on day: 20100705
channel ch01: looking for autobackup on day: 20100704
channel ch01: looking for autobackup on day: 20100703
channel ch01: looking for autobackup on day: 20100702
channel ch01: looking for autobackup on day: 20100701
channel ch01: looking for autobackup on day: 20100630
channel ch01: looking for autobackup on day: 20100629
channel ch01: looking for autobackup on day: 20100628
channel ch01: looking for autobackup on day: 20100627
channel ch01: looking for autobackup on day: 20100626
channel ch01: looking for autobackup on day: 20100625
channel ch01: looking for autobackup on day: 20100624
channel ch01: looking for autobackup on day: 20100623
channel ch01: looking for autobackup on day: 20100622
channel ch01: looking for autobackup on day: 20100621
channel ch01: autobackup found: c-2563884143-20100621-03
channel ch01: control file restore from autobackup complete
Finished restore at 08-JUL-10
released channel: ch01



11)   Now shutdown the database and mount it using the restored controlfile

>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 8 15:56:47 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size                  2041368 bytes
Variable Size            1258297832 bytes
Database Buffers          553648128 bytes
Redo Buffers               14729216 bytes
Database mounted.

12)  Find out upto which point you can do the restore.
(This is useful if you don't know the time when the server crashed or the time of valid backups available)

>rman

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jul 9 11:39:01 2010

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

RMAN> connect target /

connected to target database: HYPP (DBID=2563884143, not open)

RMAN> run{
2> allocate channel ch01 type sbt;
3> send 'NB_ORA_CLIENT=hypprdbv1-bn';
4> restore database preview;
5> }

using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: sid=1092 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2007072323)
sent command to channel: ch01
Starting restore at 08-JUL-10

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5949    Incr 0  239.00M    SBT_TAPE    00:01:24     21-JUN-10
        BP Key: 5949   Status: AVAILABLE  Compressed: NO  Tag: 06_21_10_F
        Handle: HYPP_F_06_21_2010_6051_1_722258752   Media: 310917
Keep: LOGS               Until: 21-SEP-10
  List of Datafiles in backup set 5949
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5    0  Incr 10513166137701 21-JUN-10 /adm02/u0001/oracle/datafile/HYPP/tools_01.dbf
  9    0  Incr 10513166137701 21-JUN-10 /adm02/u0001/oracle/datafile/HYPP/hypp_apps_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5950    Incr 0  371.00M    SBT_TAPE    00:02:04     21-JUN-10
        BP Key: 5950   Status: AVAILABLE  Compressed: NO  Tag: 06_21_10_F
        Handle: HYPP_F_06_21_2010_6050_1_722258752   Media: 310917
Keep: LOGS               Until: 21-SEP-10
  List of Datafiles in backup set 5950
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 10513166137700 21-JUN-10 /adm02/u0001/oracle/datafile/HYPP/system01.dbf
  3    0  Incr 10513166137700 21-JUN-10 /adm02/u0001/oracle/datafile/HYPP/sysaux01.dbf
  4    0  Incr 10513166137700 21-JUN-10 /adm02/u0001/oracle/datafile/HYPP/users01.dbf
  8    0  Incr 10513166137700 21-JUN-10 /adm02/u0001/oracle/datafile/HYPP/hypp_bi_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5952    Incr 0  3.89G      SBT_TAPE    00:03:37     21-JUN-10
        BP Key: 5952   Status: AVAILABLE  Compressed: NO  Tag: 06_21_10_F
        Handle: HYPP_F_06_21_2010_6049_1_722258751   Media: 310917
Keep: LOGS               Until: 21-SEP-10
  List of Datafiles in backup set 5952
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2    0  Incr 10513166137699 21-JUN-10 /adm02/u0001/oracle/datafile/HYPP/undo_01.dbf
  6    0  Incr 10513166137699 21-JUN-10 /adm02/u0001/oracle/datafile/HYPP/hypp_data_01.dbf
  7    0  Incr 10513166137699 21-JUN-10 /adm02/u0001/oracle/datafile/HYPP/hypp_idx_01.dbf
  10   0  Incr 10513166137699 21-JUN-10 /adm02/u0001/oracle/datafile/HYPP/hyp_hal_data_01.dbf


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5955    256.00K    SBT_TAPE    00:00:56     21-JUN-10
        BP Key: 5955   Status: AVAILABLE  Compressed: NO  Tag: ARCH_06_21_10_11
        Handle: HYPP_A_06_21_2010_6055_1_722259284   Media: /netbpmed02cdc_dsu2_2/hypprdbv1-bn_1277134493_C1_F1

  List of Archived Logs in backup set 5955
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1783    10513166138999 21-JUN-10 10513166139334 21-JUN-10

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
1672    1    1782    A 21-JUN-10 /adm02/u8001/HYPP/arch/arch_HYPQ_0001_0000001782_0637224303.arc
1673    1    1       A 08-JUL-10 /adm02/u8001/HYPP/arch/arch_HYPQ_0001_0000000001_0723832272.arc
Media recovery start SCN is 10513166137699
Recovery must be done beyond SCN 10513166137701 to clear data files fuzziness
Finished restore at 09-JUL-10
released channel: ch01

Now we know we can go upto SCN 10513166137701 during the restore and make sure you change the datafile locations during the restore since you are restoring onto a new server which has different mount point naming conventions.



13)  Now start the database restore.

Now prepare the restore script for HYPP and run in nohup:

>vi HYPP_restore.sh
#/bin/ksh
ORACLE_HOME=/adm02/u0001/oracle/product/10.2.0/db_1
ORACLE_SID=HYPP
export ORACLE_HOME
export ORACLE_SID
/adm02/u0001/oracle/product/10.2.0/db_1/bin/rman >>HYPP_restore.log<
connect target /
run {
allocate channel ch01 type sbt;
send 'NB_ORA_CLIENT=hypprdbv1-bn';
set until scn=10513166137701;
set newname for datafile 1 to '/adm02/u0001/oracle/datafile/HYPP/system01.dbf';
set newname for datafile 3 to '/adm02/u0001/oracle/datafile/HYPP/sysaux01.dbf';
set newname for datafile 4 to '/adm02/u0001/oracle/datafile/HYPP/users01.dbf';
set newname for datafile 8 to '/adm02/u0001/oracle/datafile/HYPP/hypp_bi_01.dbf';
set newname for datafile 5 to '/adm02/u0001/oracle/datafile/HYPP/tools_01.dbf';
set newname for datafile 9 to '/adm02/u0001/oracle/datafile/HYPP/hypp_apps_01.dbf';
set newname for datafile 2 to '/adm02/u0001/oracle/datafile/HYPP/undo_01.dbf';
set newname for datafile 6 to '/adm02/u0001/oracle/datafile/HYPP/hypp_data_01.dbf';
set newname for datafile 7 to '/adm02/u0001/oracle/datafile/HYPP/hypp_idx_01.dbf';
set newname for datafile 10 to '/adm02/u0001/oracle/datafile/HYPP/hyp_hal_data_01.dbf';
restore database;
switch datafile all;
recover database;
}
EOF


14)  Now kick off the above script in nohup:

RMAN>
connected to target database: HYPP (DBID=2563884143, not open)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18>
using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: sid=1094 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2007072323)
sent command to channel: ch01
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
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 08-JUL-10

channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /adm02/u0001/oracle/datafile/HYPP/tools_01.dbf
restoring datafile 00009 to /adm02/u0001/oracle/datafile/HYPP/hypp_apps_01.dbf
channel ch01: reading from backup piece HYPP_F_06_21_2010_6051_1_722258752
channel ch01: restored backup piece 1
piece handle=HYPP_F_06_21_2010_6051_1_722258752 tag=06_21_10_F
channel ch01: restore complete, elapsed time: 00:02:36
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /adm02/u0001/oracle/datafile/HYPP/system01.dbf
restoring datafile 00003 to /adm02/u0001/oracle/datafile/HYPP/sysaux01.dbf
restoring datafile 00004 to /adm02/u0001/oracle/datafile/HYPP/users01.dbf
restoring datafile 00008 to /adm02/u0001/oracle/datafile/HYPP/hypp_bi_01.dbf
channel ch01: reading from backup piece HYPP_F_06_21_2010_6050_1_722258752
channel ch01: restored backup piece 1
piece handle=HYPP_F_06_21_2010_6050_1_722258752 tag=06_21_10_F
channel ch01: restore complete, elapsed time: 00:01:36
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /adm02/u0001/oracle/datafile/HYPP/undo_01.dbf
restoring datafile 00006 to /adm02/u0001/oracle/datafile/HYPP/hypp_data_01.dbf
restoring datafile 00007 to /adm02/u0001/oracle/datafile/HYPP/hypp_idx_01.dbf
restoring datafile 00010 to /adm02/u0001/oracle/datafile/HYPP/hyp_hal_data_01.dbf
channel ch01: reading from backup piece HYPP_F_06_21_2010_6049_1_722258751
channel ch01: restored backup piece 1
piece handle=HYPP_F_06_21_2010_6049_1_722258751 tag=06_21_10_F
channel ch01: restore complete, elapsed time: 00:02:26
Finished restore at 08-JUL-10

datafile 1 switched to datafile copy
input datafile copy recid=11 stamp=723831657 filename=/adm02/u0001/oracle/datafile/HYPP/system01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=12 stamp=723831657 filename=/adm02/u0001/oracle/datafile/HYPP/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=13 stamp=723831657 filename=/adm02/u0001/oracle/datafile/HYPP/users01.dbf
datafile 8 switched to datafile copy
input datafile copy recid=14 stamp=723831657 filename=/adm02/u0001/oracle/datafile/HYPP/hypp_bi_01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=15 stamp=723831657 filename=/adm02/u0001/oracle/datafile/HYPP/tools_01.dbf
datafile 9 switched to datafile copy
input datafile copy recid=16 stamp=723831657 filename=/adm02/u0001/oracle/datafile/HYPP/hypp_apps_01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=17 stamp=723831657 filename=/adm02/u0001/oracle/datafile/HYPP/undo_01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=18 stamp=723831657 filename=/adm02/u0001/oracle/datafile/HYPP/hypp_data_01.dbf
datafile 7 switched to datafile copy
input datafile copy recid=19 stamp=723831657 filename=/adm02/u0001/oracle/datafile/HYPP/hypp_idx_01.dbf
datafile 10 switched to datafile copy
input datafile copy recid=20 stamp=723831657 filename=/adm02/u0001/oracle/datafile/HYPP/hyp_hal_data_01.dbf

Starting recover at 08-JUL-10

starting media recovery

channel ch01: starting archive log restore to default destination
channel ch01: restoring archive log
archive log thread=1 sequence=1782
channel ch01: reading from backup piece HYPP_A_06_21_2010_6054_1_722259284
channel ch01: restored backup piece 1
piece handle=HYPP_A_06_21_2010_6054_1_722259284 tag=ARCH_06_21_10_11
channel ch01: restore complete, elapsed time: 00:01:36
archive log filename=/adm02/u8001/HYPP/arch/arch_HYPQ_0001_0000001782_0637224303.arc thread=1 sequence=1782
media recovery complete, elapsed time: 00:00:02
Finished recover at 08-JUL-10
released channel: ch01

RMAN>

Recovery Manager complete.

15)  This applies only if the logfiles are missing or logfile location is different or you want to put them in a new mount point after the restore

Before we open the database in resetlogs we need to make sure the logfiles locations are changed properly

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/hyp01/u9001/HYPP/redo/redo01a.log
/hyp01/u9002/HYPP/redo/redo01b.log
/hyp01/u9002/HYPP/redo/redo02a.log
/hyp01/u9003/HYPP/redo/redo02b.log
/hyp01/u9003/HYPP/redo/redo03a.log
/hyp01/u9004/HYPP/redo/redo03b.log


Above is still pointing to the old locations “/hyp01/” so now change them to the new location;

connect to the database :

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED




Run the below script to rename the logfiles to new locations:

alter database rename file  '/hyp01/u9001/HYPP/redo/redo01a.log' to '/adm02/u0001/oracle/datafile/HYPP/redo01a.log';
alter database rename file '/hyp01/u9002/HYPP/redo/redo01b.log' to '/adm02/u0001/oracle/datafile/HYPP/redo01b.log';
alter database rename file  '/hyp01/u9002/HYPP/redo/redo02a.log' to '/adm02/u0001/oracle/datafile/HYPP/redo02a.log';
alter database rename file  '/hyp01/u9003/HYPP/redo/redo02b.log' to '/adm02/u0001/oracle/datafile/HYPP/redo02b.log';
alter database rename file  '/hyp01/u9003/HYPP/redo/redo03a.log' to '/adm02/u0001/oracle/datafile/HYPP/redo03a.log';
alter database rename file  '/hyp01/u9004/HYPP/redo/redo03b.log' to '/adm02/u0001/oracle/datafile/HYPP/redo03b.log';

Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.

Verify the changes has been taken affect:

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/adm02/u0001/oracle/datafile/HYPP/redo01a.log
/adm02/u0001/oracle/datafile/HYPP/redo01b.log
/adm02/u0001/oracle/datafile/HYPP/redo02a.log
/adm02/u0001/oracle/datafile/HYPP/redo02b.log
/adm02/u0001/oracle/datafile/HYPP/redo03a.log
/adm02/u0001/oracle/datafile/HYPP/redo03b.log

6 rows selected.

16)  Final Step:-  Open the database in RESTLOGS mode( SINCE WE JUST DID AN INCOMPLETE MEDIA RECOVERY)
SQL>
SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE

Do a quick check to verify all the files are online:

SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
SYSTEM


Now the database is ready for Application Rock & Roll. J


FAQS:
Q) Why I used this method or why can't you use that/this way? or Tom said this way to do the job?
A)  You can mess up oracle in numerous ways at the same time you can use numerous ways to do the restore.
The methods or scripts I used here are best of my ability and knowledge, if you know a better, simple and short way please use so and post the same if it is successful.

5 comments:

Ram said...

Thanks Sameer. Very useful stuff

amit4oracle said...

Thanks Sameer. This is helpful.

Arvind Narsing said...

Hi Shaik,

While setting new name to the datafiles... how do you know that how many datafiles are there and their ID's...

please let me know

Shahid Md said...

@Aravind- You will get the datafile information from control file which is already restored.

siddiqui javed said...

Through dynamic query u can find out