oracle | ADG 部署2

chkl 2025-04-16

193

环境准备：两台服务器(rman方式搭建ADG）
主库：192.168.1.42 主机名：host01预装了oracle19c-db软件监听和库都是正常的
备库：192.168.1.33 主机名：host02预装了oracle19c-db软件（无监听，无数据库）
实例名：PRODDG

vim  /etc/hosts
192.168.1.42 host01
192.168.1.33 host02

1、主库开归档、强制日志，不开启闪回。

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
ALTER DATABASE FORCE LOGGING;
SYS@PRODDG> show parameter db_recovery

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size	     big integer 8256M
SYS@PRODDG>show parameter diag

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest 		     string	 /u01/app/oracle 

 SHUTDOWN IMMEDIATE;
CREATE PFILE  FROM SPFILE;
cd $ORACLE_HOME/dbs 
scp initPRODDG.ora orapwPRODDG  host02:$ORACLE_HOME/dbs

2、主库修改pfile文件后启动

cd $ORACLE_HOME/dbs
vim initPRODDG.ora
## 修改*.local_listener=''
## 添加
DB_UNIQUE_NAME=PRODDG01
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODDG01,PRODDG02)'
DB_FILE_NAME_CONVERT='PRODDG02','PRODDG01'
LOG_FILE_NAME_CONVERT='PRODDG02','PRODDG01'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
 'LOCATION=/u01/app/oracle/fast_recovery_area
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
  DB_UNIQUE_NAME=PRODDG01'
LOG_ARCHIVE_DEST_2=
 'SERVICE=PRODDG02 ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=PRODDG02'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=PRODDG02

启动数据库后启动强制日志，添加备用日志组

sqlplus / as sysdba
CREATE SPFILE FROM PFILE ;
startup 
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE FALSHBACK ON;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PRODDG/stan_redo01.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PRODDG/stan_redo02.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PRODDG/stan_redo03.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/PRODDG/stan_redo04.log') SIZE 200M;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/oradata/stan_pg.ctl';
SELECT db_unique_name,open_mode,database_role,flashback_on FROM V$DATABASE;

修改监听

vim listener.ora
SID_LIST_LISTENER=
  (SID_LIST=
       (GLOBAL_DBNAME=PRODDG01.example.com )
       (SID_NAME=PRODDG)
       (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
      )
     (SID_DESC=
       (GLOBAL_DBNAME=PRODDG01_DGMGRL.example.com )
       (SID_NAME=PRODDG)
       (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
      )
  )
lsnrctl  reload
vim tnsnames.ora
PRODDG01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDG01.example.com)
    )
  )
PRODDG02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDG02.example.com)
    )
  )

3、备库修改pfile文件后启动

mkdir -p /u01/app/oracle/oradata/PRODDG
mkdir -p /u01/app/oracle/admin/PRODDG/adump
cd $ORACLE_HOME/dbs
vim initPRODDG.ora
## 修改*.local_listener=''
## 添加
DB_UNIQUE_NAME=PRODDG02
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODDG02,PRODDG01)'
DB_FILE_NAME_CONVERT='PRODDG01','PRODDG02'
LOG_FILE_NAME_CONVERT='PRODDG01','PRODDG02'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
  DB_UNIQUE_NAME=PRODDG02'
LOG_ARCHIVE_DEST_2=
 'SERVICE=PRODDG01 ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=PRODDG01'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=PRODDG01

修改监听

vim listener.ora
SID_LIST_LISTENER=
  (SID_LIST=
       (GLOBAL_DBNAME=PRODDG02.example.com )
       (SID_NAME=PRODDG)
       (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
      )
     (SID_DESC=
       (GLOBAL_DBNAME=PRODDG02_DGMGRL.example.com )
       (SID_NAME=PRODDG)
       (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
      )
  )
lsnrctl  reload
vim tnsnames.ora
PRODDG01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDG01.example.com)
    )
  )
PRODDG02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDG02.example.com)
    )
  )

备库duplicate在线复制主库

sqlplus sys/oracle@PRODDG as sysdba
create spfile from pfile;
startup nomount;
[oracle@host02 dbs]$ rman target sys/oracle@PRODDG01 auxiliary sys/oracle@PRODDG02

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 16 17:18:45 2025
Version 19.3.0.0.0

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

connected to target database: PRODDG (DBID=1034241150)
connected to auxiliary database: PRODDG (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 16-APR-25
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=263 device type=DISK

SYS@PRODDG >select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_NAME		       OPEN_MODE	    DATABASE_ROLE
------------------------------ -------------------- ----------------
FLASHBACK_ON
------------------
PRODDG02		       MOUNTED		    PHYSICAL STANDBY
NO


SYS@PRODDG >ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION;

Database altered.

SYS@PRODDG >ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SYS@PRODDG > ALTER DATABASE OPEN;

Database altered.

SYS@PRODDG >select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_NAME		       OPEN_MODE	    DATABASE_ROLE
------------------------------ -------------------- ----------------
FLASHBACK_ON
------------------
PRODDG02		       READ ONLY	    PHYSICAL STANDBY
NO


SYS@PRODDG >select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_NAME		       OPEN_MODE	    DATABASE_ROLE
------------------------------ -------------------- ----------------
FLASHBACK_ON
------------------
PRODDG02		       READ ONLY	    PHYSICAL STANDBY
NO
SYS@PRODDG > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SYS@PRODDG >select db_unique_name,open_mode,database_role,flashback_on from v$database;

DB_UNIQUE_NAME		       OPEN_MODE	    DATABASE_ROLE
------------------------------ -------------------- ----------------
FLASHBACK_ON
------------------
PRODDG02		       READ ONLY WITH APPLY PHYSICAL STANDBY
NO

oracle 备份恢复

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

oracle | ADG 部署2

1、主库开归档、强制日志，不开启闪回。

2、主库修改pfile文件后启动

3、备库修改pfile文件后启动

评论