Dump Redo Log - Insert Single Row Record

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> set transaction name 'DungNC';
Transaction set.

SQL> insert into test.nhanvien values (4,'D');
1 row created.

SQL> select xid,status from v$transaction;
XID              STATUS
---------------- ----------------
03000600D20A0000 ACTIVE

SQL> select b.member
from v$log a, v$logfile b
where a.group#=b.group#
and a.status = 'CURRENT';  2    3    4

MEMBER
--------------------------------------------------------------------------------
+DATA/orcl/onlinelog/group_1.262.815134559
+DATA/orcl/onlinelog/group_1.263.815134559

SQL> commit;
Commit complete.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> alter system dump logfile '+DATA/orcl/onlinelog/group_1.262.815134559';
System altered.



REDO RECORD - Thread:1 RBA: 0x000049.0000000b.0010 LEN: 0x01ec VLD: 0x05
SCN: 0x0000.00208c95 SUBSCN:  1 05/25/2013 07:33:33

CHANGE #1 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0000.00208c4a SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0006 sqn: 0x00000ad2 flg: 0x0012 siz: 136 fbi: 0
            uba: 0x00c005a9.00eb.14    pxid:  0x0000.000.00000000

CHANGE #2 TYP:0 CLS:22 AFN:3 DBA:0x00c005a9 OBJ:4294967295 SCN:0x0000.00208c49 SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 136 spc: 4916 flg: 0x0012 seq: 0x00eb rec: 0x14
            xid:  0x0003.006.00000ad2
ktubl redo: slt: 6 rci: 0 opc: 11.1 [objn: 75259 objd: 75259 tsn: 4]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c005a9.00eb.12
prev ctl max cmt scn:  0x0000.00208892  prev tx cmt scn:  0x0000.002088c3
txn start scn:  0xffff.ffffffff  logon user: 0  prev brb: 12584358  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000226  hdba: 0x01000222
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1)

CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x01000226 OBJ:75259 SCN:0x0000.00208c46 SEQ:1 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0003.006.00000ad2    uba: 0x00c005a9.00eb.14
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000226  hdba: 0x01000222
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 8
fb: --H-FL-- lb: 0x2  cc: 2
null: --
col  0: [ 2]  c1 05
col  1: [ 1]  44

CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number   = 36
serial  number   = 458
transaction name = DungNC
version 186646784
audit sessionid 4294967295
Client Id =

Truncate Partition Table with GLOBAL INDEX and LOCAL INDEX

1. Create 4 tablespace

SQL> create tablespace part1 datafile '/d01/oracle/database/oradata/DUNGDB/file1.dbf' size 1M;
Tablespace created.

SQL> create tablespace part2 datafile '/d01/oracle/database/oradata/DUNGDB/file2.dbf' size 1M;
Tablespace created.

SQL> create tablespace part3 datafile '/d01/oracle/database/oradata/DUNGDB/file3.dbf' size 1M;
Tablespace created.

SQL> create tablespace part4 datafile '/d01/oracle/database/oradata/DUNGDB/file4.dbf' size 1M;
Tablespace created.


2. Create partition table TEST_PARTITION

SQL> CREATE TABLE test_partition
(test_id NUMBER(5),
test_name VARCHAR2(30),
test_date DATE)
PARTITION BY RANGE(test_date)
(PARTITION tb_part1 VALUES LESS THAN(TO_DATE('20/10/2010','DD/MM/YYYY')) TABLESPACE part1 ,
PARTITION tb_part2 VALUES LESS THAN(TO_DATE('21/10/2010','DD/MM/YYYY')) TABLESPACE part2 ,
PARTITION tb_part3 VALUES LESS THAN(TO_DATE('22/10/2010','DD/MM/YYYY')) TABLESPACE part3 ,
PARTITION tb_part4 VALUES LESS THAN(TO_DATE('23/10/2010','DD/MM/YYYY')) TABLESPACE part4);

Table created.


3. Create GLOBAL INDEX and PRIMARY KEY

SQL> CREATE UNIQUE INDEX test_id_ix ON test_partition(test_id) GLOBAL;
Index created.


SQL> Alter table test_partition add (
Constraint test_id_pk primary key (test_id)
Using index test_id_ix);
Table altered.


4. Insert data to table TEST_PARTITION

