Creating Roles

SQL>select * from dba_roles;

                To see all the roles.

                connect,resource,dba.....etc are roles.

SQL>create role role1;

SQL>select * from dba_roles;

 

Grant all the privileges to a role and assign the role to the users.

 

SQL>grant create session,create table to role1;

SQL>desc role_sys_privs;

SQL>select * from role_sys_privs where role = 'ROLE1';

                To see the privileges of the roles.

SQL>grant role1 to venu;             

                Assigning the role to user.

SQL>desc dba_role_privs;

SQL>select * from dba_role_privs where grantee='VENU';

                To see the roles assigned to the user.

SQL>revoke role1 from venu;

SQL>conn venu/venu

                ORA-01045 : user venu lacks create session privilege : login denied

Droping User Accounts

SQL>drop user venu cascade;

SQL>select * from all_users;

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

                We can restore the system created users but we cant get the users created accounts.

root>sqlplus / as sysdba

SQL>select * from all_users;

SQL>conn venu/venu

                In one terminal

SQL>conn venu1/madhu

                In another terminal

SQL>desc V$session;

SQL>select sid,serial#,username from V$session where username not in('SYS');

                List all the users that are connected to the database except the 'SYS' user.

SQL>alter system kill session '125,47';

                127 - sid               

                47  - serial#

SQL>select server from V$session;

                To see the process(Dedicated Server process(DSP)/Shared Server Process(SSP)).

Locking User Accounts

SQL>alter user venu1 account lock;

SQL>conn venu1/venu1

                ORA-28000 : the account is locked

SQL>conn / as sysdba

SQL>alter user venu1 account unlock;

SQL>conn venu1/venu1

SQL>alter user venu1 identified by madhu;

                To change the password of the user.

SQL>select * from all_users;

Creating a User

SQL>create user venu identifies by venu;

SQL>select * from all_users;

SQL>select username,default_tablespace,temporary_tablespace,profile from dba_users where username='VENu';

SQL>conn venu/venu

SQL>show user;

SQL>conn / as sysdba

SQL>show user

SQL>grant create session to venu;

SQL>conn venu/venu

venu>create table emp(eid number(3));

                ORA-01031 : insufficient privileges

venu>conn / as sysdba

SQL>grant create table to venu;

SQL>conn venu/venu

venu>create table emp(eid number(3));

venu>insert into emp(111);

                ORA-01950 : no privileges on tablespace 'USERS'

venu>conn / as sysdba

SQL>alter user venu quota 10m on users;

SQL>conn venu/venu

venu>insert into emp(111);

venu>commit;

venu>select * from emp;

venu>select * from session_privs;

                Lists the privileges of the current user

venu>conn / as sysdba

SQL>desc dba_sys_privs;

SQL>select * from dba_sys_privs where grantee='VENU';

                By using this view, we can see the privileges of any user.

SQL>desc dba_ts_quotas;

SQL>select username,tablespace_name,max_bytes from dba_ts_quotas where username='VENU';

                To see the quota of the given users.

SQL>grant connect,resource to venu1 identified by venu1;

                Another way of user creating an user by granting privileges at the time of user creation.

SQL>conn venu1/venu1

venu1>select * from session_privs;

                To see the privileges of a particular user in which we exist.

venu1>conn / as sysdba

SQL>revoke create session from venu;

SQL>revoke create session from venu1;

root>su - oracle

root>export ORACLE_SID = hrms

root>sqlplus / as sysdba

 

SQL>startup

SQL>desc dba_users;

SQL>select * from all_users;

                Lists all the existing users.

SQL>select username,default_tablespace,temporary_tablespace,profile from dba_users where            

username='SCOTT';

                                List the default tablespace for the given username.

Snapshot too old error(ORA-1555)

In case of parallel update and select, if the consistent past image is not available for the user who has already started reading the data from undo, such type of user is going to get snapshot too old error.

We can minimise the occurance of snapshot too old error by commiting more frequently or by creating a bigger undo tablespace.

To minimise, the occurance of snapshot too old error(if possible to eliminate) oracle has introduced retension guarantee in 10g version of oracle.

If we create undo tablespace with retension guarantee, oracle retains the data in the undo segments as per the parameter value "undo_retension".

Once we create undo tablespace, by default all 10 undo segments will be online.

Undo Management

Prior to 9i, undo segments are called "rollback segments".

As a DBA, we need to create the rollback segments manually by specifying the following init.ora parameters :

                1.undo_management = auto

                2.rollback_segments = rbs,rbs2,rbs3.....

