data guard idle instance

[luis diaz]

Hi all

(oracle 10gr2 -- linux rhel4)

I install a physical standby database in a separate server.
So I have 2 servers...instance  names: primary and stndby

So, after execute all the steps I try and I see this error message from the alert log of primary db:

Wed Jun 23 10:15:31 2010
Errors in file /exlibris/app/oracle/admin/primary/bdump/primary_arc0_9910.trc:
ORA-01034: ORACLE not available
PING[ARC0]: Heartbeat failed to connect to standby 'stndby'. Error is 1034.

I check internet and I see that probably it can be a password file issue.

I check the password file by using sqlplus from one server to an other:

From standby to primary:

sqlplus sys/xxxxxxx@primary as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 23 09:06:30 2010

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

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

SQL>

Looks fine

And now from primary to target:

sqlplus sys/xxxxxxx@stndby as sysdba                   
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 23 10:20:39 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle>

Strange because the standby db is UP and MOUNTED.
I follow this steps on the standby:
SQL> startup nomount
ORACLE instance started.

Total System Global Area  734003200 bytes
Fixed Size                  1221564 bytes
Variable Size             192941124 bytes
Database Buffers          536870912 bytes
Redo Buffers                2969600 bytes


SQL> alter database mount standby database;
Database altered.


and then

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

The log of all those steps seem to be clean:

LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on.
IMODE=BR
ILAT =48
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
  processes                = 400
  __shared_pool_size       = 184549376
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  resource_manager_plan    = SYSTEM_PLAN
  sga_target               = 734003200
  control_files            = /exlibris/oradata/stndby/stndby.ctl
  db_file_name_convert     = primary, stndby
  log_file_name_convert    = /exlibris/oradata/primary, /exlibris/oradata/stndby
  db_block_size            = 8192
  __db_cache_size          = 536870912
  compatible               = 10.2.0.1.0
  log_archive_config       = DG_CONFIG=(primary,stndby)
  log_archive_dest_1       = LOCATION=/exlibris/oradata/stndby/arch VALID_FOR=(A                                  LL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stndby
  log_archive_dest_2       = SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFIL                                  ES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary
  log_archive_dest_state_1 = ENABLE
  log_archive_dest_state_2 = ENABLE
  log_archive_max_processes= 2
  log_archive_min_succeed_dest= 1
  standby_archive_dest     = /exlibris/oradata/stndby/arch
  log_archive_trace        = 0
  log_archive_format       = %t_%s_%r.dbf
  fal_client               = stndby
  fal_server               = primary
  log_buffer               = 2923520
  archive_lag_target       = 0
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /exlibris/oradata/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  standby_file_management  = AUTO
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  recyclebin               = off
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = greendata.es
  instance_name            = stndby
  service_names            = stndby
  cursor_space_for_time    = TRUE
  job_queue_processes      = 10
  background_dump_dest     = /exlibris/app/oracle/admin/stndby/bdump
  user_dump_dest           = /exlibris/app/oracle/admin/stndby/udump
  core_dump_dest           = /exlibris/app/oracle/admin/stndby/cdump
  audit_file_dest          = /exlibris/app/oracle/admin/stndby/adump
  db_name                  = primary
  db_unique_name           = stndby
  open_cursors             = 300
  optimizer_mode           = CHOOSE
  pga_aggregate_target     = 209715200
  dg_broker_start          = TRUE
PMON started with pid=2, OS id=23206
PSP0 started with pid=3, OS id=23208
MMAN started with pid=4, OS id=23210
DBW0 started with pid=5, OS id=23212
LGWR started with pid=6, OS id=23214
CKPT started with pid=7, OS id=23216
SMON started with pid=8, OS id=23218
RECO started with pid=9, OS id=23220
CJQ0 started with pid=10, OS id=23222
MMON started with pid=11, OS id=23224
MMNL started with pid=12, OS id=23226
DMON started with pid=14, OS id=23229
Wed Jun 23 09:09:00 2010
alter database mount standby database
Wed Jun 23 09:09:04 2010
Setting recovery target incarnation to 1
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=15, OS id=23231
Wed Jun 23 09:09:04 2010
ARC0: Archival started
ARC1: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Wed Jun 23 09:09:04 2010
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC0: Thread not mounted
ARC1 started with pid=16, OS id=23233
ARC1: Becoming the heartbeat ARCH
ARC1: Thread not mounted
Wed Jun 23 09:09:04 2010
Successful mount of redo thread 1, with mount id 1559949548
Wed Jun 23 09:09:04 2010
Physical Standby Database mounted.
Completed: alter database mount standby database
Wed Jun 23 09:09:06 2010
Starting Data Guard Broker (DMON)
Wed Jun 23 09:09:49 2010
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Jun 23 09:09:49 2010
Attempt to start background Managed Standby Recovery process (stndby)
MRP0 started with pid=17, OS id=23235
Wed Jun 23 09:09:49 2010
MRP0: Background Managed Standby Recovery process started (stndby)
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 33
Wed Jun 23 09:09:55 2010
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSI                                  ON



And if I check v$database:
SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

That is the reason why I do not anderstand the IDLE message

Some one knows?
THanks

[ddf]

On Jun 23, 4:19 am, luis diaz <luisete2...@gmail.com> wrote:
> Hi all
>
> (oracle 10gr2 -- linux rhel4)
>
> I install a physical standby database in a separate server.
> So I have 2 servers...instance  names: primary and stndby
>
> So, after execute all the steps I try and I see this error message from the
> alert log of primary db:
>

> *Wed Jun 23 10:15:31 2010

> Errors in file
> /exlibris/app/oracle/admin/primary/bdump/primary_arc0_9910.trc:
> ORA-01034: ORACLE not available
> PING[ARC0]: Heartbeat failed to connect to standby 'stndby'. Error is 1034.

> *

> *LICENSE_SESSIONS_WARNING = 0

> *

>
> And if I check v$database:
> SQL> select open_mode from v$database;
>
> OPEN_MODE
> ----------
> MOUNTED
>
> That is the reason why I do not anderstand the IDLE message
>
> Some one knows?
> THanks

You need to check the tnsnames.ora file on the primary as it likely
lists an incorrect ORACLE_HOME for the standby database. The message
would be correct as no database named stndby is running under the
listed ORACLE_HOME.



David Fitzjarrell

[Luis]

Hi
the tnsnames? I have a value of ORACLE_HOME in the listener, in
TNSNAMES I do not have any oracle_home value:

Listener Primary:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = verde.greendata.es)(PORT =
1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /exlibris/app/oracle/product/102/)
(SID_NAME = primary)
)
(SID_DESC =
(GLOBAL_DBNAME = stndby)
(ORACLE_HOME = /exlibris/app/oracle/product/102/)
(SID_NAME = stndby)
)
)

