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

最新推荐文章于 2023-09-21 10:22:59 发布

cs9088

最新推荐文章于 2023-09-21 10:22:59 发布

阅读量166

点赞数

文章标签：数据库

[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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30182853/viewspace-1974000/