Server Administration :: Database Shutdown Is Required Or Not
Apr 24, 2011
We have 2 Unix SunOS server, named as SUNOS01 and SUNOS02, there is some space need to add in SUNOS02 and for that I have to bring Database down on SUNOS02. When I checked I found that the DB is pointing to SUNOS01, i.e. there is no DB on SUNOS02, So, Do I required to shutdown the DB on SUNOS01 or it is not required to do anything from Database side. I mean to say should I ask Unix team to bring down the unix server and add the space.Also, if DB shutdown is not required then what I have to do afetr they have added space and bring the server up from DB side.
I issued a SHUTDOWN IMMEDIATE command on my database and for over 2 hours and got no response. I did this because I wanted to restart the database due to many hanging sessions which had led to high CPU usage on the server by oracle process.
Oracle Service<dbname> instance using the Windows Services screen which failed with an error 1053 message. At the same time I got an ORA-01013 message saying user requested cancel of operation. See below:
SQL> SHUTDOWN IMMEDIATE ORA-01013: user requested cancel of current operation SQL> SHUTDOWN IMMEDIATE ORA-01089: immediate shutdown in progress - no operations are permitted SQL> SHUTDOWN ABORT ORA-24324: service handle not initialized ORA-01041: internal error. hostdef extension doesn't exist
What I want to know is how do I proceed? Also how do I check to see the status of the Shutdown operation?
I was monitoring a database job to collect statistics, it was scheduled using DBMS_JOBS..I found that it was running during business hours so i got the session ID of the job using;
select sid from dba_jobs_running where job=11;
I then i killed the job using;
select serial# from v$session where sid =232; alter system kill session '232, 10852'; select sid from dba_jobs_running where job=11;
no rows selected...After some time i again fired the same command
select sid from dba_jobs_running where job=11;
SID ---------- 232
and found that the same job is again running..This behavior was repeated again N again. i have attached the spool file for the same...
what could be the reason that the job is starting all over again even after killing the session and what should be done to stop it..I understand that once the database shuts down and if the job is still running then it will restart once the database is up..In this case, Should i remove the job and re submit it again..
I recently installed Oracle 10g on my windows Xp laptop. It has become considerably slow since then. I want to start the database server only when I need it, and not every time I start my laptop. I looked around in OEM and did found a way.
We are unable to drop user due to below error, how to drop the below user without shutdown the database.
SQL> drop user mvm_2010 cascade; drop user mvm_2010 cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-14452: attempt to create, alter or drop an index on temporary table already in use
Find an appropriate script to automate Oracle DBs in one server? This db server have 6 instances. We always done the starting up and shutting down manually, although we have a reference script that does this but in Oracle v7.3.4. We do want to include the automatic start/stop of dbconsole for accessing it via OEM.
I have installed oracle 11g Release 11.1.0.1 on windows server 2008.I access this server from a client machine running on winXP through SQL PLUS. I wanted to shutdown and start DB server from this client machine. Accordingly i issued shutdown immediate command from SQLPLUS.
Now when i tried to start the DB with "STARTUP NOMOUNT" command i received error 12514. I understand this error means that the listener received a request to establish connections to a database but since DB services are not up and running, hence the error. So my first question is how do I resolve this error.
Continuing further, i tried to re-connect to the database as sys, and i received another error ORA-01041. So, my second question is why did i receive this error and again how can i resolve it
may be you can point me to relevant reference in Oracle documentation) how can i start and stop oracle from a client machine using SQL interface in CLI mode and windows environment and avoid this error.
I want to shutdown the database using shutdown immediate and startup the database using startup mount.
1.)My system is client system if I connect start--> program -->Accessories --> communication --> Remote Desktop connection (connect server system) , open Run type cmd after
Shutdown immediate (database is shutdown)
Startup mount (database is mounted no problem it's working fine)
2.) But if I, open Run type cmd in client system If I run same command, I'm getting below error (ORA-12560: TNS:protocol adapter error).
C:>set oracle_sid=dbadb C:>sqlplus / as sysdba SQL*Plus: Release 10.1.0.2.0 - Production on ╘± ╠ß± 31 18:09:27 2009 Copyright (c) 1982, 2004, Oracle. All rights reserved. ERROR: ORA-12560: TNS:protocol adapter error
[code].....
3. If I run same command in Sql Plus I'm getting below error(ORA-12514: TNS:listener does not currently know of service requested in connect descriptor).
SQL*Plus: Release 10.1.0.2.0 - Production on Ôñ Ìáñ 31 18:23:13 2009 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options
I am upgrading a DB using catupgrd.sql, and one of the prerequisites is to shut down the listener. Now, I have multiple database instances registered with this listener, and I don't want the other ones to become unavailable while I do the upgrade. Is there any way to do this for a particular instance only?
I have one question regarding privileges required for export.I read in a document that
Quote: If you do not have the system privileges contained in the EXP_FULL_DATABASE role, you cannot export objects contained in another user's schema. For example, you cannot export a table in another user's schema, even if you created a synonym for it.
what are the system privileges required in EXP_FULL_DATABASE role to perform export objects contained in another users schema?
When I am trying to connect to sql from scott or any other user i am getting this error message ,
ORA-01033: ORACLE initialization or shutdown in progress
I have been getting this error message for 4 four days . Butt when i am trying to connect from SYS user it connects successfully ,but when i do query it gives me following error message .
ORA-01219: database not open: queries allowed on fixed tables/views only
I shutdown Database from SYS user ,and start it again .But at startup it gives me following error message .
ORA-00204: error in reading (block 3, # blocks 8) of control file ORA-00202: control file: 'D:ORACLEPRODUCT10.2.0ORADATAORCLCONTROL03.CTL' ORA-27091: unable to queue I/O ORA-27070: async read/write failed OSD-04006: ReadFile() failure, unable to read from file O/S-Error: (OS 23) Data error (cyclic redundancy check).
I have oracle 10g as well as oracle 8i on my server. currently we are using oracle 10g. now we get the following error when we try to connect to oracl 10g database.
Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:Documents and Settingsomni>cd C:>sqlplus olog The system cannot find the path specified. C:>sqlplus/nolog SQL*Plus: Release 8.1.5.0.0 - Production on Tue May 15 16:39:13 2012 (c) Copyright 1999 Oracle Corporation. All rights reserved. SQL> connect sys/admin as sysdba Connected to an idle instance. SQL> shutdown abort; ORACLE instance shut down. SQL> startup nomount; LRM-00109: could not open parameter file 'D:oro8iDATABASEINIT.ORA' ORA-01078: failure in processing system parameters
The location of pfile of oracle 10g database is c:/oracle/product/10.0.0.1/admin/pfile.
We have changed the SYSDATE to 03-FEB-1994 in our AIX machine where oracle11g database is running. Once the sysdate is changed db and listner are getting down immediately and its generating the core in $ORACLE_HOME/dbs
This AIX machine is for PT test and as per one requirement we are changing the sysdate. Moreover, we found that database is getting down if we change the sysdate till 1999. If we change the sysdate to 2000, then we are not seeing any issue.
Below point is for additional info. After changing the sysdate and trying to connect to the db user, we are getting the below issue.
I found some of the tablesapces whose used% exceeded 80%.Based on this output i thought of increasing the datafile.But there are many tablespaces of DATA1 whose autoextensible column some are set to NO, and some are set to YES. I believe we dont need to bother about the tablespace whose Autoextensible column set to YES as Oracle will automatically manage until the Maxsize of it. But some tablesapce of DATA1 are set to NO. Should i recommend to add size for this DATA1 tablespace whose autoextensible are set to YES/NO or leave it as it is.
Please see the column below.
Quote: FILE_IDTABLESPACE_NAMESIZE(M)USED(M)FREE(M)MAX(M)AUTOEXTENSIBLE% USED 59DATA13276732741.1325.7532767YES100 60DATA13276732741.6925.1932767YES100 61DATA1327673274026.8832767YES100 76DATA13270032193.31506.560NO98 77DATA13270030858.51841.380NO94 78DATA13270029476.563223.310NO90
I searched the knowledge base here and in other forums and haven't found the answer. There was a similar condition presented earlier, but it turned out the resolution was to run the >shutdown/>startup on the server rather than from a client. Not applicable in my case!
Shutdown and startup called used to work with 9i, now startup fails in 10g. ORACLE_HOME and ORACLE_SID are set and report correct values. What is different in 10g that the script (shown below) now throws a TNS listener error after shutdown?
I successfully upgraded from 9i to 10g (10.2.0.5.0) on Windows Server 2003, 32-bit last weekend. Now our backup_script.bat and oracle_stop_start.sql throw "ORA-12514: TNS:listener does not currently know of service requested in the connect descriptor"
This batch file and sql script predate me and worked with 9i for years. Now the startup command in the oracle_stop_start throws this error after executing shutdown. The backup batch file calls:
sqlplus "system/manager@wind as sysdba" @D:ptcwindchillackupsoracle_stop_start
The entire oracle_stop_start.sql is as follows:
shutdown immediate; startup open; quit
What is different in 10g that this now doesn't work? Shutdown works, but it will not startup due to the listener error. I have made the backups work by commenting out the call to sqlplus and I'm getting good backup dumps.
In my virtual image of the server I have found that I can duplicate the error after >shutdown. I have found that if I exit sql*plus and go back in as follows I can get the database instance to restart from the command line:
c:>sqlplus /nolog sql>connect sys/* as sysdba sql>startup open wind;
That does start, mount and open the database instance.
Is there a better way to be making sure that everything is stopped before executing the >exp command to create a backup dump? I will look into and test datapump export/import and RMAN for creating the backups, after I get this working again.
why this worked in sql*plus in 9i but not in 10g? Can I make TNS Listener smarter? Change or add variables to the startup command?
The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.
i install oracle 10g on linux on perform the following step but my database is not auto start with the machine
Automatic startup and shutdown oracle on linux Oracle database server provides two scripts to configure automatic database startup and shutdown process.
The scripts are, $ORACLE_HOME/bin/dbstart $ORACLE_HOME/bin/dbshut
Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d.
-Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script.
In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d.
If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.
Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and startup or shutdown the database.
Automate Startup/Shutdown of Oracle Database on Linux
Step 01: Be sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /var/opt/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:Y Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.
If in my database there is two database named arju and arjudup then my oratab file will contain the entry like, arju:/var/opt/oracle/product/10.2.0/db_1:Y arjudup:/var/opt/oracle/product/10.2.0/db_1:Y where /var/opt/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.
Step 02: Create a script to call dbstart and dbshut. In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.
a) Login as root. b) Change directories to /etc/init.d c) Create a file called dbora and chmod it to 750.
# touch dbora # chmod 750 dbora d)Edit the dbora file and make the contents of it like below.
we have a production database 'X'. Now i have created a test database 'T' and did'nt configured another listener to it! The issue is when i cam connecting to oracle through sqlplus i am directly connecting to Test database 'T' but not the production database 'X'----ofcourse i can login to production DB afterwards. but initially i want to access the production database 'X'.
The Database is Shutdown, no user is complaining about it and also you are not monitoring it through alert log file, then how will you, as a DBA, come to know about it?
We are having a problem trying to automate our 11g database startup on a Linux Platform (Redhat). This is the procedure we followed.
1. Logged in as the root user.
2. Edited the oratab file for the platform.
vi /etc/oratab db:/u01/app/oracle/product/11.2.0/dbhome_1:Y
3. Create a file called dbora in /etc/init.d directory
4. Entered the following in dbora:
#! /bin/sh -x # # Change the value of ORACLE_HOME to specify the correct Oracle home # directory for your installation. ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[Code] .....
4. Changed permissions of dbora
chgrp dba dbora chmod 750 dbora
5. Created symbolic links to the dbora script in the appropriate run-level script directories:
