Tuesday, November 6, 2012

RMAN - Restore database in alternate/ secondary location using image copy backups


RMAN - Restore database in alternate/ secondary location using image copy backups

Objective:

To list the restore procedure in the event of complete loss of database files.
Assumption is that all database files (control file, datafiles, online redo logs, parameter file, password file) are lost in the primary location where the database is running.
Task is to restore and recover the database in a new/alternate/secondary location.
Assumption is that usable RMAN backups are available.
In the exercise, I have used image copy backups of datafiles(from netapp snap backup) to avoid the time delay in restoring the datafiles.

Environment:

RACPROD is a RAC database comprising of 3 instances running on servers’ rachost04/05/10. It is an 11.2.0.2 database.

Files needed to restore/ recover and open the database

Parameter file
Password file - for password file authentication. Here we will be using OS authentication.
Control files
Data files
Online redo log files
Archived Logs

Testing Restore/Recovery:

Decide the alternate location/server. Make sure necessary storage is available. Make sure the backups are accessible.

Restoring Parameter File from Control File autobackup

temphost | RACPROD | /export/oracle
> export ORACLE_SID=RACPROD
temphost | RACPROD | /export/oracle
> export PATH=$ORACLE_HOME/bin:$PATH
temphost | RACPROD | /export/oracle
> rman nocatalog

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Oct 10 15:45:44 2012

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

RMAN>


Obtain Database ID from control file autobackups. The highlighted part below is the DBID of the database.


temphost | orcl | /export/RACPROD/conv_oradata/RACPROD/ctl
> ls -ltr
total 165016
-rw-r----- 1 oracle dba 10911744 2012-10-18 05:03 c-2058292391-20121018-00
-rw-r----- 1 oracle dba 10911744 2012-10-18 06:02 c-2058292391-20121018-01
-rw-r----- 1 oracle dba 10911744 2012-10-18 07:13 c-2058292391-20121018-02
-rw-r----- 1 oracle dba  1228800 2012-10-19 05:04 control_file_bk_OraSnap.2012-10-19
-rw-r----- 1 oracle dba 10911744 2012-10-19 05:04 c-2058292391-20121019-00
-rw-r----- 1 oracle dba 10911744 2012-10-19 06:03 c-2058292391-20121019-01
-rw-r----- 1 oracle dba 10911744 2012-10-19 07:17 c-2058292391-20121019-02
-rw-r----- 1 oracle dba  1228800 2012-10-20 05:05 control_file_bk_OraSnap.2012-10-20


RMAN> CONNECT TARGET /

connected to target database (not started)

RMAN> SET DBID 2058292391

executing command: SET DBID

RMAN> STARTUP NOMOUNT

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/export/oracle/product/11.2.0.2/MAN11G/dbs/initRACPROD.ora'

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

Total System Global Area     158662656 bytes

Fixed Size                     2224584 bytes
Variable Size                 96472632 bytes
Database Buffers              54525952 bytes
Redo Buffers                   5439488 bytes



RMAN> RUN
2> {
3>   ALLOCATE CHANNEL c1 DEVICE TYPE disk ;
4>   SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/export/RACPROD/conv_oradata/RACPROD/ctl/%F';
5>   RESTORE SPFILE
6>     TO PFILE '/export/oracle/product/11.2.0.2/MAN11G/dbs/initRACPROD.ora'
7>     FROM AUTOBACKUP;
8>   SHUTDOWN ABORT;
9> }

allocated channel: c1
channel c1: SID=67 device type=DISK

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 10-OCT-12

channel c1: looking for AUTOBACKUP on day: 20121010
channel c1: looking for AUTOBACKUP on day: 20121009
channel c1: looking for AUTOBACKUP on day: 20121008
channel c1: looking for AUTOBACKUP on day: 20121007
channel c1: AUTOBACKUP found: /export/RACPROD/conv_oradata/RACPROD/ctl/c-2058292391-20121007-01
channel c1: restoring spfile from AUTOBACKUP /export/RACPROD/conv_oradata/RACPROD/ctl/c-2058292391-20121007-01
channel c1: SPFILE restore from AUTOBACKUP complete
Finished restore at 10-OCT-12

