Assigning Users Groups and Permissions

Setting users groups and permissions
=======================================

root@ora11gr2 ~]# groupadd jamil
[root@ora11gr2 ~]# groupadd ayaan
[root@ora11gr2 ~]# cat /etc/group

jamil:x:504:
ayaan:x:505:


[root@ora11gr2 ~]# useradd -g jamil -G ayaan saad
[root@ora11gr2 ~]# passwd saad
Changing password for user saad.
New UNIX password:
BAD PASSWORD: it is too short
Retype new UNIX password:
passwd: all authentication tokens updated successfully.


[root@ora11gr2 ~]# su - saad
[saad@ora11gr2 ~]$ exit
logout


[root@ora11gr2 ~]# cat /etc/passwd

saad:x:501:504::/home/saad:/bin/bash

[root@ora11gr2 ~]#


[root@ora11gr2 ~]# mkdir saad
[root@ora11gr2 ~]# chown -R saad:jamil saad/
[root@ora11gr2 ~]# chmod -R 775 saad/
[root@ora11gr2 ~]# ls -al saad/
total 12
drwxrwxr-x  2 saad jamil 4096 Oct 14 10:50 .
drwxr-x--- 16 root root  4096 Oct 14 10:50 ..

Making Partition in Linux

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1097     8811621   83  Linux
/dev/sda2            1098        1228     1052257+  82  Linux swap / Solaris
/dev/sda3            1229        1304      610470   83  Linux

Disk /dev/sdb: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table
[root@ol5-11gr2 ~]#
[root@ol5-11gr2 ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1): 1  
Last cylinder or +size or +sizeM or +sizeK (1-130, default 130): +200M

Command (m for help): p

Disk /dev/sdb: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1          25      200781   83  Linux

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
e
Partition number (1-4): 2
First cylinder (26-130, default 26):
Using default value 26
Last cylinder or +size or +sizeM or +sizeK (26-130, default 130):
Using default value 130

Command (m for help): p

Disk /dev/sdb: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1          25      200781   83  Linux
/dev/sdb2              26         130      843412+   5  Extended

Command (m for help): n
Command action
   l   logical (5 or over)
   p   primary partition (1-4)
l
First cylinder (26-130, default 26):
Using default value 26
Last cylinder or +size or +sizeM or +sizeK (26-130, default 130): +200M

Command (m for help): p

Disk /dev/sdb: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1          25      200781   83  Linux
/dev/sdb2              26         130      843412+   5  Extended
/dev/sdb5              26          50      200781   83  Linux

Command (m for help): n
Command action
   l   logical (5 or over)
   p   primary partition (1-4)
l
First cylinder (51-130, default 51):
Using default value 51
Last cylinder or +size or +sizeM or +sizeK (51-130, default 130): +200M

Command (m for help): p

Disk /dev/sdb: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1          25      200781   83  Linux
/dev/sdb2              26         130      843412+   5  Extended
/dev/sdb5              26          50      200781   83  Linux
/dev/sdb6              51          75      200781   83  Linux

Command (m for help): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@ol5-11gr2 ~]# fdisk -

Unable to open -
[root@ol5-11gr2 ~]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1097     8811621   83  Linux
/dev/sda2            1098        1228     1052257+  82  Linux swap / Solaris
/dev/sda3            1229        1304      610470   83  Linux

Disk /dev/sdb: 1073 MB, 1073741824 bytes
255 heads, 63 sectors/track, 130 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1          25      200781   83  Linux
/dev/sdb2              26         130      843412+   5  Extended
/dev/sdb5              26          50      200781   83  Linux
/dev/sdb6              51          75      200781   83  Linux
[root@ol5-11gr2 ~]# cd /
[root@ol5-11gr2 /]#
[root@ol5-11gr2 /]# mkfs -t ext3 /dev/sdb5
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=1024 (log=0)
Fragment size=1024 (log=0)
50200 inodes, 200780 blocks
10039 blocks (5.00%) reserved for the super user
First data block=1
Maximum filesystem blocks=67371008
25 block groups
8192 blocks per group, 8192 fragments per group
2008 inodes per group
Superblock backups stored on blocks:
        8193, 24577, 40961, 57345, 73729

