Backup and recovery of read-only tablespace

-- ====================================
-- Backup and recovery of read-only tablespace
-- ====================================

I. Features of read-only tablespaces
Use read-only tablespace to avoid frequent backup of static data
When alter tablespace TBS read only is used, the data file will execute the Checkpoint Process (write all the contents of the dirty buffer to the disk ),
The current SCN number is marked, and the header of the data file storing the SCN is frozen. The frozen information of the data file is also recorded in the control file.
Read-Only tablespace objects can be cleared.

Ii. Backup of read-only tablespace
Generally, only one backup is required for the read-only tablespace. That is, when the tablespace status changes, backup immediately.
You can use the OS system CP command to back up or RMAN to back up read-only tablespaces.
We recommend that you enable backup optimization when using RMAN.
RMAN> Configure backup Optimization on;

Read-Only tablespace does not support Hot Backup
SQL> alter tablespace tbs1 begin backup;
Alter tablespace tbs1 begin backup
*
Error at line 1:
ORA-01642: Begin backup not needed for read only tablespace 'tbs1'

Iii. Restoration and restoration of read-only tablespaces
The problem with restoring and restoring read-only tablespaces is that the control file controls read-only tablespaces in the following three situations:
-----------------------------------------------------------------------------
Case Backup 1 crash status recovery
-----------------------------------------------------------------------------
Case 1 read-only copies the backup read-only tablespace to the destination (Restore)
Case 2 read-only read-write first restore backup1, then recover (applied log)
Case 3 read-write read-only first restore backup1, then recover (applied log)

Read-Only tablespace recovery considerations
When recreating a control file
When renaming a data file
When a backup control file is used

The following table space tbs1 is set to read-only, and the script for recreating the control file is compared before and after the table space tbs1 is set to read-only.

SQL> alter Database Backup controlfile to trace as '/tmp/rectl1. SQL'; </P> <p> SQL> alter tablespace tbs1 read only; </P> <p> SQL> alter Database Backup controlfile to trace as '/tmp/rectl2. SQL '; </P> <p> SQL> Ho diff/tmp/rectl1. SQL/tmp/rectl2. SQL <br/> 69, 70c69 <br/> <'/u01/APP/Oracle/oradata/orcl/example01.dbf ', <br/> <'/u01/APP/Oracle/oradata/orcl/tbs01.dbf' <br/> --- <br/>'/u01/APP/Oracle/oradata/orcl /example01.dbf' <br/> 97a97, 102 <br/> -- files in read-only tablespaces are now named. <br/> alter database rename file 'missing00006 '<br/> to'/u01/APP/Oracle/oradata/orcl/tbs01.dbf '; <br/> -- online the files in read-only tablespaces. <br/> alter tablespace "tbs1" online; <br/>

Comparison
1. When the create controlfile command is used, the data files of the read-only tablespace are not listed in datafile.
2. After the control file is successfully created and opened, run the alter database rename FILE command to rename the data file of the read-only tablespace.
3. Use alter tablespace readonly_tablespacename online to bring the read-only tablespace online

Iv. demonstrate restoration of read-only tablespace changes
1. demonstrate that the entire process is a read-only tablespace (corresponding to Case 1 described above)

SQL> Create Table Scott. tb1 tablespace tbs1 <br/> 2 as select * from Scott. EMP; </P> <p> SQL> commit; </P> <p> SQL> alter tablespace tbs1 read only; </P> <p> SQL> SELECT FILE #, name, enabled from V $ datafile where file # = 6; </P> <p> file # Name Enabled <br/> ---------- certificate ---------- <br/> 6/u01/APP/Oracle/oradata/orcl/tbs01.dbf read only </P> <p> SQL> Ho CP/u01/APP/Oracle/oradata/orcl/tbs01.dbf/tmp/tbs01.dbf </P> <p> SQL> insert into scott. tb1 (empno, ename) values (3333, 'Thomas '); </P> <p> SQL> Update Scott. tb1 set sal = Sal * 1.2 where ename = 'Scott '; </P> <p> SQL> Delete from Scott. tb1 where ename = 'Scott '; <br/> -- run the preceding three commands and receive the same error message <br/> ORA-00372: file 6 cannot be modified at this time <br/> ORA-01110: data file 6: '/u01/APP/Oracle/oradata/orcl/tbs01.dbf' <br/>

