一、查看存档模式
1.在system用户下:
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL NOARCHIVELOG
2.在dba用户下可以采用上面的方式,也可以采用如下方式:
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 32
Current log sequence 33
二、修改存档方式(在dba用户下)
1.打开数据库至mount状态
1)关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
2)打开至mount状态
SQL> select instance_name,status from V$instance;
3)查看数据库状态
SQL> select instance_name,status from V$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl MOUNTED
4)修改为存档模式
SQL> alter database archivelog;
Database altered.
5)查询是否修改成功(用两种方法查看)
方法一:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 33
Next log sequence to archive 34
Current log sequence 34
方法二:
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG
三、修改存贮位置
1.打开数据库至open
SQL> alter database open;
2.查看存贮存档日志的位置参数(下面只罗列了部分参数)
SQL> show parameter archive;
NAME TYPE VALUE
------------------------------------ ----------- --------
log_archive_dest_1 string
log_archive_dest_2 string
log_archive_dest_3 string
3.建立存贮路径,我建了三个目录分别备份三份,分别是:
目录1:/u03/ora11g/archive
目录2:/u04/ora11g/archive
目录3:/u05/ora11g/archive
读者需要自己建立。
4.修改参数
目录1:
SQL> ALTER system set log_archive_dest_1='location=/u03/ora11g/archive';
System altered.
目录2(mandatory表示强制的):
SQL> ALTER system set log_archive_dest_2='location=/u04/ora11g/archive mandatory';
System altered.
目录3(optional表示非强制的,可选择的):
SQL> ALTER system set log_archive_dest_3='location=/u05/ora11g/archive optional';
System altered.
5.查询结果下面只罗列了部分参数)
1)查询参数
SQL> show parameter archive;
NAME TYPE VALUE
--------------------------- ----------- ------------------------------
log_archive_dest_1 string location=/u03/ora11g/archive
log_archive_dest_2 string location=/u04/ora11g/archive m
log_archive_dest_3 string location=/u05/ora11g/archive o
2)查询物理位置
SQL> host ls -l /u03/ora11g/archive
total 0
我们发现物理位置并没有文件,下面我是参数生效:
3)切换日志
SQL> alter system switch logfile;
System altered.
5)修改检查点
SQL> alter system checkpoint;
System altered.
4)再次查询
SQL> host ls -l /u03/ora11g/archive
total 7268
-rw-r----- 1 oracle oinstall 7427584 Jun 2 13:03 1_34_877370858.dbf
SQL> host ls -l /u04/ora11g/archive
total 7268
-rw-r----- 1 oracle oinstall 7427584 Jun 2 13:03 1_34_877370858.dbf
SQL> host ls -l /u05/ora11g/archive
total 7268
-rw-r----- 1 oracle oinstall 7427584 Jun 2 13:03 1_34_877370858.dbf
此时,我们发现已经有了文件。
四、修改相关参数
1.修改存档日志最大进程数
SQL> alter system set log_archive_max_processes=3;
System altered.
2.修改存档日志最小成功数
SQL> alter system set log_archive_min_succeed_dest=2;
System altered.
3.查询结果
SQL> show parameter log_archive_m;
NAME TYPE VALUE
------------------------------------ ----------- ------
log_archive_max_processes integer 3
log_archive_min_succeed_dest integer 2