[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.
using this logical backups only simple restore is possible recovery is not possible.
[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.
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.
Thank you for sharing very nice content
ReplyDeleteSql server DBA Online Training
This was really helpful. Very good explanation with good examples.
ReplyDelete