Restoring Database to a new location using rman

Scenario

All database files got lost (controlfiles,redo,datafiles), server parameter files is also lost (spfile).

The database has to be restored meeting the following conditions
  1. After restoring all database files(controlfiles, redolog, datafiles) should have new location
  2. Spfile will be residing at  same location.

Environment

 Oracle database 11.2.0.1.0
OEL 5.7


Before Disaster

Database files (datafiles,controlfiles,redo) /u01/app/oracle/oradata/orcl
spfil $ORACLE_HOME/dbs
archive log location /u01/oracle/arch
backup controlfile location -/u01/app/oracle/
controlfile autobackup is turned on

After recovery

 spfile same location ($ORACLE_HOME/dbs)
databasfiles and controlfiles /u01/oracle/oradata/orcl
archive log location /u01/oracle/arch

There is a controlfile autobackup and database backup taken sometime before disaster.


Restoring and recovering
  • Create new directory structures
          cd /u01/oracle
          mkdir -p oradata/orcl

  • start rman
          cd /u01/oracle/bkp
          rman target / | tee -a rman_test_log.log

  • set dbid
          set dbid=1334549344

  • startup database in nomount mode
          startup nomount;

  • set controlfile autobackup format (the autoback was not at its default location)
          set controlfile autobackup format for device type disk to '/u01/app/oracle/%F'

  • restore spfile
          restore spfile from autobackup;

  • shutdown and startup in nomount(this will start with newly created spfile)
          startup nomount force ;

  • set controlfile new location
          sql "alter system set control_files=''/u01/oracle/oradata/orcl/    control01.dbf'',''/u01/oracle/oradata/orcl/control02.dbf'' scope=spfile" ;

  • shutdown and startup in nomount(for controlfile new location to take effect)
          startup nomount force ;

  • make sure the controlfile location is set to our new location with sqlplus
          sqlplus / as sysdba
          show parameter controlfile

previous
   control_files string /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/   oracle/oradata/orcl/control02.ctl
now
 control_files string /u01/oracle/oradata/orcl/control01.dbf, /u01/oracle/ oradata/orcl/control02.dbf
  •   restore controlfile (it should be restored in new location)
          restore controlfile from autobackup;

  • now mount the database
          alter database mount ;
  • run the rman command to rename datafiles,tempfile to newlocation and restore the database
   run
  { 
  set newname for database to '/u01/oracle/oradata/orcl/%b' ;
  restore database ;
  switch datafile all;
  switch tempfile all;
  }


  • run sql statements to rename redo logs to new location

          SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/ redo01.log'' TO ''/u01/oracle/oradata/orcl/redo01.log'' ";
          SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/ redo02.log'' TO ''/u01/oracle/oradata/orcl/redo02.log'' ";

          SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log'' TO ''/u01/oracle/oradata/orcl/redo03.log'' ";

  • recover database until last sequence
          recover database until sequence 7 ;

  • open database with resetlogs
          alter database open resetlogs ;


Rman Session of  Restore and Recovery

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Feb 19 15:03:11 2013

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

connected to target database: ORCL (DBID=1334549344)

listing of backup taken when the database was up and running

RMAN> list backup summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
47 B A A DISK 19-FEB-13 1 1 NO TAG20130219T150521
48 B F A DISK 19-FEB-13 1 1 NO TAG20130219T150524
49 B A A DISK 19-FEB-13 1 1 NO TAG20130219T150620
50 B F A DISK 19-FEB-13 1 1 NO TAG20130219T150621

RMAN> list backup;

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


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
47 93.13M DISK 00:00:02 19-FEB-13
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150521
Piece Name: /u01/oracle/bkp/arc20130219ORCL1mo2cath_1_1

List of Archived Logs in backup set 47
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 1668460 19-FEB-13 1671683 19-FEB-13
1 4 1671683 19-FEB-13 1672642 19-FEB-13
1 5 1672642 19-FEB-13 1672835 19-FEB-13

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48 Full 1.22G DISK 00:00:51 19-FEB-13
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150524
Piece Name: /u01/oracle/bkp/20130219ORCL1no2catl_1_1
List of Datafiles in backup set 48
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1672845 19-FEB-13 /u01/app/oracle/oradata/orcl/system01.dbf
2 Full 1672845 19-FEB-13 /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 Full 1672845 19-FEB-13 /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 Full 1672845 19-FEB-13 /u01/app/oracle/oradata/orcl/users01.dbf
5 Full 1672845 19-FEB-13 /u01/app/oracle/oradata/orcl/example01.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
49 80.50K DISK 00:00:00 19-FEB-13
BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150620
Piece Name: /u01/oracle/bkp/arc20130219ORCL1oo2cavc_1_1