Once we create a rollback segment, by default it is offline and we need to make it online manually.

If the parameter rollback_segments is set in the init.ora, all rollback segments will come online during the instance startup.

With respect to rollback segments there are more chances of getting out of space errors and snap shot too old errors.

As a DBA, we need to continuously monitor the growth of the rollback segments.

We can also allocate a specific transaction to a specific sengment.

                SQL> set transaction use rollback segment rbs1;

                SQL> create rollback segment rbs1

                                tablespace rbs_ts

                                storage(initial 100k

                                                next 100k

                                                minextent 2

                                                optimal 200k

                                                maxextents 6);

The minimum number of extents that need to be allocated is 2 because it follows head and tail mechanism.

Optimal represents the size in bytes to what extent a rollback segment has to shrink once the transaction is completed.

More than one transaction can groq in rollback segments or undo segments.

Oracle deallocates the extents of a rollback segments once the size of the rbs exceeds the optimal parameter value or if there are contiguous free extents.

The header or pointers of a rollback segment moves to the next extent if it is free or inactive.

Since there are somany disadvantages with rollback segments oracle has introduced the concept of "undo management".

In 9i, oracle has introduced automatic undo management.

Once we create undo tablespace by default oracle creates 10 undo segments with the naming convention "syssmu$n".

For every DML operation we requrire undo segments.

There are 2 types of undo segments:

                1.system undo segments

                2.non system undo segments

All undo segments that are created in the undo tablespace are non system undo segments. These undo segments will be used by the business users to perform DML operations.

Allocation of transactions to undo segments and deallocation of transactions from undo segments will be taken care by oracle itself.

Note : All DDL statements are auto commit.

Once we create a database, by default one undo segment will be created in system.dbf and this will be used by the system itself.

The purpose of undo segments is :

                1.transaction rollback

                2.transaction recovery

                3.instance recovery

                4.read consistancy

In order to use automatic undo management, we need to set the following init.ora parameters :

                1.undo_management = auto

                2.undo_tablespace = undo_ts

                3.undo_retension = <default value 900>

                                900 seconds.

"undo_retension" represents how long the commited data has to be retrieved in the undo. As per the parameter value, oracle tries to retain the data in the undo but it is not guaranteed.

If there is no space for the other incoming transactional data, oracle overrides.

Storage Parameters

They are

                1.initial

                2.next

                3.minextents

                4.maxextents

                5.pctincrease (percentage increase)

Note : The storage parameter which is applicable only to rollback segments is "optimal".

pct increase storage parameter is not applicable to rollback segments.

 

With the help of storage parameters, we can control the number of extents allocated for a segment as well as the sizes of the extent.

We can specify the storage parameter either at the time of tablespace creation or at the time of object creation.

From 9i, oracle recommands not to specify because oracle takes care of the allocation of extents and its sizes.

If we specify the storage parameters at tablespace creation as well as at object creation, oracle considers the storage parameters that are specified at object creation(oracle gives priority to the object level storage parameter).

 

                SQL> create tablespace hrms_ts

                                datafile '/u01/app/oracle/oradata/hrms/hrms_ts01.dbf' size 100m

                                default storage(initial 100k

                                                                next 100k

                                                                minextents 2

                                                                pctincrease 50

                                                                maxextents 10);

 

                SQL> create table emp1(empno number(10),ename varchar2(20),sal number(10,2))

                                storage(initial 200k

                                                next 200k

                                                minextents 2

                                                pctincrease 50

                                                maxextents 20);

 

Initial represents the size of the first extent.

next represents the size in bytes of the next extent(second extent).

pctincrease represents the size in bytes by which the third and subsequent extent over the preceding extent.

User authentication types

User authentication is of 3 types :

                database authentication

                operating system authentication

                network authentication

Authenticating at the database level is nothing but database authentication.

Operating system authentication means, user is authenticated at OS level. For this we need to set a parameter "os_authent_prefix".

Network authentication means authenticating users who are connecting as sys from remote machines.

Profiles

Profile is nothing but collection of resource limits.

As a DBA, we create some profiles and these profiles will be assigned to the database users.

"pupbld.sql" script is going to create the default profile.

SQL> create profile developer_profile limit

                failed_login_attempts 3

                idle_time 5

                password_life_time 7

                password_grace_time 2

                password_reuse_time 30

                sessions_per_user 2;

As a DBA, we should not grant DBA, sysoper and sysdba to normal database users, because they contain administrative privileges.

sysoper privileges :

                shutdown

                startup

                alter database mount/open

                alter database archievelog

                recover database

                alter database backup controlfile

