Dataguard Config

All posts relating to Oracle database administration.

Moderator: Tim...

Re: Dataguard Config

Postby jnrpeardba » Thu Mar 28, 2013 4:18 pm

Hi Tim,

Yes to all of your questions and see results below:

To ensure the correct sid is set

[oracle@pipdl3rhdb01 oracle]$ . oraenv
ORACLE_SID = [dsprd01] ? dsprd01
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@pipdl3rhdb01 oracle]$ sqlplus sys/password@dsprd01_stby as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 28 15:39:13 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: ^C
[oracle@pipdl3rhdb01 oracle]$

I create another password file and copied this across to the standby, placed in $ORACLE_HOME/dbs

orapwd file=orapwdsprd01 password=password entries=25 ignorecase=y force=y

<On primary>

[oracle@pipdl3rhdb01 dbs]$ ls -ltr orapw*

-rw-r-----. 1 oracle oracle 4608 Mar 28 14:24 orapwdsprd01

<On standby>

[oracle@pipdlr3hdb02 dbs]$ ls -ltr orapw*

-rw-r----- 1 oracle oracle 4608 Mar 28 14:27 orapwdsprd01

<tnsnames.ora on standby - which contains both primary and standby entries>

DSPRD01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.60.34)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dsprd01)
)
)

DSPRD01_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.60.35)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dsprd01_stby)
)
)

<Listener.ora on standby - which contains the entry for the stanby instance>

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_NAME = dsprd01_stby.pip.local)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = dsprd01_stby)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pipdlr3hdb02)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

<tnsnames.ora entry on primary for the standby database>

dsprd01_stby
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=pipdlr3hdb02)(PORT=1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dsprd01_stby)
)
)

<Listener.ora entry on primary for standby database>

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dsprd01_stby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = dsprd01_stby)
)
)

Anything else I can check - just let me know

And many thanks again

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: Dataguard Config

Postby Tim... » Thu Mar 28, 2013 4:50 pm

Hi.

I noticed you are using the SERVICE_NAME in the tnsnames.ora entry for the standby "(SERVICE_NAME = dsprd01_stby)", rather than the "(ORACLE_SID = XXXXX)".

Change that to using the SID for the standby entry and I think you my be OK.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Dataguard Config

Postby jnrpeardba » Thu Mar 28, 2013 9:48 pm

Thanks Tim,

I will add the following to my tnsnames.ora

Code: Select all
ORACLE_SID=DSPRD01_STBY


I'll change that, give it a go and let you know in the morning

Cheers

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: Dataguard Config

Postby Tim... » Mon Apr 01, 2013 8:11 am

OK.

Let me know how you get on.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Dataguard Config

Postby jnrpeardba » Tue Apr 02, 2013 8:26 am

Hi Tim,

Sorry for not getting back to you sooner - how dare they close the offices during a bank holiday :shock:

I modified the trnsnames.ora on the standby and received the same error - see log below

Code: Select all
#COGPRD01_STBY =
#  (DESCRIPTION =
#    (ADDRESS_LIST =
#      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.60.35)(PORT = 1521))
#    )
#    (CONNECT_DATA =
#      (SERVICE_NAME = cogprd01_stby)
#    )
#  )
#

DSPRD01_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.60.35)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dsprd01_stby)
        (ORACLE_SID = dsprd01_stby)
    )
  )


The standby database for cogprd01_stby works fine and currently shipping and applying logs

The status of the listener and as you can see the dsprd01_stby listsner is still blocked
Code: Select all
[oracle@pipdlr3hdb02 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-APR-2013 08:36:20

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pipdlr3hdb02)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                02-APR-2013 08:33:11
Uptime                    0 days 0 hr. 3 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/pipdlr3hdb02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pipdlr3hdb02)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cogprd01XDB" has 1 instance(s).
  Instance "cogprd01", status READY, has 1 handler(s) for this service...
Service "cogprd01_stby" has 1 instance(s).
  Instance "cogprd01", status READY, has 1 handler(s) for this service...
Service "dsprd01" has 1 instance(s).
  Instance "dsprd01", status BLOCKED, has 1 handler(s) for this service...
