logs archiveIRC Archive / Freenode / #oracle / 2010 / July / 9 / 1
dsdel
hmm I'm installing oracle on an english win2008 server in argentina. localization is set to argentina for the 2008 server - oracle get's that setting and is displaying itself also in spanish - can I somehow change the display language of oracle without affecting nls_lang or other territorial settings?
mator
dsdel, export LANG=en_US ?
since a database locate could/shall be different from system locale setting
dsdel
mator: will try, thx
andybe
HI, I need to manipulate a large amount of data. I had issues yesterday of the archivelogs filling up the disk. If I issue the command "alter database noarchivelog", run my DML statements and then issue "alter database archivelog", will this "turn off" archive logging and not record the changes I make (i don't care about the data/table, it's on a dev server)?
I just want to be sure Oracle won't try to "catch up" when I turn logging back on.
stede
hi ... i've got a col with the type char(8 byte) and all rows contains one letter and 7 whitespaces. how can i trim the whitespaces and set the datatype to char(1)? if i try to update the col to a single letter the whitspaces are there again. plz help
andybe
stede .. did you just update the data in the column, or did you try changing the data type to char(1)?
stede
updating ... like this: UPDATE rfm_schedule SET profile='A' WHERE profile='A ';
but.. i fixed it know
andybe
ok, yes char cols are always padded
glad you foxed it :)
stede
yea .. i done it know while changing to varchar2 reduce cols to one letter and than back to char
         

andybe
that's what I just going to suggest :)
stede
it's no problem becase it's not a running production system =)
ok =)
fine
ty
andybe
np
although I think you can change the datatype straight to char(1) and it would take the first byte .. but don't quote me on that :)
stede
andybe: no ... it raises an error that the data contained by the col is to big for reduce
andybe
aha, thanks .. I've never tried it so wasn't sure :)
Symbi0nt
is there a good way to work around the "not a single-group group function" error?
I mean I have to group on the date stuff by halfyear and also want to have it in the select clause.
hypno
so here's a stupid question: why would i only be able to select from a table (afaik) if i quote it like: select * from 'foo', ?
select * from foo; does not work and sqlplus will complain about a missing table.
Symbi0nt
if there is no table with that name
hypno
well, why does 'foo' work then?
or is 'foo' a different object? a view? some other thing in oracle which calls for special treatment?
Symbi0nt
hypno: 'foo' does not work for me.
hypno
huh?
with 'foo' i did not ment that literally, of course.
andybe
Hi, I need to manipulate a large amount of data. I had issues yesterday of the archivelogs filling up the disk. If I issue the command "alter database noarchivelog", run my DML statements and then issue "alter database archivelog", will this "turn off" archive logging and not record the changes I make (i don't care about the data/table, it's on a dev server)?
hypno
in reality, i have /something/ with a different name than 'foo', but i can only access it by giving explicit quotes around it for some reason.
prodigy
hi does anyone have experience with rman and netbackup
andybe
hypno: i've come in mid-conversation, but do you mean something like SELECT * FROM "foo" works, but SELECT * FROM FOO does not?
hypno
andybe: i have not tryied with "foo", but with 'foo', and that works while foo does not, yes.
andybe: and i wonder why that is. :)
andybe
hypno: what is the statement your trying to run?
hypno
andybe: exactly like you wrote.
andybe: select * from 'foo' works, but select * from foo does not.
andybe
any object name in double-quotes is case sensitive, so "foo" is not the same as FOO. Objects are upper-case by default. Are you sure it's single quotes your using not double quotes?
(actually, all object names are case-sensitive, oracle just uses upper case by default :)
.. in your example [select * from from foo], would be executed as [SELECT * FROM FOO], but the table name is "foo".
hypno
andybe: ok.
         

