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

Thread: standby database query

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    standby database query

    i received the following error when i tried to open the standby database in read mode after creating it.

    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: 'D:\STANDBY\ORADATA\ORACL\SYSTEM01.DBF'


    I repeated the steps for creating standby database thrice but got the same error everytime.

    Before opening the database in read mode, i also noticed that the redo logs
    have not been archived at the location of standby database specified by
    log_archive_dest_2='service=stby' on primary database and standy_archive_dest on standby database.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    then you need to make sure that your archive logs are being shipped

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Thanks i have corrected the error. The problem was reallly with the shipping of archive logs.

    Now, facing a new problem:
    On my primary database, i executed the query:
    select max(sequence#) from v$log_history;
    output is : 20

    On my standby database, i executed the query:
    select max(sequence#) from v$log_history;
    output is : 15

    So, i started log apply services using:
    recover managed standby database disconnect from session;
    Again on my standby database, i executed the query:
    select max(sequence#) from v$log_history;
    output is : 15

    Then i issued the command:
    recover managed standby database cancel;
    error: managed recovery not active.

    So, i simply opened the database in read mode and it opened (i wondered).
    It means that the logs haven't been applied as i found so because the changes on primary database did not reflect in the standby database(opened in read only mode)

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    moreover, when the database was in managed recovery mode, I issued the following command:
    select process,status from v$managed_standby;

    It did not show any MRPn process.

    How can i overcome it?

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Any information in the standby db alert log? any trace files are generating?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  6. #6
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    contents of standby alert log file are:

    Wed Nov 19 11:40:23 2008
    Starting ORACLE instance (normal)
    LICENSE_MAX_SESSION = 0
    LICENSE_SESSIONS_WARNING = 0
    SCN scheme 2
    Using log_archive_dest parameter default value
    LICENSE_MAX_USERS = 0
    SYS auditing is disabled
    Starting up ORACLE RDBMS Version: 9.2.0.1.0.
    System parameters with non-default values:
    processes = 150
    timed_statistics = TRUE
    shared_pool_size = 50331648
    large_pool_size = 8388608
    java_pool_size = 33554432
    lock_name_space = stby
    control_files = D:\standby\oradata\oracl\control_sb01.ctl
    db_file_name_convert = e:\oracle\oradata\oracl, d:\standby\oradata\oracl
    log_file_name_convert = e:\oracle\oradata\oracl, d:\standby\oradata\oracl
    db_block_size = 8192
    db_cache_size = 25165824
    compatible = 9.2.0.0.0
    remote_archive_enable = true
    log_archive_start = TRUE
    log_archive_dest_1 = location=d:\standby\archive mandatory reopen=300
    standby_archive_dest = D:\standby\archive_primary
    db_file_multiblock_read_count= 16
    standby_file_management = auto
    fast_start_mttr_target = 300
    control_file_record_keep_time= 7
    undo_management = AUTO
    undo_tablespace = UNDOTBS1
    undo_retention = 10800
    remote_login_passwordfile= NONE
    db_domain =
    instance_name = stby
    dispatchers = (PROTOCOL=TCP) (SERVICE=oraclXDB)
    job_queue_processes = 10
    hash_join_enabled = TRUE
    background_dump_dest = d:\standby\admin\oracl\bdump
    user_dump_dest = d:\standby\admin\oracl\udump
    core_dump_dest = d:\standby\admin\oracl\cdump
    sort_area_size = 524288
    db_name = oracl
    open_cursors = 300
    star_transformation_enabled= FALSE
    query_rewrite_enabled = FALSE
    pga_aggregate_target = 25165824
    aq_tm_processes = 1
    PMON started with pid=2
    DBW0 started with pid=3
    LGWR started with pid=4
    CKPT started with pid=5
    SMON started with pid=6
    RECO started with pid=7
    CJQ0 started with pid=8
    QMN0 started with pid=9
    Wed Nov 19 11:40:27 2008
    starting up 1 shared server(s) ...
    starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
    ARCH: STARTING ARCH PROCESSES
    ARC0 started with pid=12
    ARC0: Archival started
    ARC1 started with pid=13
    ARC1: Archival started
    Wed Nov 19 11:40:28 2008
    ARCH: STARTING ARCH PROCESSES COMPLETE
    Wed Nov 19 11:40:28 2008
    ARC0: Thread not mounted
    Wed Nov 19 11:40:28 2008
    ARC1: Thread not mounted
    Wed Nov 19 11:40:45 2008
    alter database mount standby database
    Wed Nov 19 11:40:49 2008
    Successful mount of redo thread 1, with mount id 1716663741.
    Wed Nov 19 11:40:49 2008
    Standby Database mounted.
    Completed: alter database mount standby database
    Wed Nov 19 11:40:52 2008
    ALTER DATABASE RECOVER managed standby database disconnect from session
    Attempt to start background Managed Standby Recovery process
    MRP0 started with pid=15
    MRP0: Background Managed Standby Recovery process started
    Starting datafile 1 recovery in thread 1 sequence 15
    Datafile 1: 'D:\STANDBY\ORADATA\ORACL\SYSTEM01.DBF'
    Starting datafile 2 recovery in thread 1 sequence 15
    Datafile 2: 'D:\STANDBY\ORADATA\ORACL\UNDOTBS01.DBF'
    Starting datafile 3 recovery in thread 1 sequence 15
    Datafile 3: 'D:\STANDBY\ORADATA\ORACL\CWMLITE01.DBF'
    Starting datafile 4 recovery in thread 1 sequence 15
    Datafile 4: 'D:\STANDBY\ORADATA\ORACL\DRSYS01.DBF'
    Starting datafile 5 recovery in thread 1 sequence 15
    Datafile 5: 'D:\STANDBY\ORADATA\ORACL\EXAMPLE01.DBF'
    Starting datafile 6 recovery in thread 1 sequence 15
    Datafile 6: 'D:\STANDBY\ORADATA\ORACL\INDX01.DBF'
    Starting datafile 7 recovery in thread 1 sequence 15
    Datafile 7: 'D:\STANDBY\ORADATA\ORACL\ODM01.DBF'
    Starting datafile 8 recovery in thread 1 sequence 15
    Datafile 8: 'D:\STANDBY\ORADATA\ORACL\TOOLS01.DBF'
    Starting datafile 9 recovery in thread 1 sequence 15
    Datafile 9: 'D:\STANDBY\ORADATA\ORACL\USERS01.DBF'
    Starting datafile 10 recovery in thread 1 sequence 15
    Datafile 10: 'D:\STANDBY\ORADATA\ORACL\XDB01.DBF'
    Media Recovery Log D:\STANDBY\ARCHIVE_PRIMARY\ARC00015.001
    Incomplete recovery applied all redo ever generated.
    Recovery completed through change 1023250
    MRP0: Media Recovery Complete
    MRP0: Background Media Recovery process shutdown
    Wed Nov 19 11:40:59 2008
    Completed: ALTER DATABASE RECOVER managed standby database d
    Wed Nov 19 11:41:09 2008
    ALTER DATABASE RECOVER managed standby database cancel
    ORA-16136 signalled during: ALTER DATABASE RECOVER managed standby database c...
    Wed Nov 19 11:41:30 2008
    alter database open read only
    Wed Nov 19 11:41:30 2008
    SMON: enabling cache recovery
    Wed Nov 19 11:41:31 2008
    Database Characterset is WE8MSWIN1252
    replication_dependency_tracking turned off (no async multimaster replication found)
    Completed: alter database open read only
    Wed Nov 19 11:42:01 2008
    ***Warning - Executing transaction without active Undo Tablespace
    Wed Nov 19 11:42:42 2008
    Restarting dead background process QMN0
    QMN0 started with pid=9

  7. #7
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    pls help out

  8. #8
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Please run the below commands in your standby DB and post the result.

    select max(sequence#) from v$archived_log;

    select max(sequence#) from v$archived_log where applied = 'YES';

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  9. #9
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    on primary database:
    SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

    THREAD# SEQUENCE# APP
    ---------- ---------- ---
    1 1 NO
    1 2 NO
    1 3 NO
    1 4 NO
    1 5 NO
    1 6 NO
    1 7 NO
    1 8 NO
    1 9 NO
    1 10 NO
    1 11 NO

    THREAD# SEQUENCE# APP
    ---------- ---------- ---
    1 12 NO
    1 13 NO
    1 13 NO
    1 11 NO
    1 10 NO
    1 12 NO
    1 14 NO
    1 14 YES
    1 15 NO
    1 15 NO
    1 16 NO

    THREAD# SEQUENCE# APP
    ---------- ---------- ---
    1 16 NO
    1 17 NO
    1 17 NO
    1 18 NO
    1 18 NO
    1 19 NO
    1 19 NO
    1 20 NO
    1 20 NO
    1 21 NO
    1 21 NO

    33 rows selected.


    on standby database:
    SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

    THREAD# SEQUENCE# APP
    ---------- ---------- ---
    1 14 YES
    1 15 NO
    1 16 NO
    1 17 NO
    1 18 NO
    1 19 NO
    1 20 NO
    1 21 NO

    8 rows selected.

  10. #10
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    start redo apply and monitor the alert log on standby.

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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