Oracle Management and Maintenance. Install, create, start, and close the database Frequently Asked Questions (1)

Oracle Management and Maintenance. Install, create, start, and close the database Frequently Asked Questions (1)

20070605

Papayas

I. Content Overview

This article describes how to solve common problems during Oracle Installation and database startup in Linux. This part
The content has been mentioned in more detail in the Configuration Management Server series. Here is a special summary.

Ii. Installation of Oracle databases in Linux

Full process of installing oralce92 on Virtual Machine redhatlinux9
Http://blog.csdn.net/liwei_cmg/archive/2006/05/28/759286.aspx
Starting from server construction (4). installing and configuring oracle in Linux
Http://blog.csdn.net/liwei_cmg/archive/2007/04/18/1568923.aspx

3. Create a database

Two methods are usually used to create a database:

A. Database Configuration guest ent that comes with Oracle

The graphical installation mode is very simple.
Error-prone, not recommended. However, dbca has a very useful function, that is, to generate a database creation script.


Question 1. after you click Create Database, the following error occurs: Home/Oracle/ora9/Oracle/bin/dbca: Line 124: 31614 segmentation fault $ jre_dir/bin/JRE-doracle_home = $ oh-djdbc_protocol = thin-mx64m-classpath $ classpath oracle. sysman. assistants. dbca. dbca $ arguments
The graphical interface disappears.

Modify dbca and change the last line:
# Run dbca
$ Jre_dir/bin/JRE-native-doracle_home = $ oh-djdbc_protocol = thin-mx64m-classpath $ classpath oracle. sysman. assistants. dbca. dbca $ arguments
Fi
A native parameter is added.

Problem 2. ora-29807 error during database creation

ORA-29807: specified operator does not exist
This is a known issue (bugs 2925665). You can click on the "Ignore" button to continue.
Once dbca has completed database creation, remember to run the 'prvtxml. plb' script
From $ ORACLE_HOME/rdbms/admin independently, as the user SYS. It is also advised
To run the 'utlrp. SQL 'script to ensure that there are no invalid objects in the database
This time.

This is a bug in Oracle. You can select "Ignore". After the database is created successfully, run rdbms/admin/utlrp. SQL by the Sys user.


Question 3: the user name and password prompt box is displayed at the end of the database creation. After you enter the password and exit, the screen does not respond.

This problem is probably due to the native parameter set in the last line of dbca. The solution is the opposite of the solution. This
Even if dbca is killed, the database cannot be started. For how to solve the startup problem, see the database startup problem.
.


B. Use the CREATE DATABASE statement to create a database

In the last step of Database Configuration guest ent, it is saved as a script and runs through the Oracle user
You can.

It is mainly composed of the following files. Linux. Sh is the main shell:
Createdbcatalog. SQL createdb. SQL Linux. Sh
Createdbfiles. SQL init. ora postdbcreation. SQL

After successful display:

SQL> show errors;
No errors.
SQL>
SQL> REM ============================================ ==============================================
SQL> REM end utlrcmp. SQL
SQL> REM ============================================ ==============================================
SQL> execute utl_recomp.recomp_serial ();

PL/SQL procedure successfully completed.

SQL>
SQL> REM ============================================ ======================================
SQL> REM run component validation procedure
SQL> REM ============================================ ======================================
SQL>
SQL> execute dbms_registry.validate_components;

PL/SQL procedure successfully completed.

SQL>
SQL> REM ============================================ ==============================================
SQL> REM end utlrp. SQL
SQL> REM ============================================ ==============================================
SQL> shutdown;
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> connect sys/change_on_install as sysdba
Connected to an idle instance.
SQL> set echo on
SQL> spool/home/ora/ora9/Oracle/assistants/dbca/logs/postdbcreation. Log
SQL> Create spfile = '/home/ora/ora9/Oracle/dbs/spfilelinux. ora 'from pfile = '/home/ora/ora9/admin/Linux/scripts/init. ora ';

File Created.

SQL> startup;
Oracle instance started.

Total system global area 122754448 bytes
Fixed size 451984 bytes
Variable Size 58720256 bytes
Database buffers 62914560 bytes
Redo buffers 667648 bytes
Database mounted.
Database opened.
SQL> exit;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0-Production
With the partitioning, OLAP and Oracle Data Mining options
Jserver release 9.2.0.4.0-Production

