Backup & Recovery :: Dropping Tablespace Including Contents And Datafiles
Jun 17, 2013
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:APPADMINISTRATORORADATATESTSYSTEM01.DBF
2 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSAUX01.DBF
3 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUNDOTBS01.DBF
4 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUSERS01.DBF
5 Full 997754 17-JUN-13 C:DATAFABCD01.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.
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.
[code]....
how to recover those tablespace's datafile?What happened to earlier tablesapce backup?
View 9 Replies
ADVERTISEMENT
May 25, 2010
I am looking for a script to find the tbs usage including datafiles. Tablespaces Datafile Size Used Free Used% Free% Max MB
Need all the data in MB and all the datafile space in every tablespace.I have searched internet,but couldn't find this type of format.
View 9 Replies
View Related
Apr 27, 2012
Does change in segment size effect the rman backup size..??
I'm trying to understand do Rman backup the empty block from datafiles..?? And if it escape the empty block than inserting data in tables (increasing the number of used block) might change the size of total backup (Rman) size
View 2 Replies
View Related
Oct 13, 2011
I have no backup taken in my oracle server.Today morning i created a data file and unknowingly deleted it. Now i need to restore the datafile with its contents without using RMAN.
View 14 Replies
View Related
Apr 20, 2013
Need the command for backup all the datafiles using RMAN. Not any specific data file.
Also another thing, when i try to take full database backup, it creates two .bkp file. What the two files for?
input datafile file number=00004 name=/oracle/oradb/oradata/ora138/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-APR-13
channel ORA_DISK_1: finished piece 1 at 20-APR-13
piece handle=/oracle/oradb/flash_recovery_area/ORA138/backupset/2013_04_20/o1_mf_nnndf_TAG20130420T171350_8q4q2gop_.bkp tag=TAG20130420T171350 comment=NONE
[code].......
View 3 Replies
View Related
Jun 29, 2011
Can we restore a database if we have only the datafiles available? The database has been dropped but we have just the datafiles with NO system, sysaux,users and undo datafiles that were saved after taking the database offline and then dropping the database. I know this is strange but if possible, I want to see if we can retrieve some of the data from those datafiles to another database. BTW, There is no backup available for this database except the dbf files on OS.
View 3 Replies
View Related
Sep 7, 2012
i want to clone the test db to dev db but i dont want default users and tablespaces(SYS,SYSAUX,...) in the test db datapump and exp are not working, then contacted ORACLE support and they concluded that test db sysaux was corrupted.i took RMAN all tablespace bkup except default tablespaces. then created new database using DBCA and now i want to restore and recover all those test db tablesapces but while i'm doing restore its saying
RMAN> restore tablespace MAIN_DATA;
Starting restore at 07-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
creating datafile file number=20 name=/opt/oracle/oradata/DEV71/main_data01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 07-SEP-12
i created all tablespaces in dev same as test db,
View 6 Replies
View Related
Apr 18, 2013
we are using dataware house of huge amount of data I want to take backup using rman so that I restore oracle instance and specific tablespace only not full database
View 4 Replies
View Related
Jul 13, 2013
I recently started reading about oracle backup and recovery concepts, and I needed to understand what is happening where we put a tablespace to a backup mode (alter tablespace tablespace_name begin backup) ?
View 10 Replies
View Related
Aug 15, 2011
i fired query to find the tablespace_name for the table FYI below
SQL> select table_name , tablespace_name from dba_tables where
2 table_name like 'CLAIM_HEADER';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CLAIM_HEADER
it showing missing ....find out tablespace_name.
View 6 Replies
View Related
Mar 29, 2012
I have a "prj_tbl" named tablespace in a user's schema in my database. i have given 100mb size to the "prj_tbl" at the time of its creation and auto extend is open for 10mb,and now this tablespace is using nearly 3.6mb space for its data and remaining space is free,now i want to reduce the size of "prj_tbl" tablespace to 20mb and when i tried to resize it then ,
It throws an error- "'ora-03297 file contains used data beyond requested resize value oracle"
I think this message showing that my new size is small than the size of data on my tablespace but it shows free space nearly 96mb then it means my new size is larger than the size of data on tablespace. How should I reduce the size..
View 5 Replies
View Related
Jun 15, 2012
How can we clean a tablespace(delete all the contents) without dropping the tablespace.
View 3 Replies
View Related
Jul 20, 2011
I have taken the backup of a tablespace using the below mentioned command.
run
{
allocate channel c1 device type disk;
backup tablespace tcs_tbs;
}
Now I after even after one month or one year I want to restore the backup on my same database to bring the tablespace to the previous position.
Is it possible to restore even after one year, Is retention period gonna play its role in this?
View 9 Replies
View Related
Aug 22, 2012
I have taken the tablespace export it came 2.1gb and for the same user i have taken the schema export it came 5.1gb
why their is a lot of difference in size?
View 3 Replies
View Related
Mar 24, 2013
Operating System - WindowXP
oracle version 10.2.0.1
I was learning some recovery part in my home laptop. Database is in Archivelog, flashback mode. All of sudden, i deleted it from OS level with out taking backup of it.
When i tried to open database, it failed to start. Database is in mount mode.while trying to open, it gives message -
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATADBSYSTEM01.DBF'
I tried to create on file named "UNDOTBS01.DBF" but oracle is not recognizing it.
View 11 Replies
View Related
Feb 7, 2011
How many times i can do recover (TSPITR) a tablespace ? While trying to recover tspitr tablespace on 2nd time i got error message ORA-01178 file 13 created before last create controlfile,but first time it was success.
View 1 Replies
View Related
Apr 5, 2011
Imagine this situation
Current time :05 Apr 2011
I have to perform the TSPITR of a tablespace to recover it to a time 03-apr-2011 5 pm.Now I have done that.
Now I find that I need to actually recover it ti 04-apr-2011.Can I again recover the tablespace to 04-Apr after recovering it to 03-Apr by mistake I am not usng recovery catalog;
View 13 Replies
View Related
Jul 27, 2011
I am doing some test, seeking your expert opinion.I have a database and have the following backup strategy.
1) Database running in archive log mode.
2) First I backup all the table space excluding one tablespace 'DP_TS_LOB'.
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE 'DP_TS_LOBS';
RMAN> BACKUP DATABASE;
3) I take a separate Backup of DP_TS_LOB tablespace.
RMAN> BACKUP TABLESPACE DP_TS_LOB;
4) I backup all the archived redo logs.
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
RMAN> backup archivelog all delete input;
Now I wanted to restore the database from the backup excluding the DP_TS_LOB and bring it up and running. DP_TS_LOB is huge and used only on certain work flows. I don't want the database to be down until we restore DP_TS_LOB. I wanted to restore all the other tablespace and bring the database up operational and restore and recover the DP_TS_LOB tablespace datafile's in background taking the datafiles of DP_TS_LOB tablespace offline.I tried the following but unsuccessful.
RMAN> restore controlfile to 'g:ctl_bckctl_01' from autobackup;
RMAN> restore spfile to 'g:ctl_bckspfile' from autobackup;
RMAN> sql "create PFILE = ''G:ctl_bckPFILE'' from SPFILE = ''G:ctl_bckSPFILE''";
Update the new parameter with new control file name. Copied the control file and parameter file to corresponding location.
RMAN> startup nomount pfile=D:appdivaproduct11.1.0db_1databasepfile;
RMAN> restore database skip tablespace 'dp_ts_lob';
RMAN> alter database mount;
RMAN> sql 'alter database datafile 8 offline'; ==> datafile of DP_TS_LOB tablespace.
First I assumed "Recover Database" will only recover online database files. But got the following error.
RMAN> recover database;
Starting recover at 27-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:23:03
RMAN-06094: datafile 8 must be restored
Then I tried recovering data file separately, but same error.
RMAN> recover datafile 1;
Starting recover at 27-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:24:30
RMAN-06067: RECOVER DATABASE required with a backup or created control file
View 4 Replies
View Related
Mar 29, 2013
I created an encrypted tablespace for testing. I later dropped it but don't remember if I specified "including contents and datafiles". The tablespace was empty and there are no datafiles for it. However, the information for this dropped tablespace still shows up in v$encrypted_tablespaces. How do I get that lingering information removed?
View 3 Replies
View Related
Dec 23, 2012
i Cannot drop old undo tablespace. While dropping the old undo tablespace we get an error
ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU77$' found, terminate dropping tablespace
SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
TABLESPACE_NAME STATUS SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
APPS_UNDO NEEDS RECOVERY _SYSSMU77$
View 11 Replies
View Related
Oct 29, 2008
i want to delete one datafile from tablespace witch contain 2 datafiles
i did
ALTER DATABASE DATAFILE 'ton_fichier' OFFLINE DROP;
and delete the file physiquly but i have already my datafile witch i have delete in my tablespace
View 14 Replies
View Related
May 10, 2011
My supervisor wants to remove all the archivelogs since it was just a test for 1 year the DB is not actually YET been used. Problem is they want it to be used as soon as possible w/o recreating again the database and just removing some data on tables and removed archive logs. how to safely removed the existing archived logs and create a full backup with archived fresh to sequence 1.
View 2 Replies
View Related
Jul 12, 2010
Due to improper documentations of a certain project, I need to drop a DEFAULT tablespace of a newly created instance including it's associated datafiles by using this command:
"DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;"
The default tablespace name is QWER (qwer01.dbf) and I added 2 datafiles in it, re: OPD_SML01.dbf & EXYT_SML01.dbf.
Do I have to do it online or offline?
View 1 Replies
View Related
Dec 20, 2011
Can we add a datafile mapped to a shared network drive in windows server.
View 1 Replies
View Related
Jan 21, 2013
I need a PL/SQL stored procedure which will accept a datafile name as parameter and dynamically create and execute "alter tablespace" command to add this passed datafile dynamically.
View 6 Replies
View Related
Nov 18, 2011
How can I drop a lower case tablespace name from my DB?
SQL> select * from v$version;
BANNER
------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
[code]....
View 2 Replies
View Related
Feb 25, 2012
We have two tablespace xyz_ts & pqr_ts having 167 datafiles . We have to reduce this number of files. These tablespace having 580 GB size .
View 1 Replies
View Related
Jul 26, 2012
I want to remove a tablespace with it's datafile.
I've ran: DROP TABLESPACE x_tbs INCLUDING CONTENTS;
I've read(should have read it prior to action) [URL]tm
and I understand now that I should have stated "AND DATAFILES", However, too late now. the tablespace is removed, but the datafile x_tbs is still present in a folder of my OS.
Is it safe to manually remove it by deleting it from the operating system?
View 2 Replies
View Related
Feb 10, 2011
We are trying to restore a database on the different server using RAM incremental level 0 and 1 backups. We have weekly Incremental level 0 backups along with incremental backups on daily basis. During recovery of Incremental level 0 backup, RMAN is expecting/looking for Incremental Level 0 backup of prior week as well. Is this the usual process for RMAN to look for prior level 0 backup though we need to restore from the current level 0 backup and roll forward by a week with subsequent Level 1 backup.?
We intend to restore from the Level 0 of 1/16 and roll forward with daily incrementals till 1/22. RMAN is expecting level 0 backupsets from 1/9 backup as well and keep failing since those backups are not available. quick inputs since we are in middle of recovery and trying to resolve if this is even doable.
View 11 Replies
View Related
Oct 20, 2011
Here is the details
[oracle@localhost dbs]$ export ORACLE_SID=stby
[oracle@localhost dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 20 09:47:34 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> !
[oracle@localhost dbs]$ pwd
/oraeng/app/oracle/product/10.2.0/dbs
[oracle@localhost dbs]$ exit
exit
[code].....
View 1 Replies
View Related