Service "dsprd01_stby" has 2 instance(s).
  Instance "dsprd01", status BLOCKED, has 1 handler(s) for this service...
  Instance "dsprd01_stby", status UNKNOWN, has 1 handler(s) for this service...
Service "misprd01XDB" has 1 instance(s).
  Instance "misprd01", status READY, has 1 handler(s) for this service...
Service "misprd01_stby" has 1 instance(s).
  Instance "misprd01", status READY, has 1 handler(s) for this service...
Service "prsprd01XDB" has 1 instance(s).
  Instance "prsprd01", status READY, has 1 handler(s) for this service...
Service "prsprd01_stby" has 1 instance(s).
  Instance "prsprd01", status READY, has 1 handler(s) for this service...
Service "sibprd01XDB" has 1 instance(s).
  Instance "sibprd01", status READY, has 1 handler(s) for this service...
Service "sibprd01_stby" has 1 instance(s).
  Instance "sibprd01", status READY, has 1 handler(s) for this service...
The command completed successfully


As you can see the listener for dsprd01_stby is still blocked - However I attempted to connect via rman (more in hope than in confidence)

Code: Select all
[oracle@pipdlr3hdb02 admin]$ rman target sys/pipdwds@dsprd01 auxiliary sys/pipdwds@dsprd01_stby

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 2 08:36:49 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DSPRD01 (DBID=4042425853)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges


So Basically the same error as before - I stopped and restarted the database in mount mode to both primary and standby and proved that there is not an issue with the passwords as both connections proved successful

Code: Select all
[oracle@pipdlr3hdb02 admin]$ sqlplus sys/pipdwds@dsprd01 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 2 08:37:22 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


Connection now attempted to the standby which causes error when I try via rman - is now successful
Code: Select all
[oracle@pipdlr3hdb02 admin]$ sqlplus sys/pipdwds@dsprd01_stby as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 2 08:40:47 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[/code]

So if the database is started and not mounted it will error, however if started, mounted and opened the connections are fine
Could it be an issue with a parameter within the spfile - password exlusive and all the main ones are set (log archive, state etc....) - what other parameter could be
affecting this ?

Thanks again Tim,

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: Dataguard Config

Postby Tim... » Tue Apr 02, 2013 9:08 am

Hi.

I really can't think what is causing your problem. You can see what I did in my article and it all worked fine. There is something you are missing, but I can't see what it is. The fact it worked fine for you on other systems also suggests this is a configuration issue...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Dataguard Config

Postby jnrpeardba » Tue Apr 02, 2013 4:56 pm

Hi Tim,

Enough of this messing about - starting halfway, trying to work things out. I simply removed the standby and recreated and now logs are shipping
and applying successfully.

I have no idea as to what was different apart from I have more grey hairs than I did last week :o

Thanks again for your assistance Tim and have a nice evening

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: Dataguard Config

Postby Tim... » Tue Apr 02, 2013 5:39 pm

LOL.

Glad to hear it worked out in the end. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Dataguard Config

Postby jnrpeardba » Wed Apr 10, 2013 10:42 am

Hi Tim,

All was working well for about a week or so and out of the 5 standby's - 4 of them working as should

I believe there was a reboot of the server last week and since last Tuesday one of my databases is shipping but not applying the logs to the standby. Even up to 10 mins ago, I can clearly see logs are shipping, but when I check the status via v$archived_log, I can see the logs are not applied.

I have checked the following



Code: Select all
on Primary
----------

 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
      2279 10-APR-2013 10:45:41 10-APR-2013 10:55:42 NO
      2280 10-APR-2013 10:55:42 10-APR-2013 11:05:42 NO
      2280 10-APR-2013 10:55:42 10-APR-2013 11:05:42 NO
      2281 10-APR-2013 11:05:42 10-APR-2013 11:15:43 NO
      2281 10-APR-2013 11:05:42 10-APR-2013 11:15:43 NO
      2282 10-APR-2013 11:15:43 10-APR-2013 11:25:43 NO
      2282 10-APR-2013 11:15:43 10-APR-2013 11:25:43 NO

