oracle+

Oracle资料大集合

分隔符要与ctl文件中fields terminated by指定的一致，这个例子中为"|" 
ctl和dat文件就绪后可以执行上载，命令为： 
sqlldr dbuser/oracle control=emp.ctl data=emp.dat 
也可以将dat文件合并在ctl文件中，ctl文件改写为： 
emp2.ctl 
load data 
infile * 
append 
into table emp 
fields terminated by '|' 
( 
no float external, 
name char(20), 
age integer external, 
duty char(1), 
salary float external, 
upd_ts date(14) 'YYYYMMDDHH24MISS' 
) 
begindata 
100000000003|Mulder|000020|1|000000005000|20020101000000 
100000000004|Scully|000025|2|000000008000|20020101235959 
控制文件中infile选项跟sqlldr命令行中data选项含义相同，如使用infile *则表明数据在本控制文件以begin data开头的区域内。 
这样命令变成： 
sqlldr dbuser/oracle control=emp2.ctl 
conventional path 
通过常规通道方式上载。 
rows：每次提交的记录数 
bindsize：每次提交记录的缓冲区 
readsize：与bindsize成对使用，其中较小者会自动调整到较大者 
sqlldr先计算单条记录长度，乘以rows，如小于bindsize，不会试图扩张rows以填充bindsize；如超出，则以bindsize为准。 
命令为： 
sqlldr dbuser/oracle control=emp.ctl log=emp.log rows=10000 bindsize=8192000 
direct path 
通过直通方式上载，不进行SQL解析。 
命令为： 
sqlldr dbuser/oracle control=emp.ctl log=emp.log direct=true 
4.2 exp 
参见dmp/exp_demo.sh。 
将数据库内的各对象以二进制方式下载成dmp文件，方便数据迁移。 
buffer：下载数据缓冲区，以字节为单位，缺省依赖操作系统 
consistent：下载期间所涉及的数据保持read only，缺省为n 
direct：使用直通方式 ，缺省为n 
feeback：显示处理记录条数，缺省为0，即不显示 
file：输出文件，缺省为expdat.dmp 
filesize：输出文件大小，缺省为操作系统最大值 
indexes：是否下载索引，缺省为n，这是指索引的定义而非数据，exp不下载索引数据 
log：log文件，缺省为无，在标准输出显示 
owner：指明下载的用户名 
query：选择记录的一个子集 
rows：是否下载表记录 
tables：输出的表名列表 
下载整个实例 
exp dbuser/oracle file=oradb.dmp log=oradb.log full=y consistent=y direct=y 
user应具有dba权限 
下载某个用户所有对象 
exp dbuser/oracle file=dbuser.dmp log=dbuser.log owner=dbuser buffer=4096000 feedback=10000 
下载一张或几张表 
exp dbuser/oracle file=dbuser.dmp log=dbuser.log tables=table1,table2 buffer=4096000 feedback=10000 
下载某张表的部分数据 
exp dbuser/oracle file=dbuser.dmp log=dbuser.log tables=table1 buffer=4096000 feedback=10000 query=\"where col1=\'…\' and col2 \<…\" 
不可用于嵌套表 
以多个固定大小文件方式下载某张表 
exp dbuser/oracle file=1.dmp,2.dmp,3.dmp,… filesize=1000m tables=emp buffer=4096000 feedback=10000 
这种做法通常用在：表数据量较大，单个dump文件可能会超出文件系统的限制 
直通路径方式 
direct=y，取代buffer选项，query选项不可用 
有利于提高下载速度 
consistent选项 
自export启动后，consistent=y冻结来自其它会话的对export操作的数据对象的更新，这样可以保证dump结果的一致性。但这个过程不能太长，以免回滚段和联机日志消耗完 
4.3 imp 
参见dmp/imp_demo.sh。 
将exp下载的dmp文件上载到数据库内。 
buffer：上载数据缓冲区，以字节为单位，缺省依赖操作系统 
commit：上载数据缓冲区中的记录上载后是否执行提交 
feeback：显示处理记录条数，缺省为0，即不显示 
file：输入文件，缺省为expdat.dmp 
filesize：输入文件大小，缺省为操作系统最大值 
fromuser：指明来源用户方 
ignore：是否忽略对象创建错误，缺省为n，在上载前对象已被建立往往是一个正常现象，所以此选项建议设为y 
indexes：是否上载索引，缺省为n，这是指索引的定义而非数据，如果上载时索引已建立，此选项即使为n也无效，imp自动更新索引数据 
log：log文件，缺省为无，在标准输出显示 
rows：是否上载表记录 
tables：输入的表名列表 
touser：指明目的用户方 
上载整个实例 
imp dbuser/oracle file=oradb.dmp log=oradb.log full=y buffer=4096000 commit=y ignore=y feedback=10000 
上载某个用户所有对象 
imp dbuser/oracle file=dbuser.dmp log=dbuser.log fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000 
上载一张或几张表 
imp dbuser2/oracle file=user.dmp log=user.log tables=table1,table2 fromuser=dbuser touser=dbuser2 buffer=2048000 commit=y ignore=y feedback=10000 
以多个固定大小文件方式上载某张表 
imp dbuser/oracle file=\(1.dmp,2.dmp,3.dmp,…\) filesize=1000m tables=emp fromuser=dbuser touser=dbuser2 buffer=4096000 commit=y ignore=y feedback=10000 
4.4 sqlplus 
参见sqlplus/download.sh。 
仅列出常用的选项，对复杂的应用不作深究 
4.4.1 命令行参数 
/ as {sysdba|sysopr}：使用操作系统用户验证，以osdba或osopr一员的身份登录，如验证通过，被赋予sysdba或sysopr的权限 
使用格式：sqlplus "/ as sysdba" 
/nolog：不执行connect操作，直接进入sqlplus操作界面 
-s：silent模式，不显示sqlplus启动信息和提示符 
<：接受sql脚本从标准输入重定向 
<<：立即文档 
4.4.2 提示符命令 
accept variable [number|char|date] [format format] [default default] [prompt text] [hide]：接受输入变量 
例子：accept pwd char format a8 prompt 'Password:' hide 
column column [format format] [heading heading]：设定对某个域的显示格式 
如果要同时改变某域的输出长度和标题，必须使用column命令 
见emp的定义，name本为char(20)，输出缩为10位，duty本为 char(1)，扩张为6位，以便有足够的空间显示中文标题。 
SQL>column name format a10 heading '姓名'; 
SQL>column duty format a6 heading '职位'; 
SQL>column age format 999999 heading '年龄'; 
SQL>column upd_ts format a14 heading '更新时间'; 
SQL>select name,duty,age,upd_ts from emp; 
show option：显示SET的选项 
spool [filename|off]：输出重定向文件 
timing [start text|show|stop]：定时器 
4.4.3 SET选项 
autocommit：自动提交insert、update、delete带来的记录改变，缺省为off 
colsep：域输出分隔符 
define：识别命令中的变量前缀符，缺省为on，也就是'&'，碰到变量前缀符，后面的字符串作为变量处理 
如果待更新内容包含'&'（在URL中很常见），而define非设为off，sqlplus会把'&'后面紧跟的字符串当成变量，提示输入，这里必须重新输入'&'和那个字符串，才能实现正常更新。将define设为off，就不再进行变量判断。 
SQL>set define off; 
SQL>update bbs_forum set url='http://www.xxx.com/bbs/show.php&forum_id=1' where forum_id=1; 
echo：显示start启动的脚本中的每个sql命令，缺省为on 
feedback：回显本次sql命令处理的记录条数，缺省为on 
heading：输出域标题，缺省为on 
linesize：输出一行字符个数，缺省为80 
如果一行输出超过linesize，会回车到第二行，这样格式就会混乱。 
markup html：html格式输出，缺省为off 
通常需要与spool配合，否则html输出就没有意义。 
numwidth：输出number类型域长度，缺省为10 
长number类型的域常常因为输出长度的问题，引起误会。 
pagesize：输出每页行数，缺省为24 
为了避免分页，可设定为0。 
termout：显示脚本中的命令的执行结果，缺省为on 
timing：显示每条sql命令的耗时，缺省为off 
trimout：去除标准输出每行的拖尾空格，缺省为off 
trimspool：去除重定向（spool）输出每行的拖尾空格，缺省为off 
4.4.4 例子 
以文本形式下载表数据 
oracle缺乏将表中数据输出至文本文件的工具，因此只能利用sqlplus和unix工具做变通的处理 
sqlplus -s dbuser/oracle </dev/null 
set colsep |; 
set echo off; 
set feedback off; 
set heading off; 
set pagesize 0; 
set linesize 1000; 
set numwidth 12; 
set termout off; 
set trimout on; 
set trimspool on; 
spool tmp.txt; 
select * from emp; 
spool off; 
exit 
EOF 
tr -d ' ' < tmp.txt >emp.txt 删除空格，可选 
注意：一定要用spool，如果在命令行中直接用>tmp.txt可能会造成数据缺失，至少在Unixware7上如此 
假定某域是char(n)，如中间出现回车\n，则下载出的这条记录的格式将会错乱，不宜采用此方法 