Oracle instance shut down

RMAN>


Edit the restored parameter file to make changes to reflect directories on the new server. Remove/Edit RAC based parameters like cluster_database, remote_listener, RACPROD1.thread, RACPROD2.undo_tablespace, etc. We will restore and open a single instance database and add instances later on if needed. Adding instances is not covered here. I will do that in another post.

After editing the restored parameter file

temphost | RACPROD | /export/oracle/product/11.2.0.2/MAN11G/dbs
> cat initRACPROD.ora
*.audit_file_dest='/export/oracle/diag/rdbms/DPROD/DPROD/adump'
*.cluster_database=false
*.compatible='11.2.0.2'
*.control_files='/export/RACPROD/conv_oradata/DPROD/control01.ctl','/export/RACPROD/conv_oradata/DPROD/control02.ctl','/export/RACPROD/conv_oradata/DPROD/control03.ctl'
*.db_block_size=8192
*.db_name='DPROD'
*.diagnostic_dest='/export/oracle'
*.open_cursors=300
*.parallel_max_servers=285
*.parallel_servers_target=128
*.pga_aggregate_target=400M
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=1G
*.undo_tablespace='UNDOTBS1'
*.log_archive_dest_1='LOCATION=/export/RACPROD/conv_oradata/DPROD/archive/'
*.log_archive_format=arch_%t_%s_%r.arc



Create directories as needed.

> mkdir -p /export/oracle/diag/rdbms/RACPROD/RACPROD/trace
> mkdir -p /export/oracle/diag/rdbms/RACPROD/RACPROD/alert
> mkdir -p /export/oracle/diag/rdbms/RACPROD/RACPROD/cdump
> mkdir -p /export/oracle/diag/rdbms/RACPROD/RACPROD/adump


Start an instance using the restored and edited parameter file

> sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 19 20:11:36 2012

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

Connected to an idle instance.

SQL> startup force nomount pfile='/export/oracle/product/11.2.0.2/MAN11G/dbs/initRACPROD.ora';
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233336 bytes
Variable Size             281021448 bytes
Database Buffers          780140544 bytes
Redo Buffers                5541888 bytes
SQL>

Restore the control file from an autobackup and then mount the database.

> rman nocatalog

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Oct 19 20:16:00 2012

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

RMAN> SET DBID 2058292391

executing command: SET DBID

RMAN> connect target /

connected to target database:  (not mounted)
using target database control file instead of recovery catalog

RMAN> RUN
2> {
3> ALLOCATE CHANNEL c1 DEVICE TYPE disk;
4> RESTORE CONTROLFILE FROM '/export/RACPROD/conv_oradata/RACPROD/ctl/c-2058292391-20121019-02';
5> ALTER DATABASE MOUNT;
6> }

allocated channel: c1
channel c1: SID=178 device type=DISK

Starting restore at 19-OCT-12

channel c1: restoring control file
channel c1: restore complete, elapsed time: 00:00:01
output file name=/export/RACPROD/conv_oradata/DPROD/control01.ctl
output file name=/export/RACPROD/conv_oradata/DPROD/control02.ctl
output file name=/export/RACPROD/conv_oradata/DPROD/control03.ctl
Finished restore at 19-OCT-12

database mounted
released channel: c1

RMAN>


Catalog the datafile backups(image copy) from the netapp snap restore.
Restore the archives to a subdirectory or directory of your choice and catalog them.

RMAN> CATALOG START WITH '/export/RACPROD/conv_oradata/DPROD/';

searching for all files that match the pattern /export/RACPROD/conv_oradata/DPROD/

List of Files Unknown to the Database
=====================================
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltpidx_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx1_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx2_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx3_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx4_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx5_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx6_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx7_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx8_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts1_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts2_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts3_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts4_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts5_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts6_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts7_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts8_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/sysaux01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/system01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/undotbs01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/undotbs02.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/undotbs03.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/users01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_1_166_790190759.arc
File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_1_167_790190759.arc
File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_1_168_790190759.arc
File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_2_112_790190759.arc
File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_3_160_790190759.arc

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltpidx_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx1_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx2_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx3_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx4_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx5_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx6_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx7_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_idx8_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts1_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts2_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts3_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts4_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts5_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts6_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts7_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/oltp_ts8_01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/sysaux01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/system01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/undotbs01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/undotbs02.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/undotbs03.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/users01.dbf
File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_1_166_790190759.arc
File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_1_167_790190759.arc
File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_1_168_790190759.arc
File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_2_112_790190759.arc
File Name: /export/RACPROD/conv_oradata/DPROD/archive/arch_3_160_790190759.arc

