冷備份實驗,裸裝置上備份

一、冷備份實驗模拟當機，加歸檔補齊

實驗步驟：

1.檢視要備份的資料檔案

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/oracle/oradata/zwb/system01.dbf

/oracle/oradata/zwb/undotbs01.dbf

/oracle/oradata/zwb/sysaux01.dbf

/oracle/oradata/zwb/users01.dbf

/oracle/oradata/zwb/example01.dbf

2、檢視要備份的控制檔案

SQL> select name from v$controlfile;

/oracle/oradata/zwb/control01.ctl

/oracle/oradata/zwb/control02.ctl

/oracle/oradata/zwb/control03.ctl

3.檢視要備份的日志檔案

SQL> select member from v$logfile;

MEMBER

/oracle/oradata/zwb/redo03.log

/oracle/oradata/zwb/redo02.log

/oracle/oradata/zwb/redo01.log

4、檢視是否歸檔模式，沒有設為歸檔

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/archlog

Oldest online log sequence     17

Next log sequence to archive   19

Current log sequence           19

5、關資料庫

SQL> shutdown immediate

6、開始備份

[oracle@testsvr bak]$ cd /oracle/oradata/zwb/

[oracle@testsvr zwb]$ cp * /oracle/bak    ---備份至/oracle/bak

7、開啟資料庫，切歸檔

[oracle@testsvr bak]$ sqlplus '/as sysdba'

SQL> startup

SQL> alter system switch logfile;

System altered.

8、換使用者做事

SQL> conn zwb/zwb

Connected.

SQL> create table t1 as select * from v$bh;   ---随便建立表格

Table created.

SQL> select count(*) from t1;   ---看一下建的表的資料

  COUNT(*)

----------

      4959

9、連續插資料再切歸檔，送出

SQL> insert into t1 select * from t1;

4959 rows created.

SQL> commit;

Commit complete.

9918 rows created.

10、假設此時資料庫當機

SQL> select count(*) from t1;    --當之前的資料

     19836

***************************************  

SQL> insert into t1 select * from t1;   --再插資料

19836 rows created.

SQL> select count(*) from t1;     ---當後的資料放在redolog裡

     39672

11、檢視最後更改值存放在哪裡，由于是存放在CURRENT redolog裡，是以是存放在2号檔案

SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

         1          1         26   52428800          1 YES ACTIVE

       751890 23-MAY-12

         2          1         27   52428800          1 NO  CURRENT

       751893 23-MAY-12

         3          1         25   52428800          1 YES ACTIVE

       751772 23-MAY-12

即如果/oracle/oradata/zwb/redo02.log 資料損壞，最後插的資料永遠丢失

***************************************************  此時當機

12、當機

SQL> shutdown abort;

ORA-01031: insufficient privileges

SQL> conn /as sysdba

ORACLE instance shut down.

13、模拟資料檔案、控制檔案、日志檔案由于當機均丢失

[oracle@testsvr bak]$ pwd    ---進入備份目錄

/oracle/bak

[oracle@testsvr zwb]$ pwd

/oracle/oradata/zwb

[oracle@testsvr zwb]$ rm -rf *

[oracle@testsvr zwb]$ ls -rtl

total 0

14、将之前的冷備份恢複

[oracle@testsvr bak]$ pwd

[oracle@testsvr bak]$ cp * /oracle/oradata/zwb

[oracle@testsvr zwb]$ ls -rtl   --檢視已恢複

total 1090552

-rw-r-----  1 oracle oinstall   7061504 May 23 23:43 control01.ctl.beifen

-rw-r-----  1 oracle oinstall   7061504 May 23 23:43 control01.ctl.bak

-rw-r-----  1 oracle oinstall   7389184 May 23 23:43 control01.ctl

-rw-r-----  1 oracle oinstall   7389184 May 23 23:43 control03.ctl

-rw-r-----  1 oracle oinstall   7389184 May 23 23:43 control02.ctl

-rw-r-----  1 oracle oinstall 104865792 May 23 23:43 example01.dbf

-rw-r-----  1 oracle oinstall  52429312 May 23 23:43 redo01.log

-rw-r-----  1 oracle oinstall  52429312 May 23 23:43 redo02.log

-rw-r-----  1 oracle oinstall   7061504 May 23 23:43 standby01

-rw-r-----  1 oracle oinstall  52429312 May 23 23:43 redo03.log

-rw-r-----  1 oracle oinstall 251666432 May 23 23:44 sysaux01.dbf

-rw-r-----  1 oracle oinstall 503324672 May 23 23:44 system01.dbf

-rw-r-----  1 oracle oinstall   5251072 May 23 23:44 users01.dbf

-rw-r-----  1 oracle oinstall  31465472 May 23 23:44 undotbs01.dbf

-rw-r-----  1 oracle oinstall  20979712 May 23 23:44 temp01.dbf

15、讀歸檔

SQL> startup mount;

ORACLE instance started.

SQL> recover database;    ---不行

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

SQL> recover database using backup controlfile;   ---補齊歸檔

16、最終檢視

SQL> alter database open read only;      ---需要不完全恢複

alter database open read only

*

ERROR at line 1:

ORA-16004: backup database requires recovery

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/oracle/oradata/zwb/system01.dbf'

SQL> recover database using backup controlfile until cancel;      ----不完全恢複

ORA-00279: change 751893 generated at 05/23/2012 23:23:00 needed for thread 1

ORA-00289: suggestion : /oracle/archlog/1_27_765904367.dbf

ORA-00280: change 751893 for thread 1 is in sequence #27

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

SQL> conn zwb/zwb      ---最終檢視結果

SQL> select count(*) from t1;

結論：當機前未歸檔的資料丢失

 二、裸裝置上做冷備：（以備份1号資料檔案為例）

1、SQL> select file#,name,bytes from v$datafile;     ---查出1号資料檔案大小

     FILE# NAME                                          BYTES

---------- ---------------------------------------- ----------

         1 /oracle/oradata/zwb/system01.dbf          503316480

         2 /oracle/oradata/zwb/undotbs01.dbf          31457280

         3 /oracle/oradata/zwb/sysaux01.dbf          251658240

         4 /oracle/oradata/zwb/users01.dbf            17039360

         5 /oracle/oradata/zwb/example01.dbf         104857600

2、[oracle@testsvr ~]$ ls -l /oracle/oradata/zwb/system01.dbf     ---系統上的大小

-rw-r-----  1 oracle oinstall 503324672 May 25 23:08 /oracle/oradata/zwb/system01.dbf

3、SQL> select 503324672-503316480 from dual;    --相差8192，是作業系統頭

503324672-503316480

-------------------

               8192

4、SQL> select (503316480+8192)/8192 from dual;   ---count的值

(503316480+8192)/8192

---------------------

                61441

SQL> select TABLESPACE_NAME,BLOCK_SIZE from dba_tablespaces;   ---bs來源