Oracle In Depth: Backup and Recovery

Showing posts with label Backup and Recovery. Show all posts

Friday, April 4, 2025

Wednesday, April 4, 2018

Refresh using datapump

Refresh using datapump

Schema Level

impdp system/***@<service_name> directory=EXPORT_DIR2 dumpfile=<dump_files_names>.dmp logfile=<log_file_name>.log schemas=odb parallel=4

--- If Dumpfiles locations are different at os level

impdp odb/Ke45LILP@traxmt dumpfile=TEST:TRAXMP_EXP_BKP_2014-07-02_%U.dmp,EXPORT_DIR2:TRAXMP_EXP_BKP_2014-11-24_04.dmp logfile=traxmp_traxmt_today.log schemas=odb parallel=4

Table Level

expdp directory=EXPORT dumpfile=WO_TASK_CARD_200315.dmp logfile=WO_TASK_CARD_200315.log parallel=4 tables=odb.WO_TASK_CARD flashback_time=systimestamp

Export using select option

expdp system/carrier@traxmisu schemas=ODB INCLUDE=TABLE,VIEW,PACKAGE,PROCEDURE,INDEX:"LIKE'MWH_%'" estimate_only=y

expdp system/carrier@traxmisu schemas=ODB INCLUDE=TABLE,VIEW,PACKAGE,PROCEDURE,INDEX:"IN (select object_Name from dba_objects where object_name like 'MWH%')" estimate_only=y

select object_Name from dba_objects where object_name like 'MWH%'

expdp DADM/passwd DIRECTORY=DMP_DIR SCHEMAS=DADM DUMPFILE=dadm_data.dmp

CONTENT=DATA_ONLY include=TABLE:"IN (SELECT tbl_name FROM list_of_tables)"

expdp system/carrier@traxmisu schemas=ODB INCLUDE=TABLE:"IN$select object_Name from dba_objects where object_name like'MWH%'$" estimate_only=y

expdp system/carrier@traxmisu schemas=ODB INCLUDE=TABLE:"LIKE'MWH_%'" directory=EXPORT_DIR1 dumpfile=TRAXMISU_MWHT.dmp logfile=TRAXMISU_MWHT.log

impdp system/carrier@dlyfrmut schemas=ODB directory=EXPORT dumpfile=DLYFRMUT_MWHT.dmp logfile=DLYFRMUT_MWHT.log

expdp system/carrier@traxmisu schemas=ODB INCLUDE=PACKAGE:"LIKE'MWH_%'" estimate_only=y

impdp system/carrier@dlyfrmut schemas=ODB directory=EXPORT dumpfile=TRAXMISU_MWHT.dmp logfile=DLYFRMUT_IMP.log TABLE_EXISTS_ACTION=REPLACE

Attaching and Detaching a datapump Job

you can deattach or reattch datapump job.

1.) Get the Job name

desc dba_datapump_jobs

Jetstar_SYS@traxmp > select owner_name,Job_name,state from dba_datapump_jobs

OWNER_NAME JOB_NAME STATE

------------------------------ ------------------------------ ------------------------------

SYSTEM SYS_EXPORT_SCHEMA_09 EXECUTING

SYSTEM SYS_EXPORT_SCHEMA_10 EXECUTING

SYSTEM SYS_EXPORT_SCHEMA_08 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_05 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_03 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_01 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_07 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_04 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_02 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_06 NOT RUNNING

2.) Attach the job using attach option

expdp system/carrier attach=SYS_EXPORT_SCHEMA_09

3.) We can check status,Kill job and resume it.

> staus

> KILL_JOB

Datapump Job Process

ps -ef | grep expdp

ps -ef | grep ora_dm* master

ps -ef | grep ora_dw* worker ( paralle 4 then worker 4 )

desc dba_datapump_jobs

Jetstar_SYS@traxmp > /

OWNER_NAME JOB_NAME STATE

------------------------------ ------------------------------ ------------------------------

SYSTEM SYS_EXPORT_SCHEMA_09 EXECUTING

SYSTEM SYS_EXPORT_SCHEMA_10 EXECUTING

SYSTEM SYS_EXPORT_SCHEMA_08 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_05 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_03 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_01 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_07 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_04 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_02 NOT RUNNING

SYSTEM SYS_EXPORT_SCHEMA_06 NOT RUNNING

10 rows selected.

Jetstar_SYS@prod > !date

Wed Jul 5 01:03:11 GMT 2017

Jetstar_SYS@prod >

[oracle@meldc1trx01 ~]$ ps -ef | grep ora_dw*

oracle 15561 1 0 Jul02 ? 00:00:17 ora_dm00_traxmp

oracle 15573 1 0 Jul02 ? 00:03:23 ora_dw00_traxmp

oracle 15589 1 0 Jul02 ? 00:14:34 ora_dw01_traxmp

oracle 15593 1 0 Jul02 ? 00:12:39 ora_dw02_traxmp

oracle 15598 1 0 Jul02 ? 00:06:08 ora_dw03_traxmp

oracle 18542 1 0 Feb13 ? 01:16:00 ora_diag_traxmp

oracle 18546 1 0 Feb13 ? 00:05:04 ora_dbrm_traxmp

oracle 18550 1 0 Feb13 ? 11:31:01 ora_dia0_traxmp

oracle 18558 1 0 Feb13 ? 11:25:56 ora_dbw0_traxmp

oracle 18586 1 0 Feb13 ? 00:00:04 ora_d000_traxmp

oracle 18823 1 0 Jul04 ? 00:00:07 ora_dm0h_traxmp

oracle 18838 1 0 Jul04 ? 00:03:39 ora_dw06_traxmp

oracle 18851 1 2 Jul04 ? 00:16:06 ora_dw07_traxmp

oracle 18855 1 1 Jul04 ? 00:11:32 ora_dw05_traxmp

oracle 18859 1 1 Jul04 ? 00:08:44 ora_dw04_traxmp

oracle 22358 22253 0 01:02 pts/3 00:00:00 grep ora_dw*

You have new mail in /var/spool/mail/oracle

Using EXCLUDE while importing

For PROD lower environment refresh Please use below updated import command

impdp odb/<password>@<Instance_nanme> directory=EXPORT_DIR2 dumpfile=<dump_file>.dmp logfile=<log_file>.log

schemas=odb EXCLUDE=INDEX:\"=\'I_PRODDOC_CONTENT\'\"

Once all the objects get imported,run below sqls to crate the index manaully

##make sure you are connected as ODB user.

EXECUTE CTX_DDL.DROP_PREFERENCE('PRODDOC_DETAIL_FILTER');

EXECUTE CTX_DDL.CREATE_PREFERENCE('PRODDOC_DETAIL_FILTER','INSO_FILTER');

EXECUTE CTX_DDL.DROP_STOPLIST('PRODDOC_DETAIL_STOPLIST');

EXECUTE CTX_DDL.CREATE_STOPLIST('PRODDOC_DETAIL_STOPLIST');

DROP INDEX I_PRODDOC_CONTENT;

DROP INDEX I_PRODDOC_CONTENT FORCE;

CREATE INDEX I_PRODDOC_CONTENT ON "PRODDOC_DETAIL"(DOCUMENT_CONTENT)

INDEXTYPE IS CTXSYS.CONTEXT

PARAMETERS('FILTER PRODDOC_DETAIL_FILTER STOPLIST PRODDOC_DETAIL_STOPLIST');

EXECUTE CTX_DDL.SYNC_INDEX('I_PRODDOC_CONTENT');

Note:

issue:

If index creation throws error like below

ERROR at line 1:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-20000: Oracle Text error:

DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER

ORA-06512: at "CTXSYS.DRUE", line 160

ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

solutions:

follow below steps to fix it

Jetstar_ODB@PRODjjt > conn ctxsys/ctxsys

connected

Jetstar_CTXSYS@PRODjjt > $ORACLE_HOME/ctx/admin/defaults/drdefxx

Jetstar_CTXSYS@PRODjjt > @$ORACLE_HOME/ctx/admin/defaults/drdefgb.sql;

Creating lexer preference...

and create the index I_PRODDOC_CONTENT.

1. you can deattach or reattch datapump job.
2. you can parallelise datapump job [PARALLEL=4 & %U]
3. without export can do import using NETWORK_LINK parameter.
4. can calculate export dump file size using ESIMATE_ONLY.
5. expdp utility used which is caled by dbms_datapump procedure
6. remapping capabilities during import [REMAP_DATAFILE,EMAP_SCHEMA,REMAP_TABLESPACE,REUSE_DATAFILES]
7. TABLE_EXISTS_ACTION=replace/truncate while importing.

exp help=y

A good place to start is by identifying the most frequent data pump export command line parameters:

PARAMETER	DESCRIPTION
ATTACH	[SCHEMA.]JOB_NAME Name of an already existing and executing job to connect to. Need EXP_FULL_DATABASE privilege for other schemas
COMPRESSION	META_DATA_ONLY \| ALL \| NONE \| DATA_ONLY Compress the dump file contents for the specified criteria
CONSISTENT	Y\|N Starting in release 11.2, this option gives a read consistent backup, just like flachback_time
CONTENT	ALL \| META_DATA_ONLY \| DATA_ONLY Filter the export of dump file contents to the specified criteria
DIRECTORY	DATA_PUMP_DIR \| DIRECTORY_NAME Name of directory object pointing to a valid server directory
DUMPFILE	[DIRECTORY_NAME:]FILE_NAME [, ...] The name (and optionally the directory) of the export data file
ESTIMATE	N \| Y Do not export, but rather just estimate the disk space required
ESTIMATE_ONLY	BLOCKS, STATISTICS Method export uses to calculate the disk space for data only
EXCLUDE	OBJECT_TYPE[:NAME_FILTER_EXPRESSION] [, ...] Database object types as a whole or by object name filter to specifically exclude from the export
FILESIZE	INTEGER [B \| K \| M \| G] The maximum file size permitted for any export dump file
FLASHBACK_TIME	SYSTIMESTAMP \| DATE Allows a read consistent export in expdp. See these notes on using flashback time.
FULL	N \| Y Whether to perform a full database export or not Requires EXP_FULL_DATABASE privilege
INCLUDE	OBJECT_TYPE[:NAME_FILTER_EXPRESSION] [, ...] Database object types as a whole or by object name filter to specifically include in the export
JOB_NAME	SYS_EXPORT_<mode>_NN \| JOB_NAME Name by which export job can be referenced (e.g. ATTACH)
LOGFILE	EXPORT.LOG \| [DIRECTORY_NAME:]FILE_NAME The name (and optionally the directory) of the export log file
NOLOGFILE	N \| Y Whether or not to suppress creation of the export log file
PARALLEL	1 \| INTEGER The maximum number of concurrent threads for the export
PARFILE	[DIRECTORY_SPECIFICATION]FILE_NAME Name of the operating system specific parameter file
QUERY	[[SCHEMA.]TABLE_NAME:] FILTER_EXPRESSION Data filter condition applied to all tables or by schema and object name filters during the export
REUSE_DUMPFILES	N \| Y Whether or not to overwrite pre-existing export dump files
SAMPLE	[[SCHEMA.]TABLE_NAME]N, where .000001>=N<100 Probability that a data block of rows will be included in the export's sampling of the data (i.e. subset)
SCHEMAS	SCHEMA [, ...] The schema or schemas to export Need EXP_FULL_DATABASE privilege for other schemas
STATUS	0 \| INTEGER The frequency in seconds which job displays client feedback
TABLES	[SCHEMA.]TABLE_NAME[:PARTITION_NAME] [, ...] List of tables for a table mode database export Restricted to a single schema
TABLESPACES	TABLESPACE_NAME [, ...] List of tablespaces for a tablespace mode database export
TRANSPORT_FULL_CHECK	N \| Y Whether or not dependencies verified between transportable tablespace objects in the transport set to those outside the set
TRANSPORT_TABLESAPCES	TABLESPACE_NAME [, ...] List of tablespaces for a transportable tablespace mode database export (target database version >= source version)

Moreover, to use the stop and restart data pump job capabilities, run data pump in interactive mode so as to get the data pump prompt. Then the following commands are also quite frequently useful:

PARAMETER	DESCRIPTION
CONTINUE_CLIENT	Connect client to currently executing job or restarts the job, and resumes logging mode (i.e. status output)
EXIT_CLIENT	Disconnect client connection to currently executing job and terminate the client process, but leave server job running
KILL_JOB	Detach all client processes connected to this data pump job and then terminate (i.e. kill) the currently running job
START_JOB	Start or resume the current data pump job
STOP_JOB	[IMMEDIATE] Detach all client processes connected to this data pump job and then orderly shutdown the currently running job

Oracle In Depth

Menu