sysdba privileges :

                sysoper privileges with admin option plus

                create database

                recover database until

                alter database begin backup

                alter database end backup

                restricted session

grant and revoke are the commands that are used to grant and revoke privileges.

We can grant a privilege or a role either with "with grant" option or with "with admin" option.

Types of Roles

Role is nothing but collection of privileges.

There are 2 types of roles :

                1.predefined roles

                2.DBA created roles

Examples of predefined roles :

                connect, resource, dba, exp_full_database, imp_full_database, delete_catalog_role, select_catalog_role.....etc

 

SQL> create role developer_user;

SQL> grant create table, create index, create session to developer_user;

SQL> create user scott identified by tiger default tablespace hrms_ts role developer_user;

 

Whatever the roles that we create, we assign them to database users. Role can be assigned to a role.

 

"select_catalog_role" grants a select privilege on all data dictionary views / tables.

Types of Privileges

                1.system privileges

                Example : create tablespace, create user, drop tablespace etc.

                2.object privileges

                Example : create table, drop table, create index etc.

As a DBA, we should not grant system privileges to the normal database users.

 

Note : Every user within the database is attached to a specific tablespace.

User Management

User and Schema both are interchangable words. A user is attached to only one schema.

Schema is nothing but collection of database objects like tables, materialised views, synonyms, views, indexes, sequences, packages, procedures, functions, triggers etc....

Note : delete + commit is going to delete all the records permanently without releasing the extents to the datafile.

Truncate is going to delete all the records permanently but releases all the extents to the datafile just by retaining the initial extent.

User management is one of the important aspect for a DBA.

DBA has to safe guard the objects created by the developers otherwise it leads to security concern.

The moment once we create a database, default users that are created are

                1.sys(super user)

                2.system

                3.dbsnmp(required for oracle networking)

                4.outln(contains stored outlines)

                5.sysman(required for oracle enterprise manager console)

Once we create a database recommanded to change the default passwords of all the default users.

Users contain some privileges. Privilege is a right to execute a specific task or specific statement.

States of Online Redo Log file

Online redo log file exists in any one of the following states:

1.current

2.active

3.inactive

4.unused

5.stale

 

If log writer is pointing then the state of the online redo log file is "current".

An online redo log file which is required for recovery exists in "active" state.

We cannot drop an active redo log file.

 

Note : We cannot resize an online redo log file.

 

An online redo log file which is not required for recovery either exists in inactive/unused/stale state.

 

An online redo log file with partial/incomplete data exists in stale state.

Online Redo Log File Management

Online redo log files are very important for the functionality of the database.

Every change initially will be recorded into online redo log file.

Minimal information will be recorded into online redo log file if the tablespace is created with "nologging".

Prior to 10g, the minimum size of the online redo log file is 64K whereas from 10g, it is 4MB.

If we create a database with DBCA, oracle creates online redo log files of sizes 50MB.

In the industry, generally the sizes of online redo log files will be in MB's.

Without redo log files, database will not function.

Since redo log files are very important, recommanded to mirror them to avoid single point of failures(keep mirrored copied in ta separate file system).

Identical copies of  online redo log files within a group are called "members".

Members within a group contains the same data and contains the same log sequence number.

Log Writer writes the data into the online redo log files in cyclic manner.

Log Writer writes the data into online redo log fies under the following circumstances :

1.If 1/3 of redo buffer cache is filled

2.IF the data reaches 1 MB

3.If the user commits

4.For every 3 seconds

5.Before DBWR writes to respective datafiles

Sections of Control File

1.Non Reusable Section

2.Reusable Section

 

Reusable section of the control file contains RMAN backup info.Since control file is one of the

important file, it is recommanded to mirror/multiplex to avoid single point of failures.

 

If the control file is lost, we cannot mount the database. If the control file is lost during the

operations of the database, database will not function.

 

To avoid single point failures, recommanded to mirror, but see that mirrored copies resides in a

separate file system.

 

If all the control files are lost, we need to restore the control files from the backup and we need

to perform recovery.

 

Whenever we create a new tablespace or add a new datafile, it is going to change the structure of

the control file(adding new redo log group or dropping a member also does that).

 

Whenever we change the structure of the control file, recommanded to issue the fillowing command :

                SQL> alter database backup controlfile to trace;

The above command creates a text based control file in "udump" directory or "diagnostic_dest"

location.

 

If we want to change the name of the database, we need to change the database name in the Pfile as

well as in the control file by recreating the control file.

 

Maximum we can maintain upto 8 control files.

Control File Management

