| Droping tablespace including contents and datafiles, will delete Rman contents also? [message #587621] |
Mon, 17 June 2013 08:07  |
ishika_20
Messages: 339
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  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 #587700 is a reply to message #587679] |
Tue, 18 June 2013 03:11 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
