|
Step 1: Confirm Database Name
and Identify Tablespace Name to be used for DR Test.
SQL>
select INSTANCE_NAME, VERSION from v$instance;
INSTANCE_NAME VERSION
----------------
-----------------
opsdba 10.2.0.2.0
SQL>
select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP1
SQL>
select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/users01.dbf
/u02/ORACLE/opsdba/sysaux01.dbf
/u02/ORACLE/opsdba/undotbs01.dbf
/u02/ORACLE/opsdba/system01.dbf
/u02/ORACLE/opsdba/users05.dbf
/u02/ORACLE/opsdba/users02.dbf
/u02/ORACLE/opsdba/users03.dbf
/u02/ORACLE/opsdba/users06.dbf
/u02/ORACLE/opsdba/users07.dbf
/u02/ORACLE/opsdba/users04.dbf
10
rows selected.
Step 2: Create a new tablespace
with 1 Datafile which will be used for recovery exercise.
SQL>
create tablespace drtbs datafile '/u02/ORACLE/opsdba/drtbs1.dbf'
size
100M extent management local;
Tablespace
created.
SQL>
select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
DRTBS
TEMP1
6
rows selected.
SQL> select file_name from dba_data_files where
tablespace_name=
'DRTBS';
FILE_NAME
--------------------------------------------------------------
/u02/ORACLE/opsdba/drtbs1.dbf
SQL>
exit
Step 3: Take a full Backup of
Database & Archive log.
RMAN>
backup database plus archivelog;
Starting
backup at 28-JAN-07
current
log archived
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=154 devtype=DISK
channel
ORA_DISK_1: starting archive log backupset
channel
ORA_DISK_1: specifying archive log(s) in backup set
input
archive log thread=1 sequence=1 recid=362 stamp=612901138
input
archive log thread=1 sequence=2 recid=363 stamp=612901141
input
archive log thread=1 sequence=3 recid=364 stamp=612901146
input
archive log thread=1 sequence=4 recid=365 stamp=612943256
input
archive log thread=1 sequence=5 recid=366 stamp=612976032
input
archive log thread=1 sequence=6 recid=367 stamp=612976036
input
archive log thread=1 sequence=7 recid=368 stamp=613049876
input
archive log thread=1 sequence=8 recid=369 stamp=613049878
input
archive log thread=1 sequence=9 recid=370 stamp=613049879
input
archive log thread=1 sequence=10 recid=371 stamp=613049880
channel
ORA_DISK_1: starting piece 1 at 28-JAN-07
channel
ORA_DISK_1: finished piece 1 at 28-JAN-07
piece
handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.145.1.1.
613089429
tag=TAG20070128T223709 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:16
channel
ORA_DISK_1: starting archive log backupset
channel
ORA_DISK_1: specifying archive log(s) in backup set
input
archive log thread=1 sequence=1 recid=372 stamp=613049882
input
archive log thread=1 sequence=2 recid=373 stamp=613049884
input
archive log thread=1 sequence=3 recid=374 stamp=613049885
input
archive log thread=1 sequence=4 recid=375 stamp=613049887
input
archive log thread=1 sequence=5 recid=376 stamp=613049888
input
archive log thread=1 sequence=6 recid=377 stamp=613049889
input
archive log thread=1 sequence=7 recid=378 stamp=613049890
channel
ORA_DISK_1: starting piece 1 at 28-JAN-07
channel
ORA_DISK_1: finished piece 1 at 28-JAN-07
piece
handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.146.1.1.
613089445
tag=TAG20070128T223709 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel
ORA_DISK_1: starting archive log backupset
channel
ORA_DISK_1: specifying archive log(s) in backup set
input
archive log thread=1 sequence=1 recid=379 stamp=613089428
channel
ORA_DISK_1: starting piece 1 at 28-JAN-07
channel
ORA_DISK_1: finished piece 1 at 28-JAN-07
piece
handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.147.1.1.
613089453
tag=TAG20070128T223709 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished
backup at 28-JAN-07
Starting
backup at 28-JAN-07
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting full datafile backupset
channel
ORA_DISK_1: specifying datafile(s) in backupset
input
datafile fno=00001 name=/u02/ORACLE/opsdba/system01.dbf
input
datafile fno=00003 name=/u02/ORACLE/opsdba/sysaux01.dbf
input
datafile fno=00002 name=/u02/ORACLE/opsdba/undotbs01.dbf
input
datafile fno=00011 name=/u02/ORACLE/opsdba/drtbs1.dbf
input
datafile fno=00004 name=/u02/ORACLE/opsdba/users01.dbf
input
datafile fno=00005 name=/u02/ORACLE/opsdba/users02.dbf
input
datafile fno=00006 name=/u02/ORACLE/opsdba/users03.dbf
input
datafile fno=00007 name=/u02/ORACLE/opsdba/users05.dbf
input
datafile fno=00010 name=/u02/ORACLE/opsdba/users04.dbf
input
datafile fno=00008 name=/u02/ORACLE/opsdba/users06.dbf
input
datafile fno=00009 name=/u02/ORACLE/opsdba/users07.dbf
channel
ORA_DISK_1: starting piece 1 at 28-JAN-07
channel
ORA_DISK_1: finished piece 1 at 28-JAN-07
piece
handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455
tag=TAG20070128T223735 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished
backup at 28-JAN-07
Starting
backup at 28-JAN-07
current
log archived
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting archive log backupset
channel
ORA_DISK_1: specifying archive log(s) in backup set
input
archive log thread=1 sequence=2 recid=380 stamp=613089480
channel
ORA_DISK_1: starting piece 1 at 28-JAN-07
channel
ORA_DISK_1: finished piece 1 at 28-JAN-07
piece
handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.149.1.1.
613089480
tag=TAG20070128T223800 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished
backup at 28-JAN-07
Starting
Control File and SPFILE Autobackup at 28-JAN-07
piece
handle=/opt/oracle/product10gpr2/dbs/c-1493612009-20070128-03
comment=NONE
Finished
Control File and SPFILE Autobackup at 28-JAN-07
RMAN>exit
Step 4: Add a New Datafile to
that Tablespace and verify that the new file is now a member of that
tablespace. Also switch few log files just for confirmation.
SQL>
select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/drtbs1.dbf
SQL>
alter tablespace drtbs add datafile '/u02/ORACLE/opsdba/drtbs2.
dbf'
size 100m;
Tablespace
altered.
SQL>
select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/drtbs1.dbf
/u02/ORACLE/opsdba/drtbs2.dbf
SQL>
alter system switch logfile;
System
altered.
SQL>
/
System
altered.
Step 5: Create a new Table in
that tablespace and perform some DML operation. Also after DML operations
switch some logfile.
SQL>
create table t1(col1 number(10)) tablespace DRTBS;
Table
created.
SQL>
insert into t1 values (&a);
Enter
value for a: 1
old 1: insert into t1 values(&a)
new 1: insert into t1 values(1)
1
row created.
SQL>
/
Enter
value for a: 2
old 1: insert into t1 values(&a)
new 1: insert into t1 values(2)
1
row created.
SQL>
/
Enter
value for a: 3
old 1: insert into t1 values(&a)
new 1: insert into t1 values(3)
1
row created.
SQL>
/
Enter
value for a: 4
old 1: insert into t1 values(&a)
new 1: insert into t1 values(4)
1
row created.
SQL>
commit;
Commit
complete.
SQL>
select * from t1;
COL1
----------
1
2
3
4
SQL>
alter system switch logfile;
System
altered.
Step 6: In the OS Level remove
all files of that tablespace including the newly added one (whose backup does
not exist).
opsdba:/opt/oracle>cd
/u02/ORACLE/opsdba/
opsdba:/u02/ORACLE/opsdba>ls
–lrt drtbs*.dbf
total
1441496
-rw-r----- 1 oracle
dba 104865792 Jan 28 22:38
drtbs1.dbf
-rw-r----- 1 oracle
dba 104865792 Jan 28 23:08
drtbs2.dbf
opsdba:/u02/ORACLE/opsdba>rm
-r drtbs*.dbf
opsdba:/u02/ORACLE/opsdba>ls
-lrt drtbs*.dbf
ls:
drtbs*.dbf: No such file or directory
opsdba:/u02/ORACLE/opsdba>
Step 7: Try to bring the
tablespace offline and we will get error message as follows.
opsdba:/u02/ORACLE/opsdba>sql
SQL>
alter tablespace drtbs offline;
alter
tablespace drtbs offline
*
ERROR
at line 1:
ORA-01116:
error in opening database file 11
ORA-01110: data file 11:
'/u02/ORACLE/opsdba/drtbs1.dbf'
ORA-27041:
unable to open file
Linux-x86_64
Error: 2: No such file or directory
Additional
information: 3
Step 8: Now bring the Tablespace
offline with IMMEDIATE option and confirm.
SQL>
alter tablespace drtbs offline immediate;
Tablespace
altered.
SQL>
select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------
---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
USERS ONLINE
TEMP1 ONLINE
DRTBS OFFLINE
6
rows selected.
Step 9: Now connect to RMAN and Confirm
that No backup exist for the Newly added Datafile. Then try to restore the TABLESPACE
and we will see that RMAN
is creating that newly added Datafile as a part of the restore process. This is a new feature in 10G.
SQL>
select file_id, file_name from dba_data_files where
tablespace_name='DRTBS';
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------
----------
11
/u02/ORACLE/opsdba/drtbs1.dbf
12
/u02/ORACLE/opsdba/drtbs2.dbf
SQL>
exit;
opsdba:/u02/ORACLE/opsdba>rman
target /
Recovery
Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:18:
09
2007
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected
to target database: OPSDBA (DBID=1493612009)
RMAN>
list backup of datafile 11;
using
target database control file instead of recovery catalog
List
of Backup Sets
===================
BS
Key Type LV
-------
---- -- ---------- ----------- ------------ ---------------
129 Full
669.09M DISK 00:00:15 28-JAN-07
BP Key: 129 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T223735
Piece Name:
/opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455
List of Datafiles in backup set 129
File LV
---- -- ---- ---------- --------- ----
11
Full 2747296 28-JAN-07
/u02/ORACLE/opsdba/drtbs1.dbf
RMAN> list backup of
datafile 12;
No
output …
RMAN>
restore tablespace drtbs;
Starting
restore at 28-JAN-07
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=144 devtype=DISK
creating datafile fno=12 name=/u02/ORACLE/opsdba/drtbs2.dbf
channel
ORA_DISK_1: starting datafile backupset restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring
datafile 00011 to /u02/ORACLE/opsdba/drtbs1.dbf
channel
ORA_DISK_1: reading from backup piece /opt/oracle/backup/
opsdba/OPSDBA.20070128.148.1.1.613089455
channel
ORA_DISK_1: restored backup piece 1
piece
handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455 tag=TAG20070128T223735
channel ORA_DISK_1: restore complete, elapsed time:
00:00:04
Finished
restore at 28-JAN-07
opsdba:/u02/ORACLE/opsdba>rman
target /
Step 10: Start Recovery of that
Tablespace.
opsdba:/u02/ORACLE/opsdba>rman
target /
Recovery
Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:49:33 2007
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected
to target database: OPSDBA (DBID=1493612009)
RMAN>
recover tablespace drtbs;
Starting
recover at 28-JAN-07
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=154 devtype=DISK
starting
media recovery
un
Jan 28 23:22:36 2007
alter
database recover logfile '/u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf'
Sun
Jan 28 23:22:36 2007
Media
Recovery Log /u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf
Sun
Jan 28 23:22:36 2007
Recovery
of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
Mem# 0 errs 0:
/u02/ORACLE/opsdba/redo03.log
Sun
Jan 28 23:22:36 2007
Recovery
of Online Redo Log: Thread 1 Group 2 Seq 4 Reading mem 0
Mem# 0 errs 0:
/u02/ORACLE/opsdba/redo02.log
Sun
Jan 28 23:22:36 2007
Recovery
of Online Redo Log: Thread 1 Group 1 Seq 5 Reading mem 0
Mem# 0 errs 0: /u02/ORACLE/opsdba/redo01.log
Sun
Jan 28 23:22:36 2007
Media
Recovery Complete (opsdba)
Completed:
alter database recover logfile '/u02/ORACLE/opsdba/arch/arch_1_2_613052894.dbf'
Sun
Jan 28 23:22:52 2007
media
recovery complete, elapsed time: 00:00:00
Finished
recover at 28-JAN-07
RMAN>
exit
Recovery
Manager complete.
Step 11: Bring the Tablespace
online and confirm .
SQL>
alter tablespace drtbs online;
Tablespace
altered.
SQL>
select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------
---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
USERS ONLINE
TEMP1 ONLINE
DRTBS ONLINE
6
rows selected.
SQL>
alter system switch logfile;
System altered.
SQL> /
System altered.
SQL>
select * from t1;
COL1
----------
1
2
3
4
SQL>
select file_name from dba_data_files where tablespace_name=
'DRTBS';
FILE_NAME
----------------------------------------------------------------------
----------
/u02/ORACLE/opsdba/drtbs1.dbf
/u02/ORACLE/opsdba/drtbs2.dbf
SQL>
exit
Step 12: As a standard practice
immediately after the recovery please take a FULL DATABASE BACKUP.
opsdba:/u02/ORACLE/opsdba>rman
target /
Recovery
Manager: Release 10.2.0.2.0 - Production on Sun Jan 28 23:25:
01
2007
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
connected
to target database: OPSDBA (DBID=1493612009)
RMAN>
backup database plus archivelog;
Starting
backup at 28-JAN-07
current
log archived
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=144 devtype=DISK
skipping
archive log file /u02/ORACLE/opsdba/arch/arch_1_1_613052894.
dbf;
already backed up 1 time(s)
skipping
archive log file /u02/ORACLE/opsdba/arch/arch_1_2_613052894.
dbf;
already backed up 1 time(s)
channel
ORA_DISK_1: starting archive log backupset
channel
ORA_DISK_1: specifying archive log(s) in backup set
input
archive log thread=1 sequence=3 recid=381 stamp=613091353
input
archive log thread=1 sequence=4 recid=382 stamp=613091355
input
archive log thread=1 sequence=5 recid=383 stamp=613092208
input
archive log thread=1 sequence=6 recid=384 stamp=613092210
input
archive log thread=1 sequence=7 recid=385 stamp=613092318
channel
ORA_DISK_1: starting piece 1 at 28-JAN-07
channel
ORA_DISK_1: finished piece 1 at 28-JAN-07
piece
handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.154.1.1.
613092318
tag=TAG20070128T232518 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished
backup at 28-JAN-07
Starting
backup at 28-JAN-07
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting full datafile backupset
channel
ORA_DISK_1: specifying datafile(s) in backupset
input
datafile fno=00001 name=/u02/ORACLE/opsdba/system01.dbf
input
datafile fno=00003 name=/u02/ORACLE/opsdba/sysaux01.dbf
input
datafile fno=00002 name=/u02/ORACLE/opsdba/undotbs01.dbf
input
datafile fno=00011 name=/u02/ORACLE/opsdba/drtbs1.dbf
input
datafile fno=00012 name=/u02/ORACLE/opsdba/drtbs2.dbf
input
datafile fno=00004 name=/u02/ORACLE/opsdba/users01.dbf
input
datafile fno=00005 name=/u02/ORACLE/opsdba/users02.dbf
input
datafile fno=00006 name=/u02/ORACLE/opsdba/users03.dbf
input
datafile fno=00007 name=/u02/ORACLE/opsdba/users05.dbf
input
datafile fno=00010 name=/u02/ORACLE/opsdba/users04.dbf
input
datafile fno=00008 name=/u02/ORACLE/opsdba/users06.dbf
input
datafile fno=00009 name=/u02/ORACLE/opsdba/users07.dbf
channel
ORA_DISK_1: starting piece 1 at 28-JAN-07
channel
ORA_DISK_1: finished piece 1 at 28-JAN-07
piece
handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.155.1.1.
613092320
tag=TAG20070128T232520 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished
backup at 28-JAN-07
Starting
backup at 28-JAN-07
current
log archived
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting archive log backupset
channel
ORA_DISK_1: specifying archive log(s) in backup set
input
archive log thread=1 sequence=8 recid=386 stamp=613092345
channel
ORA_DISK_1: starting piece 1 at 28-JAN-07
channel
ORA_DISK_1: finished piece 1 at 28-JAN-07
piece
handle=/opt/oracle/backup/opsdba/OPSDBA.20070128.156.1.1.
613092346
tag=TAG20070128T232545 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished
backup at 28-JAN-07
Starting
Control File and SPFILE Autobackup at 28-JAN-07
piece
handle=/opt/oracle/product10gpr2/dbs/c-1493612009-20070128-07
comment=NONE
Finished
Control File and SPFILE Autobackup at 28-JAN-07
RMAN>
list backup of datafile 11;
List
of Backup Sets
===================
BS
Key Type LV
-------
---- -- ---------- ----------- ------------ ---------------
129 Full
669.09M DISK 00:00:15 28-JAN-07
BP Key: 129 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T223735
Piece Name:
/opt/oracle/backup/opsdba/OPSDBA.20070128.148.1.1.
613089455
List of Datafiles in backup set 129
File LV
---- -- ---- ---------- --------- ----
11
Full 2747296 28-JAN-07
/u02/ORACLE/opsdba/drtbs1.dbf
BS
Key Type LV
-------
---- -- ---------- ----------- ------------ ---------------
136 Full
669.73M DISK 00:00:21 28-JAN-07
BP Key: 136 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T232520
Piece Name: /opt/oracle/backup/opsdba/OPSDBA.20070128.155.1.1.
613092320
List of Datafiles in backup set 136
File LV
---- -- ---- ---------- --------- ----
11
Full 2748771 28-JAN-07
/u02/ORACLE/opsdba/drtbs1.dbf
RMAN>
list backup of datafile 12;
List
of Backup Sets
===================
BS
Key Type LV
-------
---- -- ---------- ----------- ------------ ---------------
136 Full
669.73M DISK 00:00:21 28-JAN-07
BP Key: 136 Status: AVAILABLE Compressed: NO Tag: TAG
20070128T232520
Piece Name:
/opt/oracle/backup/opsdba/OPSDBA.20070128.155.1.1.
613092320
List of Datafiles in backup set 136
File LV
---- -- ---- ---------- --------- ----
12
Full 2748771 28-JAN-07
/u02/ORACLE/opsdba/drtbs2.dbf
RMAN>
______________________________________END
_________________________________
No comments:
Post a Comment