Oracle数据库什么模式,Oracle 数据库的各种状态和模式

Oracle数据库的各种状态和模式

1 options for Starting Up a Database

SQL*PLUS

Recovery Manager

Oracle Enterprise Manager

You can use these three methods to start up a database.

2 preparing to start an Instance

"sqlplus /nolog" to start SQL*PLUS without connecting to the database

"connect username/password as SYSDBA " connect to Oracel as SYSDBA

3启动数据库时可以启动到不同的状态：

3.1 START NOMOUNT：根据初始化参数建立SGA,只读取初始化参数文件.

3.2 START MOUNT：根据初始化参数建立SGA。加载数据库，读取控制文件信息。

3.3 START：等价于STARTUP OPEN根据初始化参数建立SGA。加载数据库，读取控制文件信息。打开所有数据文件，数据库可以进行访问。

3.4 START RESTRICT

Restricted Mode:只有具有Create SESSION系统权限和RESTRICTED SESSION权限的用户(一般来讲是DBA)能访问数据.

You can start an instance and mount and open a database in restricted mode so that the database is available only to administrative personnel (not general database users). Use this mode of database startup when you need to accomplish one of the following tasks:

uPerform. an export or import of database data

uPerform. a data load (with SQL*Loader)

uTemporarily prevent typical users from using data

uDuring certain migration and upgrade operations

Start an instance (and, optionally, mount and open the database) in restricted mode by using the STARTUP command with the RESTRICT option:

STARTUP RESTRICT

Later, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION feature:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

3.5 STARTUP FORCE：等价于SHUTDOWN ABORT + STARTUP。

3.6 STARTUP OPEN READ ONLY：以只读方式打开数据库.只许不产生redo log的操作.

Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes. While opening a database in read-only mode guarantees that datafile and redo log files are not written to, it does not restrict database recovery or operations that change the state of the database without generating redo. For example, you can take datafiles offline or bring them online since these operations do not affect data content.

If a query against a database in read-only mode uses temporary tablespace, for example to do disk sorts, then the issuer of the query must have a locally managed tablespace assigned as the default temporary tablespace. Otherwise, the query will fail.

The following statement opens a database in read-only mode:

ALTER DATABASE OPEN READ ONLY;

You can also open a database in read-write mode as follows:

ALTER DATABASE OPEN READ WRITE;

4关闭数据库的不同方法：

4.1 SHUTDOWN：等价于SHUTDOWN NORMAL不允许新的会话登陆，等所有的连接都退出后关闭数据库。

4.2 SHUTDOWN TRANSACTIONAL：不允许新的会话登陆，当所有当前事务结束时，断开所有连接用户。

4.3 SHUTDOWN IMMEDIATE：不允许新的会话登陆，所有没有提交的事务全部回滚，断开所有连接用户。

4.4 SHUTDOWN ABORT：不允许新的会话登陆，当前运行的SQL语句立即中止，没有提交的事务不进行回滚，断开所有连接用户。下次重起时，Oracle自动进行实例恢复(instance recovery)。

5数据库的不同状态：

5.1 QUIESCED状态：只DBA的事务可以继续进行,其它非DBA的事务停在那里,但不会返回任何错.对终端用户来说,好像数据库很慢,暂停了.当恢复正常状态时,所有停顿的事务得以继续进行.

当sys或system用户发出ALTER SYSTEM QUIESCE RESTRICTED语句，所有的非DBA用户在当前会话状态变为

inactive时，进入到停顿状态。当执行ALTER SYSTEM UNQUIESCE，其他用户的会话恢复。 Only DBA transactions, queries, fetches, or PL/SQL statements are allowed. All non-DBA logins after this statement is issued are queued by the Database Resource Manager.

注意：只有SYS和SYSTEM用户有ALTER SYSTEM QUIESCE RESTRICTED的权限。其实是由于DBA角色的其他用户也没有这个权限。

执行这个操作的前提是：自从本次数据库启动以来，必须一直设置着资源限制。

To place a database into a quiesced state, issue the following statement:

ALTER SYSTEM QUIESCE RESTRICTED

Actions are suitable in Queisced State:

lActions that can fail if concurrent user transactions access the same object. For example, changing the schema of a database table or adding a column to an existing table where a no-wait lock is required.

lActions whose undesirable intermediate effect can be seen by concurrent user transactions. For example, a multistep procedure for reorganizing a table where the table is first exported, then dropped, and finally imported. A concurrent user who attempted to access the table after it was dropped, but before import, would see disturbing results.

5.2 SUSPEND状态：发出ALTER SYSTEM SUSPEND语句后，任何I/O操作都被停止，直到发出ALTER SYSTEM RESUME语句。

The ALTER SYSTEM SUSPEND statement suspends a database by halting all input and output (I/O) to datafiles (file header and file data) and control files, thus allowing a database to be backed up without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state.

对数据库状态的查询：

SQL> select database_status from v$instance;

DATABASE_STATUS

-----------------

SUSPENDED

Notes:

1 You cannot start a database instance if you are connected to the database through a shared server process.