oracle的日志管理

QQ叫紫珊

　　redo日志和归档日志的区别：
　　redo是循环切换写的只能恢复最近三个日志文件，归档是一直写的能恢复任何时候的日志文件
　　一句话：归档是加长版的redo
　　###########################################################################################
　　redo文件管理和使用
　　案例1：增加redo组
　　初始默认三组redo

　　SQL>>
　　Database>　　确认：select * from v$log
　　SQL> desc v$log;
　　Name                       Null?    Type
　　----------------------------------------- -------- ----------------------------
　　GROUP#                         NUMBER    --组号
　　THREAD#                        NUMBER    --线程号，单节点数据库下都是1
　　SEQUENCE#                        NUMBER    --序号
　　BYTES                            NUMBER    --文件大小
　　MEMBERS                        NUMBER    --成员数。默认是1
　　ARCHIVED                        VARCHAR2(3)  --是否已经归档
　　STATUS                         VARCHAR2(16) --状态
　　FIRST_CHANGE#                        NUMBER        ---起始的检查点
　　FIRST_TIME                        DATE          --上面的检查点对应的时间
　　select * from v$logfile   --查询redo文件的路径
　　###########################################################################################
　　案例2：给redo文件做多路复用（二路复用）
　　group1      group2      group3
　　disk1    redo01.log   redo02.log  redo03.log
　　--------------------------------------------
　　disk2    redo0101.log  redo0201.log  redo0301.log
　　LGWR写文件的时候是一组一组写的，同时写组1 的两个文件，两个文件的内容是一样的。

　　SQL>>　　--成员

　　Database>
　　SQL>>
　　Database>
　　SQL>>
　　Database>
　　SQL>>
　　Database>　　select * from v$logfile  --确认
　　这个操作创建在一个磁盘上是没有任何意义的，必须要创建两个磁盘上。
　　############################################################################################
　　案例3：删除一个redo组

　　SQL>>　　alter database drop logfile group 4
　　*
　　ERROR at line 1:
　　ORA-01623: log 4 is current log for instance ORCL (thread 1) - cannot drop
　　ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/ORCL/redo04.log'
　　ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/ORCL/redo0401.log'   --current的组是不能删除的

　　SQL>>　　alter database drop logfile group 4
　　*
　　ERROR at line 1:
　　ORA-01624: log 4 needed for crash recovery of instance ORCL (thread 1)
　　ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/ORCL/redo04.log'
　　ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/ORCL/redo0401.log'   --ACTIVE的组也是不能删除的

　　SQL>>
　　Database>　　##########################################################################################
　　案例4：删除redo组成员

　　SQL>>　　alter database drop logfile member '/u01/oracle/oradata/ORCL/redo01.log'
　　*
　　ERROR at line 1:
　　ORA-01609: log 1 is the current log for thread 1 - cannot drop members
　　ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ORCL/redo01.log'
　　ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/ORCL/redo0101.log'   --current的成员不能删除

　　SQL>>
　　Database>
　　SQL>>
　　Database>　　一个数据库最少得两组redo

　　SQL>>　　alter database drop logfile group 4
　　*
　　ERROR at line 1:
　　ORA-01567: dropping log 4 would leave less than 2 log files for instance ORCL   --最少两个redo组
　　(thread 1)
　　ORA-00312: online log 4 thread 1: '/u01/oracle/oradata/ORCL/redo0401.log'
　　注意：redo文件被删除后，物理上是不会被删除的，需要手工在系统上删除。
　　看清楚数据字典再删，不小心删掉正在使用的就麻烦了。
　　[oracle@oracle3 ORCL]$ rm -rf redo02*
　　[oracle@oracle3 ORCL]$ rm -rf redo03*
　　[oracle@oracle3 ORCL]$ rm -rf redo04.log
　　练习：将数据库改为5组，二路复用，要求创建在不同的磁盘上。

