What is the Redo Log?

Redo Logs consist of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has associated online redo logs to protect the database in case of an instance failure.

Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

Redo Log Data Dictionary Views

The following views provide information on redo logs.

View	Description
V$LOG	Displays the redo log file information from the control file
V$LOGFILE	Identifies redo log groups and members and member status
V$LOG_HISTORY	Contains log history information

The following query returns the control file information about the redo log for a database.

SELECT * FROM V$LOG;

GROUP# THREAD# SEQ BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

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

1 1 10605 1048576 1 YES ACTIVE 11515628 16-APR-00

2 1 10606 1048576 1 NO CURRENT 11517595 16-APR-00

3 1 10603 1048576 1 YES INACTIVE 11511666 16-APR-00

4 1 10604 1048576 1 YES INACTIVE 11513647 16-APR-00

To see the names of all of the member of a group, use a query similar to the following:

SELECT * FROM V$LOGFILE;

GROUP# STATUS MEMBER

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

1 D:\ORANT\ORADATA\IDDB2\REDO04.LOG

2 D:\ORANT\ORADATA\IDDB2\REDO03.LOG

3 D:\ORANT\ORADATA\IDDB2\REDO02.LOG

4 D:\ORANT\ORADATA\IDDB2\REDO01.LOG

If STATUS is blank for a member, then the file is in use.

Important Question No:2

V1 What are constraints in SQL ? What are various types of it ? Give SQL

Commands to apply and revoke constraints in SQL. (May 2019)(7)

Answer No.2

SQL constraints are used to specify rules for data in a table.

SQL Create Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

· NOT NULL - Ensures that a column cannot have a NULL value

· UNIQUE - Ensures that all values in a column are different

· PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

· FOREIGN KEY - Uniquely identifies a row/record in another table

· CHECK - Ensures that all values in a column satisfies a specific condition

· DEFAULT - Sets a default value for a column when no value is specified

· INDEX - Used to create and retrieve data from the database very quickly

SQL NOT NULL Constraint

By default, a column can hold NULL values.

The NOT NULL constraint enforces a column to NOT accept NULL values.

This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

SQL NOT NULL on CREATE TABLE

The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created:

Example

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

SQL NOT NULL on ALTER TABLE

To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL:

ALTER TABLE Persons
MODIFY Age int NOT NULL;

DROP CONSTRAINT

The DROP CONSTRAINT command is used to delete a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint.

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

MySQL:

ALTER TABLE Persons
DROP INDEX UC_Person;

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY;

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

MySQL:

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

Important Question No:3

V1 What do you mean by table space ? Explain various commands related

To table space management. (May 2019)(8)

V2 Explain, how to create table space and data files in oracles.

(May 2017)(7)

Answer No.2

Oracle divides a database into one or more logical storage units called tablespaces.

Each tablespace consists of one or more files called datafiles. A datafile physically stores the data objects of the database such as tables and indexes on disk.

In other words, Oracle logically stores data in the tablespaces and physically stores data in datafiles associated with the corresponding tablespaces.

The following picture illustrates the relationship between a database, tablespaces, and datafiles:

By using tablespaces, you can perform the following operations:

· Control the storage size allocated for the database data.

· Grant specific space quotas to the database users

· Control the availability of data by taking tablespaces online or offline (more on this later).

· Improve the performance of the database by allocating data storage across devices.

· Perform partial database backup or recovery.

Default tablespaces in Oracle

Oracle comes with the following default tablespaces: SYSTEM, SYSAUX, USERS, UNDOTBS1, and TEMP.

· The SYSTEM and SYSAUX tablespaces store system-generated objects such as data dictionary tables. And you should not store any object in these tablespaces.

· The USERS tablespace is helpful for ad-hoc users.

· The UNDOTBS1 holds the undo data.

· The TEMP is the temporary tablespace which is used for storing intermediate results of sorting, hashing, and large object processing operations.

Online and Offline Tablespaces

A tablespace can be online or offline. If a tablespace is offline, you cannot access data stored in it. On the other hand, if a tablespace is online, its data is available for reading and writing.

Note that the SYSTEM tablespace must always be online because it contains the data dictionary that must be available to Oracle.

Normally, a tablespace is online so that its data is available to users. However, you can take a tablespace offline to make data inaccessible to users when you update and maintain the applications.

