Oracle – Página: 12 – Rodrigo Silva e Silva

Criar Tablespace

12:49:57 SYS@ORCL > create tablespace tools datafile '/u01/oracle/oradata/ORCL/tools.dbf' size 200m autoextend on next 100m;
Tablespace created.

1 2	12:49:57 SYS@ORCL > create tablespace tools datafile '/u01/oracle/oradata/ORCL/tools.dbf' size 200m autoextend on next 100m; Tablespace created.

Consultando espaço livre na tablespace.

select * from dba_free_space where tablespace_name = 'TOOLS';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
TOOLS                                  13        128  208666624      25472           13

select * from dba_free_space where tablespace_name = 'TOOLS';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

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

TOOLS 13 128 208666624 25472 13

Instalando o Statspack

@?/rdbms/admin/spcreate

1	@?/rdbms/admin/spcreate

Defina a senha do perfstat

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat

Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat

Definir a tablespace padrão

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS              STATSPACK DEFAULT TABLESPACE
------------------------------ --------------------- ----------------------------
PROD01_DATA                    PERMANENT
PROD01_INDX                    PERMANENT
SYSAUX                         PERMANENT             *
TOOLS                          PERMANENT
USERS                          PERMANENT
Elapsed: 00:00:00.00

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: TOOLS

Using tablespace TOOLS as PERFSTAT default tablespace.

Choose the Default tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store user data. Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installation FAILING, as

using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace. This is the tablespace

in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE

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

PROD01_DATA PERMANENT

PROD01_INDX PERMANENT

SYSAUX PERMANENT *

TOOLS PERMANENT

USERS PERMANENT

Elapsed: 00:00:00.00

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace: TOOLS

Using tablespace TOOLS as PERFSTAT default tablespace.

Definir a tablespace temporária para o usuário PERFSTAT

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE
------------------------------ --------------------- --------------------------
TEMP                           TEMPORARY             *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
...

...

...

12:53:33 SYS@ORCL > set echo off;
Creating Package STATSPACK...

Package created.

Elapsed: 00:00:07.92
No errors.
Creating Package Body STATSPACK...

Package body created.

Elapsed: 00:01:29.14
No errors.

<strong>NOTE:</strong>
<strong>SPCPKG complete. Please check spcpkg.lis for any errors.</strong>

Choose the Temporary tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store temporary data (e.g. for sort workareas). Specifying the SYSTEM

tablespace for the user's temporary tablespace will result in the

installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE

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

TEMP TEMPORARY *

Pressing <return> will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

NOTE:

SPCUSR complete. Please check spcusr.lis for any errors.

...

12:53:33 SYS@ORCL > set echo off;

Creating Package STATSPACK...

Package created.

Elapsed: 00:00:07.92

No errors.

Creating Package Body STATSPACK...

Package body created.

Elapsed: 00:01:29.14

No errors.

<strong>SPCPKG complete. Please check spcpkg.lis for any errors.</strong>

Coletando estatísticas manual.

execute statspack.snap;

1	execute statspack.snap;

Coletando estatísticas automática.

@?/rdbms/admin/spauto

1	@?/rdbms/admin/spauto

Listando os Snapshots

[oracle@ORCL]$ sqlplus perfstat/perfstat

select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database
order by 1, 2;

NAME         SNAP_ID Date/Time
--------- ---------- -------------------
ORCL               1 16.04.2018:13:00:04
ORCL               2 16.04.2018:14:00:18
ORCL               3 16.04.2018:15:00:29
ORCL              11 16.04.2018:16:00:11
ORCL              12 16.04.2018:17:00:26
ORCL              13 16.04.2018:18:00:26
ORCL              14 16.04.2018:19:00:11
ORCL              15 16.04.2018:20:00:11
ORCL              16 16.04.2018:21:00:20
ORCL              17 16.04.2018:22:00:05

10 rows selected.

[oracle@ORCL]$ sqlplus perfstat/perfstat

select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')

"Date/Time" from stats$snapshot,v$database

order by 1, 2;

NAME SNAP_ID Date/Time

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

ORCL 1 16.04.2018:13:00:04

ORCL 2 16.04.2018:14:00:18

ORCL 3 16.04.2018:15:00:29

ORCL 11 16.04.2018:16:00:11

ORCL 12 16.04.2018:17:00:26

ORCL 13 16.04.2018:18:00:26

ORCL 14 16.04.2018:19:00:11

ORCL 15 16.04.2018:20:00:11

ORCL 16 16.04.2018:21:00:20

ORCL 17 16.04.2018:22:00:05

10 rows selected.

Analisando relatório

@?/rdbms/admin/spreport

1	@?/rdbms/admin/spreport

Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL          ORCL                  1 16 Apr 2018 13:00     5
                                  2 16 Apr 2018 14:00     5
                                  3 16 Apr 2018 15:00     5
                                 11 16 Apr 2018 16:00     5
                                 12 16 Apr 2018 17:00     7
                                 13 16 Apr 2018 18:00     7

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11
Begin Snapshot Id specified: 11

