Read-Only Tablespaces
This paper is a discussion of read-only tablespaces. It covers the benefits
and drawbacks of having read-only tablespaces as well as several recovery
scenarios.
Definition:
----------
Starting with V7.1, you can now convert (alter) a tablespace to be read-only.
A read-only tablespace allows users to do only reads from the tables within it.
No data manipulation is allowed. This read-only option also causes the
database to not write to these files once their tablespace is altered to be
read-only. This allows users to take advantage of media that allows for
read-only operations such as CD-Roms and Write Once Read Many (WORM) drives.
Advantages/disadvantages:
------------------------
The advantage of having a read-only tablespace is that it doesn't have to
be backed up with the regular backups. Once a tablespace is altered to
read-only, a backup of this tablespace is taken. No other backups will be
needed unless it is altered back to read-write. No recover will be needed for
this tablespace because there were no changes made to it, thus it
can be simply altered online. (A full discussion of backup and recovery of
read-only tablespaces later in this paper).
When to make a tablespace read-only?
Because of this read-only restriction, only certain types of data will be
possible for a read-only tablespace. The information in these tablespaces need
to be stagnant, things like lookup tables or historical information are ideal
for this type of configuration.
Consider a billing application, every month's data is in a separate table.
The billing clerks will only be entering information for the current month,
but may receive inquiries on the billing of the previous months. The DBA
creates two tablespaces, Current_billing and Past_billing, at the begining
of every month, the dba creates a table in the current_billing tablespace
for the new month ahead, and moves the previous month's table to past_billing.
The past_billing tablespace is a good candidate for a read-only tablespace.
Of course, the dba will have to convert it to read-write to move the previous
month's billing table into it, but as soon as this is done, he can alter it
back to read-only, and take a backup. For that whole month, no backup is
needed of this Past_billing tablespace.
A read-only tablespace can be set up for many different scenarios, however, you
do not want to set up a tablespace read-only that needs to be constantly
changed to read-write. A good rule of thumb, is if you are altering the
read-only tablespace as many times as you backup the rest of the database,
then you need to ask, if you are not saving in the maintenance of
backups, then what are you getting from the read-only tablespace?? ability
to use special media types?? extra security to ensure users are not changing
the data of the tables in this tablespace?? Answering these types of questions
will help you decide if you wish to make this a read-only tablespace.
Prerequisites:
-------------
In order to alter a tablespace to read-only, the following conditions must be
met:
1. The tablespace must be online.
2. The tablespace cannot contain an active rollback segment.
3. The tablespace must not be in begin backup mode.
4. The COMPATIBLE init.ora parameter must be set to 7.1.0 or higher.
5. There cannot be any active transactions in the database at the time you are
altering the tablespace to read-only. This restriction is on the entire
database, not just the tablespace being altered to read-only.
Backup and Recovery:
-------------------
When a tablespace is altered to read-only the datafiles for this tablespace are
marked with a read-only status in the control file with a stop scn. In
addition, the datafile header is also marked with the same stop scn. Finally,
the data dictionary table TS$ is also updated.
When you go to the backup of the file and try to bring it online, Oracle will
compare the stop scn from the datafile header to that of the control file.
If they are the same, no recovery is needed. When using the current control
file, recovery is simple. You need to offline the read-only datafiles and
start recovery. If there is a discrepancy between the two, then some sort
of recovery will need to applied.
This is why it is ESSENTIAL to make a backup of the datafile(s) once a
tablespace is altered to be read-only. After this time, no other backups need
to be taken unless the tablespace is altered back to read-write.
When using backup control files, if you do not know if the read-only tablespace
has toggled between read-only and read-write, you may want to play it safe
by recreating your control file with the resetlogs option. This is described
in detail as scenario #4 below.
When using a backup controlfile, oracle has no recognition of the stop SCN,
therefore it expects the DBA to tell it when to stop. It will also not be able
to do instance recovery, so at some point it will ask for an archive file you
may not have. At this point, you can submit the online redo log file. If
you select the wrong one, you may see an error like:
Applying logfile...
ORA-00310: archived log contains sequence 35; sequence 36 required
Try again until you get the right sequence number.
The following are some scenarios of read-only tablespace recovery:
1. You have:
backup of read-only tablespace file when it is read-only.
current control file showing tablespace is read-only
To recover you must:
take read-only tablespace offline
restore the datafile backup
alter the tablespace online
In this scenario, no recovery is needed for the tablespace regardless of how
old the backup is. No redo is needed to bring the datafile(s) to current
status since it was read-only and no changes could have taken place in it.
If you try to issue recovery, you will receive an error indicating that no
recovery is needed.
SQLDBA> recover tablespace reonly;
ORA-00283: Recovery session canceled due to errors
ORA-00264: no recovery required
2. You have:
backup of read-only tablespace when it was read-only.
current control file shows tablespace as read-write.
tablespace is currently read-write.
To recover you must:
take the tablespace offline
issue the recover tablespace command
Oracle will prompt you for all archive files since the tablespace was made
writeable to the current log. It will apply information from the online logs,
so it is possible that it will not ask for all archives generated.
3. You have:
backup of read-only tablespace when it was read-write.
current control file shows tablespace as read-only
tablespace is currently read-only
To recover you must:
take the tablespace offline
issue the recover tablespace command
Oracle will ask for archive files from datafile backup to when it was altered
to read-only. If during the time between the backup and the time of recovery,
the tablespace was made read-only, then oracle will also ask for archives when
the file was read-only. For example, lets say that the datafile backup at
11 (read-write), and the tablespace was read-only from 16-19, then read-write
from 20-24, finally read-only again at 24. Recovery needs 11-24. The point
is that although the tablespace was read-only from 16-19, those archives are
not skipped, they will still be required for recovery.
4. You have:
backup of control file showing the tablespace read-write (20)
the datafile backup is when tablespace was read-only(16)
The tablespace is currently read-only(24)
archives up to 27 --
to recover:
recover database using backup controlfile
NOTE: see using backup controlfile above.
It will request the first archive file when the tablespace was read-write
and continue through the current redo log file. To open you must use
resetlogs option, 'alter database open resetlogs'.
When the resetlogs option is used, this negates all previous backups. The
sequence number is reset to 1. If you must use this option, it is essential to
take a full backup of the database.
5. You have:
backup of control file showing the tablespace read-only
the datafile backup is when the tablespace was read-write
The tablespace is currently read-only
You will not be able to recover using this control file, if you try to
issue recovery, you will get an error like:
SQLDBA> recover database using backup controlfile
ORA-00283: Recovery session canceled due to errors
ORA-01233: file 3 is read only - cannot recover using backup controlfile
ORA-01110: data file 3: '/u06/bugmnt/tar9212155.4_2/reonly.dbf'
To recover you must recreate the control file:
alter database backup controlfile to trace;
A trace file will be generated with the create controlfile statement. Delete
all other information from this trace file. It is essential to add the
read-only datafile with the others. It will be listed further down from the
create statement indicating the file is MISSING. Finally, be sure to create
the control file with 'resetlogs' option.
If don't specify resetlogs with the create control file you may see:
SQLDBA> @control
ORA-01503: CREATE CONTROLFILE failed
ORA-01229: data file 2 is inconsistent with logs
ORA-01110: data file 2: '/u06/bugmnt/tar9212155.4_2/rbsT716.dbf'
Once the control file has been recreated, to recover:
Issue recover database using backup control file
NOTE: see using backup controlfile above.
Oracle will ask for archive for the read-only datafile when it was read-write
to the current log. Once recovery has completed, issue 'alter database open
resetlogs'.
When the resetlogs option is used, this negates all previous backups. The
sequence number is reset to 1. If you must use this option, it is essential to
take a full backup of the database.
It is important to note that the initial check of the control file for
read-only tablespaces will effect all recovery, not just if you are recovering
the read-only tablespace. For example, if you are recovering a different
tablespace with the backup control file, the recovery will not be able to
proceed because of the read-only tablespace. If your intention is to recover
a different tablespace, you have two options.
- If you know that the read-only tablespace is consistent with the control
file, you can simply take the datafile offline before proceeding with the
recovery. You can use the 'alter database datafile 'xxx' offline'. After
the database is restarted, then you need to alter the read-only tablespace
online to allow access of it's data.
- You can recreate the control file as described above.
출처:http://www.fors.com/orasupp/rdbms/dba/33402_1.HTM
This paper is a discussion of read-only tablespaces. It covers the benefits
and drawbacks of having read-only tablespaces as well as several recovery
scenarios.
Definition:
----------
Starting with V7.1, you can now convert (alter) a tablespace to be read-only.
A read-only tablespace allows users to do only reads from the tables within it.
No data manipulation is allowed. This read-only option also causes the
database to not write to these files once their tablespace is altered to be
read-only. This allows users to take advantage of media that allows for
read-only operations such as CD-Roms and Write Once Read Many (WORM) drives.
Advantages/disadvantages:
------------------------
The advantage of having a read-only tablespace is that it doesn't have to
be backed up with the regular backups. Once a tablespace is altered to
read-only, a backup of this tablespace is taken. No other backups will be
needed unless it is altered back to read-write. No recover will be needed for
this tablespace because there were no changes made to it, thus it
can be simply altered online. (A full discussion of backup and recovery of
read-only tablespaces later in this paper).
When to make a tablespace read-only?
Because of this read-only restriction, only certain types of data will be
possible for a read-only tablespace. The information in these tablespaces need
to be stagnant, things like lookup tables or historical information are ideal
for this type of configuration.
Consider a billing application, every month's data is in a separate table.
The billing clerks will only be entering information for the current month,
but may receive inquiries on the billing of the previous months. The DBA
creates two tablespaces, Current_billing and Past_billing, at the begining
of every month, the dba creates a table in the current_billing tablespace
for the new month ahead, and moves the previous month's table to past_billing.
The past_billing tablespace is a good candidate for a read-only tablespace.
Of course, the dba will have to convert it to read-write to move the previous
month's billing table into it, but as soon as this is done, he can alter it
back to read-only, and take a backup. For that whole month, no backup is
needed of this Past_billing tablespace.
A read-only tablespace can be set up for many different scenarios, however, you
do not want to set up a tablespace read-only that needs to be constantly
changed to read-write. A good rule of thumb, is if you are altering the
read-only tablespace as many times as you backup the rest of the database,
then you need to ask, if you are not saving in the maintenance of
backups, then what are you getting from the read-only tablespace?? ability
to use special media types?? extra security to ensure users are not changing
the data of the tables in this tablespace?? Answering these types of questions
will help you decide if you wish to make this a read-only tablespace.
Prerequisites:
-------------
In order to alter a tablespace to read-only, the following conditions must be
met:
1. The tablespace must be online.
2. The tablespace cannot contain an active rollback segment.
3. The tablespace must not be in begin backup mode.
4. The COMPATIBLE init.ora parameter must be set to 7.1.0 or higher.
5. There cannot be any active transactions in the database at the time you are
altering the tablespace to read-only. This restriction is on the entire
database, not just the tablespace being altered to read-only.
Backup and Recovery:
-------------------
When a tablespace is altered to read-only the datafiles for this tablespace are
marked with a read-only status in the control file with a stop scn. In
addition, the datafile header is also marked with the same stop scn. Finally,
the data dictionary table TS$ is also updated.
When you go to the backup of the file and try to bring it online, Oracle will
compare the stop scn from the datafile header to that of the control file.
If they are the same, no recovery is needed. When using the current control
file, recovery is simple. You need to offline the read-only datafiles and
start recovery. If there is a discrepancy between the two, then some sort
of recovery will need to applied.
This is why it is ESSENTIAL to make a backup of the datafile(s) once a
tablespace is altered to be read-only. After this time, no other backups need
to be taken unless the tablespace is altered back to read-write.
When using backup control files, if you do not know if the read-only tablespace
has toggled between read-only and read-write, you may want to play it safe
by recreating your control file with the resetlogs option. This is described
in detail as scenario #4 below.
When using a backup controlfile, oracle has no recognition of the stop SCN,
therefore it expects the DBA to tell it when to stop. It will also not be able
to do instance recovery, so at some point it will ask for an archive file you
may not have. At this point, you can submit the online redo log file. If
you select the wrong one, you may see an error like:
Applying logfile...
ORA-00310: archived log contains sequence 35; sequence 36 required
Try again until you get the right sequence number.
The following are some scenarios of read-only tablespace recovery:
1. You have:
backup of read-only tablespace file when it is read-only.
current control file showing tablespace is read-only
To recover you must:
take read-only tablespace offline
restore the datafile backup
alter the tablespace online
In this scenario, no recovery is needed for the tablespace regardless of how
old the backup is. No redo is needed to bring the datafile(s) to current
status since it was read-only and no changes could have taken place in it.
If you try to issue recovery, you will receive an error indicating that no
recovery is needed.
SQLDBA> recover tablespace reonly;
ORA-00283: Recovery session canceled due to errors
ORA-00264: no recovery required
2. You have:
backup of read-only tablespace when it was read-only.
current control file shows tablespace as read-write.
tablespace is currently read-write.
To recover you must:
take the tablespace offline
issue the recover tablespace command
Oracle will prompt you for all archive files since the tablespace was made
writeable to the current log. It will apply information from the online logs,
so it is possible that it will not ask for all archives generated.
3. You have:
backup of read-only tablespace when it was read-write.
current control file shows tablespace as read-only
tablespace is currently read-only
To recover you must:
take the tablespace offline
issue the recover tablespace command
Oracle will ask for archive files from datafile backup to when it was altered
to read-only. If during the time between the backup and the time of recovery,
the tablespace was made read-only, then oracle will also ask for archives when
the file was read-only. For example, lets say that the datafile backup at
11 (read-write), and the tablespace was read-only from 16-19, then read-write
from 20-24, finally read-only again at 24. Recovery needs 11-24. The point
is that although the tablespace was read-only from 16-19, those archives are
not skipped, they will still be required for recovery.
4. You have:
backup of control file showing the tablespace read-write (20)
the datafile backup is when tablespace was read-only(16)
The tablespace is currently read-only(24)
archives up to 27 --
to recover:
recover database using backup controlfile
NOTE: see using backup controlfile above.
It will request the first archive file when the tablespace was read-write
and continue through the current redo log file. To open you must use
resetlogs option, 'alter database open resetlogs'.
When the resetlogs option is used, this negates all previous backups. The
sequence number is reset to 1. If you must use this option, it is essential to
take a full backup of the database.
5. You have:
backup of control file showing the tablespace read-only
the datafile backup is when the tablespace was read-write
The tablespace is currently read-only
You will not be able to recover using this control file, if you try to
issue recovery, you will get an error like:
SQLDBA> recover database using backup controlfile
ORA-00283: Recovery session canceled due to errors
ORA-01233: file 3 is read only - cannot recover using backup controlfile
ORA-01110: data file 3: '/u06/bugmnt/tar9212155.4_2/reonly.dbf'
To recover you must recreate the control file:
alter database backup controlfile to trace;
A trace file will be generated with the create controlfile statement. Delete
all other information from this trace file. It is essential to add the
read-only datafile with the others. It will be listed further down from the
create statement indicating the file is MISSING. Finally, be sure to create
the control file with 'resetlogs' option.
If don't specify resetlogs with the create control file you may see:
SQLDBA> @control
ORA-01503: CREATE CONTROLFILE failed
ORA-01229: data file 2 is inconsistent with logs
ORA-01110: data file 2: '/u06/bugmnt/tar9212155.4_2/rbsT716.dbf'
Once the control file has been recreated, to recover:
Issue recover database using backup control file
NOTE: see using backup controlfile above.
Oracle will ask for archive for the read-only datafile when it was read-write
to the current log. Once recovery has completed, issue 'alter database open
resetlogs'.
When the resetlogs option is used, this negates all previous backups. The
sequence number is reset to 1. If you must use this option, it is essential to
take a full backup of the database.
It is important to note that the initial check of the control file for
read-only tablespaces will effect all recovery, not just if you are recovering
the read-only tablespace. For example, if you are recovering a different
tablespace with the backup control file, the recovery will not be able to
proceed because of the read-only tablespace. If your intention is to recover
a different tablespace, you have two options.
- If you know that the read-only tablespace is consistent with the control
file, you can simply take the datafile offline before proceeding with the
recovery. You can use the 'alter database datafile 'xxx' offline'. After
the database is restarted, then you need to alter the read-only tablespace
online to allow access of it's data.
- You can recreate the control file as described above.
출처:http://www.fors.com/orasupp/rdbms/dba/33402_1.HTM