日本搞逼视频_黄色一级片免费在线观看_色99久久_性明星video另类hd_欧美77_综合在线视频

國內(nèi)最全IT社區(qū)平臺 聯(lián)系我們 | 收藏本站
阿里云優(yōu)惠2
您當前位置:首頁 > 數(shù)據(jù)庫 > 數(shù)據(jù)庫應用 > 用熱備+歸檔恢復損壞的非系統(tǒng)表空間

用熱備+歸檔恢復損壞的非系統(tǒng)表空間

來源:程序員人生   發(fā)布時間:2014-12-24 08:29:41 閱讀次數(shù):3261次
通常,我們都是用RMAN去還原數(shù)據(jù)文件,再利用歸檔做恢復,如果沒有有效的備份集,但是有熱備份生成的文件,那末1樣可以進行恢復,這里演示的是非系統(tǒng)表空間對應的數(shù)據(jù)文件破壞后的恢復。

--首先獲得熱備份的語句
SQL> select 'alter tablespace '||tablespace_name|| ' begin backup;' 
  2  ||chr(10) 
  3  ||'cp '||file_name||' /u01/' 
  4  ||chr(10) 
  5  ||'alter tablespace '||tablespace_name|| ' end backup;' as "script"
  6  from dba_data_files where tablespace_name='ZLM';

script
--------------------------------------------------------------------------------
alter tablespace ZLM begin backup;
cp /u01/app/oracle/oradata/ora10g/zlm01.dbf /u01/
alter tablespace ZLM end backup;


SQL> alter tablespace ZLM begin backup;

SQL> !
[oracle@ora10g ~]$ cp /u01/app/oracle/oradata/ora10g/zlm01.dbf /u01/
[oracle@ora10g ~]$ exit
exit

SQL> alter tablespace ZLM end backup;

--驗證熱備已產(chǎn)生
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            1340174 2014⑴1⑵8

6 rows selected.

--連接到測試用戶開始履行事務
SQL> conn zlm/zlm
Connected.
SQL> create table emp as select * from scott.emp;

Table created.

SQL> set lin 130
SQL> set pages 130
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980⑴2⑴7        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02⑵0       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02⑵2       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09⑵8       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04⑴9       3000                    20
      7839 KING       PRESIDENT            1981⑴1⑴7       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05⑵3       1100                    20
      7900 JAMES      CLERK           7698 1981⑴2-03        950                    30
      7902 FORD       ANALYST         7566 1981⑴2-03       3000                    20
      7934 MILLER     CLERK           7782 1982-01⑵3       1300                    10

14 rows selected.

SQL> update emp set sal=sal+100;

14 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> update emp set sal=sal+100;

14 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> update emp set sal=sal+100;

14 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> update emp set sal=sal+100;

14 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

1共履行了4次update操作,并切換了4第二天志

--查看當前的數(shù)據(jù)
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980⑴2⑴7       1200                    20
      7499 ALLEN      SALESMAN        7698 1981-02⑵0       2000        300         30
      7521 WARD       SALESMAN        7698 1981-02⑵2       1650        500         30
      7566 JONES      MANAGER         7839 1981-04-02       3375                    20
      7654 MARTIN     SALESMAN        7698 1981-09⑵8       1650       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01       3250                    30
      7782 CLARK      MANAGER         7839 1981-06-09       2850                    10
      7788 SCOTT      ANALYST         7566 1987-04⑴9       3400                    20
      7839 KING       PRESIDENT            1981⑴1⑴7       5400                    10
      7844 TURNER     SALESMAN        7698 1981-09-08       1900          0         30
      7876 ADAMS      CLERK           7788 1987-05⑵3       1500                    20
      7900 JAMES      CLERK           7698 1981⑴2-03       1350                    30
      7902 FORD       ANALYST         7566 1981⑴2-03       3400                    20
      7934 MILLER     CLERK           7782 1982-01⑵3       1700                    10

14 rows selected.

--破壞數(shù)據(jù)文件
SQL> !
[oracle@ora10g ~]$ cat >> abc.txt << EOF
> abc
> efg
> hij
> EOF
[oracle@ora10g ~]$ cat abc.txt
abc
efg
hij
[oracle@ora10g ~]$ cp abc.txt /u01/app/oracle/oradata/ora10g/zlm01.dbf
[oracle@ora10g ~]$ cat /u01/app/oracle/oradata/ora10g/zlm01.dbf
abc
efg
hij
[oracle@ora10g ~]$ 

--切換日志3次后繼續(xù)履行查看
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'

SQL> !
[oracle@ora10g ~]$ cp /u01/zlm01.dbf /u01/app/oracle/oradata/ora10g/zlm01.dbf
[oracle@ora10g ~]$ exit
exit

SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'

把原來熱備生成的文件替換掉破壞的6號文件,仍然提示沒法讀取

--把故障文件offline后再online
SQL> alter database datafile 6 offline;

Database altered.

SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'

此時,會提示6號文件需要做介質(zhì)恢復

