search
HomeDatabaseMysql Tutorial冷备下模拟rm -rf *.dbf恢复案例

关于备份恢复一直是所有关系型数据库的重头戏。下面会介绍冷备数据库,并模拟破坏数据文件进行恢复数据库,并涉及到其他相关内容

关于备份恢复一直是所有关系型数据库的重头戏。下面会介绍冷备数据库,并模拟破坏数据文件进行恢复数据库,并涉及到其他相关内容。
[Oracle@localhost ~]$ cat /etc/RedHat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)

SQL> select * from v$version where rownum

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
首先介绍一下冷备份(完全脱机备份),需要把数据库shutdown后,粘贴复制就可了,先备份先:
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

[oracle@localhost orcl3939]$ cp *.dbf  /home/oracle/beifeng
[oracle@localhost orcl3939]$ cp *.log  /home/oracle/beifeng
[oracle@localhost orcl3939]$ cp *.ctl  /home/oracle/beifeng
控制文件只需要备份一份就行了,因为是镜像文件,完全一样。
是不是很简单!
这种方式适合archivelog和noarchivelog,其中涉及到了几类文件:
logfile:v$log  v$logfile
controlfile:v$controlfile
datafile:dba_data_files
temp files:dba_temp_files
其中的临时文件并不是我们备份的对象,因为备份文件可以理解成数据库的虚拟内存,如linux中,我们分区时,如果内存较小时,可以分配swap分区,作用是交换缓存数据,作为内存不足的一种选择而已。
关于archivelog和noarchivelog,生产环境中几乎都是archivelog:
SQL> archive log list;
数据库日志模式            非存档模式
自动存档            禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列    424
当前日志序列          426

此时我的练习库是非归档模式,那我们启动归档模式:
SQL> startup mount;
ORACLE 例程已经启动。
SQL> alter database archivelog;
数据库已更改。

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

此时数据库已经是归档模式。
关于归档文件存放位置,看参数:

SQL> show parameter db_recover
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                _area
db_recovery_file_dest_size          big integer 3852M

第一个参数是存放位置,第二个参数是该空间的大小。
flash_recovery _area目录是10g才有的,便于管理归档等文件。
我们可以修改db_recovery_file_dest:
SQL> alter system set db_recovery_file_dest =' ';
系统已更改。
SQL>  show parameter db_recover
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size          big integer 3852M

此时归档文件存放的目录回到了10g之前:
SQL> show parameter log_archive_dest
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                    string
log_archive_dest_1                  string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string
log_archive_dest_2                  string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_29                  string
log_archive_dest_3                  string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                  string
log_archive_dest_5                  string
log_archive_dest_6                  string
log_archive_dest_7                  string
log_archive_dest_8                  string
log_archive_dest_9                  string

指定目录位置即可用,但是这样很不利于管理。

简介下SCN:System change number | System Commit Number 但是很多情况下理解成第一种更为准确。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

What are the different types of indexes in MySQL?What are the different types of indexes in MySQL?Apr 25, 2025 am 12:12 AM

There are four main index types in MySQL: B-Tree index, hash index, full-text index and spatial index. 1.B-Tree index is suitable for range query, sorting and grouping, and is suitable for creation on the name column of the employees table. 2. Hash index is suitable for equivalent queries and is suitable for creation on the id column of the hash_table table of the MEMORY storage engine. 3. Full text index is used for text search, suitable for creation on the content column of the articles table. 4. Spatial index is used for geospatial query, suitable for creation on geom columns of locations table.

How do you create an index in MySQL?How do you create an index in MySQL?Apr 25, 2025 am 12:06 AM

TocreateanindexinMySQL,usetheCREATEINDEXstatement.1)Forasinglecolumn,use"CREATEINDEXidx_lastnameONemployees(lastname);"2)Foracompositeindex,use"CREATEINDEXidx_nameONemployees(lastname,firstname);"3)Forauniqueindex,use"CREATEU

How does MySQL differ from SQLite?How does MySQL differ from SQLite?Apr 24, 2025 am 12:12 AM

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

What are indexes in MySQL, and how do they improve performance?What are indexes in MySQL, and how do they improve performance?Apr 24, 2025 am 12:09 AM

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Explain how to use transactions in MySQL to ensure data consistency.Explain how to use transactions in MySQL to ensure data consistency.Apr 24, 2025 am 12:09 AM

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function