Analysis of common difficult problems in starting Oracle

oracle| problems to start Oracle common problem Analysis (2003-10-1)

This article is from "Network Administrator World" 2003 phase 10th "Fault Diagnosis" column

The startup and shutdown of instances and databases is one of the important responsibilities of DBAs. Only open the database, other users can manipulate the data in the database. Once the database is closed, it cannot be manipulated. It is often the case for DBAs to shut down and restart the database in order to optimize and adjust the operation of the application. If the user has entered the database, using the shutdown IMMEDIATE or shutdown abort command to perform a shutdown of the database, the user will lose the connection until the database restarts. Frequent shutdown and startup can have a certain impact on database performance and, of course, affect the user's use of the database. This article analyzes the problems that are frequently encountered when Oracle starts and shuts down from the perspective of managing databases, and summarizes the issues and considerations that should be noted in starting and shutting down Oracle databases.

Start FAQ

(a) How to start normally when a control file is corrupted?
System environment
Operating system: Windows Advanced Server
Database: Oracle 8i R2 (8.1.6) for NT Enterprise Edition
Installation path: C:\ORACLE
Error phenomenon
Because the hard drive has bad track, a control file damaged, or manually deleted a control file, control Panel of Oracle related services can start successfully, but with Sql*plus can not connect. The error prompt is "ora-01034:oracle not available".
Solving method
(1) Run the command directly from the Start menu and go to "svrmgrl>" prompt mode.
(2) at the "svrmgrl>" prompt, type the "Connect internal" command.
(3) at the "svrmgrl>" prompt, type the "shutdown abort" command to close the database.
(4) Locate the Init.ora file, which is generally located under the "C:\Oracle\admin\oradb\pfile" directory, and then modify the Init.ora file so that the unusable control file is not defined in the Control_files parameter. such as "C:\Oracle\oradata\oradb\control03.ctl" can be removed in Init.ora or replaced with other bootable control files.
Before modification:
control_files= ("C:\Oracle\oradata\oradb\control01.ctl"). C:\Oracle\oradata\oradb\control02.ctl "," "C:\Oracle\oradata\oradb\control03.ctl")
After modification:
Control_files= ("C:\Oracle\oradata\oradb\
Control01.ctl "," C:\Oracle\oradata\oradb\control02.ctl ")
(5) Restart the database instance to resolve the problem.
Note: There should be at least two control files in the control file.

(b) How do I open a database after an online hot backup fails?
System environment:
Operating system: Windows Advanced Server
Database: Oracle 8i R2 (8.1.6) for NT Enterprise Edition
Installation path: C:\ORACLE
Error phenomenon
The server fails (for example, power off, etc.) when an online hot backup occurs. When the server is restarted and the database is restarted, the database cannot be opened and Oracle requires media recovery because the tablespace is still in hot backup state.
Solving method
(1) Set the database to archive mode first
C:\>svrmgrl
Svrmgrl>connect Internal
Svrmgrl>alter tablespace table space name begin backup;
Description: This is the error caused by the forced shutdown of the database when the hot backup mode is not finished in the tablespace.
Svrmgrl>shutdown Abort
Svrmgrl>startup Mount
(2) Set the data file for this tablespace to "end Backup" mode when the database is not open
Svrmgrl>alter database datafile ' table space data file name ' end backup;
(3) Perform tablespace media recovery
Svrmgrl>recover tablespace table space name;
Svrmgrl>alter database open;

(iii) When Oracle starts, the listener does not start or open an error?
Error behavior:
When Oracle starts, the listener does not start or open an error; server-side: Log on normally with Username/password, but Username/password@alias login unsuccessful; client: with username/ Password@alias Login Not successful
Solving method
(1) If the NT machine name is modified, then the host parameter in the Listener.ora file is changed to the new NT machine name, and the ORACLETNSLISTENER80 service can be restarted.
For example:
LISTENER =
(Address_list =
(address = (PROTOCOL = TCP) (Host = nt_name) (Port = 1521))
(Address= (PROTOCOL=TCP) (host=nt_name) (port= 1526))
)
(2) Using the Listener utility: LSNRCTL80.exe.
(3) Check listener parameter file: c:\orant\NET80\admin\
Listener.ora.
(4) Check listener log files: c:\orant\NET80\log\
Listener.log.
(5) Tnsnames.ora file configuration error, reconfigure.
(6) Check client side Tnsnames.ora.

