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

Initialize the parameter file:

-PFILE:Static parameter file,PFILE

-SPFILE:Persistent server parameter file,SPFILE

PFILE-- initSID.ora

Text file

Modified with an operating system editor

Modifications made manually

Changes take effect on the next start up

Only opened during instance startup

Default location is $ORACLE_HOME/dbs

Creating a PFILE

Created from a sample init.ora file

-Sample installed by the Oracle Universal Installer (OUI)

-Copy sample using operating system copy command

-Uniquely identified by database SID

$cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initexample.ora

Mofify the initSID.ora

-Edit the parameters

-Specific to database needs

Example

-Creating a pfile from spfile.

SQL > create pfile from spfile'

SPFILE- spfileSID.ora

Binary file

Maintained by the Oracle server

Always resides on the server side

Ability to make changes persistent across shutdown and start up

Can self-tune parameter vallues

Can have Recovery Manager support backing up to the initialization parameter file

Creteing an SPFILE

Created from a PFILE file

SQL > create spfile='$ORACLE_HOME/dbs/spfileexample.ora'from pfile='$ORACLE_HOME/dbs/initexample.ora'

Can be executed before or after instance start up

Modify Parameters in SPFILE:

SQL > ALTER system set parameter=value

Tip: if the scope option is not used in the command, the scope option defaults to both.

SQL > alter system set fast_start_mttr_target=250 comment='hello world'

$strings examplespfile.ora

DEFERRED (delayed effective)

SQL > select name,issys_modifiable from v$parameter where issys_modifiable='DEFERRED'

Modify Parameters in SPFILE

Changing parameter values

SQL > alter system set undo_tablespace=undots02

Specifying temporary or persistent changes

SQL > alter system set undo_tablespace=undots02 scope=both

Deleting or resetting values

SQL > alter system reset parameter_name sid='*'

SPFILE vs PFILE:

1.An SPFILE can be backed-up with RMAN (RMAN cannot backupPFILEs).

2.Reduce human errors.The SPFILE is maintained by theserver. Parameters are checked before changes are accepted.

3.Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine).

4.Easy to find stored in a central location ($ORACLE_HOME/dbs).

Note: SPFILE is better than PFILE!

STARTUP Command Behavior

Order of precedence:

-spfileSID.ora

-Default SPFILE → spfile.ora

-initSID.ora

Specified PFILE can override precedence.

SQL > STARTUP PFILE=$ORACLE_HOME/dbs/initexample.ora

PFILE can indicate to use SPFILE.

$cat $ORACLE_HOME/dbs/initDBA1.ORA

Spfile=$ORACLE_HOME/dbs/spfileDBA1.ora

Who can start the database?

Database startup and shutdown are powerful administrative options and are restricted to users who connect to Oracle Database with administrator privileges. Depending on the operating system, one of the following conditions establishes administrator privileges for a user:

The users's operating system privileges allow him or her to connect using administrator privileges.

The user is granted the SYSDBA or SYSOPER privileges and the database users

Password files to authenticate database administrators.

When you connect with SYSDBA privileges, you are in the schema owned by SYS. When you connect as SYSOPER, you are in the public schema. SYSOPER privileges are subset of SYSDBA privileges.

Starting Up a Database

Refer to the online documentation:

Http://docs.oracle.com/cd/E11882_01/server.112/e40540/startup.htm#CNCPT005

STARTUP Command

Start up the instance and open the database:

SQL > STARTUP

SQL > STARTUP PFILE=$ORACLE_HOME/dbs/initdb01.ora

STARTUP [FORCE] [RESTRICT] [NOMOUNT] [MIGRATE] [QUIET] [PFILE=]

[MOUNT [EXCLUSIVE] x |

OPEN

ALTER DATABASE Command

Change the state of the database from NOMOUNT to MOUNT:

SQL > ALTER DATABASE MOUNT

Open the database as a read-only database:

SQL > ALTER DATABASE OPEN READ ONLY

Restricted Mode:

Use the STARTUP command to restrict access to a database

SQL > STARTUP RESTRICT

Use the ALTER SYSTEM command to place an instance in restricted mode:

SQL > alter system enable restricted session

SQL > alter system disable restricted session

Kill a process session:

SQL > select sid,serial#,username from v$session

SQL > alter system kill session'40 record9; # # 40 recorder 9 means SID,SERIAL.

Read-OnlyMode

Opening a database in read-only mode:

SQL > startup mount

SQL > alter database open read only

Can be used to:

-Execute queries

-Execute disk sorts using locally managed tablespaces

-Take data files offline and online, but not tablespaces

-Perform recovery of offline data files and tablespaces

ShuttingDown the Database

Database Behavior

ABORT

IMMEDIATE

TRANSACTIONAL

NORMAL

Permits new user connections

No

No

No

No

Waits until current sessions end

No

No

No

Yes

Waits until current transactions end

No

No

Yes

Yes

Performs a checkpoint and closes open files

No

Yes

Yes

Yes

SQL > shutdown abort

SQL > shutdown immediate

SQL > shutdown transactional

SQL > shutdown normal

DiagnosticFiles

Diagnostic files

-Contain information about significant events

-Used to resolve problems

-Used to better manage the database on a day-to-day basis

Several types exist:

-alertSID.log file

-Background tracefiles

-User trace files

Alert Log File

AlertSID.log file:

-Records the commands

-Records results of major events

-Used for day-to-day operational information

-Used for diagnosingd atabase errors

Each entry has a time stamp associated with it

Must be managed by DBA

Location defined by BACKGROUND_DUMP_DEST

SQL > show parameter background_dump_dest

Enable/DisabledUser Tracing

Session level:

-Using the ALTERSESSION command:

SQL > alter session set sql_trace = true

Instance level:

-Setting the initialization parameter:

SQL_TRACE = TRUE

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.