S:Solaris
DB:10g
I am simulating a recovery of drop undo tablespace. for the same I have done the following things:
1. Dropped the only undo datafile from the os level while the database was open.
2. Then I created a table and inserted and updated some values in it and I was also allowed to commit.
When I am updating the rows of the table,the before images must have been written to UNDO but in my case there is no UNDO datafile.
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.
regarding sizing undo tablespace and undo_retention parameter.we have to implement the database in production system with 40 users but how much space should be allocated to undo tablespace is there any propotions related to virtual memory and the
parameter.i have gone thru oracle doc's and some related sites.its an ERP aplications that contains 20 modules .I am an new one to this dba level
At a time my 20 GB undo tablespace was full. So i increased the tablespace size upto 48 GB. Then i saw 45 GB was used. Then i changed undo_retention=60. After that am seeing that 48 GB is full.
1) why it's happened?
2) Here what is the effect of undo_retention=60
3) How to resolve?.
Can an undo tablespace be too large and actually hurt performance? I have seen a system with a dedicated 1 TB drive for undo tablespace (no guarantee) with an undo_retention of 7 days. Would this hurt performance? What about setting an undo_retention of 24 hours with no guarantee? The only mention I could find online said that it would not hurt performance but I wanted to double check. You would think that Oracle does not care if it deletes the undo at 15 minutes or if it deletes the undo at a later date such as 7 days later and the performance should stay the same.
View 3 Replies View RelatedI am trying to drop 90 columns from a big partitioned table. I was trying a physical drop first and since it is taking longer time I decided make the columns unused state and drop them. However I was able to set them to unused state.
Now I am trying to drop those unused columns from the table, it is running since 22hours Apporox. I am keep increasing the undo tablespace to retain the undo data.
I also have decreased the undo_retention to 300 from 900.
My question is there any better way to drop these columns. And is there any way to flush out the data from undo.
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$
When i takeind export i got error like this..
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE_DATA:"POS"."TBK_POS_FACT":"KROATL200404"]
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small"
from this can we increase the undo_retention value or undo tablespace size?
I'm an Oracle novice and from what I've read so far, it seems that you should be able to do rollbacks and data recovery using the redo logs. I'm having a difficulty understanding the need for the undo tablespace.
View 2 Replies View Relatedit seems that you should be able to do rollbacks and data recovery using the redo logs. I'm having a difficulty understanding the need for the undo tablespace.
View 3 Replies View RelatedAs the undo segments are used in round robin fashion, Is it possible that with varying load (concurrent users, size and number of transactions), the size of Undo tablespace on a particular day is less than the Undo tablespace size few days back, by any chance?
As a basic understanding I know that Undo is preserved for read consistency and transaction, instance recovery So if there are lot of transaction on a database on 05 Feb and before that, but there aren't any transactions on 6,7,8,9, then on 10th Feb can we see the Undo tablespace size is less than that of 05 Feb?
In the following case when data belonging to table is not required for any queries, transactions, even then the undo size is not restored upon dropping the table.
As such for large operations and batch processes shall we keep undo tablespace with files as 'Autoextend' with 'Maxsize' as 'Unlimited'?
SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available,
Kbytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space
[code]....
why total size for undotbs1 is different from the acutal data file size in Operating system.
select tablespace_name, sum(bytes/1024/1024) from dba_data_files
where tablespace_name like 'UNDO%'
group by tablespace_name;
tablespacename total size
UNDOTBS1 2000
UNDOTBS2 7284
[code]....
rdbms 11gr2 undo tablespace is not fixed size. and the undo guarantee is not set.should we set undo_retention to
select max(tuned_undoretention) from v$undostat ; to avoid 1555 error?
you have an undo tablespace which has autoextend on feature.after a timegap your undo tablespace presently is 100GB
here as a DBA what you will do?
When I am importing, I get these errors
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (XXXXXXXXXXXXXXXX) violated
Column 1 2
Column 2 OFFLINE
[code]....
I added a datafile in undo tablespace (its an ASM database). I doubt that since I added the datafile to undo tablespace, I am getting this error.
we have a situation where both undo tablespaces were almost filled i.e UNDOTBS1 99% and UNDOTBS2 100% filled so i add data files to it and then i found a lot of blocking session and was just killing them through EM then i stop my front end listener and also down the service, now i don't have any blocking session but on EM a big WAIT is coming. alert log shows nothing serious, it was showing deadlock but now it is over as well.
View 8 Replies View RelatedI have a serious doubt in oracle architecture functionality, when a user issues a update statement the data blocks are carried to db buffer cache and where does the changes to the data blocks are made???? Does a copy of the data block is kept in db buffer cache and the changes are made to the block in buffer cache?? or the a copy of the data block is kept in undo tablespace and changes are made to the blocks in the undo tablespace???
In simple the changes to the data blocks are made at db buffer cache or undo tablespace?
i'm facing a problem while i'm inserting millions of record from table to table that undo tablespace reach 100% full and execution aborted. , how can free the undo tablespace ??? many of extendes are offline. will it flush automatically ??? or what i should do
View 4 Replies View RelatedDuring rollback phase of instance recovery, where is the undo applied from:
- redo logs
or
- undo generated during redo application in roll forward phase?
what is the role of undo cache or undo tablespace in recovery?
View 3 Replies View RelatedCan we find out no of undo segments in undo Table space ? If so , how to find? what's their max limit ?
View 9 Replies View RelatedI 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 Relatedi 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.
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..
I need to recover a tablespace called "SBSECD01_MDS" (point-in-time).I need to determine and resolve dependencies.
select obj1_owner, obj1_name, obj1_type, ts1_name, ts2_name from sys.ts_pitr_check where (ts1_name = 'SBSECD01_MDS' and ts2_name != 'SBSECD01_MDS') or (ts1_name != 'SBSECD01_MDS' and ts2_name = 'SBSECD01_MDS');
own1 name1 obj1type ts1_name ts2_name
--------------- -------------------- -------- ------------------------------ --------------------
SBSECD01_MDS MDS_PATHS_N7 INDEX SBSECD01_MDS -1
so I can see index SBSECD01_MDS.MDS_PATHS_N7 's ts2_name is "-1".reason: Domain/Functional Indexes not supported...My question,
1, what means ts2_name?
2. what means "-1"? (it looks not like a tablespace name)
3. in this case, Can I perform a successful TSPITR?
If not, how can do to make a successful TSPITR? (do I need to drop that index first, then recreate it after recovery?)
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?
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?
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 RelatedI am getting error when i try to restore one tablespace using RMAN.
RMAN> restore tablespace test;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "test": expecting one of: "double-quoted-string, identifier, single-quoted-string"
RMAN-01007: at line 1 column 20 file: standard input
But it's working for other tablespace
RMAN> restore tablespace users;
Starting restore at 10-OCT-12
using channel ORA_DISK_1
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 00004 to /u01/app/oracle/product/11.2.0/db_1/dbs/TEST/users01.dbf
[code]...
Oracle 11.2.0.1.0 on CentOS 5.5
i am now facing Tablespace Point-in-time subject. Doing some tests, the following problem appeared:
sql statement: alter database datafile 1 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 10 online
[code]....
ORA-01516: nonexistent log file, data file, or temporary file "10"I am using the following command to restore one of my tablespaces:
RMAN> run {
2> recover tablespace tbs_testes_pitr until time "to_date('25/07/2012 19:50:00','dd/mm/yyyy hh24:mi:ss')"
3> auxiliary destination '+DATA';
4> }The error described at the begin of this thread only appears at the end of the operation. I did some research, and the error ORA-01516 indicates that the datafile is not known by the database. When this error appears, i have to do a media recovery on datafile 10, which in fact exists, and is the datafile atached to the tablespace that i am trying to recover
