Tuesday, January 15, 2013

Restoring RMAN backup to new server

Server information

 Backup Location for database

D:\rmanbackup\db\

 Backup Location for Archivelog
D:\rmanbackup\ar\

 Backup Location for Controlfile Auto Backup
D:\rmanbackup\ctl\

 -------------------------------------------------------------------------------------------------
Backup Script
RUN {
ALLOCATE CHANNEL ch00 TYPE DISK FORMAT 'D:\rmanbackup\db\%d_DB_%u_%s_%p';
set controlfile autobackup format for device type disk to "D:\rmanbackup\ctl\cf_HOMEdb.%F";
backup
filesperset 4
INCREMENTAL LEVEL 0 AS COMPRESSED BACKUPSET database;
sql "alter system archive log current";
release channel ch00;
ALLOCATE CHANNEL CH01 TYPE DISK FORMAT 'D:\RMANBACKUP\AR\al_%U';
backup 
filesperset 4
AS COMPRESSED BACKUPSET archivelog all;
RELEASE CHANNEL CH01;
}
---------------------------------------------------------------------------------------------
Add Instance on Destination Server
C:\Windows\system32>oradim -new -sid HOMEdb
Instance created.


 Create directory structure on Destination server to copy backup pieces
D:\rmanbackup\db
D:\rmanbackup\ar
--------------------------------------------------------------------------------------------------
Copy backup pieces on Destination Servers
--------------------------------------------------------------------------------------------------
1-  first set ORACLE_SID and the DBID of the source DB

C:\> set ORACLE_SID=HOMEdb

RMAN> set dbid=1547250382

executing command: SET DBID

2- Create the dummy pfile

Note: In dummy parameter file never use 
db_recovery_file_dest 
and 
db_recovery_file_dest_size 
parameters otherwise you will encounter this error
1RMAN-00571: ===========================================================
42RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
43RMAN-00571: ===========================================================
44RMAN-03002: failure of restore command at 11/18/2010 08:46:22
45RMAN-06026: some targets not found - aborting restore
46RMAN-06023: no backup or copy of datafile 4 found to restore
47RMAN-06023: no backup or copy of datafile 3 found to restore
48RMAN-06023: no backup or copy of datafile 1 found to restore
Explanation

Metalink document 965122.1.

The problem were those autobackups in FRA that I showed before. That files belonged to different incarnation than the available backups current incarnation.

[From Metalink document]
If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined, RMAN execute and implicit crosscheck and catalog of all the objects in the Flash Recovery Area.

RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURRENT incarnation in the controlfile then changes controlfile CURRENT incarnation to the one found in the file that is being cataloged.

This prevents database from restoring backups that belong to old CURRENT incarnation. 
RMAN considers backup availble for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.
So I’ve decided to temporary disable FRA during recovery process commentingdb_recovery_file_dest and db_recovery_file_dest_size parameters in parameter file.

 Add only these parameters to dummy parameter file
-------------------------------------------------------------

 large_pool_size=100m
shared_pool_size=1024m
db_cache_size=300m
audit_file_dest='D:\app\FARHAT\admin\HOMEdb\adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='D:\app\FARHAT\oradata\HOMEdb\CONTROL1.CTL'
db_block_size=8192
db_domain=''
db_name='HOMEdb'
diagnostic_dest='D:\app\FARHAT'
remote_login_passwordfile='exclusive'


--------------------------------------------------------------
3- Start the instance with duumy pfile

RMAN> startup nomount pfile='c:\temp\recotest\pfileHOMEdb.ora'

connected to target database (not started)
Oracle instance started

Total System Global Area    1553379328 bytes

Fixed Size                     2255464 bytes
Variable Size               1224738200 bytes
Database Buffers             318767104 bytes
Redo Buffers                   7618560 bytes



 Connect to  rman and first check availability in backup pieces using "preview" in addition to restore command  


 C:\Windows\system32>rman target /

 Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 15 11:57:50 2013

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

 connected to target database: not started

 RMAN> set dbid 1547250382

 RMAN> startup nomount pfile='d:\rmanbackup\initHOMEdb.ora';

 RMAN> restore controlfile to 'D:\app\FARHAT\oradata\HOMEdb\CONTROL01.CTL' from 'D:\rmanbackup\db\HOMEDB_DB_BSNVCO24_5500_1' preview;

 Starting restore at 15-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=218 device type=DISK

 Finished restore at 15-JAN-13

 Run same command to restore CONTROLFILE without preview switch

 RMAN> restore controlfile to 'D:\app\FARHAT\oradata\HOMEdb\CONTROL01.CTL' from 'D:\rmanbackup\db\HOMEDB_DB_BSNVCO24_5500_1' ;
Starting restore at 15-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=218 device type=DISK

 Finished restore at 15-JAN-13

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



