1 2 3 Previous Next 31 Replies Latest reply: Jan 17, 2013 6:36 AM by CKPT RSS

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

    974427
      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)
          kuljeet singh -
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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