Taking Tablespaces Offline
For better performance while accessing data in a read-only tablespace, you can issue a
query that accesses all of the blocks of the tables in the tablespace just before making it read-only. A simple query, such as SELECT COUNT (*), executed against each table ensures that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for the database to check the status of the transactions that most recently modified the blocks.
You may want to take a tablespace offline for any of the following
reasons:
reasons:
- To make a portion of the database unavailable while allowing normal access to the remainder of the database perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)
- To make an application and its group of tables temporarily unavailable while updating or maintaining the application when a tablespace is taken offline, the database takes all the associated files offline.
take the following tablespaces offline: - SYSTEM
- The undo tablespace
- Temporary tablespaces
Before taking a tablespace offline, consider altering the tablespace allocation of any
users who have been assigned the tablespace as a default tablespace. Doing so is advisable because those users will not be able to access objects in the tablespace while it is offline.
users who have been assigned the tablespace as a default tablespace. Doing so is advisable because those users will not be able to access objects in the tablespace while it is offline.
You can specify any of the following parameters as part of the ALTER
TABLESPACE...OFFLINE statement:
TABLESPACE...OFFLINE statement:
Clause : NORMAL
Description :
A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace can be currently offline as the
result of a write error. When you specify OFFLINE NORMAL, the database takes a
checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL
is the default.
result of a write error. When you specify OFFLINE NORMAL, the database takes a
checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL
is the default.
Clause : TEMPORARY
Description :
A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When you specify OFFLINE TEMPORARY, the database takes offline the datafiles that are not already offline, checkpointing them as it does so.
If no files are offline, but you use the temporary clause, media recovery is not required to bring the tablespace back online.
However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online.
However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online.
Clause : IMMEDIATE
Description :
A tablespace can be taken offline immediately, without the database taking a checkpoint on any of the datafiles. When you specify OFFLINE IMMEDIATE, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in
NOARCHIVELOG mode.
NOARCHIVELOG mode.
Caution: If you must take a tablespace offline, use the NORMAL clause (the default) if possible. This setting guarantees that the tablespace will not require recovery to come
back online, even if after incomplete recovery you reset the redo log sequence
using an ALTER DATABASE OPEN RESETLOGS statement. Specify TEMPORARY only when you cannot take the tablespace offline normally. In this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Specify IMMEDIATE only after trying both the normal and temporary settings.
back online, even if after incomplete recovery you reset the redo log sequence
using an ALTER DATABASE OPEN RESETLOGS statement. Specify TEMPORARY only when you cannot take the tablespace offline normally. In this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Specify IMMEDIATE only after trying both the normal and temporary settings.
The following example takes the users tablespace offline normally:
ALTER TABLESPACE users OFFLINE NORMAL;
Bringing Tablespaces Online
You can bring any tablespace in an Oracle Database online whenever the database is
open. A tablespace is normally online so that the data contained within it is
available to database users.
open. A tablespace is normally online so that the data contained within it is
available to database users.
If a tablespace to be brought online was not taken offline "cleanly" (that is, using the NORMAL clause of the ALTER TABLESPACE OFFLINE statement), you must first perform media recovery on the tablespace before bringing it online.
Otherwise, the database returns an error and the tablespace remains offline.
Otherwise, the database returns an error and the tablespace remains offline.
ALTER TABLESPACE users ONLINE;
Using Read-Only Tablespaces Making a tablespace read-only prevents write operations on the datafiles in the tablespace. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database.
Read-only tablespaces also provide a way to protecting historical data so that users cannot modify it. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.
Read-only tablespaces also provide a way to protecting historical data so that users cannot modify it. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.
Note:
Making a tablespace read-only cannot in itself be used to satisfy archiving or data
publishing requirements, because the tablespace can only be brought online in the database in which it was created.
publishing requirements, because the tablespace can only be brought online in the database in which it was created.
You can drop items, such as tables or indexes, from a read-only tablespace, but you
cannot create or alter objects in a read-only tablespace. You can execute statements that update the file description in the data dictionary, such as ALTER TABLE...ADD or ALTER TABLE...MODIFY, but you will not be able to utilize the new description until the tablespace is made read/write.
cannot create or alter objects in a read-only tablespace. You can execute statements that update the file description in the data dictionary, such as ALTER TABLE...ADD or ALTER TABLE...MODIFY, but you will not be able to utilize the new description until the tablespace is made read/write.
Read-only tablespaces can be transported to other databases. And, since read-only
tablespaces can never be updated, they can reside on CD-ROM or WORM (Write
Once-Read Many) devices.
tablespaces can never be updated, they can reside on CD-ROM or WORM (Write
Once-Read Many) devices.
All tablespaces are initially created as read/write. Use the READ ONLY clause in the ALTER TABLESPACE statement to change a tablespace to read-only. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.
Before you can make a tablespace read-only, the following conditions must be met.
- The tablespace must be online. This is necessary to ensure that there is no undo information that needs to be applied to the tablespace.
- The tablespace cannot be the active undo tablespace or SYSTEM tablespace.
- The tablespace must not currently be involved in an online backup, because the end of a backup updates the header file of all datafiles in the tablespace.
For better performance while accessing data in a read-only tablespace, you can issue a
query that accesses all of the blocks of the tables in the tablespace just before making it read-only. A simple query, such as SELECT COUNT (*), executed against each table ensures that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for the database to check the status of the transactions that most recently modified the blocks.
The following statement makes the flights tablespace read-only:
ALTER TABLESPACE flights READ ONLY;
You can issue the ALTER TABLESPACE...READ ONLY statement while the database is
processing transactions. After the statement is issued, the tablespace is put into a transitional read-only state. No transactions are allowed to make further changes (using DML statements) to the tablespace. If a transaction attempts further changes, it is terminated and rolled back. However, transactions that already made changes and that attempt no further changes are allowed to commit or roll back.
processing transactions. After the statement is issued, the tablespace is put into a transitional read-only state. No transactions are allowed to make further changes (using DML statements) to the tablespace. If a transaction attempts further changes, it is terminated and rolled back. However, transactions that already made changes and that attempt no further changes are allowed to commit or roll back.
When there are transactions waiting to commit, the ALTER TABLESPACE...READ ONLY statement does not return immediately. It waits for all transactions started before you
issued the ALTER TABLESPACE statement to either commit or rollback.
issued the ALTER TABLESPACE statement to either commit or rollback.
Note:
This transitional read-only state only occurs if the value of the initialization parameter COMPATIBLE is 8.1.0 or greater. If this parameter is set to a value less than 8.1.0, the ALTER TABLESPACE ...READ ONLY statement fails if any active transactions exist.
If you find it is taking a long time for the ALTER TABLESPACE statement to complete, you can identify the transactions that are preventing the read-only state from taking effect. You can then notify the owners of those transactions and decide whether to terminate the transactions, if necessary.
The following example identifies the transaction entry for the ALTER TABLESPACE...READ ONLY statement and note its session address (saddr):
SELECT SQL_TEXT, SADDR FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS;
SQL_TEXT SADDR
---------------------------------------- --------
alter tablespace tbs1 read only
The start SCN of each active transaction is stored in the V$TRANSACTION view. Displaying this view sorted by ascending start SCN lists the transactions in execution
order. From the preceding example, you already know the session address of the
transaction entry for the read-only statement, and you can now locate it in the
V$TRANSACTION view. All transactions with smaller start SCN, which indicates an
earlier execution, can potentially hold up the quiesce and subsequent read-only
state of the tablespace.
order. From the preceding example, you already know the session address of the
transaction entry for the read-only statement, and you can now locate it in the
V$TRANSACTION view. All transactions with smaller start SCN, which indicates an
earlier execution, can potentially hold up the quiesce and subsequent read-only
state of the tablespace.
SELECT SES_ADDR, START_SCNB
FROM V$TRANSACTION
ORDER BY START_SCNB;
SES_ADDR START_SCNB
----------------- --------------------
800352A0 3621
--> waiting on this txn
--> waiting on this txn
80035A50 3623
--> waiting on this txn
--> waiting on this txn
80034AF0 3628
--> this is the ALTER TABLESPACE statement
--> this is the ALTER TABLESPACE statement
80037910 3629
--> don't care about this txn
--> don't care about this txn
After making the tablespace read-only, it is advisable to back it up immediately.
As long as the tablespace remains read-only, no further backups of the tablespace
are necessary, because no changes can be made to it.
are necessary, because no changes can be made to it.
Use the READ WRITE keywords in the ALTER TABLESPACE statement to change a tablespace to allow write operations. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.
A prerequisite to making the tablespace read/write is that all of the datafiles in the tablespace, as well as the tablespace itself, must be online. Use the DATAFILE...ONLINE
clause of the ALTER DATABASE statement to bring a datafile online. The V$DATAFILE
view lists the current status of datafiles.
clause of the ALTER DATABASE statement to bring a datafile online. The V$DATAFILE
view lists the current status of datafiles.
The following statement makes the flights tablespace writable:
ALTER TABLESPACE flights READ WRITE;
ALTER TABLESPACE flights READ WRITE;
Making a read-only tablespace writable updates the control file entry for the datafiles,
so that you can use the read-only version of the datafiles as a starting point for recovery.
so that you can use the read-only version of the datafiles as a starting point for recovery.
Creating a Read-Only Tablespace on a WORM Device
Follow these steps to create a read-only tablespace on a CD-ROM or WORM (Write
Once-Read Many) device.
Once-Read Many) device.
1) Create a writable tablespace on another device. Create the objects that belong in the tablespace and insert your data.
2)Alter the tablespace to make it read-only.
3) Copy the datafiles of the tablespace onto the WORM device. Use operating system commands to copy the files.
4) Take the tablespace offline.
5)5. Rename the datafiles to coincide with the names of the datafiles you copied onto your WORM device. Use ALTER TABLESPACE with the RENAME DATAFILE clause. Renaming the datafiles changes their names in the control file.
6) Bring the tablespace back online.
No comments:
Post a Comment