SQL> col error for a10
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR         CHANGE# TIME
---------- ------- ------- ---------- ---------- ----------
         6 OFFLINE OFFLINE               1385889 2014⑴1⑵9

可以看到6號文件現(xiàn)在是offline狀態(tài),需要做恢復

SQL> select * from v$recovery_log;

   THREAD#  SEQUENCE# TIME
---------- ---------- ----------
ARCHIVE_NAME
----------------------------------------------------------------------------------------------------------------------------------
         1         58 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_58_b7mbppk8_.arc

         1         59 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_59_b7mbsb96_.arc

         1         60 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_b7mbt18j_.arc

         1         61 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_b7mbtoy8_.arc

         1         62 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_62_b7mbw693_.arc

         1         63 2014⑴1⑵9
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_63_b7mc54hm_.arc


6 rows selected.

v$recovery_log這個視圖中查詢到的,都是恢復需要用到的歸檔日志文件

SQL> recover datafile 6;
ORA-00279: change 1385889 generated at 11/29/2014 18:46:26 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_58_%u_.arc
ORA-00280: change 1385889 for thread 1 is in sequence #58


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1387492 generated at 11/29/2014 19:15:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_59_%u_.arc
ORA-00280: change 1387492 for thread 1 is in sequence #59
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_58_b7mbppk8_.arc' no longer needed
for this recovery


ORA-00279: change 1387536 generated at 11/29/2014 19:16:58 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_%u_.arc
ORA-00280: change 1387536 for thread 1 is in sequence #60
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_59_b7mbsb96_.arc' no longer needed
for this recovery


ORA-00279: change 1387553 generated at 11/29/2014 19:17:21 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_%u_.arc
ORA-00280: change 1387553 for thread 1 is in sequence #61
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_b7mbt18j_.arc' no longer needed
for this recovery


ORA-00279: change 1387562 generated at 11/29/2014 19:17:41 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_62_%u_.arc
ORA-00280: change 1387562 for thread 1 is in sequence #62
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_b7mbtoy8_.arc' no longer needed
for this recovery


ORA-00279: change 1387587 generated at 11/29/2014 19:18:30 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_63_%u_.arc
ORA-00280: change 1387587 for thread 1 is in sequence #63
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_62_b7mbw693_.arc' no longer needed
for this recovery


Log applied.
Media recovery complete.

當把v$recovery_log中列出的5個歸檔日志全部利用后,介質(zhì)恢復完成

--再次把6號文件online
SQL> alter database datafile 6 online;

Database altered.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980⑴2⑴7       1200                    20
      7499 ALLEN      SALESMAN        7698 1981-02⑵0       2000        300         30
      7521 WARD       SALESMAN        7698 1981-02⑵2       1650        500         30
      7566 JONES      MANAGER         7839 1981-04-02       3375                    20
      7654 MARTIN     SALESMAN        7698 1981-09⑵8       1650       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01       3250                    30
      7782 CLARK      MANAGER         7839 1981-06-09       2850                    10
      7788 SCOTT      ANALYST         7566 1987-04⑴9       3400                    20
      7839 KING       PRESIDENT            1981⑴1⑴7       5400                    10
      7844 TURNER     SALESMAN        7698 1981-09-08       1900          0         30
      7876 ADAMS      CLERK           7788 1987-05⑵3       1500                    20
      7900 JAMES      CLERK           7698 1981⑴2-03       1350                    30
      7902 FORD       ANALYST         7566 1981⑴2-03       3400                    20
      7934 MILLER     CLERK           7782 1982-01⑵3       1700                    10

14 rows selected.

此時,表空間ZLM及對應的數(shù)據(jù)文件zlm01.dbf都已順利地恢復了






生活不易,碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
程序員人生
------分隔線----------------------------
分享到:
------分隔線----------------------------
關(guān)閉
程序員人生
主站蜘蛛池模板: 国产精品福利在线 | 中文字幕 视频一区 | 粉嫩欧美一区二区三区高清影视 | 精品一区二区三区在线视频 | 国产男女乱淫真高清视频免费 | 成人观看 | 精品久久在线 | 麻豆视频免费版 | 成人高清视频在线观看 | 亚洲一级片免费 | 国产成人精品一区二区三区四区 | 97国产一区二区精品久久呦 | 麻豆精品一区二区 | 成人精品一区二区三区校园激情 | 久久久久玖玖 | 久久久久久久91 | 欧美日本韩国一区二区三区 | 国产精品久久久av久久久 | 久久精品国产免费 | 热久热久 | 亚洲成人一区二区 | 一级毛片视频 | 久久久久久久久久一区二区 | 国产精品久久久久久模特 | 亚洲精美视频 | 99久久国 | 99国产精品99 | 亚洲国产二区 | 国产一区二区欧美精品 | 99精品视频免费 | 国产精品99一区二区三区 | 久久久www成人免费无遮挡大片 | 日本午夜视频 | 九九热精品视频 | 一区二区视频观看 | 国产精品99久久免费观看 | 国产成人精品一区二 | 欧美最猛性xxxx | 欧美日韩高清在线观看 | 国产精品裸体一区二区三区 | 美女国产免费 |