& # 8203; instance management and database startup / shutdown 01/27 Update SLTechnology News&Howtos

& # 8203; instance management and database startup / shutdown

2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Instance management and database startup / shutdown

2.1 instance and parameter files

1. Instance function: used to manage and access database.

Instance reads the initialization parameter file (init parameter files) during the startup phase.

2. Init parameter files: manage instance-related startup parameters. Location: $ORACLE_HOME/dbs

3. Pfile: (parameter file) static parameter file.

1) text file, the parameters must be modified through the editor.

2) the modified parameters will not take effect until the instance is restarted next time.

3) the pfile parameter file may not be on the database server.

Naming method: init+SID.ora

4. Spfile: (system parameter file) dynamic parameter file.

1) binary files, which cannot be modified through the editor.

2) strings can be viewed under Linux.

3) it must be under the specified path of the database server segment.

Naming method: spfile+SID.ora

Static and dynamic parameters

After spfile reads memory, some parameters can be modified directly in memory and take effect immediately for the current instance. Such parameters are called dynamic parameters. Except for dynamic parameters, they are static parameters. Static parameters modify the spfile file. Dynamic parameters become invalid after instance shuts down, while static parameters do not take effect until the next time instance starts.

How to modify the spfile file:

Alter system set parameter = value [scope=memory | spfile | both]

The alter system reset parameter [scope=memory | spfile | both] SID='*' / / restores the default value.

The first scope=memory parameter modification takes effect immediately, but does not modify the spfile file.

The second scope=spfile modifies the spfile file, which takes effect after restart.

The third scope=both should be satisfied with the first two.

If you don't write scope, which is the default, that's the third.

* Note: if the spfile is not modified, the static parameters cannot be changed.

By looking at the v$parameter, you can determine which parameters can be modified in the memory and make the scope.

10:38:35 SQL > desc v$parameter

Where:

ISSYS_MODIFIABLE parameter: corresponding to alter system command, that is, system-level modification

10:38:35 SQL > select distinct issys_modifiable from v$parameter

ISSYS_MODIFIABLE

IMMEDIATE / / corresponding scope=memory

FALSE / / can only be scope=spfile, that is, modify the spfile file until the next startup.

DEFERRED / / other session is valid

ISSES_MODIFIABLE parameter: corresponding to the alter session command, that is, session-level modification

10:38:35 SQL > select distinct isses_modifiable from v$parameter

ISSES_MODIFIABLE

TRUE / / indicates that it can be modified

FALSE / / indicates that it cannot be modified

SQL > select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='sql_trace'

NAME ISSES_MODIFIABLE ISSYS_MODIFIABLE

Sql_trace TRUE IMMEDIATE

This result indicates that the sql_trace parameters can be changed at the session level or both at the system level (dynamic parameters).

How to determine whether a parameter is a dynamic parameter or a static parameter?

The first method: view the dynamic performance view v$parameter

ISSES_MODIFIABLE (session level)

TRUE / / dynamic parameters

FALSE / / static parameters

ISSYS_MODIFIABLE (database level)

IMMEDIATE / / dynamic parameters, corresponding to scope=memory

FALSE / / static parameters, only scope=spfile, that is, modify the spfile file, the next startup will take effect.

DEFERRED / / other session is valid

The second method: heuristic method

Give an example of 1:log_buffer

1) alter system set log_buffer=3145728 scope=both; (alter system set log_buffer=3145728;)

SQL > alter system set log_buffer=3145980 scope=both

Alter system set log_buffer=3145980 scope=both

An error occurred on line 1:

ORA-02095: the specified initialization parameters cannot be modified

The above parameters are static and need to be written into spfile:

SQL > alter system set log_buffer=5242880 scope=spfile

The system has changed.

SQL > show parameter log_buffer

NAME TYPE VALUE

Log_buffer integer 3145728

SQL >

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 595594832 bytes

Database Buffers 243269632 bytes

Redo Buffers 9326592 bytes

The database is loaded.

The database is already open.

SQL > show parameter log_buffer

