Tuesday 30 August 2016

How to take the full database backup using export and import utility?

[oracle@appsdb oracle]$ mkdir exp
[oracle@appsdb oracle]$ cd exp
Here export and import utilities are working from the OS level.for taking the backup the database should be open.
[oracle@appsdb exp]$ exp file=full.dmp log=full.log full=y
Export: Release 11.2.0.1.0 - Production on Tue Aug 30 17:47:11 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
EXP-00068: tablespace ROOM is offline
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table                    DEF$_AQCALL          0 rows exported
. . exporting table                   DEF$_AQERROR          0 rows exported
. . exporting table                  DEF$_CALLDEST          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table               DEF$_DEFAULTDEST          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table               DEF$_DESTINATION          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                     DEF$_ERROR          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                       DEF$_LOB          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                    DEF$_ORIGIN          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                DEF$_PROPAGATOR          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table       DEF$_PUSHED_TRANSACTIONS          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. about to export ABC's tables via Conventional Path ...
. . exporting table                           DEPT          4 rows exported
. . exporting table                          DUMMY          1 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.

[oracle@appsdb exp]$ ls
full.dmp  full.log
Export and Import operations are nothing but export is used to export the data to a dump file.dump files are 8 to 10 times smaller than the datafile. where as import is nothing but importing the data from dump file and import the objects into the database.

[oracle@appsdb exp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 30 17:53:01 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
SYS
SYSTEM
ABC
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM

8 rows selected.

SQL> drop user abc;       
drop user abc
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'ABC'

Here user abc contains any objects we need to go with cascade option otherwise drop user username.

SQL> drop user abc cascade;

User dropped.

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
SYS
SYSTEM
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM

7 rows selected.
here abc user is dropped.so now we have the full database backup we had taken using export utility.
let us see how to import using import utility.
[oracle@appsdb exp]$ imp file=full.dmp log=full.log full=y
Export: Release 11.2.0.1.0 - Production on Tue Aug 30 17:47:11 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to import the entire database ...
importing OUTLN's objects into OUTLN
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing ABC's objects into ABC
About to enable constraints...
Import terminated successfully with warnings.

[oracle@appsdb exp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 30 17:59:57 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
SYS
SYSTEM
ABC
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM

8 rows selected.

SQL> conn abc/abc
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                               TABLE
DUMMY                          TABLE
EMP                                  TABLE
SALGRADE                     TABLE

using this logical backups only simple restore  is possible recovery is not possible.






2 comments:

  1. This was really helpful. Very good explanation with good examples.

    ReplyDelete