ORACLE 管理常用命令

编辑文件/etc/sysctl.conf,具体配制与操作系统不同而不同,本实例在SUSE-LINUX 32位8C,16G通过

kernel.shmall = 2097152
kernel.shmmax = 34359738368
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144 // 256K
net.core.rmem_max = 1048576 // 1M
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
创建ORACLE用户,最好用orarun包创建用户
groupadd -g 400 oinstall
groupadd -g 401 dba
useradd -u 400 -g oinstall -G dba -c "Oracle Software Owner" oracle
设置编码
export LC_ALL=c
export DISPLAY=155.20.25.189:0.0
编辑文件/etc/profile
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_SID=cdtss
export NLS_LANG=AMERICAN_AMERICA.zhs16gbk
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/tmp
export TMPDIR=/tmp

./runInstaller -ignoresysprereqs
安装完成

1.修改过initsid.ora. 注释修改后的参数
2.如果是spfile,则:
sqlplus /as sysdba
create pfile fromo spfile;
create spfile from pfile;
设置SGA
alter system set sga_target=160M;
alter system set sga_max_size=16384M;

create spfile from pfile='/opt/oracle/product/10.2/db_1/dbs/init.ora'
alter system reset sort_area_size scope=spfile sid='*'  #从spfile中移出sort_area_size
create spfile='/tmp/pfile.tst' from spfile

startup pfile='**/init.ora'

添加执行权限
chmod a+x ./oracle
变更所有权
chown -R oracle:dba ./oracle

启动控制台
./emctl start dbConsole
vi $ORACLE_HOME/../dbs/ vi initeygle.ora修改sga大小

启动Listener
./lsnrctl start
启动网络配制
./netmgr

sysctl -a
如果出现:TNS-12555
chmod 777 /var/tmp/.oracle

LANG="zh_CN.GB18030"

反安装ORACLE
3.停止Listener
[oracle@ora920 oracle]$ lsnrctl stop

4.停止HTTP服务
[oracle@ora920 oracle]$ $ORACLE_HOME/Apache/Apache/bin/apachectl stop

5.用su或者重新登录到root(如想重新安装可以保留oracle用户，省得输入环境变量了)

6.将安装目录删除
[root@ora920 /root]# rm -rf /opt/oracle/

7.将/usr/bin下的文件删除
[root@ora920 /root]# rm /usr/bin/dbhome
[root@ora920 /root]# rm /usr/bin/oraenv
[root@ora920 /root]# rm /usr/bin/coraenv

8.将/etc/oratab删除
[root@ora920 /root]# rm /etc/oratab

9.将/etc/oraInst.loc删除
[root@ora920 /root]# rm /etc/oraInst.loc

10.将oracle用户删除(若要重新安装,可以不删除)
[root@ora920 /root]# userdel –r oracle

11.将用户组删除(若要重新安装,可以不删除)
[root@ora920 /root]# groupdel oinstall
[root@ora920 /root]# groupdel dba

12.将启动服务删除
[root@ora920 /root]# chkconfig --del dbora

/home/oracle/oraInventory/orainstRoot.sh
/home/oracle/product/10.2.0/db_1/root.sh

