Backup and Recovery : #01

Prepare Backup and Recovery Strategy

Whatever backup strategy you choose, it is important to obtain agreement from all appropriate levels of management.

• Business requirements
• Operational requirements
• Technical considerations
• Management concurrence

Business Impact:

==================

You should understand the impact that down time has on the business. Management must quantify the cost of down time and the loss of data and compare this with the cost of reducing down time and minimizing data loss.

MTTR Database availability is a key issue for a DBA. In the event of a failure the DBA should strive to reduce the Mean-Time-To-Recover (MTTR). This strategy ensures that the database is unavailable for the shortest possible amount of time. Anticipating the types of failures that can occur and using effective recovery strategies, the DBA can ultimately reduce the MTTR.

MTBF Protecting the database against various types of failures is also a key DBA task. To do this, a DBA must increase the Mean-Time-Between-Failures (MTBF). The DBA must understand the backup and recovery structures within an Oracle database environment and configure the database so that failures do not often occur.

Evolutionary Process A backup and recovery strategy evolves as business, operational, and technical requirements change. It is important that both the DBA and appropriate management review the validity of a backup and recovery strategy on a regular basis.

Operational Requirements:

=================================

24-Hour Operations :In a situation where a database must be available 24 hours a day, 7 days a week for continuous operation. Proper database configuration is necessary to support these operational requirements because they directly affect the technical aspects of the database environment

Technical Considerations:

=============================

• Resources: hardware, software, manpower, and time 
• Physical image copies of the operating system files
• Logical copies of the objects in the database
• Transaction volume which affects desired frequency of backups

Here are some questions to consider when selecting a backup strategy:

How much data do you have?

Do you have the machine power and capacity to support backups?

Is the data easily recreated?

Can you reload the data into the database from a flat file?

Does the database configuration support resiliency to different types of failures?

Disaster Recovery Issues:

==============================

data is so important that you must ensure resiliency even in the event of a complete system failure. Natural disasters and other issues can affect the availability of your data and must be considered when creating a disaster recovery plan. Here are some questions to consider when selecting a backup and recovery strategy:

What will happen to your business in the event of a serious disaster such as:

– Flood, fire, earthquake, or hurricane

– Malfunction of storage hardware or software

If your database server fails, will your business be able to operate during the hours, days, or even weeks it might take to get a new hardware system?

Do you store backups at an off-site location?

Solutions:

Off-site backups
Data Guard which protects critical data by automating the creation, management, and monitoring aspects of a standby database environment.

Loss of Key Personnel

In terms of key personnel, consider the following questions:

How will a loss of personnel affect your business?

If your DBA leaves the company or is unable to work, will you be able to continue to run the database system?

Who will handle a recovery situation if the DBA is unavailable?

Introduction of Backup & Recovery

Why we need backup & recovery ?

• Protected the database from failure
• Minimize data loss
• Increase mean time between failure
• decrease mean time to recover

One of a database administrator’s (DBA) major responsibilities is to ensure that the database is available for use.
To protect the data from the various types of failures that can occur, the DBA must back up the database regularly. Without a current backup, it is impossible for the DBA to get the database up and running if there is a file loss, without losing data.

Categories of Failures:
=======================================

Different types of failures may occur in an Oracle database environment. These include:

• Statement failure
• User process failure
• User error
• Instance failure
• Media failure
• Network failure

Each type of failure requires a varying level of involvement by the DBA to recover effectively from the situation. In some cases, recovery depends on the type of backup strategy that has been implemented.

Statement Failure:
=======================================

Statement failure occurs where there is a logical failure in the handling of a statement in an Oracle program. Types of statement failures include:

• A logical error occurs in the application.
• The user attempts to enter invalid data into the table, perhaps violating integrity constraints.
• The user attempts an operation with insufficient privileges, such as an insert on a table using only SELECT privileges.
• The user attempts to create a table but exceeds the user’s allotted quota limit.
• The user attempts an INSERT or UPDATE on a table, causing an extent to be allocated, but insufficient free space is available in the tablespace.

Resolutions for Statement Failures
=============================================

DBA intervention after statement failures will vary in degree, depending on the type of failure, and may include the following:

• Fix the application so that logical flow is correct. Depending on your environment this may be an application developer task rather than a DBA task.
• Modify the SQL statement and reissue it. This may also be an application developer task rather than a DBA task.
• Provide the necessary database privileges for the user to complete the statement successfully.
• Issue the ALTER USER command to change the quota limit.
• Add file space to the tablespace. Technically, the DBA should make sure this does not happen; however, in some cases it may be necessary to add file space. A DBA can also use the RESIZE and AUTOEXTEND options for data files.

When a statement failure is encountered, it is likely that the Oracle server or the operating system will return an error code and a message. The failed SQL statement is automatically rolled back, then control is returned to the user program. The application developer or DBA can use the Oracle error codes to diagnose and help resolve the failure.


oracle provide later resuming, the execution of large database operations in the event of space allocation failures. This enables an administrator to take corrective action, instead of the Oracle database server returning an error to the user. After the error condition is corrected, the suspended operation automatically resumes. This feature is called resumable space allocation and the statements that are affected are called resumable statements



User Process Failures
===============================

A user’s process may fail for a number of reasons; however, the more common causes include:

The user performed an abnormal disconnect in the session. For example, a user issues a [Ctrl] + [Break] in SQL*Plus while connected to a database in a client-server configuration.
• The user’s session was abnormally terminated. One possible scenario is the user rebooted the client while connected to a database in a client-server configuration.
• The user’s program raised an address exception which terminated the session. This is common if the application does not properly handle exceptions when they are raised.

Resolution of User Process Failures
================================

The DBA will rarely need to take action to resolve user process errors. The user process cannot continue to work, although the Oracle server and other user processes will continue to function.

PMON Background Process

• The PMON background process is usually sufficient for cleaning up after an abnormally terminated user process.
• When the PMON process detects an abnormally terminated server process, it rolls back the transaction of the abnormally terminated process, and releases any resources and locks it has acquired.

User Errors:
=============

DBA intervention is usually required to recover from user errors. Common Types of User Errors

• The user accidentally drops or truncates a table.
• The user deletes all rows in a table.
• The user commits data, but discovers an error in the committed data.

A key issue in any database and application environment is to make sure that users are properly trained and are aware of database availability and integrity implications
A DBA should understand the types of applications and business operations that may result in loss of data from user errors and how to implement recovery measures for those situations. Some recovery situations may be quite extensive, such as restoring the database to a point-in time just prior to the error, exporting the lost data, and then importing that data back into the database from which it was lost.

• Recover from a valid backup.
• Import the table from an export file.
• Use LogMiner to determine the time of error.
• Recover with a point-in-time recovery.
• Use LogMiner to perform object-level recovery.
• Use FlashBack to view and repair historical data

Instance Failure:
================

An instance failure may occur for numerous reasons:

• A power outage occurs that causes the server to become unavailable.
• The server becomes unavailable due to hardware problems such as a CPU failure,memory corruption, or an operating system crash.
• One of the Oracle server background processes (DBWn, LGWR, PMON, SMON,CKPT) experiences a failure.

To recover from instance failure, the DBA:

• Starts the instance by using the “startup” command. The Oracle server will automatically recover, performing both the roll forward and rollback phases.
• Investigates the cause of failure by reading the instance alert.log file and any other trace files that were generated during the instance failure.

Instance Recovery
=====================

Instance recovery restores a database to its transaction-consistent state just prior to instance failure. The Oracle server automatically performs instance recovery when the database is opened if it is necessary.
No recovery action needs to be performed by you. All required redo information is read by SMON. To recover from this type of failure, start the database:

After the database has opened, notify users that any data that they did not commit must be re-entered


There may be a time delay between starting the database and the “Database opened” notification—this is the roll forward phase that takes place while the database is mounted.

– SMON performs the roll forward process by applying changes recorded in the online redo log files from the last checkpoint.
– Rolling forward recovers data that has not been recorded in the database files, but has been recorded in the online redo log, including the contents of rollback segments.

Rollback can occur while the database is open, because either SMON or a server process can perform the rollback operation. This allows the database to be available for users more quickly.



