Move DB with Oracle 11g Binaries

Problem Statement:

1. Move DB with Oracle Binaries on New Storage
2. Create new DEV/UAT from Production.

Scenarios:

While creating new UAT or DEV from production and make this version of oracle to the same patch set level as of production, there are more than to move DB with Oracle Binaries.

Examples:

One way to move DB with Oracle Binaries is to install from the base version and upgrade the DB with the required patch set.

Another way to copy the entire binary from Production to the UAT/DEV and you will get the same patch set level of DB as of production.

The latter approach can be followed on same machine or on a different machine.

The approach:

Here, we are taking the second approach.  This approach requires below steps be performed on source and destination.

Steps on Source:

• Login in Unix box with oracle user

• Take the list of all DATAFILES, CONTROL FILES, LOGFILES including path

Perform steps:

• • Login using SQLPLUS with DBA user and execute below query.

SELECT SUBSTR(NAME,1,LENGTH(NAME)) FROM V$DATAFILE

SELECT SUBSTR(MEMBER,1,LENGTH(MEMBER)) FROM V$LOGFILE

SELECT SUBSTR(NAME,1,LENGTH(NAME))FROM V$CONTROLFILE

• Backup current init.ora file
• Create new init.ora file using

Perform steps:

• • Login using SQLPLUS with DBA user
  • Execute CREATE PFILE FROM SPFILE;

• Shutdown existing DB using shutdown immediate using SQLPLUS with SYS user

• Stop Listener using “lsnrctl stop”

• Confirm there are no executable running for this DB using “ps –fu oracle“

• Get the list of oracle inventory using “$ORACLE_HOME/OPatch/opatch lsinv –all”

Steps on Destination:

• Create directory /NewMountPoint/app/oracle/product/11.2.0.4/dbhome_1

• Copy oracle binaries from source mount point to  destination mount point

• Copy all data files (including indexes data files) of source DB to Destination DB

• Copy control files on location:

• Start-up Source DB

• Modify init.ora on destination for relevant changes like DB Name and location related parameters.

• Export ORACLE_SID=destdb

• Copy oraInventory directory

Perform steps:

• • cp -Rp  /OldMountPoint/app/oraInventory  /NewMountPoint/app/
  • Update path in oraInst.loc file

• Modify the environment file sourced from .bash_profile to reflect correct path.

• clone the Oracle Home using:

/NewMountPoint/app/oracle/product/11.2.0.4/dbhome_1/oui/bin/runInstaller \

-clone \

-silent \

-defaultHomeName

ORACLE_HOME=/NewMountPoint/app/oracle/product/11.2.0.4/dbhome_1 \

ORACLE_BASE=/NewMountPoint/app/oracle \

-waitForCompletion

• Run any scripts which are asked to run as root user by runinstaller

• Modify /etc/oratab.  tail –f /etc/oratab :

Entries to be replaced:

• • From: dbname:/OldMountPoint/app/oracle/product/11.2.0/dbhome_1:N
  • To : dbname: /NewMountPoint/app/oracle/product/11.2.0.4/dbhome_1:N

• Reset current environment using . oraenv

• Update listener.ora file

• Check Oracle inventory to validate the cloning of oracle home

$ORACLE_HOME/OPatch/opatch lsinv -all

• Copy new created init.ora of existing database

• Make relevant path change in init.ora (i.e. control file location etc)

Entries to be replaced:

• • From: .control_files=’/OldMountPoint1/oracle/oradata/dbname/control01.ctl’,’/OldMountPoint2/oracle/oradata/dbname/control02.ctl’,’/OldMountPoint/oracle/oradata/dbname/control03.ctl’

• • .control_files=’/NewMountPoint1/oracle/oradata/dbname/control01.ctl’,’/NewMountPoint2/oracle/oradata/dbname/control02.ctl’,’/NewMountPoint3/oracle/oradata/dbname/control03.ctl’

• Start-up Database to mount

• Rename all data files and index files with the new location of data/index files copied.

Use this command for all data files:

• • ALTER DATABASE RENAME /OLDLOCATION/df01.dbf TO /NEWLOCATION/df01.dbf

• Open DB using alter database.  You may have to perform instance recovery using reset logs.

• Start the listener service.

Your DB at new location is ready to be used.