tuning advisor queries

select client_name,status from dba_autotask_task;

select client_name,status from dba_autotask_client;

select client_name, operation_name, status from dba_autotask_operation;

tablespaces

Tablespaces:

A database is divided into one or more logical storage units called tablespaces. Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data, Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures.

 Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.

Add More Space for a Database

The size of a tablespace is the total size of the all datafiles that corresponds to the tablespace. The size of a database is the collective size of the tablespaces that constitute the database.

You can enlarge a database in three ways:

·       Add a datafile to a tablespace

·       Add a new tablespace

·       Increase the size of a datafile

When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace.

Type of Tablespaces:

SYSTEM Tablespace: Every Oracle database contains a tablespace named SYSTEM, which Oracle Database creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

SYSAUX tablespace: The SYSAUX tablespace is an auxiliary tablespace to system tablespace The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR).

UNDO tablespace: Undo tablespaces are special tablespaces used for storing undo information. When the first DML operation is run within a transaction, the transaction is bound assigned to an undo segment in the current undo tablespace.

An undo tablespace cannot be dropped if it is being used by any instance.

USER tablespace: it is default tablespace for users.

TEMP tablespace: Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation.

More tablespaces:

A very small database may need only the SYSTEM tablespace; however, Oracle recommends that you create at least one additional tablespace to store user data separate from data dictionary information. This gives you more flexibility in various database administration operations and reduces contention among dictionary objects and schema objects for the same datafiles.

A database administrator can perform the following actions:

·       Create new tablespaces

·       Add datafiles to tablespaces

·       Set and alter default segment storage settings for segments created in a tablespace

·       Make a tablespace read only or read/write

·       Make a tablespace temporary or permanent

·       Rename tablespaces

·       Drop tablespaces

·       Transport tablespaces across databases and platforms

VIEWS related to tablespaces and datafiles

è dba_tablespaces, user_tablespaces

è dba_data_files, dba_temp_files

è v$tablespace

è dba_segments, user_segments

è  dba_free_space, user_free_space

Creating tablespace:

SQL> create tablespace test1 datafile '/u01/app/oracle/oradata/test001.dbf' size 50M;

Creating tablespace with auto extended on.

Sql>CREATE TABLESPACE ts_something DATAFILE '/u01/app/oracle/ts_sth.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M;

To check the tablespace created.

SQL> set lines 300

SQL> col FILE_NAME for a50

SQL> set pages 100

SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;

To check the max size of datafiles;

SQL>  select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files;

Adding datafiles to tablespaces:

SQL> alter tablespace test1 add datafile '/u01/app/oracle/oradata/test002.dbf' size 50M;

Resizing datafiles

SQL> alter database datafile '/u01/app/oracle/oradata/test001.dbf' resize 100M;

To alter maxsize

SQL> alter database datafile '/u01/app/oracle/oradata/test003.dbf' autoextend on maxsize 6G;

Creating undo tablespace:

Sql>CREATE UNDO TABLESPACE undots1DATAFILE '/u01/app/oracle/oradata/undotbs_1a.dbf'SIZE 10M AUTOEXTEND ON;

Creating Temporary tablespace:

Sql>CREATE TEMPORARY TABLESPACE temp_mtr TEMPFILE '/dbf1/mtr_temp01.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M;

è Note, a temporary tablespace has tempfiles, not datafiles.

Droping datafiles:

Sql>alter tablespace<ts name> drop datafile ‘<filename>’;

Droping tablespaces;

Sql>DROP TABLESPACE <TSname>;

Including datafiles in OS level

Sql>DROP TABLESPACE <TSname> INCLUDING CONTENTS AND DATAFILES;

Move or Rename a datafile do the following.

->   offline datafile.

->   Rename the physical file on the OS.

->   Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.

->  recover datafile.
-> put the datafile online.

Sql>alter datafile '/u01/app/oracle/oradata/orcl/users01.dbf' offline;

Sql>exit

$mv  /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/users02.dbf

$sqlplus / as sysdba

Sql>Alter database rename file ‘u01/app/oracle/oradata/orcl/users01.dbf’ to              ‘/u01/app/oracle/oradata/users02.dbf’

Sql>recover datafile '/u01/app/oracle/oradata/orcl/users01.dbf';

Sql>alter datafile '/u01/app/oracle/oradata/orcl/users01.dbf' online;