Control file is a binary file and minimun number of control files required for a database is one.

Oracle scans the content of the control file in the MOUNT stage.

Oracle continuously updates the control file during the operations of the database.

Control file contains the following information :

1.Name of the database

2.Timestamp when the database was created

3.Names of the Tablespaces

4.Names and locations of the datafiles.

5.CheckPoint information

6.Log Sequence Number

7.RMAN Backup information .....etc.....

To view the database size

SQL> select sum(bytes/1024/1024) from dba_segments;

To view the free space in the datafiles

SQL> desc dba_free_space;

SQL> select tablespace_name,file_id,bytes from dba_free_space where tablespace_name = 'venu';

Changing Tablespace to 'read only'

SQL>alter tablespace venu read only;

SQL>alter tablespace venu read write;

Creating a User to clarify the 'deffered extent allocation'

SQL> create user venu

                identified by v1;

SQL> grant connect,resource to venu;

SQL> connect venu/v1;

SQL> create table emp(eid number(3));

SQL> desc user_extents;

SQL> set wrap off;

SQL> select segment_name,segment_type,tablespace_name from user_extents;

SQL> insert into emp values(111);

SQL> select segment_name,segment_type,tablespace_name from user_extents;

Changing the Default Permanent Tablespace and the Temporary Tablespace

SQL> alter database default temporary tablespace temp02;

SQL> alter database default tablespace venu01;

Removing a temporary tablespace from a temporary group

SQL> alter tablespace tempo1

                tablespace group '';

SQL> select * from dba_tablespace_group;

SQL> set wrap off;

SQL> select * from database_properties;

SQL> set wrap on;

Adding Temporary tablespace to an existing temporary tablespace group

SQL> alter tablespace temp01

                tablespace group tempgroup;

SQL> select * from dba_tablespace_groups;

Creating Temporary Tablespace Group

SQL> select * from dba_tablespace_groups;

SQL> create temporary tablespace temp02

                tempfile '/u02/app/oracle/ssss/temp02.dbf'

                size 2m

                tablespace group tempgroup;

SQL> select * from dba_tablespace_groups;

Creating Temporary Tablespaces

SQL> create temporary tablespace temp01

                tempfile '/u02/app/oracle/ssss/temp01.dbf'

                size 2m;

ReSizing the Bigfile Tablespace

SQL> alter tablespace bigts resize 4m;

Note : this command is only applicale for Bigfile tablespaces not for small tablespaces.

SQL> alter database datafile

                '/u02/app/oracle/ssss/venu1.dbf'

                resize 4m;

note : we need to alter the database if we want to resize the small tablespace datafiles.

Creating Bigfile Tablespace

SQL> create bigfile tablespace bigts

                datafile '/u02/app/oracle/ssss/bigts01.dbf'

                size 2m;

SQL> select tablespace_name,block_size,status,contents,bigfile from dba_tablespaces;

SQL> select file_name,file_id,tablespace_name,bytes from dba_data_files;

Procedures to migrate from Local to Dictionary Managed Tablespace and viceversa

SQL>exec dbms_space_admin.tablespace_migrate_to_local('venu');

note: migrates from Dictionary Managed tablespace to Local

SQL>exec dbms_space_admin.tablespace_migrate_from_local('venu');

note: migrates from Local Managed Tablespace to Dictionary Managed Tablespace.

SQL>select tablespace_name,extent_management,segment_space_management,status,logging from dba_tablespaces;

Creating Locally Managed Tablespaces

SQL> create tablespace venu1

                datafile '/u02/app/oracle/ssss/venu1.dbf'

                size=2m

                extent management local

                segment space management auto;

Creating Dictionary Managed Tablespaces

If the database is created using DBCA then we cannot create the Dictionary Managed Tablespace, we can create the Dictionary Managed Tablespace only when the database is created manually.

 

SQL> create tablespace venu

                datafile '/u02/app/oracle/ssss/venu01.dbf'

                size=2m

                extent management dictionary;

Note : By default, segment management is manual for Dictionary Managed Tablespaces and auto for Locally Managed Tablespaces. We can alter them if we need.

Exporting a database

root> su - oracle

root> export ORACLE_SID = ssss

root> sqlplus / as sysdba

note : if the memory is not sufficient we need to execute the following command as a root user at OS level

                root> !mo

                       mount -t tmpfs shmfs -o size=2g /dev/shm

SQL> select name,open_mode from V$database;

SQL> select name from V$tablespace;

SQL> select name from V$datafile.

Renaming a datafile

1.offline a tablespace

2.change the datafile name at OS level.

3.issue alter tablespace command at oracle level.

4.online the tablespace.

 

SQL> select name from v$datafile;

SQL> select file_name,file_id,tablespace_name,bytes from dba_data_files;

SQL> alter tablespace madhu offline;

 

OS LEVEL :

oracle> cd /u03/app/oracle/ssss

oracle> ls

oracle> mv or cp venu01.dbf mudhu01.dbf

 

SQL> alter tablespace madhu remane

                datafile '/u03/app/oraacle/ssss/venu01.dbf' to '/u03/app/oracle/ssss/madhu01.dbf';

SQL> alter tablespace madhu nologging;

SQL> select tablespace_name,logging from dba_tablespaces;

SQL> alter tablespace madhu logging;

Renaming a tablespace

SQL> alter tablespace venu rename to madhu;

SQL> select name from v$tablespace;

Offlining a tablespace

SQL> select name from v$tablespace;

SQL> alter tablespace venu offline;

SQL> select tablespace_name,block_size,status,contents,bigfile from dba_tablespces;

SQL> alter tablespace venu online;

SQL> select tablespcae_name,status from dba_tablespaces;

Adding a datafile

SQL> alter tablespace venu

     add

     datafile '/u03/app/oracle/ssss/venu02.dbf' size 5m;

SQL> select name from v$tablespace;

note : we need to specify the size of the tablespace compulsorily.

SQL> select tablespace_name,file_name,file_id from dba_data_files where tablespace_name='venu';

Creating a TableSpace

SQL> create tablespace venu

     datafile '/u03/app/oracle/ssss/venu01.dbf' size 10m;

                -->this throws error as there is no directory structure.

root> mkdir -p /u03/app/oracle/ssss

root> chown -R oracle:oinstall /u03/app/oracle/ssss

root> chmod -R 775 /u03/app/oracle/ssss

SQL> create tablespace venu

     datafile '/u03/app/oracle/ssss/venu01.dbf' size 10m;

                Note : we cannot create a tablespace without creating a datafile.

SQL> desc dba_tablespaces;

SQL> select tablespace_name,block_size,status,extent_management,segment_space_management,logging from dba_tablespaces;

SQL> save ts1.sql -- this file contains the previous query, so that we can execute it later.

SQl> !pwd

                /home/oracle

                note : use ! before any OS command to run in SQL prompt.

SQL> get ts1.sql (or) @ts1.sql  (or) start ts1.sql --> to run the queries in the ts1.sql

SQL> desc dba_tablespaces;

SQL> select tablespace_name,block_size,status,contents,bigfile from dba_tablespaces;

Before creating a table space check the path

SQL> select name from v$datafile;

SQL> desc dba_data_files;

SQL> select file_name,file_id,tablespace_name,bytes from dba_data_files;

                by using this view we can find out which datafile is related to which tablespace and its size;

Quering v$ views

Only database created using DBCA will be listed in the /etc/oratab file, but not the manually created databases.

root> ORACLE_SID = ssss --->exporting the database id

root> sqlplus / as sysdba

SQL> startup

SQL> select name,open_mode,log_mode from v$database;

SQL> select instance_name,status from v$instance;

SQL> select name from v$tablespace;

SQL> select name from v$datafile;

Advantages of having separate Tablespaces

1.We can segregate the metadata with the business data.

2.We can separate the permanent segments with temporary as well as undo segments.

3.We can create application specific Tablespaces.

4.We can make individual Tablespaces offline.

5.We can make specific Tablespaces read only.

6.We can backup Tablespace by tablespace.

7.In the event of loss, we can restore and recover a specific tablespace.

Types of Segments

Database contains 3 types of segments :

1.permanent segments(emp,dept etc)

2.temporary segments

3.undo segments(for DML operations)

Temporary segments are created during sorting operations. Once the sorting operations are completed, temporary segments will be dropped automatically.

Note : A segment will never span across tablespaces but segments spans across datafiles belongs to the same tablespace.

In the industry, there should be proper monitoring mechanism and proper alerting mechanism.

In some customer environments we use the third party monitoring tools or Enterprise Manager Grid  Control or Shell Scripts submitted as cronjobs. The monitoring tool or shell script sends a mail alert to the DBA_DL(DBA_Distribution_List). If the space in the datafile is 85% utilised, it sends a threshold alert. If 95% of the space is utilised, it sends a critical alert to the mail box.

Generally in the industry, we recommended to have small sized datafiles for better performance.

Extents size could be uniform or auto allocate. By default it is auto allocate.