Writing inode tables: done                            
Creating journal (4096 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 35 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@ol5-11gr2 /]# mkdir saad
[root@ol5-11gr2 /]# ls saad
[root@ol5-11gr2 /]# mount /dev/sdb5 saad

AWR Features in 10g

The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:

• Automatic Database Diagnostic Monitor
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using:

BEGIN

  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.

    interval  => 30);          -- Minutes. Current value retained if NULL.

END;

/

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

Extra snapshots can be taken and existing snapshots can be removed using:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN

  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

    low_snap_id  => 22, 

    high_snap_id => 32);

END;

/

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing like:

BEGIN

  DBMS_WORKLOAD_REPOSITORY.create_baseline (

    start_snap_id => 210, 

    end_snap_id   => 220,

    baseline_name => 'batch baseline');

END;

/

The pair of snapshots associated with a baseline are retained until the baseline is

explicitly deleted:

BEGIN

  DBMS_WORKLOAD_REPOSITORY.drop_baseline (

    baseline_name => 'batch baseline',

    cascade       => FALSE); -- Deletes associated snapshots if TRUE.

END;

/

Baseline information can be queried from the DBA_HIST_BASELINE view.

Workload Repository Views

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
• V$METRIC - Displays metric information.
• V$METRICNAME - Displays the metrics associated with each metric group.
• V$METRIC_HISTORY - Displays historical metrics.
• V$METRICGROUP - Displays all metrics groups.
• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
• DBA_HIST_BASELINE - Displays baseline information.
• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
• DBA_HIST_SNAPSHOT - Displays snapshot information.
• DBA_HIST_SQL_PLAN - Displays SQL execution plans. 
• DBA_HIST_WR_CONTROL - Displays AWR setting

Workload Repository Reports

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the

awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

Undo TableSpace Management

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

• Roll back transactions when a ROLLBACK statement is issued
• Recover the database
• Provide read consistency
• Analyze data as of an earlier point in time by using Flashback Query
• Recover from logical corruptions using Flashback features

Earlier releases of Oracle Database used rollback segments to store undo. Oracle9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends that you use undo tablespace to manage undo rather than rollback segments.

Switching to Automatic Management of Undo Space

To go for automatic management of undo space set the following parameter.

Steps:-

1. If you have not created an undo tablespace at the time of creating a database then, create an undo tablespace by typing the following command

SQL>create undo tablespace myundo datafile 
         ‘/u01/oracle/ica/undo_tbs.dbf’ size 500M
                        autoextend ON next 5M ;

When the system is first running in the production environment, you may be unsure of the space requirements of the undo tablespace. In this case, you can enable automatic extension for datafiles of the undo tablespace so that they automatically increase in size when more space is needed

2. Shutdown the Database and set the following parameters in parameter file.

UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=myundo

3. Start the Database.

Now Oracle Database will use Automatic Undo Space Management.

Calculating the Space Requirements For Undo Retention

You can calculate space requirements manually using the following formula:

UndoSpace = UR * UPS + overhead

where:

• UndoSpace is the number of undo blocks
• UR is UNDO_RETENTION in seconds. This value should take into consideration long-running queries and any flashback requirements.
• UPS is undo blocks for each second
• overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)

As an example, if UNDO_RETENTION is set to 3 hours, and the transaction rate (UPS) is 100 undo blocks for each second, with a 8K block size, the required undo space is computed as follows:

(3 * 3600 * 100 * 8K) = 8.24GBs

To get the values for UPS, Overhead query the V$UNDOSTAT view. By giving the following statement

SQL> Select * from V$UNDOSTAT;

Altering UNDO Tablespace

If the Undo tablespace is full, you can resize existing datafiles or add new datafiles to it

The following example extends an existing datafile

SQL> alter database datafile ‘/u01/oracle/ica/undo_tbs.dbf’ resize 700M

The following example adds a new datafile to undo tablespace

SQL> ALTER TABLESPACE myundo

     ADD DATAFILE '/u01/oracle/ica/undo02.dbf' SIZE 200M AUTOEXTEND ON
                   NEXT 1M MAXSIZE UNLIMITED;

Dropping an Undo Tablespace

Use the DROP TABLESPACE statement to drop an undo tablespace. The following example drops the undo tablespace undotbs_01:

SQL> DROP TABLESPACE myundo;

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails.

Switching Undo Tablespaces

You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.

The following statement switches to a new undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = myundo2;

Assuming myundo is the current undo tablespace, after this command successfully executes, the instance uses myundo2 in place of myundo as its undo tablespace.

Viewing Information about Undo Tablespace

To view statistics for tuning undo tablespace query the following dictionary

SQL>select * from v$undostat;

To see how many active Transactions are there and to see undo segment information give the following command

SQL>select * from v$transaction;

To see the sizes of extents in the undo tablespace give the following query

SQL>select * from DBA_UNDO_EXTENTS;

Changing Sessions Parameter

SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU

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

processes                                      122             150        150        150

sessions                                       130             170        170        170

enqueue_locks                                  272             324       2230       2230

enqueue_resources                              186             199        968  UNLIMITED

ges_procs                                        0               0          0          0

ges_ress                                         0               0          0  UNLIMITED

ges_locks                                        0               0          0  UNLIMITED

ges_cache_ress                                   0               0          0  UNLIMITED

ges_reg_msgs                                     0               0          0  UNLIMITED

ges_big_msgs                                     0               0          0  UNLIMITED

ges_rsv_msgs                                     0               0          0          0

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU

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

gcs_resources                                    0               0     279488     279488

gcs_shadows                                      0               0     279488     279488

dml_locks                                        1             140        748  UNLIMITED

temporary_table_locks                            0               9  UNLIMITED  UNLIMITED

transactions                                 32508           44739        187  UNLIMITED

branches                                         0              15        187  UNLIMITED

cmtcallbk                                        0               5        187  UNLIMITED

sort_segment_locks                             274             400  UNLIMITED  UNLIMITED

max_rollback_segments                           19              30         38         38

max_shared_servers                               1               1         20         20

parallel_max_servers                             0               5          6          6

22 rows selected.

SQL> show parameter session

NAME                                 TYPE        VALUE

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

java_max_sessionspace_size           integer     0

java_soft_sessionspace_limit         integer     0

license_max_sessions                 integer     0

license_sessions_warning             integer     0

logmnr_max_persistent_sessions       integer     1

mts_sessions                         integer     165

session_cached_cursors               integer     0

session_max_open_files               integer     10

sessions                             integer     170

shared_server_sessions               integer     165

SQL> show parameter process

NAME                                 TYPE        VALUE

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

aq_tm_processes                      integer     1

db_writer_processes                  integer     1

job_queue_processes                  integer     10

log_archive_max_processes            integer     2

processes                            integer     150

SQL> show parameter transactions

NAME                                 TYPE        VALUE

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

transactions                         integer     187

transactions_per_rollback_segment    integer     5

Formula for setting  sessions

Sessions  = (1.1 * PROCESSES) + 5

For Prime Database

Processes = 220

Sessions =247

Changing  these paramaters requires downtime of database

Now change the parameters in pfile and

Startup  pfile=’ /appOracle/product/9.2.0/dbs/initprimepro.ora’;

SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU

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

processes                                       40              41        220        220

sessions                                        43              46        247        247

enqueue_locks                                  175             178       3140       3140

enqueue_resources                              172             172       1304  UNLIMITED

ges_procs                                        0               0          0          0

ges_ress                                         0               0          0  UNLIMITED

ges_locks                                        0               0          0  UNLIMITED

ges_cache_ress                                   0               0          0  UNLIMITED

ges_reg_msgs                                     0               0          0  UNLIMITED

ges_big_msgs                                     0               0          0  UNLIMITED

ges_rsv_msgs                                     0               0          0          0

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU

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

gcs_resources                                    0               0     279488     279488

gcs_shadows                                      0               0     279488     279488

dml_locks                                        0              10       1084  UNLIMITED

temporary_table_locks                            0               0  UNLIMITED  UNLIMITED

transactions                                     2               8        271  UNLIMITED

branches                                         0               0        271  UNLIMITED

cmtcallbk                                        0               0        271  UNLIMITED

sort_segment_locks                              12              12  UNLIMITED  UNLIMITED

max_rollback_segments                           11              11         55         55

max_shared_servers                               1               1         20         20

parallel_max_servers                             0               0          6          6