Media Failures
=================

Media failure involves a physical problem when reading from or writing to a file that is necessary for the database to operate. Media failure is the most serious type of failure because it usually requires DBA intervention.
Common Types of Media Related Problems

• The disk drive that held one of the database files experienced a head crash.
• There is a physical problem reading from or writing to the files needed for normal database operation.
• A file was accidentally erased.

Resolutions for Media Failures
================================================

A tested recovery strategy is the key component to resolving media failure problems. The ability of the DBA to minimize down time and data loss as a result of media failure depends on the type of backups that are available. A recovery strategy, therefore, depends on the following:

• The backup method you choose and which files are affected.
• The Archivelog mode of operation of the database. If archiving is used, you can apply archived redo log files to recover committed data since the last backup

Oracle User Management :#03

Privilege

User Privilege

Authentication means to authenticate a system user account ID for access to an Oracle database.
Authorization means to verify that a system user account ID has been granted the right, called a privilege, to execute a particular type of SQL statement or to access objects belonging to another system user account.
In order to manage system user access and use of various system objects, such as tables, indexes, and clusters, Oracle provides the capability to grant and revoke privileges to individual user accounts.

Example privileges include the right to:

• Connect to a database
• Create a table
• Select rows from another user’s table
• Execute another user’s stored procedure

There are six categories of privileges:

• System privileges allow a system user to perform a specific type of operation or set of operations. Typical operations are creating objects, dropping objects, and altering objects.
• Schema Object privileges allow a system user to perform a specific type of operation on a specific schema object. Typical objects include tables, views, procedures, functions, sequences, etc.
• Table privileges are schema object privileges specifically applicable to Data Manipulation Language (DML) operations and Data Definition Language (DDL) operations for tables.
• View privileges apply to the use of view objects that reference base tables and other views.
• Procedure privileges apply to procedures, functions, and packages
• Type privileges apply to the creation of named types such as object types, VARRAYs, and nested tables.

System Privileges:


The number of system privileges has grown by version to version .
A complete listing is available by querying the view named SYSTEM_PRIVILEGE_MAP.

Privileges can be divided into three categories:
Those enabling system wide operations, for example, CREATE SESSION, CREATE TABLESPACE.
Those enabling the management of an object that is owned by the system user,for example, CREATE TABLE.
Those enabling the management of an object that is owned by any system user, for example, CREATE ANY TABLE.

If you can create an object, such as that privilege provided by the CREATE TABLE privilege, then you can also drop the objects you create.

Category : session
Privilege: Create Session,Alter Session

Category :TABLESPACE
Privilege: Create Tablespace,Alter Tablespace,Drop Tablespace,Unlimited Tablespace

Category :TABLE
Privilege: Create Table,Create Any Table,Alter Any Table,Drop Any Table,Select Any Table

Category :INDEX
Privilege: Create Any Index,Alter Any Index

Some privileges, such as UNLIMITED TABLESPACE cannot be granted to a role.


Granting System Privileges:

The command to grant a system privilege is the GRANT command. Some example GRANT commands are shown here.

sql>GRANT ALTER TABLESPACE, DROP TABLESPACE TO user1;
Grant succeeded.
sql>GRANT CREATE SESSION TO user1 WITH ADMIN OPTION;
Grant succeeded.

In general, you can grant a privilege to either a user or to a role. You can also grant a privilege to PUBLIC - this makes the privilege available to every system user.
The WITH ADMIN OPTION clause enables the grantee (person receiving the privilege) to grant the privilege or role to other system users or roles; however, you cannot use this clause unless you have, yourself, been granted the privilege with this clause.
The GRANT ANY PRIVILEGE system privilege also enables a system user to grant or revoke privileges.
The GRANT ANY ROLE system privilege is a dangerous one that you don't give to the average system user since then the user could grant any role to any other system user.


SYSDBA and SYSOPER Privileges

SYSDBA and SYSOPER are special privileges that should only be granted to a DBA.
This table lists example privileges associated with each of these special privileges.

SYSOPER: STARTUP,SHUTDOWN,ALTER DATABASE OPEN | MOUNT,RECOVER DATABASE,ALTER DATABASE ARCHIVELOG,RESTRICTED SESSION,ALTER DATABASE BEGIN /END BACKUP
SYSDBA : SYSOPER PRIVILEGES THAT INCLUDE THE WITH ADMIN OPTION.CREATE DATABASE,RECOVER DATABASE UNTIL

Displaying System Privileges:

You can display system privileges by querying the DBA_SYS_PRIVS view.

SQL> SELECT * FROM dba_sys_privs WHERE Grantee='USER1' ;
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
USER1                          ALTER TABLESPACE                         NO
USER1                          DROP TABLESPACE                          NO
USER1                          CREATE SESSION                           YES

SQL>

You can view the users who have SYSOPER and SYSDBA privileges by querying v$pwfile_users

SQL> SELECT * FROM v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SQL>

The view SESSION_PRIVS gives the privileges held by a user for the current logon session.

SQL> conn user1/user1
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER TABLESPACE
DROP TABLESPACE


Revoking System Privileges :


The REVOKE command can be used to revoke privileges from a system user or from a role. Only privileges granted directly with a GRANT command can be revoked.
There are no cascading effects when a system privilege is revoked. For example, the DBA grants the SELECT ANY TABLE WITH ADMIN OPTION to system user1, and then system user1 grants the SELECT ANY TABLE to system user2, then if system user1 has the privilege revoked, system user2 still has the privilege.

revoke create table from user1;
revoke create session from user2;

Oracle User Management :#02

External OS User Authentication in Oracle

External Authentication requires the creation of user accounts that are maintained by Oracle. Passwords are administered by an external service such as the operating system or a network service.

This option is generally useful when a user logs on directly to the machine where the Oracle server is running.
A database password is not used for this type of login.
In order for the operating system to authenticate users, a DBA sets the init.ora
parameter OS_AUTHENT_PREFIX to some set value – the default value is OPS$ in order to provide for backward compatibility to earlier versions of Oracle
This prefix is used at the operating system level when the user's account username.
You can also use a NULL string (a set of empty double quotes: "" ) for the prefix so that the Oracle username exactly matches the Operating System user name. This eliminates the need for any prefix.

SQL> show parameter os_authent_prefix
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
SQL>

If the OS_AUTHENT_PREFIX parameter is Not NULL, then set the parameter with the following command if using spfile.

SQL> alter system set OS_AUTHENT_PREFIX='' scope=spfile;


Restart the service of the Database. 

The REMOTE_OS_AUTHENT parameter can be set to force acceptance of a client operating system user name from a nonsecure connection.
If the REMOTE_OS_AUTHENT parameter is FALSE (don’t allow OS authentication) set the parameter to TRUE
This is NOT a good security practice.
Setting REMOTE_OS_AUTHENT = FALSE creates a more secure configuration based on server based authentication of clients.
Changes in the parameter take effect the next time the instance starts and the database is mounted.

SQL> alter system set remote_os_authent=true scope=spfile;
System altered.

SQL> show parameter os_au
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$
remote_os_authent                    boolean     TRUE
SQL>
SQL> create user ops$oracle  identified externally;
User created.
SQL>
SQL> grant create session to ops$oracle;
Grant succeeded.

[oracle@localhost admin]$ sqlplus /
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 11 08:30:49 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select user from dual;

USER
------------------------------
OPS$ORACLE

SQL>

Oracle User Management :#01

The CREATE USER command creates a system user as shown here.

CREATE USER Test IDENTIFIED BY test12345;

• A DBA must have the CREATE USER system privilege.
• The IDENTIFIED BY clause specifies the user password
• User to connect to Oracle, you must grant the user the CREATE SESSION system privilege.
• Each username must be unique within a database
• Each user/schema for the storage of objects within the database
• Two users can name objects identically because the objects are referred to globally by using a combination of the username and object name.

A complete example of the CREATE USER command:

CREATE USER Test
IDENTIFIED BY test12345
DEFAULT TABLESPACE Users
TEMPORARY TABLESPACE Temp
QUOTA 1M ON Users
PROFILE Developer
ACCOUNT UNLOCK
PASSWORD EXPIRE;

