Oracle日常运维

2024年 5月 7日 86.7k 0

一般日常维护的都会放进碗里来的

数据库配置

修改Oracle内存

查看当前内存配置

show parameter memory,万一配置错了起不来,只能通过pfile文件重新启动

Oracle日常运维-1Oracle日常运维-2

修改memory_target

MEMORY_TARGET:oracle所能使用的最大内存,该参数可以动态调整

MEMORY_MAX_TARGET:MEMORY_TARGET参数所能动态设定的最大值,不能动态调整,需要重启数据库

SQL>alter system set memory_max_target=2048M scope=spfile;

SQL> alter system set memory_target =2048M scope=spfile;

Oracle日常运维-3

重启数据库后生效:

Oracle日常运维-4

连接数/会话

oracle限制某个用户的连接数

1. 查看resource_limit参数:

SQL> show parameter resource_limit

设置 RESOURCE_LIMIT参数为TRUE,开启资源限制:

alter system set resource_limit =TRUE;

该改变对密码资源无效,密码资源总是可用的

2. 创建PROFILE :

SQL>create profile sessnum limit sessions_per_user 20; --最大连接数限制为20

3. 将PROFILE指定给用户:

SQL>alter user test profile sessnum;

另外,SESSIONS_PER_USER不会像10g和11g那样限制12C中的并行从进程。

drop profile sessnum cascade;

当某个profile被删除时,如果这个profile已经被分配给某个用户,那么我们在删除的时候要加上cascade,并且已经被分配的用户的profile会被自己修改成default profile。

当前进程的连接数

select count(*) from v$process;

当前会话的连接数

select count(*) from v$session;

查看数据库的并发连接数

select count(*) from v$session where status='ACTIVE';

查询数据库允许的最大连接数

select value from v$parameter where name = 'processes';

修改数据库允许的最大连接数:

alter system set processes = 300 scope = spfile;

(需要重启数据库才能实现连接数的修改)

查看当前数据库建立的会话情况

select count(1),username from v$session group by username

结束/杀掉会话

SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'ETC';

ALTER SYSTEM KILL SESSION '13,772';

字符集

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

AMERICAN_AMERICA.ZHS16GBK

注意:以上两种字符集是一样的

select * fromnls_database_parameters(服务端字符)

select * fromnls_session_parameters(客户端字符)

查询语句:select userenv('language') from dual;(客户端字符)

查询结果:SIMPLIFIED CHINESE_CHINA.ZHS16GBK

oracle最大游标数修改和查询

查看ORACLE最大游标数

SQL> show parameter open_cursors;

查看当前打开的游标数目

SQL> select count(*) from v$open_cursor;

修改ORACLE最大游标数

SQL> alter system set open_cursors=1000 scope=both;

关闭密码过期

--1、查看用户的proifle是哪个,一般是default: (DBSNMP=》MONITORING_PROFILE)

SELECT username,PROFILE FROM dba_users;

--2、查看指定概要文件(如default)的密码有效期设置:

SELECT * FROM dba_profiles s WHERE resource_name='PASSWORD_LIFE_TIME';

--3、将密码有效期由默认的180天修改成“无限制”:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

ALTER PROFILE MONITORING_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;

查看Oracle版本 32位/64位

select * from v$version

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

启动数据库

startup nomount选项:(通常启动到这里可以做create database , create or recreate control file 或者是mount standby database等动作)

startup mount 选项 (通常启动到这里的目的有recover dtabase ; backup database ; rename db files ,change archivelog mode等)

startup open 选项:(打开数据文件,日志文件)

startup mount

alter database open read only;

startup mount

alter database open read write;

【注意:alter database *** 命令需要在database为mount状态下才行,如果数据库已启动,先要shutdown】

关闭数据库

重启数据库:

shutdown immediate;

startup;

使用shutdown的时候 normal和immediate可能都因为连接没有释放而造成数据库无法重启的现象。

shutdown abort;

startup

表名信息查询

select * from tabs where table_name like '%BAK'

重新配置em https=>http

emca -config dbcontrol db

