It is quite possible that we fall in a situation where we have to re-create a Database using the Backup (RMAN backup). It is not so rare scenario. The new Database can reside on new Server or existing server. Basic thing is,
We need to have a new database in Nomount state and Name of database should be Same. Means DB_NAME
Parameter should be like it was in old database. However, We can have different DB_UNIQUE_NAME for new database.
Another thing is, we cannot re-recreate a database on Windows machine using RMAN backup taken on Linux/Solaris OS and vice versa. Cross platform thing does not work here.
Following are the steps to recreate database from RMAN Backup.
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
1: In this database name will be same. but Instance name will be different.
2:- C:\Documents and Settings\20001>oradim -new -sid findb -intpwd seed -startmode m
3:- Create pfile. either copy any existing or create new one.
keep db_name as FINDB <-- found in init.ora file of OLD database
4:-create directory structure on OS level.
5:-follow steps
C:\Documents and Settings\20001>set oracle_sid=findb
C:\Documents and Settings\20001>sqlplus sys as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 2 14:47:37 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup nomount pfile=%oracle_home%\database\initFINDB.ora
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 239078276 bytes
Database Buffers 364904448 bytes
Redo Buffers 7135232 bytes
SQL> create spfile from pfile;
File created.
6: Put the RMAN backup to the new server on which you want to re-create the database.
7: Connect to RMAN
C:\Documents and Settings\20001>Set Oracle_sid=FINDB
C:\Documents and Settings\20001>rman target sys/seed
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 2 14:50:33 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: FINDB (not mounted)
As at this stage, we can see that show all shows all the vanilla setting of RMAN configuration.
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
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 ARCHIVELOG DELETION POLICY TO NONE; # default
RMAN> Set DBID=496261842
executing command: SET DBID
Our next step is to re-store the controlfile(s) from the BACKUPSET. Command to restore Control file dependes weather we are going to use AUTOBACKUP FORMAT or restoring from a particular backup set. Here we have little complexity. If we have the same directory structure on new server like we had on original server from where we had taken backup.
Eg. Following commands show the location (E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-00) of control file backup in backupset and on new server I do not have this location. So RESTORE FROM AUTOBACKUP would not work. So I will use RESTORE FROM statement.
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 13.80M DISK 00:00:02 20-DEC-11
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20111220T144031
Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-00
Control File Included: Ckp SCN: 9376006 Ckp time: 20-DEC-11
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 13.80M DISK 00:00:03 20-DEC-11
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20111220T144425
Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-01
Control File Included: Ckp SCN: 9376116 Ckp time: 20-DEC-11
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 13.80M DISK 00:00:02 20-DEC-11
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20111220T144814
Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-02
Control File Included: Ckp SCN: 9376236 Ckp time: 20-DEC-11
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 13.80M DISK 00:00:02 20-DEC-11
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20111220T151429
Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-03
Control File Included: Ckp SCN: 9377372 Ckp time: 20-DEC-11
8:
RMAN> Run
2> {
3> Set Controlfile Autobackup Format For Device Type Disk to 'F:\Rman_Bkup\FINDB_851_91\FINDB_CTL_%F';
4> RESTORE CONTROLFILE FROM 'F:\Rman_Bkup\FINDB_851_91\FINDB_CTLFILE_C-496261842-20111222-00';
5> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 02-JAN-12
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\FINDB\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\FINDB\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\FINDB\CONTROL03.CTL
Finished restore at 02-JAN-12
Setting DBID is very important before we start recovery process. If we do not set DBID and try to use above run block than we get following error
RMAN> run
2> {
3> Set Controlfile Autobackup Format For Device Type Disk to 'F:\BKUP\Seed_Bkup\SEED_CTL_%F';
4> Restore Controlfile from Autobackup;
5> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 27-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
recovery area destination: D:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: no autobackups found in the recovery area
autobackup search outside recovery area not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/27/2011 14:30:26
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
9:- Alter database mount;
RMAN> Alter database Mount;
database mounted
released channel: ORA_DISK_1
10:- if Database backup set expired then crosscheck backup once.
RMAN> list backup of datafile 3;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
30 Full 35.00M DISK 00:00:18 21-DEC-11
BP Key: 33 Status: EXPIRED Compressed: YES Tag: FULL_DB_BKP
Piece Name: D:\BKUP\SEED_BKUP\SEED_11MUP6LQ_1_1.BKP
List of Datafiles in backup set 30
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 16375812 21-DEC-11 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSAUX01.DBF
RMAN> Crosscheck Backup
RMAN> list backup of datafile 4;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31 Full 32.96M DISK 00:00:12 21-DEC-11
BP Key: 34 Status: AVAILABLE Compressed: YES Tag: FULL_DB_BKP
Piece Name: D:\BKUP\SEED_BKUP\SEED_12MUP6MJ_1_1.BKP
List of Datafiles in backup set 31
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 16375848 21-DEC-11 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF
11:- Restore database; <-- of backup is at its default location;
Now! Restore of database also has some of complexity. After Mounting the database, we check the backup sets and using “List Backup” command , we can see the location ,time and every other detail of backup set but the status of every backup set will be ‘EXPIRED’ because BACKUPSET are not on their default location. So we need to use catalog log command to update the BACKUPSET information in control file so that it actually reads our backup set. One easy method of doing so is
RMAN> CATALOG START WITH 'F:\Rman_Bkup\FINDB_851_91\FINDB_BKP_0' NOPROMPT;
searching for all files that match the pattern F:\Rman_Bkup\FINDB_851_91\FINDB_BKP_0
List of Files Unknown to the Database
=====================================
File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_07MUMK7R_1_1.BKP
File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_08MUMKJE_1_1.BKP
File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_09MUMKQD_1_1.BKP
File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_0BMUS65U_1_1.BKP
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_07MUMK7R_1_1.BKP
File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_08MUMKJE_1_1.BKP
File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_09MUMKQD_1_1.BKP
File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_0BMUS65U_1_1.BKP
Now, RMAN can use, newly cataloged backup set.
RMAN> Run
2> {
3> Allocate Channel ch1 device type disk format 'F:\Rman_Bkup\FINDB_851_91\FINDB_BKP_%U.BKP';
4> SET NEWNAME FOR DATAFILE 'E:\ORACLE\ORADATA\FINDB\PSINDEX2.DBF' TO 'F:\oracle\findb\PSINDEX2.DBF';
5> SET NEWNAME FOR DATAFILE 'E:\ORACLE\ORADATA\FINDB\PSINDEX.DBF' TO 'F:\oracle\findb\PSINDEX.DBF';
6> RESTORE DATABASE;
7> SWITCH DATAFILE ALL;
8> RESTORE DATABASE;
9> ALTER DATABASE OPEN RESETLOGS;
10> }
Any suggestion,query,correction will be highly appriciated.
Thanks and Regards
Jai
No comments:
Post a Comment