Community
Learn
Tools Library
AI Tools
Leisure
search
English
Home > Database > Mysql Tutorial > Oracle 培训笔记：归档模式备份恢复

Oracle 培训笔记：归档模式备份恢复

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release： 2016-06-07 17:04:32
Original
946 people have browsed it

1、数据库置归档模式，设置归档路径1.1 查询归档状态SQLgt; select log_mode from v$database;LOG_MODE------------NOARCHIVELO

1、数据库置归档模式，设置归档路径

1.1 查询归档状态
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     9
当前日志序列           11


1.2 置归档模式
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
Oracle 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  251658240 bytes
Fixed Size                  1248356 bytes
Variable Size              79692700 bytes
Database Buffers          163577856 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
SQL> alter database archivelog;
SQL> alter database open;

设置非归档：alter database noarchivelog;

1.3 设置归档路径
SQL> show parameter log_archive_dest_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string

SQL> show parameter log_archive_min_succeed_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_min_succeed_dest         integer     1

SQL> show parameter format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      ARC%S_%R.%T

SQL> alter system set log_archive_format='arc%S_%R_%d.%T' scope=spfile;

系统已更改。


设置归档路径
alter system set log_archive_dest_1='location=e:\arc1 mandatory';
alter system set log_archive_dest_2='location=e:\arc2 mandatory';
alter system set log_archive_dest_3='location=e:\arc3 optional';

alter system switch logfile;

使用闪回恢复区
alter system set log_archive_dest_1='';
alter system set log_archive_dest_2='';
alter system set log_archive_dest_3='';
alter system set log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST';

1.4 备份数据库
冷备份：关闭数据库，备份数据文件、控制文件、参数文件


2、备份
冷备份

热备份
alter tablespace xxx begin backup;
物理拷贝
alter tablespace xxx end backup;

alter database begin backup;
物理拷贝
alter database end backup;

alter tablespace users begin backup;

3、完全恢复
3.1 模拟数据变化，切换日志
insert into t01 values(40);
commit;
alter system switch logfile;

insert into t01 values(50);
commit;
alter system switch logfile;

insert into t01 values(60);
commit;
alter system switch logfile;

insert into t01 values(70);
commit;
alter system switch logfile;


SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

删除数据文件users01.dbf

SQL> startup
ORACLE 例程已经启动。

Total System Global Area  251658240 bytes
Fixed Size                  1248356 bytes
Variable Size              79692700 bytes
Database Buffers          163577856 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 4 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 4: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\USERS01.DBF'

还原数据文件user01.dbf

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- --------------
         4 ONLINE  ONLINE                                                                        213083 03-9月 -11

SQL> select * from v$recovery_log;

   THREAD#  SEQUENCE# TIME
---------- ---------- --------------
ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

         1         15 03-9月 -11
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_15_763HHZ1G_.ARC

         1         16 03-9月 -11
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_16_763HHZKL_.ARC


SQL> recover datafile 4;
ORA-00279: 更改 213083 (在 09/03/2011 13:39:09 生成) 对于线程 1 是必需的
ORA-00289: 建议: E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_15_%U_.ARC
ORA-00280: 更改 213083 (用于线程 1) 在序列 #15 中


指定日志: {=suggested | filename | AUTO | CANCEL}

ORA-00308: 无法打开归档日志 'E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_15_763HHZ1G_.ARC'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。


SQL>
SQL> recover datafile 4;
ORA-00279: 更改 213083 (在 09/03/2011 13:39:09 生成) 对于线程 1 是必需的
ORA-00289: 建议: E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_15_%U_.ARC
ORA-00280: 更改 213083 (用于线程 1) 在序列 #15 中


指定日志: {=suggested | filename | AUTO | CANCEL}
d:\1_15_763HHZ1G_.ARC
ORA-00279: 更改 213171 (在 09/03/2011 13:41:18 生成) 对于线程 1 是必需的
ORA-00289: 建议: E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_16_%U_.ARC
ORA-00280: 更改 213171 (用于线程 1) 在序列 #16 中
ORA-00278: 此恢复不再需要日志文件 'd:\1_15_763HHZ1G_.ARC'


指定日志: {=suggested | filename | AUTO | CANCEL}
d:\O1_MF_1_16_763HHZKL_.ARC
已应用的日志。
完成介质恢复。
SQL> alter database open;

数据库已更改。

3.2 在线恢复数据文件
alter tablespace users offline;
还原数据文件
恢复数据文件
alter tablespace users online;

system、undo表空间必须在mounted状态恢复

3.3 闪回恢复区监控
select * from v$recovery_file_dest;
select * from v$flash_recovery_area_usage;

3.4 控制文件损坏恢复
数据库发生结构性变化时及时备份。（表空间、数据文件增删）
SQL> alter database backup controlfile to 'd:\control.ctl';

1）部分控制文件损坏
复制存在的文件，，再打开数据库

2）控制文件全部损坏
情况1：有备份
关闭数据库
删除所有控制文件
还原控制文件
启动数据库到mounted状态
恢复数据库 recover database using backup controlfile;
SQL> recover database using backup controlfile;
ORA-00279: change 205641 generated at 09/03/2011 11:52:37 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_

15_%U_.ARC
ORA-00280: change 205641 for thread 1 is in sequence #15


Specify log: {=suggested | filename | AUTO | CANCEL}
d:\1_15_763HHZ1G_.ARC
ORA-00279: change 213171 generated at 09/03/2011 13:41:18 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_

16_%U_.ARC
ORA-00280: change 213171 for thread 1 is in sequence #16
ORA-00278: log file 'd:\1_15_763HHZ1G_.ARC' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
d:\O1_MF_1_16_763HHZKL_.ARC
ORA-00279: change 213175 generated at 09/03/2011 13:41:19 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_

17_%U_.ARC
ORA-00280: change 213175 for thread 1 is in sequence #17
ORA-00278: log file 'd:\O1_MF_1_16_763HHZKL_.ARC' no longer needed for this
recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
E:\oracle\product\10.2.0\oradata\mydb\redo02.log
ORA-00279: change 213180 generated at 09/03/2011 13:41:23 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_

18_%U_.ARC
ORA-00280: change 213180 for thread 1 is in sequence #18
ORA-00278: log file 'E:\oracle\product\10.2.0\oradata\mydb\redo02.log' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
E:\oracle\product\10.2.0\oradata\mydb\redo03.log
ORA-00279: change 213184 generated at 09/03/2011 13:41:24 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\MYDB\ARCHIVELOG\2011_09_03\O1_MF_1_

19_%U_.ARC
ORA-00280: change 213184 for thread 1 is in sequence #19
ORA-00278: log file 'E:\oracle\product\10.2.0\oradata\mydb\redo03.log' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
E:\oracle\product\10.2.0\oradata\mydb\redo01.log
Log applied.
Media recovery complete.
打开数据库 alter database open resetlogs;

linux

Related labels：
Oracle教程 Oracle 培训笔记：归档模式备份恢复
source：php.cn
Previous article：Oracle EBS 销售时物料保留出错相关症状和处理方法 Next article：Oracle 脚本：清空当前用户下所有表的数据
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
Team collaboration - What should I do if someone needs the feature I wrote as a dependency in git flow?
From 1970-01-01 08:00:00
0
0
0
Objective-c - Constraints for iOS a warning issue
From 1970-01-01 08:00:00
0
0
0
Confusion about using gitlab's fork&pull request mode within the team
From 1970-01-01 08:00:00
0
0
0
Objective-c - In iOS development, Instagram cannot be authorized after logging in. Instagram does not jump back to the application. How to get the callback address?
From 1970-01-01 08:00:00
0
0
0
Version Control - About the use of SVN and GIT in company projects?
From 1970-01-01 08:00:00
0
0
0
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template