Home » RDBMS Server » Backup & Recovery » Droping tablespace including contents and datafiles, will delete Rman contents also? (11g, 11.2.0.1, window XP)
Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587621] Mon, 17 June 2013 08:07 Go to next message
ishika_20
Messages: 334
Registered: December 2006
Location: delhi
Senior Member
Dear All,

Need your's assistance to clarify my doubt.

Steps -

1) I have created One tablespace "ABCD" and assigned one user on it "ABCD_AR". I made table through the user and made some transaction.

2) Then I have taken RMAN backup, which include backup of tablespace (ABCD) also.

 List of Datafiles in backup set 10
 File LV Type Ckp SCN    Ckp Time  Name
 ---- -- ---- ---------- --------- ----
 1       Full 997754     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSTEM01.DBF
 2       Full 997754     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSAUX01.DBF
 3       Full 997754     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\UNDOTBS01.DBF
 4       Full 997754     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\USERS01.DBF
 5       Full 997754     17-JUN-13 C:\DATAF\ABCD01.DBF



3) After taking Rman backup, I issued command - "Drop tablespace ABCD including contents and datafiles;"

4) When i issue "LIst backup" through RMAN, its shows blank for the particular tablespace datafile.

Below are the scripts -



SQL> DROP TABLESPACE ABCD INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1374304 bytes
Variable Size             239077280 bytes
Database Buffers           67108864 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Documents and Settings\Administrator>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jun 17 18:17:36 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2116481712)

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5       7.13M      DISK        00:00:00     17-JUN-13
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20130617T163830
        Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_06_17\O1_MF_ANNNN_T
AG20130617T163830_8VXVKH7R_.BKP

  List of Archived Logs in backup set 5
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    7       988720     17-JUN-13 993133     17-JUN-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    956.93M    DISK        00:00:52     17-JUN-13
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20130617T163832
        Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_06_17\O1_MF_NNNDF_T
AG20130617T163832_8VXVKK1F_.BKP
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 993139     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSTEM01.DBF
  2       Full 993139     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSAUX01.DBF
  3       Full 993139     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\UNDOTBS01.DBF
  4       Full 993139     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\USERS01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    9.36M      DISK        00:00:01     17-JUN-13
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20130617T163832
        Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_06_17\O1_MF_NCSNF_T
AG20130617T163832_8VXVM969_.BKP
  SPFILE Included: Modification time: 17-JUN-13
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 993163       Ckp time: 17-JUN-13

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       8.00K      DISK        00:00:00     17-JUN-13
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20130617T163930
        Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_06_17\O1_MF_ANNNN_T
AG20130617T163930_8VXVMBQD_.BKP

  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    8       993133     17-JUN-13 993168     17-JUN-13

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       10.69M     DISK        00:00:01     17-JUN-13
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20130617T181059
        Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_06_17\O1_MF_ANNNN_T
AG20130617T181059_8VY0YVMH_.BKP

  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    7       988720     17-JUN-13 993133     17-JUN-13
  1    8       993133     17-JUN-13 993168     17-JUN-13
  1    9       993168     17-JUN-13 997744     17-JUN-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    957.35M    DISK        00:01:43     17-JUN-13
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20130617T181103
        Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_06_17\O1_MF_NNNDF_T
AG20130617T181103_8VY0YZPW_.BKP
  List of Datafiles in backup set 10
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 997754     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSTEM01.DBF
  2       Full 997754     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSAUX01.DBF
  3       Full 997754     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\UNDOTBS01.DBF
  4       Full 997754     17-JUN-13 C:\APP\ADMINISTRATOR\ORADATA\TEST\USERS01.DBF
  5       Full 997754     17-JUN-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    9.36M      DISK        00:00:04     17-JUN-13
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20130617T181103
        Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_06_17\O1_MF_NCSNF_T
AG20130617T181103_8VY12COF_.BKP
  SPFILE Included: Modification time: 17-JUN-13
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 997848       Ckp time: 17-JUN-13

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12      128.00K    DISK        00:00:00     17-JUN-13
        BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: TAG20130617T181255
        Piece Name: C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_06_17\O1_MF_ANNNN_T
AG20130617T181255_8VY12HG3_.BKP

  List of Archived Logs in backup set 12
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    10      997744     17-JUN-13 997856     17-JUN-13

RMAN> restore database;

Starting restore at 17-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\APP\ADMINISTRATOR\ORADATA\TEST\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\APP\ADMINISTRATOR\ORADATA\TEST\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\APP\ADMINISTRATOR\ORADATA\TEST\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TEST\BACKUPSE
T\2013_06_17\O1_MF_NNNDF_TAG20130617T181103_8VY0YZPW_.BKP
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/17/2013 18:17:56
ORA-19870: error while restoring backup piece C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\TEST\BACKUPSE
T\2013_06_17\O1_MF_NNNDF_TAG20130617T181103_8VY0YZPW_.BKP
ORA-19573: cannot obtain exclusive enqueue for datafile 1

RMAN> exit



