Chinaunix首页 | 论坛 | 博客
  • 博客访问: 663383
  • 博文数量: 52
  • 博客积分: 7065
  • 博客等级: 少将
  • 技术积分: 2361
  • 用 户 组: 普通用户
  • 注册时间: 2004-10-03 16:24
文章分类

全部博文(52)

文章存档

2021年(8)

2019年(2)

2018年(14)

2011年(1)

2010年(3)

2009年(4)

2008年(20)

分类: Oracle

2021-10-08 19:09:32


ORACLE 创建数据库的时候要不要开启日志归档?

oracle数据库可以运行在2种模式下:归档模式(archivelog)和非归档模式(noarchivelog) 。
归档模式可以提高Oracle数据库的可恢复性。

生产数据库都应该运行在此模式下,归档模式应该和相应的备份策略相结合,只有归档模式没有相应的备份策略只会带来麻烦。

在默认情况下,oracle数据库是在非归日志档模式中创建的,

在非归档日志模式中,进行日志切换时会直接重写redo log,如果此时数据文件因为介质失败被损坏,则数据库恢复时会丢失掉被重写的数据;

在归档日志模式下,数据库可以应用最近一次数据库备份开始生成的所有归档日志文件,保证数据无丢失;大部分的生产数据库以archivelog模式运行。

oracle数据库在开启归档日志模式后,会自动启动新的进程:归档器ARCn。默认情况下是开启4个进程,在实际应用中最多可以启动30个归档器进程。

[oracle@dbserver1 archivelog]$ ps -ef | grep -i ora_
oracle 26567 1 0 08:48 ? 00:00:00 ora_arc0_orcl
oracle 26573 1 0 08:48 ? 00:00:00 ora_arc1_orcl
oracle 26575 1 0 08:48 ? 00:00:00 ora_arc2_orcl
oracle 26577 1 0 08:48 ? 00:00:00 ora_arc3_orcl

归档日志(Archive Log)是非活动的重做日志备份.通过使用归档日志,可以保留所有重做历史记录,当数据库处于ARCHIVELOG模式并进行日志切换式,

后台进程ARCH会将重做日志的内容保存到归档日志中.当数据库出现介质失败时,使用数据文件备份,归档日志和重做日志可以完全恢复数据库.

#########################

非归档模式 切换 归档模式
修改之前:
确认环境。查看数据库,是否开启归档:  ---如下显示的信息,前提是安装数据库的时候,没有选择开启闪回,也没有选择开启归档

复制代码
SQL> archive log list Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 4 Current log sequence 6 SQL> select name,log_mode from v$database;
NAME      LOG_MODE --------- ------------ ORCL      NOARCHIVELOG

SQL> show parameter db_recovery
NAME                     TYPE     VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 0
复制代码

#########  如下 测试没有开启闪回的情况下 数据库从非归档模式切换到归档模式的操作 ############

复制代码
SQL> shutdown immediate Database closed.

SQL> startup mount
ORACLE instance started.

SQL> alter database archivelog; Database altered.

SQL> alter database open; Database altered.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6 SQL> select name from v$archived_log;
no rows selected

SQL> alter system archive log current;
System altered.

SQL> select name from v$archived_log;
NAME -------------------------------------------------------------------------------- /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf

SQL> show parameter recover;                                          
NAME                     TYPE     VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 0 db_unrecoverable_scn_tracking     boolean     TRUE
recovery_parallelism integer 0 SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
no rows selected
复制代码

将log_archive_log设置为默认值时,归档目录将会变为?/dbs/arch。

复制代码
SQL> alter system set log_archive_dest_1='';
System altered.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           ?/dbs/arch
Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7 SQL> shutdown immediate Database closed. Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
复制代码

重启数据库到mount后,归档目录变回为原先默认的目录。

复制代码
SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7 ---设置归档最大开启进程个数  SQL> alter system set log_archive_max_processes = 5;
System altered. ---修改日志存放格式  SQL> alter system set log_archive_format = "archive_%t_%s_%r.log" scope=spfile;
System altered.
SQL> shutdown immediate Database dismounted.
ORACLE instance shut down

SQL> startup
ORACLE instance started.

Total System Global Area 939495424 bytes
Fixed Size 2258840 bytes
Variable Size 637536360 bytes Database Buffers 293601280 bytes
Redo Buffers 6098944 bytes Database mounted. Database opened.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7 SQL> show parameter db_recovery
NAME                     TYPE     VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 0 SQL> select name from v$archived_log; 
NAME -------------------------------------------------------------------------------- /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf

SQL> alter system switch logfile; 
System altered.

SQL> select recid, name, first_time from v$archived_log;

NAME -------------------------------------------------------------------------------- /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_947091054.dbf /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/archarchive_1_7_94709105 4.log SQL> select NAME,SPACE_LIMIT/1024/1024/1024,SPACE_USED/1024/1024/1024,NUMBER_OF_FILES from V$RECOVERY_FILE_DEST;

no rows selected

SQL> select flashback_on from v$database;
FLASHBACK_ON ------------------ NO
复制代码

#############################

开启数据库闪回  设置闪回恢复区路径  以及大小    测试 先关闭归档模式 然后再开启归档后的 默认路径 为数据库的闪回路径

查看数据库,是否开启闪回   
注意:开启闪回功能的话,需要在归档模式下,要不闪回没有意义!!!!