2240 rows selected.

on standby
----------
 SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
      2274 10-APR-2013 10:05:39 10-APR-2013 10:05:42 NO
      2275 10-APR-2013 10:05:42 10-APR-2013 10:15:40 NO
      2276 10-APR-2013 10:15:40 10-APR-2013 10:25:41 NO
      2277 10-APR-2013 10:25:41 10-APR-2013 10:35:41 NO
      2278 10-APR-2013 10:35:41 10-APR-2013 10:45:41 NO
      2279 10-APR-2013 10:45:41 10-APR-2013 10:55:42 NO
      2280 10-APR-2013 10:55:42 10-APR-2013 11:05:42 NO
      2281 10-APR-2013 11:05:42 10-APR-2013 11:15:43 NO
      2282 10-APR-2013 11:15:43 10-APR-2013 11:25:43 NO

922 rows selected.

SQL>

on primary
------------

select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log     where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union     select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);

LOGS             TIME
---------------- ---------------------------
Last applied  :  03-APR-13:15:28:49
Last received :  10-APR-13:10:35:41

On Standby
----------


LOGS             TIME
---------------- ---------------------------
Last applied  :  03-APR-13:15:28:49
Last received :  10-APR-13:10:35:41


on Primary
----------
select sequence#, archived, applied from v$archived_log order by sequence#;

 SEQUENCE# ARC APPLIED
---------- --- ---------
      2274 YES NO
      2275 YES NO
      2275 YES NO
      2276 YES NO
      2276 YES NO
      2277 YES NO
      2277 YES NO

2240 rows selected.

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1       2255          1        669
ARCH      CLOSING               1       2256          1        620
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1       2272          1        128
ARCH      CLOSING               1       2272          1        128
ARCH      CLOSING               1       2273          1        198
ARCH      CLOSING               1       2274          1          4
ARCH      CLOSING               1       2275       8192        685
ARCH      CLOSING               1       2276       2048        806
ARCH      CLOSING               1       2277          1        925
ARCH      CLOSING               1       2264          1        834

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1       2265          1        627
ARCH      CLOSING               1       2266          1        685
ARCH      CLOSING               1       2267          1        675
ARCH      CLOSING               1       2268          1        900
ARCH      CLOSING               1       2269          1          2
ARCH      CLOSING               1       2270          1          5
ARCH      CLOSING               1       2271       2048       1012
ARCH      CLOSING               1       2243          1        642
ARCH      CLOSING               1       2244          1        674
ARCH      CLOSING               1       2245       2048        640
ARCH      CLOSING               1       2246          1        886

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1       2247          1        655
ARCH      CLOSING               1       2248          1        628
ARCH      CLOSING               1       2249          1        664
ARCH      CLOSING               1       2250          1        625
ARCH      CLOSING               1       2251       2048        789
ARCH      CLOSING               1       2252          1        668
ARCH      CLOSING               1       2253          1        804
ARCH      CLOSING               1       2254          1        626
LNS       WRITING               1       2278        516          1

31 rows selected.

SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

SQL> select dest_id, status, destination, error from v$archive_dest where dest_id <=2;
rows will be truncated


   DEST_ID STATUS    DESTINATION
---------- --------- -----------------------------------------------------------
         1 VALID     USE_DB_RECOVERY_FILE_DEST
         2 VALID     dsprd01_stby

SQL> select * from v$dataguard_status order by timestamp;

FACILITY                 SEVERITY         DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
------------------------ ------------- ---------- ----------- ---------- --- --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Log Transport Services   Control                0         463          0 YES 10-APR-13 ARCa: Beginning to archive thread 1 sequence 2278 (6965540-6966241)
Log Transport Services   Control                0         464          0 YES 10-APR-13 ARCa: Completed archiving thread 1 sequence 2278 (6965540-6966241)
Log Transport Services   Control                0         465          0 YES 10-APR-13 LNS: Beginning to archive log 2 thread 1 sequence 2279

256 rows selected.

SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      dsprd01
fal_server                           string      DSPRD01_STBY
SQL>

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=dsprd01_stby LGWR asyn
                                                 c valid_for=(online_logfile,pr
                                                 imary_role) db_unique_name=dsp
                                                 rd01_stby
SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=use_db_recovery_file_
                                                 dest valid_for=(all_logfiles,
                                                 all_roles) db_unique_name=dspr
                                                 d01

SQL> show parameter log_archive_dest_state_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1             string      ENABLE


SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(dsprd01,dsprd01_stby)


Test that I can connect to standby database and server

[oracle@pipdl3rhdb01 trace]$ sqlplus sys/password@dsprd01_stby as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 10 10:52:43 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@pipdl3rhdb01 trace]$

I can see this error in the alert log file

Wed Apr 10 10:01:22 2013
Archived Log entry 3343 added for thread 1 sequence 2271 ID 0xf0f2d6fd dest 1:
Wed Apr 10 10:03:06 2013
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 3135 for archive log file 1 to 'dsprd01_stby'
Errors in file /u01/app/oracle/diag/rdbms/dsprd01/dsprd01/trace/dsprd01_nsa2_5228.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 1 thread 1 sequence 2272 (3135)
Wed Apr 10 10:03:08 2013

***********************************************************************
Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pipdlr3hdb02)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dsprd01_stby_DGB)(UR=A)(CID=(PROGRAM=oracle)(HOST=pipdl3rhdb01)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 10-APR-2013 10:03:08
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537

TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507

TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0

***********************************************************************

Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pipdlr3hdb02)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dsprd01_stby_DGB)(UR=A)(CID=(PROGRAM=oracle)(HOST=pipdl3rhdb01)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 10-APR-2013 10:03:08
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537

TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507

TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0


***********************************************************************

Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pipdlr3hdb02)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dsprd01_stby_DGB)(UR=A)(CID=(PROGRAM=oracle)(HOST=pipdl3rhdb01)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 10-APR-2013 10:03:08
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537

TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507

TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0
Wed Apr 10 10:03:26 2013
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
Wed Apr 10 10:03:28 2013
Thread 1 advanced to log sequence 2273 (LGWR switch)
  Current log# 2 seq# 2273 mem# 0: /u03/datafiles/dsprd01/redo02.log
Wed Apr 10 10:03:28 2013
Archived Log entry 3344 added for thread 1 sequence 2272 ID 0xf0f2d6fd dest 1:
Wed Apr 10 10:03:28 2013
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Apr 10 10:03:34 2013
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Wed Apr 10 10:05:36 2013
Thread 1 cannot allocate new log, sequence 2274
Checkpoint not complete
  Current log# 2 seq# 2273 mem# 0: /u03/datafiles/dsprd01/redo02.log
Thread 1 advanced to log sequence 2274 (LGWR switch)
  Current log# 3 seq# 2274 mem# 0: /u03/datafiles/dsprd01/redo03.log
Wed Apr 10 10:05:39 2013
Archived Log entry 3347 added for thread 1 sequence 2273 ID 0xf0f2d6fd dest 1:
Wed Apr 10 10:05:39 2013
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thread 1 cannot allocate new log, sequence 2275
Checkpoint not complete
  Current log# 3 seq# 2274 mem# 0: /u03/datafiles/dsprd01/redo03.log
Thread 1 advanced to log sequence 2275 (LGWR switch)
  Current log# 1 seq# 2275 mem# 0: /u03/datafiles/dsprd01/redo01.log
Wed Apr 10 10:05:42 2013
Archived Log entry 3349 added for thread 1 sequence 2274 ID 0xf0f2d6fd dest 1:
Wed Apr 10 10:15:40 2013
Thread 1 advanced to log sequence 2276 (LGWR switch)
  Current log# 2 seq# 2276 mem# 0: /u03/datafiles/dsprd01/redo02.log
Wed Apr 10 10:15:40 2013
Archived Log entry 3351 added for thread 1 sequence 2275 ID 0xf0f2d6fd dest 1:
Wed Apr 10 10:25:41 2013
Thread 1 advanced to log sequence 2277 (LGWR switch)
  Current log# 3 seq# 2277 mem# 0: /u03/datafiles/dsprd01/redo03.log
Wed Apr 10 10:25:41 2013
Archived Log entry 3353 added for thread 1 sequence 2276 ID 0xf0f2d6fd dest 1:
Wed Apr 10 10:35:41 2013
Thread 1 advanced to log sequence 2278 (LGWR switch)
  Current log# 1 seq# 2278 mem# 0: /u03/datafiles/dsprd01/redo01.log
Wed Apr 10 10:35:41 2013
Archived Log entry 3355 added for thread 1 sequence 2277 ID 0xf0f2d6fd dest 1:
Wed Apr 10 10:45:41 2013
Thread 1 advanced to log sequence 2279 (LGWR switch)
  Current log# 2 seq# 2279 mem# 0: /u03/datafiles/dsprd01/redo02.log
Wed Apr 10 10:45:41 2013
Archived Log entry 3357 added for thread 1 sequence 2278 ID 0xf0f2d6fd dest 1:

Again on primary

SQL> select severity, error_code, message, timestamp from v$dataguard_status where dest_id = 2;

SEVERITY      ERROR_CODE MESSAGE
------------- ---------- -------------------------------------------------------
Warning             3135 LNS: Attempting destination LOG_ARCHIVE_DEST_2 network
Warning             3135 LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect a
Error               3135 Error 3135 for archive log file 1 to 'dsprd01_stby'



On Standby - I cancelled and restarted the redo transport, but still no joy

SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active

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

Database altered.

SQL>


I appeared as though the media recovery was not running, but I had started this first thing this morning

Can you think of anything else I have forgotten or that I can do in order to have these logs applied successfully on the standby?

Many thanks

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: Dataguard Config

Postby Tim... » Wed Apr 10, 2013 10:48 am

Hi.

I would suggest you shutdown the standby database. Make sure all processes are gone (nothing stray hanging about). Then start up the standby and start managed recovery as normal.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Dataguard Config

Postby jnrpeardba » Wed Apr 10, 2013 12:12 pm

Hi Tim,

I completed all of that but still no change - see below:

Code: Select all
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2230072 bytes
Variable Size             343935176 bytes
Database Buffers          180355072 bytes
Redo Buffers                7942144 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

SQL>


SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
      2296 10-APR-2013 12:55:01 10-APR-2013 12:57:33 NO
      2297 10-APR-2013 12:57:33 10-APR-2013 12:57:36 NO
      2298 10-APR-2013 12:57:36 10-APR-2013 12:57:39 NO
      2299 10-APR-2013 12:57:39 10-APR-2013 12:58:04 NO
      2300 10-APR-2013 12:58:04 10-APR-2013 12:58:05 NO
      2301 10-APR-2013 12:58:05 10-APR-2013 12:58:07 NO

941 rows selected.


SQL> select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
  2      from v$archived_log     where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
  3      union     select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
  4      from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);

