一、达梦数据库开启与关闭归档操作
1、开启归档的方法
SQL 开启归档
SQL> alter database mount;
操作已执行
已用时间: 368.463(毫秒). 执行号:0.
SQL> alter database archivelog;
操作已执行
已用时间: 3.729(毫秒). 执行号:0.
SQL> select arch_mode from v$database;
行号 ARCH_MODE
---------- ---------
1 Y
已用时间: 0.666(毫秒). 执行号:712.
SQL> alter database add archivelog 'type=local,dest=/dm8/arch,file_size=64,space_limit=10240';
操作已执行
已用时间: 0.966(毫秒). 执行号:0.
SQL> alter database open;
操作已执行
已用时间: 324.410(毫秒). 执行号:0.
SQL> select * from v$dm_arch_ini;
2、关闭归档
SQL 关闭归档
SQL> alter database mount;
操作已执行
已用时间: 438.355(毫秒). 执行号:0.
SQL> alter database noarchivelog;
操作已执行
已用时间: 41.445(毫秒). 执行号:0.
SQL> alter database delete archivelog 'type=local,dest=/dm8/arch';
操作已执行
已用时间: 0.519(毫秒). 执行号:0.
SQL> alter database open;
操作已执行
已用时间: 322.559(毫秒). 执行号:0.
SQL> select arch_mode from v$database;
59 / 105
行号 ARCH_MODE
---------- ---------
1 N
已用时间: 0.654(毫秒). 执行号:714.
SQL> select * from v$dm_arch_ini;
二、Oracle RAC开启归档操作
1、节点1连接数据库,设置归档日志存放位置(存放归档日志的磁盘)
sqlplus / as sysdba
create pfile='/home/oracle/bak_pfile.ora' from spfile;
alter system set log_archive_dest_1='location=+ARCH' scope=spfile sid='*';
--(1)Oracle 19c RAC确认当前CDB 归档模式
SQL> archive log list; #当前为非归档模式
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/19.3.0.0/db_1/dbs/arch
Oldest online log sequence 11
Current log sequence 13
(2)将归档路径改到asm(如asm中没有相应路径可手动建)
SQL> alter system set log_archive_dest_1='location=+ARCH' scope=spfile sid='*';
#修改归档日志路径到asm2、首先在节点二 关闭数据库:
[oracle@gpnms3g2:/home/oracle]$sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 9 19:05:17 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.3、连接节点1数据库:
[root@gpnms3g1 ~]# su - oracle
上一次登录:一 8月 9 18:41:19 CST 2021pts/2 上
[oracle@gpnms3g1:/home/oracle]$sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 9 19:07:23 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> show parameter recovery; #查看恢复文件存放位置
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_recovery_file_dest stringdb_recovery_file_dest_size big integer
0
recovery_parallelism integer
0
SQL> set lin 2000;
SQL> alter system set db_recovery_file_dest_size=1500m;
System altered.
SQL> alter system set db_recovery_file_dest='+dgrecovery';
System altered.4、#查看当前数据库模式是否为集群模式
SQL> show parameter cluster;NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string#如果是集群模式,修改其参数使其值为false
SQL> alter system set cluster_database=false scope=spfile;
System altered.5、#修改完这参数之后需关闭节点1数据库,之后将其开启为挂载状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup mount;
ORACLE instance started.
Total System Global Area 3154116608 bytes
Fixed Size 2929352 bytes
Variable Size 771755320 bytes
Database Buffers 2365587456 bytes
Redo Buffers 13844480 bytes
Database mounted.6、#将处于挂载状态的数据库修改为归档模式
SQL> alter database archivelog;
Database altered.#打开闪回模式,如果未创建快速恢复目录/闪回区,执行下列语句会出现报错提示目录不存在
SQL> alter database flashback on;
Database altered.7、#修改完归档模式后一定要将数据库重新设置为集群模式,否则节点2无法启动,即参数cluster_database=true
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL> alter database open;8、#参数cluster_database设置完成后一定要重启节点1数据库令其生效,否则节点2启动实例到mount将报错ora-01102
关闭节点1数据库
SQL> shutdown immediate;
启动节点一数据库:
SQL> startup
ORACLE instance started.
Total System Global Area 3154116608 bytes
Fixed Size 2929352 bytes
Variable Size 771755320 bytes
Database Buffers 2365587456 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.9、启动节点二数据库:
SQL> startup
ORACLE instance started.
Total System Global Area 3154116608 bytes
Fixed Size 2929352 bytes
Variable Size 822086968 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL>10、验证是否开启归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 38
Next log sequence to archive 40
Current log sequence 4011、切换日志:
SQL> alter system switch logfile;
学习社区: 达梦在线服务平台(https://eco.dameng.com)