只要存在有效的备份，恢复SYSTEM表空间数据文件丢失故障是比较容易的。这里演示的是最基本的使用物理备份恢复SYSTEM表空间丢失的
只要存在有效的备份，恢复SYSTEM表空间数据文件丢失故障是比较容易的。这里演示的是最基本的使用物理备份恢复SYSTEM表空间丢失的方法。
1.环境准备
我们在Oracle11g中进行测试，数据库处于非归档状态。
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6
Current log sequence 8
SQL>
2.打tar包，进行物理备份
首先要弄清两个概念：打包和压缩。打包是指将一大堆文件或目录变成一个总的文件；压缩则是将一个大的文件通过一些压缩算法变成一个小文件。linux下最常用的打包程序就是tar了，使用tar程序打出来的包我们常称为tar包，tar包文件的命令通常都是以.tar结尾的。生成tar包后，就可以用其它的程序来进行压缩。
我们使用tar命令将HOEGH数据库的物理文件打tar包，命名为HOEGH.tar.gz。注意，物理备份必须是在数据库关停阶段进行。
[oracle@hoegh oradata]$ tar -zcvf HOEGH.tar.gz HOEGH
HOEGH/
HOEGH/redo03.log
HOEGH/temp01.dbf
HOEGH/control01.ctl
HOEGH/control02.ctl
HOEGH/system01.dbf
HOEGH/sysaux01.dbf
HOEGH/users01.dbf
HOEGH/undotbs01.dbf
HOEGH/example01.dbf
HOEGH/redo02.log
HOEGH/redo01.log
3.启动数据库，删除system数据文件
下面，我们来模拟system数据文件丢失的故障场景。
首先，启动数据库。
SQL> startup
ORACLE instance started.
Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 515902212 bytes
Database Buffers 419430400 bytes
Redo Buffers 4919296 bytes
Database mounted.
Database opened.
SQL>
接下来，删除system01.dbf数据文件。
[oracle@hoegh HOEGH]$ rm system01.dbf
[oracle@hoegh HOEGH]$
4.重启数据库报错ORA-01157和ORA-01110
删除数据文件后，我们重启数据库，数据库在尝试启动到open状态时，由于找不到system表空间的数据文件，报错。
SQL>
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 515902212 bytes
Database Buffers 419430400 bytes
Redo Buffers 4919296 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'
SQL>
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>
我们看到，这个时候数据库处于mount状态。查看报警日志文件，我们可以更清晰的看到整个过程。
[oracle@hoegh trace]$ pwd
/u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace
[oracle@hoegh trace]$
[oracle@hoegh trace]$ tailf alert_HOEGH.log
……
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 2106090167
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Sat Jul 11 09:01:47 2015
ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_dbw0_6016.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_ora_6135.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'
ORA-1157 signalled during: ALTER DATABASE OPEN...
5.恢复数据文件
我们需要把之前的数据备份恢复到数据库当中，因此，首先我们就要解tar包，恢复之前备份的数据文件；然后，将备份的system数据文件拷贝到HOEGH数据文件目录当中。
[oracle@hoegh oradata]$ mkdir -p back
[oracle@hoegh oradata]$
[oracle@hoegh oradata]$ tar -zxvf HOEGH.tar.gz -C back/
HOEGH/
HOEGH/redo03.log
HOEGH/temp01.dbf
HOEGH/control01.ctl
HOEGH/control02.ctl
HOEGH/system01.dbf
HOEGH/sysaux01.dbf
HOEGH/users01.dbf
HOEGH/undotbs01.dbf
HOEGH/example01.dbf
HOEGH/redo02.log
HOEGH/redo01.log
[oracle@hoegh oradata]$
[oracle@hoegh oradata]$ cp back/HOEGH/system01.dbf HOEGH/
[oracle@hoegh oradata]$
6.恢复数据库
首先，尝试使用alter database open;命令打开数据库，我们看到系统提示需要进行介质恢复。
接下来，使用recover database;命令恢复数据库；
最后，再次使用alter database open;命令打开数据库。
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'
SQL>
SQL> recover database;
Media recovery complete.
SQL>
SQL> alter database open;
Database altered.
SQL>
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL>
此时我们看到数据库已经处于open状态了，至此我们成功地使用物理备份恢复了之前“丢失”的system数据文件。通过alert报警日志我们再来看一下介质恢复以及打开数据库的整个过程。
Sat Jul 11 09:02:46 2015
alter database open
Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_ora_6135.trc:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'
ORA-1113 signalled during: alter database open...
ALTER DATABASE RECOVER database
Media Recovery Start
started logmerger process
Sat Jul 11 09:02:53 2015
Recovering data file 1 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
Parallel Media Recovery started with 2 slaves
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/HOEGH/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/HOEGH/redo02.log
Media Recovery Complete (HOEGH)
Completed: ALTER DATABASE RECOVER database
Sat Jul 11 09:03:23 2015
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 8, block 878, scn 919739
Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/HOEGH/redo02.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 8, block 878, scn 939740
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Sat Jul 11 09:03:24 2015
Thread 1 advanced to log sequence 9 (thread open)
Thread 1 opened at log sequence 9
Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/HOEGH/redo03.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Jul 11 09:03:24 2015
SMON: enabling cache recovery
[6135] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1328894 end:1328914 diff:20 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Jul 11 09:03:24 2015
QMNC started with pid=22, OS id=6188
MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AMMySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.
MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AMMySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.
MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AMMySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.
The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AMSQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.
MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AMThe basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA
MySQL's Role: Databases in Web ApplicationsApr 17, 2025 am 12:23 AMThe main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
MySQL: Building Your First DatabaseApr 17, 2025 am 12:22 AMThe steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.
MySQL: A Beginner-Friendly Approach to Data StorageApr 17, 2025 am 12:21 AMMySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.
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
WebStorm Mac version
Useful JavaScript development tools
Notepad++7.3.1
Easy-to-use and free code editor
Atom editor mac version download
The most popular open source editor
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.
