[主从搭建二]CentOS oracle12c ADG搭建

alonso
930 阅读4分钟

[主从搭建二]CentOS oracle12c ADG搭建

  1. 目录

    • 主机、备机hosts配置
    • 主机参数配置
    • 主机网络配置
    • 主机修改pfile文件
    • 主机创建相关目录
    • 主机上拷贝文件到备机
    • 备机网络配置
    • 备机上修改从主库发送过来的pfile
    • 备机创建相关目录
    • 备机启动oracle到nomount状态
    • 使用rman连接主机与备机
    • 备机数据库开启
    • 测试数据同步

  2. 主机、备机hosts配置

    主机、备机 配置一样

    [root@localhost ~] cat /etc/hosts

# 添加内容： 
172.19.0.140  NODEDB1    NODEDB1.COM
172.19.0.190  NODEDB2    NODEDB2.COM

  3. 主机参数配置

    日志配置

    # sqlplus 登录
sqlplus / as sysdba
# 先关闭数据库
SQL> shutdown immediate;
# 启动到nomount状态
SQL> startup nomount;
# 在启动到mount状态
SQL> alter database mount;
 
# 配置为强日志记录
# 修改数据库为强制记日志，这是必须的操作，主库的每一步操作都得记录到日志中去。
SQL> alter database force logging;    
 
# 修改为归档模式
# 修改数据库为归档模式，因为dg是通过传送归档日志到备库然后应用来保证主备库一致的
SQL> alter database archivelog;       
 
# 创建redo 日志文件
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/orcl/app/oracle/oradata/orcl/redo04.log') size 50M; 
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/orcl/app/oracle/oradata/orcl/redo05.log') size 50M; 
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/orcl/app/oracle/oradata/orcl/redo06.log') size 50M; 
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/orcl/app/oracle/oradata/orcl/redo07.log') size 50M; 	
 
#查询日志文件
SQL> select group#,type,member from v$logfile;

    创建pfile

    这里创建pfile是为了做一些主库参数的配置；
    后续步骤还得拷贝到备库再次修改成备库的配置；
    通过pfile才能在主备库之间建立联系；
    主库和备库中的pfile相似但有区别。 

    # 创建 pfile
create pfile from spfile;

    关闭数据库

    # 再次关闭数据库并退出
SQL> shutdown immediate;
SQL> exit

    创建归档目录

    文件路径可自定义，但需要和后续步骤的配置保持一致

    cd /orcl/app/oracle/oradata/orcl
mkdir archivelog

  4. 主机网络配置

    修改 listener.ora

    监听配置

    # 查看 /orcl/app/oracle/product/12.2.0/db_1/network/admin/listener.ora 配置
[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ cat listener.ora

# 替换成如下内容：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /orcl/app/oracle/product/12.2.0/db_1)
      (PROGRAM = extproc)
    )

 (SID_DESC =
      (SID_NAME = orcl)
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /orcl/app/oracle/product/12.2.0/db_1)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = NODEDB1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /orcl/app/oracle

    修改 tnsname.ora

    tnsname 连接主库和备库的连接串管理

    # 查看 /orcl/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora 配置
[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ cat tnsnames.ora

# 替换成如下内容：
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = NODEDB1)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = NODEDB1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = NODEDB1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (UR=A)
    )
  )
                        
ORCL_S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = NODEDB2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (UR=A)
    )
  )

    重启监听

    # 监听停止
lsnrctl stop  
# 监听启动
lsnrctl start   
# 或 监听重载
lsnrctl reload 

# 可查看监听状态
lsnrctl status

    测试连接

    # 测试主库连接
tnsping ORCL_P
# 测试从库连接
tnsping ORCL_S

  5. 修改pfile文件

    修改 initorcl.ora

    目录：/orcl/app/oracle/product/12.2.0/db_1/dbs
    '#' 为备注，不能出现在内容中，实测将备注放内容中后续生成spfile报错

    [oracle@localhost dbs]$ cat initorcl.ora 
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=12314476544
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=100663296
orcl.__large_pool_size=939524096
orcl.__oracle_base='/orcl/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=5066719232
orcl.__sga_target=15166603264
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=1778384896
orcl.__streams_pool_size=0
*.audit_file_dest='/orcl/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/orcl/app/oracle/oradata/orcl/control01.ctl','/orcl/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.diagnostic_dest='/orcl/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCL'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=4812m
*.processes=2560
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=14436m
*.undo_tablespace='UNDOTBS1'

# 下面DB_UNIQUE_NAME 配置上上面tns文件中配置了主库链接（因为我是主库）
DB_UNIQUE_NAME=ORCL_P
# 下面配置上主库和备库链接
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL_P,ORCL_S)'
#下面设置好归档目录（就是上面自定义创建的归档目录，归档日志将存放在里面）
LOG_ARCHIVE_DEST_1='LOCATION=/orcl/app/oracle/oradata/orcl/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_P'
LOG_ARCHIVE_DEST_2='SERVICE=ORCL_S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_S'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc  
# 配置备库tns链接
FAL_SERVER=ORCL_S  
STANDBY_FILE_MANAGEMENT=AUTO

    再次关闭数据库，并通过pfile启动到nomount状态,并且创建spfile

    [oracle@localhost dbs]$ sqlplus / as sysdba
SQL> shutdown immediate
# 如下命令执行前 保证 initorcl.ora 文件内容格式，清除上面的注释内容
SQL> startup nomount pfile='/orcl/app/oracle/product/12.2.0/db_1/dbs/initorcl.ora';
SQL> create spfile from pfile; 
SQL> alter database mount;
SQL> alter database open;
SQL> exit

  6. 主机创建相关目录

    # 主库