In 10g, oracle has introduced temporary tablespace group which contains multiple temporary tablespaces.

Note : temporary segment created in the temporary tablespace group spans across multiple temporary tablespaces.

In 11g, oracle has introduced deffered extent allocation, which means oracle will not allocate the extent once we create a segment/table. Oracle delays the extent allocation until we insert a record into the segment. Deffered extent allocation is more useful in case of new application development environment(we can successfully create tables scripts even there is no space in the datafile.

 

Types of Tablespaces

Tablespaces are of 2 types:

                1.Locally managed tablespace

                2.Dictionary managed tablespace

From 9i, once we create a tablespace by default it is locally managed.

In case of dictionary managed tablespace, information about free extents and free blocks will be stored in the data dictionary.

In case of locally managed tablespace, information about free extents and free blocks will be stored within the tablespace.

Once we create a tablespace, by default it will be created in the read/write mode and by default it is online.

By default, logging is enabled for every tablespace that we create. Generally, no logging is enabled during bulk load operations and index creations.

Note : once the bulk load operations are completed, enable logging for the tablespaces and immediately take the backup of the database.

By default, segment space management is auto. In the case of auto, oracle considers some of the blocks as BMB’s(Bit Map Blocks). These BMB’s indicates the status of the blocks within the extent.

In case of manual(older versions of oracle), oracle uses free lists and free list groups to manage the space.

Every user within the database has to be assigned to a specific tablespace. Prior to 10g, if we create a user without assigning a tablespace, by default, it will be assigned to the system.dbf(not recommended).

In version 10 g, oracle has introduced a default permanent tablespace for the database. If a user is created without explicitly assigning a tablespace, user will be assigned to the default permanent tablespace.

A database contains one default permanent tablespace, one default undo tablespace and default temporary tablespace.

TableSpace Management

TableSpace is one of the logical structure of the database.The minimum tablespaces that are required to create a database are system and sysaux.

Sysaux tablespace was introduced in oracle10g version.Sysaux is considered as an auxilary tablespace for the  system and some of the components meta data  have been moved from system to sysaux.

In order to know the components that are moved from system to sysaux, issue the following select statement:

                select * from V$sysaux_occupants;

We can not offline system tablespace but we can offline sysaux tablespace.we can not rename system & sysaux tablespace.

In oracle 10g, oracle has introduced Big file tablespace which contains only one datafile.Big file tablespace datafile can grow upto tera bytes.

A tablespace can have maximum 1024 datafiles.

Recommanded to create Big file tablespace if third party volume manager or ASm(Automatic Storage Management) is in place.

If the block size is 8K, a datafile can grow upto 32GB and if the block size is 16K, a datafile can grow upto 64GB.

Recommanded to create Big file in OLAP environments(dataware housing).It can also be created in OLTP systems.

Block Space Utilisation Parameters

inittrans - 1

maxtrans  - 255

pctfree   - 10%

pctused   - 40%

 

In version 9i, oracle has introduced the concept of different block sizes likes 2k,4k,8k,16k,32k and 64k.

In order to create a tablespace of different block size, we need to set the following init.ora parameter.

                                db_nk_cache_size

n is an integer(2,4,8,16,32,64)

e.g : db_4k_cache_size = 800M

Oracle Block Structure

Header of the block contains data block address, table directory, row directory and transaction slots. By default, 10% of the space is reserved as free and 40% of the space can be utilised to dump the data. Free space is generally meant for future updates. Whenever we insert a record, oracle writes from bottom to top, so that it can grow towards free if required.

Discussion On Views

There are two types of views in oracle. They are static views and dynamic views.

Views which are starting with USER_,ALL_,DBA_ are called static views.

Static views gets the information from data dictionary(system.dbf).

Normal users can issue  a select statement on USER_ as well as ALL_ views.

Only DBA can issue a select statememt on DBA_ tables.

Note : we should not issue any DML statements on the Base tables.

Note : The only base table where we can issue DML statement is aud$.

All $tables are called base tables.

All V$ views are called Dynamic views. Dynamic views are  going to get the information from the

instance as well as data dictionary.Dynamic views give accurate information compared to static views.

Some Script Files

catalog.sql : sql.dsq creates the base tables. Base tables are not in the understandable format(tab

$,col$,priv$ etc).

Since base tables are not in the understandable format, we create views & synonyms on top of it.

catalog.sql creates views & synonyms.

 

catproc.sql : catproc.sql is going to create the predefined packages, procedures, functions etc.

e.g: dbms_output, dbms_job, dbms_schedular etc.

 

pupbld.sql : This script is going to create the predefined profile.

Overview of Physical & Logical Structures

Steps to create database manually

1.create Pfile from the dummy file.The Pfile(init.ora)is in ORACLE_HOME/dbs.

2.create required number of directories.

3.startup nomount

4.create database script & execute it.

5.execute the following scripts to create default profiles, functions, procedures, views, synonyms

etc.

                i)catalog.sql

                ii)catproc.sql

                iii)connect to system/manager

                iv)pupbld.sql

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

                >su - oracle

                >cd $ORACLE_HOME/dbs

                >pwd

                >ls

                >cp init.ora initssss.ora

                >ls

                >vi initssss.ora

                                db_name = 'ssss'

                                audit_file_dest = 'u01/app/oracle/admin/ssss/adump'

                                db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area'

                                diagnostic_dest = '/u01/app/oracle'

                                control_files = ('/u02/app/oracle/ssss/control01.ctl')

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

                >mkdir -p /u01/app/oracle

                >mkdir -p /u01/app/oracle/admin/ssss/adump

                >mkdir -p /u01/app/oracle/fast_recovery_area

                >mkdir -p /u02/app/oracle/ssss