------------删除oracle10g--------------
1. 运行 $ORACLE_HOME/bin/localconfig delete
2. rm -rf $ORACLE_BASE/*
3. rm -f /etc/oraInst.loc /etc/oratab
4. rm -rf /etc/oracle
5. rm -f /etc/inittab.cssd
6. rm -f /usr/local/bin/coraenv /usr/local/bin/dbhome /usr/local/bin/oraenv

修改oracle参数
alter system set xxx=xxx scope=spfile;
shutdown immediate;
startup;

设置oracle归档:

sqlplus ‘/ as sysdba’
alter system set log_archive_dest_1=’location=/yang/arch’ scope=spfile;
alter system set log_archive_start=TRUE scope=spfile;
alter system set log_archive_format=’ arch%t_%s_%r.arc’ scope=spfile;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

数据库置为归档模式后，可以按如下方式检验一下：
SQL> archive log list

#将logminer的数据字典从system表空间转移到新建的表空间，防止撑满system表空间
execute dbms_logmnr_d.set_tablespace('tbs_stream');
创建表空间:
create tablespace tbs_stream datafile '+VGDATA/h10g/datafile/tbs_stream01.dbf'
size 100m autoextend on maxsize unlimited segment space management auto;

创建用户:
create user strmadmin identified by strmadmin
default tablespace tbs_stream temporary tablespace temp; 
授权用户:
#授权Stream管理用户
grant connect,resource,dba,aq_administrator_role to strmadmin;

#启用Database 追加日志
alter database add supplemental log data;

#启用Table追加日志
alter table add supplement log group log_group_name(table_column_name) always;

#建立数据库链
create database link h10g connect to strmadmin identified by strmadmin using 'h10g';

ASM

ASM新建,扩表空间
create tablespace cdweb_index datafile size 10G;
alter tablespace cdweb_index add datafile size 10G;

//ASM安装
rpm -Uvh oracleasm-support-2.1.2-1.SLE10.x86_64.rpm  / oracleasmlib-2.0.2-1.x86_64.rpm / oracleasm-2.6.16.21-0.8-smp-2.0.3-1.x86_64.rpm
/etc/init.d/oracleasm configure
This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                                done
Scanning the system for Oracle ASMLib disks:                          done

停:/etc/init.d/oracleasm disable
启:/etc/init.d/oracleasm enable
创建:/etc/init.d/oracleasm createdisk VOL1 /dev/dm-0
删除:/etc/init.d/oracleasm deletedisk VOL1
查询:/etc/init.d/oracleasm listdisks
查询指定: /etc/init.d/oracleasm querydisk VOL1
扫描:/etc/init.d/oracleasm scandisks
/etc/init.d/oracleasm status
###########
select * from v$asm_disk;
select * from v$asm_diskgroup;
alter diskgroup XXX add disk '/dev/oracleasm/disks/VOL2' name data02;
###########

ASM安装日志:
155.20.16.15上dm-0~13全部做了asm-disk
155.20.16.18上dm-0~1做了asm-disk,dm2~3没做.
select name from v$datafile;
select member from v$logfile;

 

如果没有归档：
 shutdown immediate;
 startup mount;
 alter database archivelog; / alter database noarchivelog;
 alter database open;
 archive log list;
 
 删除归档日志：
 rman
 delete archivelog until time 'sysdate';
 ALTER SYSTEM ARCHIVE LOG CURRENT;
恢复到指定时间
run {
restore archivelog from time "to_date('2009-06-05 14:00:00', 'YYYY-MM-DD HH24:MI:SS')";
 }

export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
recover database until time '2009-06-05 13:00:00';
alter database open resetlogs;

list backup of archivelog from time "to_date('2009-06-01 14:00:00', 'YYYY-MM-DD HH24:MI:SS')";

run
{
　　set until time "to_date('2009-06-05 13:00:00', 'YYYY-MM-DD HH24:MI:SS')"
　　restore force database;
　　recover database;
　　alter database open resetlogs;
}

 
 数据文件坏损 ORA-01251:
  startup mount;
  Alter database create datafile  '/home/oracle/oracle_10g/oradata/CDTSS/cdds';
  recover datafile '/home/oracle/oracle_10g/oradata/CDTSS/cdds'
  alter database open;

查看相关日志文件详细信息
select * from v$log;
select * from v$recovery_file_dest;
RMAN
 rman target /
 crosscheck archivelog all;
 delete expired archivelog all;
 delete noprompt archivelog all;

 备份:
 backup full database format 'D:/oracle/archived/db_%U' plus archivelog format 'D:/oracle/archived/ar_%U';

export ORACLE_SID=+ASM1
sqlplus / as sysdba
shutdown immediate;
startup;
select name,state from v$asm_diskgroup;
alter diskgroup RECOVERYDEST mount;
exit
export ORACLE_SID=devdb1
sqlplus /nolog
startup;

2.7 手工注册归档日志
当有异常发生，远程归档失败后；需要手工归档日志以使capture进程能够继续下去
命令格式：
ALTER DATABASE REGISTER LOGICAL LOGFILE 'D:/oracle/product/10.2.0/oradata/S
ERVICE/ARCHIVE/ARC00065_0676907427.001' for 'CAPTURE_SXLSS';
注意：CAPTURE_SXLSS也要用引号引起来

创建分区表：
create tablespace partitionTestTableSpace
datafile 'partitionTestTableSpace' size 50M

create table niegc_part
(
part_id integer primary key,
part_date date,
part_desc varchar2(100)
)
partition by range(part_date)
 (
 partition "200906" values less than(to_date('2009-06-30','yyyy-mm-dd')) tablespace partitionTestTableSpace,

 partition "200907" values less than(to_date('2009-07-31','yyyy-mm-dd')) tablespace partitionTestTableSpace,

 partition "200908" values less than(to_date('2009-08-31','yyyy-mm-dd')) tablespace partitionTestTableSpace
 );

修改表空间：
select a.tablespace_name,
       a.bytes bytes_used,
       b.largest,
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) percent_used
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files x
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 Order by ((a.bytes - b.bytes) / a.bytes) DESC;
----
ALTER DATABASE DATAFILE '/dev/vg01/rlv_sysaux'  AUTOEXTEND off;
ALTER DATABASE DATAFILE '/dev/vg01/rlv_sysaux'  AUTOEXTEND ON  next 200m MAXSIZE UNLIMITED;

引用表创建视图授权
grant select on dj_nsrxx to wb with grant option;
grant select on dj_nsrjdxx to wb with grant option;
grant select on dj_nsrxx_clxx to wb with grant option;

exp 'sys/oracle@du as sysdba' ...
exp /'sys/oracle@du as sysdba/' ...
STREAM触发器不触发方案
begin
   DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY('用户名','触发器名',FALSE);
end;
du -s -h

--KILL 掉ORACLE后
ipcs -as
ipcrm -s sid
ipcrm -m mid