Oracle数据库常用运维SQL语句

活动状态检查

通过查询基本视图，确认数据库和实例处于正常运行状态，可以对外提供数据服务。

1、实例状态

> select instance_name,status from gv$instance;

*实例状态->实例/数据库状态，查询返回实例名称、状态，正常状态应为Open。

$ crsctl status resource -t *集群状态

> select name,open_mode from v$PDBS; *PDB状态

GDSCTL> config shard *Sharding状态

2、连接用户

> select inst_id,username,cout(*) from gv$session group by inst_id,username; *查询数据库以用户分组连接数。

3、会话信息

> select sessions_current,sessions_highwater from v$license;

*实例当前会话数和启动最高连接会话数量。

4、参数检查

> select value from v$parameter where name='open_cursors';

*查询给定参数的设置值，示例参数缺省值为300，通常中等规模数据库推荐设置为1000。

5、参数修改

> alter system set undo_retention=3600 comment='default 900' sid='*' scope=both;

*修改给定的初始化参数，RAC环境需要注意SID参数。

6、隐含参数

> alter system set "_optimizer_use_feedback"=false scope=spfile;

*应对特殊问题，有时需要设置以下划线开头的隐含参数。

> alter system set "_optimizer_use_feedback"=false scope=spfile;

*示例关闭了11.2中引入的Cardinality Feedback - 基线反馈特性。

> alter system set "_use_single_log_writer"=true;

*示例关闭了12c中并行LGWR特性。

7、实例异常

当连接数据库实例出现缓慢、挂起等现象，需要进行诊断和分析，甚至可能需要重新启动数据库实例。

7.1 信息采集

> sqlplus -prelim / as sysdba

> oradebug setmypid

> oradebug unlimit

> oradebug hanganalyze 3

> oradebug dump systemstate 266

.....间隔一定时间，如20秒，执行下一次数据采集

> oradebug hanganalyze 3

> oradebug dump systemstate 266

示范命令，通过采集系统的Hang信息、系统状态信息等，可以分析系统挂起的原因，间隔采样，可以用于对比变化，辅助分析。

7.2 跟踪

> alter session set events '10046 trace name context forever,level 12';

> shutdown immedaite;

> startup mount;

> alter session set events '10046 trace name context forever,level 12';

> alter datebase open;

如果在数据库关闭、启动时遇到阻塞、挂起等，可以通过示范命令进行跟踪，获取跟踪文件进行分析。

7.3 安全停库

> alter system checkpoint;

> alter system archive log current;

> shutdown immediate;

如果数据库出现异常需要重新启动，可以通过示范命令执行检查点、归档命令，然后尝试以立即方式关闭数据库。

7.4 强制停库

> shutdown abort;

> startup nomount;

> alter database mount;

> alter database open;

如果立即方式不能顺利关闭数据库，强制的关闭方式为abort。示范命令可以通过分步骤的方式执行数据库启动。

8、连接异常

当连接数据库出现异常，需要检测包括网络连通性，监听器状态等信息。

8.1 连通性(linux下切换到oracle用户)

$ tnsping tns_name

通过tnsping工具测试配置的服务名称，观察网络是否连通以及响应时间。如果能ping通，则说明客户端能解析listener的机器名，而且listener也已经启动，但是并不能说明数据库已经打开，而且tnsping的过程与真正客户端连接的过程也不一致。如果不能ping通，则肯定连不到数据库。

8.2 监听器

$ lsnrctl status LISTENER

$ lsnrctl status LISTENER_SCAN1

$ lsnrctl service

在数据库服务器上，通过lsnrctl工具检查监听状态和服务信息。

8.3 监听日志检查

adrci> show alert (类似vi编辑器，使用:q退出)

在服务器上，可以通过adrci工具，显示各类告警文件，检查监听器日志，可以诊断监听问题。

日志信息检查

1、Trace文件检查

> select value from v$diag_info where name='Default Trace File';

> show parameter user_dump_dest;

获取会话或全局转储位置，诊断时需检查相应文件内容。

2、监听日志检查

$ lsnrctl status LISTENER

$ lsnrctl status LISTENER_SCAN1

$ lsnrctl service