RMAN>


Recover the database.
Note:  No restore is needed here as I use files(image copies) from the netapp snap backup to save restore time.
In general case, the restore command below should be left uncommented.

> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Oct 19 20:24:41 2012

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

connected to target database: RACPROD (DBID=2058292391, not open)

RMAN> RUN
2> {
3>   ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
4>   # rename the data files and online redo logs
5>   SET NEWNAME FOR DATAFILE 1 TO '/export/RACPROD/conv_oradata/DPROD/system01.dbf';
6>   SET NEWNAME FOR DATAFILE 2 TO '/export/RACPROD/conv_oradata/DPROD/sysaux01.dbf';
7>   SET NEWNAME FOR DATAFILE 3 TO '/export/RACPROD/conv_oradata/DPROD/undotbs01.dbf';
8>   SET NEWNAME FOR DATAFILE 4 TO '/export/RACPROD/conv_oradata/DPROD/users01.dbf';
9>   SET NEWNAME FOR DATAFILE 5 TO '/export/RACPROD/conv_oradata/DPROD/undotbs02.dbf';
10>   SET NEWNAME FOR DATAFILE 6 TO '/export/RACPROD/conv_oradata/DPROD/undotbs03.dbf';
11>   SET NEWNAME FOR DATAFILE 7 TO '/export/RACPROD/conv_oradata/DPROD/oltp_01.dbf';
12>   SET NEWNAME FOR DATAFILE 8 TO '/export/RACPROD/conv_oradata/DPROD/oltpidx_01.dbf';
13>   SET NEWNAME FOR DATAFILE 9 TO '/export/RACPROD/conv_oradata/DPROD/oltp_ts1_01.dbf';
14>   SET NEWNAME FOR DATAFILE 10 TO '/export/RACPROD/conv_oradata/DPROD/oltp_ts2_01.dbf';
15>   SET NEWNAME FOR DATAFILE 11 TO '/export/RACPROD/conv_oradata/DPROD/oltp_ts3_01.dbf';
16>   SET NEWNAME FOR DATAFILE 12 TO '/export/RACPROD/conv_oradata/DPROD/oltp_ts4_01.dbf';
17>   SET NEWNAME FOR DATAFILE 13 TO '/export/RACPROD/conv_oradata/DPROD/oltp_ts5_01.dbf';
18>   SET NEWNAME FOR DATAFILE 14 TO '/export/RACPROD/conv_oradata/DPROD/oltp_ts6_01.dbf';
19>   SET NEWNAME FOR DATAFILE 15 TO '/export/RACPROD/conv_oradata/DPROD/oltp_ts7_01.dbf';
20>   SET NEWNAME FOR DATAFILE 16 TO '/export/RACPROD/conv_oradata/DPROD/oltp_ts8_01.dbf';
21>   SET NEWNAME FOR DATAFILE 17 TO '/export/RACPROD/conv_oradata/DPROD/oltp_idx1_01.dbf';
22>   SET NEWNAME FOR DATAFILE 18 TO '/export/RACPROD/conv_oradata/DPROD/oltp_idx2_01.dbf';
23>   SET NEWNAME FOR DATAFILE 19 TO '/export/RACPROD/conv_oradata/DPROD/oltp_idx3_01.dbf';
24>   SET NEWNAME FOR DATAFILE 20 TO '/export/RACPROD/conv_oradata/DPROD/oltp_idx4_01.dbf';
25>   SET NEWNAME FOR DATAFILE 21 TO '/export/RACPROD/conv_oradata/DPROD/oltp_idx5_01.dbf';
26>   SET NEWNAME FOR DATAFILE 22 TO '/export/RACPROD/conv_oradata/DPROD/oltp_idx6_01.dbf';
27>   SET NEWNAME FOR DATAFILE 23 TO '/export/RACPROD/conv_oradata/DPROD/oltp_idx7_01.dbf';
28>   SET NEWNAME FOR DATAFILE 24 TO '/export/RACPROD/conv_oradata/DPROD/oltp_idx8_01.dbf';
29>   SQL "ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo01a.log''
30>       TO ''/export/RACPROD/conv_oradata/DPROD/redo01a.log'' ";
31>   SQL "ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo01b.log''
32>       TO ''/export/RACPROD/conv_oradata/DPROD/redo01b.log'' ";
33>   SQL "ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo02a.log''
34>       TO ''/export/RACPROD/conv_oradata/DPROD/redo02a.log'' ";
35>   SQL "ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo02b.log''
36>       TO ''/export/RACPROD/conv_oradata/DPROD/redo02b.log'' ";
37>   SQL "ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo03a.log''
38>       TO ''/export/RACPROD/conv_oradata/DPROD/redo03a.log'' ";
39>   SQL "ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo03b.log''
40>       TO ''/export/RACPROD/conv_oradata/DPROD/redo03b.log'' ";
41>   SQL "ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo04a.log''
42>       TO ''/export/RACPROD/conv_oradata/DPROD/redo04a.log'' ";
43>   SQL "ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo04b.log''
44>       TO ''/export/RACPROD/conv_oradata/DPROD/redo04b.log'' ";
45>   SQL "ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo05a.log''
46>       TO ''/export/RACPROD/conv_oradata/DPROD/redo05a.log'' ";
47>   SQL "ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo05b.log''
48>       TO ''/export/RACPROD/conv_oradata/DPROD/redo05b.log'' ";
49>   SQL "ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo06a.log''
50>       TO ''/export/RACPROD/conv_oradata/DPROD/redo06a.log'' ";
51>   SQL "ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo06b.log''
52>       TO ''/export/RACPROD/conv_oradata/DPROD/redo06b.log'' ";
53>   # restore the database and switch the data file names
54>   # RESTORE DATABASE; WE USE IMAGE COPY FROM SNAP RESTORE, SO NO RESTORE NEEDED
55>   SWITCH DATAFILE ALL;
56>   # recover the database
57>   RECOVER DATABASE;
58> }

