Steps To Migrate Oracle 10g NON-ASM database to ASM Database On A Remote Server.
Before we start migrating the database to a remote server, hardware/software of source and destination servers are as follows:
Attention: Please replace PrimeDG with MarsDB
Source:
Source Server Name
|
Linux-225
|
Source Server IP Address
|
192.168.0.225
|
Source Database Name
|
PrimeDG
|
Source Database File System
|
Ext3 (non-ASM)
|
Source Server Type
|
Non-RAC
|
Source Server Version
|
10.2.0.4
|
Source Server OS
|
RHEL 4 u8
|
Source Database Backup Location
|
/u99/PrimeDG/Backup
|
Source Database Archivelog Location
|
/u99/PrimeDG/Archive
|
Source Dump locations:
|
/u01/app/oracle/admin/PrimeDG/adump
/u01/app/oracle/admin/PrimeDG/bdump
/u01/app/oracle/admin/PrimeDG/cdump
/u01/app/oracle/admin/PrimeDG/udump
|
Source Controlfile Locations:
|
/u02/oradata/PrimeDG/
/u03/oradata/PrimeDG/
/u04/oradata/PrimeDG/
|
Source Redo Logs Locations:
|
/u15/oradata/PrimeDG
/u16/oradata/PrimeDG/
/u17/oradata/PrimeDG/
|
Source Server Date and Time:
|
Is matched with Destination Server date and time
|
Destination:
Destination Server Name
|
Linux-VM-233
|
Destination Server Name
|
192.168.0.233
|
Destination Database Name
|
PrimeDG
|
Destination Database File System
|
ASM
|
Destination Server Type
|
2-node RAC Cluster
(However we will be restoring on node1 only. This same procedure applies to NON-RAC to NON-RAC database migration as well.)
|
Destination Server Version
|
10.2.0.4
|
Source Server OS
|
RHEL 4 u8
|
Destination Database Backup Location
|
/u99/PrimeDG/Backup
|
Destination Database Archivelog Location
|
/u99/PrimeDG/Archive
|
Destination Dump locations:
|
/u01/app/oracle/admin/PrimeDG/adump
/u01/app/oracle/admin/PrimeDG/bdump
/u01/app/oracle/admin/PrimeDG/cdump
/u01/app/oracle/admin/PrimeDG/udump
|
Destination Controlfile Locations:
|
+RC1
+RC2
+FLASH
|
Destination Redo Logs Locations:
|
+RC1
+RC2
+FLASH
|
Destination Server Date and Time:
|
Is matched with Source Server Date and Time
|
Section -1 (On Destination Server)
Create all necessary directories on Destination Server.
- Login to Linux-VM-233 Server as oracle
Copy paste the following to create directories. Assuming $ORACLE_BASE is set to /u01/app/oracle
If restoring on RAC, create the following directories on both nodes.
#All Dump Directories
mkdir -p $ORACLE_BASE/admin/PrimeDG/adump
mkdir -p $ORACLE_BASE/admin/PrimeDG/bdump
mkdir -p $ORACLE_BASE/admin/PrimeDG/cdump
mkdir -p $ORACLE_BASE/admin/PrimeDG/udump
#Controlfiles
mkdir -p /u02/oradata/PrimeDG
mkdir -p /u03/oradata/PrimeDG
mkdir -p /u04/oradata/PrimeDG
#Flash Recovery Area
mkdir -p /u05/flash_recovery_area/PrimeDG
#Redo Logs
mkdir -p /u15/oradata/PrimeDG
mkdir -p /u16/oradata/PrimeDG
mkdir -p /u17/oradata/PrimeDG
#Export / Import
mkdir -p /u18/PrimeDG
# Backup and Archive
mkdir -p /u99/PrimeDG/Backup
mkdir -p /u99/PrimeDG/Archive
|
Section – 2 (On Source Server)
Take a Backup on Source Server.
- Log in to Linux-225 server as your server login.
- sudo su - oracle
- export ORACLE_SID=PrimeDG
- rman target /
- show all;
For the purpose of this document, output of show all should match this. If not, you may want to change your RMAN settings. - backup database plus archivelog; ## This will take a full backup with all the archive logs
- exit;
Section - 3 (On Source Server)
Send the backups from SOURCE Server to DESTINATION Server
Let’s verify if the backups are available at the location given to rman /u99/PrimeDG/Backup location.
ls -ltrh /u99/PrimeDG/Backup
Please note that database DBID is also recorded in controlfile backup. We will need this in section-4 of restoring database process.
Please note that database DBID is also recorded in controlfile backup. We will need this in section-4 of restoring database process.
scp /u99/PrimeDG/Backup/* 192.168.0.233:/u99/PrimeDG/Backup/.
scp /u99/PrimeDG/Archive/* 192.168.0.233:/u99/PrimeDG/Archive/.
Section – 4 (On Source Server)
Copy the output of the following output that needs to be used in the rman block later.
export ORACLE_SID=PrimeDG
sqlplus / as sysdba
set linesize 200
set pagesize 200
set heading off
|
The RED output is what you will need to plug into the restore script in our next section of Section -6.
Section – 5 (On Destination Server)
Create appropriate ASM directories before we start restoring
execute asm bash profile to go to ASM HOME
~/.bash_profile_asm
export ORACLE_SID=+ASM Note: (or export ORACLE_SID=+ASMn if RAC. Replace “n” with instane number)
asmcmd -p
cd +Data
mkdir oradata
cd oradata
mkdir PrimeDG
cd PrimeDG
--At the end, verify you have the following directory by typing “pwd”
+DATA/oradata/ PrimeDG
For commands that can be used at “asmcmd” utility, execute “help” on the asmcmd> prompt.
Section – 6 (On Destination Server)
Restore, restore and open database.
Now, let us go back to ORACLE_HOME to start database restore process.
. ~/.bash_profile
export ORACLE_SID=PrimeDG
rman target /
set DBID=1794272723; # use dbid from the output of controlfile
startup force nomount; # This will give a warning/error as we don’t have pfile yet. You may see an error LRM-00109. this error is perfectly fine.
set controlfile autobackup format for device type disk to '/u99/PrimeDG/Backup/control_n_spfile_%F'; # To find the controlfile location from backup.
# the following will restore pfile from spfile.
run
{
allocate channel c1 type disk;
restore spfile to pfile
'/u01/app/oracle/product/10.2.0/db_1/dbs/initPrimeDG.ora' from autobackup;
shutdown abort;
}
exit; # Exit out of RMAN utility to make changes in pfile.
- At this point, pfile is restored to the $ORACLE_HOME/dbs (NOT ASM_HOME/dbs) location.
- Verify the existence of pfile using “ls -ltr $ORACLE_HOME/dbs/initPrimeDG.ora”.
- If any changes are required, this is the time to make parameter changes in pfile using vi editor.
For example:
You might want to change the controlfile location from (/u02...., /u03...., /u04....) to ASM diskgroup name +FLASH.
- Once satisfied with the changes, move to the next step of
- restoring controlfiles,
- restore database,
- recover database.
export ORACLE_SID=PrimeDG
rman target /
set DBID=1794272723 # use dbid from the output of controlfile
startup force nomount; # This time, you should not see any errors as we have pfile in $ORACLE_HOME/dbs location
set controlfile autobackup format for device type disk to '/u99/PrimeDG/Backup/control_n_spfile_%F'; ## this will set the controlfile location.
restore controlfile from autobackup; # This will restore controlfile to locations specified in the pfile.
mount database; # this will mount the database
run
{
set newname for datafile 1 to '+DATA/oradata/PrimeDG/system01.dbf';
set newname for datafile 1 to '+DATA/oradata/PrimeDG/system01.dbf';
set newname for datafile 2 to '+DATA/oradata/PrimeDG/undotbs01.dbf';
set newname for datafile 3 to '+DATA/oradata/PrimeDG/sysaux01.dbf';
set newname for datafile 4 to '+DATA/oradata/PrimeDG/users01.dbf';
set newname for datafile 5 to '+DATA/oradata/PrimeDG/DEVTBS01.dbf';
set newname for tempfile 1 to '+DATA/oradata/PrimeDG/temp01.dbf';
restore database check readonly; ## this will restore the database from the full backup {“check readonly” will process read-only files too}
switch datafile all; # to switch all datafiles from non-ASM to ASM.
recover database; ## this will recover the database upto the last archivelog available in /u99/PrimeDG/Archive
}
alter database open resetlogs; ## this will open the database and reset the sequence number to 1.
exit;
Section - 7
Verify the database is up and add the tempfile.
export ORACLE_SID=PrimeDG
sqlplus / as sydba
--Create temporary tablespace if needed
Create temporary tablespace temp05 TEMPFILE '+DATA/PrimeDG/temp05.dbf' SIZE 20M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp05;
--Verify if any existing temporary tablespace exists (if any)
select tablespace_name from dba_tablespaces where tablespace_name like 'TEMP%' and tablespace_name <> 'TEMP05' order by 1;
--Drop existing temporary tablespaces identified by the above query using
Drop tablespace <temp_tablespace_name>;
select status from v$instance; -- this should say open.
exit;
Section -8
Take a full backup of the database;
export ORACLE_SID=PrimeDG
rman target /
backup database plus archivelog;
exit;
Hope this helps.
--Moid
P.S. For complete step by step instructions on migrating database from a single standalone to 2-node RAC cluster, visit my blog http://moidmuhammad.blogspot.com/2010/06/how-to-migrate-single-standalone-to-2.html
