Migrar datafiles de Local 11G – …::: César DBA :::…

1 – Verificar se estamos conectado no database correto:

set colsep   " | "
SET LINESIZE  145
SET PAGESIZE  9999
alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
SELECT HOST_NAME,INSTANCE_NAME,STATUS,VERSION FROM GV$INSTANCE; 
 
HOST_NAME                                                        | INSTANCE_NAME    | STATUS       | VERSION
---------------------------------------------------------------- | ---------------- | ------------ | -----------------
lab11g.localdomain

SQL

2 – Local dos controlfiles:

set linesize 3000 
SET LINESIZE 3000 
col NAME format a70 
select name from gv$controlfile;
show parameter control_files
 
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g_novo/control01.ctl
/u01/app/oracle/oradata/ora11g_novo/control02.ctl
 
SQL> show parameter control_files
 
NAME                                 | TYPE        | VALUE
------------------------------------ | ----------- | ------------------------------
control_files                        | string      | /u01/app/oracle/oradata/ora11g_novo/control01.ctl,
                                     |             | /u01/app/oracle/oradata/ora11g_novo/control02.ctl

SQL

3 – Local dos redologs:

SET LINESIZE 3000
col file_name format a70
select Member  from gv$logfile;
 
MEMBER
-------------------------------------------------
/u01/app/oracle/oradata/ora11g_novo/redo03.log
/u01/app/oracle/oradata/ora11g_novo/redo02.log
/u01/app/oracle/oradata/ora11g_novo/redo01.log

SQL

4 – Local dos datafiles:

set linesize 3000 
SET LINESIZE 3000 
col NAME format a70 
select file#,NAME from gv$datafile order by file#; 
 
     FILE# | NAME
---------- | ----------------------------------------------------------------------
         1 | /u01/app/oracle/oradata/ora11g_novo/system01.dbf
         2 | /u01/app/oracle/oradata/ora11g_novo/sysaux01.dbf
         3 | /u01/app/oracle/oradata/ora11g_novo/undotbs01.dbf
         4 | /u01/app/oracle/oradata/ora11g_novo/users01.dbf
         5 | /u01/app/oracle/oradata/ora11g_novo/TASY_DATA01.dbf
         6 | /u01/app/oracle/oradata/ora11g_novo/TASY_DATA02.dbf
         7 | /u01/app/oracle/oradata/ora11g_novo/DBA_DADOS01.dbf
         8 | /u01/app/oracle/oradata/ora11g_novo/tbs_auditoria.dbf
         9 | /u01/app/oracle/oradata/ora11g_novo/cm_dados1.dbf
 
9 rows selected.

SQL

5 – Arquivos temporários:

SET LINESIZE 3000
col file_name format a70
select tablespace_name,file_name from dba_temp_files;
SELECT v.file#, t.file_name, v.status from dba_temp_files t, gv$tempfile v WHERE t.file_id = v.file#;
 
 
TABLESPACE_NAME                | FILE_NAME
------------------------------ | ----------------------------------------------------------------------
TEMP                           | /u01/app/oracle/oradata/ora11g/temp01.dbf
 
SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, gv$tempfile v WHERE t.file_id = v.file#;
 
     FILE# | FILE_NAME                                                              | STATUS
---------- | ---------------------------------------------------------------------- | -------
         1 | /u01/app/oracle/oradata/ora11g/temp01.dbf                              | ONLINE

SQL

6 – Gerar os sqls para serem usados após a copia dos datafiles, ajuste o script de acordo com o seu ambiente.

6.1 – Datafiles:

set long 90000
set linesize 32000
set trimspool on
set heading off
set tab off
set feedback off
SET echo off
set pagesize 0
--select substr(name,16) from gv$datafile d order by d.FILE#;
select 'alter database rename file '||''''||name||''''||'  to  '||''''||'/u02'||substr(name,16)||''''||';' from gv$datafile d order by d.FILE#;
 
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/system01.dbf'  to  '/u02/oradata/ora11g_novo/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/sysaux01.dbf'  to  '/u02/oradata/ora11g_novo/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/undotbs01.dbf'  to  '/u02/oradata/ora11g_novo/undotbs01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/users01.dbf'  to  '/u02/oradata/ora11g_novo/users01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/TASY_DATA01.dbf'  to  '/u02/oradata/ora11g_novo/TASY_DATA01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/TASY_DATA02.dbf'  to  '/u02/oradata/ora11g_novo/TASY_DATA02.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/DBA_DADOS01.dbf'  to  '/u02/oradata/ora11g_novo/DBA_DADOS01.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/tbs_auditoria.dbf'  to  '/u02/oradata/ora11g_novo/tbs_auditoria.dbf';
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/cm_dados1.dbf'  to  '/u02/oradata/ora11g_novo/cm_dados1.dbf';

SQL

6.2 – Redologs:

set long 90000
set linesize 32000
set trimspool on
set heading off
set tab off
set feedback off
SET echo off
set pagesize 0
select 'alter database rename file '||''''||Member||''''||'  to  '||''''||'/u02'||substr(Member,16)||''''||';' from v$logfile d;
 
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/redo03.log'  to  '/u02/oradata/ora11g_novo/redo03.log';
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/redo02.log'  to  '/u02/oradata/ora11g_novo/redo02.log';
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/redo01.log'  to  '/u02/oradata/ora11g_novo/redo01.log';

SQL

6.3 – Temp:

set long 90000
set linesize 32000
set trimspool on
set heading off
set tab off
set feedback off
SET echo off
set pagesize 0
select 'alter database tempfile '||''''||file_name||''''||' offline;' from dba_temp_files
union all
select 'alter database rename file '||''''||file_name||''''||'  to  '||''''||'/u02'||substr(file_name,16)||''''||';' from dba_temp_files
union all
select 'alter database tempfile '||''''||file_name||''''||' online;' from dba_temp_files;
 
 
alter database tempfile '/u01/app/oracle/oradata/ora11g_novo//temp01.dbf' offline;
alter database rename file '/u01/app/oracle/oradata/ora11g_novo/temp01.dbf'  to  '/u02/oradata/ora11g_novo/temp01.dbf';
alter database tempfile '/u02/oradata/ora11g_novo/temp01.dbf' online;

SQL

7 – Backup controlfiles:

SQL > alter database backup controlfile to '/u02/backup/controlfile/bkpctl-antes.ctl';
SQL > !ls -lat /u02/backup/controlfile/bkpctl-antes.ctl
 
SQL> alter database backup controlfile to '/u02/backup/controlfile/bkpctl-antes.ctl';
 
Database altered.
 
SQL> !ls -lat /u02/backup/controlfile/bkpctl-antes.ctl
-rw-r----- 1 oracle oinstall 9977856 Nov 27 12:26 /u02/backup/controlfile/bkpctl-antes.ctl

SQL

8 – Backup spfile:

SQL> create pfile = '/u02/backup/controlfile/initora11g.ctl' from spfile;
 
File created.
 
SQL> !ls -lat /u02/backup/controlfile/initora11g.ctl
-rw-r--r-- 1 oracle oinstall 1312 Nov 27 12:41 /u02/backup/controlfile/initora11g.ctl
 
SQL > shut immediate

SQL

9 – Copiar os datafiles via rsync para manter as permissões.

[oracle@lab11g ora11g]$ mkdir -p /u02/oradata/ora11g_novo/
[oracle@lab11g ora11g]$ chmod 775 /u02/oradata/ora11g_novo/
[oracle@lab11g ora11g]$ chown -R oracle:oinstall
 
#Controlfile
[oracle@lab11g ora11g]$ cp /u01/app/oracle/oradata/ora11g_novo/control01.ctl /u02/oradata/ora11g_novo/
[oracle@lab11g ora11g]$ cp /u01/app/oracle/oradata/ora11g_novo/control02.ctl /u02/oradata/ora11g_novo/
 
[oracle@lab11g ora11g]$ ls -lat /u02/oradata/ora11g_novo/*.ctl*
-rw-r----- 1 oracle oinstall 9977856 Nov 27 12:42 /u02/oradata/ora11g_novo/control02.ctl
-rw-r----- 1 oracle oinstall 9977856 Nov 27 12:42 /u02/oradata/ora11g_novo/control01.ctl
 
#spfile
cp /u01/app/oracle/product/11.2.0.4/db_1/dbs/initora11g.ora /u01/app/oracle/product/11.2.0.4/db_1/dbs/initora11g.ora_bkp_27112021
 
ls -lat /u01/app/oracle/product/11.2.0.4/db_1/dbs/initora11g.ora_bkp_27112021
-rw-r--r-- 1 oracle oinstall 1052 Nov 27 12:44 /u01/app/oracle/product/11.2.0.4/db_1/dbs/initora11g.ora_bkp_27112021
 
#Datafiles
rsync -Pav /u01/app/oracle/oradata/ora11g_novo/ /u02/oradata/ora11g_novo/
 
sending incremental file list
./
DBA_DADOS01.dbf
    104,865,792 100%   38.68MB/s    0:00:02 (xfr#1, to-chk=14/16)
TASY_DATA01.dbf
     10,493,952 100%   15.76MB/s    0:00:00 (xfr#2, to-chk=13/16)
TASY_DATA02.dbf
     10,493,952 100%   12.62MB/s    0:00:00 (xfr#3, to-chk=12/16)
cm_dados1.dbf
    134,225,920 100%   57.53MB/s    0:00:02 (xfr#4, to-chk=11/16)
control01.ctl
      9,977,856 100%   28.84MB/s    0:00:00 (xfr#5, to-chk=10/16)
control02.ctl
      9,977,856 100%   19.46MB/s    0:00:00 (xfr#6, to-chk=9/16)
redo01.log
     52,429,312 100%   16.87MB/s    0:00:02 (xfr#7, to-chk=8/16)
redo02.log
     52,429,312 100%   71.12MB/s    0:00:00 (xfr#8, to-chk=7/16)
redo03.log
     52,429,312 100%   39.12MB/s    0:00:01 (xfr#9, to-chk=6/16)
sysaux01.dbf
    692,068,352 100%   44.00MB/s    0:00:15 (xfr#10, to-chk=5/16)
system01.dbf
    817,897,472 100%   54.44MB/s    0:00:14 (xfr#11, to-chk=4/16)
tbs_auditoria.dbf
    104,865,792 100%   13.92MB/s    0:00:07 (xfr#12, to-chk=3/16)
temp01.dbf
     35,659,776 100%   59.77MB/s    0:00:00 (xfr#13, to-chk=2/16)
undotbs01.dbf
    408,952,832 100%   30.21MB/s    0:00:12 (xfr#14, to-chk=1/16)
users01.dbf
     56,369,152 100%   13.98MB/s    0:00:03 (xfr#15, to-chk=0/16)
 
sent 2,553,761,015 bytes  received 304 bytes  42,210,930.89 bytes/sec
total size is 2,553,136,640  speedup is 1.00

ShellScript

10 – Mudar local dos controlfiles.

SQL> startup nomount
SQL> alter system set control_files='/u02/oradata/ora11g_novo/control01.ctl','/u02/oradata/ora11g_novo/control02.ctl' scope=SPFILE;
SQL> shutdown immediate
SQL> startup mount

SQL

11 – Verifique se os controlfiles estão no novo diretorio.

set linesize 3000 
SET LINESIZE 3000 
col NAME format a70 
show parameter control_files
NAME
----------------------------------------------------------------------
/u02/oradata/ora11g_novo/control01.ctl
/u02/oradata/ora11g_novo/control02.ctl

SQL

12 – Renomear os datafiles para o novo local.

12.1 – Redolog:

SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/redo03.log'  to  '/u02/oradata/ora11g_novo/redo03.log';
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/redo02.log'  to  '/u02/oradata/ora11g_novo/redo02.log';
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/redo01.log'  to  '/u02/oradata/ora11g_novo/redo01.log';
 
SET LINESIZE 3000
col file_name format a70
select Member  from gv$logfile;
 
MEMBER
-------------------------------------
/u02/oradata/ora11g_novo/redo03.log
/u02/oradata/ora11g_novo/redo02.log
/u02/oradata/ora11g_novo/redo01.log

SQL

12.2 – Datafiles

SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/system01.dbf'  to  '/u02/oradata/ora11g_novo/system01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/sysaux01.dbf'  to  '/u02/oradata/ora11g_novo/sysaux01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/undotbs01.dbf'  to  '/u02/oradata/ora11g_novo/undotbs01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/users01.dbf'  to  '/u02/oradata/ora11g_novo/users01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/TASY_DATA01.dbf'  to  '/u02/oradata/ora11g_novo/TASY_DATA01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/TASY_DATA02.dbf'  to  '/u02/oradata/ora11g_novo/TASY_DATA02.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/DBA_DADOS01.dbf'  to  '/u02/oradata/ora11g_novo/DBA_DADOS01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/tbs_auditoria.dbf'  to  '/u02/oradata/ora11g_novo/tbs_auditoria.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/cm_dados1.dbf'  to  '/u02/oradata/ora11g_novo/cm_dados1.dbf';
 
set linesize 3000 
SET LINESIZE 3000 
col NAME format a70 
select file#,NAME from gv$datafile order by file#; 
 
     FILE# NAME
---------- ----------------------------------------------------------------------
         1 /u02/oradata/ora11g_novo/system01.dbf
         2 /u02/oradata/ora11g_novo/sysaux01.dbf
         3 /u02/oradata/ora11g_novo/undotbs01.dbf
         4 /u02/oradata/ora11g_novo/users01.dbf
         5 /u02/oradata/ora11g_novo/TASY_DATA01.dbf
         6 /u02/oradata/ora11g_novo/TASY_DATA02.dbf
         7 /u02/oradata/ora11g_novo/DBA_DADOS01.dbf
         8 /u02/oradata/ora11g_novo/tbs_auditoria.dbf
         9 /u02/oradata/ora11g_novo/cm_dados1.dbf
 
9 rows selected.

SQL

13 – Finalizamos abrindo o banco e acompanhando os logs por um período de tempo, recomendo realizar um valide se possível.

SQL> alter database open;

SQL

14 – Check alertlog:

[oracle@lab11g ora11g]$ tail -f /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/alert_ora11g.log

ShellScript

15 – Renomear os datafiles temp.

SQL> alter database tempfile '/u01/app/oracle/oradata/ora11g_novo//temp01.dbf' offline;
SQL> alter database rename file '/u01/app/oracle/oradata/ora11g_novo/temp01.dbf'  to  '/u02/oradata/ora11g_novo/temp01.dbf';
SQL> alter database tempfile '/u02/oradata/ora11g_novo/temp01.dbf' online;
 
SET LINESIZE 3000
col file_name format a70
select tablespace_name,file_name from dba_temp_files;
SELECT v.file#, t.file_name, v.status from dba_temp_files t, gv$tempfile v WHERE t.file_id = v.file#;
 
 
TABLESPACE_NAME                FILE_NAME
------------------------------ ----------------------------------------------------------------------
TEMP                           /u02/oradata/ora11g_novo/temp01.dbf
 
SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, gv$tempfile v WHERE t.file_id = v.file#;
 
     FILE# FILE_NAME                                                              STATUS
---------- ---------------------------------------------------------------------- -------
         1 /u02/oradata/ora11g_novo/temp01.dbf

SQL

16 – Default location datafile:

alter system set db_create_file_dest='/u02/oradata/ora11g_novo' scope=both;

SQL

17 – Validar a integridade do banco de dados.

[oracle@lab11g scripts]$ cat validar_database.sh
#!/bin/bash
ORACLE_HOSTNAME=lab11g; export ORACLE_HOSTNAME
ORACLE_UNQNAME=ora11g; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ora11g; export ORACLE_SID
ORACLE_PATH=/home/oracle/Gerais; export ORACLE_PATH
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
 
export HOJE=`date +%Y%m%d`
export LOGS=/home/oracle/scripts/logs
 
 
# VALIDAR DATABASE
rman target / nocatalog msglog $LOGS/validate_rman_$HOJE.spool << EOF
RUN
{
VALIDATE CHECK LOGICAL DATABASE;
}
exit
EOF

SQL

18 – Executar script de validate.

[oracle@lab11g scripts]$ nohup ./validar_database.sh &

ShellScript

Leave a Reply Cancel reply