Q
Problem solve Get help with specific problems with your technologies, process and projects.

Migrating database to new storage box

We have got new EMC storage and want to migrate our Oracle database (300+ GB) to the new storage box.

We have got new EMC storage and want to migrate our Oracle database 300+ to the new storage box. We have two servers (say A and B), SUN E6500 Server with Solaris 8 and Oracle 8.0.5 (A=production server and B=standby server). We have connected the new CX-500 storage to Box B and created all the file systems same as Box A. Our aim is to migrate all the Oracle data by taking a cold backup from Box A and restoring to Box B and after successful migration declare Box B as the production server.

For test purpose we have done the following:

  • Taken Oracle cold backup and orahome (Oracle -binaries) from production server (Box A) and restored all into Box B.
  • After the restore host was changed in listener.ora and tnsnames.ora from A to B.

Oracle was started successfully and Unix user "oracle" is able to connect to database using SQL*Plus <username>/passwd and when the listener is started other clients are able to connect also.

$whoami
oracle
$sqlplus username/passwd
Connected
SQL>
But the problem started when we tried to connect the database with Unix user. Other than Oracle having the primary group same as Oracle (i.e., DBA) using SQL*Plus username/passwd and we are getting the following messages:
$whoami
xyz
$sqlplus username/password

Error:

ORA-01034 : Oracle not available
....
However , using listener xyz is able to connect to DB as follows.
$whoami
xyz
$sqlplus username@dbname
pawword:
Connected.
SQL>
My questions:
  1. When /orahome is restored from Box A to Box B, why are non-Oracle Unix users not able to connect to DB without listener?
  2. Shall we avoid restoring orahome file system to Box B and restore only Oracle cold backup into Box B? Because when we restore ONLY cold backup and orahome of Box B was not touched then we didn't had the problem mentioned in (1).
  3. What would be the best way to migrate data 300+ GB from Box A to Box B other than what we have planned for?
Regards.
  1. Non-oracle unix users are seeing the ORA-1034 error because their environment variables are not defined correctly. Ensure that the ORACLE_HOME and ORACLE_SID values are correct. Since you move the Oracle home directory, they will need to make this change.
  2. There is no issue with moving the Oracle home directory if you feel the need to.
  3. There are multiple ways to move a 300GB database. I personally do not feel that a 300GB database is that big since I have tablespaces larger than that. You can simply restore the backup as you have done, and that is acceptable. Or, you can shut down your database and move the datafiles to the new location, followed by updating the control files with the ALTER DATABASE RENAME FILE command. Or, you can use export/import or Transportable Tablespaces for your move. There is no wrong way here.

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close