　　alter database add logfile group 3 '/u01/oracle/oradata/ORCL/redo0301.log'>　　alter database add logfile member '/u02/redo/redo0202.log' to group 2;
　　###########################################################################################
　　案例5：调整redo文件大小
　　需求：将redo文件大小从50M调整为100M
　　思路：
　　1、将inactive状态的redo组删除，重新创建redo文件大小100M，并添加二路复用。
　　2、将active和current状态的redo组切换为inactive状态的redo组删除，再重新创建并添加二路复用。
　　#########################################################################################
　　案例6：打开数据库的归档日志--生产环境必须要打开的
　　1、查看数据库的归档模式
　　SQL> conn /as sysdba
　　Connected.
　　SQL> archive log list;
　　Database log mode           No Archive Mode   --当前数据库处于非归档模式状态
　　Automatic archival           Disabled                  --自动归档是没有启用的
　　Archive destination           USE_DB_RECOVERY_FILE_DEST  --归档文件的目的地
　　Oldest online log sequence     153                   当前日志中序号最老的一个
　　Current log sequence           154                 --当前的日志的sequence
　　SQL> show parameter DB_RECOVERY_FILE_DEST
　　NAME                     TYPE     VALUE
　　------------------------------- ----------- ------------------------------
　　db_recovery_file_dest         string      /u01/oracle/flash_recovery_area     --归档目的地路径默认在闪回恢复区
　　db_recovery_file_dest_size         big integer 2G                                --归档目的地大小
　　2、将归档创建在其他的目录中，二路复用，模拟两磁盘
　　1 创建归档目录
　　[oracle@oracle3 oracle]$ mkdir arch2
　　[oracle@oracle3 oracle]$ pwd
　　/u01/oracle
　　[oracle@oracle3 u03]$ mkdir arch1
　　2 停机改参数
　　SQL> shutdown immediate
　　Database closed.
　　Database dismounted.
　　ORACLE instance shut down.
　　SQL> create pfile from spfile;
　　File created.
　　在$ORACLE_HOME/dbs下的initORCL.ora文件中添加三个参数：
　　*.log_archive_dest_1='LOCATION=/u03/arch1'
　　*.log_archive_dest_2='LOCATION=/u01/oracle/arch2'    --归档目的地参数
　　*.log_archive_format='archivelog_%t_%s_%r'             --归档文件格式参数
　　3、启动打开归档模式
　　SQL> create spfile from pfile;
　　File created.
　　SQL> startup mount                   --要在mount阶段打开归档模式
　　ORACLE instance started.
　　Total System Global Area 1258291200 bytes

　　Fixed>
　　Variable>　　Database Buffers      922746880 bytes
　　Redo Buffers           32284672 bytes
　　Database mounted.

　　SQL>>
　　Database>　　SQL> archive log list;
　　Database log mode           Archive Mode
　　Automatic archival           Enabled
　　Archive destination           /u01/oracle/arch2
　　Oldest online log sequence     153
　　Next log sequence to archive   154
　　Current log sequence           154

　　SQL>>
　　Database>　　测试：

　　SQL>>
　　System>
　　SQL>>
　　System>　　[oracle@oracle3 arch1]$ ll
　　总计 8152
　　-rw-r----- 1 oracle oinstall 8330240 06-27 09:34 archivelog_1_154_849370865
　　-rw-r----- 1 oracle oinstall    1024 06-27 09:34 archivelog_1_155_849370865
　　[oracle@oracle3 arch1]$ cd /u01/oracle/arch2/
　　[oracle@oracle3 arch2]$ ll
　　总计 8152
　　-rw-r----- 1 oracle oinstall 8330240 06-27 09:34 archivelog_1_154_849370865
　　-rw-r----- 1 oracle oinstall    1024 06-27 09:34 archivelog_1_155_849370865
　　SQL> show parameter log_archive_dest   --oracle支持10路归档
　　NAME                     TYPE     VALUE
　　------------------------------------ ----------- ------------------------------
　　log_archive_dest             string
　　log_archive_dest_1             string     LOCATION=/u03/arch1
　　log_archive_dest_10             string
　　log_archive_dest_2             string     LOCATION=/u01/oracle/arch2
　　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
　　[oracle@oracle3 arch2]$ ps -ef|grep ora_arc
　　oracle    4052     1  0 09:34 ?        00:00:00 ora_arc0_ORCL  --每一路都是一个独立的进程，二路复用就两个进程
　　oracle    4054     1  0 09:34 ?        00:00:00 ora_arc1_ORCL   --归档进程，不是在nomount阶段启动，在open阶段启动。
　　select * from v$archived_log where dest_id=2
　　SQL> desc v$archived_log
　　Name                       Null?    Type
　　----------------------------------------- -------- ----------------------------
　　RECID                            NUMBER         --主键
　　STAMP                            NUMBER         --用数字表示的时间戳  单位：厘秒   1s=100厘秒   (t1-t2)/100=时间差
　　NAME                            VARCHAR2(513)  --归档文件的路径
　　DEST_ID                        NUMBER         --目的地编号
　　THREAD#                        NUMBER         --线程号
　　SEQUENCE#                        NUMBER         --对应redo的序号
　　RESETLOGS_CHANGE#                    NUMBER
　　RESETLOGS_TIME                     DATE
　　RESETLOGS_ID                        NUMBER
　　FIRST_CHANGE#                        NUMBER         起始的检查点SCN
　　FIRST_TIME                        DATE           上面字段对应的时间
　　NEXT_CHANGE#                        NUMBER         终止的检查点的SCN
　　NEXT_TIME                        DATE           上面字段对应的时间
　　BLOCKS                         NUMBER         归档文件含有的数据块数
　　BLOCK_SIZE                        NUMBER         块大小
　　CREATOR                        VARCHAR2(7)    --创建进程
　　REGISTRAR                        VARCHAR2(7)    --注册进程
　　STANDBY_DEST                        VARCHAR2(3)
　　ARCHIVED                        VARCHAR2(3)    --文件完成的标志
　　APPLIED                        VARCHAR2(3)
　　DELETED                        VARCHAR2(3)
　　STATUS                         VARCHAR2(1)--文件的状态：A 可用的 D 表示已经被删除了 X 已经物理删除，但是数据库上没有更新
　　COMPLETION_TIME                    DATE          --归档文件完成的时间
　　DICTIONARY_BEGIN                    VARCHAR2(3)
　　DICTIONARY_END                     VARCHAR2(3)
　　END_OF_REDO                        VARCHAR2(3)
　　BACKUP_COUNT                        NUMBER
　　ARCHIVAL_THREAD#                    NUMBER
　　ACTIVATION#                        NUMBER
　　IS_RECOVERY_DEST_FILE                    VARCHAR2(3)   --是否在闪回恢复区
　　COMPRESSED                        VARCHAR2(3)   --归档文件存储是否压缩数据
　　FAL                            VARCHAR2(3)
　　END_OF_REDO_TYPE                    VARCHAR2(10)
　　将归档的一路调整到闪回恢复区
　　*.log_archive_dest_2='LOCATION=USE_DB_RECOVERY_FILE_DEST'  --闪回恢复区的归档文件名称是oracle自己定义的，无法修改
　　除此之外将闪回恢复区的空间上限调大：

　　SQL>>
　　System>　　SQL> show parameter DB_RECOVERY_FILE_DEST
　　NAME                     TYPE     VALUE
　　------------------------------------ ----------- ------------------------------
　　db_recovery_file_dest             string     /u01/oracle/flash_recovery_area
　　db_recovery_file_dest_size         big integer 30G
　　#########################################################################################
　　计算redo文件大小设置的依据
　　一般要求redo半小时或者20分钟切换一次，每天生成的归档量是一定的。
　　假设redo大小是Xm 半小时切换一次
　　初始情况下redo大小50m，自然切换方式，redo大小=归档大小，计算每天的redo量=50M X 当前的redo的sequence差
　　多次测算计算当前库的每天redo量=Ymb。
　　Xmb * 48 = Y                 X=ceil(Y/48)*(1.1~1.2)  redo文件设置xmb基本保证每天归档个数是48个
　　ceil--天花板，向上取整函数
　　计算归档磁盘容量一年= （X * 48 *365）* 1.1
　　设置切换的频率：

　　SQL>>
　　System>　　SQL> show parameter archive_lag
　　NAME                     TYPE     VALUE
　　------------------------------------ ----------- ------------------------------
　　archive_lag_target             integer     1800   --半小时切换一次
　　根据归档文件大小估算，制定磁盘设备数量方案一：
　　Xmb * 48 * 365 * 1.5
　　测算数据文件的增长，以制定采购磁盘设备数量
　　select tbs,
　　sum(totalM),
　　sum(usedM),
　　sum(remaindM),
　　round(sum(usedM) / sum(totalM), 2) as usedPer,
　　round(sum(remaindM) / sum(totalM), 2) as remaindPer

　　from (select b.FILE_ID>　　b.TABLESPACE_NAME tbs,
　　b.BYTES / 1024 / 1024 totalM,
　　(b.BYTES - sum(a.BYTES)) / 1024 / 1024 usedM,
　　sum(a.BYTES) / 1024 / 1024 remaindM
　　from dba_free_space a, dba_data_files b
　　where a.FILE_ID = b.FILE_ID
　　group by b.FILE_ID, b.TABLESPACE_NAME, b.BYTES
　　order by b.TABLESPACE_NAME)
　　group by tbs
　　方案二：
　　多次求差测算平均值，估计数据文件的增长量。Zmb * 365 * 1.2 * 3
　　###########################################################################################
　　案例：移动redo文件
　　需求：本地磁盘sda的redo文件移动到/u01/oracle/oradata/ORCL/redo下--数据库物理结构的改变
　　1、创建移动的路径
　　[oracle@oracle3 ORCL]$ mkdir redo
　　2、关闭数据库
　　SQL> shutdown immediate
　　Database closed.
　　Database dismounted.
　　ORACLE instance shut down.
　　3、移动
　　[oracle@oracle3 ORCL]$ mv *.log redo/
　　4、启动数据库到mount
　　SQL> startup mount
　　ORACLE instance started.
　　Total System Global Area 1258291200 bytes

　　Fixed>
　　Variable>　　Database Buffers      922746880 bytes
　　Redo Buffers           32284672 bytes
　　Database mounted.

　　SQL>>
　　Database>
　　SQL>>
　　Database>
　　SQL>>
　　Database>
　　SQL>>
　　Database>
　　SQL>>
　　Database>　　select * from v$logfile   --确认
　　###########################################################################################
　　增加归档进程的个数，并行写
　　SQL> show parameter log_archive_max
　　NAME                     TYPE     VALUE
　　------------------------------------ ----------- ------------------------------
　　log_archive_max_processes       integer     2

　　SQL>>
　　System>　　[oracle@oracle3 redo]$ ps -ef|grep ora_arc
　　oracle    4253     1  0 11:38 ?        00:00:00 ora_arc0_ORCL
　　oracle    4255     1  0 11:38 ?        00:00:00 ora_arc1_ORCL
　　oracle    4275     1  0 11:40 ?        00:00:00 ora_arc2_ORCL
　　oracle    4277     1  0 11:40 ?        00:00:00 ora_arc3_ORCL
　　虽然redo是按时切换的，但是如果一个大事务的操作还是会引发redo文件的频繁切换，所以，
　　如果我们发现报警日志中redo文件切换频繁，可以临时调整这个参数增加归档日志并发写入的程度。
　　###########################################################################################