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:

u       Perform. an export or import of database data

u       Perform. a data load (with SQL*Loader)

u       Temporarily prevent typical users from using data

u       During 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:

l         Actions 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.

l         Actions 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.