LOGS             TIME
---------------- ---------------------------
Last applied  :  03-APR-13:15:28:49
Last received :  10-APR-13:12:55:01

SQL>


on primary
----------

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>


SQL> select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log     where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
  2    3      union     select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
  4      from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);

LOGS             TIME
---------------- ---------------------------
Last applied  :  03-APR-13:15:28:49
Last received :  10-APR-13:12:58:07

SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
      2298 10-APR-2013 12:57:36 10-APR-2013 12:57:39 NO
      2299 10-APR-2013 12:57:39 10-APR-2013 12:58:04 NO
      2299 10-APR-2013 12:57:39 10-APR-2013 12:58:04 NO
      2300 10-APR-2013 12:58:04 10-APR-2013 12:58:05 NO
      2300 10-APR-2013 12:58:04 10-APR-2013 12:58:05 NO
      2301 10-APR-2013 12:58:05 10-APR-2013 12:58:07 NO
      2301 10-APR-2013 12:58:05 10-APR-2013 12:58:07 NO

2240 rows selected.

SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      dsprd01
fal_server                           string      DSPRD01_STBY
SQL>

SQL> show parameter dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/dsp
core_dump_dest                       string      /u01/app/oracle/diag/rdbms/dsp
max_dump_file_size                   string      unlimited
shadow_core_dump                     string      partial
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/dsp

