RMAN: restore database on another node including rename

In my urge to write down some basic RMAN-checklists I could use in the future, consider the following:

You’ve got no DataGuard at your disposal. Single node, relying on your RMAN-backup on disk, which is fortunately copied to tape. Now your complete node is burning down. What’s is left is your RMAN-backup (made by this script).
In a bad scenario you’ll have to restore this to another node.

No rocket science, but at that very moment you’re not very relaxed, and will be happy that it’s documented in the past by or for you how to do it. Also to be used to make a copy of the production-database, however I find the technique of ‘duplicating with RMAN‘ easier and more adequate for this purpose. Here are the basics of a restore on another node and give the database another name. Yes, it can be scripted and make it faster at certain points , but that’s not the point in this story….

Example: restore the database DEVDB (node LARRY) on the node ELLISON and give it the name DEVDBNEW.
The daily backups on LARRY are on /local/data/oracle/rman/DEVDB

On ELLISON, create the same backup-directory:
As root:
>> mkdir –p /local/data/oracle/rman/DEVDB
>> chown –R oracle:oinstall /local
As an alternative: if there is already a /backup/data/oracle or something like that, use a symbolic link:
>> ln –s /backup local

The intention here is to copy the bunch from LARRY to ELLISON, but if it’s possible, you can always mount the directory ‘/local/data/oracle/rman/DEVDB’ on ELLISON to the same directory on LARRY, so you don’t have to copy. Saves a lot of time, only be aware of the ‘..cfc..’-file. If the origin database is in ‘autobackup’ mode, this file will be overwritten by the origin database when there are changes. Also the new database is using this file, while restoring and may also be in autobackup-mode. So make a copy of the original file (which has a date which is the end of the backup you are using).

Find out where the files should be placed:

select name from v$tempfile
union
select member from v$logfile
union
select name from v$datafile
union
select name from v$controlfile;

or: use ‘report schema’ in RMAN.

As Oracle:
Edit /etc/oratab on ELLISON, with the origin name, temporarily:
DEVDB:/software/oracle/product/10202:N
Run .oraenv (DEVDB), for the right environment.

Create directories for the database files (these ones already with the new db name):
>>mkdir –p /data10/oradata/DEVDBNEW/arch

Create new backup directories:
>> mkdir –p /backup/ELLISON/DEVDBNEW/export
>> mkdir –p /backup/ELLISON/DEVDBNEW/rman

Create directories for the admin files (with the new db name):
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/adump
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/bdump
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/cdump
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/udump
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/scripts
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/pfile
>> mkdir –p /software/oracle/product/10202/admin/DEVDBNEW/dpdump

Create a temporary pfile for the new database (use the old DEVDB-pfile e.g.).
Put it in /home/oracle/dba/job e.g.
Example:

+++++++++++++++++++++++++++++++++++++++++++
DEVDB.__db_cache_size=570425344
DEVDB.__java_pool_size=16777216
DEVDB.__large_pool_size=16777216
DEVDB.__shared_pool_size=922746880
DEVDB.__streams_pool_size=33554432
*.audit_file_dest=’/software/oracle/product/10202/admin/DEVDBNEW/adump’
*.background_dump_dest=’/software/oracle/product/10202/admin/DEVDBNEW/bdump’
*.compatible=’10.2.0.2.0′
*.control_files=’/data10/oradata/DEVDBNEW/control01.ctl’,’/data10/oradata/DEVDBNEW/control02.ctl’,’/data10/oradata/DEVDBNEW/control03.ctl’
*.core_dump_dest=’/software/oracle/product/10202/admin/DEVDBNEW/cdump’
*.db_block_size=8192
*.db_domain=’world’
*.db_file_multiblock_read_count=16
*.db_name=’DEVDB‘
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEVDBXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/data10/oradata/DEVDBNEW/archivelog’
*.log_archive_format=’arch_%d_%t_%r_%s.arc’
*.nls_numeric_characters=’,.’
*.open_cursors=300
*.pga_aggregate_target=1048576000
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_max_size=2097152000
*.sga_target=1572864000
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/software/oracle/product/10202/admin/DEVDBNEW/udump’
+++++++++++++++++++++++++++++++++++++++++++