From the above demonstration, we can see that any DML operation on the data in the read-only tablespace is not available.
In
In the Oracle tablespace and data file article, you can perform the delete operation on the read-only tablespace (Version: 10.2.0.1.0 ).
Database patch issues. This version is 10.2.0.4.0.

-- Open with vim? /Oradata/orcl/tbs01.dbf file to simulate the destruction of the read-only tablespace data file <br/> -- after the database is restarted received the following error message <br/> ORA-01157: cannot identify/lock data file 6-see dbwr trace file <br/> ORA-01110: data file 6: '/u01/APP/Oracle/oradata/orcl/tbs01.dbf' </P> <p> SQL> Ho CP/tmp/tbs01.dbf/u01/APP/Oracle/oradata/orcl/ tbs01.dbf </P> <p> SQL> alter database open; </P> <p> SQL> select count (1) from Scott. tb1; </P> <p> count (1) <br/> ---------- <br/> 16 <br/>

2. Demonstrate the restoration of a read-only backup that is damaged after it is changed from read-only to read/write (corresponding to case 2 described above)

SQL> alter tablespace tbs1 read write; </P> <p> SQL> insert into scott. tb1 (empno, ename) values (3333, 'Thomas '); </P> <p> SQL> commit; </P> <p> -- use Vim to open the/u01/APP/Oracle/oradata/orcl/tbs01.dbf file and perform any operations to simulate data files that damage the read/write tablespace. <br/> -- the error message is not received after the database is restarted </P> <p> SQL> insert into scott. tb1 (empno, ename) values (4444, 'Jackson '); <br/> insert into scott. tb1 (empno, ename) values (4444, 'Jackson ') <br/> * <br/> error at line 1: <br/> ORA-00376: file 6 cannot be read at this time <br/> ORA-01110: data file 6: '/u01/APP/Oracle/oradata/orcl/tbs01.dbf' </P> <p> SQL> select * from V $ recover_file; </P> <p> file # online _ error change # time <br/> ---------- ------- ---------------- ---------- --------- <br/> 6 offline file not found 0 </P> <p> SQL> Ho CP/tmp/tbs01.dbf/u01/APP/Oracle/oradata/orcl/tbs01.dbf </P> <p> SQL> recover datafile 6; <br/> media recovery complete. </P> <p> SQL> alter tablespace tbs1 online; </P> <p> tablespace altered. <br/> SQL> select * from Scott. tb1 where ename = 'Thomas '; </P> <p> empno ename job Mgr hiredate Sal comm deptno <br/> ---------- --------- ---------- <br/> 3333 Thomas <br/>

3. demonstrate how to recover from a read/write tablespace to a read-only tablespace with only backup of the read/write tablespace (corresponding to case 3 described above)

SQL> SELECT FILE #, name, enabled from V $ datafile where file # = 6; </P> <p> file # Name Enabled <br/> ---------- certificate ---------- <br/> 6/u01/APP/Oracle/oradata/orcl/tbs01.dbf read write </P> <p> SQL> alter tablespace tbs1 begin backup; </P> <p> SQL> Ho CP/u01/APP/Oracle/oradata/orcl/tbs01.dbf/tmp/tbs01.dbf </P> <p> SQL> alter tablespace tbs1 end backup; </P> <p> SQL> Del Ete from Scott. tb1 where empno = 3333; </P> <p> SQL> commit; </P> <p> SQL> alter tablespace tbs1 read only; </P> <p> -- open with vim? /Oradata/orcl/tbs01.dbf file to simulate the destruction of the read-only tablespace data file <br/> -- reboot the database and receive the following error message </P> <p> ORA-01157: cannot identify/lock data file 6-see dbwr trace file <br/> ORA-01110: data file 6: '/u01/APP/Oracle/oradata/orcl/tbs01.dbf' </P> <p> SQL> Ho CP/tmp/tbs01.dbf/u01/APP/Oracle/oradata/orcl/ tbs01.dbf </P> <p> SQL> recover datafile 6; </P> <p> SQL> alter database open; </P> <p> SQL> select * from Scott. tb1 where ename = 'Thomas '; </P> <p> No rows selected <br/>

