ORA-16072: a minimum of one standby database destination is required
Posted by Mir Sayeed Hassan on July 15th, 2018
ORA-16072: a minimum of one standby database destination is required
This error occurs in one of our production database after restore & recover in a database open stage
SQL> startup mount ORACLE instance started. Total System Global Area 396726272 bytes Fixed Size 2253504 bytes Variable Size 318770496 bytes Database Buffers 67108864 bytes Redo Buffers 8593408 bytes Database mounted.
SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 31979 Session ID: 386 Serial number: 3
Check the alert log
[oracle@DB ~]$ adrci ADRCI: Release 11.2.0.4.0 - Production on Sun Jul 15 15:18:58 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ADR base = "/u01/app/oracle"
adrci> show home ADR Homes: diag/rdbms/dummy/orcl diag/rdbms/dummy/afcdb diag/rdbms/afcdb/afcfb diag/rdbms/afcdb/afcdb diag/rdbms/proddb/proddb
adrci; set home diag/rdbms/proddb/proddb
adrci> show alert -tail 100 DIA-48415: Syntax erro r found in string [> show ] at column [7]
adrci> show alert -tail 100 2018-07-15 15:16:34.924000 +04:30 RECO started with pid=15, OS id=31965 MMON started with pid=16, OS id=31967 MMNL started with pid=17, OS id=31969 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... starting up 1 shared server(s) ... ORACLE_BASE from environment = /u01/app/oracle ALTER DATABASE MOUNT 2018-07-15 15:16:39.066000 +04:30 Successful mount of redo thread 1, with mount id 1041454355 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT 2018-07-15 15:16:54.952000 +04:30 alter database open read write LGWR: STARTING ARCH PROCESSES ARC0 started with pid=23, OS id=31986 2018-07-15 15:16:56.043000 +04:30 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE LGWR: Primary database is in MAXIMUM AVAILABILITY mode ARC0: STARTING ARCH PROCESSES LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR LGWR: Minimum of 1 LGWR standby database required Errors in file /u01/app/oracle/diag/rdbms/proddb/proddb/trace/proddb_lgwr_31959.trc: ORA-16072: a minimum of one standby database destination is required LGWR (ospid: 31959): terminating the instance due to error 16072 System state dump requested by (instance=1, osid=31959 (LGWR)), summary=[abnormal instance termination]. System State dumped to trace file /u01/app/oracle/diag/rdbms/proddb/proddb/trace/proddb_diag_31947_20180715151656.trc Dumping diagnostic data in directory=[cdmp_20180715151656], requested by (instance=1, osid=31959 (LGWR)), summary=[abnormal instance termination]. --------------------
Solution:
[oracle@DB ~]$ !sq SQL> alter database set standby database to maximize performance; Database altered.
SQL> shu immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.
SQL> startup mount ORACLE instance started. Total System Global Area 396726272 bytes Fixed Size 2253504 bytes Variable Size 318770496 bytes Database Buffers 67108864 bytes Redo Buffers 8593408 bytes Database mounted.
SQL> alter database open; Database altered.
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE
====================Hence tested & verified in our DB======================