ORACLE数据库运维总结

ORACLE 体系架构
宕机sqlplus 登录
sqlplus -prelim / as sysdba


查看执行计划
select * from table(dbms_xplan.display);


单机：
show parameter dump     查询alter日志目录
archive log list;     查看归档路径
show parameter recovery;    看归档日志
show parameter db_recovery_file_dest;
select * from v$version;    查看数据库版本
show parameter pga  
show parameter sga    查看sga/pga
show parameter processes;    查看连接数
select * from nls_database_parameters;  查看字符集
select sum(bytes/1024/1024/1024) from dba_segments;
show parameter sga
select name from v$database; 
select  instance_name,status from v$instance;  查看实例状态
ps -ef | grep ora_      查看数据库有几个实例
查看一个参数的值
SQL> show parameter parameter_name

查询最近系统产生的归档日志量
select to_char(next_time, 'yyyy-mm-dd') hourtime,round(sum(blocks * block_size) / 1024 / 1024 / 1024) archlog_GB
    from v$archived_log                       
   where dest_id = 1                       
     and next_time > sysdate - 15                     
   group by to_char(next_time, 'yyyy-mm-dd')                     
   order by to_char(next_time, 'yyyy-mm-dd'); 


查看alter日志最直接
select * from v$diag_info;

慢一点
show parameter background_dump_dest;


关闭归档
shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;

更改归档默认路径

alter system set log_archive_dest_1='location=/u01/archivelog' scope =both;
archive log list;
shutdownimmediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

ORACLE数据库修改最大连接数
1)查询当前数据库会话数
select count(*) from v$session;
select count(*) from v$process;

查询inactive的连接数
select count(*) from v$session where status='INACTIVE';

进程满了杀服务
杀掉oracle进程: kill -9 `ps -ef|grep "oracle" |grep "LOCAL=NO"|awk '{print $2}'`

2)查看最大会话数

show parameter processes;
3)修改process和session值
alter system set processes=5000 scope=spfile;
create pfile from spfile;
shutdown immediate;
startup
4)查询什么程序占用了最多的连接数
select machine,program,count(*) from v$session where status='INACTIVE' group by machine,program;

5）部署脚本定时删除会话
*/5 * * * * /backup/scripts/kill_session.sh

