Archiving Partition
To archive a partition, follow these steps:
1. Make the tablespace to be archived READ ONLY.
ALTER TABLESPACE CM_XT039_P2011JAN READ ONLY;
2. Check the feasibility of archive using ILM_ARCH_SW = ‘N’.
Select count(1) from CISADM.CI_TD_ENTRY PARTITION P2011JAN where ILM_ARCH_SW = ‘N’;
• IF the above query has a count of greater than ZERO records - Change the tablespace back to read and write mode. Archive cannot be done. Do not execute further steps. Stop archiving partition.
ALTER TABLESPACE CM_XT039_P2011JAN READ WRITE;
• IF above query has ZERO records - Archive can be performed. Continue executing the remainder of the procedure.
3. Create separate archive tablespace for the partition that needs to be archived.
CREATE BIGFILE TABLESPACE CM_XT039_P2011JAN_ARC DATAFILE '+DATA' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
4. Create staging tables and load data for all child tables for the MO first.
a. CI_TD_ENTRY_CHA
CREATE TABLE CM_XT701_P2011JAN_ARC PARALLEL NOLOGGING TABLESPACE CM_XT039_P2011JAN_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_ENTRY_CHA PARTITION (P2011JAN_S01)
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_ENTRY_CHA PARTITION (P2011JAN_S02)
UNION ALL
.
.
.
.
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_ENTRY_CHA PARTITION (P2011JAN_S08)
);
ALTER TABLE CM_XT701_P2011JAN_ARC NOPARALLEL LOGGING;
b. CI_TD_MSG_PARM
CREATE TABLE CM_XT04_P2011JAN_ARC PARALLEL NOLOGGING TABLESPACE CM_XT039_P2011JAN_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_MSG_PARM PARTITION (P2011JAN_S01)
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_MSG_PARM PARTITION (P2011JAN_S02)
UNION ALL
.
.
.
.
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_MSG_PARM PARTITION (P2011JAN_S08)
);
ALTER TABLE CM_XT04_P2011JAN_ARC NOPARALLEL LOGGING;
c. CI_TD_LOG
CREATE TABLE CM_XT721_P2011JAN_ARC PARALLEL NOLOGGING TABLESPACE CM_XT039_P2011JAN_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_LOG PARTITION (P2011JAN_S01)
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_LOG PARTITION (P2011JAN_S02)
UNION ALL
.
.
.
.
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_LOG PARTITION (P2011JAN_S08)
);
ALTER TABLE CM_XT721_P2011JAN_ARC NOPARALLEL LOGGING;
d. CI_TD_SRTKEY
CREATE TABLE CM_XT041_P2011JAN_ARC PARALLEL NOLOGGING TABLESPACE CM_XT039_P2011JAN_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_SRTKEY PARTITION (P2011JAN_S01)
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_SRTKEY PARTITION (P2011JAN_S02)
UNION ALL
.
.
.
.
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_SRTKEY PARTITION (P2011JAN_S08)
);
ALTER TABLE CM_XT041_P2011JAN_ARC NOPARALLEL LOGGING;
e. CI_TD_DRLKEY
CREATE TABLE CM_XT037_P2011JAN_ARC PARALLEL NOLOGGING TABLESPACE CM_XT039_P2011JAN_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_DRLKEY PARTITION (P2011JAN_S01)
UNION ALL
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_DRLKEY PARTITION (P2011JAN_S02)
UNION ALL
.
.
.
.
UNION ALL
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_DRLKEY PARTITION (P2011JAN_S08)
);
ALTER TABLE CM_XT037_P2011JAN_ARC NOPARALLEL LOGGING;
5. Create staging table and load data for parent table.
CREATE TABLE CM_XT039_P2011JAN_ARC NOLOGGING PARALLEL TABLESPACE CM_XT039_P2011JAN_ARC AS
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_ENTRY PARTITION (P2011JAN);
ALTER TABLE CM_XT039_P2011JAN_ARC NOPARALLEL LOGGING;
6. Export tablespace using TRANSPORT_TABLESPACES method.
ALTER TABLESPACE CM_XT039_P2011JAN_ARC READ ONLY;
expdp system/manager DIRECTORY=DUMP_DIR DUMPFILE= CM_XT039_P2011JAN_ARC.DMP TRANSPORT_TABLESPACES = CM_XT039_P2011JAN_ARC LOGFILE=EXP_ CM_XT039_P2011JAN_ARC.LOG TRANSPORT_FULL_CHECK=Y
Make sure tablespace datafile required for further import should be preserved.
<<Transport THE FILE to LOCAL DB DIRECTORY DUMP_DIR like connected to asmcmd and copied the file from cp cm_xt039_p201101_tbs_ar.553.913864937 /tugbu_perf_02/BACKUPS/test_verification/ >>
7. Drop the partition, partition tablespace and archive tablespace(as it is already exported).
ALTER TABLE CISADM.CI_TD_ENTRY DROP PARTITION P2011JAN UPDATE INDEXES;
DROP TABLESPACE CM_XT039_P2011JAN INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE CM_XT039_P2011JAN_ARC INCLUDING CONTENTS AND DATAFILES;