Oracle11g常用运维命令
1.常用命令
本文中假定 oracle sid=orcl , 版本为11.2.0。
启停:
#start /stop oracle server:
SQL> shutdown immediate
SQL> startup -- startup nomount; startup mount;
#start:
lsnrctl start
sqlplus /nolog
connect sys/oracle as sysdba
startup
emctl start dbconsole
#stop:
emctl stop dbconsole
lsnrctl stop
shutdown immediate
##查看监听listener
#在数据库服务器上，可以通过lsnrctl工具检查监听状态和服务信息，
su - oracle
echo $ORACLE_SID
Lnrctl start|stop [listener-name]
lsnrctl status LISTENER
lsnrctl status LISTENER_SCAN1
lsnrctl service
#
nc -z 127.0.0.1 1521
tnsping ORCL@127.0.0.1
locate listener.ora
lsnrctl start
lsnrctl status
常用命令:
常用命令
su - oracle
env |grep ora -i
echo $ORACLE_HOME
#ls -l $ORACLE_HOME/bin
cat $ORACLE_HOME/install/portlist.ini
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938
#sqlplus
sqlplus sys/oracle as sysdba
sqlplus /nolog
set line 1000;
set pagesize 100;
set tab off;
show user;
show parameter;
#1.实例名：
select instance_name from sys.v$instance;
#2.服务名：
select global_name from global_name;
SQL>select * from product_component_version ;
SQL>select name,type,value from v$parameter where name='sga_max_size';
SQL> show parameter db_writer_processes;
SQL> show sga;
Total System Global Area 8.5516E+10 bytes
Fixed Size 2262656 bytes
Variable Size 3.1139E+10 bytes
Database Buffers 5.4224E+10 bytes
Redo Buffers 151117824 bytes
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
SQL> select pid,username,program from v$process where background = '1' order by program;
PID USERNAME PROGRAM
---------- --------------- ------------------------------------------------
24 oracle oracle@LeDB-124133063 (ARC0)
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /orcl/archivelog_dest
#session
会话信息
SELECT SESSIONS_CURRENT,SESSIONS_HIGHWATER FROM v$license;
辅助查询，实例当前会话数和启动最高连接会话数量。
SELECT inst_id,username,COUNT(*) FROM gv$session GROUP BY inst_id,username;
查询数据库连接数以实例和用户分组。
参数检查
SELECT name, value FROM v$parameter WHERE name='open_cursors';
查询给定参数的设置值，示例参数缺省值为300，通常中等规模数据库推荐设置为1000。
参数修改
ALTER SYSTEM SET undo_retention=3600 COMMENT='default 900' SID='*' SCOPE=both;
安全停库
SQL>alter system checkpoint;
SQL>alter system archive log current;
SQL>shutdown immediate;
如果数据库出现异常需要重新启动，可以通过示范命令执行检查点、归档命令，然后尝试以立即方式关闭数据库
2.DB系统级别
查看各个实例内存分配情况
SELECT COMPONENT,CURRENT_SIZE/1024/1024,MIN_SIZE FROM V$SGA_DYNAMIC_COMPONENTS;
查看后台运行进程
select * from v$bgprocess where paddr not like '00';
查看正在运行的sql，并清理
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
from v$session a, v$sqlarea b
where a.sql_address = b.address;
select saddr,sid,serial#,paddr,username,status from v$session where username is not null and sid=1612;
alter system kill session '1612,29251';
或者
select 'alter system kill session '''||vs.sid||','||vs.SERIAL#||',@'||vs.INST_ID||''';'
from gV$session vs
where username=upper('scott');
查看不同用户的连接数：
select username,count(username) from v$session where username is not null group by username;
查看oralce各种字符集
oralce字符集包括server、client、dump文件字符集，三个组成部分(语言、地域和字符集)，
Language： 指定服务器消息的语言， 影响提示信息是中文还是英文
Territory： 指定服务器的日期和数字格式，
Charset： 指定字符集。
如:AMERICAN _ AMERICA. ZHS16GBK
所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据，前面影响的只是提示信息是中文还是英文。
--oracle server端的字符集
select userenv('language') from dual;
--dmp文件的字符集
select nls_charset_name(to_number('0354','xxxx')) from dual;
--oracle client端的字符集
echo $NLS_LANG
set nls_lang=AMERICAN_AMERICA.ZHS16GBK
--补充
--数据库服务器字符集（来源于props$，是表示数据库的字符集）
select * from nls_database_parameters;
--客户端字符集环境（其来源于v$parameter）
select * from nls_instance_parameters;
--会话字符集环境（来源于v$nls_parameters，表示会话自己的设置）
select * from nls_session_parameters;
查看各个资源限制
select * from v$resource_limit ;
日志管理：
set linesize 1000
col MEMBER for a60
日志文件信息
select log.GROUP#,log.THREAD#,log.BYTES/1024/1024 as "(MB)",log.ARCHIVED,log.STATUS,logfile.TYPE,logfile.MEMBER
from v$logfile logfile,v$log log
where log.GROUP#=logfile.GROUP#;
standby日志文件信息
select log.GROUP#,log.THREAD#,log.BYTES/1024/1024 as "(MB)",log.ARCHIVED,log.STATUS,logfile.TYPE,logfile.MEMBER
from v$logfile logfile,v$standby_log log
where log.GROUP#=logfile.GROUP#;
v$logfile（redolog和standbylog位置和状态），v$log只记录redo的大小和其相关信息，v$log只记录standby log大小和相关信息
增删日志
添加新的日志
alter database add logfile group 1 ('/orcl/app/oracle/oradata/orcl/redo01.log','/orcl/app/oracle/oradata/orcl/redo01a.log') size 1G;
alter database add logfile group 1 ('/orcl/app/oracle/oradata/orcl/redo01.log') size 1G;
对已有组添加日志
alter database add logfile member '/orcl/app/oracle/oradata/orcl/redo01.log' to group 1;
删除日志或日志组
alter database drop logfile member '/data2/onlinelog/redo01.log';
alter database drop logfile group 1;
添加standby log，基本和添加日志一样
alter database drop standby logfile group 7;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/orcl/app/oracle/oradata/orcl/stdbyredo01.log') size 1G;
alter system switch logfile;
3.对象级别
查看表结构，分区表信息等
查看表结构，分区表信息等
select dbms_metadata.get_ddl('TABLE','SMS_TO_ISMG7','SMS9885') FROM DUAL;
查看表空间建立语句
set long 90000
select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;
查看视图定义
select * from user_views;
select dbms_metadata.get_ddl('VIEW','SMS_TO_ISMG7_ALL') FROM DUAL;
查看每个表index的信息
select * from dba_indexes where TABLE_NAME=upper('sms_from_ismg');
select * from dba_ind_columns where index_name=upper('UN_SMS_FROM_ISMG');
查看表空间情况
set linesize 200
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used",c.contents,c.extent_management
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b,
(select tablespace_name,contents,extent_management from dba_tablespaces) c
where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc;
表空间对应的物理文件
select * from dba_data_files where TABLESPACE_NAME='WMS';
扩容表空间
alter tablespace PTPRC add datafile '/data/oradata/orcl/ptprc02.dbf' size 500M autoextend on ;
查看表情况
select segment_name,bytes/1024/1024,tablespace_name,PARTITION_NAME
from user_segments
where segment_type = 'TABLE';
表占用空间由大到小
select tablespace_name,segment_name,bytes/1024/1024 usesize from dba_segments
order by usesize desc;
查看SYSAUX空间存储了那些非核心功能数据
select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME from V$SYSAUX_OCCUPANTS;
查看数据库版本
select * from v$version;
各种文件查看
spfile
show parameter spfile;
控制文件
select * from v$controlfile;
日志文件
select * from v$log;
select * from v$logfile;
表空间&及其文件
col FILE_NAME for a50
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024,STATUS from dba_data_files order by TABLESPACE_NAME,FILE_NAME;
数据库启动（nomount（spfile\pfile）、mount(controlfile)、open）
4.用户管理相关
--快速创建账号
create user dbtest identified by dbtest;
grant connect,resource to dbtest;
--用户和其默认表空间
set linesize 300
select username,default_tablespace,temporary_tablespace from dba_users where DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM');
select username,default_tablespace,temporary_tablespace from dba_users where DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM') and USERNAME not in ('SCOTT','ORACLE_OCM','XS$NULL','MDDATA','DIP','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','MONITOR');
--查看当前用户所有表
select * from tab;
--查看某个用户模式下所有对象
SELECT owner,object_name, object_type FROM dba_objects WHERE owner= 'SCOTT';
--查看用户的所有role权限
select granted_role from dba_role_privs where grantee='BOSS_NEW_W';
--查看用户具有的具体权限
select privilege from dba_sys_privs where grantee='BOSS_NEW_W'
union
select privilege from dba_sys_privs where grantee in
(select granted_role from dba_role_privs where grantee='BOSS_NEW_W');
--补充
SELECT grantee,privilege,admin_option FROM dba_sys_privs WHERE grantee IN ('SCOTT') ORDER BY grantee;
--用户创建语句,权限语句
select dbms_metadata.get_ddl('USER','BOSS_NEW_W') from dual;
select dbms_metadata.get_granted_ddl('ROLE_GRANT','BOSS_NEW_W') from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','BOSS_NEW_W') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_tab_privs);
查看Role创建语句
set linesize 1800
set pages 10000
set long 90000
--设置按单词换行
col a for a200 wrapped word
查看Role创建语句
-- Create the roles
SELECT DBMS_METADATA.GET_DDL('ROLE', 'SSE_ROLE') a from dual;
-- Roles which are granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'SSE_ROLE') a from dual;
-- System privileges granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'SSE_ROLE') a from dual;
-- Table privileges granted to roles
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'SSE_ROLE') a from dual;
--解锁用户
select username,account_status from dba_users; --查看用户状态
alter user SCOTT account unlock; --解开LOCKED
alter user SCOTT identified by triger;--解开EXPIRED
oracle一个创建用户、创建表空间、授权、建表的完整过程
create user dbtest identified by dbtest;
alter user dbtest identified by dbtest;
select username,default_tablespace from dba_users;
create tablespace dbtest_tbs datafile '/orcl/app/oracle/oradata/orcl/dbtest.dbf' size 200M;
alter user dbtest default tablespace dbtest_tbs;
grant create session,create table,create view,create sequence,unlimited tablespace to dbtest;
conn dbtest/dbtest;
select *from session_privs;
drop user dbtest cascade;
drop tablespace dbtest_TBS including contents and datafiles;
5.高可用
备库切换为主
$ sqlplus / as sysdba
停止应用恢复模式
alter database recover managed standby database finish;
转换standbydb为primary db
alter database commit to switchover to primary;
开启数据库
alter database open;
select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
shutdown /normoal/immediate/abort
6. DATAGUARD
DATAGUARD各种状态查看
set linesize 10000
# query to show information about the protection mode, the protection level, the role of the database, and switchover status:
查看当前DG的角色、保护模式等等（常用）
SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
# On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log.
查看备库归档情况
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Or
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
# On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.
根据V$ARCHIVED_LOG的SEQUENCE#，查看归档是否被应用
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
# Query the physical standby database to monitor Redo Apply and redo transport services activity at the standby site.
查看备库Redo Apply和日志传输服务释放正常（常用）
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
# To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view.
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
# The V$DATAGUARD_STATUS fixed view displays events that would typically be triggered by any message to the alert log or server process trace files.
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
# Determining Which Log Files Were Not Received by the Standby Site.
查看备库那些日志文件没有接收
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);
# If a delayed apply has been specified or an archive log is missing then switchover may take longer than expected.
# Check v$managed_standby
select process, status, sequence# from v$managed_standby;
# OR alternatively:
select name, applied from v$archived_log;
查看主备同步状态是否正常，1）查看主的归档号 2）查看备的归档号和MRP0进程状态(APPLYING_LOG) 。或者直接查看备库当前接收和最后应用的归档号和MRP0进程状态(APPLYING_LOG)
-- Verify that the primary has archived a log for the thread(s); On the primary database issue the following;
SQL> SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE# =
(SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;
-- Verify that the last sequence# received and the last sequence# applied to standby database.
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