>chown -R oracle:oinstall /u01/app/oracle/

>chown -R oracle:oinstall /u01/app/oracle/admin/ssss/adump/

>chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area/

>chown -R oracle:oinstall /u02/app/oracle/ssss/

                >chmod -R 775 /u01/app/oracle/

                >chmod -R 775 /u01/app/oracle/admin/ssss/adump/

                >chmod -R 775 /u01/app/oracle/fast_recovery_area/

                >chmod -R 775 /u02/app/oracle/ssss/

>export ORACLE_SID = ssss

>sqlplus / as sqldba

SQL>startup nomount

ORA-00845 : MEMORY_TARGET not supported on this system.

root>mount -t tmpfs shmfs -o size = 2g /dev/shm

SQL>startup nomount

SQL>define_editor = "vi"

SQL>ed dbscript.sql

                create database ssss

                datafile '/u02/app/oracle/ssss/systemo1.dbf'

                size 10m autoextend on

                sysaux datafile '/u02/app/oracle/ssss/sysaux01.dbf'

                size 10m autoextend on

                default tablespace ssss_ts

                datafile '/u02/app/oracle/ssss/ssss_ts01.dbf'

                size 10m autoextend on

                undo tablespace undotbs01

                datafile '/u02/app/oracle//ssss/undo01.dbf'

                size 10m autoextend on

                default temporary tablespace temp_ts

                tempfile '/u02/app/oracle/ssss/temp01.dbf'

                size 10m autoextend on logfile

                group 1('/u02/app/oracle/ssss/redo01.log') size 4m,

                group 2('/u02/app/oracle/ssss/redo02.log') size 4m

                controlfile reuse;

SQL>@dbscript.sql

SQL>ed run.sql

                @$ORACLE_HOME/rdbms/admin/catalog.sql

                @$ORACLE_HOME/rdbms/admin/catproc.sql

                conn system/manager

                @$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL>@run.sql

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

oracle> cd $ORACLE_HOME/sqlplus/admin

oracle> ls

oracle> vi glogin.sql

                set sqlprompt '&_user>'

oracle>sqlplus / as sysdba

SQL>select * from all_users;

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

oracle>sqlplus / as sysdba

SQL> select * from all_users;

Steps to install oracle on Linux

As per Oracle Flexible Architecture(OFA) oracle recommends us the following

1.create oinstall & dba groups

2.create oracle base

3.create oracle home

note: oracle base is not mandatory, if we are planning to install multiple versions of oracle in the

same server, recommended to have oracle base.

                >groupadd oinstall

                >groupadd dba

                or

                >groupadd -g 500 oinstall

                >groupadd -g 501 dba

500 & 501 are group id's

2.create the directory structure for oracle base and oracle home.

                >mkdir -p /u01/app/oracle/product  -->oracle base

                >mkdir -p /u01/app/oracle/product/11.2.0/db_home -->11g home

3.changing ownership & setting proper permissions on directory structures.

4.configure kernal parameters as per installation docuement.

note : In OEL(Oracle Enterprise Linux) its not necessary to set, but in other flavours of OS, we need

to set the parameters as per installation docuements.

kernal.Shmmax : This parameter represents the size in bytes of a single shared memory segment.Oracle recommends to set as much as possible to accomidate the entire SGA in a single shared memory segment.

Memory is allocated to SGA in the form of granules(4mb,16mb).

