Oraklet

De fleste benytter Recovery Manager (rman) til å ta sikkerhetskopi av datafiler. Men rman kan også brukes til å flytte datafiler. Fordelen med det er at du har et godt sikkerhetsnett som beskytter deg og omgivelsen mot de store feilene.

Å flytte en datafile fra A til B er ikke noe problem, men hva hvis filnavnet inneholder ny linje eller noen andre sære tegn som kom dit av mistak?

Her vises et eksempel med en datafil i tablespace USERS som må flyttes.

Begynn med å sjekke hvilket filnummer tablespacen har og ta den offline.

 
z00u074:DR1R> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Ti Mar 16 07:59:16 2010

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

11g SYS@DR1R SQL> SELECT file_id, file_name
                                FROM dba_data_files
                                WHERE tablespace_name = 'USERS';

   FILE_ID FILE_NAME
---------- ------------------------------------------------------------
         8 /u04/oradata/DR1R/DR1R_users_
            01.dbf

11g SYS@DR1R SQL> ALTER TABLESPACE users OFFLINE;

Tablespace altered.

Datafil 8 skal flyttes, det gjør vi i rman.

 
z00u074:DR1R> rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Ti Mar 16 07:41:25 2010

connected to target database: DR1R (DBID=1942219211)

RMAN> COPY DATAFILE 8 to '/u04/oradata/DR1R/DR1R_users_01.dbf';

Starting backup at 16/03/2010 07:55:29

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00008 name=/u04/oradata/DR1R/DR1R_users_
01.dbf

output file name=/u04/oradata/DR1R/DR1R_users_01.dbf tag=TAG20100316T075530 RECID=1 STAMP=713778931

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 16/03/2010 07:55:31

RMAN> LIST COPY LIKE '%users%';

specification does not match any archived log in the recovery catalog

List of Datafile Copies

=======================
Key     File S Completion Time     Ckp SCN    Ckp Time
------- ---- - ------------------- ---------- -------------------
1       8    A 16/03/2010 07:55:31 72709346997 16/03/2010 07:42:09

        Name: /u04/oradata/DR1R/DR1R_users_01.dbf

        Tag: TAG20100316T075530

RMAN> SWITCH DATAFILE 8 TO COPY;

datafile 8 switched to datafile copy "/u04/oradata/DR1R/DR1R_users_01.dbf"

RMAN> LIST COPY LIKE '%users%';

specification does not match any archived log in the recovery catalog

List of Datafile Copies

=======================

Key     File S Completion Time     Ckp SCN    Ckp Time
------- ---- - ------------------- ---------- -------------------
2       8    A 16/03/2010 07:56:01 72709346997 16/03/2010 07:42:09

        Name: /u04/oradata/DR1R/DR1R_users_
01.dbf

        Tag: TAG20091126T103852

Dermed er datafilen flyttet på en trygg og hurtig måte! Da gjenstår det å rydde opp, sjekk først at filen er på riktig sted i SQL*PLus.

 
11g SYS@DR1R SQL> SELECT file_id, file_name
                                FROM dba_data_files
                                WHERE tablespace_name = 'USERS';

   FILE_ID FILE_NAME
---------- ------------------------------------------------------------
         8 /u03/oradata/DR1R/DR1R_users_01.dbf

11g SYS@DR1R SQL> ALTER TABLESPACE users ONLINE;

Tablespace altered.

Fjern filen fra disken ved hjelp av rman.

 
RMAN> DELETE COPY LIKE '%users%';

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=513 device type=DISK

specification does not match any archived log in the recovery catalog

List of Datafile Copies

=======================

Key     File S Completion Time     Ckp SCN    Ckp Time
------- ---- - ------------------- ---------- -------------------
2       8    A 16/03/2010 07:56:01 72709346997 16/03/2010 07:42:09

        Name: /u04/oradata/DR1R/DR1R_users_
01.dbf

        Tag: TAG20091126T103852

Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy
datafile copy file name=/u04/oradata/DR1R/DR1R_users_
01.dbf RECID=2 STAMP=713778961
Deleted 1 object