Oracle 11g DG 搭建

27 Oct 2020 • home

Oracle 11g DG 搭建

  1. 前言
  2. 手册整体配置流程说明
  3. 背景描述
  4. 部署准备
  5. 操作步骤

1.   前言

1.1手册内容

OracleDG迁移部署 SOP手册，本文介绍如何使用OracleDG 迁移数据库。

1.2手册的目的　    

本手册编写目的是为了后续在迁移上云过程做一个指导。

1.3手册包含范围        

本手册包含范围为本次项目中oracleDG部署文档。 适合的操作系统（本次项目迁移系统）：

  • centos. 7.6
  • Centos 7.4
  • Centos 7.5
  • Centos 7.3

2. 手册整体配置流程说明

大模块 小模块 备注
准备阶段 停库备份  
准备阶段 DG配置文件调整  
部署启动阶段 配置DG模式  
完成优化阶段    

3. 背景描述

本次SOP手册是在如下环境中完成，XX本地服务器信息与XX上信息如下：

源环境信息 服务器环境 操作系统 公网 备注
  x86服务器 centos7.3 15M  
目标端环境信息 服务器环境 操作系统 公网 备注
  华为云服务器 centos7.3 按量300M  

4. 部署准备

4.1. 前提准备条件

依据线下oralce服务器配置开通云上机器，并保证云上线下网络通过VPN打通

5. 操作步骤

5.1. Primary 数据库配置及相关操作

5.1.1. 确认primary库处于归档模式

SQL> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4

#若不是归档模式需要开启：

SQL>shutdown immediate 
startup mount
alter database archivelog;
archive log list;

5.1.2. 将primary库置为FORCE LOGGING 模式

SQL> alter database force logging;
SQL> select force_logging from v$database;
FOR
---
YES
Database altered.

5.1.3. 添加STANDBY日志文件

alter database add standby logfile group 4 ('/u01/app/oracle/oradata/cjkmid/redo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/cjkmid/redo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/cjkmid/redo06.log') size 50m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/cjkmid/redo07.log') size 50m;

查看创建添加的STANDBY日志文件

5.1.4. 创建primary库客户端初始化参数文件

1).创建主库中的pfile

SQL> create pfile from spfile;
File created.

2).备份到backup目录用于创建备库的pfile

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@rac1 dbs]$ cp ./initorcl.ora /home/oracle/backup/

3).修改后主库pfile中内容如下

orcl.__db_cache_size=1191182336
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=536870912
orcl.__sga_target=1610612736
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=369098752
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_tablespace='UNDOTBS1'
#################################################################
#Parameters for Primary Database.
#################################################################
*.DB_NAME='cjkmid'
*.DB_UNIQUE_NAME= cjkmid
*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
*.LOG_ARCHIVE_CONFIG=''
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= cjkmid'
*.LOG_ARCHIVE_DEST_2='SERVICE= cjkmiddg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= cjkmiddg'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.standby_file_management=auto
#################################################################
#Parameters which using for switch over from Primary to Standby.
#################################################################
*.FAL_SERVER= cjkmiddg
*.FAL_CLIENT= cjkmid
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cjkmid','/u01/app/oracle/oradata/cjkmid'
*.log_file_name_convert='/u01/app/oracle/oradata/cjkmid','/u01/app/oracle/oradata/cjkmid'
*.standby_archive_dest='/u01/app/oracle/archivelog'

4).通过pfile 重建spfile

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

SQL> create spfile from pfile='initcjkmid.ora';
File created.

5).修改监听配置文件

vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = cjkmid)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (GLOBAL_DBNAME = cjkmid)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.1.53)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

6).配置tnsnames.ora文件

[oracle@rac1 admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin

# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
CJKMID =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.1.53)(PORT = 1521))
    )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = cjkmid)
   )
 )

CJKMIDDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.66.53)(PORT = 1521))
   )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = cjkmiddg)
   )
 )

7).启动数据库，测试

tnsping cjkmid

开启数据库，启动失败

解决：参数配置多了空格

5.1.5. rman备份数据库,在闪回区中

$ rman tartget/
backup database plus archivelog;
backup current controlfile for standby;

执行这两句后会在对应的闪回区（默认在/u01/app/oracle/flash_recovery_area下）

SQL>alter system set db_recovery_file_dest_size=50G scope=both;

RMAN>backup database plus archivelog;
 
RMAN>backup current controlfile for standby;

5.2. Standby数据库配置及相关操作

5.2.1. 创建所需目录（注意OMF管理的文件）

mkdir -p /u01/app/oracle/admin/cjkmid/adump
mkdir -p /u01/app/oracle/admin/cjkmid/dpdump
mkdir -p /u01/app/oracle/diag/rdbms/cjkmid/cjkmiddg/trace
mkdir -p /u01/app/oracle/diag/rdbms/cjkmid/cjkmiddg/cdump
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/archivelog
mkdir -p /u01/app/oracle/oradata/cjkmid

5.2.2. 复制数据文件到standby库对应的目录

从主数据库服务器上拷贝文件

1).拷贝闪回区内容

scp -r /u01/app/oracle/fast_recovery_area/* root@172.20.66.53:/u01/app/oracle/fast_recovery_area/

2).拷贝参数文件

scp -r /u01/app/oracle/product/11.2.0/db_1/dbs/initcjkmid.ora root@172.20.66.53:/u01/app/oracle/product/11.2.0/db_1/dbs/initcjkmid.ora

3).拷贝密码文件

scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwcjkmid root@172.20.66.53:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwcjkmid

4).拷贝监听文件和tns文件

scp /u01/app/oracle/product/11.2.0/db_1/network/admin/*.ora root@172.20.66.53:/u01/app/oracle/product/11.2.0/db_1/network/admin/

5.2.3. 修改相应配置

1).修改监听配置文件

[oracle@stuaapp02 admin]$ vi listener.ora

2).修改TNS配置文件

此处保持不变

3).重启监听服务

lsnrctl stop lsnrctl start

4).standby的初始化参数如下

orcl.__db_cache_size=1191182336
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=536870912
orcl.__sga_target=1610612736
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=369098752
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=536870912
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_tablespace='UNDOTBS1'
#################################################################
#Parameters for Standby Database.
#################################################################
*.DB_NAME='cjkmid'
*.DB_UNIQUE_NAME=cjkmiddg
*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
*.LOG_ARCHIVE_CONFIG=' '
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cjkmid','/u01/app/oracle/oradata/cjkmid'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cjkmid','/u01/app/oracle/oradata/cjkmid'
*.STANDBY_ARCHIVE_DEST='/u01/app/oracle/archivelog'
*.FAL_SERVER=cjkmid
*.FAL_CLIENT=cjkmiddg
*.STANDBY_FILE_MANAGEMENT=AUTO
#################################################################
#Parameters which using for switch over from Standby to Primary.
#################################################################
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cjkmiddg'
*.LOG_ARCHIVE_DEST_2='SERVICE=cjkmid LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cjkmid'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

5).通过该pfile 创建spfile

SQL> create spfile from pfile= 'initcjkmid.ora';
File created.

5.2.4. 恢复数据库

启动备库到nomount startup nomount

[oracle@dg2 admin]$ rman target sys/Qyoracle@cjkmid auxiliary /
RMAN> duplicate target database for standby nofilenamecheck;
RMAN> exit

  • 磁盘空间不足，需要扩容

  • 扩容后重新执行恢复

  • 关闭数据库 shutdown immediate

5.2.5. 启动redo 应用

startup nomount;

alter database mount standby database;
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

6. 验证

  • 主库 
    SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog
Oldest online log sequence 73
Next log sequence to archive 75
Current log sequence 75
  • 备库 
    SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/archivelog
Oldest online log sequence 70
Next log sequence to archive 0
Current log sequence 75

7. 切换到只读模式

SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.

7.1.1. 切换到同步模式(不需要停库)

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
 oracle

Comments

Related Posts