oracle exp 二进制,Oracle备份之exp自动逻辑备份（二）

exp是oracle的逻辑备份，是将数据库中的数据导出到二进制数据库文件。

exp的三种导出方式

1.表方式(T方式)，将指定表的数据导出。

2.用户方式(U方式)，将指定用户的所有对象及数据导出。

3.全库方式(Full方式)，数据库中的所有对象导出。

[oracle@TEST admin]$ cat /backup/exp.sh

#!/bin/sh

export currentTime=`date "+%Y-%m-%d_%H:%M:%S"`

export ORACLE_HOME="/u01/app/oracle/product/11.2.0.4"

export DIR="/backup"

#export serviceName=test

echo

echo -begin-

echo $currentTime

#touch /backup/$currentTime

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

$ORACLE_HOME/bin/exp hscmp/hscmp  owner=\(hscmp, scott\) file=$DIR/$currentTime.dmp log=$DIR/$currentTime.log buffer=104857600

#1>/dev/null 2>&1

pwd

echo "-end-"

exp userid=用户名/密码 owner=\(用户1，用户2\) file=导出路径/文件名.dmp log=导出路径/文件名.log

buffer=数据缓冲区大小

[oracle@TEST backup]$ ./exp.sh

-begin-

2017-01-09_23:10:07

Export: Release 11.2.0.1.0 - Production on Mon Jan 9 23:10:07 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

EXP-00056: ORACLE error 12504 encountered

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

EXP-00000: Export terminated unsuccessfully

/backup

-end-

找不到service_name，因为是本地导出，开启本地监听后，不需要加@servicename

[oracle@TEST admin]$ cat listener.ora

# listener.ora Network Configuration File:

/u01/app/oracle/product/11.2.0.4/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = test)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4)

(SID_NAME = test)

)

)

LISTENER =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.40)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

导出过程中报错

EXP-00091: Exporting questionable statistics.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)可能字符集转换

原来是导出的字符集与oracle的字符集不一样

1、查看database中的NLS_CHARACTERSET的值

SQL> select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET';

PARAMETERVALUE

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

NLS_CHARACTERSETAL32UTF8

SQL> select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET';

PARAMETERVALUE

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

NLS_CHARACTERSET AL32UTF8

2、根据第一步查出来的NLS_CHARACTERSET(即ZHS16GBK)来设定

windows环境：cmd > set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

linux环境：Shell > export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

EXP-00010: READHSCMP is not a valid username

没有readhscmp这个用户 后来添加上scott用户