A reader, August 27, 2004 - 4:55 pm UTC
Tom,
Also what processes need to be terminated before a shutdown? There was an occassion a process wasn't terminated (it wasn't known at that time that this process was using oracle) and shutdown immediate was used. This caused problems. Is there a formal way to know it? or just look for process in unix using ps?
August 27, 2004 - 5:33 pm UTC
if processes are connected to the database and you want to shutdown, then shutdown immediate|abort it called for.
"this caused problems" is far too vague to say anything else.
A reader, August 27, 2004 - 5:38 pm UTC
Tom,
The process kept on using oracle even after shutdown immediate was used. This resulted in thousands of messages to the log and eventually resulting in a space issue. It wasn't known until that time that the process was infact using oracle. Also what was puzzling is that shutdown immediate was used. I am concerned a similar situation might arise.
August 27, 2004 - 7:23 pm UTC
huh?
what "log"
what "messages"
the devil is in the details -- so, give the details.
Oracle Developer
But I am not able to shutdown Database(10g) from the home page.(URL)
Dawar, August 27, 2004 - 6:46 pm UTC
Tom,
I can logon to Oracle DB 10g through DB console or
sqlplus without any problem.
But I am not able to shutdown DB from the home
page.(URL)
If I clicks on Shutdown button.
Its brings me 500 Internal Server Error page.
Its does not effect any thing.
I can come back to my home page by clicking on back
button.
Any help will be appreciated.
Note: I am able to shutdown database from sqlplus.
OS:SLES8
Regards,
Dawar
August 27, 2004 - 7:32 pm UTC
please contact support for that one.
A reader, August 29, 2004 - 5:30 pm UTC
Hi Tom,
We first administered "shutdown". It didn't shutdown even after 45 mins. The following is found in udump trc file
*** SESSION ID:(10.1039) 2004-08-04 14:50:10.966
SHUTDOWN: waiting for logins to complete.
*** 2004-08-04 14:55:22.035
SHUTDOWN: waiting for logins to complete.
*** 2004-08-04 15:00:33.105
SHUTDOWN: waiting for logins to complete.
*** 2004-08-04 15:05:44.165
SHUTDOWN: waiting for logins to complete.
*** 2004-08-04 15:10:55.224
SHUTDOWN: waiting for logins to complete.
*** 2004-08-04 15:16:06.284
Then in the same window after ^C was done, "shutdown immediate" was given. This shut the database down. Before shutting down, some processes were terminated using kill. But after shutdown immediate was used, one of them was still trying to use oracle. That process wasn't killed using kill because it wasn't known at that time. Due to this, it sent thousands of messages to syslog. The syslog filled up soon and was only noticed when the filesystem space was getting full. I am not able to provide any syslog messages because they are currently not existent in syslog.
Why did this occur?
August 29, 2004 - 6:21 pm UTC
that is to be exactly expected? that is the documented behaviour here.
sorry if you have a rogue process that is writing to syslog, that has nothing to do with Oracle though.
What happened is:
a) someone you work with wrote a program
b) this program logs itself to syslog
c) this program got logged of of the database -- and started logging an uncontrollable amount of records because of that.
this program has what is known as "a bug" and needs to be corrected to detect that "hey, database isn't up, lets log that and exit"
A reader, August 29, 2004 - 6:32 pm UTC
Thanks very much for the reply Tom.
But when shutdown immediate kills processes, why was this not killed automatically? My question is do we have to kill every process manually? If so is there a oracle way of doing it? Thank you again for the attention
August 29, 2004 - 7:28 pm UTC
shutdown immediate doesn't kill processes, it terminates the database.
if you shutdown immediate -- does the sqlplus session you have (or any sqlplus "client") get killed? No - we do not kill "clients", that would be very rude.
processes -- client processes running where ever on the network -- they are not our "domain".
You have a bug in a developed application.
You need to have the developer remove the infinite loop it gets into that they coded into their application.
We cannot kill clients - the client could be a windoze program connected to a unix machine. the client could be a program on a mainframe running against a windows server. In short, the client lives anywhere in the network -- we cannot kill it -- we should NOT kill it. The client very politely recieves an error message and what the client decides to do with it is 100% up to the client.
Oracle Developer
Dawar, August 30, 2004 - 4:10 pm UTC
I have installed Oracle 10g on SuSE Linux Ent. Server 8.
I think Initially I entered incorrect OS password and sys password on the logon screen form console.
I also checked on, Save as Preferred Credential.
Now I can logon to the dbconsole as sysdba by re-entering correct password of sys but I am not able to use
shutdown button to shutdown the database. If I clicks on shudown button its bring error page.(setDbUserPassword)
But If I logon as another user, In my case as a hr I am able to shutdown DB.By click on Shutdown button.
I also noticed when I select user sys from logon screen its bring username and password on the screen. That password is different than the original apssword. It contains ********** (with 10*) but my password is 7 characters long.
I also noticed same issue with OS password.
I need to clear this old password from memory, cached or buffer of dbconsole.
How I will clear old password from memory???
I also noticed the first two lines are indicates about the setDbUserPassword.
here is very first line from error page.
at oracle.sysman.emo.adm.instance.changeState.
ChangeStateObject.setDbUserPassword(ChangeStateObject.
java:335)
Regards,
Dawar
Oracle Developer
Dawar, August 31, 2004 - 2:52 pm UTC
Tom,
I re installed Oracle database 10g on SuSE Linux Enterprise Server 8 edition from scratch and its works.
But this massage pop up when I used my browser first time before start database.
Actually when I clicks on yes to start database.
The web browser you are using is not optimized for this release of Oracle enterprise manager. It is recommended that you use one of the following:
* Microsoft Internet Explorer version 5.5, 6.0 or higher
* Netscape Navigator version 7 and higher
*Mozilla version 1.3.1 and higher
Do you know how to upgrade mozilla or install internet explorer 5.5, 6.0 or higher or SLES 8.
or on Linux.
Regards,
Dawar
August 31, 2004 - 3:07 pm UTC
i have IE 6.0 running (for the svg -- scalable vector graphics support) running on RHAS3.0 using crossover office myself.
to upgrade mozilla, goto mozilla.org and get it?
OK
Siva, September 04, 2004 - 1:30 pm UTC
Dear Tom,
What privileges should I have to startup and shutdown Oracle Instance?Even though I have sysoper/sysdba privileges,I am not able to do it.Any other privileges must
I have?
Please do reply.
September 04, 2004 - 1:46 pm UTC
they would be the only ones you need.
you must be doing "something else wrong", but without anything other than "i am not able to do it", thats about all I can say.
Problem with shutdown on 10g
Andre, November 15, 2004 - 4:43 pm UTC
Dear Tom,
I have Oracle 10g running under Solaris 9, on irregular basis I encounter strange situation: In order to do a clean shutdown our script issues "startup force" and then "shutdown immediate", this worked fine in Oracle 8i, but after migration to 10g I noticed that occasionally all Oracle processes remain in memory (PMON, SMON, etc) after shutdown completed. I can see them with "ps -ef|grep oracle". Could you please explain why is this happening and do those processes have an effect on closed DB.
Thanks.
November 15, 2004 - 9:18 pm UTC
if pmon, smon, etc are still there - the database is "up", not "down"
that would be "a problem" -- i have not seen it myself. You'll want to contact support if you see this.
Anyone done further analysis on this problem
Pramit, January 19, 2005 - 10:07 pm UTC
We are facing the same problem with 9.2.0.5. on AIX. All the background processes keep running even though oracle instance is not available. There is nothing written in alert log..damn...
January 20, 2005 - 10:14 am UTC
umm, I don't see that being the "same problem" as anyone had here.
what happened to the poster above was
a) they wrote a program, a client program
b) that program when disconnected from the database due to a shutdown did not properly handle that event
c) instead that client program wrote to a log file, filling their file system
they had a bug in developed code.
IF all of your background processes are UP - then the instance is available but the database might not be open or you might have the wrong sid or you might have the wrong tns entry or .............
but you give us nothing but nothing to work with. darn indeed.
Shutting down when RMAN is running....
yogeeraj, February 11, 2005 - 5:06 am UTC
hi
I am wondering if a RMAN backup prohibits a shutdown immediate on the database that's being backed up (target)?
I had to do a shutdown immediate on our database, but the shutdown immediate didn't complete until 2 hours later.
A scheduled RMAN backup of the database was running at the time...
how to explain this?
thank you for a reply.
regards
yogeeraj
February 11, 2005 - 8:12 pm UTC
did you test it and see what would happen in your test environment?
(this is what I would do...)
startup force
A reader, April 08, 2005 - 4:43 am UTC
startup force is shorthand for
shutdown abort
startup
This has worked fine for a long time, but the last couple of times when we have done this, it doesnt restart the database, it just shuts it down. My alert log shows
Fri Apr 8 03:49:30 2005
Starting ORACLE instance (force)
License high water mark = 242
Instance terminated by USER, pid = 28070
And thats it!
I come in and do a 'startup' manually and it starts up fine.
Any ideas?
Thanks
April 08, 2005 - 7:28 am UTC
I've never seen that happen myself, no.
difference between startup and startup force
Murali, June 29, 2005 - 8:46 pm UTC
Hi Tom,
whats the difference when we issue startup or startup force command for a crashed database, i know startup force does a shutdown abort, but if the database has already crashed with ora 600 error and if we try with start the database with startup force does it differ from startup??
June 29, 2005 - 9:03 pm UTC
if any piece of the database is still up after the "crash", the abort in the startup force cleans it up.
startup force = shutdown abort+startup
shutdown hangs
Alay, September 21, 2005 - 9:44 am UTC
Hi tom,
Suppose all users are connected to database and i give command 'shutdown normal' after login as sysdba. So it won't allow to shutdown the database until all users logout and sysdba's session will hung and wait until all user logout. If now if i realize that i have given wrong command instead of 'shutdown immediate' . So now what can i do? after giving 'shutdown normal' it won't allow to make new connection. So what would be the option to shutdown the database?
September 21, 2005 - 7:30 pm UTC
control-c -- connecting as sysdba will still work.
Start
atul, September 27, 2005 - 10:32 am UTC
Hi,
if i install oracle binaries with oracle unix user.
And if i create another unix user as "test" which is same as sidname of the db.
And if i gave test dba & oracle group.
Then can i start/shutdown oracle instnce with test user,
Whats the correct way to do it?
Thanks
September 27, 2005 - 12:01 pm UTC
the name of the user and the name of the sid are not relevant.
the user needs be in the "dba" group - the group you chose upon installation, if they are - they can connect / as sysdba and startup/shutdown
if you don't want that, remove them from the group
Database Not Started Automatically
Nazmul Hoque, November 18, 2005 - 12:41 am UTC
Dear Tom,
I have install Oracle 8.1.7 on OS windows 2000, Its working fine, Problem is only database not started automatically while i restart my server. I have to run the below from command prompt every day morning to start database.
SVRMGRL -> CONNECT INTERNAL -> STARTUP
Please note i have checked the services of my OS, and found
OracleOraHome81Agent, OracleOraHome81Datagather, OracleOraHome81HTTPSERVER, OracleOraHome81TNSLISTNER, OracleOraHome81GASLDATA (mydatabase) as Automatic and
OracleOraHome81clientcache,OracleOraHome81PIAGINGSERVER as mamual.
Please get me a solution to start database automatically when my OS start.
Thanks
Nazmul
Dhaka-Bangladesh
November 18, 2005 - 10:34 am UTC
use oradim to set the autostart for the database.
Auto Start database with OS
Nazmul Hoque, November 18, 2005 - 11:45 pm UTC
Dear Tom,
Please tell us about use od oradim and set of autostart set.
Plase help me......
Thanks
Nazmul Hoque
November 19, 2005 - 10:19 am UTC
fortunately, we document the tools - a DBA managing a database under windows really needs to know about oradim (if you don't, it is doubtful you have tested restoring your database to another machine for example!!! and that is the only thing you are not allowed to mess up - recovery!!!!)
see
</code>
http://www.oracle.com/technology/documentation/oracle8i_arch_816.html <code>
and get the oracle8i administrator's guide for windows nt if you don't already have it.
WHY NOT START AUTOMATICALLY WITH O/S W-2000
NAZMUL HOQUE, November 20, 2005 - 12:13 am UTC
DEAR TOM,
MY PROBLEM IS VERY SIMPLE. I HAVE INSTALLED ORACLE 8i release 8.1.7 on our new server HP Proliant ML350. DATABASE IS START WITH OS W/2000 ATUOMATICALLY. I NEED TO START DATABASE MANULLAY EVERY DAT MORNING. PLEASE TELL WHAT I NEED TO DO TO START DATABASE AUTOMATICALLY.
PLEASE NOTE IN MY OLD SETUP DATADASE START AUTOMATICALLY WITH OS(NT/Oracle 8i 8.1.7.
THANKS
NAZMUL
November 20, 2005 - 8:23 am UTC
(caps lock stuck? sounds like you are SHOUTING at us)
Why are you rebooting a machine every night.
But, use oradim - AS I stated right above. Get the admin guide, read the section on oradim. Do what it tells you to do, then- keep on reading about oradim and the other NT admin things you need to know.
That is my very simple answer. I have led you to the edge of the lake - please - take a drink.
Auto Start Database - W/2000
Nazmul Hoque, November 21, 2005 - 12:35 am UTC
Dear Sir,
I have read the Oradim from the CD and found below, If I am not wrong i have to delete one line form sqlnet.ora and reboot the server or start oracle services again.
Please give your export openion.
Thanks
Hoque
--------------------------------------------------
Oracle Database Service on Windows 2000 in AutoStart Mode with Logon as SYSTEM
(Bug 1397927) If you have installed an Oracle database service on Windows 2000, when logging in as SYSTEM user (Local System), with startup mode set to Automatic, it is possible that the Oracle database service starts but the database does not start automatically. The following error message is written to the ORADIM.LOG file in the ORACLE_BASE\ORACLE_HOME\DATABASE directory.
ORA-12640: Authentication adapter initialization failed
It has also been observed that the services like Oracle Enterprise Management Agent, Oracle Enterprise Manager Management Server and Oracle Internet Directory may also fail for the same reason because they cannot connect to the database.
The workarounds for this problem are:
1. Remove the line sqlnet.authentication_services=(NTS) from SQLNET.ORA or set sqlnet.authentication_services=(NONE) in SQLNET.ORA.
2. Start the database manually after the Oracle database service has started using SQL*Plus connecting as user name INTERNAL. Note that the INTERNAL user name will not be available after release 8.1.7.
3. Start the service as a specific user:
1. Choose Start > Settings > Control Panel > Services. The Services dialog box appears.
2. Select the service you want to start.
3. Click the Startup button. The Service dialog box appears.
4. Click This account and specify the user name and corresponding password.
November 21, 2005 - 8:28 am UTC
well, do you have the symptom? do you have that message in your log?
retrive datas in datafiles
khater ali, November 21, 2005 - 5:16 am UTC
Hi Tom
how are u.here we are using oracle version 8i and 9i.now i have a database backup of oracle 8i.now we are going to uninstall oracle 8i and install oracle 9i.my question is :in future if my management wants some data's which is resides in oracle 8i datafiles, shall i copy all the oracle 8i datafiles and put it into oracle 9i version and retrive data's or not.......
November 21, 2005 - 8:45 am UTC
why not upgrade the 8i database to 9i??
ora-600 error
khater ali, November 21, 2005 - 5:39 am UTC
Hi Tom
Here we are using oracle 9.2.0.1.0. as well as we using xml files to load into database.but i often faced ora-600 error in my alert log file.i contact oracle metalink there is some solutions to solve the ora-600 error,but there is no solution for my error arguments.i sent the arguments with this mail.somebody told me to upgrade the database.please give me some valuable ideas to solve the ora-600 problem
ORA-00600: internal error code, arguments: [kopu1pkl1], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [729], [1048], [space leak], [], [], [], [], []
ORA-07445: exception encountered: core dump [00000001029598F0] [SIGSEGV] [Address not mapped to obje
ct] [0x000000000] [] []
November 21, 2005 - 8:50 am UTC
You really need to work with support in order to solve this - and yes, they likely will ask you to apply the current patch sets which include many fixes for things over time. You are missing 6 patch sets as it is.
Auto Start batabase OS w/2000
NAZMUL HOQUE, November 21, 2005 - 11:46 pm UTC
Dear Sir,
I have check and found under folder : ORCLE\ORA81\Database\
FILE ORADIM WITH BELOW LINE :
ORA-12640 :Authentication adapter initialization failed
AND SQLNET AS BELOW :
======================
# SQLNET.ORA Network Configuration File: E:\oracle\ora81\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DEFAULT_DOMAIN = gasl.com
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
PLS ADVISE
NAZMUL HOQUE
November 22, 2005 - 8:16 am UTC
umm, you already debugged that right above?
You found the note
which has the solution
and verified it applies to you?
upgrade oracle 8.1.7.0.0 to oracle 9.2.0.1.0
P.M.Khater Ali, November 28, 2005 - 12:18 am UTC
Hi Tom
we are using oracle 8.1.7.0.0.now we are going to upgrade the oracle to 9.2.0.1.0 i dont know how to upgrade the oracle.i havent any books for that.shall i upgrade the oracle through DBCA or manually. Here one of my friend says that ,"DBCA have some constraints " so u manually upgrade the the oracle .what is ur comments and send me any books or links to upgrade the oracle thrrough DBCA and manually
thanks
khater
root.sh shell script
khater ali, December 26, 2005 - 4:37 am UTC
why we are running root.sh shell scripts while installling oracle in solaris.if u didnt run this what happened
December 26, 2005 - 10:51 am UTC
to fix ownerships of files.
if you do not run that, not all of the files will have the correct owership and privileges.
fetching rows in datafiles
khater ali, December 27, 2005 - 12:22 am UTC
Hai tom,
how are you.i have one doubt.see my database is in mount state.now i query V$ views .it will produce the result. now my question is, at the time of mount stage(database) the datafile are not in open stage .then where the db's fetch results for V$ views
December 27, 2005 - 9:38 am UTC
v$ views are primarily queries against in memory data structures, they are not based on "tables on disk"
oracle-platform
khater ali, January 02, 2006 - 2:31 am UTC
hi
what is the difference between unix and windows in the oracle
architecture point of view and oracle backround process point of
view
thanks
khater ali
January 02, 2006 - 9:56 am UTC
we are a single process with multiple threads on windows.
we are multiple single threaded processes on unix.
but both have dbwr, lgwr, reco, smon, pmon, mmon, etc etc etc....
so architecturally, "not much different", one uses threads - the other processes, the they are logically equivalent.
SQLNET.AUTHENTICATION_SERVICES parameter
Olaf, January 09, 2006 - 7:00 am UTC
Tom,
I have the following issue on Windows 2003 and Oracle 8.1.7 database. When I have SQLNET.AUTHENTICATION_SERVICES=nts in sqlnet.ora file my database will not open at the system start (I get error message ORA-12640: Authentication adapter initialization failed in oradim.log file).
When I comment/remove the line SQLNET.AUTHENTICATION_SERVICES=nts from sqlnet.ora the database starts, but I can't do connect as sysdba....
What is workaround if I want to have both: autostart AND connect as sysdba (I have some scripts that needs to connect to the database without password)
Thank in advance
January 09, 2006 - 8:05 am UTC
please utilize support for installation and configuration issues - especially on windows :)
connect / as sysdba
should work with or without nts assuming you are logged into the os as the software owner.
ORA-12640
Mark A. Williams, January 09, 2006 - 9:31 am UTC
re: ora-12640
You might want to check MetaLink note 136214.1 if you have not already. It contains information on the issue (including a bug number) and a patch reference.
[quote]
1397927 (not published): This bug occurs using
SQLNET.AUTHENTICATION_SERVICES=(NTS) in the sqlnet.ora, the
8.1.7.0.0 service will not open the database, and an ORA-12640 is
reported in the ORACLE_HOME\database\oradim.log. The fix is
available on MetaLink. It is Patch 1522966, for Product:SQL*Net,
Platform:MS WindowsNT. This problem occurs only on Windows2000,
not on WindowsNT.
[/quote]
- Mark
January 09, 2006 - 9:39 am UTC
Mark - thanks much.
SQLNET.AUTHENTICATION_SERVICES parameter
Olaf, January 13, 2006 - 4:51 am UTC
Thanks for your help.
You say that "connect / as sysdba should work with or without nts assuming you are logged into the os as the software owner".
The problem is, if the SQLNET.AUTHENTICATION_SERVICES=NTS parameter is not set, connect /@SID as sysdbba does NOT work (even if I am logged on as a software owner and a member of ORA_DBA group).
As far as I set this parameter a can connect. If I remove this parameter (as my backup agent requiers) the connect / as sysdba does not work again.
BTW another question: Mark recommends to install some patch for 8.1.7.0. I checked another system with oracle 8.1.7.2.1
The system have no problem with autostart regardless SQLNET.AUTHENTICATION_SERVICES parameter, but have the SAME problem not allowing to connect as sysdba without setting the NTS. Could you please explain what this parameter is for and HOW CAN I CHECK WHETHER THE CERTAIN PATCH WAS INSTALLED ON ORACLE (in this case the patch 1522966 pointed by Mark.
Thanks in advance
Olaf
January 13, 2006 - 11:12 am UTC
that is not connecting locally - when you do the /@sid - that is not a sid, that is a tns connect string, that is over the network (yes, even if you are on the same machine).
import database
khater ali, April 11, 2006 - 10:05 am UTC
Hai Tom,
while importing one dump file, i face
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
errors .
Then i give autoextension on in the relative datafile.but still now i got the same error.Please give me your suggestion
sample error lines
------------------
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 DU5Q-6B209-AA1
Column 2 DU5Q
Column 3 6B209
Column 4 AA1
Column 5
Column 6 Tens Asy- Eng Drv Belt - 1 / vehi
Column 7 1
Column 8 123457
Column 9 p
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 RFDU5Q-6090-AA1
Column 2 RFDU5Q
Column 3 6090
Column 4 AA1
Column 5
Column 6 CYLINDER HEAD CASTING - RH & LH (c
Column 7 2
Column 8 123457
Column 9 p
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 -W704693-
Column 2
Column 3 W704693
Column 4
Column 5
Column 6 BOLT - Tappet rail head bolt (8 mm
Column 7 12
Column 8 123457
Column 9 p
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 DU5Q-6150-AA1
Column 2 DU5Q
Column 3 6150
Column 4 AA1
Column 5
Column 6 Piston Rings - Top (upper comp. ri
Column 7 8
Column 8 123457
Column 9 p
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 DU5Q-6211-BA1
Column 2 DU5Q
Column 3 6211
Column 4 BA1
Column 5
Column 6 CON ROD BEARING: GRADE 1 (8 bearin
Column 7 16
Column 8 123457
Column 9 p 192 rows imported
. . importing table "WERS_FUP"
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 12127 771FBA
Column 2 12127
Column 3 771F
Column 4 BA
Column 5 EN01
Column 6 030701
Column 7 004
Column 8 EN01E10014111008
Column 9 EN01
April 11, 2006 - 4:12 pm UTC
are you undergoing character conversions here as well - from a single byte to multi-byte characterset?
cpu and RAM usage
khater Ali, April 13, 2006 - 3:30 am UTC
1)How much memory and cpu usage using for one Database Without connect any users
2)How much memory and cpu usage using for one Database.In this Database 40 concurrent users are using the database
April 13, 2006 - 7:49 am UTC
1) memory - you decide, that is something you setup. cpu - nominal (almost none) if you don't have anyone doing anything.
2) somewhere between 0 and infinity for both. Depends entirely on what you are doing.
Sorry, I know you were looking for an answer like "42" - but such an answer for a question like this does not exist.
khater Ali, April 13, 2006 - 3:58 am UTC
Hai Tom,
while importing one dump file, i face
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
errors .
Then i give autoextension on in the relative datafile.but still now i got the
same error.Please give me your suggestion
sample error lines
------------------
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 DU5Q-6B209-AA1
Column 2 DU5Q
Column 3 6B209
Column 4 AA1
Column 5
Column 6 Tens Asy- Eng Drv Belt - 1 / vehi
Column 7 1
Column 8 123457
Column 9 p
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 RFDU5Q-6090-AA1
Column 2 RFDU5Q
Column 3 6090
Column 4 AA1
Column 5
Column 6 CYLINDER HEAD CASTING - RH & LH (c
Column 7 2
Column 8 123457
Column 9 p
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 -W704693-
Column 2
Column 3 W704693
Column 4
Column 5
Column 6 BOLT - Tappet rail head bolt (8 mm
Column 7 12
Column 8 123457
Column 9 p
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 DU5Q-6150-AA1
Column 2 DU5Q
Column 3 6150
Column 4 AA1
Column 5
Column 6 Piston Rings - Top (upper comp. ri
Column 7 8
Column 8 123457
Column 9 p
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 DU5Q-6211-BA1
Column 2 DU5Q
Column 3 6211
Column 4 BA1
Column 5
Column 6 CON ROD BEARING: GRADE 1 (8 bearin
Column 7 16
Column 8 123457
Column 9 p 192 rows imported
. . importing table "WERS_FUP"
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column
Column 1 12127 771FBA
Column 2 12127
Column 3 771F
Column 4 BA
Column 5 EN01
Column 6 030701
Column 7 004
Column 8 EN01E10014111008
Column 9 EN01
Followup:
are you undergoing character conversions here as well - from a single byte to
multi-byte characterset?
importing database characterset is UTF8
and export database characterset is WE8ISO8859P1
How to resolve the above problem
April 13, 2006 - 7:53 am UTC
you are going from single byte (we8iso8859p1) whereby every character takes at MOST 1 byte to be stored
to multi-byte - where characters make take MORE THAN one byte.
When you define a column to be "varchar2(20)" - that permits 20 BYTES - you might be able to put as few as 5 charcters in that with UTF8 (I think it is between 1 and 4 bytes per character).
if you define the column as "varchar2(20 CHAR)" it would permit 20 characters (upto 4000 bytes maximum - so a varchar2(4000 CHAR) might only be able to really hold 1,000 characters).
In your case, you might be able to use the nls_length_semantics parameter to have the varchar2(20) silently converted into varchar2(20 CHAR) during your table creates - but bear in mind that changes the storage characteristics of your table.
The problem you are seeing is that when the single byte data is converted to multi-byte data - it exceeds the number of bytes you have configured oracle to use to store that column.
while importing
kahter ali, April 17, 2006 - 3:02 am UTC
so can we change the characeterset from utf8 to we8iso8859p1
or is there any other way to resolve the problem
April 17, 2006 - 8:02 am UTC
utf8 is a SUPERSET (bigger) than we8iso - so no, you cannot "change"
but yes, you can create a NEW database with the w8iso characterset and import into that.
or, you can do what I said above if you need utf8 of course.
Machine problem
Khater Ali, June 14, 2006 - 1:35 am UTC
Hai Tom,
How are you ?i have one problem.i connect one database and run one query .it produced 2 rows .but the same query i ran in some other client machine it produces o rows .How is it posibble.database and schemas are same.
for example i connect one database(iasdb) through one client machine(machine name: abc).then i ran query.Then i connect the same database(iasdb) through another client machine(mach name:def).Then i ran query.But it produces 0 rows.How is it possible.
The query is
------------
SELECT DISTINCT poh.segment1 rfq_number,
poh.creation_date rfq_creation,
rfqv.quote_num Quote_Number,
rfqv.vendor_site_code Supplier_Site,
rfqv.vendor_name Supplier,
poh.po_header_id rfq_header_id,
rfqv.quote_header_id,
DECODE (rfqv.quote_header_id,NULL,NULL,fmceas_archv12_get_po(rfqv.quote_header_id)) po_number,
SUBSTR(pol.attribute1,1,INSTR(pol.attribute1,''-'',1) -1 ) requisition_number,
fmceas_get_attachments_pkg.get_attach_count(poh.po_header_id,NULL,NULL,NULL) attach_count
FROM po.po_headers_all poh, po.po_lines_all pol,apps.FMCPO_RFQ_VENDORS_V rfqv
WHERE poh.po_header_id=5021492
AND poh.po_header_id=pol.po_header_id
AND poh.po_header_id=rfqv.po_header_id
AND pol.po_header_id=rfqv.po_header_id
AND pol.line_type_id <> 1000
AND poh.type_lookup_code='RFQ'
ORDER BY rfq_number DESC NULLS LAST
June 14, 2006 - 8:51 am UTC
tkprof it (trace it)
something is different, either the database is NOT the same, or you are using a different user with different views of the database.
the query plan in the trace file will help us, along with the parsing user information and such.
Shutdown database on AIX at machine shutdown/reboot
monika, June 26, 2006 - 2:52 am UTC
Hi Tom,
From one of your article I learned how to auto start database on Linux machine by writing a script S99local in rc3.d directory to to point to orastart script. I've to do the same activity on AIX box and was able to locate the rc3.d directory. I wanted to confirm that is it same for AIX as well?
Also where to place the database shutdown script? In rc9.d I guess ? Please confirm.
June 26, 2006 - 7:43 am UTC
please confirm with your AIX guides?
the rc stuff is fairly standard, but you would be best served by checking out your AIX documentation
Question on startup instance as a member of OS dba group
Judy, September 26, 2006 - 11:57 pm UTC
Hi Tom,
Both OS 'oracle' user and our DBAs are in the OS dba group. We noticed that if we log into instance as individual DBA by '/ as sysdba' and startup instance (8.1.7.4), those background processes will be owned by the individual DBA but the files generated by the oracle processed are still owned by 'oracle' user on unix.
dbauser1 25590 1 0 Sep 1 ? 5:20 ora_dbw0_ORCL
dbauser1 12214 1 0 Sep 1 ? 5:10 ora_arc1_ORCL
dbauser1 25604 1 0 Sep 1 ? 0:00 ora_reco_ORCL
dbauser1 25594 1 0 Sep 1 ? 5:19 ora_dbw2_ORCL
dbauser1 25602 1 0 Sep 1 ? 0:47 ora_smon_ORCL
dbauser1 25592 1 0 Sep 1 ? 5:18 ora_dbw1_ORCL
dbauser1 25606 1 0 Sep 1 ? 41:23 ora_snp0_ORCL
dbauser1 25588 1 0 Sep 1 ? 0:41 ora_pmon_ORCL
dbauser1 25598 1 0 Sep 1 ? 34:43 ora_lgwr_ORCL
dbauser1 25596 1 0 Sep 1 ? 5:22 ora_dbw3_ORCL
dbauser1 25600 1 0 Sep 1 ? 1:30 ora_ckpt_ORCL
dbauser1 25608 1 0 Sep 1 ? 5:43 ora_arc0_ORCL
How can we explain the permission on the newly generated files (i.e. output files thru the pl/sql procedures)? We like the resulting permissions that are consistent to be 'oracle', but need to convince other users that the individual-DBA-owned processes will not cause problems.
Thanks a lot in advance for your support!
September 27, 2006 - 4:28 am UTC
well, the output files are created by the dedicated server (assuming dedicated server since I see no shared servers here), not by dbw, smon and so on.
the os processes however should not be owned by that user - what are your permissions on $ORACLE_HOME/bin/oracle, should look like this:
$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 ora10gr2 ora10gr2 94234116 Aug 4 14:37 /home/ora10gr2/bin/oracle
the setuid should be there so that:
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1262248 bytes
Variable Size 473959768 bytes
Database Buffers 125829120 bytes
Redo Buffers 7122944 bytes
Database mounted.
Database opened.
idle> !id
uid=500(tkyte) gid=500(tkyte) groups=500(tkyte),501(ora9ir2),502(ora10gr1),503(ora10gr2),504(ora9ir1)
idle> !ps -aef | grep ora10gr2
tkyte 32651 27698 0 04:17 pts/1 00:00:00 rlwrap /home/ora10gr2/bin/sqlplus / as sysdba
tkyte 32652 32651 0 04:17 pts/2 00:00:00 /home/ora10gr2/bin/sqlplus
ora10gr2 32655 1 0 04:17 ? 00:00:00 ora_pmon_ora10gr2
ora10gr2 32657 1 0 04:17 ? 00:00:00 ora_psp0_ora10gr2
ora10gr2 32659 1 0 04:17 ? 00:00:00 ora_mman_ora10gr2
ora10gr2 32661 1 0 04:17 ? 00:00:00 ora_dbw0_ora10gr2
ora10gr2 32663 1 0 04:17 ? 00:00:00 ora_lgwr_ora10gr2
Judy, September 27, 2006 - 11:08 am UTC
Hi Tom, thank you very much for your quick reply! I checked the info as you mentioned and don't understand why we have those oracle processes owned by the db starter. I expected those to be owned by oracle instead. Otherwise, what might be wrong if the individual user owns the oracle processes like what we see now? Thanks a lot.
>ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle dba 38524376 Oct 16 2005 /opt/oracle/product/ora8174_64/bin/oracle
>id
uid=24037(dbauser1) gid=203(dba)
SQL> !ps -efa | grep dbauser1
dbauser1 25590 1 0 Sep 24 ? 7:50 ora_dbw0_ORCL
dbauser1 12214 1 0 Sep 24 ? 7:09 ora_arc1_ORCL
dbauser1 25604 1 0 Sep 24 ? 0:00 ora_reco_ORCL
dbauser1 25594 1 0 Sep 24 ? 7:49 ora_dbw2_ORCL
dbauser1 25602 1 0 Sep 24 ? 0:53 ora_smon_ORCL
dbauser1 25592 1 0 Sep 24 ? 7:48 ora_dbw1_ORCL
dbauser1 4134 4131 0 10:16:43 pts/te 0:00 -ksh
dbauser1 25606 1 0 Sep 24 ? 77:47 ora_snp0_ORCL
dbauser1 6693 4134 0 10:20:13 pts/te 0:00 sqlplus / as sysdba
dbauser1 25588 1 0 Sep 24 ? 0:50 ora_pmon_ORCL
dbauser1 25598 1 0 Sep 24 ? 59:09 ora_lgwr_ORCL
dbauser1 6736 6735 8 10:20:39 pts/te 0:00 ps -efa
dbauser1 25596 1 0 Sep 24 ? 7:52 ora_dbw3_ORCL
dbauser1 6735 6693 0 10:20:39 pts/te 0:00 grep dbauser1
dbauser1 6697 1 0 10:20:13 ? 0:00 oracleORCL (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
dbauser1 25600 1 0 Sep 24 ? 1:58 ora_ckpt_ORCL
dbauser1 25608 1 0 Sep 24 ? 9:13 ora_arc0_ORCL
>more /etc/passwd | grep oracle
oracle:x:301:203::/home/oracle:/usr/bin/ksh
>more /etc/passwd | grep dbauser1
dbauser1:x:24037:203::/home/dba/dbauser1:/usr/bin/ksh
>more /etc/group | grep dba
dba::203:oracle,dbauser2,dbauser3,dbauser1,oraapp2
and, no OS setting preventing setuid from working
Judy, September 27, 2006 - 2:11 pm UTC
Hi Tom, more information. I checked the file /etc/mnttab on server and did not see the 'nosuid' option set for '/dev/vgsw01/lvol17' that is used by the oracle binary.
>more /etc/mnttab | grep /opt/oracle/product/ora8
......
/dev/vgsw01/lvol17 /opt/oracle/product/ora8174_64 vxfs ioerror=mwdisable,delaylog,dev=40120011 0 0 1156705448
/dev/vgsw01/lvol16 /opt/oracle/product/ora816_64 vxfs ioerror=mwdisable,delaylog,dev=40120010 0 0 1156705448
......
-hosts /net autofs ignore,indirect,nosuid,soft,nobrowse,dev=60000000 0 0 1156705509
/etc/auto.direct /opt/pvcs autofs ignore,direct,dev=60000001 0 0 1156705509
......
After the member of DBA group starts up the instance, there's no error message or trace file generated, and the newly generated files are all belong to oracle user. The only question shown is the ownership of the processes. If this presents some potential problem, we have to have everyone switch to oracle user for performing the startup/shutdown of the instance.
Thanks.
September 27, 2006 - 4:19 pm UTC
might be something unique/different with your os, i use solaris/linux - what about you?
does your OS "ps" show the "right" (the one you thing should be showing) userid?
Judy, September 27, 2006 - 6:12 pm UTC
Hi Tom, I really appreciate your quick replies!
We're using HP-UX 11.23 now. What I expected are to startup the instance as a member of the dba group (i.e. dbauser1 in this case, and tkyte in your case) but have the oracle processes owned by oracle software owner (i.e. ora10gr2 in your case, oracle in our case).
However, my OS 'ps' showed real userid (dbauser1) so I wonder if something will be wrong if the oracle processes are owned by user other than 'oracle'.
The 'setuid' on oracle binary should produce results like yours so that we can have different DBAs or operators to perform startup/shutdown DB without interfering the permissions of oracle processes and file systems.
Do you think anything could be wrong in our case? I have not seen any but need to prove the reliability.
Thanks again!
September 28, 2006 - 2:46 am UTC
you say "showed the real userid", I rather think "it is showing the user that ran the process, but is currently now showing the 'real' userid the process is running under"
looks ok, maybe other hpux users can provide input, I don't have one sitting around right now.
shutdown immediate rights to any user
Tariq Zia Lakho, November 10, 2006 - 6:06 am UTC
I want to down my database through a normal user except sys or system.....
Can you please tell me if i want to create a normal user ABC.....what r the rights to give it only for database down. It is possible or not.....If possible tell me the rights.
November 10, 2006 - 8:59 am UTC
they would need sysoper and they would need to connect ... AS SYSOPER
(sort of like you connect as sysdba)
shutdown immediate by normal user in 9i
Tariq Zia Lakho, November 10, 2006 - 6:15 am UTC
keeping in mind I am uding Oracle9i. version 9.2.0.1.0
purpose of outln schema
khater ali, November 15, 2006 - 7:00 am UTC
What is the purpose of outln schema?.i accidently deleted the outln schema.after some time i tried to connect to oracle as a "gream" user.It shows
--------
ORA-00604: error occurred at recursive SQL level 1
ORA-18008: cannot find OUTLN schema
Warning: You are no longer connected to ORACLE.
November 15, 2006 - 7:30 am UTC
"accidentally" - how do you accidentally drop a schema?
It is used for stored outlines, that user must have a trigger that says "please generate or use this stored outline" that fires when they connect and it is failing because you "accidentally" dropped a schema.
to: khater ali
Michel Cadot, November 15, 2006 - 8:07 am UTC
Have a look at Metalink, there is a couple of notes to solve your issue (you're not the first one to "accidently" drop this schema since its introduction in 8.x).
Michel
oracle on windows
Khater Ali, November 30, 2006 - 4:06 am UTC
Hai Tom.How are you.I am new to oracle on Windows.Here we have a 4 oracle Databases in Windows platform.In the Task manager utility the process column shows one of oracle.exe Displayed 100%.My question is
1)4 services are running.Is Each services consider as one oracle.exe
2)one of the oracle.exe shows 100%-Where is the problem.Is the problem related to oracle database or we have to increase the RAM.
November 30, 2006 - 9:58 am UTC
1) each database instance you have running will have an oracle.exe process associated with it.
2) 100% WHAT. and why would increasing RAM be thrown out as a fix? I'm assuming "100% CPU" - which means "it is doing something, log into that instance and see what it is doing". Adding RAM won't really decrease CPU needs.
Import into PROD
Khater Ali, January 02, 2007 - 4:38 am UTC
Hai Tom,
We have 2 databases.One is PROD and another one is DEV.We have same schema (for example user1)in two databases.I want to import the schema(user1) to the PROD from DEV.My question is shall i drop the existing schema and import the new one.because when i import the new to PROD.It show some constraints error.Import dump has 50 to 60 thousand tables.So every time we cant able to disable all constraints.Or is there any other way to meet the goal.I used ignore=y parameter also
January 02, 2007 - 8:05 am UTC
only you can answer this, do you want to drop that schema and recreate it? if so, go for it.
database shutdown abort mode: need investigation
romit acharya, January 24, 2007 - 7:51 am UTC
Tom,
Today the prod env was down ( its an 11.5.8 apps based env). Alert log shows the follwoing
Creating archive destination LOG_ARCHIVE_DEST_2: '/moop/archivelogs2/1_49554.dbf'
Log actively being archived by another process
Wed Jan 24 11:44:32 2007
Creating archive destination LOG_ARCHIVE_DEST_1: '/moop/archivelogs/1_49554.dbf'
Wed Jan 24 11:45:02 2007
ARC0: Evaluating archive log 4 thread 1 sequence 49554
ARC0: Unable to archive log 4 thread 1 sequence 49554
Log actively being archived by another process
Wed Jan 24 11:45:11 2007
ARCH: Completed archiving log 4 thread 1 sequence 49554
Wed Jan 24 12:00:42 2007
Completed checkpoint up to RBA [0xc193.2.10], SCN: 0x0852.8bd4e236
Wed Jan 24 12:07:24 2007
Shutting down instance (abort)
License high water mark = 819
Instance terminated by USER, pid = 2504
Is there any way we can find out teh session details or any otehr info which may help us to find how the db was down and why?
its an sos.. as shutdown abort can be done only by dba or guys having sysdba role...
Thanks
Rom
Recover database with archive log mode
khater ali, March 13, 2007 - 1:27 am UTC
Hai Tom,
How are you ?. I need a clarification from your side.One of my database is corrupted which is running in archive log mode. I have a back up of the database but i dont have redo log files. Now iam in the process of recover the database with archive log files.Can i recover the entire database without the help of online redo log files.or should i apply the online redo file for recovering the whole database.
March 13, 2007 - 11:22 am UTC
if you do not have the online redo logs - but you have a backup and archived redo logs, you can
a) restore backup
b) apply all of the archives you have
c) realize that anything committed into the missing online redo logs is gone forever
before you begin - STRONGLY encourage you to get in touch with support and tell them your situation and work out a plan.
Shutdown/Start
Sagar., May 20, 2008 - 8:04 am UTC
I believe database becomes smarter if it is not shutdown as it has cached data ,parsed sqls ,build plans.So when we shutdown this is all gone.Database now has to start afresh.
Problem is for daily cold backup I have to shutdown database take backup and start the database.This is preferred method by small to medium sized organisations which do not have 24X7 operation.
May 20, 2008 - 11:39 am UTC
cold backups have nothing to do with 24x7 operations.
You are basically saying these sites do not do anything important. If they lost an entire days work because the media failed at 5pm that day - it is OK - meaning on any given day, the complete loss of all transactions is perfectly OK with them.
That is what you are saying here - if you need to do cold backups, you must not be in archive log mode, so therefore - you must be willing to lose all data someday - not MIGHT lose, but WILL LOSE ALL TRANSACTIONS.
this has nothing to do with 24x7, this has everything to do with data loss - and you are saying it is ok to lose all data one day. It has nothing to do with small, medium, large - it has everything to do with data protection. You are saying these medium sized organizations will just spend the weekend recreating the data they lost and that isn't a problem (they are small - who cares right)
cold Backups
Phil Singer, May 20, 2008 - 3:06 pm UTC
Tom, have I missed something recently? While a shop running in norachivelog mode would have to use cold backups as its backup strategy, there never used to be anything that would keep a shop from running in archivelog mode, but shutting down and doing a cold backup every weekend or so. Indeed, back before RMAN made hot backups easy (and 24/7 operation was not a requirement) this was a common backup strategy.
May 20, 2008 - 4:12 pm UTC
Maybe I'm missing something.
why would you shutdown if you are in archivelog mode.
ESPECIALLY if there wasn't somewhere there to fix it when
a) it won't shutdown
b) it won't start back up
without intervention
RMAN made backups easier.
RMAN did not make hot backups easier than cold backups - hot was never NEVER - repeat NEVER harder than cold.
It was not any harder to backup hot pre-rman than it was to backup cold.
to backup
COLD HOT
-------------- ------------------
shutdown (error prone) begin backup for tablespaces (trivial, plsql block)
copy datafiles copy datafiles
startup (error prone) end backup (trivial, plsql block)
hot has never been "harder" than cold
and the steps to restore from a hot backup are identical to restoring with a cold backup.
you
a) restore datafiles
b) apply archived redo logs
c) apply online redo logs
you are recovered - regardless of backup mode.
It is a myth that 'hot' backups are 'harder' in any way shape or form.
So, I have to presume that if they are doing cold backups - they cannot be in archivelog mode - especially if they do not have someone sitting there during the shutdown/startup - the precise times when the database won't shutdown or won't startup without a human fixing something
Sagar, May 21, 2008 - 8:08 am UTC
>>cold backups have nothing to do with 24x7 operations.
If the operation is 24X7 how can we take cold backup?(Need to shutdown database for this).
>>You are basically saying these sites do not do anything >>important. If they lost an entire days work because the >>media failed at 5pm that day - it is OK - meaning on any >>given day, the complete loss of all transactions is >>perfectly OK with them.
Acually we won't lose any data.We have multiplexed archive logs as well as Redo logs.
>>That is what you are saying here - if you need to do >>cold backups, you must not be in archive log mode, so >>therefore - you must be willing to lose all data >>someday - not MIGHT lose, but WILL LOSE ALL >>TRANSACTIONS.
As already said we are in Archive log mode with multiplexing of archive and redo log.If server crashes I can restore yesterdays cold backup,startup mount and apply the archive logs including the redo logs from the multiplexed location.
>>this has nothing to do with 24x7, this has everything to >>do with data loss - and you are saying it is ok to lose >>all data one day. It has nothing to do with small, >>medium, large - it has everything to do with data >>protection. You are saying these medium sized >>organizations will just spend the weekend recreating the >>data they lost and that isn't a problem (they are small ->> who cares right)
No,we cannot lose data.As a DBA we should ensure we do not lose single byte of committed data.
Just that the Cold Backup method is more easy/surest/safest/known method( According to me :) ).
May 21, 2008 - 9:10 am UTC
... >>cold backups have nothing to do with 24x7 operations.
...
I was referring to this in the context of the above question.
they wrote:
... This is preferred method by small to medium sized organisations which do not have 24X7
operation. ....
I was saying the "24x7" and "cold backup" have NOTHING to do with each other here. That they are or are not 24x7 has no bearing on cold vs hot. Your archive log mode is the only driving factor there.
If you are not in archive log mode, you can only do cold (and hence, by obvious extension could not be 24x7)
But the fact you are not 24x7 does not imply "cold is the way to go", it does not work that way.
....
Acually we won't lose any data.We have multiplexed archive logs as well as Redo
logs.
........
the BACKUP HOT gosh darn it, I had to assume that if you are doing cold it was because you are not in archive log mode, because I cannot imagine any other reason for doing it.
(do you see my point now - the ONLY reason I can think of for considering a cold unattended backup is because you are not in archive log mode)
If you are in archive log mode, then don't even consider cold backups - just ignore their existence.
PLEASE EXPLAIN TO ME WHY COLD IS
a) easier
b) surest
c) safest
I think it is
a) harder - you have to shutdown, I doubt you will be sitting at the terminal when you do that will you - what will you do when the pager goes off a 3am when you do the backup and the database won't shutdown?
You have to startup, I doubt (copy above.......) when it won't startup.
b) less sure - it will get stuck because you are doing unattended startup and shutdowns.
c) less safe - because they will not happen when the database doesn't shutdown/startup and will cause unplanned outages because the database won't be open for business when people come in in the morning.
Now, you tell me why they are easier, why they are surest, why they are safest - because they are not, not not.
Alexander the ok, May 21, 2008 - 10:29 am UTC
How about
d) Flushes your library + buffer cache then Oracle will have to do all that work over again every week.
May 21, 2008 - 12:04 pm UTC
oh, we didn't even mention the negative performance impact, I was just rebutting his list of easy/safe/secure of which is it not/not/not
I agree on that!!!
Sagar, May 23, 2008 - 12:43 am UTC
Hi Tom,
Yes, performance!!!.That is why I asked in my first line
in my first post on this thread.
"I believe database becomes smarter if it is not shutdown as it has cached data ,parsed sqls ,build
plans.So when we shutdown this is all gone.Database now has to start afresh."
Thanks for the detailed explanation and valuable time given.I hope this is quiet useful for other readers like me who doubt hot backup.
NT Service and dbshut
A reader, May 29, 2009 - 1:59 pm UTC
Greetings Thomas,
and thanks like always,
why do not the database log it 's shutdown, when using windows service or using the dbshut in unix/linux services?
Please clear.
June 01, 2009 - 7:23 pm UTC
I don't know what you mean. The alert log shows the shutdown/startup history.
NT service and dbshut
A reader, June 02, 2009 - 3:25 am UTC
greetings Thomas,
what i mean when i restart the server and there are windows and Linux/UNIX services for the database, i found no logs for the database that it had been shutdown.
June 02, 2009 - 7:36 am UTC
did you look in the alert log.
if you use shutdown normal/immediate - there will be a record of the shutdown.
if you use a shutdown abort - there (cannot) will not be.
Success of shutdown immediate command (how to confirm from another script)?
Anand, June 25, 2009 - 4:20 pm UTC
Hi
Our Environment: Oracle 10g 10.2.0.4 32-bit on Windows 2003 R2 32-bit
We reboot database server Second-Sunday of every month. Schedule scripts to shutdown instance (shutdown immediate) and then to reboot server.
To automate the process:
How to confirm/check if 'shutdown immediate' is successful so that we can make the script (which reboots Windows of DBServer) dependent on the success of 'shutdown immediate'.
On DBServer - C:\DBScripts\shutdown_orcl.bat
--------------------------------------------
net stop OracleDBConsoleORCL
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
set ORACLE_SID=ORCL
sqlplus /nolog @C:\DBScripts\shutdown_orcl.sql > C:\AUTO\shutdown_orcl.log
exit
On DBServer - C:\DBScripts\shutdown_orcl.sql
--------------------------------------------
connect / as sysdba
shutdown immediate;
exit;
On Domain Server - C:\WinScripts\restart_server.bat
---------------------------------------------------
REM This script reboots Windows of DBServer
shutdown -r -f -m \\nameofDBServer
Thanks for your valuable answers.
Anand
June 26, 2009 - 10:53 am UTC
if you spooled the output of the shutdown immediate, you could look for:
...
ops$tkyte%ORA10GR2> connect / as sysdba
Connected.
sys%ORA10GR2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys%ORA10GR2> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[tkyte@dellpe ~]$
....
in the output - you could use return codes (sqlplus WHENEVER commands to exit with a non-zero return code on error) as well.
but, if you ask me, un-attended reboots are deadly, you will find yourself driving into work every second-sunday of every month to fix something. If you need to reboot your servers on a schedule, you need to ask yourself "why, why is that - why does the rest of the world never reboot and we reboot on a schedule, what are we doing that causes that and what should we be doing to avoid that"
un-attended shutdown/restarts - never.