[DG]oracle12cr2 RAC + DG(2+1)

最新推荐文章于 2023-12-27 14:47:16 发布

ciqiyuan9775

最新推荐文章于 2023-12-27 14:47:16 发布

阅读量157

点赞数 1

文章标签：数据库网络

1.安装ORACLE12C R2 数据库软件

...略

2.DG 主库配置

(1).打开主库归档模式及force_logging

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DGRECOVER
Oldest online log sequence     137
Next log sequence to archive   138
Current log sequence           138
SQL> select force_logging from v$database;

FORCE_LOGGING
------------------------------------------------------------------------------
NO

SQL> alter database force logging;

(2).创建standby redolog 组

- 要比主库日志组多一组。
- 要比主库日志文件大一点。

SQL>

alter system set standby_file_management=manual scope=both sid='*';
alter database add standby logfile thread 1 group 7 size 52M;
alter database add standby logfile thread 1 group 8 size 52M;
alter database add standby logfile thread 1 group 9 size 52M;
alter database add standby logfile thread 1 group 10 size 52M;
alter database add standby logfile thread 2 group 11 size 52M;
alter database add standby logfile thread 2 group 12 size 52M;
alter database add standby logfile thread 2 group 13 size 52M;
alter database add standby logfile thread 2 group 14 size 52M;
alter system set standby_file_management=auto scope=both sid='*';

(3).配置主库参数文件，主库RAC原有参数不变，添加下列参数:

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb12c,racdb12cdg)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DGRECOVER VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb12c' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=racdb12cdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb12cdg' scope=both sid='*';
alter system set fal_client='racdb12c' scope=spfile sid='*';
alter system set fal_server='racdb12cdg' scope=spfile sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set db_file_name_convert='/oradata/racdb12cdg/datafilesys/','+DGSYSTEM/racdb12c/','/oradata/racdb12cdg/datafileuser/','+DGDATA1/racdb12c/','/oradata/racdb12cdg/datafileuser/','+DGDATA2/racdb12c/' scope=spfile sid='*';
alter system set log_file_name_convert='/redofile/orilogfile/','+DGSYSTEM/RACDB12C/ONLINELOG/','/redofile/mirrlogfile/','+DGDATA1/RACDB12C/ONLINELOG/','/redofile/orilogfile/','+DGDATA2/RACDB12C/ONLINELOG/' scope=spfile sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';

alter system reset db_create_file_dest sid='*';

参数说明:
http://czmmiao.iteye.com/blog/1311070

参数名	生效场景	说明
DB_UNIQUE_NAME	主备	如果主备db_unique_name不一样，需要与LOG_ARCHIVE_CONFIG配合使用
LOG_ARCHIVE_CONFIG	主备	列出主备库上的DB_UNIQUE_NAME 参数。默认情况下，定义该参数能确保主备库数据库能够互相识别对方
LOG_ARCHIVE_DEST_1	主备	LOG_ARCHIVE_DEST_1='LOCATION=/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Primary'
LOG_ARCHIVE_DEST_2	主	指定primary归档redo log到该参数定义的standby database上 LOG_ARCHIVE_DEST_2='SERVICE=DR1 lgwr async VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) ' DR1为tnsnames.ora文件里定义的Oracle Net名称
FAL_SERVER FAL_CLIENT	备	Data Guard能够自动检测，解决归档裂缝，不需要DBA的介入。这需要配置FAL_CLIENT， FAL_SERVER 这两个参数 FAL_CLIENT和FAL_SERVER两个参数都是Oracle Net Name
DB_FILE_NAME_CONVERT	备	primary与standby上diskgroup的名称或是数据文件的存放路径不一致的时候，需要定义该参数进行转换，否则standby apply后无法创建与primary一致的数据文件并报错。 db_file_name_convert=主数据库数据文件目录，备用数据库数据文件目录
LOG_FILE_NAME_CONVERT	备	同DB_FILE_NAME_CONVERT类似，定义主备log文件的存放路径转换。
STANDBY_FILE_MANAGEMENT	备	用来控制是否自动将Primary数据库增加表空间或数据文件的改动，传播到物理Standby数据库

(4).主库添加DG TNS

racdb12cdg =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racdg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb12cdg)
    )
)

(5).主库所有节点配置静态监听

su – grid
添加
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = racdb12c)
      (ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/db_1)
     (SID_NAME = racdb12c1)
    )
)

(6).主库进行RMAN备份

RMAN> backup database format '/backup/racdb12cfull_%u_%s_%p';

(7).创建pfile

SQL> create pfile='/backup/standby.pfile' from spfile;

主库配置完毕!

3.DG 备库配置

(1).拷贝RMAN备份和pfile文件

(2).拷贝密码文件

12C开始，密码文件存放在共享存储。
查询crs中相关db配置
$srvctl config database -d racdb12c
Database unique name: racdb12c
Database name: racdb12c
Oracle home: /oracle/app/oracle/product/12.2.0.1/db_1
Oracle user: oracle
Spfile: +DGSYSTEM/RACDB12C/PARAMETERFILE/spfile.279.960749253
Password file: +DGSYSTEM/RACDB12C/PASSWORD/pwdracdb12c.267.960742987
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DGSYSTEM,DGDATA1,DGDATA2
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: racdb12c1,racdb12c2
Configured nodes: rac1,rac2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

将密码文件拷贝到本地
create or replace directory source_dir as '+DGSYSTEM/RACDB12C/PASSWORD';
create or replace directory dest_dir as '/oracle/app/oracle/product/12.2.0.1/db_1/dbs';
begin
     dbms_file_transfer.copy_file(
     source_directory_object => 'source_dir',
     source_file_name => 'pwdracdb12c.267.960742987',
     destination_directory_object => 'dest_dir',
     destination_file_name => 'pwdracdb12c'
     );
end;
/

(3).创建日志存放目录

mkdir -p /oracle/app/oracle/admin/racdb12cdg/adump
mkdir -p /oracle/app/oracle/diag/rdbms/racdb12cdg

(4).修改备库参数文件

*.audit_file_dest='/oracle/app/oracle/admin/racdb12cdg/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/oradata/control_file/control01.ctl'
*.db_block_size=8192
*.db_name='racdb12c'
*.db_unique_name='racdb12cdg'
*.diagnostic_dest='/oracle/app/oracle'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=200m
*.processes=2000
*.remote_login_passwordfile='exclusive'
*.sga_target=800m

(5).配置备库静态监听

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = racdb12cdg)
      (ORACLE_HOME = /oracle/app/oracle/product/12.2.0.1/db_1)
      (SID_NAME = racdb12cdg)
)
)

(6).设置环境变量

export TMP=/tmp
export LANG=en_US
export TMPDIR=$TMP
export ORACLE_HOSTNAME=racdg
export ORACLE_UNQNAME=racdb12cdg
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1
export ORACLE_SID=racdb12cdg
export ORACLE_TERM=xterm
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin; export PATH
export THREADS_FLAG=native

(7).修改备库参数

- 创建spfile
SQL> startup nomount pfile='/backup/standby.pfile'
SQL> create spfile from pfile='/backup/standby.pfile';

SQL> startup nomount;

- 修改参数

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(racdb12cdg,racdb12c)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb12cdg' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=racdb12c LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb12c' scope=both sid='*';
alter system set fal_client='racdb12cdg' scope=spfile sid='*';
alter system set fal_server='racdb12c' scope=spfile sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set db_file_name_convert='+DGSYSTEM/racdb12c/','/oradata/racdb12cdg/datafilesys/','+DGDATA1/racdb12c/','/oradata/racdb12cdg/datafileuser/','+DGDATA2/racdb12c/','/oradata/racdb12cdg/datafileuser/' scope=spfile sid='*';
alter system set log_file_name_convert='+DGSYSTEM/RACDB12C/ONLINELOG/','/redofile/orilogfile/','+DGDATA1/RACDB12C/ONLINELOG/','/redofile/mirrlogfile/','+DGDATA2/RACDB12C/ONLINELOG/','/redofile/mirrlogfile/' scope=spfile sid='*';

- 重启只nomount状态

- 物理备库创建

$rman target sys/sysoracle@racdb12c auxiliary sys/sysoracle@racdb12cdg

RMAN> duplicate target database for standby;

重建temp表空间

select * from v$tempfile;

alter tablespace temp add tempfile '/oradata/racdb12cdg/datafilesys/tempfile/temp.274.960743057' size 100m;

(8).启动备库mrp同步进程

SQL> select OPEN_MODE,PROTECTION_MODE,ACTIVATION#,DATABASE_ROLE,SWITCHOVER#,SWITCHOVER_STATUS FROM V$DATABASE;
OPEN_MODE PROTECTION_MODE ACTIVATION# DATABASE_ROLE SWITCHOVER# SWITCHOVER_STATUS
---------- -------------------- ----------- -------------------- ----------- --------------------
MOUNTED MAXIMUM PERFORMANCE 0 PHYSICAL STANDBY 0 RECOVERY NEEDED

上述结果说明目前主备库不同步

启动mrp进程
alter database recover managed standby database disconnect from session;
停止mrp进程
alter database recover managed standby database cancel;

分别在主库1,2节点进行归档日志切换，然后观察备库日志。

(9).启动备库ADG模式（只读，并同时应用日志）

停止mrp进程
alter database recover managed standby database cancel;
打开数据库
alter database open;
启动mrp进程
alter database recover managed standby database disconnect from session;

SQL> select OPEN_MODE,PROTECTION_MODE,ACTIVATION#,DATABASE_ROLE,SWITCHOVER#,SWITCHOVER_STATUS FROM V$DATABASE;
OPEN_MODE PROTECTION_MODE ACTIVATION# DATABASE_ROLE SWITCHOVER# SWITCHOVER_STATUS
---------- -------------------- ----------- -------------------- ----------- --------------------
READ ONLY MAXIMUM PERFORMANCE 3850218903 PHYSICAL STANDBY 3850218903 WITH APPLY NOT ALLOWED

4.RAC+DG 主备切换switchover

切换前确认事项:
- 确认主库和备库间网络连接通畅
- 确认没有活动的会话连接在数据库中
- primary数据库处于打开的状态，standby数据库处于mount或ADG状态
- 确保Standby 数据库处于archivelog模式
- 如果设置了redo应用延迟，那么将这个设置去掉
- 确保配置了主库和从库的初始化参数，使得切换完成后，DATAGUARD机制可以顺利的运行。

命令:
主库: select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
-------------------------------- ----------------------------------------
PRIMARY TO STANDBY
SESSION ACTIVE 代表仍有活动会话

备库: SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
-------------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

NOT ALLOWED,和 TO PRIMARY 状态表示可以切换

关闭RAC其他节点，仅保留一个节点
确保备库为最大性能模式
备库:select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- ----------------------------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

检查备库归档日志是否全部应用
SQL> select * from v$archived_log where applied='NO';

no rows selected

切换（主->备）
主库: alter database commit to switchover to physical standby with session shutdown;

切换后
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
-------------------------------- ----------------------------------------
PHYSICAL STANDBY RECOVERY NEEDED

切换(备->主)
备库:
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
-------------------------------- ----------------------------------------
PHYSICAL STANDBY TO PRIMARY

执行切换

alter database commit to switchover to primary;

确认新主库状态:
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
-------------------------------- ----------------------------------------
PRIMARY NOT ALLOWED

回切
查看主库(single)状态
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
-------------------------------- ----------------------------------------
PRIMARY TO STANDBY
查看备库(RAC)状态
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
-------------------------------- ----------------------------------------
PHYSICAL STANDBY NOT ALLOWED

关闭备库(RAC)其他节点
查看备库(RAC)所有日志是否应用
select * from v$archived_log where applied='NO';

切换(主库(single) –> 备库)
alter database commit to switchover to physical standby;
查看新备库状态(single)
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
-------------------------------- ----------------------------------------
PHYSICAL STANDBY RECOVERY NEEDED

切换(备库(rac)->主库)
alter database commit to switchover to primary;
查看新主库状态(RAC)
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
-------------------------------- ----------------------------------------
PRIMARY NOT ALLOWED

启动mrp进程
alter database recover managed standby database disconnect from session;
启动其他节点

5.RAC + DG主备切换 failover
Failover切换一般是PRIMARY 数据库发生故障后的切换，这种情况是STANDBY数据库发挥其作用的情况。这种情况下切换对redo的处理，就显得很重要。如果处理好，就不会有数据丢失，否则就会有数据丢失。而且这个过程是不可逆的，DG环境会被破坏。

由于PRIMARY数据库无法启动，所以FAILOVER切换所需条件并不多，只要检查STANDBY是否运行在最大保护模式下，如果是的话，需要将其置为最大性能模式，否则切换到PRIMARY角色也无法启动

但如果主库在发生故障前，开启了闪回功能，那在将备库切换为主库后，还可以将原来的主库闪回到故障前，再重新改为备库，这样，又可以组成一套完整的DG。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15412087/viewspace-2149251/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15412087/viewspace-2149251/