Oracle常用命令3

qmf

管理数据文件和临时文件

创建数据文件
在创建表空间时创建数据文件
create tablespace tbs01 datafile '/opt/oracle/oradb/oradata/inomc/tbs01.dbf' size 100M autoextend on next 10M;
在创建表空间时创建临时数据文件
create temporary tablespace lmtemp tempfile '/opt/oracle/oradb/oradata/inomc/lmtemp01.dbf' size 20M autoextend on next 1M
创建并添加一个数据文件到表空间
alter tablespace tbs01 add datafile '/opt/oracle/oradb/oradata/inomc/tbs02.dbf' size 100M autoextend on next 10M;
创建并添加一个临时数据文件到表空间
alter tablespace temp add tempfile '/opt/oracle/oradb/oradata/inomc/temp02.dbf' size 100M autoextend on next 10M;
改变数据文件大小
alter database datafile '/opt/oracle/oradb/oradata/inomc/temp02.dbf' resize 50M;
打开数据文件自动增长
alter database datafile '/opt/oracle/oradb/oradata/inomc/tbs01.dbf' autoextend on next 10M;
关闭数据文件自动增长
alter database datafile '/opt/oracle/oradb/oradata/inomc/tbs01.dbf' autoextend off;
在archivelog模式下使数据文件联机
在使数据文件online之前，必须执行media恢复
recover datafile 5;
alter database datafile '/opt/oracle/oradb/oradata/inomc/tbs01.dbf' online;
在archivelog模式下使数据文件脱机
alter database datafile '/opt/oracle/oradb/oradata/inomc/tbs01.dbf' offline;
在noarchivelog模式下使数据文件脱机
alter database datafile '/opt/oracle/oradb/oradata/inomc/tbs01.dbf' offline drop;
在noarchivelog模式下使数据文件联机
recover datafile 5;
alter database datafile '/opt/oracle/oradb/oradata/inomc/tbs01.dbf' online;
修改表空间中所有数据文件的可用性
alter tablespace tbs01 datafile offline;
recover datafile 5;
alter tablespace tbs01 datafile online;
同一个表空间的数据文件重命名
1.    alter tablespace tbs01 offline normal
2.    使用操作系统命令修改文件名称：mv /opt/oracle/oradb/oradata/inomc/tbs01.dbf /opt/oracle/oradb/oradata/inomc/tbs011.dbf
3.    alter tablespace tbs01 rename datafile '/opt/oracle/oradb/oradata/inomc/tbs01.dbf' to '/opt/oracle/oradb/oradata/inomc/tbs001.dbf';
4.    alter tablespace tbs01 online;
同一个表空间的数据文件的重定向
1.    alter tablespace tbs01 offline normal
2.    使用操作系统命令修改文件的位置：mv /opt/oracle/oradb/oradata/inomc/tbs01.dbf /opt/oracle/oradb/data/tbs01.dbf
3.    alter tablespace tbs01 rename datafile '/opt/oracle/oradb/oradata/inomc/tbs01.dbf' to '/opt/oracle/oradb/data/tbs01.dbf';
4.    alter tablespace tbs01 online;
删除数据文件(当表空间只有一个数据文件时，不允许删除)
alter tablespace tbs01 drop datafile '/opt/oracle/oradb/oradata/inomc/tbs001.dbf'
删除临时数据文件
alter tablespace temp drop tempfile '/opt/oracle/oradb/oradata/inomc/temp01.dbf';
或alter database tempfile '/opt/oracle/oradb/oradata/inomc/temp01.dbf' drop including datafiles;
管理undo数据

打开自动undo管理(通过修改初始化参数文件)
alter system set undo_management=spfile scope=spfile;
alter system set undo_tablespace=undo01 scope=spfile;
alter system set undo_retention=1800 scope=spfile
创建undo表空间
详见表空间管理一节
备份恢复数据库

逻辑导入导出
使用exp/imp工具导入导出数据
exp/imp帮忙信息
exp –help或者imp -help
输出信息如下所示
oracle@i2ksvr:~> exp help=y

Export: Release 11.1.0.7.0 - Production on Mon Jun 21 19:30:20 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.



You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T11,T12), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
VOLSIZE              number of bytes to write to each tape volume
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export

Export terminated successfully without warnings.
数据导出举例
exp scott/tiger file=/opt/huawei/emp.dmp log=exp.log compress=n direct=y
数据导入举例
imp scott/tiger tables(dept,emp) file=/opt/huawei/emp.dmp log=imp.log ignore=y
rman备份恢复
使用rman工具连接到oracle数据库
连接到要备份的数据库
rman target username/password@alias_name
或者
rman target /
使用带有catalog的连接数据库
rman catalog username/password@alias_name target username/password@alias_name
查看当前的rman设置
show all;
显示信息如下:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/oradb/home/dbs/snapcf_inomc.f'; # default
如果要修改其值，可以使用如下SQL(示例)
configure controlfile autobackup on;
修改结果如下所示
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
备份数据库(只在archivelog模式下，如是在noarchivelog模式下，需要停止数据库，此种方式在I2000数据库操作中不适合，此处不讨论)
backup database plus archivelog delete input;
备份控制文件
backup current controlfile;
备份表空间
backup tablespace system plus archivelog delete input;
备份归档日志
backup archivelog all delete input;
使用如下命令可以封装所有的备份操作，全库备份数据库，控制文件，归档日志文件等
run
{
allocate channel 'dev_0' type disk;
backup as compressed backupset incremental level 0
  format '/opt/oracle/backup/itdb_full_database.dbf'
  database;
sql 'alter system archive log current';
backup
  format '/opt/oracle/backup/itdb_arc_all.arc'
  archivelog all delete input;
backup
  format '/opt/oracle/backup/itdb_ctl.ctl'
  current controlfile;
release channel 'dev_0';
}
查看备份和副本
list backup;
恢复数据库
首先确定恢复的位置，有如下四种：
1.       还原到最新
restore database;
recover database;
2.       还原到指定的scn
set until scn 234234234;
restore database;
recover database;
或者是
recover database until scn 23423423;
3.       还原到指定的日志序列
set until sequence 110 thread 1;
restore database;
recover database;
或者是
recover database until sequence 100 thread 1;
4.       还原到指定的时间
set until time "to_date('2009-3-26 13:00:00','YYYY-MM-DD HH24:MI:SS')";
restore database;
recover database;
或者是
recover database "to_date('2009-3-26 13:00:00','YYYY-MM-DD HH24:MI:SS')";

fengerapple

Thank you very much for sharing!The good man!The good life of peace!