如何冻结数据库

from metalink.

Goal

What is the behaviour of the following two SQL statements?
ALTER SYSTEM SUSPEND;
ALTER SYSTEM RESUME;

Fix

When SUSPEND / RESUME should be used
===============================
These two commands are used when the DB backup is to taken using a SnapView. A snapshot (NOT related to replication) is the snapshot of the datafiles under hotbackup mode, to an intermediate location.
The backup utility then forms the actual hotbackup files in the backup location that is done offline to DB.
Taking a snapshot of the DB is very fast and this time the backup utility (e.g EMC Clarion / Veritas) needs the DB Not to have any I/O.

The following command sets the DB in suspend mode where any form of I/O will suspend.
ALTER SYSTEM SUSPEND;
To get back the DB in normal mode we need to Resume it using:
ALTER SYSTEM RESUME;

Its only when we take backup of the DB using a snapview we need to use SUSPEND/RESUME. For normal hotbackup using of this command is Not recommended.

Normally the following sequence of operations are performed during a snapview backup:
// Put all the tablespaces under hotbackup mode
ALTER TABLESPACE system BEGIN BACKUP;
:::::::::::::::::::::::::::::::::::::::::::::::
ALTER SYSTEM SUSPEND;
// Take the snapview of the datafiles (This is very fast normally takes a few seconds for a medium sized DB)
ALTER SYSTEM RESUME;
// Put all the tablespaces under normal mode
ALTER TABLESPACE system END BACKUP;
:::::::::::::::::::::::::::::::::::::::::::::::

Behavior of SUSPEND / RESUME
========================
When we put the DB in SUSPEND mode, a flag in the instance is set which bars any form of I/O thereafter till its RESUMed or DB is restarted. Any DML / SELECE which can be catered with information from memory will be completed however. The RESUME command can be given from a different session.


We can run the SUSPEND / RESUME command multiple times but the information goes to the alert.log only once the first time the DB is suspended or resumed.

SQL> alter system suspend;
System altered.

SQL> select database_status from v$instance;
DATABASE_STATUS
-----------------
SUSPENDED


SQL> alter system resume;
System altered.

SQL> select database_status from v$instance;
DATABASE_STATUS
-----------------
ACTIVE



o We will do a FTS of scott.emp table before the DB in suspend
conn scott/tiger
select * from emp;

o From a sysdba connection we suspend the DB
conn / as sysdba
alter system suspend;

o From scott/tiger the "select * from emp;" will succeed as the required information is already available in shared pool and buffer cache

o But a select on dept table will Hang as this incurs I/O

o Even if we connect as SYSDBA the I/O restriction is there.

o shutdown immediate will also hang for a DB in SUSPEND mode

o shutdown abort however succeeds as it kills the instance (there by clearing the flag) and the does the I/O

o restarting the DB will clear the SUSPEND flag

o It has been reported that sometimes the new SYSDBA connections to a SUSPENDed 8i DB hangs (Bug: 1657819)
Here we can possibly use 'alter system flush shared_pool;' as an workaround. The issue has been fixed in 9i releases.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60560/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/936/viewspace-60560/