SQL> INSERT INTO test_partition VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (2,'Test case 2',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (3,'Test case 3',TO_DATE('20/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (4,'Test case 4',TO_DATE('20/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (5,'Test case 5',TO_DATE('21/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (6,'Test case 6',TO_DATE('21/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (7,'Test case 7',TO_DATE('22/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> INSERT INTO test_partition VALUES (8,'Test case 8',TO_DATE('22/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
1 row created.

SQL> commit;
Commit complete.


SQL> SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE TABLE_NAME='TEST_PARTITION';

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TB_PART1 PART1 TO_DATE(' 2010-10-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TB_PART2 PART2 TO_DATE(' 2010-10-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TB_PART3 PART3 TO_DATE(' 2010-10-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TB_PART4 PART4 TO_DATE(' 2010-10-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA



5. Truncate 1 partition of table and try to insert data to table

SQL> ALTER TABLE TEST_PARTITION TRUNCATE PARTITION tb_part1;
Table truncated.


SQL> select * from TEST_PARTITION;

TEST_ID TEST_NAME TEST_DATE
---------- ------------------------------ ---------
3 Test case 3 20-OCT-10
4 Test case 4 20-OCT-10
5 Test case 5 21-OCT-10
6 Test case 6 21-OCT-10
7 Test case 7 22-OCT-10
8 Test case 8 22-OCT-10

6 rows selected.

SQL> INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));
INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'))
*
ERROR at line 1:
ORA-01502: index 'SYS.TEST_ID_IX' or partition of such index is in unusable state



SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX UNUSABLE


6. Index need to rebuild

SQL> alter index TEST_ID_IX rebuild;

Index altered.


SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX VALID



SQL> INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> INSERT INTO TEST_PARTITION VALUES (2,'Test case 2',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> commit;

Commit complete.

SQL> select *
2 from TEST_PARTITION;

TEST_ID TEST_NAME TEST_DATE
---------- ------------------------------ ---------
1 Test case 1 19-OCT-10
2 Test case 2 19-OCT-10
3 Test case 3 20-OCT-10
4 Test case 4 20-OCT-10
5 Test case 5 21-OCT-10
6 Test case 6 21-OCT-10
7 Test case 7 22-OCT-10
8 Test case 8 22-OCT-10

8 rows selected.


7. Try again with LOCAL INDEX (Local index can't used to build Primary key)

SQL> Alter table TEST_PARTITION drop primary key;

Table altered.

SQL> DROP INDEX test_id_ix;

Index dropped.

SQL> CREATE INDEX test_id_ix ON test_partition(test_id) LOCAL;

Index created.

SQL> ALTER TABLE TEST_PARTITION TRUNCATE PARTITION tb_part1;

Table truncated.

SQL> select *
2 from TEST_PARTITION;

TEST_ID TEST_NAME TEST_DATE
---------- ------------------------------ ---------
3 Test case 3 20-OCT-10
4 Test case 4 20-OCT-10
5 Test case 5 21-OCT-10
6 Test case 6 21-OCT-10
7 Test case 7 22-OCT-10
8 Test case 8 22-OCT-10

6 rows selected.

SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX N/A

SQL> INSERT INTO TEST_PARTITION VALUES (1,'Test case 1',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> INSERT INTO TEST_PARTITION VALUES (2,'Test case 2',TO_DATE('19/10/2010 10:10:10','DD/MM/YYYY HH24:MI:SS'));

1 row created.

SQL> commit;

Commit complete.

SQL> select INDEX_NAME,STATUS
2 from user_indexes
3 where INDEX_NAME='TEST_ID_IX';

INDEX_NAME STATUS
------------------------------ --------
TEST_ID_IX N/A


8. Clear test case

SQL> DROP INDEX test_id_ix;
Index dropped.

SQL> DROP TABLE TEST_PARTITION CASCADE CONSTRAINTS PURGE;
Table dropped.

SQL> DROP TABLESPACE part1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE part2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> DROP TABLESPACE part3 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> DROP TABLESPACE part4 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

Expdp/Impdp Metadata Only Oracle Schema

$ sqlplus / as sysdba

SQL> create directory export_dump as '/home/oracle/backup';
Directory created.

SQL> create user export1 identified by oracle123;
User created.

SQL> grant connect,resource to export1;
Grant succeeded.

SQL> create table export1.data1 as select * from dba_objects;
Table created.

SQL> exit

$ mkdir -p /home/oracle/backup

$ expdp system/oracle123 DUMPFILE=export.dmp DIRECTORY=export_dump SCHEMAS=export1 content=metadata_only

 $ impdp system/oracle123 DUMPFILE=export.dmp DIRECTORY=export_dump SCHEMAS=export1 content=metadata_only remap_schema=export1:import1

$ impdp system/oracle123 DUMPFILE=export.dmp DIRECTORY=export_dump SCHEMAS=export1 content=metadata_only remap_schema=export1:import2

 $ sqlplus / as sysdba

SQL> create table export1.data2 as select * from dba_tables;
Table created.

SQL> exit

$ expdp system/oracle123 DUMPFILE=export3.dmp DIRECTORY=export_dump SCHEMAS=export1 content=metadata_only

 $ impdp system/oracle123 DUMPFILE=export3.dmp DIRECTORY=export_dump SCHEMAS=export1 content=metadata_only table_exists_action=replace remap_schema=export1:import2

Tổng kết:
- Expdp có thể export metadata
- Khi impdp, nếu chưa có user thì sẽ tạo user (user system)
- Bổ sung tham số table_exists_action để việc impdp uyển chuyển hơn
- Khi impdp, nếu user có tồn tại sẽ báo lỗi ORA-31684: Object type USER:"IMPORT2" already exists