Put the new and old name for the database in the listener.ora:

++++++++++++++++++++++++++++++++++++++++++
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /software/oracle/product/10.2)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /software/oracle/product/10202)
(SID_NAME = DEVDBNEW)
)
(SID_DESC =
(ORACLE_HOME = /software/oracle/product/10202)
(SID_NAME = DEVDB)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ELLISON.domain.nl)(PORT = 1521))
)
)
++++++++++++++++++++++++++++++++++++++++++
Restart the listener (reload should be working too, but in the past I had some troubles with it..):
>>lsnrctl stop
>>lsnrctl start

Edit the tnsnames.ora on ELLISON:

+++++++++++++++++++++++++++++++++++++++++++
DEVDBNEW =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ELLISON.domain.nl)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEVDBNEW)
)
)

DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ELLISON)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB.world)
)
)
+++++++++++++++++++++++++++++++++++++++++++

Make a list of all the datafiles of DEVDB, and where these files should be on ELLISON.
You may use something like this:

+++++++++++++++++++++++++++++++++++++++++++
select ‘set newname for datafile ‘||file#||’ to ”’||
replace(name,’/local/data/oracle/DEVDB/datafiles/’,’/data10/oradata/DEVDBNEW/’)||”’;’
from v$datafile where name like ‘%/datafiles/%’
union all
select ‘set newname for datafile ‘||file#||’ to ”’||
replace(name,’/local/data/oracle/DEVDB/’,’/data10/oradata/DEVDBNEW/’)||”’;’
from v$datafile where name not like ‘%/datafiles/%’;

select ‘sql “alter database rename file ””’||member||””’ to ””’
||replace(member,’/local/data/oracle/DEVDB/redo/’,’/data10/oradata/DEVDBNEW/’)||””'”;’
from v$logfile;
+++++++++++++++++++++++++++++++++++++++++++

For the duplicate, create a script like this:

Restore_DEVDB.rman.
++++++++++++++++++++++++++++++++++++++++++++
RUN
{
allocate channel c2 device type disk;
# rename the datafiles and online redo logs
set newname for datafile 1 to ‘/data10/oradata/DEVDBNEW/system01.dbf’;
set newname for datafile 2 to ‘/data10/oradata/DEVDBNEW/undotbs01.dbf’;
set newname for datafile 3 to ‘/data10/oradata/DEVDBNEW/sysaux01.dbf’;
set newname for datafile 4 to ‘/data10/oradata/DEVDBNEW/users01.dbf’;
set newname for datafile 5 to ‘/data10/oradata/DEVDBNEW/ODIN_DATA1.dbf’;
set newname for datafile 6 to ‘/data10/oradata/DEVDBNEW/ODIN_INDEX1.dbf’;
set newname for datafile 7 to ‘/data10/oradata/DEVDBNEW/ESDNW_DATA.dbf’;
etc…
set newname for datafile 25 to ‘/data10/oradata/DEVDBNEW/inet_lob3.dbf’;
set newname for datafile 26 to ‘/data10/oradata/DEVDBNEW/inet_lob4.dbf’;
sql “alter database rename file ”/local/data/oracle/DEVDB/redo/redo04a.log”
to ”/data10/oradata/DEVDBNEW/redo04a.log””;
sql “alter database rename file ”/local/data/oracle/DEVDB/redo/redo04b.log”
to ”/data10/oradata/DEVDBNEW/redo04b.log””;
sql “alter database rename file ”/local/data/oracle/DEVDB/redo/redo03a.log”
to ”/data10/oradata/DEVDBNEW/redo03a.log””;
sql “alter database rename file ”/local/data/oracle/DEVDB/redo/redo03b.log”
to ”/data10/oradata/DEVDBNEW/redo03b.log””;
sql “alter database rename file ”/local/data/oracle/DEVDB/redo/redo02a.log”
to ”/data10/oradata/DEVDBNEW/redo02a.log””;
sql “alter database rename file ”/local/data/oracle/DEVDB/redo/redo02b.log”
to ”/data10/oradata/DEVDBNEW/redo02b.log””;
sql “alter database rename file ”/local/data/oracle/DEVDB/redo/redo01a.log”
to ”/data10/oradata/DEVDBNEW/redo01a.log””;
sql “alter database rename file ”/local/data/oracle/DEVDB/redo/redo01b.log”
to ”/data10/oradata/DEVDBNEW/redo01b.log””;
# restore the database and switch the datafile names
RESTORE DATABASE;
SWITCH DATAFILE ALL;
# recover the database
RECOVER DATABASE;
}
+++++++++++++++++++++++++++++++++++++++++

