check standby database
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: check standby database

  1. #1
    Join Date
    Jan 2007
    Posts
    231

    How to check standby

    Hello all,
    We have created a standby.How to check whether it is working or not.
    can I use the command alter database open read only and connect as user
    and check all rows are present in standby as well as in primary database.
    Last edited by ams-jamali; 03-27-2007 at 05:05 AM.

  2. #2
    Join Date
    Jan 2007
    Posts
    231
    hi,
    If standby can be opened in read only mode.Here is command i have given and the error it showed.

    sql>alter database open read only;
    ORA-16004: backup database requires recovery
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: '/mnt/db/oracle/oradata/ISPRO20/system01.dbf'

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    you havent create dyour standby correctly or you havent applied all the necessary archive logs

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    It looks like datafile '/mnt/db/oracle/oradata/ISPRO20/system01.dbf' is younger than the other files of your database, in plain english, this file is "in the future".
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jan 2007
    Posts
    231
    I have taken fresh hot backup from production(.dbf,redolog files to standby) created controlfile and performed performed recoery..etc.(the same task what i had done before).
    It shows..

    SQL> alter database open read only;
    alter database open read only
    *
    ERROR at line 1:
    ORA-16004: backup database requires recovery
    ORA-01195: online backup of file 1 needs more recovery to be consistent
    ORA-01110: data file 1: '/mnt/db/oracle/oradata/ISPRO20/system01.dbf'

    this time it shows different error.then I performed following task..

    SQL> alter database recover standby datafile 1 until consistent with controlfile;
    alter database recover standby datafile 1 until consistent with controlfile
    *
    ERROR at line 1:
    ORA-00279: change 942879428 generated at 03/28/2007 00:02:10 needed for thread
    1
    ORA-00289: suggestion : /mnt/backup/arch/ISPRO20/-1063755125_1_14501.arc
    ORA-00280: change 942879428 for thread 1 is in sequence #14501

    It shows sequence #14501.But we have that sequence #14501 in that path.

    So pls help how to proceed further ...

  6. #6
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184
    Use the following in the mount stage of your standby database:

    SQL> recover standby database;

    write "auto" when it asks for archive log files. (Assuming you check the archives transferred from primary to standby locations)

    SQL> alter database open read only;

    Post the output.

  7. #7
    Join Date
    Jan 2007
    Posts
    231
    hi paresh..

    I had simulated the same error in test box and succeeded in that,but frustation was what went wrong i can't find.

    What i did was..
    switched log files on prod, and then worked in standby which repeats the same error.I shut down both dband went out came back open standby alone and made usual recovery that i had done previously.
    SQL>alter database recover managed standby database disconnect from session;
    SQL>recover managed standby database cancel;
    SQL>alter database open read only;
    It holds for a while and then opened in read only mode(What a wonder..?)
    then i shutdowned standby;
    Then went to prod kept in mount state,then standby kept in mount,then opened prod and it SYNC with standby.
    !!!!Working properly.

    I reported to my boss,and asked for down time for real PRODDB.He too accepted next weekend.
    I have made up my mind to do the same process.
    I will send you next week the result.

    If u have any idea on this regard kindly inform me.What actually went wrong.

  8. #8
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184
    Quote Originally Posted by ams-jamali
    hi paresh..

    I had simulated the same error in test box and succeeded in that,but frustation was what went wrong i can't find.

    What i did was..
    switched log files on prod, and then worked in standby which repeats the same error.I shut down both dband went out came back open standby alone and made usual recovery that i had done previously.
    SQL>alter database recover managed standby database disconnect from session;
    SQL>recover managed standby database cancel;
    SQL>alter database open read only;
    It holds for a while and then opened in read only mode(What a wonder..?)
    then i shutdowned standby;
    Then went to prod kept in mount state,then standby kept in mount,then opened prod and it SYNC with standby.
    !!!!Working properly.

    I reported to my boss,and asked for down time for real PRODDB.He too accepted next weekend.
    I have made up my mind to do the same process.
    I will send you next week the result.

    If u have any idea on this regard kindly inform me.What actually went wrong.

    You must not have applied the last archive log shipped.

  9. #9
    Join Date
    Jan 2007
    Posts
    231
    No.,I have checked latest archive that is generated in prod and checked in standby too,the archive log number is same in both.
    (Another thing i made is
    alter system switch log file in prod and checked in standby whether it is sync with prod. )

  10. #10
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184
    Quote Originally Posted by ams-jamali
    No.,I have checked latest archive that is generated in prod and checked in standby too,the archive log number is same in both.
    (Another thing i made is
    alter system switch log file in prod and checked in standby whether it is sync with prod. )

    How did you check?

    Please provide output of the following query:

    On Primary:
    SQL> select max(sequence#) from v$log_history;

    On Standby:
    SQL> select max(sequence#) from v$log_history;

    Should be same.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width