8 Replies Latest reply: Jun 5, 2014 1:09 AM by Warrior25 RSS

    Problem with restoring DB

    jerry44

      I've backed up database using sqldeveloper > tools > database export > duped into sql file. But now I don't know how to restore it, especially  tables, they contain important data. Database is 11g.

        • 1. Re: Problem with restoring DB
          EdStevens

          user8851646 wrote:

           

          I've backed up database using sqldeveloper > tools > database export > duped into sql file. But now I don't know how to restore it, especially  tables, they contain important data. Database is 11g.

          that's going to depend on the exact options you selected when you created the export.  If it really created a sql file, then you should have a file with a bunch of sql statements that you can execute.  Have you actually looked at the file to get an idea of what you have?

           

          If your data is really important you should be backing up the database. I hope you haven't yet lost your important data.  After the data loss is not the time to start figuring out how you are going to be able to restore it.

          • 2. Re: Problem with restoring DB
            rp0428
            I've backed up database using sqldeveloper > tools > database export > duped into sql file. But now I don't know how to restore it, especially  tables, they contain important data. Database is 11g.

            Well - now you may have a bit of a problem. Sql developer just creates one (or more) SQL files depending on what options you chose.

             

            If you put everything into ONE file then you restore what you want by MANUALLY finding and executing the portions of that file that you want to use.

             

            The below is just a SMALL example of an 'export' to one file of ONLY the SCOTT dept and emp tables and data. You can see how everything (TABLE DDL, DATA) is just mixed together:

            --------------------------------------------------------
            --  File created - Monday-June-02-2014  
            --------------------------------------------------------
            --------------------------------------------------------
            --  DDL for Table DEPT
            --------------------------------------------------------

              CREATE TABLE "SCOTT"."DEPT"
               ( "DEPTNO" NUMBER(2,0),
            "DNAME" VARCHAR2(14 BYTE),
            "LOC" VARCHAR2(13 BYTE)
               ) SEGMENT CREATION IMMEDIATE
              PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
              TABLESPACE "USERS" ;
            --------------------------------------------------------
            --  DDL for Table EMP
            --------------------------------------------------------

              CREATE TABLE "SCOTT"."EMP"
               ( "EMPNO" NUMBER(4,0),
            "ENAME" VARCHAR2(10 BYTE),
            "JOB" VARCHAR2(9 BYTE),
            "MGR" NUMBER(4,0),
            "HIREDATE" DATE,
            "SAL" NUMBER(7,2),
            "COMM" NUMBER(7,2),
            "DEPTNO" NUMBER(2,0)
               ) SEGMENT CREATION IMMEDIATE
              PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
              TABLESPACE "USERS" ;
            REM INSERTING into SCOTT.DEPT
            SET DEFINE OFF;
            Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
            Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH**','DALLAS');
            Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (30,'SALES****','CHICAGO');
            Insert into SCOTT.DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');
            REM INSERTING into SCOTT.EMP
            SET DEFINE OFF;
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'new_SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),30,null,20);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'new_JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'new_SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'new_ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'new_FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
            Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);

            • 3. Re: Problem with restoring DB
              Emad Al-Mousa

              Hi,

               

              as others mentioned this is not the best of taking backup (use impdp/expdp or RMAN).

               

              it seems you have done it like this guide ? (http://www.oracle.com/technetwork/developer-tools/sql-developer/export-intro-1-161239.html)

               

               

              Regards,

              • 4. Re: Problem with restoring DB
                Gaurav_91

                Hey,

                 

                If your DB still exist, i will suggest you to take backup using any of the oracle suggested method like exp/imp, expdp/impdp etc and then import it on desired location.

                 

                If you still want to use this .sql file for importing the data, then just open it and find the needed data you need from it and then execute it.

                • 5. Re: Problem with restoring DB
                  Warrior25

                  Now onwards just open the sql file and extract the needed data and then try to execute it. You will get all the data. But next time make sure to follow the Oracle Recommended methods to take backup.

                  • 6. Re: Problem with restoring DB
                    Gaurav_91

                    Hi Warrior25,

                     

                    Why are you referring the above post to me?

                    • 7. Re: Problem with restoring DB
                      Warrior25

                      Oops that was done by mistake.

                      • 8. Re: Problem with restoring DB
                        Warrior25

                        Now onwards just open the sql file and extract the needed data and then try to execute it. You will get all the data. But next time make sure to follow the Oracle Recommended methods to take backup.