In case of some errors such as hardware failures, Oracle automatically takes an online tablespace offline. Any attempt to access data in offline tablespace will result in an error.

Read-Only Tablespaces

The read-only tablespaces allow Oracle to avoid performing backup and recovery of large, static parts of a database. Because Oracle doesn’t update the files of a read-only tablespace, you can store the files on the read-only media.

Oracle allows you to remove objects such as tables and indexes from a read-only tablespace. However, it does not allow you to create or alter objects in a read-only tablespace.

When you create a new tablespace, it is in the read-write mode. To change a tablespace to read-only tablespace, you use the ALTER TABLESPACE command with the READ ONLY option.

Introduction to the CREATE TABLESPACE statement

The CREATE TABLESPACE statement allows you to create a new tablespace. The following illustrates how to create a new tablespace named tbs1 with size 1MB:

CREATE TABLESPACE tbs1

DATAFILE 'tbs1_data.dbf'

SIZE 1m;

In this statement:

· First, specify the name of the tablespace after the CREATE TABLESPACE keywords. In this example, the tablespace name is tbs1.

· Second, specify the path to the data file of the tablespace in the DATAFILE clause. In this case, it is tbs1.dbf. Note that you can use the datafile full path.

· Third, specify the size of the tablespace in the SIZE clause. In this example, 1m stands for 1MB, which is quite small.

Once the tablespace is created, you can find its information by querying data from the dba_data_files view:

SELECT

tablespace_name,

file_name,

bytes / 1024/ 1024 MB

FROM

dba_data_files;

Important Question No:4

V1 What are various types of users supported by oracle system ? Explain

Default privileges for each users. (May 2019)(8)

Answer No.4

Each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database.

Creating Users

You create a database user with the CREATE USER statement.To create a user, you must have the CREATE USER system privilege. Because it is a powerful privilege, a DBA or security administrator is normally the only user who has the CREATE USER system privilege.

Following example creates a user and specifies the user password, default tablespace, temporary tablespace where temporary segments are created, tablespace quotas, and profile.

Example Create a User and Grant the Create Session System Privilege

CREATE USER jward

IDENTIFIED BY AZ7BC2

DEFAULT TABLESPACE data_ts

QUOTA 100M ON test_ts

QUOTA 500K ON data_ts

TEMPORARY TABLESPACE temp_ts

PROFILE clerk;

GRANT create session TO jward;

A newly created user cannot connect to the database until granted the CREATE SESSION system privilege.

Specifying a Name

Within each database, a user name must be unique with respect to other user names and roles. A user and role cannot have the same name. Furthermore, each user has an associated schema. Within a schema, each schema object must have a unique name.

Altering Users

Users can change their own passwords. However, to change any other option of a user security domain, you must have the ALTER USER system privilege. Security administrators are typically the only users that have this system privilege, as it allows a modification of any user security domain. This privilege includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.

You can alter user security settings with the ALTER USER statement. Changing user security settings affects the future user sessions, not current sessions.

The following statement alters the security settings for the user, avyrros:

ALTER USER avyrros

IDENTIFIED EXTERNALLY

DEFAULT TABLESPACE data_ts

TEMPORARY TABLESPACE temp_ts

QUOTA 100M ON data_ts

QUOTA 0 ON test_ts

PROFILE clerk;

The ALTER USER statement here changes the security settings for the user avyrros as follows:

· Authentication is changed to use the operating system account of the user avyrros.

· The default and temporary tablespaces are explicitly set for user avyrros.

· avyrros is given a 100M quota for the data_ts tablespace.

· The quota on the test_ts is revoked for the user avyrros.

· avyrros is assigned the clerk profile.

Dropping Users

When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema, if any, are immediately dropped.

A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user sessions using the SQL statement ALTER SYSTEM with the KILL SESSION clause.

You can drop a user from a database using the DROP USER statement. To drop a user and all the user schema objects (if any), you must have the DROP USER system privilege. Because the DROP USER system privilege is powerful, a security administrator is typically the only type of user that has this privilege

User and Profile Information in Data Dictionary Views

The following data dictionary views contain information about database users and profiles:

