Table space status (2). Table space status
Tablespace status-read only, READ WRITE
1. The primary purpose of the read-only tablespace is to eliminate the need for backup and recovery of most static data in the database. Oracle does not update read-only tablespaces that love your files, so these files can be stored in Read-Only media such as CD-ROM or WORM drives.
2. Read-Only tablespaces are not designed to meet archiving requirements. The read-only tablespace cannot be modified. If you need to modify the records in the read-only tablespace, you must first set the tablespace to read/write. After the tablespace is updated, it can be reset to read-only.
3. Because the read-only tablespace cannot be modified, as long as it is not set to read/write, it does not need to be backed up repeatedly. In addition, if you need to recover the database, you do not need to recover the read-only tablespace because they have not been modified.
4. objects such as tables or indexes can be deleted from read-only tablespaces, but objects cannot be created or modified. You can execute the statement to modify the file description in the data dictionary, such as alter table... ADD or alter table... MODIFY, but no new description information can be added, unless the tablespace is set to read/write.
5. Read-Only tablespace can be exported and imported to other databases. Since read-only tablespaces cannot be modified, they can be stored in CD-ROM or WORM (one write-multiple reads) facilities.
6. read/write is used for initial creation of all tablespaces. You can use the read only clause to set the tablespace to READ-ONLY. The premise is that you must have system permissions for alter tablespace or manage tablespace.
Before using alter tablespace... read only, the following conditions must be met:
> The tablespace is online. This is to ensure that no UNDO information needs to be applied to the tablespace. NOTE: If it is offline, the UNDO information will be stored in the SYSTEM tablespace, And the UNDO information will be applied when it is restored to online.
> The active UNDO or SYSTEM tablespace cannot be modified.
> The tablespace cannot be in the currently in-progress online backup because the header information of all data files in the tablespace is updated at the end of the backup.
> To Improve the Performance of reading data from the read-only tablespace, You can query all data blocks in the table that accesses the tablespace before setting it to read-only. A simple query like select count (*) ensures the optimal efficiency of data blocks in the tablespace during subsequent access. This approach does not require the database to check the Transaction Status of recently modified data blocks.
7. You can execute the alter tablespace... read only statement when the database is processing the transaction. After the statement is executed, the tablespace is in the read-only transaction status. No transactions (DML operations) are allowed to be applied to tablespaces. If a transaction is attempted, the operation is terminated and rolled back. However, transactions that have made changes and do not make further changes are allowed to perform the commit or roll back operation.
If alter tablespace... before the read only statement is executed, a transaction has been executed, but rolled back to a storage point and rolled back his changes to the table space. Then alter tablespace... the read only statement does not wait for the transaction of this activity.
8. Transaction-level read-only status can be used only when the initialization parameter COMPATIBLE is 8.1.0 or above. If the parameter value is less than 8.1.0 and an active transaction exists, the alter tablespace... read only statement fails.
9. If the execution time of the atler tablespace statement is too long, you need to find the transactions that prevent the read-only status from taking effect. The following statement finds the transaction entry and session address (saddr) for executing the alter tablespace... raed only statement ):
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 starting SCN of each activity TRANSACTION is stored in the V $ TRANSACTION View. The smaller the starting SCN, the earlier the operation. The more likely this statement will block subsequent changes to the read-only status.
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
You can use the following statement to find the user blocking the transaction:
Select t. SES_ADDR, S. USERNAME, S. MACHINE
From v $ session s, V $ TRANSACTION T
Where t. SES_ADDR = S. SADDR
Order by t. SES_ADDR
SES_ADDR USERNAME MACHINE
------------------------------------------------
800352A0 David B David blap --> Contact this user
80035A50 MIKEL LAB61 --> Contact this user
80034AF0 DBA01 STEVEFLAP
80037910 NICKD NICKDLAP
We recommend that you back up the tablespace immediately after you set it to read-only. As long as the tablespace remains in the read-only status, you do not need to back up again because there will be no updates to the table space.
10. Use the later tablespace... read write statement to restore the READ-only TABLESPACE, provided that it has the alter tablespace or manage tablespace permission.
When the tablespace is set to read/write, all data files and tablespaces in the tablespace must be online. You can use the alter database... DATAFILE... ONLINE statement to set the data file to online. The V $ DATAFILE view displays the current status of the data file.
To change the tablespace to writable mode, you need to update the control file to use the read-only version of the data file as the recovery start point.
11. Create a read-only tablespace in the WORM device.
(1) create a writable tablespace. Create an object and insert data.
(2) set the tablespace to read-only.
(3) run the operating system command to copy the tablespace data file to the WORM device.
(4) set the tablespace to offline.
(5) Rename the data file to comply with the naming rules for the data file copied to the WORM device. Use the alter tablespace... rename datafile statement to modify the control file.
(6) restore the tablespace to the online status.
Lab:
1. Set the tablespace to read only.:
SQL> alter tablespace dcsopen_tbs read only;
Tablespace altered.
2. view the data file status:
SQL> select file #, name, status from v $ datafile where file # = 11;
FILE # NAME STATUS
------------------------------------------------------------------------------------
11/oracle/oradata_petest/petest/dcsopen_tbs02.dbf ONLINE
3. log on to the read-only tablespace:
SQL> select * from test1;
T1ID T1V
--------------------
1 t1
2 t2
3 t3
You can read the tables in the table.
SQL> insert into test1 values (12, 't12 ');
Insert into test1 values (12, 't12 ')
*
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: '/oracle/oradata_petest/petest/dcsopen_tbs01.dbf'
When a transaction is executed, the system prompts that the data file No. 7 cannot be modified.
How to cancel ROLL-forward pending status db2 55039 error, state no. 0X0080
If the tablespace is temporarily suspended due to LOAD, you can change the tablespace status to normal by backing up the tablespace. The command is as follows:
Db2 connect to dbname user db2admin
Prompt for password:
Db2 backup database dbname tablespace (tablespace name)
Run the following command to check the table space status. The status has changed to normal.
Db2 list tablespaces
How can I back up and restore an Oracle database with the same tablespace as the two usernames?
Oracle backup methods:
1). Export/Import (EXP/IMP ).
2). Hot Backup.
3). Cold backup.
Note: Exporting spare parts is a logical backup, while cold backup and hot backup are physical backup.
1. Export/Import (Export/Import)
The Export can be used to extract data from the database, and the Import can be used to send the extracted data back to the Oracle database.
1. Simple Export of data (Export) and Import of data (Import)
Oracle supports three types of output:
(1) Export the data of the specified table in the T mode.
Exp amis/amis @ amisdb tables = B _S_AGT_LNE_ACCT_H_0714 file = B _S_AGT_LNE_ACCT_H_0714.dmp
Imp odsbdata/odsbdata @ amisdb file = B _S_AGT_DEP_ACCT_TDE_H_0714.dmp full = y ignore = y
(2) user mode (U mode), which exports all objects and data of the specified user.
(3) Full database mode (Full mode). All objects in the databases are exported.
The data Import process is the inverse process of data Export (Export). The data files are imported into the database and the database data is exported to the data file respectively.
2. incremental Export/Import
Incremental export is a common data backup method. It can only be implemented for the entire database and must be exported as a SYSTEM. The default export file name is export. dmp. If you do not want your output file to be named export. dmp, you must specify the file name to use in the command line.
Incremental export includes three types:
(1) Complete incremental Export (Complete)
Three databases are backed up, for example:
Exp system/manager inctype = complete file = 040731.dmp
(2) incremental Export
Back up the data changed after the last backup, for example:
Exp system/manager inctype = incremental file = 040731.dmp
(3) "accumulative" incremental Export
The accumulative export mode is used to export the changed information in the database after the last "full" export. For example:
Exp system/manager inctype = cumulative file = 040731.dmp
The database administrator can schedule a backup schedule and export data in three different ways.
For example, you can make the following arrangements for database blocking tasks:
Monday: Full backup ()
Tuesday: Incremental Export (B)
Wednesday: Incremental Export (C)
Thursday: Incremental Export (D)
Friday: accumulative Export (E)
Saturday: Incremental Export (F)
Sunday: Incremental Export (G)
If the database is accidentally damaged on Sunday, the database administrator can follow these steps to reply to the database:
Step 1: Use the create database Command to regenerate the DATABASE structure;
Step 2: create a large enough additional rollback.
Step 3: import A in full Increments:
Imp system/manager inctype = restore full = y FILE =
Step 4: Accumulative incremental import E:
Imp system/manager inctype = restore full = y file = E
Step 5: import the latest incremental F:
Imp system/manager inctype = restore full = y file = F
Ii. Cold backup
The cold backup occurs when the database is shut down normally. When the full text is...>