在数据库服务器上，通过lsnrctl工具检查监听状态和服务信息。

adrci> show alert (类似vi编辑器，使用:q退出)

在服务器上，可以通过adrci工具，显示各类告警文件，检查监听器日志，可以诊断监听问题。

3、errorstack分析

当遇到ORA- 误区，而数据库的输出信息不足时，可以采用errorstack进行跟踪，采集更详细的转储信息。

重做日志维护

Oracle REDO日志是数据库的核心组件，检查其状态，维护其成员，监控其归档，审核其性能，是DBA的重要工作。

1、REDO组和成员

> select group#,sequence#,archived,status from v$log;

*查询日志组号、序号，是否归档完成和状态信息，如多组日志显示ACTIVE状态，则可能说明数据库存在IO方面的性能问题。

> select group#,member from v$logfile;

*查看日志组和成员信息。

> select group#,member from v$logfile where type='STANDBY';

> select group#,dbid,bytes from v$standby_log;

*查看DataGuard及Active DataGuard中standby redo log的信息。

2、REDO维护

> alter database add logfile group 10 ('/oracle/dbs/log1c.rdo') size 500M;

> alter database add logfile member '/oracle/dbs/log1c.rdo' to group 10;

*在日志切换频繁时，可能需要增加日志组或者加大日志大小。

> alter database add logfile thread 2 group 10;

*增加RAC中的日志组

> alter database drop logfile group 10;

> alter database drop logfile member '/oracle/dbs/redo03.log';

*删除指定日志组或日志成员，注意：只能对INACTIVE状态的日志执行删除操作。

3、归档维护

> archive log list;

*检查数据库是否处于归档模式。

> startup mount;

> alter database archivelog;

*在mount状态改变归档模式，启动归档模式之后，务必制定备份归档的日常策略，防止磁盘空间被耗尽。

4、执行归档

> alter system switch logfile;

*切换日志组，开始写入下一个日志组。

> alter system archive log current;

*对当前日志组执行归档，切换到下一个日志组，在RAC会对所有实例执行归档，Thread参数指定归档实例。

5、调整归档路径

> alter system set log_archive_dest_2='location=&path ' sid='&sid ';

*如果数据库因归档耗尽空间，可以制定另外的归档路径，以尽快归档日志，恢复数据库运行。

空间信息检查

确保数据存储空间可用，定期检查表空间余量，进行表空间和文件维护。

1、空间使用

> select * from sys.sm$ts_used;

*查看数据库表空间的使用信息。

> select * from sys.sm$ts_used;

*查看数据库表空间的剩余空间。

> select group_number,name,total_mb,free_mb from v$asm_diskgroup;

> select * from gv$asm_operation;

> alter diskgroup ssddg rebalance modify power 1000;

*ASM磁盘组的空间。

2、文件信息

> select tablespace_name,file_name from dba_data_files;

*查看数据库表空间的数据文件信息。

3、文件维护

> alter database datafile '&path' resize 900M;

> alter tablespace &tba_name add datafile '&path' size 900M;

*对数据库的表空间容量进行扩容。

锁闩信息检查

Lock/Latch是数据库控制并发的核心手段，检查相关信息可以监控数据库的事务和运行状况。

1、锁信息

> select sid,type,lmode,ctime,block from v$lock where type not in ('MR','AE');

*查看锁会话ID，类型，持有时间等，注意：如果block>1，可能意味着阻塞了其他会话。

2、锁故障排查

在数据库出现锁竞争和阻塞时，需要排查和处理锁定，必要时通过Kill阻塞进程消除锁定。

2.1 查询阻塞会话

> select sid,sql_sid,status,blocking_session from v$session where sid in (select session_id from v&locked_object);

*查询当前锁事务中阻塞会话与被阻塞会话的sid,sql_sid和状态信息。

2.2 阻塞SQL文本

> select sql_id,sql_text from v$sqltext where sql_id='&sql_id' order by piece;

通过sql_sid 查询得到的SQL文本，例如，通过sql_id查询出阻塞的SQL语句。

2.3 锁阻塞对象信息

> select owner.object_name,object_type from dba_objects where object_id in (select object_id from v$locked_object);