zjts

1521

Oracle日常运维-5

http://opcserver252:1158/em/console

Oracle日常运维-6

监听日志(windows下监听日志超过4G监听会失败)

Oracle日常运维-7

日志位置:/home/oracle/app/oracle/diag/tnslsnr/primary/listener/trace

日志内容:

Oracle日常运维-8

1.>lsnrctl set log_status off # 先把日志状态停掉,这样就不会写监听器日志

2. 备份或者删除监听日志

3.>lsnrctl set log_status on # 重新打开日志,开始记录监听器日志。该文件会自动创建

4.>lsnrctl status #检查监听状态

归档操作

删除归档日志

rman target system/Zjts8356@zjts

list archivelog all;

delete archivelog all completed before ‘sysdate-3’;

主库执行后,备库已同步控制文件,所以备库直接删除物理日志好了

可以直接删除物理文件

直接系统删除后,控制文件中的记录还在

RMAN> crosscheck archivelog all;(将磁盘或者磁带上不存在的日志标记为expired 这个检查应该是控制文件跟实际文件进行的一个效验)

delete expired archivelog all; 删除过期的归档日志(这里的过期是指手动实际删除日志,这里的删除应该是控制文件中的记录删除)

(备库的日志基本不受控制,在备库上操作跟在主库上操作一致)

Linux下操作删除

[oracle@primary Desktop]$ rman

RMAN> connect target /

关闭归档

shutdown immediate;

startup mount;

alter database noarchivelog;

archive log list;

alter database open;

审计

查看有哪些对象审计权限和对哪些用户进行了对象权限审计

select * from dba_priv_audit_opts order by user_name;

*在11g中默认启用了对登录注销操作LOGON/LOGOFF的审计

select count(1),username from dba_audit_trail group by username --登录登出数量统计

select action_name,count(*) from dba_audit_trail group by action_name; --基本都是LOGON LOGOFF

取消此类审计:

noaudit session whenever successful;

可以取消对一些登录特别频繁的用户的审计:

SQL> noaudit session by ts_ahs; (无效)

SQL> noaudit session whenever successful;(有效)

查看审计参数

SQL> show parameter audit

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest string C:\APP\ADMINISTRATOR\ADMIN\ZJTS\ADUMP

audit_sys_operations boolean FALSE

audit_trail string DB

开启审计功能后,这个值会非常大

select count(*) sum from AUD$;

truncate table aud$; --清除数据

关闭审计功能

alter system set audit_trail=none scope=spfile;

shutdown immediate;

startup;

如果不想重启数据库,毕竟在用的东西哪能说重启就重启,可以使用如下:

SQL> noaudit all statements;

Noaudit succeeded.

SQL> noaudit all privilege;

Noaudit succeeded.

通过上面2条命令基本上都可以关闭了,还剩下一个EXEMPT ACCESS POLICY,单独noaudit一下就可以了:

SQL> noaudit EXEMPT ACCESS POLICY;

Noaudit succeeded.

自此所有开启的审计选项全部关闭了。

表和表空间

查看所有表的行数

select t.TABLE_NAME,t.NUM_ROWS from user_tables t;

如果你想刷新 user_tables 表,可以通过执行以下语句来实现:

EXEC DBMS_STATS.GATHER_TABLE_STATS('USER', 'TABLE_NAME');

其中,将 USER 替换为你自己的用户名, TABLE_NAME 替换为你想要刷新的表的名称。

视图表

--dba_tables : 系统里所有的表的信息,需要DBA权限才能查询

--all_tables : 当前用户有权限的表的信息

--user_tables: 当前用户名下的表的信息

查看有哪些表空间:

select tablespace_name from dba_tablespaces;

select tablespace_namefrom dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1') and contents not in ('TEMPORARY')

查询表空间和物理文件的对应关系

select file_name,tablespace_name,bytes from dba_data_files order by tablespace_name,file_name

删除非空表空间,包含物理文件

drop tablespace NCC_INDEX03 including contents and datafiles;

表空间重命名

alter tablespace jdmp_data rename to energy;