(iv) How do you log in Sysoper, sysdba in Oracle Enterprise Manager (OEM) tools?
Software Environment:
Windows nt4.0+oracle 8.0.4
Oracle installation path is: C:\ORANT
Error phenomenon
When you log on to the database with an Oracle Enterprise Manager (OEM) tool, either the SYS user, System user, or other user, you are denied access to the SYSDBA or Sysoper role in the Connect as column, Error: Ora-01017:invalid Username/password;logon denied, but successfully logged in as normal.
Error analysis
There are three ways to connect in an Oracle database:
(1) Normal mode, basic connection mode, lower level.
(2) SYSOPR system mode, System connection mode, high level.
(3) SYSDBA Administrator Way, is the database administrator connection way, the highest level.
In Oracle8, SYS, System is already just a normal user with privileges, and internal is the DBA. So when the DBA is managing the data, you can only use a privileged "internal" connection to make no mistakes.
Solution for
Connect in internal/oracle mode.

Launch of Oracle Database

Start instance
Start the instance so that Oracle server completes the following tasks:
Read the parameter file Init.ora file to determine how to initialize the instance;
Allocating the storage space for the SGA according to the SGA size provided in the parameter file;
Start the background process;
Open the background trace file.
Instance startup is normal and constrained to start. The so-called constraint startup is that when the database is opened, only the user with the privileges of the restricted session system can be connected to it. Normal startup allows all users to connect to it.
There are three ways to start an instance:
Start the instance only;
Starts the instance and installs the database, but does not open the database;
Start the instance and install and open the database.

Database installation
A database installation is the connection between a specified database and a started instance. The installed database remains closed until it is opened, and only DBAs can access it at this time. When the database is installed, the instance looks for the control file specified by the Control-files parameter in the parameter file, opens it, obtains the data file name and log file name of the database, and then confirms that the files exist. If you want multiple instances to install the same database concurrently, the DBA can choose to install the database in parallel. If you want only one instance to install the database, select the private way to install the database. If the Oracle version does not support the parallel server option, the database can only be installed in a private manner. When you choose to install the database in parallel, the number of instances of concurrent installations of the same database is limited by the predefined maximum value, and you must have a named private rollback segment or a public rollback segment available in the parameter file.

Open Database
Until the installed database is opened, all legitimate users (except DBAs) cannot connect to and access the database, and only when they are opened can they be connected and accessed. While the database is open, the instance also opens the online data file and the online log file. If the database is closed due to an exception to the instance or a power failure, the next time it is opened, an instance recovery is performed automatically. When the database is opened, the instance attempts to get one or more rollback segments. In a distributed transaction, if a database is suddenly shut down because of a power failure, if one or more distributed transactions are not committed or rolled back at this point, the instance recovery continues when the database is reopened, and the Reco daemon automatically, immediately and consistently resolves any distributed transactions that have not yet been committed or rolled back.

Common Open Database and instance command mode
(1) Startup Nomount
Non-installation startup, this startup mode is executable: Rebuild control file, rebuild database and start instance, that is, start SGA and background process, this startup only need Init.ora file.
(2) Startup Mount DBName
Installation startup, this startup mode is executable: Database log archiving, database recovery and renaming some database files.
(3) Startup Open dbname
You can access the data in the database by executing "nomount" and then performing mount, and then opening all the database files, including the Redolog file, in this way.
(4) The startup equals the following three commands
Startup Nomount
ALTER DATABASE Mount
ALTER DATABASE Open
(5) Startup restrict
Constraint, which enables the database to be started, but only with certain privileged users, and when unprivileged users access it, the following prompts appear:
ERROR:
Ora-01035:oracle only allows users with restricted session permissions to use.
(6) Startup force
Forced to start the way, when the database can not be closed, the startup force to complete the database shutdown, the first shutdown database, and then execute the normal start database command.
(7) Startup pfile= parameter filename
Start with the initialization parameter file, read the parameter file, and then start the database as set in the parameter file. Example: Startup Pfile=e:\oracle\admin\oradb\pfile\init.ora
(8) Startup exclusive
Sql>conect Internal
When connected as a internal, the Connect command does not require a password because its authorization is provided implicitly by the operating system. The internal connection is equivalent to the SYS connection, both of which are users of the Oracle data dictionary.

Application
Start oracle8i under UNIX, and the whole process steps as follows:
(1) Telnet host IP or host name
user:***** password:*****
(2) lsnrctl start
(3) SVRMGRL
(4) Svrmgrl>connect Internal
(5) Svrmgrl>startup

Shutdown of instance and database

To close an instance
After the instance is closed, the SGA is undone from memory and the background process is stopped. The steps to close the instance are as follows:
(1) Start SQL*DBA
(2) as a internal user connected to the database
(3) Close instance
Close Database
Remove Database
Close instance

Close Database
When the database shuts down, all the database and log entry data in the SGA are first written to the appropriate data files and log files. Then close all the online data files and log files. The control file remains open after the database is closed and removed, and you can reopen the database if you need to open it.

