This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jan 23, 2013 6:38 AM by 802461 RSS

Multiple instance at same server with same ORACLE_HOME and Same SID

802461 Newbie
Currently Being Moderated
Hello gurus, for cost reduction my company looks to reduce the standby servers and consolidade standby databases on 2 or 3 big standby servers to reduce oracle licenses.
question is i have about 30 instances with same SID how to configure it to run like 10 instances with same SID, ORACLE_HOME in the same machine? is it possible?

will be running oracle 11r2 on unix hpux

br

TI
  • 1. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    mseberg Guru
    Currently Being Moderated
    Hello and welcome to the forum.

    Yes. So the Oracle home is the same, but each standby has its own SID.

    The simple thing is to have something like

    /u01/app/oracle/oradata

    And then each standby a folder under it. In any event have it mirror the primary database folder structure.

    My shop has 12 running on one large Dell server.

    to switch between : ( I'm sure you know that )

    export ORACLE_SID=

    Best Regards

    mseberg
  • 2. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    802461 Newbie
    Currently Being Moderated
    thanks for reply

    yes oracle_home is the same, nowadays as i have 1 server for each standby so my SID is the same all around. for new architecture i´ll need to put different SID for each standby right ?
    so for now on master i know i´ll need for sure add tns entries for standby with new SID i decide to use. my doubt is at standby side configuration parameters.
    i have to use some parameter to tell oracle that before path was /oradata/mydb and now is /mill1/oradata/mydb ??? probably but never did.
    also production db is the same name all around so lets use mydb as sid how to translate it to mydbmill1 at standby database?

    thanks a lot for your help.

    TI
  • 3. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    mseberg Guru
    Currently Being Moderated
    Right. Each database will have it own SID. With multiples the tnsnames.ora and listener.ora all have entries for each Standby.

    If the folder structure is different than primary then the Parameters DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are used to convert to the new locations.
    Both of these take input in two's, the before location and the after location.

    Example :
    DB_FILE_NAME_CONVERT=('/u01/app/oracle/oradata/RECOVER2', '/u01/app/oracle/oradata/RECLONE','/u01/oradata/RECOVER2','/u01/oradata/RECLONE')
    LOG_FILE_NAME_CONVERT=('/u01/app/oracle/flash_recovery_area/RECOVER2', '/u01/app/oracle/flash_recovery_area/RECLONE')
    The Standby SID for each primary can be whatever you need. The Parameter DB_NAME would be the same on both, but DB_UNIQUE_NAME would be different.

    Does that make sense?

    So if I have two database PRIMARY and STANDBY, Standby will have its own password file, its own INIT file, its own directory structure ( but it could also be the same ).



    Best Regards

    mseberg
  • 4. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    802461 Newbie
    Currently Being Moderated
    Thanks a lot to reply.

    yes make sense, as i said i have nowadays 1 stdby per server so is exactly the same as primary(name,sid,paths) , now i´ll consolidade then, so paths must be changed for sure.

    so on master side, nothing changes, just on standby side i need to use those new parameters?
    on oracle 11 i have to use the same passwd file for primary and standby so in this case all primary and all standby involved will need to have a copy of that passwd file right??

    after this am i finished with the server implementation ???? do i need to use fal_client and fal_server parameters? today they´re empty on my configuration.

    thanks a lot

    br
    Ti
  • 5. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    mseberg Guru
    Currently Being Moderated
    Hello again
    so on master side, nothing changes, just on standby side i need to use those new parameters?
    Yes.
    on oracle 11 i have to use the same passwd file for primary and standby so in this case all primary and all standby involved will need to have a copy of that passwd file right??
    You copy and rename the password file in Oracle 11


     do i need to use fal_client and fal_server parameters? 
    Just FAL_SERVER, FAL_CLIENT is not needed in Oracle 11.

    Best Regards

    mseberg
  • 6. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    802461 Newbie
    Currently Being Moderated
    Hello i reopen this question as i got one problem at my environment.
    what happens was, all standby was running perfect as configurations done using parameters to convert file names and log names.
    then, the primary database moved to the same node as standby was running. no problems as they´re in different path.
    what happens was with primary db running in the same server, i was creating another standby for another database in this server where we consolidade standby.
    when i put the new standby in recover mode it tried to use the rlog in the primary database and primary database crashed. i have to switch standby to primary.

    so for some reason the parameter to convert datafile name and log names doesn´t work for redo logs.

    i created the standby redo logs before try to put the standby in recover mode, it created the files okay. but when put in recover it tried to use the files in the original path, and in this case was a production db running.

    any idea why this happens there? should i rename all redologs then create standby redo logs in the new folder paths before i put in recover mode ?

    thanks for your help
  • 8. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    mseberg Guru
    Currently Being Moderated
    Can you post the result of :

    show PARAMETER DB_FILE_NAME_CONVERT
    show PARAMETER LOG_FILE_NAME_CONVERT

    From the database(s) where you are having an issue?

    Without this information is very hard to guess what the issue is.

    Best Regards

    mseberg
  • 9. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    802461 Newbie
    Currently Being Moderated
    hello thanks again for help.
    parameters used.
    *.db_file_name_convert='/disk/prod/2/oradata/prod','/disk/prod/DR/2/oradata/prodtur','/disk/prod/3/oradata/prod','/disk/prod/DR/3/oradata/prodtur'
    *.log_file_name_convert='/disk/prod/5/oradata/prod/DB_ARCHIVE/redo_arch','/disk/prod/DR/5/oradata/prodtur/DB_ARCHIVE/redo_arch'


    the problem was when we moved a prod db to the same node as DR and the path of prod was avaiable, and i try to start one new standby by put it in recover mode, as i said , it tried to use the rlogs on prod path, and it results to primary db error like "rlog doesn´t belong to this database' and primary db faild.

    i think maybe this parameters are not enouth for rlog files or i miss something else on configuration.


    thanks a lot.

    Edited by: 799458 on Jan 3, 2013 8:01 AM
  • 10. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    mseberg Guru
    Currently Being Moderated
    Checking....

    OK, so if I understand correctly its just the archive logs you are having a conflict with?

    If yes then log_archive_dest_2 could be the problem child.

    If you are using an SPFILE I would create a new PFILE from it and print it out and review for the conflict parameter.

    It almost has to be a parameter on the Standby side.

    DB_RECOVERY_FILE_DESTis another I would double check.

    Best Regards

    mseberg

    Edited by: mseberg on Jan 3, 2013 11:09 AM

    Edited by: mseberg on Jan 3, 2013 11:16 AM
  • 11. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    802461 Newbie
    Currently Being Moderated
    thanks to reply

    so is not the archivelogs the problem is the REDO LOG GROUPS.

    after create the standby control file normally we create on standby database the standby log files.

    even after this standby files created like : alter database add standby logfile ' /path/srl10'
    i got the error when i tried:' recover managed standby database disconnect'

    after this command it tried to use the RLOG files from production path, not from the converted path.

    at converted path i have the rlog files copied from prod when i copy all files to create the standby and also the srlxx files created at standby to be used as standby log files but anyway it goes to production path and killed the prod database wich crash as i told before.
    i´m thinking that maybe i should use the convert files parameter but should rename the rlogs on standby anyway? or rename all files?

    please help.
    thanks a lot
  • 12. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    mseberg Guru
    Currently Being Moderated
    Will try. Dealing with System crash at my site right now.



    Sent email to my friend CKPT asking him to look at it.

    Later

    OK. So it should be LOG_FILE_NAME_CONVERT. The document says :

    "When the standby database is updated, this parameter converts the log file name on the primary database to the log file name on the standby database. The file must exist on the standby database and must be writable or the recovery process will halt with an error."

    So I assume this is what's happening. We need to confirm probably with the alert log on the Standby the exact error. Can you post this?

    Not my best day, many fires to deal with but I will forward as best I can.

    Worth a look

    http://troubleshootingappsdba.blogspot.com/2009/04/renamingdropping-and-recreating-online.html

    Best Regards

    mseberg

    Edited by: mseberg on Jan 3, 2013 5:45 PM
  • 13. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    CKPT Guru
    Currently Being Moderated
    I reviewed all the posts of threads, below is my findings. if am missing somewhere please let me know.
    when i put the new standby in recover mode it tried to use the rlog in the primary database and primary database crashed. i have to switch standby to primary. so for some reason the parameter to convert datafile name and log names doesn´t work for redo logs.
    Can you share the log when primary database crashed? If you put standby in recovery mode why it will use the redo log of primary?
    have you configured properly with the LOG_FILE_NAME_CONVERT?
    i created the standby redo logs before try to put the standby in recover mode, it created the files okay. but when put in recover it tried to use the files in the original path, and in this case was a production db running.
    If you have the same location of redo log files as primary and standby, After the duplication the standby redo log files created in same location until unless you modify LOG_FILE_NAME_CONVERT
    Whnever you moved your primary to standby server. Then you must change the location of standby redo logs on standby because you cannot share the standby redo logs of primary with standby.
    *.db_file_name_convert='/disk/prod/2/oradata/prod','/disk/prod/DR/2/oradata/prodtur','/disk/prod/3/oradata/prod','/disk/prod/DR/3/oradata/prodtur'
    *.log_file_name_convert='/disk/prod/5/oradata/prod/DB_ARCHIVE/redo_arch','/disk/prod/DR/5/oradata/prodtur/DB_ARCHIVE/redo_arch'
    Even though you change the values LOG_FILE_NAME_CONVERT, they cannot be relocated automotically if they already created. You must drop and create with new location other than the primary location.
  • 14. Re: Multiple instance at same server with same ORACLE_HOME and Same SID
    802461 Newbie
    Currently Being Moderated
    hello thanks to reply.

    the alert error when db crash is.

    Fri Dec 21 15:12:22 2012
    Archived Log entry 120173 added for thread 1 sequence 92004 ID 0x34ba9ea2 dest 1:
    Fri Dec 21 15:18:57 2012
    Errors in file /oracle/app/oracle_prod/rdbms/log/diag/rdbms/prod/prod/trace/prod_lgwr_21802.trc:
    ORA-00305: log 14 of thread 1 inconsistent; belongs to another database
    ORA-00312: online log 14 thread 1: '/disk/prod/2/oradata/prod/rlog14'
    ORA-00305: log 14 of thread 1 inconsistent; belongs to another database
    ORA-00312: online log 14 thread 1: '/disk/prod/3/oradata/prod/rlog14'
    Errors in file /oracle/app/oracle_prod/rdbms/log/diag/rdbms/prod/prod/trace/prod_lgwr_21802.trc:
    ORA-00305: log 14 of thread 1 inconsistent; belongs to another database
    ORA-00312: online log 14 thread 1: '/disk/prod/2/oradata/prod/rlog14'
    ORA-00305: log 14 of thread 1 inconsistent; belongs to another database
    ORA-00312: online log 14 thread 1: '/disk/prod/3/oradata/prod/rlog14'
    LGWR (ospid: 21802): terminating the instance due to error 305
    Fri Dec 21 15:18:58 2012
    opiodr aborting process unknown ospid (8015) as a result of ORA-1092
    Fri Dec 21 15:18:58 2012
    opiodr aborting process unknown ospid (8092) as a result of ORA-1092
    Fri Dec 21 15:18:58 2012
    ORA-1092 : opitsk aborting process]
    and then all process went down and db went down.

    the parameter you asked was like this: *.log_file_name_convert='/disk/prod/5/oradata/prod/DB_ARCHIVE/redo_arch','/disk/prod/DR/5/oradata/prodtur/DB_ARCHIVE/redo_arch'
    Even though you change the values LOG_FILE_NAME_CONVERT, they cannot be relocated automotically if they already created. You must drop and create with new location other than the primary location.
    so RLOGs created in the primary needs to be renamed or recreated on standby controlfile anyway even with the parameters above? only RLOG or any other files? so far all datafiles are okay as the standby gets aligned.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points