ps:这里只为了登录数据库,不考虑数据的恢复
在一次使用IMPDP给我虚拟机上数据库导数据时候,因为数据量多导致在导入的过程种虚拟机有限的磁盘空间被占满,为此想到了先把不用的数据文件删除了释放一部分空间让数据先导入,然后再处理删除的数据文件。
但是事与愿违,数据文件虽然删除了(rm -rf),磁盘空间并没有释放出来,这个问题原因暂还没找到,那咋办呢,我想到了我还有归档日志文件可以删,归档日志基本占了我一半的空间。然后同样rm -rf 删除归档日志,至此空间释放出来,导入继续。
下面进入正题:
1.重启数据库
--连接数据库(不知道啥原因,直接使用sqlplus不能使用了,应该是环境变量出了点啥情况,这里我直接切到bin下执行)
sqlplus / as sysdba
SQL> startup ORA-01081: cannot start already-running ORACLE - shut it down first SQL> connect admin/123456 ERROR: ORA-01033: ORACLE initialization or shutdown in progress Process ID: 0 Session ID: 0 Serial number: 0 Warning: You are no longer connected to ORACLE. SQL> shutdown normal ORA-01012: not logged on SQL> shutdowm immediate SP2-0734: unknown command beginning "shutdowm i..." - rest of line ignored. SQL> SHUTDOWN IMMEDIATE ORA-01012: not logged on SQL> startup ORA-01031: insufficient privileges SQL> shutdown immediate ORA-01012: not logged on SQL> exit [oracle@orcl bin]$ ps -ef|grep ora
也算是一波三折,启动说是在运行,关闭也各种报错,既然这样我就考虑直接全关闭了,然后处理ora 01012的问题,再把数据库关闭
这里百度找了下01012的处理方式,把smon这个进程kill了再关闭
然后再关闭数据库,提示数据库状态是无效的,说明杀了进程数据库也随着关闭了,二话不说直接启动数据库
2.将异常的文件设为offline
SQL> startup ORACLE instance started. Total System Global Area 2421825536 bytes Fixed Size 2215744 bytes Variable Size 1392509120 bytes Database Buffers 1006632960 bytes Redo Buffers 20467712 bytes Database mounted. ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/data/oracle/oradata/ORCL/datafile/busi_data01.dbf' SQL> alter database datafile 2 offline; Database altered. SQL> alter datebase datafile 5 offline; alter datebase datafile 5 offline * ERROR at line 1: ORA-00940: invalid ALTER command SQL> alter database datafile 5 offline; Database altered.
因为要设的文件多上面列出部分,其他的就循环操作就行(启动数据库会去找文件从而提示某文件出问题,这里使用命令 根据文件号将文件设为脱机 offline,设好更改数据库状态为open ,命令:alter database open ,然后根据提示重复 设置文件脱机--设open 直到启动成功)
3.登录上数据库后删除这些表空间,然后再创建,直接删除可能因还有对象没删除导致删除失败,这里加入INCLUDING CONTENTS and DATAFILES
DROP TABLESPACE busi_data INCLUDING CONTENTS and DATAFILES; CREATE TABLESPACE busi_data DATAFILE '/data/oracle/oradata/ORCL/datafile/busi_data01.dbf' SIZE 50M AUTOEXTEND ON NEXT 10 M ;
4.其中包含一个数据库自带表空间数据文件(2号文件)状态也还不是ONLINE,这个文件当时没有删除,这里需要个恢复操作,才能改变其状态
SQL> ALTER DATABASE DATAFILE 2 online; ALTER DATABASE DATAFILE 2 online * ERROR at line 1: ORA-01113: file 2 needs media recovery ORA-01110: data file 2: '/data/oracle/oradata/ORCL/datafile/o1_mf_sysaux_jmn1kbhq_.dbf' SQL> recover datafile '/data/oracle/oradata/ORCL/datafile/o1_mf_sysaux_jmn1kbhq_.dbf'; ORA-00279: change 3366517 generated at 10/19/2021 19:16:08 needed for thread 1 ORA-00289: suggestion : /disk_sdb/oracles/archive/1_1655_1082840662.dbf ORA-00280: change 3366517 for thread 1 is in sequence #1655 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00279: change 3387606 generated at 10/20/2021 09:27:15 needed for thread 1 ORA-00289: suggestion : /disk_sdb/oracles/archive/1_1656_1082840662.dbf ORA-00280: change 3387606 for thread 1 is in sequence #1656 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} Log applied. Media recovery complete. SQL> ALTER DATABASE DATAFILE 2 online; Database altered.
5.至此数据库可以恢复使用了