If I go to the standby and I execute echo $ORACLE_HOME I see a / as
diference.
THis is the probleme??

echo $ORACLE_HOME
/exlibris/app/oracle/product/102

[ameet kumar]

Hi..

 

   Copy the password file from primary side to stndby server after that restart the standby database and also chech that both listener are started (primary and standby)

 

--
You received this message because you are subscribed to the Google Groups "ORACLE_DBA_EXPERTS" group.
To post to this group, send email to oracle_db...@googlegroups.com.
To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.

--

Thanks & Regards

Ameet Kumar
Technical Team Lead (Oracle Database & ERP)

Ora-Tech Systems (Pvt.) Ltd.
190-A, Block A, S.M.C.H.S
Shahrah-e-Faisal
Karachi, Pakistan
74400

Tel : (+92-21) 3-111 – ORACLE (111-672-253)  Ext: 218
Mobile: +92-334-3215398
Fax : (92-21) 34535994
Email: ameet...@ora-tech.com

[ddf]

> > David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

It could very well be the issue as there is no database named stndby
running under that ORACLE_HOME; it could also be that your
tnsnames.ora file and listener.ora file are listing the wrong SID for
the stndby database as the SID could be in all upper-case letters.
Something is misconfigured in one of those two files or in the
listener.ora on the standby server because according to Oracle no
instance of the listed name is running under the specified
ORACLE_HOME.

A proper configuration would result in your connection attempt
displaying the following message:

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

Since the database is only mounted Oracle cannot know if the database
is starting up, shutting down or staying where it is. You cannot
connect remotely to the instance/database named stndby since it is not
open.



David Fitzjarrell

[ddf]

On Jun 23, 1:30 pm, ameet kumar <nihalaniam...@gmail.com> wrote:
> Hi..
>
>    Copy the password file from primary side to stndby server after that
> restart the standby database and also chech that both listener are started
> (primary and standby)
>
>
>
>
>

[bulk anipped]

>
> Thanks & Regards
>
> Ameet Kumar
> Technical Team Lead (Oracle Database & ERP)
>
> Ora-Tech Systems (Pvt.) Ltd.
> 190-A, Block A, S.M.C.H.S
> Shahrah-e-Faisal
> Karachi, Pakistan
> 74400
>
> Tel : (+92-21) 3-111 – ORACLE (111-672-253)  Ext: 218
> Mobile: +92-334-3215398
> Fax : (92-21) 34535994

> Email: ameet.ku...@ora-tech.com- Hide quoted text -

>
> - Show quoted text -

Please explain in detail how a password file problem results in a
"Connected to an idle iostance" message as I'm confused by your
'answer'. Oracle is reporting that no instance by the given SID is
running under the specified ORACLE_HOME, a fact having nothing at all
to do with the password file. For the sake of argument let's suppose
I have a database named snuffy. Setting the proper environment I can
connect to snuffy without error:

smando@plinkensparg:/opt/oracle
<snuffy> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 23 22:46:11 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

snuffy>>

