This forum is currently locked. You can't register or post questions at this time. (read more)

Dataguard Config

All posts relating to Oracle database administration.

Moderator: Tim...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Dataguard Config

Postby Tim... » Fri Mar 22, 2013 9:51 am

Hi.

The status suggests that starting managed recovery will automatically resolve the gap...

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

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

Re: Dataguard Config

Postby jnrpeardba » Mon Mar 25, 2013 8:39 am

Thanks Tim,

All seems to be fine just now - so thanks again

However (you know me I always seem to have another theory :shock: )

With one of my instances on primary, which was not shipping and applying logs, I did a manual remote copy and then applied via

Code: Select all

alter database register logfile

So for example archive logs stored in 2013_03_19 I applied one at a time. I was wondering is there a quicker way to apply like an entire directory. One day I may be on a site and there will be 50 - 60 archive logs. There must be a better way to copy the details into a notepad and then modifying the path and issuing a register for 50 odd files. Is there a way I can register an entire directory and all the archive logs stored within that directory?

Many thanks again :)

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Dataguard Config

Postby Tim... » Mon Mar 25, 2013 12:49 pm

Hi.

If you can query something, you can concatenate that value into the string that performs the whole action, then spool that out to a file. How are you getting your list of files?

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

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

Re: Dataguard Config

Postby jnrpeardba » Mon Mar 25, 2013 5:22 pm

Hi Tim,

For example - I logged onto the primary at the time and performed my query to check the sequence numbers and compared this to what is on the standby
I then went to the directory where the archive logs are and manually secured copy these over the network to the standby server and correct location

I then run an 'ls -l' in that folder and copy that list of files into a notepad

My notepad has an

Code: Select all

alter register logfile 'path'
I then add the file name after path so it ends up like this

Code: Select all

alter database register logfile '/u01/app/oracle/fast_recovery_area/instance/archivelog/2013_03/20/01_mf_1_164_8o0h23y7_.arc';


But I may have 25 such lines, where I need to change the filename. I then copy that section into sqlplus and execute - which all works fine.
I was just wondering if I could do something easier like

Code: Select all

alter database register directory 2013_03_20
which would in-turn register all log files contained within that directory

Hope that is clear Tim,

Many thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Dataguard Config

Postby Tim... » Mon Mar 25, 2013 5:50 pm

Hi.

In that case I would say you can process your output in a script.

Code: Select all

