Oracle restore from ufsdump to new server

jaytco · Jul 6, 2005

About a year ago we shutdown one of our DB servers, and did a final backup with ufsdump. (This was before my time). We now have reason to restore the database. I have an existing oracle database up an running on another server. This is where I plan to do my restore. Being more of a systems guy and not an oracle one, I could use a little help. I am currently restoring my oraxx dirs into its own mount point. /MNT/oldserver lets say.
So how do i get oracle to recognize the new databases? (the tnsnames.ora did have the reference to the old server, so that will need to be pointed to the local server.) I also assume that the listener.ora will need to be updated with the new info as well.

SantaMufasa · Jul 6, 2005

What flavour of *nix are you using (Unix or Linux), then the provider (Unix: Solaris, AIX, HP-UX, et cetera versus Linux: Red Hat, SuSE, et cetera)? Your answer determines where the "oratab" file resides. (Look on a running system for the contents and location of your "oratab" file...Solaris: /var/opt/oracle; most others /etc.)

Also, your profile should establish the madatory variables, "ORACLE_HOME" and "ORACLE_SID".

Once you have these two variables, so long as a local connection has these variables properly defined, and the proper ORACLE_HOME entry appears in your listener.ora, you should be well on your way.

All of this presumes that you have restored to proper directories all of your database files: control files, database data files, on-line redo log files. Although technically not part of the database, a proper init<SID>.ora file must reside in an accessible location (default: $ORACLE_HOME/dbs).

We are happy to help you get this all started. If you have additional problems/questions, post here.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

jaytco · Jul 6, 2005

The restore is on Unix Solaris 8.

Current /var/opt/oracle/oratab
[tt]ADB1:/ora01/app/oracle/product/Ora817:Y[/tt]

Would I update the oratab to include
[tt]RDB2:/MNT/RDB2/ora01/app/oracle/product/8.1.6:Y[/tt]

Restored database structure
[tt]/MNT/RDB2/ora01
/MNT/RDB2/ora02
...
[/tt]

The active $ORACLE_HOME is
[tt]/ora01/app/oracle/product/Ora817[/tt]

The original restored $ORACLE_HOME was [tt]/ora01/app/oracle/product/8.1.6 [/tt]

With that in mind should I link /ora01/app/oracle/product/8.1.6 to /MNT/RDB2/ora01/app/oracle/product/8.1.6 or will the oratab take care of this.

Knowing that I have 817 and 816 that will both have the different ORACLE_HOME and config files, can I just update the 817 (since it is the one currently working) with the new data? I guess I will need to restart oracle for changes to take effect.

BTW - I have been reading many of your posts and have got a lot of good info! Thanks!!

SantaMufasa · Jul 6, 2005

Thanks, J.

You seem to have done everything splendidly. The one thing of which to be aware is that if you issue the command:

Code:

. oraenv

It confirms the current Oracle environment (i.e., ORACLE_HOME and ORACLE_SID) settings by echoing the current ORACLE_SID. But, most importantly, it allows you to change the environment to any other that you have defined in the "...oratab" file. Therefore, when you issue the ". oraenv" command, when it says "[ADB1]", you can respond with "RDB2", and it replaces the "ADB1" definitions with the ORACLE_HOME and ORACLE_SID settings for "RDB2".

Once you have made the ". oraenv" change to "RDB2", you should be ready to try the following set of commands:

Code:

% sqlplus /nolog
SQL> connect / as sysdba
SQL> startup

Let us know your findings.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

jaytco · Jul 6, 2005

Getting closer...I think. Here is the error that I got when trying to launch sqlplus from the 8.1.6 product from the backup. I modified the tnsnames.ora and lintener.ora with the new path's.
Here is my environment fro the restored DB (Note-The Current running DB is the same minus the /MNT/RDB2 and using Ora817
):
USER=oracle
ORACLE_BASE=/MNT/RBD2/h/COTS/ORACLE/ora01/app/oracle
ORACLE_HOME=/MNT/RBD2/h/COTS/ORACLE/ora01/app/oracle/product/8.1.6
ORACLE_TERM=xsun5
LD_LIBRARY_PATH=/usr/openwin/lib:/ora01/app/oracle/product/Ora817/lib:/usr/local/lib
ORACLE_SID=RBD2
TNS_ADMIN=/MNT/RBD2/h/COTS/ORACLE/ora01/app/oracle/product/8.1.6/network/admin[/tt]

