Crontab export data pump (expdp)
Today i´m going to tell you how to perform an export data pump launched from crontab. If you has a database in noarchivelog mode an do not want to shutdown it first, in order to make and rman backup it can be a good solution to you.
However, if you want to backup your database with rman without shutdown it, you´ll have to activate archivelog mode first see this article: activate archivelog mode on database
First step to perform an expdp is to select directory in whitch we are going to locate this export:
SQL> set linesize 190 col OWNER for a15 col DIRECTORY_NAME for a35 col DIRECTORY_PATH for a70 select OWNER, DIRECTORY_NAME, DIRECTORY_PATH from dba_directories;SQL> SQL> SQL> SQL> OWNER DIRECTORY_NAME DIRECTORY_PATH --------------- ----------------------------------- ---------------------------------------------------------------------- SYS ORACLE_HOME / SYS ORACLE_BASE / SYS OPATCH_LOG_DIR /oracle/db/ohome/QOpatch SYS OPATCH_SCRIPT_DIR /oracle/db/ohome/QOpatch SYS XSDDIR /oracle/db/ohome/rdbms/xml/schema SYS DATA_PUMP_DIR /oracle/db/admin/orcl/dpdump/ SYS ORACLE_OCM_CONFIG_DIR /oracle/db/ohome/ccr/hosts/wcp12cr2/state SYS ORACLE_OCM_CONFIG_DIR2 /oracle/db/ohome/ccr/state SYS XMLDIR /oracle/db/ohome/rdbms/xml 9 rows selected.
as we can see, we can locate it in any of the above, but if we want to locate it in another one we can create it by:
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
This is the script i launched to perform a full database backup (as you see is for bash shell, but i think it will work on sh and ksh):
#!/bin/bash
fecha=`date '+%d%m%Y_%H%M%S'`
export ORACLE_BASE=/sgbd/oracle11
export ORACLE_HOME=/sgbd/oracle11/product/11.2.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$PATH:$ORACLE_HOME/bin
expdp \'/ as sysdba\' dumpfile=expdp_${ORACLE_SID}_${fecha}.dmp directory=DATA_PUMP_DIR logfile=expdp_${ORACLE_SID}_${fecha}.log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_${ORACLE_SID}
as you can see we set some parameters as
DIRECTORY Directory object to be used for dumpfiles and logfiles. FULL Export entire database or not. Default N. DUMPFILE List of destination dump files Default (expdat.dmp), LOGFILE Log file name default(export.log). EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP CONTENT Specifies data to unload where the valid keywords are:(ALL), DATA_ONLY, and METADATA_ONLY. JOB_NAME Name of export job to create
but you can get a full list of parameter by typing in your shell
expdp help=y
Then once you have decided where to place your full backup you need to store this script in a file and make it executable:
[oracle@wcp12cr2 Documents]$ cat expdp_full.sh
#!/bin/bash
fecha=`date '+%d%m%Y_%H%M%S'`
export ORACLE_BASE=/oracle/db
export ORACLE_HOME=/oracle/db/ohome
export ORACLE_SID=ORCL
export PATH=$PATH:$ORACLE_HOME/bin
expdp \'/ as sysdba\' dumpfile=expdp_${ORACLE_SID}_${fecha}.dmp directory=DATA_PUMP_DIR logfile=expdp_${ORACLE_SID}_${fecha}.log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_${ORACLE_SID}
and give expdp_full.sh execution rights:[oracle@wcp12cr2 Documents]$ chmod 750 expdp_full.sh [oracle@wcp12cr2 Documents]$ ls -ltr total 4 -rwxr-x---. 1 oracle oinstall 394 Sep 27 06:01 expdp_full.sh
i recommend you before schedule this script into crontab run it first but with SAMPLE=1 parameter(this parameter is avaiable since oracle 10.2), this way you can test the script with a few data and also schedule a test execution in crontab to check all is working properly. Once you are sure that all is working Ok, you can change it back.
[oracle@wcp12cr2 Documents]$ ./expdp_full.sh Export: Release 12.1.0.1.0 - Production on Wed Sep 27 06:13:37 2017 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."FULL_EXPDP_ORCL": "/******** AS SYSDBA" dumpfile=expdp_orcl_27092017_061337.dmp SAMPLE=1 directory=DATA_PUMP_DIR logfile=expdp_orcl_27092017_061337.log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_orcl Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA .............................................................................................. .............................................................................................. Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER . . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.320 KB 48 rows . . exported "ORDDATA"."ORDDCM_DOCS" 252.9 KB 9 rows . . exported "SYS"."AUD$" 26.85 KB 36 rows . . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.734 KB 8 rows . . exported "LBACSYS"."OLS$DIP_EVENTS" 5.515 KB 2 rows . . exported "LBACSYS"."OLS$INSTALLATIONS" 6.937 KB 2 rows . . exported "LBACSYS"."OLS$PROPS" 6.210 KB 5 rows . . exported "SYS"."DAM_CONFIG_PARAM$" 6.507 KB 14 rows . . exported "SYS"."TSDP_PARAMETER$" 5.929 KB 1 rows . . exported "SYS"."TSDP_POLICY$" 5.898 KB 1 rows . . exported "SYS"."TSDP_SUBPOL$" 6.304 KB 1 rows . . exported "SYSTEM"."REDO_DB" 23.42 KB 1 rows . . exported "WMSYS"."WM$ENV_VARS$" 6.054 KB 5 rows . . exported "WMSYS"."WM$EVENTS_INFO$" 5.789 KB 12 rows . . exported "WMSYS"."WM$HINT_TABLE$" 9.429 KB 75 rows . . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.351 KB 1 rows . . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.960 KB 1 rows . . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.08 KB 1 rows . . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 6.539 KB 8 rows . . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows .............................................................................................. .............................................................................................. Master table "SYS"."FULL_EXPDP_ORCL" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.FULL_EXPDP_ORCL is: /oracle/db/admin/orcl/dpdump/expdp_orcl_27092017_061337.dmp Job "SYS"."FULL_EXPDP_ORCL" successfully completed at Wed Sep 27 06:17:53 2017 elapsed 0 00:04:11and finally schedule in crontab adding this line to the crontab by (crontab can be edited typing "crontab -e" in your shell:
51 18 * * * /home/oracle/Documents/expdp_full.sh >>/home/oracle/Documents/expdp_full.log 2>>/home/oracle/Documents/expdp_full.errThis example will execute every day, at 18:51, normal output will write on expdp_full.log and any error in expdp_full.err
If you have any doubt about this article let me a comment and i´ll answer you as fast as i can...
Comments
Post a Comment