logs archiveIRC Archive / Freenode / #oracle / 2010 / May / 6 / 1
eoliva
My app is trying to insert a row in my table but theres a check constraint error...how can I see the details of what my constraint does?
zasz
dba_constraints
Rupan
I have an SQL database on an external disk that I need to mount read-only. I'm completely new to Oracle, having installed it two hours ago, so please be gentle
I am somewhat familiar with MySQL, if that helps
TheBonsai
is the other database an oracle database?
Rupan
yes
it was given to me on a disk
I have been given an oracle database on an external drive that I need to issue some queries to
but, I should not make any changes to the database
only issue queries
SELECTs
TheBonsai
a full database? i.e. controlfile, datafiles, logfiles?
Rupan
yes
I am strictly not allowed to modify the database and must mount the partition read-only if possible
TheBonsai
write an init.ora file with the relevant parameters (especially the control_files=....)
Rupan
do you have documentation about init.ora?
         

TheBonsai
problem: the control file has the data files registered using their name
which means you either need a new control file or the database at the same location (file names/path)
Rupan
I can arrange for path names to be identical
TheBonsai
ok. did you already have a database setup with your own installation?
Rupan
yes
I have a dedicated machine on which to do this work
TheBonsai
then take this init file as example
but i'm not sure if you can mount the partition r/o, i.e. if oracle can read-only access the controlfile
you shouldn't need any big changes to that init file. the init file just configures the runtime parameters of the instance, i.e. logging aand stuff, and, most important, where's the controlfile
the init file doesn't organize the database, this is done by the controlfile
and: maybe you need to set the compatible=... parameter, if the database was created with aanother oracle version
Rupan
which init file?
TheBonsai
you created a database with your local installation?
Rupan
yes
TheBonsai
then look at $ORACLE_HOME/dbs
there might be a initBLABLA.ora
wwhere BLABLA is your SID
Rupan
orcl
TheBonsai
copy that over to another filename and edit it
do you know the database name of the new/given database?
Rupan
sorry, my attention is split between the terminal and window
yes I do
funny
in my default installation I have no initora.orcl
TheBonsai
it should have created an init file (the dbca)
Rupan
there isn't one
TheBonsai
do you have any init* files there?
Rupan
initdw.orca
init.ora
init.bak.ora
TheBonsai
take init.ora
Rupan
so I'm looking at init.ora
db_name=DEFAULT
is that what I should be changing?
the other stuff look like they don't need modification
TheBonsai
(edit a copy!)
Rupan
yes of course
         

