OCP license.E09.130704

STUDY/OCP 2013. 7. 6. 11:39
FGA Guidelines
• To audit all statements, use a null condition.
• Policy names must be unique.
• The audited table or view must already exist when you create the policy.
• If the audit condition syntax is invalid, an ORA-28112 error is raised when the audited object is accessed.
• If the audited column does not exist in the table, no rows are audited.
• If the event handler does not exist, no error is returned and the audit record is still created.

'STUDY > OCP' 카테고리의 다른 글

OCP license.E09.130704  (1) 2013.07.06
OCP license.E08.130703  (0) 2013.07.06
OCP license.E07.130702  (0) 2013.07.06
OCP license.E06.130701  (0) 2013.07.06
OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06
  1. BlogIcon 강 양 2015.01.15 16:47 신고 Addr Modify Reply

    oh 저도 이 자격증 있는데


OCP license.E08.130703

STUDY/OCP 2013. 7. 6. 11:38
User Manager

profile and Users (:: 유저의 profile을 설정한 후 유저 아이디를 관리함 )
Users are assigned only one profile at any given time.
Profile :
 - Control resource consumption
 - Manage account statue and password expiration
Edit Profile ::
  -  General tab  : CPU, Connect time, Idle time, I/O
  -  Password tab : 패스워드를 X 번 틀렸을 경우 T 시간만큼 lock // 일정 기간 후 패스워드 변경 // 일정 패스워드를 기억하여 동일한 데이터로 바꾸지 못하게 하는 history // 암호에 대한 복잡성

oracle user lock / unlock
SQL> alter user user_id account [ unlock | lock ]

Chapter 7. Managing Schema Objects

Naming Database Objects
  • The length of names must be from 1 to 30 bytes, with these exceptions:
    – Names of databases are limited to 8 bytes.
    – Names of database links can be as long as 128 bytes.
  • Nonquoted names cannot be Oracle-reserved words.
  • Nonquoted names must begin with an alphabetic character from your database character set.
  • Quoted names are not recommended.

Understanding Data Integrity
You can use the following integrity constraints to impose restrictions on the input of column values:
• NOT NULL: By default, all columns in a table allow null values. Null means the absence of a value. A NOT NULL constraint requires that a column of a table must contain no null values. For example, you can define a NOT NULL constraint to require that a value be input in the LAST_NAME column for every row of the EMPLOYEES table.
• UNIQUE Key: A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique—that is, no two rows of a table have duplicate values in a specified column or set of columns. For example, a UNIQUE key constraint is defined on the DEPARTMENT_NAME column of the DEPARTMENTS table to disallow rows with duplicate department names. Except for special circumstances, this is enforced with a unique index.
• PRIMARY KEY: Each table in the database can have at most one PRIMARY KEY constraint. The values in the group of one or more columns subject to this constraint constitute the unique identifier of the row. In effect, each row is named by its primary key values.
The Oracle server’s implementation of the PRIMARY KEY integrity constraint guarantees that both the following are true:
  - No two rows of a table have duplicate values in the specified column or set of columns.
  - The primary key columns do not allow nulls. That is, a value must exist for the primary key columns in each row.
Under normal circumstances, the database enforces the PRIMARY KEY constraints by using indexes. The primary key constraint created for the DEPARTMENT_ID olumn in the DEPARTMENTS table is enforced by the implicit creation of:
  - A unique index on that column
  - A NOT NULL constraint for that column
• Referential integrity constraints: Different tables in a relational database can be related by common columns, and the rules that govern the relationship of the columns must be maintained. Referential integrity rules guarantee that these relationships are preserved. 
A referential integrity constraint requires that for each row of a table, the value in the foreign key must match a value in a parent key.
As an example, a foreign key is defined on the DEPARTMENT_ID column of the EMPLOYEES table. It guarantees that every value in this column must match a value in the primary key of the DEPARTMENTS table. Therefore, no erroneous department
numbers can exist in the DEPARTMENT_ID column of the EMPLOYEES table. Another type of referential integrity constraint is called a self-referential integrity constraint. This type of foreign key references a parent key in the same table.
• Check constraints: A CHECK integrity constraint on a column or set of columns requires that a specified condition be true or unknown for every row of the table. If a data manipulation language (DML) statement results in the condition of the CHECK constraint evaluating to false, then the statement is rolled back.

