| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
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.
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. However, you can meet such requirements by using the transportable tablespace feature, as described in "Transporting Tablespaces Between Databases".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.
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.
The following topics are discussed in this section:
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.
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.
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
AND SQL_TEXT LIKE 'alter tablespace%';
SQL_TEXT SADDR
---------------------------------------- --------
alter tablespace tbs1 read only 80034AF0
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.
SELECT SES_ADDR, START_SCNB
FROM V$TRANSACTION
ORDER BY START_SCNB;
SES_ADDR START_SCNB
-------- ----------
800352A0 3621 --> waiting on this txn
80035A50 3623 --> waiting on this txn
80034AF0 3628 --> this is the ALTER TABLESPACE statement
80037910 3629 --> 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.
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.
The following statement makes the flights tablespace writable:
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.
Follow these steps to create a read-only tablespace on a CD-ROM or WORM (Write Once-Read Many) device.
Create a writable tablespace on another device. Create the objects that belong in the tablespace and insert your data.
Alter the tablespace to make it read-only.
Copy the datafiles of the tablespace onto the WORM device. Use operating system commands to copy the files.
Take the tablespace offline.
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.
Bring the tablespace back online.
When substantial portions of a very large database are stored in read-only tablespaces that are located on slow-access devices or hierarchical storage, you should consider setting the READ_ONLY_OPEN_DELAYED initialization parameter to TRUE. This speeds certain operations, primarily opening the database, by causing datafiles in read-only tablespaces to be accessed for the first time only when an attempt is made to read data stored within them.
Setting READ_ONLY_OPEN_DELAYED=TRUE has the following side-effects:
A missing or bad read-only file is not detected at open time. It is only discovered when there is an attempt to access it.
ALTER SYSTEM CHECK DATAFILES does not check read-only files.
ALTER TABLESPACE...ONLINE and ALTER DATABASE DATAFILE...ONLINE do not check read-only files. They are checked only upon the first access.
V$RECOVER_FILE, V$BACKUP, and V$DATAFILE_HEADER do not access read-only files. Read-only files are indicated in the results list with the error "DELAYED OPEN", with zeroes for the values of other columns.
V$DATAFILE does not access read-only files. Read-only files have a size of "0" listed.
V$RECOVER_LOG does not access read-only files. Logs they could need for recovery are not added to the list.
ALTER DATABASE NOARCHIVELOG does not access read-only files.It proceeds even if there is a read-only file that requires recovery.
Notes:
RECOVER DATABASE and ALTER DATABASE OPEN RESETLOGS continue to access all read-only datafiles regardless of the parameter value. If you want to avoid accessing read-only files for these operations, those files should be taken offline.
If a backup control file is used, the read-only status of some files may be inaccurate. This can cause some of these operations to return unexpected results. Care should be taken in this situation.