You can also use auto backup for restoring controlfile using 
 restore controlfile to 'D:\app\FARHAT\oradata\HOMEdb\CONTROL01.CTL' from autobackup ;
---------------------------------------------------------------------------------------------
sqlplus / as sysdba
SQL> alter system set db_recovery_file_dest_size='';

 System altered.

SQL> alter system set db_recovery_file_dest='';

 System altered.


 ---------------------------------------------------------------------------------------------
RMAN> delete backup;

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

 RMAN> catalog backuppiece 'D:\rmanbackup\db\HOMEDB_DB_BNNVCMRO_5495_1';

 cataloged backup piece
backup piece handle=D:\RMANBACKUP\DB\HOMEDB_DB_BNNVCMRO_5495_1 RECID=5376 STAMP=804761443

 RMAN> catalog backuppiece 'D:\rmanbackup\db\HOMEDB_DB_BONVCN2P_5496_1';

 cataloged backup piece
backup piece handle=D:\RMANBACKUP\DB\HOMEDB_DB_BONVCN2P_5496_1 RECID=5377 STAMP=804761443

 RMAN> catalog backuppiece 'D:\rmanbackup\db\HOMEDB_DB_BPNVCN9R_5497_1';

 cataloged backup piece
backup piece handle=D:\RMANBACKUP\DB\HOMEDB_DB_BPNVCN9R_5497_1 RECID=5378 STAMP=804761444

 RMAN> catalog backuppiece 'D:\rmanbackup\db\HOMEDB_DB_BQNVCNM7_5498_1';

 cataloged backup piece
backup piece handle=D:\RMANBACKUP\DB\HOMEDB_DB_BQNVCNM7_5498_1 RECID=5379 STAMP=804761445

 RMAN> catalog backuppiece 'D:\rmanbackup\db\HOMEDB_DB_BRNVCNRM_5499_1';

 cataloged backup piece
backup piece handle=D:\RMANBACKUP\DB\HOMEDB_DB_BRNVCNRM_5499_1 RECID=5380 STAMP=804761446

 RMAN> catalog backuppiece 'D:\rmanbackup\db\HOMEDB_DB_BSNVCO24_5500_1';

 cataloged backup piece
backup piece handle=D:\RMANBACKUP\DB\HOMEDB_DB_BSNVCO24_5500_1 RECID=5381 STAMP=804761446

 RMAN> catalog backuppiece 'D:\rmanbackup\ar\AL_BUNVCO2V_1_1';

 cataloged backup piece
backup piece handle=D:\RMANBACKUP\AR\AL_BUNVCO2V_1_1 RECID=5382 STAMP=804761447

 RMAN> catalog backuppiece 'D:\rmanbackup\ar\AL_BVNVCO32_1_1';

 cataloged backup piece
backup piece handle=D:\RMANBACKUP\AR\AL_BVNVCO32_1_1 RECID=5383 STAMP=804761451

 RMAN> list backup summary;

 -----------------------------------------------------------------------------------------------
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---

5344    B  A  A SBT_TAPE    13-JAN-13       1       1       NO         TAG20130113T072055
5345    B  F  A SBT_TAPE    13-JAN-13       1       1       NO         TAG20130113T072205
5346    B  F  A SBT_TAPE    14-JAN-13       1       1       NO         TAG20130114T070103
5347    B  F  A SBT_TAPE    14-JAN-13       1       1       NO         TAG20130114T070103
5348    B  F  A SBT_TAPE    14-JAN-13       1       1       NO         TAG20130114T070103
5349    B  F  A SBT_TAPE    14-JAN-13       1       1       NO         TAG20130114T070103
5350    B  F  A SBT_TAPE    14-JAN-13       1       1       NO         TAG20130114T070103
5351    B  F  A SBT_TAPE    14-JAN-13       1       1       NO         TAG20130114T072201
5352    B  A  A SBT_TAPE    14-JAN-13       1       1       NO         TAG20130114T072300
5353    B  F  A SBT_TAPE    14-JAN-13       1       1       NO         TAG20130114T072520
5359    B  0  A DISK        14-JAN-13       1       1       YES        TAG20130114T084023
5360    B  0  A DISK        14-JAN-13       1       1       YES        TAG20130114T084023
5361    B  0  A DISK        14-JAN-13       1       1       YES        TAG20130114T084023
5362    B  0  A DISK        14-JAN-13       1       1       YES        TAG20130114T084023
5363    B  0  A DISK        14-JAN-13       1       1       YES        TAG20130114T084023
5364    B  0  A DISK        14-JAN-13       1       1       YES        TAG20130114T084023
5365    B  A  A DISK        14-JAN-13       1       1       YES        TAG20130114T090119
5366    B  A  A DISK        14-JAN-13       1       1       YES        TAG20130114T090119


 ---------------------------------------------------------------------------------------------
Restore Database

