This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: Jan 17, 2013 4:36 AM by CKPT RSS

not able to get data of primary in standby database (configured dataguard)

974427 Newbie
Currently Being Moderated
Hi all, I configured dataguard in my local system, i ve a few qsns (as i am new, ve patience)
1) scn differs wrt primary in standby (i checked, 1day difference), how to make scn same?

2)i created a table in primary, its not refelecting in standby, (below i ve pasted alertlog entries)
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:\oracle11g\app\administrator\diag\rdbms\stand\stand\trace\stand_dbw0_6916.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\SYSAUX01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:\oracle11g\app\administrator\diag\rdbms\stand\stand\trace\stand_dbw0_6916.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\UNDOTBS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:\oracle11g\app\administrator\diag\rdbms\stand\stand\trace\stand_dbw0_6916.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\USERS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:\oracle11g\app\administrator\diag\rdbms\stand\stand\trace\stand_dbw0_6916.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\EXAMPLE01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:\oracle11g\app\administrator\diag\rdbms\stand\stand\trace\stand_dbw0_6916.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\STREAM_TBS.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:\oracle11g\app\administrator\diag\rdbms\stand\stand\trace\stand_dbw0_6916.trc:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: 'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\DATA01.DBF'
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-16004 signalled during: alter database open read only...

3)wen i try to open standby database in read only mode gives below error..how do i solve it?
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\SYSTEM01.DBF'
  • 1. Re: not able to get data of primary in standby database (configured dataguard)
    KuljeetPalSingh Guru
    Currently Being Moderated
    ORA-01110: data file 2: 'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\SYSAUX01.DBF'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.
    As Database file are not available on "'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\" location.
    try to recreate the standby
  • 2. Re: not able to get data of primary in standby database (configured dataguard)
    CKPT Guru
    Currently Being Moderated
    971424 wrote:
    Hi all, I configured dataguard in my local system, i ve a few qsns (as i am new, ve patience)
    1) scn differs wrt primary in standby (i checked, 1day difference), how to make scn same?
    Please do not use IM language here, instead of "qsns" use questions.
    SCN can be differ in standby and it can be behind to primary based on the backup you have restored. Of course by performing recovery you can synchronize standby with primary database. You can use incremental backups for standby using link http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/

    2)i created a table in primary, its not refelecting in standby, (below i ve pasted alertlog entries)
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 2) The system cannot find the file specified.
    Errors in file d:\oracle11g\app\administrator\diag\rdbms\stand\stand\trace\stand_dbw0_6916.trc:
    ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
    ORA-01110: data file 2: 'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\SYSAUX01.DBF'
    ORA-27041: unable to open file
    First check whether those files are exist in that location or not and if file names are different other than primary then rename file locations. Also check the permissions on the folder/files.
  • 3. Re: not able to get data of primary in standby database (configured dataguard)
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Apparently, the standby that you have configured doesn't have datafiles 2 to 7 !

    How did you build the standby ? How did you confirm that it did get built correctly ?


    Hemant K Chitale
  • 4. Re: not able to get data of primary in standby database (configured dataguard)
    974427 Newbie
    Currently Being Moderated
    thanx for the information, placed datafiles in the location, now i am getting these message in alertlog..
    Successful mount of redo thread 1, with mount id 216728321
    Physical Standby Database mounted.
    Lost write protection disabled
    Completed: ALTER DATABASE MOUNT
    ARC4: Thread not mounted
    ARC3: Thread not mounted
    ARC5: Thread not mounted
    ARC7: Thread not mounted
    ARC6: Thread not mounted
    ARCo: Thread not mounted
    ARCk: Thread not mounted
    ARCp: Thread not mounted
    ARCc: Thread not mounted
    ARCq: Thread not mounted
    ARCd: Thread not mounted
    ARCt: Thread not mounted
    ARCn: Thread not mounted
    ARCb: Thread not mounted
    ARCe: Thread not mounted
    ARCh: Thread not mounted
    Wed Jan 16 15:17:26 2013
    ARCj: Thread not mounted
    Wed Jan 16 15:17:26 2013
    ARCl: Thread not mounted
    Wed Jan 16 15:17:26 2013
    ARCi: Thread not mounted
    Wed Jan 16 15:17:26 2013
    ARCf: Thread not mounted
    Wed Jan 16 15:17:26 2013
    ARCr: Thread not mounted
    Wed Jan 16 15:17:26 2013
    ARCs: Thread not mounted
    Wed Jan 16 15:17:26 2013
    ARCa: Thread not mounted
    Wed Jan 16 15:17:26 2013
    ARCg: Thread not mounted
    Wed Jan 16 15:17:26 2013
    ARC9: Thread not mounted
    Wed Jan 16 15:17:26 2013
    ARCm: Thread not mounted

    a very new kind of error for me...
  • 5. Re: not able to get data of primary in standby database (configured dataguard)
    CKPT Guru
    Currently Being Moderated
    971424 wrote:
    thanx for the information, placed datafiles in the location, now i am getting these message in alertlog..
    Successful mount of redo thread 1, with mount id 216728321
    Physical Standby Database mounted.
    Lost write protection disabled
    Completed: ALTER DATABASE MOUNT
    What happened if you start MRP?
    SQL> alter database recover managed standby database disconnect from session;

    Monitor for few minutes and see whether logs are transporting or not(if you configured properly)
  • 6. Re: not able to get data of primary in standby database (configured dataguard)
    974427 Newbie
    Currently Being Moderated
    thanx for reply

    SQL> alter database recover managed standby database disconnect from session;

    Database altered.

    SQL> alter database open read only;
    alter database open read only
    *
    ERROR at line 1:
    ORA-01154: database busy. Open, close, mount, and dismount not allowed now
    SQL> desc emp
    ERROR:
    ORA-04043: object emp does not exist

    in fact i have created "emp" table in primary database...
  • 7. Re: not able to get data of primary in standby database (configured dataguard)
    CKPT Guru
    Currently Being Moderated
    971424 wrote:
    thanx for reply

    SQL> alter database recover managed standby database disconnect from session;

    Database altered.

    SQL> alter database open read only;
    alter database open read only
    *
    ERROR at line 1:
    ORA-01154: database busy. Open, close, mount, and dismount not allowed now
    SQL> desc emp
    ERROR:
    ORA-04043: object emp does not exist

    in fact i have created "emp" table in primary database...
    When MRP is running you cannot open database.
    I guess you are running into 11gx, then
    1) Cancel MRP
    2) Open read-only
    3) start MRP

    Then once all the archives applied, completely synchronized then check the data what is present in primary.

    HTH.
  • 8. Re: not able to get data of primary in standby database (configured dataguard)
    974427 Newbie
    Currently Being Moderated
    tried, gave below error...

    SQL> recover managed standby database cancel;
    Media recovery complete.
    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:
    'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\SYSTEM01.DBF'

    so i tried to recover..

    SQL> recover managed standby database cancel;
    Media recovery complete.
    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:
    'D:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\SYSTEM01.DBF'
  • 9. Re: not able to get data of primary in standby database (configured dataguard)
    CKPT Guru
    Currently Being Moderated
    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:\ORACLE11G\APP\ADMINISTRATOR\ORADATA\STAND\SYSTEM01.DBF'
    I think you are in hurry to open the database rather than it is synchronized or not :)
    Lets complete the MRP to apply archives and once it is completely synchronized then cancel the MRP.
    and if all the archives are applied it means the data will be same as primary and no need to worry.
  • 10. Re: not able to get data of primary in standby database (configured dataguard)
    974427 Newbie
    Currently Being Moderated
    some what yes.....
    ok i have issued "recover managed standby database" its taking a long time.. hope so something good shall happen :)
  • 11. Re: not able to get data of primary in standby database (configured dataguard)
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,

    Before cancelling the recovery in order to open the database, mainly check if the standby is in sync with the primary.

    On Primary database:
    select max(sequence#) from v$archived_log;
    On standby database:
    select max(sequence#) from v$archived_log where applied='YES';
    Check if both the queries give out the same result. Then cancel the recovery on standby and open it.


    Regards,
    Shivananda
  • 12. Re: not able to get data of primary in standby database (configured dataguard)
    974427 Newbie
    Currently Being Moderated
    hello Shivananda,

    max sequence number at primary= 158 and standby is 146...which i have seen by scrolling up screen(without condition"applied=yes") what to do now...?

    Edited by: 971424 on Jan 16, 2013 6:00 AM

    Edited by: 971424 on Jan 16, 2013 6:02 AM

    Edited by: 971424 on Jan 16, 2013 6:03 AM
  • 13. Re: not able to get data of primary in standby database (configured dataguard)
    974427 Newbie
    Currently Being Moderated
    hi,

    its taking very long time.. have the below entries in alertlog from past 30 minutes or so..

    ORA-16037: user requested cancel of managed recovery operation
    Completed: ALTER DATABASE RECOVER managed standby database cancel
    Wed Jan 16 17:50:09 2013
    alter database open read only
    Signalling error 1152 for datafile 1!
    ORA-16004 signalled during: alter database open read only...
    Wed Jan 16 18:54:14 2013
    ALTER DATABASE RECOVER managed standby database
    Fast Parallel Media Recovery enabled
    Managed Standby Recovery not using Real Time Apply
    parallel recovery started with 5 processes
    Media Recovery Waiting for thread 1 sequence 146
  • 14. Re: not able to get data of primary in standby database (configured dataguard)
    CKPT Guru
    Currently Being Moderated
    971424 wrote:
    hi,

    its taking very long time.. have the below entries in alertlog from past 30 minutes or so..

    ORA-16037: user requested cancel of managed recovery operation
    Completed: ALTER DATABASE RECOVER managed standby database cancel
    Wed Jan 16 17:50:09 2013
    alter database open read only
    Signalling error 1152 for datafile 1!
    ORA-16004 signalled during: alter database open read only...
    Wed Jan 16 18:54:14 2013
    ALTER DATABASE RECOVER managed standby database
    Fast Parallel Media Recovery enabled
    Managed Standby Recovery not using Real Time Apply
    parallel recovery started with 5 processes
    Media Recovery Waiting for thread 1 sequence 146
    What is the each archive log file size? do you see any status changes in v$managed_standby when MRP is running?
    if the blocks are changing and status is applying, you have to wait until all the transactions been applied.

    so after 30 minutes how many archives applied total? do you think there is any progress?
1 2 3 Previous Next

Legend

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