Test has two identified , one for DEFAULT storage of objects and one for TEMPORARY objects.
Test  has the resource limitations allocated by the PROFILE named Developer. The account is unlocked (the default – alternatively the account could be created initially with the LOCK specification).
The PASSWORD EXPIRE clause requires Test to change the password prior to connecting to the database. After the password is set, when the user logs on using SQLPlus or any other software product that connects to the database, the user receives the following message at logon, and is prompted to enter a new password:

[oracle@localhost admin]$ sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 11 07:49:55 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter user-name: test
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for test
New password:
Retype new password:
Password changed


Alter user command : 
To make any other use of the command, a user must have the ALTER USER system privilege.

Use below statement change password for users

alter user test identified by test54321;

we can use Alter command to change tablespace,profile,password,quota etc.

Drop User Command

The DROP USER command is used to drop a user. Examples:

SQL> drop user test1;
User dropped.

Drop user including all reference objects

SQL>DROP USER Test CASCADE;  

Dropping a user causes the user and the user schema to be immediately deleted from the database.
If the user has created objects within their schema, it is necessary to use the CASCADE option in order to drop a user.
If you fail to specify CASCADE when user objects exist, an error message is generated and the user is not dropped.
For drop the user, the DBA must have the DROP USER system privilege.

If you want to deny access to the database, but do not want to drop the user and the
user's objects, you should revoke the CREATE SESSION privilege for the user temporarily.
You cannot drop a user who is connected to the database - you must first terminate the user's session with the ALTER SYSTEM KILL SESSION command.

Default Tablespace:

If one is not specified, the default tablespace for a user is the SYSTEM tablespace – not a good choice for a default tablespace

Temporary Tablespace:

The default Temporary Tablespace for a user is also the SYSTEM tablespace.
Allowing this situation to exist for system users will guarantee that user processing will cause contention with access to the data dictionary.
Generally a DBA will create a TEMP tablespace that will be shared by all users for processing that requires sorting and joins.

Database Authentication:

Database authentication involves the use of a standard user account and password.
Oracle performs the authentication.
System users can change their password at any time.
Passwords are stored in an encrypted format.
Each password must be made up of single-byte characters, even if the database uses a multi-byte character set.

Advantages:

• User accounts and all authentication are controlled by the database. There is no reliance on anything outside of the database.
• Oracle provides strong password management features to enhance security when using database authentication.
• It is easier to administer when there are small user communities.
• Oracle recommends using password management that includes password aging/expiration, account locking, password history, and password complexity verification.

Profile management : #01

Password management

Password management can be easily controlled by a DBA through the use of profiles.

Enabling Password Management

Password management is enabled by creating a profile and assigning the profile to system users when their account is created or by altering system user profile assignments.

Password limits set in this fashion are always enforced. When password management is in use, an existing user account can be locked or unlocked by the ALTER USER command.

Password Account Locking: 

This option automatically locks a system user account if the user fails to execute proper login account name/password entries after a specified number of login attempts.

The FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME parameter are specified as part of a profile.

The FAILED_LOGIN_ATTEMPTS is specified as an integer. 

The PASSWORD_LOCK_TIME is specified as days.

The database account can be explicitly locked with the ALTER USER command. When this happens, the account is not automatically unlocked.

Password Expiration/Aging: 

Specifies the lifetime of a password – after the specified period, the password must be changed.

The PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters are specified as part of a profile.

PASSWORD_LIFE_TIME specifies the maximum life of a password.

If the PASSWORD_GRACE_TIME is exceeded, the account automatically locks.

Both of these parameters are specified in days.

Password History:

This option ensures that a password is not reused within a specified period of time or number of password changes.

If either PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX are set to a value other than DEFAULT or UNLIMITED, the other parameter must be set to UNLIMITED.

PASSWORD_REUSE_TIME is specified in days.

PASSWORD_REUSE_MAX is an integer value specifying the number of password changes required before a password can be reused.

If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED.

If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED

Password Complexity Verification:

This option ensures that a password is complex – this helps provide protection against system intruders who attempt to guess a password.