4. Start the database

To log on to the Oracle database, you must have the administrator privilege. Generally, run the following command:
# Sqlplus/nolog
SQL> Conn/As sysdba

Note: <dbname> indicates a parameter of the database name, for example, alter database linuxdb open.

Oracle Database startup can be divided into three steps:

A. Create an oracle instance (not in the installation phase)
-----------------------------------------------------------

SQL> startup nomount

In this step, only the Oracle instance is created. First, read the init. ora initialization parameter file and start the background processes and
Initialize the system global zone SGA. Nomount is often used to repair databases, such as rebuilding parameter files, control files, and databases.
.
The init. ora parameter file defines the memory structure size, control file, number of processes, number of cursors, and rollback segments.
Important initialization database parameters. The database instance name is set according to db_name, but does not have to be the same as the name of the opened database
. After the instance is turned on, the system displays a list of SGA Memory Structures and sizes, as shown below:

Oracle instance started.

Total system global area 202445884 bytes
Fixed size 451644 bytes
Variable Size 83886080 bytes
Database buffers 117440512 bytes
Redo buffers 667648 bytes

B. Use an instance to install the database (installation phase)
-----------------------------------------------------------

SQL> startup nomount
SQL> alter database <dbname> Mount

Or

SQL> startup Mount <dbname>

Open the instance and install the database. Read the data file and rebuild log file in the control file from Oracle, and confirm
The location of the data file and the on-premises log file, but the data file and log file are not verified at this time. This method is often used
Used in database maintenance operations. Such as database log archiving, database media recovery, online or offline data files, reset
BIT data files, redo log files, and open archiving methods.

Use the following command to change to the previous status

SQL> alter database <dbname> dismount

C. Open the database (open stage)
-----------------------------------------------------------

SQL> startup nomount
SQL> alter database <dbname> Mount
SQL> alter database <dbname> open

Or

SQL> startup open <dbname>

Or

SQL> startup

In this case, when the database is opened to make the data file and the duplicate log file online, one or more rollback segments are usually requested.
The database system is working normally and can accept user requests.

Use the following command to change to the previous status

SQL> alter database <dbname> close


V. Other database startup Methods

A. startup restrict Constraints
In this way, you can start the database, but only allow access by users with certain privileges. When a non-authorized user accesses the service
Note:
ORA-01035: Oracle only available to users with restricted session privilege

B. startup Force start Mode
When the database cannot be closed, you can use startup force to close the database and start it again.

C. startup pfile = parameter file name
The startup method with the initialization parameter file. Read the parameter file first, and then start the database according to the settings in the parameter file.
For example, startup pfile = '/home/ora/ora9/Oracle/dbs/initlinux. ora ';

D. Option for concurrent services, that is, you can start multiple instances to access a database at the same time.
Startup exclusive exclusively opens the database (only one instance can be run, default)
Startup parallel open the database in parallel

6. Shut down the database

There are four options for disabling a database.

A. Shutdown normal

This is the default option for shutting down the shutdown command of the database. After the command is executed, no new database connection is allowed. In data
Before the database is closed, Oracle will close the database only after all connected users exit from the database. This
You do not need to recover any instances during the next startup. However, you must note that
You must wait for the user and the time cannot be determined.

B. Shutdown immediate

This is the most common way to shut down the database. The SQL statements currently being processed by Oracle are immediately interrupted.
Why are all transactions not committed rolled back. If a long uncommitted transaction exists in the system, use this method to close the database.
It also takes some time (the transaction rollback time ). The system does not wait for all users connected to the database to exit the system and forcibly roll back when
Previous active transactions, and then disconnect all connected users.
It is the most recommended method in practical applications. However, for busy databases, the command cannot be closed after being executed.
Manually kill the database background process (orasid) and then disable it.

C. Shutdown transactional

This option is only available after Oracle8i. This command is often used to plan to shut down the database, which causes the current connection to the system and
After the active transaction is executed and the command is run, no new connections or transactions are allowed. After all activities are completed
The database will be shut down in the same way as shutdown immediate.

D. Shutdown abort

This is the last resort to shutting down the database, and there is no way to shut down the database.
Formula, about 1% ~ A 4% probability causes the database to fail to start. Before executing this command, it is best to ensure that the Oracle process in the background is basically clear
In addition, this greatly improves the security factor.