修改表空间的数据文件名字(联机状态)

alter tablespace lims_data offline;
alter tablespace lims_data rename datafile 'E:\OracleWorkspace\lims\LIMS.DBF' to 'E:\OracleWorkspace\lims\LIMS_DATA.DBF';
alter tablespace lims_data online;

查看表空间状态

select tablespace_name,status from dba_tablespaces;

查看是否BigFile 是否自增长 已分配容量,最大容量

SELECT T.TABLESPACE_NAME,T.bigfile,D.FILE_NAME,

D.AUTOEXTENSIBLE,D.BYTES/(1024 * 1024)||'MB',D.MAXBYTES/(1024 * 1024)||'MB',D.STATUS

FROM DBA_TABLESPACES T,DBA_DATA_FILES D

WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME

ORDER BY TABLESPACE_NAME,FILE_NAME;

Oracle日常运维-9

给某表空间添加数据文件

如果表空间不是大文件类型,会有最大容量限制,默认32GB,如果满了,则需要添加数据文件。

ALTER TABLESPACE HRRTF ADD DATAFILE

'/home/oracle/zjtsdb/orcl/hrrtf2.dbf' SIZE 100M

AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

查看表空间使用情况

select f.tablespace_name,

a.total "total (M)",

f.free "free (M)",

round((f.free / a.total) * 100) "% Free"

from (select tablespace_name, sum(bytes / (1024 * 1024)) total

from dba_data_files

group by tablespace_name) a,

(select tablespace_name, round(sum(bytes / (1024 * 1024))) free

from dba_free_space

group by tablespace_name) f

where a.tablespace_name = f.tablespace_name(+)

order by "% Free";

创建表空间CREATE BIGFILE TABLESPACE

CREATE BIGFILE TABLESPACE "HRRTF" DATAFILE '/home/oracle/zjtsdb/orcl/HRRTF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE "NNC_DATA01" DATAFILE '/home/oracle/zjtsdb/orcl/NNC_DATA01' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE "NNC_DATA02" DATAFILE '/home/oracle/zjtsdb/orcl/NNC_DATA02' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE "NNC_DATA03" DATAFILE '/home/oracle/zjtsdb/orcl/NNC_DATA03' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE "NNC_INDEX01" DATAFILE '/home/oracle/zjtsdb/orcl/NNC_INDEX01' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE "NNC_INDEX02" DATAFILE '/home/oracle/zjtsdb/orcl/NNC_INDEX02' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE BIGFILE TABLESPACE "NNC_INDEX03" DATAFILE '/home/oracle/zjtsdb/orcl/NNC_INDEX03' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

查看某用户用到了哪些表空间

select DISTINCT owner,tablespace_name from dba_extents where owner like 'NC50';

查看用户和其默认表空间

select username,default_tablespace,temporary_tablespace from dba_users where username='YFRTF';

查看表和表所在表空间

Select Table_Name, Tablespace_Name From Dba_Tables Where Tablespace_Name = 'NNC_INDEX01';

创建数据库 dbca

存储过程

查看有多少存储过程

select count(*) from user_objects where object_type='PROCEDURE';

用户账号

创建用户

Create user yfrtf identifiedby zjts8356;

Grant connect,resource,dba to yfrtf;

Alter user yfrtf default tablespace hrrtf;

删除用户

drop user : 仅仅是删除用户,drop user username cascade :会删除此用户名下的所有表和视图

查看用户状态

select username,account_status from dba_users;

查看有哪些用户

select * from all_users;

解锁账户

ALTER USER DANGZHENG ACCOUNT UNLOCK;

修改密码

修改报警用户的密码

alter user zjts identified by zjts8356;

查看当前用户所有对象和数量统计

select object_type , count(*) from user_objects group by object_type

查看当前用户下有多少表(登录后查询)

select count(*) from user_tables

数据备份还原