NAME TYPE VALUE

Log_buffer integer 5242880

Example 2: (dynamic parameters)

Temporary outline

5. When startup, read the parameter file and find the $ORACLE_HOME/dbs directory. The order is to start with spfile first, and start from pfile without spfile.

Pfile and spfile can be generated from each other:

SQL > create pfile from spfile

SQL > create spfile from pfile (spfile,ORA-32002 cannot be generated online after starting with spfile: unable to create SPFILE that has been used by the instance)

* Note:

1) if you start with pfile, setting scope=spfile will fail! However, scope=memory can be set.

Pfile and spfile (new 11g features) can be generated from the current memory parameters:

SQL > create pfile from memory

SQL > create spfile from memory

With spfile,pfile, it is generally reserved for backup. In special cases, you can also use pfile to start. The command is as follows:

10:38:35 SQL > startup pfile=$ORACLE_HOME/dbs/inittimran.ora

How do I know if the instance is started by spfile or pfile?

10:38:35 SQL > show parameter spfile

NAME TYPE VALUE

Spfile string / u01/oracle/dbs/spfiletimran11g.ora

/ / if value has a value, it means that spfile is read when the database starts.

Another way is to look at the isspecified field value of parameter memory_target in v$spparameter (spfile parameter view). If it is TRUE, it is started by spfile (test site).

10:42:35 SQL > select name,value,isspecified from v$spparameter where name like 'memory_target'

NAME VALUE ISSPECIFIED

Memory_target 423624704 TRUE

EM has a good visual interface for the initial parameters, you can see

Oracle official document parameter file introduction: pfile,spfile

About Initialization Parameters and Initialization Parameter Files

When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. This file must at a minimum specify the DB_NAMEparameter. All other parameters have default values.

The initialization parameter file can be either a read-only text file, a PFILE, or a read/write binary file.

The binary file is called a server parameter file. A server parameter file enables you to change initialization parameters with ALTER SYSTEM commands and to persist the changes across a shutdown and startup. It also provides a basis for self-tuning by Oracle Database. For these reasons, it is recommended that you use a server parameter file. You can create one manually from your edited text initialization file, or automatically by using Database Configuration Assistant (DBCA) to create your database.

Before you manually create a server parameter file, you can start an instance with a text initialization parameter file. Upon startup, the Oracle instance first searches for a server parameter file in a default location, and if it does not find one, searches for a text initialization parameter file. You can also override an existing server parameter file by naming a text initialization parameter file as an argument of the STARTUP command.

Default file names and locations for the text initialization parameter file are shown in the following table:

PlatformDefault NameDefault LocationUNIX and LinuxinitORACLE_SID.ora

For example, the initialization parameter file for the mynewdb database is named:

Initmynewdb.ora

ORACLE_HOME/dbsWindowsinitORACLE_SID.oraORACLE_HOME\ database

If you are creating an Oracle database for the first time, Oracle suggests that you minimize the number of parameter values that you alter. As you become more familiar with your database and environment, you can dynamically tune many initialization parameters using the ALTER SYSTEM statement. If you are using a text initialization parameter file, then your changes are effective only for the current instance. To make them permanent, you must update them manually in the initialization parameter file, or they will be lost over the next shutdown and startup of the database. If you are using a server parameter file, then initialization parameter file changes made by the ALTER SYSTEM statement can persist across shutdown and startup.

Managing Initialization Parameters Using a Server Parameter File

Initialization parameters for the Oracle Database have traditionally been stored in a text initialization parameter file. For better manageability, you can choose to maintain initialization parameters ina binary server parameter file that is persistent across database startup and shutdown. This section introduces the server parameter file, and explains how to manage initialization parameters using either method of storing the parameters. The following topics are contained in this section.

What Is a Server Parameter File?

Migrating to a Server Parameter File

Creating a Server Parameter File

The SPFILE Initialization Parameter

Changing Initialization Parameter Values

Clearing Initialization Parameter Values

Exporting the Server Parameter File

Backing Up the Server Parameter File