4. demonstrate that the status of the data file has changed multiple times and there is no Backup Recovery Processing during the change (in fact, the log is used to reconstruct the data file)

SQL> SELECT FILE #, name, enabled from V $ datafile where file # = 6; </P> <p> file # Name Enabled <br/> ---------- certificate ---------- <br/> 6/u01/APP/Oracle/oradata/orcl/tbs01.dbf read write </P> <p> SQL> drop table Scott. tb1; </P> <p> SQL> commit; </P> <p> SQL> alter tablespace tbs1 read only; </P> <p> SQL> alter tablespace tbs1 read write; </P> <p> SQL> Create Table Scott. tb2 tablespace tbs1 as select * from Scott. EMP; </P> <p> SQL> commit; </P> <p> SQL> alter system checkpoint; </P> <p> [Oracle @ oradb orcl] $ tail-N 50 $ oracle_base/admin/orcl/bdump/alert_orcl.log <br/> errors in file/u01/APP/ oracle/admin/orcl/bdump/orcl_ckpt_4064.trc: <br/> ORA-01171: datafile 6 going offline due to error advancing checkpoint <br/> ORA-01122: database file 6 failed verification check <br/> ORA-01110: data file 6: '/u01/APP/Oracle/oradata/orcl/tbs01.dbf' ORA-01251: unknown File Header version read for file number 6 </P> <p> SQL> select * from V $ recover_file; </P> <p> file # online _ error change # time <br/> ---------- ------- --------------- ---------- --------- <br/> 6 offline file not found 0 </P> <p> SQL> SELECT FILE #, name, status from V $ datafile where file # = 6; </P> <p> file # name status <br/> ---------- certificate ------- <br/> 6/u01/APP/Oracle/oradata/orcl/tbs01.dbf recover </P> <p> SQL> alter database create datafile 6; </P> <p> SQL> recover datafile 6; <br/> media recovery complete. </P> <p> SQL> alter database datafile 6 online; </P> <p> SQL> select count (1) from Scott. tb2; </P> <p> count (1) <br/> ---------- <br/> 16 <br/>

5. Demonstrate deleting objects in read-only tablespace

SQL> SELECT FILE #, name, enabled from V $ datafile where file # = 6; </P> <p> file # Name Enabled <br/> ---------- certificate ---------- <br/> 6/u01/APP/Oracle/oradata/orcl/tbs01.dbf read only </P> <p> SQL> select segment_name, segment_type, tablespace_name, owner from dba_segments where <br/> 2 tablespace_name = 'tbs1 'and segment_name = 'tb2 '; </P> <p> segment_name segment_type tablespace_name owner <br/> -------------------- ------------------ ------------ <br/> tb2 table tbs1 Scott </P> <p> SQL> drop table Scott. tb2; </P> <p> table dropped. <br/>

V. Summary
1. When the tablespace is set to read-only, the data backup volume will be reduced.
2. Once the tablespace is set to read-only, no DML operations can be performed on the objects in the tablespace.
3. Objects in the read-only tablespace can be cleared because the drop command updates the data dictionary without updating the object itself.
4. When the tablespace status changes, immediately back up the tablespace to reduce recovery
5. If the status changes multiple times and the logs are not backed up in time, you can use online redo and archive logs to restore the logs if they are not damaged.
Run the following command:
Delete the damaged data file (RM dbfile. DBF)
Alter database create datafile n)
Restore media (recover datafile N)
Bringing damaged data files online (alter database datafile n online)
6. In the demo, the system recovers from the Mount State and from the open state in the production environment. perform the following steps:
Remove the damaged read-only tablespace (data file) offline)
Use the backup tablespace (data file) to restore (Restore)
Use archived and online logs for media recovery (recover)
Bring restored tablespaces (data files) Online)
7. If the original media is damaged and cannot be recovered to the original position, use the following command to transfer the original media.
Alter database rename File '<dir1>' to '<dir2> ';

