REHL8 ORACLE 19c DATAGUARD配置

1.oracle 用户环境:

主库:

[oracle@hzmtx admin]$ cat ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
umask 022
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/19c
export ORACLE_UNQNAME=mtxdb
export ORACLE_SID=mtxdb
export NLS_LANG=AMERICAN_AMERICA.UTF8
export CV_ASSUME_DISTID=RHEL7.6;export CV_ASSUME_DISTID
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin
export LC_ALL=en_US.UTF8

备库:

[oracle@nymtx trace]$  cat ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
umask 022
export LC_ALL="en_US.UTF-8"
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/19c
export ORACLE_UNQNAME=mtxdg
export ORACLE_SID=mtxdg
export NLS_LANG=AMERICAN_AMERICA.UTF8
export CV_ASSUME_DISTID=RHEL7.6;export CV_ASSUME_DISTID
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin

2.TNS主备库一样

[oracle@hzmtx admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/app/oracle/19c/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

MTXDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hzmtx.inno.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mtxdb)
    )
  )

MTXDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = nymtx.inno.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mtxdg)
    )
  )

3.监听

主:

[oracle@hzmtx admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/app/oracle/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mtxdb)
      (ORACLE_HOME = /oracle/app/oracle/19c)
      (SID_NAME = MTXDB)
    )
  )
LISTENER =
        (DESCRIPTION_LIST =
                (DESCRIPTION =
                        (ADDRESS = (PROTOCOL = TCP)(HOST = hzmtx.inno.com)(port = 1521))
                        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                )
        )
ADR_BASE_MTXDB = /oracle/app/oracle

备:

[oracle@nymtx admin]$ cat listener.ora
# copyright (c) 1997 by the Oracle Corporation
LISTENER =
  (DESCRIPTION_LIST =
        (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = nymtx.inno.com)(PORT = 1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
        )

SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME = mtxdg)
          (SID_NAME = mtxdg)
          (ORACLE_HOME = /oracle/app/oracle/19c)
        )
       )

3.密码文件

主:
orapwd file=orapwmtxdb entries=10 password=P1ssW-rd force=y
备:
orapwd file=orapwmtxdg entries=10 password=P2ssW-rd force=y

4.pfile:

主:

alter database force logging;
alter system set db_recovery_file_dest_size=5g;
alter system set db_recovery_file_dest='/oracle/app/oracle/recovery_area';
startup mount;
alter database archivelog;
alter database open;
alter system switch logfile;
alter database add standby logfile group 4 '/oracle/app/oracle/oradata/MTXDB/stredo04.log' size 200m;
alter database add standby logfile group 5 '/oracle/app/oracle/oradata/MTXDB/stredo05.log' size 200m;
alter database add standby logfile group 6 '/oracle/app/oracle/oradata/MTXDB/stredo06.log' size 200m;
alter database add standby logfile group 7 '/oracle/app/oracle/oradata/MTXDB/stredo07.log' size 200m;
alter system set db_unique_name='mtxdb' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(mtxdb,mtxdg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mtxdb' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=mtxdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mtxdg' scope=both;
alter system set fal_client='mtxdb' scope=both;
alter system set FAL_SERVER='mtxdg' scope=both;
alter system set DB_FILE_NAME_CONVERT='MTXDG','MTXDB' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='MTXDG','MTXDB' scope=spfile;
alter system set standby_file_management=AUTO scope=both;
shutdown immediate;
startup;
create pfile='pfile.ora' from spfile;

[oracle@hzmtx dbs]$ cat pfile.ora
mtxdb.__data_transfer_cache_size=0
mtxdb.__db_cache_size=1778384896
mtxdb.__inmemory_ext_roarea=0
mtxdb.__inmemory_ext_rwarea=0
mtxdb.__java_pool_size=0
mtxdb.__large_pool_size=16777216
mtxdb.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
mtxdb.__pga_aggregate_target=822083584
mtxdb.__sga_target=2466250752
mtxdb.__shared_io_pool_size=134217728
mtxdb.__shared_pool_size=520093696
mtxdb.__streams_pool_size=0
mtxdb.__unified_pga_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/mtxdb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/MTXDB/control01.ctl','/oracle/app/oracle/recovery_area/MTXDB/control02.ctl'
*.db_block_size=8192
*.db_domain='inno.com'
*.db_file_name_convert='mtxdg','mtxdb'
*.db_name='mtxdb'
*.db_recovery_file_dest='/oracle/app/oracle/recovery_area'
*.db_recovery_file_dest_size=5368709120
*.db_unique_name='MTXDB'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mtxdbXDB)'
*.enable_pluggable_database=true
*.fal_client='mtxdb'
*.fal_server='mtxdg'
*.local_listener='LISTENER_MTXDB'
*.log_archive_config='DG_CONFIG=(mtxdb,mtxdg)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mtxdb'
*.log_archive_dest_2='SERVICE=itpuxdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mtxdg'
*.log_file_name_convert='mtxdg','mtxdb'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=779m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2337m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

备:

[oracle@nymtx admin]$ cat $ORACLE_HOME/dbs/pfile.ora
*.audit_file_dest='/oracle/app/oracle/admin/mtxdg/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/MTXDG/control01.ctl','/oracle/app/oracle/recovery_area/MTXDG/control02.ctl'
*.db_block_size=8192
*.db_domain='inno.com'
*.db_file_name_convert='MTXDB','MTXDG'
*.db_name='mtxdb'
*.db_recovery_file_dest='/oracle/app/oracle/recovery_area'
*.db_recovery_file_dest_size=5368709120
*.db_unique_name='MTXDG'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mtxdgXDB)'
*.enable_pluggable_database=true
*.fal_client='mtxdg'
*.fal_server='mtxdb'
*.local_listener='LISTENER_MTXDG'
*.log_archive_config='DG_CONFIG=(mtxdg,mtxdb)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_F1LE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mtxdg'
*.log_archive_dest_2='SERVICE=mtxdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mtxdb'
*.log_file_name_convert='MTXDB','MTXDG'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=779m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2337m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

mkdir -p /oracle/app/oracle/admin/mtxdg/adump
mkdir -p /oracle/app/oracle/oradata/mtxdg

创建备库spfile

startup pfile='pfile.ora' nomount;
create spfile from pfile='pfile.ora';
shutdown immediate;
startup nomount;

5.测试连接

主备库以下这两个连接都成功

sqlplus sys/password@primary as sysdba 和sqlplus sys/password@standby as sysdba

6.复制数据

rman target sys/'P2ssW-rd'@mtxdb auxiliary sys/'P2ssW-rd'@mtxdg
duplicate target database for standby from active database;

7.验证