Recovering a Lost or Damaged Server Parameter File

Viewing Parameter Settings

What Is a Server Parameter File?

A server parameter file can be thought of as a repository for initialization parameters that is maintained on the system running the Oracle Database server. It is, by design, a server-side initialization parameter file. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This arrangement eliminates the need to manually update initialization parameters to make persistent any changes effected by ALTER SYSTEM statements. It also provides a basis for self-tuning by the Oracle Database server.

A server parameter file is initially built from a text initialization parameter file using the CREATE SPFILE statement. (It can also be created directly by the Database Configuration Assistant.) The server parameter file is a binary file that cannot be edited using a text editor. Oracle Database provides other interfaces for viewing and modifying parameter settings in a server parameter file.

Caution:

Although you can open the binary server parameter file with a text editor and view its text, do not manually edit it. Doing so will corrupt the file. You will not be able to start your instance, and if the instance is running, it could fail.

When you issue a STARTUP command with no PFILE clause, the Oracle instance searches an operating system-specific default location for a server parameter file from which to read initialization parameter settings. If no server parameter file is found, the instance searches for a text initialization parameter file. If a server parameter file exists but you want to override it with settings in a text initialization parameter file, you must specify the PFILE clause when issuing theSTARTUP command. Instructions for starting an instance using a server parameter file are contained in "Starting Up a Database".

Migrating to a Server Parameter File

If you are currently using a text initialization parameter file, then use the following steps to migrate to a server parameter file.

If the initialization parameter file is located on a client system, then transfer the file (for example, FTP) from the client system to the server system.

Note:

If you are migrating to a server parameter file in an Oracle Real Application Clusters environment, you must combine all of your instance-specific initialization parameter files into a single initialization parameter file. Instructions for doing this and other actions unique to using a server parameter file for instances that are part of an Oracle Real Application Clusters installation are discussed in Oracle Real Application Clusters Administration and Deployment Guide and in your platform-specific Oracle Real Application Clusters Installation Guide.

Create a server parameter file in the default location using the CREATE SPFILE FROM PFILE statement. See "Creating a Server Parameter File" for instructions.

This statement reads the text initialization parameter file to create a server parameter file. The database does not have to be started to issue a CREATE SPFILE statement.

Start up or restart the instance.

The instance finds the new SPFILE in the default location and starts up with it.

Creating a Server Parameter File

You use the CREATE SPFILE statement to create a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement.

Note:

When you use the Database Configuration Assistant to create a database, it automatically creates a server parameter file for you.

The CREATE SPFILE statement can be executed before or after instance startup. However, if the instance has been started using a server parameter file, an error is raised if you attempt to re-create the same server parameter file that is currently being used by the instance.

You can create a server parameter file (SPFILE) from an existing text initialization parameter file or from memory. Creating the SPFILE from memory means copying the current values of initialization parameters in the running instance to the SPFILE.

The following example creates a server parameter file from text initialization parameter file / u01/oracle/dbs/init.ora. In this example no SPFILE name is specified, so the file is created with the platform-specific default name and location shown in Table 2-3.

CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora'

The next example illustrates creating a server parameter file and supplying a name and location.

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora' FROM PFILE='/u01/oracle/dbs/test_init.ora'

The next example illustrates creating a server parameter file in the default location from the current values of the initialization parameters in memory.

CREATE SPFILE FROM MEMORY

Whether you use the default SPFILE name and default location or specify an SPFILE name and location, if an SPFILE of the same name already exists in the location, it is overwritten without a warning message.

When you create an SPFILE from a text initialization parameter file, comments specified on the same lines as a parameter setting in the initialization parameter file are maintained in the SPFILE. All other comments are ignored.

Oracle recommends that you allow the database to give the SPFILE the default name and store it in the default location. This eases administration of your database. For example, the STARTUP command assumes this default location to read the SPFILE.

