ITPub博客

首页 > 数据库 > Oracle > oracle备份恢复系列2--不完全恢复

oracle备份恢复系列2--不完全恢复

原创 Oracle 作者:monkeybron 时间:2016-01-08 17:46:49 0 删除 编辑
[oracle@HQ-VRW-Linux6 backup]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 8 17:24:51 2016


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> 
SQL> 
SQL> 
SQL> select file_name from dba_data_files;


FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/test11g/users01.dbf
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf
/home/oracle/app/oracle/oradata/test11g/system01.dbf
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf
/home/oracle/app/oracle/oradata/test11g/users02.dbf
/home/oracle/app/oracle/oradata/test11g/tbs01.dbf


7 rows selected.


SQL> desc timer
ERROR:
ORA-04043: object timer does not exist




SQL> 
SQL> create table timer (a number(5),b date) tablespace tbs01; 


Table created.


SQL> 
SQL> drop sequence seq_timer;


Sequence dropped.


SQL> create sequence seq_timer start with 1 increment by 1 maxvalue 9999 nocache;


Sequence created.


SQL>  set time on
17:25:31 SQL> insert into timer values(seq_timer.nextval,sysdate) ;


1 row created.


17:25:38 SQL>  insert into timer values(seq_timer.nextval,sysdate) ;


1 row created.


17:25:43 SQL> insert into timer values(seq_timer.nextval,sysdate) ;


1 row created.


17:25:50 SQL> commit;


Commit complete.


17:25:55 SQL> alter system checkpoint;


System altered.


17:26:03 SQL> /


System altered.


17:26:04 SQL> /


System altered.


17:26:05 SQL> /


System altered.


17:26:06 SQL> set line 180
17:26:12 SQL> col name for a70
17:26:17 SQL> select substr(name, 1, 70) name, CHECKPOINT_CHANGE#,LAST_CHANGE# ,creation_change#,status from v$datafile;


NAME                                                                   CHECKPOINT_CHANGE# LAST_CHANGE# CREATION_CHANGE# STATUS
---------------------------------------------------------------------- ------------------ ------------ ---------------- -------
/home/oracle/app/oracle/oradata/test11g/system01.dbf                              1527094                             7 SYSTEM
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf                              1527094                          1834 ONLINE
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf                             1527094                        923328 ONLINE
/home/oracle/app/oracle/oradata/test11g/users01.dbf                               1527094                         16143 ONLINE
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf                           1527094                        981175 ONLINE
/home/oracle/app/oracle/oradata/test11g/users02.dbf                               1527094                       1023563 ONLINE
/home/oracle/app/oracle/oradata/test11g/tbs01.dbf                                 1527094                       1487667 ONLINE


7 rows selected.


17:26:22 SQL> select substr(name, 1, 70) name, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;


NAME                                                                   REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------------------------------------- --- --- ------------------ ----------------
/home/oracle/app/oracle/oradata/test11g/system01.dbf                   NO  YES            1527094              172
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf                   NO  YES            1527094              172
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf                  NO  YES            1527094               93
/home/oracle/app/oracle/oradata/test11g/users01.dbf                    NO  YES            1527094              171
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf                NO  YES            1527094               77
/home/oracle/app/oracle/oradata/test11g/users02.dbf                    NO  YES            1527094               70
/home/oracle/app/oracle/oradata/test11g/tbs01.dbf                      NO  YES            1527094               36


7 rows selected.


17:26:31 SQL> alter database begin backup;


Database altered.


