管理ORACLE實例

初始化參數文件：

-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 <comment='text'>

<deferred> <scope=memory|spfile|both><sid='sid|*'>

提示：若命令中不使用scope選項，scope選項缺省為both。

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

$ strings examplespfile.ora

DEFERRED(延遲生效)

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 <scope=memory|spfile|both> 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

參考聯機文檔：

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=<file_name>]

[MOUNT [EXCLUSIVE] <database_name>x |

OPEN <READ {ONLY | WRITE [RECOER]} | RECOVER><database_name>

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;

殺掉某一進程會話：

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

SQL> alter system kill session '40,9';  ## 40,9表示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

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