Table 2-3 shows the default name and location for both the text initialization parameter file (PFILE) and server parameter file (SPFILE) for the UNIX, Linux, and Windows platforms, both with and without the presence of Oracle Automatic Storage Management (Oracle ASM). The table assumes that the SPFILE is a file. If it is a raw device, the default name could be a logical volume name or partition device name, and the default location could differ.

Table 2-3 PFILE and SPFILE Default Names and Locations on UNIX, LInux, and Windows

PlatformPFILE Default NameSPFILE Default NamePFILE Default LocationSPFILE Default Location

UNIX and Linux

InitORACLE_SID.ora

SpfileORACLE_SID.ora

OH/dbs or the same location as the data filesFoot 1

Without Oracle ASM:

OH/dbs or the same location as the data filesFootref 1

When Oracle ASM is present:

In the same disk group as the data filesFoot 2

Windows

InitORACLE_SID.ora

SpfileORACLE_SID.ora

OH\ database

Without Oracle ASM:

OH\ database

When Oracle ASM is present:

In the same disk group as the data filesFootref 2

Footnote 1 OH represents the Oracle home directory

Footnote 2 Assumes database created with DBCA

Note:

Upon startup, the instance first searches for an SPFILE named spfileORACLE_SID.ora, and if not found, searches forspfile.ora. Using spfile.ora enables all Real Application Cluster (Oracle RAC) instances to use the same server parameter file.

If neither SPFILE is found, the instance searches for the text initialization parameter file initORACLE_SID.ora.

If you create an SPFILE in a location other than the default location, you must create in the default PFILE location a "stub" PFILE that points to the server parameter file. For more information, see "Starting Up a Database".

When you create the database with DBCA when Oracle ASM is present, DBCA places the SPFILE in an Oracle ASM disk group, and also causes this stub PFILE to be created.

The SPFILE Initialization Parameter

The SPFILE initialization parameter contains the name of the current server parameter file. When the default server parameter file is used by the database-that is, you issue a STARTUP command and do not specify a PFILE parameter-the value of SPFILE is internally set by the server. The SQL*Plus command SHOW PARAMETERS SPFILE (or any other method of querying the value of a parameter) displays the name of the server parameter file that is currently in use.

Changing Initialization Parameter Values

The ALTER SYSTEM statement enables you to set, change, or restore to default the values of initialization parameters. If you are using a text initialization parameter file, the ALTER SYSTEM statement changes the value of a parameter only for the current instance, because there is no mechanism for automatically updating text initialization parameters on disk. You must update them manually to be passed to a future instance. Using a server parameter file overcomes this limitation.

There are two kinds of initialization parameters:

Dynamic initialization parameters can be changed for the current Oracle Database instance. The changes take effect immediately.

Static initialization parameters cannot be changed for the current instance. You must change these parameters in the text initialization file or server parameter file and then restart the database before changes take effect.

Setting or Changing Initialization Parameter Values

Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. The optional SCOPE clause specifies the scope of a change as described in the following table:

SCOPE ClauseDescriptionSCOPE = SPFILEThe change is applied in the server parameter file only. The effect is as follows:

No change is made to the current instance.

For both dynamic and static parameters, the change is effective at the next startup and is persistent.

This is the only SCOPE specification allowed for static parameters.

SCOPE = MEMORYThe change is applied in memory only. The effect is as follows:

The change is made to the current instance and is effective immediately.

For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.

For static parameters, this specification is not allowed.

SCOPE = BOTHThe change is applied in both the server parameter file and memory. The effect is as follows:

The change is made to the current instance and is effective immediately.

For dynamic parameters, the effect is persistent because the server parameter file is updated.

For static parameters, this specification is not allowed.

It is an error to specify SCOPE=SPFILE or SCOPE=BOTH if the instance did not start up with a server parameter file. The default is SCOPE=BOTH if a server parameter file was used to start up the instance, and MEMORY if a text initialization parameter file was used to start up the instance.

For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

When you specify SCOPE as SPFILE or BOTH, an optional COMMENT clause lets you associate a text string with the parameter update. The comment is written to the server parameter file.

