Hello all,
I have a 2 node test cluster with one standby server. I am getting an ora-1033 error on the primary node. I have recreated the password file multiple times in a shared location on the primary nodes and copied it to standby:
[oracle@sandbox1 test3]$ orapwd password=password file=/u01/shared_data/oradata/test3/orapwtest3 entries=5
[oracle@sandbox1 test3]$ scp orapwtest3 sandbox1-dr:/u01/shared_data/oradata/test3/
I have another test environment on this server that I was able to create with dataguard successfully months ago. I know that ora-1033 means that oracle is starting up or shutting down. However, when I googled the dataguard error below "Error 1033 received logging on to the standby" from the primary nodes and "ORA-16191" on the standby there where multiple blogs saying the issue is due to the password file. The permission on the password file both on the primary nodes and standby is -rw-r-----. 1 oracle oinstall. I was wondering if I can get some fresh eyes to see if I am missing something.
Thanks!
PRIMARY NODE
test3_1> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
test3_1> select current_scn from v$database;
CURRENT_SCN
-----------
9948466
test3_1> select host_name,status from v$instance;
HOST_NAME STATUS
---------------------------------------------------------------- ------------
sandbox1.test.com OPEN
test3_1> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
test3_1> select status, error from v$archive_dest;
STATUS ERROR
--------- -----------------------------------------------------------------
VALID
ERROR ORA-01033: ORACLE initialization or shutdown in progress <----------
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
test3_1> select message from v$dataguard_status;
MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
ARC3: Archival started
ARC2: Beginning to archive thread 2 sequence 300 (9916239-9933339)
Error 1034 received logging on to the standby
ARC2: Completed archiving thread 2 sequence 300 (9916239-9933339)
ARC2: Beginning to archive thread 2 sequence 301 (9933339-9937755)
MESSAGE
--------------------------------------------------------------------------------
ARC2: Completed archiving thread 2 sequence 301 (9933339-9937755)
Error 1033 received logging on to the standby
Error 1033 received logging on to the standby
Error 1033 received logging on to the standby
Error 1033 received logging on to the standby
Error 1033 received logging on to the standby
Error 1033 received logging on to the standby
Error 1033 received logging on to the standby
Error 1033 received logging on to the standby
Error 1033 received logging on to the standby
Error 1033 received logging on to the standby
22 rows selected.
test3_1> select username from v$pwfile_users;
USERNAME
------------------------------
SYS
test3_1> show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=/u01/shared_fra/fast_
recovery_area/test3
.
.
.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string SERVICE=test3_dr lgwr valid_for=(
online_logfiles,primary_role)
db_unique_name=test3_dr reopen=12
0
.
.
.
log_archive_dest_state_1 string enable
log_archive_dest_state_2 string enable
[oracle@sandbox1 dbs]$ tail /u01/app/oracle/diag/rdbms/test3/test3_1/trace/alert_test3_1.log
Thu Sep 29 13:48:37 2016
Error 1033 received logging on to the standby
Thu Sep 29 13:50:40 2016
Error 1033 received logging on to the standby
Thu Sep 29 13:52:44 2016
Error 1033 received logging on to the standby
Thu Sep 29 13:54:47 2016
Error 1033 received logging on to the standby
Thu Sep 29 13:56:50 2016
Error 1033 received logging on to the standby
STANDBY
test3 > select current_scn from v$database;
CURRENT_SCN
-----------
9898182
test3 > select host_name,status from v$instance;
HOST_NAME STATUS
---------------------------------------------------------------- ------------
sandbox1-dr.test.com MOUNTED
test3 > select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
test3 > select status, error from v$archive_dest;
STATUS ERROR
--------- -----------------------------------------------------------------
VALID
VALID
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
test3 > select username from v$pwfile_users;
USERNAME
------------------------------
SYS
SYSDG
SYSBACKUP
SYSKM
test3 > show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=/u01/shared_fra/fast_
recovery_area/test3_DR
.
.
.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string SERVICE=test3 lgwr valid_for=(onl
ine_logfiles,primary_role) db_
unique_name=test3 reopen=120
.
.
.
log_archive_dest_state_2 string enable
log_archive_dest_state_1 string enable
[oracle@sandbox1-dr test]$ tail -20 /u01/app/oracle/diag/rdbms/test3_dr/test3/trace/alert_test3.log
FAL[client, USER]: Error 16191 connecting to test3 for fetching gap sequence
Thu Sep 29 13:37:35 2016
Errors in file /u01/app/oracle/diag/rdbms/test3_dr/test3/trace/test3_pr00_28795.trc:
ORA-16191: Primary log shipping client not logged on standby <-------
Thu Sep 29 13:37:45 2016
FAL[client]: Failed to request gap sequence
GAP - thread 2 sequence 291-291
DBID 3337742635 branch 919669228
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------
Thu Sep 29 13:48:59 2016
db_recovery_file_dest_size of 1024000 MB is 0.06% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
[oracle@sandbox1-dr test]$ sqlplus sys@test3 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 29 14:16:59 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
test> select host_name from gv$instance union select name from v$database;
HOST_NAME
----------------------------------------------------------------
TEST3
sandbox1.test.com
sandbox2.test.com