Home » RDBMS Server » Backup & Recovery » RMAN-04006 with ORA-01031 (Auxiliary Connect) (9.2.0.8/ Win2003 Server)
RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417207] Thu, 06 August 2009 09:36 Go to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Hello,

I am experiencing an odd issue where I am unable to connect remotely to an auxiliary database so that I can perform a restore. I can connect fine to the instance in SQL*Plus when I am connected locally. I have put the auxiliary instance into startup nomount with a parameter file and that worked fine via a local connection. However, when I attempt to connect to the database via RMAN (running on target instance), I receive the following error:

RMAN> connect auxiliary sys/<sys_pw>@TRAN

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges 


When connecting locally (on the auxiliary server), there is no issue:

F:\oracleTRAN\tran9i\database>sqlplus "sys as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Aug 6 10:34:40 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL>


I believe this has to do with remote authentication. Does anyone have any suggestions as to what I should check to resolve this problem? (entries in config files, specific parameters, etc). I am running 9i database and have used this connection methodology in the past and performed restores this way previously. Both platforms have the same file structure and are running Win 2003 server.

Thank you!
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417208 is a reply to message #417207] Thu, 06 August 2009 09:44 Go to previous messageGo to next message
ThomasG
Messages: 3123
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When you want to connect remotely as an administrative user to a database that is not mounted and open, then Password File Authentication has to be set up on the server.

(Since the server can not get the password out of the user information in the not-open database)
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417212 is a reply to message #417208] Thu, 06 August 2009 10:01 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Hi ThomasG,

Thank you for your prompt reply.

As I understand your post, Password File Authentication must be enabled on the target server to connect?

I have setup PFA on the auxiliary server (created the password file, setup the sys password). After I created the password file on the auxiliary server, I modified the init<SID>.ora file to include remote_login_passwordfile=EXCLUSIVE. I then started the database in nomount using the pfile.

Did I do this correctly?

Regards,
Steve
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417215 is a reply to message #417212] Thu, 06 August 2009 10:10 Go to previous messageGo to next message
ThomasG
Messages: 3123
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Looks correct.

You can check if it works by connecting "as sysbda" from a remote system:

U:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 6 17:08:33 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect sys/<password>@<SID> as sysdba
Connected.
SQL>
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417216 is a reply to message #417207] Thu, 06 August 2009 10:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10630
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I am experiencing an odd issue where I am unable to connect remotely to an auxiliary database so that I can perform a restore

Alternatively, did you trying restoring from the remote server?
This way,
you can just set ORACLE_SID in enviroment and within rman
connect target sys/sys@targetDB

connect auxiliary  sys/sys

Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417219 is a reply to message #417215] Thu, 06 August 2009 10:29 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Thank you guys for your helpful tips. Something must still not be configured properly.

ThomasG wrote on Thu, 06 August 2009 11:10
Looks correct.

You can check if it works by connecting "as sysbda" from a remote system:

U:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 6 17:08:33 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect sys/<password>@<SID> as sysdba
Connected.
SQL>



F:\oracleprod\prod9i>sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Aug 6 11:21:10 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect sys/<sys_pw>@tran as sysdba
ERROR:
ORA-01031: insufficient privileges


Mahesh, I also tried your suggestion, but I believe I still have a configuration problem:
Mahesh Rajendran wrote on Thu, 06 August 2009

Alternatively, did you trying restoring from the remote server?
This way,
you can just set ORACLE_SID in enviroment and within rman


F:\oracleTRAN\tran9i\database>rman nocatalog

Recovery Manager: Release 9.2.0.8.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN> connect target sys/<sys_pw>@prod

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-12154: TNS:could not resolve service name


I'll have to go back to my %ORACLE_HOME%\network\ location and review my config files.

Thanks,
Steve
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417220 is a reply to message #417219] Thu, 06 August 2009 10:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10630
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>RMAN> connect target sys/<sys_pw>@prod
You need a tns entry in your remote tnsnames.ora file that will point to the target database (prod).
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417221 is a reply to message #417220] Thu, 06 August 2009 10:37 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Mahesh Rajendran wrote on Thu, 06 August 2009 11:31
>>RMAN> connect target sys/<sys_pw>@prod
You need a tns entry in your remote tnsnames.ora file that will point to the target database (prod).


You were right, the remote tns entry was missing. Resolved by issuing tnsping prod from command prompt to receive location of tnsnames.ora file that was being read. Then I inserted the tns entry into the names file it was reading and now I am able to connect using the method you described.

Thank you for your assistance. I am now going to run the recovery.

