O comando do RMAN CONVERT DATABASE na verdade é utilizado no banco de dados origem (também há uma técnica para executa-lo no banco de dados destino, mas acho mais fácil na origem) para criar uma cópia dos DATAFILEs, e um script que irá executar a migração na plataforma destino.
Este tipo de migração só é possível para as plataformas listadas na View V$TRANSPORTABLE_PLATFORM do banco de dados origem.
No teste abaixo, executarei a migração de um servidor Linux x86-64 (um RHEL 4) para outro servidor com Solaris 10, também na arquitetura Intel x86-64.
[oracle@GG01 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 7 15:57:05 2012 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SET PAGES 1000 SQL> SET LINES 210 SQL> SELECT PLATFORM_NAME FROM V$DATABASE; PLATFORM_NAME ----------------------------------------------------------------------------------------------------- Linux x86 64-bit SQL> SELECT PLATFORM_NAME FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME; PLATFORM_NAME ----------------------------------------------------------------------------------------------------- AIX-Based Systems (64-bit) Apple Mac OS HP IA Open VMS HP Open VMS HP Tru64 UNIX HP-UX (64-bit) HP-UX IA (64-bit) IBM Power Based Linux IBM zSeries Based Linux Linux IA (32-bit) Linux IA (64-bit) Linux x86 64-bit Microsoft Windows IA (32-bit) Microsoft Windows IA (64-bit) Microsoft Windows x86 64-bit Solaris Operating System (x86) Solaris Operating System (x86-64) Solaris[tm] OE (32-bit) Solaris[tm] OE (64-bit) 19 rows selected.
Ok, sendo possível a migração de acordo com a View, executarei a primeira verificação no banco de dados de origem, com a Package DBMS_TDB (TDB de Transportable DataBase).
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 DB_READY BOOLEAN;
3 BEGIN
4 DB_READY := DBMS_TDB.CHECK_DB('Solaris Operating System (x86-64)',DBMS_TDB.SKIP_READONLY);
5 END;
6 /
Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and retry.
A verificação exibe o erro de que a migração só pode ser feita com o banco de dados aberto e em READ ONLY.
Então em seguida, abro o banco de dados desta forma e re-executo a verificação.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 DB_READY BOOLEAN;
3 BEGIN
4 DB_READY := DBMS_TDB.CHECK_DB('Solaris Operating System (x86-64)',DBMS_TDB.SKIP_READONLY);
5 END;
6 /
PL/SQL procedure successfully completed.
Tudo ok até agora. Mais uma verificação deve ser feita, para sabermos se há alguma External Tables e BFILEs no banco de dados de origem. Estes tipos de objetos terão que ser migradas manualmente, se for necessário.
SQL> DECLARE 2 EXTERNAL BOOLEAN; 3 BEGIN 4 EXTERNAL := DBMS_TDB.CHECK_EXTERNAL; 5 END; 6 / The following external tables exist in the database: SH.SALES_TRANSACTIONS_EXT The following directories exist in the database: SYS.ORACLE_OCM_CONFIG_DIR, SYS.ADMIN_DIR, SYS.WORK_DIR, SYS.DATA_FILE_DIR, SYS.LOG_FILE_DIR, SYS.MEDIA_DIR, SYS.XMLDIR, SYS.SUBDIR, SYS.DATA_PUMP_DIR The following BFILEs exist in the database: PM.PRINT_MEDIA PL/SQL procedure successfully completed. SQL>
Ok, anotadas as External Table que precisaremos recriar, criamos um diretório temporário para armazenar o banco de dados. Este diretório terá o mesmo nome na origem e no destino, para facilitar a migração. O banco de dados origem está em ASM, e o destino não utilizará ASM, mas resolveremos isto logo mais.
[oracle@GG01 ~]$ su - Password: [root@GG01 ~]# mkdir /stage [root@GG01 ~]# chown oracle:dba /stage/ [root@GG01 ~]# chown -R oracle:dba /stage/ [root@GG01 ~]# exit logout
Após criar o diretório, executamos o CONVERT DATABASE no banco de dados origem, que irá criar uma cópia de cada DATAFILE no diretório recém-criado, e o script que depois será executado na plataforma destino.
Nno próprio comando eu utilizo a opção DB_FILE_NAME_CONVERT, para retirar os arquivos do ASM e passar para o diretório temporário.
Veja que durante a execução também são informados os Directories existentes e BFILEs, que teriam que ser migrados manualmente.
[oracle@GG01 ~]$ rman TARGET / Recovery Manager: Release 10.2.0.4.0 - Production on Mon May 7 16:21:03 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: CLIENTE (DBID=1146878630) RMAN> CONVERT DATABASE NEW DATABASE 'CLIENTE' TRANSPORT SCRIPT '/home/oracle/transportscript' to platform 'Solaris Operating System (x86-64)' DB_FILE_NAME_CONVERT '+DADOS/cliente/datafile/' '/stage/'; Starting convert at 07-MAY-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=159 devtype=DISK External table SH.SALES_TRANSACTIONS_EXT found in the database Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database Directory SYS.ADMIN_DIR found in the database Directory SYS.WORK_DIR found in the database Directory SYS.DATA_FILE_DIR found in the database Directory SYS.LOG_FILE_DIR found in the database Directory SYS.MEDIA_DIR found in the database Directory SYS.XMLDIR found in the database Directory SYS.SUBDIR found in the database Directory SYS.DATA_PUMP_DIR found in the database BFILE PM.PRINT_MEDIA found in the database User SYS with SYSDBA and SYSOPER privilege found in password file channel ORA_DISK_1: starting datafile conversion input datafile fno=00001 name=+DADOS/cliente/datafile/system.256.782246323 converted datafile=/home/oracle/CLIENTE/system.256.782246323 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile conversion input datafile fno=00003 name=+DADOS/cliente/datafile/sysaux.257.782246325 converted datafile=/home/oracle/CLIENTE/sysaux.257.782246325 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile conversion input datafile fno=00005 name=+DADOS/cliente/datafile/example.265.782246405 converted datafile=/home/oracle/CLIENTE/example.265.782246405 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile conversion input datafile fno=00006 name=+DADOS/cliente/datafile/users.267.782246635 converted datafile=/home/oracle/CLIENTE/users.267.782246635 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile conversion input datafile fno=00007 name=+DADOS/cliente/datafile/users.269.782246711 converted datafile=/home/oracle/CLIENTE/users.269.782246711 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile conversion input datafile fno=00008 name=+DADOS/cliente/datafile/users.270.782246711 converted datafile=/home/oracle/CLIENTE/users.270.782246711 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile conversion input datafile fno=00009 name=+DADOS/cliente/datafile/users.271.782246713 converted datafile=/home/oracle/CLIENTE/users.271.782246713 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile conversion input datafile fno=00010 name=+DADOS/cliente/datafile/users.272.782246713 converted datafile=/home/oracle/CLIENTE/users.272.782246713 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile conversion input datafile fno=00011 name=+DADOS/cliente/datafile/users.273.782246713 converted datafile=/home/oracle/CLIENTE/users.273.782246713 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile conversion input datafile fno=00012 name=+DADOS/cliente/datafile/users.274.782246713 converted datafile=/home/oracle/CLIENTE/users.274.782246713 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile conversion input datafile fno=00013 name=+DADOS/cliente/datafile/users.275.782246721 converted datafile=/home/oracle/CLIENTE/users.275.782246721 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile conversion input datafile fno=00014 name=+DADOS/cliente/datafile/users.276.782246721 converted datafile=/home/oracle/CLIENTE/users.276.782246721 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile conversion input datafile fno=00015 name=+DADOS/cliente/datafile/users.277.782246721 converted datafile=/home/oracle/CLIENTE/users.277.782246721 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile conversion input datafile fno=00016 name=+DADOS/cliente/datafile/users.278.782246723 converted datafile=/home/oracle/CLIENTE/users.278.782246723 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile conversion input datafile fno=00002 name=+DADOS/cliente/datafile/undotbs1.258.782246325 converted datafile=/home/oracle/CLIENTE/undotbs1.258.782246325 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile conversion input datafile fno=00004 name=+DADOS/cliente/datafile/users.259.782246325 converted datafile=/home/oracle/CLIENTE/users.259.782246325 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 Run SQL script /home/oracle/transportscript on the target platform to create database Edit init.ora file /u01/app/oracle/product/10.2.0/db_1/dbs/init_00nad5rn_1_0.ora. This PFILE will be used to create the database on the target platform To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform To change the internal database identifier, use DBNEWID Utility Finished backup at 07-MAY-12 RMAN>
Veja que ao final do comando, o RMAN informou que já foi criado um PFILE para facilitar nossa migração.
Na plataforma destino, criamos o diretório temporário, com o mesmo nome da origem.
login as: oracle Using keyboard-interactive authentication. Password: Last login: Mon May 7 12:45:17 2012 from 192.168.56.1 Oracle Corporation SunOS 5.10 Generic Patch January 2005 -bash-3.2$ su - Password: Oracle Corporation SunOS 5.10 Generic Patch January 2005 -bash-3.2# mkdir /stage -bash-3.2# chown -R oracle:dba /stage/ -bash-3.2# exit logout -bash-3.2$
Agora copiamos todo o conteúdo do diretório temporário da origem para o destino. Ele também poderia ser montado como um sistema NFS, que eliminaria a necessidade da cópia.
E após copiar os DATAFILEs, copio também o PFILE criado pelo RMAN.
[oracle@GG01 ~]$ scp /stage/* 192.168.56.101:/stage/ The authenticity of host '192.168.56.101 (192.168.56.101)' can't be established. RSA key fingerprint is c2:dc:d9:f3:e2:0d:07:93:da:76:6c:42:76:f0:c3:1c. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.56.101' (RSA) to the list of known hosts. Password: example.265.782246405 100% 100MB 20.0MB/s 00:05 sysaux.257.782246325 100% 240MB 9.6MB/s 00:25 system.256.782246323 100% 480MB 12.6MB/s 00:38 undotbs1.258.782246325 100% 25MB 25.0MB/s 00:01 users.259.782246325 100% 5128KB 5.0MB/s 00:00 users.267.782246635 100% 100MB 11.1MB/s 00:09 users.269.782246711 100% 100MB 11.1MB/s 00:09 users.270.782246711 100% 100MB 10.0MB/s 00:10 users.271.782246713 100% 100MB 10.0MB/s 00:10 users.272.782246713 100% 100MB 10.0MB/s 00:10 users.273.782246713 100% 100MB 10.0MB/s 00:10 users.274.782246713 100% 100MB 10.0MB/s 00:10 users.275.782246721 100% 100MB 9.1MB/s 00:11 users.276.782246721 100% 100MB 10.0MB/s 00:10 users.277.782246721 100% 100MB 10.0MB/s 00:10 users.278.782246723 100% 100MB 11.1MB/s 00:09 [oracle@GG01 ~]$ scp /u01/app/oracle/product/10.2.0/db_1/dbs/init_00nad5rn_1_0.ora 192.168.56.101:/opt/oracle/product/10.2.0/db_1/dbs/initCLIENTE.ora Password: init_00nad5rn_1_0.ora 100% 1536 1.5KB/s 00:00 [oracle@GG01 ~]$ scp /home/oracle/transportscript 192.168.56.101:/stage Password: transportscript 100% 2713 2.7KB/s 00:00 [oracle@GG01 ~]$
Certas adequações podem ser necessárias no script de migração, por exemplo, mudanças de diretórios. No meu caso, tive que alterar tudo o que era “/u01/oracle/” para “/opt/oracle”, como é o padrão no Solaris.
O PFILE também requereu algumas alterações parecidas, como os diretórios de DUMP.
-bash-3.2$ ls -lh /stage/transportscript -rw-r--r-- 1 oracle oinstall 2.6K May 7 13:38 /stage/transportscript -bash-3.2$ vi /stage/transportscript "/stage/transportscript" 79 lines, 2685 characters
Em seguida, basta executar o script de migração na plataforma destino.
-bash-3.2$ mv /stage/transportscript /stage/transportscript.sql
-bash-3.2$ export ORACLE_SID=CLIENTE
-bash-3.2$ sqlplus / AS SYSDBA
SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 7 14:03:53 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> @/stage/transportscript.sql
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
File created.
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
Control file created.
Database altered.
Tablespace altered.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Your database has been created successfully!
* There are many things to think about for the new database. Here
* is a checklist to help you stay on track:
* 1. You may want to redefine the location of the directory objects.
* 2. You may want to change the internal database identifier (DBID)
* or the global database name for this database. Use the
* NEWDBID Utility (nid).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if there the database was not opened in UPGRADE mode
DOC>
DOC> If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and
DOC> re-execute utlirp.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
2 WHERE status != 'OPEN MIGRATE';
no rows selected
SQL>
SQL> Rem
SQL> Rem Store object numbers of all valid PL/SQL-based functional indexes
SQL> Rem
SQL> DROP TABLE utlirp_enabled_func_indexes;
Table dropped.
SQL> CREATE TABLE utlirp_enabled_func_indexes AS
2 SELECT obj# FROM ind$
3 WHERE bitand(property, 2048) != 0 AND bitand(flags, 1024) = 0;
Table created.
SQL>
SQL> Rem invalidate all pl/sql modules and recompile standard and dbms_standard
SQL> @@utlip
...
SQL>
SQL> DOC
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
SQL> select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status = 3;
OBJECTS WITH ERRORS
-------------------
0
SQL>
SQL>
SQL> DOC
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;
ERRORS DURING RECOMPILATION
---------------------------
0
SQL>
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
PL/SQL procedure successfully completed.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL> set feedback 6;
Como o script finalizou sem erros, basta conferir o resultado na plataforma destino.
SQL> SELECT STATUS FROM V$INSTANCE; STATUS ------------ OPEN SQL> SELECT PLATFORM_NAME FROM V$DATABASE; PLATFORM_NAME -------------------------------------------------------------------------------- Solaris Operating System (x86-64) SQL> SELECT FILE_NAME FROM DBA_DATA_FILES; FILE_NAME -------------------------------------------------------------------------------- /stage/users.278.782246723 /stage/users.277.782246721 /stage/users.276.782246721 /stage/users.275.782246721 /stage/users.274.782246713 /stage/users.273.782246713 /stage/users.272.782246713 /stage/users.271.782246713 /stage/users.270.782246711 /stage/users.269.782246711 /stage/users.267.782246635 FILE_NAME -------------------------------------------------------------------------------- /stage/example.265.782246405 /stage/users.259.782246325 /stage/sysaux.257.782246325 /stage/undotbs1.258.782246325 /stage/system.256.782246323 16 rows selected.
Com 20 anos de experiência profissional, Oracle ACE Member – eleito pela Oracle Corporation um dos maiores especialistas do mundo em Oracle Database- Trabalhou em grande parte dos maiores bancos de dados Oracle do Brasil. Certificado em Oracle, SQL Server, DB2, MySQL, Sybase e Websphere.
Conselheiro do GPO e do GUOB, palestrante do ENPO, GUOB Tech Day e Oracle Open World, escritor da Revista SQL Magazine e Instrutor na Nerv.
apenas um pequeno erro de português… nem sei se você liga muito pra isso…
O PFILE também requeriu algumas alterações parecidas, como os diretórios de DUMP.
o certo é requerEu
abraço e parabéns pelo post…
Realmente está errado, que feio!
Obrigado Leonardo.
Abraço !