SQL> select severity, error_code, message, timestamp from v$dataguard_status where dest_id = 2;
rows will be truncated


SEVERITY      ERROR_CODE MESSAGE
------------- ---------- -------------------------------------------------------
Warning             3135 LNS: Attempting destination LOG_ARCHIVE_DEST_2 network
Warning             3135 LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect a
Error               3135 Error 3135 for archive log file 1 to 'dsprd01_stby'
Warning             3135 LNS: Attempting destination LOG_ARCHIVE_DEST_2 network
Warning             3135 LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect a
Error               3135 Error 3135 for archive log file 1 to 'dsprd01_stby'
Error              12537 PING[ARC2]: Heartbeat failed to connect to standby 'dsp
Warning             3135 LNS: Attempting destination LOG_ARCHIVE_DEST_2 network
Warning             3135 LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect a
Error               3135 Error 3135 for archive log file 2 to 'dsprd01_stby'
Warning             3135 LNS: Attempting destination LOG_ARCHIVE_DEST_2 network

SEVERITY      ERROR_CODE MESSAGE
------------- ---------- -------------------------------------------------------
Warning             3135 LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect a
Error               3135 Error 3135 for archive log file 3 to 'dsprd01_stby'

13 rows selected.

SQL> exit

[oracle@pipdl3rhdb01 prstst01]$ cd /u01/app/oracle/diag/rdbms/dsprd01/dsprd01/trace/
[oracle@pipdl3rhdb01 trace]$ ls -ltr alert*.log
-rw------- 1 oracle oracle 26366 Apr 10 12:58 alert_dsprd01.log
[oracle@pipdl3rhdb01 trace]$ tail -300 alert_dsprd01.log