run {
ALLOCATE CHANNEL ch00 device TYPE DISK;
set newname for datafile 1 to 'D:\app\FARHAT\oradata\HOMEdb\system.256.770482167';
set newname for datafile 2 to 'D:\app\FARHAT\oradata\HOMEdb\sysaux.257.770482167';
set newname for datafile 3 to 'D:\app\FARHAT\oradata\HOMEdb\undotbs1.258.770482167';
set newname for datafile 4 to 'D:\app\FARHAT\oradata\HOMEdb\users.259.770482167';
set newname for datafile 5 to 'D:\app\FARHAT\oradata\HOMEdb\example.264.770482273';
set newname for datafile 6 to 'D:\app\FARHAT\oradata\HOMEdb\undotbs2.265.770482381';
set newname for datafile 7 to 'D:\app\FARHAT\oradata\HOMEdb\HOMElog01.dbf';
set newname for datafile 8 to 'D:\app\FARHAT\oradata\HOMEdb\test_netbkup.dbf';
set newname for datafile 9 to 'D:\app\FARHAT\oradata\HOMEdb\recop1.dbf';
set newname for datafile 10 to 'D:\app\FARHAT\oradata\HOMEdb\HOMElog02.dbf';
set newname for datafile 11 to 'D:\app\FARHAT\oradata\HOMEdb\HOME_ts01.dbf';
set newname for datafile 12 to 'D:\app\FARHAT\oradata\HOMEdb\HOME_ts02.dbf';
set newname for datafile 13 to 'D:\app\FARHAT\oradata\HOMEdb\HOME_ts03.dbf';
set newname for datafile 14 to 'D:\app\FARHAT\oradata\HOMEdb\HOME_ts04.dbf';
set newname for datafile 15 to 'D:\app\FARHAT\oradata\HOMEdb\HOME_ts05.dbf';
set newname for datafile 16 to 'D:\app\FARHAT\oradata\HOMEdb\undotbs1.278.770746419';
set newname for datafile 17 to 'D:\app\FARHAT\oradata\HOMEdb\undotbs2.279.770746495';
restore database;
switch datafile all;
RELEASE CHANNEL ch00;
}
-----------------------------------------------------------------------------------------


Recover Database
RMAN> run {
ALLOCATE CHANNEL ch00 device TYPE DISK;
set until SCN 1855075485;
recover database;
alter database open resetlogs;
RELEASE CHANNEL ch00;
}

 allocated channel: ch00
channel ch00: SID=216 device type=DISK

 executing command: SET until clause

 Starting recover at 15-JAN-13

 starting media recovery

 archived log for thread 1 with sequence 30165 is already on disk as file D:\APP\FARHAT\PRODUCT\11.2.0.3\DBHOME_1\RDBMS\AR
C0000030165_0770482257.0001
archived log for thread 1 with sequence 30166 is already on disk as file D:\APP\FARHAT\PRODUCT\11.2.0.3\DBHOME_1\RDBMS\AR
C0000030166_0770482257.0001
archived log for thread 2 with sequence 27601 is already on disk as file D:\APP\FARHAT\PRODUCT\11.2.0.3\DBHOME_1\RDBMS\AR
C0000027601_0770482257.0002
archived log file name=D:\APP\FARHAT\PRODUCT\11.2.0.3\DBHOME_1\RDBMS\ARC0000030165_0770482257.0001 thread=1 sequence=3016
5
archived log file name=D:\APP\FARHAT\PRODUCT\11.2.0.3\DBHOME_1\RDBMS\ARC0000027601_0770482257.0002 thread=2 sequence=2760
1
archived log file name=D:\APP\FARHAT\PRODUCT\11.2.0.3\DBHOME_1\RDBMS\ARC0000030166_0770482257.0001 thread=1 sequence=3016
6
media recovery complete, elapsed time: 00:00:20
Finished recover at 15-JAN-13

 released channel: ch00
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 01/15/2013 11:14:31
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '+HOMEdbdata'
ORA-00312: online log 2 thread 1: '+HOMEdbflash'


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

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

 C:\Windows\system32>set oracle_sid=HOMEdb

 C:\Windows\system32>sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 15 11:15:04 2013

 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> show parameter recovery

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
SQL> alter system set db_recovery_file_dest_size='50GB';
alter system set db_recovery_file_dest_size='50GB'
*
ERROR at line 1:
ORA-32005: error while parsing size specification [50GB]
SQL> alter system set db_recovery_file_dest_size='50G';

 System altered.

 SQL> alter system set db_recovery_file_dest='D:\app\FARHAT\fast_recovery_area';

 System altered.

 SQL> alter database clear logfile group 2;

 Database altered.

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

 SQL> alter database clear logfile group 4;

 Database altered.

 SQL> alter database open resetlogs;

 Database altered.

 SQL>





































Posted by at
Labels: ,

No comments:

Post a Comment