Regards,
Steve
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417222 is a reply to message #417220] Thu, 06 August 2009 10:40 Go to previous messageGo to next message
ThomasG
Messages: 3123
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Can you confirm that the target server is indeed using the password file?

SQL> show parameter pass

NAME                          TYPE        VALUE
----------------------------- ----------- -----
remote_login_passwordfile     string      EXCLUSIVE
SQL>


And that the users are indeed added to it?

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SYSTEM                         TRUE  FALSE
SQL>
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417223 is a reply to message #417222] Thu, 06 August 2009 10:45 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
ThomasG wrote on Thu, 06 August 2009 11:40
Can you confirm that the target server is indeed using the password file?

SQL> show parameter pass

NAME                          TYPE        VALUE
----------------------------- ----------- -----
remote_login_passwordfile     string      EXCLUSIVE
SQL>



Ahh, this is interesting. On the target server, I get the following:
SQL> show parameter pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      SHARED


ThomasG wrote on Thu, 06 August 2009 11:40

And that the users are indeed added to it?

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SYSTEM                         TRUE  FALSE
SQL>



The user is setup properly though:
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417225 is a reply to message #417223] Thu, 06 August 2009 11:00 Go to previous messageGo to next message
ThomasG
Messages: 3123
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then the database doesn't seem to be using the pfile you think it is.

But when you have managed to get the restore working the other way around, you can maybe figure that one out a later time.
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417241 is a reply to message #417225] Thu, 06 August 2009 12:45 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
ThomasG wrote on Thu, 06 August 2009 12:00
Then the database doesn't seem to be using the pfile you think it is.

But when you have managed to get the restore working the other way around, you can maybe figure that one out a later time.


Yep, you are right. Got this error at end of restore:
Finished restore at 06-AUG-09
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "tran" RESETLOGS ARCHIVELOG
MAXLOGFILES    254
MAXLOGMEMBERS      2
MAXDATAFILES      500
MAXINSTANCES    16
MAXLOGHISTORY     2268
LOGFILE
GROUP  4 ( 'D:\ORADATA\TRAN\LOG4.ORA', 'E:\ORADATA\TRAN\LOG4A.ORA' ) SIZE   52
428800  REUSE,
GROUP  5 ( 'D:\ORADATA\TRAN\LOG5.ORA', 'E:\ORADATA\TRAN\LOG5A.ORA' ) SIZE   52
428800  REUSE,
GROUP  6 ( 'D:\ORADATA\TRAN\LOG6.ORA', 'E:\ORADATA\TRAN\LOG6A.ORA' ) SIZE   52
428800  REUSE
DATAFILE
'E:\ORADATA\TRAN\SYSTEM1.DBF'
CHARACTER SET WE8ISO8859P1

released channel: C1
released channel: C2
released channel: C3

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/06/2009 12:13:59
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE
failed
ORA-01990: error opening password file 'f:\oracleTRAN\tran9i\DATABASE\PWDtran.ORA'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
MAXLOGFILES    254
MAXLOGMEMBERS      2
MAXDATAFILES      500
MAXINSTANCES    16
MAXLOGHISTORY     2268
LOGFILE
GROUP  4 ( 'D:\ORADATA\TRAN\LOG4.ORA', 'E:\ORADATA\TRAN\LOG4A.ORA' ) SIZE   52
428800  REUSE,
GROUP  5 ( 'D:\ORADATA\TRAN\LOG5.ORA', 'E:\ORADATA\TRAN\LOG5A.ORA' ) SIZE   52
428800  REUSE,
GROUP  6 ( 'D:\ORADATA\TRAN\LOG6.ORA', 'E:\ORADATA\TRAN\LOG6A.ORA' ) SIZE   52
428800  REUSE
DATAFILE
'E:\ORADATA\TRAN\SYSTEM1.DBF'

RMAN>


The password file it is expecting is not the password file I thought I was using. At this point, do I have to restart the restore via RMAN or can I manually create the controlfile?
Re: RMAN-04006 with ORA-01031 (Auxiliary Connect) [message #417596 is a reply to message #417241] Mon, 10 August 2009 07:16 Go to previous message
Mahesh Rajendran
Messages: 10630
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>ORACLE error from auxiliary database
The error is from your auxiliary database.
RMAN is not finding the default directory to create the file.
The default location is $ORACLE_HOME/dbs
check your environment.
post your duplication script.
RMAN will overwrite the passwordfile (if it exists).
Fix this in your original password file and try again.
Previous Topic: Recover Database!!!
Next Topic: Recover database without dump .
Goto Forum:
  


Current Time: Sat Dec 20 10:17:26 CST 2014

Total time taken to generate the page: 0.21399 seconds