expdp 简单备份 zabbix用户库

expdp 简单备份 zabbix用户库

新装完的zabbix做个备份，出错恢复不用执行一个个sql啦。。。

[oracle@node3 ~]$ pwd
/home/oracle
[oracle@node3 ~]$ ll
total 56
-rw------- 1 oracle oinstall  100 Jun  7 15:35 .asmcmd_history
-rw------- 1 oracle oinstall 3592 Jun 17 17:57 .bash_history
-rw-r--r-- 1 oracle oinstall 2652 Jun  4 13:20 .bash_profile
-rw-r--r-- 1 oracle oinstall  267 Jun  6 15:05 .bashrc
-rw-r--r-- 1 oracle oinstall  649 Jun  6 13:15 db_env
-rw-r--r-- 1 oracle oinstall  735 Jun  4 13:20 grid_env
-rw------- 1 oracle oinstall   42 Jun  6 13:47 .lesshst
drwxr----- 3 oracle oinstall 4096 Jun  4 18:36 oradiag_oracle/
-rw------- 1 oracle oinstall 1856 Jun 17 16:34 .sqlplus_history
drwx------ 2 oracle oinstall 4096 Jun  4 14:52 .ssh/
-rw------- 1 oracle oinstall 4492 Jun  6 15:07 .viminfo
-rw-r--r-- 1 oracle oinstall   70 Jun  6 15:07 .vimrc
-rw------- 1 oracle oinstall   51 Jun 17 09:22 .Xauthority
[oracle@node3 ~]$ mkdir -p ^C
[oracle@node3 ~]$ ^C
[oracle@node3 ~]$ mkdir -p /home/oracle/expdp
[oracle@node3 ~]$ ll -d expdp/
drwxr-xr-x 2 oracle oinstall 4096 Jun 18 11:35 expdp//
[oracle@node3 ~]$ db_env
momo3
ORACLE_SID= momo3
[oracle@node3 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 18 11:35:37 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE DIRECTORY exp_dir AS '/home/oracle/expdp';
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY exp_dir TO zabbix;
Grant succeeded.
SQL>

[oracle@node3 ~]$ expdp zabbix/111111 DIRECTORY=exp_dir SCHEMAS=zabbix logfile=zabbix_20130618.log dumpfile=zabbix_20130618.dmp
Export: Release 11.2.0.3.0 - Production on Tue Jun 18 11:40:03 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
Starting "ZABBIX"."SYS_EXPORT_SCHEMA_01":  zabbix/******** DIRECTORY=exp_dir SCHEMAS=zabbix logfile=zabbix_20130618.log dumpfile=zabbix_20130618.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5.812 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "ZABBIX"."IMAGES"                           972.3 KB     187 rows
. . exported "ZABBIX"."ITEMS"                            238.4 KB     586 rows
. . exported "ZABBIX"."IDS"                              6.492 KB       4 rows
. . exported "ZABBIX"."HELP_ITEMS"                       43.12 KB     149 rows
. . exported "ZABBIX"."TRIGGERS"                         50.20 KB     257 rows
. . exported "ZABBIX"."ACTIONS"                          10.98 KB       2 rows
. . exported "ZABBIX"."APPLICATIONS"                     10.46 KB     128 rows
. . exported "ZABBIX"."AUDITLOG"                         8.554 KB       1 rows
. . exported "ZABBIX"."AUTOREG_HOST"                     7.375 KB       3 rows
. . exported "ZABBIX"."CONDITIONS"                       6.906 KB       5 rows
. . exported "ZABBIX"."CONFIG"                           25.07 KB       1 rows
. . exported "ZABBIX"."DCHECKS"                          9.398 KB       1 rows
. . exported "ZABBIX"."DRULES"                           7.726 KB       1 rows
. . exported "ZABBIX"."EVENTS"                           9.796 KB      38 rows
. . exported "ZABBIX"."EXPRESSIONS"                      7.671 KB       4 rows
. . exported "ZABBIX"."FUNCTIONS"                        15.46 KB     281 rows
. . exported "ZABBIX"."GLOBALMACRO"                      5.929 KB       1 rows
. . exported "ZABBIX"."GRAPHS"                           20.02 KB      81 rows
. . exported "ZABBIX"."GRAPHS_ITEMS"                     19.33 KB     262 rows
. . exported "ZABBIX"."GRAPH_THEME"                      12.86 KB       8 rows
. . exported "ZABBIX"."GROUPS"                           6.046 KB       4 rows
. . exported "ZABBIX"."HOSTS"                            21.21 KB      24 rows
. . exported "ZABBIX"."HOSTS_GROUPS"                     6.296 KB      24 rows
. . exported "ZABBIX"."HOSTS_TEMPLATES"                  6.296 KB      20 rows
. . exported "ZABBIX"."INTERFACE"                        8.093 KB       1 rows
. . exported "ZABBIX"."ITEMS_APPLICATIONS"               17.54 KB     702 rows
. . exported "ZABBIX"."ITEM_DISCOVERY"                   9.671 KB     109 rows
. . exported "ZABBIX"."MAPPINGS"                         7.710 KB      48 rows
. . exported "ZABBIX"."MEDIA_TYPE"                       9.476 KB       3 rows
. . exported "ZABBIX"."OPERATIONS"                       7.812 KB       3 rows
. . exported "ZABBIX"."OPGROUP"                          5.960 KB       1 rows
. . exported "ZABBIX"."OPMESSAGE"                        7.507 KB       1 rows
. . exported "ZABBIX"."OPMESSAGE_GRP"                    5.976 KB       1 rows
. . exported "ZABBIX"."OPTEMPLATE"                       5.968 KB       1 rows
. . exported "ZABBIX"."PROFILES"                         9.273 KB      11 rows
. . exported "ZABBIX"."REGEXPS"                          6.109 KB       3 rows
. . exported "ZABBIX"."SCREENS"                          7.265 KB       9 rows
. . exported "ZABBIX"."SCREENS_ITEMS"                    13.28 KB      24 rows
. . exported "ZABBIX"."SCRIPTS"                          9.265 KB       3 rows
. . exported "ZABBIX"."SESSIONS"                         6.437 KB       1 rows
. . exported "ZABBIX"."SYSMAPS"                          16.71 KB       1 rows
. . exported "ZABBIX"."SYSMAPS_ELEMENTS"                 12.75 KB       1 rows
. . exported "ZABBIX"."TRIGGER_DEPENDS"                  6.617 KB      39 rows
. . exported "ZABBIX"."USERS"                            11.78 KB       2 rows
. . exported "ZABBIX"."USERS_GROUPS"                     5.968 KB       2 rows
. . exported "ZABBIX"."USER_HISTORY"                     9.843 KB       1 rows
. . exported "ZABBIX"."USRGRP"                           7.023 KB       5 rows
. . exported "ZABBIX"."VALUEMAPS"                        6.093 KB      11 rows
. . exported "ZABBIX"."ACKNOWLEDGES"                         0 KB       0 rows
. . exported "ZABBIX"."ALERTS"                               0 KB       0 rows
. . exported "ZABBIX"."AUDITLOG_DETAILS"                     0 KB       0 rows
. . exported "ZABBIX"."DHOSTS"                               0 KB       0 rows
. . exported "ZABBIX"."DSERVICES"                            0 KB       0 rows
. . exported "ZABBIX"."ESCALATIONS"                          0 KB       0 rows
. . exported "ZABBIX"."GLOBALVARS"                           0 KB       0 rows
. . exported "ZABBIX"."GRAPH_DISCOVERY"                      0 KB       0 rows
. . exported "ZABBIX"."HISTORY"                              0 KB       0 rows
. . exported "ZABBIX"."HISTORY_LOG"                          0 KB       0 rows
. . exported "ZABBIX"."HISTORY_STR"                          0 KB       0 rows
. . exported "ZABBIX"."HISTORY_STR_SYNC"                     0 KB       0 rows
. . exported "ZABBIX"."HISTORY_SYNC"                         0 KB       0 rows
. . exported "ZABBIX"."HISTORY_TEXT"                         0 KB       0 rows
. . exported "ZABBIX"."HISTORY_UINT"                         0 KB       0 rows
. . exported "ZABBIX"."HISTORY_UINT_SYNC"                    0 KB       0 rows
. . exported "ZABBIX"."HOSTMACRO"                            0 KB       0 rows
. . exported "ZABBIX"."HOST_INVENTORY"                       0 KB       0 rows
. . exported "ZABBIX"."HOUSEKEEPER"                          0 KB       0 rows
. . exported "ZABBIX"."HTTPSTEP"                             0 KB       0 rows
. . exported "ZABBIX"."HTTPSTEPITEM"                         0 KB       0 rows
. . exported "ZABBIX"."HTTPTEST"                             0 KB       0 rows
. . exported "ZABBIX"."HTTPTESTITEM"                         0 KB       0 rows
. . exported "ZABBIX"."ICON_MAP"                             0 KB       0 rows
. . exported "ZABBIX"."ICON_MAPPING"                         0 KB       0 rows
. . exported "ZABBIX"."MAINTENANCES"                         0 KB       0 rows
. . exported "ZABBIX"."MAINTENANCES_GROUPS"                  0 KB       0 rows
. . exported "ZABBIX"."MAINTENANCES_HOSTS"                   0 KB       0 rows
. . exported "ZABBIX"."MAINTENANCES_WINDOWS"                 0 KB       0 rows
. . exported "ZABBIX"."MEDIA"                                0 KB       0 rows
. . exported "ZABBIX"."NODES"                                0 KB       0 rows
. . exported "ZABBIX"."NODE_CKSUM"                           0 KB       0 rows
. . exported "ZABBIX"."OPCOMMAND"                            0 KB       0 rows
. . exported "ZABBIX"."OPCOMMAND_GRP"                        0 KB       0 rows
. . exported "ZABBIX"."OPCOMMAND_HST"                        0 KB       0 rows
. . exported "ZABBIX"."OPCONDITIONS"                         0 KB       0 rows
. . exported "ZABBIX"."OPMESSAGE_USR"                        0 KB       0 rows
. . exported "ZABBIX"."PROXY_AUTOREG_HOST"                   0 KB       0 rows
. . exported "ZABBIX"."PROXY_DHISTORY"                       0 KB       0 rows
. . exported "ZABBIX"."PROXY_HISTORY"                        0 KB       0 rows
. . exported "ZABBIX"."RIGHTS"                               0 KB       0 rows
. . exported "ZABBIX"."SERVICES"                             0 KB       0 rows
. . exported "ZABBIX"."SERVICES_LINKS"                       0 KB       0 rows
. . exported "ZABBIX"."SERVICES_TIMES"                       0 KB       0 rows
. . exported "ZABBIX"."SERVICE_ALARMS"                       0 KB       0 rows
. . exported "ZABBIX"."SLIDES"                               0 KB       0 rows
. . exported "ZABBIX"."SLIDESHOWS"                           0 KB       0 rows
. . exported "ZABBIX"."SYSMAPS_LINKS"                        0 KB       0 rows
. . exported "ZABBIX"."SYSMAPS_LINK_TRIGGERS"                0 KB       0 rows
. . exported "ZABBIX"."SYSMAP_ELEMENT_URL"                   0 KB       0 rows
. . exported "ZABBIX"."SYSMAP_URL"                           0 KB       0 rows
. . exported "ZABBIX"."TIMEPERIODS"                          0 KB       0 rows
. . exported "ZABBIX"."TRENDS"                               0 KB       0 rows
. . exported "ZABBIX"."TRENDS_UINT"                          0 KB       0 rows
. . exported "ZABBIX"."TRIGGER_DISCOVERY"                    0 KB       0 rows
Master table "ZABBIX"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ZABBIX.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/expdp/zabbix_20130618.dmp
Job "ZABBIX"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:42:57
[oracle@node3 ~]$

[oracle@node3 ~]$ ls -hl expdp/
total 4.8M
-rw-r----- 1 oracle oinstall 4.8M Jun 18 11:42 zabbix_20130618.dmp
-rw-r--r-- 1 oracle oinstall 9.7K Jun 18 11:42 zabbix_20130618.log
[oracle@node3 ~]$
[oracle@node3 ~]$

查看帮助

[oracle@node3 ~]$ expdp help=y
Export: Release 11.2.0.3.0 - Production on Tue Jun 18 11:36:47 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:
   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
------------------------------------------------------------------------------
The available keywords and their descriptions follow. Default values are listed within square brackets.
ATTACH
Attach to an existing job.
For example, ATTACH=job_name.
CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC.
Valid keyword values are: [Y] and N.
COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS
Data layer option flags.
Valid keyword values are: XML_CLOBS.
DIRECTORY
Directory object to be used for dump and log files.
DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.
ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.
ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].
ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.
ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.
ESTIMATE_ONLY
Calculate job estimates without performing the export.
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".
FILESIZE
Specify the size of each dump file in units of bytes.
FLASHBACK_SCN
SCN used to reset session snapshot.
FLASHBACK_TIME
Time used to find the closest corresponding SCN value.
FULL
Export entire database [N].
HELP
Display Help messages [N].
INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.
JOB_NAME
Name of export job to create.
LOGFILE
Specify log file name [export.log].
NETWORK_LINK
Name of remote database link to the source system.
NOLOGFILE
Do not write log file [N].
PARALLEL
Change the number of active workers for current job.
PARFILE
Specify parameter file name.
QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".
REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].
SAMPLE
Percentage of data to be exported.
SCHEMAS
List of schemas to export [login schema].
SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.
SOURCE_EDITION
Edition to be used for extracting metadata.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES
Identifies a list of tablespaces to export.
TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].
TRANSPORT_FULL_CHECK
Verify storage segments of all tables [N].
TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.
VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.
------------------------------------------------------------------------------
The following commands are valid while in interactive mode.
Note: abbreviations are allowed.
ADD_FILE
Add dumpfile to dumpfile set.
CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT
Quit client session and leave job running.
FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.
HELP
Summarize interactive commands.
KILL_JOB
Detach and delete job.
PARALLEL
Change the number of active workers for current job.
REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].
START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.
[oracle@node3 ~]$