I configured a standby database and it worked for a while, but now I noticed that the redo logs thread on standby is far behind the primary. After researching alert log and trace files, I figured out that there is a problem with connection to the standby server.

When I type on the primary server:

sqlplus sys/manager@TESTSTAN as sysdba

The output is:

SQL*Plus: Release Production on Fri Jan 29 13:36:17 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR: ORA-01031: insufficient privileges

But I am able to connect from the standby to the primary:

[oravis@standbysrv bin]$ sqlplus sys/manager@TESTPROD as sysdba
SQL*Plus: Release Production on Fri Jan 29 13:46:18 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size          1336960 bytes
Variable Size         335546752 bytes
Database Buffers       79691776 bytes
Redo Buffers            6094848 bytes
Database mounted.
SQL> select instance_name, status from v$instance;

---------------- ------------
testprod     MOUNTED

Password file the same on both hosts. Also, I've recreated it for a few times and this didn't help. remote_login_passwordfile parameter is exclusive on both servers.

listener.ora on the standby

    (SID_LIST = 
     (SID_DESC =
        (GLOBAL_DBNAME = teststan)
        (ORACLE_HOME = /u01/app/oravis/product/11.2.0/dbhome_1)
        (SID_NAME = test) ) )

     (ADDRESS = (PROTOCOL = TCP)(HOST = standbysrv)(PORT = 1521))
     ) )

tnsnames.ora on the primary:

         (PROTOCOL = TCP)
         (HOST = standbysrv)
         (PORT = 1521)) )
 (CONNECT_DATA = (SERVICE_NAME = teststan) ) 

         (PROTOCOL = TCP)
         (HOST = oratest)
         (PORT = 1521)) )
         (SID = test)
         (SERVICE_NAME = testprod)
 ) )

According to the output of the command ps -ef|grep pmon which has shows that the instance name of the standby database is teststan.

[oravis@standbysrv ~]$ ps -ef|grep pmon oravis 2749 1 0 16:02 ? 00:00:00 ora_pmon_teststan oravis 2825 2697 0 16:03 pts/0 00:00:00 grep pmon

But in the description of listener on standby database, SID_NAME is defined as 'test'.

The Oracle tries to authenticate sys user using password file(orapwtest) which has never existed.
So set the SID_NAME parameter on listener.ora file on standby site to 'teststan'.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.