standby database

Thanks actonwang for quick response.
so that means there is only one listener.ora file,we just need to add entry for standby database and it can be called configuration at standby site?
Thanks in advance.

This is not the same directory structure because the data files will reside in different named directories.
So you have to use rename option.

Because these are different DBs (but on the sme computer) they have to have different entries in the common Listener
directories.

>>so that means there is only one listener.ora file,we just need to add entry for standby database and it can be called configuration at standby site?
     YES. standby db is no different from other database in a sense.

Thanks schwertner.
here I can use manually rename or db_file_name_convert parameter? but if I want to configure standby database at remote site, with same or different dir structure, do I need to install oracle on each and everey server or only net 8 configuration is sufficient?
Thanks in advance

Thanks actonwang.
so same site we can use only one configuration file(listener.ora).
Thanks in advance.

yes. for example, in your listaner.ora, you could have the following (change values according to your actual settings):

...........

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = primary)
      (ORACLE_HOME = C:\database\oracle\ora92)
      (SID_NAME = primary)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = standby)
      (ORACLE_HOME = C:\database\oracle\ora92)
      (SID_NAME = standby)
    )  
    ...
  )

I believe this is mentioned in the steps for standby server and I already showed the link for you in some other post.

and if   I use this ---
Primary---/oracle/oradata/standby
Standby ---/oracle/oradata/standby/newstand

It will call same dir structure or not?
Thanks in advance.

Thanks actonwang.
Yes, I have that link ,but still I got confused that's why I want to understand this, but now this is clear to me about listener.ora file.

I configured standby database on same host for test purpose, I am able to mount it but not to open, If I tried to open it ,getting message that its only restricted to read only. and if I issued these command getting these messages--

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 2 10:06:33 2006

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


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

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  126950220 bytes
Fixed Size                   453452 bytes
Variable Size             109051904 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF'


SQL> recover standby database;
ORA-00279: change 6333534 generated at 07/27/2006 10:17:22 needed for thread
20951
ORA-00289: suggestion : C:\ORACLE\ORADATA\NEWSTAND\LOG20951_0.ARC


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log 'C:\ORACLE\ORADATA\NEWSTAND\LOG20951_0.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF'

Thanks in advance

As per my understanding this is different dir structure because datafiles resides in different dir.
and if   I use this ---
Primary---/oracle/oradata/standby
Standby ---/oracle/oradata/standby/newstand

Thanks in advance.

I don't see that it has sth to do with your configuratin with listener. you generally do:

1. set up database and instance for it
2. set up listener to link to the instance.

how you choose the directory for each database is only relevant to step 1.

Thanks actonwang
but when I issued this command on cmd prompt ---
C:\Documents and Settings\Lisa>lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 02-AUG-2006 10:35:
50

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Produc
tion
Start Date                31-JUL-2006 16:32:23
Uptime                    1 days 18 hr. 3 min. 30 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   C:\MATRIX10\listener.ora
Listener Log File         C:\oracle\ora92\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MX10TEST)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MX10TEST)(PORT=8080))(Presentation=H
TTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MX10TEST)(PORT=2100))(Presentation=F
TP)(Session=RAW))
Services Summary...
Service "MX10TESTXDB" has 1 instance(s).
  Instance "MX10TEST", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "mx10test" has 2 instance(s).
  Instance "MX10TEST", status UNKNOWN, has 1 handler(s) for this service...
  Instance "MX10TEST", status READY, has 1 handler(s) for this service...
Service "newstand" has 1 instance(s).
  Instance "newstand", status UNKNOWN, has 1 handler(s) for this service...
Service "standby" has 2 instance(s).
  Instance "NEWSTAND", status READY, has 1 handler(s) for this service...
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

here services newstand has one instance and status is unknown and service standby has 2 instance and status for standby is unknown ,
I did not understand this because I am able to login primary database(standby) then why status is unknown.

Thanks in advance

>>Instance "newstand", status UNKNOWN, has 1 handler(s) for this service...
>>Service "standby" has 2 instance(s).
 >> Instance "NEWSTAND", status READY, has 1 handler(s) for this service...