Extent management:

 Whenever a segment will be full and needs extra space then oracle automatically allocates space in the forms of extents(collection of continuous blocks) .How this extent will be allocated and What will be size of this newly allocated extent is decided by extent management according to storage parameters. You can define storage parameters (initial,next,minextents,maxextents,pctincrease...) in case of dictionary managed tablespace(DMTS) but not for locally managed tablespaces(LMTS). In LMTS we have options of AUTOALLOCATE  and UNIFORM.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

Dictionary Managed Tablespaces (DMT):

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

SQL> CREATE TABLESPACE ts1 DATAFILE '/oradata/ts1_01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed Tablespaces (LMT):

Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files.

To know extent management of tablespaces; from 10g onwards by default it will create LMT.

NOTE: we cannot create dictionary tablespace if system tablespace is locally managed tablespace.

What Is Undo?

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 Oracle Flashback Query

·       Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. In this management mode, you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions.

You set the UNDO_MANAGEMENT initialization parameter to AUTO to enable automatic undo management.

UNDO tablespace management:

We assign an undo tablespace to an instance in one of two ways:

·       At instance startup. You can specify the undo tablespace in the initialization file or let the system choose an available undo tablespace.

·       While the instance is running. Use ALTER SYSTEM SET UNDO_TABLESPACE to replace the active undo tablespace with another undo tablespace.

Undo retention:

     After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

To set the undo retention period:

Set UNDO_RETENTION in the initialization parameter file (value in seconds).

UNDO_RETENTION = 1800

Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

ALTER SYSTEM SET UNDO_RETENTION = 2400;

Retention Guarantee

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.

Sql> ALTER TABLESPACE <undo tbs> retention guarantee.

Sql> ALTER TABLESPACE <undo tbs> retention noguarantee.

You can determine the current retention period by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT view. This view contains one row for each 10-minute statistics collection interval over the last 4 days. (Beyond 4 days, the data is available in the DBA_HIST_UNDOSTAT view.)TUNED_UNDORETENTION is given in seconds.

Sql>select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention from v$undostat order by end_time;

BEGIN_TIME      END_TIME        TUNED_UNDORETENTION

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

04-FEB-05 00:01 04-FEB-05 00:11               12100

      ...                                         

07-FEB-05 23:21 07-FEB-05 23:31               86700

07-FEB-05 23:31 07-FEB-05 23:41               86700

07-FEB-05 23:41 07-FEB-05 23:51               86700

07-FEB-05 23:51 07-FEB-05 23:52               86700

You can decide how many seconds based on the TUNED_UNDORETENTION.

Extending space to existing disk in VM

login as: root

oracle@192.168.86.128's password:

Last login: Sat Jun 25 09:39:36 2016

[root@localhost ~]# fdisk /dev/sda

Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.

Be careful before using the write command.

Command (m for help): n

Partition type:

   p   primary (2 primary, 0 extended, 2 free)

   e   extended

Select (default p): p

Partition number (3,4, default 3):

First sector (41943040-62914559, default 41943040):

Using default value 41943040

Last sector, +sectors or +size{K,M,G} (41943040-62914559, default 62914559):

Using default value 62914559

Partition 3 of type Linux and of size 10 GiB is set

Command (m for help): p

Disk /dev/sda: 32.2 GB, 32212254720 bytes, 62914560 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0x000b2714

   Device Boot      Start         End      Blocks   Id  System

/dev/sda1   *        2048     2050047     1024000   83  Linux

/dev/sda2         2050048    41943039    19946496   8e  Linux LVM

/dev/sda3        41943040    62914559    10485760   83  Linux

Command (m for help): t

Partition number (1-3, default 3):

Hex code (type L to list all codes): 8e

Changed type of partition 'Linux' to 'Linux LVM'

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.

The kernel still uses the old table. The new table will be used at

the next reboot or after you run partprobe(8) or kpartx(8)

Syncing disks.

[root@localhost ~]# reboot

[root@localhost ~]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes, 62914560 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0x000b2714

   Device Boot      Start         End      Blocks   Id  System

/dev/sda1   *        2048     2050047     1024000   83  Linux

/dev/sda2         2050048    41943039    19946496   8e  Linux LVM

/dev/sda3        41943040    62914559    10485760   8e  Linux LVM

Disk /dev/mapper/ol-swap: 3145 MB, 3145728000 bytes, 6144000 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk /dev/mapper/ol-root: 17.3 GB, 17276338176 bytes, 33742848 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

[root@localhost ~]# pvcreate /dev/sda3

WARNING: xfs signature detected on /dev/sda3 at offset 0. Wipe it? [y/n] y

  Wiping xfs signature on /dev/sda3.

  Physical volume "/dev/sda3" successfully created

