当联机备份中断时，数据库不能起来的解决
联机备份
SQL> alter tablespace app_data begin backup;
Tablespace altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 ACTIVE 674934 02-JAN-12
6 rows selected.
SQL> update szhr.emp set sal=1000 where empno=7369;
1 row updated.
SQL> commit;
SQL> select empno,sal from szhr.emp where empno=7369;
EMPNO SAL
---------- ----------
7369 1000
仍然可以对所在的表空间进行读写操作，因为数据文件头被冻结，不写数据文件头
情况一：
插入了数据，中断备份起不来，要进行介质恢复，DB认为因为app_data数据文件的文件头的SCN做了更新
SQL> shutdown immediate
ORA-01149: cannot shutdown - file 6 has online backup set
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/app_data.dbf'
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1220796 bytes
Variable Size 213913412 bytes
Database Buffers 381681664 bytes
Redo Buffers 7163904 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/app_data.dbf'
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 ACTIVE 674934 02-JAN-12
6 rows selected.
SQL> alter database datafile 6 online;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/app_data.dbf'
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select empno,sal from szhr.emp where empno=7369;
EMPNO SAL
---------- ----------
7369 1000
数据修改了，证明成功写入了app_data数据文件
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 674934 02-JAN-12
6 rows selected.
SQL> alter tablespace app_data end backup;
alter tablespace app_data end backup
*
ERROR at line 1:
ORA-01142: cannot end online backup - none of the files are in backup
SQL> alter tablespace app_data offline;
Tablespace altered.
SQL> alter tablespace app_data end backup;
alter tablespace app_data end backup
*
ERROR at line 1:
ORA-01140: cannot end online backup - all files are offline or readonly
SQL> select empno,sal from szhr.emp;
select empno,sal from szhr.emp
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/app_data.dbf'
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
APP_DATA OFFLINE
7 rows selected.
SQL> alter tablespace APP_DATA online;
Tablespace altered.
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
情况二：
SQL> alter tablespace app_data begin backup;
Tablespace altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 ACTIVE 736558 03-JAN-12
6 rows selected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1220796 bytes
Variable Size 213913412 bytes
Database Buffers 381681664 bytes
Redo Buffers 7163904 bytes
Database mounted.
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/app_data.dbf'
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 ACTIVE 736558 03-JAN-12
6 rows selected.
SQL> alter database datafile 6 offline;
Database altered.
SQL> alter database open;
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
APP_DATA ONLINE——假的
7 rows selected.
SQL> select * from szhr.emp;
select * from szhr.emp
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/app_data.dbf'
SQL> recover datafile 6;
Media recovery complete.
SQL> alter tablespace APP_DATA online;
Tablespace altered.
SQL> select empno,sal from szhr.emp where empno=7369;
EMPNO SAL
---------- ----------
7369 1200
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26465805/viewspace-714363/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
修改文件路径让数据库起来
下一篇：
chattr +A减少I/O
请登录后发表评论
登录
全部评论
