RMAN CONVERT DATABASE: Migrando de Linux para Solaris

RMAN CONVERT DATABASE: Migrando de Linux para Solaris

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.