oracle omf

如果创建 online redolog, datafile, 如果指定名称, 不指定存放路径, 则默认存放ORACLE_HOME

利用 OMF



修改 datafile 默认存放路径
show parameter db_create_file
mkdir /u1/oracle/datafile; <- 目录必须存在, 权限正确
alter system set db_create_file_dest='/u1/oracle/datafile';



创建表空间测试
create tablespace ts3 datafile size 100M extent management local segment space management auto;

select name from v$datafile;
/u1/oracle/datafile/BTDB/datafile/o1_mf_ts3_7f8o7dln_.dbf


当执行 drop tablespace ts3; <- 自动删除数据文件

==================================




默认状态下 archive log 存放路径
db_recovery_file_dest -> 默认状态下 archive log 存放路径
-> /u1/oracle/flash_recovery_area


默认存档格式
log_archive_format -> %t_%s_%r.dbf


启用 archive log
shutdown immediate;
startup mount;
alter system archivelog;

startup open;



修改默认 archive_log 路径

a. 如果只需要设定单独路径可以设定 LOG_ARCHIVE_DEST
b. 如果需要多路径, 可以设定 LOG_ARCHIVE_DEST_n

当设定 LOG_ARCHIVE_DEST_n 后设定 LOG_ARCHIVE_DEST 则报错
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST



1. alter system set log_archive_dest_1='LOCATION=/u1/oracle/archive';
2. alter system switch logfile;

[oracle@server archive]$ ls -lh /u1/oracle/archive/
total 836K
-rw-r----- 1 oracle oinstall 741K Nov 29 10:45 1_14_762540711.dbf
-rw-r----- 1 oracle oinstall 84K Nov 29 10:48 1_15_762540711.dbf

spfile 参数 db_recovery_file_dest 仍然 /u1/oracle/flash_recovery_area
但已经改变了 archive log 存档路径

注: alter_system_set_log_archive_dest_n 具有两种设定路径

  a. location -> 本地存储
  b. service  -> 主从复制使用 example: 'service = standbyserver'

总结
默认状态优先顺序为
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST
DB_RECOVERY_FILE_DEST




==================================
默认日志位置
sys@BTDB> select member from v$logfile;

MEMBER
----------------------------------------------
/u1/oracle/oradata/btdb/redo03.log
/u1/oracle/oradata/btdb/redo02.log
/u1/oracle/oradata/btdb/redo01.log



修改在线日志
alter system set db_create_online_log_dest_1='/u1/oracle/online';

alter database add logfile size 100m;

select member from v$logfile;

MEMBER
----------------------------------------------
/u1/oracle/oradata/btdb/redo03.log
/u1/oracle/oradata/btdb/redo02.log
/u1/oracle/oradata/btdb/redo01.log
/u1/oracle/online/BTDB/onlinelog/o1_mf_4_7f8nsn9w_.log