EXITusing target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=178 device type=DISK

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

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo01a.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo01a.log''

sql statement: ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo01b.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo01b.log''

sql statement: ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo02a.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo02a.log''

sql statement: ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo02b.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo02b.log''

sql statement: ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo03a.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo03a.log''

sql statement: ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo03b.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo03b.log''

sql statement: ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo04a.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo04a.log''

sql statement: ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo04b.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo04b.log''

sql statement: ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo05a.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo05a.log''

sql statement: ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo05b.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo05b.log''

sql statement: ALTER DATABASE RENAME FILE ''/u20_RACPROD/redo1/RACPROD/redo06a.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo06a.log''

sql statement: ALTER DATABASE RENAME FILE ''/u21_RACPROD/redo2/RACPROD/redo06b.log''      TO ''/export/RACPROD/conv_oradata/DPROD/redo06b.log''

datafile 1 switched to datafile copy
input datafile copy RECID=908 STAMP=797113161 file name=/export/RACPROD/conv_oradata/DPROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=907 STAMP=797113161 file name=/export/RACPROD/conv_oradata/DPROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=909 STAMP=797113161 file name=/export/RACPROD/conv_oradata/DPROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=912 STAMP=797113161 file name=/export/RACPROD/conv_oradata/DPROD/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=910 STAMP=797113161 file name=/export/RACPROD/conv_oradata/DPROD/undotbs02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=911 STAMP=797113161 file name=/export/RACPROD/conv_oradata/DPROD/undotbs03.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=889 STAMP=797113159 file name=/export/RACPROD/conv_oradata/DPROD/oltp_01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=890 STAMP=797113159 file name=/export/RACPROD/conv_oradata/DPROD/oltpidx_01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=899 STAMP=797113160 file name=/export/RACPROD/conv_oradata/DPROD/oltp_ts1_01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=900 STAMP=797113160 file name=/export/RACPROD/conv_oradata/DPROD/oltp_ts2_01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=901 STAMP=797113160 file name=/export/RACPROD/conv_oradata/DPROD/oltp_ts3_01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=902 STAMP=797113160 file name=/export/RACPROD/conv_oradata/DPROD/oltp_ts4_01.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=903 STAMP=797113160 file name=/export/RACPROD/conv_oradata/DPROD/oltp_ts5_01.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=904 STAMP=797113161 file name=/export/RACPROD/conv_oradata/DPROD/oltp_ts6_01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=905 STAMP=797113161 file name=/export/RACPROD/conv_oradata/DPROD/oltp_ts7_01.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=906 STAMP=797113161 file name=/export/RACPROD/conv_oradata/DPROD/oltp_ts8_01.dbf
datafile 17 switched to datafile copy
input datafile copy RECID=891 STAMP=797113159 file name=/export/RACPROD/conv_oradata/DPROD/oltp_idx1_01.dbf
datafile 18 switched to datafile copy
input datafile copy RECID=892 STAMP=797113159 file name=/export/RACPROD/conv_oradata/DPROD/oltp_idx2_01.dbf
datafile 19 switched to datafile copy
input datafile copy RECID=893 STAMP=797113159 file name=/export/RACPROD/conv_oradata/DPROD/oltp_idx3_01.dbf
datafile 20 switched to datafile copy
input datafile copy RECID=894 STAMP=797113160 file name=/export/RACPROD/conv_oradata/DPROD/oltp_idx4_01.dbf
datafile 21 switched to datafile copy
input datafile copy RECID=895 STAMP=797113160 file name=/export/RACPROD/conv_oradata/DPROD/oltp_idx5_01.dbf
datafile 22 switched to datafile copy
input datafile copy RECID=896 STAMP=797113160 file name=/export/RACPROD/conv_oradata/DPROD/oltp_idx6_01.dbf
datafile 23 switched to datafile copy
input datafile copy RECID=897 STAMP=797113160 file name=/export/RACPROD/conv_oradata/DPROD/oltp_idx7_01.dbf
datafile 24 switched to datafile copy
input datafile copy RECID=898 STAMP=797113160 file name=/export/RACPROD/conv_oradata/DPROD/oltp_idx8_01.dbf