O.k. almost all preperations are done, now we are getting somewhere..
First of all, copy the RMAN-files from LARRY tot ELLISON to the same directory.
This could take a while by the way..

Then you have to find the DBID of the DEVDB-database through sql (select dbid from v$database), by logging in RMAN (he’ll show it when logging in), or examine the RMAN-logging.

Get in to RMAN to start the restore en recover-action:
>> $ORACLE_HOME/bin/rman target / nocatalog

RMAN> set DBID 173282285;
RMAN> start force nomount pfile=’/home/oracle/dba/rob/pfileDEVDB.ora’;
RMAN> restore controlfile from ‘/local/data/oracle/rman/DEVDB/ora_cfc-173282285-20091012-00’;
RMAN> alter database mount;
RMAN> @restore_DEVDB.rman

This could take a while..
At the end RMAN may yawn about an archive-log file he’s missing after the command ‘recover database’ is run:

+++++++++++++++++++++++++++++++++++++++++++++
starting media recovery

channel c2: starting archive log restore to default destination
channel c2: restoring archive log
archive log thread=1 sequence=8483
channel c2: reading from backup piece /local/data/oracle/rman/DEVDB/ora_df700020698_s3218_s1
channel c2: restored backup piece 1
piece handle=/local/data/oracle/rman/DEVDB/ora_df700020698_s3218_s1 tag=TAG20091012T021133
channel c2: restore complete, elapsed time: 00:00:07
archive log filename=/data10/oradata/DEVDBNEW/archivelog/arch_a5413ed_1_603993520_8483.arc thread=1 sequence=8483
unable to find archive log
archive log thread=1 sequence=8484
released channel: c2
RMAN-00571: =====================================
RMAN-00569: ========= ERROR MESSAGE STACK FOLLOWS ==========
RMAN-00571: =====================================
RMAN-03002: failure of recover command at 10/14/2009 18:21:59
RMAN-06054: media recovery requesting unknown log: thread 1 seq 8484 lowscn 14398776629
++++++++++++++++++++++++++++++++++++++++++++

When investigating this, most of the times it can be ignored, while it’s a log he don’t need for it’s generated after the backup:

RMAN> alter database open resetlogs;
The database should open now..

Now some additonal steps:

RMAN > shutdown immediate
RMAN > exit

If you haven’t done it already, create a passwd file:
>> cd $ORACLE_HOME/dbs
>> orapwd file=orapwDEVDB password=manager1 entries=5

Create a spfile:
>> sqlplus “/as sysdba”
SQL> create spfile=’/software/oracle/product/10202/dbs/spfileDEVDB.ora’ from pfile=
‘/home/oracle/dba/job/pfileDEVDB.ora’;

Add file for the temp-tablespace, drop the old one (only available in the controlfile, not on disk: select tablespace_name,file_name from dba_temp_files)
SQL> startup
SQL> alter tablespace TEMP1
2 add tempfile ‘/data10/oradata/DEVDBNEW/temp101.dbf’
3 size 100M autoextend on next 50M maxsize 6G;
SQL> alter database tempfile ‘/local/data/oracle/DEVDB/datafiles/temp101.dbf’ drop;

Renaming of the DEVDB to DEVDBNEW:
SQL> shutdown immediate
SQL> startup mount
SQL> host nid target=sys/<passwd> DBNAME=DEVDBNEW
Change database ID and database name DEVDB to DEVDBNEW? (Y/[N]) => Y

