Flytning af database vha. Oracle RMAN
Skrevet af: Karsten Birch-Johansen i
Oracle RMAN 
Technorati Tags:
Oracle Recovery Manager (RMAN) Database Flytning
For et par dage siden, på baggrund af performance issues, mellem SUN og EMC respektivt og om hvorvidt man vinder ved at køre ZFS kontra UFS, var RMAN løsningen. EMC peger på UFS, og vi har valgt at forsøge, selvom ZFS adminitrations mæssigt byder på herligheder, der gør man undgår et sandt mount point helvede.
Fremfor at lave en fil flytning/kopiering, valgte vi RMAN fordi det går rigtig stærkt. Omkring 64G data hvor vi normalt skal have et 4-5 timers åbningsvindue, bl.a. pga hårdt belastede systemer, taler vi her 30 minutter for selve kopieringen, der ligger selvfølgelig lidt arbejde før og efter, men samlet set; 1 time.
Start RMAN mod target databasen (dvs den base ORACLE_SID peger på. PS target er egentlig source, men det er terminologien):
[workday::/home1/usr/oracle/kbirch/] sid ORACLE_SID
[workday:ORACLE_SID:/home1/usr/oracle/kbirch/] sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Aug 12 11:36:06 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SQL> alter database backup controlfile to trace as '/home1/usr/oracle/gen_ctrl_files_ORACLE_SID.sql'
SQL> exit;
Husk her at editere din gen_ctrl_files_ORACLE_SID.sql med oplysninger omkring, dit nye mount_point!
[workday:ORACLE_SID:/home1/usr/oracle/kbirch/] rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Tue Aug 12 11:42:52 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORACLE_SID (DBID=2061390155)
Konfigurer nu RMAN til at smide sine backups, der hvor basen skal flyttes hen:
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/[mount_pount]/databases/ORACLE_SID/%d_%f.dbf';
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/[mount_point]/databases/ORACLE_SID/%d_%f.dbf';
new RMAN configuration parameters are successfully stored
Hvor
%d = database navn
%f = fil nummer
Tag en backup af databasen - brug "as copy" for at få en en image copy, dvs en komplet kopi af datafilerne
RMAN> BACKUP AS COPY DATABASE;
Starting backup at 12-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=89 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/[mount_point_source]/databases/ORACLE_SID/sysaux_01.dbf
output filename=/[mount_point_target]/databases/ORACLE_SID/ORACLE_SID_3.dbf tag=TAG20080812T121843 recid=1 stamp=662559577
Herfra skal følgende SQL køres fra SQLPlus
[workday:ORACLE_SID:/home1/usr/oracle/kbirch] sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Aug 12 12:44:40 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SQL> set pagesize 500
SQL> set linesize 500
SQL> spool rename_datafiles
SQL> select 'mv DBNAME_' || file# || '.dbf ' || regexp_replace(name, '(.*)/(.*?)$', '2') from v$datafile order by file#;
SQL> spool off
SQL> shutdown immediate
SQL> exit;
Tilret nu initORACLE_SID.ora mht. controlfiles destination, så de peger over mod det nye mount point.
[workday:ORACLE_SID:/home1/usr/oracle/kbirch] chmod 700 rename_datafiles
[workday:ORACLE_SID:/home1/usr/oracle/kbirch] ./rename_datafiles
NB: tag en kopi af dine redolog filer, de kan blive nødvendige hvis det går galt.
I vores tilfælde, skal redologs ikke flyttes, da de allerede ligger på optimale splits.
[workday:ORACLE_SID:/home1/usr/oracle/kbirch] sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Aug 12 13:07:32 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> @gen_ctrl_files_ORACLE_SID.sql
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 2769760 bytes
Variable Size 186924192 bytes
Database Buffers 478150656 bytes
Redo Buffers 3244032 bytes
Control file created.
Media recovery complete.
Database altered.
Og da det er en 10'er, så husk lige følgende
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/[mount_point_target]/databases/ORACLE_SID/temp_01.dbf' size 320M;
Herefter skulle du være klar.
