Hoje vamos falar de um assunto que é uma atividade bastante comum no dia-a-dia de um DBA: a movimentação de datafiles.
Para explorar essa atividade vamos abordar as várias técnicas que podemos utilizar para este fim. Vamos ver desde a movimentação de datafiles usando comandos do sistema operacional até comandos exclusivos do Oracle englobando tanto movimentação offline quanto online e do Filesystem para o ASM e vice-versa.
Sem mais delongas…
Movimentação de datafiles offline com SQL*Plus e asmcmd
Vamos inicialmente ver o tamanho, os nomes e diretórios dos nossos datafiles. Precisamos avaliar também se o destino para onde vamos mover os datafiles possui espaço suficiente para comportá-los.
SQL> set pagesize 200 linesize 200 SQL> col file_name for a60 SQL> select tablespace_name, file_name, bytes/1024/1024 MB, status from dba_data_files order by 1,2; TABLESPACE_NAME FILE_NAME MB STATUS ------------------------------ ---------------------------------------------------------------------- ---------- --------- SYSAUX +DATA/LOREDB/DATAFILE/sysaux.256.901303363 630 AVAILABLE SYSTEM +DATA/LOREDB/DATAFILE/system.257.901303423 790 AVAILABLE UNDOTBS1 +DATA/LOREDB/DATAFILE/undotbs1.259.901303479 165 AVAILABLE USERS +DATA/LOREDB/DATAFILE/users.258.901303477 5 AVAILABLE USERS +DATA/LOREDB/DATAFILE/users.274.916489441 100 AVAILABLE
Verificamos também com a query acima que os datafiles possuem o status available, ou seja, eles estão disponíveis e para realizar essa movimentação vamos precisar colocá-los offline. Para colocar somente um datafile de uma tablespace offline é necessário que o banco de dados esteja em modo Archivelog, senão precisamos que toda a tablespace seja colocada offline. Neste cenário vamos trabalhar somente com a tablespace USERS e seus datafiles.
Neste cenário meu banco de dados não está em modo archivelog e não convém aqui habilitá-lo, então não conseguirei colocar qualquer datafile offline.
SQL> alter database datafile '+DATA/LOREDB/DATAFILE/users.274.916489441' offline; alter database datafile '+DATA/LOREDB/DATAFILE/users.274.916489441' offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled
Vamos então colocar a tablespace USERS offline:
SQL> alter tablespace users offline; Tablespace altered.
Vou mover o datafile do ASM para o Filesystem e para isso preciso acessar a instância +ASM:
SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options [oracle@loredata-srv ~]$ . oraenv ORACLE_SID = [loredb] ? +ASM The Oracle base remains unchanged with value /u01/app/oracle [oracle@loredata-srv ~]$ asmcmd -p ASMCMD [+] >
Para navegar pelos diretórios do ASM os comandos são bem similares e alguns até iguais aos do Shell. O comando lsdg lista os diskgroups:
ASMCMD [+] > lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 4096 1048576 4094 4032 0 4032 0 N CRS/ MOUNTED EXTERN N 512 4096 1048576 12284 6715 0 6715 0 N DATA/
Vamos até o diretório onde está o datafile que queremos mover:
ASMCMD [+] > cd +DATA/LOREDB/DATAFILE ASMCMD [+DATA/LOREDB/DATAFILE] > ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE JUL 06 12:00:00 Y SYSAUX.256.901303363 DATAFILE UNPROT COARSE JUL 06 12:00:00 Y SYSTEM.257.901303423 DATAFILE UNPROT COARSE JUL 06 12:00:00 Y UNDOTBS1.259.901303479 DATAFILE UNPROT COARSE JUL 06 12:00:00 Y USERS.258.901303477 DATAFILE UNPROT COARSE JUL 06 12:00:00 Y USERS.274.916489441 ASMCMD [+DATA/LOREDB/DATAFILE] > cp USERS.274.916489441 /u01/app/oracle/oradata copying +DATA/LOREDB/DATAFILE/USERS.274.916489441 -> /u01/app/oracle/oradata/USERS.274.916489441
Voltando ao SQL*Plus vamos alterar a informação de nome do datafile no controlfile:
SQL> alter database rename file '+DATA/LOREDB/DATAFILE/users.274.916489441' to '/u01/app/oracle/oradata/USERS.274.916489441'; Database altered.
Feito isso é só colocar a tablespace novamente online. Caso você não esteja usando o Oracle Managed Files terá que remover o arquivo do local antigo.
SQL> alter tablespace users online; Tablespace altered.
Vamos ver como ficou:
SQL> col file_name for a60 SQL> select tablespace_name, file_name, bytes/1024/1024 MB, status from dba_data_files order by 1,2; TABLESPACE_NAME FILE_NAME MB STATUS ------------------------------ ---------------------------------------------------------------------- ---------- --------- SYSAUX +DATA/LOREDB/DATAFILE/sysaux.256.901303363 630 AVAILABLE SYSTEM +DATA/LOREDB/DATAFILE/system.257.901303423 790 AVAILABLE UNDOTBS1 +DATA/LOREDB/DATAFILE/undotbs1.259.901303479 165 AVAILABLE USERS +DATA/LOREDB/DATAFILE/users.258.901303477 5 AVAILABLE USERS /u01/app/oracle/oradata/USERS.274.916489441 100 AVAILABLE
Movimentação de datafiles offline com RMAN (1)
Vamos movimentar o mesmo arquivo, mas desta vez utilizando somente o Recovery Manager (RMAN)
Vamos inicialmente conectar no banco de dados utilizando o RMAN e listar os arquivos:
[oracle@loredata-srv oradata]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jul 6 12:47:02 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: LOREDB (DBID=1937271711) RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name LOREDB List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 790 SYSTEM YES +DATA/LOREDB/DATAFILE/system.257.901303423 3 630 SYSAUX NO +DATA/LOREDB/DATAFILE/sysaux.256.901303363 4 165 UNDOTBS1 YES +DATA/LOREDB/DATAFILE/undotbs1.259.901303479 5 250 PDB$SEED:SYSTEM NO +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.266.901303619 6 5 USERS NO +DATA/LOREDB/DATAFILE/users.258.901303477 7 490 PDB$SEED:SYSAUX NO +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.265.901303619 8 260 MYPDB:SYSTEM NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.271.901304195 9 1170 MYPDB:SYSAUX NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.270.901304195 10 5 MYPDB:USERS NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.272.901304195 11 1243 MYPDB:EXAMPLE NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.269.901304195 13 100 USERS NO /u01/app/oracle/oradata/USERS.274.916489441 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 60 TEMP 32767 +DATA/LOREDB/TEMPFILE/temp.264.901303609 2 20 PDB$SEED:TEMP 32767 +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012016-01-16_06-08-07-pm.dbf 3 118 MYPDB:TEMP 32767 +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/mypdb_temp012016-01-16_06-19-54-pm.dbf
Vamos colocar a tablespace offline e depois copiar o datafile de volta para o ASM:
RMAN> alter tablespace users offline; Statement processed RMAN> copy datafile 13 to '+DATA'; Starting backup at 06-JUL-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=34 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00013 name=/u01/app/oracle/oradata/USERS.274.916489441 output file name=+DATA/LOREDB/DATAFILE/users.274.916491023 tag=TAG20160706T125022 RECID=7 STAMP=916491028 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 06-JUL-16 Starting Control File and SPFILE Autobackup at 06-JUL-16 piece handle=/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/c-1937271711-20160706-00 comment=NONE Finished Control File and SPFILE Autobackup at 06-JUL-16
O RMAN faz um image copy do arquivo e então precisamos fazer o switch para que o banco de dados passe a utilizar esta cópia do datafile:
RMAN> switch datafile 13 to copy; datafile 13 switched to datafile copy "+DATA/LOREDB/DATAFILE/users.274.916491023"
Agora é só colocar novamente a tablespace online e remover o arquivo antigo:
RMAN> alter tablespace users online; Statement processed RMAN> delete datafilecopy "/u01/app/oracle/oradata/USERS.274.916489441"; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=34 device type=DISK List of Datafile Copies ======================= Key File S Completion Time Cup SCN Ckp Time ------- ---- - --------------- ---------- --------------- 8 13 A 06-JUL-16 3627450 06-JUL-16 Name: /u01/app/oracle/oradata/USERS.274.916489441 Do you really want to delete the above objects (enter YES or NO)? yes deleted datafile copy datafile copy file name=/u01/app/oracle/oradata/USERS.274.916489441 RECID=8 STAMP=916491059 Deleted 1 objects
Vamos ver como ficou:
RMAN> report schema; Report of database schema for database with db_unique_name LOREDB List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 790 SYSTEM YES +DATA/LOREDB/DATAFILE/system.257.901303423 3 630 SYSAUX NO +DATA/LOREDB/DATAFILE/sysaux.256.901303363 4 165 UNDOTBS1 YES +DATA/LOREDB/DATAFILE/undotbs1.259.901303479 5 250 PDB$SEED:SYSTEM NO +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.266.901303619 6 5 USERS NO +DATA/LOREDB/DATAFILE/users.258.901303477 7 490 PDB$SEED:SYSAUX NO +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.265.901303619 8 260 MYPDB:SYSTEM NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.271.901304195 9 1170 MYPDB:SYSAUX NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.270.901304195 10 5 MYPDB:USERS NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.272.901304195 11 1243 MYPDB:EXAMPLE NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.269.901304195 13 100 USERS NO +DATA/LOREDB/DATAFILE/users.274.916491023 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 60 TEMP 32767 +DATA/LOREDB/TEMPFILE/temp.264.901303609 2 20 PDB$SEED:TEMP 32767 +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012016-01-16_06-08-07-pm.dbf 3 118 MYPDB:TEMP 32767 +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/mypdb_temp012016-01-16_06-19-54-pm.dbf
Movimentação de datafiles offline com RMAN (2)
Mais um exemplo bem similar ao anterior, porém com comandos diferentes.
RMAN> report schema; Report of database schema for database with db_unique_name LOREDB List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 790 SYSTEM YES +DATA/LOREDB/DATAFILE/system.257.901303423 3 630 SYSAUX NO +DATA/LOREDB/DATAFILE/sysaux.256.901303363 4 165 UNDOTBS1 YES +DATA/LOREDB/DATAFILE/undotbs1.259.901303479 5 250 PDB$SEED:SYSTEM NO +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.266.901303619 6 5 USERS NO +DATA/LOREDB/DATAFILE/users.258.901303477 7 490 PDB$SEED:SYSAUX NO +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.265.901303619 8 260 MYPDB:SYSTEM NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.271.901304195 9 1170 MYPDB:SYSAUX NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.270.901304195 10 5 MYPDB:USERS NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.272.901304195 11 1243 MYPDB:EXAMPLE NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.269.901304195 13 100 USERS NO +DATA/LOREDB/DATAFILE/users.274.916491023 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 60 TEMP 32767 +DATA/LOREDB/TEMPFILE/temp.264.901303609 2 20 PDB$SEED:TEMP 32767 +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012016-01-16_06-08-07-pm.dbf 3 118 MYPDB:TEMP 32767 +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/mypdb_temp012016-01-16_06-19-54-pm.dbf RMAN> alter tablespace users offline; Statement processed RMAN> backup as copy datafile 13 format '/u01/app/oracle/oradata/users.274.916491023'; Starting backup at 06-JUL-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00013 name=+DATA/LOREDB/DATAFILE/users.274.916491023 output file name=/u01/app/oracle/oradata/users.274.916491023 tag=TAG20160706T130604 RECID=9 STAMP=916491970 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 06-JUL-16 Starting Control File and SPFILE Autobackup at 06-JUL-16 piece handle=/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/c-1937271711-20160706-01 comment=NONE Finished Control File and SPFILE Autobackup at 06-JUL-16 RMAN> switch datafile 13 to copy; datafile 13 switched to datafile copy "/u01/app/oracle/oradata/users.274.916491023" RMAN> alter tablespace users online; Statement processed RMAN> delete datafilecopy "+DATA/LOREDB/DATAFILE/users.274.916491023"; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=34 device type=DISK List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - --------------- ---------- --------------- 10 13 A 06-JUL-16 3628718 06-JUL-16 Name: +DATA/LOREDB/DATAFILE/users.274.916491023 Tag: TAG20160706T125022 Do you really want to delete the above objects (enter YES or NO)? yes deleted datafile copy datafile copy file name=+DATA/LOREDB/DATAFILE/users.274.916491023 RECID=10 STAMP=916492017 Deleted 1 objects RMAN> report schema; Report of database schema for database with db_unique_name LOREDB List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 790 SYSTEM YES +DATA/LOREDB/DATAFILE/system.257.901303423 3 630 SYSAUX NO +DATA/LOREDB/DATAFILE/sysaux.256.901303363 4 165 UNDOTBS1 YES +DATA/LOREDB/DATAFILE/undotbs1.259.901303479 5 250 PDB$SEED:SYSTEM NO +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.266.901303619 6 5 USERS NO +DATA/LOREDB/DATAFILE/users.258.901303477 7 490 PDB$SEED:SYSAUX NO +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.265.901303619 8 260 MYPDB:SYSTEM NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/system.271.901304195 9 1170 MYPDB:SYSAUX NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/sysaux.270.901304195 10 5 MYPDB:USERS NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/users.272.901304195 11 1243 MYPDB:EXAMPLE NO +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/example.269.901304195 13 100 USERS NO /u01/app/oracle/oradata/users.274.916491023 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 60 TEMP 32767 +DATA/LOREDB/TEMPFILE/temp.264.901303609 2 20 PDB$SEED:TEMP 32767 +DATA/LOREDB/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/pdbseed_temp012016-01-16_06-08-07-pm.dbf 3 118 MYPDB:TEMP 32767 +DATA/LOREDB/FD9BD2B44413096FE043B6A9E80ABC28/DATAFILE/mypdb_temp012016-01-16_06-19-54-pm.dbf
OBS: Se o seu banco de dados estiver em modo archivelog e você optar por colocar somente os datafiles offline ao invés da tablespace será necessário efetuar um recover da tablespace após o switch ou rename e antes de colocá-la online.
Movimentação de datafiles online com SQL*Plus (12c new feature)
Para encerrar vamos ver um dos novos recursos do Oracle Database 12c para Enterprise Edition.
Basta um comando para movermos um datafile de um lugar para outro:
SQL> alter database move datafile 13 to '+DATA'; Database altered. SQL> col file_name for a60 SQL> set pages 200 lin 200 SQL> select tablespace_name, file_name, bytes/1024/1024 MB, status from dba_data_files order by 1,2; TABLESPACE_NAME FILE_NAME MB STATUS ------------------------------ ------------------------------------------------------------ ---------- --------- SYSAUX +DATA/LOREDB/DATAFILE/sysaux.256.901303363 630 AVAILABLE SYSTEM +DATA/LOREDB/DATAFILE/system.257.901303423 790 AVAILABLE UNDOTBS1 +DATA/LOREDB/DATAFILE/undotbs1.259.901303479 165 AVAILABLE USERS +DATA/LOREDB/DATAFILE/users.258.901303477 5 AVAILABLE USERS +DATA/LOREDB/DATAFILE/users.274.916492215 100 AVAILABLE
Se listarmos o datafile com o nome antigo podemos ver que ele não existe mais:
SQL> !ls -lh /u01/app/oracle/oradata/users.274.916491023 ls: impossível acessar /u01/app/oracle/oradata/users.274.916491023: Arquivo ou diretório não encontrado
Por hoje é só. Acho que vai ajudar bastante a galera que está começando na área. A movimentação de datafiles é uma tarefa simples, porém deve ser feita com bastante atenção. Espero que tenham gostado e até mais.
Abraços,
Franky