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 188.8.131.52.0 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 184.108.40.206.0 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; INSTANCE_NAME STATUS ---------------- ------------ 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_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = teststan) (ORACLE_HOME = /u01/app/oravis/product/11.2.0/dbhome_1) (SID_NAME = test) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standbysrv)(PORT = 1521)) ) ) DYNAMIC_REGISTRATION_LISTENER=off
tnsnames.ora on the primary:
TESTSTAN = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = standbysrv) (PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = teststan) ) ) TESTPROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = oratest) (PORT = 1521)) ) (CONNECT_DATA = (SID = test) (SERVICE_NAME = testprod) ) )