Convertendo Dictionary Managed Tablespaces (DMT) para Local Managed Tablespaces (LMT)

Posted By: André Luiz Dutra Ontalbaon: 08 janeiro, 2020In: DatabaseNo Comments

Hoje, encontramos um banco de dados muito antigo usando alocação de espaços de tabela muito antiga. Em outra vida, quando comecei com o banco de dados Oracle e costumávamos chamar MOSC como Metalink (@ludodba), havia uma maneira de gerenciar as extensões no banco de dados denominadas DMT (DICTIONARY MANAGED TABLESPACES), devido principalmente a problemas de desempenho, a Oracle decidiu altere esse gerenciamento das extensões para residir no dicionário de dados (SYSTEM tbs), para ser armazenado em bitmaps no espaço de tabela local, por isso eles o chamaram LOCAL MANAGED TABLESPACE (LMT).

Esse recurso foi introduzido no Oracle 9i em 2001, quase 20 anos atrás, e ainda vemos coisas antigas por aí.

As etapas que executaremos são muito rápidas e simples, mas, como estamos mudando algumas coisas principais no banco de dados, certifique-se de ter um backup completo e confiável antes.

Aqui está uma nota importante, um pouco confusa, leia-a antes de prosseguir.

Continuando, após converter SYSTEM, você não poderá mais colocar o DMT no modo de leitura e gravação.

*** NOTA IMPORTANTE ***
Nota: Depois que o espaço de tabela SYSTEM for migrado para gerenciado localmente, nenhum espaço de tabela gerenciado por dicionário no banco de dados poderá ser lido / gravado.

Se você deseja usar os espaços de tabela gerenciados por dicionário no modo de leitura / gravação, a Oracle recomenda
que você migre primeiro esses espaços de tabela para gerenciados localmente antes de migrar o espaço de tabela SYSTEM.

***********************************

1) Coloque o DATABASE em restricted session

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SELECT logins FROM v$instance;

LOGINS
---------
RESTRICTED

2) Defina a default temporary tablespace no banco de dados.

SQL> col PROPERTY_NAME form a30
SQL> col PROPERTY_VALUE form a30
SQL> col DESCRIPTION form a50
SQL> select * from database_properties where PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE SYSTEM ID of default temporary tablespace

Obs .: Muito ruim, usando o espaço da system tablespace para armazenar segmentos temporários, alguns DBAs aqui não fizeram sua lição de casa … De fato, seu trabalho.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTBS01;

Database altered.

Obs .: Verifique se o temp é gerenciado pelo dicionário, se sim, recrie-o antes para prosseguir com os outros.

3) Migre todos os outros Tablespaces gerenciados pelo DICIONÁRIO antes de MIGRAR a system.

select tablespace_name, status, extent_management 
from dba_tablespaces 
where extent_management = 'DICTIONARY';

4) Execute a migração