17:26:51 SQL> host
[oracle@HQ-VRW-Linux6 backup]$ cp /home/oracle/app/oracle/oradata/test11g/*.bdf ./
cp: cannot stat `/home/oracle/app/oracle/oradata/test11g/*.bdf': No such file or directory
[oracle@HQ-VRW-Linux6 backup]$ cp -r /home/oracle/app/oracle/oradata/test11g/*.dbf ./
[oracle@HQ-VRW-Linux6 backup]$ ls
sysaux01.dbf  system01.dbf  tbs01.dbf  temp01.dbf  testing_lmt.dbf  undotbs01.dbf  users01.dbf  users02.dbf
[oracle@HQ-VRW-Linux6 backup]$ 
[oracle@HQ-VRW-Linux6 backup]$ exit
exit


17:27:47 SQL> alter database end backup;


Database altered.


17:27:55 SQL> insert into timer values(seq_timer.nextval,sysdate) ;


1 row created.


17:28:04 SQL> insert into timer values(seq_timer.nextval,sysdate) ;


1 row created.


17:28:09 SQL> commit;


Commit complete.


17:28:11 SQL>  alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';


Session altered.


17:28:17 SQL> select a,to_char(b,'hh24:mi:ss') from timer order by 1;


         A TO_CHAR(
---------- --------
         1 17:25:38
         2 17:25:43
         3 17:25:50
         4 17:28:04
         5 17:28:09


17:28:22 SQL> 
17:28:38 SQL> 
17:28:39 SQL> 
17:28:39 SQL> alter system switch logfile'
17:28:56   2  
17:28:56 SQL> 
17:28:57 SQL> alter system switch logfile;


System altered.


17:29:01 SQL> /


System altered.


17:29:02 SQL> /


System altered.


17:29:06 SQL> /


System altered.




17:30:02 SQL> alter system archive log current;


System altered.


17:30:12 SQL> ;
  1* alter system archive log current
17:30:13 SQL> /


System altered.


17:30:14 SQL> alter system switch logfile;


System altered.


17:30:26 SQL> alter system archive log current;


System altered.


17:30:31 SQL> alter system switch logfile
17:30:35   2  /


System altered.


17:30:36 SQL> alter system archive log current;


System altered.


17:30:40 SQL> 
17:30:45 SQL> 
17:30:45 SQL> 
17:30:45 SQL> drop table timer;


Table dropped.


17:30:48 SQL>  shutdown abort;
ORACLE instance shut down.
17:30:58 SQL> 
17:31:06 SQL> 
17:31:06 SQL> startup mount;
ORACLE instance started.


Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             939525000 bytes
Database Buffers         3321888768 bytes
Redo Buffers               12107776 bytes
Database mounted.
17:31:15 SQL> select substr(name, 1, 70) name, CHECKPOINT_CHANGE#,LAST_CHANGE# ,creation_change#,status from v$datafile;


NAME                                                                   CHECKPOINT_CHANGE# LAST_CHANGE# CREATION_CHANGE# STATUS
---------------------------------------------------------------------- ------------------ ------------ ---------------- -------
/home/oracle/app/oracle/oradata/test11g/system01.dbf                              1527232                             7 SYSTEM
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf                              1527232                          1834 ONLINE
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf                             1527232                        923328 ONLINE
/home/oracle/app/oracle/oradata/test11g/users01.dbf                               1527232                         16143 ONLINE
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf                           1527232                        981175 ONLINE
/home/oracle/app/oracle/oradata/test11g/users02.dbf                               1527232                       1023563 ONLINE
/home/oracle/app/oracle/oradata/test11g/tbs01.dbf                                 1527232                       1487667 ONLINE


7 rows selected.


17:31:18 SQL> select substr(name, 1, 70) name, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;


NAME                                                                   REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------------------------------------- --- --- ------------------ ----------------
/home/oracle/app/oracle/oradata/test11g/system01.dbf                   NO  YES            1527232              182
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf                   NO  YES            1527232              182
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf                  NO  YES            1527232              103
/home/oracle/app/oracle/oradata/test11g/users01.dbf                    NO  YES            1527232              181
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf                NO  YES            1527232               87
/home/oracle/app/oracle/oradata/test11g/users02.dbf                    NO  YES            1527232               80
/home/oracle/app/oracle/oradata/test11g/tbs01.dbf                      NO  YES            1527232               46


7 rows selected.


17:31:32 SQL> shutdown abort;
ORACLE instance shut down.
17:31:53 SQL> 
17:31:54 SQL> 
17:31:54 SQL> host
[oracle@HQ-VRW-Linux6 backup]$ ls
sysaux01.dbf  system01.dbf  tbs01.dbf  temp01.dbf  testing_lmt.dbf  undotbs01.dbf  users01.dbf  users02.dbf
[oracle@HQ-VRW-Linux6 backup]$ 
[oracle@HQ-VRW-Linux6 backup]$ cd /home/oracle/app/oracle/oradata/test11g
[oracle@HQ-VRW-Linux6 test11g]$ ls
control01.ctl  redo01_1.log  redo01.rdo    redo02.log  redo03_1.log  redo03.rdo    system01.dbf  temp01.dbf       undotbs01.dbf  users02.dbf
control02.ctl  redo01.log    redo02_1.log  redo02.rdo  redo03.log    sysaux01.dbf  tbs01.dbf     testing_lmt.dbf  users01.dbf
[oracle@HQ-VRW-Linux6 test11g]$ rm *.dbf
[oracle@HQ-VRW-Linux6 test11g]$ 
[oracle@HQ-VRW-Linux6 test11g]$ cp /home/oracle/backup
backup/        backup_script/ 
[oracle@HQ-VRW-Linux6 test11g]$ cp /home/oracle/backup/*.dbf ./
[oracle@HQ-VRW-Linux6 test11g]$ ls
control01.ctl  redo01_1.log  redo01.rdo    redo02.log  redo03_1.log  redo03.rdo    system01.dbf  temp01.dbf       undotbs01.dbf  users02.dbf
control02.ctl  redo01.log    redo02_1.log  redo02.rdo  redo03.log    sysaux01.dbf  tbs01.dbf     testing_lmt.dbf  users01.dbf
[oracle@HQ-VRW-Linux6 test11g]$ 
[oracle@HQ-VRW-Linux6 test11g]$ exit
exit


17:32:39 SQL> startup mount;
ORACLE instance started.


Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             939525000 bytes
Database Buffers         3321888768 bytes
Redo Buffers               12107776 bytes
Database mounted.
17:32:48 SQL> select substr(name, 1, 70) name, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;


NAME                                                                   REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------------------------------------- --- --- ------------------ ----------------
/home/oracle/app/oracle/oradata/test11g/system01.dbf                   YES YES            1527111              173
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf                   YES YES            1527111              173
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf                  YES YES            1527111               94
/home/oracle/app/oracle/oradata/test11g/users01.dbf                    YES YES            1527111              172
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf                YES YES            1527111               78
/home/oracle/app/oracle/oradata/test11g/users02.dbf                    YES YES            1527111               71
/home/oracle/app/oracle/oradata/test11g/tbs01.dbf                      YES YES            1527111               37


7 rows selected.


17:32:51 SQL> select substr(name, 1, 70) name, CHECKPOINT_CHANGE#,LAST_CHANGE# ,creation_change#,status from v$datafile;


NAME                                                                   CHECKPOINT_CHANGE# LAST_CHANGE# CREATION_CHANGE# STATUS
---------------------------------------------------------------------- ------------------ ------------ ---------------- -------
/home/oracle/app/oracle/oradata/test11g/system01.dbf                              1527232                             7 SYSTEM
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf                              1527232                          1834 ONLINE
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf                             1527232                        923328 ONLINE
/home/oracle/app/oracle/oradata/test11g/users01.dbf                               1527232                         16143 ONLINE
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf                           1527232                        981175 ONLINE
/home/oracle/app/oracle/oradata/test11g/users02.dbf                               1527232                       1023563 ONLINE
/home/oracle/app/oracle/oradata/test11g/tbs01.dbf                                 1527232                       1487667 ONLINE


7 rows selected.




17:34:28 SQL> 
17:34:37 SQL>  alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';


Session altered.


17:34:57 SQL> recover database until time '2016-01-08 17:28:04';
ORA-00279: change 1527111 generated at 01/08/2016 17:26:51 needed for thread 1
ORA-00289: suggestion : /home/oracle/archlog/1_1_900609834.dbf
ORA-00280: change 1527111 for thread 1 is in sequence #1




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


Log applied.
Media recovery complete.
17:36:03 SQL> 
17:36:08 SQL> select substr(name, 1, 70) name, CHECKPOINT_CHANGE#,LAST_CHANGE# ,creation_change#,status from v$datafile;


NAME                                                                   CHECKPOINT_CHANGE# LAST_CHANGE# CREATION_CHANGE# STATUS
---------------------------------------------------------------------- ------------------ ------------ ---------------- -------
/home/oracle/app/oracle/oradata/test11g/system01.dbf                              1527232                             7 SYSTEM
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf                              1527232                          1834 ONLINE
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf                             1527232                        923328 ONLINE
/home/oracle/app/oracle/oradata/test11g/users01.dbf                               1527232                         16143 ONLINE
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf                           1527232                        981175 ONLINE
/home/oracle/app/oracle/oradata/test11g/users02.dbf                               1527232                       1023563 ONLINE
/home/oracle/app/oracle/oradata/test11g/tbs01.dbf                                 1527232                       1487667 ONLINE


7 rows selected.


17:36:15 SQL> select substr(name, 1, 70) name, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;


NAME                                                                   REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------------------------------------------------------------------- --- --- ------------------ ----------------
/home/oracle/app/oracle/oradata/test11g/system01.dbf                   YES NO             1527144              173
/home/oracle/app/oracle/oradata/test11g/sysaux01.dbf                   YES NO             1527144              173
/home/oracle/app/oracle/oradata/test11g/undotbs01.dbf                  YES NO             1527144               94
/home/oracle/app/oracle/oradata/test11g/users01.dbf                    YES NO             1527144              172
/home/oracle/app/oracle/oradata/test11g/testing_lmt.dbf                YES NO             1527144               78
/home/oracle/app/oracle/oradata/test11g/users02.dbf                    YES NO             1527144               71
/home/oracle/app/oracle/oradata/test11g/tbs01.dbf                      YES NO             1527144               37


7 rows selected.


17:36:23 SQL> alter database open resetlogs;


Database altered.


17:36:52 SQL> 




=============alert==============================================
Completed: ALTER DATABASE   MOUNT
Fri Jan 08 17:35:27 2016
ALTER DATABASE RECOVER  database until time '2016-01-08 17:28:04'  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 4 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database until time '2016-01-08 17:28:04'  ...
Fri Jan 08 17:36:03 2016
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log /home/oracle/archlog/1_1_900609834.dbf
Fri Jan 08 17:36:03 2016
Incomplete Recovery applied until change 1527144 time 01/08/2016 17:28:04
Media Recovery Complete (test11g)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Fri Jan 08 17:36:50 2016
alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 1527144
Archived Log entry 77 added for thread 1 sequence 11 ID 0x3ebc3970 dest 1:
Resetting resetlogs activation ID 1052522864 (0x3ebc3970)
Fri Jan 08 17:36:51 2016
===========================================================================


17:36:52 SQL> select a,to_char(b,'hh24:mi:ss') from timer order by 1;


         A TO_CHAR(
---------- --------
         1 17:25:38
         2 17:25:43
         3 17:25:50

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30182853/viewspace-1974000/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-03-30

  • 博文量
    70
  • 访问量
    77121