How did I migrate Oracle database from one server to another one?

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.