logs archiveIRC Archive / Freenode / #oracle / 2010 / July / 10 / 1
cheboygan
anyone listening on this channel?
Ramen
I am but i'm stupid
cheboygan
know anything about open world?
Ramen
no sir
ech0s7
hi
can i create an alias for an user ?
hyphenex
hi guys, I've got a quick question. I've got a table where the primary key is a number, the first item I try to 'insert' into this is where this key's value is '0', the insert is successful, but the 'ID' field for that record is then '1'. are '0' values not aloud as keys?
I guess my problem is I'm doing something like INSERT INTO institution (ID, NAME, NAMESHORT, REALADDRESS, POSTADDRESS, CITY, STATE, POSTCODE, TELEPHONE, FAX, ABN, HIDDEN) values ('1', 'Private', '', '', '', '', '', '', '', '', '', '0');.. and the ID ends up being inserted as '1'.. which is a problem
RJarett
keys can not be duplicated
hyphenex
Sorry, this is what I'm trying to insert. INSERT INTO institution (ID, NAME, NAMESHORT, REALADDRESS, POSTADDRESS, CITY, STATE, POSTCODE, TELEPHONE, FAX, ABN, HIDDEN) values (0, 'Private', '', '', '', '', '', '', '', '', '', '0');
Ohhh, would the value '0' be like an autonumber?
not the integer literal 0?
RJarett
oracle doesnt have autonumber
those make people stupid
         

hyphenex
well& if I run that insert statement many times then run a select, I've got auto-incremented ID's, is it possible a trigger could be set up by Doctrine (my ORM) to treat ID '0' as the auto-increment number?
RJarett
did you setup this db or someone else?
hyphenex
Doctrine did. I just had to build the schema and relations in yml
then it generated the SQL
RJarett
look at the table structure and for triggers
we cant magically guess what a sh*tty app is doing
hyphenex
lol, ok, thanks
RJarett
but if its trigger based, then you wouldnt want to specify the id field or a id at all
hyphenex
I think this is the trigger.. CREATE SEQUENCE INSTITUTION_seq START WITH 1 INCREMENT BY 1 NOCACHE
RJarett
thats not a trigger
hyphenex
ahh, but if I used that sequence, then I'd be in the sh*t if I was migrating data over from an access database with already a bunch of ID's
RJarett
you will be anyway
hyphenex
Why's that?
RJarett: it looks like it takes care of the sequences/auto number in this trigger http://paste2.org/p/909811.. that's very pretty, the main problem I can see though is that my primary number starts at index base 0, not 1 :(
usn_work
hi folks
kingping
Hello folks
cheboygan
hello
kingping
Do I need to include archive logs in my backup sets while performing incremental backups ?
cheboygan
probably not a bad idea
kingping
Why?
The only idea in my head about it is redundancy.
cheboygan
hard to answer an individual question about backups without knowing the whole picture for your environment
if it's a hot backup though, you need current logs to recover it
no matter what
incrementals can be both hot and cold
i'd assume you're probably doing a hot incremental
kingping
Well, I'm palling to have retention recovery with 7 days window.
An open backup, yes.
*planning
cheboygan
are these backups to disk, or are they sent off the system?
         