Vi. Quick Reference

For performance optimization, see

Oracle hard parsing and soft Parsing

Sharedpool Tuning)

Buffercache adjustment and optimization (1)

Use of Oracle table cache (cachingtable)

 

For the Oracle architecture, see

Oracle tablespace and data files

Oracle Password File

Oracle parameter file

Oracle online redo log file)

Oracle Control File)

Oracle archiving logs

Oracle rollback and undo)

Oracle database instance startup and Shutdown Process

Automated Management of Oracle10g SGA

Oracle instances and Oracle databases (Oracle Architecture)

 

For more information about the flash back feature, see

Flashback Database)

Flashback drop & recyclebin)

Oracle flash back features (flashback query, flashbacktable)

Oracle flash back feature (flashback version, flashback transaction)

 

For more information about user-managed backup and recovery, see

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management (describes media recovery and processing in detail)

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

 

For information on RMAN backup recovery and management, see

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use rmancatalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

Use RMAN to migrate a file system database to ASM

RMAN backup path confusion (when using plus archivelog)

 

For Oracle faults, see

Error Handling for ORA-32004

ORA-01658 error.

CRS-0215 error handling

ORA-00119, ORA-00132 error handling

Another spfile setting error causes the database to fail to start.

Misunderstanding and setting of the parameter fast_start_mttr_target = 0

Spfile error causing database startup failure (ORA-01565)

 

For more information about ASM, see

Create an ASM instance and an ASM Database

Management of ASM disks and directories

Use asmcmd to manage the ASM directory and files

 

For more information about SQL and PLSQL, see

Common sqlplus commands

Replace variables with SQL * Plus Environment Settings

SQL plus paging using uniread

SQL Basics--> SELECT query

SQL Basics--> Use of new_value

SQL Basics--> Set operation (Union and Union all)

SQL Basics--> Common functions

SQL Basics--> View (createview)

SQL Basics--> Create and manage tables

SQL Basics--> Multi-Table query

SQL Basics--> Filtering and sorting

SQL Basics--> Subquery

SQL Basics--> Grouping and grouping Functions

SQL Basics--> Hierarchical query (startby... connect by prior)

SQL Basics--> Rollup and cube operators implement data aggregation

PL/SQL--> Cursor

PL/SQL--> Exception Handling)

PL/SQL--> Language basics

PL/SQL--> Process Control

PL/SQL--> PL/SQL records

PL/SQL--> Create and manage packages

PL/SQL--> Implicit cursor (SQL % found)

PL/SQL--> Package overloading and initialization

PL/SQL--> Use of dbms_ddl package

PL/SQL--> DML triggers

PL/SQL--> Instead of trigger

PL/SQL--> Stored Procedure

PL/SQL--> Function

PL/SQL--> Dynamic SQL

PL/SQL--> Common Errors of dynamic SQL

 

Other Oracle features

Common Oracle directory structure (10 Gb)

Use OEM, SQL * Plus, and iSQL * Plus to manage Oracle instances

Logging mode (logging, force logging, nologging)

Logging and nologging on table and index segments

Oralceomf Functions

Oracle users, object permissions, and system Permissions

Oracle role and configuration file

Oracle Partition Table

Oracle External table

Use external tables to manage Oracle alarm logs (alaert _ $ Sid. Log)

Cluster table and cluster Table Management (index clustered tables)

Use of Data Pump expdp export tool

Use of Data Pump impdp import tool

Import and Export Oracle Partition Table Data

SQL * loader usage

Enable User Process Tracking

Configure dynamic service registration for non-default ports

Configure the Oracle client to connect to the database

Difference between systemsys and sysoper sysdba

Oracle_sid, db_name, instance_name, db_domian, global_name

Complete Oracle patches (Oracle 9i 10g 11g path)

Upgrade oracle10.2.0.1 to 10.2.0.4

Oracle kill session