that is because oracle instance registers itself automatically with local listener (dynamic registration). you can possibly remove entries in listener.ora and instance will still be registered with listener.

Use automatic renaming of the files to solve the problems with the differences in the directories.

refer to this doc ( i think i already showed you before). It should give you all you want:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/create_ps.htm#58021

Thanks actonwang.
If I remove entry from listener.ora then I am not able to login at standby database using sql*plus
and
Is there any way can  I make status ready for Service "newstand" has 1 instance(s).
  Instance "newstand", status UNKNOWN, has 1 handler(s) for this service...

Thanks in advance

Thanks schwertner
I used db_file_name_convert parameter in my initnewstand.ora file
Thanks in advance

Thanks actonwang for providing me link again.
As per doc.

3.2.9 Enable Dead Connection Detection on the Standby System
Enable dead connection detection by setting the SQLNET.EXPIRE_TIME parameter to 2 in the SQLNET.ORA parameter file on the standby system. For example:

SQLNET.EXPIRE_TIME=2

Here I have primary and standby database at a same system and it says enable this on the Standby System
and I have only one sqlnet.ora file , can I use only that file or use dynamically set up using alter system?

Thanks in advance

>>If I remove entry from listener.ora then I am not able to login at standby database using sql*plus
    you don't need to remove it.

>>SQLNET.EXPIRE_TIME=2
     It should apply to both databases.  I can not think of a way to dynamically set it up for each db.
     

Thanks actonwang for your help and support .
Now I am trying to resolve this ora error--
ORA-00279: change 6333534 generated at 07/27/2006 10:17:22 needed for thread
20951
ORA-00289: suggestion : C:\ORACLE\ORADATA\NEWSTAND\LOG20951_0.ARC


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log 'C:\ORACLE\ORADATA\NEWSTAND\LOG20951_0.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF'

Thnaks in advance

what is your setting for  standby_archive_dest ? is it C:\ORACLE\ORADATA\NEWSTAND?

do you have C:\ORACLE\ORADATA\NEWSTAND\LOG20951_0.ARC?

Thanks actonwang.
Yes my setting for standby_archive_dest is it C:\ORACLE\ORADATA\NEWSTAND
but I am unable to locate LOG20951_0.ARC log in C:\ORACLE\ORADATA\NEWSTAND

Thanks in advance.

how do you transfer archived log files from primary to standby?

look at this for enable archiving to standby db:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/create_ps.htm#58021

Thanks actonwang.
I set these parameter in init.ora(primary) file

*.log_archive_dest_1='location=c:/oracle/oradata/standby mandatory'
*.log_archive_dest_2='SERVICE=newstand reopen=60'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_format=log%t_%s.arc
*.log_archive_start=TRUE

Thanks in advance

issue:

ALTER SYSTEM ARCHIVE LOG CURRENT;

to if archive file is copied.  refer to the doc for verification.

Thanks actonwang.
I got this message after issuing this--
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM ARCHIVE LOG CURRENT
*
ERROR at line 1:
ORA-01109: database not open;

issue it in primary database. make sure your primary is running ok.

Thanks actonwang.
at primary database when I issued this command

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

but when I issued this on both its different on standby and primary
Primary database

  1  SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2*  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#
SQL> /

 SEQUENCE# FIRST_TIME      NEXT_TIME
---------- --------------- ---------------
        24 21-JUL-06       21-JUL-06
        25 21-JUL-06       21-JUL-06
        26 21-JUL-06       22-JUL-06
        30 24-JUL-06       25-JUL-06
        31 25-JUL-06       25-JUL-06
        32 25-JUL-06       26-JUL-06
        33 26-JUL-06       27-JUL-06
        34 27-JUL-06       28-JUL-06
        35 28-JUL-06       30-JUL-06
        36 30-JUL-06       31-JUL-06
        37 31-JUL-06       31-JUL-06

 SEQUENCE# FIRST_TIME      NEXT_TIME
---------- --------------- ---------------
        38 31-JUL-06       02-AUG-06
        39 02-AUG-06       02-AUG-06

