Hi Guy's
You can perform all levels of logical backups using (9i-exp) utility in oracle 11g.
>> You can do incremental, cumulative, and complete exports only in full database mode (FULL=Y).
>> Only users who have the role EXP_FULL_DATABASE can run incremental, cumulative, and complete Exports.
Note:- An incremental Export backups only those tables that have changed since the last incremental, cumulative, or complete Export. this is the reason we have to take complete backup first in-order to take incremental backup.
>> inctype=Complete
E:\ test_exp > exp file=E:\test_fullbkp log=test.log full=y inctype=complete
Export: Release 11.2.0.3.0 - Production on Wed Jan 3 11:33:43 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
EXP-00041: INCTYPE parameter is obsolete
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. 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 object type definitions
. exporting system procedural objects and actions
. . exporting table WWV_MIG_OLB_T_TP_GG_CPDTXT 0 rows exported
. . exporting table WWV_MIG_OLB_T_TP_GG_CT_TXTSGT 0 rows exported
. . exporting table WWV_MIG_OLB_T_TP_GG_GRAPHICS 0 rows exported
. . exporting table WWV_MIG_OLB_T_TP_G_GRAPHICS 0 rows exported
. . exporting table WWV_MIG_OLB_VISUALATTRIBUTE 0 rows exported
. . exporting table WWV_MIG_OLB_WINDOW 0 rows exported
. . exporting table WWV_MIG_PLSQL_LIBS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_PLSQL_LIBS is not supported. The table will not be exported.
. . exporting table WWV_MIG_PROJECTS 0 rows exported
. . exporting table WWV_MIG_PROJECT_COMPONENTS 0 rows exported
. . exporting table WWV_MIG_PROJECT_TRIGGERS 0 rows exported
. . exporting table WWV_MIG_REPORT 0 rows exported
. . exporting table WWV_MIG_RESERVED_WORDS 87 rows exported
. . exporting table WWV_MIG_REV_APEXAPP 0 rows exported
. . exporting table WWV_MIG_REV_FORMS 0 rows exported
. . exporting table WWV_MIG_REV_QUERIES 0 rows exported
. . exporting table WWV_MIG_REV_REPORTS 0 rows exported
. . exporting table WWV_MIG_REV_TABLES 0 rows exported
. . exporting table WWV_MIG_RPTS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_RPTS is not supported. The table will not be exported.
. . exporting table WWV_MIG_RPT_DATA 0 rows exported
. . exporting table WWV_MIG_RPT_DATASRC 0 rows exported
. . exporting table WWV_MIG_RPT_DATASRC_GRP 0 rows exported
. . exporting table WWV_MIG_RPT_DATASRC_SELECT 0 rows exported
. . exporting table WWV_MIG_RPT_DATA_SUMMARY 0 rows exported
. . exporting table WWV_MIG_RPT_GRP_DATAITEM 0 rows exported
. . exporting table WWV_MIG_RPT_GRP_DATAITEM_DESC 0 rows exported
. . exporting table WWV_MIG_RPT_GRP_DATAITEM_PRIV 0 rows exported
. . exporting table WWV_MIG_RPT_GRP_FIELD 0 rows exported
. . exporting table WWV_MIG_RPT_GRP_FILTER 0 rows exported
. . exporting table WWV_MIG_RPT_GRP_FORMULA 0 rows exported
. . exporting table WWV_MIG_RPT_GRP_ROWDELIM 0 rows exported
. . exporting table WWV_MIG_RPT_GRP_SUMMARY 0 rows exported
. . exporting table WWV_MIG_RPT_REPORTPRIVATE 0 rows exported
. . exporting table WWV_MIG_RPT_XMLTAGTABLEMAP 15 rows exported
. about to export OWBSYS's tables via Conventional Path ...
. . exporting table OWBRTPS 0 rows exported
. about to export OWBSYS_AUDIT's tables via Conventional Path ...
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. about to export SAM's tables via Conventional Path ...
. 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.
>> You can observe in above output there are no tables in SAM's user
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
ADD 2 TABLES IN USER SAM
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> create table sam.emp as select * from scott.emp;
Table created.
SQL> create table sam.dept as select * from scott.dept;
Table created.
SQL> commit;
Commit complete.
SQL> conn sam/s
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
>> Now take Inctype=incremental
>> You can see in this below output how oracle take incremental exports of the newly added tables.
E:\test_exp> exp file=E:\test_fullbkp log=test.log full=y inctype=incremental
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
EXP-00041: INCTYPE parameter is obsolete
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. 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 object type definitions
. exporting system procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. about to export OUTLN's tables via Conventional Path ...
. about to export ORDDATA's tables via Conventional Path ...
. . exporting table ORDDCM_CT_PRED_OPRD
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD is not supported. The table will not be exported.
. . exporting table ORDDCM_CT_PRED_OPRD_TMP
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD_TMP is not supported. The table will not be exported.
. . exporting table ORDDCM_CT_PRED_OPRD_WRK
EXP-00107: Feature (BINARY XML) of column OPERAND in table ORDDATA.ORDDCM_CT_PRED_OPRD_WRK is not supported. The table will not be exported.
. . exporting table ORDDCM_DOCS
EXP-00107: Feature (BINARY XML) of column DOC_CONTENT in table ORDDATA.ORDDCM_DOCS is not supported. The table will not be exported.
. . exporting table ORDDCM_DOCS_TMP
EXP-00107: Feature (BINARY XML) of column DOC_CONTENT in table ORDDATA.ORDDCM_DOCS_TMP is not supported. The table will not be exported.
. . exporting table ORDDCM_DOCS_WRK
EXP-00107: Feature (BINARY XML) of column DOC_CONTENT in table ORDDATA.ORDDCM_DOCS_WRK is not supported. The table will not be exported.
. . exporting table ORDDCM_MAPPING_DOCS
EXP-00107: Feature (BINARY XML) of column XSLT in table ORDDATA.ORDDCM_MAPPING_DOCS is not supported. The table will not be exported.
. . exporting table ORDDCM_MAPPING_DOCS_TMP
EXP-00107: Feature (BINARY XML) of column XSLT in table ORDDATA.ORDDCM_MAPPING_DOCS_TMP is not supported. The table will not be exported.
. . exporting table ORDDCM_MAPPING_DOCS_WRK
EXP-00107: Feature (BINARY XML) of column XSLT in table ORDDATA.ORDDCM_MAPPING_DOCS_WRK is not supported. The table will not be exported.
. about to export OLAPSYS's tables via Conventional Path ...
. about to export MDDATA's tables via Conventional Path ...
. about to export SPATIAL_WFS_ADMIN_USR's tables via Conventional Path ...
. about to export SPATIAL_CSW_ADMIN_USR's tables via Conventional Path ...
. about to export SYSMAN's tables via Conventional Path ...
. about to export MGMT_VIEW's tables via Conventional Path ...
. about to export FLOWS_FILES's tables via Conventional Path ...
. about to export APEX_PUBLIC_USER's tables via Conventional Path ...
. about to export APEX_030200's tables via Conventional Path ...
. . exporting table WWV_MIG_FORMS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_FORMS is not supported. The table will not be exported.
. . exporting table WWV_MIG_FRM_MENUS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_FRM_MENUS is not supported. The table will not be exported.
. . exporting table WWV_MIG_OLB
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_OLB is not supported. The table will not be exported.
. . exporting table WWV_MIG_PLSQL_LIBS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_PLSQL_LIBS is not supported. The table will not be exported.
. . exporting table WWV_MIG_RPTS
EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_MIG_RPTS is not supported. The table will not be exported.
. about to export OWBSYS's tables via Conventional Path ...
. about to export OWBSYS_AUDIT's tables via Conventional Path ...
. about to export SCOTT's tables via Conventional Path ...
. about to export SAM's tables via Conventional Path ...
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 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 information about dropped objects
. exporting statistics
Export terminated successfully with warnings.
>> Now Import this Incremental Backups of 2 Tables in Another Database
>> Create User at Target Machine in which you want to import these 2 incremental backup tables.
E:\imp_kkprod> imp file=E:\imp_kkprod\empdept.dmp fromuser=sam touser=sam
Import: Release 11.2.0.3.0 - Production on Mon Jan 8 13:18:46 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SAM's objects into SAM
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
Import terminated successfully without warnings.
Enjoy Reading....
In any table row is alter then what will happen. Will it take full table backup? If your answer is Yes, then will we face any problem when we will import because the table already exist by the previous full backup?
ReplyDelete