Linux环境配置oracle12c dg(dataguard)

 

0. 初始准备

    主库安装oracle软件并建库，备库只安装oracle软件

配置	主库(primary)	备库(standby)	描述
ip	192.168.23.128	192.168.23.129
hostname	sky	sea
oracle_base	/u01/app/oracle	/u01/app/oracle
oracle_home	/u01/app/oracle/product/12.2.0.1/db_1	/u01/app/oracle/product/12.2.0.1/db_1
oracle_sid	sky	sea
datafile	/u01/app/oracle/oradata/sky/	/u01/app/oracle/oradata/sea	备库要手动新建目录
archive_log	/u01/app/oracle/archivelog	/u01/app/oracle/archivelog	备库要手动新建目录
archive_log_std	/u01/app/oracle/archivelog_std	/u01/app/oracle/archivelog_std	主备库都要手动新建目录，用于主备切换后使用的归档目录
audit_file_dest	/u01/app/oracle/admin/sky/adump	/u01/app/oracle/admin/sea/adump	备库要手动新建目录

1. 主库开归档并设置强制日志模式

(1) 查看是否开归档：archive log list;
(2) 开归档： 
        shutdown immediate;
        startup mount;
        alter database archivelog;
        alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog';
        alter database force logging;
        alter database open;

2. 配置主备库监听

    (1) 主库listener.ora：hostname值用ip代替

[oracle@sky ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/network/admin/
[oracle@sky admin]$ ls
listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora
[oracle@sky admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =  
(SID_LIST =  
  (SID_DESC =  
    (GLOBAL_DBNAME = sky)
    (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
    (SID_NAME = sky)
  ) 
)
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.128)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

[oracle@sky admin]$ 

     (2) 主库tnsnames.ora：添加备库的tns（sea部分）

[oracle@sky admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_SKY =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.128)(PORT = 1521))

SKY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.128)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sky)
    )
  )
  
SEA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sea)
    )
  )

[oracle@sky admin]$ 

    (3) 将主库的listener.ora和tnsnames.ora复制到备库监听文件位置，并修改对应的ip

[oracle@sea ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/network/admin/
[oracle@sea admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@sea admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

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

ADR_BASE_LISTENER = /u01/app/oracle
[oracle@sea admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

#LISTENER_SEA =
# (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.129)(PORT = 1521))

SKY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.128)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sky)
    )
  )

SEA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sea)
    )
  )

[oracle@sea admin]$

 3. 添加备库日志组（在主库mount状态下执行）

sql>alter database add standby logfile group 4 '/u01/app/oracle/oradata/sky/redo04.log' size 100m;
sql>alter database add standby logfile group 5 '/u01/app/oracle/oradata/sky/redo05.log' size 100m;
sql>alter database add standby logfile group 6 '/u01/app/oracle/oradata/sky/redo06.log' size 100m;

4. 为主备库准备参数文件

    (1) 主库参数文件

[oracle@sky ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs
[oracle@sky dbs]$ cat initsky.ora 
sky.__data_transfer_cache_size=0
sky.__db_cache_size=503316480
sky.__inmemory_ext_roarea=0
sky.__inmemory_ext_rwarea=0
sky.__java_pool_size=16777216
sky.__large_pool_size=33554432
sky.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
sky.__pga_aggregate_target=654311424
sky.__sga_target=956301312
sky.__shared_io_pool_size=33554432
sky.__shared_pool_size=352321536
sky.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/sky/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/sky/control01.ctl','/u01/app/oracle/oradata/sky/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oracle/oradata/sea','/u01/app/oracle/oradata/sky'
*.db_name='sky'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=skyXDB)'
*.enable_pluggable_database=true
*.db_unique_name='sky'
*.fal_client='sky'
*.fal_server='sea'
*.local_listener='LISTENER_SKY'
*.log_archive_config='DG_CONFIG=(sky,sea)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sky'
*.log_archive_dest_2='SERVICE=sea arch SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sea'
*.log_archive_dest_3='LOCATION=/u01/app/oracle/archivelog_std valid_for=(standby_logfiles,standby_role) db_unique_name=sky'
*.log_file_name_convert='/u01/app/oracle/oradata/sea','/u01/app/oracle/oradata/sky'
*.memory_target=1527m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@sky dbs]$ 

    (2)备库参数文件：将主库的参数文件拷贝到备库相应位置并修改对应名称和内容参数

[oracle@sea ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/
[oracle@sea dbs]$ cat initsea.ora 
sky.__data_transfer_cache_size=0
sky.__db_cache_size=620756992
sky.__inmemory_ext_roarea=0
sky.__inmemory_ext_rwarea=0
sky.__java_pool_size=16777216
sky.__large_pool_size=33554432
sky.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
sky.__pga_aggregate_target=654311424
sky.__sga_target=956301312
sky.__shared_io_pool_size=33554432
sky.__shared_pool_size=234881024
sky.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/sea/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/sea/control01.ctl','/u01/app/oracle/oradata/sea/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oracle/oradata/sky','/u01/app/oracle/oradata/sea'
*.db_name='sky'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=skyXDB)'
*.enable_pluggable_database=true
*.db_unique_name='sea'
*.fal_client='sea'
*.fal_server='sky'
*.local_listener='LISTENER_SEA'
*.log_archive_config='DG_CONFIG=(sea,sky)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sea'
*.log_archive_dest_2='SERVICE=sky arch SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sky'
*.log_archive_dest_3='LOCATION=/u01/app/oracle/archivelog_std valid_for=(standby_logfiles,standby_role) db_unique_name=sea'
*.log_file_name_convert='/u01/app/oracle/oradata/sky','/u01/app/oracle/oradata/sea'
*.memory_target=1527m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@sea dbs]$ 

5. 拷贝主库的控制文件和密码文件到从库的相应目录下并修改名称

    主库：生成备库的控制文件

sql>alter database create standby controlfile as''/u01/app/oracle/control_std.ctl';

    备库：将生成的控制文件复制到备库的相应路径下并修改相应名称

              将密码文件复制到相应目录下并修改相应名称

6. 登录sqlplus，创建spfile，并启动到nomount状态

sql>create spfile from pfile='/u01/app/oracle/product/12.2.0.1/db_1/dbs/initsea.ora';
sql>shutdown immediate;
sql>startup nomount;
Redo Buffers		    7983104 bytes
SQL> 

7. 使用rman连接主备库

[oracle@sky ~]$ rman target sys/sky@sky auxiliary sys/sky@sea

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Apr 9 16:43:56 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SKY (DBID=3365626560)
connected to auxiliary database: SKY (not mounted)          ----注：此状态代表连接备库成功，其他状态都不行

RMAN> backup current controlfile for standby database;
RMAN> duplicate target database for standby from active database; 

8. 将备库设置为接受日志模式

sql> alter database recover managed standby database disconnect from session;