$ cd /directory/containing/logs
$ ls -ld $PWD/* | awk '{ print "alter database register logfile \x27" $9 "\x27;" }'


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

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

Re: Dataguard Config

Postby jnrpeardba » Tue Mar 26, 2013 9:51 am

Many thanks,

Worked like a charm :D

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Dataguard Config

Postby Tim... » Tue Mar 26, 2013 3:45 pm

:)
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

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

Re: Dataguard Config

Postby jnrpeardba » Wed Mar 27, 2013 4:38 pm

It's only me again Tim,

Had to add another standby today and am met with an issue which I did not encounter on the previous 5 builds.

What causes the instance to be blocked and how to resolve - see log of activity below:

Code: Select all

[oracle@pipdlr3hdb02 dbs]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 27-MAR-2013 15:40:02

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                27-MAR-2013 15:38:50
Uptime                    0 days 0 hr. 1 min. 12 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 1 instance(s).
  Instance "dsprd01", status BLOCKED, 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


Code: Select all

[oracle@pipdlr3hdb02 dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 27 15:40:50 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> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2230072 bytes
Variable Size             335546568 bytes
Database Buffers          188743680 bytes
Redo Buffers                7942144 bytes
Database mounted.
Database opened.
SQL>


Now RMAN will open correctly

Code: Select all

[oracle@pipdlr3hdb02 dbs]$ rman target sys/password@dsprd01 auxiliary sys/password@dsprd01_stby

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 27 15:41:39 2013

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

connected to target database: DSPRD01 (DBID=4042425853)
connected to auxiliary database: DSPRD01 (DBID=4042425853)

RMAN>


However to perform a duplicate I require the database to be opened and in 'nomount' mode- but when I do this the listener becomes blocked and you cannot perform the rman connection phase

Code: Select all

[oracle@pipdlr3hdb02 dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 27 15:43:12 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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2230072 bytes
Variable Size             335546568 bytes
Database Buffers          188743680 bytes
Redo Buffers                7942144 bytes
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: Select all

[oracle@pipdlr3hdb02 dbs]$ rman target sys/password@dsprd01 auxiliary sys/password@dsprd01_stby

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 27 15:43:33 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-12528: TNS:listener: all appropriate instances are blocking new connections


Code: Select all

[oracle@pipdlr3hdb02 dbs]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 27-MAR-2013 15:43:40

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                27-MAR-2013 15:38:50
Uptime                    0 days 0 hr. 4 min. 50 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).
  [b]Instance "dsprd01", status BLOCKED, has 1 handler(s) for this service...[/b]
Service "dsprd01_stby" has 1 instance(s).
  [b]Instance "dsprd01", status BLOCKED, has 1 handler(s) for this service...[/b]
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
[oracle@pipdlr3hdb02 dbs]$


What can I do in order for the database to be opened in 'nomount' mode and the listener to be ready?

Many thanks in advance

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Dataguard Config

Postby Tim... » Wed Mar 27, 2013 4:53 pm

Hi.

It looks to me like you are relying on automatic registration of the DB service with the listener. This only takes place when the database is open.

To make this work you have to define the instance in the listener.ora, as I explain in my article.

http://www.oracle-base.com/articles/11g ... uplication

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

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

Re: Dataguard Config

Postby jnrpeardba » Thu Mar 28, 2013 1:25 pm

Hi Tim,

Made my changes as described in the document

Code: Select all

Listener.ora file

[oracle@pipdlr3hdb02 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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



Listener status still showing blocked after stopping and starting the listener

Code: Select all

[oracle@pipdlr3hdb02 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 28-MAR-2013 12:41:17

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                28-MAR-2013 09:18:44
Uptime                    0 days 3 hr. 22 min. 33 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


After So now when I attempt to establish connection using RMAN - I receive a different error message

Code: Select all

[oracle@pipdlr3hdb02 admin]$ rman target sys/password@dsprd01 auxiliary sys/password@dsprd01_stby

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 28 12:41:31 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
[oracle@pipdlr3hdb02 admin]$


I have copied over the password file from primary to standby and tested the connection from prim to standby and standby to primary (see below)

Code: Select all

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

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 28 12:53:18 2013

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

ERROR:
ORA-01031: insufficient privileges


Enter user-name:

[oracle@pipdlr3hdb02 admin]$ sqlplus sys/password@dsprd01 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 28 12:54:37 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@pipdlr3hdb02 admin]$


So I can gain connection from standby to primary but not from primary to standby - My feeling is that it's an issue with the listener or tnsnames.ora
but at which end or am I way off the mark?

Jnrpeardba

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Dataguard Config

Postby Tim... » Thu Mar 28, 2013 2:02 pm

Hi.

You are running the rman session on the standby server, not the primary server right?

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

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

Re: Dataguard Config

Postby jnrpeardba » Thu Mar 28, 2013 2:31 pm

Yes I am Tim - running the rman on the standby server

Cheers again :)

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Dataguard Config

Postby Tim... » Thu Mar 28, 2013 2:35 pm

Hi.

And you restarted the listener after making the changes?

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

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

Re: Dataguard Config

Postby jnrpeardba » Thu Mar 28, 2013 2:59 pm

Also restarted the listener on the standby after making the listener changes

thx

Jnrpeardba
Last edited by jnrpeardba on Thu Mar 28, 2013 3:00 pm, edited 1 time in total.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Dataguard Config

Postby Tim... » Thu Mar 28, 2013 3:18 pm

Hi.

As far as I can remember, the only things that should stop you logging in are:

- You don't have a password file, or the password file does not contain a password that matches the primary database.
- The correct listener config has not been done.
- The correct tnsnames.ora entries are not present.
- The database is not started (in nomount mode).

You did have the ORACLE_SID exported right?

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


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests