Teguh Triharto Learning Center: .::: How to backup and restore table & database Oracle :::.

A. Backup & Restore table Oracle
1. Backup Table Database oracle
exp aisyah/hanin@tka tables=pasok file="D:\labolatorium\oracle\backup\pasok.dmp"

2. Restore Table Oracle
imp teguh/triharto@tka
imp teguh/triharto@tka tables=pasok file="D:\labolatorium\oracle\backup\pasok.dmp"

3. Capture before & after restore table

B. Backup & Restore Database Oracle
1. Backup Database Oracle (using exp general)
exp aisyah/hanin@tka
exp aisyah/hanin@localhost:1521/tka

1.1 Backup Database Oracle (using exp file directory)
exp aisyah/hanin@tka file="D:\labolatorium\oracle\backup\alltable.dmp"
exp aisyah/hanin@localhost:1521/tka file="D:\labolatorium\oracle\backup\alltable.dmp"

2. Restore Database Oracle
imp teguh/triharto@tka
imp teguh/triharto@tka tables=barang,suplier,customer,pasok,pembelian file="D:\labolatorium\oracle\backup\alltable.dmp"

3. Capture before & after restore database

4. backup custom

exp aisyah/hanin@localhost:1521/tgh file="D:\labolatorium\oracle\backup\backup_table_only.dmp" content=metadata_only tables=barang,customer

exp aisyah/hanin@localhost:1521/tgh file="D:\labolatorium\oracle\backup\backup_data_only.dmp" content=data_only tables=barang,customer

exp aisyah/hanin@localhost:1521/tgh file="D:\labolatorium\oracle\backup\backup_without_sp_triger.dmp" content=all exclude=procedure,trigger,function,view

C. Backup general
exp aisyah/hanin@tka
exp aisyah/hanin@localhost:1521/tka

D. Import General
imp teguh/triharto@tka
imp teguh/triharto@localhost:1521/tka

Log

A. Backup & Restore table Oracle
1. Backup Table Database oracle
exp aisyah/hanin@tka tables=pasok file="D:\labolatorium\oracle\backup\pasok.dmp"

C:\Users\teguh>exp aisyah/hanin@tka tables=pasok file="D:\labolatorium\oracle\backup\pasok.dmp"
Export: Release 11.2.0.1.0 - Production on Wed Apr 14 10:31:49 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table PASOK 12 rows exported
Export terminated successfully without warnings.
C:\Users\teguh>

2. Restore Table Oracle
imp teguh/triharto@tka
imp teguh/triharto@tka tables=pasok file="D:\labolatorium\oracle\backup\pasok.dmp"

C:\Users\teguh>imp teguh/triharto@tka tables=pasok file="D:\labolatorium\oracle\backup\pasok.dmp"
Import: Release 11.2.0.1.0 - Production on Wed Apr 14 10:32:15 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by AISYAH, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing AISYAH's objects into TEGUH
. importing AISYAH's objects into TEGUH
. . importing table "PASOK" 12 rows imported
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "PASOK" ADD CONSTRAINT "FK_PASOK_SUPLIER" FOREIGN KEY ("KODE_SU"
"PLIER") REFERENCES "SUPLIER" ("KODE_SUPLIER") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "PASOK" ADD CONSTRAINT "FK_PASOK_BARANG" FOREIGN KEY ("KODE_BAR"
"ANG") REFERENCES "BARANG" ("KODE_BARANG") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "PASOK" ENABLE CONSTRAINT "FK_PASOK_SUPLIER""
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "PASOK" ENABLE CONSTRAINT "FK_PASOK_BARANG""
Import terminated successfully with warnings.
C:\Users\teguh>

3. Capture before & after restore table
C:\Users\teguh>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 14 10:31:18 2021
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: teguh
Enter password:
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 * from tab;
no rows selected
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
PASOK TABLE
SQL> select * from pasok;
KODE_PASOK KODE_B KODE_ TANGGAL_P JUMLAH_PASOK KETERANGAN
---------- ------ ----- --------- ------------ ---------------
PAS-E001 ELK-01 EJ-01 01-JAN-02 8
PAS-E002 ELK-01 EJ-02 01-JAN-02 5
PAS-E003 ELK-02 EJ-01 01-FEB-02 2
PAS-E004 ELK-02 EJ-02 02-FEB-02 3
PAS-E005 ELK-02 EB-02 01-JAN-02 2
PAS-E006 ELK-03 EJ-01 03-MAR-02 5
PAS-E007 ELK-03 EJ-01 04-MAR-02 2
PAS-E008 ELK-03 EJ-01 03-MAR-02 3
PAS-E009 ELK-03 EB-01 13-MAR-02 4
PAS-E010 ELK-03 EB-01 13-MAR-02 3
PAS-E011 ELK-04 EB-01 22-APR-02 12
PAS-E012 ELK-04 EB-02 30-APR-02 9
12 rows selected.
SQL>