List of Archived Logs in backup set 49
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 1672835 19-FEB-13 1672872 19-FEB-13

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50 Full 9.42M DISK 00:00:01 19-FEB-13
BP Key: 50 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150621
Piece Name: /u01/app/oracle/c-1334549344-20130219-02
SPFILE Included: Modification time: 19-FEB-13
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1672883 Ckp time: 19-FEB-13

RMAN> show all; 
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN>

Recovery Manager complete.


Restore steps



Recovery Manager: Release 11.2.0.1.0 - Production on Tue Feb 19 15:37:40 2013

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

connected to target database (not started)

RMAN>  startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 159019008 bytes

Fixed Size 1335192 bytes
Variable Size 75497576 bytes
Database Buffers 79691776 bytes
Redo Buffers 2494464 bytes

RMAN>  set dbid=1334549344
executing command: SET DBID

RMAN>  set controlfile autobackup format for device type disk to '/u01/app/oracle/%F'
executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN>  resrote spfile from autobackup;
Starting restore at 19-FEB-13
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: looking for AUTOBACKUP on day: 20130219
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/c-1334549344-20130219-02
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/c-1334549344-20130219-02
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 19-FEB-13

RMAN>  startup noumount force ;
Oracle instance started

Total System Global Area 631914496 bytes

Fixed Size 1338364 bytes
Variable Size 482345988 bytes
Database Buffers 142606336 bytes
Redo Buffers 5623808 bytes


RMAN>
sql statement: alter system set control_files=''/u01/oracle/oradata/orcl/control01.dbf'',''/u01/oracle/oradata/orcl/control02.dbf'' scope=spfile

RMAN> startup nomount force;
Oracle instance started

Total System Global Area 631914496 bytes

Fixed Size 1338364 bytes
Variable Size 482345988 bytes
Database Buffers 142606336 bytes
Redo Buffers 5623808 bytes


RMAN> restore controlfile from autobackup;
Starting restore at 19-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130219
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/oracle/c-1334549344-20130219-02
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/c-1334549344-20130219-02
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oracle/oradata/orcl/control01.dbf
output file name=/u01/oracle/oradata/orcl/control02.dbf
Finished restore at 19-FEB-13

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

RMAN>run
{
set newname for database to '/u01/oracle/oradata/orcl/%b' ;
restore database;
switch datafile all;
switch tempfile all;
}

executing command: SET NEWNAME

Starting restore at 19-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/bkp/20130219ORCL1no2catl_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/bkp/20130219ORCL1no2catl_1_1 tag=TAG20130219T150524
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 19-FEB-13

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=807813386 file name=/u01/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=807813386 file name=/u01/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=807813386 file name=/u01/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=807813386 file name=/u01/oracle/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=807813386 file name=/u01/oracle/oradata/orcl/example01.dbf

renamed tempfile 1 to /u01/oracle/oradata/orcl/temp01.dbf in control file

RMAN>  SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo01.log''
TO ''/u01/oracle/oradata/orcl/redo01.log'' ";

sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo01.log''TO ''/u01/oracle/oradata/orcl/redo01.log''

RMAN> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo02.log''
TO ''/u01/oracle/oradata/orcl/redo02.log'' ";

sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo02.log''TO ''/u01/oracle/oradata/orcl/redo02.log''

RMAN> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log''
TO ''/u01/oracle/oradata/orcl/redo03.log'' ";

sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log''TO ''/u01/oracle/oradata/orcl/redo03.log''

RMAN>

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


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
47 93.13M DISK 00:00:02 19-FEB-13
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150521
Piece Name: /u01/oracle/bkp/arc20130219ORCL1mo2cath_1_1

List of Archived Logs in backup set 47
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 1668460 19-FEB-13 1671683 19-FEB-13
1 4 1671683 19-FEB-13 1672642 19-FEB-13
1 5 1672642 19-FEB-13 1672835 19-FEB-13

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48 Full 1.22G DISK 00:00:51 19-FEB-13
BP Key: 48 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150524
Piece Name: /u01/oracle/bkp/20130219ORCL1no2catl_1_1
List of Datafiles in backup set 48
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1672845 19-FEB-13 /u01/oracle/oradata/orcl/system01.dbf
2 Full 1672845 19-FEB-13 /u01/oracle/oradata/orcl/sysaux01.dbf
3 Full 1672845 19-FEB-13 /u01/oracle/oradata/orcl/undotbs01.dbf
4 Full 1672845 19-FEB-13 /u01/oracle/oradata/orcl/users01.dbf
5 Full 1672845 19-FEB-13 /u01/oracle/oradata/orcl/example01.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
49 80.50K DISK 00:00:00 19-FEB-13
BP Key: 49 Status: AVAILABLE Compressed: NO Tag: TAG20130219T150620
Piece Name: /u01/oracle/bkp/arc20130219ORCL1oo2cavc_1_1