usn_work
good CEST afternoon, folks
RJarett
howdy
andybe
anyone got an knowledge of archivelog?
dsdel
only you know what is inside the archivelogs :D
usn_work
rofö
rofl
andybe
lol
let me rephrase .. has anyone got any knowledge of how archivelog works?
:)
RJarett
im sure lots of people do
have you even bothered to look at them?
andybe
not what's in them no
RJarett
so why bother asking before you even try that?
stede
andybe: strange ... now i cant change the datatype of the col from char to varchar
usn_work
andybe, asking such questions leads to nothing. If you have a problem, ask about it
andybe
i have
.. but no answer :(
usn_work
<RJarett> im sure lots of people do
=> answer
RJarett
andybe: do you know how to open a file?
its really as easy as that
dsdel
andybe: if you have problems understanding the process of creating/managing and restoring with archive logs: http://tahiti.oracle.com/
andybe
yes I know how to open a file, but I'm not interested in what's in it
RJarett
Then why did you ask?
dsdel
so what do you even want to know?
:D
stede
what the hell is going on here o_O
RJarett
You asked how they work. whats in it will show how they work.
dsdel
omfg!
RJarett
If you dont even what that level, you should change the question
andybe
sorry, my question above explained, but I cut it down too much
hali
metaquestions
andybe
i want to be sure that if I turn it off, when I turn it back on it won't try and "catch up"
RJarett
turning it off can be a bad thing.
if you turn it off, and then back on, you will need a full backup after you turn it on
dsdel
naturally it won't 'catch up' - you turned it off
hali
no it won't try tro catch up, but it does render your old logs useless
andybe
it's a dev database
RJarett
and can only use them at the point of backup + new one
nothing prior
stede
how to change the type of a col from char(8) to varchar(1) .. it contains only one letter, but because of the char(8) type it was filled up by 7 whitespaces. if i try it, oracl says, that the col(so the table) has to be empty for change the type, but thats not possible for me ;)
RJarett
archive logs are simply binary versions of sql ran during that point in time
andybe
sorry, I'm not a DBA, i'm very new to this side of oracle
RJarett
stede- dbms_redefinition, or add a new col, update it w/ a trim() and drop the old column
andybe: heres an example. you have a backup as of yesterday morning + archive logs
if you restored the backup, how much data would you have lost?
andybe
1 day
RJarett
so.. what do you do it you want to get the db to the point right before the restore?
you apply the archive logs
andybe
use the archive logs?
:)
RJarett
if you are a sysadmin, think of snapshotting a filesystem. changes after the snapshot need to still be stored somewhere. so archive logs are like the change space
andybe
ok, so if I turn it off, I will only be able to restore to the last backup?
RJarett
yes
if you have one
andybe
yes we have daily backups
RJarett
even with archive logs off, you still generate redo
andybe
incremental
RJarett
well theres no good incremental backup system for oracle w/o arch logs
andybe
+weekly full backups
hypno
hmm. so an "archive log" is bascially a redo-log file? ie, it is full with transactions you can apply to the latest real backup to get to the current state, eh?
andybe
hypno: that how I understand it?
RJarett
hypno: well during that time period. current state requires all arch logs from the start of the backup on
hypno
alright.
andybe
i got it
so redo logs are different from archive logs?
RJarett
sort of, but not much
hypno
oralce folks do not use replication as a way of backup and fault tolerance?
andybe
but they will get generated even in noarchivelog
hypno
perhaps that will cost an arm and a leg, but still.
RJarett
hypno: depends. its expensive
SELECT * FROM v$log; and select * from v$log_history;
do some sh*t
and then alter system switch logfile
and keep watching it
notice what it says. itll switch the CURRENT log, and the previous will say ARCHIVING
hypno
RJarett: so you have to pay extra to get the replication functionality? not just two licenses and then go?
RJarett
hypno: yes.
replication = 2x licensing, hardware, storage, infrastructure, location, etc
hypno
well, true, but
RJarett
I just keep an image copy of my DB on another san on the box (in the FRA), and just replicate that to another site but dont attach it anywhere
plus replication is flaky and breaks easily
andybe
RJarett: I'll give that a try. The problem I had yesterday was that I updated a LARGE table and the backups where failing, so it filled up the disk! I just don't want that to happen again.
hypno
oh, heh.
RJarett
i generate up to 300gb of archive logs a night on one db. its a pain to deal with. but how much more of a pain would it be to have to recover from a minor issue?
andybe
i agree, I would not switch them off permantly or on a production db, just temp while I update one large table
(our db is a LOT smaller thanyours lol)
hali
andybe: you can run opertaions in nologging mode, which REDUCES log generation but does not eliminate it
like bulk loads
RJarett
or.. just delete the logs as they are generated, andbackup again after
hypno
another question: are there kernel modules (or whatever) to allow for filesystem like access to the database?
RJarett
no. but oracle has features like that built in
or addon stuff for it
hypno
i can sort of imagine doing: cd /oracle/instance/table/column/ ; rm *
RJarett
we were doing that at oracle even back in 97 w/ IFS/rawiron (internet file system)
well thats not how it works
theres no way to have idiot friendly commands for those who dont want to learn sql
hypno
heh
stede
RJarett: thanks
andybe
hali: I think the penny's finally dropped .. the archive logs a simply an "addition" to the backups", if I delete them, the only damage will be that I can ONLY restore to the last backup?
RJarett
well, you also cant fix block corruption or flashback
andybe
RJarret: a backup would not fix block corruption? (i don't know what flashback is)
RJarett
a backup would totally blow away the old file, and drop in the backed up database
what if you just had 1 or 2 corrupt blocks in a datafile, would you want to have to restore back to the backup of the whole db?
i wouldnt
andybe
not under normal circumstances no, I agree. But on a dev db with a small amount of data, I wouldn't be too bothered, to be honest I could just re-import the dump files :)
RJarett
dev is where you learn how to dba
andybe
lol .. i'm definatley learning loads here!
i went from a large company that had loads of DBA's .. to a small company with, well, just me.
RJarett
yeah you are a developer arent you?
andybe
yes
RJarett
figures
worst mistake a company can make
andybe
we have DBA's we can use at the hosting company, but I just want to learn some basic stuff
RJarett
because not only do devs lack all concept of how a system works, they are incapable of handling the concepts of tuning and performance
hypno
incapable? that sounds like /a bit/ of exaggeration.
RJarett
no.. really it isnt.
hypno
to the countrary, as a developer, my own experience of so called oracle consultants, let alone DBAs, are that they can talk about but do little. :P
about=a lot. duh.
RJarett
companies that hire "consultant" dbas are suckers
usn_work
(Action) likes those suckers
hypno
(Action) too
usn_work
they are putting bread in my jar. Only a bit, but they do
hypno
then again, i am a consultant developer. that makes me twice as evil. *G*
RJarett
omfg i just found a backdoor in the system that Cisco and EMC both used to host the virtual part of their conference
lets you download all the session material other people have had the system zip for DL
no wonder cisco live also lost attendee data to a hacker
crappy 3rd party solutions
matelot_
"consultant" dbas == BS
power
hi
BLZbubba
matelot_: heh indeed
executives seem to love them though
I'm getting ORA-01031: insufficient privileges trying to start the db; I have an oracle user and a dba group; should that be all I need? I nuked the oinstall group
all oracle files are owned as oracle:dba
RJarett
who are the datafiles owned as?
and how are you logging into sqlplus?
BLZbubba
sqlplus /nolog, then conn /as sysdba
but this error happens with the dbstart script
everything is oracle:dba
someone said that i could get by with no oinstall group on the machine - was that bad advice?
guess i should make oinstall the default, and dba secondary?
i also get the "warm started" message
RJarett
you failed to answer the questions
if you installed with the dba group as oinstall, you need oinstall or you need to make changes. if you installed w/ the dba group as dba you do not need to
BLZbubba
ah, /u01/app/oracle/product/11.2.0/db01/rdbms/lib/config.c shows the actual groups
i named the group "oracle"
which should really confuse people i guess :(
including me
ok thanks
RJarett
huh? renamed the group huh
just keep digging the hole
BLZbubba
heh yeah i need to reinstall clearly
is there a "standard" for where the app directory goes?
RJarett
google: oracle flexible architecture
mkdir -p /u01/app/oracle ; ln -s /u01/app /app
« prev 1 2 next »