oracleg更改归档模式目录日志大小
修改oracle重做日志文件大小
修改oracle重做⽇志⽂件⼤⼩1. 创建3个新的⽇志组SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 500M; SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') SIZE 500M; SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 500M;查看⽇志组SQL> select * from v$logfile;2.切换当前⽇志到新的⽇志组SQL> alter system switch logfile;SQL> alter system switch logfile;SQL> alter system switch logfile;切到要删除为INCACTIVE才⾏SQL> select group#,sequence#,bytes,members,status from v$log;GROUP# SEQUENCE# BYTES MEMBERS STATUS---------- ---------- ---------- ---------- ----------------1 139 ******** 1 INACTIVE2 137 ******** 1 INACTIVE3 138 ******** 1 INACTIVE4 140 524288000 1 ACTIVE5 141 524288000 1 CURRENT6 136 524288000 1 INACTIVE3.删除旧的⽇志组SQL> alter database drop logfile group 1;Database altered.SQL> alter database drop logfile group 2;Database altered.SQL> alter database drop logfile group 3;查看是否删除了⽇志组SQL> select group#,sequence#,bytes,members,status from v$log;GROUP# SEQUENCE# BYTES MEMBERS STATUS---------- ---------- ---------- ---------- ----------------4 140 524288000 1 INACTIVE5 141 524288000 1 CURRENT6 136 524288000 1 INACTIVE4.操作系统删除原⽇志组1、2、3中的⽂件[oracle@oracle122 log]$ cd /u01/app/oracle/oradata/orcl[oracle@oracle122 orcl]$[oracle@oracle122 orcl]$ pwd/u01/app/oracle/oradata/orcl[oracle@oracle122 orcl]$ lscontrol01.ctl example01.dbf redo01.log redo02.log redo03.log redo04.log redo05.log redo06.log sysaux01.dbf system01.dbftemp01.dbf undotbs01.dbf users01.dbf[oracle@oracle122 orcl]$ rm -rf redo01.log[oracle@oracle122 orcl]$ rm -rf redo02.log[oracle@oracle122 orcl]$ rm -rf redo03.log5.重建⽇志组1、2、3SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 500M;SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 500M;SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 500M;7.删除中间过渡⽤的⽇志组4、5、6 ，在删除时查询select group#,sequence#,bytes,members,status from v$log; 删除组是否为INACTIVE，如果不是INACTIVE 就要执⾏alter system switch logfile;查询看⼀下SQL> select group#,sequence#,bytes,members,status from v$log;GROUP# SEQUENCE# BYTES MEMBERS STATUS---------- ---------- ---------- ---------- ----------------1 148 524288000 1 CURRENT2 143 524288000 1 INACTIVE3 144 524288000 1 INACTIVE4 146 524288000 1 INACTIVE5 147 524288000 1 INACTIVE6 145 524288000 1 INACTIVESQL> alter database drop logfile group 4;Database altered.SQL> alter database drop logfile group 5;Database altered.SQL> alter database drop logfile group 6;Database altered.8.到操作系统删除组4、5、6[oracle@oracle122 orcl]$ rm -rf redo04.log[oracle@oracle122 orcl]$ rm -rf redo05.log[oracle@oracle122 orcl]$ rm -rf redo06.log9.备份当前最新的控制⽂件SQL> alter database backup controlfile to trace resetlogs ;。
oracle10g的归档模式和归档路径的更改
db_recovery_file_dest string /APPL/flash_recovery_area/arch
ARC0: Error 19809 Creating archive log file to '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_04_30/o1_mf_1_220_0_.arc'
这时我们可以修改它的默认限制，比如说将它增加到5G或更多，也可以将归档路径重新置到别的路径，就不会有这个限制了。
SQL> alter system set log_archive_dest_1='location=/u01/raw2arch' sid='raw2';
System altered.
SQL> show parameter log_archive
NAME TYPE VALUE
log_archive_dest_1 string location=/u01/raw2arch
... ....
log_archive_dest_state_1 string enable
... ....
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shutdown immediate;
startup mount exclusive;
oracle11g开启归档模式及修改归档目录日志满
oracle11g开启归档模式及修改归档⽬录⽇志满oracle 11g开启归档模式及修改归档⽬录⽇志满/s/blog_95b5eb8c01018ylb.htmloracle 11g开启归档模式及修改归档⽬录2011-06-28 22:29在Oracle 11g，开启archive log模式时，默认归档⽬录为db_recovery_file_dest指定。
此参数在pfile/spfile中可以指定：db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'更改归档模式需要在mount状态下，更改归档模式。
SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.--如果安装多个库，会报错，找不到句柄exit 再⽤管理员进⼊Total System Global Area 1258291200 bytesFixed Size 1219160 bytesVariable Size 318768552 bytesDatabase Buffers 922746880 bytesRedo Buffers 15556608 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 15Next log sequence to archive 17Current log sequence 17更改log_archive_dest_1参数可更改归档⽇志⽬录（pfile/spfile中参数db_recovery_file_dest指定的⽬录将⽆效）SQL> alter system set log_archive_dest_1='location=/data/oracle/log1/archive_log'; 最后的⽬录名称需要为archive_log! Linux:alter system set log_archive_dest_1='location=/u01/oracle/log/archive_log';System altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /data/oracle/log1/archive_logOldest online log sequence 26Next log sequence to archive 28Current log sequence 28实际上从Oracle 10g开始，可以⽣成多份⼀样的⽇志，保存多个位置，以防不测，⽅法如下：SQL>alter system set log_archive_dest_2='location=/data/oracle/log2/archive_log';SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /data/oracle/log2/archive_log 只能看到最新设置的归档⽬录。
oracle 11gR2 改数据库为归档模式
oracle 11gR2 改数据库为归档模式前几天在公司做了oracle 11gR2 rac for aix的安装实验,记录了下将数据库非归档模式改为归档模式的步骤,和10g步骤差不多,以下为详细步骤:第一步:设置归档目录大小和路径[oracle@p520:/oracle/app/oracle]$sqlplus "/as sysdba"SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 26 09:56:24 2010Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> show parameter db_recoverNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest stringdb_recovery_file_dest_size big integer 0SQL> alter system set db_recovery_file_dest='+dgrecover' scope=spfile;System altered.SQL> show parameter spfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string +DGSYSTEM/orcl/spfileorcl.oraSQL> alter system set db_recovery_file_dest_size=8G scope=spfile;System altered.第二步:修改cluster_database的值为falseQL> alter system set cluster_database=false scope=spfile sid='orcl1';System altered.第三步:停止所有节点上的实例[oracle@p520:/oracle/app/oracle]$srvctl stop database -d orcl第四步:启动到mount状态,改数据库为archivelog[oracle@p520:/oracle/app/oracle]$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 26 10:00:25 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to an idle instance.SQL> startup mountORACLE instance started.Total System Global Area 2137886720 bytesFixed Size 2208496 bytesVariable Size 1526730000 bytesDatabase Buffers 603979776 bytesRedo Buffers 4968448 bytesDatabase mounted.SQL> alter database archivelog;Database altered.第五步:修改cluster_database的值为tuceSQL> alter system set cluster_database=true scope=spfile sid='orcl1';System altered.第六步:关闭数据库,用srvctl 启动所有节点上的实例SQL> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options[oracle@p520:/oracle/app/oracle]$srvctl start database -d orcl第七步:检查数据库是否为归档模式[oracle@p520:/oracle/app/oracle]$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 26 10:05:13 2010Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 70Next log sequence to archive 71Current。
ORACLE数据库调整归档日志空间大小
ORACLE数据库归档日志满后造成无法启动/连接的处理方法在\app\Administrator\diag\rdbms\orcl\orcl\trace（其中orcl根据具体的数据库实例名称而定）路径下的log中可以看到以下信息：ORA-19815: W ARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.Wed Jan 9 15:00:29 2013************************************************************************You have following choices to free up space from flash recovery area:1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,then consider changing RMAN ARCHIVELOG DELETION POLICY.2. Back up files to tertiary device such as tape using RMANBACKUP RECOVERY AREA command.3. Add disk space and increase db_recovery_file_dest_size parameter toreflect the new space.4. Delete unnecessary files using RMAN DELETE command. If an operatingsystem command was used to delete files, then use RMAN CROSSCHECK andDELETE EXPIRED commands.ORA-19815: W ARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.这句日志意思是db_recovery_file_dest_size已经满了，导致数据库无法启动。
ORACLE归档日志设置
ORACLE归档日志设置在ORACLE 数据库的开发环境和测试环境中，数据库的日志模式和自动归档模式一般都是不设置的，这样有利于系统应用的调整，也免的生成大量的归档日志文件将磁盘空间大量的消耗。
但在系统上线，成为生产环境时，将其设置为日志模式并自动归档就相当重要了，因为，这是保证系统的安全性，有效预防灾难的重要措施。
这样，通过定时备份数据库和在两次备份间隔之间的日志文件，可以有效的恢复这段时间的任何时间点的数据，可以在很多时候挽回或最大可能的减少数据丢失。
一、要使OARCLE 数据库进行日志的自动归档，需要做两方面的事情；1．是数据库日志模式的设置(可为Archive Mode 和No Archive Mode)；2．就是自动归档模式设置(Automatic archival,可为Enabled 和Disabled)。
二、如何查看数据库的现行日志和自动归档模式的设置可用archive log list 命令来查看。
运行在日志自动归档模式下的数据库系统查看结果如下（一般是生产环境）：SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /backup/archivelogOldest online log sequence 2131Next log sequence to archive 2133Current log sequence 2133没有启动数据库日志模式和自动归档的数据库系统查看结果如下（一般是测试环境）：Database log mode No Archive ModeAutomatic archival DisabledArchive destination /u01/app/oracle/product/8.1.7/dbs/arch Oldest online log sequence 194Current log sequence 196三. 数据库日志模式的设置在创建数据库时，可以在CREATE DATABASE 语句中指定数据库的日志模式。
oracleg更改归档模式目录日志大小
oracle11g更改归档模式、目录、日志大小一、更改Oracle为归档模式1.关闭oracleSQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.2.启动为mount状态SQL>startupmountORACLEinstancestarted.TotalSystemGlobalAreabytesFixedSize2283984bytesVariableSizebytesDatabaseBuffersbytesRedoBuffers2945024bytesDatabasemounted.3.更改为归档模式SQL>alterdatabasearchivelogDatabasealtered.备注：‘archivelog’为归档模式；‘noarchivelog’为非归档模式.4.更改数据库为‘打开’状态SQL>alterdatabaseopen5.查看归档模式信息SQL>archvieloglist它会提示归档(de)模式、是否启用、参数DatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestinationUSE_DB_RECOVERY_FILE_DEST Oldestonlinelogsequence88Nextlogsequencetoarchive90Currentlogsequence90二、更改归档目录1.查看参数db_recovery_file_dest（1）“db_recovery_file_dest”是用于定义存储归档日志(de)目录.通过showparameter命令,显示参数(de)值为（默认为）fast_recovery_area.示例如下：SQL>showparameterdb_recoveryNAMETYPEVALUE-----------------------------------------------------------------------------db_recovery_file_deststringD:\oracle\fast_recovery_areadb_recovery_file_dest_sizebiginteger4182M（2）查看v$recovery_file_dest视图,可得知fast_recovery_area(de)空间限制、已使用(de)空间、文件数等.SQL>selectfromv$recovery_file_dest;NAMESPACE_LIMITSPACE_USEDSPACE_RECLAIMABLENUMBER_OF_FILES-----------------------------------------------------D:\oracle\fast_recovery_area27412481022.更改归档日志目录语法：altersystemset参数=值scope=spfile;示例：SQL>altersystemsetdb_recovery_file_dest='D:\oracle\archivelog's cope=spfile;Systemaltered.三、更改归档日志大小1.查看参数'db_recovery_file_dest_size'值SQL>showparameterdb_recoverNAMETYPEVALUE-----------------------------------------------------------------------------db_recovery_file_deststringD:\oracle\archivelogdb_recovery_file_dest_sizebiginteger4182M2.更改参数'db_recovery_file_dest_size'值大小SQL>altersystemsetdb_recovery_file_dest_size=41820Mscope=spfile ;Systemaltered.3.关闭数据库,重启数据库SQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>startupopen;ORACLEinstancestarted.TotalSystemGlobalAreabytesFixedSize2283984bytesVariableSizebytesDatabaseBuffersbytesRedoBuffers2945024bytesDatabasemounted.Databaseopened.4.再次查看参数'db_recovery_file_dest_size'值SQL>showparameterdb_recoNAMETYPEVALUE-----------------------------------------------------------------------------db_recovery_file_deststringD:\oracle\archivelog db_recovery_file_dest_sizebiginteger41820M。
修改Oracle归档日志大小
1、创建2个新的日志组
alter database add logfile group 4 ('H:\ORACLE\ORADATA\YLZHIS\redo04.log') size 50M;
alter database add logfile group 5 ('H:\ORACLE\ORADATA\YLZHIS\redo05.log') size 50M;
1.alter database drop logfile group 1;
2.alter database drop logfile group 2;
3.alter database drop logfile group 3;
4、操作系统下删除原日志组1、2、3中的文件
2、切换当前日志到新的日志组
1.alter system switch logfile; select * from v$log;可能需要多次执行，确认日志已经切换到4或者5
2.alter system checkpoint;
3、删除旧的日志组
改变归档日志大小只有改变日志组的大小。
方法：加入新的大的日志文件，然后删掉旧的小的日志文件
假设现有三个日志组，每个组内有一个成员，每个成员的大小为1MB，现在想把此三个日志组的成员大小都改为10MB
先查询联机日志文件路径 select * from v$logfile
alter database add logfile group 3 ('H:\ORACLE\ORADATA\YLZHIS\redo03.log') size 50M;
6、切换日志组
1.alter system switch logfile; select * from v$log;可能需要多次执行，确认日志已经切换到4、5、6
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
o r a c l e g更改归档模式
目录日志大小
Document number【980KGB-6898YT-769T8CB-246UT-18GG08】
oracle11g更改归档模式、目录、日志大小
一、更改Oracle为归档模式
1.关闭oracle
SQL>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
2.启动为mount状态
SQL>startupmount
ORACLEinstancestarted.
TotalSystemGlobalAreabytes
FixedSize2283984bytes
VariableSizebytes
DatabaseBuffersbytes
RedoBuffers2945024bytes
Databasemounted.
3.更改为归档模式
SQL>alterdatabasearchivelog
Databasealtered.
备注：‘archivelog’为归档模式；‘noarchivelog’为非归档模式。
4.更改数据库为‘打开’状态
SQL>alterdatabaseopen
5.查看归档模式信息
SQL>archvieloglist
它会提示归档的模式、是否启用、参数
DatabaselogmodeArchiveMode
AutomaticarchivalEnabled
ArchivedestinationUSE_DB_RECOVERY_FILE_DEST
Oldestonlinelogsequence88
Nextlogsequencetoarchive90
Currentlogsequence90
二、更改归档目录
1.查看参数db_recovery_file_dest
（1）“db_recovery_file_dest”是用于定义存储归档日志的目录。
通过showparameter命令，显示参数的值为（默认为）fast_recovery_area。
示例如下：
SQL>showparameterdb_recovery
NAMETYPEVALUE
-----------------------------------------------------------------------------
db_recovery_file_deststringD:\oracle\fast_recovery_area
db_recovery_file_dest_sizebiginteger4182M
（2）查看v$recovery_file_dest视图，可得知fast_recovery_area的空间限制、已使用的空间、文件数等。
SQL>select*fromv$recovery_file_dest;
NAMESPACE_LIMITSPACE_USEDSPACE_RECLAIMABLENUMBER_OF_FILES
-----------------------------------------------------
D:\oracle\fast_recovery_area2741248102
2.更改归档日志目录
语法：altersystemset参数=值scope=spfile;
示例：
SQL>altersystemsetdb_recovery_file_dest='D:\oracle\archivelog'scope=s pfile;
Systemaltered.
三、更改归档日志大小
1.查看参数'db_recovery_file_dest_size'值
SQL>showparameterdb_recover
NAMETYPEVALUE
-----------------------------------------------------------------------------
db_recovery_file_deststringD:\oracle\archivelog
db_recovery_file_dest_sizebiginteger4182M
2.更改参数'db_recovery_file_dest_size'值大小
SQL>altersystemsetdb_recovery_file_dest_size=41820Mscope=spfile; Systemaltered.
3.关闭数据库，重启数据库
SQL>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
SQL>startupopen;
ORACLEinstancestarted.
TotalSystemGlobalAreabytes
FixedSize2283984bytes
VariableSizebytes
DatabaseBuffersbytes
RedoBuffers2945024bytes
Databasemounted.
Databaseopened.
4.再次查看参数'db_recovery_file_dest_size'值
SQL>showparameterdb_reco
NAMETYPEVALUE
-----------------------------------------------------------------------------
db_recovery_file_deststringD:\oracle\archivelog
db_recovery_file_dest_sizebiginteger41820M。