The following statement changes the maximum number of failed login attempts before the connection is dropped. It includes a comment, and explicitly states that the change is to be made only in the server parameter file.

ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=3 COMMENT='Reduce from 10 for tighter security.' SCOPE=SPFILE

The next example sets a complex initialization parameter that takes a list of attributes. Specifically, the parameter value being set is the LOG_ARCHIVE_DEST_ninitialization parameter. This statement could change an existing setting for this parameter or create a new archive destination.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_4='LOCATION=/u02/oracle/rbdb1/',MANDATORY,'REOPEN=2' COMMENT='Add new destination on Nov 29' SCOPE=SPFILE

When a value consists of a list of parameters, you cannot edit individual attributes by the position or ordinal number. You must specify the complete list of values each time the parameter is updated, and the new list completely replaces the old list.

Clearing Initialization Parameter Values

You can use the ALTER SYSTEM RESET command to clear (remove) the setting of any initialization parameter in the SPFILE that was used to start the instance. Neither SCOPE=MEMORY nor SCOPE=BOTH are allowed. The SCOPE = SPFILE clause is not required, but can be included.

You may want to clear a parameter in the SPFILE so that upon the next database startup a default value is used.

See Also:

Oracle Database Reference for a complete description of views

2.2 Database startup and shutdown:

2.2.1 Startup is divided into three phases

1) nomount phase: read init parameter (read initialization parameters, start instance)

10:38:35 SQL > select status from vault instance; (this command is very useful, depending on the current database startup status, there are three started,mounted,open)

STATUS

STARTED

2) mount phase: reading control files

20:32:53 SQL > select status from v$instance

STATUS

MOUNTED

3) open phase: 1. Check all datafile, redo log, group, password file.

2. Check the consistency of the database (whether the checkpoints of controlfile, datafile and redo file are consistent)

10:38:35 SQL > select file#,checkpoint_change# from vault datafile; / / read from the control file

FILE# CHECKPOINT_CHANGE#

1 570836

2 570836

3 570836

4 570836

5 570836

6 570836

6 rows selected.

10:38:35 SQL > select file#,checkpoint_change# from vested datafile headers; / / read from datafile header

FILE# CHECKPOINT_CHANGE#

1 570836

2 570836

3 570836

4 570836

5 570836

6 570836

6 rows selected.

Before the database open, check whether the SCN of the SCN and datafile header records recorded by controlfile is consistent; if the database is consistent, open the database normally. If there is inconsistency, you need to media recover.

10:38:35 SQL > select status from v$instance

STATUS

OPEN

2.2.2 some special options when starting the database

SQL > alter database open read only

SQL > startup force

SQL > startup upgrade (only sysdba can connect)

SQL > startup restrict (login is allowed only if you have restrict session permission, sys is not restricted)

SQL > alter system enable restricted session; (restrict after open)

2.2.3 instance shutting down:

Shutdown normal: rejects a new connection, waits for the current transaction to end, waits for the current session to end, and generates checkpoints

Shutdown transactional: rejects a new connection, waits for the current transaction to end, and generates a checkpoint

Shutdown immediate: reject new connections, roll back uncommitted transactions, and generate checkpoints

Shutdown abort (startup force): transactions are not rolled back, checkpoints are not generated, and instance recovery is required for the next startup

* Note: shutdown abort will not damage database.

2.3New features of automatic diagnosis information base ADR (Automatic Diagnostic Repository) 11g

A directory (tree) structure stored under the operating system, including: early warning log files, trace files, health checks, DUMP dump files, etc.

11g replaces many older versions of parameters, such as BACKGROUND_DUMP_DEST,CORE_DUMP_DEST,USER_DUMP_DEST, with a parameter of DIAGNOSTIC_DEST.

SQL > show parameter diag

NAME TYPE VALUE

Diagnostic_dest string / u01

Note: this is the base directory of ADR. If you set the ORACLE_BASE environment variable, the default value of diagnostic_dest is set to the same directory, otherwise, the directory set for you by oracle is $ORALE_HOME/log.

10:38:35 SQL > show parameter dump / / this is Oracle11g's.