Enter value for end_snap: 13
End   Snapshot Id specified: 13



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_11_13.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: analise lentidao

Listing all Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level Comment

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

ORCL ORCL 1 16 Apr 2018 13:00 5

2 16 Apr 2018 14:00 5

3 16 Apr 2018 15:00 5

11 16 Apr 2018 16:00 5

12 16 Apr 2018 17:00 7

13 16 Apr 2018 18:00 7

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 11

Begin Snapshot Id specified: 11

Enter value for end_snap: 13

End Snapshot Id specified: 13

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_11_13. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: analise lentidao

Report Gerado

Using the report name analise_lentidao

STATSPACK report for

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          1775857365 NBS                 1 16-Apr-18 15:11 12.2.0.1.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     bdora-granleste. Linux x86 64-bit           8     8       4         15.4

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:         33 17-Apr-18 14:00:00       92       3.4
  End Snap:         35 17-Apr-18 16:00:25       96       2.6
   Elapsed:     120.42 (mins) Av Act Sess:       0.7
   DB time:      80.42 (mins)      DB CPU:      53.39 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:     6,480M     6,432M   Std Block Size:         8K
     Shared Pool:     1,184M     1,232M       Log Buffer:     4,248K

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                0.7                0.1        0.00        0.00
       DB CPU(s):                0.4                0.0        0.00        0.00
       Redo size:           26,156.2            1,868.5
   Logical reads:           14,586.2            1,042.0
   Block changes:              211.4               15.1
  Physical reads:                1.1                0.1
 Physical writes:                9.7                0.7
      User calls:              158.3               11.3
          Parses:              235.6               16.8
     Hard parses:               14.0                1.0
W/A MB processed:                0.1                0.0
          Logons:                0.3                0.0
        Executes:              570.9               40.8
       Rollbacks:               13.9                1.0
    Transactions:               14.0

Using the report name analise_lentidao

STATSPACK report for

Database DB Id Instance Inst Num Startup Time Release RAC

~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---

1775857365 NBS 1 16-Apr-18 15:11 12.2.0.1.0 NO

Host Name Platform CPUs Cores Sockets Memory (G)

~~~~ ---------------- ---------------------- ----- ----- ------- ------------

bdora-granleste. Linux x86 64-bit 8 8 4 15.4

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment

~~~~~~~~ ---------- ------------------ -------- --------- ------------------

Begin Snap: 33 17-Apr-18 14:00:00 92 3.4

End Snap: 35 17-Apr-18 16:00:25 96 2.6

Elapsed: 120.42 (mins) Av Act Sess: 0.7

DB time: 80.42 (mins) DB CPU: 53.39 (mins)

Cache Sizes Begin End

~~~~~~~~~~~ ---------- ----------

Buffer Cache: 6,480M 6,432M Std Block Size: 8K

Shared Pool: 1,184M 1,232M Log Buffer: 4,248K

Load Profile Per Second Per Transaction Per Exec Per Call

~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------

DB time(s): 0.7 0.1 0.00 0.00

DB CPU(s): 0.4 0.0 0.00 0.00

Redo size: 26,156.2 1,868.5

Logical reads: 14,586.2 1,042.0

Block changes: 211.4 15.1

Physical reads: 1.1 0.1

Physical writes: 9.7 0.7

User calls: 158.3 11.3

Parses: 235.6 16.8

Hard parses: 14.0 1.0

W/A MB processed: 0.1 0.0

Logons: 0.3 0.0

Executes: 570.9 40.8

Rollbacks: 13.9 1.0

Transactions: 14.0

Remover jobs agendados…

Checando parâmetro audit

SYS@ORCL> show parameter audit;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
audit_file_dest                      string                           /opt/oracle/admin/ORCL/adump
audit_sys_operations                 boolean                          FALSE
audit_syslog_level                   string
audit_trail                          string                           DB

SYS@ORCL> show parameter audit;

NAME TYPE VALUE

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

audit_file_dest string /opt/oracle/admin/ORCL/adump

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

Execute o seguinte sql para desativar auditoria.

SYS@ORCL > ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;

System altered.

SYS@ORCL > ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;

System altered.

Baixar a instância

SYS@ORCL > shutdown immediate;

1	SYS@ORCL > shutdown immediate;

SYS@ORCL > startup

1	SYS@ORCL > startup

Checando parâmetro audit

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
audit_file_dest                      string                           /opt/oracle/admin/nbs/adump
audit_sys_operations                 boolean                          FALSE
audit_syslog_level                   string
audit_trail                          string                            NONE

NAME TYPE VALUE

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

audit_file_dest string /opt/oracle/admin/nbs/adump

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string NONE

Categoria: Oracle

Habilitando ARCHIVELOG

Instalação Statspack / Coleta estatísticas completo

Query – Listar Tamanho dos schemas

ORA-02374: conversion error loading table

Oracle Disable audit-trail