This is implemented by use of a password verification function. A DBA can write such a function or can use the default function named VERIFY_FUNCTION.

The function that is used for password complexity verification is specified with the profile parameter, PASSWORD_VERIFY_FUNCTION.

If NULL is specified (the default), no password verification is performed.

When a DBA connected as the user SYS executes the utlpwdmg.sql script (located at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql) , the Oracle Server creates the VERIFY_FUNCTION .

[oracle@localhost admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 11 07:19:45 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.

Profile altered.

Function created.

Grant succeeded.

SQL>

Creating a Profile with Password Protection:

SQL> CREATE PROFILE developer LIMIT

PASSWORD_REUSE_TIME 1

PASSWORD_LOCK_TIME 2

PASSWORD_REUSE_MAX 3

FAILED_LOGIN_ATTEMPTS 2

PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;  

Profile created.

SQL>SELECT * FROM DBA_PROFILES WHERE PROFILE ='DEVELOPER';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

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

DEVELOPER                      COMPOSITE_LIMIT                  KERNEL   DEFAULT

DEVELOPER                      SESSIONS_PER_USER                KERNEL   DEFAULT

DEVELOPER                      CPU_PER_SESSION                  KERNEL   DEFAULT

DEVELOPER                      CPU_PER_CALL                     KERNEL   DEFAULT

DEVELOPER                      LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT

DEVELOPER                      LOGICAL_READS_PER_CALL           KERNEL   DEFAULT

DEVELOPER                      IDLE_TIME                        KERNEL   DEFAULT

DEVELOPER                      CONNECT_TIME                     KERNEL   DEFAULT

DEVELOPER                      PRIVATE_SGA                      KERNEL   DEFAULT

DEVELOPER                      FAILED_LOGIN_ATTEMPTS            PASSWORD 2

DEVELOPER                      PASSWORD_LIFE_TIME               PASSWORD DEFAULT

DEVELOPER                      PASSWORD_REUSE_TIME              PASSWORD 1

DEVELOPER                      PASSWORD_REUSE_MAX               PASSWORD 3

DEVELOPER                      PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION

DEVELOPER                      PASSWORD_LOCK_TIME               PASSWORD 2

DEVELOPER                      PASSWORD_GRACE_TIME              PASSWORD DEFAULT

SQL> create user tes identified by te;

create user tes identified by te

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20001: Password length less than 8

SQL>

Using the Data Dictionary:

Information about password and resource limits can be obtained by querying the following views:

DBA_USERS

DBA_PROFILES

Database Profiles

Profile – is a database object – a named set of resource limits to:

Restrict database usage by a system user – profiles restrict users from performing operations that exceed reasonable resource utilization. Examples of resources that need to be managed:

• Disk storage space.
• I/O bandwidth to run queries.
• CPU power.
• Connect time.

Enforce password practices – how user passwords are created, reused, and validated
Profiles are assigned to users as part of the CREATE USER or ALTER USER commands

• User accounts can have only a single profile
• A default profile can be created – a default already exists within Oracle named
• DEFAULT – it is applied to any user not assigned another profile.
• Assigning a new profile to a user account supersedes any earlier profile.
• Profiles cannot be assigned to roles or other profiles.

Profiles only take effect when resource limits are "turned on" for the database as a whole. Specify the RESOURCE_LIMIT initialization parameter.

RESOURCE_LIMIT = TRUE

Use the ALTER SYSTEM statement to turn on resource limits.

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;


Profile Specifications

Profile specifications include:

• Password aging and expiration
• Password history
• Password complexity verification
• Account locking
• CPU time
• Input/output (I/O) operations
• Idle time
• Connect time
• Memory space (private SQL area for Shared Server only)
• Concurrent sessions

If users not assigned a specific profile are automatically assigned the DEFAULT profile. The DEFAULT profile has only one significant restriction it doesn't specify a password verification function.

This query lists the resource limits for the DEFAULT profile


SQL> COLUMN profile FORMAT A10;
COLUMN resource_name FORMAT a30;
COLUMN resource FORMAT a8;
COLUMN limit FORMAT a15;
SELECT * FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT';

PROFILE    RESOURCE_NAME                  RESOURCE LIMIT
---------- ------------------------------ -------- ---------------
DEFAULT    COMPOSITE_LIMIT                KERNEL   UNLIMITED
DEFAULT    SESSIONS_PER_USER              KERNEL   UNLIMITED
DEFAULT    CPU_PER_SESSION                KERNEL   UNLIMITED
DEFAULT    CPU_PER_CALL                   KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_SESSION      KERNEL   UNLIMITED
DEFAULT    LOGICAL_READS_PER_CALL         KERNEL   UNLIMITED
DEFAULT    IDLE_TIME                      KERNEL   UNLIMITED
DEFAULT    CONNECT_TIME                   KERNEL   UNLIMITED
DEFAULT    PRIVATE_SGA                    KERNEL   UNLIMITED
DEFAULT    FAILED_LOGIN_ATTEMPTS          PASSWORD 10
DEFAULT    PASSWORD_LIFE_TIME             PASSWORD 180
DEFAULT    PASSWORD_REUSE_TIME            PASSWORD UNLIMITED
DEFAULT    PASSWORD_REUSE_MAX             PASSWORD UNLIMITED
DEFAULT    PASSWORD_VERIFY_FUNCTION       PASSWORD NULL
DEFAULT    PASSWORD_LOCK_TIME             PASSWORD 1
DEFAULT    PASSWORD_GRACE_TIME            PASSWORD 7



Creating a Profile
===================

DBA creates a profile with the CREATE PROFILE command.
This command has clauses that explicitly set resource limits. A DBA must have the CREATE PROFILE system privilege in order to use this command.
Example:

CREATE PROFILE developer LIMIT
SESSIONS_PER_USER 4
CPU_PER_SESSION unlimited
CPU_PER_CALL 6000
LOGICAL_READS_PER_SESSION unlimited
LOGICAL_READS_PER_CALL 100
IDLE_TIME 1
CONNECT_TIME 480
PASSWORD_REUSE_TIME 1
PASSWORD_LOCK_TIME 2
PASSWORD_REUSE_MAX 3
FAILED_LOGIN_ATTEMPTS 2;

Resource limits that are not specified for a new profile inherit the limit set in the DEFAULT profile.


Assigning Profiles
===========================

Profiles can only be assigned to system users if the profile has first been created. Each system user is assigned only one profile at a time. When a profile is assigned to a system user who already has a profile, the new profile replaces the old one – the current session,if one is taking place, is not affected, but
subsequent sessions are affected. Also, you cannot assign a profile to a role or another profile

profiles are assigned with the CREATE USER and ALTER USER
command. An example CREATE USER command

CREATE USER userp IDENTIFIED BY userp
PROFILE developer;

SELECT username, profile FROM dba_users
WHERE username = 'USERP';


Altering Profiles
====================
Profiles can be altered with the ALTER PROFILE command.
A DBA must have the ALTER PROFILE system privilege to use this command.
When a profile limit is adjusted, the new setting overrides the previous setting for
the limit, but these changes do not affect current sessions in process.
Example:

ALTER PROFILE developer LIMIT
CPU_PER_CALL default
LOGICAL_READS_PER_SESSION 20000
SESSIONS_PER_USER 1;




Dropping a Profile
=========================

Profiles no longer required can be dropped with the DROP PROFILE command.
The DEFAULT profile cannot be dropped.
The CASCADE clause revokes the profile from any user account to which it was
assigned – the CASCADE clause MUST BE USED if the profile has been assigned to any user account.
When a profile is dropped, any user account with that profile is reassigned the DEFAULT profile.
Examples



SQL> drop profile developer;
drop profile developer
*
ERROR at line 1:
ORA-02382: profile DEVELOPER has users assigned, cannot drop without CASCADE

SQL> DROP PROFILE DEVELOPER CASCADE;

Profile dropped.

SQL> SELECT username, profile FROM dba_users
WHERE username = 'USERP';  

USERNAME                       PROFILE
------------------------------ ------------------------------
USERP                          DEFAULT

SQL>

Changes that result from dropping a profile only apply to sessions that are created after the change – current sessions are not modified.