How did I migrate oracle database from one server to another one?
I had many assignments about migration of Oracle
databases. The last I got was to migrate databases version 11.2.0.4 from one
Windows server to another Windows server. Well, I was thinking about two
approaches:
1. Involve template-migration with data files
2. Use the good old RMAN.
If you thinking about the data location structure –
good point there! To make less trouble about migration I decided to setup
absolute the same data structure on the new server like on the old one. It means
that the new server must to have C disc like the old one, D disc like the old
one, E disc like the old one…and so on.
Although will be easier with Templates it have one
very bad consequence: It locks all users and set theirs password expiredL.
I have tried with it and yes, all users’ accounts were
locked after with expired password. HOWEVER, i had one solution to it:
Get their hash password, unlock user and renew
password with the one from hash. Yes, it worked. You can find what I did in
other post
(Unlock/restore
users/passwords after database migration)
This
process is a little bit tricky and I decided to use RMAN and try option number
two.
Preparation (old server):
·
Make a template of database (structure only) based on database you want
to copy.
·
Write down info about NLS parameters
·
Write down DBID (SELECT DBID FROM V$DATABASE;)
·
Write down info about TEMP files:
·
select file_name from dba_temp_files;
·
Write down sizes of those files, location, name and tablespace name
which files belong to.
·
Write down location where redologs files are placed (SELECT * FROM
V$LOGFILE;)
Preparation
(new server):
·
Install database software on the new server (same version, same
location!) using template you have created before.
·
Install database with the same name as old database and pay attention
on:
·
Install database with NLS parameters the same as on old server,
·
Location of FRA and DIAG folders – they have to be created manually if
the installation process will not install them (same location as on the old
server!).
·
Pay attention on firewall (the new server should not be visible on the
production network yet)
OLD SERVER:
Start
CMD window and run:
SET ORACLE_SID=TEST (TEST is the name of the database)
RMAN NOCATALOG
CONNECT TARGET /
Configure
autobackup of controlfile and location where will be backup placed:
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\TEST\BACKUP_TEST_MIG\%U';
RMAN>CONFIGURE
CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'K:\TEST\BACKUP_TEST_MIG\%F';
Now, action:
Usually
I made some control data I one test table in database which will I use against
users which will complain that they lost some data (this is just not possible).
In
one table of test-schema I made text like “Test migration “plus date/time”.
RMAN>shutdown
immediate;
RMAN>startup
mount;
RMAN>BACKUP
AS BACKUPSET full database;
RMAN>alter
database open; this
is just to check that original database is OK again (this is not reuired to do it but
I’m very careful about this) and stop database again!
RMAN>shutdown
immediate;
·
Stop listener on original server
·
Copy the backup to the Server B (controlfile and backupset file)
·
Copy pfile, passwordfile, spfile from $ORACLE_HOME/database to the new server
on the same location (overwrite!)
New server:
CMD
SET
ORACLE_SID=TEST
·
check changes on local TNSNAME.ORA so it can accept connections
Start RMAN:
rman
nocatalog
connect
target /
RMAN>
shutdown abort;
RMAN>
startup nomount;
Restore control files
RMAN> SET
DBID 1234567890; (DBID you got earlier from the old database)
RMAN>
restore controlfile from 'path/backup_controlfile'
Delete redolog files
Outside of
RMAN with WindowsExplorer delete all redo logs files (from location you wrote
earlier) to get rid of ORA-01186 fail about file-verification.
Restore datafiles
RMAN>alter
database mount;
RMAN>CROSSCHECK
ARCHIVELOG ALL;
RMAN>CROSSCHECK
BACKUP;
RMAN>CATALOG
START WITH 'full_path_to_folder_where_you _just_copied_backup' NOPROMPT;
RMAN>restore
database;
RMAN>recover
database; (this is not necessary if database is in ARCHIVELOG
mode and will fail if database you are copying is in archivelog mode but can’t
do nothing wrong anyway)
RMAN>ALTER
DATABASE OPEN RESETLOGS;
RMAN>exit
Regenerate TEMP files
Now, new
database is not ready yet as we need to regenerate temp files (you wrote
earlier info about this):
Suppose we had TEMP
and TEMP1 temporary tablespaces with
files TEMP01.DBF and TEMP02.DBF sizes 100Mb and 200Mb. Location of TEMP01.DBF
was T:\TEMP\TEMP01.DBF and same location for the other one.
First must we drop both files:
CMD
SET ORCALE_SID=TEST
sqlplus “/as sysdba”
alter database tempfile ‘T:\TEMP\TEMP01.DBF' drop;
alter database tempfile ‘T:\TEMP\TEMP02.DBF' drop;
and add the new ones:
alter tablespace TEMP
add tempfile ‘T:\TEMP\TEMP01.DBF' SIZE 100M
REUSE;
alter tablespace TEMP1
add tempfile ‘T:\TEMP\TEMP02.DBF' SIZE
200M REUSE;
You can check for invalid objects
(just in case J )
And that’s it!
I’ve used this method for seven
databases I migrated lately and it worked perfect. Hope this can be helpful to
someone J.
