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:
- When /orahome is restored from Box A to Box B, why are non-Oracle Unix users not able to connect to DB without listener?
- 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).
- What would be the best way to migrate data 300+ GB from Box A to Box B other than what we have planned for?
- 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.
- There is no issue with moving the Oracle home directory if you feel the need to.
- 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
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.