Oracle 11g rac开启归档
查看目前归档状态
#节点1 ykws1
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3098
Current log sequence 3099
#节点2 ykws2
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 439
Current log sequence 440
以下命令如无说明均在一个节点上执行,以节点一为例。
查看集群下的所有实例状态:
SQL> select instance_name,host_name,status from gv$instance;
INSTANCE_NAME HOST_NAME STATUS
------------------------ ------------------------------------------- ------------
ykws1 ykwsrac1 OPEN
ykws2 ykwsrac2 OPEN
查看集群配置:
SQL> show parameter cluster;
NAME TYPE VALUE
------------------------------------ -------------- ------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
查看数据库名称
SQL> select name from v$database;
NAME
---------
YKWS
备份spfile文件
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/oraykws1_bak.ora' from spfile;
File created.
设置节点一脱离集群,重启后生效
SQL> alter system set cluster_database=false scope=spfile sid='*';
System altered.
切换到grid用户下,停止数据库而后将数据库启动至mount状态来启动归档。
[root@ykwsrac1 ~]# su - grid
[grid@ykwsrac1 ~]$ srvctl stop database -d YKWS
[grid@ykwsrac1 ~]$ srvctl start instance -d YKWS -i ykws1 -o mount
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ykws1 MOUNTED
启动归档
SQL> alter database archivelog;
Database altered.
将节点一加入集群,重启后生效
SQL> alter system set cluster_database=true scope=spfile sid='*';
System altered.
在grid用户下重启集群数据库
[grid@ykwsrac1 ~]$ srvctl stop database -d YKWS
[grid@ykwsrac1 ~]$ srvctl start database -d YKWS
查询归档状态
#节点1 ykws1
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3098
Next log sequence to archive 3099
Current log sequence 3099
#节点2 ykws2
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 440
Next log sequence to archive 441
Current log sequence 441
到此为止,数据库归档已经启动了。此时的归档日志还是存放在默认路径下接下来修改归档日志存放路径。
将归档路径修改为ASM磁盘下,先在grid用户下查看ASM磁盘状态、路径
[grid@ykwsrac1 ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 3276800 3273554 0 3273554 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 819200 818856 0 818856 0 N FRA/
MOUNTED HIGH N 512 4096 1048576 153600 152344 61440 30301 0 Y OCR/
将归档日志路径修改为+FRA/磁盘组下,设置为立即并永久生效。
SQL> alter system set log_archive_dest_1='location=+FRA/' scope=both;
System altered.
查询修改后结果
#节点1 ykws1
SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 3098
Next log sequence to archive 3099
Current log sequence 3099
#节点2 ykws2
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 440
Next log sequence to archive 441
Current log sequence 441
注:修改归档文件名格式--alter system set log_archive_format='arc_%S_%T_%R.log' scope=spfile;
(log_archive_format为静态参数,必须scope=spfile,重启数据库后生效.)