***********************************************************************

Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pipdlr3hdb02)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dsprd01_stby_DGB)(UR=A)(CID=(PROGRAM=oracle)(HOST=pipdl3rhdb01)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 10-APR-2013 12:54:29
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537

TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507

TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0
Wed Apr 10 12:54:31 2013
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 3135 for archive log file 3 to 'dsprd01_stby'
Errors in file /u01/app/oracle/diag/rdbms/dsprd01/dsprd01/trace/dsprd01_nsa2_5228.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 3 thread 1 sequence 2295 (3135)


***********************************************************************

Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pipdlr3hdb02)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dsprd01_stby_DGB)(UR=A)(CID=(PROGRAM=oracle)(HOST=pipdl3rhdb01)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 10-APR-2013 12:54:32
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537

TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507

TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0


***********************************************************************

Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pipdlr3hdb02)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dsprd01_stby_DGB)(UR=A)(CID=(PROGRAM=oracle)(HOST=pipdl3rhdb01)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 10-APR-2013 12:54:32
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537

TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507

TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0


***********************************************************************

Fatal NI connect error 12537, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pipdlr3hdb02)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dsprd01_stby_DGB)(UR=A)(CID=(PROGRAM=oracle)(HOST=pipdl3rhdb01)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 10-APR-2013 12:54:32
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537

TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507

TNS-00507: Connection closed
    nt secondary err code: 0
    nt OS err code: 0
Wed Apr 10 12:54:55 2013
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
Wed Apr 10 12:55:01 2013
Thread 1 advanced to log sequence 2296 (LGWR switch)
  Current log# 1 seq# 2296 mem# 0: /u03/datafiles/dsprd01/redo01.log
Wed Apr 10 12:55:01 2013
Archived Log entry 3390 added for thread 1 sequence 2295 ID 0xf0f2d6fd dest 1:
Wed Apr 10 12:55:01 2013
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Apr 10 12:55:04 2013
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Wed Apr 10 12:57:31 2013
Thread 1 cannot allocate new log, sequence 2297
Checkpoint not complete
  Current log# 1 seq# 2296 mem# 0: /u03/datafiles/dsprd01/redo01.log
Thread 1 advanced to log sequence 2297 (LGWR switch)
  Current log# 2 seq# 2297 mem# 0: /u03/datafiles/dsprd01/redo02.log
Wed Apr 10 12:57:33 2013
Archived Log entry 3393 added for thread 1 sequence 2296 ID 0xf0f2d6fd dest 1:
Wed Apr 10 12:57:33 2013
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thread 1 cannot allocate new log, sequence 2298
Checkpoint not complete
  Current log# 2 seq# 2297 mem# 0: /u03/datafiles/dsprd01/redo02.log
Thread 1 advanced to log sequence 2298 (LGWR switch)
  Current log# 3 seq# 2298 mem# 0: /u03/datafiles/dsprd01/redo03.log
Wed Apr 10 12:57:36 2013
Archived Log entry 3395 added for thread 1 sequence 2297 ID 0xf0f2d6fd dest 1:
Thread 1 cannot allocate new log, sequence 2299
Checkpoint not complete
  Current log# 3 seq# 2298 mem# 0: /u03/datafiles/dsprd01/redo03.log
Thread 1 advanced to log sequence 2299 (LGWR switch)
  Current log# 1 seq# 2299 mem# 0: /u03/datafiles/dsprd01/redo01.log
Wed Apr 10 12:57:39 2013
Archived Log entry 3397 added for thread 1 sequence 2298 ID 0xf0f2d6fd dest 1:
Wed Apr 10 12:58:04 2013
Thread 1 advanced to log sequence 2300 (LGWR switch)
  Current log# 2 seq# 2300 mem# 0: /u03/datafiles/dsprd01/redo02.log
Wed Apr 10 12:58:04 2013
Archived Log entry 3399 added for thread 1 sequence 2299 ID 0xf0f2d6fd dest 1:
Thread 1 advanced to log sequence 2301 (LGWR switch)
  Current log# 3 seq# 2301 mem# 0: /u03/datafiles/dsprd01/redo03.log
Wed Apr 10 12:58:05 2013
Archived Log entry 3401 added for thread 1 sequence 2300 ID 0xf0f2d6fd dest 1:
Thread 1 advanced to log sequence 2302 (LGWR switch)
  Current log# 1 seq# 2302 mem# 0: /u03/datafiles/dsprd01/redo01.log
Wed Apr 10 12:58:07 2013
Archived Log entry 3403 added for thread 1 sequence 2301 ID 0xf0f2d6fd dest 1:
[oracle@pipdl3rhdb01 trace]$


Does it appear as though I have some kind of network issue? If so - the other 4 databases are shipping and applying successfully, so it seems a tad strange
but my alert log and the trace file are indicating this - the listener at both ends are operating as normal including the entries for primary and standby

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: Dataguard Config

Postby Tim... » Wed Apr 10, 2013 2:51 pm

Hi.

You have the database opened in read-only mode, prior to starting managed recovery. I assume you are using (and paid for) the active data guard option...

Can you try doing a normal managed recovery, rather than active data guard and see what happens then.

The error messages you are showing suggest that your standby is not able to write to its standby locations, which is possibly why no changes are being applied. make sure the standby database has the relevant standby locations available.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Dataguard Config

Postby jnrpeardba » Wed Apr 10, 2013 4:55 pm

Hi Tim,

I stopped and restarted the database with a 'startup mount'. This time I did not perform an open database read-only, simple restarted recovery media in the normal manner.

Performed a few log switches on the primary and still the same error messages, with the database only mounted and not in read-only mode.

By normal managed recovery, I take it you mean
Code: Select all
alter database recover managed standby database disconnect from session;
- which is what I have executed

Again I see logs shipping but not applying on the standby

Thanks for your assistance - the other 4 databases are all opened in read-only mode

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: Dataguard Config

Postby Tim... » Wed Apr 10, 2013 5:33 pm

Hi.

You've not mentioned my point about the archive location on the standby database. The error messages coming put of the standby suggest it is not able to write to the archive destinations, which will prevent changes from being applied. Did you check these destinations are really available on the standby?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Dataguard Config

Postby jnrpeardba » Wed Apr 10, 2013 6:00 pm

Hi Tim,

I've left the office now, but I will check that in the morning - it is strange as was writing to the location before. But I will check that out in the morning and update you

Thanks again Tim,

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

PreviousNext

Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 8 guests