View	Description
DBA_USERS	Describes all users of the database
ALL_USERS	Lists users visible to the current user, but does not describe them
USER_USERS	Describes only the current user
DBA_TS_QUOTAS USER_TS_QUOTAS	Describes tablespace quotas for users
USER_PASSWORD_LIMITS	Describes the password profile parameters that are assigned to the user
USER_RESOURCE_LIMITS	Displays the resource limits for the current user
DBA_PROFILES	Displays all profiles and their limits
RESOURCE_COST	Lists the cost for each resource
V$SESSION	Lists session information for each current session, includes user name
V$SESSTAT	Lists user session statistics
V$STATNAME	Displays decoded statistic names for the statistics shown in the V$SESSTAT view
PROXY_USERS	Describes users who can assume the identity of other users

The following sections present some examples of using these views, and assume a database in which the following statements have been executed:

CREATE PROFILE clerk LIMIT

SESSIONS_PER_USER 1

IDLE_TIME 30

CONNECT_TIME 600;

CREATE USER jfee

IDENTIFIED BY wildcat

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp_ts

QUOTA 500K ON users

PROFILE clerk;

CREATE USER dcranney

IDENTIFIED BY bedrock

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp_ts

QUOTA unlimited ON users;

CREATE USER userscott

IDENTIFIED BY scott1;

Important Question No:5

V1 Discuss various security modes for databases

(May 2017)(7)

Answer No.5

Database security encompasses a range of security controls designed to protect the Database Management System (DBMS). The types of database security measures your business should use include protecting the underlying infrastructure that houses the database such as the network and servers), securely configuring the DBMS, and the access to the data itself.

Database security controls

Database security encompasses multiple controls, including system hardening, access, DBMS configuration, and security monitoring. These different security controls help to manage the circumventing of security protocols.

System hardening and monitoring

The underlying architecture provides additional access to the DBMS. It is vital that all systems are patched consistently, hardened using known security configuration standards, and monitored for access, including insider threats.

DBMS configuration

It is critical that the DBMS be properly configured and hardened to take advantage of security features and limit privileged access that may cause a misconfiguration of expected security settings. Monitoring the DBMS configuration and ensuring proper change control processes helps ensure that the configuration stays consistent.

Authentication

Database security measures include authentication, the process of verifying if a user’s credentials match those stored in your database, and permitting only authenticated users access to your data, networks, and database platform.

Access

A primary outcome of database security is the effective limitation of access to your data. Access controls authenticate legitimate users and applications, limiting what they can access in your database. Access includes designing and granting appropriate user attributes and roles and limiting administrative privileges.

Database auditing

Monitoring (or auditing) actions as part of a database security protocol delivers centralized oversight of your database. Auditing helps to detect, deter, and reduce the overall impact of unauthorized access to your DBMS.

Backups

A data backup, as part of your database security protocol, makes a copy of your data and stores it on a separate system. This backup allows you to recover lost data that may result from hardware failures, data corruption, theft, hacking, or natural disasters.

Encryption

Database security can include the secure management of encryption keys, protection of the encryption system, management of a secure, off-site encryption backup, and access restriction protocols.

Application security

Database and application security framework measures can help protect against common known attacker exploits that can circumvent access controls, including SQL injection.

Why is database security important?

Safeguarding the data your company collects and manages is of utmost importance. Database security can guard against a compromise of your database, which can lead to financial loss, reputation damage, consumer confidence disintegration, brand erosion, and non-compliance of government and industry regulation.

Database security safeguards defend against a myriad of security threats and can help protect your enterprise from:

Saturday, May 30, 2020

DATABASE ADMINISTRATION IMPORTANT QUESTION ANSWER

What is the Redo Log?

Redo Log Data Dictionary Views

SQL Create Constraints

Syntax

SQL Constraints

SQL NOT NULL Constraint

SQL NOT NULL on CREATE TABLE

Example

SQL NOT NULL on ALTER TABLE

DROP CONSTRAINT

DROP a UNIQUE Constraint

DROP a PRIMARY KEY Constraint

DROP a FOREIGN KEY Constraint

DROP a CHECK Constraint

Default tablespaces in Oracle

Online and Offline Tablespaces

Read-Only Tablespaces

Creating Users

Specifying a Name

Altering Users

Dropping Users

User and Profile Information in Data Dictionary Views

Database security controls

System hardening and monitoring

DBMS configuration

Authentication

Access

Database auditing

Backups

Encryption

Application security

Why is database security important?

· Deployment failure

· Excessive privileges

· Privilege abuse

· Platform vulnerabilities

· Unmanaged sensitive data

· Backup data exposure

· Weak authentication

· Database injection attacks