1、数据泵工具运行于服务器端,相比客户端的exp/imp其性能更好,并能实现exp/imp的全部功能。

  2、通过使用exclude,include,content等参数,数据泵可以为数据及数据对象提供更细微级别的选择性。

  3、通过设定数据库版本号,数据泵可以兼容老版本的数据库系统。

  4、并行执行。

  5、通过estimate_only参数,数据泵可以预估导出作业所需的磁盘空间。

  6、支持分布式环境中通过数据库连接实现导入\导出。

  7、支持导入时重新映射功能(即将对象导入到新的目标数据文件、架构及表空间等)。

  8、支持元数据压缩及数据采样。

创建逻辑目录

create directory bakdata as '/home/oracle/bak';

grant read,write on directory bakdata to system;

exp/imp 数据备份还原

还原数据:

imp yfrtf/zjts8356 file=/home/oracle/20180723_0101.dmp feedback=10000 buffer=40960000 ignore=y full=y log=/home/oracle/impYfrtf.log

还原指定表:

imp yfrtf/zjts8356 file=/home/oracle/20180727_0101.dmp feedback=10000 buffer=40960000 ignore=y log=/home/oracle/impAtt.log tables=attdaypay

远程备份

exp yfrtf/zjts8356@192.168.12.88/orcl direct=y recordlength=65535 file=%backuppath%\yfrtf%curdate%.dmp log=%backuppath%\%curdate%.log tables=(peremployee)

expdp/impdp数据泵

只导出表结构:

expdp nc50/nc50 directory=bakdata dumpfile=nc02.dmp CONTENT=METADATA_ONLY tables=XX_XLOG,SM_OPERATELOG

只导出指定表

expdp 用户名/密码@oracl directory=bakdata parallel=3 dumpfile=energy.dmp tables=Table1,Table2

导出全部表但不包括指定的表:

expdp nc50/nc50 schemas=NC50 directory=bakdata dumpfile=NC50_%U.dmp logfile=NC50.log parallel=8 EXCLUDE=TABLE:\"IN ('XX_XLOG','SM_OPERATELOG','BD_CUMANDOC_0531BAK','BD_PRODUCE_20160910BAK','FA_CARDHISTORY_20131228BAK','GL_BANKRECEIPT_BAK20170216','GL_FREEVALUEBAK20140325','PUB_BILLTEMPLET_B20140627BAK','SO_SALEORDER_B_20151222BAK','GL_DETAILBACKUP')\"

开8线程导出表:

expdp system/Zjts8356 schemas=YFRTF directory=bakdata dumpfile=YFRTF_%U.dmp logfile=YFRTF.log parallel=8;

导入数据:

impdp system/zjts8356 schemas=YFRTF directory=bakdata dumpfile=YFRTF_%U.dmp logfile=YFRTF_imp.log parallel=8;

导入数据并变更用户名

从user1用户导出的数据,要导入到user2用户下:

REMAP_SCHEMA=user1:user2

示例:

Oracle日常运维-10

导入数据并变更表空间

remap_tablespace=原表空间名称:新表空间

停止数据泵任务

select job_name,state from dba_datapump_jobs;
expdp system/Zjts8356 attach=SYS_EXPORT_SCHEMA_01
stop_job
yes

复制备份文件至另一服务器

scp /home/oracle/dbbak/pz.dmp oracle@192.168.12.99:/home/oracle/dbbak

查杀僵死session

查找是否有用户类型的锁

TM DML排队

TX 事务处理排队

UL 用户提供

select * from v$lock where type in ('TM','TX','UL')

再查相应的sid和serial

select sid,serial#,terminal,program,event from v$session where sid=168

杀掉它

alter system kill session '168,62660'

--若出现错误ORA-00031: 标记要终止的会话,则

SELECT P.SPID, S.SID, S.SERIAL#, S.USERNAME, S.PROGRAM FROM V$PROCESS P,v$session S

WHERE P.ADDR=S.PADDR AND S.STATUS='KILLED';

系统级杀死进程

--ORAKILL用法:

orakill sid thread

其中:

sid:表示要杀死的进程属于的实例名

thread:是要杀掉的线程号,即第3步查询出的spid。

例:c:>orakill orcl 12345

kill –9 SPID;(linux)

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论