RMAN备份与恢复之可脱机数据文件丢失
我们可以在SQL Plus中使USERS表空间离线，切记要先离线再进行下面的操作。因为在RMAN中我们有备份，所以我们可以在RMAN中对USERS
一 表空间讲解
在上一篇文章（RMAN备份与恢复之控制文件丢失 ），我们讲解了控制文件丢失并且RMAN中有备份怎么处理，这篇文章讲解可脱机数据文件丢失的处理。
表空间是一种逻辑结构，表空间下可以存放数据文件。表空间有系统表空间（SYSTEM）、辅助表空间（SYSAUX）、用户表空间（USERS）、回退表空间（UNDOTBS1）、临时表空间（TEMP）、测试表空间（EXAMPLE）、数据表空间。其中系统表空间用于存储数据字典的信息，不能脱机，不能只读，也不能改名。辅助表空间用于存储一些工具以及一些常用的组件，10G开始引入，不能脱机，可以只读，可以改名。用户表空间用于存储用户数据的表空间，可以脱机，可以只读，可以改名。回退表空间用于存储DML操作的事务信息，不能脱机，不能只读，可以改名。临时表空间用于存放临时数据，不能脱机，不能只读，可以改名。数据表空间用于存放自定义数据，项目中我们一般创建数据表空间，数据存放在数据表空间，这是一种很好的做法，切忌把数据放在默认的USERS表空间。
至于有些表空间可以脱机，有些不可以。那是因为数据库在运行期间需要向这些表空间写入数据，比如系统表空间、辅助表空间、回退表空间、临时表空间，而用户表空间、测试表空间、数据表空间不同，只要没有对这些表空间进行操作，是可以脱机的。
二 备份与恢复可脱机数据文件讲解
备份与恢复可脱机数据文件，首先我们要使用RMAN做一个备份全库的操作，然后模拟USERS表空间丢失，此时我们查看数据会提示数据文件找不到。因为USERS表空间可以脱机，所以我们可以在SQL Plus中使USERS表空间离线，切记要先离线再进行下面的操作。因为在RMAN中我们有备份，，所以我们可以在RMAN中对USERS表空间对应的数据文件编号进行还原，实际上后台就行进行了一个拷贝操作。还原成功后，我们需要对USERS表空间对应的数据文件编号进行恢复，等待恢复完成后，再在SQL Plus中使USERS表空间在线，所有的操作完成后，USERS表空间备份与恢复完成，我们可以对数据进行访问，测试是否恢复成功。需要注意的是，使表空间在线、离线、还原和恢复数据文件一些列的操作切记不要把编号弄错了，做这些操作应该确认USERS表空间的编号，默认是4号，但是由于后期的修改，USERS表空间数据文件的编号很不能不是4号。作为DBA，需要时刻保持谨慎！
三 模拟
Step1，RMAN中备份全库，并在SQL Plus确定数据的正确性
RMAN> BACKUP DATABASE;
Starting backup at 12-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/Oracle/oradata/justdb/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/justdb/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/justdb/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/justdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-DEC-13
channel ORA_DISK_1: finished piece 1 at 12-DEC-13
piece handle=/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp tag=TAG20131212T095816 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 12-DEC-13
channel ORA_DISK_1: finished piece 1 at 12-DEC-13
piece handle=/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_ncsnf_TAG20131212T095816_9bl62lw2_.bkp tag=TAG20131212T095816 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-DEC-13
[oracle@orcl backupset]$ ll 2013_12_12/
total 1025556
-rw------- 1 oracle oinstall 9830400 Dec 12 09:58 o1_mf_ncsnf_TAG20131212T095816_9bl62lw2_.bkp
-rw------- 1 oracle oinstall 1040334848 Dec 12 09:58 o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp
RMAN> LIST BACKUP;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 992.13M DISK 00:00:19 12-DEC-13
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20131212T095816
Piece Name: /u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1088247 12-DEC-13 /u01/oracle/oradata/justdb/system01.dbf
2 Full 1088247 12-DEC-13 /u01/oracle/oradata/justdb/sysaux01.dbf
3 Full 1088247 12-DEC-13 /u01/oracle/oradata/justdb/undotbs01.dbf
4 Full 1088247 12-DEC-13 /u01/oracle/oradata/justdb/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 9.36M DISK 00:00:01 12-DEC-13
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20131212T095816
Piece Name: /u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_ncsnf_TAG20131212T095816_9bl62lw2_.bkp
SPFILE Included: Modification time: 12-DEC-13
SPFILE db_unique_name: JUSTDB
Control File Included: Ckp SCN: 1088267 Ckp time: 12-DEC-13
SQL> CONN SCOTT/tiger;
Connected.
SQL> SELECT * FROM tab;
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How does Go language implement the addition, deletion, modification and query operations of the database?
Mar 27, 2024 pm 09:39 PM
Go language is an efficient, concise and easy-to-learn programming language. It is favored by developers because of its advantages in concurrent programming and network programming. In actual development, database operations are an indispensable part. This article will introduce how to use Go language to implement database addition, deletion, modification and query operations. In Go language, we usually use third-party libraries to operate databases, such as commonly used sql packages, gorm, etc. Here we take the sql package as an example to introduce how to implement the addition, deletion, modification and query operations of the database. Assume we are using a MySQL database.
How does Hibernate implement polymorphic mapping?
Apr 17, 2024 pm 12:09 PM
Hibernate polymorphic mapping can map inherited classes to the database and provides the following mapping types: joined-subclass: Create a separate table for the subclass, including all columns of the parent class. table-per-class: Create a separate table for subclasses, containing only subclass-specific columns. union-subclass: similar to joined-subclass, but the parent class table unions all subclass columns.
Detailed tutorial on establishing a database connection using MySQLi in PHP
Jun 04, 2024 pm 01:42 PM
How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())
iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos
Jul 18, 2024 am 05:48 AM
Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps
An in-depth analysis of how HTML reads the database
Apr 09, 2024 pm 12:36 PM
HTML cannot read the database directly, but it can be achieved through JavaScript and AJAX. The steps include establishing a database connection, sending a query, processing the response, and updating the page. This article provides a practical example of using JavaScript, AJAX and PHP to read data from a MySQL database, showing how to dynamically display query results in an HTML page. This example uses XMLHttpRequest to establish a database connection, send a query and process the response, thereby filling data into page elements and realizing the function of HTML reading the database.
Analysis of the basic principles of MySQL database management system
Mar 25, 2024 pm 12:42 PM
Analysis of the basic principles of the MySQL database management system MySQL is a commonly used relational database management system that uses structured query language (SQL) for data storage and management. This article will introduce the basic principles of the MySQL database management system, including database creation, data table design, data addition, deletion, modification, and other operations, and provide specific code examples. 1. Database Creation In MySQL, you first need to create a database instance to store data. The following code can create a file named "my
How to handle database connection errors in PHP
Jun 05, 2024 pm 02:16 PM
To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.
Tips and practices for handling Chinese garbled characters in databases with PHP
Mar 27, 2024 pm 05:21 PM
PHP is a back-end programming language widely used in website development. It has powerful database operation functions and is often used to interact with databases such as MySQL. However, due to the complexity of Chinese character encoding, problems often arise when dealing with Chinese garbled characters in the database. This article will introduce the skills and practices of PHP in handling Chinese garbled characters in databases, including common causes of garbled characters, solutions and specific code examples. Common reasons for garbled characters are incorrect database character set settings: the correct character set needs to be selected when creating the database, such as utf8 or u