Constraint States
To better deal with situations where data must be temporarily in violation of a constraint, you can designate a constraint to be in various states. An integrity constraint can be enabled (ENABLE) or disabled (DISABLE). If a constraint is enabled, the data is checked
as it is entered or updated in the database. Data that does not conform to the constraint’s rule is prevented from being entered. If a constraint is disabled, then the nonconforming data can be entered into the database. An integrity constraint can be in one of the following states:

• DISABLE NOVALIDATE: New as well as existing data may not conform to the constraint because it is not checked. This is often used when the data is from an already validated source and the table is read-only, so no new data is being entered into the table.
• DISABLE VALIDATE: If a constraint is in this state, then any modification of the constrained columns is not allowed because it would be inconsistent to have validated the existing data and then allow unchecked data to enter the table. This is often used when the existing data must be validated but the data is not going to be modified and the index is not otherwise needed for performance.
• ENABLE NOVALIDATE: New data conforms to the constraint but existing data is in an unknown state. This is frequently used so that existing constraint violations can be corrected, and at the same time, new violations are not allowed to enter the system.
• ENABLE VALIDATE: Both new and existing data conform to the constraint. This is the typical and default state of a constraint.

  • Locks prevent multiple sessions from changing the same data at the same time.
  • They are automatically obtained at the lowest possible level for a given statement.
  • They do not escalate. 
Locking Mechanism
  • High level of data concurrency:
    – Row-level locks for inserts, updates, and deletes
    – No locks required for queries
  • Automatic queue management
  • Locks held until the transaction ends (with the COMMIT or ROLLBACK operation)  // lock 은 해당 row만 하고 있기 때문에 다른 행은 상관없음.

Undo Redo 차이점
Redo는 re + do의 합성어 "다시 하다."
Undo는 un + doㅇ의 합성어 "원상태로 돌리다"

Redo는 오라클에서 기본적으로 복구의 역활을 한다. 오라클 서버에 무슨작업을 하든지 모두 리두에 기록이 된다.
Undo는 작업롤백과 읽기 일관성, 복구를 한다.

redo는 복구를 할때 사용자가 했던 작업을 그대로 다시 하지만
undo는 사용자가 했던 작업을 반대로 한다. 즉 사용자의 작업을 원상태로 돌린다.

[출처] Undo와 Redo의 차이점 |작성자 UNIX

  기록하는 내용
  되돌리기 위한 변화
  재생하기 위한 변화
  롤백, 읽기 일관성, 플래쉬 백
  언두 세그먼트
  리두 로그 파일
  멀티 유저의 읽기 일관성을 보호한다.
  데이터 손실 방지


'STUDY > OCP' 카테고리의 다른 글

OCP license.E09.130704  (1) 2013.07.06
OCP license.E08.130703  (0) 2013.07.06
OCP license.E07.130702  (0) 2013.07.06
OCP license.E06.130701  (0) 2013.07.06
OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06


OCP license.E07.130702

STUDY/OCP 2013. 7. 6. 11:37
Initialization Parameter Files
When you start the instance, an initialization parameter file is read. There are two types of parameter files:
• Server parameter file: This is the preferred type of initialization parameter file. It is a binary file that can be written to and read by the database server and must not be edited manually. It resides in the server that the Oracle database is executing on, and is persistent across shutdown and startup. This is often referred to as a server parameter file (SPFILE). The default name of this file, which is automatically sought at startup, is spfile<SID>.ora.
• Text initialization parameter file: This type of initialization parameter file can be read by the database server, but it is not written to by the server. The initialization parameter settings must be set and changed manually by using a text editor so that they are persistent across shutdown and startup. The default name of this file, which is automatically sought at startup if an SPFILE is not found, is init<SID>.ora. 
It is recommended that you create an SPFILE as a dynamic means of maintaining initialization parameters. By using an SPFILE, you can store and manage your initialization parameters persistently in a server-side disk file.

SQL> alter system set p_name=value scope=(both|memory|spfile);  // both: 파일과 현재 모두 , memory: 현 시점만, spfile: 재부팅 이후

Database Startup and Shutdown (EM version)

. oracle database shutdown 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

. nomount startup
  - database를 create하거나 control files을 re-creation 하거나 backup and recovery scenarios 상태일 때 이 단계까지 startup
  - 현재 단계에서 가능,불가능한 SQL 문