TheBonsai
db_name, control_files and maybe the log pathes if you need it (which version is that?)
and memory limits, if you run it in parallel with another instance
db_unique_name if set
you may pastebin the file
Rupan
TheBonsai: I found an init<dbname>.ora in the database files
is there a way to mount a database that has been copied to the appropriate place?
TheBonsai
"mounting" a database just means (in oracle terms) that the software opens the control file
if everything is in its place (the control file names all database files), then yes
you can use this initfile with a pfile=.... parameter to the startup command (don't just startup, startup nomount pfile=.... first)
in the "nomount" stage, just the software is started
"mount" stage opens the controlfile (but not the database)
"open" opens the database
(but don't open it for now, just NOMOUNT it, step by step)
Rupan
TheBonsai: I've copied init<dbname>.ora to 'dbs'. Now how do I use it?
TheBonsai
startup nomount pfile=/path/to/the/initfile
ah wait
export ORACLE_SID=blabla
before
then: sqlplus / as sysdba
it should say you're connected to a cold instance (not started instance), something like that
Rupan
oh, sorry
I had to reread your comments a few times before it registered
TheBonsai
if you have an sqlplus console with the right ORACLE_SID set started, and it says you're connected to an instance that's not started, then tell me
hm. iirc the term is "connected to an idle instance" or so
ORACLE_HOME and ORACLE_SID are essential. they specify which installation to use and which instance to connect to
Rupan
yes we sourced the provided oracle init script
TheBonsai
so thee instance is in NOMOUNT andd ready?
(SELECT STATUS FROM V$INSTANCE;)
Rupan
hold on
TheBonsai
sorry for the double-letters
Rupan
having issues with the init file
TheBonsai
laptop keyboard, cheap sh*t, etc
Rupan
:)
the init file is not loading properly
we're fixing it right now
yes the instance is in NOMOUNT and ready
TheBonsai
ok, then ALTER DATABASE MOUNT;
Rupan
now can we set read-only?
TheBonsai
and watch out for errors
Rupan
"error on write to control file"
dsdel
bad error :/
Rupan
well, the filesystem is mounted read-only
TheBonsai
that's what i said before somewhere
Rupan
is there a way to set the database to read-only state?
TheBonsai
<TheBonsai> but i'm not sure if you can mount the partition r/o, i.e. if oracle can read-only access the controlfile
Rupan
ALTER DATABASE MOUNT READONLY; ?
TheBonsai
you can open the database read only, but not the controlfile
Rupan
okay, that makes sense
is that correct, READONLY?
TheBonsai
READ ONLY is an option for the OPEN open mode
the controlfile holds locks the database for example (exclusive database mount)
the instance must be able to write to the controlfile
you might try to work with a copy of the CF, but i never did that
i don't know if the file headers of the database don't get updated or something like that. in theory not
(i mean even if the datafile contents are unchanged, the headers might be updated, what do i know, "last open date" or whatever oracle thinks)
SHUTDOWN the instance, make a copy of the CF to a writable location, change control_files parameter
leave the filesystem readonly to be sure
and i have no clue how the redo stuff will do (even in READ ONLY status), maybe it will complain that it can't write the redo logfiles
Rupan
okay, ALTER DATABASE MOUNT completed
TheBonsai
ew
ALTER DATABASE OPEN READ ONLY;
if the redo stuff is a problem, you'll see it now
dsdel
hmmm I've got a database running on oracle unbreakable linux and trying to set up the emca
but it fails and tells me that the database is not registered at the listener (tnsping works)
the strange thing is that i've got 2 instances from lsnrctl ser
Instance "archdb", status UNKNOWN, has 1 handler(s) for this service..
Instance "archdb", status READY, has 1 handler(s) for this service...
configured only one in listener.ora and tnsnames.ora
TheBonsai
try restart the listener and after that ALTER SYSTEM REGISTER; from the instance
dsdel
http://nopaste.info/b31aeef969.html
TheBonsai: tried already but will try again hehe
not that I made some mistake somewhere
TheBonsai
is the SID configured statically in the listener.ora?
dsdel
uhsame error
not if I would be aware of - the listener.ora is pasted at http://nopaste.info/b31aeef969.html
TheBonsai
it's the SID_LIST, that's a static registration- maybe that's your UNKNOWN instance, and the READY instance is the automatic registered one
dsdel
and it's 11gr2 x86_64
remove for a try?
TheBonsai
but if the set oracle home etc is correct, it should be the same and not 2 of them
yea, comment the SID_LIST_<listenername>
dsdel
ORABASE_EXEC=/opt/oracle/11.2.0/dbhome_1/bin/orabase
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/11.2.0/dbhome_1
ORACLE_SID=archdb
ORAHOME=/opt/oracle/11.2.0/dbhome_1
ORASID=archdb
it's allt he same - triple checked
same error - did the reload with lsnrctl rel
and afterwards alter system register
lsnrctl ser shows now:
Service "archdb" has 1 instance(s). Instance "archdb", status READY, has 1 handler(s) for this service...
TheBonsai
looks better than before
yes?
i.e. only one, READY instance
dsdel
yes but emca still fails
the strange thing is that emca seems that it can connect at first to the database
repos was created and afterwards it stopped
TheBonsai
i'm not an EM expert, sorry. this one issue just was obvious, but it wasn't your real problem
dsdel
racle.sysman.emcp.exception.EMConfigException: Listener is not up or database service is not registered with it. Start the Listener and register database service and run EM Configuration Assistant again . at oracle.sysman.emcp.ParamsManager.checkListenerStatusForDBControl(ParamsManager.java:3245)
linux get's going on my nervs :D
oh I found an error
May 6, 2010 7:37:12 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
CONFIG: ORA-01031: insufficient privileges
o0wtf
under the same user who is able to conenct with sqlplus / as sysdba..
TheBonsai
SYS AS SYSDBA is alwways able to connect to the instance
dsdel
i think i found the mistake... :D
TheBonsai
EM surely doesn't connect as SYS
dsdel
yes found the error...
no password file was created for that instance
thanks TheBonsai :)
TheBonsai
aaaah
oracle...
heh
dsdel
should have checked that earlier
working now since 10 years with oracle and didn't look for the password file...
but as an apology! i never had a database until now without password file :D
Rupan
TheBonsai: it looks like something called total recall was used and we were not given the backup files
TheBonsai: is there a way to disable total recall during startup?
TheBonsai
total recall
uhm
flashback stuff?
Rupan
yeah
dsdel
TheBonsai: no
it's a new 11g feature
Rupan
this is a 10g database
dsdel
Oracle Total Recall, part of Oracle's comprehensive portfolio of database security solutions, works with Oracle Database 11g, Enterprise Edition to help companies store their data in secure, tamper-proof databases while keeping it accessible to existing applications. Oracle Total Recall requires no application changes or special interfaces and provides an optimal storage footprint. Managing historical data should no longer be an onerous task. Oracle Total Recall pr
Rupan
thats interesting
we were told that this is definitely a 10g database
dsdel
but ye based on flashback iirc
Rupan
okay
TheBonsai
total recall is a part of the flashback stuff (total recall is just a marketing name IIRC)
dsdel
The main difference between flashback and Total Recall is that with Total Recall data will be permanently stored in an archive tablespace and will the only ages out after a user defined retention time.
TheBonsai
and it exists in 10g, maybe it was extended in 11g, quite likely
Rupan
so, if there is flashback enabled and we do not have the necessary files for startup then the database is useless?
TheBonsai
(the marketing name "total recall" existed in 10g, i mean, iirc)
you can try to ALTER DATABASE FLASHBACK OFF; but i don't know if that will do it on a readonly database (you need to do it in MOUNT state anyways)
i mean "if that will do on readonly datafiles"
Rupan
we've accepted that there may be data loss since there was no other way to mount
and remounted the partition read-write
TheBonsai
and for the database version: if the instance is a 10g, and the database is a 11g, it will not open it
so the versions likely match
Junior
yello :)
TheBonsai
m00 Junior
Junior
can anyone think of a faster query for: DELETE from my_table WHERE parent_id=1 and Description='MyDescription';
?:D
TheBonsai
Rupan: by the way, regarding your second line at the beginning, you really do well ;)
« prev 1 2 3 4 next »