单机19c oracle不停机搭建ADG备库 同步数据并主备切换

0.前期准备数据库版本、补丁、操作系统版本一样ADG只需要安装数据库软件不需要dbca创建库备库ORACLE_HOME、ORACLE_SID路径和主库保持一致主库ADG-备库IP10.98.3.7010.98.3.75数据库版本19.19.0.0.019.19.0.0.0补丁19.19_DB_p35042068_190000_Linux-x86-64.zip19.19_DB_p35042068_190000_Linux-x86-64.zip操作系统版本Red Hat7.9Red Hat8.8备库hostnamewza01,oracle_sidorcl,db_unique_nameorcl备库hostnamewzabak,oracle_sidorcldg,db_unique_nameorcldg一、主库备库环境准备1、主库配置1.1.主库更改hosts文件vi /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 #DG备库 10.98.3.75 wzabak #主库 10.98.3.70 wza011.2.主库开启强日志归档模式select force_logging from v$database; alter database force logging; archive log list; --如果主库没有开启归档是需要停机窗口开启归档的 sqlplus / as sysdba shutdown immediate startup mount alter database archivelog; alter database open1.3.主库配置参数查询数据文件位置select name from v$datafile;查询日志文件位置select member from v$logfile;DG_CONFIG主库和备库必须设置不同的名称不区分大小写oracle_sid可以重复因为是异机fal_client本机oracle_sidDB_FILE_NAME_CONVERT主库路径、备库路径alter system set LOG_ARCHIVE_CONFIGDG_CONFIG(orcl,orcldg) scopeboth sid*; alter system set log_archive_dest_1LOCATION/archivelog VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAMEorcl scopespfile; alter system set LOG_ARCHIVE_DEST_2SERVICEorcldg LGWR ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAMEorcldg scopeboth sid*; alter system set fal_clientorcl scopeboth sid*; alter system set FAL_SERVERorcldg scopeboth sid*; alter system set standby_file_managementAUTO scopeboth sid*; alter system set DB_FILE_NAME_CONVERT/data/orcl,/u01/app/oracle/oradata/orcldg/datafile scopespfile sid*; alter system set LOG_FILE_NAME_CONVERT/data/orcl,/u01/app/oracle/oradata/orcldg/onlinelog scopespfile sid*; alter system set log_archive_format%t_%s_%r.arc scopespfile sid*; alter system set remote_login_passwordfileEXCLUSIVE scopespfile; alter system set PARALLEL_EXECUTION_MESSAGE_SIZE8192 scopespfile; 有scopespfile的参数需要重启数据库生效 (无停机窗口的时候可以不设置非必要的参数1.4.主库增加standby logfile日志组select GROUP#,BYTES/1024/1024 MB,STATUS from v$log; GROUP# MB STATUS ---------- ---------- ---------------- 1 200 CURRENT 2 200 INACTIVE 3 200 INACTIVE 4 200 CURRENT 备库比主库多一组redolog大小一样group最好从10开始防止和主库冲突 alter database add standby logfile thread 1 group 11 /u01/app/oracle/fast_recovery_area/redo11.log size 200m; alter database add standby logfile thread 1 group 12 /u01/app/oracle/fast_recovery_area/redo12.log size 200m; alter database add standby logfile thread 1 group 13 /u01/app/oracle/fast_recovery_area/redo13.log size 200m; alter database add standby logfile thread 2 group 14 /u01/app/oracle/fast_recovery_area/redo14.log size 200m; alter database add standby logfile thread 2 group 15 /u01/app/oracle/fast_recovery_area/redo15.log size 200m; alter database add standby logfile thread 2 group 16 /u01/app/oracle/fast_recovery_area/redo16.log size 200m;1.5.主库修改tnsnames文件cd $ORACLE_HOME/network/admin vi tnsnames.ora ORCL (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST wza-01)(PORT 1521)) (CONNECT_DATA (SERVER DEDICATED) (SERVICE_NAME orcl) ) ) ORCLDG (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST wzabak)(PORT 1521)) (CONNECT_DATA (SERVER DEDICATED) (SERVICE_NAME orcldg) ) )1.6.主库监听重启确认监听状态su - oracle lsnrctl stop lsnrctl start lsnrctl status [oraclewza01]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-APR-2025 15:55:29 Copyright (c) 1991, 2023, Oracle. All rights reserved. Connecting to (ADDRESS(PROTOCOLtcp)(HOST)(PORT1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 14-APR-2025 10:14:49 Uptime 3 days 5 hr. 40 min. 40 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/19c/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/wza01/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION(ADDRESS(PROTOCOLipc)(KEYLISTENER))) (DESCRIPTION(ADDRESS(PROTOCOLtcp)(HOST10.98.3.70)(PORT1521))) Services Summary... Service orcl has 1 instance(s). Instance orcl1, status READY, has 1 handler(s) for this service... The command completed successfully2、备库配置2.1.备库更改hosts文件和主库内容相同su - root vi /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 #DG备库 10.98.3.75 wzabak #主库 10.98.3.70 wza012.2.备库更改Oracle用户环境变量[oraclewzabak ~]$ pwd /home/oracle [oraclewzabak ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH$PATH:$HOME/.local/bin:$HOME/bin export PATH export EDITORvi export TMP/tmp export TMPDIR$TMP export ORACLE_SIDorcldg export ORACLE_BASE/u01/app/oracle export ORACLE_HOME$ORACLE_BASE/product/19c/db_1 export INVENTORY_LOCATION/u01/oraInventory export TNS_ADMIN$ORACLE_HOME/network/admin export LD_LIBRARY_PATH$ORACLE_HOME/lib export NLS_LANGAMERICAN_AMERICA.AL32UTF8 export NLS_DATE_FORMATYYYY-MM-DD HH24:MI:SS export PATH$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin:$PATH:$HOME/bin2.3.备库修改tnsnames文件和主库内容相同cd $ORACLE_HOME/network/admin vi tnsnames.ora ORCL (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST wza-01)(PORT 1521)) (CONNECT_DATA (SERVER DEDICATED) (SERVICE_NAME orcl) ) ) ORCLDG (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST wzabak)(PORT 1521)) (CONNECT_DATA (SERVER DEDICATED) (SERVICE_NAME orcldg) ) )2.4.备库修改listener.ora文件cd $ORACLE_HOME/network/admin vi listener.ora LISTENER (DESCRIPTION_LIST (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST 10.98.3.75)(PORT 1521)) (ADDRESS (PROTOCOL IPC)(KEY EXTPROC1521)) ) ) SID_LIST_LISTENER (SID_LIST (SID_DESC (GLOBAL_DBNAME orcl) (ORACLE_HOME /u01/app/oracle/product/19c/db_1) (SID_NAME orcl) ) (SID_DESC (GLOBAL_DBNAME orcldg) (ORACLE_HOME /u01/app/oracle/product/19c/db_1) (SID_NAME orcldg) ) )2.5.备库监听重启确认监听状态su - oracle lsnrctl stop lsnrctl start lsnrctl status [oraclewzabak]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-APR-2025 17:25:01 Copyright (c) 1991, 2023, Oracle. All rights reserved. Connecting to (DESCRIPTION(ADDRESS(PROTOCOLTCP)(HOST10.98.3.75)(PORT1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 16-APR-2025 17:14:50 Uptime 0 days 0 hr. 10 min. 10 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19c/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/wzabak/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION(ADDRESS(PROTOCOLtcp)(HOST10.98.3.75)(PORT1521))) (DESCRIPTION(ADDRESS(PROTOCOLipc)(KEYEXTPROC1521))) Services Summary... Service orcl has 1 instance(s). Instance orcl, status UNKNOWN, has 1 handler(s) for this service... Service orcldg has 1 instance(s). Instance orcldg, status UNKNOWN, has 1 handler(s) for this service... The command completed successfully3、复制密码文件到备库注意duplicate需要确保主备库的数据库密码用户名一致pfile(init*.ora)文件名一致3.1.主库重新生成密码文件密码文件名称orapw{主库ORACLE_SID}密码需要满足复杂度至少8位有一个特殊字符包含字母和数字spfile同路径下可以找到密码文件的位置 SQL show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /data/ORCL/PARAMETERFILE/spfil e.276.1191232127 su - grid orapwd file$ORACLE_HOME/dbs/orapworcl passworddba_12345 forcey3.2.复制密码文件到备库备库的路径不可以用$ORACLE_HOME代替除非主备库的ORACLE_HOME一模一样scp $ORACLE_HOME/dbs/orapworcl oraclewzabak:/u01/app/oracle/product/19c/db_1/dbs/ 备库 cd /u01/app/oracle/product/19c/db_1/dbs/ cp orapworcl orapworcldg4、备库修改参数文件备库的参数文件名称init{主库ORACLE_SID}.ora备库启动主库duplicate的时候读取此文件文件修改完毕之后复制一份init{备库ORACLE_SID}.ora搭建完成之后读取此文件主库: create pfile from spfile cd $ORACLE_HOME/dbs scp $ORACLE_HOME/dbs/initorcl.ora oraclewzabak:/u01/app/oracle/product/19c/db_1/dbs/ 备库 cp initorcl.ora initorcldg.ora4.1.修改参数文件enable_pluggable_databasetrue备库开启PDB模式这个是可选项su - oracle vi /u01/app/oracle/product/19c/db_1/dbs/initorcl.ora *.audit_file_dest/u01/app/oracle/audit *.audit_traildb *.compatible19.19.0.0 *.control_files/u01/app/oracle/oradata/orcldg/control01.ctl,/u01/app/oracle/oradata/orcldg/control02.ctl *.db_block_size8192 *.db_create_file_dest/u01/app/oracle/oradata/orcldg *.db_nameorcl *.diagnostic_dest/u01/app/oracle/diag/rdbms *.db_unique_nameorcldg *.dispatchers *.fal_clientorcldg *.fal_serverorcl *.LOG_ARCHIVE_CONFIGDG_CONFIG(orcl,orcldg) *.log_archive_dest_1LOCATION/u01/app/oracle/fast_recovery_area VALID_FOR(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAMEorcldg *.LOG_ARCHIVE_DEST_2SERVICEorcl LGWR ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAMEorcl *.log_archive_format%t_%s_%r.arc *.LOG_FILE_NAME_CONVERTDATA/orcl,/u01/app/oracle/oradata/orcldg/onlinelog *.DB_FILE_NAME_CONVERTDATA/orcl,/u01/app/oracle/oradata/orcldg/datefile *.nls_languageAMERICAN *.nls_territoryAMERICA *.open_cursors300 *.parallel_execution_message_size8192 *.pga_aggregate_target1006m *.processes320 *.remote_login_passwordfileEXCLUSIVE *.sga_target3018m *.standby_file_managementAUTO *.db_recovery_file_dest/u01/app/oracle/fast_recovery_area *.db_recovery_file_dest_size5242880 *.enable_pluggable_databasetrue *.service_namesorcl,orcldg log_archive_dest_state_1ENABLE log_archive_dest_state_2ENABLE4.2.根据参数文件在备库创建目录su - oracle mkdir -p /u01/app/oracle/oradata/orcldg/onlinelog mkdir -p /u01/app/oracle/oradata/orcldg/datafile mkdir -p /u01/app/oracle/oradata/orcldg/ mkdir -p /u01/app/oracle/diag/rdbms/ mkdir -p /u01/app/oracle/audit mkdir -p /u01/app/oracle/fast_recovery_area二、主备库数据同步1、备库启动至unmount[oraclewzabak ]$ sqlplus / as sysdba SQL startup nomount; ORACLE instance started. Total System Global Area 3170891368 bytes Fixed Size 9168488 bytes Variable Size 637534208 bytes Database Buffers 2516582400 bytes Redo Buffers 7606272 bytes SQL select status from v$instance; STATUS ------------------------------------ STARTED2、主库使用RMAN进行在线auxiliary克隆2.1rman登录主库和备库主备库的数据库登录用户名和密码必须相同[oraclewza01:/home/oracle]$ rman target sys/dba_12345orcl auxiliary sys/dba_12345orcldg Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 16 17:30:39 2025 Version 19.19.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID1719012227) connected to auxiliary database: ORCL (not mounted) RMAN2.2duplicate复制run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; allocate channel c6 type disk; allocate AUXILIARY channel c7 type disk; allocate AUXILIARY channel c8 type disk; allocate AUXILIARY channel c9 type disk; allocate AUXILIARY channel c10 type disk; allocate AUXILIARY channel c11 type disk; allocate AUXILIARY channel c12 type disk; DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; release channel c7; release channel c8; release channel c9; release channel c10; release channel c11; release channel c12; }2.3复制成功此时备库应为mounted状态。以下为示例logStarting restore at 18-JUL-26 channel aux1: starting archived log restore to default destination channel aux1: using network backup set from service orcl channel aux1: restoring archived log archived log thread1 sequence96993 channel aux2: starting archived log restore to default destination channel aux2: using network backup set from service orcl channel aux2: restoring archived log archived log thread1 sequence96994 channel aux3: starting archived log restore to default destination channel aux3: using network backup set from service orcl channel aux3: restoring archived log archived log thread1 sequence96995 channel aux1: restore complete, elapsed time: 00:00:00 channel aux2: restore complete, elapsed time: 00:00:01 channel aux3: restore complete, elapsed time: 00:00:01 Finished restore at 18-JUL-26 Starting recover at 18-JUL-26 Starting recover at 18-JUL-26 starting media recovery archived log for thread 1 with sequence 96993 is already on disk as file /data/app/oracle/archivelog1_96993_1204799200.dbf archived log for thread 1 with sequence 96994 is already on disk as file /data/app/oracle/archivelog1_96994_1204799200.dbf archived log for thread 1 with sequence 96995 is already on disk as file /data/app/oracle/archivelog1_96995_1204799200.dbf archived log file name/data/app/oracle/archivelog1_96993_1204799200.dbf thread1 sequence96993 archived log for thread 1 with sequence 96995 is already on disk as file /data/app/oracle/archivelog1_96995_1204799200.dbf archived log file name/data/app/oracle/archivelog1_96993_1204799200.dbf thread1 sequence96993 archived log file name/data/app/oracle/archivelog1_96994_1204799200.dbf thread1 sequence96994 archived log file name/data/app/oracle/archivelog1_96995_1204799200.dbf thread1 sequence96995 media recovery complete, elapsed time: 00:00:02 Finished recover at 18-JUL-26 Deleted 1 objects deleted archived log archived log file name/data/app/oracle/archivelog1_96994_1204799200.dbf RECID3 STAMP1238945222 Deleted 1 objects Finished Duplicate Db at 18-JUL-26 released channel: c1 released channel: c2 released channel: c3 released channel: c4 released channel: aux1 released channel: aux2 released channel: aux3 released channel: aux43、备库启动至openalter database recover managed standby database using current logfile disconnect;该命令用于启动备用数据库的恢复管理进程让备用数据库开始接收并应用主数据库传输过来的重做日志Redo Log以此保持与主数据库的数据同步。同时disconnect 选项允许在启动恢复进程后当前的 SQL 会话与恢复进程断开连接这样你可以继续使用该会话执行其他操作而恢复进程会在后台持续运行SQL alter database open; Database altered. SQL select open_mode from v$database; OPEN_MODE -------------------- READ ONLY SQL alter database recover managed standby database using current logfile disconnect; Database altered. SQL select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY SQL select status from v$instance; STATUS ------------ OPEN4、在备库确认物理备库搭建成功进程正常运行SQL select process,client_process,sequence#, THREAD# status from v$managed_standby; PROCESS CLIENT_P SEQUENCE# THREAD# STATUS --------- -------- ---------- ---------- ------------ ARCH ARCH 0 0 CONNECTED DGRD N/A 0 0 ALLOCATED DGRD N/A 0 0 ALLOCATED ARCH ARCH 0 0 CONNECTED ARCH ARCH 0 0 CONNECTED ARCH ARCH 0 0 CONNECTED RFS Archival 0 1 IDLE RFS LGWR 177 1 IDLE MRP0 N/A 170 2 WAIT_FOR_LOG RFS UNKNOWN 0 0 IDLE 10 rows selected. SQL select database_role,protection_mode,protection_level,open_mode from v$database; DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE ---------------- -------------------- -------------------- -------------------- PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY WITH APPLY SQL SELECT sequence#, thread#, first_time FROM V$ARCHIVED_LOG ORDER BY sequence#; SEQUENCE# THREAD# FIRST_TIME ---------- ---------- ------------------- 167 2 2025-04-25 10:52:31 168 2 2025-04-25 10:59:08 169 2 2025-04-25 10:59:26 169 2 2025-04-25 10:59:26 175 1 2025-04-25 10:59:06 176 1 2025-04-25 10:59:26 6 rows selected. SQL select group#,thread#,sequence#,archived,status from v$standby_log; GROUP# THREAD# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 11 1 0 NO UNASSIGNED 12 1 0 NO UNASSIGNED 13 1 177 YES ACTIVE 14 2 0 YES UNASSIGNED 15 2 0 YES UNASSIGNED 16 2 0 YES UNASSIGNED 6 rows selected.三、主备库角色切换0.应用停止向数据库写入因为主备切换有段时间停止窗口1.在主库检查 Switchover 状态SELECT SWITCHOVER_STATUS FROM V$DATABASE; -- 预期返回 TO STANDBY 或 SESSIONS ACTIVE 才能继续 检查主库状态必须在主库执行 SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE; -- 预期PRIMARY READ WRITE 检查主库是否有活动会话建议切换前先踢掉应用连接 SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL; 在主、备库执行确认最后归档已应用 SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIEDYES; 在备库确认同步状态 SELECT PROCESS, STATUS, SEQUENCE# FROM V$MANAGED_STANDBY WHERE PROCESSMRP0; -- MRP0 状态应为 APPLYING_LOGSEQUENCE# 应与主库一致2.主库执行 Switchover 切换状态变为备库-- 在主库执行将主库转为备库角色 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; -- 切换完成后主库会进入 MOUNT 状态 -- 然后关闭并启动到备库模式 SHUTDOWN IMMEDIATE; STARTUP MOUNT;3.在备库执行 Switchover 切换-- 在备库当前是 READ ONLY WITH APPLY执行 -- 先取消日志应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; -- 切换为新主库 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; -- 打开新主库 ALTER DATABASE OPEN;4.验证新主库状态-- 在新主库原备库上执行 SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE; DATABASE_ROLE OPEN_MODE ---------------- ------------ PRIMARY READ WRITE5.在新备库原主库启动日志应用如果这个新的备库准备废弃可以不做直接关机-- 在原主库现在是新备库上执行 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;6.验证ADG同步-- 在新主库原备库上查询 SELECT THREAD#, SEQUENCE#, FIRST_TIME, LAST_TIME FROM V$ARCHIVED_LOG WHERE APPLIEDYES ORDER BY SEQUENCE# DESC FETCH FIRST 3 ROWS ONLY; -- 在新备库原主库上查询 SELECT PROCESS, STATUS, SEQUENCE# FROM V$MANAGED_STANDBY WHERE PROCESSMRP0;