5 备份及恢复 
所有参见内容都在附件05_backup/下。 
5.1 export与import方式 
参见dmp/backup.sh。 
见《工具》对exp和imp的描述 
数据库中的对象是比较多的，但除了表以外占用的空间不大，所以当表中记录数量达到一定规模后，以用户的方式一下子把数据exp出来就显得不够灵活。考虑以下的策略，先exp出除表数据以外的所有对象，再分别exp出每张表的数据。 
exp dbuser所有的数据对象 
exp dbuser/oracle file=dbuser.dmp log=user.log owner=user buffer=2048000 rows=n 
exp单张表的数据 
sqlplus -s dbuser/oracle </dev/null 
set colsep |; 
set echo off; 
set feedback off; 
set heading off; 
set pagesize 0; 
set linesize 1000; 
set termout off; 
set trimout on; 
set trimspool on; 
spool tables.txt; 
select table_name from user_tables; 
spool off; 
exit; 
EOF 
for table in $(cat tables.txt) 
do 
exp dbuser/oracle file=${table}_$(date '+%Y%m%d').dmp tables=$table direct=y 
done 
5.2 冷备份 
shutdown数据库，将所有和本实例有关的文件，包括datafile,controlfile,redolog,archived redolog,initora.ora等全部备份。恢复时只要将这些文件放回从前的目录，startup数据库即可。 
5.3 联机全备份+日志备份 
5.3.1 设置 
如果数据库实例原来没有使用归档日志功能，则必须进行配置修改 
initoradb.ora： 
log_archive_start = true #实例启动时同时启动归档进程。 
log_archive_dest_1= "location=/appl/oracle/oradata/orafe/arch/arch" #归档日志目录。 
打开归档日志功能： 
shutdown数据库 
sqlplus "/ as sysdba" 
SQL>startup mount 
SQL>alter database archivelog; 
SQL>alter database open; 
可用archive log list查看状态，去除归档日志功能的命令为alter database noarchivelog。 
5.3.2 步骤 
参见online/full.sh、daily.sh，以osdba组的用户执行 
联机全备份： 
数据库处于open状态，依次对各个表空间备份 
sqlplus "/ as sysdba" 
SQL>alter tablespace system begin backup; 
复制此tablespace各个datafile 
SQL>alter tablespace system end backup; 
注意：据推测，begin backup是对tablespace冻结写入，end backup是解除冻结，因此复制datafile的过程不宜过长 
备份controlfile 
SQL>alter database backup controlfile to '…….'; 
日志备份： 
sqlplus "/ as sysdba" 
SQL>alter system archive log stop; 
移去日志目录下的所有archived redolog 
SQL>alter system archive log start; 
5.3.3 恢复 
数据库处于shutdown状态 
最差情况：磁盘全部损坏，仅保存上次联机全备份和每天日志备份 
解决硬件故障，配置系统软件及环境 
oracle用户，将全备份和日志备份转移至相应目录，根据initoradb.ora中controlfile的配置，将备份控制文件复制到响应目录下 
sqlplus "/ as sysdba" 
SQL>startup mount 
SQL>recover database until cancel using backup controlfile; 
逐个确认待恢复的archived redolog，待最后一个完成后，键入cancel，使恢复结束 
SQL>alter database open resetlogs; 
注意：由于日志已经重置，所以应尽快做一次联机全备份 
丢失某数据文件 
只要将此文件从上次联机全备份中复制至其目录，并将自上次联机全备份以来所有日志备份移至归档目录 
sqlplus "/ as sysdba" 
SQL>startup mount 
SQL>alter database recover datafile 'path/file';或者简单些recover database; 
SQL>alter database open; 
如果此文件损坏或丢失，又无备份，则只能将此文件脱机，将数据exp出来，重建表空间，再imp进去 
sqlplus "/ as sysdba" 
SQL>connect internal 
SQL>startup mount 
SQL>alter database datafile 'path/file' offline; 
SQL>alter database open; 
5.4 注意要点 
无论有多少把握，恢复前先做冷备份，此为第一原则 
不这样做，便是无路可退，一旦失误，后果不必多说。 
rollback段损坏 
这是非常严重的问题，可在initora.ora中写入_corrupted_rollback_segments=(rxx)，启动时避开损坏的rollback段，这只是权宜之计。如数据库处于archivelog，应从上一次全备份起利用备份的日志进行恢复；如数据库处于noarchivelog，应尽快将全部数据export出来，重建数据库，再import进去。所有操作之前，应做冷备份。 
数据库异常中止处理 
通过手工shutdown abort操作中止数据库，不会产生大的问题，通常直接startup无需使用介质恢复命令 
如果由于机器崩溃引起的中止，则情况严重得多，有可能要使用到上面提到的恢复方法，不过这种现象并不多见。一般需要显式使用介质恢复命令，如下： 
sqlplus "/ as sysdba" 
SQL>startup mount; 
SQL>recover database; 
SQL>alter database open;