ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 当联机备份中断时,数据库不能起来的解决

当联机备份中断时,数据库不能起来的解决

原创 Linux操作系统 作者:oracleclub 时间:2012-01-03 00:18:56 0 删除 编辑
联机备份

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博客 ” ,链接:http://blog.itpub.net/26465805/viewspace-714363/,如需转载,请注明出处,否则将追究法律责任。

下一篇: chattr +A减少I/O
请登录后发表评论 登录
全部评论

注册时间:2011-11-22

  • 博文量
    61
  • 访问量
    84502