Oracle 数据库模拟数据文件损坏恢复
数据文件有时候因为某种原因会导致损坏而导致无法启动数据库,那如何恢复呢?下面是一次模拟实验,如下1.首先创建一个表空间TEST,在创建一个表test在表空间test
数据文件有时候因为某种原因会导致损坏而导致无法启动数据库,那如何恢复呢?
下面是一次模拟实验,服务器空间,如下
1. 首先创建一个表空间TEST,在创建一个表test在表空间test上
SQL> create tablespace test datafile '/u01/app/oracle/oradata/lhz/test01.dbf' size 10M;
SQL> create table test as select * from dba_objects;
Table created
SQL> alter table test move tablespace test;
Table altered
SQL> select count(*) from test;
COUNT(*)
----------
50881
2. 然后用vi编辑数据文件
[oracle@odb1 ~]$ vi /u01/app/oracle/oradata/lhz/test01.dbf
3.随便输入什么字符,保存
4.关闭数据库实例
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
5.启动实例
SQL> startup
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 243272768 bytes
Database Buffers 348127232 bytes
Redo Buffers 6287360 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/u01/app/oracle/oradata/lhz/test01.dbf'
看到报错信息数据文件不能验证或锁定数据文件
6. 先将损坏数据文件offline,服务器空间,并打开数据库实例
SQL> alter database datafile '/u01/app/oracle/oradata/lhz/test01.dbf' offline;
Database altered.
SQL> alter database open;
Database altered.
7. 添加相同的表空间test
SQL> alter database create datafile '/u01/app/oracle/oradata/lhz/test01.dbf';
Database altered.
SQL> recover datafile 9;
Media recovery complete.
SQL> alter database datafile 9 online;
Database altered.
8.验证恢复结果:
SQL> select count(*) from andylhz.test;
COUNT(*)
----------
50881
恢复完成!
本文出自 “影子骑士” 博客,香港服务器,请务必保留此出处

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



To query the Oracle tablespace size, follow the following steps: Determine the tablespace name by running the query: SELECT tablespace_name FROM dba_tablespaces; Query the tablespace size by running the query: SELECT sum(bytes) AS total_size, sum(bytes_free) AS available_space, sum(bytes) - sum(bytes_free) AS used_space FROM dba_data_files WHERE tablespace_

There are three ways to view instance names in Oracle: use the "sqlplus" and "select instance_name from v$instance;" commands on the command line. Use the "show instance_name;" command in SQL*Plus. Check environment variables (ORACLE_SID on Linux) through the operating system's Task Manager, Oracle Enterprise Manager, or through the operating system.

Oracle View Encryption allows you to encrypt data in the view, thereby enhancing the security of sensitive information. The steps include: 1) creating the master encryption key (MEk); 2) creating an encrypted view, specifying the view and MEk to be encrypted; 3) authorizing users to access the encrypted view. How encrypted views work: When a user querys for an encrypted view, Oracle uses MEk to decrypt data, ensuring that only authorized users can access readable data.

Uninstall method for Oracle installation failure: Close Oracle service, delete Oracle program files and registry keys, uninstall Oracle environment variables, and restart the computer. If the uninstall fails, you can uninstall manually using the Oracle Universal Uninstall Tool.

The method to solve the Oracle cursor closure problem includes: explicitly closing the cursor using the CLOSE statement. Declare the cursor in the FOR UPDATE clause so that it automatically closes after the scope is ended. Declare the cursor in the USING clause so that it automatically closes when the associated PL/SQL variable is closed. Use exception handling to ensure that the cursor is closed in any exception situation. Use the connection pool to automatically close the cursor. Disable automatic submission and delay cursor closing.

Oracle Invalid numeric errors may be caused by data type mismatch, numeric overflow, data conversion errors, or data corruption. Troubleshooting steps include checking data types, detecting digital overflows, checking data conversions, checking data corruption, and exploring other possible solutions such as configuring the NLS_NUMERIC_CHARACTERS parameter and enabling data verification logging.

Deleting all data in Oracle requires the following steps: 1. Establish a connection; 2. Disable foreign key constraints; 3. Delete table data; 4. Submit transactions; 5. Enable foreign key constraints (optional). Be sure to back up the database before execution to prevent data loss.

To create a user in Oracle, follow these steps: Create a new user using the CREATE USER statement. Grant the necessary permissions using the GRANT statement. Optional: Use the RESOURCE statement to set the quota. Configure other options such as default roles and temporary tablespaces.