但是,开启归档的话,不一定需要开启闪回功能!!!
如果开启了闪回,再开启归档模式,归档日志没有指定新路径的话,那么归档日志是默认保存在闪回区的  数据库中显示为:USE_DB_RECOVERY_FILE_DEST,实际对应闪回的路径。

另外闪回区和归档路径的 位置和大小都是可以手动设置的。

复制代码
SQL> select flashback_on from v$database;
FLASHBACK_ON ------------------ NO 
SQL> shutdown immediate
SQL> startup mount

SQL> alter database flashback on;    安装数据库的时候 没有选择开启闪回,出现了如下错误 alter database flashback on * ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
复制代码

设置闪回恢复区:

设置闪回区大小: oracle默认flash_recovery_area 为2G

SQL> alter system set db_recovery_file_dest_size=5g scope=spfile;

查看闪回区指定路径的空间大小:

SQL> show parameter db_recovery_file_dest_size

NAME                     TYPE     VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size         big integer 30G

设置闪回区位置:

SQL> alter system set db_recovery_file_dest='/home/oracle/u01/app/oracle/account_flashback_area' scope=spfile;

设置闪回目标为5天,以分钟为单位,oracle默认1440分钟,即一天)

SQL> alter system set db_flashback_retention_target=7200 scope=spfile;

操作系统层面创建闪回相关目录:

复制代码
[oracle@dbserver oracle]$ mkdir -p /home/oracle/u01/app/oracle/account_flashback_area

SQL> alter database flashback on; Database altered.
SQL> alter database open; Database altered.

SQL> select flashback_on from v$database;
FLASHBACK_ON ------------------ YES

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8
复制代码

结论:如果开启了闪回,再开启归档模式,归档日志没有指定新路径的话,那么归档日志是默认保存在闪回区的  数据库中显示为:USE_DB_RECOVERY_FILE_DEST,实际对应闪回的路径。

目前我们再测试关闭闪回 重启数据库 查看归档日志路径

复制代码
SQL> select open_mode from v$database;
OPEN_MODE -------------------- READ WRITE

SQL> shutdown immediate Database closed.

SQL> startup mount
ORACLE instance started.
复制代码

关闭归档模式  ----提示我们数据库运行在归档模式 闪回开启的情况下,是不能直接关闭归档的

SQL> alter database noarchivelog; alter database noarchivelog * ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled

那么我们就先关闭闪回

复制代码
SQL> alter database flashback off; Database altered.

SQL> alter database noarchivelog; Database altered.

SQL> alter database open; Database altered.

SQL> archive log list Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6 Current log sequence 8 SQL> select flashback_on from v$database;
FLASHBACK_ON ------------------ NO
复制代码

重新启动,开启归档 查看归档路径

复制代码
SQL> shutdown immediate Database closed.
SQL> startup mount    
ORACLE instance started.
SQL> alter database archivelog; Database altered.
SQL> alter database open; Database altered.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 SQL> select flashback_on,open_mode from v$database;
FLASHBACK_ON       OPEN_MODE ------------------ -------------------- NO READ WRITE
复制代码

结论:开启归档的话,不一定需要开启闪回功能,可以不开启闪回!!!

测试3:修改归档默认路径

复制代码
SQL> shutdown immediate
SQL> startup mount
ORACLE instance started.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 SQL> alter system set log_archive_dest_1='location=/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch';
System altered.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 SQL> select flashback_on,open_mode from v$database;

FLASHBACK_ON       OPEN_MODE ------------------ -------------------- NO           MOUNTED
复制代码

测试4: 我们关闭归档 先开启闪回  再开启归档  启动数据库 查看归档路径 是否会见自定义的路径 修改为默认的闪回路径

 

复制代码
SQL> alter database noarchivelog; Database altered.

SQL> archive log list Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6 Current log sequence 8 SQL> alter database flashback on; ----非归档模式下 无法开启闪回  alter database flashback on * ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
复制代码

结论: 非归档模式下 无法开启闪回

################################################

复制代码
SQL> alter database archivelog; Database altered.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 SQL> SQL> alter database flashback on; Database altered.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8
复制代码

结论: 如果归档日志指定了存储路径 那么开启闪回的话 归档日志的存放路径还是保持用户原先指定的路径

#############################
继续:
修改归档日志为数据库默认的存放路径 重复上述操作  确认归档日志的路径 是否为闪回区的路径

复制代码
SQL> shutdown immediate Database closed.
SQL> startup mount
ORACLE instance started.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination /home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 SQL> alter system set log_archive_dest_1=''; ---修改归档日志为数据库默认的存放路径 System altered.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           ?/dbs/arch
Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 SQL> alter database open; Database altered.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           ?/dbs/arch
Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 SQL> select flashback_on,open_mode from v$database;
FLASHBACK_ON       OPEN_MODE ------------------ -------------------- YES READ WRITE

SQL> shutdown immediate Database closed.

SQL> startup
ORACLE instance started.

SQL> archive log list Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8
复制代码

结论:归档日志为数据库默认的存放路径,在闪回功能开启的情况下,切换为归档模式,归档日志的路径默认为闪回区的路径。

 

其他参考:

http://xmdiao.blog.51cto.com/6331248/1392207/
http://blog.csdn.net/weixin_36707770/article/details/53885423
http://blog.csdn.net/panfelix/article/details/43452121
http://blog.sina.com.cn/s/blog_54eeb5d90100kqf4.html

http://blog.chinaunix.net/uid-28255964-id-4344766.html



http://blog.chinaunix.net/uid-26446098-id-4979004.html

阅读(602) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~