Now let's change the ORACLE_SID to that of another running database on
that server, smilnorf. We do NOT change the ORACLE_HOME, just the
ORACLE_SID. All of a sudden smilnorf is idle:

<snuffy> export ORACLE_SID=smilnorf
smando@plinkensparg:/opt/oracle
<smilnorf> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 23 22:42:27 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

idle>>

It's because smilnorf is running under its own ORACLE_HOME, not the
ORACLE_HOME of snuffy. But, wait, let's reset the the ORACLE_SID to
SNUFFY instead of snuffy:

<snuffy> export ORACLE_SID=SNUFFY
smando@plinkensparg:/opt/oracle
<SNUFFY> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 23 22:50:29 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

idle>>

Gee, it's now idle, too, because the ORACLE_SID is not what was
specified when the database was created (all upper-case in place of
all lower-case).

The password file has absolutely nothing to do with this issue. Of
course you're welcome to prove me wrong.


David Fitzjarrell

[ameet kumar]

Hi,

 

  This error ORA-01034: ORACLE not available occure becuase of standby listener is down.also email me the contents of tnsnames.ora.

 

 

--
You received this message because you are subscribed to the Google Groups "ORACLE_DBA_EXPERTS" group.
To post to this group, send email to oracle_db...@googlegroups.com.
To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.

--

Thanks & Regards

Ameet Kumar
Technical Team Lead (Oracle Database & ERP)

Ora-Tech Systems (Pvt.) Ltd.
190-A, Block A, S.M.C.H.S
Shahrah-e-Faisal
Karachi, Pakistan
74400

Tel : (+92-21) 3-111 – ORACLE (111-672-253)  Ext: 218
Mobile: +92-334-3215398
Fax : (92-21) 34535994

Email: ameet...@ora-tech.com

[Luis]

Hello All
yesterday it was SAN JUAN, a fiesta day here in spain, so I check this
issue this morning !! And it's ok It was the ORACLE_HOME value defined
in both listener files, there was a "/" more....So I remove ii and and
restart both listeners, now I have this log I suppose it's fine:


sys@PRIMARY> alter system switch logfile;
System altered.

primary log
Thu Jun 24 11:09:12 2010
Thread 1 advanced to log sequence 12 (LGWR switch)
Current log# 3 seq# 12 mem# 0: /exlibris/oradata/primary/redo3.log

stnadby log:
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: No standby redo logfiles created
Thu Jun 24 09:55:42 2010
Media Recovery Log /exlibris/oradata/stndby/arch/1_11_722447819.dbf
Media Recovery Waiting for thread 1 sequence 12 (in transit)


Thanks a lot for all the usefull help !!
Cheers

[ddf]

> > oracle_dba_expe...@googlegroups.com<oracle_dba_experts%2Bunsubs­cr...@googlegroups.com>
> > .

> > For more options, visit this group at
> >http://groups.google.com/group/oracle_dba_experts?hl=en.
>
> --
>
> Thanks & Regards
>
> Ameet Kumar
> Technical Team Lead (Oracle Database & ERP)
>
> Ora-Tech Systems (Pvt.) Ltd.
> 190-A, Block A, S.M.C.H.S
> Shahrah-e-Faisal
> Karachi, Pakistan
> 74400
>
> Tel : (+92-21) 3-111 – ORACLE (111-672-253)  Ext: 218
> Mobile: +92-334-3215398
> Fax : (92-21) 34535994

> Email: ameet.ku...@ora-tech.com- Hide quoted text -
>
> - Show quoted text -

No, absolutely not. Having a listener down reports the following
message:

ERROR:
ORA-12541: TNS:no listener

NOT "Connected to an idle instance". With a listener down one CANNOT
connect remotely to the associated database, period. Your knowledge
of Oracle is severely lacking given the responses you've posted to
this thread.

Please read the most recent post from the Luis and see whose
assessment of the situation was correct.


David Fitzjarrell

[ameet kumar]

Ohh Sorry I have posted the solution of problem of an other group.

 

 

 

To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.

--

Thanks & Regards

Ameet Kumar
Technical Team Lead (Oracle Database & ERP)

Ora-Tech Systems (Pvt.) Ltd.
190-A, Block A, S.M.C.H.S
Shahrah-e-Faisal
Karachi, Pakistan
74400

Tel : (+92-21) 3-111 – ORACLE (111-672-253)  Ext: 218
Mobile: +92-334-3215398
Fax : (92-21) 34535994

Email: ameet...@ora-tech.com

[ddf]

Which group which thread? I monitor most of the Oracle newsgroups and
do not recall seeiong any thread on this 'heartbeat' problem.


David Fitzjarrell

[ameet kumar]

Oracle dbas yahoo group.

To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.

[ddf]

> Ameet Kumar- Hide quoted text -

>
> - Show quoted text -

I'm a member of that group and I haven't seen such a question posted.
Funny how http://tech.groups.yahoo.com/group/dba_gurus/messages/21299
doesn't look at all like http://groups.google.com/group/oracle_dba_experts.


David Fitzjarrell