遇到的问题
卡在最后一次切归档已经10分钟了,紧接着数据库和系统就崩了!!!!
天呐,PROD和OS驾崩了!
****我个人强烈不建议像我这样操作,不要把归档放到闪回里面!!!!!!!!!!!!!!!!!
*******单独给闪回建个目录
Archive destination(存档终点)有两种情形
①缺省是USE_DB_RECOVERY_FILE_DEST,其含义是采用参数db_recovery_file_dest参数的定义,即闪回恢复区
②采用参数log_archive_dest_n或log_archive_dest指定的路径
SYS@ prod>select * from test.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
已选择14行。
SYS@ prod>update test.emp set sal=1000 where sal<1000;
已更新2行。
SYS@ prod>commit;
提交完成。
SYS@ prod>select checkpoint_change#,name from v$datafile_header;
CHECKPOINT_CHANGE# NAME
------------------ ------------------------------
2030857 /u01/oradata/prod/system01.dbf
2030857 /u01/oradata/prod/sysaux01.dbf
2030857 /u01/oradata/prod/undotbs01.db
f
2030857 /u01/oradata/prod/users01.dbf
2030857 /u01/oradata/prod/example01.db
f
2030857 /u01/oradata/prod/tbsttt.dbf
2030857 /u01/oradata/prod/test01.dbf
2030857 /u01/oradata/prod/fda01.dbf
2031430 /u01/oradata/prod/arch.tbs
已选择9行。
SYS@ prod>alter system switch logfile;
系统已更改。
SYS@ prod>select checkpoint_change#,name from v$datafile_header;
CHECKPOINT_CHANGE# NAME
------------------ ------------------------------
2030857 /u01/oradata/prod/system01.dbf
2030857 /u01/oradata/prod/sysaux01.dbf
2030857 /u01/oradata/prod/undotbs01.db
f
2030857 /u01/oradata/prod/users01.dbf
2030857 /u01/oradata/prod/example01.db
f
2030857 /u01/oradata/prod/tbsttt.dbf
2030857 /u01/oradata/prod/test01.dbf
2030857 /u01/oradata/prod/fda01.dbf
2031430 /u01/oradata/prod/arch.tbs
已选择9行。
SYS@ prod>
SYS@ prod>
SYS@ prod>
SYS@ prod>commit;
提交完成。
SYS@ prod>select checkpoint_change#,name from v$datafile_header;
CHECKPOINT_CHANGE# NAME
------------------ ------------------------------
2030857 /u01/oradata/prod/system01.dbf
2030857 /u01/oradata/prod/sysaux01.dbf
2030857 /u01/oradata/prod/undotbs01.db
f
2030857 /u01/oradata/prod/users01.dbf
2030857 /u01/oradata/prod/example01.db
f
2030857 /u01/oradata/prod/tbsttt.dbf
2030857 /u01/oradata/prod/test01.dbf
2030857 /u01/oradata/prod/fda01.dbf
2031430 /u01/oradata/prod/arch.tbs
已选择9行。
SYS@ prod>alter system switch logfile;
系统已更改。
SYS@ prod>alter system switch logfile;
赶紧看alert信息啊
他喵的!!!又是归档,两次了哦,上次开库就没起来,这次用了没两下又崩了!
处理方法可查看如下连接
https://blog.csdn.net/qq_33505270/article/details/115076822
我也不是个过日子的人啊!家大业大的 就给2M????
改!!!!给300M 不过已经足够了(事实证明我确实挺抠门)
SYS@ prod>show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string /u01/oradata/prod/flash_recove
ry_area
db_recovery_file_dest_size big integer 2M
SYS@ prod>
SYS@ prod>
SYS@ prod>alter system set db_recovery_file_dest_size=300M;
系统已更改。
SYS@ prod>show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string /u01/oradata/prod/flash_recove
ry_area
db_recovery_file_dest_size big integer 300M
SYS@ prod>
SYS@ prod>
SYS@ prod>
前提:
这是我自己的单实例实验库,之前做过一次参数调整(scope=spfile both)这些操作 我就把归档空间大小给改了!后来忘了改回来。造孽啊!
建议:
针对本例子的问题:开归档时候要给定期清理计划和足够的空间大小。
单独给归档设置路径:
SYS@ prod> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string /u01/oradata/prod/flash_recove
ry_area
db_recovery_file_dest_size big integer 300M
SYS@ prod>show parameter archive
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
archive_lag_target integer 0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 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
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
standby_archive_dest string ?/dbs/arch
修改归档存放位置
SYS@ prod> alter system set log_archive_dest_1='location=/u01/oradata/prod/archive/';
系统已更改。
SYS@ prod>show parameter archive
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
archive_lag_target integer 0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string location=/u01/oradata/prod/arc
hive/
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 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
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
standby_archive_dest string ?/dbs/arch
修改归档存储文件的格式
SYS@ prod>alter system set log_archive_format ='arch_%t_%r_%s.log' scope=spfile;
系统已更改。
修改存储归档文件格式重启后生效:
SYS@ prod>show parameter archive_format
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_format string arch_%t_%r_%s.log
SYS@ prod>
alter system set log_archive_dest_2='service=standby';
远程备份,把历史日志备份到网络连接符为standby的数据库上。(service 代表远程),配置DG时有用。
log_archive_format 是定义命名格式的,一般考虑使用下面三个内置符号(模板)
%t thread# 日志线程号
%s sequence 日志序列号
%r resetlog 代表数据库的周期
群里某位大佬给出的建议
Jrojyun
2021-03-29