*通过sid查询阻塞对象的详细信息，如对象名称，所属用户等。

2.4 查询阻塞会话

> alter system kill session 'sid,serial#';

*在Oracle实例内杀死阻塞的会话进程，其中sid,serial#为终止会话对应信息，来自v$session。

2.5 杀系统进程

> select pro.spid,pro.program from v$session ses,v$process pro where ses.sid=&sid and ses.paddr=pro.addr;

# kill -9 spid

有时对于活动进程，在系统层面终止更为快速安全，示例找到系统进程号，然后kill终止。

注意：无论何时，需要认真分析，并且避免误杀重要后台进程。

3、闩检查

> select name,gets,misses,immediate_gets,spin_gets from v$latch order by 2;

*检查数据库闩的使用情况，misses、spin_gets统计高的，需要关注。

4、闩使用检查

> select addr,gets from v$latch_children where name='cache buffers chains';

> select hladdr,file#,dbablk from x$bh where hladdr in (select addr from v$latch_children where addr='&addr');

仅供学习：通过获得Latch的地址，找到该Latch守护的X$BH中相关的Buffer。

等待统计数据

Wait和Statistics数据分别代表了数据库的等待和运行数据，观察这些数据以了解数据库的等待瓶颈和健康程度。

1、等待事件查询

> select sid,event,wait_time_micro from v$session_wait order by 3;

通过等待事件和等待时间，了解数据库当前连接会话的等待情况。

注意：如果会话众多，需要限定查询输出行数。

2、TOP10等待事件

> select * from (select event,total_waits,average_wait,time_waited from v$system_event where wait_class<>'ldle' order by time_waited desc) where rownum<=10;

*查看当前数据中TOP10等待事件信息，需要分析和关注非空闲的显著等待。

3、会话统计数据

> select s.sid,s.statistic#,n.name,s.value from v$sesstat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size' and sid='&sid';

*查询数据库会话的统计信息数据，示例查询了REDO的大小，SID需要提供。

4、系统级统计数据

> select * from v$sysstat where name='redo size';

*查询整个系统的统计数据，示例显示数据库实例启动以来的REDO日志生成量。

对象检查

表、索引、分区、约束等是数据库的核心存储对象，其核心信息和对像维护是DBA重要的日常工作。

1、表

1.1 表信息数据

> select * from (select owner,table_name,num_rows from dba_tables order by num_rows desc nulls last) where rownum<11;

*查看表的基本信息数据：属主，表名，记录行数等。

1.2 表统计信息

> select table_name,last_analyzed from dba_tab_statistics where table_name='&table_name';

查询给定表(需大写)，查询最后的统计信息分析收集时间。

统计信息影响执行计划，当SQL执行异常时，需要重点分析统计信息。

2、索引信息数据

> select * from (select index_name,num_rows,leaf_blocks,clustering_factor from dba_indexes order by 4 desc nulls last) where rownum<11;

索引的基本信息，输出包括叶块数和聚簇因子等，如聚簇因子接近行数可能代表索引效率不高。

3、 DBlink信息

> select * from dba_db_links;

Bethune对接入数据库的IP做链接趋势和孤立IP分析，帮助工程师发现链接异常的情况。

4、分区对象检查

> select table_name,partitioning_type,partition_count,status from dba_part_tables;

> select table_name,partition_name,high_value from dba_tab_partitions where rownum <11;

*查看分区表的基本信息：分区类型，数量，边界值等。

5、结构信息

> set long 12000

