Por Joel Perez , Mahir M. Quluzade (OCP) & Rodrigo Mufalani
Postado em janeiro 2014
Indice:
Backup do BD de origem com o uso de comando “BACKUP TO PLATFORM”. O requisito principal no “Oracle Database 12cR1” é que a base de dados origem esteja em modo “Read-Only” para se aplicar o procedimento de Transporte, objeto do presente artigo.
C:\Users\oracle>set ORACLE_IS=wdb12c C:\Users\oracle>rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Mon Jul 22 04:02:27 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: WDB12C (DBID=1042362086) RMAN> shutdown immediate; using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down RMAN> startup mount; connected to target database (not started) Oracle instance started database mounted Total System Global Area 801701888 bytes Fixed Size 2407784 bytes Variable Size 541065880 bytes Database Buffers 255852544 bytes Redo Buffers 2375680 bytes RMAN> alter database open read only; Statement processed RMAN> BACKUP 2> TO PLATFORM='Linux x86 64-bit' 3> FORMAT 'C:\Backup2\wdb12c_transport2.bck' 4> DATABASE; Starting backup at 27-JUL-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=C:\APP\ORACLE\ORADATA\WDB12C\SYSTEM01.DBF input datafile file number=00003 name=C:\APP\ORACLE\ORADATA\WDB12C\SYSAUX01.DBF input datafile file number=00002 name=C:\APP\ORACLE\ORADATA\WDB12C\EXAMPLE01.DBF input datafile file number=00005 name=C:\APP\ORACLE\ORADATA\WDB12C\UNDOTBS01.DBF input datafile file number=00006 name=C:\APP\ORACLE\ORADATA\WDB12C\USERS01.DBF channel ORA_DISK_1: starting piece 1 at 27-JUL-13 channel ORA_DISK_1: finished piece 1 at 27-JUL-13 piece handle=C:\BACKUP2\WDB12C_TRANSPORT2.BCK tag=TAG20130727T015837 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:03:15 Finished backup at 27-JUL-13
Para o presente caso construiremos manualmente os “Controlfiles” no BD de destino baseado na informação do BD de origem, para tanto, faremos um “Backup Controlfile to trace” para tal objetivo. Também será gerado um “Parameter File” igual ao de origem como podemos verificar abaixo:
C:\Users\oracle>set ORACLE_IS=wdb12c C:\Users\oracle>sqlplus/ as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 22 04:07:28 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter database backup controlfile to trace as 'C:\Backup1\controlfile2trace2.sql'; Database altered. SQL> create pfile='C:\Backup2\pfile4linux2.ora' from spfile; File created.
Transferência dos arquivos necessários, gerados nos passos anteriores:
[oracle@oel62-ora12c ~]$ cd /tmp/Backup2 [oracle@oel62-ora12c Backup2]$ ls -l total 1925240 -rw-r--r-- 1 oracle oinstall 6007 Jul 27 14:12 CONTROLFILE2TRACE2.SQL -rw-r--r-- 1 oracle oinstall 1042 Jul 27 14:12 pfile4linux2.ora -rw-r--r-- 1 oracle oinstall 1971429376 Jul 27 14:13 WDB12C_TRANSPORT2.BCK [oracle@oel62-ora12c Backup2]$
Parâmetros ajustados para o “Parameter File” do BD de destino
ldb12c2.__data_transfer_cache_size=0 ldb12c2.__db_cache_size=369098752 ldb12c2.__java_pool_size=4194304 ldb12c2.__large_pool_size=8388608 ldb12c2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment ldb12c2.__pga_aggregate_target=281018368 ldb12c2.__sga_target=524288000 ldb12c2.__shared_io_pool_size=0 ldb12c2.__shared_pool_size=134217728 ldb12c2.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/ldb12c2/adump' *.audit_trail='db' *.compatible='12.1.0.0.0' *.control_files='/u01/app/oracle/oradata/LDB12C2/control01.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='wdb12c' *.db_unique_name='ldb12c2' *.db_create_file_dest='/u01/app/oracle/oradata' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=6930m *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=ldb12c2XDB)' *.local_listener='' *.log_archive_format='ARC%S_%R.%T' *.memory_target=768m *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'
No destino precisamos realizar a criação do diretório “audit dump”
mkdir -p /u01/app/oracle/admin/ldb12c2/adump
No destino inicializaremos a nova instância “ldb12c2” em modo “NOMOUNT” fazendo uso do “Parameter File” construído e faremos a execução do comando “RESTORE”. No comando “RESTORE” especificamos:
[oracle@oel62-ora12c ~]$ export ORACLE_SID=ldb12c2 [oracle@oel62-ora12c ~]$ rman target / Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jul 27 14:30:39 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount pfile='/tmp/Backup2/pfile4linux2.ora'; Oracle instance started Total System Global Area 801701888 bytes Fixed Size 2293496 bytes Variable Size 545259784 bytes Database Buffers 251658240 bytes Redo Buffers 2490368 bytes RMAN> RESTORE 2> FOREIGN DATABASE TO NEW 3> FROM BACKUPSET '/tmp/Backup2/WDB12C_TRANSPORT2.BCK'; Starting restore at 27-JUL-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring all foreign files in backup piece channel ORA_DISK_1: reading from backup piece /tmp/Backup2/WDB12C_TRANSPORT2.BCK channel ORA_DISK_1: restoring foreign file 1 to /u01/app/oracle/oradata/LDB12C2/datafile/ o1_mf_system_8z75gzto_.dbf channel ORA_DISK_1: restoring foreign file 3 to /u01/app/oracle/oradata/LDB12C2/datafile/ o1_mf_sysaux_8z75gzy2_.dbf channel ORA_DISK_1: restoring foreign file 5 to /u01/app/oracle/oradata/LDB12C2/datafile/ o1_mf_undotbs1_8z75h01y_.dbf channel ORA_DISK_1: restoring foreign file 2 to /u01/app/oracle/oradata/LDB12C2/datafile/ o1_mf_example_8mq196lg_.dbf channel ORA_DISK_1: restoring foreign file 6 to /u01/app/oracle/oradata/LDB12C2/datafile/ o1_mf_users_8z75h049_.dbf channel ORA_DISK_1: foreign piece handle=/tmp/Backup2/WDB12C_TRANSPORT2.BCK channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:16 Finished restore at 27-JUL-13 RMAN> shutdown immediate; using target database control file instead of recovery catalog Oracle instance shut down RMAN> exit Recovery Manager complete.
Visualizando os “Datafiles” restaurados
[oracle@oel62-ora12c ~]$ cd /u01/app/oracle/oradata/LDB12C2/datafile/ [oracle@oel62-ora12c datafile]$ ls -l total 2334764 -rw-r----- 1 oracle oinstall 807411712 Jul 27 14:42 o1_mf_sysaux_8z75gzy2_.dbf -rw-r----- 1 oracle oinstall 817897472 Jul 27 14:43 o1_mf_system_8z75gzto_.dbf -rw-r----- 1 oracle oinstall 760225792 Jul 27 14:42 o1_mf_undotbs1_8z75h01y_.dbf -rw-r----- 1 oracle oinstall 374874112 Jul 27 14:42 o1_mf_example_8mq196lg_.dbf -rw-r----- 1 oracle oinstall 5251072 Jul 27 14:41 o1_mf_users_8z75h049_.dbf [oracle@oel62-ora12c datafile]$
Ajustes realizados no “Script” de reconstrução dos “Controlfiles”
STARTUP NOMOUNT PFILE='/tmp/Backup2/pfile4linux2.ora'; CREATE CONTROLFILE REUSE DATABASE "WDB12C" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/LDB12C2/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/LDB12C2/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/LDB12C2/redo03.log' SIZE 50M BLOCKSIZE 512 DATAFILE '/u01/app/oracle/oradata/LDB12C2/datafile/o1_mf_system_8z75gzto_.dbf', '/u01/app/oracle/oradata/LDB12C2/datafile/o1_mf_sysaux_8z75gzy2_.dbf', '/u01/app/oracle/oradata/LDB12C2/datafile/o1_mf_example_8mq196lg_.dbf', '/u01/app/oracle/oradata/LDB12C2/datafile/o1_mf_undotbs1_8z75h01y_.dbf', '/u01/app/oracle/oradata/LDB12C2/datafile/o1_mf_users_8z75h049_.dbf' CHARACTER SET AL32UTF8; -- Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/LDB12C2/datafile/temp01.dbf' SIZE 92274688 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions.
Recriando “Controlfiles”, abertura do BD e ajuste de “Tablespace” temporário
[oracle@oel62-ora12c ~]$ export ORACLE_SID=ldb12c2 [oracle@oel62-ora12c ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 27 14:54:21 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> @/tmp/Backup2/CONTROLFILE2TRACE2.SQL; ORACLE instance started. Total System Global Area 801701888 bytes Fixed Size 2293496 bytes Variable Size 545259784 bytes Database Buffers 251658240 bytes Redo Buffers 2490368 bytes Control file created. Database altered. Tablespace altered. SQL>exit
Confirmação que está conectado no BD destino
[oracle@oel62-ora12c ~]$ export ORACLE_SID=ldb12c2 [oracle@oel62-ora12c ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 27 14:55:57 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select platform_name from v$transportable_platform 2 where platform_id = (select platform_id from v$database); PLATFORM_NAME -------------------------------------------------------------------------------- Linux x86 64-bit SQL> connect mahir/mahir Connected. SQL> select * from t; N ---------- 1
Conclusão
Finalizamos o exemplo de transporte de BD objeto deste artígo. O mesmo utilizou a cláusula “BACKUP TO PLATFORM” a qual foi explicado no início deste artigo, é um tipo de “Backup” transportável a uma plataforma específica.
Vantagem: É um “Backup” que não necesita converssão no destino
Restrições: O “Overhead” de conversão é realizado no banco de origem durante o “Application Downtime”
Joel é um DBA expert com mais de 12 anos de experiência, especializado nas áreas de bases de dados com especial ênfase em soluções de alta disponibilidade (RAC, Dataguard, e outros). É um palestrante habitual em eventos de Oracle como: OTN LAD TOUR e outros. É o primeiro latino-americano a ser nomeado "OTN Expert " no ano de 2003 e é Oracle ACE Director.
Mahir é um DBA Senior com mais de 10 anos de experiência no Oracle Database com foco especial em Alta disponibilidade e soluções de Recuperação contra desastres (RAC, Data Guard, RMAN,…). Atualmente trabalha para o Central Bank of the Republic of Azerbaijan. Ele é um Oracle Certified Professional DBA. Mahir é palestrante do Azerbaijan Oracle User Group (AZEROUG) e também é blogger.
Mufalani é um DBA Sr. com mais de 10 anos de experiência, começou com o Oracle 8i, mas teve a oportunidade de dar suporte a Oracle 7.3.4 em diante. É especialista em banco de dados Oracle com foco principal em Performance & Tuning e RAC. É palestrante em eventos de Oracle como: OTN LAD TOUR e outros. Atualmente trabalha como consultor diversas empresas no segmento de variados ramos como: Educação, Saúde, Tecnologia, Seguros e etc. Foi o terceiro Oracle ACE a ser nomeado no Brasil e é OCP DBA nas versões 10g e 11g e OCE RAC 10gR2.