Admin 2. 05장 Database Recovery incomplete Recovery(불완전 복구)

불완전 복구.

DB 전체의 datafile만 restore하고(control file 과 redo는 제외) Archive 와 redo를 일부만 적용시켜 과거버전으로 복구 함.

 

 

 

User_managed Incomplete Recovery

 

-- 시간 알아내기.

S SYS> create table hr.emp2 as select * from scott.emp;

Table created.

 
S SYS> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
22-JUN-09 03.00.23.411459 PM +09:00

S SYS> !date
Mon Jun 22 15:00:56 KST 2009

 

--고장 내기         이 정도 고장은 flashback db(table) 로 복구 가능

 S SYS> drop table scott.emp;

Table dropped.

S SYS> drop table hr.emp2;

Table dropped.

S SYS>

 --backup

더 악화 될 수도 있음.

-restore

[oracle@orcl backup]$ cat datafileCopy.sh
cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_50sxqqv0_.dbf /disk2/backup
cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_52d4tltd_.dbf /disk2/backup
cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_50sxqqvn_.dbf /disk2/backup
cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_53mk33rr_.dbf /disk2/backup
cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53mht8l0_.dbf /disk2/backup
cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf /disk2/backup
cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519tbfc4_.dbf /disk2/backup
cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_rcat_ts_53k2b11h_.dbf /backup
cp /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5316d4z3_.dbf /backup
cp /home/oracle/disk2/ORCL/datafile/myts_reorg0.dbf /backup

 

 

[oracle@orcl backup]$ cp o1_* /u01/app/oracle/oradata/ORCL/datafile/

[oracle@orcl ~]$ cd /backup
[oracle@orcl backup]$ ls
myts_reorg0.dbf  o1_mf_rcat_ts_53k2b11h_.dbf  o1_mf_undotbs1_5316d4z3_.dbf
[oracle@orcl backup]$ cp o1_* /u01/app/oracle/oradata/ORCL/datafile/

 

 

--recover

S SYS> startup mount

 ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              83888372 bytes
Database Buffers           79691776 bytes
Redo Buffers                2973696 bytes
Database mounted.

S SYS> alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';

Session altered.

S SYS> recover database until time '2009-06-22 15:00:23';
Media recovery complete.

 

S SYS> alter database open resetlogs;

Database altered.

 

 

--확인.
S SYS> select * from scott.emp;

S SYS> select * from hr.emp2;

 

 

-백업
[oracle@orcl ~]$ rman target / cmdfile=backupAll.rcv log=b.log

 

 

 

Incomplete Recovery by Using RMAN

1. backup

2. 고장

         S SYS> drop user scott cascade;

User dropped.

  3.   백업

  4.  고치기

run{
    shutdown immediate;
    startup mount;
    SQL 'alter session set nls_date_format
        ="YYYY-MM-DD:HH24:MI:SS"';
    SET UNTIL TIME='2009-06-22:15:47:57';
    ALLOCATE CHANNEL XX DEVICE TYPE DISK;
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN RESETLOGS;
}

시각 값만 변경해서 넣으면 됨.

[oracle@orcl ~]$ rman target / cmdfile=ir.rcv log=ir.log

5.  확인

[oracle@orcl ~]$ tail ir.log

starting media recovery
media recovery complete, elapsed time: 00:00:05

Finished recover at 22-JUN-09

database opened
released channel: XX

