2. The most important part is after removed standby. Now go back to primary and switch logfile, but it hangs in sql*plus.
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 12528 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'dgs'. Error is 12528.
Mon Jan 21 17:56:38 2013
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/oradata/DG/arch
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
Mon Jan 21 17:57:16 2013
ARC0: Becoming the 'no SRL' ARCH
Mon Jan 21 17:57:17 2013
ARC2: Becoming the 'no SRL' ARCH
...
ARCa: Becoming the 'no SRL' ARCH
Mon Jan 21 18:01:07 2013
Starting background process SMCO
Mon Jan 21 18:01:07 2013
SMCO started with pid=64, OS id=16823
Mon Jan 21 18:02:16 2013
ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance DG - Archival Error
ORA-16014: log 1 sequence# 183 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/u01/oradata/DG/redo01.log'
When startup again, it crashes immediately.
SYS@DG> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------
NEXT_TIME
---------
1 1 183 52428800 512 1 NO INACTIVE 3680301 21-JAN-13 3680304
21-JAN-13
2 1 184 52428800 512 1 NO INACTIVE 3680304 21-JAN-13 3680468
21-JAN-13
3 1 185 52428800 512 1 NO CURRENT 3680468 21-JAN-13 2.8147E+14
SYS@DG> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SYS@DG> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@DG> startup
ORACLE instance started.
Total System Global Area 680607744 bytes
Fixed Size 2229600 bytes
Variable Size 419433120 bytes
Database Buffers 251658240 bytes
Redo Buffers 7286784 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 18386
Session ID: 12 Serial number: 3
Starting background process RVWR
Tue Jan 22 09:47:37 2013
RVWR started with pid=26, OS id=16478
Database mounted in Exclusive Mode
Lost write protection disabled
WARNING: No local destinations have been defined for
archival of the Redo Log.
Completed: ALTER DATABASE MOUNT
Tue Jan 22 09:47:37 2013
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Tue Jan 22 09:47:37 2013
ARC0 started with pid=27, OS id=16481
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Tue Jan 22 09:47:38 2013
ARC1 started with pid=28, OS id=16485
Tue Jan 22 09:47:38 2013
ARC2 started with pid=29, OS id=16487
Errors in file /u01/app/oracle/diag/rdbms/dg/DG/trace/DG_ora_16479.trc:
ORA-16014: log 1 sequence# 1 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/u01/oradata/DG/redo01.log'
USER (ospid: 16479): terminating the instance due to error 16014
System state dump requested by (instance=1, osid=16479), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/dg/DG/trace/DG_diag_16422.trc
Tue Jan 22 09:47:39 2013
License high water mark = 2
Dumping diagnostic data in directory=[cdmp_20130122094739], requested by (instance=1, osid=16479), summary=[abnormal instance termination].
Instance terminated by USER, pid = 16479
USER (ospid: 16495): terminating the instance
Instance terminated by USER, pid = 16495
#trace file content
Trace file /u01/app/oracle/diag/rdbms/dg/DG/trace/DG_ora_16479.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2
System name: Linux
Node name: orarac1poc
Release: 2.6.18-238.el5
Version: #1 SMP Sun Dec 19 14:22:44 EST 2010
Machine: x86_64
VM name: VMWare Version: 6
Instance name: DG
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 16479, image: oracle@orarac1poc (TNS V1-V3)
*** 2013-01-22 09:47:38.586
*** SESSION ID:(125.5) 2013-01-22 09:47:38.586
*** CLIENT ID:() 2013-01-22 09:47:38.586
*** SERVICE NAME:() 2013-01-22 09:47:38.586
*** MODULE NAME:(sqlplus@orarac1poc (TNS V1-V3)) 2013-01-22 09:47:38.586
*** ACTION NAME:() 2013-01-22 09:47:38.586
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
DDE rules only execution for: ORA 312
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
ORA-16014: log 1 sequence# 1 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/u01/oradata/DG/redo01.log'
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+461<-kjzdssdmp adbdrv="" br="" kcfopd="" kjzdicrshnfy="" kjzduptcctx="" kpoal8="" kpooprx="" ksuitm="" opidrv="" opiexe="" opiino="" opiodr="" opiosq0="" opitsk="" ttcpip=""><-sou2o br="" opimai_real="" ssthrdmain="">----- End of Abridged Call Stack Trace -----
*** 2013-01-22 09:47:38.905
USER (ospid: 16479): terminating the instance due to error 16014
ksuitm: waiting up to [5] seconds before killing DIAG(16422)
3. The solution is to remove those parameters related to standby database.
orarac1poc:DG:/u01/app/oracle/product/11.2.0/db_2/dbs> diff initDG.ora initDG.ora.bak
#*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(DG,DGS)'
#*.log_archive_dest_2='service="dgs"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="dgs" net_timeout=30','valid_for=(all_logfiles,primary_role)'
#DG.log_archive_dest_3='location="/u01/oradata/DG/arch"',' valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
#*.log_archive_dest_state_2='ENABLE'
#DG.log_archive_dest_state_3='ENABLE'
SYS@DG> alter database open;
Database altered.
SYS@DG> alter system checkpoint;
System altered.
SYS@DG> show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) DB_UNIQUE_NAME=DG
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_state_1 string ENABLE
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
...
SYS@DG> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@DG> startup
ORACLE instance started.
Total System Global Area 680607744 bytes
Fixed Size 2229600 bytes
Variable Size 419433120 bytes
Database Buffers 251658240 bytes
Redo Buffers 7286784 bytes
Database mounted.
Database opened.