Starting recover at 19-OCT-12

starting media recovery

archived log for thread 1 with sequence 168 is already on disk as file /export/RACPROD/conv_oradata/DPROD/archive/arch_1_168_790190759.arc
archived log for thread 2 with sequence 112 is already on disk as file /export/RACPROD/conv_oradata/DPROD/archive/arch_2_112_790190759.arc
archived log for thread 3 with sequence 160 is already on disk as file /export/RACPROD/conv_oradata/DPROD/archive/arch_3_160_790190759.arc
archived log file name=/export/RACPROD/conv_oradata/DPROD/archive/arch_3_160_790190759.arc thread=3 sequence=160
archived log file name=/export/RACPROD/conv_oradata/DPROD/archive/arch_1_168_790190759.arc thread=1 sequence=168
archived log file name=/export/RACPROD/conv_oradata/DPROD/archive/arch_2_112_790190759.arc thread=2 sequence=112
unable to find archived log
archived log thread=2 sequence=113
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/19/2012 20:25:07
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 113 and starting SCN of 8609469857




You just need to open the database with RESETLOGS option, as there are no more archivelogs to apply.

The recovery process is looking for thread 2, sequence 113. This is the current sequence in production and is not archived yet.

Open the database with resetlogs option. The online redo logs get created at this point. Notice the sequence number reset for the threads.

RMAN> alter database open resetlogs;

database opened

SQL> select * from v$log order by 2,3;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME         NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
         2          1          0 1048576000        512          2 YES UNUSED                       0                               0
         1          1          1 1048576000        512          2 NO  CURRENT             8609469858 19-OCT-12            2.8147E+14
         4          2          0 1048576000        512          2 YES UNUSED                       0                               0
         3          2          1 1048576000        512          2 YES INACTIVE            8609469858 19-OCT-12            8609469866 19-OCT-12
         6          3          0 1048576000        512          2 YES UNUSED                       0                               0
         5          3          1 1048576000        512          2 YES INACTIVE            8609469858 19-OCT-12            8609469868 19-OCT-12