List of Archived Logs in backup set 49
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 1672835 19-FEB-13 1672872 19-FEB-13



RMAN> recover database until sequence 7 ;
Starting recover at 19-FEB-13
using channel ORA_DISK_1

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

Finished recover at 19-FEB-13

RMAN> report shcema
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM *** /u01/oracle/oradata/orcl/system01.dbf
2 660 SYSAUX *** /u01/oracle/oradata/orcl/sysaux01.dbf
3 60 UNDOTBS1 *** /u01/oracle/oradata/orcl/undotbs01.dbf
4 5 USERS *** /u01/oracle/oradata/orcl/users01.dbf
5 100 EXAMPLE *** /u01/oracle/oradata/orcl/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 0 TEMP 32767 /u01/oracle/oradata/orcl/temp01.dbf

RMAN>  alter database open resetlogs;
database opened

RMAN>

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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50 Full 9.42M DISK 00:00:02 19-FEB-13
BP Key: 50 Status: AVAILABLE Compressed: NO Tag: TAG20130219T164944
Piece Name: /u01/app/oracle/c-1334549344-20130219-03
Control File Included: Ckp SCN: 1672939 Ckp time: 19-FEB-13

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 9.42M DISK 00:00:03 19-FEB-13
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20130219T164953
Piece Name: /u01/app/oracle/c-1334549344-20130219-04
Control File Included: Ckp SCN: 1673164 Ckp time: 19-FEB-13

RMAN>

Comments

Post a Comment

Popular posts from this blog

Error processing request - Contact your application administrator apex 20.1

Image
After upgrading 11.2.0.4 to 19c with apex 20.1 accessing apex_admin/apex pages was throwing “Error processing request” Contact your application administrator. There were three invalid objects for APEX_200100 WWV_FLOW_SESSION_RAS WWV_FLOW_WIZARD_API WWV_FLOW_DATA_LOADER Tried compiling these objects but compile was completing with PL/SQL: ORA-00942: table or view does not exist To resolve the issue following rant needs to be given then compile (ran both grant and compile as sysdba) grant select on SYS.DBA_XS_DYNAMIC_ROLES to APEX_200100; grant select on SYS.DBA_TAB_IDENTITY_COLS to APEX_200100; alter package APEX_200100.WWV_FLOW_SESSION_RAS compile body; alter package APEX_200100.WWV_FLOW_WIZARD_API compile body; alter package APEX_200100.WWV_FLOW_DATA_LOADER compile body; Returned 0 invalid object for APEX_200100  select * from dba_objects where status = 'INVALID' and owner = 'APEX_200100' ; Now both admin and apex pages ar
Read more

APP-FND-01436: List of Values cannot find any values for you to choose error from concurrent request program parameter (R12 12.1.3)

Image
APP-FND-01436: List of Values cannot find any values for you to choose error from concurrent request program parameter After upgrading oracle application 11i(11.5.10.2) to R12(12.1.3) running concurrent program request caused this issue When concurrent program is run for a concurrent request after upgrading to R12 12.1.3 the lov, which is attached to a parameter, gives APP-FND-01436: “List of Values cannot find any values for you to choose”. When the same request is run from oracle applications 11i 11.5.10.2 the same value set is working fine. The value set is based on a table. If you go and test the value set query from the value set definition screen it returns complete successful message yet if you go to the parameter of the concurrent request program form where the particular value set is used it gives you the above error. After searching google and oracle support I found metalink note 1400963.1, EBS R12.1: Parameter for Concurrent Request Using Table Based LOV G
Read more

WEB ADI - issues encountered during initial access

Image
On accessing create document link under Desktop Integrator responsibility browser was showing following error Set BNE UIX Physical Directory profile value to proper directory at site level Location = $COMMON_TOP/html/cabo/  (11i) Location = $COMMON_TOP/webapps/oacore/html/cabo/  (R12) As this was a cloned instance the value of this profile setting was pointing to a wrong location. set as BNE UIX Physical Directory /u03/oracle/prodcomn/html/cabo/ Access to functions are controlled by function security so to access and work with web adi the user should have proper access. You should have access to particular integrator to access and work with it Find out responsibility which is created to work with web adi check the main menu for this responsibility. Modify the responsibility or Menu if default is not fitting to your needs. For instance Desktop Integration Menu has create document, Define Lay out, Define Mapping, Manage Documents, Setup options Note : you must add
Read more