Oracle 19c 单实例adg 一主一备搭建
首次搭建oracle adg，整理了搭建主备的步骤及遇到的问题
主库配置
开启主库监听
lsnrctl start复制
开启归档模式
--先查看当前的归档模式 archive log list; --当 database log mode显示为no archive mode即为已经需要开启归档，请按照如下操作步骤开启归档 shutdown immediate startup mount; --创建归档日志文件目录 mkdir -p /data3/archivedata --设置归档文件并开启归档 alter system set log_archive_dest_1='/data3/archivedata'; alter database archivelog; alter database open;复制
确保主库 force logging mode
alter database force logging; SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- YES复制
拷贝主库密码文件到备库
scp $ORACLE_HOME/dbs/orapworcl oracle@103.163.8.155:$ORACLE_HOME/dbs/orapworcldg复制
配置tnsnames.ora并拷贝到备库
# tnsnames.ora Network Configuration File: /data/oracle/oracle/db/product/19.4/n etwork/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = 103.163.8.157)(PORT = 1521)) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =103.163.8.157 )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) ORCLDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =103.163.8.155 )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldg) ) ) --将tnsnames.ora scp tnsnames.ora传到备库 tnsnames.ora 103.163.8.155:/data/oracle/db/product/19.3/network/admin复制
到备库后修改LISTENER_ORCL的host值
# tnsnames.ora Network Configuration File: /data/oracle/oracle/db/product/19.4/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = 103.163.8.155)(PORT = 1521)) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =103.163.8.157 )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) ORCLDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =103.163.8.155 )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldg) ) )复制
主库中创建pfile文件，并传到备库中
SQL> create pfile='/home/oracle/mespfile.ora' from spfile; File created. cd /home/oracle scp mespfile.ora 103.163.8.155:/home/oracle复制
在主库中增加参数
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)' scope=both; alter system set log_archive_dest_2='SERVICE=orcldg async lgwr valid_for=(online_logfiles,primary_role) db_unique_name=orcldg' scope=both; alter system set fal_server=orcldg scope=both; alter system set fal_client=orcl scope=both; alter system set standby_file_management=auto scope=both;复制
主库做全备，并拷贝至备库
创建存放备份文件的目录
mkdir -p /data3/backup复制
rman全备
rman target / run{ allocate channel c1 device type disk; allocate channel c2 device type disk; backup database format '/data3/backup/backdata_%d_%T_%U.bak'; sql 'alter system archive log current'; backup archivelog all format '/data3/backup/archlog_%d_%T_%U.bak'; backup current controlfile format '/data3/backup/cntrl_%s_%p_%s.bak'; release channel c1; release channel c2; }复制
执行备份过程中报如下错误
--执行rman备份报RMAN-03009 ORA-19502 RMAN-03009: failure of backup command on c1 channel at 01/09/2024 11:02:54 ORA-19502: write error on file "/data/oracle/oracle/backup/backdata_ORCL_20240109_142g57o9_1_1.bak", block number 3560576 (block size=8192) ORA-27072: File I/O error Additional information: 4 Additional information: 3560576 Additional information: 565248 channel c1 disabled, job failed on it will be run on another channel released channel: c1 released channel: c2 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on c2 channel at 01/09/2024 11:02:57 ORA-19502: write error on file "/data/oracle/oracle/backup/backdata_ORCL_20240109_132g57n6_1_1.bak", block number 5339904 (block size=8192) ORA-27072: File I/O error Additional information: 4 Additional information: 5339904 Additional information: 430080复制
上述问题是由于磁盘空间不够导致rman备份失败。在新的目录创建备份目录并重新执行命令，备份正常
[oracle@jcyjs4 oracle]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 9 11:27:30 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1573120881) RMAN> run{ 2> allocate channel c1 device type disk; allocate channel c2 device type disk; backup database format '/data3/backup/backdata_%d_%T_%U.bak'; sql 'alter system archive log current'; backup archivelog all format '/data3/backup/archlog_%d_%T_%U.bak'; 3> 4> 5> 6> 7> backup current controlfile format '/data3/backup/cntrl_%s_%p_%s.bak'; release channel c1; 8> 9> release channel c2; 10> } using target database control file instead of recovery catalog allocated channel: c1 channel c1: SID=3632 device type=DISK allocated channel: c2 channel c2: SID=3874 device type=DISK Starting backup at 2024-01-09 11:27:46 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set input datafile file number=00016 name=/data/oracle/oracle/oradata/ORCL/USERS_1627895937538436.dbf input datafile file number=00055 name=/minio/data/test19.dbf channel c1: starting piece 1 at 2024-01-09 11:27:46 channel c2: starting full datafile backup set channel c2: specifying datafile(s) in backup set ... 日志省略 ... Starting Control File and SPFILE Autobackup at 2024-01-09 12:09:02 piece handle=/data3/data/ORCL/autobackup/2024_01_09/o1_mf_s_1157803743_lsskpz3q_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2024-01-09 12:09:04 sql statement: alter system archive log current Starting backup at 2024-01-09 12:09:04 current log archived channel c1: starting archived log backup set channel c1: specifying archived log(s) in backup set input archived log thread=1 sequence=5633 RECID=4859 STAMP=1157785804 channel c1: starting piece 1 at 2024-01-09 12:09:04 channel c2: starting archived log backup set channel c2: specifying archived log(s) in backup set input archived log thread=1 sequence=5634 RECID=4860 STAMP=1157798188 input archived log thread=1 sequence=5635 RECID=4861 STAMP=1157803744 channel c2: starting piece 1 at 2024-01-09 12:09:04 channel c2: finished piece 1 at 2024-01-09 12:09:05 piece handle=/data3/backup/archlog_ORCL_20240109_1l2g5bn0_1_1.bak tag=TAG20240109T120904 comment=NONE channel c2: backup set complete, elapsed time: 00:00:01 channel c2: starting archived log backup set channel c2: specifying archived log(s) in backup set input archived log thread=1 sequence=5636 RECID=4862 STAMP=1157803744 channel c2: starting piece 1 at 2024-01-09 12:09:05 channel c2: finished piece 1 at 2024-01-09 12:09:06 piece handle=/data3/backup/archlog_ORCL_20240109_1m2g5bn1_1_1.bak tag=TAG20240109T120904 comment=NONE channel c2: backup set complete, elapsed time: 00:00:01 channel c1: finished piece 1 at 2024-01-09 12:09:12 piece handle=/data3/backup/archlog_ORCL_20240109_1k2g5bn0_1_1.bak tag=TAG20240109T120904 comment=NONE channel c1: backup set complete, elapsed time: 00:00:08 Finished backup at 2024-01-09 12:09:12 Starting backup at 2024-01-09 12:09:13 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set including current control file in backup set channel c1: starting piece 1 at 2024-01-09 12:09:14 channel c1: finished piece 1 at 2024-01-09 12:09:15 piece handle=/data3/backup/cntrl_55_1_55.bak tag=TAG20240109T120913 comment=NONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 2024-01-09 12:09:15 Starting Control File and SPFILE Autobackup at 2024-01-09 12:09:15 piece handle=/data3/data/ORCL/autobackup/2024_01_09/o1_mf_s_1157803755_lsskqchb_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2024-01-09 12:09:16 released channel: c1 released channel: c2复制
将备份文件传到备库
[root@jcyjs4 data3]# scp -r ./backup/ 103.163.8.155:/data3/backup Password: backdata_ORCL_20240109_1i2g5bf9_1_1.bak 100% 20GB 172.5MB/s 01:57 backdata_ORCL_20240109_152g599i_1_1.bak 100% 52GB 172.6MB/s 05:07 archlog_ORCL_20240109_1k2g5bn0_1_1.bak 100% 773MB 173.9MB/s 00:04 cntrl_55_1_55.bak 100% 13MB 123.4MB/s 00:00 backdata_ORCL_20240109_1h2g5bf9_1_1.bak 100% 22GB 173.4MB/s 02:08 backdata_ORCL_20240109_192g59q4_1_1.bak 100% 42GB 170.2MB/s 04:11 archlog_ORCL_20240109_1l2g5bn0_1_1.bak 100% 45MB 172.1MB/s 00:00 backdata_ORCL_20240109_1b2g5a7o_1_1.bak 100% 42GB 169.5MB/s 04:13 backdata_ORCL_20240109_1g2g5b38_1_1.bak 100% 36GB 169.7MB/s 03:37 backdata_ORCL_20240109_1e2g5alb_1_1.bak 100% 42GB 172.2MB/s 04:10 backdata_ORCL_20240109_172g59hh_1_1.bak 100% 23GB 173.3MB/s 02:18 backdata_ORCL_20240109_1d2g5alb_1_1.bak 98% 42GB 171.4MB/s 00:02 ETA backdata_ORCL_20240109_1d2g5alb_1_1.bak 100% 42GB 171.7MB/s 04:10 backdata_ORCL_20240109_1c2g5a7o_1_1.bak 100% 42GB 171.2MB/s 04:11 backdata_ORCL_20240109_1a2g59q4_1_1.bak 100% 41GB 170.3MB/s 04:09 backdata_ORCL_20240109_182g59hh_1_1.bak 100% 33GB 167.8MB/s 03:22 backdata_ORCL_20240109_1f2g5b38_1_1.bak 100% 41GB 171.4MB/s 04:07 archlog_ORCL_20240109_1m2g5bn1_1_1.bak 100% 4096 21.8MB/s 00:00 backdata_ORCL_20240109_162g599i_1_1.bak 100% 21GB 170.1MB/s 02:03复制
备库配置
修改mespfile.ora参数文件
修改前的mespfile.ora参数文件
orcl.__data_transfer_cache_size=0 orcl.__db_cache_size=46439333888 orcl.__inmemory_ext_roarea=0 orcl.__inmemory_ext_rwarea=0 orcl.__java_pool_size=939524096 orcl.__large_pool_size=671088640 orcl.__oracle_base='/data/oracle/oracle/db'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=12884901888 orcl.__sga_target=55834574848 orcl.__shared_io_pool_size=134217728 orcl.__shared_pool_size=6442450944 orcl.__streams_pool_size=1073741824 orcl.__unified_pga_pool_size=0 *.audit_file_dest='/data/oracle/oracle/db/admin/orcl/adump' *.audit_trail='NONE' *.compatible='19.0.0' *.control_files='/data/oracle/oracle/oradata/ORCL/control01.ctl','/data/oracle/oracle/oradata/ORCL/control02.ctl' *.db_block_size=8192 *.db_name='orcl' *.db_recovery_file_dest_size=53687091200 *.db_recovery_file_dest='/data3/data/' *.diagnostic_dest='/data/oracle/oracle/db' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.enable_goldengate_replication=TRUE *.local_listener='LISTENER_ORCL' *.log_archive_dest_1='location=/data3/data' *.memory_max_target=68719476736 *.memory_target=68719476736 *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=12884901888 *.processes=4480 *.remote_login_passwordfile='EXCLUSIVE' *.service_names='prod' *.sga_max_size=55834574848 *.sga_target=55834574848 *.streams_pool_size=1073741824 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1'复制
修改的参数包括如下
*.audit_file_dest='/data3/oracle/db/admin/orcldg/adump' *.control_files='/data3/oracle/oradata/ORCLDG/control01.ctl','/data3/oracle/oradata/ORCLDG/control02.ctl' *.db_recovery_file_dest='/data3/oracle/archdata/archivelog' *.log_archive_dest_1='location=/data3/oracle/archdata/archivelog'复制
新增的参数如下：
*.undo_tablespace='UNDOTBS1' *.db_unique_name=orcldg *.log_archive_config='DG_CONFIG=(orcl,orcldg)' *.fal_server='orcl' *.fal_client='orcldg' *.standby_file_management=auto复制
修改后的正式文件如下
orcl.__data_transfer_cache_size=0 orcl.__db_cache_size=46439333888 orcl.__inmemory_ext_roarea=0 orcl.__inmemory_ext_rwarea=0 orcl.__java_pool_size=939524096 orcl.__large_pool_size=671088640 orcl.__oracle_base='/data3/oracle/db'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=12884901888 orcl.__sga_target=55834574848 orcl.__shared_io_pool_size=134217728 orcl.__shared_pool_size=6442450944 orcl.__streams_pool_size=1073741824 orcl.__unified_pga_pool_size=0 *.audit_file_dest='/data3/oracle/db/admin/orcldg/adump' *.audit_trail='NONE' *.compatible='19.0.0' *.control_files='/data3/oracle/oradata/ORCLDG/control01.ctl','/data3/oracle/oradata/ORCLDG/control02.ctl' *.db_block_size=8192 *.db_name='orcl' *.db_recovery_file_dest_size=53687091200 *.db_recovery_file_dest='/data3/oracle/archdata/archivelog' *.diagnostic_dest='/data3/oracle/db' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.enable_goldengate_replication=TRUE *.local_listener='LISTENER_ORCL' *.log_archive_dest_1='location=/data3/oracle/archdata/archivelog' *.memory_max_target=68719476736 *.memory_target=68719476736 *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=12884901888 *.processes=4480 *.remote_login_passwordfile='EXCLUSIVE' *.service_names='prod' *.sga_max_size=55834574848 *.sga_target=55834574848 *.streams_pool_size=1073741824 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.db_unique_name=orcldg *.log_archive_config='DG_CONFIG=(orcl,orcldg)' *.fal_server='orcl' *.fal_client='orcldg' *.standby_file_management=auto复制
创建文件中对应的路径
mkdir -p /data3/oracle/archdata/archivelog mkdir -p /data3/oracle/oradata/ORCLDG mkdir -p /data3/oracle/db/admin/orcldg/adump复制
备库启动监听
[oracle@jcyjs3 archdata]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JAN-2024 17:10:27 Copyright (c) 1991, 2019, Oracle. All rights reserved. Starting /data3/oracle/db/product/19.3/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production Log messages written to /data3/oracle/db/diag/tnslsnr/jcyjs3/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jcyjs3)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 09-JAN-2024 17:10:28 Uptime 0 days 0 hr. 0 min. 1 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /data3/oracle/db/diag/tnslsnr/jcyjs3/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jcyjs3)(PORT=1521))) The listener supports no services The command completed successfully复制
将环境变量中的export ORACLE_SID的值修改为orcldg
export ORACLE_SID=orcldg --修改后使环境变量生效 source ~/.bash_profile复制
启动到nomount状态
备库使用mespfile.ora文件启动数据库到nomount状态，创建spfile文件，然后再通过spfile启动到nomount
[oracle@jcyjs3 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 10 08:42:30 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> SQL> SQL> startup nomount pfile='/home/oracle/mespfile.ora'; ORACLE instance started. Total System Global Area 5.5835E+10 bytes Fixed Size 30146136 bytes Variable Size 7784628224 bytes Database Buffers 4.7916E+10 bytes Redo Buffers 104071168 bytes SQL> SQL> SQL> create spfile from pfile='/home/oracle/mespfile.ora'; File created. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 5.5835E+10 bytes Fixed Size 30146136 bytes Variable Size 7784628224 bytes Database Buffers 4.7916E+10 bytes Redo Buffers 104071168 bytes复制
备库恢复控制文件，数据启动到mount状态
[oracle@jcyjs3 backup]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 10 08:52:08 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (not mounted) RMAN> restore standby controlfile from '/data3/oracle/backup/cntrl_55_1_55.bak'; Starting restore at 2024-01-10 08:52:33 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=5446 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/data3/oracle/oradata/ORCLDG/control01.ctl output file name=/data3/oracle/oradata/ORCLDG/control02.ctl Finished restore at 2024-01-10 08:52:34 RMAN>复制
数据库启动到mount状态
alter database mount;复制
测试主库和备库的连通性
主库连接备库
[oracle@jcyjs4 admin]$ sqlplus hxbtest/hxbtest@orcldg SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 10 09:16:22 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. ERROR: ORA-01033: ORACLE initialization or shutdown in progress Process ID: 0 Session ID: 0 Serial number: 0 Enter user-name: hxbtest Enter password: Last Successful login time: Wed Jan 10 2024 09:15:57 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0复制
备库连接主库
[oracle@jcyjs3 ~]$ sqlplus hxbtest/hxbtest@orcl SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 10 09:02:27 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Wed Jan 10 2024 09:17:11 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0复制
备库恢复数据库
catalog注册备份
RMAN> catalog start with '/data3/oracle/backup'; released channel: ORA_DISK_1 Starting implicit crosscheck backup at 2024-01-10 09:09:22 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=5446 device type=DISK Crosschecked 52 objects Finished implicit crosscheck backup at 2024-01-10 09:09:22 Starting implicit crosscheck copy at 2024-01-10 09:09:22 using channel ORA_DISK_1 Finished implicit crosscheck copy at 2024-01-10 09:09:22 searching for all files in the recovery area cataloging files... no files cataloged searching for all files that match the pattern /data3/oracle/backup List of Files Unknown to the Database ===================================== File Name: /data3/oracle/backup/backdata_ORCL_20240109_172g59hh_1_1.bak File Name: /data3/oracle/backup/archlog_ORCL_20240109_1k2g5bn0_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1e2g5alb_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1b2g5a7o_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1c2g5a7o_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1a2g59q4_1_1.bak File Name: /data3/oracle/backup/archlog_ORCL_20240109_1l2g5bn0_1_1.bak File Name: /data3/oracle/backup/archlog_ORCL_20240109_1m2g5bn1_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_162g599i_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_152g599i_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1d2g5alb_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_192g59q4_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1h2g5bf9_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1i2g5bf9_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_182g59hh_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1g2g5b38_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1f2g5b38_1_1.bak File Name: /data3/oracle/backup/cntrl_55_1_55.bak Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /data3/oracle/backup/backdata_ORCL_20240109_172g59hh_1_1.bak File Name: /data3/oracle/backup/archlog_ORCL_20240109_1k2g5bn0_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1e2g5alb_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1b2g5a7o_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1c2g5a7o_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1a2g59q4_1_1.bak File Name: /data3/oracle/backup/archlog_ORCL_20240109_1l2g5bn0_1_1.bak File Name: /data3/oracle/backup/archlog_ORCL_20240109_1m2g5bn1_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_162g599i_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_152g599i_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1d2g5alb_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_192g59q4_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1h2g5bf9_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1i2g5bf9_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_182g59hh_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1g2g5b38_1_1.bak File Name: /data3/oracle/backup/backdata_ORCL_20240109_1f2g5b38_1_1.bak File Name: /data3/oracle/backup/cntrl_55_1_55.bak RMAN>复制
crosscheck核对数据文件备份集
RMAN> crosscheck backup; using channel ORA_DISK_1 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/data/oracle/oracle/db/product/19.4/dbs/c-1573120881-20201123-00 RECID=1 STAMP=1057252431 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/data/oracle/oracle/db/product/19.4/dbs/c-1573120881-20210323-00 RECID=2 STAMP=1067961590 crosschecked backup piece: found to be 'EXPIRED' ... 日志省略 ... backup piece handle=/data3/backup/archlog_ORCL_20240109_1k2g5bn0_1_1.bak RECID=52 STAMP=1157803744 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/data3/oracle/backup/archlog_ORCL_20240109_1k2g5bn0_1_1.bak RECID=54 STAMP=1157879384 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/data3/oracle/backup/cntrl_55_1_55.bak RECID=70 STAMP=1157879385 Crosschecked 70 objects复制
备库恢复数据库
run{ set newname for database to '/data3/oracle/oradata/ORCLDG/%b'; restore database; switch datafile all; }复制
RMAN> run{ set newname for database to '/data3/oracle/oradata/ORCLDG/%b'; restore database; switch datafile all; }2> 3> 4> 5> executing command: SET NEWNAME Starting restore at 2024-01-10 09:12:08 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00004 to /data3/oracle/oradata/ORCLDG/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00056 to /data3/oracle/oradata/ORCLDG/test20.dbf channel ORA_DISK_1: reading from backup piece /data3/oracle/backup/backdata_ORCL_20240109_162g599i_1_1.bak channel ORA_DISK_1: piece handle=/data3/oracle/backup/backdata_ORCL_20240109_162g599i_1_1.bak tag=TAG20240109T112746 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:07:15 ... datafile 47 switched to datafile copy input datafile copy RECID=59 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test11.dbf datafile 48 switched to datafile copy input datafile copy RECID=60 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test12.dbf datafile 49 switched to datafile copy input datafile copy RECID=61 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test13.dbf datafile 50 switched to datafile copy input datafile copy RECID=62 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test14.dbf datafile 51 switched to datafile copy input datafile copy RECID=63 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test15.dbf datafile 52 switched to datafile copy input datafile copy RECID=64 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test16.dbf datafile 53 switched to datafile copy input datafile copy RECID=65 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test17.dbf datafile 54 switched to datafile copy input datafile copy RECID=66 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test18.dbf datafile 55 switched to datafile copy input datafile copy RECID=67 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test19.dbf datafile 56 switched to datafile copy input datafile copy RECID=68 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test20.dbf复制
备库创建standby redo logs
创建redo logs前，需要先核实主库redo log的文件大小，备库的redo logs文件大小必须和主库一直或者大于主库，另外备库的redo logs文件数量也需要多余主库一个
SQL> alter database add standby logfile thread 1 group 11('/data3/oracle/oradata/ORCLDG/standby_redo_logs/redo11.log') size 200M; Database altered. SQL> alter database add standby logfile thread 1 group 12('/data3/oracle/oradata/ORCLDG/standby_redo_logs/redo12.log') size 200M; Database altered. SQL> alter database add standby logfile thread 1 group 13('/data3/oracle/oradata/ORCLDG/standby_redo_logs/redo13.log') size 200M; SQL> alter database add standby logfile thread 1 group 14('/data3/oracle/oradata/ORCLDG/standby_redo_logs/redo14.log') size 200M;复制
查看standby 的信息
select group#,thread#,sequence#,archived,status from v$standby_log; GROUP# THREAD# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 11 1 0 YES UNASSIGNED 12 1 0 YES UNASSIGNED 13 1 0 YES UNASSIGNED 14 1 0 YES UNASSIGNED复制
备库开启应用
alter database recover managed standby database using current logfile disconnect for session;复制
查看相关进程
select process,status ,thread#,sequence# from v$managed_standby; PROCESS STATUS THREAD# SEQUENCE# --------- ------------ ---------- ---------- ARCH CONNECTED 0 0 DGRD ALLOCATED 0 0 DGRD ALLOCATED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 RFS IDLE 1 0 RFS IDLE 1 5637 MRP0 WAIT_FOR_LOG 1 5637 RFS IDLE 0 0 RFS IDLE 0 0 11 rows selected.复制
mrp0的状态为WAIT_FOR_LOG
重启数据库
SQL> startup force ; ORACLE instance started. Total System Global Area 5.5835E+10 bytes Fixed Size 30146136 bytes Variable Size 7784628224 bytes Database Buffers 4.7916E+10 bytes Redo Buffers 104071168 bytes Database mounted. ORA-10458: standby database requires recovery ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/data3/oracle/oradata/ORCLDG/system01.dbf'复制
上述问题是由于rman的备份集与致日志记录的SCN与控制文件不符，不能完成checkpoint事件，需要进行介质恢复
alter database recover managed standby database cancel; alter database open read only;复制
查看一下OPEN_MODE发现此时是READ_ONLY状态
select open_mode from v$database;复制
再看一下进程，有RFS进程
select process,status ,thread#,sequence# from v$managed_standby;复制
再开启一下应用
alter database recover managed standby database using current logfile disconnect ;复制
再看一下相关进程
select process,status ,thread#,sequence# from v$managed_standby;复制
此时发现MRP进程，且状态为APPLYING_LOG，则搭建成功。
– / END / –
可以通过下面的方式联系我
如果这篇文章为你带来了灵感或启发，就请帮忙点赞、收藏、转发；如果文章中不严谨或者错漏之处，请及时评论指正。非常感谢！
最后修改时间：2025-02-06 11:02:18
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
星星之火
1年前
评论
0知人者智，自知者明。胜人者有力，自胜者强。知足者富。强行者有志。不失其所者久。死而不亡者寿。
1年前
点赞 评论
相关阅读
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
573次阅读
2025-04-15 17:24:06
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
519次阅读
2025-04-18 14:18:38
Oracle SQL 执行计划分析与优化指南
Digital Observer
479次阅读
2025-04-01 11:08:44
墨天轮个人数说知识点合集
JiekeXu
465次阅读
2025-04-01 15:56:03
XTTS跨版本迁移升级方案（11g to 19c RAC for Linux）
zwtian
463次阅读
2025-04-08 09:12:48
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
446次阅读
2025-04-22 00:20:37
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
445次阅读
2025-04-20 10:07:02
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
423次阅读
2025-04-22 00:13:51
Oracle 19c RAC更换IP实战，运维必看！
szrsu
408次阅读
2025-04-08 23:57:08
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
392次阅读
2025-04-17 17:02:24