Remove Database
Removing a database is to sever the connection between the instance and the database, close the database control file, and leave the memory only for instance.

Common shutdown database and instance commands
(1) Shutdown normal (database shutdown)
In normal shutdown situations, Oracle waits for all connected users to end their conversations properly before shutting down the database. So the next time you start the database, you don't need to restore the instance.
(2) Shutdown immediate (immediate way to close the database)
When you select the immediate shutdown method, the implementation is closed directly. Executing shutdown immediate in SVRMGRL, the database does not shut down immediately, but shuts down (terminates the session, frees session resources) when the Oracle performs some cleanup work, and when the database cannot be closed with shutdown, shutdown Immediate can complete the operation of the database shutdown.
This shutdown is mainly used in the following situations:
① immediately power off;
②oracle is running an informal database or a database application.
This method closes the database process as follows:
①oracle ends the SQL statement that is currently being processed;
② rollback of uncommitted transactions;
③oracle does not wait for the connected user to exit the connection, interrupts them directly, using this shutdown, Oracle should automatically perform instance recovery the next time the database is started.
(3) Shutdown abort (undo instance method Close database)
This is one of the quickest ways to close, and it is mainly used in the following situations:
① a minute after the blackout;
②oracle is in the informal operation of the database or a database application, and other closed mode is invalid;
③ encountered a problem when starting a database instance.
The process of shutting down the database is as follows:
①oracle does not wait for the call to complete, directly interrupts the SQL statement being processed;
② Roll back uncommitted things.
With this shutdown, Oracle should automatically perform instance recovery the next time the database is started. The operation of starting and shutting down instances and databases requires the Connect internal system privileges, and the operation of installing and opening the database has the ALTER DATABASE system privileges. Close the database directly, the session that is accessing the database will be abruptly terminated, and if a large number of operations are executing in the database, it will take a long time to restart the database after performing shutdown abort.

Application
To turn off Oracle8i in Unix, the steps are as follows:
Svrmgrl>connect Internal
Svrmgrl>shutdown
Svrmgrl>exit

Need to be aware of issues when shutting down Oracle databases

Do not close sql*net in advance.
When you need to shutdown the database, do not close the sql*net in advance until you have confirmed that all databases on the server are closed and then close sql*net. If Sql*net is turned off, the user loses the session with the database.

Archive or delete old trace files and logs
When an instance of Oracle is started, Oracle writes diagnostic information about the instance to the specified trace and log files. Each background process adds a trace file that is stored in the specified directory, specified by the Background_dump_dest parameter in the Init.ora or Config.ora file. When a user encounters an internal error in the database, it also increases the user's trace file where the location is specified by the User_dump_dest parameter in the Init.ora or Config.ora file. The number and name forms of the trace file are determined by the operating system that uses the database.
Oracle does not automatically delete the trace file each time you start and close the database. If these unwanted files are not deleted for long periods of time, they will occupy a large amount of free space in the database. Database administrators should be very good at managing these files and can take a delete or archive strategy to avoid useless files taking up a lot of free space.
Sql*net also produces LOG files, generally called Listener.log,listener.log file size also with the number of times to start increasing, so through proper management of it can also effectively use space. To view this file, you can type a command:
Lsnrctl status
Use this command in Oracle8i, which displays the result:
o200 5% lsnrctl Status
Lsnrctl for SGI irix64:version 8.1.6.0.0-production on 09-apr-2003 09:16:26
(c) Copyright 1998, 1999, Oracle Corporation. All rights reserved.
Connecting to (Address= (PROTOCOL=IPC) (Key=pnpkey))
STATUS of the LISTENER
Alias LISTENER
Version Tnslsnr for SGI irix64:version 8.1.6.0.0-production
Start Date 19-mar-2003 14:43:53
Uptime hr. 4607182418800017439 min. sec
Trace level off
Security off
SNMP off
Listener Log File/data/ora8i/orahome/network/log/listener.log
The listener supports no services
The command completed successfully

Rename Alert log file
The ALERT log file is also used to record diagnostic information for instance. Its storage directory is also specified by the Background_dump_dest parameter. In general, ALERT log file naming method is: Alert_. LOG. The Alter log file includes key database event information, such as startup and shutdown and Redolog usage, tablespace changes, file changes, internal error information, and database backup status changes.
ALTER log is growing very fast in the use of subsequent databases. If you look at it after a period of time, its file has become very large, so it is very difficult to view it. Generally speaking, in order to leave the useful information, while not wasting a lot of space, the best way to manage is to rename it. You can change the ALTER log filename at any time, and it is recommended that you change the name when you shutdown/startup the database.