[tt]
oracle>{610} ./sqlplus /nolog

SQL*Plus: Release 8.1.6.0.0 - Production on Wed Jul 6 12:28:59 2005

(c) Copyright 2000 Oracle Corporation. All rights reserved.

*********************************************************

Your ORACLE_SID is
Not connected

SQL> connect / as sysdba
ERROR:
ORA-12505: TNS:listener could not resolve SID given in connect descriptor
[/tt]

SantaMufasa · Jul 6, 2005

J,

Could you please post the results of the following commands:

1) Contents of listener.ora:

Code:

cat $ORACLE_HOME/network/admin/listener.ora (or wherever your listener.ora resides)

2) Status of your listener:

Code:

% lsnrctl
LSNRCTL> status

Thanks.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

jaytco · Jul 6, 2005

Here is the output that you requested. This is from the 817 environment that is currently running. Looks like I need to add my config to the 817 files and not the 816.
Green is existing in 8.1.7, red is suggested add. If my assumption is not correct that by pointing to the product/8.1.6 it will find the database there, I still have a disconnect on how oracle knows where my physical database is since it is not in the 8.1.7 "default" directories. Do I have to register my DB or is that what the oratab and listener.ora do? Thanks again for all your help!!

oratab
[green]ADB1:/ora01/app/oracle/product/Ora817:Y[/green]
[red]RDB2:/MNT/RDB2/ora01/app/ORACLE/product/8.1.6:Y???[/red]

listener

Code:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = dbsrv1)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = adb1.world))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = ADB1))
      ) [RED]
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = rdb2.world))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = RDB2)
      )[/RED]
    )
    (DESCRIPTION =
      (PROTOCOL_STACK =
        (PRESENTATION = GIOP)
        (SESSION = RAW)
      )
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbsrv1)(PORT = 2481))
    )
  )

STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 2147483647
TRACE_LEVEL_LISTENER = OFF
ADMIN_RESTRICTIONS_LISTENER = ON
SID_LIST_LISTENER=
(SID_LIST=
 (SID_DESC =
  (SID_NAME = ADB1)
  (ORACLE_HOME = /ora01/app/oracle/product/Ora817)
 )[RED]
 (SID_DESC =
  (SID_NAME = RDB2)
  (ORACLE_HOME = /MNT/RDB2/ora01/app/ORACLE/product/8.1.6)
 )[/red]
 (SID_DESC=
  (ENVS=LD_LIBRARY_PATH=/ora01/app/oracle/product/Ora817/lib:/ora01/app/oracle/product/Ora817/ctx/lib)
  (SID_NAME=PLSExtProc)
  (ORACLE_HOME=/ora01/app/oracle/product/Ora817)
  (PROGRAM=extproc)
  )
 )
dbsrv1>{152}  lsnrctl status

LSNRCTL for Solaris: Version 8.1.7.4.0 - Production on 06-JUL-2005 14:24:29

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 8.1.7.4.0 - Production
Start Date                10-MAY-2005 21:28:03
Uptime                    56 days 16 hr. 56 min. 26 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /ora01/app/oracle/product/Ora817/network/admin/listener.ora
Listener Log File         /ora01/app/oracle/product/Ora817/network/log/listener.log
Services Summary...
  ADB1                  has 1 service handler(s)
  ADB1                  has 1 service handler(s)
  PLSExtProc            has 1 service handler(s)
The command completed successfully

SantaMufasa · Jul 6, 2005

J,

Yes, your instincts are very good. I am not an IPC-protocol guy (just TDP), so I'll have to rely upon your intuition there, but everything else looks good. Have you implemented the RED stuff yet, or were you waiting for a confirmation from here? You will need to stop and start your listener for the listener.ora settings to take effect. (Will you cause trouble with your users attempting to connect to the other database if you bounce the listener?)

At the end of the day, you want the lsnrctl status to show an entry for RDB2.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

jaytco · Jul 6, 2005

did the items in red and the listener is now running. However when I try to issue the following command as oracle I get permission denied.

Code:

sqlplus /nolog

Your ORACLE_SID is
SP2-0640: Not connected

SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL>

Listener status

Code:

Services Summary...
  ADB1                  has 1 service handler(s)
  ADB1                  has 1 service handler(s)
  BDB2                  has 1 service handler(s)
  PLSExtProc            has 1 service handler(s)

SantaMufasa · Jul 7, 2005

I believe we're nearly there, J.

Where you receive the error "ORA-01031: insufficient privileges" when you are trying to connect "/ as sysdba", there could be two causes. So, check the following:

1) ensure that "SQLNET.AUTHENTICATION_SERVICES = (NTS)" appears in your sqlnet.ora file.

2) ensure that the operating-system login you are using is a member of the same operating-system group as the Oracle user. You can check by issuing the Unix command, "id". The results should show both your userid and your group membership(s).

Let us know your progress.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

jaytco · Jul 7, 2005

I added the "SQLNET.AUTHENTICATION_SERVICES = (NTS)" to sqlnet.ora and am still receiving the same error. I tried the oracle:dba user, another user with dba rights and root.
I noticed that when I login as oracle with the default database ADB1 I get
[tt]
Your ORACLE_SID is
SP2-0640: Not connected

but used to get

Your ORACLE_SID is
ADB1
[/tt]
Looks like something changed but not sure what. I still see all the oracle ADB1 processes running so I guess that DB is running

SantaMufasa · Jul 7, 2005

J,

Your default Oracle instance is probably "ADB1" since that is the first entry in your "oratab" file. You must manually run the ". oraenv" command to change the "current" instance to another instance such as "RDB2".

So, use the ". oraenv" command to point to "RDB2" and try again this sequence:

Code:

% sqlplus /nolog
SQL> connect / as sysdba

...and post your results here.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

jaytco · Jul 7, 2005

here is the output

Code:

oracle>{179} /ora01/app/oracle/product/Ora817/bin/oraenv
ORACLE_SID = [ADB1] ? BDB2
arls4000>{180} sqlplus /nolog                             

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Jul 7 09:19:45 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

*********************************************************

Your ORACLE_SID is
SP2-0640: Not connected

*********************************************************
SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL>

SantaMufasa · Jul 7, 2005

J,

You have already confirmed that your sqlnet.ora contains
"SQLNET.AUTHENTICATION_SERVICES = (NTS)",

...therefore, the next item to confirm is the group membership of your login. Following your setting the ". oraenv" to "RDB2", issue the following command:

Code:

cat $ORACLE_HOME/rdbms/lib/config.s

Observe the double-quoted entries amongst the last few lines of output with the notations, ".ascii". The group name at the right end of those lines is the group to which you must be a member.

At the command prompt, issue the command: "id<enter>". The name within parentheses following "gid=" must match the group from "config.s", above.

Please post your findings here.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

jaytco · Jul 7, 2005

Hmmm, it is looking good so far. Hate those little quirks!

Code:

/* 0x0008         15 */         .ascii  "dba\0"
/* 0x0014         20 */         .align  4


                       .L13:
/* 0x0014         22 */         .ascii  "dba\0"


oracle>{186} id -a
uid=5000(oracle) gid=500(dba) groups=500(dba)

SantaMufasa · Jul 7, 2005

Well, J...we've exhausted my bag of troubleshooting tricks for the "ORA-01031: insufficient privileges" problem. At this point, we need to call in the cavalry (Oracle Tech Support). Presumably, you have a Customer Service Identification (CSI) number with Oracle. Now is the time to use it. (They are just down the road from you in Colorado Springs.) Call 800-223-1711 and have your CSI number ready, or you can navigate to Oracle Tech Support and fill out a MetaLink Technical Assistance Request (TAR).

Once you resolve your problem, Please post the resolution here so we can all learn from your findings.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

jaytco · Jul 7, 2005

Thanks for all your help. You have been a great resource. I'll let you know what I find!

Jay

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Oracle restore from ufsdump to new server

jaytco

Vendor

SantaMufasa

Technical User

jaytco

Vendor

SantaMufasa

Technical User

jaytco

Vendor

SantaMufasa

Technical User

jaytco

Vendor

SantaMufasa

Technical User

jaytco

Vendor

SantaMufasa

Technical User

jaytco

Vendor

SantaMufasa

Technical User

jaytco

Vendor

SantaMufasa

Technical User

jaytco

Vendor

SantaMufasa

Technical User

jaytco

Vendor

Similar threads

Part and Inventory Search

Sponsor