mkdir /orcl/app/oracle/oradata/orcl/backup

  7. 主机上拷贝文件到备机

    [oracle@localhost ~]# cd $ORACLE_HOME/dbs
# 拷贝到备库
[oracle@localhost dbs]$ scp ./initorcl.ora oracle@NODEDB2:$ORACLE_HOME/dbs
[oracle@localhost dbs]$ scp ./orapworcl oracle@NODEDB2:$ORACLE_HOME/dbs

  8. 备机网络配置

    修改 listener.ora

    监听配置 （注意和主机配置的区别）

    # 查看 /orcl/app/oracle/product/12.2.0/db_1/network/admin/listener.ora 配置
[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ cat listener.ora

# 替换成如下内容：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /orcl/app/oracle/product/12.2.0/db_1)
      (PROGRAM = extproc)
    )

 (SID_DESC =
      (SID_NAME = orcl)
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /orcl/app/oracle/product/12.2.0/db_1)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = NODEDB2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /orcl/app/oracle

    修改 tnsname.ora

    tnsname 连接主库和备库的连接串管理（注意和主机配置的区别）

    # 查看 /orcl/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora 配置
[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ cat tnsnames.ora

# 替换成如下内容：
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = NODEDB2)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = NODEDB2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = NODEDB1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (UR=A)
    )
  )

ORCL_S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = NODEDB2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (UR=A)
    )
  )

    重启监听

    # 监听停止
lsnrctl stop  
# 监听启动
lsnrctl start   
# 或 监听重载
lsnrctl reload 

# 可查看监听状态
lsnrctl status

  9. 备机上修改从主库发送过来的pfile

    修改 initorcl.ora

    目录：/orcl/app/oracle/product/12.2.0/db_1/dbs
    '#' 为备注，不能出现在内容中，实测将备注放内容中后续生成spfile报错

    cd /orcl/app/oracle/product/12.2.0/db_1/dbs
[oracle@oracle-1 dbs]$ cat initorcl.ora 
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=12314476544
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=100663296
orcl.__large_pool_size=939524096
orcl.__oracle_base='/orcl/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=5066719232
orcl.__sga_target=15166603264
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=1778384896
orcl.__streams_pool_size=0
*.audit_file_dest='/orcl/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/orcl/app/oracle/oradata/orcl/control01.ctl','/orcl/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.diagnostic_dest='/orcl/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCL'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=4812m
*.processes=2560
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=14436m
*.undo_tablespace='UNDOTBS1'
# 下面两个参数要在备库上添加上
*.log_file_name_convert='/orcl/app/oracle/oradata/orcl','/orcl/app/oracle/oradata/orcl' 
*.db_file_name_convert='/orcl/app/oracle/oradata/orcl','/orcl/app/oracle/oradata/orcl'
 
# 下面这个设置为tns 中的 ORCL_S 因为我是备库
DB_UNIQUE_NAME=ORCL_S
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL_P,ORCL_S)'
# 备库上同样配置自定义的归档目录
LOG_ARCHIVE_DEST_1='LOCATION=/orcl/app/oracle/oradata/orcl/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_S'
LOG_ARCHIVE_DEST_2='SERVICE=ORCL_S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_P'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#指向主库
FAL_SERVER=ORCL_P
STANDBY_FILE_MANAGEMENT=AUTO

  10. 备机创建相关目录

    mkdir /orcl/app/oracle/oradata/orcl/archivelog
mkdir /orcl/app/oracle/oradata/orcl/backup

  11. 备机启动oracle到nomount状态

    以pfile启用到nomount，不要打开数据库，为下一步准备

    sqlp / as sysdba
SQL> shutdown immediate;
SQL> startup nomount pfile="/orcl/app/oracle/product/12.2.0/db_1/dbs/initorcl.ora";
SQL> exit

  12. 使用rman连接主机与备机

    特别注意：
    1.rman连接动作在备库上做
    2.系统要求主库处于nomount，但是这个时候rman又连不上主库。开启主库rman连上之后又报错主库必须要nomount，很尴尬 ，解决办法：在tns配置文件中链接信息下面添加(UR=A) 使用静态链接，解决问题（前面步骤tns配置已经加入）。
    3.备库也必须使用pfile 启动到nomount状态（备库不能打开）

    #使用rman链接主库和备库
[oracle@localhost dbs]$ rman target sys/xxx@ORCL_P auxiliary sys/xxx@ORCL_S
# 得到如下内容（注意辅助数据库是未装载状态）：
恢复管理器: Release 12.2.0.1.0 - Production on 星期一 8月 31 21:31:39 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
已连接到目标数据库: ORCL (DBID=1577340417)
已连接到辅助数据库: ORCL (未装载)

# 开始同步
RMAN> duplicate target database for standby from active database nofilenamecheck; 
RMAN> exit;

  13. 备机数据库开启

    完成前面动作后，开启备机数据库

    [oracle@localhost dbs]$ sqlplus / as sysdba 
 # 打开数据库
 SQL> alter database open;
 
 #检查数据库状态
 SQL> select open_mode from v$database; 
 OPEN_MODE  
--------------------  
READ ONLY  
 
SQL> alter database recover managed standby database disconnect from session;

  14. 测试数据同步

    # 在主库创建表并插入数据
SQL> create table sample(id number(10),name varchar2(20));
SQL> insert into sample values(1,'www.iusaas.com');  
SQL> insert into sample values(2,'多租户系统');  
SQL> commit;  
SQL> alter system switch logfile; 
 
#在备库检查是否同步
SQL> desc sample;
SQL> select * from sample;
avatar
文章
阅读
粉丝