修改spfile参数：

修改前，先备份

create pfile from spfile;

alter system set memory_target=0 scope=spfile;

alter system set sga_max_size=7g scope=spfile;

alter system set sga_target=7g scope=spfile;

alter system set pga_aggregate_target=4g scope=spfile;

alter system set processes=6000 scope=spfile;

alter system set DB_FILES=2000 scope=spfile;

alter system set log_archive_max_processes=2 scope=spfile;

alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;

alter system set open_cursors=3000 scope=spfile;

alter system set open_links_per_instance=48 scope=spfile;

alter system set open_links=100 scope=spfile;

alter system set parallel_max_servers=20 scope=spfile;

alter system set session_cached_cursors=200 scope=spfile;

alter system set undo_retention=10800 scope=spfile;

alter system set fast_start_mttr_target=300 scope=spfile;

alter system set deferred_segment_creation=false scope=spfile;

alter system set "_external_scn_logging_threshold_seconds"=600 scope=spfile;

alter system set "_external_scn_rejection_threshold_hours"=24 scope=spfile;

alter system set result_cache_max_size=0 scope=spfile;

alter system set "_cleanup_rollback_entries"=2000 scope=spfile;

alter system set parallel_force_local=true scope=spfile;

alter system set "_gc_policy_time"=0 scope=spfile;

alter system set "_clusterwide_global_transactions"=false scope=spfile;

alter system set "_library_cache_advice"=false scope=both;

alter system set db_cache_advice=off scope=both;

alter system set disk_asynch_io=false scope=spfile;

开启归档模式

创建归档文件目录：

mkdir /data/arch

chown oracle:oinstall /data/arch

alter system set log_archive_format='%d_%t_%s_%r.arc' scope=spfile;

alter system set log_archive_dest_1='location=/data/arch' scope=spfile;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

验证是否处于归档模式：

archive log list;

alter system archive log current;

查看操作系统是否有文件生成：

[oracle@hdentdb7 dbs]$ ls -l /data/arch

total 166300

-rw-r----- 1 oracle oinstall 170291200 May 25 19:59 1_1_977070699.arc

 

改变控制文件备份路径

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/data/backup/control%F';

 

查看数据目录位置

select file_name from dba_data_files;

FILE_NAME

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

/data/oradata/orcl/users01.dbf

/data/oradata/orcl/undotbs01.dbf

/data/oradata/orcl/system01.dbf

/data/oradata/orcl/sysaux01.dbf

查看临时表空间

select tablespace_name from DBA_TEMP_FILES;

TABLESPACE_NAME

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

TEMP

创建数据库表空间

create tablespace UCEX_SERVICE_TBS

datafile '/data/oradata/orcl/ucex_01.dbf'

size 50m autoextend on;

create tablespace SLUCEX_TBS

datafile '/data/oradata/orcl/slucex_01.dbf'

size 50m autoextend on;

create tablespace ACTIVITY_TBS

datafile '/data/oradata/orcl/activity_01.dbf'

size 50m autoextend on;

创建用户

CREATE USER UCEX_SERVICE_USER IDENTIFIED BY "eczedppZsoDj3040"

DEFAULT TABLESPACE UCEX_SERVICE_TBS

TEMPORARY TABLESPACE TEMP;

CREATE USER SLUCEXTWO IDENTIFIED BY "qUAksoOLB7dHKqO6"

DEFAULT TABLESPACE SLUCEX_TBS

TEMPORARY TABLESPACE TEMP;

CREATE USER UCEX_ACTIVITY_USER IDENTIFIED BY "lPjaKVhxR5RKovTZRC"

DEFAULT TABLESPACE ACTIVITY_TBS

TEMPORARY TABLESPACE TEMP;

授权

GRANT CONNECT,RESOURCE TO UCEX_SERVICE_USER;

GRANT CONNECT,RESOURCE TO UCEX_ACTIVITY_USER;

GRANT CONNECT,RESOURCE TO SLUCEXTWO;

grant dba to UCEX_SERVICE_USER;

grant dba to UCEX_ACTIVITY_USER;

grant dba to SLUCEXTWO;

 

修改密码策略

查看创建的用户使用的 profile

set linesize 160

set pagesize 999

col username for a30

col profile for a40

select username,PROFILE from dba_users where INHERITED='NO';

密码过期时间，从11g开始，oracle对数据库所有密码默认过期时间180天：

SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;

密码登陆错误次数，对于输入错误密码导致数据库账号被锁定：

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

查看profile

set linesize 160

set pagesize 999

col profile for a20

col resource_name for a40

col limit for a30

select profile,resource_name,limit from dba_profiles order by 1;