After the command is executed, all running SQL statements are immediately aborted. All uncommitted transactions will not be rolled back. Oracle
Do not wait for the user currently connected to the database to exit the system. The instance needs to be restored when the database is started next time. the start time may be equal
More.

Comparison of Different database Close Methods:

D B C
Allow new connections
Wait till the current session is terminated ××√
Wait till the current transaction is aborted ××√ √
Force checkpoint: Close all files. * √


VII. Database startup Problems

A. parameter file Problems

SQL> startup
ORA-01078: Failure in Processing System Parameters
LRM-00109: cocould not open parameter file '/home/ora/ora9/Oracle/dbs/initunixdb. ora'

The problem is that the default parameter file cannot be found. solution:
Check the oracle_sid in the current export, and confirm that it is consistent with the SID Name of the database to be started. Then, perform the following operations.
SQL> Create pfile = '/home/ora/ora9/Oracle/dbs/initlinuxdb. ora 'from spfile = '/home/ora/ora9/Oracle/dbs/spfilelinux. ora ';
SQL> startup pfile = '/home/ora/ora9/Oracle/dbs/initlinuxdb. ora ';

Knowledge about spfile and pfile:

In Oracle databases through 8i, parameters controling memory, processor usage, control file locations and other key parameters are kept in a pfile (short for parameter file ).

The pfile is a static, plain text files which can be altered using a text editor, but it is only read at database startup. any changes to the pfile will not be read until the database is restarted and any changes to a running database will not be written to the pfile.

Due to these limitations, in 9i Oracle introduced the spfile (server parameter file ). the spfile cannot be edited by the DBA; instead it is updated by using alter system commands from within Oracle. this allows parameter changes to be persistent guest SS database restarts, but can leave you in a pinch if you need to change a parameter to get a database started but you need the database running to change the parameter.

A 9I (or later) database can have either a pfile or an spfile, or even both, but how can you tell which you have? If you have both, which one is being used? How do you go from one to the other? How do you get out of the chicken-and-the-egg quandary of a database that will not start up without you changing a parameter that's in that file you can't update unless the database is up?

Note: This information is based on an Oracle 9i installation on Solaris. your mileage may vary. I have also chosen to ignore issues of RAC installation. in my example I have used oradb as my Sid.

Am I using a pfile or an spfile?

The first thing to check is if you have a pfile or spfile. they can be specified at startup or found in the default location. the default path for the pfile is $ ORACLE_HOME/dbs/init $ oracle_sid.ora and the default for the spfile is $ ORACLE_HOME/dbs/spfile $ oracle_sid.ora.

If both a pfile and an spfile exist in their default location and the database is started without a pfile = '/path/to/init. ora' Then the spfile will be used.

Assuming your database is running you can also check the spfile parameter. either the command show parameter spfile or select value from V $ parameter where name = 'spfile'; will return the path to the spfile if you are using one. if the value of spfile is blank you are not using an spfile.

The path to the spfile will often be represented in the database? /Dbs/spfile @. ora. This may seem cryptic, but Oracle translates? To $ ORACLE_HOME and @ to $ oracle_sid so this string translates to the default location of the spfile for this database.

How can I create an spfile from a pfile?

As long as your pfile is in the default locations and you want your spfile in the default location, you can easily create an spfile with the command create spfile from pfile ;.

If you need to be more specific about the locations you can add paths to the CREATE command like this:

Create spfile = '/u01/APP/Oracle/product/9.2/dbs/spfileoradb. ora'
From pfile = '/u01/APP/Oracle/product/9.2/dbs/initoradb. ora ';

These commands shocould work even when the database is not running! This is important when you want to change a database to use an spfile before you start it.

How can I create a pfile from an spfile?

The commands for creating a pfile are almost identical to those for creating a spfile before t you reverse the order of spfile and pfile:

If your pfile is in the default location and you want your spfile created there as well run create spfile from pfile ;.

If you have, or want them in custom locations specify the paths like this:

Create pfile = '/u01/APP/Oracle/product/9.2/dbs/initoradb. ora'
From spfile = '/u01/APP/Oracle/product/9.2/dbs/spfileoradb. ora ';

Again, this can be done without the database running. this is useful when the database fails to start due to a parameter set in the spfile. this is also a good step to integrate into your backup procedures.