SQL > show parameter dump

NAME TYPE VALUE

Background_core_dump string partial

Background_dump_dest string / u01/diag/rdbms/timran11g/timran11g/trace

Core_dump_dest string / u01/diag/rdbms/timran11g/timran11g/cdump

Max_dump_file_size string unlimited

Shadow_core_dump string partial

User_dump_dest string / u01/diag/rdbms/timran11g/timran11g/trace

In oracle 11g, the file paths of fault diagnosis and tracking change greatly, and the alarm files exist in two file formats, xml file format and plain text format. The locations of these two files are the directories corresponding to Diag Alert and Diag Trace in V$DIAG_INFO.

If you are familiar with 9i, you will find that 11g merges bdump and udump into one directory / u01/diag/rdbms/timran11g/timran11g/trace.

1) tracking file:

The naming method is still:

Background Trace Files (for bg process): SID_processname_PID.trc such as: timran11g_lgwr_5616.trc

User Trace Files (for server process): SID_ora_PID.trc such as: timran11g_ora_10744.trc

In addition, .trm (trace map) file is added to record the structure information of the trc file.

SQL > select * from v$diag_info

INST_ID NAME VALUE

1 Diag Enabled TRUE

1 ADR Base / u01

1 ADR Home / u01/diag/rdbms/timran11g/timran11g

1 Diag Trace / u01/diag/rdbms/timran11g/timran11g/trace

1 Diag Alert / u01/diag/rdbms/timran11g/timran11g/alert

1 Diag Incident / u01/diag/rdbms/timran11g/timran11g/incident

1 Diag Cdump / u01/diag/rdbms/timran11g/timran11g/cdump

1 Health Monitor / u01/diag/rdbms/timran11g/timran11g/hm

1 Default Trace File

1 Active Problem Count 0

1 Active Incident Count 0

Among them, the directory corresponding to Diag Trace is the alarm log and trace file in text format, and the naming method is used in 10g.

2) alarm log:

The text format alarm date is named alter_SID.log, which contains notification messages, such as database startup or shutdown, as well as information about tablespace creation and deletion, as well as some internal error messages.

With the continuous growth of alter_SID.log, regular cleaning is necessary.

$cat dev/null > alert_timran11g.log / / empty the file

It doesn't matter if you delete $rm alter_timran11g.log / /. The next time you start, it will be created automatically.

Check alarm logs and trace files for error messages, such as when lwgr cannot write log groups, a trace file is created and a message is placed in the alarm log.

[oracle@timran trace] $tail-f / u01/diag/rdbms/timran11g/timran11g/trace/alert_timran11g.log

Space available in the underlying filesystem or ASM diskgroup.

Tue Sep 04 09:12:19 2012

Completed: ALTER DATABASE OPEN

Tue Sep 04 09:16:41 2012

Starting background process CJQ0

Tue Sep 04 09:16:41 2012

CJQ0 started with pid=29, OS id=2483

Tue Sep 04 10:19:11 2012

Drop tablespace tb1

Completed: drop tablespace tb1

one hundred and twenty six。 Identify the two situations in which you use the alert log file in your database to check the details.

(Choose two.)

A.Running a query on a table returns "ORA-600: Internal Error."

B.Inserting a value in a table returns "ORA-01722: invalid number."

C.Creating a table returns "ORA-00955: name is already used by an existing object."

D.Inserting a value in a table returns "ORA-00001: unique constraint (SYS.PK_TECHP) violated."

E.Inserting a row in a table returns "ORA-00060: deadlock detected while waiting for resource."

Answer: AE

Answer analysis:

Each database has an alert_.log file. This file is located on the same server as the database, and if $ORACLE_BASE is set, it is stored in $ORACLE_BASE/diag/rdbms///trace by default.

Database alert files are log files that list messages in chronological order, for example:

Any non-default initialization parameters used at startup

All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that have occurred

Administrative operations, such as SQL statements CREATE, ALTER, DROP DATABASE and TABLESPACE, and Enterprise Manager or

