Rodrigo Silva e Silva – Página: 17

Habilitando ARCHIVELOG

Publicado em 17 de abril de 2018 by rodrigosilvaesilva

Consulta view v$databases

SYS@ORCL > select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SYS@ORCL > select log_mode from v$database;

LOG_MODE

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

NOARCHIVELOG

Baixar o banco

SYS@ORCL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@ORCL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Iniciar o banco modo Mount

SYS@ORCL> startup mount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size            1157628808 bytes
Database Buffers         3103784960 bytes
Redo Buffers               12107776 bytes
Database mounted.

SYS@ORCL> startup mount

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size 2260088 bytes

Variable Size 1157628808 bytes

Database Buffers 3103784960 bytes

Redo Buffers 12107776 bytes

Database mounted.

Habilitar modo Archivelog

SYS@ORCL> alter database archivelog;
alter database set archivelog

1 2	SYS@ORCL> alter database archivelog; alter database set archivelog

Alterando status para Open

SYS@ORCL > alter database open;

Database altered.

SYS@ORCL > alter database open;

Database altered.

Verificando status

SYS@ORCL > select status from v$instance;

STATUS
------------
OPEN

SYS@ORCL > select status from v$instance;

STATUS

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

OPEN

Consulta view v$database

SYS@ORCL > select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SYS@ORCL > select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

Instalação Statspack / Coleta estatísticas completo

Publicado em 17 de abril de 2018 by rodrigosilvaesilva

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…

Query – Listar Tamanho dos schemas

Publicado em 16 de abril de 2018 by rodrigosilvaesilva

Query, listando tamanho dos Schema por ordem de tamanho

SELECT owner, trunc(sum(bytes)/1024/1024/1024,2) "SIZE GB" 
FROM dba_segments 
where owner not in 
	( 'SYSTEM', 'XDB', 'SYS', 'TSMSYS', 'MDSYS', 
	'EXFSYS', 'WMSYS', 'ORDSYS', 'OUTLN', 'DBSNMP') 
group by owner
order by 2 desc;

SELECT owner, trunc(sum(bytes)/1024/1024/1024,2) "SIZE GB"

FROM dba_segments

where owner not in

( 'SYSTEM', 'XDB', 'SYS', 'TSMSYS', 'MDSYS',

'EXFSYS', 'WMSYS', 'ORDSYS', 'OUTLN', 'DBSNMP')

group by owner

order by 2 desc;

OWNER                             SIZE GB
------------------------------ ----------
ORCL                                16.99
APEX_030200                           .14
SYSMAN                                .12
ORDDATA                               .01
CTXSYS                                  0
FLOWS_FILES                             0
FLAVIO                                  0
APPQOSSYS                               0
ADRIANA                                 0

8 rows selected.

OWNER SIZE GB

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

ORCL 16.99

APEX_030200 .14

SYSMAN .12

ORDDATA .01

CTXSYS 0

FLOWS_FILES 0

FLAVIO 0

APPQOSSYS 0

ADRIANA 0

8 rows selected.

Fonte: Andre Spadini

Criando volume com LVM

Publicado em 14 de abril de 2018 by rodrigosilvaesilva

Listando novo disco adicionado Vmware

[root@localhost ~]# fdisk -l

Disk /dev/sdc: 53.7 GB, 53687091200 bytes, 104857600 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 ~]# fdisk -l

Disk /dev/sdc: 53.7 GB, 53687091200 bytes, 104857600 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

Criando PV

[root@localhost ~]# pvcreate /dev/sdc
Physical volume "/dev/sdc" successfully created

1 2	[root@localhost ~]# pvcreate /dev/sdc Physical volume "/dev/sdc" successfully created

Criar VG

[root@localhost ~]# vgcreate VGBKP /dev/sdc
Volume group "VGBKP" successfully created

1 2	[root@localhost ~]# vgcreate VGBKP /dev/sdc Volume group "VGBKP" successfully created

Criar LV

[root@localhost ~]# lvcreate -l +100%FREE -n LVBKP VGBKP
Logical volume "LVBKP" created.

1 2	[root@localhost ~]# lvcreate -l +100%FREE -n LVBKP VGBKP Logical volume "LVBKP" created.

Formatar xfs

[root@localhost ~]# mkfs.xfs /dev/VGBKP/LVBKP
meta-data=/dev/VGBKP/LVBKP       isize=256    agcount=4, agsize=3276544 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=0        finobt=0
data     =                       bsize=4096   blocks=13106176, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=0
log      =internal log           bsize=4096   blocks=6399, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

[root@localhost ~]# mkfs.xfs /dev/VGBKP/LVBKP

meta-data=/dev/VGBKP/LVBKP isize=256 agcount=4, agsize=3276544 blks

= sectsz=512 attr=2, projid32bit=1

= crc=0 finobt=0

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

= sunit=0 swidth=0 blks

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

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

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

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

Criar diretório BKP

[root@localhost ~]# mkdir -pv /BKP
mkdir: created directory ‘/BKP’

1 2	[root@localhost ~]# mkdir -pv /BKP mkdir: created directory ‘/BKP’

Adicionar ponto de montagem no fstab

[root@localhost ~]# vim /etc/fstab

/dev/VGBKP/LVBKP      /BKP     xfs     defaults 0 0

[root@localhost ~]# vim /etc/fstab

/dev/VGBKP/LVBKP /BKP xfs defaults 0 0

Montando partição

[root@localhost ~]# mount -av
/                        : ignored
/boot                    : already mounted
swap                     : ignored
/u01                     : already mounted
mount: /BKP does not contain SELinux labels.
       You just mounted an file system that supports labels which does not
       contain labels, onto an SELinux box. It is likely that confined
       applications will generate AVC messages and not be allowed access to
       this file system.  For more details see restorecon(8) and mount(8).
/BKP                     : successfully mounted

[root@localhost ~]# mount -av

/ : ignored

/boot : already mounted

swap : ignored

/u01 : already mounted

mount: /BKP does not contain SELinux labels.

You just mounted an file system that supports labels which does not

contain labels, onto an SELinux box. It is likely that confined

applications will generate AVC messages and not be allowed access to

this file system. For more details see restorecon(8) and mount(8).

/BKP : successfully mounted

Visualizando a partição /BKP montada

[root@localhost ~]# df -h /BKP
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/VGBKP-LVBKP   50G   33M   50G   1% /BKP

[root@localhost ~]# df -h /BKP

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/VGBKP-LVBKP 50G 33M 50G 1% /BKP

ORA-02374: conversion error loading table

Publicado em 12 de abril de 2018 by rodrigosilvaesilva

Objetivo O erro ORA-02374 ocorre quando você tenta impontar um schema e o parametro de NLS_CHARACTERSET está diferente do banco de dados de Origem. Resolução Verificando NLS_CHARACTERSET no banco de Origem

SQL> select VALUE
     from nls_database_parameters
     where parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
WE8ISO8859P1

SQL> select VALUE

from nls_database_parameters

where parameter='NLS_CHARACTERSET';

VALUE

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

WE8ISO8859P1

Alterando NLS_CHARACTERSET no banco de dados destino (12.2.0.1)

SQL> shutdown immediate;
SQL> startup restrict;
SQL> alter database character set INTERNAL_USE WE8MSWIN1252;
SQL> shutdown immediate;
SQL> startup;

SQL> shutdown immediate;

SQL> startup restrict;

SQL> alter database character set INTERNAL_USE WE8MSWIN1252;

SQL> shutdown immediate;

SQL> startup;

← Posts recentes Posts antigos →