archive相关笔记备份

ArchonSbkd

已于 2023-03-07 15:54:47 修改

阅读量265

点赞数

文章标签：数据仓库 dba

于 2022-07-19 16:08:21 首次发布

本文链接：https://blog.csdn.net/ArchonSbkd/article/details/125874414

版权

一、前言

1、数据库两种方式:归档与非归档(可以互调)

容灾性:
非归档: 可以避免数据库内存、后台进程崩溃死机等'实例故障',但是无法避免'介质故障'比如硬盘坏了,此模式只能冷备份
归档: 可以处理任何故障。
举例:
数据库7月至今一直运行,现在19号,然后忽然要求让恢复到7月10号12:00:00这个状态。
那么归档模式完整的话,可以达到。
非归档模式不行。

2、联机重做日志文件的概念:

redolog
别名:（联机重做日志文件）（联机日志文件）（在线日志文件）
对数据库所做的所有操作会随机记录到日志文件中(除了select)
先记录到日志缓冲去,在通过LGWR这个进程记录把缓冲区中的崇左条目写到联机日志文件中去（redolog中）
等写满了就会写到归档文件中去
日志按照日志组来组织,每一个组有多个文件
日志按照组按循环方式来工作
至少有两个日志组来循环
一个被写满,就会发生日志切换,反复进行
！如果非归档模式,在切换时会直接丢弃不会写到磁盘中
！如果归档模式,在切换时会arch进程拷贝到xx路径中,就是归档过程

3、作用

①非常重要的作用:数据库的恢复
假设:
用户修改了记录,commit,之后数据库宕机
重启数据库
启动时:有前滚、回滚
达到:只要是用户commit的记录,都不会丢失。(没有commit都不保存)
②:数据挖掘
logmnr

4、归档文件后缀:

增量:inc
全备:full
归档:arch
(每一个全备之后,时间点之前的归档文件可能就在rman里检索不到了)

二、开归档

1、单实例开归档

archive log list
查看归档状态。32:46
非归档---->归档
1、关闭数据库
shutdown immediate;
2、控制文件已经打开但是用户信息不能查看
startup mount
3、打开归档
alter database archivelog;（noarchivelog）
4、打开数据库
alter database open;
5、查看归档相关信息:
show parameter db_recovery
db_recovery_file_dest查看归档到的路径
db_recovery_file_dest_size大小
6、切换日志,（老的日志会归档）
alter system switch logfile;
7、指定的归档位置1_10
show parameter log_archive_dest
8、将指定归档日志放到某个路径下(需要切换日志后生效)
alter system set log_archive_dest_1='location=/xxxxxx'
9、指定某个归档是否使用
alter system set log_archive_dest_state_1='defer';(enable)
10、查看是否开可以进行归档参数（这个参数已经废弃）
show parameter log_archive_start
<—————————————————————————————————————————>

2、集群rac开归档:

1、单节点关闭集群状态
alter system set cluster_database=false scope=spfile sid='gkl1';
2、双节点关闭数据库
shutdown immediate;
3、单节点到mount状态(控制文件已经打开但是用户信息不能查看)
startup mount
4、单节点将指定归档日志放到某个路径下(需要切换日志后生效)
alter system set log_archive_dest_1='location=/xxxxxx'
5、单节点打开归档
alter database archivelog;（noarchivelog）
6、单节点切回集群状态
alter system set cluster_database=true scope=spfile sid='gkl1';
7、双节点打开数据库
shutdown immediate;
alter database open;

三、RMAN/归档

1、进rman

rman target /

2、清归档

list archivelog all;                --查看（可以看路径）
crosscheck archivelog all;           --校验
list expired archivelog all;       --查看过期的
delete expired archivelog all;       --删除所有过期的

3、写在crontab 里

0 1 * * * /u01/app/oracle/11.2.0.4/oracle/guidang.sh >> /home/xx/$(date +"\%Y\%m\%d")zcj5435.log

#!/bin/bash
# 自己创建日志路径(oracle的)
BACK_DIR=/xxx/xx
export DATE=`date +%F`
mkdir -p $BACK_DIR/$DATE
touch $BACK_DIR/$DATE/rman_backup.log
source ~/.bash_profile
# 有时会没有profile文件,删除本行即可
# 记录日志rman_backup.log 可以后期检查是否执行成功

rman target / >> $BACK_DIR/$DATE/rman_backup.log <<EOF
# 把无效的expired的archivelog标出来
crosscheck archivelog all;
# 直接全部删除过期的归档日志
#delete noprompt expired archivelog all;
# 直接删除7天前所有的归档日志
delete noprompt archivelog all completed before 'sysdate-7';
exit
EOF

4、切换日志,（老的日志会归档）

alter system switch logfile;

5、查看单个大小

select l.GROUP#,l.THREAD#,l.BYTES,l.MEMBERS,l.STATUS,f.MEMBER from v$log l,v$logfile f where l.GROUP#=f.GROUP# order by 1;

①查看日期时间对应大小
SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",THREAD#,
count(1) "Total",
SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23",
round(count(1) / 24, 2) "Avg"
FROM V$log_history
group by trunc(first_time), to_char(first_time, 'Dy'),THREAD#
Order by 1;
②精简:
SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
count(1) "Total",
round(count(1) / 24, 2) "Avg"
FROM V$log_history
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1;

四、报错:

https://www.cnblogs.com/-txf-/p/10021721.html
Thread 2 cannot allocate new log, sequence 83272
Checkpoint not complete
重做日志文件无法正确切换保存的问题
查看信息
select * from v$log;
增加一个名字为RED004.LOG的log文件
alter database add logfile ('/xxx/RED004.LOG') size 50M;
执行一下手动切换日志文件命令
alter system switch logfile;

五、rman备份脚本crontab

1、全备

#!/bin/bash
. /export/home/oracle/.profile
cd /orabk
export ORACLE_SID=gkl1
$ORACLE_HOME/bin/rman target / log=/orabk/`date +%Y%m%d%H%M`_gkldbbk_full.log << EOF

#backup database
run {
ALLOCATE CHANNEL ch00 device type disk maxpiecesize=50G;
ALLOCATE CHANNEL ch01 device type disk maxpiecesize=50G;
sql 'alter system archive log current';
backup AS COMPRESSED BACKUPSET incremental level=0 database format='/orabk/%T_%U_gkldb_full';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;

# backup all archive logs
ALLOCATE CHANNEL ch00 device type disk maxpiecesize=50G;
ALLOCATE CHANNEL ch01 device type disk maxpiecesize=50G;
sql 'alter system archive log current';
backup AS COMPRESSED BACKUPSET archivelog all format='/orabk/%T_%U_gklarch' delete input;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;

# backup control file
ALLOCATE CHANNEL ch00 device type disk;
backup current controlfile format='/orabk/%T_%U_gklctl_full';
RELEASE CHANNEL ch00;
}

#del backup
run {
crosscheck backup;
crosscheck archivelog all;
report obsolete;
delete noprompt backup of database completed before 'sysdate -7';
delete noprompt backup of archivelog all completed before 'sysdate -7';
delete noprompt backup of controlfile completed before 'sysdate -7';
}
exit;

EOF

2、增量

#!/bin/bash
. /export/home/oracle/.profile
cd /orabk
export ORACLE_SID=gkl1
$ORACLE_HOME/bin/rman target / log=/orabk/`date +%Y%m%d%H%M`_gkldbbk_inc.log << EOF

#backup database
run {
ALLOCATE CHANNEL ch00 device type disk maxpiecesize=50G;
ALLOCATE CHANNEL ch01 device type disk maxpiecesize=50G;
sql 'alter system archive log current';
backup AS COMPRESSED BACKUPSET incremental level=1 database format='/orabk/%T_%U_gkldb_inc';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;

# backup control file
ALLOCATE CHANNEL ch00 device type disk;
backup current controlfile format='/orabk/%T_%U_gklctl_full';
RELEASE CHANNEL ch00;
}

#del backup
run {
crosscheck backup;
crosscheck archivelog all;
report obsolete;
delete noprompt backup of database completed before 'sysdate -7';
delete noprompt backup of database completed before 'sysdate -7';
delete noprompt backup of controlfile completed before 'sysdate -7';
}
exit;

EOF