6 rows selected.


Add tempfile

SQL> create temporary tablespace TEMP2 tempfile '/export/RACPROD/conv_oradata/DPROD/temp2.dbf' size 100M;

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> drop tablespace temp;

Tablespace dropped.

SQL> create temporary tablespace TEMP tempfile '/export/RACPROD/conv_oradata/DPROD/temp1.dbf' size 100M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace temp2;

Tablespace dropped.


If you want to change the database name, do the below steps. The example here shows database name changed from RACPROD to DPROD. We use utility ‘nid’ to accomplish this task.

Startup the database in mount mode.

temphost | RACPROD | /export/oracle
> sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 19 20:41:22 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233336 bytes
Variable Size             281021448 bytes
Database Buffers          780140544 bytes
Redo Buffers                5541888 bytes
Database mounted.
SQL>



> nid target=/ DBNAME=DPROD

DBNEWID: Release 11.2.0.2.0 - Production on Fri Oct 19 20:46:18 2012

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

Connected to database RACPROD (DBID=2058292391)

Connected to server version 11.2.0

Control Files in database:
    /export/RACPROD/conv_oradata/DPROD/control01.ctl
    /export/RACPROD/conv_oradata/DPROD/control02.ctl
    /export/RACPROD/conv_oradata/DPROD/control03.ctl

Change database ID and database name RACPROD to DPROD? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2058292391 to 450565595
Changing database name from RACPROD to DPROD
    Control File /export/RACPROD/conv_oradata/DPROD/control01.ctl - modified
    Control File /export/RACPROD/conv_oradata/DPROD/control02.ctl - modified
    Control File /export/RACPROD/conv_oradata/DPROD/control03.ctl - modified
    Datafile /export/RACPROD/conv_oradata/DPROD/system01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/sysaux01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/undotbs01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/users01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/undotbs02.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/undotbs03.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltpidx_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_ts1_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_ts2_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_ts3_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_ts4_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_ts5_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_ts6_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_ts7_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_ts8_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_idx1_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_idx2_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_idx3_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_idx4_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_idx5_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_idx6_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_idx7_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/oltp_idx8_01.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/temp1.db - dbid changed, wrote new name
    Datafile /export/RACPROD/conv_oradata/DPROD/temp2.db - dbid changed, wrote new name
    Control File /export/RACPROD/conv_oradata/DPROD/control01.ctl - dbid changed, wrote new name
    Control File /export/RACPROD/conv_oradata/DPROD/control02.ctl - dbid changed, wrote new name
    Control File /export/RACPROD/conv_oradata/DPROD/control03.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to DPROD.
Modify parameter file and generate a new password file before restarting.
Database ID for database DPROD changed to 450565595.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

temphost | RACPROD | /export/oracle
> 


·         Edit Pfile to make changes to db_name parameter.
·         Rename pfile to the new db name.
·         Modify /etc/oratab entry.
·         Create directories as required for dump and trace files.
·         Create the password file using orapwd utility.
·         Open the database with resetlogs option.



> cat /etc/oratab |tail
DBESTG:/export/oracle/product/11.2.0.2/MAN11G:N
CCRDEV:/export/oracle/product/11.2.0.2/MAN11G:N
DPROD:/export/oracle/product/11.2.0.2/MAN11G:N


SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      DPROD


SQL> set lines 200
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME         NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------ ------------ ------------------
         1          1          1 1048576000        512          2 YES INACTIVE            8609472546 19-OCT-12            8609492553 22-OCT-12
         2          1          2 1048576000        512          2 NO  CURRENT             8609492553 22-OCT-12            2.8147E+14
         3          2          0 1048576000        512          2 YES UNUSED                       0                               0
         4          2          0 1048576000        512          2 YES UNUSED                       0                               0
         5          3          0 1048576000        512          2 YES UNUSED                       0                               0
         6          3          0 1048576000        512          2 YES UNUSED                       0                               0

6 rows selected.


At this point we have successfully recreated the database. Backup the database before releasing to users.






No comments:

Post a Comment