13 rows selected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:/oracle/oradata/standby
Oldest online log sequence     38
Next log sequence to archive   40
Current log sequence           40

Standby database;
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2   FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

no rows selected
SQL>  archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:/oracle/oradata/NEWSTAND/
Oldest online log sequence     32
Next log sequence to archive   34
Current log sequence           34

Thanks in advance.

since both are in the same server, set one of your archive dest directly on  c:/oracle/oradata/NEWSTAND/

and if I ltried to locate it in newstand dir
it is there but it says UltimateZip file
what does that mean?
Thanks in advance

and now this command again says this
primary database
  1  SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2*  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#
SQL> /

 SEQUENCE# FIRST_TIME      NEXT_TIME
---------- --------------- ---------------
        33 26-JUL-06       27-JUL-06
        33 26-JUL-06       27-JUL-06
        34 27-JUL-06       28-JUL-06
        34 27-JUL-06       28-JUL-06
        35 28-JUL-06       30-JUL-06
        35 28-JUL-06       30-JUL-06
        36 30-JUL-06       31-JUL-06
        37 31-JUL-06       31-JUL-06
        37 31-JUL-06       31-JUL-06
        38 31-JUL-06       02-AUG-06
        39 02-AUG-06       02-AUG-06

 SEQUENCE# FIRST_TIME      NEXT_TIME
---------- --------------- ---------------
        39 02-AUG-06       02-AUG-06
        40 02-AUG-06       02-AUG-06
        40 02-AUG-06       02-AUG-06

14 rows selected.

Standby database;

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2   FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#
  3  /

 SEQUENCE# FIRST_TIME      NEXT_TIME
---------- --------------- ---------------
        33 26-JUL-06       27-JUL-06
        34 27-JUL-06       28-JUL-06
        35 28-JUL-06       30-JUL-06
        37 31-JUL-06       31-JUL-06
        39 02-AUG-06       02-AUG-06
        40 02-AUG-06       02-AUG-06

6 rows selected.

Thanks in advance

>> UltimateZip file
     it might be the format of archive file.

     it seems working. The archive files now are in standby.

Thanks actonwang.
but still I am unable to open standby database ,only able to mount it.
Thanks in advance.

standby database is meant to be in mount status for continuous recovery from primary database. You are NOT supposed to open it.

Thanks actonwang.
But if I want to open it read only for reporting purpose?
Thanks in advance.

then you need "logical standby database" instead of "physical standby database".

Thanks actonwang.

That means I created physical standby database successfull?
but if I want to see datafile using V$datafile query not able to see standby database file why?
here is output
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name from v$datafile;

NAME
------------------------------------------------------
C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF
C:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF
C:\ORACLE\ORADATA\STANDBY\CWMLITE01.DBF
C:\ORACLE\ORADATA\STANDBY\DRSYS01.DBF
C:\ORACLE\ORADATA\STANDBY\EXAMPLE01.DBF
C:\ORACLE\ORADATA\STANDBY\INDX01.DBF
C:\ORACLE\ORADATA\STANDBY\ODM01.DBF
C:\ORACLE\ORADATA\STANDBY\TOOLS01.DBF
C:\ORACLE\ORADATA\STANDBY\USERS01.DBF
C:\ORACLE\ORADATA\STANDBY\XDB01.DBF
C:\ORACLE\ORADATA\STANDBY\OEM_REPOSITORY.DBF

11 rows selected.

SQL> select * from v$controlfile;

STATUS
---------------------
NAME
------------------------------------------------------

C:\ORACLE\ORADATA\NEWSTAND\NEWCONTROL01.CTL


C:\ORACLE\ORADATA\NEWSTAND\NEWCONTROL02.CTL


C:\ORACLE\ORADATA\NEWSTAND\NEWCONTROL03.CTL


SQL> select member from v$logfile;

MEMBER
------------------------------------------------------
C:\ORACLE\ORADATA\STANDBY\REDO03.LOG
C:\ORACLE\ORADATA\STANDBY\REDO02.LOG
C:\ORACLE\ORADATA\STANDBY\REDO01.LOG