SQL*Plus statements STARTUP, SHUTDOWN, ARCHIVE LOG and RECOVER

Multiple messages and errors related to the functionality of shared servers and dispatcher processes

Error occurred while automatically refreshing the materialized view

Oracle DB uses alert logs to keep records of these events as an alternative to displaying this information on the operator console. (many systems display this information in the console at the same time. If an administrative operation completes successfully, the system writes a "completed" message and a timestamp to the warning log.

The concept of ADR is more common in 053 because it involves some important knowledge of 11g in automatic database management, such as measurement, threshold, early warning system, health monitoring, etc., which we will continue to introduce in the 053 course.

2.4 password file

There are many ways to authenticate oracle login.

2.4.1 two commonly used login authentication methods of sys: OS authentication and password file authentication.

1) OS authentication: local authentication. Oracle does not verify the user's password if the user belongs to the DBA group and is logged in locally.

Such as: sqlplua / as sysdba

2) password file authentication: it is a way of network remote authentication, only users with sysdba authority can use password files, and must enter passwords and network connectors.

Such as: sqlplus sys/oracle@timran11g as sysdba

2.4.2 ordinary user login

1) ordinary users refer to users who do not have sysdba permission, such as system, scott, or tim, who must enter a password to log in. No matter it is local or remote, their passwords are not stored in a file, but kept by oracle in their internal data dictionary.

2) by setting this parameter to TURE, you can make the password case-sensitive (11g new feature)

SQL > show parameter case

NAME TYPE VALUE

Sec_case_sensitive_logon boolean TRUE

The password file discussed in this section is the remote login authentication password file for sysdba users, which is mainly used for remote login authentication of sys users.

Location: $ORACLE_HOME/dbs/orapwSID, the so-called password file, refers to the password file of sys, which can be controlled by the remote_login_passwordfile parameter.

Three modes of parameter remote_login_passwordfile:

1) none denies sys users from connecting remotely

2) exclusive sys users can connect remotely

3) multiple share libraries can share password files

[oracle@timran ~] $cd / u01/oracle/dbs

[oracle@timran dbs] $ll

Total 52

-rw-rw---- 1 oracle oinstall 1544 08-17 07:19 hc_timran11g.dat

-rw-r--r-- 1 oracle oinstall 12920 2001-05-03 initdw.ora

-rw-r--r-- 1 oracle oinstall 8385 1998-09-11 init.ora

-rw-r--r-- 1 oracle oinstall 1024 08-17 13:23 inittimran11g.ora

-rw-r- 1 oracle oinstall 24 08-17 07:21 lkTIMRAN11

-rw-r- 1 oracle oinstall 24 08-17 10:36 lkTIMRAN11G

-rw-r- 1 oracle oinstall 1536 08-31 10:47 orapwtimran11g

-rw-r- 1 oracle oinstall 3584 09-04 17:49 spfiletimran11g.ora

This is where you put the parameter file and the (sys) password file, and orapwtimran11g is my sys password file.

Use the orapwd command to create a new sys password file:

You can delete it first, then create it, and do it under linux:

[oracle@timran dbs] $rm orapwtimran11g / / delete the sys password file

[oracle@timran dbs] $orapwd file=orapwtimran11g password=sys entries=5 force=y / / re-establish the password file

Note: the way file=orapw+sid is written

Entries means the maximum number of users who can log in with SYSDBA/SYSOPER privileges that can be included in the password file.

2.5 add scott cases (SCOTT erroneous deletion and recovery, just execute the script)

Sometimes, scott users are deleted by mistake, and never mind, you can re-create it by executing the following script.

SQL > @ $ORACLE_HOME/rdbms/admin/utlsampl.sql

You can try it: delete the scott user first, and then execute the script.

/ / Supplementary explanation

For individual user and tablespace, this can be done using the following command.

Step 1: delete user

Drop user × × cascade

Note: if user is deleted, only the schema objects under the user is deleted, and the corresponding tablespace will not be deleted.

Step 2: delete tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.