RMAN и перенос базы на другой сервер

dbstalker, 13 октября

Эта тема хорошо освещена в блоге : «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.

20 comments:

alphamek said... 

Hi,

Excellent issue, the task to move database between servers to turn easy, equal rman duplicate.

Congratulations,

Rodrigo Almeida 
August 19, 2008 12:46 PM 
Anonymous said... 

hi this is madhan,
am also trying the same thing but am getting this following error


Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/usr/lib/oracle/xe/oradata/XE/system.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/12/2008 10:55:21
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 1 lowscn 476858 found to restore 
November 10, 2008 10:18 PM 
Arju said... 

You need more logs to apply.
RMAN-06053: unable to perform media recovery because of missing log 
November 10, 2008 10:36 PM 
Anonymous said... 

hi arju this is madhan 

Thanks for ur help,I recovered the new host with the whole database backup which is taken from the different database. 

I need one more help from you,
Now i want to recover the database with the level 1 backup in the new host. I tired it but showing errors. plz tell me the procedure for it. Thanks for ur help in advance..

And can we recover a particular tablespace in new host if so plz tell me? 
November 20, 2008 1:58 AM 
Anonymous said... 

hi arju this is madhan 

Thanks for ur help,I recovered the new host with the whole database backup which is taken from the different database. 

I need one more help from you,
Now i want to recover the database with the level 1 backup in the new host. I tired it but showing errors. plz tell me the procedure for it. Thanks for ur help in advance..

And can we recover a particular tablespace in new host if so plz tell me? 
November 20, 2008 1:58 AM 
Anonymous said... 

Hello,
I am not sure weather on the new host Saturn, should exist a created database or not ? 
December 21, 2008 11:42 AM 
Arju said... 

No database exist in saturn. 
December 21, 2008 8:38 PM 
Anonymous said... 

Arju thanx for answer...
Just to make sure,it means that only oracle software is installed without any database on sturn host ?

regards 
December 22, 2008 10:12 AM 
Arju said... 

It does not matter whether any other database is there or not. Only oracle software is needed. Be sure same named database will not be there. If such you need to rename then. 
January 2, 2009 2:14 AM 
Anonymous said... 

Hi Arju,

I'm Daniel.

I'm not sure about "only Oracle software". 
Because, if you don't have any database and running configured listener you won't be able to connect to target database through RMAN on first step. May be I don't understand smth...
In any case, Thank you very much for this article. It's very useful... 
February 9, 2009 10:50 PM 
Anonymous said... 

Hi Arju,

This is Daniel again

Could you please give short instrutions how to restore database with different name?
For example, there are 2 servers with Oracle databases:
1. PRODServ and DB is PROD
and
2. TESTServ and DB is TEST.
And we have to restore database PROD on TESTServ. And this restored database must has name - TEST...

Thank you for your help in advance! 
March 23, 2009 10:41 AM 
Arju said... 

If you look for renaming database while restoring then you can use DUPLICATE DATABASE feature which is discussed on ,

http://arjudba.blogspot.com/2008/05/creating-duplicate-database-on-remote.html


Manual steps are also given in http://arjudba.blogspot.com/2008/10/how-to-clone-database-on-same-host-with.html 
March 23, 2009 6:05 PM 
Anonymous said... 

Hi Arju,

Thank you very much!
This information is very useful for me!
It's exactly that I tried to find!
Thank you very much again...

Daniel 
March 25, 2009 7:26 AM 
Anonymous said... 

Is this procedure applicable for oracle 9i 
April 30, 2009 11:33 AM 
Suresh said... 

Hi Arju
This is really very informative. Thanks for that. Could you help me out to create with different database name on new host and it should be renaming database while restoring without DUPLICATE DATABASE feature. Because there is no network/sql connectivity between production and test servers.

Thanks
Suresh 
July 1, 2009 4:23 AM 
Arju said... 

Sure, you can get help from this post. By this post you can restore a database to another host without network. Right?

Now you will use nid tool to change the database name.
How to change Database Name and DBID 
July 1, 2009 7:04 AM 
Suresh said... 

Arju
I guess the above post helps to change DB ID/Name after restoration. Isn't it? Situation is the test server has already one instance with same DB name as production.I will have to restore the production backup with different DB name in test server. 

Thanks
Suresh 
July 1, 2009 7:33 AM 
Arju said... 

Could you please post your question inside Oracle Forum so that everybody can discuss with it? 
July 1, 2009 7:41 AM 
Anonymous said... 

Arju,

Excellent article. I tried this on 9i. All went well until the step of restoring the database. RMAN cannot find the backup from new server. It says unable to open file. I looked online and found that a few other people have had the same problem. Error stack is :

RMAN-03002: failure of restore command at 08/27/2009 12:45:17
ORA-19505: failed to identify file "D:\ORACLEBACKUP\TESTDB\B_91KND383_1_1"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Help please !!

Sasi 
August 27, 2009 11:41 AM 
HUSIN said... 

Dear Arju,

Can you give me another step for cloning database on different host and different name. but not using RMAN. frankly, i am new in oracle, i dun know how to use RMAN.

Also, i'm trying to clone database, first try is working, second trying is not working.

it come up with error :

SQL> @f:\controlfile.ctl
ORACLE instance started.

Total System Global Area 1167139800 bytes
Fixed Size 455640 bytes
Variable Size 436207616 bytes
Database Buffers 729808896 bytes
Redo Buffers 667648 bytes
CREATE CONTROLFILE REUSE Set DATABASE "PUSING3" RESETLOGS
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01990: error opening password file
'F:\oracle\ora92\DATABASE\PWDpusing3.ORA'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Can you help me with this.

Thanks a lot

Devin

2 комментария

Прокоментировать

Дракоша
18 октября 2010 г. в 10:15

Имхо отсутствуют некоторые необходимые пункты.
Во первых необходимо добавить переименование temp файлов -
set newname for tempfile <старый путь> to <новый путь>
....
switch tempfile all

В команду restore обычно добавляю проверку на readonly табличные пространства -
restore database check readonly

И еще один нюанс. Если у основной базы был standby и уровень защиты был maximum availability или protection, необходимо переключить его на performance, иначе команда open resetlogs свалится с ошибкой -
sql "alter database set standby database to maximize performance"

Дракоша
19 октября 2010 г. в 09:08

Забыл добавить, что еще необходимо изменить пути к оперативным и standby log-файлам -
SQL "ALTER DATABASE RENAME FILE ''[старый путь к redo log]'' TO ''[новый путь к redo log]''"

И еще одна фенька о которой многие забывают - в 10g есть объекты базы DIRECTORIES, их используют утилиты data pump, механизмы работы с внешними таблицами и некоторые другие. Так вот если структура сервера абсолютно разная необходимо будет переопределить их. Список directories базы можно посмотреть воспользовавшись запросом -

SELECT directory_name, directory_path
from dba_directories

 

Новый комментарий

Я не спамер: введите суму 6+3



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Революция аромата: Sensa
16 июля, 1 ответа