Alterando um datafile para autoextend on

Modelo
alter database tempfile '/path/datafile_name' autoextend on next valueM maxsize sizeM'

onde,
 path: Caminho do datafile
 datafile_name: Nome do datafile
 value: Valor que será acrecido quando um datafile precisa de espaço
 size: Tamanho máximo de crescimento


Exemplo
alter database tempfile '/oracle/TST/sapdata1/temp_1/temp.data1' autoextend on next 10M maxsize 10000M'

Mais informações

ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP

Problema

BR0301E SQL error -1652 in thread 3 at location tab_onl_reorg-26, SQL statement:

'CREATE UNIQUE INDEX "SAPR3"."MLCRP~0#$" ON "SAPR3"."MLCRP#$" ("MANDT", "BELNR", "KJAHR", "POSNR", "BDATJ", "POPER", "CURTP")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "PSAPBTABI" '

ORA-01652: unable to extend temp segment by 128 in tablespace PSAPTEMP

Solução

- Faça um extend na PSAPTEMP e altere os datafiles para auto extend.

Mais informações

Exibindo espaço usado por datafile

http://learndbas.blogspot.com.br/2009/01/show-used-sapce-free-space-per-datafile.html

Mais informações

ORA-20002: Version of statistics table is too old

Problema 

BR0280I BRSPACE thread 2: time stamp: 2013-11-21 12.12.07

BR0301W SQL error -20002 in thread 2 at location tab_onl_reorg-12, SQL statement:

'BEGIN DBMS_STATS.EXPORT_TABLE_STATS (OWNNAME => '"SAPR3"', TABNAME => '"WWWDATA"', STATTAB => '"STATTAB"', STATID => '"20131121114343-81481"', CASCADE => TRUE, STATOWN => '"SYSTEM"'); END;'

ORA-20002: Version of statistics table SYSTEM.STATTAB is too old.  Please try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11229

ORA-06512: at "SYS.DBMS_STATS", line 12429

ORA-06512: at line 1

BR0280I BRSPACE thread 1: time stamp: 2013-11-21 12.12.11

Solução

- No sqlplus, execute:

exec DBMS_STATS.UPGRADE_STAT_TABLE('SYSTEM','STATTAB');

Mais informações

No Server Parameter File (SPFILE) created

Problema
**********************************************************************
SAP-Specific Pre-Upgrade Warnings
**********************************************************************
WARNING: ----> No Server Parameter File (SPFILE) created.
               Recommendation: Configure a Server Parameter File.
               See SAP note 601157.

Solução
601157 - Oracle9i: Server Parameter File

Mais informações

Reorganization of tables with BRSPACE

Reorganizing Tables with BR*Tools

646681 - Reorganization of tables with BRSPACE

Mais informações

Creating OPS$ users on Unix

361641 - Creating OPS$ users on UNIX

Mais informações

R3trans - Error 2EETW169 - Listener

Problema

maqtst:tstadm 1> R3trans -x

This is R3trans version 6.24 (release 720 - 14.03.13 - 20:13:02 ).

sizeof(Selection_t) = 336

2EETW169 no connect possible: "DBMS = ORACLE                           --- dbs_ora_tnsname = 'TST'"

R3trans finished (0012).

Analise

maqtst:oratst 2> lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 14-NOV-2013 08:29:27

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=TST.WORLD))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production

Start Date                08-NOV-2013 13:25:47

Uptime                    5 days 19 hr. 3 min. 40 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /oracle/TST/102_64/network/admin/listener.ora

Listener Log File         /oracle/TST/102_64/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TST.WORLD)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TST)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=maqtst)(PORT=1527)))

Services Summary...

Service "TST" has 1 instance(s).

  Instance "TST", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Solução

- Ajuste as variaveis de ambiente para o diretorio da nova versão.

- Execute a opção stop do listener.

- Execute a opção start do listener e observe a alteração

maqtst:oratst 4> lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 14-NOV-2013 08:30:15

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /oracle/TST/112_64/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production

System parameter file is /oracle/TST/112_64/network/admin/listener.ora

Log messages written to /oracle/diag/tnslsnr/maqtst/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TST.WORLD)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TST)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=maqtst)(PORT=1527)))

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=TST.WORLD))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production

Start Date                14-NOV-2013 08:30:19

Uptime                    0 days 0 hr. 0 min. 3 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /oracle/TST/112_64/network/admin/listener.ora

Listener Log File         /oracle/diag/tnslsnr/maqtst/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TST.WORLD)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=TST)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=maqtst)(PORT=1527)))

Services Summary...

Service "TST" has 1 instance(s).

  Instance "TST", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Mais informações

BRCONNECT - A file or directory in the path name does not exist

Problema
maqtst:oratst 3> brconnect -u system/zebra*01 -c -f stats -t oradict_stats
system/: A file or directory in the path name does not exist.

Solução
- Execute o comando com o usuário e a senha entre aspas duplas.

maqtst:oratst 3> brconnect -u "system/zebra*01" -c -f stats -t oradict_stats

Abraços

Mais informações

PSAPTEMP is empty

Problema 1:

ORA-12012: error on auto execute of job 8936

ORA-25153: Temporary Tablespace is Empty

Problema 2:

- No alert.log.

WARNING: The following temporary tablespaces contain no files.

This condition can occur when a backup controlfile has

been restored.  It may be necessary to add files to these

tablespaces.  That can be done using the SQL statement:

Referencia

Note 600513 - ORA-25153 after recovery due to missing tempfiles

Solução

- Rode o comando find / -name file para localizar os datafiles referentes a esta tablespace.

Ex:

maqtst@root:/:#find / -name temp.*

/oracle/TST/sapdata1/temp_1/temp.data1

maqtst@root:/:#

- No sqlplus rodar o comando abaixo para cada datafile localizado.

Comando: alter tablespace PSAPTEMP add tempfile '<filename>' reuse;

Ex:

SYS@TST>alter tablespace PSAPTEMP add tempfile '/oracle/TST/sapdata1/temp_1/temp.data1' reuse;

Tablespace altered.

SYS@TST>

- Executar novamente o job na db13 e verificar na db02.

Abraços

Mais informações