kingping
To disk atm.
cheboygan
if the whole backup is locally (e.g. to an FRA) then it doesn't really matter since archivelogs are already there.
other than maybe redundancy, like you said earlier
kingping
I'm not thinking of constant backups sent to tape now.
cheboygan
rman?
kingping
Yep, rman.
cheboygan
what version? 11g?
kingping
Yep 11gR2
cheboygan
i'd suggest using the FRA -- oracle is pretty good at self-managing a lot of this stuff these days
kingping
I've been thinking of whole backups once a 7 days with daily incremental backups.
FRA ?
cheboygan
sounds like a good plan
FRA = flash recovery area
just another way to manage where the backup go
kingping
Yep, they go there, AFAIK :]
$ORACLE_HOME/recovery_area
cheboygan
DB_RECOVERY_FILE_DEST init param
kingping
So I'm using it. YEs, that very parameter.
and ...DEST_SIZE
IIRC
cheboygan
do you use grid control?
Dormir
to answer that initial question about archivelogs in incremental backups, that's a definite yes
cheboygan
another thing - might seem obvious, but don't forget to enable block change tracking
(kinda obvious and easy, but sometimes it slips your mind and then later you can't figure out why the incrementals are taking just as long as fulls. <g>)
kingping
cheboygan: I'm not on EE :/
Dormir: Why archive them?
s/archive/back up/
I"m not using Grid, only Database Control.
cheboygan
i was thinking you still get database control with std
Dormir
because when you recover, Oracle has to have all of the datafiles recovered to the same point of time
cheboygan
yes but his archivelogs are already on the disk
kingping
cheboygan: Yes, the Enterprise Manager Database Control web interface, it exists here.
Dormir
the way it does that is after it applies the incremental, it will apply archivelogs to get all of the files to that point
kingping
But when I do incremental backup, I'm removing archivelogs, no?
Dormir
cheboygan: you can't assume they'll be on disk
cheboygan
you absolutely need to include them if you move your backups off the system, but i don't think it's strictly necessary if it's local -- although redundancy doesn't really hurt anything either -- and also i'm not sure what would be considered "best practice" (whatever that's worth)
kingping
Since I don't need them anymore.
Dormir
kingping: you shouldn't unless you back them up first. you'll need them when you recover
so, you run a backup on Monday, you need to recover the database the next Monday. you're saying your archivelogs will be on disk that long guaranteed?
cheboygan
the rman policies for retention and deletion can also impact this
Dormir
if you can guarantee that, then that's fine. personally, there's no way I'd trust that. I like to know for a fact I can recover in any situation
cheboygan
if your rman policy is a 7 day window, then oracle will not delete archivelogs before that window - AFAIK
Dormir
it will if oracle needs the space in the FRA and the archivelogs are backed up
cheboygan
not if it violates the policy
oh right -- if they're backed up!
Dormir
trust me, it will
it happens in my current system every day
cheboygan
but if they're backed up then it's not an issue :)
will it delete them if they're not backed up?
Dormir
but why would you trust that they'll be on disk? what if the disk crashes?
kingping
Dormir: Well, I suspect you imply that you'd like to recover to the most recent point in time, that's because you'd like to backup archive logs too. Correct?
cheboygan
if the disk crashes then all his backups are gone too - his backup is in the same place as his archivelogs anyways
Dormir
no, I backup datafiles, archivelogs, and controlfiles because that's a proper backup.
kingping
But if I only want guaranteed recover to one of the latest 7 days, then incremental backups with no archive logs included in backup sets will do for me. Right?
Dormir
kingping: you still need archivelogs
cheboygan
you need the archivelogs. but if you're putting your backup in the same place as where the archivelogs already are (which is what i gathered), then including the archivelogs is just extra redundancy.
Dormir
also saves space since the backups are compressed
cheboygan
unless as Dormir is suggesting, oracle will delete them even if they're not backed up
Dormir
no, I said it will delete them if they are backed up
cheboygan
i don't think there's backup compression in std edition is there?
ok
or maybe that's just gzip compression, i don't remember
kingping
bzip2 in edition one, afaik
Dormir
a proper database backup includes datafiles, archivelogs, controlfile, and the init.ora or spfile
kingping
Dormir: Even incremental?
Dormir
yes
kingping
Okay, I'll believe you :]
cheboygan
I think I'd go with Dormir on this one
Dormir
if you want to trust something less than that, that's fine. but you leave yourself open to issues
cheboygan
you can exclude the archivelogs if you know what you're doing, and if it's careful and tested.
kingping
I'll have to discover why. There's a Backup Guide, right?
cheboygan
http://download.oracle.com/docs/cd/E11882_01/backup.112/e10643/toc.htm
http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/toc.htm
Dormir
but why add that headache when all you have to do is back them up? I'm not sure why you're set against that
cheboygan
i'm not so much set against it.
kingping
The reason I'm asking is why I need to do incremental, when I just could include all archive logs.
cheboygan
faster recover time
with incremental
also, much faster backup time
and less resource intensive
kingping
Yes
cheboygan
you also might look at incrementally updated backups. cool new feature.
Dormir
I think that depends on the situation (faster and less resources). I seem to remember reading articles that says one is not always faster than the other
cheboygan
incremental vs full?
Dormir
yes
cheboygan
there are some factors -- but generally (with proper config) I think it's safe to say that incremental is a lot faster than a full
kingping
Probably has to do with lot of data or rare incremental backups.
cheboygan
well i've worked in some pretty large environments... depends on change rate too. i've seen environments where the change rate was so high that it significantly reduced the advantage of incrementals.
it's simple though -- an incremental only visits the blocks that changed since the last backup. a full visits every block in the DB.
Dormir
Myth #2: Incremental backups will take significantly less time to backup
than full backups.
Unfortunately, this cannot be said with 100% accuracy. While an
cheboygan
there's a plain I/O advantage
Dormir
incremental backup MAY take less time overall than a full backup of the same
datafile, the process by which RMAN checks each block for changes does not
differ between a full backup and an incremental backup.
from metalink
cheboygan
that's out-of-date bro
that's why you MUST enable block change tracking for incrementals
with BCT, it is different
kingping
Yep.
cheboygan
and that feature has been around for years
Dormir
that's as of last year. might be incorrect
cheboygan
what's the note ID ? i'm curious :)
Dormir
134214.1
you can configure it to work faster with BCT
just remembered the article...hadn't read it in awhile :)
cheboygan
note says "modified [checked for relevance] june 09"
it's probably a bit older
Dormir
yeah, last year
cheboygan
Also, this note is at the bottom of myth 2: "10g introduces Block Change Tracking which allows us to track which blocks have
changed since the last incremental backup. Use of the Block Change Tracking
feature will give very fast incremental backup performance as we then do not
need to scan the entire file for changed blocks."
Dormir
yep, agreed. like I said, it's been awhile since I read that note
kingping
Ok, suppose I have 4 archive log files per day, and they were mostly (70%) the result of UPDATE operation. With incremental backup I only get deltas to the blocks. Could it mean that it's too expensive to have these archive logs included rather than making a whole database backup?
(while including these archive logs with daily incremental backups)
Sorry for my poor english.
cheboygan
too expensive? ... reliable backups are priceless. :)
kingping
I imply substituting them with whole backups ;]
cheboygan
the question of whole vs incremental backups has no bearing on the question of including archivelogs.
you need them either way.
kingping
Okay
cheboygan
(for a recovery)
as dormir said, a complete backup consists of datafiles, controlfile, init/spfile and archivelogs.
some people include the archivelogs in the backupset. it's not a bad idea.
regardless, you NEED them... they are part of your backup.
kingping
I see. Thanks.
cheboygan
clear as mud? :)
if you're not sure, then i'd include them in your backupset.
kingping
:D I'm new to Oracle
cheboygan
hey
something i'd suggest is poking around in database control
i seem to remember an interface there for configuring backups
i think that it will take care of a lot of these details for you, and usually makes the right choices
kingping
Yep, it's not so advanced, but rather have two options as to how to back up the database.
cheboygan
it's been a long time since i looked at it
you might not learn as much as doing it by hand (and i understand the advantage of that!)
kingping
I'm trying to remember whether it allows to make backups with recovery window.
cheboygan
but FWIW it's usually safe
man, i would think that would be in there
might be hard to find though
kingping
Okay, thanks everyone, you're great chaps :] I'm going to hit the bedbug.
cheboygan
where are you?
or, what time zone?
kingping
Russia, Komsomolsk-on-Amur, +10 GMT
cheboygan
ah -- it's midday here :)
kingping
.]
jumperboy
in tnsnames.ora, does failover=on depend on using a SERVICE_NAME, or will it also work with a SID?
unfortunately, i don't control the instances, so i can't test it on my own
RJarett
it calls to a 2nd connect string that must be tnspingable
well really whats your failover_mode?
in your case, do you have 3 addresses in your list?
or 2?
im trying to remember which setting you are working with
theres like failover=on loadbalance=on with multiple addresses multiple listeners to the same instance
« prev 1 2 next »