B. Backup & Restore Database Oracle
1. Backup Database Oracle (using exp general)
exp aisyah/hanin@tka
exp aisyah/hanin@tka@localhost:1521/tka

1. Backup Database Oracle (using exp general)
exp aisyah/hanin@tka
exp aisyah/hanin@tka@localhost:1521/tka

C:\Users\teguh>exp aisyah/hanin@localhost:1521/tka file="D:\labolatorium\oracle\backup\alltable.dmp"
Export: Release 11.2.0.1.0 - Production on Wed Apr 14 11:17:01 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user AISYAH
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user AISYAH
About to export AISYAH's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export AISYAH's tables via Conventional Path ...
. . exporting table BARANG 6 rows exported
. . exporting table CUSTOMER 6 rows exported
. . exporting table PASOK 12 rows exported
. . exporting table PEMBELIAN 15 rows exported
. . exporting table SUPLIER 6 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
C:\Users\teguh>

2. Restore Database Oracle
imp teguh/triharto@tka
imp teguh/triharto@tka tables=barang,suplier,customer,pasok,pembelian file="D:\labolatorium\oracle\backup\alltable.dmp"

C:\Users\teguh>imp teguh/triharto@tka tables=barang,suplier,customer,pasok,pembelian file="D:\labolatorium\oracle\backup\alltable.dmp"
Import: Release 11.2.0.1.0 - Production on Wed Apr 14 12:27:29 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by AISYAH, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing AISYAH's objects into TEGUH
. importing AISYAH's objects into TEGUH
. . importing table "BARANG" 6 rows imported
. . importing table "CUSTOMER" 6 rows imported
. . importing table "PASOK" 12 rows imported
. . importing table "PEMBELIAN" 15 rows imported
. . importing table "SUPLIER" 6 rows imported
About to enable constraints...
Import terminated successfully without warnings.
C:\Users\teguh>

3. Capture before & after restore database

C. Backup general
exp aisyah/hanin@tka
exp aisyah/hanin@localhost:1521/tka

C:\Users\teguh>exp aisyah/hanin@localhost:1521/tka
Export: Release 11.2.0.1.0 - Production on Wed Apr 14 12:36:55 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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
Enter array fetch buffer size: 4096 > 30720
Export file: EXPDAT.DMP > D:\labolatorium\oracle\backup\alltable.dmp
(2)U(sers), or (3)T(ables): (2)U > 2
Export grants (yes/no): yes > yes
Export table data (yes/no): yes > yes
Compress extents (yes/no): yes > yes
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user AISYAH
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user AISYAH
About to export AISYAH's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export AISYAH's tables via Conventional Path ...
. . exporting table BARANG 6 rows exported
. . exporting table CUSTOMER 6 rows exported
. . exporting table PASOK 12 rows exported
. . exporting table PEMBELIAN 15 rows exported
. . exporting table SUPLIER 6 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
C:\Users\teguh>

D. Import General
imp teguh/triharto@tka
imp teguh/triharto@localhost:1521/tka

C:\Users\teguh>imp teguh/triharto@localhost:1521/tka
Import: Release 11.2.0.1.0 - Production on Wed Apr 14 12:39:43 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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
Import data only (yes/no): no > no
Import file: EXPDAT.DMP > D:\labolatorium\oracle\backup\alltable.dmp
Enter insert buffer size (minimum is 8192) 30720> 30720
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by AISYAH, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
List contents of import file only (yes/no): no > no
Ignore create error due to object existence (yes/no): no > yes
Import grants (yes/no): yes > yes
Import table data (yes/no): yes > yes
Import entire export file (yes/no): no > yes
. importing AISYAH's objects into TEGUH
. . importing table "BARANG" 6 rows imported
. . importing table "CUSTOMER" 6 rows imported
. . importing table "PASOK" 12 rows imported
. . importing table "PEMBELIAN" 15 rows imported
. . importing table "SUPLIER" 6 rows imported
About to enable constraints...
Import terminated successfully without warnings.
C:\Users\teguh>

Teguh Triharto Learning Center

Blog List

Wednesday, April 14, 2021

.::: How to backup and restore table & database Oracle :::.

No comments:

Post a Comment

Popular Posts