Oracle RAC开启归档日志&开启补充日志

Oracle RAC开启归档日志&开启补充日志

查看归档状态
su - oracle
sqlplus / as sysdba
archive log list;

查看节点实例状态
set line 120
col host_name for a30
select instance_name,host_name,status from gv$instance;

INSTANCE_NAME    HOST_NAME                      STATUS
---------------- ------------------------------ ------------
orcl1            rac1                           OPEN
orcl2            rac2                           OPEN

查看数据库集群参数
show parameter cluster;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string

修改之前先备份一下参数
create pfile='/tmp/orcl.ora.back' from spfile;

修改cluster_database参数
alter system set cluster_database=false scope=spfile sid='*';

停止所有节点的数据库，再从节点1启动到mount状态(grid用户操作)
exit
su - grid
[grid@rac1 ~]$ srvctl stop database -d orcl

[grid@rac1 ~]$ srvctl status database -d orcl
Instance orcl1 is not running on node rac1
Instance orcl2 is not running on node rac2

[grid@rac1 ~]$ srvctl start instance -d orcl -i orcl1 -o mount

[grid@rac1 ~]$ srvctl status database -d orcl
Instance orcl1 is running on node rac1
Instance orcl2 is not running on node rac2

查询节点1数据库实例状态(oracle用户操作)
exit
su - oracle
sqlplus / as sysdba
select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl1            MOUNTED

修改数据库成归档模式
alter database archivelog;

打开归档日志配置完毕后，再把将集群参数恢复回去
alter system set cluster_database=true scope=spfile sid='*';

关闭节点1数据库
shutdown immediate;

ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

切换到grid用户，再启动所有节点的数据库 
exit
su - grid
srvctl start database -d orcl
srvctl status database -d orcl

Instance orcl1 is running on node rac1
Instance orcl2 is running on node rac2

修改完毕后再查看一下归档日志的状态
exit
su - oracle
sqlplus / as sysdba
archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/dbs/arch
Oldest online log sequence     10
Next log sequence to archive   11
Current log sequence           11

select name,log_mode from v$database;

NAME      LOG_MODE
--------- ------------
ORCL      ARCHIVELOG

修改归档路径(grid用户在asm命令行操作)
exit
su - grid
asmcmd

ls
ARCH/
DATA/
OCR/

cd arch
mkdir orcl
mkdir archive_log
cd archive_log
pwd
+arch/orcl/archive_log

修改归档路径
exit
su - oracle
sqlplus / as sysdba
alter system set log_archive_dest_1='location=+ARCH/ORCL/archive_log' scope=spfile sid='*';

重启数据库
exit
su - grid
srvctl stop database -d orcl
srvctl start database -d orcl
srvctl status database -d orcl
Instance orcl1 is running on node rac1
Instance orcl2 is running on node rac2

查看归档日志修改后的路径
exit
su - oracle
sqlplus / as sysdba
archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ARCH/orcl/archive_log
Oldest online log sequence     11
Next log sequence to archive   12
Current log sequence           12
 

开启补充日志

查询补充日志的开启状态
select supplemental_log_data_min FROM v$database;
SUPPLEME
--------
NO

开启补充日志
alter database add supplemental log data;
SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
YES

重启数据库
exit
su - grid
srvctl stop database -d orcl
srvctl start database -d orcl
srvctl status database -d orcl