Restore and Recover database to a new host

In this post I will show how I can restore and recover a database to another host. In this case DBID of the database will be the same as of original database. But whenever your purpose is to create a new copy of the database then use RMAN DUPLICATE; this will change the DBID of the new database.

In this example my database name dbase1 and the database dbase1 is running on neptune machine. I like to take a backup on neptune machine, transfer the backup to saturn machine and perform restore and recover in saturn machine.

1)In neptune machine(Source)

RMAN> backup database;

Starting backup at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbf
input datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbf
input datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
input datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
input datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbf
input datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbf
input datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
input datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 06-MAY-08

Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAY-08

2)Transfer this two backup pieces to target machine(From Neptune)
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_nnndf_TAG20080 100% |***********************************************| 525 MB 00:59
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_s_654016132_42 100% |***********************************************| 6976 KB 00:00

3)Determine the DBID of source machine(Neptune here)

SQL> select dbid from v$database;
DBID
----------
3386862614

4)Now perform task on target machine(Saturn here).
First set ORACLE_SID,
-bash-3.00$export ORACLE_SID=dbase1
Then connect to rman,
-bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 05:22:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)

5)Set DBID and restore spfile to pfile.
RMAN> set dbid 3386862614
executing command: SET DBID

RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'

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

Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 bytes


RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';

Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08

Open the pfile with an editor file and if you wish change the location
6)start the instance with pfile.
RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';

Oracle instance started

Total System Global Area 209715200 bytes

Fixed Size 2019608 bytes
Variable Size 109055720 bytes
Database Buffers 92274688 bytes
Redo Buffers 6365184 bytes

7)Restore controlfile and mount the database.
RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';

Starting restore at 06-MAY-08
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oradata2/DBase1/control01.ctl
output filename=/oradata2/DBase1/control02.ctl
output filename=/oradata2/DBase1/control03.ctl
Finished restore at 06-MAY-08

RMAN> ALTER DATABASE MOUNT;

database mounted
released channel: ORA_DISK_1

8)From SQL*Plus determine the data file and redo log file name.
SQL> COLUMN NAME FORMAT a70
SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
2 UNION
3* SELECT GROUP#,MEMBER FROM V$LOGFILE

File/Grp# NAME
---------- ------------------------------------------------------------
1 /oradata2/data1/dbase1/redo01.log
1 /oradata2/data1/dbase1/system01.dbf
2 /oradata2/data1/dbase1/redo02.log
2 /oradata2/data1/dbase1/undotbs01.dbf
3 /oradata2/data1/dbase1/redo03.log
3 /oradata2/data1/dbase1/sysaux01.dbf
4 /oradata2/data1/dbase1/users01.dbf
5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf

11 rows selected.

9)Catalog your backuppiece.
RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';

cataloged backuppiece
backup piece handle=/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
recid=33 stamp=65398295
RMAN> list backup;


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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 525.67M DISK 00:01:31 06-MAY-08
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716
Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
List of Datafiles in backup set 32
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf
2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf
3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf
4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf
5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf

10)Make a script by issuing SET NEWNAME if you want different file name other than source.

In the script issue SET UNTIL clause and restore and recover database.
RMAN> @/export/home/oracle/rman

RMAN> run{
2> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
3> set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
4> set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
5> set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
6> set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
7> set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
8> set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
9> set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
10> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
11>
12> SET UNTIL SCN 745212;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }

11)Open the Database resetlogs option.
RMAN> alter database open resetlogs;

database opened.

Comments

Post a Comment

Popular posts from this blog

How to make partitioning in Oracle more Quickly

As the table size grows and full table scans happens more frequently then there is no alternative than partitioning in oracle. Partition greatly enhance performance of a query. I will start my partitioning system simply with an example. In my database the following query took 30~35 seconds to complete. After partitioning the performance increased amazingly and it then took only 1 second. My query was, SELECT DISTINCT fs.type, fs.id , fs.pid, fs.cid , fs.cr_id, fs.created_date FROM summary fs where fs.id in (select fa.id from forms fa where fa.sar in (select la.sar from login la where la.login_id=1 and fa.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') and fs.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') ) )and mode=0; After seeing above query I decide to make range partition on column created_date both in summary table and forms table. Below is the list of procedur
Read more

Copy files between Unix and Windows with rcp

There are many third party tools by which you can copy files between windows and unix machine. Some common tools are winscp, SSHSecureShellClient etc. Instead of using any third party tool, you can use original unix and windows built-in tool to do the task. The RCP (Remote CoPy) is a standard UNIX tool and a Microsoft Windows 2000 tool that copies files/folder to and from computer running the RCP service. Before you can use the RCP tool from a Windows-based computer you must do something in your UNIX computer. you must turn on the RCP service and update the security files to allow the Windows-based computer to connect to your unix machine over this service. The following steps will help you. Step 01: Turn on RCP service on UNIX Host Turn on the RCP service on unix machine by using inetd: 1. Log on as root. 2. Edit the file /etc/Inetd.conf. 3. Uncomment the lines that start with shell and that start with exec. 4. Save the file. 5. Use ps -ef |grep inetd and determine inetd service p
Read more

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq
Read more