# oracle-ops **Repository Path**: ckorig/oracle11g ## Basic Information - **Project Name**: oracle-ops - **Description**: Oracle 日常运维 - **Primary Language**: 其他 - **License**: MulanPSL-2.0 - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2025-05-16 - **Last Updated**: 2025-05-23 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # OracleOPS #### 介绍 Oracle 日常运维 #### 常用命令集 ```sql 查询数据库当前进程的连接数： 　　select count(*) from v$process; 查看数据库当前会话的连接数： 　　select count(*) from v$session; 查看数据库的并发连接数： 　　select count(*) from v$session where status='ACTIVE'; 查看当前数据库建立的会话情况： 　　select sid,serial#,username,program,machine,status from v$session; 查询数据库允许的最大连接数： 　　select value from v$parameter where name = 'processes'; 或者：show parameter processes; 　　修改数据库允许的最大连接数： 　　alter system set processes = 300 scope = spfile; (需要重启数据库才能实现连接数的修改) 重启数据库： 　　shutdown immediate; 　　startup; 查看当前有哪些用户正在使用数据： 　　select oSUSEr,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine 　　from v$session a,v$sqlarea b 　　where a.sql_address = b.address 　　order by cpu_time/executions desc; 备注：UNIX 1个用户session对应一个操作系统process，而Windows体现在线程。 启动 oracle 　　su - oracle 　　sqlplus system/pwd as sysdba //进入sql 　　startup //启动数据库 　　lsnrctl start //启动监听 　　sqlplus "/as sysdba" 　　shutdown immediate; 　　startup mount; 　　alter database open; SET PAGESIZE 50; 设置每页显示的行数 SET LINESIZE 80; 设置每行显示的字符数 SET MARKUP HTML ON; 设置结果以HTML格式显示 SET MARKUP HTML OFF; 设置结果以纯文本格式显示 ``` ### 运维脚本 ``` 一、定时备份脚本 #!/bin/bash path=/home/oracle/.bash_profile time=`date +%m%d` dbuser=账号 dbpasswd=密码 backupdir=/u01/app/oracle/admin/ora11g/dpdump #备份路径，系统自带变量 dbbackupdir=/home/oracle/dmpbackup/ #指定新的备份路径 source $path expdp $dbuser/$dbpasswd DUMPFILE=$dbuser$time.dmp LOGFILE=项目_$time.log DIRECTORY=DATA_PUMP_DIR CONTENT=ALL COMPRESSION=ALL echo "$dbpasswd数据库备份完成" mv $backupdir/$dbuser$time.dmp $dbbackupdir sleep 5 find $dbbackupdir -mtime +7 -name "*.dmp" -exec rm {} \; sleep 3 rsync -avz --progress $dbbackupdir* 账号@备份机:$webdir 二、归档清理脚本 #!/bin/bash exec 1>>/home/oracle/logs/clear_archivelog$(date +"%Y%m%d").log path=/home/oracle/.bash_profile logdir=/archive/ORA11G/archivelog/. source $path rman target sys/密码< /tmp/tablespace-${time}.txt #数据文件路径 #可以自定义路径 datafile_path=/oradata/ora11g #datafile_path=$(dirname $(find /u02 -name "users01.dbf")) #定义查询表空间信息函数，单位为MB function check { sqlplus -S "/ as sysdba" << EOF set linesize 200 set pagesize 200 select a.tablespace_name, total, free,(total-free) as usage from (select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name; quit EOF } #定义添加数据文件的函数, #$1：表空间的名称 $2：数据文件的名称 $3：数据文件大小。 function alter_datafile { sqlplus -S "/ as sysdba" << EOF ALTER TABLESPACE "$1" ADD DATAFILE '/${datafile_path}/${2}' SIZE ${3}; quit EOF } #EGRANTDATAS空闲表空间，%.0f 将小数化整数 EGRANTDATAS_FREE=$(check | grep EGRANTDATAS | awk '{printf("%.0f\n",$3)}') echo "EGRANTDATAS空闲表空间: ${EGRANTDATAS_FREE} MB " #USERS空闲表空间,%.0f 将小数化整数 USERS_FREE=$(check | grep USERS | awk '{printf("%.0f\n",$3)}') echo "USERS空闲表空间: ${USERS_FREE} MB" #判断EGRANTDATAS空闲大小是否低于8G,8192 if [ ${EGRANTDATAS_FREE} -le 8192 ];then echo "EGRANTDATAS空闲大小低于8G" #添加大小为：16G的数据文件 alter_datafile EGRANTDATAS EGRANTDATAS${time}.dbf 16384M fi #判断USERS空闲大小是否低于512MB if [ ${USERS_FREE} -le 512 ];then echo "USERS空闲大小低于512M" #添加大小为：2G的数据文件 alter_datafile USERS USERS${time}.dbf 2048M fi #清理所产生的日志文件 find /tmp -mtime +60 -name "tablespace*.txt" -exec rm -rf {} \; 四、定期清理备份文件 30 23 * * * /bin/sh /home/oracle/delbak.sh cat /home/oracle/delbak.sh #/bin/bash #location="/u02/app/admin/ora11g/dpdump" #find $location -mtime +7 -type f |xargs rm -f 五、表空间监控相关 cat /root/scripts/oracle_archive_UsageRate.sh #!/bin/bash # du -sh /archive/ | awk '{print $1}' | sed 's/G//' > /opt/zabbix/archive-log.txt used=`du -sh /archive/ | awk '{print $1}' | sed 's/G//'` total=700 #result=$(echo "scale=0; $used / $total * 100" | bc) result=$(echo "scale=2; ($used / $total * 100)" | bc | awk '{printf("%d\n", $1)}') echo $result > /opt/zabbix/archive-log.txt [root@szyky-db etc]# cd /usr/local/zabbix/etc/zabbix_agentd.conf.d/ [root@szyky-db zabbix_agentd.conf.d]# ls -l 总用量 8 -rw-r--r-- 1 zabbix zabbix 2457 8月 23 2018 discovery_java_status.conf -rw-r----- 1 zabbix zabbix 253 12月 19 2023 oracle.conf [root@szyky-db zabbix_agentd.conf.d]# cat discovery_java_status.conf #发现项目类别 UserParameter=java.jmx.discovery,/usr/local/zabbix/scripts/jmx_discovery.sh jmx_port_discovery #当前线程数 UserParameter=java.ThreadCounts[*],/usr/bin/java -jar /usr/local/zabbix/lib/cmdline-jmxclient-0.10.3.jar - 127.0.0.1:$1 java.lang:type=Threading ThreadCount 2>&1|cut -d " " -f6 #线程数峰值 UserParameter=java.PeakThreadCount[*],/usr/bin/java -jar /usr/local/zabbix/lib/cmdline-jmxclient-0.10.3.jar - 127.0.0.1:$1 java.lang:type=Threading PeakThreadCount 2>&1|cut -d " " -f6 #总纯程数 UserParameter=java.TotalStartedThreadCount[*],/usr/bin/java -jar /usr/local/zabbix/lib/cmdline-jmxclient-0.10.3.jar - 127.0.0.1:$1 java.lang:type=Threading TotalStartedThreadCount 2>&1| cut -d " " -f6 #实例当前线程数 UserParameter=java.Catalina.currentThreadCount[*],/usr/bin/java -jar /usr/local/zabbix/lib/cmdline-jmxclient-0.10.3.jar - 127.0.0.1:$1 Catalina:name=http-$2,type=ThreadPool currentThreadCount 2>&1| cut -d " " -f6 #实例当前连接数 UserParameter=java.Catalina.connectionCount[*],/usr/bin/java -jar /usr/local/zabbix/lib/cmdline-jmxclient-0.10.3.jar - 127.0.0.1:$1 Catalina:name=http-$2,type=ThreadPool connectionCount 2>&1| cut -d " " -f6 #实例最大线程数 UserParameter=java.Catalina.maxThreads[*],/usr/bin/java -jar /usr/local/zabbix/lib/cmdline-jmxclient-0.10.3.jar - 127.0.0.1:$1 Catalina:name=http-$2,type=ThreadPool maxThreads 2>&1| cut -d " " -f6 #当前繁忙线程数 UserParameter=java.Catalina.currentThreadsBusy[*],/usr/bin/java -jar /usr/local/zabbix/lib/cmdline-jmxclient-0.10.3.jar - 127.0.0.1:$1 Catalina:name=http-$2,type=ThreadPool currentThreadsBusy 2>&1| cut -d " " -f6 #堆内存使用量 UserParameter=java.HeapMemoryUsage.used[*],/usr/bin/java -jar /usr/local/zabbix/lib/cmdline-jmxclient-0.10.3.jar - 127.0.0.1:$1 java.lang:type=Memory HeapMemoryUsage 2>&1|grep used |cut -d " " -f2 #堆内存最大 UserParameter=java.HeapMemoryUsage.max[*],/usr/bin/java -jar /usr/local/zabbix/lib/cmdline-jmxclient-0.10.3.jar - 127.0.0.1:$1 java.lang:type=Memory HeapMemoryUsage 2>&1|grep max |cut -d " " -f2 #堆内存百分比 UserParameter=java.HeapMemoryUsage.per[*],/usr/local/zabbix/scripts/jmx_discovery.sh $1 $2 #当前会话数 UserParameter=java.Catalina.activeSessions[*],/usr/bin/java -jar /usr/local/zabbix/lib/cmdline-jmxclient-0.10.3.jar - 127.0.0.1:$1 Catalina:type=Manager,path=/egrantweb,host=localhost activeSessions 2>&1| cut -d " " -f6 [root@szyky-db zabbix_agentd.conf.d]# cat oracle.conf UserParameter=discovery.oracle.tablespace[*],/bin/bash /opt/zabbix/dis.sh UserParameter=tablespace.check[*],bash /opt/zabbix/zabbix.sh $1 $2 UserParameter=bak.check,cat /opt/zabbix/bak_check.log UserParameter=archiverate,cat /opt/zabbix/archive-log.txt ``` ### 数据恢复操作 ``` #查看表空间情况 SELECT tbs 表空间名,sum(totalM) 总共大小M,sum(usedM)已使用空间M,sum(remainedM) 剩余空间M,sum(usedM)/sum(totalM)*100 已使用百分比, sum(remainedM)/sum(totalM)*100 剩余百分比 FROM ( SELECT b.file_id ID, b.tablespace_name tbs,b.file_name name,b.bytes/1024/1024 totalM,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM, sum(nvl(a.bytes,0)/1024/1024) remainedM, sum(nvl(a.bytes,0)/(b.bytes)*100), (100 - (sum(nvl(a.bytes,0))/(b.bytes)*100)) FROM dba_free_space a,dba_data_files b WHERE a.file_id = b.file_id GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes ORDER BY b.tablespace_name ) GROUP BY tbs; #查看导出目录路径 SELECT * FROM all_directories WHERE directory_name = 'DATA_PUMP_DIR'; -- /u01/app/oracle/admin/ora11g/dpdump/ #查看表空间 select file_name,tablespace_name from dba_data_files; -- /oradata/ora11g/EGRANTDATAS01.dbf -- /oradata/ora11g/sysaux01.dbf -- /oradata/ora11g/system01.dbf #修改表空间 ALTER DATABASE DATAFILE '/oradata/ora11g/EGRANTDATAS01.dbf' RESIZE 30000M; ALTER tablespace EGRANTDATAS ADD datafile '/oradata/ora11g/EGRANTDATAS02.dbf' size 30000M; ALTER tablespace EGRANTDATAS ADD datafile '/oradata/ora11g/EGRANTDATAS03.dbf' size 30000M; ALTER DATABASE DATAFILE '/oradata/ora11g/sysaux01.dbf' RESIZE 4096M; ALTER DATABASE DATAFILE '/oradata/ora11g/system01.dbf' RESIZE 2048M; create tablespace EGRANTINDEXS datafile '/oradata/ora11g/EGRANTINDEXS01.dbf' size 1000M autoextend on next 100M maxsize unlimited; #创建账号 create user egrant_sfis_test identified by "egrant_sfis_test" default tablespace EGRANTDATAS; grant exp_full_database to egrant_sfis_test; grant imp_full_database to egrant_sfis_test; grant resource to egrant_sfis_test; grant connect to egrant_sfis_test; grant dba to egrant_sfis_test; GRANT CREATE TABLE TO egrant_sfis_test; GRANT CREATE SESSION TO egrant_sfis_test; GRANT CREATE VIEW TO egrant_sfis_test; GRANT UNLIMITED TABLESPACE TO egrant_sfis_test; grant EXECUTE ANY PROCEDURE to egrant_sfis_test; GRANT EXECUTE ON DBMS_SNAPSHOT_UTL TO egrant_sfis_test; GRANT SELECT ON EVREVIEWER_UNOFFICIAL TO egrant_sfis_test; GRANT create,SELECT,INSERT,UPDATE ON POST_TABLE_ACTION TO egrant_sfis_test; grant create materialized view to egrant_sfis_test; grant read, write on directory DATA_PUMP_DIR to egrant_sfis_test; -- Grant/Revoke system privileges grant create any index to egrant_sfis_test; grant create any synonym to egrant_sfis_test; grant create any view to egrant_sfis_test; grant create external job to egrant_sfis_test; grant create job to egrant_sfis_test; grant debug any procedure to egrant_sfis_test; grant debug connect session to egrant_sfis_test; grant execute any procedure to egrant_sfis_test; grant select any table to egrant_sfis_test; grant unlimited tablespace to egrant_sfis_test; ------- 权限查询 SELECT * FROM dba_users WHERE username = 'egrant_sfis_test'; SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'egrant_sfis_test'; --- 删除用户数据 DROP USER egrant_sfis_test cascade; ``` ### 排除指定表 ``` # 查看指定表空间有哪些用户 SQL> select distinct s.owner from dba_segments s where s.tablespace_name ='EGRANTDATAS'; # 切 oracle 用户 su - oracle # 进入 sqlplus sqlplus / as sysdba # 删除 sfms_demo 用户及相关数据 DROP USER sfms_demo CASCADE; # 重新创建及授权 create user sfms_demo identified by "ns123456" default tablespace EGRANTDATAS; GRANT CONNECT, RESOURCE,DBA TO sfms_demo; # exit，退出 sqlplus，直接执行导入今天导出的数据 impdp sfms_demo/ns123456 DUMPFILE=sfms-20250307.dmp DIRECTORY=DATA_PUMP_DIR LOGFILE=sfms-demo-20250307-expdp.log REMAP_SCHEMA=sfms:sfms_demo transform=OID:N impdp egrant_sfis/egrant_sfis \ DUMPFILE=EGRANT_SFIS-20250501.dmp \ DIRECTORY=DATA_PUMP_DIR \ LOGFILE=EGRANT_SFIS-20250501.log \ CONTENT=ALL \ transform=OID:N \ EXCLUDE=TABLE:\"IN\(\'COM_AUDIT_TRAIL\',\'COM_AUDIT_TRAIL_BAK\',\'ISIS_ACCESS_RECORDS\',\'SYNDATA_TASK_HISTORY\'\)\" ``` ### 归档日常维护配置 ``` 步骤 1: 确认数据库关闭状态 首先，确保数据库已经完全关闭。如果数据库正在运行，请先将其关闭： > SHUTDOWN IMMEDIATE; 步骤 2: 启动数据库到 MOUNT 模式 接下来，在 MOUNT 模式下启动数据库，而不是直接打开它： > STARTUP MOUNT; 步骤 3: 更改数据库为归档模式 现在，可以更改数据库的日志模式为归档模式： > ALTER DATABASE ARCHIVELOG; 步骤 4: 打开数据库 启用归档模式后，打开数据库： > ALTER DATABASE OPEN; 步骤 5: 验证归档模式是否已启用 最后，验证数据库是否已成功切换到归档模式： > ARCHIVE LOG LIST; 你应该看到输出类似于以下内容，表明数据库现在处于归档模式： 数据库日志模式 归档模式 自动存档 启用 存档终点 USE_DB_RECOVERY_FILE_DEST 最早的联机日志序列 34 下一个存档日志序列 36 当前日志序列 36 其他注意事项 归档日志位置：默认情况下，归档日志会存储在 USE_DB_RECOVERY_FILE_DEST 指定的位置。你可以通过以下命令查看该位置： 深色版本 SHOW PARAMETER db_recovery_file_dest; 如果需要更改归档日志存储位置，可以通过修改初始化参数 LOG_ARCHIVE_DEST_n 来指定新的路径。 备份：强烈建议在执行上述步骤之前对数据库进行全面备份，以防止任何意外的数据丢失或损坏。 维护和监控：启用归档模式后，定期检查归档日志的使用情况并制定相应的清理策略非常重要，以避免磁盘空间耗尽。 #!/bin/bash exec 1>>/home/oracle/logs/clear_archivelog$(date +"%Y%m%d").log path=/home/oracle/.bash_profile logdir=/archive/ORA11G/archivelog/. source $path rman target sys/密码<