here controlfile I can see for standby(newstand) database, but data file and redologs are still primary(standby) database.

Thanks in advance.

not sure what happened. revist steps to make sure you create your control file correctly.

Thanks actonwang.
I created control file after hot backup for all datafile, copy of online and archived redo logs and backup of controlfile to trace, then I issued this command to create standby controlfile;
alter database create standby controlfile as ' C:\ORACLE\ORADATA\NEWSTAND\NEWCONTROL01.CTL';
Thanks in advance.

Thanks actonwang.
here I tried to rename all file manually .here is output
SQL> select name from v$datafile;

NAME
----------------------------------------------------
C:\ORACLE\ORADATA\NEWSTAND\SYSTEM01.DBF
C:\ORACLE\ORADATA\NEWSTAND\UNDOTBS01.DBF
C:\ORACLE\ORADATA\NEWSTAND\CWMLITE01.DBF
C:\ORACLE\ORADATA\NEWSTAND\DRSYS01.DBF
C:\ORACLE\ORADATA\NEWSTAND\EXAMPLE01.DBF
C:\ORACLE\ORADATA\NEWSTAND\INDX01.DBF
C:\ORACLE\ORADATA\NEWSTAND\ODM01.DBF
C:\ORACLE\ORADATA\NEWSTAND\TOOLS01.DBF
C:\ORACLE\ORADATA\NEWSTAND\USERS01.DBF
C:\ORACLE\ORADATA\NEWSTAND\XDB01.DBF
C:\ORACLE\ORADATA\NEWSTAND\OEM_REPOSITORY.DBF

11 rows selected.


but when I tried to issue this command


SQL> RECOVER STANDBY DATABASE;
ORA-00279: change 6327698 generated at 07/27/2006 09:39:35 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\NEWSTAND\LOG1_33.ARC
ORA-00280: change 6327698 for thread 1 is in sequence #33


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 6332002 generated at 07/27/2006 10:07:13 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\NEWSTAND\LOG1_34.ARC
ORA-00280: change 6332002 for thread 1 is in sequence #34
ORA-00278: log file 'C:\ORACLE\ORADATA\NEWSTAND\LOG1_33.ARC' no longer needed
for this recovery


ORA-00279: change 6610681 generated at 07/28/2006 20:12:27 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\NEWSTAND\LOG1_35.ARC
ORA-00280: change 6610681 for thread 1 is in sequence #35
ORA-00278: log file 'C:\ORACLE\ORADATA\NEWSTAND\LOG1_34.ARC' no longer needed
for this recovery


ORA-00279: change 6891113 generated at 07/30/2006 02:27:34 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORADATA\NEWSTAND\LOG1_36.ARC
ORA-00280: change 6891113 for thread 1 is in sequence #36
ORA-00278: log file 'C:\ORACLE\ORADATA\NEWSTAND\LOG1_35.ARC' no longer needed
for this recovery


ORA-00308: cannot open archived log 'C:\ORACLE\ORADATA\NEWSTAND\LOG1_36.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified

Thanks in advance.

Thanks actonwang for all your support and explanation regarging standby database;
After renaming all datafile i am able to open standby database read only mode;
Thank you very much.
I don't know about the procedure for  points that how you can get it?

Thanks in advance

not sure how you did . db_file_name_convert should take care of it automatically. But it is great that you make it work :)

Thanks actonwang
I used db_file_name_convert parameter,but some how it didn't rename all datafile ,so I performed this---
SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database rename file 'C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF' to
  2  'C:\ORACLE\ORADATA\NEWSTAND\SYSTEM01.DBF';

Database altered.

And I renamed all datafile manually and then issued --
recover standby database;
alter database open read only;
Now I am able to open standby database read only

Now I am wondering if I try to configure another standby database on remote server, do I need to install oracle on that server or just create dir structure there?
Thanks in advance.

you have to create a standby database and instance as you do in the local. You'd have a oracle installed there because a listener is also required.

Thanks actonwang.
I appreciate all your help and support.

no problem. you basically figured out all the stuff by yourself :)