How can I see what's in my spfile

To view the settings in the spfile we have two options: first, we can use the command above to create a pfile from the spfile. this is simple, and fairly fast, but unnecessary if the database is running.

The better way, if the database is running, is to select the parameter you want to view from the Oracle view v $ spparameter with a command like this:

Select value from V $ spparameter where name = 'processs ';

If you try to view the spfile with a text editor it may seem like it is plain text, but beware! The spfile will not behave correctly (if it works at all) if it has been edited by a text editor.

How can I update values in my spfile?

The values in spfile are updated with the alter system command, but to update the spfile we add an additional parameter of scope.

Alter system set processes = 50 scope = spfile;

This command wocould update the parameter Processes in the spfile. since this parameter can only be set at startup, we say scope = spfile and the change will be reflected when the database is restarted. other options for scope are memory which only changes the parameter until the database is restarted, and both which changes the instance immediately and will remain in effect after the database is restarted.

How can I update values in my spfile when my database won't start?

So your database won't startup because of a problem in your spfile. you can't edit it with a text editor and you can't use alter system because your database is not running. it sounds like a problem, but really isn' t. here's what you do:

Connect up to your database as sysdba. You shocould get the message connected to an idle instance

Run the command create pfile from spfile; specifying the location as abve if necessary. You shoshould now have a fresh version of The spfile.

Edit the pfile to update the parameter you need to update.

Run the command create spfile from pfile; to move the changes you have just made back into the spfile.

Startup the database normally. It shocould read the changed spfile and start up correctly. You can optionally Delete the pfile if you are done.

B. Error ORA-01102 during startup: Cannot mount database in exclusive mode

SQL> Conn/As sysdba
Connected to an idle instance.
SQL> startup
Oracle instance started.
Total system global area 202445884 bytes
Fixed size 451644 bytes
Variable Size 83886080 bytes
Database buffers 117440512 bytes
Redo buffers 667648 bytes
ORA-01102: Cannot mount database in exclusive mode

The reason is that the lksid in the $ ORACLE_HOME/DBS directory is occupied by the lklinux file. Root User View:
# Fuser-u lklinux
Lklinux: 13468 (Oracle) 13480 (Oracle)
Release files
# Fuser-K lklinux

ORA-01102 errors, here are detailed descriptions:
Http://www.hellodba.com/cases/case-unexception_down.htm

C. Prompt ORA-01991 error opening password file...
SQL> startup
Oracle instance started.

Total system global area 202445884 bytes
Fixed size 451644 bytes
Variable Size 83886080 bytes
Database buffers 117440512 bytes
Redo buffers 667648 bytes
ORA-01990: Error opening Password File '/home/ora/ora9/Oracle/dbs/orapw'
ORA-27037: unable to obtain File status
Linux error: 2: no such file or directory
Additional information: 3

The cause is that the password verification file is lost and you can recreate it.

[Ora @ Liwei DBS] $ orapwd
Usage: orapwd file = <fname> Password = <password> entries = <users>

Where
File-name of password file (MAND ),
Password-password for sys (MAND ),
Entries-Maximum number of distinct dBA and opers (OPT ),
There are no spaces around the equal-To (=) character.

[Ora @ Liwei DBS] $ orapwd file =/home/ora/ora9/Oracle/dbs/orapwlinux Password = sys entries = 10

Note: If you forget the Sys or system password, you can use this method to recreate the password file. However, there is a simpler way to directly
Conn/As sysdba, alter user SYS identified by sys.

D. The ORA-12701 is prompted during startup: Create Database character set is not known

Check whether the path of the ora_nls33 environment variable is correct.


8. Database Network Settings

In terms of network settings, both listen and TNS have the corresponding graphical interface tool netca netmgr.
The listen TNS file is located at/home/ora/ora9/Oracle/Network/admin, that is, $ ORACLE_HOME/Network/admin.
The listener server instance listens for users in the network to access the local database. After configuration, LSNRCTL start.
Tnsnames is used to access databases of other network hosts. In Linux/Unix, pay attention to the Unix carriage return character. It is best to use
VI operation. In practical applications, incorrect carriage returns cannot be recognized by Oracle.


IX. Summary

All the problems mentioned in this article have appeared in the practice process. Here we only list the problems. The Oracle system is very complicated.
From the application perspective.