Creating image copies and switch to image copies for fast recovery

Hello, everyone!
Today, I will talk about a little of image copies. An image copy is an exact copy of a tablespace’s datafile, an archive redo log file or a control file.
This type of backup can be written only to disk. In case of recovery the time from restoration is less because the file is already on disk.
An image copy can be used like a full backup or a backup level 0, all used and an unused blocks are taken during this type of backup operation.
Also the command SWITCH will be used to perform fast recovery operation.

Let’s stat

This my production database with my tablespace called vendas, with the table clientes

SQL> select TABLESPACE_NAME from dba_tables where table_name=’CLIENTES’;

TABLESPACE_NAME
——————————
VENDAS

The command bellow will perform a an image copy from my tablespace.

RMAN> backup as copy tablespace vendas;

Starting backup at 18-APR-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/DB11G/tbs_vendas.dbf
output file name=/u01/app/oracle/fast_recovery_area/DB11G/datafile/o1_mf_vendas_bm69wq8m_.dbf tag=TAG20150418T235711 RECID=4 STAMP=877391831
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-APR-15

Starting Control File and SPFILE Autobackup at 18-APR-15
piece handle=/u01/app/oracle/fast_recovery_area/DB11G/autobackup/2015_04_18/o1_mf_s_877391832_bm69wrc2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-APR-15
Below are the values from table clientes.

SQL> select * from clientes;

NOME RUA IDADE
——————– ——————– ———-
JOSE ALAMEDA 44
PAULA ANCHIETA 28
Because a crash of my storage disk I lost my tablespace:

SQL> INSERT INTO CLIENTES VALUES (‘MARIA’, ‘DAS FLORES’, 53);
INSERT INTO CLIENTES VALUES (‘MARIA’, ‘DAS FLORES’, 53)
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/DB11G/tbs_vendas.dbf’
The information confirming that my datafile was lost.

SQL> SELECT * FROM V$RECOVER_FILE;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
———- ——- ——- —————————————————————– ———- ———
6 OFFLINE OFFLINE FILE NOT FOUND 0
No we are perform the recovery of datafile:
RMAN> SQL ‘ALTER TABLESPACE VENDAS OFFLINE IMMEDIATE’;

using target database control file instead of recovery catalog
sql statement: ALTER TABLESPACE VENDAS OFFLINE IMMEDIATE

RMAN> SWITCH TABLESPACE VENDAS TO COPY;

datafile 6 switched to datafile copy “/u01/app/oracle/fast_recovery_area/DB11G/datafile/o1_mf_vendas_bm69wq8m_.dbf”

RMAN> RECOVER TABLESPACE VENDAS;

Starting recover at 19-APR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=93 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=97 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=98 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=99 device type=DISK

starting media recovery

archived log for thread 1 with sequence 107 is already on disk as file /u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_107_bm6b3kmn_.arc
archived log for thread 1 with sequence 108 is already on disk as file /u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_108_bm6b4sp2_.arc
archived log for thread 1 with sequence 109 is already on disk as file /u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_109_bm6b8zl1_.arc
archived log for thread 1 with sequence 110 is already on disk as file /u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_110_bm6b9090_.arc
archived log for thread 1 with sequence 111 is already on disk as file /u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_111_bm6b93lx_.arc
archived log for thread 1 with sequence 112 is already on disk as file /u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_112_bm6b957b_.arc
archived log for thread 1 with sequence 113 is already on disk as file /u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_113_bm6bsb00_.arc
archived log for thread 1 with sequence 114 is already on disk as file /u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_114_bm6bscp9_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_107_bm6b3kmn_.arc thread=1 sequence=107
archived log file name=/u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_108_bm6b4sp2_.arc thread=1 sequence=108
archived log file name=/u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_109_bm6b8zl1_.arc thread=1 sequence=109
archived log file name=/u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_110_bm6b9090_.arc thread=1 sequence=110
archived log file name=/u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_111_bm6b93lx_.arc thread=1 sequence=111
archived log file name=/u01/app/oracle/fast_recovery_area/DB11G/archivelog/2015_04_19/o1_mf_1_112_bm6b957b_.arc thread=1 sequence=112
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-APR-15

RMAN> SQL ‘ALTER TABLESPACE VENDAS ONLINE’;

sql statement: ALTER TABLESPACE VENDAS ONLINE

Switch back to the original location:

RMAN> BACKUP AS COPY TABLESPACE VENDAS FORMAT ‘/u01/app/oracle/oradata/DB11G/tbs_vendas.dbf’;

Starting backup at 19-APR-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/fast_recovery_area/DB11G/datafile/o1_mf_vendas_bm69wq8m_.dbf
output file name=/u01/app/oracle/oradata/DB11G/tbs_vendas.dbf tag=TAG20150419T003237 RECID=5 STAMP=877393958
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 19-APR-15

Starting Control File and SPFILE Autobackup at 19-APR-15
piece handle=/u01/app/oracle/fast_recovery_area/DB11G/autobackup/2015_04_19/o1_mf_s_877393958_bm6cz6wb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-15

RMAN> SQL ‘ALTER TABLESPACE VENDAS OFFLINE’;

sql statement: ALTER TABLESPACE VENDAS OFFLINE

RMAN> SWITCH TABLESPACE VENDAS TO COPY;

datafile 6 switched to datafile copy “/u01/app/oracle/oradata/DB11G/tbs_vendas.dbf”

RMAN> RECOVER TABLESPACE VENDAS;

Starting recover at 19-APR-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 19-APR-15
RMAN> SQL ‘ALTER TABLESPACE VENDAS ONLINE’;

sql statement: ALTER TABLESPACE VENDAS ONLINE
Well, that it is.
I hope that you enjoy.
Felipe.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s