Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Recovering Datafile for which no backup exist (In 10G Database Using RMAN).


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 Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
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 LVType Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  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 Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
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 LVType Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 2747296    28-JAN-07 /u02/ORACLE/opsdba/drtbs1.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
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 LVType Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  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 Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
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 LVType Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  12      Full 2748771    28-JAN-07 /u02/ORACLE/opsdba/drtbs2.dbf

RMAN>
______________________________________END _________________________________



Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform