oracle RAC 集群归档模式的关闭(适用于11g/12g/18c/19c)
1、登录数据库:执行alter system set cluster_database = false scope=spfile;
2、关闭node2数据库:shutdown immediate;
3、切换node1至mount状态:
shutdown immediate;
startup mount;
4、执行关闭归档:alter database noarchivelog;
5、开启数据库:alter database open;
6、在node1执行:alter system set cluster_database=true scope=spfile;
7、关闭数据库:shutdown immediate;
8、启动node1数据库:startup
9、启动node2数据库:startup
Oracle的集群和非集群的方式关于归档日志变更的方法,基本上没有区别。 RAC集群的各个节点不需要每个都进行archive log的变更设定操作,只是在一台机器上操作即可。
检查log mode
命令:archive log list
1
更改为archivelog
设定为archivelog模式(注意:需要在mount模式下进行操作)
命令:alter database archivelog
1
更改为noarchivelog
设定为noarchivelog模式(注意:需要在mount模式下进行操作)
命令:alter database noarchivelog
--ARCHIVE LOG
--开启归档
--示例
--rac开归档
--一个节点上执行
alter system set log_archive_dest_1='location=+DGARCH' sid='*';
--两个节点上执行
shutdown immediate;
startup mount;
alter database archivelog;
archive log list;
alter database open;
archive log list;
--关闭
--首先以grid用户关闭数据库
[grid@rac1 ~]$ srvctl stop database -d bddjprd -o immediate
--启动数据库到mount
[grid@rac1 ~]$ srvctl start database -d bddjprd -o mount
--任意节点关闭关闭归档
alter database noarchivelog;
--重新启动数据库:
[grid@rac1 ~]$ srvctl stop database -d bddjprd
[grid@rac1 ~]$ srvctl start database -d bddjprd
--验证结果:
SQL> archive log list
--参考
--方案1:
--单实例(如果是RAC所有节点都要执行)
--启动归档及修改归档路径
alter system set log_archive_dest_1='location=+ARCHDG' sid='*';
--重启DB
shutdown immediate;
startup mount;
--开启归档模式
alter database archivelog;
archive log list;
--修改闪回区大小及路径
alter database flashback off;
--启库
alter database open;
--查看归档
archive log list;
--查看闪回模式
select flashback_on from V$database;
--所有节点归档当前日志
alter system archive log current;
--方案2:
--修改归档路径
alter system set log_archive_dest_1='location=+ARCHDG' sid='*';
--1.shutdown immediate all database instances
srvctl stop database -d slprd
--2.startup database in mount mode
srvctl start database -d slprd -o mount
--3.enable archvive logging
sqlplus / as sysdba
alter database archivelog;
exit;
--4.stop database
srvctl stop database -d slprd
--5.restart all database instances
srvctl start database -d slprd
--6.verify archiving is enabled by means of:
archive log list;
alter system archive log current;
--这儿如果从归档切换回非归档,按照同样的步骤设置为NOARCHIVELOG即可
--查看闪回模式
select flashback_on from V$database;
--修改闪回区大小及路径
alter database flashback off;
alter system set db_recovery_file_dest_size=5G scope=both;
alter system set db_recovery_file_dest='/home/oracle/arch' scope=both;
--数据库归档模式管理
--登陆
rman target /
--列出所有的归档日志文件
list archivelog all;
-- 与物理归档日志文件保持同步,之前移走了一部分文件,因此执行此命令后会在/oracle目录下找不到的归档日志标记为expired
crosscheck archivelog all;
--列出所有expired(过期)的归档日志文件,此时你就可看到移走的归档日志文件均被标记为expired
list expired archivelog all;
--在oracle中删除所有过期的expired文件
delete noprompt expired archivelog all;
--再列出所有的归档日志文件,就可发现移走的日志文件被删掉了
list archivelog all;
--退出
exit;
--清除归档
方法1:
--进入归档路径,删除归档文件
--直接删除物理文件
--或:find /archivelog -xdev -mtime +3 -name "*.dbf" -exec rm -f {} \;
--再从控制文件中删除记录
rman target /
run {
delete noprompt obsolete;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
}
方法2:
--删除7天前的归档日志
delete archivelog all completed before 'sysdate-7';
--删除2小时前日志
delete archivelog all completed before 'sysdate-2/24';
--也可以直接用一个指定的日期来删除(删除2天前到现在的)
delete noprompt archivelog until time "sysdate -2";
--日常维护
--查询每日归档备份大小
SELECT TRUNC(FIRST_TIME) "日期",
TRUNC(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024, 2) "大小(GB/DAY)"
FROM V$ARCHIVED_LOG GROUP BY TRUNC(FIRST_TIME) ORDER BY 1 DESC;
--查看归档空间占用情况
col name for a20
select NAME,SPACE_LIMIT/1024/1024/1024,SPACE_USED/1024/1024/1024,NUMBER_OF_FILES from V$RECOVERY_FILE_DEST;
--强制日志切换
alter system switch logfile;
--查询历史日志
select * from v$log_history;
--查询日志的归档模式
archive log list;
select dbid,name,created,log_mode from v$database;
--查询归档日志的信息
set line 333
select recid,stamp,thread#,sequence#,name from v$archived_log;
--归档日志生成量_近一周
select logtime,
count(*),
round(sum(blocks * block_size) / 1024 / 1024 / 1024) gbsize
from (select trunc(first_time, 'dd') as logtime, a.BLOCKS, a.BLOCK_SIZE
from v$archived_log a
where a.DEST_ID = 1
and a.FIRST_TIME > trunc(sysdate - 7))
group by logtime
order by logtime desc;
--归档日志生成量_各个实例近一周
select THREAD#,
logtime,
count(*),
round(sum(blocks * block_size) / 1024 / 1024) mbsize
from (select THREAD#,
trunc(first_time, 'dd') as logtime,
a.BLOCKS,
a.BLOCK_SIZE
from v$archived_log a
where a.DEST_ID = 1
and a.FIRST_TIME > trunc(sysdate - 7))
group by THREAD#, logtime
order by THREAD#, logtime desc;
--归档日志生成量_每小时
SELECT
TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'999') "00",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'999') "01",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'999') "02",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'999') "03",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'999') "04",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'999') "05",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'999') "06",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'999') "07",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'999') "08",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'999') "09",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'999') "10",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'999') "11",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'999') "12",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'999') "13",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'999') "14",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'999') "15",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'999') "16",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'999') "17",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'999') "18",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'999') "19",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'999') "20",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'999') "21",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'999') "22",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'999') "23"
FROM
V$LOG_HISTORY
GROUP BY
TO_CHAR(FIRST_TIME,'YYYY-MM-DD')
ORDER BY 1 DESC;
srvctl stop database -d dev -o immediate
startup mount;
ORACLE instance started.
Total System Global Area 1.5032E+11 bytes
Fixed Size 37587256 bytes
Variable Size 2.3622E+10 bytes
Database Buffers 1.2616E+11 bytes
Redo Buffers 499281920 bytes
Database mounted.
alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
alter database flashback off;
alter database noarchivelog;
alter database open;
srvctl stop database -d dev -o immediate
srvctl start database -d dev