OraFAQ Forum: Data Guard » primary don't want be open after standby configuration

Home » Server Options » Data Guard » primary don't want be open after standby configuration

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

primary don't want be open after standby configuration [message #516561]

Tue, 19 July 2011 05:09

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

Hi,

I did standby before and it works wighout any disturbations and even configuration seems to me not very complicated - but it was before.
now i am trying to do standby configuration once again and i don't know where I have corruption in my thinking. :/

there is database named DESK.

two physical serwers:
sczepl-db07 - primary
r00979 - standby

on each for this serwer there is listener listening on port 1521, and on each is tnsnames configured properly:

for sczepl-db07

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sczepl-db07)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\oracle\ora92)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = desk_prm)
      (ORACLE_HOME = C:\oracle\ora92)
      (SID_NAME = desk)
    )      
  )

tns
----------

desk_std =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = r00979)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = desk_std)
    )
  )

desk_prm =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sczepl-db07)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = desk_prm)
    )
  )

for r00979:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = d:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = desk_std)
      (ORACLE_HOME = d:\oracle\ora92)
      (SID_NAME = desk)
    )
  )

LISTENER =
  (DESCRIPTION =
	(ADDRESS_LIST =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = r00979)(PORT = 1521))
	)
  )

tns
-------

desk_std =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = r00979)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = desk_std)
    )
  )

desk_prm =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sczepl-db07)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = desk_prm)
    )
  )

after shudown primary system i copied every data files to the same directory on standby serwer, created pfile from primary and standby controlfile (everything like is written on many publications) and copied them to the standby.

so service for standby is working properly and init configuration looks like this:

background_dump_dest='d:\oracle\admin\desk\bdump'
core_dump_dest='d:\oracle\admin\desk\cdump'
user_dump_dest='d:\oracle\admin\desk\udump'
FAL_SERVER=desk_prm
FAL_CLIENT=desk_std
*.compatible='9.2.0.0.0'
*.control_files='r:\oracle\oradata\desk\STANDBY.CTL'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='desk'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='desk'
*.java_pool_size=0
*.job_queue_processes=4
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=R:\oracle\oradata\DESK\arch'
*.log_archive_format='log_DESK_T%TS%S.arc'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

standby redologs was added for this database.

so lets start our standby server.

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> startup nomount pfile='d:\oracle\ora92\database\initdesk.ora'
Instancja ORACLE zosta│a uruchomiona.

Total System Global Area  101785012 bytes
Fixed Size                   454068 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Baza danych zosta│a zmieniona.

SQL> alter database recover managed standby database disconnect from session;

Baza danych zosta│a zmieniona.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL>

and it looks like it works.

lets try connection from primary serwer to standby - for checking purposes.

C:\oracle\ora92\bin>sqlplus "sys/sys123@desk_std as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Jul 19 11:51:24 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production



SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select host_name from v$instance;


HOST_NAME
----------------------------------------------------------------
R00979

SQL>

so this is prove that connection primary <---> standby works. i can assure you that connection from standby to primary is also working well.

so lets back to the primary db.

SQL> show parameter log_archive_dest_

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
log_archive_dest_1                   string      LOCATION=R:\oracle\oradata\DE
                                                 K\arch
log_archive_dest_10                  string
log_archive_dest_2                   string      SERVICE=desk_std LGWR SYNC AF
                                                 IRM
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
SQL>

so second destrination parameter is made for the standby purposes and I AM SURE that desk_std alias proper which i proved before.

so lets make this primary db protected and open it.

SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL>

and alert log says:

ALTER DATABASE SET STANDBY DATABASE PROTECTED
Tue Jul 19 11:56:54 2011
Completed: ALTER DATABASE SET STANDBY DATABASE PROTECTED
Tue Jul 19 11:57:09 2011
alter database open
Tue Jul 19 11:57:09 2011
LGWR: Primary database is in CLUSTER CONSISTENT mode
LGWR: Primary database is in MAXIMUM PROTECTION mode
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LNS0 started with pid=13
Tue Jul 19 11:57:19 2011
LGWR: Error 12154 verifying archivelog destination LOG_ARCHIVE_DEST_2
LGWR: Continuing...
Tue Jul 19 11:57:19 2011
Errors in file c:\oracle\ora92\admin\desk\bdump\desk_lgwr_3692.trc:
ORA-12154: TNS:could not resolve service name





Dump file c:\oracle\ora92\admin\desk\bdump\desk_lgwr_3692.trc
Tue Jul 19 11:57:09 2011
ORACLE V9.2.0.7.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: desk

Redo thread mounted by this instance: 1

Oracle process number: 5

Windows thread id: 3692, image: ORACLE.EXE


*** SESSION ID:(4.1) 2011-07-19 11:57:09.605
Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
*** 2011-07-19 11:57:09.605 43609 kcrr.c
Initializing NetServer for dest=desk_std
Initializing PGA storage for Netserver communication
Allocating a brand new NetServer
Allocated NetServer 0
	 Starting NetServer
NetServer 0 has been started.Subscribing to KSR Channel 0
success!
Indicating recv buffer for KSR Channel 0
success
Waiting for Netserver 0 to initialize itself
*** 2011-07-19 11:57:12.620 43935 kcrr.c
    Netserver 0 has been initialized
LGWR performing a channel reset to ignore previous responses
LGWR connecting as publisher to KSR Channel 0
LGWR-NS 0 initialized for destination=desk_std
*** 2011-07-19 11:57:12.620 44380 kcrr.c
Making upiahm request to NetServer 0
Waiting for NetServer to respond to upiahm
*** 2011-07-19 11:57:15.057 45465 kcrr.c
Making upidhs request to NetServer 0 (hst 0x03FCEAC0)
NetServer pid:3740
*** 2011-07-19 11:57:19.057 45661 kcrr.c
  upidhs done status 12154
*** 2011-07-19 11:57:19.057 44566 kcrr.c
   upiahm connect done status is 12154 
Error 12154 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'desk_std'
*** 2011-07-19 11:57:19.057
LGWR: Error 12154 verifying archivelog destination LOG_ARCHIVE_DEST_2
Continuing...
ORA-12154: TNS:could not resolve service name
Standby database verification failed:16072
error 16072 detected in background process
ORA-16072: a minimum of one standby database destination is required

so why primary don't see standby db and claims that ther is no standby service? i proved that there is connection primary <--> standby.

where is mistake? i am out of any ideas. i am fighting with this for 2 days.