#!/bin/bash
source /home/oracle/.bash_profile
tmpfile=/tmp/tmp.$$
sqlplus / as sysdba <<EOF
spool $tmpfile
select spid from v\$process where addr in
(select paddr from v\$session where status='INACTIVE' and last_call_et>=600 and username in('INTERMES','SUNGROWDATA','YGDYUSER'));
spool off
EOF
for x in `cat $tmpfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmpfile







查看opatch补丁号
echo $ORACLE_HOME
/usr/local/oracle/oracle/product/11.2.0/db_1
cd /usr/local/oracle/oracle/product/11.2.0/db_1
cd OPatch/
./opatch lsinventory

删除归档
（1）rman
window/linxu多实例
set ORACLE_SID=xxx，export ORACLE_SID=xxx
rman target /
--进入rman
2)crosscheck archivelog all;
--检查归档
3)delete expired archivelog all;
--删除所有失效归档
SYSDATA-7，表明当前的系统时间7天前，before关键字表示在7天前的归档日志，如果使用了闪回功能，也会删除闪回的数据
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
delete archivelog all completed before 'SYSDATE-3';
4)exit
查询15天的归档量
select to_char(next_time, 'yyyy-mm-dd') hourtime,round(sum(blocks * block_size) / 1024 / 1024 / 1024) archlog_GB from v$archived_log where dest_id=1 and next_time > sysdate - 15 group by to_char(next_time, 'yyyy-mm-dd') order by to_char(next_time, 'yyyy-mm-dd');

闪回找数据
针对 delete 级别的误删除，在时间和 undo 允许的情况下，可以通过闪回将数据迅速找回
 select count(1) from  xxx as of timestamp systimestamp - interval '10' minute;

使用rman备份恢复

1：通过rman恢复单张表
1）创建测试表空间
create tablespace eason datafile '/home/oracle/ecology/eason.dbf' size 10m autoextend on ;
2) 创建测试用户
create user hyj identified by hyj default tablespace eason;
grant dba to hyj;
3) 创建测试表

普通表
create table students(
id int,
name varchar2 (20),
age int);

分区表
create table students_p (id int ,name varchar2(20) ,age int)
partition by range(age)
(
partition p1 values less than(18),
partition p2 values less than(40),
partition p3 values less than(60),
partition p4 values less than(maxvalue)
);   

4)插入数据
vi 写脚本
insert into students(id,name,age) values(1,'李四',20);
insert into students(id,name,age) values(2,'张帆',16);
insert into students(id,name,age) values(3,'张三',35);
insert into students(id,name,age) values(4,'王八',65);
insert into students(id,name,age) values(5,'张飞',70);
insert into students(id,name,age) values(6,'林白',41);
insert into students_p(id,name,age) values(1,'李四',20);
insert into students_p(id,name,age) values(2,'张帆',16);
insert into students_p(id,name,age) values(3,'张三',35);
insert into students_p(id,name,age) values(4,'王八',65);
insert into students_p(id,name,age) values(5,'张飞',70);
insert into students_p(id,name,age) values(6,'林白',41);

5)备份数据库
[oracle@dbserver ~]$ rman target /
connected to target database: ORCL (DBID=1622462283)
RMAN> backup database plus archivelog;


6)删除表
 truncate table students;

删除分区表数据
delete from students_p partition(p2) ;
commit；
select * from students_p partition(p2);
select * from students_p;




windows上删除归档脚本脚本（编辑文件名xxx.bat）
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-0.5';
crosscheck archivelog all;
delete expired archivelog all;


（2）find命令
find /u01/app/oracle/diag/rdbms/veoliadb/veoliadb2/trace/ -name "*.trc" -mtime +3 -exec rm -rfv{
   } \;
（3）删除备份
find /backup/archivelog -mtime +4 -name "rman*" -exec rm -rf {
   } \;
find /backup/backupsets -mtime +4 -name "cisdb*" -exec rm -rf {
   } \;




导入导出：
   create directory xxx as 'xxx';
  查询逻辑目录位置
select * from dba_directories;
（1）不知道密码
expdp \'/ as sysdba\' schemas=xxx,xxx,xxx directory=xxx dumpfile=xxx%U.dmp logfile=xxx.log parallel=4 compression=all;
window情况下
expdp " '/ as sysdba' " schemas=xxx,xxx,xxx directory=xxx dumpfile=xxx%U.dmp logfile=xxx.log parallel=4 compression=all;
window情况下
（1.1）数据库导入
impdp \'/ as sysdba\'  directory(逻辑目录名)=xxx  dumpfile=xxx%U.dmp(导出的文件名一定要对上) schemas=xxx,xxx,xxxx logfile=xxx.log parallel=4 compression=all;
（1.2）LINUX脚本导出
vi  import_实例名xxx.sh
#!/bin/bash
source /home/oracle/.bash_profile
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
impdp \'/ as sysdba\' directory=data_expdp dumpfile=ORCL201_%U.DMP schemas=PLMUSER,EAMUSER,SAPTEST,TMSUSER,SMPUSER,DCSUSER,ETROLMES,INTERMES cluster=no parallel=6


 (2)备份脚本linux上
#!/bin/bash
source /home/oracle/.bash_profile
DMP_FILE=$(date + %Y%m%d_%H%M%S).dmp
LOG_FILE= $(date + %Y%m%d%_%H%M%S).log
/home/backup
expdp \'/ as sysdba\' schemas=xxx,xxx,xxx directory=xxx dumpfile=$DMP_FILE logfile=$LOG_FILE compression=all;
清除脚本
find /home/backup/ -name "*.dmp" -mtime +1 -exec rm -rfv{
   } \;

LINUX 配置删除策略
#! /bin/bash
source /home/oracle/.bash_profile
export ORACLE_SID=xxxx
rman target / << EOF
delete archivelog all completed before 'SYSDATE-3';
exit
EOF

(3)用parfile后台运行
vi expdp.par
###########
USERID='/as sysdba'
COMPRESSION=ALL
DIRECTORY=expdp
DUMPFILE=0803_%U.dmp 
LOGFILE=0803_expdp.log
SCHEMAS=HYJ,ECOLOGY
PARALLEL=6
########

nohup expdp parfile=expdp.par &

nohup impdp parfile=impdp.par &

rman备份脚本（linux）





ORACLE DG大全
（1）DG与ADG区别
1）ADG主要解决DG时代读写不能并行的问题，使用logical standby实现ORACLE数据库的读写分离
2)  DG具有延时写入数据工能，可避免误操作而第三方工具不能实现
DG日志切换
show parameter name;
alter system archive log current；


ORACLE DG搭建

(1) 开启归档模式
    archive log list;
	shutdown immediate;
	startup mount;
	alter database archivelog;
	alter database open;
	alter system set log_archive_dest_1='location=/u01/archivelog';
(2) 设置数据库闪回和大小
   select flashback_on from v$database;
   alter system set db_recovery_file_dest_size='2G';
   alter system set db_recovery_file_dest='/u01/db_recovery_file_dest';
   alter database flashback on;
   show parameter db_recovery;
(3) 强制记录日志
    select force_logging from v$database;
	alter database force logging;
(