1 2 3 Previous Next 35 Replies Latest reply: Apr 10, 2012 6:42 AM by CKPT Go to original post RSS
      • 15. Re: DATAGUARD-RMAN- tablespace restore n recovery
        CKPT
        i could restore tablespace in rman....i am unable to open database!

        i suspect, present datfiles headers are not matched with restored control file....
        What is error?
        Can you check
        SQL> select name,status from v$datafile;
        You should have all datafiles physically exist in the location mentioned. If standby is different location, then rename those files to actual location as below example

        SQL>alter database rename file '/u01/prim/datafile/system01.dbf' as '/u02/stby/datafile/system01.dbf';

        Like that rename to all of datafiles,then start MRP..
        • 16. Re: DATAGUARD-RMAN- tablespace restore n recovery
          sachinpawan
          physcially its located in location,

          but when i queried with v$datafile, its not showing.........
          • 17. Re: DATAGUARD-RMAN- tablespace restore n recovery
            CKPT
            sachinpawan wrote:
            physcially its located in location,

            but when i queried with v$datafile, its not showing.........
            Please post output

            SQL> select name,status from v$datafile;

            ANd post from OS where data files exist

            $ls -ltr <location>
            • 18. Re: DATAGUARD-RMAN- tablespace restore n recovery
              sachinpawan
              STATUS
              -------
              NAME
              --------------------------------------------------------------------------------
              SYSTEM
              /oradatafiles/CBKDG/datafile/o1_mf_system_7r2xvft2_.dbf

              ONLINE
              /oradatafiles/CBKDG/datafile/o1_mf_sysaux_7r2xvfyo_.dbf

              ONLINE
              /oradatafiles/CBKDG/datafile/o1_mf_undotbs1_7r2xvfz6_.dbf


              STATUS
              -------
              NAME
              --------------------------------------------------------------------------------
              ONLINE
              /oradatafiles/CBKDG/datafile/o1_mf_users1_7r5cc37m_.dbf



              location of the files...

              [oracle@BMLTESTDB6 datafile]$ ls -ltr
              total 1511952
              -rw-r--r-- 1 oracle oinstall 20979712 Apr 9 04:50 o1_mf_temp_7r2xx9nm_.tmp
              -rw-r----- 1 oracle dba 104865792 Apr 10 07:15 o1_mf_users3_7r7dtm1q_.dbf
              -rw-r----- 1 oracle dba 104865792 Apr 10 07:37 o1_mf_users1_7r5cc37m_.dbf
              -rw-r--r-- 1 oracle oinstall 47194112 Apr 10 07:37 o1_mf_undotbs1_7r2xvfz6_.dbf
              -rw-r--r-- 1 oracle oinstall 555753472 Apr 10 07:37 o1_mf_sysaux_7r2xvfyo_.dbf
              -rw-r--r-- 1 oracle oinstall 713039872 Apr 10 07:37 o1_mf_system_7r2xvft2_.dbf


              i dropped users3 datafile
              • 19. Re: DATAGUARD-RMAN- tablespace restore n recovery
                CKPT
                sachinpawan wrote:
                STATUS
                -------
                NAME
                --------------------------------------------------------------------------------
                SYSTEM
                /oradatafiles/CBKDG/datafile/o1_mf_system_7r2xvft2_.dbf

                ONLINE
                /oradatafiles/CBKDG/datafile/o1_mf_sysaux_7r2xvfyo_.dbf

                ONLINE
                /oradatafiles/CBKDG/datafile/o1_mf_undotbs1_7r2xvfz6_.dbf


                STATUS
                -------
                NAME
                --------------------------------------------------------------------------------
                ONLINE
                /oradatafiles/CBKDG/datafile/o1_mf_users1_7r5cc37m_.dbf



                location of the files...

                [oracle@BMLTESTDB6 datafile]$ ls -ltr
                total 1511952
                -rw-r--r-- 1 oracle oinstall 20979712 Apr 9 04:50 o1_mf_temp_7r2xx9nm_.tmp
                -rw-r----- 1 oracle dba 104865792 Apr 10 07:15 o1_mf_users3_7r7dtm1q_.dbf
                -rw-r----- 1 oracle dba 104865792 Apr 10 07:37 o1_mf_users1_7r5cc37m_.dbf
                -rw-r--r-- 1 oracle oinstall 47194112 Apr 10 07:37 o1_mf_undotbs1_7r2xvfz6_.dbf
                -rw-r--r-- 1 oracle oinstall 555753472 Apr 10 07:37 o1_mf_sysaux_7r2xvfyo_.dbf
                -rw-r--r-- 1 oracle oinstall 713039872 Apr 10 07:37 o1_mf_system_7r2xvft2_.dbf


                i dropped users3 datafile
                When you query v$datafile, whatever it gives output, so you have same information in Controlfile.
                Even you have datafiles physically, you can ignore it.
                You have not posted what error you faced, It looks datafile names are correct, Can you check directory structure?
                also
                next time post with errors and below query

                SQL> select file#,error from v$datafile_header;

                Thanks.
                • 20. Re: DATAGUARD-RMAN- tablespace restore n recovery
                  sachinpawan
                  now i opened database with reset logs,

                  but the problem is when i tried to start MRP .....


                  SQL> select file#,error from v$datafile_header;

                  FILE# ERROR
                  ---------- -----------------------------------------------------------------
                  1
                  2
                  3
                  5

                  SQL> alter database recover managed standby database disconnect;
                  alter database recover managed standby database disconnect
                  *
                  ERROR at line 1:
                  ORA-01665: control file is not a standby control file
                  • 21. Re: DATAGUARD-RMAN- tablespace restore n recovery
                    CKPT
                    now i opened database with reset logs,

                    but the problem is when i tried to start MRP .....


                    SQL> select file#,error from v$datafile_header;

                    FILE# ERROR
                    ---------- -----------------------------------------------------------------
                    1
                    2
                    3
                    5
                    So no ERRORS
                    SQL> alter database recover managed standby database disconnect;
                    alter database recover managed standby database disconnect
                    *
                    ERROR at line 1:
                    ORA-01665: control file is not a standby control file
                    Its not a standby controlfile.
                    you can check

                    SQL> select controlfile_type from v$database;
                    -- its current controfile,

                    If you have taken backup of standby controlfile, then you simply restore.
                    So,

                    RMAN> restore standby controlfile from 'controlfile piece';

                    now it will restore standby controlfile.

                    Even i should have inform that earlier..
                    • 22. Re: DATAGUARD-RMAN- tablespace restore n recovery
                      sachinpawan
                      select controlfile_type from v$database;
                      CONTROL
                      -------
                      CURRENT




                      when i take backup i specified "backup controlfile"

                      so , how can i start standby db?

                      Edited by: sachinpawan on Apr 10, 2012 11:05 AM
                      • 23. Re: DATAGUARD-RMAN- tablespace restore n recovery
                        CKPT
                        sachinpawan wrote:
                        select controlfile_type from v$database;
                        CONTROL
                        -------
                        CURRENT




                        when i take backup i specified "backup controlfile"

                        so , how can i start standby db?

                        Edited by: sachinpawan on Apr 10, 2012 11:05 AM
                        Use above command what i have mentioned. (my previous post)
                        • 24. Re: DATAGUARD-RMAN- tablespace restore n recovery
                          sachinpawan
                          i have another copy of controlfile...

                          i changed the path of controlfile in parameter file...i pointed to copy of standby controlfile,,,, and i started DB...i am getting error...




                          SQL> shut immediate
                          Database closed.
                          Database dismounted.
                          ORACLE instance shut down.
                          SQL> startup
                          ORACLE instance started.

                          Total System Global Area 534462464 bytes
                          Fixed Size 2215064 bytes
                          Variable Size 163578728 bytes
                          Database Buffers 360710144 bytes
                          Redo Buffers 7958528 bytes
                          Database mounted.
                          ORA-10458: standby database requires recovery
                          ORA-01190: control file or data file 1 is from before the last RESETLOGS
                          ORA-01110: data file 1:
                          '/oradatafiles/CBKDG/datafile/o1_mf_system_7r2xvft2_.dbf'
                          • 25. Re: DATAGUARD-RMAN- tablespace restore n recovery
                            sachinpawan
                            CKPT wrote:
                            sachinpawan wrote:
                            select controlfile_type from v$database;
                            CONTROL
                            -------
                            CURRENT




                            when i take backup i specified "backup controlfile"

                            so , how can i start standby db?

                            Edited by: sachinpawan on Apr 10, 2012 11:05 AM
                            Use above command what i have mentioned. (my previous post)
                            which command?
                            • 26. Re: DATAGUARD-RMAN- tablespace restore n recovery
                              sachinpawan
                              SQL> select file#,error from v$datafile_header;

                              FILE# ERROR
                              ---------- -----------------------------------------------------------------
                              1 WRONG RESETLOGS
                              2 WRONG RESETLOGS
                              3 WRONG RESETLOGS
                              4 FILE NOT FOUND
                              • 27. Re: DATAGUARD-RMAN- tablespace restore n recovery
                                CKPT
                                sachinpawan wrote:
                                SQL> select file#,error from v$datafile_header;

                                FILE# ERROR
                                ---------- -----------------------------------------------------------------
                                1 WRONG RESETLOGS
                                2 WRONG RESETLOGS
                                3 WRONG RESETLOGS
                                4 FILE NOT FOUND
                                It looks, You have restored different controlfile and datafiles of different resetlogs change,
                                Please restore from the same resetlogs ID,
                                • 28. Re: DATAGUARD-RMAN- tablespace restore n recovery
                                  sachinpawan
                                  ok ....

                                  then i should take standby controlfile backup......?


                                  if you dont mind......

                                  can u guide me ....TS restore n recovery using RMAN

                                  Edited by: sachinpawan on Apr 10, 2012 1:35 PM
                                  • 29. Re: DATAGUARD-RMAN- tablespace restore n recovery
                                    CKPT
                                    revised from beginning..

                                    1) backup, delete TBS(primary) , restore on standby
                                    After this step, When you dropped tablespace, this information will be in Archivelog files, Once you
                                    restore database in standby, and start MRP these archives will be applied on standby, So it will be
                                    removed even from standby database too.

                                    So, Keep tablespace in read only mode, so there would be no updates.

                                    You said you are going to try READ only mode, have you tested?

                                    i am trying to restore tablespace in standby from rman backup;
                                    Have you restore tablespace?
                                    Even you have standby, why you are restoring even controlfile?

                                    how can i restore controlfile from that backup?
                                    RMAN> restore standby controlfile from 'backup piece'

                                    >
                                    SQL> select file#,error from v$datafile_header;

                                    FILE# ERROR
                                    -----------------------------------------------------------------
                                    1
                                    2
                                    3
                                    5
                                    >
                                    Here no errors, after some time..

                                    >
                                    SQL> select file#,error from v$datafile_header;

                                    FILE# ERROR
                                    -----------------------------------------------------------------
                                    1 WRONG RESETLOGS
                                    2 WRONG RESETLOGS
                                    3 WRONG RESETLOGS
                                    4 FILE NOT FOUND
                                    >


                                    Not sure which files are there and which controlfile you restored.
                                    Please do perform restore of controlfile & datafiles from same backup..

                                    Your scenario wont work here, because all changes will be applied in standby by archives..