Inadequate value for this parameter leads to error message "Unable to attach shared memory segment" during database creation.

kernal.Shmmni : This parameter represents the total number of shared memory segments system wide(default value 4096).For most of the environments/configurations, default value is sufficient.

kernal.sem : This is related to semaphores. Semaphore is a locking mechanism at OS level.

kernal.File-max : This parameter represents the maximum number of data files supported by oracle

(upto oracle 10g, 65536 and in oracle 11g, almost 6lacs).

kernal.net.ipv4.ip_local_port_range : represents the range of ports that can be used.

eg: default listener port is 1521.

5.set shell limits for oracle user(optional)

                >vi /etc/security/limits.conf

                                oracle soft nproc <value>

                                oracle hard nproc <value>

                                oracle soft nofile <value>

                                oracle hard nofile <value>

6.invoke runInstaller

7.configure .bash_profile ( . indicates hidden).

NOTE : block size in oracle9i is 2k whereas block size in oracle10g & 11g is 8k.

Some Base table queries

SQL>select name,open_mode,log_mode from V$database;

SQL>select instance_name,status from V$instance;

SQL>select name from V$datafile;

SQL>select name from V$controlfile;

SQL>select member from V$logfile;

SQL>select name from V$tempfile;

SQL>select name from V$tablespace;

SQL>select * from V$diag_info;  --->with this query we can see the path of the alert log file.

 

>tail -f alert_hrms.log  -->to monitor the alert log file.

Database creation using DBCA

>cat /etc/sysctl.conf -->to see the kernal parameters.

                >cat /etc/security/limits.conf --> to see the limits for the user accounts.

1.creating a directory

                >mkdir -p /u02/app/oracle

                >chown -R oracle:oinstall /uo2/app/oracle

                >chmod -R 775 /u02/app/oracle

                >su - oracle

                >which dbca --> to find the DBCA file location

                >pwd

                >dbca --> we can execute from the current directory because we configured .bash_profile.

                >xhost +

                >su - oracle

                >dbca

2.select "create database" and select "general purpose or transaction processing".

3.specify the database name.

4.deselect "configure Enterprise Manager"

5.check "use the same Administrative password for all the accounts"

6.select "use common location for all the database files". Browse and select the location to

install.

7.enable "Enable Archieving" with "specify Fast Recovery Area"

8.select "Sample Schemas"  (optional)

 

                >ps -ef|grep smon  --> to see the instances which are up and running

                >cat /etc/oratab

                >export ORACLE_HOME = hrms

                >sqlplus / as sysdba

                >shutdown immediate

Oracle Installation

BASE

ORAINVENTORY

ORACLE HOME

groups --> dba & orainstall

users -->oracle

 

1.creating required number of groups & users.

                >groupadd oinstall

                >groupadd dba

                >useradd -m -g oinstall -G dba oracle

                -g indicates primary group

                -G indicates secondary group

                create password for oracle user

                >passwd oracle

                >id oracle

               

                cat /etc/passwd -->to see the existing users

                cat /etc/group --> to see the existing groups

2.creating directories

                >mkdir -p /u01/app/oracle -->Base

                >mkdir -p /u01/app/oraInventory -->Inventory

                >mkdir -p /u01/app/oracle/product/11.2.0/db_home -->oracle home

3.changing ownership

                >chown -R oracle:oinstall /u01/app/oracle

                >chown -R oracle:oinstall /u01/app/oraInventory

                >chown -R oracle:oinstall /u01/app/oracle/product/11.2.0/db_home

4.changing or giving permissions

                >chmod -R 775 /u01/app/oracle

                >chmod -R 775 /u01/app/oraInventory

                >chmod -R 775 /u01/app/oracle/product/11.2.0/db_home

5.xhost + --> to see the host name

                >su oracle

                >pwd

6.            >./runInstaller  or  sh runInstaller  --> for normal screen

7.            >vncpasswd -->give oracle as password

                >vncserver -->give hostname and password

                >cd /opt

                >cd /database

                >ls

                >.runInstaller

8.deselect "I wish to receive security updates via my oracle support".

deselect skip software updates.

select "install database software only"

select "single instance database installation"

select "Enterprise Edition"

 

9.select first script & paste in the terminal. This script sets proper permissions.

select second script & paste in the terminal(shift+insert). All the database entries will be stored

in the oratab file when we create the database using DBCA.

 

10.configuring .bash_profile

                >vi .bash_profile

                                export ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home

                                export PATH = $ORACLE_HOME/bin:$PATH:$HOME/bin

                >..bash_profile