Recovery Manager complete.

 

 [oracle@orcl ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 22 15:59:44 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

 

[oracle@orcl bdump]$ cd /u01/app/oracle/admin/orcl/bdump

[oracle@orcl bdump]$ tail alert_orcl.log
Mon Jun 22 15:57:03 2009
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=23, OS id=15523
Mon Jun 22 15:57:09 2009
LOGSTDBY: Validating controlfile with logical metadata
Mon Jun 22 15:57:09 2009
LOGSTDBY: Validation complete
Completed: alter database open resetlogs

 

 

  6.  백업

resetlogs 로 열리면 무조건 백업.

 

 

 

 

 

col name form a70;
select SEQUENCE#,name from v$archived_log
where name is not null
order by name
/

 

 SEQUENCE# NAME
---------- ----------------------------------------------------------------------
         1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _1_53y87r2m_.arc

         1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _1_53ybsrkt_.arc

 

 

 S SYS> alter system switch logfile;

여러번....

 

S SYS>  @archivedLog

 SEQUENCE# NAME
---------- ----------------------------------------------------------------------
         1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _1_53y87r2m_.arc

         1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _1_53ybsrkt_.arc

         1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _1_53ycsgmt_.arc

         2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _2_53ycstyo_.arc

         3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _3_53ycvnvw_.arc

         4 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _4_53ycvz4t_.arc

 

 

 

 

 

S SYS> select Max(RESETLOGS_ID) from v$archived_log
  2  ;

MAX(RESETLOGS_ID)
-----------------
        690220616

 

 

 S SYS> select resetlogs_id, count(*) from v$archived_log group by resetlogs_id
  2  order by resetlogs_id;

RESETLOGS_ID   COUNT(*)
------------ ----------
   686930489         18
   689963472          1
   690217991          1
   690220616         12                                 현재의 recovery에 사용됨

 

 

 

하드가 모자라서 archive를 백업해 놓은 경우

 

 S SYS> @archivedLog2

 SEQUENCE# NAME
---------- ----------------------------------------------------------------------
         1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _1_53ycsgmt_.arc

         2 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _2_53ycstyo_.arc

         3 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _3_53ycvnvw_.arc

         4 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _4_53ycvz4t_.arc

         5 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _5_53ycwotx_.arc

         6 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _6_53ycwv80_.arc

         7 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _7_53ycx1v2_.arc

         8 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _8_53ycx774_.arc

         9 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _9_53ycxbv0_.arc

        10 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _10_53ycxbw2_.arc

        11 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _11_53ycxgwq_.arc

        12 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/o1_mf_1
           _12_53ycxgxn_.arc

12 rows selected.

 

[oracle@orcl ~]$ mv /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/*  /disk2/backup

[oracle@orcl ~]$ rm /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53mht8l0_.dbf

 

[oracle@orcl ~]$ vi recoverUntilSequence.rcv

run{
    shutdown abort;
    start mount;
    set until sequence 5 thread 1;
    restore database;
    recover database;
}

[oracle@orcl ~]$ vi recoverUntilSequence2.rcv

run{
    recover database;
    alter database open;
}

 

 

[oracle@orcl ~]$ cd /disk2/backup/
[oracle@orcl backup]$ ls
o1_mf_1_10_53ycxbw2_.arc  o1_mf_1_1_53ycsgmt_.arc  o1_mf_1_6_53ycwv80_.arc
o1_mf_1_11_53ycxgwq_.arc  o1_mf_1_2_53ycstyo_.arc  o1_mf_1_7_53ycx1v2_.arc
o1_mf_1_12_53ycxgxn_.arc  o1_mf_1_3_53ycvnvw_.arc  o1_mf_1_8_53ycx774_.arc
o1_mf_1_1_53y87r2m_.arc   o1_mf_1_4_53ycvz4t_.arc  o1_mf_1_9_53ycxbv0_.arc
o1_mf_1_1_53ybsrkt_.arc   o1_mf_1_5_53ycwotx_.arc

 

 

[oracle@orcl backup]$ cp o1_mf_1_1_53ycsgmt_.arc  /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/
[oracle@orcl backup]$ cp o1_mf_1_2_53ycstyo_.arc /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/
[oracle@orcl backup]$ cp o1_mf_1_3_53ycvnvw_.arc /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/
[oracle@orcl backup]$ cp o1_mf_1_4_53ycvz4t_.arc /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/
[oracle@orcl backup]$ cp o1_mf_1_5_53ycwotx_.arc /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_06_22/

 RMAN 으로 복구. 위에 만들어둔 rcv 파일 실행.

 

 

백업로그나 Alert 로그 꼭 확인.