oracle创建数据库环境初始化

1.关闭防火墙

service iptables stop

chkconfig iptables off

1. 永久性生效

开启：chkconfig iptables on

关闭：chkconfig iptables off

2. 即时生效，重启后失效

开启：service iptables start

关闭：service iptables stop

3.redhat7关闭防火墙

systemctl stop firewalld
systemctl disable firewalld
 

 

 

2.修改系统表空间大小

alter database datafile '/u01/app/oracle/XXX/undotbs01.dbf' RESIZE 20G; 

alter database datafile '/u01/app/oracle/XXX/sysaux01.dbf' RESIZE 10G; 

alter database datafile '/u01/app/oracle/XXX/system01.dbf' RESIZE 2G; 

alter database datafile '/u01/app/oracle/XXX/users01.dbf' RESIZE 1G; 

create tablespace CAN_DATA  datafile '/u01/app/oracle/HNSCAN/can_data01.dbf' SIZE 30G;

create tablespace CAN_IDEX  datafile '/u01/app/oracle/HNSCAN/can_idex01.dbf' SIZE 30G;

ALTER TABLESPACE CAN_DATA add DATAFILE  '/u01/app/oracle/HNSCAN/can_data02.dbf' SIZE 30G;

ALTER TABLESPACE CAN_IDEX add DATAFILE  '/u01/app/oracle/HNSCAN/can_idex02.dbf' SIZE 30G;

--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

 

3.创建用户

create user can identified by can;
alter user can default tablespace can_data;

grant create session to can;
grant create sequence to can;
grant create table to can;
grant unlimited tablespace to can;
grant create procedure to can;

GRANT debug any procedure, debug connect session TO can;

 

grant create trigger to can;
grant select any table to can;
grant create view to can;

4.修改参数

alter system set processes = 1000 scope = spfile;
alter profile MONITORING_PROFILE limit connect_time unlimited;   --（或跟一个时间值，如1000，单位为分钟） 
alter profile MONITORING_PROFILE limit idle_time unlimited;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER SYSTEM SET db_recovery_file_dest_size=900g scope=both;   -- 如果有归档日志的话
alter system set db_files=2000 scope=spfile;

 

--内存管理

mount -t tmpfs shmfs -o size=32G /dev/shm
vim /etc/fstab
tmpfs /dev/shm tmpfs  defaults,size=32G      0 0

show parameter memory_target;
show parameter sga_target;
show parameter pga_aggregate_target;
alter system set memory_target=14G scope=spfile;
alter system set MEMORY_MAX_TARGET=15G scope=spfile;
alter system set sga_target=0 scope=spfile;
alter system set pga_aggregate_target=0 scope=spfile;

 5.redolog设置

select * from v$log;
alter system switch logfile;

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4; -- 如果无法切换

alter database drop logfile group 1;
ALTER DATABASE ADD LOGFILE GROUP 1('/data/GZBUS/redo001.log') SIZE 2G;

alter database drop logfile group 2;
ALTER DATABASE ADD LOGFILE GROUP 2('/data/GZBUS/redo002.log') SIZE 2G;

alter database drop logfile group 3;
ALTER DATABASE ADD LOGFILE GROUP 3('/data/GZBUS/redo03.log') SIZE 2G;

6.在centOS7 中Oracle缺少文件

/u01/app/oracle/product/11.2.0/dbhome_1/bin空文件
e2eme
emagtm
emagtmc
emdctl
emtgtctl2
nmei
nmhs
nmocat
nmosudo
nmupm
ojmxtool