[root@localhost ~]# vgdisplay

  --- Volume group ---

  VG Name               ol

  System ID

  Format                lvm2

  Metadata Areas        1

  Metadata Sequence No  3

  VG Access             read/write

  VG Status             resizable

  MAX LV                0

  Cur LV                2

  Open LV               2

  Max PV                0

  Cur PV                1

  Act PV                1

  VG Size               19.02 GiB

  PE Size               4.00 MiB

  Total PE              4869

  Alloc PE / Size       4869 / 19.02 GiB

  Free  PE / Size       0 / 0

  VG UUID               Ay0nlL-BUuA-ltwR-GzYz-5NUl-ooBM-qBRNA6

[root@localhost ~]# pvscan

  PV /dev/sda2   VG ol              lvm2 [19.02 GiB / 0    free]

  PV /dev/sda3                      lvm2 [10.00 GiB]

  Total: 2 [29.02 GiB] / in use: 1 [19.02 GiB] / in no VG: 1 [10.00 GiB]

[root@localhost ~]# vgextend ol /dev/sda3

  Volume group "ol" successfully extended

[root@localhost ~]# pvscan

  PV /dev/sda2   VG ol   lvm2 [19.02 GiB / 0    free]

  PV /dev/sda3   VG ol   lvm2 [10.00 GiB / 10.00 GiB free]

  Total: 2 [29.02 GiB] / in use: 2 [29.02 GiB] / in no VG: 0 [0   ]

[root@localhost ~]# lvdisplay

  --- Logical volume ---

  LV Path                /dev/ol/swap

  LV Name                swap

  VG Name                ol

  LV UUID                A3qVoi-7ZI3-K6EN-ioa3-f1J3-n8ND-0yR3Xn

  LV Write Access        read/write

  LV Creation host, time localhost, 2015-01-02 15:22:08 -0500

  LV Status              available

  # open                 2

  LV Size                2.93 GiB

  Current LE             750

  Segments               1

  Allocation             inherit

  Read ahead sectors     auto

  - currently set to     256

  Block device           252:0

  --- Logical volume ---

  LV Path                /dev/ol/root

  LV Name                root

  VG Name                ol

  LV UUID                0anOak-0QeZ-qmE2-QVyL-Bji9-lMzg-5I2S3k

  LV Write Access        read/write

  LV Creation host, time localhost, 2015-01-02 15:22:08 -0500

  LV Status              available

  # open                 1

  LV Size                16.09 GiB

  Current LE             4119

  Segments               1

  Allocation             inherit

  Read ahead sectors     auto

  - currently set to     256

  Block device           252:1

[root@localhost ~]# lvextend /dev/ol/root /dev/sda3

  Extending logical volume root to 26.09 GiB

  Logical volume root successfully resized

[root@localhost ~]# df -h

Filesystem           Size  Used Avail Use% Mounted on

/dev/mapper/ol-root   17G   11G  6.0G  63% /

devtmpfs             857M     0  857M   0% /dev

tmpfs                866M   80K  866M   1% /dev/shm

tmpfs                866M  9.0M  857M   2% /run

tmpfs                866M     0  866M   0% /sys/fs/cgroup

/dev/sda1            997M  153M  844M  16% /boot

[root@localhost ~]# resize2fs /dev/ol/root

resize2fs 1.42.9 (28-Dec-2013)

resize2fs: Bad magic number in super-block while trying to open /dev/ol/root

Couldn't find valid filesystem superblock.

[root@localhost ~]# xfs_growfs /dev/mapper/ol-root

meta-data=/dev/mapper/ol-root    isize=256    agcount=4, agsize=1054464 blks

         =                       sectsz=512   attr=2, projid32bit=1

         =                       crc=0

data     =                       bsize=4096   blocks=4217856, imaxpct=25

         =                       sunit=0      swidth=0 blks

naming   =version 2              bsize=4096   ascii-ci=0 ftype=0

log      =internal               bsize=4096   blocks=2560, version=2

         =                       sectsz=512   sunit=0 blks, lazy-count=1

realtime =none                   extsz=4096   blocks=0, rtextents=0

data blocks changed from 4217856 to 6838272

[root@localhost ~]# df -h

Filesystem           Size  Used Avail Use% Mounted on

/dev/mapper/ol-root   27G   11G   16G  39% /

devtmpfs             857M     0  857M   0% /dev

tmpfs                866M   80K  866M   1% /dev/shm

tmpfs                866M  9.0M  857M   2% /run

tmpfs                866M     0  866M   0% /sys/fs/cgroup

/dev/sda1            997M  153M  844M  16% /boot