Example output:
++++++++++++++++++++++++++++++++++++++++++++
DBNEWID: Release 10.2.0.2.0 – Production on Thu Oct 15 12:47:28 2009

Connected to database DEVDB (DBID=173282285)

Connected to server version 10.2.0

Control Files in database:
/data10/oradata/DEVDBNEW/control01.ctl
/data10/oradata/DEVDBNEW/control02.ctl
/data10/oradata/DEVDBNEW/control03.ctl

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

Proceeding with operation
Changing database ID from 173282285 to 1803605536
Changing database name from DEVDB to DEVDBNEW
Control File /data10/oradata/DEVDBNEW/control01.ctl – modified
Control File /data10/oradata/DEVDBNEW/control02.ctl – modified
Control File /data10/oradata/DEVDBNEW/control03.ctl – modified
Datafile /data10/oradata/DEVDBNEW/system01.dbf – dbid changed, wrote new name
……………………………………………………………………
Datafile /data10/oradata/DEVDBNEW/temp101.dbf – dbid changed, wrote new name
Control File /data10/oradata/DEVDBNEW/control01.ctl – dbid changed, wrote new name
Control File /data10/oradata/DEVDBNEW/control02.ctl – dbid changed, wrote new name
Control File /data10/oradata/DEVDBNEW/control03.ctl – dbid changed, wrote new name
Instance shut down

Database name changed to DEVDBNEW.
Modify parameter file and generate a new password file before restarting.
Database ID for database DEVDBNEW changed to 1803605536.
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.
++++++++++++++++++++++++++++++++++++++++++++

SQL> exit

Edit the /etc/oratab-file with the new db-name:
DEVDBNEW:/software/oracle/product/10202:N
run .oraenv (DEVDBNEW) for setting your environment.

Create passwwd file:
>> cd $ORACLE_HOME/dbs
>> orapwd file=orapwDEVDBNEW password=manager1 entries=5

Edit the temporary /home/oracle/dba/job/pfileDEVDB.ora, rename this to pfileDEVDBNEW.ora e.g.

Example
+++++++++++++++++++++++++++++++++++++++++++
*.audit_file_dest=’/software/oracle/product/10202/admin/DEVDBNEW/adump’
*.background_dump_dest=’/software/oracle/product/10202/admin/DEVDBNEW/bdump’
*.compatible=’10.2.0.2.0′
*.control_files=’/data10/oradata/DEVDBNEW/control01.ctl’,’/data10/oradata/DEVDBNEW/control02.ctl’,’/data10/oradata/DEVDBNEW/control03.ctl’
*.core_dump_dest=’/software/oracle/product/10202/admin/DEVDBNEW/cdump’
*.db_block_size=8192
*.db_domain=’world’
*.db_file_multiblock_read_count=16
*.db_name=’DEVDBNEW‘
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEVDBNEWXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/data10/oradata/DEVDBNEW/archivelog’
*.log_archive_format=’arch_%d_%t_%r_%s.arc’
*.nls_numeric_characters=’,.’
*.open_cursors=300
*.pga_aggregate_target=1048576000
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_max_size=2097152000
*.sga_target=1572864000
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/software/oracle/product/10202/admin/DEVDBNEW/udump’
++++++++++++++++++++++++++++++++++++++++++

Create a new spfile:
>> sqlplus “/as sysdba”
SQL> create spfile=’/software/oracle/product/10202/dbs/spfileDEVDBNEW.ora’ from pfile=
‘/home/oracle/dba/rob/pfileDEVDBNEW.ora’;
Start the database with resetlogs:
SQL> startup mount
SQL> alter database open resetlogs;

Done.

===============

Of Motorcycles, Databases and the Cloud

Leave A Comment

Recent Tweets Jobaclenl

RMAN: restore database on another node including rename

Related Posts

Of Motorcycles, Databases and the Cloud

Leave A Comment

Recent Tweets Jobaclenl