📄 备份与恢复测试2.doc
字号:
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 5 NO CURRENT
2 3 YES ACTIVE
3 4 NO ACTIVE
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 2 'C:\ORACLE\ORADATA\testdb\REDO02.LOG' SIZE 100M,
10 GROUP 3 'C:\ORACLE\ORADATA\testdb\REDO03.LOG' SIZE 100M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'C:\ORACLE\ORADATA\testdb\SYSTEM01.DBF',
14 'C:\ORACLE\ORADATA\testdb\UNDOTBS01.DBF',
15 'C:\ORACLE\ORADATA\testdb\INDX01.DBF',
16 'C:\ORACLE\ORADATA\testdb\TOOLS01.DBF',
17 'C:\ORACLE\ORADATA\testdb\USERS01.DBF'
18 CHARACTER SET ZHS16GBK
19 ;
CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG
*
ERROR 位于第 1 行:
ORA-01503: CREATE CONTROLFILE ??
ORA-01192: ??????????
~~~~~~~~~~~~~~~~~~~~~~
如果是NORESETLOGS,那么他要检查当前的在线日志,而现在没有了
SQL> CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 'C:\ORACLE\ORADATA\testdb\REDO01.LOG' SIZE 100M,
10 GROUP 2 'C:\ORACLE\ORADATA\testdb\REDO02.LOG' SIZE 100M,
11 GROUP 3 'C:\ORACLE\ORADATA\testdb\REDO03.LOG' SIZE 100M
12 -- STANDBY LOGFILE
13 DATAFILE
14 'C:\ORACLE\ORADATA\testdb\SYSTEM01.DBF',
15 'C:\ORACLE\ORADATA\testdb\UNDOTBS01.DBF',
16 'C:\ORACLE\ORADATA\testdb\INDX01.DBF',
17 'C:\ORACLE\ORADATA\testdb\TOOLS01.DBF',
18 'C:\ORACLE\ORADATA\testdb\USERS01.DBF'
19 CHARACTER SET ZHS16GBK
20 ;
CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS ARCHIVELOG
*
ERROR 位于第 1 行:
ORA-01503: CREATE CONTROLFILE ??
ORA-01565: ????'C:\ORACLE\ORADATA\testdb\REDO01.LOG'??? -->>这个文件不在了
ORA-27041: ??????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 2) O5M3UR2;5=V86(5DND<~!#
SQL> CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 2 'C:\ORACLE\ORADATA\testdb\REDO02.LOG' SIZE 100M,
10 GROUP 3 'C:\ORACLE\ORADATA\testdb\REDO03.LOG' SIZE 100M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'C:\ORACLE\ORADATA\testdb\SYSTEM01.DBF',
14 'C:\ORACLE\ORADATA\testdb\UNDOTBS01.DBF',
15 'C:\ORACLE\ORADATA\testdb\INDX01.DBF',
16 'C:\ORACLE\ORADATA\testdb\TOOLS01.DBF',
17 'C:\ORACLE\ORADATA\testdb\USERS01.DBF'
18 CHARACTER SET ZHS16GBK
19 ;
CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS ARCHIVELOG
*
ERROR 位于第 1 行:
ORA-01503: CREATE CONTROLFILE ??
ORA-00200: ????????
ORA-00202: ????: 'C:\oracle\oradata\testdb\control01.ctl' --这个文件不能被覆盖,删除三个控制文件
ORA-27086: skgfglk: ?????? - ?????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 5) >\>x7CNJ!#
SQL> shutdown
ORA-01507: ??????
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 2 'C:\ORACLE\ORADATA\testdb\REDO02.LOG' SIZE 100M,
10 GROUP 3 'C:\ORACLE\ORADATA\testdb\REDO03.LOG' SIZE 100M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'C:\ORACLE\ORADATA\testdb\SYSTEM01.DBF',
14 'C:\ORACLE\ORADATA\testdb\UNDOTBS01.DBF',
15 'C:\ORACLE\ORADATA\testdb\INDX01.DBF',
16 'C:\ORACLE\ORADATA\testdb\TOOLS01.DBF',
17 'C:\ORACLE\ORADATA\testdb\USERS01.DBF'
18 CHARACTER SET ZHS16GBK
19 ;
控制文件已创建
SQL> alter database open;
alter database open
*
ERROR 位于第 1 行:
ORA-01589: ??????????? RESETLOGS ? NORESETLOGS ??
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR 位于第 1 行:
ORA-01152: ?? 1 ????????????
ORA-01110: ???? 1: 'C:\ORACLE\ORADATA\TESTDB\SYSTEM01.DBF'
SQL> recover database using backup controlfile until cancel;
ORA-00279: ?? 69456 (? 03/02/2005 10:26:30 ??) ???? 1 ????
ORA-00289: ??: C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_3.DBF
ORA-00280: ?? 69456 ???? 1 ???? # 3 ???
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: ?? 69616 (? 03/02/2005 16:53:52 ??) ???? 1 ????
ORA-00289: ??: C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_4.DBF
ORA-00280: ?? 69616 ???? 1 ???? # 4 ???
ORA-00278: ??????????? 'C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_3.DBF'
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: ?? 69661 (? 03/02/2005 16:53:54 ??) ???? 1 ????
ORA-00289: ??: C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_5.DBF
ORA-00280: ?? 69661 ???? 1 ???? # 5 ???
ORA-00278: ??????????? 'C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_4.DBF'
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: ???????? 'C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_5.DBF'
ORA-27041: ??????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 2) O5M3UR2;5=V86(5DND<~!#
SQL> recover database using backup controlfile until cancel;
ORA-00279: ?? 69661 (? 03/02/2005 16:53:54 ??) ???? 1 ????
ORA-00289: ??: C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_5.DBF
ORA-00280: ?? 69661 ???? 1 ???? # 5 ???
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
介质恢复已取消。
SQL> alter database open resetlogs;
数据库已更改。
SQL> select count(*),status from arch group by status;
COUNT(*) ST
---------- --
98656 ok --还原了部分数据,但不能还原在线日志中的数据
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如果数据库是abort或者老的数据文件备份,那么就涉及到控制文件和数据文件不一致,需要恢复,就涉及到当前被破坏的在线日志,
,启动时仍然需要用在线活动日志进行恢复.如果是正常的shutdown,而且没有用老的数据备份,那么控制文件和数据文件是一致的,
不需要当前在线日志.而在abort或者老的数据文件备份在这种情况下，只能用备份的datafile和备份的controlfile来做不完
全恢复，都将造成数据丢失。因此多重controlfile 和 online redo log file 很重要。通常情况下是在正常运行数据库时,
当前在线日志被破坏的,此时马上会数据库不正常DOWN机,也就出现1.5.2的情景
我们知道如果是abort关闭的,那么有些事务是不完整的或者有些事务没有被写到数据文件,在数据文件中有这样的标识,这样,用
create controlfile ... noresetlogs或者resetlogs创建时读取数据文件头信息,该文件在控制文件信息,比如结束SCN设置为无穷大)
打开时就需要当前在线日志恢复
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
####################################################
# [测试3] shutdown abort,控制文件损坏,备份控制文件,
# 当前在线日志损坏,有数据文件备份
####################################################
还原原来备份的数据文件,控制文件
SQL> shutdown
ORA-01109: 数据库未打开
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-01991: ???????'C:\oracle\ora92\DATABASE\PWDtestdb.ORA'
SQL> recover database using backup controlfile ;
ORA-00279: ?? 69456 (? 03/02/2005 10:26:30 ??) ???? 1 ????
ORA-00289: ??: C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_3.DBF
ORA-00280: ?? 69456 ???? 1 ???? # 3 ???
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: ?? 69572 (? 03/02/2005 17:07:43 ??) ???? 1 ????
ORA-00289: ??: C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_4.DBF
ORA-00280: ?? 69572 ???? 1 ???? # 4 ???
ORA-00278: ??????????? 'C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_3.DBF'
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: ?? 69617 (? 03/02/2005 17:07:44 ??) ???? 1 ????
ORA-00289: ??: C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_5.DBF
ORA-00280: ?? 69617 ???? 1 ???? # 5 ???
ORA-00278: ??????????? 'C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_4.DBF'
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: ???????? 'C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_5.DBF'
ORA-27041: ??????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 2) O5M3UR2;5=V86(5DND<~!#
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR 位于第 1 行:
ORA-01113: ?? 1 ??????
ORA-01110: ???? 1: 'C:\ORACLE\ORADATA\TESTDB\SYSTEM01.DBF'
SQL> select resetlogs_change# - 1,CONTROLFILE_CHANGE# from v$database;
RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#
------------------- -------------------
68658 69617
SQL> recover database using backup controlfile until cancel;
ORA-00279: ?? 69617 (? 03/02/2005 17:07:44 ??) ???? 1 ????
ORA-00289: ??: C:\ORACLE\ORADATA\TESTDB\ARCHIVE\1_5.DBF
ORA-00280: ?? 69617 ???? 1 ???? # 5 ???
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
介质恢复已取消。
SQL> select resetlogs_change# - 1,CONTROLFILE_CHANGE# from v$database;
RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#
------------------- -------------------
68658 69617
SQL> alter database open resetlogs;
数据库已更改。
SQL> select count(*),status from arch group by status;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -