oracle 19C dataguard 搭建

矫情吗；* 2022-08-31 02:23 572阅读 0赞

### 1.安装前环境 ###

主库 全部安装好

备库 只安装数据库软件，建立监听

sid不用一致，dbname要一致

### 2.主库配置 ###

**2.1数据库开启归档模式**

archive log list //查看归档模式，默认为非归档(必须在sqlplus 下执行)
    1）  shutdown immediate
    2）  startup mount
    3）  alter database archivelog
    4）  alter database open;

设置归档路径(oracle 用户)

mkdir /oracle/u01/app/oracle/oradbdg_archivelog

alter system set log_archive_dest_1='location=/oracle/u01/app/oracle/oradbdg_archivelog';

**2.2强制日志模式**

alter database force logging;

**2.3创建standby redolog日志组**  
**这里要注意standby redolog日志组要与redo日志组文件大小相同，数量多一个用来冗余**

查看备库的logfile文件

select * from v$logfile;

**因为redo log日志组有3组所以要至少创建4个standby redolog日志组**

alter database add standby logfile group4('/oracle/u01/app/oracle/oradata/oradb/standbyredo01.log') size 512m;
    alter database add standby logfile group5('/oracle/u01/app/oracle/oradata/oradb/standbyredo02.log') size 512m;
    alter database add standby logfile group6('/oracle/u01/app/oracle/oradata/oradb/standbyredo03.log') size 512m;
    alter database add standby logfile group7('/oracle/u01/app/oracle/oradata/oradb/standbyredo04.log') size 512m;

**2.4主库创建密码文件**

su - oracle
    orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y

**2.5通过spfile创建一个pfile文件**

create pfile='/tmp/init.ora' from spfile;

**2.6编辑网路服务名配置文件**

# tnsnames.ora Network Configuration File: /oracle/u01/app/oracle/product/19.3/dbhome_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.
    
    LISTENER_ORADB =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))
    
    
    ORADBPR =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.125)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = oradb)
        )
      )
    
    
    ORADBDG =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.126)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = oradb)
        )
      )

通过tnsping来测试

tnsping oradbpr
    tnsping oradbdg

**2.7修改参数文件**  
这里可以通过直接修改刚刚生成的pfile文件，重启数据库至mount状态，通过修改后的pfile文件来启动数据库 ,注释要删掉

oradb.__data_transfer_cache_size=0
    oradb.__db_cache_size=3590324224
    oradb.__inmemory_ext_roarea=0
    oradb.__inmemory_ext_rwarea=0
    oradb.__java_pool_size=0
    oradb.__large_pool_size=16777216
    oradb.__oracle_base='/oracle/u01/app/oracle'#ORACLE_BASE set from environment
    oradb.__pga_aggregate_target=318767104
    oradb.__sga_target=4580179968
    oradb.__shared_io_pool_size=134217728
    oradb.__shared_pool_size=822083584
    oradb.__streams_pool_size=0
    oradb.__unified_pga_pool_size=0
    *.audit_file_dest='/oracle/u01/app/oracle/admin/oradb/adump'
    *.audit_trail='db'
    *.compatible='19.0.0'
    *.control_files='/oracle/u01/app/oracle/oradata/ORADB/control01.ctl','/oracle/u01/app/oracle/oradata/ORADB/control02.ctl'
    *.db_block_size=8192
    *.db_file_name_convert='/oracle/u01/app/oracle/oradata/oradbdg','/oracle/u01/app/oracle/oradata/ORADB'	##db_file_name_convert 主数据库和备用数据库的数据文件转换目录对映(目标数据库数据文件目录，本地数据库数据文件目录)
    *.db_name='oradb'
    *.db_unique_name='oradbpr'	##代表主库的唯一名，主备库都要有各自的唯一名，可自定义
    *.diagnostic_dest='/oracle/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)'
    *.fal_client='oradbpr'	##FAL参数定义的数据库名同样取自本地tnsnames.ora里配置的Oracle Net Service Name，clinent代表本地Oracle Net Service Name
    *.fal_server='oradbdg'	##FAL参数定义的数据库名同样取自本地tnsnames.ora里配置的Oracle Net Service Name，server是目标端Oracle Net Service Name
    *.enable_pluggable_database=true
    *.local_listener='LISTENER_ORADB'
    *.log_archive_config='DG_CONFIG=(oradbpr,oradbdg)'	##列出主备库上的DB_UNIQUE_NAME 参数，默认情况下，定义该参数能确保主备库数据库能够互相识别对方
    *.log_archive_dest_1='location=/oracle/u01/app/oracle/oradbdg_archivelog'	##本地归档路径，主库与备库都需要定义各自的online redo log的归档地址，以系统实际的存放路径为准
    *.log_archive_dest_2='SERVICE=oradbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradbdg'	##该参数仅当数据库角色为主库时生效，指定primary归档redo log到该参数定义的standby database上，其中SERVICE=zzwdg里面SERVICE为上面的fal_server，DB_UNIQUE_NAME=zzwdg为备库的DB_UNIQUE_NAME
    *.log_file_name_convert='/oracle/u01/app/oracle/oradata/oradbdg','/oracle/u01/app/oracle/oradata/ORADB'	##主数据库和备用数据库的日志文件转换目录对映(目标数据库数据文件目录，本地数据库数据文件目录)
    *.nls_language='SIMPLIFIED CHINESE'
    *.nls_territory='CHINA'
    *.open_cursors=300
    *.pga_aggregate_target=300m
    *.processes=300
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sga_target=4356m
    *.standby_file_management='AUTO'	##该参数值设置为AUTO，主库执行的表空间创建操作也会被传播到物理Standby数据库上执行
    *.undo_tablespace='UNDOTBS1'

然后

shutdown immediate;
    create spfile from pfile='/tmp/init.ora';
    startup;

### 3.备库配置  ###

**3.1先通过scp把主库的密码文件，监听文件，pfile文件copy到备库**

密码文件：
    scp /oracle/u01/app/oracle/product/19.3/dbhome_1/dbs/orapworadb oracle@172.16.1.126:/oracle/u01/app/oracle/product/19.3/dbhome_1/dbs/
    
    监听文件（listener.ora ,tnsnames.ora）：
    scp /oracle/u01/app/oracle/product/19.3/dbhome_1/network/admin/listener.ora oracle@172.16.1.126:/oracle/u01/app/oracle/product/19.3/dbhome_1/network/admin/
    scp /oracle/u01/app/oracle/product/19.3/dbhome_1/network/admin/tnsnames.ora oracle@172.16.1.126:/oracle/u01/app/oracle/product/19.3/dbhome_1/network/admin/
    
    pfile文件：
    scp /tmp/initoradb.ora oracle@172.16.1.126:/tmp/initoradb.ora

**3.2修改监听文件(主机名)**

# listener.ora Network Configuration File: /oracle/u01/app/oracle/product/19.3/dbhome_1/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = oradb)
          (SID_NAME = oradb)
        )
      )
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = oracletest2)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )

重启监听

lsnrctl stop
    lsnrctl start

**3.3创建主备需要的目录**

mkdir -p /oracle/u01/app/oracle/admin/oradbdg/adump
    mkdir -p /oracle/u01/app/oracle/admin/oradbdg/dbdump
    mkdir -p /oracle/u01/app/oracle/admin/oradbdg/pfile
    mkdir -p /oracle/u01/app/oracle/oradata/oradbdg
    mkdir -p /oracle/u01/app/oracle/fast_recovery_area/oradbdg
    mkdir -p /oracle/u01/app/oracle/oradata/oradbdg/archivelog

**3.5修改pfile参数文件****改参数文件 **

> 修改以下这几项
> 
> **\*.audit\_file\_dest  
> \*.control\_files  
> \*.db\_file\_name\_convert  
> \*.db\_unique\_name  
> \*.fal\_client  
> \*.fal\_server  
> \*.log\_file\_name\_convert**

**3.6 启动备库到nomount**

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

### 4.利用RMAN在主库上执行，将主库恢复到备库上  ###

**主库上面执行**

**登录**

rman target sys/Zggfxxb2018@oradbpr auxiliary sys/Zggfxxb2018@oradbdg

开始复制

duplicate target database for standby from active database nofilenamecheck;

**回复完成后备库处于mount状态**

SQL> select status from v$instance;
    
    STATUS
    ------------
    MOUNTED

配置只读

ALTER DATABASE OPEN READ ONLY;
    
    alter database recover managed standby database using current logfile disconnect;

启动日志应用

alter database recover managed standby database using current logfile disconnect from session;

查看DG是否是实时应用

SQL> select  DEST_NAME , STATUS , RECOVERY_MODE from v$archive_dest_status
    
    DEST_NAME                      STATUS    RECOVERY_MODE
    ------------------------------ --------- -----------------------
    LOG_ARCHIVE_DEST_1             VALID     MANAGED REAL TIME APPLY
    LOG_ARCHIVE_DEST_2             VALID     IDLE
    LOG_ARCHIVE_DEST_3             INACTIVE  IDLE
    LOG_ARCHIVE_DEST_4             INACTIVE  IDLE
    LOG_ARCHIVE_DEST_5             INACTIVE  IDLE
    LOG_ARCHIVE_DEST_6             INACTIVE  IDLE
    LOG_ARCHIVE_DEST_7             INACTIVE  IDLE
    LOG_ARCHIVE_DEST_8             INACTIVE  IDLE
    LOG_ARCHIVE_DEST_9             INACTIVE  IDLE
    LOG_ARCHIVE_DEST_10            INACTIVE  IDLE
    LOG_ARCHIVE_DEST_11            INACTIVE  IDLE

成功!