配置归档与闪回

1.概念描述

归档：待完善
闪回：待完善

2.实验环境：

系统：OLE6.10 双节点（71&72）
数据库：12CR2 RAC

3.实战

3.1.开启归档模式

• 关闭另一个实例 72

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

• 设置恢复目录及归档空间大小

SQL> show parameter recovery;

NAME				     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest		     string

db_recovery_file_dest_size	     big integer
0
recovery_parallelism		     integer
0
SQL> alter system set db_recovery_file_dest_size=2G;                          

System altered.

SQL> alter system set db_recovery_file_dest='+dgrecovery';

System altered.

SQL> show parameter recovery;

NAME				     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest		     string
+dgrecovery
db_recovery_file_dest_size	     big integer
2G
recovery_parallelism		     integer
0
SQL> 

• 设置数据库为非集群模式

SQL> alter system set cluster_database=false scope=spfile;

System altered.

• 关闭数据库启动到mount

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size		    2932336 bytes
Variable Size		  369099152 bytes
Database Buffers	  671088640 bytes
Redo Buffers		    5455872 bytes
Database mounted.

• 打开归档。

SQL> alter database archivelog;

Database altered.

• 设置数据库为集群模式

SQL> alter system set cluster_database=true scope=spfile;

System altered.

• 重启数据库并打开

SQL> shutdown immediate;

SQL> startup;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size		    2932336 bytes
Variable Size		  373293456 bytes
Database Buffers	  666894336 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.

• 启动另一个实例(72)

SQL> startup;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size		    2932336 bytes
Variable Size		  432013712 bytes
Database Buffers	  608174080 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.

• 测试是否生成归档日志

切换日志文件，grid用户查看日志文件是否归档

SQL>alter system switch logfile;

[grid@chdb71:/home/grid]$asmcmd
ASMCMD> pwd
+DGRECOVERY/CHDB/ARCHIVELOG/2022_07_12
ASMCMD> ls
thread_1_seq_38.257.1109863211
thread_1_seq_39.258.1109863449
thread_2_seq_10.256.1109863209

归档&闪回当目录：
ARCHIVELOG/
FLASHBACK/

• 如果需要开闪回，在这一步设置。

SQL> alter database flashback on;

Database altered.


SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------
YES

3.2.关闭归档模式

• 关闭另一个实例（关主节点71归档,先关72实例）

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

• 设置数据库为非集群模式

SQL> show parameter cluster;

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

SQL> alter system set cluster_database=false scope=spfile;

System altered.

关闭数据库启动到mount

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size		    2932336 bytes
Variable Size		  369099152 bytes
Database Buffers	  671088640 bytes
Redo Buffers		    5455872 bytes
Database mounted.
SQL> alter database flashback off;

Database altered.

关闭闪回，关闭归档

SQL> alter database flashback off;

Database altered.

SQL> alter database noarchivelog;

Database altered.

设置数据库为集群模式

SQL> alter system set cluster_database=true scope=spfile;

System altered.

重启数据库并打开

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size		    2932336 bytes
Variable Size		  373293456 bytes
Database Buffers	  666894336 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.

启动另一个实例(72)

SQL> startup;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size		    2932336 bytes
Variable Size		  432013712 bytes
Database Buffers	  608174080 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.

检查是否配置生效(72)

SQL> archive log list;
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     41
Current log sequence	       42

注意：生产环境归档必开；闪回根据情况开启，生产环境不建议开，有归档才有闪回；闪回是建立在归档之上的
空间归档如果是800G;设置归档大小为600G;当磁盘快满的时候可以加一点，然后慢慢调整。