[관리] 계정/ArchiveLog

계정 관리

요약  

user (default/Temp Ts,Profile,expired,status..) 정보 확인

select username,default_tablespace "DFTS", temporary_tablespace "TPTS", profile 
from dba_users
where username='SMSUSER';

user (ROLE) 권한 조회

select * from dba_sys_privs
where grantee='TROLE'; -- trole

사용자에게 부여된 role 조회

select * from dba_role_privs
where grantee='SMSUSER';

profile 조회

select *from dba_profiles
where profile='SAMPLE_PROF';

default tablepace생성

create tablespace ts_sms
datafile '/oradata/EHR/ts_sms01.dbf' size 10M;

temporary tablespace 생성

create temporary tablespace temp_sms
tempfile '/oradata/EHR/temp_sms01.dbf' size 10M;

profile 관리 (생성 /삭제)

create profile sample_prof limit
failed_login_attempts 3
password_lock_time 5
password_life_time 15
password_reuse_time 15;

drop profile sample_prof cascade;-- 기사용 사용자는 default로 변경

role 관리

create role trole;
grant create session,create table to trole;

사용자생성

create user smsuser
identified by "smspwd"
default tablespace ts_sms
temporary tablespace  temp_sms  
quota unlimited on ts_sms
quota 0m on system --system tablepsace cannot use
profile sample_prof
account unlock;
grant resource,connect,trole to smsuser;

 

예시- 신규 User/Role/Profile 생성 및 확인

•  
  SQL> create tablespace ts_sms

  datafile '/oradata/EHR/ts_sms01.dbf' size 10M;  

   

         Tablespace created.

   

  SQL> create temporary tablespace temp_sms

  tempfile '/oradata/EHR/temp_sms01.dbf' size 10M; 

   

         Tablespace created.

   

  SQL> create profile sample_prof limit

  failed_login_attempts 3

  password_lock_time 5

  password_life_time 15

  password_reuse_time 15; 

   

         Profile created.

    

  SQL> create user smsuser

  identified by "smspwd"

  default tablespace ts_sms

  temporary tablespace  temp_sms  

  quota unlimited on ts_sms

  quota 0m on system --system tablepsace cannot use

  profile sample_prof

  account unlock;  

   

         User created.

     

  SQL> grant resource,connect to smsuser;

   

         Grant succeeded.

   

  SQL> create role trole;

   

         Role created.

   

  SQL> grant create session,create table to trole;

   

         Grant succeeded.

   

  SQL> grant resource,connect,trole to smsuser;

   

         Grant succeeded.

      

   

  SQL> select tablespace_name,bytes/1024/1024 MB,file_name

  from dba_data_files;

   

         TABLESPACE    MB FILE_NAME

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

         SYSTEM    430 /oraindex/EHR/system01.dbf

         UNDOTBS1  1024 /oradata/EHR/undotbs01.dbf

         SYSAUX    210 /oradata/EHR/sysaux01.dbf

         TS_SMS     10 /oradata/EHR/ts_sms01.dbf

         USERS      5 /oradata/EHR/users01.dbf

         UNDO01     10 /oradata/EHR/undo01.dbf

   

         6 rows selected.

   

  SQL> select username,default_tablespace "DFTS", temporary_tablespace "TPTS", profile 

  from dba_users

  where username='SMSUSER';  

   

         USERNAME         DFTS         TPTS        PROFILE

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

         SMSUSER          TS_SMS         TEMP_SMS        SAMPLE_PROF

     

   

  SQL> select * from dba_role_privs

  where grantee='SMSUSER'; 

   

         GRANTEE          GRANTED_ROLE        ADM DEF

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

         SMSUSER          RESOURCE         NO  YES

         SMSUSER          TROLE         NO  YES

         SMSUSER          CONNECT         NO  YES

    

   

  SQL> select * from dba_sys_privs

  where grantee='TROLE'; 

   

         GRANTEE          PRIVILEGE    ADM

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

         TROLE          CREATE TABLE    NO

         TROLE          CREATE SESSION    NO

   

  SQL> select *from dba_profiles

  where profile='SAMPLE_PROF'; 

   

         PROFILE          RESOURCE_NAME   RESOURCE LIMIT

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

         SAMPLE_PROF         COMPOSITE_LIMIT   KERNEL  DEFAULT

         SAMPLE_PROF         SESSIONS_PER_USER  KERNEL  DEFAULT

         SAMPLE_PROF         CPU_PER_SESSION   KERNEL  DEFAULT

         SAMPLE_PROF         CPU_PER_CALL   KERNEL  DEFAULT

         SAMPLE_PROF         LOGICAL_READS_PER_SESSION KERNEL  DEFAULT

         SAMPLE_PROF         LOGICAL_READS_PER_CALL  KERNEL  DEFAULT

         SAMPLE_PROF         IDLE_TIME   KERNEL  DEFAULT

         SAMPLE_PROF         CONNECT_TIME   KERNEL  DEFAULT

         SAMPLE_PROF         PRIVATE_SGA   KERNEL  DEFAULT

         SAMPLE_PROF         FAILED_LOGIN_ATTEMPTS  PASSWORD 3

         SAMPLE_PROF         PASSWORD_LIFE_TIME  PASSWORD 15

   

         PROFILE          RESOURCE_NAME   RESOURCE LIMIT

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

         SAMPLE_PROF         PASSWORD_REUSE_TIME  PASSWORD 15

         SAMPLE_PROF         PASSWORD_REUSE_MAX  PASSWORD DEFAULT

         SAMPLE_PROF         PASSWORD_VERIFY_FUNCTION  PASSWORD DEFAULT

         SAMPLE_PROF         PASSWORD_LOCK_TIME  PASSWORD 5

         SAMPLE_PROF         PASSWORD_GRACE_TIME  PASSWORD DEFAULT

   

         16 rows selected.

 

 

Archive Log 설정

설명

• Archive redo log mode 변경 순서 (spfile)
  • Shutdown -> parameter file 수정 ->Mount -> mode 변경 ->  Open

요약

설정체크

archive log list;

parameter file 수정

alter system set log_archive_dest_1= 'location=/data/arc1' scope=spfile; 
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

모드변경

alter database archivelog;

예시- Archive Log 설정

SQL> archive log list;

       Database log mode No Archive Mode

       Automatic archival Disabled

       Archive destination USE_DB_RECOVERY_FILE_DEST

       Oldest online log sequence 30

       Current log sequence 32

 

SQL> alter system set log_archive_dest_1= 'location=/data/arc1' scope=spfile;

 

       System altered.

 

SQL> alter system set log_archive_dest_2= 'location=/data/arc2' scope=spfile;

 

       System altered.

 

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

 

       System altered.

 

SQL> shutdown immediate;

       Database closed.

       Database dismounted.

       ORACLE instance shut down.

SQL> startup mount;

       ORACLE instance started.

 

       Total System Global Area 1610612736 bytes

       Fixed Size 2096632 bytes

       Variable Size 385876488 bytes

       Database Buffers 1207959552 bytes

       Redo Buffers 14680064 bytes

       Database mounted.

SQL> archive log list;

       Database log mode No Archive Mode

       Automatic archival Disabled

       Archive destination /data/arc2

       Oldest online log sequence 31

       Current log sequence 33

 

SQL> alter database archivelog;

 

       Database altered.

 

 

SQL> alter database open;

 

       Database altered.

 

SQL> archive log list;

       Database log mode Archive Mode

       Automatic archival Enabled

       Archive destination /data/arc2

       Oldest online log sequence 31

       Next log sequence to archive 33

       Current log sequence 33