oracle | ADG 部署

环境准备：两台服务器(冷备方式搭建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、主库开归档，以冷备方式同步数据库文件到host02

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
 SHUTDOWN IMMEDIATE;
CREATE PFILE  FROM SPFILE;
cd $ORACLE_HOME/dbs 
cp initPRODDG.ora orapwPRODDG /u01/app/oracle/oradata/PRODDG/
cd /u01/app/oracle/oradata
tar -zcvf pg_cold.tar.gz PRODDG/
scp pg_cold.tar.gz host02:/u01/app/oracle/oradata

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=USE_DB_RECOVERY_FILE_DEST
  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;

scp stan_pg.ctl host02:/u01/app/oracle/oradata/

修改监听

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文件后启动

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)
    )
  )

启动

mkdir -p /u01/app/oracle/admin/PRODDG/adump
export ORACEL_SID=PRODDG
sqlplus / as sysdba
CREATE SPFILE FROM PFILE ;
 STARTUP MOUNT;

rman target /
 restore controlfile from '/u01/app/oracle/oradata/stan_pg.ctl';
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
shu immediate
startup 
SELECT db_unique_name,open_mode,database_role,flashback_on FROM V$DATABASE;
alter database falshback on ;

4、测试SNAPSHOT STANDBY

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN READ WRITE; 
create table t (a number);
insert into t values(1);
commit;
select * from t;

转回PHYSICAL STANDBY

shu immediate
startup mount
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
alter database open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

5、自动切换

两边都做

alter system reset log_archive_dest_1;
alter system reset log_archive_dest_2;
show parameter broker
alter system set DG_BROKER_START=true;
SHUTDOWN IMMEDIATE;
STARTUP 

使用dgmgrl管理
主库

dgmgrl sys/oracle
CREATE CONFIGURATION 'OCM' AS PRIMARY DATABASE IS 'PRODDG01'  CONNECT IDENTIFIER IS PRODDG01;
 SHOW CONFIGURATION;
 ADD DATABASE 'PRODDG02' AS  CONNECT IDENTIFIER IS PRODDG02;
 ENABLE CONFIGURATION;
 ENABLE DATABASE 'PRODDG02';
 EDIT DATABASE 'PRODDG01' SET PROPERTY 'LogXptMode'='SYNC';
 EDIT DATABASE 'PRODDG02' SET PROPERTY 'LogXptMode'='SYNC';
 EDIT DATABASE 'PRODDG01' SET PROPERTY 'ArchiveLagTarget'=50;
 EDIT DATABASE 'PRODDG02' SET PROPERTY 'ArchiveLagTarget'=50;
 EDIT DATABASE 'PRODDG01' SET PROPERTY FastStartFailoverTarget='PRODDG02';
 EDIT DATABASE 'PRODDG02' SET PROPERTY FastStartFailoverTarget='PRODDG01';
EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
SHOW CONFIGURATION;
 ENABLE FAST_START FAILOVER;
 SHOW FAST_START FAILOVER;

备库

dgmgrl sys/oracle "START OBSERVER" &

切换

switchover to PRODDG02 ;
switchover to PRODDG01 ;