Hello All,
We are in the process of upgrading a database from 8.0.6 to 8.1.7. The
upgrade is not a problem. We have been asked to come out with a fast
rollback strategy in case we have to fall back, say after a week, to the
8.0.6 database with no data loss. Any fresh ideas?
Thanks
Raj
--
You are lucky to have all your databases in archivelog mode. We have large
datawarehouses here, where business is quite acceptable to a recovery from
a cold backup taken 3 months earlier. RMAN is goood, but waiting for
Oracle to come out with an object point in time recovery, before we can
BHR has taken quite a beating. I am sure the point everyone's trying to
make is that a 99% BHR does not indicate a well tuned database, and a 50%
BHR does not indicate a poorly peforming database. A low BHR does not mean
I need to increase db_buffers as was believed and practiced earlier.
Hi All,
OPS 8.0.6.2 on Sun 2.6 nodes. The other day, the users reported that they
were unable to complete transactions, and before we could take a proper
look, the database was shutdown aborted, and started up. I am now trying to
investigate possible reasons for these errors. Just before the
Thanks Tim for your response to this. I agree with you. For now, I have
come up with this theory :
I feel that the Unable to use system rollback tablespace errors were a
result of the Out of transaction slots message. Transactions were
assigned as they came in to different transaction slots in
Since everyone's advocating RMAN here, I decided to start an RMAN backup
for one of our development databases. And I had issues with disk space.
Now, one of the tablespaces has been allocated 15 Gb of space, of which
only 2.2 Gb has been used as per dba_extents. I started an RMAN backup
directing
Yeah, Tim. You nailed it. Data was indeed purged from this tablespace.
Thanks once again
Raj
Tim Gorman
Hello Folks,
Oracle 7.3.4 on SunOS 2.6. We were testing out a hot backup using BCV with
scripts provided by EMC. The database fails to open after the restore and
claims that it needs to apply more logs to be consistent. The script
performs a log switch, then select the archive_change# from
Hello Folks,
I carried out a test, whereby I had just one rollback segment online with a
block size of 2K, and then running updates against the database thru
different sessions without commiting any of them? As expected, it accepted
the first 21 transactions, and on the 22nd transaction, threw
]
cc: Rajesh Rao/CHASE@CHASE
October 08, Subject: Re: ORA-1554: Out of transaction
slots
2002 02:44 PM
Thanks Gopal for the info provided. I would want to know how I can simulate
this error? I did some tests but I got an Unable to use system rollback
segment for non system ... .
We had one of our applications report this in the application log. I am
trying to find out why?
Thanks
Raj
adisys.com To: [EMAIL PROTECTED]
cc: Rajesh Rao/CHASE@CHASE
October 08, Subject: Re: ORA-1554: Out
of transaction slots
2002 02:44 PM
It's dependant on block size, or at least
We have a developer here, installing a third party application, who claims
one of his delete campaign process is hanging. I looked at the wait
events, saw nothing, and asked him to politely to go look at the code.
After much analysys, the developer now complains, that Oracle is not
executing a
Thats a nice idea. The problem here is that the owner of the table is the
one executing the drop. And its not via SQLPlus. So, the
use of product_profile is also ruled out. The SQLNet tracing suggested by
Robert also seems a good idea, worth trying out. They have been pointing me
to their log
And I Used oradebug at level 12 and set the file size to unlimited, and I
am not running out of space in the udump destination. So, why dont I see
the execution plan?
- Forwarded by Rajesh Rao/CHASE on 10/10/02 11:22 AM
Rajesh Rao
To: [EMAIL PROTECTED
I am not talking of the execution plan for the drop table statement, but
for the select that's run prior to it? I opened a TAR for it, and Metalink
states: sql trace messages are not in sync with the session. Some
messages will only dump to trace when the cursor is closed, or the program
to unlimited, and I
am not running out of space in the udump destination. So, why dont I see
the execution plan?
- Forwarded by Rajesh Rao/CHASE on 10/10/02 11:22 AM -
Rajesh Rao
To: [EMAIL PROTECTED]
October
Whoa Stephane You might be onto something here. The developer
confirmed that they do use Pro*C and cursors in the process. Will
investigate.
Thanks a ton
Raj
How do you use this script to find bottlenecks in the system? It would only
show you the scattered and sequential read waits? The query would not
display rest of the wait events
Raj
This is definitely possible with RMAN. The only hitch is that in case you
use RMAN to write to disk, the same directory structure must exist on the
target server. The workaround would be to create soft links. Also, in case,
you are moving the datafiles to a different destination, then one needs
If the database crash causes media failure of your archive destination or
your online log destination, then you can only recover the database upto
the last archivelog availability, and you would need to open the database
with resetlogs. This is true for both scenarios.
Assuming no media
That would be the manual mode. Recover standby database. With 8i, and the
multiple archive destinations, you have the sustained recovery mode.
Recover managed standby database. A sustained recovery mode will lock
your telnet window, and will be dedicated to doing just the recovery. But
then, one
Dont you mean complete recovery?
You could then perform an incomplete recovery until 02:10:01 - 02:10:59 or
02:21:01 - 02:21:59, etc.
Fink, Dan
Dan,
What you intend to say is that if the database crashed at say 2:15:30, you
can do an incomplete recovery upto say 2:10:30, right? Why cant I do a
complete recovery ? or what stops me from doing an incomplete recovery
until 2:15:25?
Raj
Not sure, Though it does run queries against the database, does the OEM
Agent not use MIB?
One attachment (0k)
Jamadagni,
- Forwarded by Rajesh Rao/CHASE on 10/17/02 05:15 PM -
Rajesh Rao
I need to estimate disk space sizing requirements for a new database
server. Does anyone have a document that I can send forth to the
developers, asking for inputs from them, about the objects, the growth
patterns, et all? Or any links?
Thanks
Raj
--
Please see the official ORACLE-L FAQ:
The only correct resolution to this is to ensure that DML transactions
referencing the tables being backed up, are not run at the same time.
Bringing the big rollback segment online wont buy you anything. You could
delay the inevitable by removing the optimal clauses if set.
Raj
We too make extensive use of the product, though our team does not need to
maintain it. Just the backend database. Seems to work as intended, alerting
the operations staff in case of failures. I have not heard the folks trash
this product. Though, I have had them ask me for JIL scripts, whatever
To summarise, the goal should be to spead I/O evenly across the devices.
Right?
Raj
Markham, Richard
Grant privileges on v_$sysstat
dist cash
Mike,
While not necessary, it would be a good idea to precreate the tablespaces.
Else, for a clean import the first time, you would need to ensure that the
directory structure for the datafiles exists, you have the permissions to
create files at the OS level, ensure that files dont already exist
Johnson,
Auditing, atleast in 8i, wont help. Maybe, if its not an overhead, you
could have a logon trigger that enables tracing for those users.
Raj
Simply speaking, consider SID to be the name for the various background
process (DBWR, LGWR, et all) and the memory structures (SGA, PGA). A SID
needs to be associated with a database, which would be the DB_NAME. The SID
and DB_NAME need not be the same. They have to be different in an Oracle
Mike,
These events are generally set at the advice of Oracle Support, or when the
DBA needs to do some tracing. You might not find these in the docs, but
Metalink should have notes on them.
Raj
If you follow OFA, it works for NT ;-)
Tom Pall
You make me nostalgic. Reminds me of the times when I used to use elm and
pine as my email software. I remember using uuencode to encode the files,
and sending them like a normal text file. The receiver then had to save the
email as an ascii text, and use uudecode to retrieve the attachment.
And have a similarly tested and signed off rollback strategy in place. An
immediate rollback, as well as a rollback strategy after n number of days.
Raj
One attachment (0k)
As an example, something that yours truly was involved with, and still have
the scars to show for it. A migration from a lower version of Oracle, to a
higher version, on a completely new server. The scripts ran fine, and the
implementation plan worked fine. However, the application started
I also have been burnt by an simple alter table add column default string
script. A pretty simple script. The developers tested it, the QA team too
approved it. Was expected to run in a matter of minutes. The problem was
the data in production was 100 times over. Missed out on volume testing.
Thats Utopia for us. Production DBAs have no access to Development and QA
and Test boxes. The application developers code for everything, they test
it, send it to QA, who perform the implementation on the QA databases. We
only review the scripts, after it has been approved by QA, and do some
Everything good comes at a price. And never trust any system, where to shut
down, you got to click on start ;-)
Regards
Raj
Steve Perry
Hello All,
Is resizing the datafiles the only way of reducing the size of an RMAN full
backup? Oracle Version 8.0.6. We take RMAN hot backups to disk, and the
size of the backup has grown considerably. There's one large table which we
were considering truncating. But looks like that would not
Hello All,
8.0.6.2.0 Parallel Server on Solaris 2.6 nodes. We have intermittent
outages, once every 2 days, here on a production database. What happens is
that all DML transactions dont get committed or rolled back, and start
filling up the transaction slots in the rollback segments. On a normal
Andrey,
Instead of explicitly using 'where rowid = ', why not consider the use
'for update of' and 'where current of'?
Raj
Andrey Bronfin
Communication gap and ignorance. The developers proably want to create new
sequences. And they are used to writing queries as Select
sequencename.nextval from dual; So they call it sequences on the dual
table.
For most unix Platforms, the SID can be upto 8 characters in length. For
NT, I think, it should be 4 mainly because of DOS file name limitations. I
dont have the resources on hand, but I guess one could confirm this by just
trying to mount an instance with varying SID Names, until you get the
Dont use a PL/SQL cursor and loop, when bulk deletes are a possibility.
Delete from table where rownum 1000;
commit;
Delete from table where rownum 1000;
commit;
.
I would prefer this approach. 20 is not much. With a create table as
you
I asked this question the other day, but got no replies. I make one more
attempt and rephrase the question. A curious mind wants to know Assuming
a block size of 2K, what would be the size of the bitmap header if I create
a uniform LMT with datafiles of size 32K, 64K, 100K, 1M and 64M?
Metalink
Rajesh
Rao/CHASE@CHATo: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
SE cc
. That
was what helped me get to the right answers.
Regards
Raj
Rajesh
Rao/CHASE@CHATo: Multiple recipients of
list
ORACLE-L [EMAIL PROTECTED]
SE cc:
Sent by: Subject: Bitmap
How about logminer?
Igor Neyman
ineyman@perce
The rollback segment will not shrink if there are active transactions in
the rollback segment i.e xacts 0 in v$rollstat for that rollback segment.
Check out which transaction is using the rollback segments using
v$transaction where XIDUSN = usn from v$rollname. The ses_addr in
v$transaction
If the column is defined as not null, then varchar2(1) and char(1) would
make no difference. Also, if the columns can accept null values, and no
values are entered, there's no difference either. The developers seem to be
under the misunderstanding that when a column is defined as char, and no
1. Data and Index segments have different storage and sizing requirements.
That's the main reason for them to be put up in different tablespaces.
2. If all indexes are in a seperate tablespace of their own, one could
avoid backing up this tablespace if time and space are a constraint.
3. The I/O
An example on my understanding of Reverse Key Indices:
Say you have a table EMP with a column EMPNO, and an index on this
field.This number is incremented sequentially for every new employee that
joins in. And as employees retire, say, the records are deleted. This
would generally mean
Hello Folks,
Given an Oracle 7.3.4 database, how would you tune a query as under, other
than suggesting a migration to a higher version. This query is currently
performing a lot of I/O, obviously doing a full tablescan on CAMPMAIN.
SELECT CAMPNAME,ASGNMTTYPE,CAMPRTGNUM, LTRIM(RTRIM(CAMPTYPE))
If I had that oppurtunity, I would lap up Cary Millsap's Hot clinic to be
conducted in NYC around July 9-11. You should be able to get more
information from www.hotsos.com.
Raj
Not a factor for this error, but what's the ORACLE_SID?
maheswara.rao@s
Thanks for the reply, Stephen. When I had a look at the Batch job
performance problems, I vented it out the same way you did. The naive
business folks were told The database is slow. Tune it.
I created a seperate schema with the culprit tables, updated the column
values to trim them, removed
This threads getting to be a lengthy one, and I might as well add my two
cents to it. I too come from the same school of thought. That you are OCP
certified holds no water with me. OK. So you are OCP Certified.That dont
impress me much.
One of my first questions, when I am asked to conduct an
The fact that the industry is trending to a managed service model for
database administration is NOT A BAD thing for salaried DBAs out there. I
think the opposite is true.
So, let us all second the Oracle sales pitch about them managing our
databases for us, and hope to sneak into Oracle ;-)
And how much time will my database take? The database size is 118Gb, the
disk transfer rate is 1 Mbps, the physical memory is 64Mb and two 486's ;-)
The 'ALTER SYSTEM ARCHIVELOG CURRENT' is the preferred one that should be
used for backup scripts.
The reason being, if you have noticed when you do an 'ALTER SYSTEM SWITCH
LOGFILE', you get the prompt back immediately. This command performs a
database checkpoint, switches to the next log,
And I charge less than Steve Adams or Jonathan Lewis (3.5 times lesser),
for 'comparable' features (could be physical). So, hire me, and Save a
million in 5 years.
Who buys that? ;-)
Raj
Hi Fellow DBAs,
I and a fellow DBA are currently debating about how insufficient space in
the archive destination freezes up the DB. He claims that as soon as the
ARCH process is unable to write to the disk, the db freezes. I am of the
opinion that it does not. It will only report an error (Any
Hello Folks,
I was teaching DBA stuff to a newbie, and I had him play with an Oracle
7.3.4 instance on an old HP box. I was teaching him about oradebug, ipcs
and how to associate shared memory segments with an Oracle Instance. After
some time, he came back and said there were 2 Oracle instances
Hello Folks,
A datawarehouse. There are only a few selected SQL's run against this
database. And all this SQL's are tuned to optimum during design with hints
embedded to take the least execution time. The SQL's currently execute in
times, much better than what the expected response time was by
Hello Gurus,
A SQL tuning question. Given three large tables with the same millions of
rows, and all three are referenced in a query, without any filter, as
under:
Select ..
from largetableA a, largeTableB b, largeTableC c
where a.empnum = b.empnum
and a.empnum =
Words of Wisdom ;-)
But, me thinks, I will go ahead and disable that once a week analyze cron
job. The CPU can be better utilized for the other night batch jobs.
Thanks
Raj
The CBO, presently does quite a good job. It chooses a sort merge join on
the tables. Given my understanding of the data distribution in the tables,
I agree its the best execution plan. But this kills my temporary
tablespace, ORA-1652. To accomodate this query, I altered the sort area for
the
I remember reading a reply from X$ Gopal to a similar question, talking
about the possible use of BBED, which is an Oracle support tool.
Raj
Thanks Jonathan, for your inputs. I am trying to understand hash joins. New
to it.
As I understand this, and looking at the plan that you have mentioned :
hash
table C
hash
table B
table A
1. Tables B and C will be hashed in parallel, and Hash table for A
By the way, when I added the ORDERED and USE_HASH hints to this query, I
did not encounter the errors about the TEMP tablespace utilization, as with
the MERGE SORT JOIN.
Thanks a ton, Jonathan, for your inputs.
Raj
John,
You seem to be one of those lucky DBAs who don't have performance issues
:)
This is just one of the 150 databases. A DBA needs performance issues,
backup failures, disk failures, 600 errors, dropped tables, et all, to
justify wages above the minimum wages law ;-)
Raj
Do you have a lot of locally managed tablespaces? Doing a read from
dba_extents can cause quite an I/O with locally managed tablespaces.
My 2 cents.
Raj
Check out the statspack viewer tool at http://www.geocities.com/alexdabr/
mkb
Do a SHOW DEF at the SQL prompt. What does it show? If anything other than
, do a
SQL Set def
Raj
Lance Prais [EMAIL PROTECTED]@fatcity.com on 01/09/2002 05:35:21
PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL
AFAIK, RMAN has a duplicate command, which will initiate a backup, copy the
concerned datafiles onto the target host, and then perform an incomplete
recovery using the archived logs. This will also generate a unique DBID for
the new cloned database. I have never used this. But am curious to
On the topic, I once had a tablespace with 300,000+ free extents and 0 used
extents. We executed a drop tablespace command, and looking at fet$ and the
rate at which it was dropping extents from the table, we estimated it would
take us 64 hours. This was on a 7.3.4 db, and we thought it better
Export doesn't generate any rollback, right, so what is it supposed to
accomplish by doing this incantation?
Sorry to press the point, but could you elaborate on how that COULD
possibly make any difference for 'snapshot too old'?
For the same reason, any other transaction could end up with
Could we do it in 7.3, 8.0? ;-) Open the catexp.sql and modify some table
creation scripts, possibly some exutab tables to say obj$.name != Table1,
Table2.
Hic !! Nooo. I did not say that ;-)
What I say is, include all the tablenames except the two that you do not
need in your parfile.
John,
I DISAGREE. The gymnastics of assigning a large rollback segment to an
export could avoid the snapshot too old error.
I agree with Jeremy when he says export does not generate rollback. But I
was trying to impress upon him that still an export could end up with the
snapshot too old
Is it a simple select statement, or is it a cursor select in an PL/SQL
block? Does her transaction itself perform any DML on those tables?
Raj
Walter K [EMAIL PROTECTED]@fatcity.com on 01/25/2002 11:15:26 AM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To:
Precisely the point I was trying to make, when I put the question if it was
a normal select, or if it was within a PL/SQL block? The myth is that
snapshot too old happens only when some other transaction was in the
process of performing an DML on a table, when you did a select on it. It
can
Jeremiah, it is.
Thanks, Kirit ;-)
Raj
Deshpande, Kirti [EMAIL PROTECTED]@fatcity.com on 01/25/2002
12:55:27 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Guys, in the next round, please correct
Use SQL%ROWCOUNT.
BEGIN
insert into table1
select * from table2;
dbms_output.put_line(SQL%ROWCOUNT);
END;
/
Executing this PL/SQL block should display you the number of rows that were
inserted into table1.
Raj
[EMAIL PROTECTED]@fatcity.com on 01/25/2002 01:20:40 PM
Please
Hey Fellas,
I have an application DBA who insists that the pctincrease at the data
tablespace should be set to 0 so that SMON does not coalesce the
tablespace. He says coalesce will be performed by using a scheduled batch
job written for that purpose. He states that having SMON to perform an
Thanks Melissa. I was wrong, I guess. For this is what I found on Metalink.
1. Because SMON acquires the Space Transaction (ST) enqueue in exclusive mode, other
processes requiring the enqueue will be
blocked. This is typically manifested by multiple oerr:ORA-1575 errors.
2. SMON sits in a
Okie. To be more specific. This is a siebel application running against a
7.3.4 database. So the 8i features for space management are out of the
question. Second, there are large tables with varied values for initial and
next extent in the tablespace. There are also going to be temporary tables
Barb,
As per my understanding of delayed block count, the definition that you
give, is apt for Oracle 7.3 and below. Your statement that it does not
clean the datablocks that were modified also holds true.
To add further, starting with Oracle 8, Oracle introduced a fast commit
mechanism,
Now, will someone tell me, if he is kidding, or is that a genuine serious
question ;-)
Raj
JoJo Al-Zawawi [EMAIL PROTECTED]@fatcity.com on 01/29/2002 03:16:07 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL
For Oracle security issues, check out the site
http://documents.iss.net/literature/DatabaseScanner/reports/oracle/OraPolicy.pdf
Plus, also there was one by Kevin Loney about security. I forget the site.
Raj
And now, all you need is a search engine.
Durinda.Jones
No, but the catch is, your database needs to be running on Microsoft NT ;-)
[EMAIL PROTECTED]
To add further, what I have read is, on Windows NT, being able to edit the
registry, could allow one to change the ORA_PWFILE value, and point to
their own password file. Hence, access to the registry should be resticted.
Raj
Remco,
When you say you cannot drop, what do you mean? Does it wait forever? If
so, your table could be made up of thousands of extents. There are tips on
Metalink on how to drop a table with a large number of extents, and to make
it run faster? Look up.
And as for the dictionary table you are
Geesh... Your Q made me nostalgic. Reminded me of the days when I used to
uuencode files and mail them over. Have a look a uuencode, and mailx.
Raj
You first need to uuencode the file. Have a look at the uuencode command.
Then, use mailx.
mailx -sSubject [EMAIL PROTECTED] uuencoded file here.
Raj
1 - 100 of 209 matches
Mail list logo