> select dbms_metadata.get_ddl ('&obj_type',&obj_name','&user') from dual;

根据提供的对象类型(TABLE,INDEX)和用户(需大写)，获取结构信息。

6、统计信息收集

> exec dbms_stats.gather_table_stats (ownname=>'&owner',tabname=>'&table_name');

> exec dbms_stats.gather_index_stats (ownname=>'&owner',indname=>'&index_name');

收集统计信息是一项复杂任务，需谨慎，示例对给出用户、索引名的对象采集统计信息。

7、约束信息

> select constraint_name,constraint_type from dba_constraints where table_name='&table_name';

*查询指定数据表的约束信息，包括名称和类型。

8、失效对象检查

> select owner,object_name,object_type,status from dba_objects where status<> 'VALID';

*检查数据库中的失效对象信息，通常运行健康的数据库中不应有失效的对象。

9、闪回查询

闪回查询功能对于恢复DML及部分DDL误操作非常便利，DBA必备技能。

9.1 时间闪回

> select * from &table_name as of timestamp to_timestamp('2015-02-04 00:02:09','yyyy-mm-dd hh24:mi:ss');

*闪回表数据，基于时间点的表数据闪回查询。

9.2 SCN闪回

> select * from &table_name as of scn &scn;

*闪回数据表，基于SCN的表数据查询，需要提供SCN，如果不明确SCN，可以通过时间点闪回查询。

9.3 闪回DROP

> flashback table &old_tab to before drop rename to &new_table;

*闪回删除操作，对已经删除的表进行闪回恢复并重命名。

AWR报告检查

通过AWR报告了解日常高峰时段数据库各项指标和运行状况，通过对比报告观察和基线的变化，通过趋势分析持续关注数据库日常运行状态。

1、本地AWR

> @?/rdbms/admin/awrrpt

*生成本地AWR报告信息，需要根据提示输入相应的信息

2、指定实例AWR

> @?/rdbms/admin/awrrpti

*生成指定实例AWR报告

3、RAC AWR

> @$ORACLE_HOME/rdbms.admin/awrgrpt.sql

> @$ORACLE_HOME/rdbms.admin/awrgrpti.sql

4、AWR对比报告

> @?/rdbms/admin/awrddrpt

*生成本地AWR时间段对比报告

5、指定实例对比

> @?/rdbms/admin/awrddrpi

*生成指定实例AWR时间段对比报告

6、系统性能集成报告

> @$ORACLE_HOME/rdbms/admin/perfhubrpt.sql

7、AWR信息提取

> @?/rdbms/admin/awrextr

*使用awrextr脚本将AWR性能数据导出，可以用于留错或者异地分析。

8、AWR信息加载

> @?/rdbms/admin/awrload

*通过awrload，可以将导出的AWR性能数据导入到其他数据库中，便于集中和分析。

SQL报告检查

对TOP SQL进行持续关注和分析，通过SQL报告分析SQL的效率、性能，并做出报告和优化建议等。

1、SQL执行

1.1 Explain SQL执行计划

> explain plan for select count(*) from user_objects;

> select * from table(dbms_xplan.display);

示例通过explain plan for 方法获取SQL执行计划。

1.2 Autotrace SQL执行计划

> set autotrace traceonly explain;

> select count(*) from user_objects;

> set autotrace off;

通过SQL*PLUS的autotrace功能获取SQL执行计划。

1.3 DBMS_XPLAN SQL执行计划

> select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'));

通过DBMS_XPLAN包获取SQL执行计划，sql_id需要提供。

2、10053事件跟踪

> alter session set tracefile_identifier='10053';

> alter session set events '10053 trace name context forever,level 1';

> <execute sql statements>

> alter session set events '10053 trace name context off';

通过10053事件来查看执行计划和详细的SQL解析过程，trace文件提供了Oracle如何选择执行计划的原因。

3、绑定变量

> select dbms_sqltune.extract_bind(bind_data,1).value_string from wrh$_sqlstat where sql_id='&sql_id';

*查询SQL语句的绑定变量以及历史绑定变量值，需要给定SQL_ID信息。

4、SQL报告

4.1 SQL报告

> @?/rdbms/admin/awrsqrpt

> @?/rdbms/admin/awrsqrpi

4.2 指定SQL的监控报告

> select dbms_sqltune.report_sql_monitor(sql_id=>'&sql_id',report_level=> 'all',type=>'active') as report from dual;

*生成指定SQL_ID的SQL Monitor Report。

定时任务检查

检查数据库定时任务执行情况，确保后台任务正确执行，尤其应关注统计信息收集等核心任务。

1、用户定时任务

> select job,log_user,last_date,next_date,interval,broken,failures from dba_jobs;

*查询用户的定时任务(job)信息，确保任务在期望的时间成功执行。

2、系统定时任务

> select job_name,start_data,repeat_interval from dba_scheduler_jobs;

*查询系统定时调度信息，查询显示了任务名称、初始启动日期以及重复间隔。

3、系统定时任务-11g+

> select client_name,mean_job_duration from dba_aotutask_client;

*11g之后增加的字典表，记录每个在7天和30天维护任务的统计信息，查询显示名称和平均执行时间。

4、启停统计信息任务-10g

> exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB);

> exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB);

*关闭和开启oracle 10g统计信息自动采集任务。

5、启停统计信息任务-11g+

> exec dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>null,window_name=>null);

> exec dbms_auto_task_admin.enable(client_name=>'auto optimizer stats collection',operation=>null,window_name=>null);

*关闭和开启统计信息自动采集任务。

备份

数据备份重于一切，日常应检查备份执行情况，并检查备份的有效性，确保备份能够保障数据安全，备份安全加密也应兼顾。

1、备份模式

1.1 用户模式-exp/imp

$ exp enmo/enmo file=/enmo.dmp log=/enmo.log owner=enmo

$ imp enmo/enmo file=/enmo.dmp log=/enmo.log fromuser=enmo touser=enmo

*将数据按指定用户导出和导入。

1.2 用户模式-expdp/impdp

$ expdp system/manager directory=svr_dir schemas=scott dumpfile=expdp.dmp

$ impdp system/manager directory=svr_dir schemas=scott dumpfile=expdp.dmp remap_schema=scott:enmo remap_tablespace=users:enmo

*将数据库进行按用户导出和导入示例，impdp示例中，分别重新映射了导入的Schema和表空间。

1.3 表模式-expdp/impdp

$ expdp scott/tiger directory=svr_dir tables=emp,dept dumpfile=tables.dmp

$ impdp scott/tiger directory=svr_dir dumpfile=tables.dmp tables=emp,dept;

*将数据库进行按表导出和导入。

2、物理备份检查

> select backup_type,start_time,completion_time,block_size from v$backup_set;

*检查备份集信息，确保备份有效和及时是DBA的重要工作之一。RMAN的备份信息记录在控制文件中。

3、控制文件

3.1 自动控制文件备份

RMAN> show all;

RMAN> configure controlfile autobackup on;

控制文件对数据库十分重要，建议启动控制文件的自动备份，示例显示的是通过RMAN的设置。

3.2 手动控制文件备份

RMAN> backup current controlfile;

> alter database backup controlfile to '/tmp/control.bak';

*通过RMAN或者SQL命令手动备份控制文件，备份的是控制文件的二进制拷贝。

3.3 转储控制文件

> alter session set events 'immediate trace name controlf level 8';

*通过上面命令转储控制文件二进制信息到文本，研究这些信息，可以极大加深对于数据库的了解。

> alter database backup controlfile to trace;

*通过SQL命令转储控制文件到文本，可以用于重建控制文件。

4、RMAN备份数据库

RMAN> backup format '/data/backup/%U' database plus archivelog;

对于DBA备份是第一重要工作，在归档模式下，执行全库备份可以简化为示例的一个命令(需要根据容量进行分片)。

基本信息检查

基本信息包括版本、组件、补丁集等信息，定期检查数据库信息并登录在案是数据库生命周期管理的重要内容之一。

1、版本组件

> select * from v$version;

*查看数据库的版本信息

> select * from v$option;

*查看数据库的组件信息

2、容量检查

ASMCMD> lsdg

ASMCMD> lsdsk -p

> select group_number,mount_status,total_mb,free_mb from v$asm_disk;

> select disk_number,name,failgroup,path,mode_status, state,total_mb,free_mb from v$asm_disk order by 2,3,4,5;

> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

如果使用了ASM管理，可以通过示例查看ASM磁盘及磁盘组容量等信息。

3、PSU检查

> select * from dba_registry_history;

*查询数据库的版本升级历史信息

$ $ORACLE_HOME_OPatch/opatch Isinventoty

*查询数据库补丁历史信息，是系统级的命令工具

Oracle数据库常用运维SQL语句

常见问题FAQ

SVIP

发表评论

如需帝国cms功能定制以及二次开发请联系我们

升级SVIP会员

工作时间