select 'execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('''||tablespace_name||''');' cmd 
from dba_tablespaces where extent_management = 'DICTIONARY'
and tablespace_name <> 'SYSTEM';

CMD
----------------------------------------------------------------------------------------
execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('INDX');
execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TOOLS');
execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TESTTBS1');
execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TESTTBS2');
execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('INDX');

PL/SQL procedure successfully completed.

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TOOLS');

PL/SQL procedure successfully completed.

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TESTTBS1');

PL/SQL procedure successfully completed.

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('TESTTBS2');

PL/SQL procedure successfully completed.

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');

PL/SQL procedure successfully completed.

5) Verifique se todos os Tablespace, exceto SYSTEM, foram migrados para o LMT, após executar a migração do SYSTEM para o LMT, se você deixou algum DMT (espaço de tabela gerenciado por dicionário), não poderá mais colocá-los para leitura e gravação.

select tablespace_name, status, extent_management 
from dba_tablespaces 
where extent_management = 'DICTIONARY'
and tablespace_name <> 'SYSTEM';

no rows selected

6) Coloque todos os TABLESPACE que não sejam UNDO, TEMP e SYSAUX no modo somente leitura.

select 'ALTER TABLESPACE '||tablespace_name||' READ ONLY;' CMD
from dba_tablespaces 
where tablespace_name NOT in ('SYSTEM','SYSAUX') 
and contents not in ('UNDO','TEMPORARY');

CMD
----------------------------------------------------------
ALTER TABLESPACE INDX READ ONLY;
ALTER TABLESPACE TOOLS READ ONLY;
ALTER TABLESPACE TESTTBS1 READ ONLY;
ALTER TABLESPACE TESTTBS2 READ ONLY;
ALTER TABLESPACE USERS READ ONLY;

7) Verifique o status da Tablespaces

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
INDX READ ONLY
TOOLS READ ONLY
TESTTBS1 READ ONLY
TESTTBS2 READ ONLY
USERS READ ONLY
UNDOTBS01 ONLINE
TEMPTBS01 ONLINE
SYSAUX ONLINE

8) Coloque SYSAUX tablespace offline

SQL> alter tablespace SYSAUX offline;

Tablespace altered.

9) Check o status do tablespaces novamente, somente UNDO, TEMP e SYSTEM deve estar online.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
INDX READ ONLY
TOOLS READ ONLY
TESTTBS1 READ ONLY
TESTTBS2 READ ONLY
USERS READ ONLY
UNDOTBS01 ONLINE
TEMPTBS01 ONLINE
SYSAUX OFFLINE

10) Agora podemo migrar a SYSTEM tablespaces

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

PL/SQL procedure successfully completed.

11) Coloque as tablespaces novamente em read write e online mode

select 'ALTER TABLESPACE '||tablespace_name||' READ WRITE;' CMD
from dba_tablespaces 
where tablespace_name NOT in ('SYSTEM','SYSAUX') 
and contents not in ('UNDO','TEMPORARY');

CMD
-----------------------------------------------------------
ALTER TABLESPACE INDX READ WRITE;
ALTER TABLESPACE TOOLS READ WRITE;
ALTER TABLESPACE USR READ WRITE;
ALTER TABLESPACE TESTTBS2 READ WRITE;
ALTER TABLESPACE USERS READ WRITE;

SQL> ALTER TABLESPACE INDX READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE TOOLS READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE USR READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE TESTTBS2 READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE USERS READ WRITE;

Tablespace altered.

12) Coloque a SYSAUX novamente online

SQL> ALTER TABLESPACE SYSAUX ONLINE;

Tablespace altered.

13) Check o status das tablespaces, mais uma vez.

SQL> select tablespace_name, status, extent_management from dba_tablespaces;

TABLESPACE_NAME STATUS EXTENT_MAN
------------------------------ --------- ----------
SYSTEM ONLINE LOCAL
INDX ONLINE LOCAL
TOOLS ONLINE LOCAL
TESTTBS1 ONLINE LOCAL
TESTTBS2 ONLINE LOCAL
USERS ONLINE LOCAL
UNDOTBS01 ONLINE LOCAL
TEMPTBS01 ONLINE LOCAL
SYSAUX ONLINE LOCAL

14) Desabilite o restricted session do banco de dados

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

SELECT logins FROM v$instance;

LOGINS
---------
ALLOWED

Se você não seguir todas as etapas cuidadosamente, poderá encontrar alguns problemas como estes abaixo

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

ERROR at line 1:
ORA-10644: SYSTEM tablespace cannot be default temporary tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, UNDO, TEMP not
found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
TEMP
USERS
IDX
SYSAUX
UNDO

SQL> alter tablespace USERS read only;
SQL> alter tablespace IDX read only;

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

ERROR at line 1:
ORA-10648: Tablespace SYSAUX is not offline
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

Esperamos que isso ajude vocês!!!

Rodrigo Mufalani e Andre Luiz Dutra Ontalba

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

Tags: oracle19c;

André Luiz Dutra Ontalba

DBA ATG Oracle há 27 anos, especialista em Banco de Dados Oracle e SQL SERVER, MySQL, Azure, AWS, Oracle Cloud. Bacharel em Ciências da Computação Pós Graduado pela FIAP em Administração de Banco de dados, MBA em Administração de TI pela FGV a MBA em Master Information Technology pela FIAP e BABSON e Mestrado em Gestão Estratégica em Tecnologias de Informação pela UNIATLANTICO e UNIB . Com sólidos conhecimentos em Banco de Dados e Sistemas operacionais, possui certificações Oracle OCP 10g/11/12g/2019 Oracle Cloud Professional, AWS Professional, Azure Expert e mais 85 outra certificações. Strategic Customer Service Manager at Oracle / Oracle ACE Alumni Member