SQL> select name from v$datafile; (x)
SQL> select member from v$logfile; (x)
SQL> select table_name from user_tables; (x)
SQL> select instance_name, status from v$instance; (o)
SQL> startup nomount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size              1218992 bytes
Variable Size            100664912 bytes
Database Buffers       180355072 bytes
Redo Buffers              2973696 bytes

SQL> select instance_name, status from v$instance;

----------------         ------------
orcl                        STARTED

. mount startup
  - Control file을 instance에서 open 하는 단계
  - For example, the database must be mounted but must not be opened during the following tasks:
• Renaming data files (Data files for an offline tablespace can be renamed when the database is open.)
• Enabling and disabling online redo
  - 현재 단계에서 가능,불가능한 SQL 문
SQL> show parameter control_files
SQL> select instance_name, status from v$instance; (o)
SQL> select name from v$datafile; (o)
SQL> select member from v$logfile; (o)
SQL> select table_name from user_tables; (x)
  - nomount 단계에서 mount 하는 방법
SQL>  alter database mount;

Database altered.

-- Renaming or Relocating data files & redo logfiles;
eg. /u01/system01.dbf -> /u02/system01.dbf
1) os file copy
cp /u01/system01.dbf  /u02/system01.dbf
2) controlfile 내용 변경
SQL> alter database rename file 'old name' to 'new name';
SQL> alter database rename file '/u01/system01.dbf' to '/u02/system01.dbf';
3) os file 삭제
rm /u01/system01.dbf

.open startup
  - 전체 oracle database 를 사용하는 단계
  - mount 단계에서 open 하는 방법
SQL> alter database open;

Database altered.

shutdown mode
Shutdonw ModeAITN
Allows new connectionsXXXX
Waits until current ssions endXXXO
Waits until current transactions endXXOO
Forces a checkpoint and close filesXOOO

1) shutdown nomal (= shutdown )   :: allow new connections(X)
2) shutdown transactional :: user's statu = connection, 아무 것도 하고 있지 않으면 shutdown 진행
3) shutdown immediate
4) shutdown abort :: 비정상 종료 ; 현재 미완성중인 데이터베이스 commit, roolback 하지 않고 정지, 재부팅시 Instance Recovery를 이용해 작업

Restrict Database (DBA를 제외한 다른 user 의 access를 막음(recovery, modify 등의 작업을 할 때))
 - 처음 데이터베이스를 부팅할때 접근을 막으려면 startup할때 restrict를 하면 됨.
 - 만약 현재 접속중인 사람이 있다면 restircted session은 enable 의 방해가 받을 수 있으므로 session kill을 해줘야 함
SQL> conn / as sysdba
SQL> shutdown immediate
SQL> startup restrict 
SQL> alter system enable restricted session;
SQL> alter system disable restricted session;
SQL> select sid, serial#, username from v$session where username='HR';
       SID       SERIAL#    USERNAME
----------      ----------     -----------------------
       148                80    HR
SQL> alter system kill session '148, 3';
SQL> alter system disable restricted session;

Space Management in Tablespaces
• Locally managed tablespace:  // 관리차원에서 편해서 지금 많이 사용 됨
– Free extents are managed in the tablespace.
– A bitmap is used to record free extents.
– Each bit corresponds to a block or group of blocks.
– The bit value indicates free or used extents.
– The use of locally managed tablespaces is recommended.
• Dictionary-managed tablespace:
– Free extents are managed by the data dictionary.
– Appropriate tables are updated when extents are allocated or unallocated.
– These tablespaces are supported only for backward compatibility.

Administrator Authentication
Operating System Security
  • DBAs must have the OS privileges to create and delete files.
  • Typical database users should not have the OS privileges to create or delete database files.
Administrator Security
  • SYSBA and SYSOPER connections are authorized via password file or OS.
    – Password file authentication records the DBA user by name.
    – OS authentication does not record the specific user.
    – OS authentication takes precedence over password file authentication for SYSDBA and SYSOPER.

'STUDY > OCP' 카테고리의 다른 글

OCP license.E09.130704  (1) 2013.07.06
OCP license.E08.130703  (0) 2013.07.06
OCP license.E07.130702  (0) 2013.07.06
OCP license.E06.130701  (0) 2013.07.06
OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06