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

冷不防 2023-03-04 15:27 52阅读 0赞

查看归档状态  
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