Kindly suggest me how to recover those tablespace's datafile?
What happened to earlier tablesapce backup?


Regards,
Ishika
Re: Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587624 is a reply to message #587621] Mon, 17 June 2013 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ There is no problem having a blank name, it cannot find it because it is no more in the dictionary table it queries
2/ Your problem is with "ORA-19573: cannot obtain exclusive enqueue for datafile 1":
ORA-19573: cannot obtain %s enqueue for datafile %s
 *Cause:  The file access enqueue could not be obtained for a file
          specified in a backup, copy or restore operation.
          If the enqueue type shown is 'shared', then the file is the
          input file for a backup or copy.  If the type is 'exclusive', then
          the file is the output file for a datafile copy or restore which
          is attempting to overwrite the currently active version of that
          file - in this case, the file must be offline or the database must
          be closed.  If the type is 'read-only', then you are attempting
          to back up or copy this file while the database is in NOARCHIVELOG
          mode.
 *Action: Wait until the conflicting operation is complete, then retry
          the copy or backup.  If the database is in NOARCHIVELOG mode, then
          all files being backed up must be closed cleanly.

As your database is open you cannot restore datafile 1 (SYSTEM tablespace).

If you just want to restore the tablespace you could use "RESTORE TABLESPACE" but as you dropped it you culd not recover until the current SCN of the database.
There are solutions, it depends on what you want to get back and what you allow you to lose.

Regards
Michel
Re: Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587668 is a reply to message #587621] Mon, 17 June 2013 22:59 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
The Tablespace and its Datafile no longer belong to the database (they are no longer listed in the data dictionary and controlfile once you issue the DROP TABLESPACE .... command).

If you want that tablespace back you would have to use another server :
1. Get a controlfile backup that was created before the tablespace was dropped
2. Get a database backup that was created before the tablespace was dropped
3. Restore the controlfile and database backups to another server and do an RECOVER DATABASE to a point in time before when the DROP TABLESPACE was issued

Thereafter, you have two choices
A. Use Transportable Tablespace to transport and plug in the tablespace from the other server to the original server
B. Selectively use export to export schemas / objects from that tablespace on the other server and then import into the database on the original server




BTW, the error you have on the RESTORE DATABASE is because your database is still OPEN and datafile 1 (the SYSTEM tablespace) is active. You shouldn't be doing a RESTORE DATABASE.

Hemant K Chitale
Re: Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587676 is a reply to message #587668] Tue, 18 June 2013 00:10 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
BTW, the error you have on the RESTORE DATABASE is because your database is still OPEN and datafile 1 (the SYSTEM tablespace) is active. You shouldn't be doing a RESTORE DATABASE.


Is this not what I have said?
Why do you repeat the same thing?

Regards
Michel
Re: Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587679 is a reply to message #587668] Tue, 18 June 2013 00:20 Go to previous messageGo to next message
ishika_20
Messages: 334
Registered: December 2006
Location: delhi
Senior Member
Dear Michel / Hemant,

Do my backup, taken before issuing drop command has tablespace data? As we know, List command is used to check the details of the backup. So, if data-dictionary doesn't have data then LIST command will not show tablespace details of the backup?

Here, I am confused.

Regards,
Ishika

Re: Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587682 is a reply to message #587679] Tue, 18 June 2013 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not need the name, the key is the number which it has.
LIST is a basic command, it does not show all information it has.

Regards
Michel
Re: Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587683 is a reply to message #587682] Tue, 18 June 2013 01:44 Go to previous messageGo to next message
ishika_20
Messages: 334
Registered: December 2006
Location: delhi
Senior Member
Dear Michel

It means backup is available but LIST command doesn't show all information. Though, this is the only command to know the information about the backup ...rite ?

Regards,
Ishika
Re: Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587686 is a reply to message #587683] Tue, 18 June 2013 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need this information, just let RMAN does its work.
What problem are you trying to solve?

Regards
Michel
Re: Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587700 is a reply to message #587679] Tue, 18 June 2013 03:11 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
LIST BACKUP shows backups. However, it also does a join to the actual data dictionary. If a tablespace and/or datafile has been dropped since the backup was taken, LIST cannot show that Tablespace/datafile.

For example, see what error is returned if you issue a LIST BACKUP OF TABLESPACE ABCD;

As for the "NULL" appearing for the datafile, Oracle knows that a backup of then-existant datafile 5 was made but, since the datafile doesn't exist in the database, it can no longer show you the file name when you issue a LIST BACKUP.


Hemant K Chitale
Re: Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587742 is a reply to message #587700] Tue, 18 June 2013 06:01 Go to previous message
ishika_20
Messages: 334
Registered: December 2006
Location: delhi
Senior Member
Dear Hemant,

Great help to understand the logic of LIST !!!

LIST BACKUP shows backups. However, it also does a join to the actual data dictionary.


Thanks

Regards,
Ishika
Previous Topic: Restore db ?
Next Topic: backup utility problems
Goto Forum:
  


Current Time: Thu Apr 17 21:12:39 CDT 2014

Total time taken to generate the page: 1.83549 seconds